[大数据]MySQL学习笔记（三）— 查询篇（DQL语言）

1. 基础查询

"""1. 查询表中的单个字段"""
USE myemployees;
SELECT last_name FROM employees;
"""2. 查询表中的多个字段"""
SELECT last_name, salary, email FROM employees;
"""3. 查询表中的所有字段"""
SELECT * FROM employees;
# 选中区域+F12：格式化命令
# 选中区域+F9：执行命令
"""4. 查询常量值"""
SELECT 100;
SELECT 'john';
"""5. 查询表达式"""
SELECT 100%98;
"""6. 查询函数"""
SELECT VERSION();
"""7. 为字段起别名"""
# 方式一
SELECT 100%98 AS RESULT;
SELECT last_name AS 姓, first_name AS 名 FROM employees;
# 方式二
SELECT last_name 姓, first_name 名 FROM employees;
SELECT salary AS "output" FROM employees;
"""8. 去重"""
SELECT DISTINCT department_id FROM employees;
"""9. +号的作用：运算符，不能用于拼接，强转字符进行算术运算，若转换失败则转换成0"""
SELECT 
	last_name+first_name AS 姓名
FROM
	employees; ## 0
"""10. 拼接"""
SELECT CONCAT(`a`, `b`, `c`) AS RESULTS
"""11. 案例"""
SELECT 
	IFNULL(字段名, new_value) AS new_name,
	comission_pct
FROM
	employees;
SELECT 
	CONCAT(`first_name`, ',', `last_name`, ',', `job_id`, ',', IFNULL(comission_pct, 0)) AS OUTPUT
FROM
	employees;

2. 条件查询

'''一. 按条件表达式筛选，条件运算符：> < = <> != >= <='''
# 1. 工资>12000的员工信息
SELECT * FROM employees WHERE salary>12000;
# 2. 部门编号！=90号的员工名和部门编号
SELECT 
	last_name, department_id 
FROM 
	employees 
WHERE 
	department_id!=90;
'''二. 按逻辑表达式筛选，条件运算符：&& || ! and or not'''
# 1. 工资在10000到20000之间的员工名、工资以及奖金
SELECT last_name, salary, commision_pct FROM employees WHERE salary>=10000 AND salary<=20000;
# 2. 查询部门编号不是在90到110之间，或者工资高于15000的员工信息
SELECT * FROM employees WHERE department_id<90 OR department_id>110 OR salary>15000;
SELECT * FROM employees WHERE NOT(department_id>=90 AND department_id<=110) OR salary>15000;
'''三. 模糊查询：like between...and in is...null'''
'''like'''
# 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 FROM employees WHERE last_name LIKE '_\_%';
# 自定义转义符号
SELECT last_name FROM employees WHERE last_name LIKE '_$_%' ESCAPE '$
';
'''between and'''
# 1. 查询员工编号在100到120之间的员工信息(包含临界值)
SELECT * FROM employees WHERE employee_id BETWEEN 100 AND 120;
'''in'''
# 1. 查询员工工种编号是IT_PROG、AD_VP、AD_PRES中的一个的员工名和工种编号
SELECT last_name, job_id FROM employees WHERE job_id in ('IT_PROG', 'AD_VP', 'AD_PRES');
'''is null'''
# 1. 查询没有奖金的员工名和奖金率
SELECT last_name, comission_pct FROM employees WHERE comission_pct IS NULL;
'''安全等于<=>'''
SELECT last_name, comission_pct FROM employees WHERE comission_pct <=> NULL;
SELECT last_name, salary FROM employees WHERE salary <=> 12000;
# 案例：查询员工号为176的员工姓名和部门号和年薪
SELECT last_name, department_id, salary*12*(1+IFNULL(comission_pct, 0)) AS 年薪 FROM employees;

3. 排序查询

# 查询员工信息，要求工资从高到低
# asc升序（默认），DESC降序
SELECT * FROM employees ORDER BY salary DESC;
# 查询部门编号>=90的员工信息，按入职时间先后进行排序
SELECT * FROM employees WHERE department_id>=90 ORDER BY hiredate DESC;
# 按表达式排序：按年薪高低显示员工的信息和年薪
SELECT *, salary*12*(1+IFNULL(commision_pct, 0)) 年薪 FROM employees ORDER BY salary*12*(1+IFNULL(commision_pct, 0)) DESC;
SELECT *, salary*12*(1+IFNULL(commision_pct, 0)) 年薪 FROM employees ORDER BY 年薪 DESC;
# 按姓名长度显示员工姓名和工资
SELECT LENGTH(last_name) 字节长度, last_name, salary FROM employees ORDER BY LENGTH(last_name) DESC;
# 先按工资排序，再按编号排序
SELECT * FROM employees ORDER BY salary ASC, employee_id DESC; 

4. 常见函数

# select func(args) [from table];
'''1. 单行函数'''
# 1. 字符函数
# length (一个字母一个字节，一个中文3个字节,utf-8)
SELECT LENGTH('john');
# concat
SELECT CONCAT(last_name, '_', first_name) full_name FROM employees;
# upper, lower
SELECT UPPER('john');
SELECT CONCAT(UPPER(last_name), '_', LOWER(first_name)) full_name FROM employees;
# substr, substring(str, pos, len)
"""索引位置从1开始"""
SELECT SUBSTR('李莫愁爱上了陆展元', 6) LOVER;
SELECT SUBSTR('李莫愁爱上了陆展元', 1, 3) out_put;
# instr 返回子串第一次出现的索引，如果找不到返回0
SELECT INSTER('杨不悔爱上了殷六侠', '殷六侠') AS out_put;
# trim 去掉前后的空格或字符
SELECT TRIM('   张翠山。  ') AS out_put;
SELECT TRIM('a' FROM 'aaaa张翠山aaaa') AS out_put;
# lpad/rpad 指定字符实现左/右填充
SELECT LPAD('殷素素', 10, '*') AS out_put;
SELECT RPAD('殷素素', 12, 'ab') AS out_put;
# replace 全部替换
SELECT REPLACE('张无忌周芷若爱上了周芷若', '周芷若', '赵敏') AS out_put;
# 2. 数学函数
# round 四舍五入
SELECT ROUND(1.65); //2
SELECT ROUND(1.567, 2);  //1.57
# ceil 向上取整, 返回>=该参数的最小整数
SELECT CEIL(1.002); // 1
# floor 向下取整，返回<=该参数的最大整数
SELECT FLOOR(-9.99); // 9
# truncate 截断
SELECT TRUNCATE(1.6999, 1); //1
# mod 取余
SELECT MOD(10, 3); //1
# 3. 日期函数
# now 返回当前系统日期+时间
SELECT NOW();
# curdate 返回当前日期，不含时间
SELECT CURDATE();
# curtime 返回当前时间，不含日期
SELECT CURTIME();
# 获取指定部分，年、月、日、小时、分钟、秒
SELECT YEAR(NOW()) 年;
SELECT YEAR('1991-1-1') 年;
SELECT YEAR(hirdate) 年 FROM employees;
SELECT MONTH(NOW()) 月;
SELECT MONTHNAME(NOW()) 月;
# str_to_date 将合法日期字符转成日期，字符对应关系如图
SELECT str_to_date('1998-3-2', '%Y-%c-%d') AS output;
SELECT * FROM employees WHERE hirdate=STR_TO_DATE('4-3 1992', '%c-%d %Y');
# date_format 将日期格式转换成字符
SELECT DATE_FORMAT(NOW(), '%y年%m月%d日') AS output;
SELECT last_name, DATE_FORMAT(hirdate, '%m月/%d日 %y年') 入职日期 FROM employees WHERE commision_pct IS NOT NULL;

SELECT VERSION(); //版本查看
SELECT DATABASE(); //查看数据库
SELECT USER(); //查看用户
# 4. 流程控制函数
# if: if else 效果
SELECT IF(10>5, '大', '小')；
SELECT last_name, comission_pct, IF(comission_pct IS NULL, '没奖金呵呵', '有奖金嘻嘻') 备注 FROM employees;
# case函数
# switch case
SELECT salary 原始工资, department_id, 
CASE department_id
WHEN 30 THEN salary*1.1
WHEN 40 THEN salary*1.2
WHEN 50 Then salary*1.3
ELSE salary
END AS 新工资
FROM employees;
# 多重if
SELECT salary,
CASE
WHEN salary>20000 THEN 'A'
WHEN salary>15000 THEN 'B'
WHEN salary>10000 THEN 'C'
ELSE 'D'
END AS 工资级别
FROM employees;
'''2. 分组函数/统计函数'''
# sum求和 avg （数值型）/ max min （数值、字符、日期型）/ count （支持所有类型）
# 均忽略null值运算
# 可以和distinct搭配
SELECT SUM(salary) FROM employees;
SELECT AVG(salary) FROM employees;
SELECT MAX(salary) FROM employees;
SELECT MIN(salary) FROM employees;
SELECT COUNT(salary) FROM employees;
SELECT SUM(salary) 和, AVG(salary) 均值, MAX(salary) 最大, MIN(salary) 最小, COUNT(salary) 个数 FROM employees;
SELECT SUM(DISTINCT salary), SUM(salary) FROM employees;
SELECT COUNT(DISTINCT salary), COUNT(salary) FROM employees;
# count函数
# 统计行数
SELECT COUNT(*) FROM employees;
SELECT COUNT(1) FROM employees;
# 和分组函数一同查询的西段有限制
SELECT AVG(salary), employee_id FROM employees; // 显示一行，意义不大

5. 分组查询

/*
	select 分组函数，列（要求出现在group by的后面）
	from 表
	【where 筛选条件】
	group by 分组的列表
	【order by 子句】
*/
# 案例一： 查询每个工种的最高工资
SELECT MAX(salary), job_id FROM employees GROUP BY job_id;
# 案例二：查询每个位置上的部门个数
SELECT COUNT(*), location_id FROM departments GROUP BY location_id;
# 案例三：查询邮箱中包含a字符的，每个部门的平均工资
SELECT AVG(salary), department_id FROM employees WHERE email LIKE '%a%' GROUP BY department_id;
# 案例四：查询有奖金的每个领导手下员工的最高工资
SELECT MAX(salary), manager_id FORM employees WHERE comission_pct IS NOT NULL GROUP BY manager_id;
# 案例五：查询哪个部门的员工个数>2，分组后的筛选
SELECT COUNT(*) num, department_id FROM employees GROUP BY department_id HAVING COUNT(*)>2;
# 案例六：查询每个工种有奖金的员工最高工资>12000的工种编号和最高工资
SELECT MAX(salary), job_id 
FROM employees
WHERE commission_pct IS NOT NULL 
GROUP BY job_id 
HAVING MAX(salary)>12000;
# 案例七：查询领导编号>102的每个领导手下的最低工资>5000的领导编号，以及其最低工资
SELECT MIN(salary), manager_id
FROM employees
WHERE manager_id>102
GROUP BY manager_id
HAVING MIN(salary)>5000;
/*
分组前筛选（优先考虑）：数据源为原始表，放在GORUP BY语句前，关键字为WHERE
分组后筛选：数据源为分组后的结果集合，放在GROUP BY语句后，关键字为HAVING
*/
# 案例：按员工姓名的长度分组，查询每一组员工个数，筛选员工个数>5的有哪些
SELECT COUNT(*), LENGTH(last_name) len_name
FROM employees
GROUP BY LENGTH(last_name)
HAVING COUNT(*)>5;
# 按多个字段分组
# 查询每个部门每个工种员工的平均工资
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
ORDER BY AVG(salary) DESC;

6. 连接查询

/*
多表查询
*/
SELECT * FROM beauty;
SELECT * FROM boys;
# 笛卡尔乘积错误现象，没有有效的连接条件
SELECT NAME, boyName FROM beauty, boys;
/*
连接查询分类：
	按年代分类：sql92标准，sql99标准【推荐】
	按功能分类：
		内连接：
			等值连接
			非等值连接
			自连接
		外连接
			左外连接
			右外连接
			全外连接
		交叉连接
*/
# 一、sql92标准
# 1、等值连接
# 案例一：查询女神名和对应的男神名
SELECT NAME, boyName
FROM beauty, boys
WHERE beauty.boyfriend_id=boys.id;
# 案例二：查询员工名和对应的部门名
SELECT last_name, department_name
FROM employees, departments
WHERE employees.`department_id`=departments.`department_id`;
# 查询工种名、员工名、工种号
SELECT E.last_name, E.job_id, J.job_title
FROM employees AS E, jobs J
WHERE E.`job_id`=J.`job_id`;
# 查询有奖金的员工名、部门名
SELECT last_name, department_name
FROM employees e, departments d
WHERE e.`department_id`=d.`department_id`
AND e.`commision_pct` IS NOT NULL;
# 查询城市名中第二个字符为o的部门名和城市名
SELECT department_name, city
FROM departments a, locations l
WHERE d.`location_id`=l.`location_id`
AND city LIKE '_o%';
# 查询每个城市的部门个数
SELECT COUNT(*) 个数, city
FROM departments d, locations l
WHERE d.`location_id`=l.`location_id`
GROUP BY city
# 查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
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`;

## 2. 非等值连接
# 查询员工工资和工资级别
SELECT salary, grade_level
FROM employees e, job_grades g
WHERE e.salary BETWEEN g,`lowest_salary` AND g.`highest_salary`;

## 3. 自连接
# 查询员工名和上级的名称
SELECT .last_name, e.employee_id, m.employee_id, m.last_name
FROM employees e, employees m
WHERE e.`manager_id`=m.`employee_id`

## 二、sql99语法
/*
语法：
	select 查询列表
	from 表一 别名
	join 表二 别名 【连接类型】
	on 连接条件
	【where 筛选条件】
	【group by 分组】
	【having 筛选条件】
	【order by 排序列表】
*/

# 内连接
/*
select 查询列表
from 表1 别名
inner join 表2 别名
on 连接条件
*/

# 等值连接
# 查询员工名、部门名
SELECT last_name, department_name
FROM employees e
INNER JOIN departments d
ON e.`deparetment_id`=d.`department_id`;
# 查询名字中包含e的员工名和工种名（添加筛选条件）
SELECT last_name, job_title
FROM employees e
INNER JOIN jobs j
ON e.`job_id`=d.`job_id`
WHERE e.`last_name` LIKE '%e%';
# 查询部门个数>3的城市名和部门个数 （添加分组）
SELECT city, COUNT(*) 部门个数
FROM departments d
INNER JOIN locations l
ON d.`location_id`=l.`location_id`
GROUP BY city
HAVING COUNT(*)>3;
# 查询哪个部门的员工个数>3的部门名和员工个数，并按个数降序（添加排序）
SELECT COUNT(*), department_id
FROM employees e
INNER JOIN departments d
ON e.`deparetment_id`=d.`department_id`
GROUP BY department_name
HAVING COUNT(*)>3
ORDER BY COUNT(*) DESC;
# 查询员工名、部门名、工种名，并按部门名降序（三表连接）
SELECT last_name, department_name, job_title
FROM employees e
INNER JOIN department_d ON e.`deparetment_id`=d.`department_id`
INNER JOIN jobs j ON e.`job_id`=d.`job_id`
ORDER BY department_name DESC;

# 非等值连接
# 查询员工工资级别
SELECT salary, grade_level
FROM employees e
JOIN job_grades g
ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`;
# 查询每个工资级别个数>2个数，并按工资级别降序
SELECT COUNT(*), grade_level
FROM employees e
JOIN job_grades g
ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`;
HAVING COUNT(*)>20
GROUP BY grade_level DESC;

# 自连接
# 查询员工名字、上级名字
SELECT e.last_name, m.last_name
FROM employees e
INNER JOIN employees m
ON e.`manager_id`=m.`employee_id`;

# 外连接
/*
查询一个表中有，另一个表中没有的记录
*/
# 左外连接
# 查询男朋友不在男神表的女神名
SELECT b.name, bo.
FROM beauty b
LEFT OUTER JOIN boys bo
ON b.`boyfriend_id`=bo.`id`
WHERE bo.`id` IS NULL;
# 查询哪个部门没有员工
# 左外
SELECT d.*, e.employee_id
FROM departments d
LEFT OUTER JOIN employees e
ON d.`deparetment_id`=e.`department_id`
WHERE e.`employee_id` IS NULL;
# 右外
SELECT d.*, e.employee_id
FROM employees e
RIGHT OUTER JOIN departments d
ON d.`deparetment_id`=e.`department_id`
WHERE e.`employee_id` IS NULL;

# 全外连接
USE girls;
SELECT b.*, bo.*
FROM beauty b
FULL OUTER JOIN boys bo
ON b.`boyfriend_id`=bo.`id`;

# 交叉连接
SELECT b.*, bo.*
FROM beauty b
CROSS JOIN boys bo;
# 总结如图

# 查询编号>3的女神的男朋友信息，如果有、则列出详细，若没有用null填充
SELECT b.id, b.name, bo.*
FROM beauty b
LEFT OUTER JOIN boys bo
ON b.`boyfriend_id`=bo.`id`
WHERE b.`id`>3
# 查询哪个城市没有部门
SELECT city, d.*
FROM departments d
RIGHT OUTER JOIN locations l
ON d.`location_id`=l.`location_id`
WHERE d.`department_id` IS NULL;
# 查询部门名为SAL或IT的员工信息
SELECT e.*, d.department_name 
FROM departments d
LEFT JOIN employees e
ON d.`deparetment_id`=e.`department_id`
WHERE d.`deparetment_name` IN('SAL', 'IT');

7. 子查询

/*
出现在其他语句中的select为子查询或内查询
*/
# 一、where或having后面
# 1. 标量子查询（单行子查询）
# 谁的工资比Abel高
SELECT *
FROM employees
WHERE salary>(
	SELECT salary
	FROM employees
	WHERE last_name='Abel'
);
# 返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资
SELECT last_name, job_id, salary
FROM employees
WHERE job_id=(
	SELECT salary
	FROM employees
	WHERE employee_id=141
) AND salary>(
	SELECT salary
	FROM employees
	WHERE employee_id=143
)
# 返回工资最少的员工的last_name, job_id, salary
SELECT last_name, job_id, salary
FROM employees
WHERE salary=(
	SELECT MIN(salary)
	FROM employees
);
# 查询最低工资大于50号部门最低工资的部门和id及其最低工资
SELECT MIN(salary), department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
	SELECT MIN(salary)
	FROM employees
	WHERE department_id=50
);
# 2. 列子查询（多行子查询）
# 返回location_id是1400或1700的部门中的所有员工姓名
SELECT last_name
FROM employees
WHERE department_id IN(
	SELECT department_id
	FROM departments
	WHERE location_id IN(1400, 1700)
);
# 返回其他部门中比job_id为‘IT_PROG’部门任意工资低的员工好、姓名、job_id和salary
SELECT last_name, employee_id, job_id, salary
FROM employees 
WHERE salary<ANY(
	SELECT DISTINCT salary
	FROM employees
	WHERE job_id='IT_PROG'
) AND job_id<>'IT_PROG';
# 返回其他部门中比job_id为‘IT_PROG’部门所有工资低的员工好、姓名、job_id和salary
SELECT last_name, employee_id, job_id, salary
FROM employees 
WHERE salary<(
	SELECT MIN(salary)
	FROM employees
	WHERE job_id='IT_PROG'
) AND job_id<>'IT_PROG';
# 3. 行子查询（多行多列）
# 查询员工编号最小，工资最高的员工信息
SELECT * 
FROM employees
WHERE (employee_id, salary)=(
	SELECT MIN(employee_id), MAX(salary)
	FROM employees
)
# 列子查询
SELECT *
FROM employees
WHERE employee_id=(
	SELECT min(employee_id)
	FROM employees
)AND salary=(
	SELECT MAX(salary)
	FROM employees
)

# 二、select后面
# 查询每个部门的员工个数
SELECT d.*, (
	SELECT COUNT(*)
	FROM employees e
	WHERE d.`deparetment_id`=e.`department_id`
)
FROM departments d;
# 查询员工号=102的部门名
SELECT (
	SELECT department_name
	FROM departments d
	INNER JOIN employees e
	ON d.`deparetment_id`=e.`department_id`
	WHERE e.`employee_id`=102
) 部门名;

# 三、from后面
SELECT sg_dep.*, g.`grade_level`
FROM (
	SELECT AVG(salary) ag, department_id
	FROM employees 
	GROUP BY department_id;
) ag_dep
INNER JOIN job_grades g
ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;

# 四、exists后面（相关子查询）
# 查询有员工的部门名
SELECT department_name
FROM employees e
WHERE EXISTS(
	SELECT *
	FROM departments d
	WHERE d.`deparetment_id`=e.`department_id`
);
# 查询没有女朋友的男神信息
SELECT bo.*
FROM boys bo
WHERE bo.id NOT IN(
	SELECT boyfriend_id
	FROM beauty
);
SELECT bo.*
FROM boys bo
WHERE NOT EXISTS(
	SELECT boyfriend_id
	FROM beauty b
	WHERE bo.`id`=b.`boyfriend_id`
);

8. 分页查询

/*
	select 查询列表
	from 表
	【join type join 表2
	on 连接条件
	where 筛选条件
	group by 分组字段
	having 分组后的筛选
	order by 排序多的字段
	limit 【offset,】 size】
*/
# 查询前5条员工信息
SELECT * FROM employee LIMIT 0, 5;
SELECT * FROM employee LIMIT 5;
# 查询第11条到第15条信息
SELECT * FROM employee LIMIT 10, 15;
# 查询有奖金的员工信息，并且工资较高的前10名显示出来
SELECT * 
FROM employees 
WHERE commission_pct IS NOT NULL 
ORDER BY salary DESC
LIMIT 10;

9. 联合查询

/*
将多条查询语句的结果合并成一个结果
查询语句1
UNION
查询语句2
UNION
查询语句3
......
特点：
	多条查询语句查询列数一致
	要求多条查询语句每一列类型和顺序最好一致
*/
# 查询部门编号>90或邮箱包含a的员工信息
SELECT * FROM employees WHERE email LIKE '%a%' OR department_id>90;
# 联合查询
SELECT * FROM employees WHERE email LIKE '%a%' 
UNION
SELECT * FROM employees WHERE department_id>90;
# 查询中国用户中男性信息及外国用户中年男性的用户信息
SELECT id, cname, csex FROM t_ca WHERE csex='男'
UNION
SELECT t_id, tName, tGender FROM t_ua WHERE tGender='male';