WEB——06.MySQL(3)

WEB——06.MySQL(3)

子查询(嵌套查询)

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

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

• 查询工资最高的员工信息

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

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

  select * from emp where job=(select job from emp where name=“孙悟空”) and name!=“孙悟空”;

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

​ 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 ;

关联关系

• 只一个项目中创建的表和表之间存在的业务关系
• 关系如下：
  • 一对一：有AB两张表，A表中一条数据对应B表的一条数据，B表中一条数据对应A表的一条数据。
  • 一对多：有AB两张表，A表中一条数据对应B表的多条数据，B表中一条数据对应A表的一条数据。
  • 多对多：有AB两张表，A表中一条数据对应B表的多条数据，B表中一条数据对应A表的多条数据。

表和表之间如何建立关系？

• 一对一：在AB任意一张表里添加一个建立关系的字段 指向另外一张表的主键

• 一对多：在一对多的两张表中，在“多”的表里面添加建立关系的字段 指向另外一张表的主键

• 多对多：创建一个单独的关系表，表里面有两个字段指向另外两个表的主键

• 多对多举例: 苍老师: 小红,小花,小绿 传奇哥: 小花,小绿
  create table t(id int primary key auto_increment,name varchar(20));
  create table s(id int primary key auto_increment,name varchar(20));
  create table t_s(tid int ,sid int);
  insert into t values(null,‘苍老师’),(null,‘传奇哥’);
  insert into s values(null,‘小红’),(null,‘小花’),(null,‘小绿’);
  insert into t_s values(1,1),(1,2),(1,3),(2,2),(2,3);

​ 老师表:t
​ 学生表:s
​ 关系表:t_s

• 查询每个老师名字和对应的学生姓名

  select t.name,s.name
  from t join t_s ts on t.id=ts.tid join s on s.id=ts.sid;

• 查询苍老师的学生姓名

select s.name from t join t_s ts on t.id=ts.tid join s on s.id=ts.sid where t.name=“苍老师”;

关联查询

• 查询存在关联关系的表的查询方式称为关联查询

• 关联查询的方式包括：等值连接，内连接，外连接

等值连接

• 格式：select * from A,B where 关联关系 and 其他条件；
• 查询工资高于2000的员工的姓名和对应的部门名

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

• 查询程序员的姓名，工资，部门名，部门地点

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

内连接

• 等值连接和内连接查询到的是一样的数据，推荐使用内连接

• 格式：select * from A join B on 关联关系 where 其他条件；

• 查询工资高于2000的员工姓名和对应的部门名

  ​ select e.name ,d.name from emp e inner join dept d on e.dept_id = d.id where sal>2000;

• 查询程序员的姓名，工资，部门名，部门地点

  select e.name,e.sal,d.name,d.loc fom emp e join dept d on e.dept_id=d.id where job=“程序员”;

外连接

• 等值连接和内连接查询到的是两个表的交际数据

• 外连接查询到的是一张表的全部和另外一张表的交集

• 格式：select * from A left/rigth join B on 关联关系 where 其他条件；

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

  select e.name d.name from emp e left join dept d on e.dept_t=d.id;

• 查询所有部门的名字，地址，和对应的员工姓名，工资

  select d.name d.loc e.name,e,sal from emp e right join dept d on e.dept_id=d.id;

关联查询总结

• 如果查询的数据是两个表的交际数据，使用等值连接或内连接(推荐)
• 如果查询的是一张表的全部和另外一张表的交际则使用外连接
  pt d on e.dept_id=d.id;

关联查询总结

• 如果查询的数据是两个表的交际数据，使用等值连接或内连接(推荐)
• 如果查询的是一张表的全部和另外一张表的交际则使用外连接