查看: 42|回复: 0

[Mysql数据库] MySQL(五)

发表于 2018-4-28 08:00:00
关系
  • 创建成绩表scores,结构如下
    • id
    • 学生
    • 科目
    • 成绩
  • 思考:学生列应该存什么信息呢?
  • 答:学生列的数据不是在这里新建的,而应该从学生表引用过来,关系也是一条数据;根据范式要求应该存储学生的编号,而不是学生的姓名等其它信息
  • 同理,科目表也是关系列,引用科目表中的数据

  • 创建表的语句如下
    1. create table scores(
    2. id int primary key auto_increment,
    3. stuid int,
    4. subid int,
    5. score decimal(5,2)
    6. );
    复制代码
    外键
    • 思考:怎么保证关系列数据的有效性呢?任何整数都可以吗?
    • 答:必须是学生表中id列存在的数据,可以通过外键约束进行数据的有效性验证
    • 为stuid添加外键约束
    1. alter table scores add constraint stu_sco foreign key(stuid) references students(id);
    复制代码
    • 此时插入或者修改数据时,如果stuid的值在students表中不存在则会报错
    • 在创建表时可以直接创建约束
  1. create table scores(
  2. id int primary key auto_increment,
  3. stuid int,
  4. subid int,
  5. score decimal(5,2),
  6. foreign key(stuid) references students(id),
  7. foreign key(subid) references subjects(id)
  8. );
复制代码
外键的级联操作
  • 在删除students表的数据时,如果这个id值在scores中已经存在,则会抛异常
  • 推荐使用逻辑删除,还可以解决这个问题
  • 可以创建表时指定级联操作,也可以在创建表后再修改外键的级联操作
  • 语法
  1. alter table scores add constraint stu_sco foreign key(stuid) references students(id) on delete cascade;
复制代码

级联操作的类型包括:

  • restrict(限制):默认值,抛异常
  • cascade(级联):如果主表的记录删掉,则从表中相关联的记录都将被删除
  • set null:将外键设置为空
  • no action:什么都不做
先看个问题
  • 问:查询每个学生每个科目的分数
  • 分析:学生姓名来源于students表,科目名称来源于subjects,分数来源于scores表,怎么将3个表放到一起查询,并将结果显示在同一个结果集中呢?
  • 答:当查询结果来源于多张表时,需要使用连接查询
  • 关键:找到表间的关系,当前的关系是
    • students表的id---scores表的stuid
    • subjects表的id---scores表的subid
  • 则上面问题的答案是:
    1. select students.sname,subjects.stitle,scores.score
    2. from scores
    3. inner join students on scores.stuid=students.id
    4. inner join subjects on scores.subid=subjects.id;
    复制代码
  • 结论:当需要对有关系的多张表进行查询时,需要使用连接join

连接查询
    • 连接查询分类如下:
      • 表A inner join 表B:表A与表B匹配的行会出现在结果中
      • 表A left join 表B:表A与表B匹配的行会出现在结果中,外加表A中独有的数据,未对应的数据使用null填充
      • 表A right join 表B:表A与表B匹配的行会出现在结果中,外加表B中独有的数据,未对应的数据使用null填充
    • 在查询或条件中推荐使用“表名.列名”的语法
    • 如果多个表中列名不重复可以省略“表名.”部分
    • 如果表的名称太长,可以在表名后面使用' as 简写名'或' 简写名',为表起个临时的简写名称
练习
  • 查询学生的姓名、平均分
  1. select students.sname,avg(scores.score)
  2. from scores
  3. inner join students on scores.stuid=students.id
  4. group by students.sname;
复制代码
  • 查询男生的姓名、总分
  1. select students.sname,avg(scores.score)
  2. from scores
  3. inner join students on scores.stuid=students.id
  4. where students.gender=1
  5. group by students.sname;
复制代码
  • 查询科目的名称、平均分
  1. select subjects.stitle,avg(scores.score)
  2. from scores
  3. inner join subjects on scores.subid=subjects.id
  4. group by subjects.stitle;
复制代码
  • 查询未删除科目的名称、最高分、平均分
  1. select subjects.stitle,avg(scores.score),max(scores.score)
  2. from scores
  3. inner join subjects on scores.subid=subjects.id
  4. where subjects.isdelete=0
  5. group by subjects.stitle;
复制代码




上一篇:MySQL(四)
下一篇:MySQL(八)
回复

使用道具 举报