1 數(shù)據(jù)庫(kù)概述

• 數(shù)據(jù)庫(kù)：英文為 DataBase，簡(jiǎn)稱DB，它是存儲(chǔ)和管理數(shù)據(jù)的倉(cāng)庫(kù)。

• 數(shù)據(jù)庫(kù)管理系統(tǒng)：DataBase Management System (DBMS)，操縱和管理數(shù)據(jù)庫(kù)的大型軟件。

• SQL：Structured Query Language，簡(jiǎn)稱SQL，結(jié)構(gòu)化查詢語(yǔ)言，它是操作關(guān)系型數(shù)據(jù)庫(kù)的編程語(yǔ)言，定義了一套操作關(guān)系型數(shù)據(jù)庫(kù)的統(tǒng)一標(biāo)準(zhǔn)。

  • 關(guān)系型數(shù)據(jù)庫(kù)（RDBMS）：建立在關(guān)系模型基礎(chǔ)上，由多張相互連接的二維表組成的數(shù)據(jù)庫(kù)。
  • 而所謂二維表，指的是由行和列組成的表，如下圖：

  [外鏈圖片轉(zhuǎn)存失敗,源站可能有防盜鏈機(jī)制,建議將圖片保存下來(lái)直接上傳(img-J6yz7641-1692540509322)(E:\javaweb\筆記\day06\img\圖1.png)]

  • 二維表的優(yōu)點(diǎn)：

    • 使用表存儲(chǔ)數(shù)據(jù)，格式統(tǒng)一，便于維護(hù)

    • 使用SQL語(yǔ)言操作，標(biāo)準(zhǔn)統(tǒng)一，使用方便，可用于復(fù)雜查詢

2 MySQL概述

2.1 MySQL安裝

官網(wǎng)下載地址

2.1.1 解壓&添加環(huán)境變量

• 首先解壓對(duì)應(yīng)的安裝包到對(duì)應(yīng)的文件夾目錄下，然后配置環(huán)境變量到path中

如何驗(yàn)證是否添加成功？

右鍵開(kāi)始菜單(就是屏幕左下角)，選擇命令提示符(管理員)（或者搜索cmd，一定要以管理員身份打開(kāi))，打開(kāi)黑框，敲入mysql，回車。
如果提示Can't connect to MySQL server on 'localhost'則證明添加成功；
如果提示mysql不是內(nèi)部或外部命令，也不是可運(yùn)行的程序或批處理文件則表示添加添加失敗，請(qǐng)重新檢查步驟并重試。

2.1.2 初始化MySQL

以管理員身份，運(yùn)行命令行窗口：
在剛才的命令行中，輸入如下的指令：

mysqld --initialize-insecure

2.1.3 注冊(cè)MySQL服務(wù)

命令行（注意必須以管理員身份啟動(dòng)）中，輸入如下的指令，回車執(zhí)行：

mysqld -install

2.1.4 啟動(dòng)MySQL服務(wù)

在黑框里敲入net start mysql，回車。

net start mysql  // 啟動(dòng)mysql服務(wù)
    
net stop mysql  // 停止mysql服務(wù)

2.1.5 修改默認(rèn)賬戶密碼

在黑框里敲入mysqladmin -u root password 1234，這里的1234就是指默認(rèn)管理員(即root賬戶)的密碼，可以自行修改成你喜歡的。

mysqladmin -u root password 1234

2.1.6 登錄MySQL

右鍵開(kāi)始菜單，選擇命令提示符，打開(kāi)黑框。
在黑框中輸入，mysql -uroot -p，回車，出現(xiàn)下圖且左下角為password>，然后正確輸入密碼，則登錄成功。

退出mysql：

exit
quit

登陸參數(shù)：

mysql -u用戶名 -p密碼 -h要連接的mysql服務(wù)器的ip地址(默認(rèn)127.0.0.1) -P端口號(hào)(默認(rèn)3306)

2.2 卸載MySQL

以管理員身份打開(kāi)命令提示符窗口：

1. 敲入net stop mysql，回車。

net stop mysql

2. 再敲入mysqld -remove mysql，回車。

mysqld -remove mysql

3. 最后刪除MySQL目錄及相關(guān)的環(huán)境變量。

至此，MySQL卸載完成！

2.3 連接服務(wù)器上部署的數(shù)據(jù)庫(kù)

mysql -u用戶名 -p密碼 [-h數(shù)據(jù)庫(kù)服務(wù)器的IP地址 -P端口號(hào)]

2.4 數(shù)據(jù)模型

MySQL是關(guān)系型數(shù)據(jù)庫(kù)，是基于二維表進(jìn)行數(shù)據(jù)存儲(chǔ)的，具體的結(jié)構(gòu)圖下:

• 通過(guò)MySQL客戶端連接數(shù)據(jù)庫(kù)管理系統(tǒng)DBMS，然后通過(guò)DBMS操作數(shù)據(jù)庫(kù)
• 使用MySQL客戶端，向數(shù)據(jù)庫(kù)管理系統(tǒng)發(fā)送一條SQL語(yǔ)句，由數(shù)據(jù)庫(kù)管理系統(tǒng)根據(jù)SQL語(yǔ)句指令去操作數(shù)據(jù)庫(kù)中的表結(jié)構(gòu)及數(shù)據(jù)
• 一個(gè)數(shù)據(jù)庫(kù)服務(wù)器中可以創(chuàng)建多個(gè)數(shù)據(jù)庫(kù)，一個(gè)數(shù)據(jù)庫(kù)中也可以包含多張表，而一張表中又可以包含多行記錄。

在Mysql數(shù)據(jù)庫(kù)服務(wù)器當(dāng)中存儲(chǔ)數(shù)據(jù)，需要：

1. 先去創(chuàng)建數(shù)據(jù)庫(kù)（可以創(chuàng)建多個(gè)數(shù)據(jù)庫(kù)，之間是相互獨(dú)立的）
2. 在數(shù)據(jù)庫(kù)下再去創(chuàng)建數(shù)據(jù)表（一個(gè)數(shù)據(jù)庫(kù)下可以創(chuàng)建多張表）
3. 再將數(shù)據(jù)存放在數(shù)據(jù)表中（一張表可以存儲(chǔ)多行數(shù)據(jù)）

2.5 SQL簡(jiǎn)介

2.5.1 SQL通用語(yǔ)法

1. SQL語(yǔ)句可以單行或多行書寫，以分號(hào)結(jié)尾。
2. SQL語(yǔ)句可以使用空格/縮進(jìn)來(lái)增強(qiáng)語(yǔ)句的可讀性。
3. MySQL數(shù)據(jù)庫(kù)的SQL語(yǔ)句不區(qū)分大小寫。
4. 注釋：
   • 單行注釋：-- 注釋內(nèi)容 或 # 注釋內(nèi)容
   • 多行注釋： /* 注釋內(nèi)容 */

2.3.2 分類

SQL語(yǔ)句根據(jù)其功能被分為四大類：DDL、DML、DQL、DCL

3 數(shù)據(jù)庫(kù)設(shè)計(jì)-DDL

3.1 數(shù)據(jù)庫(kù)的項(xiàng)目開(kāi)發(fā)流程

在項(xiàng)目開(kāi)發(fā)中，針對(duì)于數(shù)據(jù)庫(kù)來(lái)說(shuō)，主要包括三個(gè)階段：

1. 數(shù)據(jù)庫(kù)設(shè)計(jì)階段
   • 參照頁(yè)面原型以及需求文檔設(shè)計(jì)數(shù)據(jù)庫(kù)表結(jié)構(gòu)
2. 數(shù)據(jù)庫(kù)操作階段
   • 根據(jù)業(yè)務(wù)功能的實(shí)現(xiàn)，編寫SQL語(yǔ)句對(duì)數(shù)據(jù)表中的數(shù)據(jù)進(jìn)行增刪改查操作
3. 數(shù)據(jù)庫(kù)優(yōu)化階段
   • 通過(guò)數(shù)據(jù)庫(kù)的優(yōu)化來(lái)提高數(shù)據(jù)庫(kù)的訪問(wèn)性能。優(yōu)化手段：索引、SQL優(yōu)化、分庫(kù)分表等

3.2 數(shù)據(jù)庫(kù)操作

DDL中數(shù)據(jù)庫(kù)的常見(jiàn)操作：查詢、創(chuàng)建、使用、刪除。

3.2.1 查詢數(shù)據(jù)庫(kù)

• 查詢所有數(shù)據(jù)庫(kù)：

show databases;

• 查詢當(dāng)前數(shù)據(jù)庫(kù)：

select database();

• 我們要操作某一個(gè)數(shù)據(jù)庫(kù)，必須要切換到對(duì)應(yīng)的數(shù)據(jù)庫(kù)中。

  • 通過(guò)指令：select database() ，就可以查詢到當(dāng)前所處的數(shù)據(jù)庫(kù)

3.2.2 創(chuàng)建數(shù)據(jù)庫(kù)

語(yǔ)法：

//這里包括下面的方括號(hào)里面的代碼都是可選的
create database [ if not exists ] 數(shù)據(jù)庫(kù)名;

注意：在同一個(gè)數(shù)據(jù)庫(kù)服務(wù)器中，不能創(chuàng)建兩個(gè)名稱相同的數(shù)據(jù)庫(kù)，否則將會(huì)報(bào)錯(cuò), 故我們?cè)谶@里假如了盼盼，當(dāng)不存在數(shù)據(jù)庫(kù)名的時(shí)候才創(chuàng)建對(duì)應(yīng)的數(shù)據(jù)庫(kù)

3.2.3 使用數(shù)據(jù)庫(kù)

語(yǔ)法：

use 數(shù)據(jù)庫(kù)名 ;

我們要操作某一個(gè)數(shù)據(jù)庫(kù)下的表時(shí)，就需要通過(guò)該指令，切換到對(duì)應(yīng)的數(shù)據(jù)庫(kù)下，否則不能操作。

2.2.4 刪除數(shù)據(jù)庫(kù)

語(yǔ)法：

drop database [ if exists ] 數(shù)據(jù)庫(kù)名 ;

如果刪除一個(gè)不存在的數(shù)據(jù)庫(kù)，將會(huì)報(bào)錯(cuò)。

可以加上參數(shù) if exists ，如果數(shù)據(jù)庫(kù)存在，再執(zhí)行刪除，否則不執(zhí)行刪除。

3.3 圖形化工具

在命令行當(dāng)中來(lái)敲這些SQL語(yǔ)句很不方便，主要的原因有以下 3 點(diǎn)：

1. 沒(méi)有任何代碼提示。（全靠記憶，容易敲錯(cuò)字母造成執(zhí)行報(bào)錯(cuò)）
2. 操作繁瑣，影響開(kāi)發(fā)效率。（所有的功能操作都是通過(guò)SQL語(yǔ)句來(lái)完成的）
3. 編寫過(guò)的SQL代碼無(wú)法保存。

在項(xiàng)目開(kāi)發(fā)當(dāng)中，通常為了提高開(kāi)發(fā)效率，都會(huì)借助于現(xiàn)成的圖形化管理工具來(lái)操作數(shù)據(jù)庫(kù)。
目前MySQL主流的圖形化界面工具有以下幾種：

• SQLyog
• Navicat
• DataGrip
• IDEA

DataGrip是JetBrains旗下的一款數(shù)據(jù)庫(kù)管理工具，是管理和開(kāi)發(fā)MySQL、Oracle、PostgreSQL的理想解決方案。

官網(wǎng)鏈接

3.4 表操作

關(guān)于表結(jié)構(gòu)的操作也是包含四個(gè)部分：創(chuàng)建表、查詢表、修改表、刪除表。工作中一般都是直接基于圖形化界面操作。

3.4.1 創(chuàng)建

• 語(yǔ)法：

create table  表名(
	字段1  字段1類型 [約束]  [comment  字段1注釋 ],
	字段2  字段2類型 [約束]  [comment  字段2注釋 ],
	......
	字段n  字段n類型 [約束]  [comment  字段n注釋 ] 
) [ comment  表注釋 ] ;

注意： [ ] 中的內(nèi)容為可選參數(shù)； 最后一個(gè)字段后面沒(méi)有逗號(hào)

• 約束

概念：所謂約束就是作用在表中字段上的規(guī)則，用于限制存儲(chǔ)在表中的數(shù)據(jù)。

作用：就是來(lái)保證數(shù)據(jù)庫(kù)當(dāng)中數(shù)據(jù)的正確性、有效性和完整性。（后面的學(xué)習(xí)會(huì)驗(yàn)證這些）

在MySQL數(shù)據(jù)庫(kù)當(dāng)中，提供了以下5種約束：

注意：約束是作用于表中字段上的，可以在創(chuàng)建表/修改表的時(shí)候添加約束。

主鍵自增：auto_increment

• 每次插入新的行記錄時(shí)，數(shù)據(jù)庫(kù)自動(dòng)生成字段(主鍵)下的值
• 具有auto_increment的數(shù)據(jù)列是一個(gè)正數(shù)序列開(kāi)始增長(zhǎng)(從1開(kāi)始自增)

• 數(shù)據(jù)類型

MySQL中的數(shù)據(jù)類型有很多，主要分為三類：數(shù)值類型、字符串類型、日期時(shí)間類型。

數(shù)值類型

字符串類型

日期時(shí)間類型

3.4.2 查詢

• 查詢當(dāng)前數(shù)據(jù)庫(kù)所有表

show tables;

• 查看指定表結(jié)構(gòu)

desc 表名 ;#可以查看指定表的字段、字段的類型、是否可以為NULL、是否存在默認(rèn)值等信息

• 查詢指定表的建表語(yǔ)句

show create table 表名 ;

3.4.3 修改

• 刪除表語(yǔ)法：

drop  table [ if exists ]  表名;

if exists ：只有表名存在時(shí)才會(huì)刪除該表，表名不存在，則不執(zhí)行刪除操作(如果不加該參數(shù)項(xiàng)，刪除一張不存在的表，執(zhí)行將會(huì)報(bào)錯(cuò))。

4 數(shù)據(jù)庫(kù)操作-DML

DML英文全稱是Data Manipulation Language(數(shù)據(jù)操作語(yǔ)言)，用來(lái)對(duì)數(shù)據(jù)庫(kù)中表的數(shù)據(jù)記錄進(jìn)行增、刪、改操作。

• 添加數(shù)據(jù)（INSERT）
• 修改數(shù)據(jù)（UPDATE）
• 刪除數(shù)據(jù)（DELETE）

4.1 增加(insert)

insert語(yǔ)法：

• 向指定字段添加數(shù)據(jù)

  insert into 表名 (字段名1, 字段名2) values (值1, 值2);

• 全部字段添加數(shù)據(jù)

  insert into 表名 values (值1, 值2, ...);

• 批量添加數(shù)據(jù)（指定字段）

  insert into 表名 (字段名1, 字段名2) values (值1, 值2), (值1, 值2);

• 批量添加數(shù)據(jù)（全部字段）

  insert into 表名 values (值1, 值2, ...), (值1, 值2, ...);

Insert操作的注意事項(xiàng)：

1. 插入數(shù)據(jù)時(shí)，指定的字段順序需要與值的順序是一一對(duì)應(yīng)的。

2. 字符串和日期型數(shù)據(jù)應(yīng)該包含在引號(hào)中。

3. 插入的數(shù)據(jù)大小，應(yīng)該在字段的規(guī)定范圍內(nèi)。

4.2 修改(update)

• update語(yǔ)法：

update 表名 set 字段名1 = 值1 , 字段名2 = 值2 , .... [where 條件] ;

注意事項(xiàng):

1. 修改語(yǔ)句的條件可以有，也可以沒(méi)有，如果沒(méi)有條件，則會(huì)修改整張表的所有數(shù)據(jù)。

2. 在修改數(shù)據(jù)時(shí)，一般需要同時(shí)修改公共字段update_time，將其修改為當(dāng)前操作時(shí)間。

4.3 刪除(delete)

• delete語(yǔ)法：

delete from 表名  [where  條件] ;

注意事項(xiàng):

? ? DELETE 語(yǔ)句的條件可以有，也可以沒(méi)有，如果沒(méi)有條件，則會(huì)刪除整張表的所有數(shù)據(jù)。

? ? DELETE 語(yǔ)句不能刪除某一個(gè)字段的值(可以使用UPDATE，將該字段值置為NULL即可)。

? ? 當(dāng)進(jìn)行刪除全部數(shù)據(jù)操作時(shí)，會(huì)提示詢問(wèn)是否確認(rèn)刪除所有數(shù)據(jù)，直接點(diǎn)擊Execute即可。

5 數(shù)據(jù)庫(kù)操作-DQL

DQL英文全稱是Data Query Language(數(shù)據(jù)查詢語(yǔ)言)，用來(lái)查詢數(shù)據(jù)庫(kù)表中的記錄。

查詢關(guān)鍵字：SELECT

5.1 語(yǔ)法

DQL查詢語(yǔ)句，語(yǔ)法結(jié)構(gòu)如下：

SELECT
	字段列表
FROM
	表名列表
WHERE
	條件列表
GROUP  BY
	分組字段列表
HAVING
	分組后條件列表
ORDER BY
	排序字段列表
LIMIT
	分頁(yè)參數(shù)

5.2 基本查詢（不帶任何條件）

在基本查詢的DQL語(yǔ)句中，不帶任何的查詢條件，語(yǔ)法如下：

• 查詢多個(gè)字段

  select 字段1, 字段2, 字段3 from  表名;
  

• 查詢所有字段（通配符）

  select *  from  表名;
  

*號(hào)代表查詢所有字段，在實(shí)際開(kāi)發(fā)中盡量少用（不直觀、影響效率）

• 設(shè)置別名

  select 字段1 [ as 別名1 ] , 字段2 [ as 別名2 ]  from  表名;
  

• 去除重復(fù)記錄

  select distinct 字段列表 from  表名;
  

5.3 條件查詢（where）

語(yǔ)法：

select  字段列表  from   表名   where   條件列表 ; -- 條件列表：意味著可以有多個(gè)條件

學(xué)習(xí)條件查詢就是學(xué)習(xí)條件的構(gòu)建方式，而在SQL語(yǔ)句當(dāng)中構(gòu)造條件的運(yùn)算符分為兩類：

• 比較運(yùn)算符
• 邏輯運(yùn)算符

常用的比較運(yùn)算符如下:

常用的邏輯運(yùn)算符如下:

5.4 分組查詢（group by）

5.4.1 聚合函數(shù)

之前我們做的查詢都是橫向查詢，就是根據(jù)條件一行一行的進(jìn)行判斷，而使用聚合函數(shù)查詢就是縱向查詢，它是對(duì)一列的值進(jìn)行計(jì)算，然后返回一個(gè)結(jié)果值。（將一列數(shù)據(jù)作為一個(gè)整體，進(jìn)行縱向計(jì)算）

語(yǔ)法：

select  聚合函數(shù)(字段列表)  from  表名 ;

注意 : 聚合函數(shù)會(huì)忽略空值，對(duì)NULL值不作為統(tǒng)計(jì)。

常用聚合函數(shù)：

count ：按照列去統(tǒng)計(jì)有多少行數(shù)據(jù)。

• 在根據(jù)指定的列統(tǒng)計(jì)的時(shí)候，如果這一列中有null的行，該行不會(huì)被統(tǒng)計(jì)在其中。

sum ：計(jì)算指定列的數(shù)值和，如果不是數(shù)值類型，那么計(jì)算結(jié)果為0

max ：計(jì)算指定列的最大值

min ：計(jì)算指定列的最小值

avg ：計(jì)算指定列的平均值

5.4.2 分組查詢

分組： 按照某一列或者某幾列，把相同的數(shù)據(jù)進(jìn)行合并輸出。

分組其實(shí)就是按列進(jìn)行分類(指定列下相同的數(shù)據(jù)歸為一類)，然后可以對(duì)分類完的數(shù)據(jù)進(jìn)行合并計(jì)算。

分組查詢通常會(huì)使用聚合函數(shù)進(jìn)行計(jì)算。

語(yǔ)法：

select  字段列表  from  表名  [where 條件]  group by 分組字段名  [having 分組后過(guò)濾條件];

5.5 排序查詢（order by）

排序在日常開(kāi)發(fā)中是非常常見(jiàn)的一個(gè)操作，有升序排序，也有降序排序。

語(yǔ)法：

select  字段列表  
from   表名   
[where  條件列表] 
[group by  分組字段 ] 
order  by  字段1  排序方式1 , 字段2  排序方式2 … ;

• 排序方式：

  • ASC ：升序（默認(rèn)值）

  • DESC：降序

5.6 分頁(yè)查詢（limit）

分頁(yè)查詢語(yǔ)法：

select  字段列表  from   表名  limit  起始索引, 查詢記錄數(shù) ;

6 多表設(shè)計(jì)

項(xiàng)目開(kāi)發(fā)中，在進(jìn)行數(shù)據(jù)庫(kù)表結(jié)構(gòu)設(shè)計(jì)時(shí)，會(huì)根據(jù)業(yè)務(wù)需求及業(yè)務(wù)模塊之間的關(guān)系，分析并設(shè)計(jì)表結(jié)構(gòu)，由于業(yè)務(wù)之間相互關(guān)聯(lián)，所以各個(gè)表結(jié)構(gòu)之間也存在著各種聯(lián)系，基本上分為三種：

• 一對(duì)多(多對(duì)一)

• 多對(duì)多

• 一對(duì)一

而在實(shí)際開(kāi)發(fā)中，基于圖形化界面會(huì)更加便捷，這里就不對(duì)如何操作圖形化界面做過(guò)多贅述

6.1 外鍵約束

外鍵約束：讓兩張表的數(shù)據(jù)建立連接，保證數(shù)據(jù)的一致性和完整性。

對(duì)應(yīng)的關(guān)鍵字：foreign key
外鍵約束的語(yǔ)法：

-- 創(chuàng)建表時(shí)指定
create table 表名(
	字段名    數(shù)據(jù)類型,
	...
	[constraint]   [外鍵名稱]  foreign  key (外鍵字段名)   references   主表 (主表列名)	
);


-- 建完表后，添加外鍵
alter table  表名  add constraint  外鍵名稱  foreign key(外鍵字段名) references 主表(主表列名);

當(dāng)我們添加外鍵約束時(shí)，我們得保證當(dāng)前數(shù)據(jù)庫(kù)表中的數(shù)據(jù)是完整的。

• 外鍵約束（foreign key）：保證了數(shù)據(jù)的完整性和一致性。

物理外鍵和邏輯外鍵

• 物理外鍵

  • 概念：使用foreign key定義外鍵關(guān)聯(lián)另外一張表。
  • 缺點(diǎn)：
    • 影響增、刪、改的效率（需要檢查外鍵關(guān)系）。
    • 僅用于單節(jié)點(diǎn)數(shù)據(jù)庫(kù)，不適用與分布式、集群場(chǎng)景。
    • 容易引發(fā)數(shù)據(jù)庫(kù)的死鎖問(wèn)題，消耗性能。

• 邏輯外鍵

  • 概念：在業(yè)務(wù)層邏輯中，解決外鍵關(guān)聯(lián)。
  • 通過(guò)邏輯外鍵，就可以很方便的解決上述問(wèn)題。

在現(xiàn)在的企業(yè)開(kāi)發(fā)中，很少會(huì)使用物理外鍵，都是使用邏輯外鍵。 甚至在一些數(shù)據(jù)庫(kù)開(kāi)發(fā)規(guī)范中，會(huì)明確指出禁止使用物理外鍵 foreign key

6.2 一對(duì)多

一對(duì)多關(guān)系實(shí)現(xiàn)：在數(shù)據(jù)庫(kù)表中多的一方，添加字段，來(lái)關(guān)聯(lián)屬于一這方的主鍵。

6.3 一對(duì)一

一對(duì)一關(guān)系表在實(shí)際開(kāi)發(fā)中應(yīng)用起來(lái)比較簡(jiǎn)單，通常是用來(lái)做單表的拆分，也就是將一張大表拆分成兩張小表，將大表中的一些基礎(chǔ)字段放在一張表當(dāng)中，將其他的字段放在另外一張表當(dāng)中，以此來(lái)提高數(shù)據(jù)的操作效率。

應(yīng)用場(chǎng)景舉例：

如果在業(yè)務(wù)系統(tǒng)當(dāng)中，對(duì)用戶的基本信息查詢頻率特別的高，但是對(duì)于用戶的身份信息查詢頻率很低，此時(shí)出于提高查詢效率的考慮，我就可以將這張大表拆分成兩張小表，第一張表存放的是用戶的基本信息，而第二張表存放的就是用戶的身份信息。他們兩者之間一對(duì)一的關(guān)系，一個(gè)用戶只能對(duì)應(yīng)一個(gè)身份證，而一個(gè)身份證也只能關(guān)聯(lián)一個(gè)用戶。

• 一對(duì)一 ：在任意一方加入外鍵，關(guān)聯(lián)另外一方的主鍵，并且設(shè)置外鍵為唯一的(UNIQUE)

6.4 多對(duì)多

多對(duì)多的關(guān)系在開(kāi)發(fā)中屬于也比較常見(jiàn)的。比如：學(xué)生和老師的關(guān)系，一個(gè)學(xué)生可以有多個(gè)授課老師，一個(gè)授課老師也可以有多個(gè)學(xué)生。在比如：學(xué)生和課程的關(guān)系，一個(gè)學(xué)生可以選修多門課程，一個(gè)課程也可以供多個(gè)學(xué)生選修。

7 多表查詢

7.1 分類

多表查詢：查詢時(shí)從多張表中獲取所需數(shù)據(jù)

單表查詢的SQL語(yǔ)句：select 字段列表 from 表名;

那么要執(zhí)行多表查詢，只需要使用逗號(hào)分隔多張表即可，如： select 字段列表 from 表1, 表2;

• 笛卡爾積：笛卡爾乘積是指在數(shù)學(xué)中，兩個(gè)集合(A集合和B集合)的所有組合情況。

在多表查詢時(shí)，需要消除無(wú)效的笛卡爾積，只保留表關(guān)聯(lián)部分的數(shù)據(jù)
在SQL語(yǔ)句中，如何去除無(wú)效的笛卡爾積呢？只需要給多表查詢加上連接查詢的條件即可。

多表查詢可以分為：

1. 連接查詢

   • 內(nèi)連接：相當(dāng)于查詢A、B交集部分?jǐn)?shù)據(jù)
     

2. 外連接

   • 左外連接：查詢左表所有數(shù)據(jù)(包括兩張表交集部分?jǐn)?shù)據(jù))

   • 右外連接：查詢右表所有數(shù)據(jù)(包括兩張表交集部分?jǐn)?shù)據(jù))

3. 子查詢

7.2 內(nèi)連接

內(nèi)連接查詢：查詢兩表或多表中交集部分?jǐn)?shù)據(jù)。

內(nèi)連接從語(yǔ)法上可以分為：

• 隱式內(nèi)連接

• 顯式內(nèi)連接

隱式內(nèi)連接語(yǔ)法：

select  字段列表   from   表1 , 表2   where  條件 ... ;

顯式內(nèi)連接語(yǔ)法：

select  字段列表   from   表1  [ inner ]  join 表2  on  連接條件 ... ;

多表查詢時(shí)給表起別名：

tableA  as  別名1  ,  tableB  as  別名2 ;

tableA  別名1  ,  tableB  別名2 ;

注意事項(xiàng):

一旦為表起了別名，就不能再使用表名來(lái)指定對(duì)應(yīng)的字段了，此時(shí)只能夠使用別名來(lái)指定字段。

7.3 外連接

外連接分為兩種：左外連接 和 右外連接。

左外連接語(yǔ)法結(jié)構(gòu)：

select  字段列表   from   表1  left  [ outer ]  join 表2  on  連接條件 ... ;

左外連接相當(dāng)于查詢表1(左表)的所有數(shù)據(jù)，當(dāng)然也包含表1和表2交集部分的數(shù)據(jù)。

右外連接語(yǔ)法結(jié)構(gòu)：

select  字段列表   from   表1  right  [ outer ]  join 表2  on  連接條件 ... ;

右外連接相當(dāng)于查詢表2(右表)的所有數(shù)據(jù)，當(dāng)然也包含表1和表2交集部分的數(shù)據(jù)。

注意事項(xiàng)：

左外連接和右外連接是可以相互替換的，只需要調(diào)整連接查詢時(shí)SQL語(yǔ)句中表的先后順序就可以了。而我們?cè)谌粘ｉ_(kāi)發(fā)使用時(shí)，更偏向于左外連接。

7.4 子查詢

7.4.1 介紹

SQL語(yǔ)句中嵌套select語(yǔ)句，稱為嵌套查詢，又稱子查詢。

SELECT  *  FROM   t1   WHERE  column1 =  ( SELECT  column1  FROM  t2 ... );

子查詢外部的語(yǔ)句可以是insert / update / delete / select 的任何一個(gè)，最常見(jiàn)的是 select。

根據(jù)子查詢結(jié)果的不同分為：

1. 標(biāo)量子查詢（子查詢結(jié)果為單個(gè)值[一行一列]）

2. 列子查詢（子查詢結(jié)果為一列，但可以是多行）

3. 行子查詢（子查詢結(jié)果為一行，但可以是多列）

4. 表子查詢（子查詢結(jié)果為多行多列[相當(dāng)于子查詢結(jié)果是一張表]）

子查詢可以書寫的位置：

1. where之后
2. from之后
3. select之后

7.4.2 標(biāo)量子查詢

子查詢返回的結(jié)果是單個(gè)值(數(shù)字、字符串、日期等)，最簡(jiǎn)單的形式，這種子查詢稱為標(biāo)量子查詢。

常用的操作符： = <> > >= < <=

7.4.3 列子查詢

子查詢返回的結(jié)果是一列(可以是多行)，這種子查詢稱為列子查詢。

常用的操作符：

7.4.4 行子查詢

子查詢返回的結(jié)果是一行(可以是多列)，這種子查詢稱為行子查詢。

常用的操作符：= 、<> 、IN 、NOT IN

7.4.5 表子查詢

子查詢返回的結(jié)果是多行多列，常作為臨時(shí)表，這種子查詢稱為表子查詢。

7.5 相關(guān)案例及代碼

-- 部門管理//數(shù)據(jù)準(zhǔn)備
create table tb_dept
(
    id          int unsigned primary key auto_increment comment '主鍵ID',
    name        varchar(10) not null unique comment '部門名稱',
    create_time datetime    not null comment '創(chuàng)建時(shí)間',
    update_time datetime    not null comment '修改時(shí)間'
) comment '部門表';

insert into tb_dept (id, name, create_time, update_time)
values (1, '學(xué)工部', now(), now()),
       (2, '教研部', now(), now()),
       (3, '咨詢部', now(), now()),
       (4, '就業(yè)部', now(), now()),
       (5, '人事部', now(), now());


-- 員工管理
create table tb_emp
(
    id          int unsigned primary key auto_increment comment 'ID',
    username    varchar(20)      not null unique comment '用戶名',
    password    varchar(32) default '123456' comment '密碼',
    name        varchar(10)      not null comment '姓名',
    gender      tinyint unsigned not null comment '性別, 說(shuō)明: 1 男, 2 女',
    image       varchar(300) comment '圖像',
    job         tinyint unsigned comment '職位, 說(shuō)明: 1 班主任,2 講師, 3 學(xué)工主管, 4 教研主管, 5 咨詢師',
    entrydate   date comment '入職時(shí)間',
    dept_id     int unsigned comment '部門ID',
    create_time datetime         not null comment '創(chuàng)建時(shí)間',
    update_time datetime         not null comment '修改時(shí)間'
) comment '員工表';

INSERT INTO tb_emp
(id, username, password, name, gender, image, job, entrydate, dept_id, create_time, update_time)
VALUES (1, 'jinyong', '123456', '金庸', 1, '1.jpg', 4, '2000-01-01', 2, now(), now()),
       (2, 'zhangwuji', '123456', '張無(wú)忌', 1, '2.jpg', 2, '2015-01-01', 2, now(), now()),
       (3, 'yangxiao', '123456', '楊逍', 1, '3.jpg', 2, '2008-05-01', 2, now(), now()),
       (4, 'weiyixiao', '123456', '韋一笑', 1, '4.jpg', 2, '2007-01-01', 2, now(), now()),
       (5, 'changyuchun', '123456', '常遇春', 1, '5.jpg', 2, '2012-12-05', 2, now(), now()),
       (6, 'xiaozhao', '123456', '小昭', 2, '6.jpg', 3, '2013-09-05', 1, now(), now()),
       (7, 'jixiaofu', '123456', '紀(jì)曉芙', 2, '7.jpg', 1, '2005-08-01', 1, now(), now()),
       (8, 'zhouzhiruo', '123456', '周芷若', 2, '8.jpg', 1, '2014-11-09', 1, now(), now()),
       (9, 'dingminjun', '123456', '丁敏君', 2, '9.jpg', 1, '2011-03-11', 1, now(), now()),
       (10, 'zhaomin', '123456', '趙敏', 2, '10.jpg', 1, '2013-09-05', 1, now(), now()),
       (11, 'luzhangke', '123456', '鹿杖客', 1, '11.jpg', 5, '2007-02-01', 3, now(), now()),
       (12, 'hebiweng', '123456', '鶴筆翁', 1, '12.jpg', 5, '2008-08-18', 3, now(), now()),
       (13, 'fangdongbai', '123456', '方東白', 1, '13.jpg', 5, '2012-11-01', 3, now(), now()),
       (14, 'zhangsanfeng', '123456', '張三豐', 1, '14.jpg', 2, '2002-08-01', 2, now(), now()),
       (15, 'yulianzhou', '123456', '俞蓮舟', 1, '15.jpg', 2, '2011-05-01', 2, now(), now()),
       (16, 'songyuanqiao', '123456', '宋遠(yuǎn)橋', 1, '16.jpg', 2, '2007-01-01', 2, now(), now()),
       (17, 'chenyouliang', '123456', '陳友諒', 1, '17.jpg', NULL, '2015-03-21', NULL, now(), now());

-- ==========================================================
-- 多表查詢
select *
from tb_emp,
     tb_dept
where tb_emp.dept_id = tb_dept.id;


-- ==========================內(nèi)連接================================
-- 查詢員工的姓名，及所屬部門的名稱
-- （隱式內(nèi)連接實(shí)現(xiàn)）
# select 字段列表 from 表1， 表二 where 條件
select tb_emp.name '姓名', tb_dept.name '部門'
from tb_emp,
     tb_dept
where tb_emp.dept_id = tb_dept.id;


-- 顯式內(nèi)連接實(shí)現(xiàn)
# select 字段列表 from 表1 [inner] join 表2 on 連接條件
select tb_emp.name '姓名', tb_dept.name '部門'
from tb_dept
         inner join tb_emp on dept_id = tb_dept.id;

select tb_emp.name '姓名', tb_dept.name '部門'
from tb_dept
         join tb_emp on dept_id = tb_dept.id;

-- 給表起別名
select e.name '姓名', d.name '部門'
from tb_dept d,
     tb_emp e
where e.dept_id = d.id;



-- ==========================外連接================================
-- 查詢部門所有員工的姓名，和對(duì)應(yīng)的部門名稱（左外連接）
-- select 字段列表 from 表1 left [outer] join 表2 on 連接條件
select e.name '姓名', d.name '部門名稱'
from tb_emp e
         left outer join tb_dept d on e.dept_id = d.id;

-- 右外連接實(shí)現(xiàn)
select e.name '姓名', d.name '部門名稱'
from tb_dept d
         right outer join tb_emp e on e.dept_id = d.id;



-- 查詢部門表所有部門所對(duì)應(yīng)的員工名稱（右外連接）
-- select 字段列表 from 表1 right [outer] join 表2 on 連接條件
select e.name '員工姓名', d.name '部門名稱'
from tb_emp e
         right outer join tb_dept d on e.dept_id = d.id;

-- 左外連接實(shí)現(xiàn)
select e.name '員工姓名', d.name '部門名稱'
from tb_dept d
         left outer join tb_emp e on e.dept_id = d.id;



-- ==========================子查詢================================
-- 標(biāo)量子查詢
# 查詢 教研部 所有員工的信息
# 1.查詢教研部的id
select id
from tb_dept
where name = '教研部';

# 2.查詢員工表的部門id為教研部的所有員工
select *
from tb_emp
where dept_id = 2;

# 合并在一起
select *
from tb_emp
where dept_id = (select id from tb_dept where name = '教研部');


# 查詢 '方東白' 入職之后的員工信息
# 1.查詢 '方東白' 入職的時(shí)間
select entrydate
from tb_emp
where name = '方東白';

# 2.查詢?cè)谶@時(shí)間之后所有的員工的信息
select *
from tb_emp
where entrydate >= '2012-11-01';

# 合并
select *
from tb_emp
where entrydate >= (select entrydate from tb_emp where name = '方東白');


-- 列子查詢
-- 查詢教研部和咨詢部的所有員工信息
# 1.查詢教研部和咨詢部的id
select id
from tb_dept
where name in ('教研部', '咨詢部');

# 2.查詢對(duì)應(yīng)id的所有員工的信息
select *
from tb_emp
where dept_id in (2, 3);

# 合并
select *
from tb_emp
where dept_id in (select id from tb_dept where name in ('教研部', '咨詢部'));



-- 行子查詢
# 查詢和 '韋一笑' 入職日期和職位都相同的員工信息
# 1.查詢 '韋一笑' 的入職日期和職位
select entrydate
from tb_emp
where name = '韋一笑';
select job
from tb_emp
where name = '韋一笑';
select entrydate, job
from tb_emp
where name = '韋一笑';


# 2.根據(jù)'韋一笑'的入職日期和職位在emp表中查詢與其相同的員工的信息
select *
from tb_emp
where entrydate = '2007-01-01'
  and job = 2;

# 合并
select *
from tb_emp
where entrydate = (select entrydate from tb_emp where name = '韋一笑')
  and job = (select job
             from tb_emp
             where name = '韋一笑');

# 優(yōu)化
select *
from tb_emp
where (entrydate, job) = (select entrydate, job from tb_emp where name = '韋一笑');


-- 表子查詢
# 查詢?nèi)肼毴掌谠?2006-1-1'之后的員工信息，及其部門名稱
# 查詢?nèi)肼毴掌谠?'2006-1-1'之后的所有員工
select *
from tb_emp
where entrydate >= '2006-1-1';

# 根據(jù)他們的部門id查詢其所對(duì)應(yīng)的部門的名稱
select e.*, d.name
from (select *
      from tb_emp
      where entrydate >= '2006-1-1') e
         left join tb_dept d
                   on e.dept_id = d.id;



-- 多表查詢（案列）
-- 數(shù)據(jù)準(zhǔn)備
-- 分類表
create table category
(
    id          int unsigned primary key auto_increment comment '主鍵ID',
    name        varchar(20)      not null unique comment '分類名稱',
    type        tinyint unsigned not null comment '類型 1 菜品分類 2 套餐分類',
    sort        tinyint unsigned not null comment '順序',
    status      tinyint unsigned not null default 0 comment '狀態(tài) 0 禁用，1 啟用',
    create_time datetime         not null comment '創(chuàng)建時(shí)間',
    update_time datetime         not null comment '更新時(shí)間'
) comment '分類';

-- 菜品表
create table dish
(
    id          int unsigned primary key auto_increment comment '主鍵ID',
    name        varchar(20)      not null unique comment '菜品名稱',
    category_id int unsigned     not null comment '菜品分類ID',
    price       decimal(8, 2)    not null comment '菜品價(jià)格',
    image       varchar(300)     not null comment '菜品圖片',
    description varchar(200) comment '描述信息',
    status      tinyint unsigned not null default 0 comment '狀態(tài), 0 停售 1 起售',
    create_time datetime         not null comment '創(chuàng)建時(shí)間',
    update_time datetime         not null comment '更新時(shí)間'
) comment '菜品';

-- 套餐表
create table setmeal
(
    id          int unsigned primary key auto_increment comment '主鍵ID',
    name        varchar(20)      not null unique comment '套餐名稱',
    category_id int unsigned     not null comment '分類id',
    price       decimal(8, 2)    not null comment '套餐價(jià)格',
    image       varchar(300)     not null comment '圖片',
    description varchar(200) comment '描述信息',
    status      tinyint unsigned not null default 0 comment '狀態(tài) 0:停用 1:啟用',
    create_time datetime         not null comment '創(chuàng)建時(shí)間',
    update_time datetime         not null comment '更新時(shí)間'
) comment '套餐';

-- 套餐菜品關(guān)聯(lián)表
create table setmeal_dish
(
    id         int unsigned primary key auto_increment comment '主鍵ID',
    setmeal_id int unsigned     not null comment '套餐id ',
    dish_id    int unsigned     not null comment '菜品id',
    copies     tinyint unsigned not null comment '份數(shù)'
) comment '套餐菜品中間表';

-- ================================== 導(dǎo)入測(cè)試數(shù)據(jù) ====================================
-- category
insert into category (id, type, name, sort, status, create_time, update_time)
values (1, 1, '酒水飲料', 10, 1, '2022-08-09 22:09:18', '2022-08-09 22:09:18');
insert into category (id, type, name, sort, status, create_time, update_time)
values (2, 1, '傳統(tǒng)主食', 9, 1, '2022-08-09 22:09:32', '2022-08-09 22:18:53');
insert into category (id, type, name, sort, status, create_time, update_time)
values (3, 2, '人氣套餐', 12, 1, '2022-08-09 22:11:38', '2022-08-10 11:04:40');
insert into category (id, type, name, sort, status, create_time, update_time)
values (4, 2, '商務(wù)套餐', 13, 1, '2022-08-09 22:14:10', '2022-08-10 11:04:48');
insert into category (id, type, name, sort, status, create_time, update_time)
values (5, 1, '經(jīng)典川菜', 6, 1, '2022-08-09 22:17:42', '2022-08-09 22:17:42');
insert into category (id, type, name, sort, status, create_time, update_time)
values (6, 1, '新鮮時(shí)蔬', 7, 1, '2022-08-09 22:18:12', '2022-08-09 22:18:28');
insert into category (id, type, name, sort, status, create_time, update_time)
values (7, 1, '湯類', 11, 1, '2022-08-10 10:51:47', '2022-08-10 10:51:47');

-- dish
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time)
values (1, '王老吉', 1, 6.00,
        'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/41bfcacf-7ad4-4927-8b26-df366553a94c.png', '', 1,
        '2022-06-09 22:40:47', '2022-06-09 22:40:47');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time)
values (2, '北冰洋', 1, 4.00,
        'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/4451d4be-89a2-4939-9c69-3a87151cb979.png',
        '還是小時(shí)候的味道', 1, '2022-06-10 09:18:49', '2022-06-10 09:18:49');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time)
values (3, '雪花啤酒', 1, 4.00,
        'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/bf8cbfc1-04d2-40e8-9826-061ee41ab87c.png', '', 1,
        '2022-06-10 09:22:54', '2022-06-10 09:22:54');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time)
values (4, '米飯', 2, 2.00,
        'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/76752350-2121-44d2-b477-10791c23a8ec.png', '精選五常大米', 1,
        '2022-06-10 09:30:17', '2022-06-10 09:30:17');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time)
values (5, '饅頭', 2, 1.00,
        'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/475cc599-8661-4899-8f9e-121dd8ef7d02.png', '優(yōu)質(zhì)面粉', 1,
        '2022-06-10 09:34:28', '2022-06-10 09:34:28');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time)
values (6, '老壇酸菜魚(yú)', 5, 56.00,
        'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/4a9cefba-6a74-467e-9fde-6e687ea725d7.png',
        '原料：湯，草魚(yú)，酸菜', 1, '2022-06-10 09:40:51', '2022-06-10 09:40:51');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time)
values (7, '經(jīng)典酸菜鮰魚(yú)', 5, 66.00,
        'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/5260ff39-986c-4a97-8850-2ec8c7583efc.png',
        '原料：酸菜，江團(tuán)，鮰魚(yú)', 1, '2022-06-10 09:46:02', '2022-06-10 09:46:02');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time)
values (8, '蜀味水煮草魚(yú)', 5, 38.00,
        'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/a6953d5a-4c18-4b30-9319-4926ee77261f.png', '原料：草魚(yú)，湯', 1,
        '2022-06-10 09:48:37', '2022-06-10 09:48:37');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time)
values (9, '清炒小油菜', 6, 18.00,
        'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/3613d38e-5614-41c2-90ed-ff175bf50716.png', '原料：小油菜', 1,
        '2022-06-10 09:51:46', '2022-06-10 09:51:46');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time)
values (10, '蒜蓉娃娃菜', 6, 18.00,
        'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/4879ed66-3860-4b28-ba14-306ac025fdec.png', '原料：蒜，娃娃菜',
        1, '2022-06-10 09:53:37', '2022-06-10 09:53:37');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time)
values (11, '清炒西蘭花', 6, 18.00,
        'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/e9ec4ba4-4b22-4fc8-9be0-4946e6aeb937.png', '原料：西蘭花', 1,
        '2022-06-10 09:55:44', '2022-06-10 09:55:44');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time)
values (12, '熗炒圓白菜', 6, 18.00,
        'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/22f59feb-0d44-430e-a6cd-6a49f27453ca.png', '原料：圓白菜', 1,
        '2022-06-10 09:58:35', '2022-06-10 09:58:35');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time)
values (13, '清蒸鱸魚(yú)', 5, 98.00,
        'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/c18b5c67-3b71-466c-a75a-e63c6449f21c.png', '原料：鱸魚(yú)', 1,
        '2022-06-10 10:12:28', '2022-06-10 10:12:28');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time)
values (14, '東坡肘子', 5, 138.00,
        'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/a80a4b8c-c93e-4f43-ac8a-856b0d5cc451.png', '原料：豬肘棒', 1,
        '2022-06-10 10:24:03', '2022-06-10 10:24:03');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time)
values (15, '梅菜扣肉', 5, 58.00,
        'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/6080b118-e30a-4577-aab4-45042e3f88be.png', '原料：豬肉，梅菜',
        1, '2022-06-10 10:26:03', '2022-06-10 10:26:03');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time)
values (16, '剁椒魚(yú)頭', 5, 66.00,
        'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/13da832f-ef2c-484d-8370-5934a1045a06.png', '原料：鰱魚(yú)，剁椒',
        1, '2022-06-10 10:28:54', '2022-06-10 10:28:54');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time)
values (17, '饞嘴牛蛙', 5, 98.00,
        'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/7a55b845-1f2b-41fa-9486-76d187ee9ee1.png',
        '配料：鮮活牛蛙，絲瓜，黃豆芽', 1, '2022-06-10 10:37:52', '2022-06-10 10:37:52');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time)
values (18, '雞蛋湯', 7, 4.00,
        'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/c09a0ee8-9d19-428d-81b9-746221824113.png', '配料：雞蛋，紫菜',
        1, '2022-06-10 10:54:25', '2022-06-10 10:54:25');
insert into dish (id, name, category_id, price, image, description, status, create_time, update_time)
values (19, '平菇豆腐湯', 7, 6.00,
        'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/16d0a3d6-2253-4cfc-9b49-bf7bd9eb2ad2.png', '配料：豆腐，平菇',
        1, '2022-06-10 10:55:02', '2022-06-10 10:55:02');

-- setmeal
insert into setmeal (id, category_id, name, price, status, description, image, create_time, update_time)
values (1, 4, '商務(wù)套餐A', 20.00, 1, '',
        'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/21a5ed3a-97f6-447a-af9d-53deabfb5661.png',
        '2022-06-10 10:58:09', '2022-06-10 10:58:09');
insert into setmeal (id, category_id, name, price, status, description, image, create_time, update_time)
values (2, 4, '商務(wù)套餐B', 22.00, 1, '',
        'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/8d0075f8-9008-4390-94ca-2ca631440304.png',
        '2022-06-10 11:00:13', '2022-06-10 11:11:37');
insert into setmeal (id, category_id, name, price, status, description, image, create_time, update_time)
values (3, 3, '人氣套餐A', 49.00, 1, '',
        'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/8979566b-0e17-462b-81d8-8dbace4138f4.png',
        '2022-06-10 11:11:23', '2022-06-10 11:11:23');

-- setmeal_dish
insert into setmeal_dish (id, setmeal_id, dish_id, copies)
values (1, 1, 1, 1);
insert into setmeal_dish (id, setmeal_id, dish_id, copies)
values (2, 1, 4, 1);
insert into setmeal_dish (id, setmeal_id, dish_id, copies)
values (3, 1, 11, 1);
insert into setmeal_dish (id, setmeal_id, dish_id, copies)
values (4, 2, 2, 1);
insert into setmeal_dish (id, setmeal_id, dish_id, copies)
values (5, 2, 4, 1);
insert into setmeal_dish (id, setmeal_id, dish_id, copies)
values (6, 2, 9, 1);
insert into setmeal_dish (id, setmeal_id, dish_id, copies)
values (7, 3, 2, 1);
insert into setmeal_dish (id, setmeal_id, dish_id, copies)
values (8, 3, 6, 1);
insert into setmeal_dish (id, setmeal_id, dish_id, copies)
values (9, 3, 5, 1);

-- 需求：
-- 1.查詢價(jià)格低于10元的菜品的名稱、價(jià)格、及其菜品的分類名稱
/*
 菜品表：dish(name, price, category_id)， 分類表（id, name)
 */

# a.查詢價(jià)格低于10元的菜品的名稱、價(jià)格、及其菜品的分類id
select name, price, category_id
from dish
where price < 10;

# 根據(jù)菜品的分類id得到對(duì)應(yīng)的菜品分類名稱
select d.name '菜品', d.price '價(jià)格', c.name '菜品分類'
from (select name, price, category_id from dish where price < 10) d,
     category c
where d.category_id = c.id;

select d.name '菜品', d.price '價(jià)格', c.name '菜品分類'
from dish d,
     category c
where d.price < 10
  and d.category_id = c.id;


-- 2.查詢所有價(jià)格在10元（包含）到50元（包含）之間且狀態(tài)為起售的菜品，
--   展示出菜品的名稱、價(jià)格、及其菜品的分類名稱（即使沒(méi)有菜品分類，也要將菜品查詢）
/*
 菜品表dish(price, status) 展示：name, price, category_id
 分類表：name
 即使沒(méi)有菜品分類，也要將菜品查詢: dish 在左邊
 */
# a.查詢符合條件的所有的菜品
select *
from dish
where price between 10 and 50
  and status = 1;

# b.根據(jù)菜品的分類id查詢對(duì)應(yīng)的分類名稱并顯示數(shù)據(jù)
select d.name '菜品', d.price '價(jià)格', c.name '菜品分類'
from (select * from dish where price between 10 and 50 and status = 1) d
         join category c on d.category_id = c.id;

select d.name '菜品', d.price '價(jià)格', c.name '菜品分類'
from dish d
         left join category c on d.category_id = c.id
where d.price between 10 and 50
  and d.status = 1;



-- 3.查詢每個(gè)分類下最貴的菜品展示出分類的名稱，最貴的菜品的價(jià)格
/*
 分類表category(id, name)
 菜品表dish(price)
 */
# a.菜單欄所有的菜品按分類分組
select category_id
from dish
group by category_id;

# b.按照菜品分組找到對(duì)應(yīng)分組里面價(jià)格最貴的價(jià)格
select category_id, max(price) maxPrice
from dish
group by category_id
having max(price);

# c.根據(jù)菜品id去找到對(duì)應(yīng)的菜品的名稱
select c.name '菜品分類', d.maxPrice '最大價(jià)格'
from (select category_id, max(price) maxPrice from dish group by category_id having max(price)) d,
     category c
where d.category_id = c.id;

select c.name '菜品分類', max(d.price) '最大價(jià)格'
from dish d,
     category c
where d.category_id = c.id
group by c.name;



-- 4.查詢各個(gè)分類下 菜品狀態(tài)為起售，并且該分類下菜品數(shù)量大于等于3 的分類名稱。
/*
 品數(shù)量大于等于3：說(shuō)明是一個(gè)套餐
 菜品狀態(tài)為起售：套餐的狀態(tài)
 */
# a.查詢setmeal中菜品狀態(tài)為起售的菜品
select *
from setmeal
where status = 1;

# b.setmeal_dish:分組統(tǒng)計(jì)copies
select setmeal_id
from setmeal_dish
group by setmeal_id
having sum(copies) >= 3;

# c.根據(jù)對(duì)應(yīng)的id顯示分類的名稱
select name
from setmeal
where id in (select setmeal_id
             from setmeal_dish
             group by setmeal_id
             having sum(copies) >= 3)
  and status = 1;

select c.name '菜品分類', count(*) '菜品數(shù)量'
from category c,
     dish d
where c.id = d.category_id
  and c.status = 1
group by c.name
having count(*) >= 3;



-- 5.查詢 商務(wù)套餐A 中包含了哪些菜品（展示出套餐名稱，價(jià)格，包含的菜品名稱，價(jià)格，份數(shù)）
# a.在套餐表中找到 商務(wù)套餐A 的id
select id, name, price
from setmeal
where name = '商務(wù)套餐A';

# b.在套餐菜品關(guān)系表setmeal-dish中找到對(duì)應(yīng)setmeal_id為1的菜品的id,份數(shù)
select a.*, copies, dish_id
from setmeal_dish,
     (select id, name, price
      from setmeal
      where name = '商務(wù)套餐A') a
where setmeal_id = a.id;

# 根據(jù)對(duì)應(yīng)的dish_id查找對(duì)應(yīng)菜品的名稱，價(jià)格
select b.name '套餐名稱', b.price '套餐價(jià)格', c.name '菜品名稱', c.price '菜品價(jià)格', b.copies '菜品份數(shù)'
from (select a.*, copies, dish_id
      from setmeal_dish,
           (select id, name, price
            from setmeal
            where name = '商務(wù)套餐A') a
      where setmeal_id = a.id) b,
     dish c
where c.id in (b.dish_id);

select s.name '套餐名稱', s.price '套餐價(jià)格', d.name '菜品名稱', d.price '菜品價(jià)格', sd.copies '菜品份數(shù)'
from dish d,
     setmeal_dish sd,
     setmeal s
where d.id = sd.dish_id
  and s.id = sd.setmeal_id
  and s.name = '商務(wù)套餐A';


-- 6.查詢出低于菜品平均價(jià)格的菜品信息（展示出菜品名稱，菜品價(jià)格）
/*
 菜品表dish(name, price) avg(pricae)
 */
# a.查詢出菜品的平均價(jià)格
select avg(price)
from dish;

# b.查詢出低于菜品平均價(jià)格的所有的菜品信息并展示
select name, price
from dish
where price <= (select avg(price) from dish);

8 事物

8.1 介紹

在實(shí)際的業(yè)務(wù)開(kāi)發(fā)中，有些業(yè)務(wù)操作要多次訪問(wèn)數(shù)據(jù)庫(kù)。一個(gè)業(yè)務(wù)要發(fā)送多條SQL語(yǔ)句給數(shù)據(jù)庫(kù)執(zhí)行。需要將多次訪問(wèn)數(shù)據(jù)庫(kù)的操作視為一個(gè)整體來(lái)執(zhí)行，要么所有的SQL語(yǔ)句全部執(zhí)行成功。如果其中有一條SQL語(yǔ)句失敗，就進(jìn)行事務(wù)的回滾，所有的SQL語(yǔ)句全部執(zhí)行失敗。

簡(jiǎn)而言之：事務(wù)是一組操作的集合，它是一個(gè)不可分割的工作單位。事務(wù)會(huì)把所有的操作作為一個(gè)整體一起向系統(tǒng)提交或撤銷操作請(qǐng)求，即這些操作要么同時(shí)成功，要么同時(shí)失敗。

事務(wù)作用：保證在一個(gè)事務(wù)中多次操作數(shù)據(jù)庫(kù)表中數(shù)據(jù)時(shí)，要么全都成功,要么全都失敗。

8.2 操作

MYSQL中有兩種方式進(jìn)行事務(wù)的操作：

1. 自動(dòng)提交事務(wù)：即執(zhí)行一條sql語(yǔ)句提交一次事務(wù)。（默認(rèn)MySQL的事務(wù)是自動(dòng)提交）
2. 手動(dòng)提交事務(wù)：先開(kāi)啟，再提交

事務(wù)操作有關(guān)的SQL語(yǔ)句：

手動(dòng)提交事務(wù)使用步驟：

• 第1種情況：開(kāi)啟事務(wù) => 執(zhí)行SQL語(yǔ)句 => 成功 => 提交事務(wù)
• 第2種情況：開(kāi)啟事務(wù) => 執(zhí)行SQL語(yǔ)句 => 失敗 => 回滾事務(wù)

8.3 四大特性

面試題：事務(wù)有哪些特性？

• 原子性（Atomicity）：事務(wù)是不可分割的最小單元，要么全部成功，要么全部失敗。
• 一致性（Consistency）：事務(wù)完成時(shí)，必須使所有的數(shù)據(jù)都保持一致?tīng)顟B(tài)。
• 隔離性（Isolation）：數(shù)據(jù)庫(kù)系統(tǒng)提供的隔離機(jī)制，保證事務(wù)在不受外部并發(fā)操作影響的獨(dú)立環(huán)境下運(yùn)行。
• 持久性（Durability）：事務(wù)一旦提交或回滾，它對(duì)數(shù)據(jù)庫(kù)中的數(shù)據(jù)的改變就是永久的。

事務(wù)的四大特性簡(jiǎn)稱為：ACID

• 原子性（Atomicity） ：原子性是指事務(wù)包裝的一組sql是一個(gè)不可分割的工作單元，事務(wù)中的操作要么全部成功，要么全部失敗。

• 一致性（Consistency）：一個(gè)事務(wù)完成之后數(shù)據(jù)都必須處于一致性狀態(tài)。

? 如果事務(wù)成功的完成，那么數(shù)據(jù)庫(kù)的所有變化將生效。

? 如果事務(wù)執(zhí)行出現(xiàn)錯(cuò)誤，那么數(shù)據(jù)庫(kù)的所有變化將會(huì)被回滾(撤銷)，返回到原始狀態(tài)。

• 隔離性（Isolation）：多個(gè)用戶并發(fā)的訪問(wèn)數(shù)據(jù)庫(kù)時(shí)，一個(gè)用戶的事務(wù)不能被其他用戶的事務(wù)干擾，多個(gè)并發(fā)的事務(wù)之間要相互隔離。

? 一個(gè)事務(wù)的成功或者失敗對(duì)于其他的事務(wù)是沒(méi)有影響。

• 持久性（Durability）：一個(gè)事務(wù)一旦被提交或回滾，它對(duì)數(shù)據(jù)庫(kù)的改變將是永久性的，哪怕數(shù)據(jù)庫(kù)發(fā)生異常，重啟之后數(shù)據(jù)亦然存在。

9 索引

9.1 介紹

索引(index)：是幫助數(shù)據(jù)庫(kù)高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu) 。

• 簡(jiǎn)單來(lái)講，就是使用索引可以提高查詢的效率。

優(yōu)點(diǎn)：

1. 提高數(shù)據(jù)查詢的效率，降低數(shù)據(jù)庫(kù)的IO成本。
2. 通過(guò)索引列對(duì)數(shù)據(jù)進(jìn)行排序，降低數(shù)據(jù)排序的成本，降低CPU消耗。

缺點(diǎn)：

1. 索引會(huì)占用存儲(chǔ)空間。
2. 索引大大提高了查詢效率，同時(shí)卻也降低了insert、update、delete的效率。

9.2 結(jié)構(gòu)

MySQL數(shù)據(jù)庫(kù)支持的索引結(jié)構(gòu)有很多，如：Hash索引、B+Tree索引、Full-Text索引等。

我們平常所說(shuō)的索引，如果沒(méi)有特別指明，都是指默認(rèn)的 B+Tree 結(jié)構(gòu)組織的索引。

B+Tree結(jié)構(gòu)：

• 每一個(gè)節(jié)點(diǎn)，可以存儲(chǔ)多個(gè)key（有n個(gè)key，就有n個(gè)指針）
• 節(jié)點(diǎn)分為：葉子節(jié)點(diǎn)、非葉子節(jié)點(diǎn)
  • 葉子節(jié)點(diǎn)，就是最后一層子節(jié)點(diǎn)，所有的數(shù)據(jù)都存儲(chǔ)在葉子節(jié)點(diǎn)上
  • 非葉子節(jié)點(diǎn)，不是樹(shù)結(jié)構(gòu)最下面的節(jié)點(diǎn)，用于索引數(shù)據(jù)，存儲(chǔ)的的是：key+指針
• 為了提高范圍查詢效率，葉子節(jié)點(diǎn)形成了一個(gè)雙向鏈表，便于數(shù)據(jù)的排序及區(qū)間范圍查詢

拓展：

非葉子節(jié)點(diǎn)都是由key+指針域組成的，一個(gè)key占8字節(jié)，一個(gè)指針占6字節(jié)，而一個(gè)節(jié)點(diǎn)總共容量是16KB，那么可以計(jì)算出一個(gè)節(jié)點(diǎn)可以存儲(chǔ)的元素個(gè)數(shù)：16*1024字節(jié) / (8+6)=1170個(gè)元素。

• 查看mysql索引節(jié)點(diǎn)大小：show global status like ‘innodb_page_size’; – 節(jié)點(diǎn)大?。?6384

當(dāng)根節(jié)點(diǎn)中可以存儲(chǔ)1170個(gè)元素，那么根據(jù)每個(gè)元素的地址值又會(huì)找到下面的子節(jié)點(diǎn)，每個(gè)子節(jié)點(diǎn)也會(huì)存儲(chǔ)1170個(gè)元素，那么第二層即第二次IO的時(shí)候就會(huì)找到數(shù)據(jù)大概是：1170*1170=135W。也就是說(shuō)B+Tree數(shù)據(jù)結(jié)構(gòu)中只需要經(jīng)歷兩次磁盤IO就可以找到135W條數(shù)據(jù)。

對(duì)于第二層每個(gè)元素有指針，那么會(huì)找到第三層，第三層由key+數(shù)據(jù)組成，假設(shè)key+數(shù)據(jù)總大小是1KB，而每個(gè)節(jié)點(diǎn)一共能存儲(chǔ)16KB，所以一個(gè)第三層一個(gè)節(jié)點(diǎn)大概可以存儲(chǔ)16個(gè)元素(即16條記錄)。那么結(jié)合第二層每個(gè)元素通過(guò)指針域找到第三層的節(jié)點(diǎn)，第二層一共是135W個(gè)元素，那么第三層總元素大小就是：135W*16結(jié)果就是2000W+的元素個(gè)數(shù)。

結(jié)合上述分析B+Tree有如下優(yōu)點(diǎn)：

• 千萬(wàn)條數(shù)據(jù)，B+Tree可以控制在小于等于3的高度
• 所有的數(shù)據(jù)都存儲(chǔ)在葉子節(jié)點(diǎn)上，并且底層已經(jīng)實(shí)現(xiàn)了按照索引進(jìn)行排序，還可以支持范圍查詢，葉子節(jié)點(diǎn)是一個(gè)雙向鏈表，支持從小到大或者從大到小查找

9.3 語(yǔ)法

創(chuàng)建索引

create  [ unique ]  index 索引名 on  表名 (字段名,... ) ;

查看索引

show  index  from  表名;

刪除索引

drop  index  索引名  on  表名;

注意事項(xiàng)：

• 主鍵字段，在建表時(shí)，會(huì)自動(dòng)創(chuàng)建主鍵索引

• 添加唯一約束時(shí)，數(shù)據(jù)庫(kù)實(shí)際上會(huì)添加唯一索引文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-666468.html

到了這里，關(guān)于09 數(shù)據(jù)庫(kù)開(kāi)發(fā)-MySQL的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！