表的关系

一对一

很少用

一对多

创建外键，一个人对应多个车，但是一个车只能属于一个人

多对多

  1. 创建老师表
  2. 创建学生表
  3. 创建学生与老师的关联表——>避免出现大量冗余数据
    1. create table teachers(
    2.  id int primary key auto_increment,
    3.  name varchar(50)
    4. );
    5. create table students(
    6. id int primary key auto_increment,
    7. name varchar(50)
    8. );
    9. alter table students,teachers character set utf8;
    10. create table tea_stu_rel(
    11. tid int,
    12. sid int,
    13. constraint foreign key(tid) references teachers(id),
    14. constraint foreign key(sid) references students(id)
    15. );

    连接查询

笛卡尔集

设A、B是两个非空集合，任给x∈A，y∈B，将它们组成一个有序对(x，y)，把这种有序对作为新的元素，这些元素的全体组成一个新的集合，称为集合A与集合B的笛卡儿乘积集合，记作A×B。即A×B={(x,y)|x∈A且y∈B}
例 A={1，2}，B={0，1}
A×B={(1，0),(1，1)，(2，0)，(2，1)}，
B×A={(0，1)，(0，2)，(1，1)，(1，2)}，
显然，A×B≠B×A。

  1. select * from students,scores; #直接产生笛卡尔集

image.png

  1. select * from students,scores where students.id=scores.sid; #通过外键建立联系，输出外键一致的结果

image.png
注：查询的时候并不需要强制设置外键

内连接

表后面是跟着的是别名，可用来做简写

  1. select stu.id,stu.name,sc.sid,sc.course,sc.score from students stu inner join scores sc on stu.id=sc.sid;

image.png

左连接

左边表的数据全部查出来，而右边表的信息只查询满足条件的数据

  1. select stu.id,stu.name,sc.sid,sc.course,sc.score from students stu left join scores sc on stu.id=sc.sid;

image.png

右连接

右边表的数据全部查出来，而左边表只查询满足条件的数据

  1. select stu.id,stu.name,sc.sid,sc.course,sc.score from students stu right join scores sc on stu.id=sc.sid;

image.png

多表联查

查询多对多的表关系
表car有2个外键：
image.png

  1. select p.name,c.name,c.color,c2.name from car c ,person p ,city c2 where c.pid = p.id and c.cid = c2.id;

image.png

  1. select p.name,c.name,c.color,c2.name from car c join person p on c.pid = p.id
  2. join city c2 on c.cid = c2.id;

image.png

  1. select e.name,e.salary,d.name,l.grade from employees e,departments d,`level` l
  2. where e.depid=d.id 
  3. and e.salary between l.lowSalay and l.highSalay;

image.png

子查询

select中还有其它的select

  1. 选择和鲁班在同一个经理手下的：

    1. select name,mangerid from employees where mangerid=(select mangerid from employees where name='鲁班');

    image.png

  2. 选择薪资大于售后部所有人的员工信息

    1. select name,salary from employees e 
    2. where salary > (select max(salary) from employees e2 where depid=6);

    image.png

  3. 查询（工作、薪资）和妲己相同的员工

    1. select * from employees e where (salary,job) in (select salary,job from employees e where name='妲己');

    image.png

  4. 查询有2个员工及其以上的经理的信息

    1. select * from mangers m where id in
    2. (select group_concat(mangerid) from employees e group by mangerid having count(mangerid)>=2);

    image.png

  5. 查询编号为8的员工的姓名，薪水，部门名称和部门所在地（查询2个表）

    1. select e.name,e.salary,d.name,d.`local` from employees e ,departments d where e.depid = d.id and e.id =8;

    image.png