一、MYSQL的那些約束你掌握了幾種？

在MYQSL中，有那么一些約束，比如不能為null，或者是必須唯一的，就像身份證一樣，每個人的身份證號一定是唯一的，性別可以是男，也可以是女，但不是為null，

下面介紹MYSQL中的6種約束

1、NOT NULL ：約束某列不能為空，

-- 創建一個圖書表，id不能為空
create table book(id int  not null);

2、UNIQUE : 約束某列的某行有唯一值，

-- 創建一個圖書表，id唯一，不能重復
create table book(id int  unique);

3、DEFAULT : 沒有給列賦值時給一個默認值，

-- 創建一個語文書表，默認name為unknown
create table book(name varchar(20) default
'unknown');

4、PRIMARY KEY ：NOT NUL和 UNIQUE的結合，即不能為空， 值必須唯一，一般表的ID會設成primary key，這樣的好處是為表創立一個唯一的標識，能夠更方便的找到表中特殊的資訊，一般常與auto_increment連用，表示自增主鍵，

-- 創建一個圖書，每個圖書有自己的名稱，價格，但序列號是唯一的
create table book(
id int primary key auto_increment,
name varchar(20),
price int );

5、FOREIGN KEY :外鍵約束，一個表的資料和另一個表匹配，增加耦合性，

-- 創建班級表，
create table class(
id int primary key auto_increment,
name varchar(20)
);
-- 學生表,一個學生對應一個班級，一個班級可以有多個學生
create table student(
id int primary key auto_increment,
name varchar(20),
classes_id int ,
foreign key (classes_id) references class(id)
);

6、CHECK : 保證列中的值符合特定的條件，

-- MySQL使用時不報錯，但忽略該約束：
create table student(
id int primary key auto_increment,
name varchar(20),
sex varchar(1),
check (sex='男' or sex='女')
);

二、表與表之間的三種關系

1、一對一關系
eg：人和身份照號碼

一個人只能擁有一個身份證號碼

2、一對多關系
eg:學生和班級的關系

一個班級可以有多個學生，一個學生只屬于一個班級

3、多對多關系
eg：老師和班級的關系

一個班級可以有多個老師，一個老師也可以教多個班，

三、查詢

3.1 :聚合查詢

聚合查詢通過4中聚合函式完成，他們分別是：**
1、count() ：回傳查詢到的數量，和查詢型別是不是數字無關，

-- 查詢圖書表有多少書
select  count(*) from book;

2、sum() ：回傳查詢資料的總和，如果資料不是數字就沒有意義

-- 查詢三年二班有多少學生
select sum(student) from class;

3、avg() : 回傳查詢的資料的平均數，如果資料不是數字沒有意義

-- 查詢三年二班學生數學的平均分
select avg(math) from class;

4、max() : 回傳查詢的資料的最大值，如果資料不是數字沒有意義

-- 查詢三年二班數學的最高分
select max(math) from class;

5、min(): 回傳查詢的資料的最小值，如果資料不是數字沒有意義

-- 查詢三年二班數學的最低分
select min(math) from class;

3.2、GROUP BY

使用group by字句可以對指定列進行分組查詢，但是需要滿足，select后的
欄位必須是分組依據的欄位，如果其他欄位想出現就必須使用聚合函式，

eg:
準備資料，創建一個學生表，含有學生id，姓名，數學和語文成績，

create table  student(
-- 學生id
id int primary key auto_increment,
-- 學生姓名
student_name varchar(20),
-- 學生數學成績
math int,
-- 學生語文成績
chinese int 
);

插入資料

insert into student(student_name,math,chinese) values
('張三',66,67),
('李四',44,98),
('王五',88,89);

查看當前表的資料

使用GROUP BY查詢，學生的最高、平均、最低數學成績

3.3、HAVING和group by 搭配使用

在使用group by 字句后，如果還需要條件篩選的話，就不能用where了，需要用having來篩選條件，

-- 查詢總成績大于150的同學
select student_name , sum(math+chinese) from student group by student_name having sum(math+chinses)>150;

結果只有王五同學總分大于150.

3.4、聯合查詢

上面的查詢都是在一張表中進行查詢，但實際應用的程序中，經常是在幾張表中查詢資料，這就用到了聯合查詢，多張表查詢就是對這些表取笛卡爾積，在篩選有用的資料，學習聯合查詢之前，需要先了解什么笛卡爾積，

總得來說，笛卡爾積就是羅列出兩張表所有可能出現的資料，

準備資料：

drop table if exists classes;
drop table if exists student;
drop table if exists course;
drop table if exists score;

create table classes (id int primary key auto_increment, name varchar(20), `desc` varchar(100));

create table student (id int primary key auto_increment, sn varchar(20),  name varchar(20), qq_mail varchar(20) ,
        classes_id int);

create table course(id int primary key auto_increment, name varchar(20));

create table score(score decimal(3, 1), student_id int, course_id int);

insert into classes(name, `desc`) values 
('計算機系2019級1班', '學習了計算機原理、C和Java語言、資料結構和演算法'),
('中文系2019級3班','學習了中國傳統文學'),
('自動化2019級5班','學習了機械自動化');

insert into student(sn, name, qq_mail, classes_id) values
('09982','黑旋風李逵','xuanfeng@qq.com',1),
('00835','菩提老祖',null,1),
('00391','白素貞',null,1),
('00031','許仙','xuxian@qq.com',1),
('00054','不想畢業',null,1),
('51234','好好說話','say@qq.com',2),
('83223','tellme',null,2),
('09527','老外學中文','foreigner@qq.com',2);

insert into course(name) values
('Java'),('中國傳統文化'),('計算機原理'),('語文'),('高階數學'),('英文');

insert into score(score, student_id, course_id) values
-- 黑旋風李逵
(70.5, 1, 1),(98.5, 1, 3),(33, 1, 5),(98, 1, 6),
-- 菩提老祖
(60, 2, 1),(59.5, 2, 5),
-- 白素貞
(33, 3, 1),(68, 3, 3),(99, 3, 5),
-- 許仙
(67, 4, 1),(23, 4, 3),(56, 4, 5),(72, 4, 6),
-- 不想畢業
(81, 5, 1),(37, 5, 5),
-- 好好說話
(56, 6, 2),(43, 6, 4),(79, 6, 6),
-- tellme
(80, 7, 2),(92, 7, 6);

3.4.1內連接

語法：
1、select 欄位名 from 表1 +別名(可不寫) inner join 表2+別名(可不寫) on
連接條件 and 其他條件

示例：
查詢白素貞同學的成績

查詢所有同學的總成績：
當我們寫一些比較復雜的SQL陳述句時，非常建議大家不要一次性寫完，這樣很容易寫錯，而SQL除錯不了，所以在實際寫的時候可以像下面這樣，一行一行的寫，每次寫一個條件，這樣就很清楚了，

select student.sn,student.name,student.qq_mail，
sum(score.score) from student
join score
on student.id=score.student_id 
group by score.student_id;

2、select 欄位名 from 表1 +別名(可以不寫) ，表2+別名(可以不寫) where 連接條件 and 其他條件

示例：
查詢白素貞同學的成績：

查詢所有學生的qq郵箱和總成績:

這兩種方式查詢的結果相同，

3.4.2 外連接

內連接和外連接的區別，在于對"空值"的區別，如果表里沒有"空值"(不是單純的NULL，而是泛指兩張表的資料對不上)，那內連接和外連接就沒有區別了，

eg:
先洗掉成績和學生表，新建一份在加上3個同學，

create table student(id int primary key auto_increment,name varchar(20));
create table score(studentId int ,score int );
insert into student values(null,'張三');
insert into student values(null,'李四');
insert into student values(null,'王五');

先來看一下當前表的資料

select * from student;

再給成績表中插入三行資料，但是做一點點區別，

insert into score values(1,55);
insert into score values(2,85);
insert into score values(4,91);

這里故意將本該是3的王五的id改成4，

這時候發生了資料不對應的情況，王五同學在分數表中沒有成績，id為4的同學在學生表中，沒有資訊，

這時候如果我們查詢兩張表的結果會怎么樣呢？
查詢每位同學的成績，姓名資訊，

select student.name,score.score from student join 
score on student.id=score.studentId;

內連接最后的結果既沒有王五同學的成績和姓名資訊，也沒有id為4的同學的資訊，

內連接相當于對上面兩張表取了交集，

外連接相對于內連接來說，分為左外連接和右外連接，在寫法上和內連接很相似，只需要在原來的陳述句中對于join前再加上left或者是right就表示內連接或者外連接了

左外連接：最終結果以join左側的表為主，盡可能的顯示左側表的資訊，

select student.name,score.score from student left
 join 
score on student.id=score.studentId;

右外連接：最終結果以join右側的表為主，盡可能的顯示右側表的資訊，

select student.name,score.score from student right
 join 
score on student.id=score.studentId;

3.4.3 自連接

自連接就是同一張表連接自身進行查詢，

eg：查詢語文成績比英語成績高的資訊

這次查詢和以往不同的是這是針對行和行的查詢，之前的例如查詢每位同學的成績，姓名資訊，這都是列與列之間的關系，

既然不好解決問題，不妨換個思路，那我就不進行行和行比較了，我想辦法轉換成列和列比較，

1、先看看它的笛卡爾積長什么樣
需要注意的是如果你直接select * from score,score ;查詢的話是會報錯的，需要你使用as起一個別名，

select * from score as a,score as b;

最終結果有四百行，我這里放一部分，

接下來在進一步篩選有用的資訊，挑選出同一個同學的資訊

select * from score as a,score as b where a.student_id=b.student_id;

現在縮減成了62行

最后直接一步到位，但是需要先回頭看看語文和英語的id，一個是4，另一個是6

insert into course(name) values
('Java'),('中國傳統文化'),('計算機原理'),('語文'),('高階數學'),('英文');

這樣我們在通過課程id篩選

select * from score as a,score as b 
where a.student_id=b.student_id and a.course_id=4 and b.course_id=6;

最終結果就只剩下一了，

再來練習一下java比計算機原理分數高的同學的資訊，我們這次直接到上圖的那一步，

select * from score as a,score as b
 where a.student_id=b.student_id and a.course_id=1 and b.course_id=3;

course_id為1的是java的分數，course_id為3的是計算機原理的分數

這樣下來，一行不僅包含了java的列，還包含了計算機原理的列，通過自連接就完成了行轉列的效果，

來看看最終結果，計算機原理成績比JAVA高的資訊

select * from score as a,score as b
 where a.student_id=b.student_id and a.course_id=1 and b.course_id=3 and a.score<b.score;

3.5、子查詢

子查詢就是其他sql陳述句中的查詢陳述句，所以也叫做嵌套查詢，

3.5.1單行子查詢

回傳一行子查詢的就是單行子查詢，
eg：查詢和“不想畢業”同學的同班同學，

還是一步步來，先查詢不想畢業同學的班級id，

select classes_id from student where name='不想畢業';

不想畢業同學在一班，在通過一班這個資訊找他的同學，

select name from student where classes_id=1;

我們用兩次查詢得到了想要的結果，那試試子查詢怎么樣，

select name from student where
 classes_id=(select classes_id from student where name='不想畢業');

在上面的SQL陳述句中先執行第二個SQL陳述句作為前面查詢name的條件，這就是子查詢，

3.5.2多行子查詢

多行子查詢回傳的是多條記錄

eg：查詢語文或者英語成績的資訊
1、先查詢語文或者英語成績的課程id

select id from course where name='語文' or name='英文';

2、再在課程表中，根據課程id找對應的資訊

select * from score where course_id=4 or course_id=6;

再用多行子查詢試試
多行子查詢得到的是多條記錄，外層查詢需要使用in關鍵字，單行子查詢只回傳一條記錄直接用=即可，

 select * from score where course_id in(
select id from course where name='語文' or name='英文');

3.6、合并查詢

把多個select查詢的結果合并成一個結果就是合并查詢，有些像取并集，它包含兩個關鍵字
union : 針對重復的進行去重
union all 不會針對重復的去重
union這有一個前提是兩邊的查詢得到的結果的列是一致的，包括數量和型別，

eg：查詢id小于3，或者名字為“英文”的課程

select * from course where id<3 or name ='英文';

使用union查詢

select * from course where id<3 union select * from course where name='英文';

效果是一樣的，

四、結尾

上面就是這次的所有內容了，祝大家作業順利，學習順利，沖沖沖