1、子查询
子查询指一个查询语句嵌套在另一个查询语句内部的查询。
很多时候查询需要从结果集中获取数据,或者需要从同一个表中先计算得出一个数据结果,然后与这个数据结果进行比较。
在select、from、where/having、exists后面都可以使用子查询
1.1 where或having后面
1.1.1 where后面的子查询。
例:查询工资最高的员工信息。
SELECT * FROM emplyees WHERE salary = (
SELECT max(salary) FROM emplyees
);
1.1.2. having后面的子查询。
例:查询最低工资大于部门6的最低工资的部门id。
SELECT department_id,min(salary) FROM emplyees GROUP BY department_id
HAVING min(salary) > (
SELECT min(salary) FROM emplyees WHERE department_id = 6
);
1.2 select后面
例:查询员工姓名,工资以及所有员工平均工资。
SELECT t1.emplyee_name,t1.salary,(
SELECT avg(salary) FROM emplyees
) avg_salary
FROM emplyees t1;
1.3 from后面
将子查询的结果集当作是一张新的表格。(必须得加个别名)
例:查询每个部门的平均工资等级。
SELECT * FROM (
SELECT AVG(salary) avg_salary_dpt,department_id
FROM emplyees
GROUP BY department_id
) t1 LEFT JOIN job_grades t2
ON t1.avg_salary_dpt BETWEEN t2.lowest_sal AND t2.higest_sal;
1.4 exists后面(相关子查询)
语法:exists(完整的查询语句)。当子查询有结果返回1,没有结果返回0。
例:查询有员工的部门。
SELECT * FROM departments t1
WHERE EXISTS(
SELECT * FROM emplyees t2
WHERE t2.department_id = t1.department_id
);
2、分页查询
当要显示的数据,一页显示不全或者数据量过大不适合一次性展示的时候,需要用分页提交sql请求。
limit放在查询语句的最后。
select [查询列表]
from 表
[连接类型join 表2 on 连接条件]
[where 筛选条件]
[group by 筛选条件]
[having 分组后筛选]
[order by 排序]
limit offset,size
offset:要显示的记录的起始索。
size:要显示的记录个数。
例一:查询前五个员工信息。
SELECT * FROM emplyees LIMIT 0,5;
例二:查询第6到第10个的员工信息。
SELECT * FROM emplyees LIMIT 5,5;
3、联合查询
将多条查询语句的结果合并成一个结果。
场景:要查询的结果来自于多张表,且多张表没有直接的连接关系,但查询的信息一致。
例:查询学上姓名与教师姓名,在同一个结果集返回。
SELECT name FROM student
UNION
SELECT tname FROM teacher;
UNION会有去重效果,使用UNION ALL可以保留重复的部分
SELECT name FROM student
UNION ALL
SELECT tname FROM teacher;
4、DML语法
数据操纵语句,用于添加、修改、删除数据库记录。
数据库的所有操作其实本质都是修改
4.1 insert插入语句
4.1.1 语法
方式一
insert into 表名(列名1,列名2,…) values (值1,值2,…);
方式二
insert into 表名 set 列名1 = 值1,列名2 = 值2,…
案例
将信息(张三/男)插入到员工表中)
insert into emplyees (emplyee_name, sex) values ("张三", "男");
或者
insert into emplyees set emplyee_name = "张三", sex = "男";
4.1.2 两种插入方式的对比
方式一支持批量插入,使用逗号隔开。
insert into emplyees (emplyee_name, sex) values ("张三", "男"),("李四", "女"),("王五","男");
方式一支持子查询,方式二不支持。
将查询结果直接插入到表中
INSERT INTO emplyees(emplyee_name, email, sex, salary)
SELECT emplyee_name, email, sex, salary
FROM emplyees WHERE emplyee_id in (1,2);
4.2 update修改语句
4.1.2 修改单表的记录
UPDATE 表名 SET 列名1 = 值1,列名2 = 值2,… [WHERE 筛选条件] -- 不加where会把整张表的数据改掉
例:修改emplyee_id为1的员工信息。
UPDATE emplyees SET emplyee_name = '李四', email = 'lisi@163.com'
WHERE emplyee_id = 1;
4.1.3. 修改多表的记录
例:修改员工emplyee_id为1的员工信息,以及该员工对应的部门名称
UPDATE emplyees t1
LEFT JOIN departments t2
ON t1.department_id = t2.department_id
SET t2.department_name = '后勤部'
WHERE t1.emplyee_id = 1;
4.3 delete删除
4.3.1. 删除单表的记录
DELETE FROM 表名 WHERE 筛选条件 -- 不加where默认删除整张表的数据
例:删除员工id为1001的数据
DELETE FROM emplyees WHERE emplyee_id = 1001;
4.3.2 truncate
清空数据,只能删除全表的数据,比delete效率高一点点。
truncate table 表名;