mysql语法总结day2

day2：
1、sql99连接查询语法：
语法：
select 查询列表
from 表1 别名 【连接类型】inner/left/right/cross
join 表2 别名
on 连接条件
【where 筛选条件】
【group by 分组】
【having 筛选条件】
【order by 排序列表】
分类：
内连接：inner
外连接:
左外: left [outer]
右外: right [outer]
全外: full [outer]
交叉连接: cross

交叉连接: cross		

1.1、内连接
语法：

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

分类:
	等值连接
	非等值连接
	自连接

特点：
	a.添加排序、分组、筛选
	b.inner 可以省略
	c.筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读
	d.inner join 连接和sql92中的等值连接效果是一样的，都是查询多表的交集	
1.1.1、等值连接
   		 #查询员工名，部门名，工种名，并按部门名降序(三表连接)

		SELECT e.last_name,d.department_name,j.job_title
		FROM employees e
		INNER JOIN departments d ON e.`department_id`=d.`department_id`
		INNER JOIN jobs j ON j.`job_id`=e.`job_id`
		ORDER BY d.`department_name` DESC;

1.1.2、 非等值连接
#查询每个工资级别的个数>2的，并且进行一个排序（按工资级别降序）

		SELECT COUNT(*),grade_level
		FROM employees e 
		INNER JOIN 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;	
		DESC;	
1.1.3、自连接
		#查询姓名中包含字符k的员工的名字、上级的名字
		SELECT e.last_name,m.last_name
		FROM employees e	INNER JOIN employees m
		ON e.`employee_id`=m.`manager_id`	
		WHERE e.`last_name` LIKE '%k%';

1.2、外连接 — left outer join/right outer join/full outer join
应用场景：查询一个表中有一个表中没有的记录
特点：
1、外连接的查询结果为主表中的所有记录
如果从表中有和他匹配的，则显示匹配的值
如果从表中没有和它匹配的，则显示null
外连接查询结果=内连接的查询结果+主表中有而从表中没有的记录
2、左外连接，left join左边的是主表
右外连接，right join右边的是主表
3、左外和右外交换两个表的顺序，可以实现同样的效果
#引入：查询男朋友不在男神表的女神名 ----girls库
SELECT * FROM beauty;
SELECT * FROM boys;

				#左外连接
				SELECT b.name,bo.*
				FROM beauty b
				LEFT OUTER JOIN boys bo
				ON b.`boyfriend_id` = bo.`id`
				WHERE bo.`id` IS NOT NULL;
				
				#右外连接
				SELECT b.name,bo.*
				FROM boys bo
				LEFT OUTER JOIN beauty b
				ON bo.`id`=b.`boyfriend_id`
				WHERE bo.`id` IS NOT NULL;	
				
				#以boys为主表
				SELECT bo.boyName,b.*
				FROM boys bo
				LEFT OUTER JOIN beauty b
				ON bo.`id`=b.`boyfriend_id`;
				
				#案例1:查询哪个部门没有员工 ---myemployee表
				#左外连接
				SELECT d.*,e.employee_id
				FROM departments d
				LEFT OUTER JOIN employees e
				ON d.`department_id`=e.`department_id`
				WHERE e.`employee_id` IS NULL;
				
				#右外连接
				SELECT d.*,e.employee_id 
				FROM employees e
				RIGHT OUTER JOIN departments d 
				ON e.`department_id`=d.`department_id`
				WHERE e.`employee_id` IS NULL;		

1.3、全外连接
mysql中不支持全外连接，但是全外连接的效果=内连接的效果+表1中有单表2中没有的+表2中有但是表1中没有的效果之和
1.4、交叉连接 —笛卡尔乘积 cross join
结果行数=表1行数 、 表2行数之积
SELECT b.,bo.
FROM beauty b
CROSS JOIN boys bo;
总结：
#sql 92 VS sql 99
#功能：sql99支持的较多
#可读性：sql99实现连接条件和筛选条件的分离，可读性较高

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

	分类：
	按子查询出现的位置：
		select后面：
			仅仅支持标量子查询
		from后面：
			支持表子查询
		where或having后面：
			支持标量子查询（单行）、列子查询（多行）、行子查询
		exists后面（相关子查询）
			支持表子查询
	按结果集的行列数不同：
		标量子查询（结果集只有一行一列）
		列子查询（结果集只有一列多行）
		行子查询（结果集有一行多列）
		表子查询（结果集一般为多行多列）

2.1、where或者having后面
特点：
1、子查询放在小括号内
2、子查询一般放在条件右侧
3、标量子查询，一般搭配着单行操作符（> < >= <= <>）使用
4、列子查询，一般搭配着多行操作符（IN、any/some、all）使用
5、子查询的执行优先于主查询的执行，主查询的条件用到了子查询的结果
2.1.1、标量子查询（单行子查询）
#案例1、谁的工资比Abel高
#step1:查询Abel的工资
SELECT salary
FROM employees
WHERE last_name=‘Abel’;
#step2:查询员工的信息，满足salary>step1的结果
SELECT * FROM employees
WHERE salary>(
SELECT salary FROM employees
WHERE last_name=‘Abel’
);
#案例2、返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资
#step1查询141号员工的job_id
SELECT job_id
FROM employees
WHERE employee_id=141;
#step2查询143号员工的salary
SELECT salary
FROM employees
WHERE employee_id=143;
#step3查询员工的姓名，job_id和工资，要求job_id=step1并且salary>ste2
SELECT last_name,job_id,salary
FROM employees
WHERE job_id=(
SELECT job_id
FROM employees
WHERE employee_id = 141
) AND salary > (
SELECT salary
FROM employees
WHERE employee_id = 143
);
#案例3：查询最低工资大于50号部门的最低工资的部门id和器最低工资
#step1查询50号部门的最低工资
SELECT MIN(salary)
FROM employees
WHERE department_id=50;
#step2查询每个部门的最低工资
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id;
#step3筛选step2，满足min(salary)>step1
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary) > (
SELECT MIN(salary)
FROM employees
WHERE department_id = 50
);
2.1.2、列子查询（多行子查询------一列多行）通常搭配多行比较操作符使用
操作符 含义
in/not in 等于或是不等于列表中的任意一个
any/some 盒子查询返回的某一个值比较
all 和子查询返回的所有值比较
#案例1、返回location_id是1400或1700的部门中的所有员工姓名
#step1查询location_id是1400或1700的部门编号
SELECT DISTINCT department_id
FROM departments
WHERE location_id IN (1400,1700);
#step2查询出员工姓名，要求部门号是step1中的一个
SELECT last_name
FROM employees
WHERE department_id IN(
SELECT department_id
FROM departments
WHERE location_id IN(1400,1700)
);
#案例2、返回其他工种中比job_id为’IT_PROG’工种任一工资低的员工的：工号、姓名、job_id以及salary
#step1查询job_id为‘IT_PROG’部门的任一工资
SELECT DISTINCT salary
FROM employees
WHERE job_id = ‘IT_PROG’;
#step2查询员工号、姓名、job_id以及salary.salary<step1中的任意一个
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary<ANY(
SELECT DISTINCT salary
FROM employees
WHERE job_id=‘IT_PROG’
) AND job_id <> ‘IT_PROG’;
#或者
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary<(
SELECT MAX(salary)
FROM 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 salary< ALL(
SELECT DISTINCT salary
FROM employees
WHERE job_id = ‘IT_PROG’
) AND job_id <> ‘IT_PROG’;
#或
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’;
2.1.3、行子查询（结果集是一行多列或者是多行多列）
#案例：查询员工编号最小并且工资最高的员工信息
SELECT *
FROM employees
WHERE (employee_id,salary) = (
SELECT MIN(employee_id),MAX(salary)
FROM employees
);
#step1查询最小的员工编号
SELECT MIN(employee_id)
FROM employees;
#step2查询最高工资
SELECT MAX(salary)
FROM employees;
#step3查询员工信息
SELECT ***
FROM employees
WHERE employee_id=(
SELECT MIN(employee_id)
FROM employees
)AND salary=(
SELECT MAX(salary)
FROM employees
);
2.2 select后面------只能是一行一列，标量子查询
#案例：查询每个部门的员工个数
SELECT d.,(
SELECT COUNT()
FROM employees e
WHERE e.department_id=d.department_id
) 个数
FROM departments d;
2.3 from后面（将子查询结果充当一张表，要求必须起别名）
#案例：查询每个部门的平均工资的工资等级
#step1 查询每个部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id;
SELECT * FROM job_grades;
#step2 连接step1的结果集合job_grades表，筛选条件平均工资between lowest_salary and highest_salary
SELECT ag_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;
2.4、放在exists后面（相关子查询）
语法：
exists(完整的查询语句)
结果只有两种情况：
1或0
#案例1：查询有员工的部门名
SELECT department_name
FROM departments d
WHERE EXISTS(
SELECT *
FROM employees e
WHERE d.department_id=e.department_id
);
#使用in
SELECT department_name
FROM departments d
WHERE d.department_id IN (
SELECT department_id
FROM employees
);
#案例2：查询没有女朋友的男神信息
#in的方式
SELECT bo.*
FROM boys bo
WHERE bo.id NOT IN(
SELECT boyfriend_id
FROM beauty
);
#exists子查询
SELECT bo.*
FROM boys bo
WHERE NOT EXISTS(
SELECT boyfriend_id
FROM beauty b
WHERE bo.id = b.boyfriend_id
);
3、分页查询
应用场景：当前显示的数据，一页显示不全，需要分页提交sql请求
语法：
select 查询列表 --------step7
from 表 --------step1
【join type join 表2 --------step2
on 连接条件 --------step3
where 筛选条件 --------step4
group by 分组字段 --------step5
having 分组后的筛选 --------step6
order by 排序的字段】 --------step8
limit [offset,]size; --------step9
offset:要显示条目的起始索引（mysql中索引从1开始，但是在这里从0开始）
size:要显示的条目个数
特点：
1、limit语句放在查询语句的最后，不光是语法上的最后，执行顺序也是在最后
2、公式：
显示的页数是page，每页的条目数是size
select 查询列表
from 表
limit (page-1)*size,size
#案例1、查询前五条员工信息
SELECT * FROM employees LIMIT 0,5;
SELECT * FROM employees LIMIT 5;
#案例2、查询第11条到第25条员工信息
SELECT * FROM employees LIMIT 10,15;
#案例3、有奖金的员工的信息并且工资较高的前10名显示出来
SELECT *
FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY salary DESC
LIMIT 10;
5、总结：
5.1 内连接
语法:
select 查询列表
from 表1 别名
[inner] join 表2 别名 on 连接条件
where 筛选条件
group by 分组列表
having 分组后的筛选
order by 排序列表
limit 【offset,】size;
特点：
1、表的顺序可以调换，部分主从表
2、内连接的结果=多表的交集
3、n表连接至少需要n-1个连接条件
分类：
等值连接、非等值连接、自连接
5.2 外连接
语法:
select 查询列表
from 表1 别名
left|right|full [outer] join 表2 别名 on 连接条件
where 筛选条件
group by 分组列表
having 分组后的筛选
order by 排序列表
limit 【offset,】size;
特点：
1、查询的结果=主表中的所有行，其中从表和它匹配的将显示所有匹配行，如果没有匹配的则显示null
2、left join左边的就是主表，right join右边就是主表，full join两边都是主表
3、一般用于查询除了交集部分的剩余的不匹配的行
5.3 交叉连接
语法：
select 查询列表
from 表1 别名
cross join 表2 别名
特点：
类似于笛卡尔乘积
5.4 子查询
含义：嵌套子其他语句内部的select 语句称为子查询或是内查询
外面的语句可以是insert/update/delete/select等 ；
外面如果为select语句，则此语句称为外查询或是主查询。
分类：1、按出现的位置
select后面 仅仅支持标量子查询
from后面 表子查询
where或是having后面 标量子查询/列子查询/行子查询
exists后面 标量子查询/列子查询/行子查询/表子查询
2、按结果集的行列
标量子查询（单行子查询）：结果集为一行一列
列子查询（多行子查询）：结果集为多行一列
行子查询：结果集为多行多列
表子查询（嵌套子查询）：结果集为表
5.5 分页查询
应用场景：当查询的条目数太多，一页显示不全
语法：
select 查询列表
from 表
limit [offset,] size;
注意：
offsetd代表的是起始的条目索引，默认从0开始
size代表的是显示的条目数
公式：
假如要显示的页数为page,每一页条目数为size
select 查询列表
from 表
limit (page-1)*size,size;