mysql单表查询练习

单表查询

select distinct 字段 from 表 where 条件 group by 字段 having 条件 order by 字段 limit 起始位置,显示个数；

执行顺序 from -> where -> group by -> having -> select -> distinct -> order by -> limit
表：

SELECT使用

CONCAT()用于拼接字符串
DISTINCT去重

1.查出所有员工的名字，薪资,格式为<名字:egon> <薪资:3000>

SELECT CONCAT("<名字：",name,"> <薪资：",salary,">") FROM employee;

2.查出所有的岗位（去掉重复）

SELECT DISTINCT post FROM employee;

3.查出所有员工名字，以及他们的年薪,年薪的字段名为annual_year

SELECT name,salary * 12 as annual_year FROM employee;

WHERE使用

1. 算数运算符 > < >= <= == !=
2. 逻辑运算符 and or not
3. between 80 and 100 在80和100之间
4. in (1,10,90) 是1或10或30
5. like “xiao%”
   like “xiao_”
   %可以匹配任意多个字符，_可以匹配任意一个字符

1. 查看岗位是teacher的员工姓名、年龄

SELECT name,age FROM employee where post="teacher";

2. 查看岗位是teacher且年龄大于30岁的员工姓名、年龄

SELECT name,age FROM employee WHERE post="teacher" AND age > 30;

3. 查看岗位是teacher且薪资在9000-10000范围内的员工姓名、年龄、薪资

SELECT name,age,salary FROM employee WHERE post="teacher" AND salary BETWEEN 9000 AND 10000;

4. 查看岗位描述不为NULL的员工信息

SELECT * FROM employee WHERE post_comment IS NOT NULL;

5. 查看岗位是teacher且薪资是10000或9000或30000的员工姓名、年龄、薪资

SELECT name,age,salary FROM employee WHERE post="teacher" AND salary IN (10000,9000,30000);

6. 查看岗位是teacher且薪资不是10000或9000或30000的员工姓名、年龄、薪资

SELECT name,age,salary FROM employee WHERE post="teacher" AND salary NOT IN (10000,9000,30000);

7. 查看岗位是teacher且名字是jin开头的员工姓名、年薪

SELECT name,salary FROM employee WHERE post="teacher" AND name LIKE "jin%";

GROUP BY使用

将字段分组。使用后，select字段只能用分组字段或者聚合函数
聚合函数：

• MAX()获取每组该字段的最大值
• MIN()获取每组该字段的最小值
• AVG()获取每组该字段的平均值
• SUM()获取每组该字段的总和，求总和
• COUNT()获取每组该字段的总数，统计个数用
• GROUP_CONCAT(";",name)每组所有记录中该字段拼接，第一个参数为分隔符

1. 查询岗位名以及岗位包含的所有员工名字

SELECT post,GROUP_CONCAT(";",name) FROM employee GROUP BY post;

2. 查询岗位名以及各岗位内包含的员工个数

SELECT post,COUNT(id) FROM employee GROUP BY post;

3. 查询公司内男员工和女员工的个数

SELECT sex,COUNT(id) FROM employee GROUP BY sex;

4. 查询岗位名以及各岗位的平均薪资

SELECT post,AVG(salary) FROM employee GROUP BY post;

5. 查询岗位名以及各岗位的最高薪资

SELECT post,MAX(salary) FROM employee GROUP BY post;

6. 查询岗位名以及各岗位的最低薪资

SELECT post,MIN(salary) FROM employee GROUP BY post;

7. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资

SELECT sex,AVG(salary) FROM employee GROUP BY sex;

HAVING过滤

having过滤只能在group by分组之后使用
having过滤条件只能使用过滤字段和聚合函数（注意where过滤条件不能只用聚合函数）

1. 查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数

SELECT post,GROUP_CONCAT(name),COUNT(ID) FROM employee GROUP BY post HAVING COUNT(ID) < 2;

2. 查询各岗位平均薪资大于10000的岗位名、平均工资

SELECT post,AVG(salary) FROM employee GROUP BY post HAVING AVG(salary) > 10000;

3. 查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资

SELECT post,AVG(salary) FROM employee GROUP BY post HAVING AVG(salary) BETWEEN 10000 AND 20000;

ORDER BY

排序，默认升序，ASC升序，DESC降序

1. 查询所有员工信息，先按照age升序排序，如果age相同则按照hire_date降序排序

SELECT * FROM employee ORDER BY age ASC,hire_date DESC;

2. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资升序排列

SELECT post,AVG(salary) FROM employee GROUP BY post HAVING AVG(salary) > 10000 ORDER BY AVG(salary) ASC;

3. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资降序排列

SELECT post,AVG(salary) FROM employee GROUP BY post HAVING AVG(salary) > 10000 ORDER BY AVG(salary) DESC;

LIMIT

限制查询的记录条数，limit 起始位置,显示个数
SELECT * FROM employee LIMIT 0,5; 显示前五条
SELECT * FROM employee LIMIT 5,5; 从第六条开始，显示五条

REGEXP

正则表达式
查看所有员工中名字是jin开头，n或者g结果的员工信息
SELECT * FROM employee WHERE name REGEXP "^jin.*[ng]$";