外键约束和进阶查询
外键约束
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分的学生