1. E.R實體關(guān)系圖

-- E.R實體關(guān)系圖是通過圖表的形式來表示數(shù)據(jù)庫中表和字段以及表和表之間的關(guān)鍵。
-- 表和表之間的關(guān)系主要有四種: 1對1,1對多,多對1,多對多

2. 外鍵約束

-- 外鍵約束: 讓字段的值取值范圍在另外一張表的主鍵中
-- 怎么添加外鍵約束: 1) 保證當前表中有一個字段能夠保持另外一張表的主鍵 2）添加外鍵約束
-- 不同對應關(guān)系外鍵的添加的要求不同:
-- 一對一: 可以添加到任意一張表中
-- 一對多和多對一: 添加到多的那張表中
-- 多對多: 兩張表沒有辦法建立多對多的對應關(guān)系，需要第三張表才行

use school;
ALTER TABLE tb_student add COLUMN colid int COMMENT '所在學院';  -- 在學生表中添加新的字段保存學院表的主鍵

3.怎么添加約束

-- 3.1 創(chuàng)建表或者添加字段的時候直接在字段后面添加約束
-- 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);

-- 創(chuàng)建表的時候添加外鍵約束

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 多對多關(guān)系的外鍵約束

create table if not EXISTS tb_record
(
reid int auto_increment COMMENT '選課記錄編號',
sid int COMMENT '學生的外鍵',
cid int COMMENT '課程外鍵',
redate date COMMENT '選課日期',
score FLOAT comment '分數(shù)',
PRIMARY KEY (reid),
FOREIGN KEY (sid) REFERENCES tb_student (stuid),
FOREIGN KEY (cid) REFERENCES tb_course (couid)  
);

4.高級查詢

-- 4.1.去重: select distinct 字段名 FROM 表名;

SELECT distinct redate FROM tb_record ORDER BY redate;
SELECT distinct sid FROM tb_record;    -- 查詢所有選課的學生的id

-- 4.2.限制和分頁
-- 限制: SELECT * FROM 表名 limit N; - 查詢的時候只獲取前N條數(shù)據(jù)
-- 偏移: SELECT * FROM 表名 limit M offset N; - 跳過前N條數(shù)據(jù)獲取M條數(shù)據(jù)(從第N+1條數(shù)據(jù)開始，獲取M條數(shù)據(jù))
-- SELECT * FROM 表名 limit M,N - 跳過前M條數(shù)據(jù)取N條數(shù)據(jù)

SELECT * FROM tb_record LIMIT 5;   -- 獲取tb_record表中前5條數(shù)據(jù)
SELECT * FROM tb_record LIMIT 7 OFFSET 3;  -- 跳過前3條獲取7條數(shù)據(jù)
SELECT * FROM tb_record limit 3,7;   -- 跳過前3條獲取7條數(shù)據(jù)

SELECT * FROM tb_record ORDER BY score desc LIMIT 3;   -- 獲取成績前3的選課記錄

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

-- 4.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;    -- 求和所有分數(shù)的和，如果某一個記錄的分數(shù)是空，那么這條記錄不存與運算
SELECT avg(score) as avg_score from tb_record;    -- 求平均分（空不參與運算）
select count(score) as c_score from tb_record;    -- 統(tǒng)計分數(shù)的個數(shù)(空不參與計算)

-- 4.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);

-- 獲取每個學生選課數(shù)量
select sid,count(cid) from tb_record group by (sid);

-- 4.5.子查詢: 將一個查詢的結(jié)果作為另外一個查詢的條件或者查詢對象
-- 第一種子查詢: 將查詢結(jié)果作為另外一個查詢的條件

-- 獲取成績是最高分的所有的學生的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);

-- 獲取分數(shù)前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);

-- 第二種子查詢: 將一個查詢的結(jié)果作為另一個查詢的查詢對象
-- 注意: 如果要將查詢結(jié)果作為查詢對象，那么查詢結(jié)果對應的查詢必須重命名
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 '%成都' ;

-- 4.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(分數(shù))
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(分數(shù))
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;

-- 查詢男女學生的人數(shù)(分組和聚合)
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;

-- 查詢每個學生的姓名和選課數(shù)量
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;