2022.3.24学习总结

dql语言完结

1.子查询剩余部分

#2.列子查询（多行子查询）
/*多行比较操作符：in /not in等于列表中的任意一个/都不等于,相当于简化or的作用
                  any/some和子查询返回的某一个值比较
                  all和子查询返回的所有值比较
*/      

#案例1，返回location_id是1400或1700的部门中的所有员工姓名
SELECT last_name
FROM employees
WHERE department_id IN (
SELECT department_id
FROM departments
WHERE location_id IN (1400.,1700)
);      

#案例2：返回其他部门中比job_id为'IT_PROG'部门任一工资低的员工的：工号，姓名，job_id以及salary
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE job_id <> 'IT_PROG'
AND salary < ANY(
SELECT salary
FROM employees
WHERE job_id = 'IT_PROG'
);

#案例3：返回其他部门中比job_id为'IT_PROG'部门所有工资低的员工的：工号，姓名，job_id以及salary
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE job_id <> 'IT_PROG'
AND salary < ALL(
SELECT salary
FROM employees
WHERE job_id = 'IT_PROG'
);

#3.行子查询（结果集是一行多列或多行多列）

#案例：查询员工编号是最小的并且工资最高的员工信息
#常规
SELECT *
FROM employees
WHERE employee_id <= ALL(
SELECT employee_id FROM employees)
AND salary >= ALL(
SELECT salary FROM employees);
#行子查询写法，把要查的东西打包
SELECT *
FROM employees
WHERE (employee_id,salary)=(
SELECT MIN(employee_id),MAX(salary)
FROM employees);

#二、放在select后面，可代替

#案例1：查询每个部门的员工个数
#外连接
SELECT COUNT(e.`first_name`),d.department_id
FROM employees AS e RIGHT JOIN departments AS d
ON e.`department_id`=d.`department_id`
GROUP BY d.`department_id`;

#子查询
SELECT d.*,(
SELECT COUNT(*)
FROM employees e
WHERE e.department_id=d.department_id
)个数
FROM departments d;

#案例2：查询员工号=102的部门名
#连接查询
SELECT d.department_name
FROM departments AS d INNER JOIN employees AS e
ON d.`department_id`=e.`department_id`
WHERE e.employee_id=102;

#子查询
SELECT department_name
FROM departments
WHERE department_id =(
SELECT department_id
FROM employees
WHERE employee_id=102
);

#三、from后面,把第一次查询的结果当做一张表，必须起别名，再连接查询
#非等值连接是属于内查询
#案例：查询每个部门的平均工资的工资等级

SELECT a.*,j.grade_level
FROM
(SELECT AVG(salary) AS ag,department_id AS d
FROM employees
GROUP BY department_id) AS a INNER JOIN job_grades AS j
WHERE a.ag BETWEEN j.lowest_sal AND j.highest_sal;

#上面的where可以用on代替

#4.放在exists后面（相关子查询）,exists=是否存在
/*
exists(完整的查询语句)
结果只有1和0
可被代替
*/
#引入
SELECT EXISTS(SELECT employee_id FROM employees WHERE salary=30000);

#练习1:查询和Zlotkey相同部门的员工姓名和工资
SELECT department_id,last_name,salary
FROM employees
WHERE department_id=(
SELECT department_id
FROM employees
WHERE last_name='Zlotkey'
);

#练习2：查询工资比公司平均工资高的员工的工号，姓名和工资
SELECT employee_id,last_name,salary
FROM employees
WHERE salary>(
SELECT AVG(salary) FROM employees
);

#练习3：查询各部门中工资比本部门平均工资高的员工号，姓名工资
SELECT e.department_id,e.employee_id,e.last_name,e.salary
FROM employees AS e INNER JOIN
(SELECT AVG(salary) AS davg,department_id
FROM employees
GROUP BY department_id) AS d
ON e.`department_id`=d.department_id
WHERE e.`salary`>d.davg;

#练习4：查询和姓名中包含字母u的员工在相同部门的员工的工号和姓名
SELECT employee_id,last_name
FROM employees
WHERE department_id IN (
SELECT department_id
FROM employees
WHERE last_name LIKE '%u%');

#练习5：查询在部门的location_id为1700的部门工作的员工的员工号
SELECT e.`employee_id`
FROM employees AS e INNER  JOIN departments AS d ON e.`department_id`=d.`department_id`
WHERE d.`location_id`=1700;

#练习6：查询管理者是king的员工姓名和工资
SELECT last_name,salary
FROM employees
WHERE manager_id IN(
SELECT employee_id
FROM employees
WHERE last_name='K_ing');

#练习7：查询工资最高的员工的姓名，要求first_name和last_name显示为一列，列名为姓.名
SELECT CONCAT(last_name,first_name) AS '姓.名'
FROM employees
WHERE salary >= ALL(
SELECT salary
FROM employees);

#练习8：查询工资最低的员工信息
SELECT *
FROM employees
WHERE salary <= ALL(
SELECT salary
FROM employees);

#练习9：查询平均工资最低的部门信息

SELECT *
FROM departments AS d
WHERE d.`department_id`=(
SELECT department_id
FROM (SELECT AVG(salary) AS savg,department_id
FROM employees
GROUP BY department_id
ORDER BY savg
LIMIT 1) AS davg);

#练习10，查询平均工作最低的部门信息和该部门的平均工资
SELECT d.*,savg
FROM departments AS d,(SELECT AVG(salary) AS savg,department_id
FROM employees
GROUP BY department_id
ORDER BY savg
LIMIT 1) AS davg
WHERE d.`department_id`=davg.department_id;

#练习11：查询平均工资最高的job信息
SELECT *
FROM jobs AS j
WHERE j.`job_id`=(
SELECT job_id
FROM (SELECT AVG(salary) AS savg,job_id
FROM employees
GROUP BY job_id
ORDER BY savg DESC
LIMIT 1) AS javg);

#练习12：查询平均工资高于公司平均工资的部门有哪些

SELECT davg.department_id
FROM
(SELECT AVG(salary) AS savg ,department_id
FROM employees
GROUP BY department_id) AS davg
WHERE davg.savg>
(SELECT AVG(salary)
FROM employees);

#练习13：查询出公司所有manager的详细信息
SELECT *
FROM employees
WHERE employee_id IN(
SELECT DISTINCT manager_id
FROM employees);

#练习14：各个部门中最高工资中最低的那个部门的最低工资是多少
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING department_id=
(
SELECT dmax.department_id FROM
(SELECT MAX(salary) AS smax,department_id
FROM employees
GROUP BY department_id
ORDER BY smax
LIMIT 1) AS dmax
);

#练习15：查询平均工资最高的部门的manager的详细信息
SELECT * FROM employees WHERE employee_id=
(
SELECT manager_id
FROM departments AS d
WHERE d.`department_id`=
(
SELECT davg.department_id FROM
(SELECT AVG(salary) AS savg,department_id
FROM employees
GROUP BY department_id
ORDER BY savg DESC
LIMIT 1) AS davg
));

2.分页查询

#分页查询
/*
应用场景：要显示的数据一页显示不全，需要分页提交sql请求
语法：
     select 查询列表
     from  表名
     【join type jion 表2
     on 连接条件
     where 筛选条件
     group by 分组字段
     having 分组后字段
     order by 排序的字段】
     limit offset,size;
     
     offset要显示条目的起始索引（从0开始）
     size要显示的条目个数
     
     特点：1.limit 语句放在最后
           2.公式：
           要显示的页数是page，每页条目数是size
           
           select 查询列表
           from 表
           limit(page-1)*size,size;
         
*/
#案例1：查询5条员工信息
SELECT * FROM employees LIMIT 0,5;
SELECT * FROM employees LIMIT 5;

#案例2：查询第十一条到第二十五条

SELECT * FROM employees LIMIT 10,15;

#案例3：查询有奖金的员工信息，并且工资较高的前十名
SELECT *
FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY salary DESC
LIMIT 10;

#案例

3.联合查询

#联合查询
/*
union：联合 合并：将多条查询语句的结果合并成一个结果


语法：查询语句1
      union
      查询语句2
      union
      .....
      
 适合：要查询的结果来自多个表，且多个表没有直接的连接关系，但要查询的信息一致时
 
 特点：1.要求多个表查询的列数一致
       2.要求列数的内容一致
       3.union会自动去重，union all会显示包括重复的所有
       
       
*/

#引入：查询部门编号大于90或邮箱中包含a的员工信息

SELECT * FROM employees WHERE department_id >90 OR email LIKE '%a%';

SELECT * FROM employees WHERE department_id >90
UNION
SELECT * FROM employees WHERE email LIKE '%a%';

4.DML初步

#DML语言
/*
数据操作语言
插入：insert
修改：update
删除：delete

值：values
字符型：verchar
日期:datev
*/

#一、插入语句
#方式一:经典插入
/*
语法：

insert into 表名(列名1,......) value(值1，...)
*/

#1.要求插入值的类型要与列的类型一致或兼容
INSERT INTO beauty(id,NAME,sex,borndate,phone,photo,boyfriend_id)
VALUES(13,'河豚宝宝','女','1996-12-04','13257088075',NULL,99);

SELECT * FROM beauty;

INSERT INTO boys(id,boyName,userCP)
VALUE(99,'喵喵',NULL);

#2.不可以为null的列必须插入值，可以为null的列是如何插入值的？
#方式一：写列名，值写null，方式二：列和值啥也不写直接省略掉
INSERT INTO beauty(id,NAME,sex,phone)
VALUES(15,'mimi','女','1000000');

#3.列的顺序可以调换
INSERT INTO beauty(NAME,id,phone,sex)
VALUES('hah',16,'2222222','女');

#4.列数和值的个数必须一致，和第2条对应上

#5.可以省略列名，默认所有列,并且列的顺序和表中顺序一致
INSERT INTO beauty()
VALUES(17,'koko','女',NULL,'19999',NULL,3);

5、明日计划,DML剩余内容