数据库笔记

发表于 更新于 分类于 阅读次数：
  • PostgreSQL
  • MongoDB
  • MySQL
  • Oracle

PostgreSQL

PostgreSQL是自由的对象-关系型数据库服务器（数据库管理系统），在灵活的BSD许可证下发行。它在其他开放源代码数据库系统（比如MySQL和Firebird），和专有系统（比如Oracle、Sybase、IBM的DB2和Microsoft SQL Server）之外，为用户提供了又一种选择。

安装

安装环境系统为CentOS7.6 X86_64, 安装方式为yum方式安装, 安装的数据库版本为 11.0 版本

  • 添加源

    1
    yum install https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-7.6-x86_64/pgdg-centos11-11-2.noarch.rpm

  • 安装软件

    1
    yum install postgresql11-client postgresql11-server postgresql11-contrib

  • 初始化数据库

    1
    /usr/pgsql-11/bin/postgresql-11-setup initdb

  • 启动服务

    1
    2
    3
    4
    systemctl start postgresql-11.service
    systemctl status postgresql-11.service
    firewall-cmd --permanent --add-service=postgresql 
    firewall-cmd --reload

  • 其他信息

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    # 配置文件位置
    $ sudo vim /var/lib/pgsql/11/data/postgresql.conf
    # 监听外部请求->修改配置文件
    # - Connection Settings -
    listen_addresses = '*'   # 这里改为监听全网
    #listen_addresses = 'localhost'         # what IP address(es) to listen on;
                                          # comma-separated list of addresses;
                                          # defaults to 'localhost'; use '*' for all
                                          # (change requires restart)
    #port = 5432     # 默认端口

    # 允许外部访问,编辑下列配置文件,修改认证方法为md5
    $ sudo vim /var/lib/pgsql/11/data/pg_hba.conf
    # TYPE  DATABASE        USER            ADDRESS                 METHOD

    # "local" is for Unix domain socket connections only
    local   all             all                                     trust  # 本地访问
    # IPv4 local connections:
    host    all             all             0.0.0.0/0           md5   # ipv4 允许全网访问,密码验证
    # host    all             all             127.0.0.1/32            md5 
    # IPv6 local connections:
    host    all             all             ::1/128                 md5
1
2
3
4
5
6
7
  
- 配置用户,设置管理员用户密码

  ```bash
  # 配置管理员(postgres)用户密码
  [root@localhost ~]# su - postgres
  -bash-4.2$ psql -c "alter user postgres with password '123456'"   # 配置管理员密码,也可配置其他用户

shell操作

Windows版本有图形化管理界面pgAdmin直接使用即可

  • 登录数据库

    1
    2
    3
    4
    5
    6
    psql -h localhost -p 5432 -U test_user -d test_db
    # -h : 指定主机地址
    # -p : 指定端口
    # -U : 指定用户名
    # -d : 指定数据库
    # 如果不指定数据库,则会登录默认和用户名相同的数据库

  • psql相关命令

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    # 登录psql 创建用户和数据库并赋予权限
    su - postgres  # 切换到数据库管理员账户
    psql  # 切换到数据库提示符下
    \d   # 查看数据库
    create user <UserName> with password '<UserPassword>';   # 创建用户
    create database <DataBaseName> owner <UserName>;   # 创建数据库
    grant all privileges on database <DBName> to <DBUser>;;  # 赋予权限
    \q  # 退出

    # 登录数据库看上面的命令

    \h      #查看所有的sql关键字
    \?      #命令行操作的帮助
    \d      #查看当前schema 中所有的表
    \d+     #显示更多内容,比如可以查询分区表的信息
    \q      #退出pg命令行
    \d      #schema.table 查看表的结构
    \x      #横纵显示切换
    \dT+    #显示扩展类型相关属性及描述
    \dx     #显示已安装的扩展插件
    \l      #列出所有的数据库
    \timing #显示执行时间
    \c database_name        #切换数据库
    set search to schema    #切换schema
    explain sql             #解释或分析sql执行过程

事务操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
# 使用事务操作,需要使用BEGIN和COMMIT关键字来包裹
BEGIN;
UPDATE accounts SET balance = balance - 100.00
    WHERE name = 'Alice';
-- etc etc
COMMIT;
# 使用保存点和ROLLBACK来回滚变更操作
BEGIN;
UPDATE accounts SET balance = balance - 100.00
    WHERE name = 'Alice';
SAVEPOINT my_savepoint;   # 建立保存点
UPDATE accounts SET balance = balance + 100.00
    WHERE name = 'Bob';
-- oops ... forget that and use Wally's account
ROLLBACK TO my_savepoint;  # 回滚到保存点
UPDATE accounts SET balance = balance + 100.00   # 回滚完成之后继续执行本地语句
    WHERE name = 'Wally';
COMMIT;

OVER (PARTITION BY)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT depname, empno, salary, rank() OVER (PARTITION BY depname ORDER BY salary DESC) FROM empsalary;
  depname  | empno | salary | rank
-----------+-------+--------+------
 develop   |     8 |   6000 |    1
 develop   |    10 |   5200 |    2
 develop   |    11 |   5200 |    2
 develop   |     9 |   4500 |    4
 develop   |     7 |   4200 |    5
 personnel |     2 |   3900 |    1
 personnel |     5 |   3500 |    2
 sales     |     1 |   5000 |    1
 sales     |     4 |   4800 |    2
 sales     |     3 |   4800 |    2
(10 rows)

常用SQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
-- 查询值为空的时候设置默认值
SELECT COALESCE(long, '') FROM tabName;

-- 创建表
create table test_tab(
test_col int null
)

-- 删除表
drop table test_tab;

-- 增加列,设置主键,设置自增序列
alter table test_tab 
add column id SERIAL primary key,   -- 自动建立 test_tab_id_seq 序列表
add column test_col2 varchar(255) null;

-- 修改字段类型,同增加一样支持同时操作多列
ALTER TABLE test_tab
ALTER COLUMN test_col TYPE varchar(20);

-- 修改列为主键
-- ALTER TABLE TABLE ADD PRIMARY KEY (column_1, column_2);
ALTER TABLE test_tab ADD PRIMARY KEY (id);


-- 删除主键
-- 查看主键
//test_db=> \d test_tab
//                                     Table "public.test_tab"
//  Column   |          Type          | Collation | Nullable |               Default                
//-----------+------------------------+-----------+----------+--------------------------------------
// test_col  | character varying(20)  |           |          | 
// id        | integer                |           | not null | nextval('test_tab_id_seq'::regclass)
// test_col2 | character varying(255) |           |          | 
//Indexes:
//    "test_tab_pkey" PRIMARY KEY, btree (id) 
//
//test_db=> 
ALTER TABLE test_tab DROP CONSTRAINT test_tab_pkey;

-- with as 语法
with tmp_tab as (select id from test_tab)
select * from tmp_tab;

-- 创建序列(自增序列) 最新的数据库支持 serial 字段,自增字段
CREATE SEQUENCE users_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
-- 修改存在的字段为序列字段
alter table users alter column id set default nextval('users_id_seq');

-- 视图操作相关
-- 创建视图
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
ORDER BY column_name
-- 删除视图
DROP VIEW view_name;
-- 更新视图结构
CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition;
-- 其中 CREATE OR REPLACE VIEW 是 pg 的语言扩展功能。

特别记录

不熟练的操作记录

转换字符串浮点数到double类型

使用case根据正则判断然后转换数据

1
2
3
4
5
6
WITH subquery AS (SELECT '1.1232'::text as value) 
   SELECT 
   CASE WHEN value ~ '^(-)?[0-9]+\.?([0-9]+)?$'
        THEN value::double precision 
        ELSE 0 
   END FROM subquery;

随机数生成

按照指定范围生成整数或者小数

1
2
3
4
-- 指定范围的小数 0.00001 <= x < 0.001 并保留 6位小数
SELECT round((random()*(0.001-0.00001)+0.00001)::numeric ,6);
-- 指定范围的整数  10 <= x < 25之间
SELECT floor(random()*(25-10)+10);

查看正在执行的sql

用于解决莫名其妙的等待sql执行完成错误

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT 
    procpid, 
    start, 
    now() - start AS lap, 
    current_query 
FROM 
    (SELECT 
        backendid, 
        pg_stat_get_backend_pid(S.backendid) AS procpid, 
        pg_stat_get_backend_activity_start(S.backendid) AS start, 
       pg_stat_get_backend_activity(S.backendid) AS current_query 
    FROM 
        (SELECT pg_stat_get_backend_idset() AS backendid) AS S 
    ) AS S ,pg_stat_activity pa
WHERE 
   current_query <> '<IDLE>' and  procpid<> pg_backend_pid() and pa.pid=s.procpid and pa.state<>'idle'
ORDER BY 
   lap DESC;

运行上面的sql会查看到当前正在执行sql语句,第一列是进程id,进入系统直接kill此进程,然后重启后台服务即可正常操作了

1
kill -9 <ID>

MongoDB

MonggoDB是一种以文档形式存储数据的数据库,适合大文档存取使用,数据是key, value 形式

MongoDB安装

本安装操作参考官网手册安装MongoDB Manual
环境: CentOS7 安装方式: yum

配置yum

因为官网的yum源访问速度太慢,这里切换到阿里云的yum源,注意示例的版本是4.0

1
2
3
4
5
[mongodb-org-4.0]
name=MongoDB Repository
baseurl=http://mirrors.aliyun.com/mongodb/yum/redhat/7Server/mongodb-org/4.0/x86_64/
gpgcheck=0
enabled=1

安装

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 素质二连,建立缓存
[root@localhost yum.repos.d]# yum clean all
[root@localhost yum.repos.d]# yum makecache
# 安装
[root@localhost yum.repos.d]# sudo yum install -y mongodb-org

Installed:
  mongodb-org.x86_64 0:4.0.9-1.el7                                                                                        

Dependency Installed:
  mongodb-org-mongos.x86_64 0:4.0.9-1.el7 mongodb-org-server.x86_64 0:4.0.9-1.el7 mongodb-org-shell.x86_64 0:4.0.9-1.el7
  mongodb-org-tools.x86_64 0:4.0.9-1.el7 

Complete!
[root@localhost yum.repos.d]#

服务启动

服务默认端口号是27017

1
2
3
4
5
6
7
8
9
10
11
12
13
# 启动服务
systemctl start mongod.service 
# 停止服务
systemctl stop mongod.service 
# 重启服务
systemctl restart mongod.service 

# 防火墙放行
[root@localhost yum.repos.d]# firewall-cmd --permanent --add-service=mongodb
success
[root@localhost yum.repos.d]# firewall-cmd --reload
success
[root@localhost yum.repos.d]#

修改配置文件

因为使用的是yum方式安装,这里直接去/etc/下修改mongod.conf文件,找到下面这段该修改为0.0.0.0,:: 来监听IPV4和IPV6全网段.修改完重启服务

1
2
3
4
5
6
7
# network interfaces
net:
  port: 27017
  bindIp: 0.0.0.0
# 打开认证
security:               
  authorization: enabled  #开启密码验证

MongoDB常用操作

Linux中在shell中的任何地方使用mongo进入MongoDB的shell界面
shell中使用use创建数据库,但是库中必须有集合才会生效

库->集合->文档

可以把集合简单的理解为关系型数据库中的表,文档是关系型数据库中表中的记录

下图展示了向一个集合中插入一条数据的命令,不需要事先建立集合

mongodb插入数据

连接mongo数据库

下面这三种方式是一样的

1
2
3
mongo mongodb://mongodb0.example.com:28015
mongo --host mongodb0.example.com:28015
mongo --host mongodb0.example.com --port 28015

认证方式连接

指定了--password参数,不输入密码,shell会自动提示输入密码

1
mongo --username alice --password --authenticationDatabase admin --host mongodb0.examples.com --port 28015

Shell操作

mongo shell 是一个完备的Java script 解释器，可以执行任何JavaScript程序。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
# 显示数据库列表
show dbs
# 显示正在使用的数据库
db
# 切换数据库
use <database>
# 删除数据库
db.dropDatabase()
# 查看集合
show collections
# 查询集合中的信息
db.<collection name>.find()
db.test.find({}).forEach(printjson) # 查询集合内容并循环打印json形式的数据
# 带条件查询
db.<collection name>.find({"name": "zhangsan"})
db.tmpCollection.find({$or: [{name: "zhangsan"}, {age: 24}]})
db.tmpCollection.find({name: {$in: ["zhangsan", "lisi"]}})
# mongo支持select语法查询数据(在mongo shell 中实验失败,在数据库管理软件中能用)
select * from <collection name>
SELECT * FROM tmpCollection WHERE 1=1 AND (name='zhangsan' OR age=24)
# 插入数据
db.test.insertMany([{"name": "zhangsan", "age": 23}, {"name": "lisi", "age": 23}])
db.test.insertOne({"name": "wangwu", "age": 24})
# 删除数据，括号内为条件，当删除多个的时候会删除所有符合条件的，删除单个则执行一次删除一个
# 删除不存在的数据，也会操作成功不会失败。
db.test.deleteMany({"age": 23})
db.test.deleteOne({"name": "wangwu"})
db.test.deleteMany({})
# 更新数据
db.test.updateOne(
   { name: "wangwu" },  # 更新的目标，这里是updateOne所以就算重复也只更新找到的第一条
   {
     $set: { age: 24},  # 更新的字段
     $currentDate: { lastModified: true }  # 新增字段(lastModified)并设置为当前时间
   }
)

db.test.updateMany(
   { age: 23 },   # 修改所有age为23的数据
   {
     $set: { age: 30},  # 修改为 30
     $currentDate: { lastModified: true }
   }
)

db.test.updateOne({name: "张三"}, {$set: {"other.sex": "女"}}) # 多层级数据修改

# update 命令格式
db.collection.update(criteria,objNew,upsert,multi)
#criteria：查询条件
#objNew：update对象和一些更新操作符
#upsert：如果不存在update的记录，是否插入objNew这个新的文档，true为插入，默认为false，不插入。
#multi：默认是false，只更新找到的第一条记录。如果为true，把按条件查询出来的记录全部更新。

# 删除字段
db.test.update({},{$unset:{'sex':''}},false, true)
# 删除集合
db.test.drop()

# 使用正则匹配，修改匹配出的记录
db.test.find({'addresses.city':{'$regex': /菏泽市定陶县/ }}).forEach( 
function(item) {
    var tmp = String(item.addresses.city)
    tmp = tmp.replace('菏泽市定陶县','菏泽市定陶县区')
    if (tmp == null){
        print(item.addresses.city)   
    }

    item.addresses.city = tmp;
    
    db.test.save(item);  # 注意save是覆盖操作
} 
);

MongoDB问题记录

创建管理账户: 消除以下告警

1
2
3
4
5
6
7
8
9
10
11
12
Server has startup warnings: 
2019-04-27T09:45:30.588+0800 I CONTROL  [initandlisten] 
2019-04-27T09:45:30.588+0800 I CONTROL  [initandlisten] ** WARNING: Access control is not enabled for the database.
2019-04-27T09:45:30.588+0800 I CONTROL  [initandlisten] **          Read and write access to data and configuration is unrestricted.
2019-04-27T09:45:30.588+0800 I CONTROL  [initandlisten] 
2019-04-27T09:45:30.588+0800 I CONTROL  [initandlisten] 
2019-04-27T09:45:30.589+0800 I CONTROL  [initandlisten] ** WARNING: /sys/kernel/mm/transparent_hugepage/enabled is 'always'.
2019-04-27T09:45:30.589+0800 I CONTROL  [initandlisten] **        We suggest setting it to 'never'
2019-04-27T09:45:30.589+0800 I CONTROL  [initandlisten] 
2019-04-27T09:45:30.589+0800 I CONTROL  [initandlisten] ** WARNING: /sys/kernel/mm/transparent_hugepage/defrag is 'always'.
2019-04-27T09:45:30.589+0800 I CONTROL  [initandlisten] **        We suggest setting it to 'never'
2019-04-27T09:45:30.589+0800 I CONTROL  [initandlisten]

解决方式

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
# 第一个警告是因为数据库没有配置管理员密码,创建管理员密码
# 进入Mongo的shell中
> use admin
switched to db admin
> db.createUser(
...   {
...     user: "userAdmin",  // 用户名
...     pwd: "123456",   // 密码
...     roles: [ { role: "userAdminAnyDatabase", db: "admin" } ]  // 角色权限和数据库
...   }
... )
Successfully added user: {
        "user" : "userAdmin",
        "roles" : [
                {
                        "role" : "userAdminAnyDatabase",
                        "db" : "admin"
                }
        ]
}
> 
# 下面两个警告的解决方案
[root@localhost yum.repos.d]# sudo echo "never" > /sys/kernel/mm/transparent_hugepage/enabled
[root@localhost yum.repos.d]# sudo echo "never" >  /sys/kernel/mm/transparent_hugepage/defrag

MariaDB(MySQL)

MariaDB数据库的一些常用操作

安装

去官网找到yum源配置点击这里,执行下面的命令替换为中科大的源

1
2
3
sudo sed -i 's#yum.mariadb.org#mirrors.ustc.edu.cn/mariadb/yum#' /etc/yum.repos.d/mariadb.repo
# 建议使用 HTTPS
sudo sed -i 's#http://mirrors.ustc.edu.cn#https://mirrors.ustc.edu.cn#g' /etc/yum.repos.d/mariadb.repo

示例：

1
2
3
4
5
[mariadb]
name = MariaDB
baseurl = https://mirrors.ustc.edu.cn/mariadb/yum/10.3/centos7-amd64
gpgkey=https://mirrors.ustc.edu.cn/mariadb/yum/RPM-GPG-KEY-MariaDB
gpgcheck=1

执行安装

1
yum install MariaDB-server MariaDB-client

启动服务&防火墙设置

1
2
3
4
5
6
7
8
# mariadb 10.4 版本使用这个命令启动: systemctl start mariadb
# 因为 10.4 版本服务名称变更, 由 mysqld -> mariadb
[root@localhost yum.repos.d]# systemctl start mysqld
[root@localhost yum.repos.d]# firewall-cmd --permanent --add-service=mysql
success
[root@localhost yum.repos.d]# firewall-cmd --reload 
success
[root@localhost yum.repos.d]#

运行初始化工具

1
[root@localhost ~]# mysql_secure_installation

字符集问题

  • 修改表和表中左右字段的字符集方法
1
ALTER TABLE <table_name> CONVERT TO CHARACTER SET <character_name>

MySQL实现ROW_NUMBER() OVER (PARTITION BY ORDER BY )

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
-- 创建测试数据
create table score_info(
 id int primary key auto_increment,
 score int,
 class_name int
)
go
insert into score_info(score, class_name) values(70, 1),(80, 1),(100, 1),(77, 1),(86, 1),(64, 1)
go
insert into score_info(score, class_name) values(70, 2),(80, 2),(100, 2),(77, 2),(86, 2),(64, 2)
go
insert into score_info(score, class_name) values(70, 3),(80, 3),(100, 3),(77, 3),(86, 3),(64, 3)
go
select * from score_info

-- 对测试数据的class_name进行分组,按score进行降序排序
SELECT
    class_name,
    score,
    rank 
FROM
    (   SELECT
            a.*,
            IF(@p=a.class_name,@r:=@r+1,@r:=1) AS rank,   -- 判断如果,变量p等于class_name那么r递增,反之为1
            @p:= a.class_name  -- 给变量p赋值 class_name (被分组的列)
        FROM
            score_info a,  -- 原数据表
            (   SELECT
                    @p:=NULL,
                    @r:=0) v   -- 创建变量p和r
        ORDER BY   -- 重点,必须 - 优先 - 对被分组的列进行排序
            a.class_name,
            a.score DESC ) tmp_result
-- 实现原理: order by 执行后会把每组的数据排好序,然后使用if三元表达式,来对每条数据追加一个序号
            
-- 执行结果
 class_name     score     rank    
 -------------  --------  ------- 
 1              100       1       
 1              86        2       
 1              80        3       
 1              77        4       
 1              70        5       
 1              64        6       
 2              100       1       
 2              86        2       
 2              80        3       
 2              77        4       
 2              70        5       
 2              64        6       
 3              100       1       
 3              86        2       
 3              80        3       
 3              77        4       
 3              70        5       
 3              64        6

MySQL插入或者更新

在程序中,经常会用到插入或者更新这项功能,来确保数据的唯一性,mysql支持两种插入更新方式

  • 插入数据,如果存在则更新指定的字段进行更新

    1
    insert into score_info(id, score, class_name) values(1, 99, 5) on DUPLICATE key update class_name=100, score=55

  • 插入数据,全部更新为新值

    1
    replace into score_info(id, score, class_name) values(1,1000, 5)

时间转换

  • 日期类型转字符串: date 参数是合法的日期。format 规定日期/时间的输出格式。
1
select DATE_FORMAT(date,format) from table
  • 字符串时间转日期
1
select str_to_date('2018-08-16 08:00:00','%Y-%m-%d %H:%i:%s')
格式 描述
%a 缩写星期名
%b 缩写月名
%c 月，数值
%D 带有英文前缀的月中的天
%d 月的天，数值(00-31)
%e 月的天，数值(0-31)
%f 微秒
%H 小时 (00-23)
%h 小时 (01-12)
%I 小时 (01-12)
%i 分钟，数值(00-59)
%j 年的天 (001-366)
%k 小时 (0-23)
%l 小时 (1-12)
%M 月名
%m 月，数值(00-12)
%p AM 或 PM
%r 时间，12-小时（hh:mm:ss AM 或 PM）
%S 秒(00-59)
%s 秒(00-59)
%T 时间, 24-小时 (hh:mm:ss)
%U 周 (00-53) 星期日是一周的第一天
%u 周 (00-53) 星期一是一周的第一天
%V 周 (01-53) 星期日是一周的第一天，与 %X 使用
%v 周 (01-53) 星期一是一周的第一天，与 %x 使用
%W 星期名
%w 周的天 （0=星期日, 6=星期六）
%X 年，其中的星期日是周的第一天，4 位，与 %V 使用
%x 年，其中的星期一是周的第一天，4 位，与 %v 使用
%Y 年，4 位
%y 年，2 位

常见错误记录

  • Root用户远程登录失败
    虽然开启了允许远程登录,但是有时候就是莫名其妙登录失败,无法连接数据库
1
2
3
use mysql;   # 进入mysql库
update user set host = '%' where user = 'root';  #这一行执行可能会报错,不用管,直接执行下面这句
FLUSH PRIVILEGES;

Oracle

Oracle数据库的一些常用操作

创建表

1
2
3
4
5
CREATE TABLE <库名>.<表名>(
    id number null,
    name varchar2(32) null,
    insert_time date null
)

表字段修改删除添加操作

1
2
3
4
5
6
alter table tablename add (column datatype default,….)  -- 添加字段
alter table tablename modify (column datatype default,….)  -- 修改字段
alter table tablename drop (column)  -- 删除字段
alter table tablename ADD unique(`column_name`)  -- 添加唯一字段
alter table tablename rename column old_column to new_column   -- 修改列名
alter table tablename rename to new_tablename   -- 修改表名

表的相关操作

1
2
3
4
create table table_name_new as select * from table_name_old where 1=2; -- 只复制表结构,删除where或者条件为True则复制表和表数据
delete from table_name  -- 清空表数据,加where条件删除符合条件的数据
TRUNCATE TABLE table_name  -- 直接清空表数据,不会产生日志,貌似无法恢复
update table_name set column_name=column_value where conditions  -- 更新表记录,where后面跟条件,否则整个表都会变更

时间转换

  • to_date(‘string time’, ‘format’) 字符串时间转datetime
1
select to_date('2018-08-23 12:00:00','yyyy-mm-dd hh24:mi:ss') from dual
  • to_char(datetime_column, ‘format’) datetime类型转字符串
1
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual
格式 描述
YYYY 四位表示的年份
YYY，YY，Y 年份的最后三位、两位或一位，缺省为当前世纪
MM 01~12的月份编号
MONTH 九个字符表示的月份，右边用空格填补
MON 三位字符的月份缩写
WW 一年中的星期
D 星期中的第几天
DD 月份中的第几天
DDD 年所中的第几天
DAY 九个字符表示的天的全称，右边用空格补齐
HH，HH12 一天中的第几个小时，12进制表示法
HH24 一天中的第几个小时，取值为00~23
MI 一小时中的分钟
SS 一分钟中的秒
SSSS 从午夜开始过去的秒数

获取分组后记录详细数据

举例: 按表的A字段分组,获取B字段最大值的那条记录的其他信息
使用场景: 某表按场景id进行分组,现要求获取分组后最大值的详细记录时间,这时候就无法使用group by进行分组了,因为group by分组可以获取最大时间,但是无法获取这个最大时间对应的时间,使用max(time)是错误的,如果把时间加入group by会造成分组失败.

1
select * from (select t.*, row_number() OVER (PARTITION BY <COL_1> ORDER BY <COL_2 desc) rn from (<TABLE>) t )where rn=1

SQL说明: 按<TABLE>中的COL_1进行分组,然后按COL_2进行排序,升序或者降序并赋予行号, 最后外层SQL直接获取行号为1的就是最上面的一条结果

对数据进行分组计算

使用OVER(PARTITION BY 字段名),按照指定字段对数据进行分组,然后计算.例如获取一个月每天的数据之和

1
2
3
4
5
6
7
8
9
select 
distinct    -- 去重数据
start_time,  -- 时间
sum(data) OVER(PARTITION BY start_time) as "数据"   -- OVER(PARTITION BY start_time) 按什么字段进行分组
from tanble_name
where 1=1
AND start_time >= to_date('2019-01-26 00:00:00','yyyy-mm-dd hh24:mi:ss')
AND start_time <= to_date('2019-02-25 23:59:59','yyyy-mm-dd hh24:mi:ss')
order by start_time

多张表同时关联

举例: 以A表为准left joinB表,C表,D表…. 

1
2
3
4
5
select * from
table1 left join table2 on 条件1   
left join table3 on 条件2   
left join table4 on 条件3  
where 条件4

锁表查询和解锁

查询因为某种原因操作,表被锁死,只能查询无法插入数据,无法删除数据的问题

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 查询锁表信息
select sess.sid, 
    sess.serial#, 
    lo.oracle_username, 
    lo.os_user_name, 
    ao.object_name, 
    lo.locked_mode 
    from v$locked_object lo, 
    dba_objects ao, 
    v$session sess 
where ao.object_id = lo.object_id and lo.session_id = sess.sid

-- 4712为sid, 2807为serial#  <--这是删除锁表操作
alter system kill session '4712,2807'

建立自增序列

Oracle并没有像MySQL一样的自增属性,只能通过建立序列和触发器来实现这个功能

  • 序列参数说明
1
2
3
4
5
6
INCREMENT BY： 指定序列号之间的间隔，该值可为正的或负的整数，但不可为0。序列为升序。忽略该子句时，缺省值为1。
START WITH：指定生成的第一个序列号。在升序时，序列可从比最小值大的值开始，缺省值为序列的最小值。对于降序，序列可由比最大值小的值开始，缺省值为序列的最大值。
MAXVALUE：指定序列可生成的最大值。
NOMAXVALUE：为升序指定最大值为1027，为降序指定最大值为-1。
MINVALUE：指定序列的最小值。
NOMINVALUE：为升序指定最小值为1。为降序指定最小值为-1026。
  • 建立序列
1
2
3
4
5
create sequence computer_room_id
increment by 1
start with 1
minvalue 1
maxvalue 999999999
  • 修改序列
1
2
3
4
alter sequence computer_room_id
   increment by 2  
   minvalue 1
   maxvalue 999999999
  • 使用序列
1
2
3
4
-- 获取
select computer_room_id.Nextval from dual
-- 插入时获取
insert into computer_room_info(id, city_name, room_name) values(computer_room_id.Nextval,'city','room')

同时插入多条数据

Oracle的insert和MySQL有点区别,insert后面的values不能写多个

1
2
3
4
INSERT all
INTO table_1(col_1,col_2) values(val_1, val_2)
INTO table_2(col_1,col_2) values(val_1, val_2)
SELECT 1 FROM DUAL

带条件的INSERT ALL

1
2
3
4
5
6
7
8
9
INSERT ALL
    WHEN order_total < 100000 THEN
        INTO small_orders
    WHEN order_total > 100000 AND order_total < 200000 THEN
        INTO medium_orders
    ELSE
        INTO large_orders
    SELECT order_id, customer_id, order_total, sales_rep_id 
      FROM orders

插入或者更新

从T2表获取数据插入或者更新T1表的内容

1
2
3
4
MERGE INTO  TEST T1
USING (SELECT '2' as A FROM dual) T2 on (T1.ID=T2.ID)
WHEN MATCHED THEN UPDATE SET T1.NAME='newtest2'
WHEN NOT MATCHED THEN  INSERT (T1.ID, T1.NAME) VALUES ('1', 'newtest2');

字符串截取

  • substr函数
1
2
substr(截取字符, 开始位置, 结束位置)
substr(截取字符, 开始位置)    -- 默认结束位置为末尾
  • instr函数
1
2
instr(原字符串, 目标字符串)  -- 获取字符的开始位置
instr(原字符串, 目标字符串, 开始位置, 第几次出现)

字符串截取示例
name 大概是这样子zhangsan_123,以下sql截取出名字,使用substr和instr

1
select * from tableName where substr(name, 0, instr(name, '_') - 1) = 'zhangsan'

常见错误记录

  • ORA-00932: 数据类型不一致: 应为 -, 但却获得 CLOB
    数据库字段为clob类型,直接where clob_column='string'查询时,数据库抛出本错误.
1
select * from table where to_char(clob_column)='string'