MySQL学习笔记之基础操作(三)

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;
版权声明:seasilo 发表于 2021-01-05 19:12:59。
转载请注明:MySQL学习笔记之基础操作(三) | 海筒自习室

暂无评论

暂无评论...