sql多表查询+小题目

查询

1. 单表查询：聚合函数，条件查询，分页，排序，分组
2. 多表查询：内连接（方言版，标准版），外连接（左外，右外），子查询（结果集多行多列）

单表查询：

1. 条件查询：

   in函数表示一个集合

   SELECT * from t_emps;
   SELECT * FROM t_emps WHERE sex='女';
   SELECT * FROM t_emps WHERE sex<>'女';
   SELECT * FROM t_emps WHERE sex<>'女' and salary>5000 AND deptId=5;
   SELECT * FROM t_emps WHERE sex<>'女' and salary>5000 AND deptId in(4,5);
   

2. 聚合查询：

   SELECT count(*) FROM t_emps;//总数
   SELECT sum(salary) from t_emps;//工资总数
   SELECT Max(salary) from t_emps;//最大工资
   SELECT MIN(salary) from t_emps;//最低工资
   SELECT avg(salary) from t_emps;//平均工资
   

3. 分页查询：

   limit(0，5)0表示开始页数，5表示要展示的页数

   SELECT * from t_emps LIMIT 0,5;//第一页
   SELECT * from t_emps LIMIT 5,5;//第二页
   SELECT * from t_emps LIMIT 10,5;//第三页
   SELECT * from t_emps LIMIT 15,5;//第四页
   SELECT * from t_emps LIMIT 20,5;//第五页
   

4. 排序

   SELECT * FROM t_emps ORDER BY birth ASC,salary Asc;
   SELECT * from t_emps WHERE sex<>'女' and deptId=1 ORDER BY salary desc ;
   SELECT * from t_emps WHERE sex='女' ORDER BY salary ASC;
   
   

5. 分组

   结合聚合函数使用

   SELECT id,SUM(salary) from t_emps GROUP BY id;
   SELECT count(*),sex FROM t_emps GROUP BY deptId;
   

6. 模糊查询

   %表示多个占位符

   _表示一个占位符

   SELECT * FROM t_emps WHERE name LIKE '%曹%';
   SELECT * FROM t_emps WHERE name LIKE '曹_';
   

多表查询：

1. 笛卡尔连接

   SELECT *  FROM t_dept,t_emps;
   

2. 内连接

   SELECT * FROM t_dept d,t_emps e WHERE d.did=e.deptId;
   //方言版内连接
   SELECT * from t_dept d INNER JOIN t_emps e on d.did=e.deptId;
   //标准版内连接
   

3. 外连接

   SELECT * from t_dept d LEFT JOIN t_emps e on d.did=e.deptId;
   //左外连接
   SELECT * from t_dept d RIGHT JOIN t_emps e on d.did=e.deptId; 
   //右外连接
   
   SELECT * from t_dept d full JOIN t_emps e on d.did=e.deptId;//全连接，mysql不支持
   

4. 工资最高的人所在的部门

思路：

1. 首先找查询到最高的工资；
2. 根据最高的工资找到对应的部门号（部门号是另一个表的主键）
3. 将另一个表的id=部门号；

SELECT MAX(salary) from t_emps;
SELECT deptId from t_emps WHERE salary=(SELECT MAX(salary) from t_emps);

SELECT dname FROM t_dept where did=(SELECT deptId from t_emps WHERE salary=(SELECT MAX(salary) from t_emps));

2. 工资在平均工资上下浮动1000以内的人和部门信息

   思路：

   1. 先在t_emps查询平均工资
   2. t_dept，t_emps进行内查询，找到人和部门信息
   3. 筛选条件当工资在平均工资上下浮动1000以内

   SELECT avg(salary) FROM t_emps;
   SELECT * from t_dept t INNER JOIN t_emps e WHERE did=deptId;
   SELECT * from t_dept t INNER JOIN t_emps e ON did=deptId WHERE salary 
   BETWEEN ((SELECT AVG(salary) from t_emps)-1000) AND ((SELECT AVG(salary) from t_emps)+1000);
   

3. 人数最多的部门是哪个部门

   SELECT * from t_dept d INNER JOIN t_emps e WHERE d.did=e.deptId;//两表内连接查询
   SELECT count(*) c,dname from t_dept d INNER JOIN t_emps e WHERE d.did=e.deptId GROUP BY d.did;//根据did分组内连接显示数量和部门名
   SELECT COUNT(*) c,deptId FROM t_emps GROUP BY deptId;//根据部门号分组显示数量
   SELECT MAX(e.c)  FROM (SELECT COUNT(*) c,deptId FROM t_emps GROUP BY deptId) e;//筛选人数最多的部门