MySQL学习笔记之基础操作(三) - 海筒研习社.SeaSilo.CN

1. 外键约束和表关联关系

• 约束：约束是一种限制，它通过对表的行或列的数据做出限制，来确保表的数据的完整怀、关联性
• foreign key功能：建立表与表之间的某种约束关系，由于这种关系的存在，能够让表与表之间的数据，更加完整，关连性更强。
• foreign key外键定义语法：[constraint symol] foreign key (外键字段)
  pefrerences tbl_name (主表主键)
  [on delete {restrict|cascade|set null|no action}]
  [on update {restrict|cascade|set null|no action}]
  # 该语法可在create table和alter table时使用注意：
  1. 并不是任何情况表关系都需要建立外键来约束，如果没有类似上面的约束关系时也可以不建立。
  2. 从表的外键字段数据类型与指定的主表主键应该相同。

• 通过外键名称解除外键约束alter table person drop foreign key dept_fk;
  # 查看外键名称
   show create table person;注意：删除外键后发现desc查看索引标志还在，其实外键也是一种索引，需要将外键名称的索引删除之后才可以。
• 级联动作
  • restrict(默认)：on delete restrict on update restrict
    • 主表删除记录时，若从表中有相关联记录则不允许主表删除
    • 主表更改主键字段值时，从表有相关记录则不允许更改
  • cascade: 数据级联更新 on delete cascade on update cascade
    • 主表删除记录或更改被参照字段的值时，从表会级联更新
  • set null: on delete set null on update set null
    • 主表删除记录时，从表外键字段值变为null
    • 主表更改主键字段值时，从表外键字段值变以null

2. 表关联关系

​ 认识数据之间的依赖关系是更加合理创建数据表关联性的前提。常见表数据关系有一对多，多对多：

• 一对多关系 一张表有一第记录可以对应另外一张表中多条记录；但反过来，另外一张表的一条记录只能对应第一张表的一第记录，这种关系就是一对多或多对一
• 多对多关系一对表中(A)的一条记录能够对应另外一张表(B)中的多条记录；同时B表中的一条记录也能对应A表中的多条记录# 示例：
  create table athlete (id int primary key auto_increment,name archar(30),
  age tinyint not null,country varchar(30) not null);
  ​
  create table item(  id int primary key auto_increment,  rname varchar(30) not null);
  ​
  create table athlete_item(  id int primary key auto_increment,  aid int not null,  tid int not null,   foreign key (aid) references athlete (id),
     foreign key (tid) references item (id));

3. E-R模型图

• E-R模型图E-R模型（entry-Relationship）即"实体-关系" 数据模型，有于数据库设计，用简单的图(E_R图)反映了现实世界中存在的事物或数据以及他们之间的关系。
• 实体、属性、关系
  • 实体1、描述客观事物的概念
    2、表示方法:矩形框
    3、示例: 一个人、一辆车
  • 属性1、实体具有的某种特性
    2、表示方法：椭圆形
    3、示例：
    感受属性：悲伤、喜悦、刺激、愤怒....
  • 关系1、实体之间的联系
    2、一对一关联（1:1）
    3、一对多关联（1：n）
    4、多对多关联（m:n)
  • ER图绘制矩形框代表实体，菱形框代表关系，椭圆形代表属性

4. 表连接

• 简单多表查询多个表数据可联合查询，语法格式如下：select 字段1，字段2....from 表1，表2...[where 条件]
• 笛卡尔积现象就是将A表的第一条记录与B表的每一条记录强行拼在一起。结果产生A*B条记录。
• 内连接内连接查询只会查找到符合条件的记录，其实结果和表关联查询是一样的，官方更推荐使用内连接查询。select 字段列表
  from 表1 inner join 表2
  on 表1.字段 = 表2.字段
  # 示例：
  select * from person inner join dept on person.dept_id = dept.id;

• 左链接：左表全部显示，显示右表中与左表匹配的项select 字段列表
  from 表1 left join 表2
  on 表1.字段 = 表2.字段；
  # 示例
  select * from person left join dept on person.dept_id=dept.id;
  # 查询每个部门员工人数
  select dname,count(name) form dept left join person on dept.id=person.dept_id group by dname;
• 右链接：右表全部显示，显示左表中与右表匹配的项select 字段列表
  from 表1 right join 表2
  on 表1.字段 = 表2.字段；
  # 示例：
  select * from person right join dept on person.dept_id=dept.id;
  # 注意：我们尽量使用数据量大的表作为基准表，放在前面。

综合查询练习
•
create table class(cid int primary key auto_increment,
                  caption char(4) not null);
                  
create table teacher(tid int primary key auto_increment,
                    tname varchar(32) not null);
                    
create table student(sid int primary key auto_increment,
                    sname varchar(32) not null,
                    gender enum('male','female','others') not null default 'male',
                    class_id int,
                    foreign key(class_id) references class(cid)
                    on update cascade
                    on delete cascade);
                    
create table course(cid int primary key auto_increment,
                   cname varchar(16) not null,
                   teacher_id int,
                   foreign key(teacher_id) references teacher(tid)
                   on update cascade
                   on delete cascade);
                   
create table score(sid int primary key auto_increment,
                  student_id int,
                  course_id int,
                  number int(3) not null,
                  foreign key(student_id) references student(sid)
                   on update cascade
                   on delete cascade,
                   foreign key(course_id) references course(cid)
                   on update cascade
                   on delete cascade);
                   
数据插入过程省略...
•
1. 查询每位老师教授的课程数量
select tname,count(teacher_id) from
teacher left join course on course.teacher_id=teacher.tid
group by tname;
2. 查询学生的信息及学生所在班级信息
select sid,sname,gender,caption from student inner join class on class_id=cid;
3. 查询各科成绩最高和最低的分数,形式 : 课程ID  课程名称 最高分  最低分
select course.id as 课程ID,cname,max(number),min(number) from
course left join score
on course_id=cid
group by cname,course.id;
4. 查询平均成绩大于85分的所有学生学号,姓名和平均成绩
select student.sid,sname,avg(number) from
student left join score
on score.student_id=student.sid
group by sname,student.sid having avg(number)>85;
5. 查询课程编号为2且课程成绩在80以上的学生学号和姓名
select student.sid,sname
from student left join score
on score.student_id=student.sid
where course_id=2 and score.number>80;
6. 查询各个课程及相应的选修人数
select score.course_id,cname,count(score.course_id)
from course left join score
on course.cid=score.course_id
group by score.course_id,cname;