数据库关系与查询详解-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_42569562/article/details/82827665

一：“一对多”关系

在“多”方维护关系，具体地说，是在“多”方表中创建外键 (Foreign key)关联 “一”方表中的主键。创建外键的语法： [constraint 外键约束名称] foreign key(外键字段) references 主表名称(主表主键)

1.先创建“一”方学校表
  create table schools(
    id int primary key auto_increment,
    school_name  varchar(20) not null,
    address varchar(30),
    history  text
  );

2.再创建"多"方学生表
  create table students(
     id int primary key auto_increment,
     name  varchar(20) not null,
     age   int,
     sex   varchar(10) not null,
     school_id int,
     constraint fk_students foreign key(school_id) references schools(id)
        on delete cascade on update cascade
  );

3. 插入学校表数据
   insert into schools(school_name,address,history)values
        ('清华大学','北京','清华大学是一所很古老的大学...'),
        ('北京大学','北京',null),
        ('西安交大','西安','西安交大是陕西省最好的大学...');

4. 插入学生表数据
   insert into students(name,age,sex,school_id)values('令狐冲',20,'男',1),
   ('马云',25,'男',2),('马化腾',22,'男',2),('李开复',28,'男',1);

5. 查询3号学生的姓名、年龄、性别、学校名称
    insert into students(name,age,sex,school_id)values('令狐冲',20,'男',1),
   ('马云',25,'男',2),('马化腾',22,'男',2),('李开复',28,'男',1);


6. 查询1号学校的所有学生       
   select * from students where school_id=1;

6. 查询1号学校的所有学生,以及他们关联的学校名称
    select  students.name,students.age,students.sex,schools.school_name 
      from schools,students where schools.id=students.school_id 
        and schools.id=1;

二： “一对一”关系

在某一方创建“唯一外键”关联另一方。使用"unique"关键字创建唯一约束

1.先创建person表
create table person(
   id  int primary key auto_increment,
   name   varchar(20) not null,
   sex  varchar(10) not null
);

2.再创建IDCard表
create table IDCard(
   cardid   varchar(20)  primary key,
   address  varchar(20) not null,
   person_id int unique,
   constraint fk_idcard foreign key(person_id) references person(id)
);

3.插入相关数据
  insert into person(name,sex)values('张三','男'),('李四','男')
     ,('张红','女'),('令狐冲','男');
  insert into IDCard(cardid,address,person_id)values('zs123456','北京',1),
      ('ls123456','西安',2),('zh123456','上海',3);

4.查询1号人对应的卡号
  select person.name,IDCard.cardid from person,IDCard 
     where person.id=IDCard.person_id and person.id=1;

三：“多对多”关系

通过第三方中间表维护“多对多”的关系，中间表至少需要两个外键字段，分别关联于两个“多”方表。

1.创建成员表
  create table member(
     id int primary key auto_increment,
    nickname  varchar(20)   not null,
    sex    varchar(10)  not null
  );


2. 创建QQ群表
   create table qq(
      id int primary key auto_increment,
      qqname   varchar(20)   not null
   );

3. 创建第三方中间表
   create table member_qq(
      member_id   int,
      qq_id  int,
      level   int,
      foreign key(member_id) references member(id),
      foreign key(qq_id) references qq(id),
      primary key(member_id,qq_id)
   );

4. 插入相关数据
   insert into member(nickname,sex)values('清晨','男'),
        ('云淡风轻','男'),('道法自然','女');
   insert into qq(qqname)values('Python交流群'),
        ('读书分享群'),('美食群'),('绘画学习交流群');

   insert into member_qq(member_id,qq_id,level)values(1,1,10),
        (1,2,5),(1,4,6),(2,2,5),(3,2,6);

5. 查询1号成员的昵称与其加入的群名
   select member.nickname,qq.qqname from member,qq,member_qq
     where member.id=member_qq.member_id and member_qq.qq_id=qq.id
           and member.id=1;

四：连接查询

1. 等值连接

select 字段名1,字段名2,.... from 表A,表B,... where 连接条件 [其他过滤条件]

2.内连接(inner join)

select 字段名1,字段名2,.... from 表A inner join 表B on 连接条件 [其他过滤条件]

3.外连接

1. 左外连接(left join) select 字段名1,字段名2,.... from 表A left join 表B on 连接条件 [其他过滤条件] 注意：左外连接左表的数据全部查询出来，右表数据只有满足连接条件和其他过滤条件才能查询出来。
右外连接(right join)select 字段名1,字段名2,.... from 表A right join 表B on 连接条件 [其他过滤条件]注意：右外连接右表的数据全部查询出来，左表数据只有满足连接条件和其他过滤条件才能查询出来。
1. 使用内连接查询2号学生对应的学校
   select students.name,students.sex,schools.school_name 
       from students inner join schools on
       students.school_id=schools.id and students.id=2;

2. 使用左外连接查询人的姓名与他的身份证号,要求将所有人的信息都查出来
   select person.name,IDCard.cardid from person left join IDCard
      on person.id=IDCard.person_id;

3. 将所有成员与他加入的QQ群查询出来
   select member.nickname,qq.qqname from member inner join member_qq
     on member.id=member_qq.member_id 
     inner join qq on member_qq.qq_id=qq.id;


   select member.nickname,qq.qqname from qq inner join member_qq
      on member_qq.qq_id=qq.id
       inner join member on member.id=member_qq.member_id;

七：子查询（嵌套查询）

子查询的查询结果集经常用作外部查询的条件。应该使用括号将子查询括起来。子查询的常用关键字： 1. in 用来检查字段值是否在子查询结果集中 2. all 用来判断字段值是否满足子查询结果集中所有的记录 3. any 用来判断字段值是否满足子查询结果集中任何一条记录

CREATE TABLE emp(
    empno         INT  primary key auto_increment,
    ename         VARCHAR(50),
    job       VARCHAR(50),
    mgr       INT,
    hiredate      DATE,
    sal       DECIMAL(7,2),
    comm          DECIMAL(7,2),
    deptno        INT
);

INSERT INTO emp VALUES(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO emp VALUES(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO emp VALUES(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO emp VALUES(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO emp VALUES(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO emp VALUES(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO emp VALUES(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
INSERT INTO emp VALUES(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20);
INSERT INTO emp VALUES(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO emp VALUES(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
INSERT INTO emp VALUES(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20);
INSERT INTO emp VALUES(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
INSERT INTO emp VALUES(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
INSERT INTO emp VALUES(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);


INSERT INTO emp VALUES(9527,'SCOTT','CLERK',7566,'1989-05-20',3000,NULL,10);
INSERT INTO emp VALUES(6789,'JONES','MANAGER',7839,'1981-04-02',1500,NULL,20);
INSERT INTO emp VALUES(9999,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
问题：

-- 查询与SCOTT同一个部门的员工。
   select * from emp where deptno in (select deptno from emp where ename='SCOTT');
   
-- 工资高于JONES的员工。
   select * from emp where sal > all (select sal from emp where ename='JONES');
     
   
-- 工资高于30号部门所有人的员工信息。
   方法一：
   select * from emp where sal>(select max(sal) from emp where deptno=30);
   
   方法二：
   select * from emp where sal>all(select sal from emp where deptno=30);

   方法三：
   select * from emp where sal>(select max(sal) from emp group by deptno having deptno=30);
   
   
-- 查询工作和工资与MARTIN（马丁）完全相同的员工信息。
   select * from emp where (job,sal) in (select job,sal from emp where ename='MARTIN');
   
   
-- 查询佣金comm不为null的记录。
   select * from emp where comm is not null;
     

-- 在emp表中查询，要求查询每个员工的姓名与他对应的上司的名字
   
   select  worker.ename as 员工姓名,manager.ename as 经理姓名 from emp as worker left join emp as manager
      on worker.mgr=manager.empno;

八：合并查询结果集(union)

select 字段1,字段2,... from 表A union[all] select 字段1,字段2,... from 表B;

注意点：
合并表的字段数量应该一样，对应的数据类型应该匹配

查询结果集的字段名以第一个表为准,所以排序时应该以第一个表的字段名为标准
如果查询结果集中包含重复的记录，则union会自动去重，而union all会将所有记录都查询出来（包含重复记录）。
1.创建business表
  create table business(
     id int primary key auto_increment,
     name varchar(20) not null,  
     money double
  );

2.创建工人表
  create table worker(
     id int primary key auto_increment,
     workername    varchar(10) not null,  
     sal  double
  );

3. 插入相关记录
  insert into business(name,money)values('马云',100),
       ('刘强东',300),('李嘉诚',800),('比尔盖茨',200);

  insert into worker(workername,sal)values('张三',2000),
       ('李四',3000),('王五',1800),('王五',1800),('王五',1800);

4. 合并business表与worker表的查询结果集
   select name,money from business
   union
   select workername,sal from worker;

5. 合并business表与worker表的查询结果集,并根据money降序排序
   select name,money from business
   union
   select workername,sal from worker 
   order by money desc;

MySQL学习笔记02