DQL 查询

七、起别名（用于区分重复字段）

方式一：使用as关键字

SELECT last_name AS “姓 名” FROM employees;

方式二：使用空格

SELECT USER() 用户名;
SELECT USER() “用户名”;
SELECT USER() ‘用户名’;
SELECT last_name “姓 名” FROM employees;

八、+的作用

– 需求：查询 first_name 和last_name 拼接成的全名，最终起别名为：姓 名

方案1：使用+ pass×

SELECT first_name+last_name AS “姓 名”
FROM employees;

方案2：使用concat拼接函数

SELECT CONCAT(first_name,last_name) AS “姓 名”
FROM employees;

九、distinct的使用

需求：查询员工涉及到的部门编号有哪些

SELECT DISTINCT department_id FROM employees;

条件查询：

特点：

1、按关系表达式筛选
关系运算符：> < >= <= = <>
补充：也可以使用!=,但不建议
2、按逻辑表达式筛选
逻辑运算符：and or not
补充：也可以使用&& || ! ，但不建议
3、模糊查询
like
in
between and
is null

查询工种编号不是SH_CLERK或IT_PROG的员工信息
#方式1：
SELECT *
FROM employees
WHERE job_id NOT IN(‘SH_CLERK’,‘IT_PROG’);

案例1：查询部门编号不是 50-100之间员工姓名、部门编号、邮箱
#方式1：
SELECT last_name,department_id,email
FROM employees
WHERE department_id <50 OR department_id>100;

#方式2：
SELECT last_name,department_id,email
FROM employees
WHERE NOT(department_id>=50 AND department_id<=100);

Day 2 排序查询

查询邮箱中包含 e 的员工信息，并先按邮箱的字节数降序，再按部门号升序

SELECT *
FROM employees
WHERE email LIKE ‘%e%’
ORDER BY LENGTH(email) DESC,department_id ASC;

只有limit字据放在ORDER BY后面，其他子句放其前面

分组函数：

sum(字段名)：求和 处理数值型
avg(字段名)：求平均数 处理数值型
max(字段名)：求最大值
min(字段名)：求最小值
count(字段名)：计算该字段中非空字段值的个数

和分组函数一同查询的字段要求是 group by后的字段！

分组查询（搭配分组函数使用）：

★语法（关键字顺序是固定的！！！）：

select 查询列表
from 表名
where 筛选条件
group by 分组列表
having 分组后筛选
order by 排序列表;
注意！！ 查询列表必须是分组函数和group by后出现的字段

简单的分组查询

#案例1：查询每个工种的员工平均工资
SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id;

#案例2：查询每个领导的手下人数
SELECT COUNT(*),manager_id
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id;

可以实现分组前的筛选

#案例1：查询邮箱中包含a字符的 每个部门的最高工资
SELECT MAX(salary) 最高工资,department_id
FROM employees
WHERE email LIKE ‘%a%’
GROUP BY department_id;

#案例2：查询每个领导手下有奖金的员工的平均工资
SELECT AVG(salary) 平均工资,manager_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY manager_id;

可以实现分组后的筛选

每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
SELECT job_id,MAX(salary)
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING MAX(salary)>12000;

分组后筛选的案例3：

领导编号>102的 每个领导手下的最低工资>5000的最低工资
SELECT MIN(salary) 最低工资,manager_id
FROM employees
WHERE manager_id>102
GROUP BY manager_id
HAVING MIN(salary)>5000 ;

★特点：

①查询列表往往是 分组函数和被分组的字段 ★
②分组查询中的筛选分为两类
筛选的基表 使用的关键词 位置
分组前筛选 原始表 where group by 的前面

分组后筛选 分组后的结果集 having group by的后面

where——group by ——having
group by 后可以写多个字段
问题：分组函数做条件只可能放在having后面！！！

案例4

查询没有奖金的员工的最高工资>6000的工种编号和最高工资,按最高工资升序
SELECT MAX(salary) 最高工资,job_id
FROM employees
WHERE commission_pct IS NULL
GROUP BY job_id
HAVING MAX(salary)>6000
ORDER BY MAX(salary) ASC;

★★★连接查询（多表查询）

当查询语句涉及到的字段来自于多个表时，就会用到连接查询
笛卡尔乘积现象：表1 有m行，表2有n行，结果=m*n行
发生原因：没有有效的连接条件
如何避免：添加有效的连接条件

等值连接

#一）等值连接
① 多表等值连接的结果为多表的交集部分
②n表连接，至少需要n-1个连接条件
③ 多表的顺序没有要求
④一般需要为表起别名
⑤可以搭配前面介绍的所有子句使用，比如排序、分组、筛选

查询女神名和对应的男神名
SELECT NAME,boyName
FROM boys,beauty
WHERE beauty.boyfriend_id= boys.id;

2、为表起别名
①提高语句的简洁度
②区分多个★★重名的字段
注意：如果为表起了别名，则查询的字段就不能使用原来的表名去限定

#查询员工名、工种号、工种名
SELECT e.last_name,e.job_id,j.job_title
FROM employees e,jobs j
WHERE e.job_id=j.job_id;

可以加筛选：

#案例：查询有奖金的员工名、部门名
SELECT last_name,department_name,commission_pct
FROM employees e,departments d
WHERE e.department_id=d.department_id
AND e.commission_pct IS NOT NULL;

#案例2：查询城市名中第二个字符为o的部门名和城市名
SELECT department_name,city
FROM departments d,locations l
WHERE d.location_id = l.location_id
AND city LIKE ‘_o%’;

可以加分组

案例1：查询每个城市的部门个数
SELECT COUNT(*) 个数,city
FROM departments d,locations l
WHERE d.location_id=l.location_id
GROUP BY city;

案例2：查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
SELECT department_name,d.manager_id,MIN(salary)
FROM departments d,employees e
WHERE d.department_id=e.department_id
AND commission_pct IS NOT NULL
GROUP BY department_name,d.manager_id;

可以加排序

#案例：查询每个工种的工种名和员工的个数，并且按员工个数降序
SELECT job_title,COUNT(*)
FROM employees e,jobs j
WHERE e.job_id=j.job_id
GROUP BY job_title
ORDER BY COUNT(*) DESC;

可以实现三表连接

查询员工名、部门名和所在的城市
SELECT last_name,department_name,city
FROM employees e,departments d,locations l
WHERE e.department_id=d.department_id
AND d.location_id=l.location_id
AND city LIKE ‘s%’ （以s开头的城市）
ORDER BY department_name DESC;

非等值连接

查询员工的工资和工资级别
SELECT salary,grade_level
FROM employees e,job_grades g
WHERE salary BETWEEN g.lowest_sal AND g.highest_sal

自连接

查询 员工名和上级的名称
SELECT e.employee_id,e.last_name,m.employee_id,m.last_name
FROM employees e,employees m
WHERE e.manager_id=m.employee_id;