缩写全称和对应 SQL： 现在有这样一个公司部门人员各个信息的数据库，包含了如下几个表： departments 部门表字段：
dept_emp 雇员部门表字段：
dept_manager领导部门表字段：
employees雇员表字段：
salaries薪资表字段：
titles岗位表字段：
- 基本查询-查询departments表的所有数据
-
```
select * from departments;
```
- 字段查询-查询employees表里所有的雇佣日期hire_date
-
```
select hire_date from employees;
```
- 条件查询-查询employees表里所有男性员工M
-
```
select * from employees where gender='M';
```
- 排序-查询departments表里的所有部门并按部门序号进行从小到大排序展示
```
select * from departments order by dept_no;
```
若是想要按部门序号从大到小进行排序的话就可以使用DESC：
```
select * from departments order by dept_no desc;
```
- 分页-将departments表按部门序号进行从小到大排序后取前4个
```
select * from departments order by dept_no limit 4;
```
再取偏移量offset为3后的前4个
```
select * from departments order by dept_no limit 4 offset 3;
```
去重-现在想知道titles表中的岗位头衔有多少种，就需要对title进行去重处理
```
select distinct title from titles;
```
基本条件查询在上述已经说明：
```
select * from table_name where a=1
```
其余条件查询SQL：
实操演示：
- LIKE通配-现在要取出employees里所有名字为C开头的人
```
select * from employees where first_name like 'C%';
```
再取employees里所有名字为C开头，第3个字母为y的人
```
select * from employees where first_name like 'C_y%';
```
- BETWEEN AND-查询employees中字母顺序显示名字在“Anneke”（包括）和“Chirstian”（包括）的人
```
select * from employees where first_name between 'Anneke' and 'Chirstian';
```
- IN-现在，要从employees表中选取姓氏为 ‘Simmel’和’Peir’ 的人
```
select * from employees where last_name in ('Simmel','Peir');
```
GROUP BY、SUM-现取salaries表中各个员工emp_no的薪资总和
```
select emp_no,sum(salary) from salaries group by emp_no;
```
- HAVING-现在接着上一步，取员工总薪资大于1000000的员工
```
select emp_no,sum(salary) from salaries group by emp_no having sum(salary)>1000000;
```
- COUNT、AVG-取salaries表中薪资排名前100名的平均薪资(需要利用子查询)
```
select avg(salary) from (select salary from salaries order by salary desc limit 100) as s;
```
SQLW3C: https://www.w3school.com.cn/sql/sql_having.asp
[原文链接](https://mp.weixin.qq.com/s?__biz=MzU3NDM4ODEzMg==&mid=2247487075&idx=1&sn=925c89eda41e8f5489f3d2bf8c2fe36b&chksm=fd326ca8ca45e5be113a1bf175cc7333d4604cb064a790c675eb14f3e80b7870795970c2143e#rd)
[更多技术文章](https://qrcode.ceba.ceshiren.com/link?name=article&project_id=qrcode&from=juejin×tamp=1663233229&author=ML
