多表查询

笛卡尔集

产生条件：
省略连接条件
连接条件无效
所有表中的所有行互相连接

select * from t_mysql_beauty,t_mysql_boys

等值/连接查询

使用连接在多个表中查询数据
在 WHERE 子句中写入连接条件。
在表中有相同列时，在列名之前加上表名前缀

区分重复的列名
在不同表中具有相同列名的列可以用表的别名
加以区分。
如果使用了表别名，则在select语句中需要使
用表别名代替表名
表别名最多支持32个字符长度，但建议越少越
好

表的别名
使用别名可以简化查询。
使用表名前缀可以提高执行效率。

连接多个表
连接 n个表,至少需要 n-1个连接条件。

连接查询

含义：又称多表查询，当查询的字段来自于多个表时，就会用到连接查询

示例：
SELECT last_name,department_name
FROM t_mysql_employees,departments
WHERE t_mysql_employees.`department_id`=t_mysql_departments.`department_id`;

为表起别名

提高语句的简洁度
区分多个重名的字段

示例：
select b.`name`,s.boyName  from t_mysql_beauty b,t_mysql_boys s

加筛选

示例：
SELECT department_name,city
FROM t_mysql_departments t_mysql_d,locations l
WHERE d.`location_id` = l.`location_id`
AND city LIKE '_o%';

加分组

SELECT COUNT(*) 个数,city
FROM t_mysql_departments d,t_mysql_locations l
WHERE d.`location_id`=l.`location_id`
GROUP BY city;

非等值连接

示例：
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';

自连接

示例：
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`;

join链接

内连接

语法：select 查询列表 from 表1 别名 inner join 表2 别名 on 连接条件;

示例：
#案例1.查询员工名、部门名SELECT last_name,department_name
FROM t_mysql_departments dJOIN  t_mysql_employees e
ON e.`department_id` = d.`department_id`;

添加筛选

示例：
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%';

加分组

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;

加排序

示例：
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;

三表连接

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;

自连接

示例：SELECT e.last_name,m.last_nameFROM t_mysql_employees eJOIN t_mysql_employees mON e.`manager_id`= m.`employee_id`;

外连接

应用场景：用于查询一个表中有，另一个表没有的记录
特点：
1、外连接的查询结果为主表中的所有记录
如果从表中有和它匹配的，则显示匹配的值
如果从表中没有和它匹配的，则显示null
外连接查询结果=内连接结果+主表中有而从表没有的记录
2、左外连接，left join左边的是主表
右外连接，right join右边的是主表
3、左外和右外交换两个表的顺序，可以实现同样的效果
4、全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1没有的

左外连接

  SELECT d.*,e.employee_idFROM t_mysql_departments dLEFT OUTER JOIN t_mysql_employees eON d.`department_id` = e.`department_id`WHERE e.`employee_id` IS NULL;

右外连接

SELECT d.*,e.employee_idFROM t_mysql_employees eRIGHT OUTER JOIN t_mysql_departments dON d.`department_id` = e.`department_id`WHERE e.`employee_id` IS NULL;

全外连接

常见函数

字符函数

作用	函数	结果
转小写	LOWER(‘SQL Course’)	sql course
转大写	UPPER(‘SQL Course’)	SQL COURSE
拼接	CONCAT(‘Hello’, ‘World’)	HelloWorld
截取	SUBSTR(‘HelloWorld’,1,5)	Hello
长度	LENGTH(‘HelloWorld’)	10
字符出现索引值	INSTR(‘HelloWorld’, ‘W’)	6
字符截取后半段	TRIM(‘H’ FROM ‘HelloWorld’)	elloWorld
字符替换	REPLACE(‘abcd’,‘b’,‘m’)	amcd

2、数字函数

作用	函数	结果
四舍五入	ROUND(45.926, 2)	45.93
截断	TRUNC(45.926, 2)	45.92
求余	MOD(1600, 300)	100

3、日期函数

作用	函数	结果
获取当前日期	now()
将日期格式的字符转换成指定格式的日期	STR_TO_DATE(‘9-13-1999’,’%m-%d-%Y’)	1999-09-13
将日期转换成字符	DATE_FORMAT(‘2018/6/6’,‘%Y年%m月%d日’)	2018年06月06日

常见函数

概念：类似于java的方法，将一组逻辑语句封装在方法体中，对外暴露方法名
好处：1、隐藏了实现细节 2、提高代码的重用性
调用：select 函数名(实参列表) 【from 表】;
特点：
①叫什么（函数名）
②干什么（函数功能）
分类：
1、单行函数
如 concat、length、ifnull等
2、分组函数
功能：做统计使用，又称为统计函数、聚合函数、组函数

子查询

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

一、where或having后面

示例：谁的工资比 Abel 高?
1、查询Abel的工资
SELECT salary
FROM t_mysql_employees
WHERE last_name = 'Abel'2、查询员工的信息，满足 salary>①结果
SELECT *
FROM t_mysql_employees
WHERE salary>(SELECT salaryFROM t_mysql_employeesWHERE last_name = 'Abel'
);

列子查询（多行子查询）

示例：返回location_id是1400或1700的部门中的所有员工姓名
1、查询location_id是1400或1700的部门编号
SELECT DISTINCT department_id
FROM t_mysql_departments
WHERE location_id IN(1400,1700)2、查询员工姓名，要求部门号是①列表中的某一个
SELECT last_name
FROM t_mysql_employees
WHERE department_id  <>ALL(SELECT DISTINCT department_idFROM t_mysql_departmentsWHERE location_id IN(1400,1700)
);

行子查询（结果集一行多列或多行多列）

示例：查询员工编号最小并且工资最高的员工信息
SELECT * 
FROM t_mysql_employees
WHERE (employee_id,salary)=(SELECT MIN(employee_id),MAX(salary)FROM t_mysql_employees
);

二、select后面

仅仅支持标量子查询

示例：查询每个部门的员工个数
SELECT d.*,(SELECT COUNT(*)FROM t_mysql_employees eWHERE e.department_id = d.`department_id`) 个数FROM t_mysql_departments d;

三、from后面

将子查询结果充当一张表，要求必须起别名

示例：查询每个部门的平均工资的工资等级
1、查询每个部门的平均工资
SELECT AVG(salary),department_id
FROM t_mysql_employees
GROUP BY department_id
SELECT * FROM t_mysql_job_grades;
2、连接1结果集和job_grades表，筛选条件平均工资 between lowest_sal and highest_salSELECT  ag_dep.*,g.`grade_level`
FROM (SELECT AVG(salary) ag,department_idFROM t_mysql_employeesGROUP BY department_id
) ag_dep
INNER JOIN job_grades g
ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;

四、exists后面（相关子查询）

语法：exists(完整的查询语句)
结果：1或0

示例：查询有员工的部门名
SELECT department_name
FROM t_mysql_departments d
WHERE d.`department_id` IN(SELECT department_idFROM t_mysql_employees
)

分页查询

应用场景：当要显示的数据，一页显示不全，需要分页提交sql请求
语法：
select 查询列表
from 表
【join type join 表2
on 连接条件
where 筛选条件
group by 分组字段
having 分组后的筛选
order by 排序的字段】
limit 【offset,】size;
offset要显示条目的起始索引（起始索引从0开始）
size 要显示的条目个数
特点：
①limit语句放在查询语句的最后
②公式
要显示的页数 page，每页的条目数size
select 查询列表
from 表
limit (page-1)*size,size;

示例：查询前五条员工信息
SELECT * FROM  t_mysql_employees LIMIT 0,5;
SELECT * FROM  t_mysql_employees LIMIT 5;