数据库笔记

  • 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
# 使用事务操作,需要使用BEGINCOMMIT关键字来包裹
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,:: 来监听IPV4IPV6全网段.修改完重启服务

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'