MySQL資料庫 ---MySQL表的增刪改查(進階)

文章目錄

MySQL表的增刪改查(進階)

1. 資料庫約束

1.1 約束型別

1.2 NULL約束

1.3 UNIQUE：唯一約束

1.4 DEFAULT：預設值約束

1.5 PRIMARY KEY：主鍵約束

1.6 FOREIGN KEY：外來鍵約束

1.7 CHECK約束（瞭解）

2. 表的設計

2.1 一對一

2.2 一對多

2.3 多對多

3. 新增

4. 查詢

4.1 聚合查詢

4.1.1 聚合函式

4.1.1.1 COUNT

4.1.1.2 SUM

4.1.1.3 AVG

4.1.1.4 MAX

4.1.1.5 MIN

案例求所有分數小於90的同學的平均分

4.1.2 GROUP BY子句

4.1.3 HAVING

顯示平均工資低於1500的角色和它的平均工資

4.2 聯合查詢

4.2.1 內連線

語法:

示例1: 查詢名字為"許仙"的同學的所有成績

示例2: 查詢所有同學的總成績,以及該同學的基本資訊

示例3: 查詢所有同學的每一科的成績,和同學的相關資訊

4.2.2 外連線

語法:

示例1: 查詢所有同學的成績，及同學的個人資訊，如果該同學沒有成績，也需要顯示

4.2.3 自連線

示例1: 所有計算機原理的成績 比 Java 成績高的同學

4.2.4 子查詢

單行子查詢：子查詢只有一行

示例1: 查詢與“不想畢業” 同學的同班同學：

多行子查詢：返回多行記錄的子查詢

查詢"語文"或者"英文"課程的成績資訊

4.2.5 合併查詢

示例1: 查詢 id < 3 或者 名字為 "英語"的課程

示例2: 查詢id小於3，或者名字為“Java”的課程

4.2.6 內連 外連 集合圖

MySQL表的增刪改查(進階)

1. 資料庫約束

約束型別 說明 示例

NULL約束 使用NOT NULL指定列不為空 name varchar(20) not null,

UNIQUE唯一約束 指定列為唯一的、不重複的 name varchar(20) unique,

DEFAULT預設值約束 指定列為空時的預設值 age int default 20,

主鍵約束 NOT NULL 和 UNIQUE 的結合 id int primary key,

外來鍵約束 關聯其他表的主鍵或唯一鍵 foreign key (欄位名) references 主表(列)

CHECK約束（瞭解） 保證列中的值符合指定的條件 check (sex =‘男’ or sex=‘女’)

1.1 約束型別

NOT NULL - 指示某列不能儲存 NULL 值。

UNIQUE - 保證某列的每行必須有唯一的值。

DEFAULT - 規定沒有給列賦值時的預設值。

PRIMARY KEY - NOT NULL 和 UNIQUE 的結合。確保某列（或兩個列多個列的結合）有唯一標識，有助於更容易更快速地找到表中的一個特定的記錄。

FOREIGN KEY - 保證一個表中的資料匹配另一個表中的值的參照完整性。

CHECK - 保證列中的值符合指定的條件。對於MySQL資料庫，對CHECK子句進行分析，但是忽略CHECK子句。

1.2 NULL約束

建立表時，可以指定某列不為空：

create table student (

    id int not null,

    name varchar(20),

    score decimal(3,1)

);

1

2

3

4

5

1.3 UNIQUE：唯一約束

create table student (

    id int unique,

    name varchar(20),

    score decimal(3,1)

);

1

2

3

4

5

1.4 DEFAULT：預設值約束

create table student (

    id int unique not null,

    name varchar(20) default 'unknown',

    score decimal(3,1)

);

1

2

3

4

5

1.5 PRIMARY KEY：主鍵約束

等價於 not null + unique

create table student (

    id int primary key,

    name varchar(20),

    score decimal(3,1)

);

1

2

3

4

5

如何保證主鍵不重複?人工保證不太靠譜.

可以藉助資料庫自動來生成.---- auto_increment

create table student (

    id int primary key auto_increment,

    name varchar(20),

    score decimal(3,1)

);

1

2

3

4

5

自增的特點是:

如果表中沒有任何的記錄,自增從1開始.

如果表中已經有記錄了,自增從上一條記錄往下自增.

如果中間某個資料刪了,再次插入資料,剛才刪掉的自增主鍵的值不好被重複利用

1.6 FOREIGN KEY：外來鍵約束

描述兩張表的之間的關聯關係

外來鍵用於關聯其他表的主鍵或唯一鍵，語法：

foreign key (欄位名) references 主表(列)

1

例:

create table class (

    id int primary key auto_increment,

    name varchar(20)

);

create table student (

    id int primary key auto_increment,

    name varchar(20),

    classId int,

    foreign key(classId) references class(id)

);

1

2

3

4

5

6

7

8

9

10

11

1.7 CHECK約束（瞭解）

MySQL使用時不報錯，但忽略該約束：

create table user_test (

id int,

name varchar(20),

sex varchar(1),

check (sex ='男' or sex='女')

);

1

2

3

4

5

6

2. 表的設計

2.1 一對一

2.2 一對多

2.3 多對多

多對多的關係兩者之間的對應關係是非常複雜的.

多對多這個關係複雜需要引入中間表來解決這個問題.

例如,描述每個同學的每個科目的考試成績.

先建立表來描述同學的資訊,然後建立表描述科目資訊.

create table student(

    id int primary key auto_increment,

    name varchar(20)

);

create table course(

    id int primary key auto_increment,

    name varchar(20)

);

insert into student values

(null,'甲'),

(null,'乙'),

(null,'丙'),

(null,'丁');

insert into course values

(null,'語文'),

(null,'數學'),

(null,'英語'),

(null,'物理'),

(null,'化學');

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

為了描述每個同學每一科考了多少分,就需要搞一箇中間表來描述.

create table score(

    courseId int,

    studentId int,

    score decimal(3,1)

);

insert into score values

(1,1,90);

1

2

3

4

5

6

7

8

如果想查詢"甲"這個同學的"語文"成績如何?

此時的查詢過程就會更復雜.

先找到甲的studentld

在找到語文的courseld

結合這兩個id再在score表中查詢

3. 新增

插入查詢結果

語法:

insert into [表名] select [列名],[列名]... from [表名];

1

案例:

create table user(

    id int primary key auto_increment,

    name varchar(20),

    description varchar(1000)

);

insert into user values

(null,'曹操','亂世梟雄'),

(null,'劉備','仁德之主'),

(null,'孫權','年輕有為');

create table user2(

    name varchar(20),

    description varchar(1000)

);

insert into user2 select name,description from user;

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

4. 查詢

4.1 聚合查詢

4.1.1 聚合函式

函式 說明

COUNT([DISTINCT] expr) 返回查詢到的資料的 數量

SUM([DISTINCT] expr) 返回查詢到的資料的 總和，不是數字沒有意義

AVG([DISTINCT] expr) 返回查詢到的資料的 平均值，不是數字沒有意義

MAX([DISTINCT] expr) 返回查詢到的資料的 最大值，不是數字沒有意義

MIN([DISTINCT] expr) 返回查詢到的資料的 最小值，不是數字沒有意義

4.1.1.1 COUNT

4.1.1.2 SUM

4.1.1.3 AVG

4.1.1.4 MAX

4.1.1.5 MIN

案例求所有分數小於90的同學的平均分

4.1.2 GROUP BY子句

SELECT 中使用 GROUP BY 子句可以對指定列進行分組查詢。需要滿足：使用 GROUP BY 進行分組查詢時， SELECT 指定的欄位必須是“分組依據欄位”，其他欄位若想出現在SELECT 中則必須包含在聚合函式中。

select column1, sum(column2), .. from table group by column1,column3;

1

案例:

create table emp(

id int primary key auto_increment,

name varchar(20) not null,

role varchar(20) not null,

salary numeric(11,2)

);

insert into emp(name, role, salary) values

('馬雲','服務員', 1000.20),

('馬化騰','遊戲陪玩', 2000.99),

('孫悟空','遊戲角色', 999.11),

('豬無能','遊戲角色', 333.5),

('沙和尚','遊戲角色', 700.33),

('隔壁老王','董事長', 12000.66);

1

2

3

4

5

6

7

8

9

10

11

12

13

14

查詢每個角色的最高工資、最低工資和平均工資

select role,max(salary),min(salary),avg(salary) from emp group by role;

1

4.1.3 HAVING

GROUP BY 子句進行分組以後，需要對分組結果再進行條件過濾時，不能使用 WHERE 語句，而需要用HAVING

顯示平均工資低於1500的角色和它的平均工資

select role,avg(salary) from emp group by role having avg(salary) <1500;

1

4.2 聯合查詢

實現聯合查詢的基本機制 : 笛卡爾積

測試資料:

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

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

4.2.1 內連線

語法:

select 欄位 from 表1 別名1 [inner] join 表2 別名2 on 連線條件 and 其他條件;

select 欄位 from 表1 別名1,表2 別名2 where 連線條件 and 其他條件;

1

2

注:多表查詢時,寫列的時候要寫成[表名].[列名]

示例1: 查詢名字為"許仙"的同學的所有成績

思路 : 許仙在 student 表中 ,成績是score表, 對兩個表進行笛卡爾積,如何按照條件篩選,名字為許仙,id相同.

select score.score from student,score where student.id = score.student_id and student.name = '許仙';

select score.score from student inner join score on student.id = score.student_id and student.name = '許仙';

1

2

示例2: 查詢所有同學的總成績,以及該同學的基本資訊

思路 : 同學資訊在student表中,成績是score表,對兩個表進行笛卡爾積.然後按照篩選條件篩選.

select student.id,student.name, sum(score.score) from student,score where student.id = score.student_id group by student.id;

1

注: 如果某一列若干行的值已經相同的了,group by 沒影響

如果某一個列若干行不相同,group by 最終就只剩下一條記錄.

示例3: 查詢所有同學的每一科的成績,和同學的相關資訊

思路 : 需要3張表 studednt表 score表 course表

先對3張表進行笛卡爾積. 然後根據id進行篩選

select student.id,student.name,course.name,score.score from student,score,course where student.id = score.student_id and course.id = score.course_id;

1

4.2.2 外連線

外連線分為左外連線和右外連線。如果聯合查詢，左側的表完全顯示我們就說是左外連線；右側的表完全顯示我們就說是右外連線。

語法:

-- 左外連線，表1完全顯示

select 欄位名 from 表名1 left join 表名2 on 連線條件;

-- 右外連線，表2完全顯示

select 欄位 from 表名1 right join 表名2 on 連線條件;

1

2

3

4

示例1: 查詢所有同學的成績，及同學的個人資訊，如果該同學沒有成績，也需要顯示

select student.id,student.name,course.name,score.score from student left join score on student.id = score.student_id left join course on score.course_id = course.id;

1

4.2.3 自連線

自連線是指在同一張表連線自身進行查詢

示例1: 所有計算機原理的成績 比 Java 成績高的同學

先找到Java和計算機原理課程id — 計算機組成原理id=3 javaid = 1

對score表 進行 笛卡爾積 (score表 和 score表 分為命名為 s1 和 s2)

篩選條件1 s1.student_id = s2.student.id

篩選條件2 s1.course_id = 3 s2.course_id =1

篩選條件3 s1.score > s2.score

select s1.student_id from score s1,score s2 where s1.student_id = s2.student_id and s1.course_id = 3 and s2.course_id = 1 and s1.score > s2.score;

1

4.2.4 子查詢

子查詢是指嵌入在其他sql語句中的select語句，也叫巢狀查詢

單行子查詢：子查詢只有一行

示例1: 查詢與“不想畢業” 同學的同班同學：

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

1

多行子查詢：返回多行記錄的子查詢

查詢"語文"或者"英文"課程的成績資訊

[NOT] IN關鍵字：

-- 使用in

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

-- 使用 not in

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

1

2

3

4

5

6

2. [NOT] EXISTS關鍵字：

-- 使用exists

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

-- 使用not exists

select * from score where not exists (select score.course_id from course where (name != '語文' and name != '英文') and course.id = score.course_id);

1

2

3

4

5

如果子表查詢的結果集合比較小,就使用in

如果子表查詢的結果集合比較大,而主表的集合小,就使用exists

4.2.5 合併查詢

相當於把多個查詢的結果集合合併成一個集合

可以使用集合操作符union，union all

示例1: 查詢 id < 3 或者 名字為 "英語"的課程

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

1

如果兩個查詢結果中存在相同的記錄,就會只保留一個

如果不想去重,可以使用 union all即可.

示例2: 查詢id小於3，或者名字為“Java”的課程

select * from course where id<3 union all select * from course where name='Java';

1