常用的sql语句总结

假设有两张表，一张是部门表department，一张是员工表staff
部门表的number代表部门编号，name代表部门名称

员工表的name代表员工姓名，number代表所属部门编号，commission代表额外佣金，salary代表固定工资

问题如下：
1.查询出工资最低的员工姓名以及工资

SELECT `name`,`salary` FROM staff WHERE salary = (SELECT MIN(staff.salary) FROM staff)

2.查询每个员工的所属部门

SELECT staff.`name` AS `员工名称`,department.`name` AS `部门名称` FROM staff,department WHERE staff.number = department.number

3.统计每个部门的人数，显示部门名称和部门人数

SELECT department.`name` AS `部门名称` ,COUNT(*) AS `部门人数` FROM staff,department WHERE staff.number = department.number GROUP BY `部门名称`

4.分别计算各部门平均commission额外佣金，平均salary固定工资，以及平均总工资
把计算出来的平均值强制转换为DECIMAL(10,2)

SELECT department.`name` AS `部门名称`,CAST(AVG(staff.`
commission`) AS DECIMAL(10,2)) AS `平均额外佣金`,CAST(AVG(staff.salary) AS DECIMAL(10,2)) AS `平均固定工资`,CAST(AVG(staff.`
commission` + staff.salary) AS DECIMAL(10,2)) AS `平均总工资` FROM staff,department WHERE staff.number = department.number GROUP BY department.`name`

5.计算commission额外佣金大于salary固定工资50%的员工姓名

SELECT staff.`name` AS `员工名称`, staff.`
commission` AS `额外佣金`, staff.salary AS `固定工资` FROM staff WHERE staff.`
commission`/staff.salary >= 1.5 ORDER BY staff.`
commission`/staff.salary

6.左连接，左边的表是全的

SELECT staff.`name`,department.`name` FROM staff LEFT JOIN department ON staff.number = department.number

7.右连接，右边的表是全的

SELECT staff.`name`,department.`name` FROM staff RIGHT JOIN department ON staff.number = department.number

8.内连接，两表的交集

SELECT staff.`name`,department.`name` FROM staff INNER JOIN department ON staff.number = department.number

9.全连接

SELECT * FROM staff FULL JOIN department

10.查询平均总工资大于等于12000的部门（Having的用法）

SELECT department.`name` AS `部门名称`,CAST(AVG(staff.`
commission`) AS DECIMAL(10,2)) AS `平均额外佣金`,CAST(AVG(staff.salary) AS DECIMAL(10,2)) AS `平均固定工资`,CAST(AVG(staff.`
commission` + staff.salary) AS DECIMAL(10,2)) AS `平均总工资` FROM staff,department WHERE staff.number = department.number GROUP BY department.`name` HAVING CAST(AVG(staff.`
commission` + staff.salary) AS DECIMAL(10,2)) >= 12000

11.正则表达式的使用，查询某姓的员工信息

SELECT * FROM staff WHERE `name` REGEXP '李'

12.字符串连接

SELECT CONCAT(`name`,'-----',salary) AS '员工固定工资' FROM staff

13.降序DESC

SELECT CONCAT(`name`,'-----',salary) AS '员工固定工资' FROM staff ORDER BY salary DESC

14.升序ASC

SELECT CONCAT(`name`,'-----',salary) AS '员工固定工资' FROM staff ORDER BY salary ASC

15.左连接查询出来的值为null，将null设为未知部门

SELECT staff.`name`,IFNULL(department.`name`,'未知部门') FROM staff LEFT JOIN department ON staff.number = department.number

16.ISNULL函数返回值为0,1