mysql单表查询练习

心已赠人 2023-10-11 13:36 45阅读 0赞

# 单表查询 #

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

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

## 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]$";`

[format_png]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9ub3RlLnlvdWRhby5jb20veXdzL3B1YmxpYy9yZXNvdXJjZS8yNjU3MTc2ZThhZTkxOTZiMzI0NzJhMzM0ODM3YjE0MS94bWxub3RlLzUxMkU4OTg0MjYxMzQxOUFCNzVENjNENkI3QkU4MUI4LzQ1NTU0?x-oss-process=image/format,png