4-13总结

外键约束和进阶查询

外键约束

1、添加和删除约束

1）添加约束 alter table 表名 约束名 约束（字段）

alter table t_teacher add constraint name_unique unique(rea_name);

2）删除约束

alter table t_teacher drop constraint name_unique;

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

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

– 3）代码添加外键

创建学院

create table if not exists college

(col_id int not null comment ‘学院id’

col_name varchar(10) not null comment ‘学院名字’,

primary key(col_id)

)engine=innodb;

– 添加学院
insert into t_college(col_id,col_name) values
(1101,‘数信学院’),
(1102,‘信管学院’),
(1103,‘辛基学院’);

创建学生表和学院约束

create table if not exists t_student

(stu_id int not null comment ‘学生id’,

stu_name varchar(10) not null comment ‘学生姓名’,

stu_age int not null comment ‘年龄’,

col_id int not null comment ‘学院id’

primary key(stu_id),

foreign key(col_id) references t_college(col_id)

)endine=innodb;

– 创建和课程的约束

create table if not exists t_course
(cou_id int not null comment ‘课程id’,
tea_id int not null comment ‘老师id’,
cou_name varchar(5) not null comment ‘课程名字’,
primary key(cou_id),
foreign key(tea_id) references t_teacher(tea_id)
)engine=innodb;

– 创建分数表（创建时添加外键和外键约束）

create table t_score

(sc_id int not null comment ‘分数id’,

score decimal default 0 comment ‘分数’,

stu_id int not null comment ‘学号’,

cou_id int not null comment ‘课程id’,

primary key(sc_id),

foreign key(stu_id) references t_student(stu_id),

foreign key(cou_id) rferences t_course(cou_id)

)engine=innodb;

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

create table t_score
(sc_id int not null comment ‘分数id’,
score decimal default 0 comment ‘分数’,
primary key(sc_id)
)engine=innodb;

添加外键

alter table t_score add colum stu_id int comment ‘学生外键’,

alter table t_score add colum cou_id int comment ‘课程外键’

添加外键约束

alter table t_score add constraint score_stu foreign key(stu_id) references t_student(stu_id);

alter table t_score add constraint score_stu foreign key(cou_id) references t_course(cou_id);

– 删除约束
use school2;
alter table t_score drop foreign key score_stu; – 删除后面添加外键约束，score_stu是外键索引名
alter table t_score drop t_teacher_ibfk_1; – 删除创建时添加的外键约束，t_teacher_ibfk_1是外键索引名

– 4、列重命名
select stuname as 姓名,stusex as 性别 from tb_student;
select stubirth as 生日,collid as 学院id from tb_student;

– 5、对查询结果重新赋值
– if (条件,值1,值2) - mysql专有
select stuname,if (stusex,‘男’,‘女’) as gender from tb_student;

select if(stuname=‘岳不群’,‘yuebuqun’,stuname) as 姓名,stuaddr from tb_student;
select stusex from tb_student where stusex<>1;

– sql通用 case 字段 when 条件 then 值1 else 值2 end
select case when 1 then ‘男’ else ‘女’ end as gender from tb_student;

– 6、队列进行合并
select concat(stuname,stuaddr) as new_name from tb_student;
select concat(stuname,stubirth) as 姓名生日 from tb_student;

– 7、模糊查询 - 查询的时候通过like条件来指定查询对象
select * from tb_student where stuname like ‘杨%’;
select * from tb_student where stuname like ‘%我%’; – % 表示任意内容出现任意个数
select stuname,stuaddr from tb_student where stuname like ‘隔%’;
select * from tb_student where stuname like ‘杨_’; – _表示任意一个内容

– 8、排序
– 排序：原来该怎么查就怎么查，查完后在添加排序语法
select * from tb_student order by stubirth; – 默认升序
select * from tb_student order by stubirth desc; – 降序

– 9、将日期换成年龄
– 计算年龄的公式：datediff(curdate()-当前日期,stu_birth-表示出生日期) div 365
select stuname,stubirth,datediff(curdate(),stubirth) div 365 as age from tb_student;
select stuname,stubirth,datediff(curdate(),stubirth) div 365 as age,stuaddr from tb_student;

– 10、去重(只能获取去重后的字段值)
select distinct teaid from tb_course;
select distinct stusex from tb_student;

– 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条数据

select stuname,stusex,datediff(curdate(),stubirth)div 365 as age from tb_student limit 5 offset 4;

– 高级查询
– 12、聚合
– 聚合函数：max()/min()/sum()/avg()/count()
– 聚合函数在没有分组的时候使用没有意义
select max(score) as 最高分 from tb_record;
select min(score) as 最低分 from tb_record;
select sum(score) as 总分 from tb_record;
select avg(score) as 平均分 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,max(score) as max_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分的学生