MySQL基础-03 DQL的学习

MySQL基础-03 DQL的学习

1 基础查询

语法：
select 查询列表
from 表名；

查询列表可以是：表中的字段、常量值、表达式、函数
查询的结果是一个虚拟的表格

例如：

1. 查询表中的单个字段

select last_name from employees;

2. 查询表中的多个字段

select last_name,salary,email from employees;

3. 查询表中所有的字段

select * fromvemployees;

4. 查询常量值

select 100;
select 'john';

5. 查询表达式

select 100%98;

6. 查询函数

select version();

7. 起别名
   方式一

select 100%98 as 结果；
select last_name as 姓,first_name as 名 from employees;

方式二

select last_name  姓,first_name  名 from employees;

select salary as "out put" from employees;

8. 去重
   查询员工表中涉及到的所有的部门编号

select distinct department_id from employees;

9. +号的作用 (运算符的作用)
   select 100+90; 两个操作数都为数值型，则作加法运算
   select ‘123’+90; 其中一方为字符型，试图将字符型数值转换成数值型，如果转换成功，则继续做加法运算;如果转换失败则将字符型数值转换成0；
   select ‘john’+90; 0+90
   select null+10; 只要其中一方为null,则结果肯定为null；
   例如：
   查询员工名和姓连接成一个字段，并显示为姓名
   select last_name+first_name as 姓名 from employees;（错误）
   select concat(last_name,first_name) as 姓名 from employees;

2 条件查询

语法
select 查询列表
from 表名
where 查询条件 ;

根据查询条件来分类：

• 条件运算符：>、 <、 = 、!= 、 <>、 >=、 <=
• 按逻辑表达式：&&、 || 、！ and、 or 、not
• 模糊查询： like 、 between and、 in、 is null

2.1 按条件表达式筛选

条件运算符：>、 <、 = 、!= 、 <>、 >=、 <=

1. 查询工资>12000的员工信息

select * from employees
where salary>12000;

2. 查询部门编号不等于90的员工名和部门编号
   不等于（!= 或 <>）

select last_name,department_id
from employees
where department_id<>90;

2.2 按逻辑表达式筛选

按逻辑表达式：&&、 || 、！ and、 or 、not

1. 查询工资在10000到20000之间的员工名、工资及奖金

select last_name,salary,commisson_pct
from employees
where salary=10000 and salary=20000;

2. 查询部门编号不是在90到110之间或者工资高于15000的员工信息

select *
from employees
where department_id<90 and department_id>110 or salary>15000;

2.3 模糊查询

模糊查询： like 、 between and、 in、 is null
like 一般和通配符搭配使用
通配符
% 任意多个字符包含0个字符
_ 任意单个字符

1. 查询员工名中包含字符a的员工信息

select *
from employees
where last_name like '%a%';

2. 查询员工名中第三个字符为e，第五个字符为a的员工的员工名和工资

select last_name,salary
from employees
where last_name like '__e_a%';

3. 查询员工名中第二个字符为_的员工名

select last_name,salary
from employees
where last_name like  ‘_\_%’;

select last_name,salary
from employees
where last_name like  ‘_ $_%’  ESCAPE  '$';

between and

• 使用between and 可以提高语句的简洁度
• 包含临界值 <=> []

4. 查询员工编号在100到120之间的员工信息

select *
from employees
where employee_id>=100 and employee_Id<=120;

select *
from employees
where employee_id between 100 and 120;

in
含义：用于判断字段的值是否属于in列表中的某一项
特点：
- 使用 in 比 or 简洁
- in 列表的值类型必须一致或兼容
-

5. 查询员工的编号是 IT_PROG、AD_VP、AD_PRES中的一个员工名和工种编号

select lase_name,job_name
from employees
where job_id='IT_PROG' or job_id='AD_VP' or job_id='AD_PRES'; 

select lase_name,job_name
from employees
where job_id in('IT_PROG' ,'AD_VP','AD_PRES'); 

is null / is not null
6. 查询没有奖金的员工和奖金率

select last_name,commission_pct
from employees
where commission_pct is null;

安全等于 <=>

select last_name,commission_pct
from employees
where commission_pct <=> null;

7. 查询工资为12000的员工信息

select last_name,salary 
from employees
where salary <=> 12000;

3 排序查询

语法
select 查询列表
from 表
[where 筛选条件]
order by 排序列表 [asc | desc]

特点：

• asc 升序 desc 降序
• 默认 asc
• order by 子句中可以支持单个字段、多个字段、表达式、函数、别名
• order by 子句一般是放在查询语句的最后面，但limit 子句除外

1. 查询员工信息，要求工资从高到低排序

select * from employees order by salary desc;

2. 查询部门标号>=90 的员工信息，按入职时间的先后进行排序

select * 
from employees
where department_id>=90
order by hiredate asc;

3. 按年薪的高低显示员工的信息和年薪[按表达式排序]

select *,salary*12(1+ifnull(commison_pct,0))
from employees
order by salary*12(1+ifnull(commison_pct,0)) desc;

4. 按年薪的高低显示员工的信息和年薪[按别名排序]

select *,salary*12(1+ifnull(commison_pct,0)) 年薪
from employees
order by 年薪 desc;

5. 按姓名的长度显示员工的姓名和工资【按函数排序】

select length(last_name) 字节长度,last_name,salary
from employees
order by length(last_name) desc;

6. 查询员工信息，要求先按工资排序，再按员工编号排序

select *
from employees
order by salary asc,employee_Id desc;

4 分组查询

引入
查询每个部门的平均工资

可以使用 group by 子句将表中的数据分成若干组
语法：
select column,group_function(column)
from table
[where condition]
[group by group_by_expression]
[order by column];

查询列表 要求：分组函数和group by 后出现的字段

1. 查询每个工种的最高工资

SELECT MAX(salary),job_id
FROM employees
GROUP BY job_id;

2. 查询每个位置上的部门个数

SELECT COUNT(*),`location_id`
FROM `departments`
GROUP BY `location_id`;

3. 查询邮箱中包含a字符的，每个部门的平均工资

SELECT AVG(salary),`department_id`
FROM `employees`
WHERE email LIKE '%a%'
GROUP BY `department_id`;

4. 查询有奖金的每个领导手下员工的最高工资

SELECT MAX(salary),`manager_id`
FROM `employees`
WHERE `commission_pct` IS NOT NULL
GROUP BY manager_id;

添加分组后筛选
5. 查询哪个部门员工的个数>2

SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*)>2;

6. 查询每个工种有奖金的员工的最高工资>12000 的工种编号和最高工资

SELECT MAX(salary),job_id
FROM employees
WHERE `commission_pct` IS NOT NULL
GROUP BY job_id
HAVING MAX(salary)>12000;

7. 查询领导编号>102 的每个领导手下的最低工资>5000的领导编号以及最低工资

SELECT MIN(salary),manager_id
FROM employees
WHERE manager_id>102
GROUP BY manager_id
HAVING   MIN(salary)>5000;

按表达式或函数分组
8. 按员工姓名的长度分组，查询每一组员工个数，筛选员工个数>5的有哪些？

SELECT COUNT(*),LENGTH(last_name) len_name
FROM employees
GROUP BY len_name
HAVING COUNT(*)>5;

按多个字段分组
9. 查询每个部门每个工种的员工的平均工资

SELECT AVG(salary),`department_id`,`job_id`
FROM `employees`
GROUP BY `department_id`,`job_id`;

添加排序

SELECT AVG(salary),`department_id`,`job_id`
FROM `employees`
where department_id is not null
GROUP BY `department_id`,`job_id`
having  AVG(salary)>10000
order by  AVG(salary) desc;

小结：

• 分组查询中的筛选条件分为两类：

	数据源	位置	关键字
分组前筛选	原始表	group by 子句的前面	where
分组后筛选	分组后的结果集	group by 子句的后面	having

分组函数做条件肯定是放在having 子句中
能用分组前筛选的，就优先考虑使用分组筛选

• group by 子句支持单个字段分组，多个字段分组（多个字段之间用逗号隔开，没有顺序要求），表达式或函数（用的较少）
• 也可以添加排序（排序放在整个分组查询的最后）