-- 修改数据 UPDATE 表名 SET 字段名1 = 值1 , 字段名2 = 值2 , .... [ WHERE 条件 ];
删除数据
1 2
-- 删除数据 DELETE FROM 表名 [ WHERE 条件 ];
DQL
DQL查询语句，语法结构如下：
1 2 3 4 5 6 7 8 9 10 11 12 13 14
SELECT 字段列表 FROM 表名列表 WHERE 条件列表 GROUP BY 分组字段列表 HAVING 分组后条件列表 ORDER BY 排序字段列表 LIMIT 分页参数
基本查询
1 2 3 4 5 6 7 8 9
-- 查询多个字段 SELECT 字段1, 字段2, 字段3 ... FROM 表名; -- 查询所有字段 SELECT * FROM 表名; -- 字段设置别名 SELECT 字段1 [ AS 别名1 ] , 字段2 [ AS 别名2 ] ... FROM 表名; SELECT 字段1 [ 别名1 ] , 字段2 [ 别名2 ] ... FROM 表名; -- 去除重复记录 SELECT DISTINCT 字段列表 FROM 表名;
条件查询
1
SELECT 字段列表 FROM 表名 WHERE 条件列表;
常用的比较运算符如下
比较运算符
功能
>
大于
>=
大于等于
<
小于
<=
小于等于
=
等于
<>或 !=
不等于
BETWEEN ... AND ...
在某个范围之内（含最小、最大值）
IN(...)
在in之后的列表中的值
LIKE占位符
模糊匹配（_匹配单个字符， %匹配任意个字符）
IS NULL
为空
IS NOT NULL
不为空
常用的逻辑运算符如下
逻辑运算符
功能
AND或&&
并且（多个条件同时成立）
OR或`
NOT或 !
非 ， 不是
1 2 3 4 5 6 7 8 9 10 11 12 13
-- 查询年龄等于18的员工 select * from emp where age = 18; -- 查询有身份证号的员工信息 select * from emp where idcard is not null; -- 查询年龄不等于18的员工信息 select * from emp where age <> 18; -- 查询年龄等于18或30或40的员工信息 select * from emp where age = 18 or age = 30 or age = 40; select * from emp where age in(18,30,40); -- 查询姓名为两个字的员工信息 select * from emp where name like '__'; -- 查询身份证号最后一位是X的员工信息 select * from emp where idcard like '%X';
聚合函数
将一列数据作为一个整体，进行纵向计算， NULL值不参与所有聚合函数运算。
1
SELECT 聚合函数(字段列表) FROM 表名;
常见的聚合函数
函数
功能
count
统计数量
max
最大值
min
最小值
avg
平均值
sum
求和
1 2 3 4 5 6 7
-- 统计总记录数 select count(*) from emp; select count(1) from emp; -- 统计idcard字段不为null的记录数 select count(idcard) from emp; -- 统计男员工的年龄之和 select sum(age) from emp where sex = '男';
分组查询
1 2
SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组 后过滤条件];
-- 根据性别分组，统计男性员工 和 女性员工的数量 select gender, count(*) from emp group by gender; -- 查询年龄小于45的员工，并根据工作地址分组，获取员工数量大于等于3的工作地址 select workaddress, count(*) address_count from emp where age < 45 group by workaddress having address_count >= 3; -- 统计各个工作地址上班的男性及女性员工的数量 select workaddress, gender, count(*) '数量' from emp group by gender,workaddress;
排序查询
1
SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1 , 字段2 排序方式2;
排序方式：
ASC：升序（默认值）
DESC： 降序
注意事项:
如果是升序, 可以不指定排序方式ASC；
如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序；
1 2 3 4 5 6
-- 根据年龄对公司的员工进行升序排序 select * from emp order by age asc; -- 根据入职时间, 对员工进行降序排序 select * from emp order by entrydate desc; -- 根据年龄对公司的员工进行升序排序，年龄相同，再按照入职时间进行降序排序 select * from emp order by age asc , entrydate desc;
分页查询
1
SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数;
注意事项:
起始索引从0开始，起始索引 = （查询页码 - 1） 每页显示记录数*。
分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT。
如果查询的是第一页数据，起始索引可以省略，直接简写为limit 10。
1 2 3 4
-- 查询第1页员工数据，每页展示10条记录 select * from emp limit 0,10; -- 查询第2页员工数据，每页展示10条记录，(页码-1)*每页展示记录数 select * from emp limit 10,10;
执行顺序
DQL语句的执行顺序为：from … where … group by … having … select … order by … limit …
DCL
管理用户
1 2 3 4 5 6 7 8 9
-- 查询用户 -- Host代表当前用户访问的主机，User代表访问该数据库的用户名，MySQL中通过Host和User来标识一个用户。 select * from mysql.user; -- 创建用户 CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码'; -- 修改用户密码 ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码' ; -- 删除用户 DROP USER '用户名'@'主机名' ;
-- CASCADE ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 主表名 (主表字段名) ON UPDATE CASCADE ON DELETE CASCADE; -- SET NULL ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 主表名 (主表字段名) ON UPDATE set null ON DELETE set null ;
多表查询
多表关系
关系
实现
案例
一对一
任意一方加入外键，关联另外一方的主键，并设置外键为唯一
用户与用户详情的关系
一对多
多的一方建立外键，指向一的一方的主键
部门与员工的关系，一个部门对应多个员工，一个员工对应一个部门
多对多
建立第三张中间表，中间表至少包含两个外键，分别关联两方主键
学生与课程的关系，一个学生可以选修多门课程，一门课程也可以供多个学生选择
多表查询分类
连接查询
内连接：相当于查询A、B交集部分数据
外连接：
左外连接：查询左表所有数据，以及两张表交集部分数据
右外连接：查询右表所有数据，以及两张表交集部分数据
自连接：当前表与自身的连接查询，自连接必须使用表别名
子查询
内连接
内连接的语法分为两种：隐式内连接、显式内连接。
1 2 3 4 5 6 7 8 9 10
-- 隐式内连接 SELECT 字段列表 FROM 表1 , 表2 WHERE 条件 ... ; -- 显式内连接 SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 连接条件 ... ; -- 查询每一个员工的姓名及关联的部门名称 -- 为表起别名，简化SQL编写，表起别名后，不能再使用表名来指定对应的字段，只能使用别名来指定字段 select e.name,d.name from emp e , dept d where e.dept_id = d.id; select e.name,d.name from emp e join dept d on e.dept_id = d.id; -- 查询年龄小于30岁的员工的姓名、职位、部门信息 select e.name , e.job , d.name from emp e inner join dept d on e.dept_id = d.id where e.age < 30;
外连接
外连接分为两种，分别是：左外连接和右外连接。
1 2 3 4 5 6 7 8
-- 左外连接，相当于查询表1(左表)的所有数据，也包含表1和表2交集部分的数据。 SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件 ... ; -- 右外连接，相当于查询表2(右表)的所有数据，也包含表1和表2交集部分的数据。 SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件 ... ; -- 查询emp表的所有数据, 和对应的部门信息 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;
-- 自连接 SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ... ; -- 查询员工及其所属领导的名字 select a.name , b.name from emp a , emp b where a.managerid = b.id; -- 查询所有员工 emp 及其领导的名字 emp , 如果员工没有领导, 也需要查询出来 select a.name '员工', b.name '领导' from emp a left join emp b on a.managerid = b.id;
联合查询
union查询，就是将多次查询的结果合并起来，形成一个新的查询结果集。
1 2 3
SELECT 字段列表 FROM 表A ... UNION [ ALL ] SELECT 字段列表 FROM 表B ....;
注意：
对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。
union all会将全部的数据直接合并在一起，union会对合并之后的数据去重。
1 2 3 4
-- 查询薪资低于5000的员工和年龄大于50岁的员工，并去重 select * from emp where salary < 5000 union all select * from emp where age > 50;
SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2 );
根据子查询结果不同，分为：
标量子查询（子查询结果为单个值）
列子查询（子查询结果为一列）
行子查询（子查询结果为一行）
表子查询（子查询结果为多行多列）
根据子查询位置，分为：
WHERE之后
FROM之后
SELECT之后
标量子查询
子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式，这种子查询称为标量子查询。
常用的操作符：= <> > >= < <=
1 2 3 4
-- 查询"销售部"的所有员工信息 select * from emp where dept_id = (select id from dept where name = '销售部'); -- 查询在"张三"入职之后的员工信息 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 2 3 4 5 6
-- 查询 "销售部" 和 "市场部" 的所有员工信息 select * from emp where dept_id in (select id from dept where name = '销售部' or name = '市场部'); -- 查询比 财务部 所有人工资都高的员工信息 select * from emp where salary > all ( select salary from emp where dept_id = (select id from dept where name = '财务部') ); -- 查询比研发部其中任意一人工资高的员工信息 select * from emp where salary > any ( select salary from emp where dept_id = (select id from dept where name = '研发部') );
行子查询
子查询返回的结果是一行（可以是多列），这种子查询称为行子查询。
常用的操作符：= 、<> 、IN 、NOT IN
1 2
-- 查询与 "张三" 的薪资及直属领导相同的员工信息 select * from emp where (salary,managerid) = (select salary, managerid from emp where name = '张三');
表子查询
子查询返回的结果是多行多列，这种子查询称为表子查询。
常用的操作符：IN
1 2 3 4
-- 查询与 "张三" , "李四" 的职位和薪资相同的员工信息 select * from emp where (job,salary) in ( select job, salary from emp where name = '张三' or name = '李四' ); -- 查询入职日期是 "2020-01-01" 之后的员工信息 , 及其部门信息 select e.*, d.* from (select * from emp where entrydate > '2020-01-01') e left join dept d on e.dept_id = d.id ;
