Mysql连接和子查询

本文主要是介绍Mysql连接和子查询，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

Sql92语法：仅仅支持内连接

①等值连接

②非等值连接

③自连接

Sql99语法(推荐使用)：支持内连接+外连接+交叉连接

按功能分类：

内连接：

1.等值连接

2.非等值连接

3.自连接

外连接：

1.左外连接

2.右外连接

3.全外连接(Mysql不支持)

交叉连接：

Sql92标准：

语法： select 字段1,字段2 from 表1 t1,表2 t2 where 条件;

1.等值连接

案例1：查询员工名和对应的部门名
SELECT last_name,department_name from employees e,departments d WHERE e.department_id=d.department_id;

案例2：查询员工名，工种号，工种名

SELECT last_name,job_title,j.job_id FROM employees e,jobs j where e.job_id=j.job_id;

employees表中也有job_id而jobs表中也有job_id，会有歧义，因此需要指明是哪个表中的job_id

案例3：查询有奖金的员工名、部门名

SELECT last_name,department_name FROM employees e,departments d WHERE e.department_id=d.department_id AND commission_pct is not null;

可以跟筛选：

案例4：查询城市名中第二个字母为o的部门名和城市名

SELECT department_name,city from departments d,locations l WHERE d.location_id=l.location_id AND city LIKE '_o%';

可以跟分组：

案例5：查询每个城市的部门个数

SELECT count(*),city from departments d,locations l WHERE d.location_id=l.location_id GROUP BY city;

可以跟排序：

案例6：查询每个工种的工种名和员工的个数，并且按员工个数降序

SELECT job_title,COUNT(*) FROM employees e,jobs j WHERE e.job_id=j.job_id GROUP BY job_title ORDER BY COUNT(*) desc;

可以实现多表的连接：

案例7：查询员工名、部门名和所在的城市

SELECT last_name,department_name,city FROM employees e,departments d,locations l where e.department_id=d.department_id AND d.location_id = l.location_id;

2.非等值连接

案例1：查询员工的工资和工资级别

SELECT salary,grade_level FROM employees e,job_grades jg WHERE e.salary BETWEEN lowest_sal AND highest_sal;

3.自连接(顾明思议自己连接自己)

案例：查询员工的名称和上级的名称

SELECT e.last_name,m.last_name FROM employees e,employees m WHERE e.manager_id=m.employee_id;

1. 显示所有员工的姓名，部门号和部门名称

SELECT last_name,d.department_id,department_name FROM employees e,departments d where e.department_id=d.department_id;

2.查询90号部门员工的job_id和90号部门的location_id

SELECT job_id,location_id FROM employees e,departments d WHERE e.department_id=d.department_id AND d.department_id='90';

3.查询所有有奖金的员工的last_name,depart_name,location_id,city

SELECT last_name,department_name,l.location_id,city FROM employees e,departments d,locations l WHERE e.department_id=d.department_id AND d.location_id=l.location_id and commission_pct is not null;

4.选择city在Toronto工作的员工的last_name,job_id,department_id,department_name

SELECT last_name,job_id,d.department_id,department_name,city FROM employees e,departments d,locations l WHERE e.department_id=d.department_id AND d.location_id=l.location_id AND city='Toronto';

5.查询每个工种、每个部门的部门名、工种名和最低工资

SELECT job_title,department_name,MIN(salary) FROM employees e,departments d,jobs j WHERE e.department_id=d.department_id AND e.job_id=j.job_id GROUP BY department_name,job_title;

6. 查询每个国家下的部门个数大于2的国家编号

SELECT country_id,COUNT(*) FROM departments d,locations l WHERE d.location_id=l.location_id GROUP BY country_id HAVING COUNT(*)>2;

7. 选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号

SELECT e.last_name employees,e.employee_id 'Emp#',m.last_name 'Manager',m.employee_id 'Mgr#' FROM employees e,employees m where e.manager_id=m.employee_id;

总结：
           1.多表连接的结果是它们之间的交集
           2.n个表连接需要n-1个条件
           3.一般要为表起别名
           4.可以跟分组函数、分组查询、排序等组合使用

Sql99语法：

语法：

select 查询列表 from 表1 别名 [ 连接类型] join 表2 别名 on 连接条件

[ where 筛选条件] [ group by 分组] [having 筛选条件] [order by 排序列表]

分类：

1.内连接：inner join

2.外连接：

①左外连接:left [outer] join

②右外连接:right [outer] join

③全外连接:full [outer] join

3.交叉连接:cross join

内连接：

1.等值连接

案例1：查询员工名、部门名

SELECT last_name,department_name FROM employees e INNER join departments d on e.department_id=d.department_id;

案例2：查询名字中包含e的员工名和工种名(刷选)

SELECT last_name,job_title FROM employees e INNER JOIN jobs j on e.job_id=j.job_id WHERE job_title LIKE '%e%';

案例3：查询部门个数>3的城市名和部门个数

SELECT city,COUNT(*) FROM departments d LEFT JOIN locations l ON d.location_id=l.location_id GROUP BY city HAVING COUNT(*)>3;

案例4：查询那个部门的部门员工个数>3的部门名和员工个数，并按个数降序(排序)

SELECT COUNT(*),department_name FROM employees e LEFT JOIN departments d on e.department_id=d.department_id GROUP BY department_name ORDER BY COUNT(*) desc;

案例5：查询员工名，部门名、工种名，并按部门名降序

SELECT last_name,department_name,job_title FROM employees e LEFT JOIN departments d on e.department_id=d.department_id LEFT JOIN jobs j on e.job_id=j.job_id ORDER BY department_name desc;

2.非等值连接

案例：查询工资级别个数>2的个数，并且按工资级别排序

SELECT COUNT(*),grade_level FROM employees e INNER JOIN job_grades jg on salary BETWEEN lowest_sal AND highest_sal GROUP BY grade_level;

3.自连接

案例：查询员工的名字、上级的名字

SELECT e.last_name employees,m.last_name manager FROM employees e INNER JOIN employees m on e.manager_id=m.employee_id;

外连接：

1.查询主表中的所有记录，如果从表有和它匹配的则显示，如果没有和它匹配的则显示null。

2.查询结果=内连接结果+主表中有从表中没有的记录

左外连接：left左边的是主表

案例1：查询那个部门没有员工

SELECT department_name FROM departments d LEFT JOIN employees e on d.department_id=e.department_id where e.employee_id is null;

右外连接：right右边的是主表

全外连接：mysql暂时不支持，查询结果=内联结果+表1中有表2中没有+表2中有表1中没有的

交叉连接：笛卡尔积

1.SELECT beauty.*,boys.boyName FROM beauty LEFT JOIN boys on beauty.boyfriend_id = boys.id WHERE beauty.id>3;

2.SELECT DISTINCT city,department_id FROM locations l LEFT JOIN departments d on d.location_id=l.location_id WHERE department_id is NULL;

3.SELECT e.*,department_name FROM employees e LEFT JOIN departments d on e.department_id=d.department_id WHERE department_name in ('SAL','IT');

子查询：

含义：出现在其他语句中的select语句，被称为一个子查询，外围的查询语句被称为主查询或者外查询

按子查询出现的位置进行一个分类：

1.放在select后面，只能是标量子查询(一行一列)

2.放在from后面，作为数据源

支持表子查询

3.放在where/having后面

标量子查询

行子查询

列子查询

4.放在exists后面(相关子查询)

表子查询

按结果集行列数不同进行一个分类：

1.标量子查询(一行一列)

2.列子查询(一列多行)

3.行子查询(一行多列)

4.表子查询(结果集)

一、where/having后面的子查询

特点：

①使用一对()包含

②一般放在条件的右侧

③标量子查询一般搭配单行操作符(<,>,<=,>=,<>)使用

④列子查询一般搭配多行操作符(in,any/some,all)使用

#标量子查询

案例1：谁的工资比Abel高

SELECT last_name FROM employees where salary>(SELECT salary FROM employees WHERE last_name='Abel');

案例2：返回job_id与141员工相同，salary比143号员工多的员工姓名，job_id和工资

SELECT 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：返回公司工资最少的员工的last_name,job_id和salary

SELECT last_name,job_id,salary FROM employees WHERE salary=(SELECT MIN(salary) FROM employees);

案例4：查询最低工资大于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');

列子查询：

一般与in，any/some，all搭配使用

案例1：返回location_id是1400或1700的部门中的所有员工姓名

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

SELECT employee_id,last_name,job_id,salary FROM employees WHERE salary <(SELECT MIN(salary) FROM employees WHERE job_id='IT_PROG');

行子查询：

案例1：查询员工编号最小并且工资最高的员工信息

SELECT e.* FROM employees e where (employee_id,salary)=(SELECT MIN(employee_id),MAX(salary) FROM employees);

二、放在select后面的标量子查询

案例1：查询每个部门的员工个数
SELECT d.*,(SELECT COUNT(*) FROM employees e WHERE e.department_id = d.department_id ) FROM departments d;

案例2：查询员工号为102的部门名

SELECT (SELECT d.department_name FROM departments d LEFT JOIN employees e on e.department_id=d.department_id WHERE e.employee_id='102');

三、放在from后面

案例1：查询每个部门的平均工资的工资等级

SELECT grade_level,mm.d_id,mm.ag_sal FROM job_grades,(SELECT avg(salary) ag_sal,department_id d_id FROM employees GROUP BY department_id) mm WHERE mm.ag_sal BETWEEN lowest_sal AND highest_sal;

案例2： 查询员工号为102的部门名

SELECT d.department_name FROM departments d,(SELECT department_id d_id FROM employees WHERE employee_id='102') mm WHERE d.department_id=mm.d_id;

四、exists后面(相关子查询)

案例1：查询所有员工的部门名

SELECT DISTINCT department_name FROM departments d WHERE EXISTS (SELECT * FROM employees e WHERE e.department_id=d.department_id);

1.查询和Zlotkey相同部门的员工姓名和工资

SELECT last_name,salary FROM employees WHERE department_id = (SELECT department_id FROM employees WHERE last_name='Zlotkey');

2.查询工资比公司平均工资高的员工的员工号，姓名和工资

SELECT employee_id,last_name,salary FROM employees WHERE salary>(SELECT avg(salary) FROM employees);

3.查询各部门中工资比本部门平均工资高的员工的员工号，姓名和工资

SELECT e.employee_id,e.last_name,e.salary FROM employees e,(SELECT avg(salary) ag_sal,department_id FROM employees GROUP BY department_id) mm WHERE mm.department_id =e.department_id AND salary > mm.ag_sal;