DQL（二）
- 多表查询
- 常见函数
- 1、字符函数
- 2、数字函数
- 3、日期函数
- 4、其他函数
- 5、流程控制函数
- 子查询
- 分页查询
多表查询
1、笛卡尔集
相当于一种存储方式
笛卡尔集会在下面条件下产生
– 省略连接条件
– 连接条件无效
– 所有表中的所有行互相连接
– 为了避免笛卡尔集， 可以在 WHERE 加入有 效的连接条件。
2、等值/连接
使用连接在多个表中查询数据
• 在 WHERE 子句中写入连接条件。
• 在表中有相同列时，在列名之前加上表名前缀
含义：又称多表查询，当查询的字段来自于多个表时，就会用到连接查询
笛卡尔乘积现象：表1 有m行，表2有n行，结果=m*n行
发生原因：没有有效的连接条件
如何避免：添加有效的连接条件
分类：
按年代分类：
sql92标准:仅仅支持内连接
sql99标准【推荐】：支持内连接+外连接（左外和右外）+交叉连接
按功能分类：
内连接：
等值连接
非等值连接
自连接
外连接：
左外连接
右外连接
全外连接
交叉连接
一、sql92标准
1、等值连接
① 多表等值连接的结果为多表的交集部分
②n表连接，至少需要n-1个连接条件
③ 多表的顺序没有要求
④一般需要为表起别名
⑤可以搭配前面介绍的所有子句使用，比如排序、分组、筛选
1：查询女神名和对应的男神名
SELECT NAME,boyName FROM boys,beauty WHERE beauty.boyfriend_id= boys.id;
2：查询员工名和对应的部门名
SELECT last_name,department_name FROM t_mysql_employees,departments WHERE t_mysql_employees.`department_id`=t_mysql_departments.`department_id`;
2、为表起别名
①提高语句的简洁度
②区分多个重名的字段
1、查询员工名、工种号、工种名
SELECT e.last_name,e.job_id,j.job_title FROM t_mysql_employees e,jobs j WHERE e.`job_id`=j.`job_id`;
3、两个表的顺序是否可以调换
1、查询员工名、工种号、工种名
SELECT e.last_name,e.job_id,j.job_title FROM t_mysql_jobs j,t_mysql_employees e WHERE e.`job_id`=j.`job_id`;
4、可以加筛选
1、查询有奖金的员工名、部门名
SELECT last_name,department_name,commission_pct FROM t_mysql_employees e,t_mysql_departments d WHERE e.`department_id`=d.`department_id` AND e.`commission_pct` IS NOT NULL;
2：查询城市名中第二个字符为o的部门名和城市名
SELECT department_name,city FROM t_mysql_departments t_mysql_d,locations l WHERE d.`location_id` = l.`location_id` AND city LIKE '_o%';
5、可以加分组
1：查询每个城市的部门个数
SELECT COUNT(*) 个数,city FROM t_mysql_departments d,t_mysql_locations l WHERE d.`location_id`=l.`location_id` GROUP BY city;
2：查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
SELECT department_name,d.`manager_id`,MIN(salary) FROM t_mysql_departments d,t_mysql_employees e WHERE d.`department_id`=e.`department_id` AND commission_pct IS NOT NULL GROUP BY department_name,d.`manager_id`;
6、可以加排序
1、查询每个工种的工种名和员工的个数，并且按员工个数降序
SELECT job_title,COUNT(*) FROM t_mysql_employees e,t_mysql_jobs j WHERE e.`job_id`=j.`job_id` GROUP BY job_title ORDER BY COUNT(*) DESC;
7、可以实现三表连接？
1、查询员工名、部门名和所在的城市
SELECT last_name,department_name,city FROM t_mysql_employees e,t_mysql_departments d,t_mysql_locations l WHERE e.`department_id`=d.`department_id` AND d.`location_id`=l.`location_id` AND city LIKE 's%' ORDER BY department_name DESC;
2、非等值连接
1：查询员工的工资和工资级别
SELECT salary,grade_level FROM t_mysql_employees e,t_mysql_job_grades g WHERE salary BETWEEN g.`lowest_sal` AND g.`highest_sal` AND g.`grade_level`='A';
3、自连接
1、查询 员工名和上级的名称
SELECT e.employee_id,e.last_name,m.employee_id,m.last_name FROM t_mysql_employees e,t_mysql_employees m WHERE e.`manager_id`=m.`employee_id`;
3、join链接
二、sql99语法
分类：
内连接（★）：inner
外连接
左外(★):left 【outer】
右外(★)：right 【outer】
全外：full【outer】
交叉连接：cross
一）内连接
分类：
等值
非等值
自连接
特点：
①添加排序、分组、筛选
②inner可以省略
③ 筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读
④inner join连接和sql92语法中的等值连接效果是一样的，都是查询多表的交集
1、等值连接
1.查询员工名、部门名
SELECT last_name,department_name FROM t_mysql_departments d JOIN t_mysql_employees e ON e.`department_id` = d.`department_id`;
2.查询名字中包含e的员工名和工种名（添加筛选）
SELECT last_name,job_title FROM t_mysql_employees e INNER JOIN t_mysql_jobs j ON e.`job_id`= j.`job_id` WHERE e.`last_name` LIKE '%e%';
3. 查询部门个数>3的城市名和部门个数，（添加分组+筛选）
①查询每个城市的部门个数
②在①结果上筛选满足条件的
SELECT city,COUNT(*) 部门个数 FROM t_mysql_departments d INNER JOIN t_mysql_locations l ON d.`location_id`=l.`location_id` GROUP BY city HAVING COUNT(*)>3;
4.查询哪个部门的员工个数>3的部门名和员工个数，并按个数降序（添加排序）
①查询每个部门的员工个数
SELECT COUNT(*),department_name FROM t_mysql_employees e INNER JOIN t_mysql_departments d ON e.`department_id`=d.`department_id` GROUP BY department_name
② 在①结果上筛选员工个数>3的记录，并排序
SELECT COUNT(*) 个数,department_name FROM t_mysql_employees e INNER JOIN t_mysql_departments d ON e.`department_id`=d.`department_id` GROUP BY department_name HAVING COUNT(*)>3 ORDER BY COUNT(*) DESC;
5.查询员工名、部门名、工种名，并按部门名降序（添加三表连接）
SELECT last_name,department_name,job_title FROM t_mysql_employees e INNER JOIN t_mysql_departments d ON e.`department_id`=d.`department_id` INNER JOIN t_mysql_jobs j ON e.`job_id` = j.`job_id` ORDER BY department_name DESC;
二）非等值连接
1、查询员工的工资级别
SELECT salary,grade_level FROM t_mysql_employees e JOIN t_mysql_job_grades g ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`;
2、查询工资级别的个数>20的个数，并且按工资级别降序
SELECT COUNT(*),grade_level FROM t_mysql_employees e JOIN t_mysql_job_grades g ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal` GROUP BY grade_level HAVING COUNT(*)>20 ORDER BY grade_level DESC;
三）自连接
1、查询员工的名字、上级的名字
SELECT e.last_name,m.last_name FROM t_mysql_employees e JOIN t_mysql_employees m ON e.`manager_id`= m.`employee_id`;
2、查询姓名中包含字符k的员工的名字、上级的名字
SELECT e.last_name,m.last_name FROM t_mysql_employees e JOIN t_mysql_employees m ON e.`manager_id`= m.`employee_id` WHERE e.`last_name` LIKE '%k%';
四）外连接
应用场景：用于查询一个表中有，另一个表没有的记录
特点：
1、外连接的查询结果为主表中的所有记录
如果从表中有和它匹配的，则显示匹配的值
如果从表中没有和它匹配的，则显示null
外连接查询结果=内连接结果+主表中有而从表没有的记录
2、左外连接，left join左边的是主表
右外连接，right join右边的是主表
3、左外和右外交换两个表的顺序，可以实现同样的效果
4、全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1没有的
左外连接
SELECT b.*,bo.* FROM t_mysql_boys bo LEFT OUTER JOIN t_mysql_beauty b ON b.`boyfriend_id` = bo.`id` WHERE b.`id` IS NULL;
1：查询哪个部门没有员工
左外
SELECT d.*,e.employee_id FROM t_mysql_departments d LEFT OUTER JOIN t_mysql_employees e ON d.`department_id` = e.`department_id` WHERE e.`employee_id` IS NULL;
右外
SELECT d.*,e.employee_id FROM t_mysql_employees e RIGHT OUTER JOIN t_mysql_departments d ON d.`department_id` = e.`department_id` WHERE e.`employee_id` IS NULL;
全外
SELECT b.*,bo.* FROM t_mysql_beauty b FULL OUTER JOIN t_mysql_boys bo ON b.`boyfriend_id` = bo.id;
交叉连接
SELECT b.*,bo.* FROM t_mysql_beauty b CROSS JOIN boys bo;常见函数
1、字符函数
一、字符函数
1.length 获取参数值的字节个数
SELECT LENGTH(‘john’);
SELECT LENGTH('张三丰hahaha');
SHOW VARIABLES LIKE '%char%'
2.concat 拼接字符串
SELECT CONCAT(last_name,'_',first_name) 姓名 t_mysql_FROM employees;
3.upper、lower
SELECT UPPER('john');
SELECT LOWER('joHn');
1、将姓变大写，名变小写，然后拼接
SELECT CONCAT(UPPER(last_name),LOWER(first_name)) 姓名 FROM t_mysql_employees;
4.substr、substring
注意：索引从1开始
1、截取从指定索引处后面所有字符
SELECT SUBSTR('李莫愁爱上了陆展元',7) out_put;
2、截取从指定索引处指定字符长度的字符
SELECT SUBSTR('李莫愁爱上了陆展元',1,3) out_put;
1、姓名中首字符大写，其他字符小写然后用_拼接，显示出来
SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(last_name,2))) out_put FROM t_mysql_employees;
5.instr 返回子串第一次出现的索引，如果找不到返回0
SELECT INSTR('杨不殷六侠悔爱上了殷六侠','殷八侠') AS out_put;
6.trim
SELECT LENGTH(TRIM(' 张翠山 ')) AS out_put;
SELECT TRIM('aa' FROM 'aaaaaaaaa张aaaaaaaaaaaa翠山aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa') AS out_put;
7.lpad 用指定的字符实现左填充指定长度
SELECT LPAD('殷素素',2,'*') AS out_put;
8.rpad 用指定的字符实现右填充指定长度
SELECT RPAD('殷素素',12,'ab') AS out_put;
9.replace 替换
SELECT REPLACE('周芷若周芷若周芷若周芷若张无忌爱上了周芷若','周芷若','赵敏') AS out_put;2、数字函数
二、数学函数
round 四舍五入
SELECT ROUND(-1.55);
SELECT ROUND(1.567,2);
ceil 向上取整,返回>=该参数的最小整数
SELECT CEIL(-1.02);
floor 向下取整，返回<=该参数的最大整数
SELECT FLOOR(-9.99);
truncate 截断
SELECT TRUNCATE(1.69999,1);
mod取余
/*
mod(a,b) ： a-a/b*b
mod(-10,-3):-10- (-10)/(-3)*（-3）=-1
*/
SELECT MOD(10,-3);
SELECT 10%3;3、日期函数
三、日期函数
now 返回当前系统日期+时间
SELECT NOW();
curdate 返回当前系统日期，不包含时间
SELECT CURDATE();
curtime 返回当前时间，不包含日期
SELECT CURTIME();
可以获取指定的部分，年、月、日、小时、分钟、秒
SELECT YEAR(NOW()) 年;、
SELECT YEAR('1998-1-1') 年;
SELECT YEAR(hiredate) 年 FROM t_mysql_employees;
SELECT MONTH(NOW()) 月;
SELECT MONTHNAME(NOW()) 月;
str_to_date 将字符通过指定的格式转换成日期
SELECT STR_TO_DATE('1998-3-2','%Y-%c-%d') AS out_put;
查询入职日期为1992--4-3的员工信息
SELECT * FROM employees WHERE hiredate = '1992-4-3';
SELECT * FROM employees WHERE hiredate = STR_TO_DATE('4-3 1992','%c-%d %Y');
date_format 将日期转换成字符
SELECT DATE_FORMAT(NOW(),'%y年%m月%d日') AS out_put;
查询有奖金的员工名和入职日期(xx月/xx日 xx年)
SELECT last_name,DATE_FORMAT(hiredate,'%m月/%d日 %y年') 入职日期 FROM t_mysql_employees WHERE commission_pct IS NOT NULL;4、其他函数
SELECT VERSION();
SELECT DATABASE();
SELECT USER();5、流程控制函数
1.if函数： if else 的效果
SELECT IF(10<5,'大','小');
SELECT last_name,commission_pct,IF(commission_pct IS NULL,'没奖金，呵呵','有奖金，嘻嘻') 备注 FROM t_mysql_employees;
2.case函数的使用一： switch case 的效果
/*查询员工的工资，要求
部门号=30，显示的工资为1.1倍
部门号=40，显示的工资为1.2倍
部门号=50，显示的工资为1.3倍
其他部门，显示的工资为原工资
*/
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 t_mysql_employees;
3.case 函数的使用二：类似于 多重if
查询员工的工资的情况
如果工资>20000,显示A级别
如果工资>15000,显示B级别
如果工资>10000，显示C级别
否则，显示D级别
SELECT salary,
CASE
WHEN salary>20000 THEN 'A'
WHEN salary>15000 THEN 'B'
WHEN salary>10000 THEN 'C'
ELSE 'D'
END AS 工资级别
FROM t_mysql_employees;子查询
含义：
出现在其他语句中的select语句，称为子查询或内查询
外部的查询语句，称为主查询或外查询
分类：
按子查询出现的位置：
select后面：
仅仅支持标量子查询
from后面：
支持表子查询
where或having后面：★
标量子查询（单行） √
列子查询 （多行） √
行子查询
exists后面（相关子查询）
表子查询
一、where或having后面
1、标量子查询（单行子查询）
2、列子查询（多行子查询）
3、行子查询（多列多行）
特点：
①子查询放在小括号内
②子查询一般放在条件的右侧
③标量子查询，一般搭配着单行操作符使用
④子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果
1.标量子查询★
1：谁的工资比 Abel 高?
①查询Abel的工资
SELECT salary FROM t_mysql_employees WHERE last_name = 'Abel'
②查询员工的信息，满足 salary>①结果
SELECT * FROM t_mysql_employees WHERE salary>(
SELECT salary FROM t_mysql_employees WHERE last_name = 'Abel'
);
2：返回job_id与141号员工相同，salary比143号员工多的员工 姓名，job_id 和工资
①查询141号员工的job_id
SELECT job_id FROM t_mysql_employees WHERE employee_id = 141
②查询143号员工的salary
SELECT salary FROM t_mysql_employees WHERE employee_id = 143
③查询员工的姓名，job_id 和工资，要求job_id=①并且salary>②
SELECT last_name,job_id,salary FROM t_mysql_employees WHERE job_id = (
SELECT job_id FROM t_mysql_employees WHERE employee_id = 141
) AND salary>(
SELECT salary FROM t_mysql_employees WHERE employee_id = 143
);
3：返回公司工资最少的员工的last_name,job_id和salary
①查询公司的 最低工资
SELECT MIN(salary) FROM t_mysql_employees
②查询last_name,job_id和salary，要求salary=①
SELECT last_name,job_id,salary FROM t_mysql_employees WHERE salary=(
SELECT MIN(salary) FROM t_mysql_employees
);
4：查询最低工资大于50号部门最低工资的部门id和其最低工资
①查询50号部门的最低工资
SELECT MIN(salary) FROM t_mysql_employees WHERE department_id = 50
②查询每个部门的最低工资
SELECT MIN(salary),department_id FROM t_mysql_employees GROUP BY department_id
③ 在②基础上筛选，满足min(salary)>①
SELECT MIN(salary),department_id FROM t_mysql_employees GROUP BY department_id HAVING MIN(salary)>(
SELECT MIN(salary) FROM t_mysql_employees WHERE department_id = 50
);
1、非法使用标量子查询
SELECT MIN(salary),department_id FROM t_mysql_employees GROUP BY department_id HAVING MIN(salary)>(
SELECT salary FROM t_mysql_employees WHERE department_id = 250
);
2.列子查询（多行子查询）★
1：返回location_id是1400或1700的部门中的所有员工姓名
①查询location_id是1400或1700的部门编号
SELECT DISTINCT department_id FROM t_mysql_departments WHERE location_id IN(1400,1700)
②查询员工姓名，要求部门号是①列表中的某一个
SELECT last_name FROM t_mysql_employees WHERE department_id <>ALL(
SELECT DISTINCT department_id FROM t_mysql_departments WHERE location_id IN(1400,1700)
);
2：返回其它工种中比job_id为‘IT_PROG’工种任一工资低的员工的员工号、姓名、job_id 以及salary
①查询job_id为‘IT_PROG’部门任一工资
SELECT DISTINCT salary FROM t_mysql_employees WHERE job_id = 'IT_PROG'
②查询员工号、姓名、job_id 以及salary，salary<(①)的任意一个
SELECT last_name,employee_id,job_id,salary FROM t_mysql_employees WHERE salary<ANY(
SELECT DISTINCT salary FROM t_mysql_employees WHERE job_id = 'IT_PROG'
) AND job_id<>'IT_PROG';
或
SELECT last_name,employee_id,job_id,salary FROM t_mysql_employees WHERE salary<(
SELECT MAX(salary) FROM t_mysql_employees WHERE job_id = 'IT_PROG'
) AND job_id<>'IT_PROG';
3：返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工 的员工号、姓名、job_id 以及salary
SELECT last_name,employee_id,job_id,salary FROM employees WHERE t_mysql_salary<ALL(
SELECT DISTINCT salary FROM t_mysql_employees WHERE job_id = 'IT_PROG'
) AND job_id<>'IT_PROG';
或
SELECT last_name,employee_id,job_id,salary FROM t_mysql_employees WHERE salary<(
SELECT MIN( salary) FROM t_mysql_employees WHERE job_id = 'IT_PROG'
) AND job_id<>'IT_PROG';
3、行子查询（结果集一行多列或多行多列）
1：查询员工编号最小并且工资最高的员工信息
SELECT * FROM t_mysql_employees WHERE (employee_id,salary)=(
SELECT MIN(employee_id),MAX(salary) FROM t_mysql_employees
);
①查询最小的员工编号
SELECT MIN(employee_id) FROM t_mysql_employees
②查询最高工资
SELECT MAX(salary) FROM t_mysql_employees
③查询员工信息
SELECT * FROM t_mysql_employees WHERE employee_id=(
SELECT MIN(employee_id) FROM t_mysql_employees
)AND salary=(
SELECT MAX(salary) FROM t_mysql_employees
);
二、select后面
/*
仅仅支持标量子查询
*/
1：查询每个部门的员工个数
SELECT d.*,(
SELECT COUNT(*) FROM t_mysql_employees e WHERE e.department_id = d.`department_id`
) 个数 FROM t_mysql_departments d;
2：查询员工号=102的部门名
SELECT (
SELECT department_name,e.department_id FROM t_mysql_departments d INNER JOIN employees e ON d.department_id=e.department_id WHERE e.employee_id=102
) 部门名;
三、from后面
/*
将子查询结果充当一张表，要求必须起别名
*/
1：查询每个部门的平均工资的工资等级
①查询每个部门的平均工资
SELECT AVG(salary),department_id FROM t_mysql_employees GROUP BY department_id
SELECT * FROM t_mysql_job_grades;
②连接①的结果集和job_grades表，筛选条件平均工资 between lowest_sal and highest_sal
SELECT ag_dep.*,g.`grade_level` FROM (
SELECT AVG(salary) ag,department_id FROM t_mysql_employees GROUP BY department_id
) ag_dep INNER JOIN job_grades g ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;
四、exists后面（相关子查询）
SELECT EXISTS(SELECT employee_id FROM t_mysql_employees WHERE salary=300000);
1：查询有员工的部门名
1、in
SELECT department_name FROM t_mysql_departments d WHERE d.`department_id` IN(
SELECT department_id FROM t_mysql_employees
)
2、exists
SELECT department_name FROM t_mysql_departments d WHERE EXISTS(
SELECT * FROM t_mysql_employees e WHERE d.`department_id`=e.`department_id`
);
2：查询没有女朋友的男神信息
1、in
SELECT bo.* FROM t_mysql_boys bo WHERE bo.id NOT IN(
SELECT boyfriend_id FROM t_mysql_beauty
)
2、exists
SELECT bo.* FROM t_mysql_boys bo WHERE NOT EXISTS(
SELECT boyfriend_id FROM t_mysql_beauty b WHERE bo.`id`=b.`boyfriend_id`
);分页查询
应用场景：当要显示的数据，一页显示不全，需要分页提交sql请求
特点：
①limit语句放在查询语句的最后
②公式
要显示的页数 page，每页的条目数size
1：查询前五条员工信息
SELECT * FROM t_mysql_employees LIMIT 0,5;
SELECT * FROM t_mysql_employees LIMIT 5;
2：查询第11条——第25条
SELECT * FROM t_mysql_employees LIMIT 10,15;
3：有奖金的员工信息，并且工资较高的前10名显示出来
SELECT * FROM t_mysql_employees WHERE commission_pct IS NOT NULL ORDER BY salary DESC LIMIT 10 ;
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
