相信的心就是你的魔法

02.外键约束和高级查询

发表于 更新于 分类于
本文字数： 3.8k 阅读时长 ≈ 3 分钟

1.E.R实体关系图

E.R实体关系图是通过图标的形式来表示数据库中表和字段以及表和表之间的关系。

表和表之间的关系主要有四种：一对一，一对多，多对一，多对多

E.R实体关系图示例

2.外键约束

什么是外键约束

外键约束就是：让字段的值的取值范围在另外一张表的主键中

怎么添加外键约束

1）保证当前表中有一个字段能够保存另外一张表的主键
2）添加外键约束

不同的对应关系，外键的添加的要求（方式）不同：

  • 一对一：可以添加到任意一张表中
  • 一对多和多对一：添加到多对应的那张表中，比如学生和学院的对应关系中（一个学生对应一个学院，而一个学院可以对应多个学生），外键应当添加到学生表中
  • 多对多：两张表之间不能直接建立多对多的对应关系，需要使用一张额外的表

3.怎么添加约束

3.1 创建表或者添加字段的时候直接在字段后面添加约束

3.2 通过修改表的表示添加和删除约束

  • 添加普通约束：alter table 表名 add constraint 约束索引名 约束名（字段）; – 给指定字段添加指定约束（只能添加唯一约束和主键约束）

    1
    alter table tb_student add constraint unique_collid unique(collid);

  • 删除普通约束：alter table 表名 drop index 约束索引名; – 删除指定约束

    1
    alter table tb_student drop index unique_collid;

  • 添加外键约束：alter table 表1 add constraint 约束索引名 foreign key（字段1） references 表2（字段2）; – 给表1中的字段1添加外键约束，并且字段1的值依赖表2中的字段2，字段1和字段2不能同名

    1
    alter table tb_student add constraint fk_collid_coll foreign key(collid) references tb_college(collid);

  • 创建的时候添加外键约束

    1
    2
    3
    4
    5
    6
    7
    8
    create table if not exists tb_test
    (
    tid int auto_increment,
    t_name varchar(20) not null,
    sid int,
    primary key(tid),
    foreign key(sid) references tb_teacher(teaid)
    );

  • 删除外键约束：alter table 表名 drop foreign key 外建索引名;

    1
    alter table tb_student drop foreign key fk_collid_coll;

  • 多对多关系的外键约束

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    create table if not exists tb_record
    (
    reid int auto_increment comment '选课记录编号',
    sid int comment '学生的外键',
    cid int comment '课程外键',
    redate date comment '选课日期',
    score float comment '分数',
    primary key(reid),
    foreign key(sid) references tb_student(stuid),
    foreign key(cid) references tb_course(couid)
    );

4. 高级查询

以下的查询操作全部基于下表：

建立E.R关系表

4.1 去重操作

select distinct 字段名 from 表名；

1
2
select distinct redate from tb_record order by redate;
select distinct sid from tb_record;	--	查询所有选课的学生的id

4.2 限制和分页

  • 限制：select * from 表名 limit N； – 查询的时候只获取前N条数据
    1
    select * from tb_record limit 5;
  • 偏移：select * from 表名 limit M offset N； – 跳过前N条数据获取M条数据（从第N+1条数据开始获取M条数据）
    写法二：select * from 表名 limit M,N； – 跳过前M条获取N条数据
    1
    2
    3
    select * from tb_record limit 7 offset 3;	--	跳过前3条获取7条数据
    select * from tb_record limit 3,7;	--	跳过前3条获取7条数据
    select * from tb_record order by score desc limit 3;	--	获取成绩前三的选课记录

4.3 聚合

max（），min（），sum（），avg（），count（） - mysql中的方法

1
2
3
4
5
select max(score)as max_score from tb_record;
select min(score)as min_score from tb_record;
select sum(score)as sum_score from tb_record;
select avg(score)as avg_score from tb_record;
select count(score)as c_score from tb_record;	--	注意：空值不参与求和、平均值的运算，也不参与个数统计

4.4 分组

select 聚合操作 from 表名 group by （字段）； - 按指定字段的值对表进行分组，然后对每个分组进行聚合操作。

注意：分组后，除了分组字段以外，其他字段只能进行聚合操作;
在分组后如果需要加条件，需要用having代替where

eg：获取每个学生的平均分

1
select sid,avg(score) from tb_record group by (sid);

每个学生平均分

获取每个学科的平均分

1
select cid,avg(score) from tb_record group by (cid);

每个学科平均分

获取每个学生选课数量

1
select sid,count(cid) from tb_record group by (sid);

4.5 子查询

将一个查询的结果作为另外一个查询的条件或者查询对象

第一种子查询：将查询结果作为另一个查询的条件

eg：获取成绩是最高分的所有学生的id

1
select sid,score from tb_record where score=(select max(score) from tb_record);

eg：获取最高的三个分数对应的所有的学生的id

1
2
3
4
5
select distinct score from tb_record order by score desc limit 3;
select sid,score from tb_record where score in (select distinct score from tb_record order by score desc limit 3);--注意：个别版本不支持limit的子查询操作

-- 获取分数前三的所有学生的id
select stu_id,score from tb_record where score in(select t.score from (select distinct score from tb_record order by score desc limit 3)as t);

eg：获取所有选了两门课程以上的学生的id

1
select count(cid) from tb_record group by (sid) having count(cid)>2 ;

eg：获取所有选了两门课程以上的学生的姓名

1
select stuname from tb_student where stuid in (select sid from tb_record group by (sid) having count(cid)>2);

第二种子查询：将一个查询的结果作为另一个查询的对象

注意：如果要将查询结果作为查询对象，那么查询结果对应的查询必须重命名

1
2
select * from tb_student limit 4,5;
select stuname from (select * from tb_student limit 4,5) as t1;	--	临时表必须重命名

4.6 连接查询

同时查询多张表的数据

  • 内连接

写法一：

select * from 表名1，表名2，表名3，… where 连接条件 查询条件；

注意：如果既有连接条件又有查询条件，查询条件必须放在连接条件后面

eg：查询所有学生的名字和对应的学院的名称

1
select stuname, collname from tb_student,tb_college where tb_student.collid=tb_college.collid;

eg：查询学生每个学科的成绩：xx（学生名字），xx（学科名），xx（分数）

1
select stuname,couname,score from tb_student,tb_course,tb_record where tb_student.stuid=tb_record.sid and tb_course.couid=tb_record.cid;

写法二：

select * from 表1 inner join 表2 on 表2连接条件 inner join 表3 on 表3连接条件 … where 查询条件；

注意：连接中，如果不写连接条件，最后会形成笛卡尔积的现象；在方法二中，如果存在中间表（衍生表），中间表要放在最前面。

  • 外连接

在mySQL中，外连接只支持左外连接（left join）和右外连接（right join）

表1 left join 表2：先将表1中的记录全部取出来，按连接条件去依次连接表2中的记录；如果表1中的记录在表2中不存在满足条件的记录，则该记录连接的结果为null。

表1 right join 表2：原理同上。

------ 本文结束------