关联

  • 建立成绩单scores,构造以下
    • id
    • 学员
    • 学科
    • 考试成绩
  • 思索:学员列应当存哪些信息内容呢?
  • 答:学员列的数据并不是在这儿在建的,而应当从学员表引入回来,关联也是一条数据;依据现代性规定应当储存学员的序号,而不是学员的名字等其他信息内容
  • 同样,科目表也是关联列,引入学科表中的数据

MySQL(五)  数据库  第1张

  • 创建表的句子以下
    create table scores(
    id int primary key auto_increment,
    stuid int,
    subid int,
    score decimal(5,2)
    );

    外键约束

    • 思索:如何确保关联列数据的实效性呢?一切整数金额都行吗?
    • 答:务必是学员表中id列存有的数据,能够 根据外键约束开展数据的实效性认证
    • 为stuid加上外键约束
    alter table scores add constraint stu_sco foreign key(stuid) references students(id);
    
    • 这时插进或是改动数据时,假如stuid的值在students表中不会有则会出错
    • 在创建表时能够 立即建立管束
create table scores(
id int primary key auto_increment,
stuid int,
subid int,
score decimal(5,2),
foreign key(stuid) references students(id),
foreign key(subid) references subjects(id)
);

外键约束的联级实际操作

  • 在删掉students表的数据时,假如这一id值在scores中早已存有,则会抛出现异常
  • 强烈推荐应用逻辑性删掉,还能够处理这个问题
  • 能够 创建表时特定联级实际操作,还可以在创建表后再改动外键约束的联级实际操作
  • 英语的语法
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
  • 则上边难题的回答是:
    select students.sname,subjects.stitle,scores.score
    from scores
    inner join students on scores.stuid=students.id
    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 缩写名'或' 缩写名',为表起个临时性的缩写名字

训练

  • 查寻学员的名字、均分
select students.sname,avg(scores.score)
from scores
inner join students on scores.stuid=students.id
group by students.sname;
  • 查寻男孩子的名字、总成绩
select students.sname,avg(scores.score)
from scores
inner join students on scores.stuid=students.id
where students.gender=1
group by students.sname;
  • 查寻学科的名字、均分
select subjects.stitle,avg(scores.score)
from scores
inner join subjects on scores.subid=subjects.id
group by subjects.stitle;
  • 查寻未删掉学科的名字、满分、均分
select subjects.stitle,avg(scores.score),max(scores.score)
from scores
inner join subjects on scores.subid=subjects.id
where subjects.isdelete=0
group by subjects.stitle;