数据库常见的查询

例子1：查询课程总量（不重复）

select count(distinct courseName) from 表名;

例子2：查询平均工资

select avg(salary) from 表名;

例子4：查询每个工种的最高工资

select max(salary),job_id,job_name from 表名 group by job_id;

例子5：查询每个位置上的部门个数

select count(*),location_id from department group by location_id;

例子6：查询哪个部门的员工个数>=2

(1)查询每个部门的员工个数

select count(*),department_id from 表名 group by department_id;

(2)根据一的结果进行筛选，查询哪个部门员工个数>=2

select count(*),department_id from 表名 group by department_id having count(*)>=2;

例子7：查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资

(1)查询每个工种有奖金的员工的最高工资

select max(salary),job_id from 表名 where commission_pct is not 'null' group by job_id;

(2)根据一的结果进行筛选，筛选出最高工资>12000

select max(salary),job_id from 表名 where commission_pct is not 'null' group by job_id having max(salary)>12000;

例子8：按多个字段进行分组

题目：查询每个部门每个工种的员工的平均工资

select avg(salary),department_id,job_id from group by department_id,job_id;

八、连接查询

1）分类

内连接（inner join）、外连接（左和右）、交叉连接（cross join）、全连接（full join）

2）内连接（inner join）

特点：取两张表查询结果的交集

分类：内连接又分为等值连接、非等值连接和自连接

补充：内连接的inner join中的inner可以省略。

1.等值连接

语法：

select tab1.col1，tab2.col2 from tab1，tab2 where tab1.id =tab2.id;

select tab1.col1，tab2.col2 from tab1 inner join tab2 ON tab1.id =tab2.id;

例子1：查询哪个部门的员工数大于等于3的部门名和员工个数，并按个数降序。

select count(*) as 个数,department_name from employee e

inner join department d ON e.department_id =d.department_id

group by department_id having count(*)>=3 order by count(*) desc;

例子2（三表连接）：查询员工名、部门名、工种名，并按部门名降序

select name,department_name,job_tietle from employee as e

inner join department as d on e.department_id =d.department_id

inner join jobs as j ON e.job_id =j.job_id

order by department_name desc;

2.非等值连接

例子：

select e.name,j.grade_level from employee as e inner jonin job_grade as j ON e.salary between j.lowest_sal and highest_sal;

3.自连接

一张表自己连接自己

例子：查询员工名和其上级的名称。

select e.name,m.name from employee as e inner join employee as m on e.mannager_id =m.id;

3）外连接（left、right）

左外连接：left outer join（特点：查询结果以左边表（主表）为准。左边表查出来有几条数据，一共就有几条数据）

右外连接：right outer join（特点：查询结果以右边表（主表）为准。右边表查出来有几条数据，一共就有几条数据）

4）交叉连接，又称“笛卡尔连接”或"笛卡尔积"

假如A表中的数据为m行，B表中的数据有n行，那么A和B做笛卡尔积，结果为m*n行

例子：

表1
id1	name1
1	张一
2	李二

表2
id2	name2
1	zs
2	ls
3	ww

查询SQL：select * FROM 表1 CROSS JOIN 表2;

查询结果
id1	name1	id2	name2
1	张一	1	zs
1	张一	2	ls
1	张一	3	ww
2	李二	1	zs
2	李二	2	ls
2	李二	3	ww

5）全连接full join

特点：取两张表查询结果的并集

注意：mysql不支持full join，执行会报错

例子：

表1
id1	name1
1	张一
2	李二
5	王五

表2
id2	name2
1	zs
2	ls
3	ww

查询SQL：select * FROM 表1 FULL JOIN 表2;

查询结果
id1	name1	id2	name2
1	张一	1	zs
2	李二	2	ls
5	王五	null	null
null	null	3	22

九、子查询

1）外查询和内查询、关联子查询

查询语句分为：外(父)查询（主查询）、内查询（子查询）

外查询仅使用内查询的最终结果，外查询和内查询没关联。

关联子查询：内查询会引用外查询的对象。比如select name,age from student where sid<=3 as sResult and age>(select age from sResult limit 1);

2）子查询分类

1.按返回结果分类

标量子查询(单行子查询)：1行1列
列子查询(多行子查询)：1列多行
行子查询：1行多列
表子查询：多行多列

2.按位置分类

select后from前：标量子查询
from后where前：表子查询
where后：标量子查询、列子查询、行子查询

3.按执行顺序分类

可分为相关(关联)子查询、不相关(非关联)子查询。

（1）相关子查询：先执行主查询，再执行子查询

标量子查询中：where 20>(子查询语句);

多行子查询中：exists

（2）不相关子查询：先执行子查询，再执行主查询

标量子查询中：where 列名>（子查询语句）

多行子查询中：in、all、some(any)

3)子查询的操作符

1.标量子查询、行子查询

（1）操作符有哪些

>、=、和!=、between and

（2）例子

标量子查询：

例子1：查询工资最少的员工的基本信息

select * from employee where salary=(select min(salary) from employee);

例子2：查询最低工资高于4号部门的最高工资的部门id和其最低工资

select depart_id,min(salary) from employee

group by depart_id

having min(salary)>(select max(salary) from employee where depart_id=4);

行子查询：

例子1：查询公号最小且工资最高的员工的基本信息

SELECT *FROM employee WHERE (employee_id,salary)=(SELECT MIN(employee_id),MAX(salary) FROM employees);

2.列子查询

（1）操作符有哪些

in和not in、some(any)、all、exists和not exists

（2）in

等于子查询结果的任意一个值

（3）any（some）

和子查询结果中的任意一个比较

注意：=any相当于in，<>any相当于not in

（4）all

和子查询结果中的所有值比较

（5）exists

检查子查询结果是否为空。存在数据就返回值True，否则返回False

注意：和and一起用的情况特殊，例子如下:

查询 course 表中是否存在 id=1 的课程，如果存在，就查询出 student 表中 age 字段大于 24 的记录。

SELECT * FROM student

WHERE age>24 AND EXISTS(SELECT course_name FROM course WHERE id=1);

（6）补充说明

列子查询的all和any（some）可以被单行子查询替代

例子1：返回比job_id为“IT_ProG”任一工资低的员工

select name from employee where salary<any(select distinct salary from employee where job_id='IT_ProG');

可以替换为

select name from employee where salary<(select max(salary) from employee where job_id='IT_ProG');

例子2：返回比job_id为“IT_ProG”所有工资低的员工

select name from employee where salary<all(select distinct salary from employee where job_id='IT_ProG');

可以替换为

select name from employee where salary<(select min(salary) from employee where job_id='IT_ProG');