82.数据的使用2.

-- 1. E.R实体关系图
-- E.R实体关系图是通过图表的形式来表示数据库中表和字段以及表和表之间的关键。
-- 表和表之间的关系主要有四种: 1对1,1对多,多对1,多对多

-- 2. 外键约束
-- 外键约束: 让字段的值取值范围在另外一张表的主键中
-- 怎么添加外键约束: 1) 保证当前表中有一个字段能够保持另外一张表的主键 2）添加外键约束
-- 不同对应关系外键的添加的要求不同:
-- 一对一: 可以添加到任意一张表中
-- 一对多和多对一: 添加到多的那张表中
-- 多对多: 两张表没有办法建立多对多的对应关系，需要第三张表才行
use school;
ALTER TABLE tb_student add COLUMN colid int COMMENT '所在学院'; -- 在学生表中添加新的字段保存学院表的主键

-- 3.怎么添加约束
-- 3.1 创建表或者添加字段的时候直接在字段后面添加约束
-- 3.2 通过修改表的表示添加和删除约束
-- alter table 表名 add constraint 约束索引名 约束名(字段); -给指定字段添加指定约束(只能添加唯一约束和主键约束)
-- alter table 表名 drop index 约束索引名; -- 删除指定约束
ALTER TABLE tb_student add constraint unique_collid unique (collid);
ALTER TABLE tb_student drop INDEX unique_collid;

-- 3.3 外键约束的添加
-- alter table 表1 add CONSTRAINT 约束索引名 foreign key (字段1) references 表2 (字段2);
-- 给表1中的字段1添加外键约束，并且字段1的值依赖表2中的字段2；注意: 外键索引名最好唯一
alter table tb_student add CONSTRAINT fk_collid_coll foreign key (colid) references tb_college (collid);

-- 给老师添加学院的外键和外键约束
alter table tb_teacher add COLUMN cid int; -- 添加学院外键
alter table tb_teacher add constraint fk_couid_tea
FOREIGN KEY (cid)
REFERENCES tb_college (collid);

-- 给课程添加老师的外键和外键约束
ALTER TABLE tb_course add COLUMN tid int; -- 添加老师外键
ALTER TABLE tb_course add constraint fk_teaid_cou
FOREIGN KEY (tid)
REFERENCES tb_teacher (teaid);

-- 创建表的时候添加外键约束
create table if not EXISTS tb_test
(
tid int auto_increment,
tname varchar(10),
sid int,
PRIMARY key (tid), -- 设置主键
FOREIGN KEY (sid) REFERENCES tb_teacher (teaid) -- 设置外键
);

drop TABLE if EXISTS tb_test;

-- 3.4 删除外键约束
-- alter table 表名 drop FOREIGN KEY 外键索引;
alter table tb_student drop FOREIGN KEY fk_collid_coll;

-- 3.4.5 多对多关系的外键约束
create table if not EXISTS tb_record
(
reid int auto_increment COMMENT '选课记录编号',
sid int COMMENT '学生的外键',
cid int COMMENT '课程外键',
redate date COMMENT '选课日期',
score FLOAT comment '分数',
PRIMARY KEY (reid),
FOREIGN KEY (sid) REFERENCES tb_student (stuid),
FOREIGN KEY (cid) REFERENCES tb_course (couid)
);

-- ============================4.高级查询===============================
-- 1.去重: select distinct 字段名 FROM 表名;
SELECT distinct redate FROM tb_record ORDER BY redate;
SELECT distinct sid FROM tb_record; -- 查询所有选课的学生的id

-- 2.限制和分页
-- 限制: SELECT * FROM 表名 limit N; - 查询的时候只获取前N条数据
-- 偏移: SELECT * FROM 表名 limit M offset N; - 跳过前N条数据获取M条数据(从第N+1条数据开始，获取M条数据)
-- SELECT * FROM 表名 limit M,N - 跳过前M条数据取N条数据
SELECT * FROM tb_record LIMIT 5; -- 获取tb_record表中前5条数据
SELECT * FROM tb_record LIMIT 7 OFFSET 3; -- 跳过前3条获取7条数据
SELECT * FROM tb_record limit 3,7; -- 跳过前3条获取7条数据

SELECT * FROM tb_record ORDER BY score desc LIMIT 3; -- 获取成绩前3的选课记录

SELECT DISTINCT score from tb_record ORDER BY score DESC LIMIT 3;

-- 3.聚合: max(),min(),sum(),avg(),count() - mysql
use school;

select max(score) as max_score FROM tb_record; -- 获取tb_record中最高分
select min(score) as min_score from tb_record; -- 获取tb_record中最底分
SELECT sum(score) as sum_score from tb_record; -- 求和所有分数的和，如果某一个记录的分数是空，那么这条记录不存与运算
SELECT avg(score) as avg_score from tb_record; -- 求平均分（空不参与运算）
select count(score) as c_score from tb_record; -- 统计分数的个数(空不参与计算)

-- 4.分组：
-- select 聚合操作 from 表名 group by (字段); - 按指定字段的值对表进行分组，然后对每个分组进行聚合操作。
-- 注意: 分组后，除了分组字段以外，其他字段只能聚合操作
-- 在分组后如果加条件，需要用having代替where

-- 获取每个学生的平均分
select sid, avg(score) from tb_record group by(sid);

-- 获取每个学科的平均分
select cid, avg(score) from tb_record group by (cid);

-- 获取每个学生选课数量
select sid,count(cid) from tb_record group by (sid);

-- 5.子查询: 将一个查询的结果作为另外一个查询的条件或者查询对象
-- 第一种子查询: 将查询结果作为另外一个查询的条件

-- 获取成绩是最高分的所有的学生的id
select max(score) as max_s from tb_record;
select sid from tb_record where score=(select max(score) as max_s from tb_record);

-- 获取分数前3的所有的学生id (版本不支持limit的子查询)
-- select distinct(score) from tb_record ORDER BY score desc limit 3;
-- select sid from tb_record where score in (select distinct(score) from tb_record ORDER BY score desc limit 3);

-- 获取选了2门课程以上的学生的id(分组+聚合)
select sid,count(cid) from tb_record group by (sid) having count(cid)>2;
select sid,count(cid) as c_course from tb_record group by (sid) having c_course>2;
select sid from tb_record group by (sid) having count(cid)>2;

-- 获取选了2门课程以上的学生的姓名(子查询)
select stuname from tb_student where stuid in (select sid from tb_record group by (sid) having count(cid)>2);

-- 第二种子查询: 将一个查询的结果作为另一个查询的查询对象
-- 注意: 如果要将查询结果作为查询对象，那么查询结果对应的查询必须重命名
select * from tb_student limit 4,5;
select stuname from (select * from tb_student limit 4,5) as t1;

-- select stuname as sname, stuaddr as saddr from tb_student WHERE stusex=0;
select sname,saddr from (select stuname as sname, stuaddr as saddr from tb_student WHERE stusex=0) as t1 where saddr like '%成都' ;

-- 6. 连接查询： 同时查询多张表
-- select * from 表名1,表名2,表名3 连接条件 查询条件;
-- (1, 2, 3), (a, b, c) --> (1a,1b,1c, 2a,2b,2c,3a,3b,3c)
-- 注意: 如果既有连接条件又有查询条件，查询条件必须放在连接条件的后面

-- 查询所有学生的名字和学院名字
select stuname, collname from tb_student, tb_college where tb_student.colid=tb_college.collid;

-- 查询学生每个学科的成绩: xxx(学生名字) xx(学科名) xx(分数)
select stuname, couname, score from
tb_student,
tb_course,
tb_record
where tb_student.stuid=tb_record.sid and tb_course.couid=tb_record.cid;

-- 查询所有大于70分的学生的学科成绩： xxx(学生名字) xx(学科名) xx(分数)
select stuname, couname, score from
tb_student,
tb_course,
tb_record
where tb_student.stuid=tb_record.sid and tb_course.couid=tb_record.cid and score>70;

-- 查询所有学生信息
SELECT * from tb_student;
-- 查询所有课程名称及学分(映射)
SELECT couname, coucredit FROM tb_course;

-- 查询所有学生的姓名和性别(映射)
SELECT stuname, if(stusex,'男', '女') as gender FROM tb_student;

-- 查询所有女学生的姓名和出生日期
SELECT stuname, stubirth FROM tb_student where stusex=0;

-- 查询所有80后学生的姓名、性别和出生日期
SELECT stuname, if(stusex, '男', '女') as gender, stubirth FROM tb_student
WHERE stubirth >= '1980-1-1' and stubirth <= '1989-12-31';

-- 查询姓"杨"的学生姓名和性别(模糊)
SELECT stuname, if(stusex, '男', '女') as gender FROM tb_student where stuname like '杨%';

-- 查询姓"杨"名字两个字的学生姓名和性别
SELECT stuname, if(stusex, '男', '女') as gender FROM tb_student where stuname like '杨_';

-- 查询姓"杨"名字三个字的学生姓名和性别
SELECT stuname, if(stusex, '男', '女') as gender FROM tb_student where stuname like '杨__';

-- 查询名字中有"不"字或"嫣"字的学生的姓名
SELECT stuname FROM tb_student where stuname like '%不%' or stuname like '%嫣%';

-- 查询没有录入家庭住址的学生姓名
SELECT stuname FROM tb_student WHERE stuaddr is NULL;

-- 查询录入了家庭住址的学生姓名
SELECT stuname FROM tb_student WHERE stuaddr is not NULL;

-- 查询学生选课的所有日期(去重)
SELECT distinct redate 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 if(stusex, '男生', '女生') as gender, count(stuid) as c_stu FROM tb_student GROUP BY (stusex);

-- 查询课程编号为1111的课程的平均成绩
-- select avg(score) FROM tb_record GROUP BY (cid) having cid=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) as avg_score FROM tb_record GROUP BY (sid) having avg_score>=90;

-- 查询年龄最大的学生的姓名(子查询)
-- select min(stubirth) from tb_student;
SELECT stuname FROM tb_student where stubirth=(select min(stubirth) from tb_student);

-- 查询年龄最大的学生姓名和年龄
SELECT stuname, DATEDIFF(CURDATE(),stubirth) div 365 as age FROM tb_student where stubirth=(select min(stubirth) from tb_student);

-- 查询选了两门以上的课程的学生姓名(子查询，分组，聚合)
-- select sid FROM tb_record GROUP BY (sid) HAVING count(cid)>2;
select stuname FROM tb_student where stuid in (select sid FROM tb_record GROUP BY (sid) HAVING count(cid)>2);

-- 查询学生姓名、课程名称以及成绩(连接查询)
select stuname, couname, score FROM
tb_student,
tb_course,
tb_record
where tb_student.stuid=tb_record.sid and tb_course.couid=tb_record.cid;

-- 查询学生姓名、课程名称以及成绩按成绩从高到低查询第11-15条记录
select stuname, couname, score FROM
tb_student,
tb_course,
tb_record
where tb_student.stuid=tb_record.sid and tb_course.couid=tb_record.cid
ORDER BY score DESC
LIMIT 10, 5;

-- 查询选课学生的姓名和平均成绩(连接查询，子查询)
select stuname, avg(score) FROM
tb_student as t1,
tb_record as t2
where t1.stuid=t2.sid GROUP BY(sid);

-- 方法2：
-- select sid, avg(score) as avg_s FROM tb_record GROUP BY (sid);
select stuname, avg_s FROM
tb_student as t1,
(select sid, avg(score) as avg_s FROM tb_record GROUP BY (sid)) as t2
where t1.stuid=t2.sid;

-- 查询每个学生的姓名和选课数量
select stuname, count(cid) FROM
tb_student as t1,
tb_record as t2
WHERE t1.stuid=t2.sid
GROUP BY (sid);

-- select sid, COUNT(cid) as count_c FROM tb_record GROUP BY (sid);
SELECT stuname, count_c FROM
tb_student as t1,
(select sid, COUNT(cid) as count_c FROM tb_record GROUP BY (sid)) as t2
WHERE t1.stuid=t2.sid;