数据库基础知识——DQL语言（二）

1.多表连接查询

笛卡尔乘积：表1，有m行；表2，有n行，如果连接条件省略或无效，则结果=m*n；
解决办法：添加上连接条件

1.1 sql92语法

sql92语法的多表连接查询包含：等值连接、非等值连接、自连接

1.1.1 等值连接

1.等值连接的结果 = 多个表的交集
2.n表连接，至少需要n-1个连接条件
3.多个表不分主次，没有顺序要求
4.一般为表起别名，提高阅读性和性能

#将beauty表中的女生和boys表中的男生匹配
select name,boyName
from beauty,boys
where beauty.boyfriend_id=boys.id;

#查询员工名，员工工种id和员工工种名
select last_name,employees.job_id,job_title
from employees,jobs
where employees.job_id=jobs.job_id;

#查询员工名和对应的部门
select last_name,department_name
from employees,departments
where employees.department_id=departments.department_id;

#查询有奖金的员工名和部门名
select last_name,department_name
from employees,departments
where employees.department_id=departments.department_id
and employees.commission_pct is not null;

#查询每个城市的部门个数
select count(*),city
from locations,departments
where locations.location_id=departments.location_id
group by city;

#查询员工名，部门名和所在的城市
select last_name,departments.department_name,locations.city
from employees,departments,locations
where employees.department_id=departments.department_id
and departments.location_id=locations.location_id;

1.1.2 sql92:非等值连接

#查询员工名，员工的工资和工资级别
select last_name,salary,grade_level
from employees,job_grades
where salary between lowest_sal and highest_sal;

1.1.3 sql92:自连接

#查询员工名和上级姓名
select e.last_name,e.employee_id,l.last_name as "leader"
from employees e,employees l
where e.manager_id=l.employee_id;

1.2 sql99语法

含义：1999年推出的sql语法
支持：
等值连接、非等值连接 （内连接）
外连接
交叉连接

语法：

select 字段，...
from 表1
【inner|left outer|right outer|cross】join 表2 on  连接条件
【inner|left outer|right outer|cross】join 表3 on  连接条件
【where 筛选条件】
【group by 分组字段】
【having 分组后的筛选条件】
【order by 排序的字段或表达式】

好处：语句上，连接条件和筛选条件实现了分离，简洁明了！

1.2.1 等值连接

#查询员工名和部门名
select last_name,department_name
from employees
inner join departments
on employees.department_id=departments.department_id;

#查询名字中包含e的员工名和工种名（包含筛选）
select last_name,job_title
from employees
inner join jobs
on employees.job_id=jobs.job_id
where last_name like "%e%";

#查询部门个数大于3的城市和部门个数
select city,count(*)
from locations
inner join departments
on locations.location_id=departments.location_id
group by city
having count(*)>3;

1.2.2 非等值连接

#查询员工名和工资和工资水平
select last_name,salary,grade_level
from employees
inner join job_grades
on employees.salary between job_grades.lowest_sal and job_grades.highest_sal;

1.2.3 内连接

#查询员工的名字和上级姓名
select e.last_name,l.last_name as "leader"
from employees as e
inner join employees as l
on e.manager_id=l.employee_id;

1.2.4 左右连接（外连接）

用于查询一个表中有，另外一个表中没有的记录
特点：
		外连接的查询结果为主表中的所有记录，如果从表中有和主表匹配的，则显示对应的值，否则显示为null；
		左外连接：left join的左边是主表；右外连接：right join的右边是主表；

#查询女神的男朋友，要是没有，显示null
select name,boyName
from beauty
left join boys
on beauty.boyfriend_id=boys.id;

2.子查询

含义：

一条查询语句中又嵌套了另一条完整的select语句，其中被嵌套的select语句，称为子查询或内查询
在外面的查询语句，称为主查询或外查询

特点：

1、子查询都放在小括号内
2、子查询可以放在from后面、select后面、where后面、having后面，但一般放在条件的右侧
	       select后面：仅仅支持标量子查询
	       from后面：支持表子查询
	       where或者having后面：标量子查询，列子查询，行子查询
	       exists后面：表子查询
3、子查询优先于主查询执行，主查询使用了子查询的执行结果
4、子查询根据查询结果的行数不同分为以下两类：
① 单行子查询
	结果集只有一行
	一般搭配单行操作符使用：> < = <> >= <= 
	非法使用子查询的情况：
	a、子查询的结果为一组值
	b、子查询的结果为空
	
② 多行子查询
	结果集有多行
	一般搭配多行操作符使用：any、all、in、not in
	in： 属于子查询结果中的任意一个就行
	any和all往往可以用其他查询代替

2.1 where或者having后面

标量子查询（单行子查询）
列子查询（多行子查询）
行子查询（多列多行）

特点：
子查询一般放在小括号内；
子查询一般放在条件的右侧；
标量子查询，一般搭配着单行操作符使用>< >= <= = <>
列子查询一般搭配着多行操作符使用in、any、some、all

#查询比Abel工资高的员工名和工资
select last_name,salary
from employees
where salary>(
		select salary 
		from employees 
		where last_name="Abel"
);

#查询job_id与141号员工相同，salary比143号员工多的员工的姓名，job_id和salary
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
);

#查询最低工资大于50号部门最低工资的部门id和工资
select department_id,min(salary)
from employees
group by department_id
having min(salary)>(
		select min(salary)
		from employees
		where department_id=50
);

#查询location_id是1400或者1700的部门中的所有员工的姓名
select last_name
from employees
where department_id in (
		select department_id
		from departments
		where location_id in (1400,1700)
);

3.分页查询

应用场景：

实际的web项目中需要根据用户的需求提交对应的分页查询的sql语句

语法：

select 字段|表达式,...
from 表
【where 条件】
【group by 分组字段】
【having 条件】
【order by 排序的字段】
limit 【起始的条目索引，】条目数;

特点：

1.起始条目索引从0开始

2.limit子句放在查询语句的最后

3.公式：select * from  表 limit （page-1）*sizePerPage,sizePerPage
假如:
每页显示条目数sizePerPage
要显示的页数 page

#查询前五条员工信息
select *
from employees
limit 0,5;

#查询第11-25条
select *
from employees
limit 10,25;

#查询有奖金的，工资前十名的员工姓名
select last_name,salary
from employees
where commission_pct is not null
order by salary desc
limit 10;

4.联合查询

引入：
union 联合、合并

语法：

select 字段|常量|表达式|函数 【from 表】 【where 条件】 union 【all】
select 字段|常量|表达式|函数 【from 表】 【where 条件】 union 【all】
select 字段|常量|表达式|函数 【from 表】 【where 条件】 union  【all】
.....
select 字段|常量|表达式|函数 【from 表】 【where 条件】

特点：

1、多条查询语句的查询的列数必须是一致的
2、多条查询语句的查询的列的类型几乎相同
3、union代表去重，union all代表不去重

#查询员工的名字中，邮箱中含有a，并且部门号大于90的员工信息
select * from employees where last_name like "%a%"
union
select * from employees where email like "%a%"
union 
select * from employees where department_id>90;