04-SQL开发

SQL开发

• DCL-数据控制语言：grant授权、revoke撤权
• DDL-数据定义语言：create、drop、alter 对表增删改
• DML-数据操作语言：insert、dedlete、update 对表中数据增删改
• DQL-数据查询语言：select
• TCL-事务控制语言：commit、rollback

DCL

用户管理

create user 用户名@'访问控制列表' identified by '密码';
-- 创建用户并赋予密码，此时用户无任何权限
-- MySQL用户由两部分组成，用户名和访问控制列表（允许访问的IP）
-- 访问控制列表中的百分号代表任意网段，比如192.168.1.%代表192.168.1网段下的所有IP，%代表所以网段
drop user 用户名@'访问控制列表';
-- 删除用户

权限

• 系统权限（维护数据库权限）：create database、create table、create view等
• 对象权限（对数据库对象处理权限）：insert、update、delete、select、execute
• all privileges：全部权限

权限管理

• 授权管理要求用户必须存在
• 授权时只能授予当前用户具有的权限
• 回收权限时只能回收已经赋予的权限

授予权限

grant 权限列表 on 数据库对象 to 用户列表
-- 多个权限或用户使用逗号分隔，比如：insert,update,delete
-- 数据库对象可以使用星号通配符，比如：test.*代表test库下的所有表
-- 单个权限可使用括号进行对表中某一列进行权限控制，比如：select(column11,columnl2)代表只能查询column1和column2这两列

回收权限

revoke 权限列表 on 数据库对象 from 用户列表
-- 同样的多个权限或用户使用逗号分隔
-- 同样的数据库对象可以使用星号通配符
-- 同样的单个权限可使用括号进行对表中某一列进行权限控制

角色管理

MySQL8.0以上支持角色访问控制

create role 角色列表;	-- 创建角色
grant 权限列表 on 数据库对象 to 角色列表;	-- 赋予角色权限
grant 角色 to 用户列表;	-- 赋予用户角色
revoke role from 用户列表;	-- 回收用户角色
revoke 权限列表 on 数据库对象 to 角色列表;	-- 回收角色权限
drop role 角色列表;	-- 删除角色

DDL

数据库

create datebase 数据库名 character set = 'utf8' collate 'utf8_bin';
-- 后面参数分别指定了字符集和排序规则，可省，省略后使用默认字符集和排序规则
-- ci后缀即case insensitive，不区分大小写
-- cs后缀即case sensitive，区分大小写
-- bin后缀即binary，二进制的比较
alter database 数据库名 character set = 'utf8' collate 'utf8_bin';	-- 只能修改字符集和排序规则
drop database 数据库名;	-- 删除数据库

表

create table 表名(	-- 创建表
	字段名 数据类型 列级约束 comment "字段备注信息",	--同时指定列级约束，comment关键字来声明备注信息
  ...,
  表级约束	-- 同时表级约束
)comment "表备注信息";	-- 还可以指定存储引擎和字符集等信息，省略后使用默认存储引擎和字符集
-- 无论是表级约束还是列级约束都可以可使用 constraint 约束名 的形式为约束起名，若省略后系统自动取名
create table 表名 as 查询语句;	-- 表的复制，将子查询结果当作表创建出来

alter table 表名 alter 字段名 set default 新默认值;	-- 修改字段默认值
alter table 表名 alter 字段名 drop default;	--删除字段默认值
alter table 表名 modify 字段名 数据类型 列级约束;	-- 修改字段定义，还可以使用 first|after 字段名 形式修改字段位置
alter table 表名 change 字段名 新字段名 数据类型 列级约束;	-- 修改字段定义同时修改字段名，还可以使用 first|after 字段名 形式修改字段位置
alter table 表名 add 字段名 数据类型 列级约束; -- 添加字段，还可以使用 first|after 字段名 形式指定添加位置
alter table 表名 add 表级约束;	--添加表级约束
alter table 表名 drop 字段名;	-- 字段删除
alter table 表名 drop 表级约束;	-- 删除表级约束
alter table 表名 column rename 字段名 to 新字段名;	-- 字段重命名

truncate table 表名;	--截断表，清空数据无法回滚，效率很高
rename table 表名 to 新表名;	--表重命名
drop table 表名;	-- 表删除

索引

MySQL的Btree索引使用的是B+树数据结构存储，B+树是一种平衡树，所有节点都是按键大小顺序放在同一层叶子节点，对于MySQL的InnoDB存储引擎中叶子节点指向的主键位置，而MyISAM存储引擎中叶子节点指向的是物理存储位置

• 支持全值匹配的查询：包括等值查询和in查询，只有在in中的值过多时才可能用不到索引（不等于和not in是无法使用到索引的）
• 支持范围查询：包括between and和大于、小于、大于等于、小于等于的查询
• 支持前缀索引子查询：组合索引为A,B,C
  • 支持A,B,C，A,B，A的索引查询
  • 对于C,B,A，B,AMySQL优化器会自动优化顺序来适应索引
  • 对于A,C的查询只会用到A索引，不会用到C索引
  • 但不能支持B,C，C，B的索引查询
  • 因为索引是有序的，所以会导致第二个和第三个键在索引中是分散的，就无法做到快速查询

注意：有主键约束（主键索引）和唯一约束（唯一索引）的字段会自动添加索引

explain sql;	-- 查看该sql语句的执行计划
create index 索引名 on 表名(字段列表);	-- 创建索引，可以为多个字段添加索引
create unique index 索引名 on 表名(字段列表);	--创建唯一索引，可以为多个字段添加索引
alter table 表名 add index 索引名(字段列表);	-- 修改表结构方式添加索引
drop index 索引名 on 表名;	-- 删除索引

视图

视图可视为临时表，或代理表

create or replace view 视图名 as (sql);	-- 创建或替换视图，只有DQL语句才能创建视图
alter view 视图名 as (sql);	-- 修改视图
drop view 视图名;	-- 删除视图
desc view 视图名;	-- 查看视图定义

DML

插入数据（insert）

insert into 表名(字段列表) value(字段值列表);	-- 	插入单条数据
insert into 表名(字段列表) values(字段值列表1),(字段值列表2),...;	-- 插入多条数据
insert into 表名(字段列表) (sql);	--将查询结果插入表内
-- 若省略字段列表，就必须按照表中所有字段依次对应

-- 对于出现索引字段冲突时，插入是无法成功的
-- 可以在插入语句后面跟上 on duplicate key update 更新列表; 语句进行监听该事件
-- 若发生该事件后就会执行更新操作

更新数据（update）

update 表名 set 更新列表 where 查询条件;	-- 若where条件省略，则全表更新
update 表名 set 更新列表 where 查询条件 order by 字段名 limit 0,10;	-- 可指定只更新部分符合条件数据

删除数据（delete）

delete from 表名 where 查询条件;	-- 若where条件省略，则删除全部数据，delete删除后可以回滚，但效率很低
delete from 表名 where 查询条件 order by 字段名 limit 0,10; -- 可指定只删除部分符合条件数据
delete 表1 from 表1 join 表2 using(同名字段) where 查询条件;	-- 若from中有多个表时需要在delete关键字后指定删除那个表中符合条件的数据

删除重复数据

删除表中重复中数据，用于建立唯一索引

select main.* 	-- 最后将select换成delete即可
from 表名 as main
join (
	select 重复数据字段列表,min(id) as min_id	-- 查出重复数据中主键ID最小的ID，当然也可以是最大的
  from 表名 
  group by 重复数据字段列表 	-- 根据重复数据字段进行分组
  having count(重复字段列表) > 1	-- 该字段的分组下个数大于1，则说明有有重复数据
) as tmp
on(main.重复数据字段列表 = tmp.重复数据字段列表)	-- 临时表与主表使用重复数据字段进行关联
where id > min_id	-- 过滤除啦最小ID以外的重复数据

DQL

书写顺序如下：不可以调换位置，语法要求

1. select
2. from
3. where
4. group by
5. having
6. order by

执行顺序：

1. from（从那张表）-->
2. where（筛选有用的数据）-->
3. group by（进行分组）-->
4. having（再对分组后数据过滤）-->
5. select（查询出来）-->
6. order by（对查询结果排序）

简单查询（select）

select "hello world";	-- 可跟常量
select current_timestamp;	-- 可跟系统常量（变量）
select version();	-- 可跟函数
select 1+1;	-- 可跟表达式
select 1+1 as "别名";	--可为列取别名，注意英文可不加引号，非英文必须使用引号包裹
select * from 表名;	-- 星号代表全映射
select 表名.字段列表 from 表名;	-- 字段进行映射，使用 表名.字段名 的形式进行映射
select 字段列表 from 表名;	-- 字段映射时，若只有一个表，则可省略表名
select distinct 字段列表 from 表名;	-- 可去除重复的行

连接查询（from）

select * from 数据库.表名;	-- 可指定数据库下的某个表
select * from 表名;	-- 也可是单独表表，数据库对象默认是当前使用数据库
select * from 表名 as "别名";	-- 可为表取一个别名，在字段映射时可以使用别名进行字段映射

注意：不管使用什么连接都不会消除产生笛卡尔积，不能减少其匹配次数

以下是sql1999语法，sql92（在where子句中添加条件）不建议用，因为结构不够清晰，虽然on字句后括号可以省略不写，建议写上，层次更加清晰

交叉连接

select * from 表1 join 表2;	-- 在没有指定连接条件下的连接查询就是交叉连接
select * from 表1 cross join 表2;	-- 显式指定交叉连接
select * from 表1 inner join 表2;	-- 显式指定内连接，为指定连接条件的情况下也是交叉连接
-- select * from 表1 cross inner join 表2	-- 报错，不能同时使用cross和inner两个关键字

内连接

select * from 表1 natural join 表2;	-- 自然连接，自动找到同名字段进行内连接，若没有则会是交叉连接，不会出现同名列
select * from 表1 inner join 表2 using(同名字段);	-- 有多个同名字段时使用该同名字段进行自然连接，inner可省，不会出现同名列
select * from 表1 inner join 表2 on(连接条件);	-- 使用该连接条件进行内连接，inner可省，会出现同名列

外连接

select * from 表1 left outer join 表2 on(连接条件)|using(同名字段);
-- 左表为主表进行外连接，必须有连接条件可使用on子句或using子句（不显示同名列），outer可省
select * from 表1 right outer join 表2 on(连接条件)|using(同名字段);
-- 右表为主表进行外连接，必须有连接条件可使用on子句或using子句（不显示同名列），outer可省
select * from 表1 full outer join 表2 on(连接条件)|using(同名字段);	-- MySQL不支持全外连接，Oracle支持
-- 右右表都为主表进行外连接，必须有连接条件可使用on子句或using子句（不显示同名列），outer可省

多连接

select * 
from 表1 join 表2 on(连接条件) 
join 表3 on(连接条件)
...
-- 可以是外连接也可以是内连接
-- 执行顺序是表1连接表2后的结果表，再与表3进行连接，依次类推

条件查询（where）

• 除了is null和is not null任何运算符和null值运算结果都为null
• where子句中无法使用聚合函数，因为sql语句的执行顺序

比较运算符

符号	描述
=	等于
>	大于
<	小于
>=	大于等于
<=	小于等于
<>或!=	不等于
between minValue and maxValue	在[minValue,maxValue]区间之内
not between minValue and maxValue	不在[minValue,maxValue]区间之内
is null	等于空，不能用=null，否则返回空
is not null	不等于空，不能用<>null或!=null，否则返回空
like	模糊查询，只能比较字符串，百分号表示任意个任意字符，下划线表示一个任意字符
not like	反模糊查询
in()	等于集合中任意元素，使用逗号分隔多个元素
not in()	不等于集合中的所有元素，使用逗号分隔多个元素

逻辑运算符

符号	描述
and或&&	与
or或`		`	或
not	非
xor	异或（相同为假0）

分组统计查询（group by和having）

select * from 表名 group by 分组字段列表;
-- 对多个字段联合分组时，就将多个字段看成一个字段
-- 对null分组或未分组时，就是将所有数据视为一组
-- 当sql语句中有group by的话，select后只能跟聚合函数和参与分组的字段
select * from 表名 group by 分组字段列表 having 过滤条件;	-- 只有group by后才能用having子句
-- having后可以使用聚合函数进行过滤

聚合函数

• where子句中无法使用聚合函数
• 聚合函数自动忽略空
• 聚合函数对未分组的数据进行处理时，就是将所有数据视为一组

函数名	描述
count(字段名) count(*) count(distinct 字段名)	统计该分组中该字段个数 可以是通配符星号表示多列 可以使用distinct关键字统计出非重复字段值的个数
sum(字段名)	统计该分组中该字段合计，只能是单列的数值列
avg(字段名)	统计该分组中该字段平均，只能是单列的数值列
max(字段名)	统计该分组中该字段最大值，只能是单列
min(字段名)	统计该分组中该字段最小值，只能是单列

非聚合函数

时间函数

函数名	描述
curdate()	返回当前日期
curtime()	返回当前时间
now()	返回当前日期和时间
date_format(date,fmt)	使用fmt格式对date时间进行格式化，可以是时间字符串或时间类型 比如：date_format("1970/1/1 0:0:0","%Y-%m-%d %H:%i:%s")、date_format(now(),"%Y-%m-%d %H:%i:%s")都返回1970-01-01 00:00:00
sec_to_time(seconds)	将秒数转化为time（时:分:秒）的形式字符串
time_to_sec(time)	将time（时:分:秒）的形式字符串转化为秒数
datediff(date1,date2)	返回两个日期之间相差的天数
date_add(date,interval expr unit)	为date增加指定单元时间，比如： date_add(now(),interval 1 day)返回当前时间加一天的时间 date_add(now(),interval -1 day)返回当前时间减一天的时间 date_add(now(),interval 1 year)返回当前时间减一年的时间 date_add(now(),interval "1:30" hour_minute)返回当前时间加一小时三十分钟
extract(unit from date)	提取出date的时间单元，比如： extract(year from now())返回当前时间的年份 extract(month from now())返回当前时间的月份 extract(day from now())返回当前时间的日
unix_timestamp(date)	返回当前时间戳，或将date转化为时间戳
from_unixtime(timestamp)	将时间戳转化为date时间类型

字符串函数

函数名	描述
concat(str...)	拼接字符串
concat_ws(sep,str...)	用指定分隔符拼接字符串
char_length(str)	返回字符串的字符个数
length(str)	返回字符串的字节个数
format(x,d)	小数格式化，将x格式化为指定小数位（四舍五入）的字符串，非小数部分每三个使用逗号分隔
left(str,len)	从str的左侧截取len个字符
right(str,len)	从str的右侧截取len个字符
substring(str,pos,len)	从str的pos位置截取len个字符，pos为负代表倒数，len省略代表截取到最后
substring_index(str,delim,count)	截取按delim分隔的前count个子串，count为负代表后几个子串
locate(substr,str)	返回在str 子串substr第一次出现的位置
trim(string)	返回去除首尾空格的字符串，也可指定去除首尾指定字符的字符串，比如： trim('x' from "xxhelloxx")返回hello

其他函数

函数名	描述
user()	返回当前用户
fnull(字段名,默认值)	若该字段为null，则返回默认值
round(x,d)	返回对x进行d位小数的四舍五入
rand()	返回一个0到1之间的随机数 可用于随机排序，使用order by rand()语句排序即可，但是性能较差
md5(str)	返回字符串的md5值
case when 条件 then 返回结果 when 条件 then 返回结果 else 返回结果 end	开关语句when可有多个，可以看作一个匿名函数

排序查询（order by）

默认情况下各个行数据的排序规则为：InnoDB默认按照主键顺序进行排序，MyISAM默认按照写入磁盘顺序进行排序

select * from 表名 order by 字段名 排序规则,...;
-- 排序规则不指定时默认升序，asc升序，desc降序
-- 多字段排序，越靠前的字段越起到主导作用，不强制要求排序字段要从出现在select子句中
-- 列名也可以是数字，代表根据第几列字段排序

分页查询（limit）

select * from 表名 limit 0,10;	-- 只有MySQL支持limit，Oracle需要使用序列实现分页
-- 第一个参数是跳过多少条数据，第二个参数是返回多少条数据，通过改变第一个参数达到翻页效果

子查询

where子句中的子查询

子查询返回单行单列，即一个单元格

select * from 表名 where 字段名=(单行单列子查询);
-- 将子查询结果当作一个值即可

子查询返回单行多列，即一行单元格

select * from 表名 where (字段1,字段2...)=(单行多列子查询);
-- 将子查询当作多个字段对应的值即可
select * from 表名 where
字段1=(单行单例子查询)
and
字段2=(单行单列子查询)
-- 上面写法是此写法的简写，

子查询返回多行单列，即一列单元格

select * from 表名 where 字段 in(多行单列子查询);
-- 若返回结果是一个单列集合，则必须用集合筛选操作，in、not in、any、all、some
-- any:存在量词，存在一个，<any(多行多列子查询)表示小于其中一个元素就跳出
-- all:全称量词，任意一个，<all(多行多列子查询)表示小于其中所有元素，遍历所有元素
-- some:与any功能相同
-- some、any和all只能用于子查询
-- 尤其要注意not in返回null的情况

子查询未返回结果

select * from 表名 where exists(子查询);
-- 若子查询能查出内容则返回真，否则返回假

having子句中的子查询

使用规则与where子句一样，由于where效率高于having所以，在有统计函数且是子查询结果是单行单列时才用

from子句中的子查询

将子查询看作临时表即可，且必须指定别名，注意：在连接查询中也是产生临时表

集合操作

并集（union）

MySQL仅实现了并集，Oracle实现了交集（intersect）、差集（intersect）操作

子查询1 union [all] 子查询2
-- 将两个查询结果并集
-- all省略情况下不会出现重复数据，若不省略会出现重复数据
-- 其中的查询语句字段数量必须一致

TCL

事务四大特性ACID

只有DML才有事务机制

• A：原子性：事务是最小的工作单元，不可再分
• C：一致性：事务中多条DML语句同时成功或失败
• I：隔离性：不同事务之间隔离
  1. 读未提交（read uncommitted）：另一个事务未提交，当前事务可以读另一个未提交数据，存在**脏读（dirty read）**现象
  2. 读已提交（read committed）：另一个事务提交后，当前事务就能读到，存在**不可重复读（not repeatable read）**现象
  3. 可重复读（repeatable read）：不管另一个事务如何修改数据，当前事务总是读到开启事务时的数据未修改状态的结果，存在**幻影读（phantom read）**现象，即读到的数据是缓存，是幻象的
  4. 序列化读（serializable）：事务排队执行，效率低
  5. 更新提交：两个事务对同一数据更新操作，都提交后，存在丢失更新现象
• D：持久性：数据持久化到硬盘文件，事务结束

MySQL中的事务

设置事务隔离级别

set global transaction isolation level 隔离级别;	-- 设置全局事务隔离级别
set session transaction isolation level 隔离级别;	-- 设置当前会话的事务隔离级别
-- read uncommitted 读未提交
-- read committed 读已提交
-- repeatable read 可重复读
-- serializable 序列化读

show variables like "%isolation";	-- 查看当前会话事务隔离级别
select @@global.tx_isolation;	--  查看全局事务隔离级别
-- MySQL默认是repeatable read可重复的隔离级别

事务的开启

start transaction;	-- 开启事务
begin;	-- 开启事务

savepoint 保存点名;	-- 设置保存点，可回滚当前保存点

rollback 保存点名;	-- 回滚到保存点
rollback;	-- 回滚整个事务
commit;	-- 提交整个事务

事务阻塞

除啦事务隔离级别为序列化读的情况下可能发生事务阻塞现象，在可重复的级别也可能发生事务阻塞现象，就是在两个事务同时修改同一数据时会发生阻塞，原因是InnoDB在实现该隔离级别时使用到的锁机制

• 共享锁：被加锁的对象只能被持有锁的事务读取但不能修改，其他事务无法对该对象进行修改，但允许其他事务加共享锁进行读取
• 排他锁：被加锁的对象能被持有锁的事务读取或修改，其他事务无法对该对象进行修改，不允许其他事务加任何锁

所以在查询数据时对数据加共享锁，多个查询并不会阻塞事务，但是对数据修改时加排他锁，多个更新可能会阻塞不同事务的执行

监控阻塞

MySQL5.6以及以下是没有sys库，从MySQL5.7开始才默认有sys库，需要手动导入，可从官方获取

wget https://github.com/mysql/mysql-sys/archive/master.zip	#下载sys库的安装文件
unzip master.zip	#解压
cd mysql-sys-master/	#进入解压目录
mysql -u root -p < ./sys_56.sql	#导入数据
cd ../	#导入完成后，准备删除安装文件
rm -rf mysql-sys-master master.zip	#删除安装文件

使用sys.innodb_lock_waits视图监控阻塞SQL

select waiting_pid as "被阻塞线程连接ID",	-- 使用connection_id()函数可获取当前连接ID
waiting_query as "被阻塞SQL",
blocking_pid as "阻塞线程连接ID",
blocking_query as "阻塞SQL",
wait_age as "阻塞时间",
sql_kill_blocking_query as "建议操作"
from sys.innodb_lock_waits
where (unix_timestamp()-unix_timestamp(wait_started))>30;	-- 过滤阻塞时长，单位秒

解决阻塞方法

• 手动终止占用资源事务
• 优化占用资源SQL，使其尽快释放资源

死锁

并行执行事务相互之间占有对方所需的资源，MySQL内部实现了对死锁的监控

监控死锁

set global innodb_print_all_deadlocks=no;	-- 开启将死锁日志记录到错误日志中
show variables like "log_error";	-- 查看错误日志存放位置

解决死锁方法

• 不手动处理，数据库会自行回滚占用资源较少的事务，让另一个事务继续执行，这就导致程序中事务有时能执行成功，有时执行失败
• 最好的解决方式就是将并发事务按照相同顺序占用资源，从而将死锁降为阻塞，若事务执行足够快，也只会阻塞一小会

SQL开发注意事项

• 对于条件查询的调试，使用limit 0,1的效率比count(*)要高
• 对于判断更新是否成功，使用ROW_COUNT()函数判断修改行数，比再查一遍效率高
• 不要在on子句中使用条件查询，因为对于外连接可能会出现未过滤掉的数据，而且语义也不清晰
• 不要向非空字段强制插入null值，虽然会转化称对应数据类型的0，但是这不符合数据库约束
• 注意子查询中字段的作用域，比如下面这种情况：t1表有f1,f2字段，t2表有没有f1字段

select f1,(select f1 from t2 limit 0,1) from t1;
-- 虽然t2表中没有f1字段，但是并不会报错，因为f1会引用父查询的f1字段
select f1,(select t2.f1 from t2 limit 0,1) from t1;
-- 若加上表名指定字段则才会显示报错
-- 虽然加上表名可以解决这种隐式错误，但是最好的方法是不使用子查询，而是使用连接查询代替子查询