SQL通用语法
- SQL 语句可以单行或多行书写，以分号结尾。
- SQL 语句可以使用空格/缩进 来增强语句的可读性。
- MySQL 数据库的SQL语句不区分大小写，关键字建议使用大写。
- 注释：
- 单行注释：-- 注释内容 或 # 注释内容（MySQL特有）
- 多行注释：/* 注释内容 */
SQL分类
| 分类 | 全称 | 说明 |
|---|---|---|
| DDL | Data Definition Language | 数据定义语言，用来定义数据库对象（数据库，表，字段） |
| DML | Data Manipulation Language | 数据操作语言，用来对数据库表中的数据进行增删改 |
| DQL | Data Query Language | 数据查询语言，用来查询数据库中表的记录 |
| DCL | Data Control Language | 数据控制语言，用来创建数据库用户、控制数据库的访问权限 |
DDL
DDL - 数据库操作
-
查询
查询所有数据库
SHOW DATABASES;查询当前数据库
SELECT DATABASE(); -
创建
CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则];IF NOT EXISTS ：如果这个数据库名称不存在则创建，存在则不执行任何操作
-
删除
DROP DATABASE [IF EXISTS] 数据库名;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注释],
......
字段n 字段n类型 [COMMENT 字段n注释]
) [COMMENT 表注释];
例：
DDL - 表操作 - 修改
-
添加字段
ALTER TABLE 表名 ADD 字段名 类型(长度) [COMMENT 注释] [约束];案例：
为emp表增加一个新的字段 “昵称” 为 nickname，类型为varchar(20)
ALTER TABLE emp ADD nickname varchar(20) comment 昵称; -
修改数据类型
ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度); -
修改字段名和字段类型
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) [COMMENT 注释] [约束];案例：
将emp表的nickname字段修改为username，类型为varchar(30)
ALTER TABLE emp change nickname username varchar(30); -
删除字段
ALTER TABLE 表名 DROP 字段名;案例：
将emp表的字段username 删除
ALTER TABLE emp drop username; -
修改表名
ALTER TABLE 表名 RENAME TO 新表名;案例：
将emp表的表名修改为employee
ALTER TABLE emp RENAME TO employee;
DDL - 表操作 - 删除
-
删除表
DROP TABLE [IF EXISTS] 表名; -
删除指定表，并重新创建该表
TRUNCATE TABLE 表名;注意：在删除表时，表中的全部数据也会被删除。
DML
DML，用来对数据库中表的数据记录进行增删改操作。
- 添加数据（INSERT）
- 修改数据（UPDATE）
- 删除数据（DELETE）
DML - 添加数据
-
给指定字段添加数据
INSERT INTO 表名 (字段名1,字段名2,...) VALUES (值1,值2,...); -
给全部字段添加数据
INSERT INTO 表名 VALUES (值1,值2,...); -
批量添加数据
INSERT INTO 表名 (字段名1,字段名2,...) VALUES (值1,值2,...),(值1,值2,...),(值1,值2,...); INSERT INTO 表名 VALUES (值1,值2,...),(值1,值2,...),(值1,值2,...);注意：
- 插入数据时，指定的字段顺序需要与值的顺序是一一对应的。
- 字符串和日期型数据应该包含在引号中。
- 插入的数据大小，应该在字段的规定范围内。
DML - 修改数据
UPDATE 表名 SET 字段名1 = 值1,字段名2 = 值2,... [WHERE 条件];
注意：修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据。
DML - 删除数据
DELETE FROM 表名 [WHERE 条件];
注意：
- DELETE 语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数据。
- DELETE 语句不能删除某一字段的值（可以使用UPDATE）。
DQL
DQL，数据查询语言，用来查询数据库中表的记录。
DQL - 语法
SELECT
字段列表
FROM
表名列表
WHERE
条件列表
GROUP BY
分组字段列表
HAVING
分组后条件列表
ORDER BY
排序字段列表
LIMIT
分页参数
DQL - 基本查询
-
查询多个字段
SELECT 字段1，字段2，字段3 ... FROM 表名; SELECT * FROM 表名; -
设置别名
SELECT 字段1 [AS 别名1],字段2 [AS 别名2] ... FROM 表名; -
去除重复记录
SELECT DISTINCT 字段列表 FROM 表名;
DQL - 条件查询
-
语法
select 字段列表 from 表名 where 条件列表; -
条件
比较运算符 功能 > 大于 >= 大于等于 < 小于 <= 小于等于 = 等于 <> 或 != 不等于 between … and … 在某个范围之内（含最小、最大值） in(…) 在in之后的列表中的值，多选一 like 占位符 模糊匹配（ _匹配单个字符，%匹配任意个字符）is null 是null and 或 && 并且（多个条件同时成立） or 或 || 或者（多个条件任意一个成立） not 或 ! 非，不是 -
条件查询案例
-
查询年龄等于 28 的员工信息
select * from emp where age = 28; -
查询年龄小于 35 的员工信息
select * from emp where age < 35; -
查询年龄小于等于 20 的员工信息
select * from emp where age <= 20; -
查询没有身份证号的员工信息
select * from emp where idcard is null; -
查询有身份证号的员工信息
select * from emp where idcard is not null; -
查询年龄不等于 38 的员工信息
select * from emp where age != 38; select * from emp where age <> 38; -
查询年龄在20岁(包含) 到 35岁(包含)之间的员工信息
select * from emp where age >= 20 and age <= 35; select * from emp where age >= 20 && age <= 35; select * from emp where age between 20 and 35; -
查询性别为 女 且年龄小于 25岁的员工信息
select * from emp where gender = '女' and age < 25; -
查询年龄等于18 或 20 或 40 的员工信息
select * from emp where age = 18 or age = 20 or age = 40; select * from emp where age in (18,20,40); -
查询姓名为两个字的员工信息
select * from emp where name like '__'; -
查询身份证号最后一位是X的员工信息
select * from emp where idcard like '%X';
-
DQL - 聚合函数
-
介绍
将一列数据作为一个整体，进行纵向计算。
-
常见聚合函数
函数 功能 count 统计数量 max 最大值 min 最小值 avg 平均值 sum 求和 -
语法
select 聚合函数(字段列表) from 表名;
注意：null 值不参与所有聚合函数运算。
-
聚合函数案例
-
统计该企业员工数量
select count(*) from emp; -
统计该企业员工的平均年龄
select avg(age) from emp; -
统计该企业员工的最大年龄
select max(age) from emp; -
统计该企业员工的最小年龄
select min(age) from emp; -
统计西安地区员工的年龄之和
select sum(age) from emp where workaddress = '西安';
-
DQL - 分组查询
-
语法
select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件]; -
where 与 having 区别
- 执行时机不同：where 是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤。
- 判断条件不同：where不能对聚合函数进行判断，而having可以。
注意：
- 执行顺序：where > 聚合函数 > having
- 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。
-
分组查询案例
-
根据性别分组，统计男性员工 和 女性员工的数量
select gender, count(*) from emp group by gender; -
根据性别分组，统计男性员工 和 女性员工的平均年龄
select gender, avg(age) from emp group by gender; -
查询年龄小于 45 的员工，并根据工作地址分组 获取员工数量大于等于3的工作地址
select workaddress, count(*) as address_count from emp where age < 45 group by workaddress having count(*) >= 3;
-
DQL - 排序查询
-
语法
select 字段列表 from 表名 order by 字段1 排序方式1,字段2 排序方式2; -
排序方式
- ASC：升序（默认值）
- DESC：降序
注意：如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序。
-
排序查询案例
-
根据年龄对公司的员工进行升序排序
select * from emp order by age asc; -
根据入职时间，对员工进行降序排序
select * from emp order by entrydate desc; -
根据年龄对公司的员工进行升序排序，年龄相同，再按照入职时间进行降序排序
select * from emp order by age asc, entrydate desc;
-
DQL - 分页查询
-
语法
select 字段列表 from 表名 limit 起始索引, 查询记录数;注意：
- 其实索引从0开始，起始索引= （查询页码 - 1）* 每页显示记录数。
- 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是limit。
- 如果查询的是第一页数据，起始索引可以省略，直接简写为
limit 查询记录数。如limit 10
-
分页查询案例
-
查询第1页员工数据，每页展示10条记录
select * from emp limit 10; select * from emp limit 0, 10; -
查询第2页员工数据，每页展示10条记录
select * from emp limit 1, 10;
-
DQL - 执行顺序
DCL
数据控制语言，用来管理数据库 用户、控制数据库的访问 权限。
DCL - 管理用户
-
查询用户
use mysql; select * from user; -
创建用户
create user '用户名'@'主机名' identified by '密码'; -
修改用户密码
alter user '用户名'@'主机名' identified with mysql_native_password by '新密码'; -
删除用户
drop user '用户名'@'主机名';
注意：
- 主机名可以使用 % 通配。
- 这类SQL开发人员操作的比较少，主要是DBA（数据库管理员）使用。
DCL - 权限控制
MySQL 中定义了很多种权限，但是常见的就以下几种：
| 权限 | 说明 |
|---|---|
| all，all privileges | 所有权限 |
| select | 查询数据 |
| insert | 插入数据 |
| update | 修改数据 |
| delete | 删除数据 |
| alter | 修改表 |
| drop | 删除数据库/表/视图 |
| create | 创建数据库/表 |
其他权限描述及含义，可以直接参考官方文档。
-
语法
-
查询权限
show grants for '用户名'@'主机名'; -
授予权限
grant 权限列表 on 数据库名.表名 to '用户名'@'主机名'; -
撤销权限
revoke 权限列表 on 数据库.表名 from '用户名'@'主机名';
注意：
- 多个权限之间，使用逗号分隔。
- 授权时，数据库名和表名可以使用 * 进行通配，代表所有。
-
函数
函数 是指一段可以直接被另一段程序调用的程序或代码。
字符串函数
MySQL 中内置了很多字符串函数，常用的几个如下：
| 函数 | 功能 |
|---|---|
| concat(S1, S2, …, Sn) | 字符串拼接，将S1，S2，…，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个长度的字符串 |
select 函数(参数);
数值函数
常见的数值函数如下：
| 函数 | 功能 |
|---|---|
| ceil(x) | 向上取整 |
| floor(x) | 向下取整 |
| mod(x, y) | 返回x/y的模 |
| rand() | 返回0~1内的随机数 |
| round(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 之间的天数 |
案例：
-
查询所有员工的入职天数，并根据入职天数倒序排序
select name, datediff(curdate(), entrydate) as entrydays from emp order by entrydays desc;
流程函数
流程函数也是很常用的一类函数，可以在SQL语句中实现条件筛选，从而提高语句的效率。
| 函数 | 功能 |
|---|---|
| if(value, t, f) | 如果value为true，则返回t，否则返回f |
| ifnull(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
注意：约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束。
多表查询
多表关系
概述
项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为三种：
- 一对多（多对一）
- 多对多
- 一对一
一对多（多对一）
- 案例：部门 与 员工的关系
- 关系：一个部门对应多个员工，一个员工对应一个部门
- 实现：在多的一方建立外键，指向一的一方的主键
多对多
- 案例：学生 与 课程的关系
- 关系：一个学生可以选修多门课程，一门课程也可以供多个学生选择
- 实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键
一对一
- 案例：用户 与 用户详情的关系
- 关系：一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率
- 实现：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的（unique）
多表查询概述
- 概述：指从多张表中查询数据
- 笛卡尔积：笛卡尔乘积是指在数学中，两个集合A集合 和 B集合的所有组合情况。（在多表查询时，需要消除无效的笛卡尔积）
- 多表查询分类
- 连接查询
- 内连接：相当于查询A、B交集部分数据
- 外连接：
- 左外连接：查询左表所有数据，以及两张表交集部分数据
- 右外连接：查询右表所有数据，以及两张表交集部分数据
- 自连接：当前表与自身的连接查询，自连接必须使用表别名
- 子查询
- 连接查询
连接查询 - 内连接
-
内连接查询语法
-
隐式内连接
select 字段列表 from 表1, 表2 where 条件 ...; -
显式内连接
select 字段列表 from 表1 [inner] join 表2 on 连接条件 ...;
-
-
内连接演示
-- 1. 查询每一个员工的姓名 ， 及关联的部门的名称（隐式内连接实现） -- 表结构：emp ， dept -- 连接条件：emp.dept_id = dept.id select emp.name, dept.name from emp, dept where emp.dept_id = dept.id; select e.name, d.name from emp e, dept d where e.dept_id = d.id; -- 2. 查询每一个员工的姓名 ， 及关联的部门的名称（显式内连接实现） -- 表结构：emp ， dept -- 连接条件：emp.dept_id = dept.id select e.name, d.name from emp e inner join dept d where e.dept_id = d.id; select e.name, d.name from emp e join dept d where e.dept_id = d.id;注意：如果我们为表起了别名，那么此时我们就不能够再直接通过表名来限定字段。
连接查询 - 外连接
-
左外连接
select 字段列表 from 表1 left [outer] join 表2 on 条件 ...; -
右外连接
select 字段连接 from 表1 right [outer] join 表2 on 条件 ...; -
外连接演示
-- 1. 查询emp 表的所有数据，和对应的部门信息（左外连接） -- 表结构：emp ， dept -- 连接条件：emp.dept_id = dept.id select e.*, d.name from emp e left join dept d on e.dept_id = d.id; -- 2. 查询dept表的所有数据，和对应的员工信息（右外连接） -- 表结构：emp ， dept -- 连接条件：emp.dept_id = dept.id select d.*, e.* from emp e right join dept d on e.dept_id = d.id;
连接查询 - 自连接
-
自连接查询语法：
select 字段列表 from 表A 别名A join 表A 别名B on 条件 ...;自连接查询，可以是内连接查询，也可以是外连接查询。
-
自连接演示
-- 1. 查询员工 及其 所属领导的名字 -- 表结构：emp select a.name, b.name from emp a, emp b where a.managerid = b.id; -- 2. 查询所有员工 emp 及其领导的名字 emp，如果员工没有领导，也需要查询出来 -- 表结构：emp a, emp b select a.name '员工', b.name '领导' from emp a left join emp b on a.managerid = b.id;
联合查询 - union, union all
对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。
select 字段列表 from 表A ...
union [all]
select 字段列表 from 表B ...;
联合查询演示
-- union all, union
-- 1. 将薪资低于 5000 的员工，和 年龄大于50 岁的员工全部查询出来
select * from emp where salary < 5000
union
select * from emp where age > 50;
-- union all : 当多个条件同时满足，会出现重复记录
-- union : 当多个条件同时满足，会将重复的记录进行合并，实现去重
注意： 对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致！！！
子查询
-
概念：SQL 语句中嵌套select 语句，称为嵌套查询，又称子查询。
select * from t1 where column1 = (select column1 from t2);子查询外部的语句可以是insert / update / delete / select 的任何一个。
-
根据子查询结果不用，分为：
- 标量子查询（子查询结果为单个值）
- 列子查询（子查询结果为一列）
- 行子查询（子查询结果为一行）
- 表子查询（子查询结果为多行多列）
标量子查询
子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式，这种子查询成为标量子查询。
常用的操作符： = <> > >= < <=
案例：
-- 1. 查询 “销售部” 的所有员工信息
-- a. 查询“销售部” 部门ID
select id from dept where name = '销售部';
-- b. 根据销售部门ID，查询员工信息
select * from emp where dept_id = (select id from dept where name = '销售部');
-- 2. 查询在 “方东白” 入职之后的员工信息
-- a. 查询 方东白 的入职日期
select entrydate from emp where name = '方东白';
-- b. 查询指定入职日期之后入职的员工信息
select * from emp where entrydate > (select entrydate from emp where name = '方东白');
列子查询
子查询返回的结果是一列（可以是多行），这种子查询称为列子查询。
常用的操作符：in、not in、any、some、all
| 操作符 | 描述 |
|---|---|
| in | 在指定的集合范围之内，多选一 |
| not in | 不在指定的集合范围之内 |
| any | 子查询返回列表中，有任意一个满足即可 |
| some | 与any等同，使用some的地方都可以使用any |
| all | 子查询返回列表的所有值都必须满足 |
案例：
-- 1. 查询 “销售部” 和 “市场部” 的所有员工信息
-- a. 查询 “销售部” 和 “市场部” 的部门ID
select id from dept where name = '销售部' or name = "市场部";
-- b. 根据部门ID，查询员工信息
select * from emp where dept_id in (select id from dept where name = '销售部' or name = '市场部');
-- 2. 查询比财务部所有人工资都高的员工信息
-- a. 查询所有财务部 人员工资
select id from dept where name = '财务部'; -- 得到财务部id
select salary from emp where dept_id = (select id from dept where name = '财务部'); -- 得到财务部 所有人员工资
-- b. 比 财务部 所有人 工资都高的员工信息
select * from emp where salary > all (select salary from emp where dept_id = ( select id from dept where name = '财务部') );
-- 3. 查询比研发部其中任意一人工资高的员工信息
select * from emp where salary > any ( select salary from emp where dept_id = (select id from dept where name = '财务部') );
行子查询
子查询返回的结果是一行（可以是多列），这种子查询称为行子查询。
常用的操作符： = 、<> 、in 、not in
案例：
-- 1. 查询与 “张无忌”的 薪资及直属领导 相同的员工信息
-- a. 查询 “张无忌”的薪资及直属领导
select salary, managerid from emp where name = '张无忌';
-- b. 查询与 “张无忌” 的薪资及直属领导相同的员工信息
select * from emp where (salary, managerid) = (select salary, managerid from emp where name = '张无忌');
表子查询
子查询返回的结果是多行多列，这种子查询称为表子查询。
常用的操作符：in
案例：
-- 1. 查询与 “鹿杖客” ，“宋远桥”的 职位和薪资相同 的员工信息
-- a. 查询 “鹿杖客” ，“宋远桥”的 职位和薪资
select job, salary from emp where name = '鹿杖客' or name = '宋远桥';
-- b. 查询与 “鹿杖客” ，“宋远桥”的 职位和薪资相同 的员工信息
select * from emp where (job, salary) in ( select job, salary from emp where name = '鹿杖客' or name = '宋远桥');
-- 2. 查询入职日期是 “2006-01-01” 之后的员工信息，及其部门信息
-- a. 入职日期是 “2006-01-01” 之后的员工信息
select * from emp where entrydate > '2006-01-01';
-- b. 查询这 部分员工 对应的 部门信息
select e.*, d.* from (select * from emp where entrydate > '2006-01-01') e left join dept d on e.dept_id = d.id;
事务
事务 是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。
默认MySQL 的事务是自动提交的，也就是说，当执行一条DML语句，MySQL会立即隐式的提交事务。
事务操作、
-
查看/设置事务提交方式
select @@autocommit; -- 1 表示自动提交 -- 0 表示手动提交 set @@autocommit = 0; -
提交事务
commit; -
回滚事务
rollback; -
开启事务
start transaction 或 begin; -
查看事务隔离级别
select @@transaction_isolation; -
设置事务隔离级别
set [session|global] transaction isolation level {read uncommitted | read committed | repeatable read | serializable} -- session 表示当前会话 -- global 表示全局
select job, salary from emp where name = ‘鹿杖客’ or name = ‘宋远桥’);
– 2. 查询入职日期是 “2006-01-01” 之后的员工信息，及其部门信息
– a. 入职日期是 “2006-01-01” 之后的员工信息
select * from emp where entrydate > ‘2006-01-01’;
– b. 查询这 部分员工 对应的 部门信息
select e., d. from (select * from emp where entrydate > ‘2006-01-01’) e left join dept d on e.dept_id = d.id;
# 事务
事务 是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。
默认MySQL 的事务是自动提交的，也就是说，当执行一条DML语句，MySQL会立即隐式的提交事务。
## 事务操作、
- 查看/设置事务提交方式
```mysql
select @@autocommit; -- 1 表示自动提交 -- 0 表示手动提交
set @@autocommit = 0;
-
提交事务
commit; -
回滚事务
rollback; -
开启事务
start transaction 或 begin; -
查看事务隔离级别
select @@transaction_isolation; -
设置事务隔离级别
set [session|global] transaction isolation level {read uncommitted | read committed | repeatable read | serializable} -- session 表示当前会话 -- global 表示全局注意：事务隔离级别越高，数据越安全，但是性能越低。
598
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
