目录
通用语法及分类
通用语法
1. sql语句可以单行或多行书写,以分号结尾。
2. sql语句可以使用空格/缩进来增强语句的可读性。
3. MYSQL数据库的sql语句不区分大小写,关键字建议使用大写
4. 注释:
- 单行注释:-- 注释内容 或 # 注释内容
- 多行注释:/* 注释内容 */
分类
分类 | 全称 | 说明 |
DDL | Data Definition Language | 数据定义语言,用来定义数据库对象(数据库、表、字段) |
DML | Data Manipulation Language | 数据操作语言,用来对数据表中的数据经行增删改 |
DQL | Data Query Language | 数据查询语言,用来查询数据库中表的记录 |
DCL | Data Control Language | 数据控制语言,用来创建数据库用户、控制数据库的权限 |
DDL-数据库操作
- 查询
查询所有数据库 - show databases;
查询当前数据库 - select database(); - 创建
create database [ if not exists] 数据库名 [default charset 字符集] [ collate 排序规则]; - 删除
DROP DATABASE [IF EXISTS] 数据库名; - 使用
USE 数据库名;
DDL-表操作-查询
- 查询当前数据库所有表
show tables; - 查询表结构
desc 表名; - 查询指定表的建表语句
show create table 表名;
DDL-表操作-创建
CREATE TABLE 表名(
字段1 字段1类型 [comment 字段1注释],
字段2 字段2类型 [comment 字段2注释],
字段3 字段3类型 [comment 字段3注释],
......
字段3 字段3类型 [comment 字段3注释]
)[comment 标注释];
DDL-表操作-数据类型
mysql中的数据类型有很多,主要分为三类:数值类型、字符串类型、日期时间类型
DDL-表操作-修改
- 添加字段
alter table 表名 字段名 类型(长度) [comment 注释] [约束]; - 修改数据类型
alter table 表名 modify 字段名 新数据类型(长度): - 修改字段名和字段类型
alter table 表名 change 旧字段名 新字段名 类型(长度) [comment 注释] [约束]; - 删除字段
alter table 表名 drop 字段名; - 修改表名
alter table 表名 rename to 新表名; - 删除表
drop table [ if exists ] 表名; - 删除指定表,并重新创建该表(清空表数据)
truncate table 表名;
DML -数据操作-添加数据
- 给指定字段添加数据
insert into 表名 (key1,key2,key3,......) values (value1,value2,value3,......); - 给全部字段添加数据
insert into 表名 values (value1,value2,value3,......) - 批量添加数据
insert into 表名 (key1,key2,...) values (value1,value2,...),(value1,value2,...);
insert into 表名 values (value1,value2,...),(value1,value2,...);
DML-数据操作-修改数据
update 表名 set key1=value1,key2=value2,... [ where 条件];
DML-数据操作-删除数据
delete from 表名 [ where 条件];
DQL-数据查询-查询数据
SELECT
字段列表 4
FROM
表名列表 1
WHERE
条件列表 2
GROUP BY
分组字段列表 3
HAVING
分组后条件列表
ORDER BY
排序字段列表 5
LIMIT
分页参数 6
- 查询多个字段
select key1,key2,key3,... from 表名;
select * from 别名; - 设置别名
select key1 [as 别名1],key2 [as 别名2] ... from 表名; - 去除重复记录
select distinct 字段列表 from 表名;
条件查询(where)
select 字段列表 from 表名 where 条件列表
比较运算符 | 功能 |
> | 大于 |
>= | 大于等于 |
< | 小于 |
<= | 小于等于 |
= | 等于 |
<> 或 != | 不等于 |
between ... and ... | 在某个范围之内(包含最小、大值) |
in (...) | 在某个值中 |
like 占位符 | 模糊匹配(_匹配单个字符,%匹配任意个字符) |
is null | 是 null |
逻辑运算符 | 功能 |
and 或 && | 并且 多个条件同时成立 |
or 或 || | 或者 多条件成立一个 |
not 或 ! | 非 不是 |
聚合函数
(count - 总量,max - 最大值,min - 最小值,avg - 平均数,sum - 求和)
select 聚合函数(字段) from 表名;
分组查询(group by)
select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件] ...;
where与having的区别
- 执行时机不同:where是分组之前经行过滤,不满足where条件,不参与分组;而having是分组之后经行过滤
- 判断条件不同:where不能对聚合函数经行判断,having可以
排序查询 (order by)
select 字段列表 from 表名 order by 字段1 排序方式1,字段2 排序方式2;
asc 升序(默认) desc 降序
分页参数 (limit)
select 字段列表 from 表名 limit 起始索引,查询数量;
DCL-管理用户
- 查询用户
use mysql;
select * from user; - 创建用户
create user '用户名'@'主机名' identified by '密码'; - 修改用户密码
alter user '用户名'@'主机名' identified with mysql_native_password by '新密码'; - 删除用户
drop user '用户名'@'主机名'
DCL-权限控制
权限 | 说明 |
ALL,ALL PRIVILEGES | 所有权限 |
select | 查询数据 |
insert | 插入数据 |
update | 修改数据 |
delete | 删除数据 |
alter | 修改表 |
drop | 删除数据库/表/视图 |
create | 创建数据库 |
- 查询权限
show grants for '用户名'@'主机名'; - 授予权限
grant 权限列表 on 数据库名.表名 TO '用户名'@'主机名'; - 撤销权限
revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';
注意:
- 多个权限之间,使用 ‘ , ’ 分隔
- 授权时,数据库名和表名可以使用 * 进行通配,代表说有。
函数
字符串函数 | 功能 |
CONCAT(S1,S2,...,Sn) | 字符串拼接,将S1 ... Sn 拼接成一个字符串 |
LOWER(str) | 将字符串str全部转为小写 |
UPPER(str) | 将字符串str全部转为大写 |
LPAD(str,n,pad) | 左填充,用字符串pad对str的左边进行填充,达到n个字符串长度 |
RPAD(str,n,pad) | 右填充,用字符串pad对str的右边进行填充,达到n个字符串长度 |
TRIM(str) | 去掉字符串头部和尾部的空格 |
SUBSTRING(str,start,len) | 返回字符串str从start位置起的len长度的字符串 |
数值函数 | 功能 |
CEIL(x) | 向上取整 |
FLOOR(x) | 向下取整 |
RAND() | 返回0~1内的随机数 |
ROUND(x,y) | 求参数x的四舍五入的值,保留y位小数 |
MOD(x,y) | 返回(x/y)的模 |
日期函数 | 功能 |
CURDATE() | 返回当前日期 |
CURTIME() | 返回当前时间 |
NOW() | 返回当前日期和时间 |
YEAR(date) | 获取指定date的年份 |
MONTH(date) | 获取指定date的月份 |
DAY(date) | 获取指定date的日期 |
DATE_ADD(date,interval expr type) | 返回一个日期/时间值加上一个时间间隔expr后的时间值 |
DATEDIFF(date1,date2) | 返回其实时间date1 和结束时间date2 之间的天数 |
UNIX_TIMESTAMP(date) | 将date转换成时间戳 |
FROM_UNIXTIME(time) | 将时间戳转换为 Y-m-d H:i:s |
流程函数 | 功能 |
if(condition,t,f) | 如果condition为true,则返回他,否则返回f |
if null(value1,value2) | 如果value1不为空,返回value1,否则返回value2 |
case when [val1] then [res1] ...else [default] end | 如果val1为true,返回res1,...否则返回default默认值 |
case [expr] when [val1] then [res1] ...else [default] end | 如果expr的值等于val1 则返回res1, 否则返回default默认值 |
约束
- 概念:约束是作用与表中字段上的规则,用于限制存储在表中的数据
- 目的:保证数据库中数据的正确,有效性和完整性
- 分类
约束 描述 关键字 非空约束 限制该字段的数据不能为null NOT NULL 唯一约束 保证改字段的所有数据都是唯一,不能重复 UNIQUE 主键约束 主键是一行数据的唯一标识,要求非空且唯一 PRIMARY KEY 默认约束 保存数据时,如果未指定该字段的值,则采用默认值 DEFAULT 检查约束(8.0.16版本之后) 保证字段满足某一个条件 CHECK 外键约束 用来让两张表的数据之间建立链接,保证数据的一致性和完整性 FOREIGN KEY
外键约束语法
create table 表名(
字段名 数据类型,
......
[constarint] [外键名称] foreign key (外键字段名) references 主表 (主表列名)
);
alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 主表(主表列名)
如:alter table wk_user add constraint user_city_id foreign key (city_id) references wk_city(id);
删除外键
alter table 表名 drop foreign key 外键名称;
外键约束-删除/跟新行为
行为 | 说明 |
NO ACTION | 当前父表中删除/跟新对应记录时,首先检查该记录是否有对应外键,如果有则不允许删除/跟新(与RESTRICT 一致) |
RESTRICT | 当前父表中删除/跟新对应记录时,首先检查该记录是否有对应外键,如果有则不允许删除/跟新(与 NO ACTION 一致) |
CASCADE | 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有,则也删除/更新外键在子表中的记录 |
SET NULL | 当在附表中删除对应记录时,首先检查该记录是否有对应外键,如果有则设置子表中该外键值为null (要求表设计可以为null) |
SET DEFAULT | 父表有变更时,子表将外键列设置成一个默认的值(innodb不支持) |
多表查询
多表关系
- 一对多 再多的一方建立外键,指向一的一方的主键
- 多对多 建立第三张中间表,中间表至少包含两个外键,分别关联两方主键
- 一对一 在任意乙方加入外键,关联另外一方的主键,并设置外键为唯一的(UNIQUE)
多表查询概述
- 概述:指从多张表中查询数据
- 笛卡尔积:笛卡尔乘积是指在数学中,两个集合A和B的所有组合情况。(多表查询时,需要消除无效的笛卡尔积)
内连接
相当于查询A、B交集部分数据
隐式:select 字段列表 from 表1,表2 where 条件;
显式:select 字段列表 from 表1 [inner] join 表2 on 连接条件;
外连接
- 左外连接:查询左表所有数据,以及两张表交集部分数据
select 字段列表 from 表1 left join 表2 on 条件; - 右外连接:查询右表所有数据,以及两张表交集部分数据
select 字段列表 from 表1 right join 表2 on 条件;
自连接
当前表与自身的连接查询,自连接必须使用表别名
select 字段列表 from 表A 别名A join 表B 别名B on 条件;
联合查询-union , union all
对于union查询,就是把多次查询的结果合并起来,形成一个新的查询结果集(字段列表必须一致)
select 字段列表 from 表A ....
union [all]
select 字段列表 from 表B ....
注意:对于联合查询的多张表的列数必须一致,字段类型也必须一致
union all 会将全部的数据直接合并在一起,union 会对合并之后的数据去重
子查询(嵌套查询)
select * from t1 where column1=(select column1 from t2);
子查询外部的语句可以是insert/update/delete/select 的任何一个
标量子查询(子查询结果为一个单值)
常用的操作符:= 、<>、 >、 >=、 <、 <=
列子查询(子查询结果为一列)
常用的操作符:in 、 not in 、any 、some 、all
操作符 | 描述 |
IN | 在指定的集合范围之内,多选一 |
NOT IN | 不在指定的集合范围之内 |
ANY | 子查询返回列表中,有任意一个满足即可 |
SOME | 与ANY等同,使用SOME的地方都可以使用ANY |
ALL | 子查询返回列表的所有制都必须满足 |
select * from test where age > ALL (select age from test where date>0)
行子查询(子查询结果为一行)
常用操作符:=、<>、IN、NOT IN
select * from test where (age,date) = (select age,date from test where id =10)
表子查询(子查询结果为多行多列)
常用操作符:IN
select * from test where (age,date) in (select age,date from test where id >20)
事务
事务是一组操作的集合,它是一个不可分割的工作单位,事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求,即这些操作要么同时成功,要么同时失败。
- 开启事务 start transaction 或者 begin;
- 提交事务 commit;
- 回滚事务 rollback;
事务特性
- 原子性:事务是不可分割的最小操作单元,要么全部成功,要么全部失败
- 一致性:事务完成时,必须使所有的数据都保持一致状态
- 隔离性:数据库系统提供的隔离机制,保证事务在不受外部并发操作影响的独立环境下运行
- 持久性:一旦提交或回滚,它对数据库中的数据的改变就是永久的
并发事务问题
问题 | 描述 |
脏读 | 一个事务读取到另外一个事务还没有提交的数据 |
不可重复读 | 一个事务先后读取同一条记录,但两次读取的数据不同 |
幻读 | 一个事务按照条件查询数据时,没有对应的数据行,但是在插入数据时,有发现这行数据已经存在 |
事务的隔离级别
隔离级别 | 脏读 | 不可重复读 | 幻读 | 性能排名 |
read uncommitted | v | v | v | 1 |
read committed | x | v | v | 2 |
repeatable read(默认) | x | x | v | 3 |
serializeble | x | x | x | 4 |
查看事务隔离级别:select @@TRANSACTION_ISOLATION;
设置事务隔离级别:SET [ SESSION(当前会话)|GLOBAL(所有会话)] TRANSACTION ISOLATION LEVEL {read uncommitted | read committed | repeatable read | serializeble}