数据库-基本的SQL(3)

子查询(嵌套查询)

• 举例

1. 查询工资高于 2 号部门平均工资的员工信息

select avg(sal) from emp where dept_id=2;select * from emp where sal>(select avg(sal) from emp where dept_id=2);

2. 查询工资高于程序员最高工资的员工信息

select max(sal) from emp where job=‘程序员’;

select * from emp where sal>(select max(sal) from emp where job=‘程序员’);

3. 查询工资最高的员工信息

select * from emp where sal=(select max(sal) from emp);

4. 查询和孙悟空相同工作的员工信息

select * from emp where job=(select job from emp where name=‘孙悟空’) and name!="孙

悟空";

5. 查询拿最低工资的员工同事们的信息(同事指同一部门)

select min(sal) from emp;

select dept_id from emp where sal=(select min(sal) from emp);

select * from emp where dept_id=(select dept_id from emp where sal=(select min(sal) from

emp)) and sal!=(select min(sal) from emp);

数值计算+ - * / %

• 举例

1. 查询每个员工的姓名,工资和年终奖(5 个月的工资)

select name,sal,5*sal 年终奖 from emp;

2. 给 3 号部门的员工每人涨薪 5 块钱

update emp set sal=sal+5 where dept_id=3;

表的关联查询

关联关系

• 创建表的时候,表和表之间存在的业务关系

• 有哪几种关系:

  • 一对一: 有 AB 两张表,A 表中的一条数据对应 B 表中的一条数据, 同时 B 表中的一条数

  据也对应 A 表中的一条数据.

  

  • 一对多:有 AB 两张表,A 表中的一条数据对应 B 表中的多条数据, 同时 B 表中的一条数据对应A表中的一条数据

  

  • 多对多:有 AB 两张表,A 表中的一条数据对应 B 表中的多条数据, 同时 B 表中的一条数据

    也对应 A 表中的多条数据.

• 如果表之间存在关联关系,如何建立关系?

  • 一对一: 在两张表的任意一张表中添加建立关系的字段指向另外一张表的主键

  • 一对多: 在多的表中添加建立关系的字段指向另外一张表的主键

  • 多对多: 新建一张关系表,表中至少有两个字段用于建立关系, 指向另外两张表的主

    键

关联查询

• 同时从多张表中查询数据的方式称为关联查询
• 包括三种查询方式:
  • 等值连接
  • 内连接
  • 外连接

等值连接

• 格式：select 字段信息 from A,B where A.xxx=B.xxx(关联关系) and 其他条件

1. 查询每个员工的姓名\工资和对应的部门名

select e.name,sal,d.name from emp e,dept d where e.dept_id=d.id;

2. 查询工资高于 2000 的员工姓名,工资和对应的部门名和地址

select e.name,sal,d.name,loc from emp e,dept d where e.dept_id=d.id and sal>2000;

3. 查询程序员的部门地址

select distinct loc from emp e,dept d where e.dept_id=d.id and job=“程序员”;

内连接

• 等值连接和内连接的作用一样,查询的都是两个表的"交集数据"(存在关系的数据)
• 格式：select 字段信息 from A join B on A.x=B.x where 其他条件

1. 查询每个员工的姓名\工资和对应的部门名

select e.name,sal,d.name

from emp e join dept d on e.dept_id=d.id;

2. 查询工资高于 2000 的员工姓名,工资和对应的部门名和地址

select e.name,sal,d.name,loc

from emp e join dept d on e.dept_id=d.id where sal>2000;

3. 查询程序员的部门地址

select distinct loc

from emp e join dept d on e.dept_id=d.id where job=“程序员”

外连接

• 外连接查询到的是一张表的全部和另外一张表的交集数据
• 格式：select 字段信息 from A left/right join B on A.x=B.x where 其他条件

1. 查询所有的员工姓名和对应的部门名

select e.name,sal,d.name

from emp e left join dept d on e.dept_id=d.id;

2. 查询所有部门名和对应的员工姓名

select d.name,e.name

from emp e right join dept d on e.dept_id=d.id;

关联查询总结:

• 如果需要同时查询多张表的数据使用关联查询
• 如果查询到的是两张表的交集数据使用等值连接或内连接(推荐)
• 如果查询的是一张表的全部和另外一张表的交集则使用外连接

dept d on e.dept_id=d.id;

关联查询总结:

• 如果需要同时查询多张表的数据使用关联查询
• 如果查询到的是两张表的交集数据使用等值连接或内连接(推荐)
• 如果查询的是一张表的全部和另外一张表的交集则使用外连接