1.通用语法及分类

• SQL通用语法

  1. SQL可以单行或多行书写，以分号结尾。
  2. SQL可以使用空格/缩进来增强语句的可读性。
  3. MYSQL数据库的SQL语句不区分大小写，关键字建议使用大写。
  4. 注释：单行注释--注释内容或#注释内容。多行注释/ 注释内容 /。

• SQL分类：

  • DDL(Data Defination Language)：数据定义语言，用来定义数据库对象（数据库，表，字段）
  • DML(Data Manipulation Language)：数据操作语言，用来对数据库中的数据进行增删改。
  • DQL(Data Query Language)：数据查询语言，用来查询数据库中表的记录。
  • DCL(Data Control Language)：数据控制语言，用来创建数据库用户、控制数据库访问权限。

1.1 DDL(数据定义语言)

数据库操作

• 查询

查询所有数据库：

SHOW DATABASES;

查询当前数据库：

SELECT DATABASE();

• 创建

CREATE DATABASE[IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则];

• 删除

DROP DATABASE[IF EXISTS] 数据库名;

• 使用

USE 数据库名;

表操作

• 查询当前数据库所有表

SHOW TABLES;

• 查询表结构

DESC 表名;

• 查询指定表的建表语句

SHOW CREATE TABLE 表名;

• 创建

CREATE TABLE 表名(
  字段名1 字段1类型 [COMMENT 字段1注释],
  字段名2 字段2类型 [COMMENT 字段2注释],
  字段名3 字段3类型 [COMMENT 字段3注释]
)[COMMENT 表注释];

例如：

create  table if not exists student(
    sid int,
    name varchar(20),
    gender varchar(20),
    age int,
    birth date, 
    address varchar(20),
    score double
);

• 修改

  • 添加字段

  ALTER TABLE 表名 ADD 字段名 类型(长度) [COMMENT 注释][约束];

  例如给student表添加一个新的字段：dept，类型为varchar(20)

  ALTER TABLE student ADD 'dept' VARCHAR(20);

  • 修改数据类型

  修改数据类型

  ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度);

  修改字段名和字段类型

  ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) [COMMENT 注释] [约束];

  • 删除字段

  ALTER TABLE 表名 DROP 字段名;

  • 修改表名

  ALTER TABLE 表名 RENAME TO 新表名;

• 删除

  • 删除表

  DROP TABLE[IF EXISTS] 表名;

  • 删除指定表，并重新创建该表

  TRUNCATE TABLE 表名;

数值类型

字符串类型

日期类型

1.2 DML(数据操作语言)

用来对数据库中的数据记录进行增删改操作。

• 添加数据(INSERT)
• 修改数据(UPDATE)
• 删除数据(DELETE)

添加数据

1. 给指定字段添加数据

   INSERT INTO 表名 (字段名1,字段名2,...) VALUES (值1,值2,...);

2. 给全部字段添加数据

   INSERT INTO 表名 VALUES (值1,值2,...);

3. 批量添加数据

   INSERT INTO 表名 (字段名1,字段名2,...) VALUES (值1,值2,...),(值1,值2,...),(值1,值2,...);

   INSERT INTO 表名 VALUES (值1,值2,...),(值1,值2,...),(值1,值2,...);

字符串和日期型数据应该包含在引号中。

修改数据

UPDATE 表名 SET 字段名1=值1, 字段名2=值2, ...[WHERE 条件];

删除数据

DELETE FROM 表名 [WHERE 条件];

1.3 DQL(数据查询语言)

查询关键字：SELECT

语法：

SELECT
    字段列表
FROM
    表名列表
WHERE
    条件列表
GROUP BY
    分组字段列表
HAVING
    分组后条件列表
ORDER BY
    排序字段列表
LIMIT
    分页参数

• 基本查询

  1. 查询多个字段

     SELECT 字段1, 字段2, ... FROM 表名;

     SELECT * FROM 表名;

  2. 设置别名

     SELECT 字段1 [AS 别名1], 字段2[AS 别名2], ... FROM 表名;

  3. 去除重复记录

     SELECT DISTINCT 字段列表 FROM 表名;

• 条件查询

  语法：

  SELECT 字段列表 FROM 表名 WHERE 条件列表;       

  条件：

  比较运算符	功能
  >	大于
  >=	大于等于
  <	小于
  <=	小于等于
  =	等于
  <> 或 !=	不等于
  BETWEEN ... AND ...	在某个范围内（含最小、最大值）
  IN(...)	在in之后的列表中的值，多选一
  LIKE 占位符	模糊匹配（_匹配单个字符，%匹配任意个字符）
  IS NULL	是NULL

  逻辑运算符	功能
  AND 或 &&	并且（多个条件同时成立）
  OR 或 ||	或者（多个条件任意一个成立）
  NOT 或 !	非，不是

  例子：

  -- 年龄等于30
  select * from employee where age = 30;
  -- 年龄小于30
  select * from employee where age < 30;
  -- 小于等于
  select * from employee where age <= 30;
  -- 没有身份证
  select * from employee where idcard is null or idcard = '';
  -- 有身份证
  select * from employee where idcard;
  select * from employee where idcard is not null;
  -- 不等于
  select * from employee where age != 30;
  -- 年龄在20到30之间
  select * from employee where age between 20 and 30;
  select * from employee where age >= 20 and age <= 30;
  -- 下面语句不报错，但查不到任何信息
  select * from employee where age between 30 and 20;
  -- 性别为女且年龄小于30
  select * from employee where age < 30 and gender = '女';
  -- 年龄等于25或30或35
  select * from employee where age = 25 or age = 30 or age = 35;
  select * from employee where age in (25, 30, 35);
  -- 姓名为两个字
  select * from employee where name like '__';
  -- 身份证最后为X
  select * from employee where idcard like '%X';

• 聚合函数

  将一列数据作为一个整体，进行纵向计算。

  常见的聚合函数

  函数	功能
  count	统计数量
  max	最大值
  min	最小值
  avg	平均值
  sum	求和

  语法：

  SELECT 聚合函数(字段列表) FROM 表名;

  null值不参与聚合函数的运算

  例子：

  --1.统计员工数量
  select count(*) from emp;
  
  --2.统计该企业员工的平均年龄
  select avg(age) from emp;
  
  --3.统计该企业员工的最大年龄
  select max(age) from emp;
  
  --4.统计该企业员工的最小年龄
  select min(age) from emp;
  
  --5.统计西安地区员工的年龄之和
  select sum(age) from emp where workaddress='西安';

• 分组查询

  语法：

  SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [Having 分组过滤条件];

  where和having的区别：

  1. 执行时机不同：where是在分组前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤。
  2. 判断条件不同：where不能对聚合函数进行判断，而having可以。

  例子：

  --1.根据性别分组，统计男性员工和女性员工的数量
  select gender, count(*) from emp group by gender;
  
  --2.根据性别分组，统计男性员工和女性员工的平均年龄
  select gender, avg(age) from emp group by gender;
  
  --3.查询年龄小于45的员工，并根据工作地址进行分组，获取员工数量大于等于3的工作地址
  select workaddress, count(*) address_count from emp where age < 45 group by workaddress having address_count >= 3;

  注意：

  • 执行顺序：where > 聚合函数 >having。
  • 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。

• 排序查询

  语法：

  SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1, 字段2 排序方式2;

  ASC 升序，DESC降序。

  例子：

  --1.根据年龄对公司员工进行升序排序
  select * from emp order by age asc;
  
  --2.根据入职时间对员工进行降序排序
  select * from emp order by entrydate desc;
  
  --3.根据年龄对员工进行升序排序，年龄相同，再按照入职时间进行降序排序
  select * from emp order by age asc, entrydate desc;

• 分页查询

  语法：

  SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询记录数;

  注意：

  • 起始索引从0开始，起始索引=（查询页码-1）*每页显示记录数
  • 分页查询是数据库的方言，不同数据库有不同实现，MySQL是LIMIT
  • 如果查询的是第一页数据，起始索引可以省略，直接简写 LIMIT 10

  例子：

  --1.查询第1页员工数据，每页展示10条记录
  select * from emp limit 0, 10;
  
  --2.查询第2页员工数据，每页展示10条记录
  select * from emp limit 10, 10;

DQL的执行顺序：FROM -> WHERE -> GROUP BY -> SELECT ->ORDER BY -> LIMIT.

1.4 DCL(数据控制语言)

• 查询用户

  SELECT * FROM user;

• 创建用户

  CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

• 修改用户密码

  ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password or '新密码';

• 删除用户

  DROP USER '用户名'@'主机名';

例子：

-- 创建用户test，只能在当前主机localhost访问
create user 'test'@'localhost' identified by '123456';
-- 创建用户test，能在任意主机访问
create user 'test'@'%' identified by '123456';
create user 'test' identified by '123456';
-- 修改密码
alter user 'test'@'localhost' identified with mysql_native_password by '1234';
-- 删除用户
drop user 'test'@'localhost';

权限控制

• 查询权限

  SHOW GRANTS FOR '用户名'@'主机名';

• 授予权限

  GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';

• 撤销权限

  REMOVE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';

注意：

• 多个权限用逗号分隔
• 授权时，数据库名和表名可以用 * 进行通配，代表所有

2.函数

• 字符串函数
• 数值函数
• 日期函数
• 流程函数

2.1 字符串函数

例子：

-- 拼接
select concat('Hello', 'World');

--小写
select lower('Hello');

--大写
select upper('Hello');

--左填充
select lpad('01',5,'-'); # 结果：---01

--右填充
select rpad('01',5,'-'); # 结果：01---

--去除空格
select trim(' Hello World  '); # 结果：Hello World

--切片
select substring('Hello MySQL',1,5); # 结果：Hello

2.2 数值函数

例子：

-- 向上取整
select ceil(1.1);

-- 向下取整
select floor(1.9);

-- 返回x/y的模
select mod(x,y);

-- 求随机数
select rand();

-- 四舍五入
select round(2.345, 2);

2.3 日期函数

--DATE_ADD
select date_add(now(), INTERVAL 70 YEAR);

--DATEDIFF
select datediff('2021-10-01', '2021-12-01');

2.4 流程控制函数

-- 需求：查询emp表的员工姓名和工作地址（北京/上海 --> 一线城市   其他 --> 二线城市）
select
    name,
    case when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end as '工作地址'
from emp;

3. 约束

概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据。

目的：保证数据库中数据的正确、有效性和完整性。

分类：

例子：

create table user(
    id int primary key auto_increment comment '主键',
    name varchar(10) not null unique comment '姓名',
    age int check ( age>0 && age<=120 ) comment '年龄',
    status char(1) default '1' comment '状态',
    gender char(1) comment '性别'
) comment '用户表';

外键约束

• 添加外键

  语法：

  -- 创建时添加外键
  CREATE TABLE 表名(
  字段名 数据类型,
  ...
  [CONTRAINT][外键名称] FORGEIN KEY (外键字段名) REFFERENCES 主表 (主表列名)
  )
  
  -- 创建后添加外键
  ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段名) REFFERENCES 主表 (主表列名);

  例子：

  -- 创建部门表
  create table dept(
    id int auto_increment comment 'ID' primary key ,
    name varchar(50) not null comment '部门名称'
  )comment '部门表';
  
  -- 给员工表添加外键
  alter table emp add constraint fk_emp_dept_id foreign key(dept_id) references dept (id);

• 删除外键

  语法：

  ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

  例子：

  alter table emp drop foreign key fk_emp_dept_id;

• 删除/更新行为

  行为	说明
  NO ACTION	当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。（与RESTRICT一致）
  RESTRICT	当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。（与NO ACTION一致）
  CASCADE	当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则也删除/更新外键在子表中的记录。
  SET NULL	当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null（这就要求该外键允许取null）
  SET DEFAULT	父表有变更时，子表将外键列设置成一个默认的值（Innodb不支持）

  语法

  ALTER 表名 ADD CONTRAINT 外键名称 FOREIGN KEY(外键字段) REFERENCES 主表名 (主表字段名) ON UPDATE CASCADE ON DELETE CASCEDE;

4. 多表查询

4.1 多表关系

• 一对多（多对一）
  • 案例：部门与员工之间的关系
  • 关系：一个部门对应多个员工，一个员工对应一个部门
  • 实现：在多的一方建立外键，指向一的一方的主键
• 多对多
  • 案例：学生与课程之间的关系
  • 关系：一个学生可以选择多门课程，一门课程也可以供多个学生选择
  • 实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方的主键
• 一对一
  • 用户与用户详情的关系
  • 关系：一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升效率
  • 实现：在任意一方加入外键，关联另一方的主键，并设置外键为唯一的

4.2查询

合并查询（笛卡尔积）

select * from emp,dept;

消除无效笛卡尔积

select * from emp, dept where emp.id=dept.id;

4.3 内连接查询

查询两张表交集的部分

• 隐式内连接：

  SELECT 字段列表 FROM 表1,表2 WHERE 条件...;

• 显式内连接

  SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件...;

显式性能比隐式高

--查询每一个员工的姓名，及关联的部门（隐式内连接实现）
--表结构：emp, dept
select emp.name, dept.name from emp, dept where emp.dept_id = dept.id;
--使用别名简化上面的sql语句
select e.name, d.name from emp e, dept d where e.dept_id = d.id;

--查询每一个员工的姓名，及关联的部门（显式内连接实现）
--表结构：emp, dept
select e.name, d.name from emp e inner join dept d on e.dept_id = d.id;

4.3 外连接查询

• 左外连接

  查询左表所有数据，以及两张表交际部分数据

  SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件...;

• 右外连接

  查询右表所有数据，以及两张表交际部分数据

  SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件...;

例子：

-- 查询emp表的所有数据，和对应的部门信息（左外连接）
-- 表结构：emp，dept
select e.*, d.name from emp e left outer join dept d on e.dept_id = d.id;

-- 查询dept表的所有数据，和对应员工的信息（右外连接）
select d.*, e.* from emp e right outer join dept d on e.dept_id = d.id;

4.4 自连接查询

语法：

SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件...;

自连接查询可以是内连接查询，也可以是外连接查询。

例子：

-- 查询员工及其所属领导的名字
select a.name, b.name from employee a, employee b where a.managerid = b.id;
-- 没有领导的也查询出来
select a.name, b.name from employee a left join employee b on a.managerid = b.id;

4.5 联合查询-union，union all

把多次查询的结果合并起来，形成一个新的查询结果集。

语法：

SELECT 字段列表 FROM 表A...
UNION[ALL]
SELECT 字段列表 FROM 表B...;

例子：

-- 将薪资低于5000的员工和年龄大于50的员工全部查询出来
select * from emp where salary < 5000
union all
select * from emp where age > 50;

• UNION ALL 会有重复结果，UNION 不会
• 联合查询比使用or效率高，不会使索引失效

4.6 子查询

概念：SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询。

子查询的外部语句可以是INSERT，UPDATE，DELETE，SELECT中的任何一个。

根据子查询的结果不同，分为：

• 标量子查询（子查询的结果为单个值）
• 列子查询（子查询的结果为一列）
• 行子查询（子查询结果为一行）
• 表子查询（子查询结果为多行）

根据子查询的位置，可分为：

• WHERE之后
• FROM之后
• SELECT之后

4.6.1标量子查询

常用的操作符：=, <>, >, >=, <, <=。

例子：

--1. 查询销售部的所有员工信息
--1.1 查询销售部的ID
select id from dept where name='销售部';
--1.2 根据销售部ID查询员工信息
select * from emp where dept_id = 4;
--1.3 合并
select * from emp where dept_id = (select id from dept where name = '销售部'); 

4.6.2 列子查询

子查询的返回结果是一列（可以是多行）。

常用操作符：IN, NOT IN, ANY, SOME, ALL。

例子：

--1. 查询销售部和市场部的所有员工信息
select * from emp where dept_id in (select id from dept where name='销售部' or name = '市场部');

--2. 查询比财务部所有人工资都高的员工信息
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 = '研发部'));

4.6.3 行子查询

子查询的返回结果是一行（可以是多列）。

常用操作符：IN, NOT IN, =, <>。

例子：

--查询与'张无忌'的薪资及直属领导相同的员工信息
select * from emp where (salary, managerid) = (select salary, managerid from emp where name = '张无忌');

4.6.4 表子查询

子查询的返回结果是多行多列

常用操作符：IN

例子：

--查询与'鹿杖客'和'宋远桥'职位和薪资相同的员工信息
select * from emp where (job, salary) in (select job, salary from emp where name = '鹿杖客' or name = '宋远桥');

--查询入职日期是2006-01-01之后的员工，及其部门信息
select * from emp where entrydate > '2006-01-01';
select e.*, d.* from (select * from emp where entrydate > '2006-01-01') e left join dept d on e.dept_id = d.id;

5. 事务

事务 是一组操作的集合，它是一个不可分割的工作单位，事务会被所有操作作为一个整体一起向系统提交或撤销操作请求，这些操作要么同时成功，要么同时失败。

5.1 事务操作

-- 张三向李四转账1000
-- 1.查询张三账户余额
select * from account where name = '张三';
-- 2.将张三账户余额扣除1000
update account set money = money - 1000 where name = '张三';

模拟sql错误

--3. 将李四账户余额加1000
update account set money = money + 1000 where name = '李四';
# 上述语句会导致，张三余额扣除一千但是李四余额并没有增加一千

为了解决这个问题，需要将三个操作封装成一个事务。

操作方式一：

-- 查看事务的提交方式
select @@autocommit; # 查询提交方式，默认是1表示自动提交，我们需要将其修改成0
--设置事务提交方式，该设置仅对当前会话生效
set @@autocommit = 0;

-- 张三向李四转账1000
-- 1.查询张三账户余额
select * from account where name = '张三';
-- 2.将张三账户余额扣除1000
update account set money = money - 1000 where name = '张三';
--3. 将李四账户余额加1000
update account set money = money + 1000 where name = '李四';

-- 提交事务
commit;

-- 如果异常则需要执行下面
rollback;

操作方式二：

--开启事务(也可以使用begin transaction)
start transaction;

-- 张三向李四转账1000
-- 1.查询张三账户余额
select * from account where name = '张三';
-- 2.将张三账户余额扣除1000
update account set money = money - 1000 where name = '张三';
--3. 将李四账户余额加1000
update account set money = money + 1000 where name = '李四';

--提交
commit;
--回滚
rollback;

5.2 事务四大特性

• 原子性（Atomicity）：事务是不可分割的最小单元，要么全部成功，要么全部失败。
• 一致性（Consistency）：事务完成时，必须使所有的数据保持一致状态（例如不管转账是否成功张三和李四账户余额保持一致）。
• 隔离性（Isolation）：数据库提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行（事务的并发）。
• 持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

5.3 并发事务问题

5.4 事务的隔离级别

• √表示在当前隔离级别下该问题会出现
• Serializable 性能最低；Read uncommitted 性能最高，数据安全性最差

-- 查看事务的隔离级别
select @@transaction_isolation

-- 设置隔离级别
SET [ SESSION | GLOBAL ] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE };
# session 表示当前会话，global表示全局会话