SQL目录
文章目录
一、SQL分类
分类 | 说明 |
---|---|
DDL | 数据定义语言,用来定义数据库对象(数据库,表,字段) |
DML | 数据操作语言,用来对数据库表中的数据进行增删改 |
DQL | 数据查询语言,用来查询数据库中表的记录 |
DCL | 数据控制语言,用来创建数据库用户、控制数据库的访问权限 |
1、DDL
-
DDL的数据库操作
-
查询
-
查询所有数据库
show datadases;
-
查询当前数据库
select datadase();
-
-
创建
create database [if not exists] 数据库名 [default charset 字符集] [coliate 排序规则]
-
删除
drop datadase [if exists] 数据库名
-
使用
use 数据库名
-
-
DDL的表操作
-
查询
-
查询当前数据库所有表
show tables;
-
查询表结构
desc 表名
-
查询指定表的建表语句
show create table 表名
-
-
创建
create table 表名( 字段1 字段1类型[comment 注释], 字段2 字段3类型[comment 注释], 字段3 字段4类型[comment 注释], ... 字段n 字段n类型[comment 注释] )[comment 注释];
-
修改
-
添加字段
alter table 表名 add 字段名 类型(长度)[comment 注释][约束];
-
修改字段的数据类型
alter table 表名 modify 字段名 新数据类型(长度);
-
修改字段的数据类型
alter table 表名 change 旧字段名 新字段名 类型(长度)[comment 注释][约束];
-
删除字段
alter table 表名 drop 字段名;
-
修改表名
alter table 表名 rename to 新表名;
-
-
删除
-
删除表
drop table [if exists] 表名;
-
删除指定表,并重新创建该表
truncate table 表名;
-
-
2、数据类型
- 数值型
类型 | 大小 | 有符号(SIGNED)范围 | 无符号(UNSIGNED)范围 |
---|---|---|---|
TINYINT | 1 byte | (-128. 127) | (0,255) |
SMALLINT | 2 bytes | (-32768, 32767) | (0.65535) |
MEDIUMINT | 3 bytes | ||
INT或INTEGER | 4 bytes | ||
BIGINT | 8 bytes | ||
FLOAT | 4 bytes | ||
DOUBLE | 8 bytes | ||
DECIMAL | 依赖于M(精度)和D(标度)的值 | 依赖于M(精度)和D(标度)的值 |
- 字符型
类型 | 大小 | 描述 |
---|---|---|
CHAR | 0-255 bytes | 定长字符串 |
VARCHAR | 0-65535 bytes | 变长字符串 |
TINYBLOB | 0-255 bytes | 不超过255个字符的二进制数据 |
TINYTEXT | 0-255 bytes | 短文本字符串 |
BLOB | 0-65 535 bytes | 二进制形式的长文本数据 |
TEXT | 0-65 535 bytes | 长文本数据 |
MEDIUMBLOB | 0-16 777 215 bytes | 二进制形式的中等长度文本数据中等长度文本数据 |
MEDIUMTEXT | 0-16 777 215 bytes | 中等长度文本数据 |
LONGBLOB | 0-4 294 967 295 bytes | 二进制形式的极大文本数据 |
LONGTEXT | 0-4 294 967 295 bytes | 极大文本数据 |
- 日期型
类型 | 大小 | 范围 | 格式 |
---|---|---|---|
DATE | 3 | 1000-01-01 至 9999-12-31 | YYYY-MM-DD |
TIME | 3 | -838:59:59至 838:59:59 | HH:MM:SS |
YEAR | 1 | 1901至2155 | YYYY |
DATETIME | 8 | 1000-01-01 00:00:00至 9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS |
TIMESTAMP | 4 | 1970-01-01 00:00:01 至 2038-01-19 03:14:07 | YYYY-MM-DD HH:MM:SS |
3、DML
-
添加数据
-
给指定字段添加数据
insert into 表名(字段名1, 字段名2) values(值1, 值2);
-
给所有字段添加数据
insert into 表名 values (值1, 值2);
-
批量添加数据
insert into 表名(字段名1, 字段名2) values(值1, 值2), (值1, 值2);
insert into 表名 values (值1, 值2), (值1, 值2);
-
-
修改数据
update 表名 set 字段名1 = 值1, 字段名2 = 值2 [where 条件];
注意:修改语句的条件可以有,如果没有,则修改整张表的所有数据。
-
删除数据
delete from 表名 [where 条件];
注意:
- delete语句的条件可以有,如果没有,则修改整张表的所有数据。
- delete语句不能删除某一个字段的值(可以用update)
4、DQL
-
语法 — 编写循序
select 字段列表 from 表名列表 where 条件列表 group by 分组字段列表 having 分组后条件列表 order by 排序字段列表 limit 分页参数
-
执行顺序
from 表名列表 where 条件列表 group by 分组字段列表 having 分组后条件列表 select 字段列表 order by 排序字段列表 limit 分页参数
1)基本查询
-
查询多个字段
select 字段1, 字段2, 字段n from 表名;
select * from 表名;
-
设置别名 - as 可以不写
select 字段1 [as 别名], 字段2 别名, 字段n from 表名;
-
去除重复记录
select distinct 字段列表 from 表名;
2)条件查询
-
语法
select 字段列表 from 表名 where 条件列表;
-
条件
比较运算符 功能 <> 或 != 不等于 between … and … 某个范围之内(含最小,最大值) is null 空,null in() 在in之后的列表中的值,多选一 like 占位符 模糊匹配( _ 匹配单个字符,% 匹配任意字符)
’ ‘ 单引号。‘___’ ‘%’逻辑运算符 功能 and 或 && 并且 or 或 || 或者 not 或 ! 非
3)聚合函数查询
-
将一列数据作为一个整体,进行纵向计算。
函数 功能 count 统计数量 max 最大值 min 最小值 avg 平均值 sum 求和 -
语法
select 聚合函数(字段列表) from 表名;
NULL 不进行计算。
4)分组查询
-
语法
select 字段列表 from [where 条件] group by 分组字段名 [having 分组后过滤条件];
-
where与having区别
执行时机不同: where是分组之前进行过滤不满足where条件,不参与分组;而having是分组之后对结果进行过滤。判断条件不同::where不能对聚合函数进行判断,而having可以。
-
注意
执行顺序::where > 聚合函数 > having 。
分组之后,查询的字段一般为聚合函数和分组字段,查询其他字段无任何意义。
5)排序查询
-
语法
select 字段列表 from 表名 order by 字段1 排序方式1, 字段2 排序方式2;
-
排序方式
- asc — 升序(默认)
- desc — 降序
-
注意:
如果是多字段排序,当第一个字段值相同时,才会根据第二个字段进行排序。
6)分页查询
-
语法
select 字段列表 from 表名 limit 起始索引, 查询记录;
-
注意
- 起始索引从0开始,起始索引 = (查询页码 - 1) * 每页显示记录数。
- 分页查询是数据库的方言,不同的数据库有不同的实现,MySQL中是LIMIT。
- 如果查询的是第一页数据,起始索引可以省略,直接简写为limit 查询记录。
5、DCL
-
管理用户
-
查询用户
use mysql; select * from user;
-
创建用户
create user '用户名'@'主机名' identified by '密码'; create user '用户名'@'%' identified by '123456';# 任意主机用%表示
-
修改用户密码
alter user '用户名'@'主机名' identified with mysql_native_password by '新密码';
-
删除用户
drop user '用户名'@'主机名';
注意:
- 主机名可以使用 % 通配。
-
-
权限控制
权限 说明 all, all privileges 所有权限 select 查询数据 insert 插入数据 update 修改数据 delete 删除数据 alter 删除表 drop 删除数据/表/视图 create 创建数据库/表 -
查询权限
show grants for '用户名'@'主机名';
-
授予权限
grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
-
撤销权限
revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';
注意:
- 多个权限之间,用逗号分隔开。
- 授权时,数据库名和表名可以使用 * 进行通配,表示所有。
-
二、函数
1、字符函数
函数 | 功能 |
---|---|
concat | 字符串拼接 |
lower | 将字符串转小写 |
upper | 将字符串转大写 |
lpad(str, n, pad) | 左填充,用字符串pad对str的左边进行填充,达到n个字符串长度 |
rpad(str, n, pad) | 右填充,用字符串pad对str的右边进行填充,达到n个字符串长度 |
trim(str) | 去掉字符串头部和尾部的空格 |
substring(str, start, len) | 返回从字符串str从start位置起的len个长度的字符串,索引值从1开始 |
函数用法:select 函数(参数);
2、数值函数
函数 | 功能 |
---|---|
ceil(x) | 向上取整 |
floor(x) | 向下取整 |
mod(x, y) | 返回x/y的模 |
rand() | 返回0到1的随机数,无数个随机数 |
round(x, y) | 求参数x的四舍五入的值,保留y位小数 |
3、日期函数
函数 | 功能 |
---|---|
curdate() | 返回当前日期 |
curtime() | 返回当前时间 |
now() | 返回当前日期和时间 |
year(date) | 获取指定date的年份 |
month(date) | 获取指定date的月份 |
day(date) | 获取指定date的日期 |
date_add(date, interval expr type) | 返回一个日期/时间值加上一个时间间隔expr后的时间值 |
datediff(date1, date2) | 返回起始时间date1和结束时间date2之间的天数 |
4、流程函数
函数 | 功能 |
---|---|
if(value, t, f) | 如果value为true,则返回t,否则返回f |
ifnull(value 1, value 2) | 如果value不为空,则返回t,否则返回f |
case when [val1] then [res1] … else [default] end | 如果val为true, 返回res1, 否则返回default默认值 |
case [expr] when [val1] then [res1] … else [default] end | 如果expr的值等于val1, 返回res1, 否则返回default默认值 |
三、约束
约束 | 描述 | 关键字 |
---|---|---|
非空约束 | 限制该字段的数据不能为null | not null |
唯一约束 | 保证该字段的所有数据都是唯一、不重复的 | unique |
主键约束 | 主键是一行数据的唯一标识,要求非空且唯一 | primary key |
默认约束 | 保存数据时,如果未指定字段的值,则采用默认值 | default |
检查约束 | 保证字段值满足某一条件 | check |
外键约束 | 用来让两张表的数据之间建立联系,保证数据的一致性和完整性 | foreign key |
自动增长 | auto_increment |
1、外键约束
-
语法
- 建表时添加外键
[constraint] [外键名称] foreign key(外键字段名) references 主表(主表列表);
- 建表完成之后的添加外键
alter table 表名 add constraint 外键名称 foreign key(外键字段名) references 主表(主表列表);
- 删除外键
alter table 表名 drop foreign key 外键名称;
-
删除/更新行为
行为 说明 no action 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有则不允许删除/更新 restrict 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有则不允许删除/更新 cascade 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有则也删除/更新外键在子表的记录 set null 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有则设置子表中该外键值为null set default 父表有变更时,子表将外键设置成一个默认的值 alter table 表名 add constraint 外键名 foreign key(外键字段名) references 主表(主表列表) on update cascade on delete cascade;
alter table 表名 add constraint 外键名 foreign key(外键字段名) references 主表(主表列表) on update set null on delete set null;
2、其它约束语法
-
建表时添加约束 – 列级
create table 表名( 字段1 字段1类型 constraint 约束, 字段2 字段2类型 constraint 约束 )
-
建表时添加约束 – 表级
create table 表名( 字段1 字段1类型, 字段2 字段2类型, constraint 约束名称 约束() )
-
删除主键约束
alert table 表名 drop primary key;
-
删除唯一约束
alert table 表名 drop index 唯一约束名称;
-
删除检查约束
alert table 表名 drop constraint 检查约束名;
四、多表查询
1、连接查询
1)内连接
-
隐式内连接
select 字段列表 from 表1, 表2 where 条件;
-
显示内连接
select 字段列表 from 表1 [inner] join 表2 on 连接条件;
2)外连接
-
左外连接
select 字段列表 from 表1 left [outer] join 表2 on 连接条件;
相当于查询表1(左表)的所有数据 包含表1和表2交集部分的数据
-
右外连接
select 字段列表 from 表1 right [outer] join 表2 on 连接条件;
相当于查询表2(右表)的所有数据 包含表1和表2交集部分的数据
3)自连接
-
语法
select 字段列表 from 表A 别名A join 表A 别名B on 连接条件;
自连接查询,可以时内连接查询,也可以是外连接查询。
4)联合查询
select 字段列表 from 表A ...
union [all]
select 字段列表 from 表B ...
对于联合查询的多张表的列数必须保持一致,字段类型也需要保持一致。
union all 会将全部的数据直接合并在一起, union 会将合并之后的数据去重。
2、子查询
-
标量子查询
子查询返回的结果是单个值(数字、字符串、日期等),最简单的形式,这种子查询成为标量子查询。
常用的操作符: = > >= < <=
-
列子查询
子查询返回的结果是一列(可以是多行),这种子查询称为列子查询。
常用的操作符: in、 not in、any、some、all
操作符 描述 in 在指定的集合范围之内,多选一 not in 不在指定的集合范围之内 any 子查询返回列表中,有任意一个满足即可 some 与any等同,使用some的地方都可以使用any all 子查询返回列表的所有值都必须满足 -
行子查询
子查询返回的结果是一行(可以是多列),这种子查询称为行子查询。
常用的操作符:= 、>、IN 、NOT IN例子:
select * from emp where (salary, managerid) = (select salary, managerid from emp where name = '张无忌');
-
表子查询
子查询返回的结果是多行多列,这种子查询称为表子查询。
常用的操作符:IN
五、事务
1、事务操作
-
查看/设置事务提交方式
select @@autocommit; set @@autuocommit = 0;
-
打开事务
start transaction 或 begin;
-
事务提交
commit;
-
回滚事务
rollback;
2、事务四大特性
- 原子性(Atomicity): 事务是不可分割的最小操作单元,要么全部成功,要么全部失败。
- 一致性(Consistency): 事务完成时,必须使所有的数据都保持一致状态。
- 隔离性(lsolation): 数据库系统提供的隔离机制,保证事务在不受外部并发操作影响的独立环境下
- 持久性(Durability): 事务一旦提交或回滚,它对数据库中的数据的改变就是永久的。
3、并发事务问题
问题 | 描述 |
---|---|
脏读 | 一个事务读到另外一个事务还没有提交的数据 |
不可重复读 | 一个事务先后读取同一条记录,但两次读取的数据不同,称之为不可重复读 |
幻读 | 一个事务按照条件查询数据时,没有对应的数据行,但是在插入数据时,又发现这行数据已经存在,好像出现了"幻影” |
4、事务隔离级别
隔离级别 | 脏读 | 不可重复读 | 幻读 |
---|---|---|---|
read uncommitted 读未提交 | 1 | 1 | 1 |
read commiitted 读已提交 | 0 | 1 | 1 |
repeatable read(MySQL默认) 可重复读 | 0 | 0 | 1 |
serializable 串行化 | 0 | 0 | 0 |
注意:事务隔离级别越高,数据越安全,但是性能越低
-
查看事务隔离级别
select @@transaction_isolation;
-
设置事务隔离级别
set [session|global] transaction isolation level {read uncommitted|read commiitted|repeatable read|serializable};
六、存储引擎
存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表的,而不是基于库的,所以存储引擎也可被称为表类型
-
在创建表时,默认指定的存储引擎innodb
create table 表名( 字段1 字段1类型[comment 注释], 字段2 字段2类型[comment 注释], ... 字段n 字段n类型[comment 注释] )engine = innodb [comment 注释];
-
查询当前支持的存储引擎
show engines;
-
inoDB
-
介绍
InnoDB是一种兼顾高可靠性和高性能的通用存储引擎,在MySQL 5.5之后,InnoDB是默认的 MySQL存储引擎。
-
特点
DML操作遵循ACID模型,支持事务;
行级锁,提高并发访问性能;
支持 外键FOREIGN KEY约束,保证数据的完整性和正确性; -
文件
xxx.ibd: xxx代表的是表名,innoDB引擎的每张表都会对应这样一个表空间文件,存储该表的表结构(frm
sdi) 、数据和索引。
参数: innodb_file_per_table -
逻辑存储结构
-
-
MylSAM
-
介绍
MylSAM是MySQL早期的默认存储引擎。
-
特点
不支持事务,不支持外键,支持表锁,不支持行锁,访问速度快 -
文件
xxx.sdi :存储表结构信息
xxx.MYD:存储数据
xxx.MYI:存储索引
-
-
Memory
- 介绍
Memory引擎的表数据时存储在内存中的,由于受到硬件问题、或断电问题的影响,只能将这些表作为临时表或缓存使用。 - 特点
内存存放
hash索引(默认) - 文件
xxx.sdi:存储表结构信息
- 介绍
InnoDB:
是Mysql的默认存储引擎,支持事务、外键。如果应用对事务的完整性有比较高的要求,在并发条件下要求数据的一致性,数据操作除了插入和查询之外,还包含很多的更新、删除操作,那么InnoDB存储引擎是比较合适的选择
MyISAM:
如果应用是以读操作和插入操作为主,只有很少的更新和删除操作,并且对事务的完整性、并发性要求不是很高,那么选择这个存储引擎是非常合适的。
MEMORY:
将所有数据保存在内存中,访问速度快,通常用于临时表及缓存。MEMORY的缺陷就是对表的大小有限制,太大的表无法缓存在内存中,而且无法保障数据的安全性。
七、索引
1、索引结构
-
介绍
索引(index)是帮助MysQL高效获取数据的数据结构(有序)。在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据,这样就可以在这些数据结构上实现高级查找算法,这种数据结构就是索引 -
优缺点
优势 劣势 提高数据检索的效率,降低数据库的IO成本 索引列也是要占用空间的 通过索引列对数据进行排序,降低数据排序的成本,降低CPU的消耗 索引大大提高了查询效率,同时却也降低更新表的速度,如对表进行INSERT、UPDATE、DELETE时,效率降低。行插入、更新、删除时,效率降低。 -
索引结构
索引结构 描述 B+Tree索引 常见的索引类型,大部分引擎都支持B+树索引 Hash索引 只有精确匹配索引列的查询才有效,不支持范围查询 -
B+Tree
My5QL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上,增加一个指向相邻叶子节点的链表指针,就形成了带有顺序指针的B+Tree,提高区间访问的性能。 -
Hash
哈希索引就是采用一定的hash算法,将键值换算成新的hash值,映射到对应的槽位上,然靥存储在hash表中。如果两个(或多个)键值,映射到一个相同的槽位上,他们就产生了hash冲突(也称为hash碰撞),可以通过链表来解决。
-
Hash索引特点
-
Hash索引只能用于对等比较(=,in),不支持范围查询(between,>, <,…)
-
无法利用索引完成排序操作
-
查询效率高,通常只需要一次检索就可以了,效率通常要高于B+tree索引
-
-
存储引擎支持
在MySQL中,支持hash索引的是Memory引擎,而innoDB中具有自适应hash功能,hash索引是存储引擎根据B+Tree索引在指定条件下自动构建的。
-
-
为什么InnoDB存储引擎选择使用B+tree索引结构?
- 相对于二叉树,层级更少,搜索效率高;
- 对于B-tree,无论是叶子节点还是非叶子节点,都会保存数据,这样导致一页中存储的键值减少,指针跟着减少,要同样保存大量数据,只能增加树的高度,导致性能降低;
- 相对Hash索引,B+tree支持范围匹配及排序操作。
2、索引分类
分类 | 含义 | 特点 | 关键字 |
---|---|---|---|
主键索引 | 针对于表中主键创建的索引 | 默认自动创建,只能有一个 | PRIMARY |
唯一索引 | 避免同一个表中某数据列中的值重复 | 可以有多个 | UNIQUE |
常规索引 | 快速定位特定数据 | 可以有多个 | |
全文索引 | 全文索引查找的是文本中的关键词,而不是比较索引中的值 | 可以有多个 | FULLTEXT |
-
innoDB存储引擎中的索引
分类 含义 特点 聚集索引(Clustered Index) 将数据存储与索引放到了一块,索引结构的叶子节点保存了行数据 必须有,而且只有一个 二级索引(Secondary Index) 将数据与索引分开存储,索引结构的叶子节点关联的是对应的主键 可以存在多 聚集索引选取规则:
- 如果存在主键,主键索引就是聚集索引。
- 如果不存在主键,将使用第一个唯一(UNIQUE)索引作为聚集索引。
- 如果表没有主键,或没有合适的唯一索引,则InnoDB会自动生成一个rowid作为隐藏的聚集索引。
3、索引语法
-
创建索引
create [unique|fulltext] index 索引名 on 表名(列表1,...);
-
查看索引
show index from 表名;
-
删除索引
drop index 索引名 on 表名;
4、SQL性能分析
-
SQL执行频率
查看当前数据库的insert、update、delete、select的访问次数。
show global status like 'Com_______';
-
慢查询日志
慢查询日志记录了所有执行时间超过指定参数(long_query_time,单位:秒,默认10秒)的所有SQL语句的日志。
- 查看慢日志查询是否开启
show variables like 'slow_query_log0';
-
profile详情
show profile 能够在SQL优化时帮助我们了解时间消耗到哪里去了。通过have_profiling参数,能够看到当前MySQL是否支持。
- profile操作。
select @@have_profiling;
- 默认profiling是关闭的,查看是否打开
select @@profiling;
- 开启profiling
set profiling = 1;
-
查看每一条SQL的耗时基本情况
show profiles;
-
查看指定query_id的SQL语句各个阶段的耗时情况
show profile for query query_id;
-
查询指定query_id的SQL语句CPU的使用情况
show profile cpu for query query_id;
-
explain执行计划
explain或者desc命令获取MySQL如何执行select语句的信息,包括在select语句执行过程中表如何连接和连接顺序。
-
语法 ---- 直接在select语句之前加上关键词explain/desc
explain select 字段列表 from 表名 where 条件;
-
八、视图
-
视图是一种虚拟存在的表,视图中的数据并不在数据库中实际存在。
-
创建
create [or replace] view 视图名称[(列名列表)] as select语句 [with [cascaded|local] check option];
-
查询
查看创建视图语句:show create view 视图名称; 查看视图数据: select * from 视图名称 ....... ;
-
修改
方式一:create or replace view 视图名称[(列名列表)] as select语句 [with [cascaded|local] check option]; 方式二:alter view 视图名称[(列名列表)] as select语句 [with [cascaded|local] check option];
-
删除
drop view 视图名称;
-
视图检查选项
- cascaded(默认)----检查所有级联的视图检查选项,需要满足所有级联的视图检查选项的条件。
- local----对所依赖的视图进行递归检查。
-
视图更新
- 要使视图可更新,视图中的行与基础表之间必须存在一对一的关系。
- 视图不可更新,(使用聚合函数,distinct, group by, having, union, union all)
九、存储过程
就是数据库SQL语言层面的代码封装与重用
注意:在命令行中,执行创建存储过程时,需要关键字delimiter
-
delimiter指定指定SQL语句的结束符
delimiter $$ . . . delimiter; #结束指定SQL语句的结束符
-
创建
create procedure 存储过程名称[(参数列表)] begin SQL语言 end;
-
调用
call 名称[(参数)];
-
查看存储过程的定义
show create procedure 存储过程名称;
-
删除
drop procedure 存储过程名称;