mysql 复杂查询

聚合函数

函数是一个功能体，提供若干数据，产出结果-饺子机

COUNT(...)数量

SUM(...) 求和

AVG(...)平均

MAX (...)最大

MIN(...) 最小

聚合查询/分组查询

查询出所有员工的数量

SELECT COUNT(eid) FROM emp;

SELECT COUNT(*) FROM emp; #推荐写法；

查询出工资8000以上女员工姓名性别生日按工资降序排列，取前三个人

SELECT ename,sex,birthday,salary FROM emp WHERE salary >8000 AND sex=0

ORDER BY salary DESC

LIMIT 0,3;

查询所有员工的工资总和

SELECT SUM(salary) FROM emp;

查询所有员工的平均工资

SELECT SUM(salary)/COUNT(*) FROM emp;

SELECT AVG(salary) FROM emp;

查询男员工工资最高的

SELECT ename,MAX(salary) FROM emp WHERE sex=1;

查询出年龄最大的员工

SELECT MIN(birthday),ename FROM emp;

分组查询：只能查询分组条件和聚合函数

*查询男女员工的平均工资，最高工资

SELECT sex,AVG(salary),MAX(salary) FROM emp GROUP BY sex;

查询每个部门的员工数量，最高工资，最低工资；

SELECT deptId,COUNT(*),MAX(salary),MIN(salary) FROM emp GROUP BY deptId ;

函数补充

YEAR() 获取日期中的年份

MONTH() 获取日期中的月份

查询出1993年出生的员工所有列

SELECT*FROM emp WHERE YEAR(birthday)=1993;

查询5月出生的所有列

SELECT*FROM emp WHERE MONTH(birthday)=5;

子查询

查询出研发部员工所有的列

SELECT * FROM emp WHERE deptId=(SELECT did FROM dept WHERE dname='研发部');

查询出比tom工资高的员工有哪些；

SELECT * FROM emp WHERE salary>(SELECT salary FROM emp WHERE ename='tom');

查询出和TOM同一年的员工

SELECT*FROM emp WHERE YEAR(birthday)=(SELECT YEAR(birthday) FROM emp WHERE ename='tom' );

多表查询

查询所有员工姓名及其部门名称；

SELECT ename,dname FROM emp,dept WHERE did=deptId;

上述多表查询语法是SQL-92中的，无法查询出没有部门的员工，也没法查出没有员工的部门。

SQL-99中提出了新的多表查询语法

内连接 INNER JOIN ...ON

SELECT ename,dname FROM emp INNER JOIN dept ON deptId=did;

左外连接 LEFT OUTER JOIN ..ON

SELECT ename,dname FROM emp LEFT OUTER JOIN dept ON deptId= did;

查询结果是左侧所有的记录都显示；

OUTER 可以省略

SELECT ename,dname FROM emp LEFT JOIN dept ON deptId =did;

右外连接 RIGHT OUTER JOIN...ON

SELECT ename,dname FROM emp RIGHT OUTER JOIN dept ON deptId=did;

查询右侧所有的记录都显示；

全连接 FULL JOIN

显示左侧和右侧所有的记录--mysql 不支持

UNION 合并相同的项

UNION ALL 不合并相同的项

(select ename,dname from emp left join dept on deptid=did)

UNION ALL

(SELECT ename,dname FROM emp RIGHT OUTER JOIN dept ON deptId=did);

这个出来的有重复项，是左外连接和右外连接合并的