外键约束和查询进阶

外键约束和查询进阶

外键约束

– 1.添加和删除约束
– 1)添加和删除普通约束 - 唯一约束
– 添加约束: alter table 表名 add constraint 约束名 约束(字段);

use school;
alter table tb_teacher add constraint name_unique unique(tea_name);

– 删除约束

alter table tb_teacher drop constraint name_unique;

– 2）添加和删除外键约束
– E.R图(实体关系图)
– 表和表之间的对应：一对一、一对多、多对多
– 什么是外键：表中保存另外一张表的主键的字段

– 怎么添加外键约束：
– 一对一，可以将外键添加到任意一个表中。
– 一对多，将外键添加到N所在的一方
– 多对多，需要创建一张新的表，新的表中有两个外键

– 3）代码添加外键

use school;
– 创建学院表
create table tb_college
(
collid int auto_increment comment ‘编号’,
collname varchar(50) not null comment ‘名称’,
collintro varchar(500) default ‘’ comment ‘介绍’,
primary key (collid)
);

– 创建学生表
create table tb_student
(
stuid int not null comment ‘学号’,
stuname varchar(20) not null comment ‘姓名’,
stusex boolean default 1 comment ‘性别’,
stubirth date not null comment ‘出生日期’,
stuaddr varchar(255) default ‘’ comment ‘籍贯’,
collid int not null comment ‘所属学院’,
primary key (stuid),
foreign key (collid) references tb_college (collid)
);

– 创建教师表
create table tb_teacher
(
teaid int not null comment ‘工号’,
teaname varchar(20) not null comment ‘姓名’,
teatitle varchar(10) default ‘助教’ comment ‘职称’,
collid int not null comment ‘所属学院’,
primary key (teaid),
foreign key (collid) references tb_college (collid)
);

– 创建课程表
create table tb_course
(
couid int not null comment ‘编号’,
couname varchar(50) not null comment ‘名称’,
coucredit int not null comment ‘学分’,
teaid int not null comment ‘授课老师’,
primary key (couid),
foreign key (teaid) references tb_teacher (teaid)
);

– 分数表(创建表的时候添加外键和外键约束)
create table tb_score
(
scoreid int auto_increment not null comment ‘分数id’,
stuid int not null comment ‘学号’,
couid int not null comment ‘编号’,
score decimal default 0 comment ‘分数’,
primary key (scoreid),
foreign key (stuid) references tb_student(stuid),
foreign key (couid) references tb_course(couid)；

– 表创建好以后再添加外键和外键约束

create table tb_score
(
scoreid int auto_increment not null comment ‘分数id’,
score decimal default 0 comment ‘分数’,
primary key (scoreid)
);

– 添加外键

alter table tb_score add column stuid int comment ‘学生外键’;
alter table tb_score add column couid int comment ‘课程外键’;

– 添加外键约束

alter table tb_score add constraint score_stu foreign key (stuid) references tb_student(stuid);
alter table tb_score add constraint score_cou foreign key (couid) references tb_course(couid);
alter table tb_course add constraint cou_tea foreign key (teaid) references tb_teacher(teaid);

– 删除外键约束

alter table tb_score drop foreign key score_cou;
alter table tb_course drop foreign key tb_course_ibfk_1;

查询进阶

– 1.查学生表中所有的数据

select * from tb_student;

– 2.查指定列的数据

select stuname, stuaddr from tb_student;

– 3.按条件查询

select * from tb_student where stusex=0;

– 4.列重命名

select stuname as 姓名, stusex as 性别, stuaddr from tb_student;

– 5.对查询结果重新赋值
– mysql专有：if(条件,值1,值2)
– sql通用：case 字段 when 条件 then 值1 else 值2 end

select stuname, if(stusex,‘男’,‘女’) as gender from tb_student;
select case stusex when 1 then ‘男’ else ‘女’ end as gender from tb_student;

select if(stuname=‘杨逍’, ‘YangXiao’, stuname) as name from tb_student;

– 6.对列进行合并

select concat(stuaddr, stuname)as new_name from tb_student;

– 7.模糊查询 - 查询的时候时候通过like条件来指定查询对象

– % ：任意内容出现任意个数
– _：任意内容出现一次

select * from tb_student where stuname like ‘杨%’;
select * from tb_student where stuname like ‘%我%’;
select * from tb_student where stuname like ‘杨%悔’;
select * from tb_student where stuname like ‘杨_’;

– 8. 排序

– 排序：查询的时候原来该怎么查怎么查，查完后再加排序的语法
– 降序加：desc

select * from tb_student order by stubirth desc;
select stuname, stuaddr, stubirth from tb_student where stusex=1 order by stubirth;
select stuname, if(stusex,‘男’,‘女’) as gender from tb_student order by stusex;

– 9.将日期换成年龄

– 计算年龄的公式：datediff(curdate(), 出生日期) div 365

select stuname, stubirth, datediff(curdate(), stubirth) div 365 as age from tb_student;

– 10.去重(只能获取去重后的字段值)

select distinct teaid from tb_course ;

– 11. 限制和分页 - 获取表中部分数据

select * from tb_student limit 5; – 获取前5条数据
select * from tb_student limit 5 offset 4; – 跳过前4条取5条数据
select * from tb_student limit 5,4; – 跳过前5取4条数据

– 12.聚合

– 聚合函数：max()/min()/sum()/avg()/count()
– 聚合函数在没有分组的时候使用没有意义

select max(score) as max_score from tb_record;
select min(score) as min_score from tb_record;
select sum(score) from tb_record;
select avg(score) from tb_record;
select count(score) from tb_record;

– 13.分组 - 将指定字段值相同的记录作为一个分组
– 分组查询的时候字段只能写分组字段和聚合结构

select sid, avg(score) from tb_record group by (sid); – 查询每个学生的平均分
select sid, max(score) from tb_record group by(sid);
select cid, avg(score) from tb_record group by (cid); – 查询每个学科的平均分

– 如果查询中有分组操作，加条件进行筛选的时候用having代替where

select sid, avg(score) as avg_score from tb_record group by (sid) having avg_score>80; – 查询平均大于80的学生和对应的平均分

作业

– 查询所有学生信息
select * from tb_student;
– 查询所有课程名称及学分(别名)
select couname,coucredit from tb_course;
– 查询所有学生的姓名和性别（性别用男和女表示）
select stuname,if(stusex,‘男’,‘女’) as 性别 from tb_student;
– 查询所有女学生的姓名和出生日期
select stuname,stubirth from tb_student where stusex=0;
– 查询所有80后学生的姓名、性别和出生日期
select stuname,stusex,stubirth from tb_student where ‘1980-0-0’<=stubirth and stubirth<‘1990-0-0’;
– 查询姓"杨"的学生姓名和性别
select stuname,stusex from tb_student where stuname like ‘杨%’;
– 查询姓"杨"名字三个字的学生姓名和性别
select stuname,stusex from tb_student where stuname like ‘杨__’;
– 查询名字中有"不"字或"嫣"字的学生的姓名
select stuname from tb_student where stuname like ‘%不%’ or ‘%嫣%’;
– 查询没有录入家庭住址的学生姓名
select stuname from tb_student where stuaddr is null;
– 查询录入了家庭住址的学生姓名
select stuname from tb_student where stuaddr is not null;
– 查询学生选课的所有日期(去重)
select distinct seldate from tb_record;
– 查询学生的家庭住址(去重)
select distinct stuaddr from tb_student;
– 查询男学生的姓名和生日按年龄从大到小排列
select stuname,stubirth from tb_student where stusex=1 order by stubirth;
– 查询年龄最大的学生的出生日期
select min(stubirth) from tb_student;
– 查询年龄最小的学生的出生日期
select max(stubirth) from tb_student;
– 查询男女学生的人数
select stusex,count(stusex) from tb_student group by stusex;
– 查询课程编号为1111的课程的平均成绩
select avg(score) from tb_record where cid=1111;
– 查询学号为1001的学生所有课程的平均分
select avg(score) from tb_record where sid=1001;
– 查询每个学生的学号和平均成绩
select sid,avg(score) from tb_record group by(sid);
– 查询平均成绩大于等于90分的学生的学号和平均成绩
select sid,avg(score) from tb_record group by(sid) having avg(score)>=90;