DQL多表、分页、子查询，常见函数以及DML(数据管理语言)

一、多表联查

1.笛卡尔积
将多个表的数据进行一一对应，所得到结果为多表的笛卡尔积。结果的数量为所有表的数量的乘积。

笛卡尔集会在下面条件下产生：

1. 省略连接条件
2. 连接条件无效
3. 所有表中的所有行互相连接

为了避免笛卡尔集，可以在WHERE加入有效的连接条件

2.传统连接
等值连接：

1. 多表等值连接的结果为多表的交集部分
2. n表连接，至少需要n-1个连接条件
3. 多表的顺序没有要求
4. 一般需要为表起别名
5. 可以搭配排序、分组、筛选

例：查询员工名和对应的部门名

SELECT
	last_name 名字,
	department_name 部门名 
FROM
	t_mysql_employees e,
	t_mysql_departments d 
WHERE
	e.department_id = d.department_id;

结果：

3.内连接
语法：select 列名 from 表1 别名 inner join 表2 别名 on 连接条件;
特点：

1. 添加排序、分组、筛选
2. inner可以省略
3. 筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读
4. inner join连接和sql92语法中的等值连接效果是一样的，都是查询多表的交集

等值连接：
例：查询员工名、部门名

SELECT
	e.last_name,
	d.department_name 
FROM
	t_mysql_employees e
	JOIN t_mysql_departments d ON e.department_id = d.department_id;

4.外连接
应用场景：用于查询一个表中有，另一个表没有的记录

特点：

1. 外连接的查询结果为主表中的所有记录
   如果从表中有和它匹配的，则显示匹配的值；如果从表中没有和它匹配的，则显示null。
   外连接查询结果=内连接结果+主表中有而从表没有的记录。

2. 左外连接，left join左边的是主表
   右外连接，right join右边的是主表

3. 左外和右外交换两个表的顺序，可以实现同样的效果

4. 全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1没有的

左外语法：

select 列名 from 主表 left join 从表 on 筛选条件;

右外语法:

select 列名 从表 right join 主表 on 筛选条件;

全外语法:

select 列名 from 表1 full join 表2 on 筛选条件;

二、常用函数

1.字符函数
length：获取字节个数

SELECT LENGTH('hello world');#结果：11

concat：拼接字符串

SELECT CONCAT('hello','world');#结果：helloworld

LOWER：转小写
UPPER：转大写

SELECT LOWER('HELLOWORLD');#结果：helloworld
SELECT UPPER('helloworld');#结果：HELLOWORLD

substr：截取

SELECT SUBSTR('helloworld',1,5);#结果：hello

instr：字符出现索引值

SELECT INSTR('helloworld','e');#结果：2

replace：替换

SELECT REPLACE('helloworld','o','a');#结果：hellawarld

2.数字函数
round：四舍五入

select round(1.55);#结果：2

ceil：向上取整

select ceil(1.02);#结果：2

floor：向下取整

select floor(1.9);#结果：1

truncate：截断

SELECT TRUNCATE(2.333333,2);#结果：2.33

mod：取余

SELECT MOD(1600,300);#结果：100

3.日期函数
SELECT NOW();返回当前系统日期+时间
SELECT CURDATE();返回当前系统日期，不包含时间
SELECT CURTIME();返回当前时间，不包含日期

#获取指定年、月、日、小时、分钟、秒
select year(now);#month/day/hour...

#STR_TO_DATE将字符通过指定的格式转换成日期
SELECT STR_TO_DATE('1998-3-2','%Y-%m-%d');

#date_format 将日期转换成字符
SELECT DATE_FORMAT(NOW(),'%y年%m月%d日');

4.流程控制函数
if函数：

SELECT
	last_name,
	commission_pct,
IF
	( commission_pct IS NULL, '没奖金，呵呵', '有奖金，嘻嘻' ) 备注 
FROM
	t_mysql_employees;
	

结果：

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
	t_mysql_employees;
	

结果：

三、子查询

含义：出现在其他语句中的select语句，称为子查询或内查询。
外部的查询语句，称为主查询或外查询。

例1：查询工资比Abel高的员工名字和工资

SELECT
	last_name,
	salary 
FROM
	t_mysql_employees 
WHERE
	salary > ( SELECT salary FROM t_mysql_employees WHERE last_name = 'Abel' );
	

例2：查询工种与141号员工相同、工资比143号员工高的姓名，工种，工资

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 );
	

四、分页查询

应用场景：当要显示的数据，一页显示不全，需要分页提交sql请求

语法：
select 列名 from 表【jointype join 表2 on 连接条件 where 筛选条件 group by 分组字段 having 分组后的筛选 order by 排序的字段】limit 【offset】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;
	

五、DML（数据管理语言）

简介：DML 是对数据库的增删改操作，即添加表中的数据（insert），删除表中的数据（delete），修改表中的数据（update）。

1.添加数据
关键字：INSERT
语法：INSERT INTO 表名 (列名,列名…) VALUES (值,值…);
例：添加一条数据

INSERT INTO t_mysql_departments ( department_id, department_name, manager_id, location_id )
VALUES
	( 70, 'Public Relations', 100, 1700 );
	

2.修改数据
关键字：UPDATE
语法：UPDATE 表名 列名=新值 WHERE 筛选条件;
• 如果省略 WHERE 子句，则表中的所有数据都将被更新
例：修改张无忌的女朋友的手机号为114

UPDATE t_mysql_boys bo
INNER JOIN t_mysql_beauty b ON bo.`id` = b.`boyfriend_id` 
SET b.`phone` = '119',
bo.`userCP` = 1000 
WHERE
	bo.`boyName` = '张无忌';

3.删除数据
关键字：DELETE
语法：DELETE FROM 表名 WHERE 筛选条件;
• 如果省略 WHERE 子句，则表中的全部数据将被删除
例：删除张无忌的女朋友的信息

DELETE b 
FROM
	t_mysql_beauty b
	INNER JOIN t_mysql_boys bo ON b.`boyfriend_id` = bo.`id` 
WHERE
	bo.`boyName` = '张无忌';

delete与truncate的区别：

	DELETE	TRUNCATE
加WHERE条件	√	×
删除效率	比TRUNCATE低	比DELETE高
如有自增,删除再插入	从断点开始	从1开始
返回值	√	×
回滚	√	×