MySQL（三）

子查询、分页查询、联合查询、DML语法

01 子查询

/*
概念:子查询指一个查询语句嵌套在另一个查询语句内部的查询
特点:嵌套查询，先执行子部分
优点:实现动态绑定
注：在select、from、where/having、exists后面都可以使用子查询
    
*/
​
#where或having后面
# where后面的子查询
# 案例：查询工资最高的员工信息
SELECT max(salary) FROM emplyees; -- 最高工资29000
SELECT * FROM emplyees WHERE salary = 29000; -- 最高工资对应的员工信息
-- 使用子查询实现
SELECT * FROM emplyees
WHERE salary =
(
SELECT max(salary) FROM emplyees
);
​
​
# having后面的子查询。
#案例：查询最低工资大于部门6的最低工资的部门id
SELECT min(salary) FROM emplyees 
WHERE department_id = 6; -- 部门6的最低工资
4019.61
SELECT department_id,min(salary) 
FROM emplyees GROUP BY department_id
HAVING min(salary) > 4019.61;  -- 最低工资大于4019.61的部门
​
-- 使用子查询实现
SELECT department_id,min(salary) 
FROM emplyees
GROUP BY department_id
HAVING min(salary) > 
(
SELECT min(salary) FROM emplyees WHERE department_id = 6
);
​
#select后面
#案例：查询员工姓名，工资以及所有员工平均工资
SELECT t1.emplyee_name,t1.salary,
(
SELECT avg(salary) FROM emplyees
) avg_salary
FROM emplyees t1;
​
#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;
​
#   exists后面（相关子查询）
#   语法：exists(完整的查询语句)。当子查询有结果返回1，没有结果返回0
SELECT EXISTS(SELECT * FROM emplyees WHERE emplyee_id = -1); -- 0
SELECT EXISTS(SELECT * FROM emplyees); -- 1
​
#案例：查询有员工的部门
SELECT * FROM departments t1
WHERE EXISTS(
SELECT * FROM emplyees t2
WHERE t2.department_id = t1.department_id
);
​
​

02 分页查询与联合查询

 #分页查询
        /*当要显示的数据，一页显示不全或者数据量过大不适合一次性展示的时候，需要用分页提交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;
​
        #联合查询
        #将多条查询语句的结果合并成一个结果。
#场景：要查询的结果来自于多张表，且多张表没有直接的连接关系，但查询的信息一致。
#案例：查询学上姓名与教师姓名，在同一个结果集返回
        SELECT name FROM student
        UNION
        SELECT tname FROM teacher;
​
        -- UNION会有去重效果，使用UNION ALL可以保留重复的部分
        SELECT name FROM student
        UNION ALL
        SELECT tname FROM teacher;
​
    }

03 DML语法

#概念：数据操纵语句，用于添加、修改、删除数据库记录。
​
#insert插入语句
# 语法：
-- 方式一
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 = "男";
-- 表名后面的列名可以省略，默认是所有列名，填写的值需要把所有的列补全
insert into emplyees values 
(null, "张三", null, "男", null, null, null,
null, null, null, null);
​
-- 两种插入方式的对比
-- 方式一支持批量插入，使用逗号隔开
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);
​
​
#update修改语句
# 修改单表的记录
UPDATE 表名 SET 列名1 = 值1,列名2 = 值2,… [WHERE 筛选条件]
-- 不加where会把整张表的数据改掉，条件忘记了就准备跑路吧
​
#案例：修改emplyee_id为1的员工信息
UPDATE emplyees SET emplyee_name = '李四', email = 'lisi@163.com' WHERE emplyee_id = 1;
​
#修改多表的记录
update 表1 别名
inner|left|right join 表2 别名
on 连接条件
set 列 = 值
where 筛选条件
#案例：修改员工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;
​
​
#delete删除
# 删除单表的记录
DELETE FROM 表名 WHERE 筛选条件 -- 不加where默认删除整张表的数据
#案例：删除员工id为1001的数据
DELETE FROM emplyees WHERE emplyee_id = 1001;
​
#删除多表的记录
DELETE 表1的别名，表2的别名
FROM 表1 别名
inner|left|right join 表2 别名
ON 连接条件
WHERE 筛选条件
​
#案例：删除员工id为1001的员工信息，以及该员工对应的部门信息。
DELETE t1,t2
FROM emplyees t1
LEFT JOIN departments t2
ON t1.department_id = t2.department_id
WHERE t1.emplyee_id = 1;
​
#truncate
#清空数据，只能删除全表的数据，比delete效率高一点点
truncate table 表名;