MySQL數據庫

Web后端開發_02

數據庫介紹

什么是數據庫？

數據庫：DataBase（DB），是存儲和管理數據的倉庫

數據庫管理系統：DataBase Management System (DBMS)，操縱和管理數據庫的大型軟件。
SQL：Structured Query Language，操作關系型數據庫的編程語言，定義了一套操作關系型數據庫統一標準。

數據庫產品

HelloGitHub｜數據庫排名

https://hellogithub.com/report/db-engines/

數據庫設計

MySQL概述

安裝、配置

MySQL安裝：

MySQL官方提供了兩個不同的版本：

我下載安裝的是MySQL社區版（MySQL Community Server 8.0.28）

黑馬給的有（MySQL Community Server 8.0.31）的安裝配置方法，這里不在贅述。

MySQL-企業開發使用方式

mysql -u用戶名 -p密碼 [-h數據庫服務器IP地址 -p端口號]

數據模型

關系型數據庫（RDBMS）：建立在關系模型基礎上，由多張相互連接的二維表組成的數據庫

特點

使用表存儲數據，格式統一，易于維護
使用SQL語言操作，標準統一，使用方便，可用于復雜查詢

SQL簡介

SQL：一門操作關系型數據庫的編程語言，定義操作所有關系型數據庫的統一標準。

通用語法

SQL語句可以單行或多行書寫，以分號結尾。
SQL語句可以使用空格/縮進來增強語句的可讀性。
MySQL數據庫的SQL語句不區分大小寫。
注釋：
1. 單行注釋：--注釋內容 或 #注釋內容（MySQL特有）
2. 多行注釋：/*注釋內容*/

SQL分類

SQL語句通常被分為四大類：

開發的三個階段

數據庫設計-DDL

DDL英文全稱是Data Definition Language，數據定義語言，用來定義數據庫對象(數據庫、表)。

數據庫

查詢
查詢所有數據庫
show databases;
查詢當前數據庫
select database();

查詢表結構
desc 表名;
查詢當前數據庫中的所有表名
show tables;
查詢指定表的建表語句
show create table 表名;

創建
create database [if not exists] 數據庫名 [default charset 字符集] [collate 排序規則];

刪除
drop database [if exists] 數據庫名;

使用
use 數據庫名;

注意事項：

上述語法中的database，也可以替換成schema。如：create schema db01;

代碼示例：

#查詢所有數據庫
SHOW DATABASES;#創建數據庫
create database db01;#創建數據庫db01,如果沒有在創建
create database if not exists db01;#創建數據庫db02,如果沒有在創建
create database if not exists db02;#創建數據庫db03,如果沒有在創建
create database if not exists db03;#使用數據庫db01
use db01;#查看當前正在使用的數據庫
select database();#查詢所有數據庫
show databases;#刪除數據庫
drop database db03;#該數據庫存在時刪除，不存在時也不報錯
drop database if exists db02;#查詢所有數據庫
show schemas;

MySQL客戶端工具-圖像化工具

DataGrip：

介紹：DataGrip是JetBrains旗下的一款數據庫管理工具，是管理和開發MySQL、Oracle、PostgreSQL的理想解決方案。
官網：https://www.jetbrains.com/zh-cn/datagrip/
安裝：黑馬參考資料中提供的《DataGrip安裝手冊》

IDEA中內嵌的有數據庫管理工具

IDEA中的控制臺

表（創建、查詢、修改、刪除）

DDL-表操作-創建

create table 表名（字段1 字段1類型[ comment 字段1注釋 ],字段2 字段2類型[ comment 字段2注釋 ],字段3 字段3類型[ comment 字段3注釋 ]
）[ comment 表注釋];

DDL-表操作-查詢

查詢當前數據庫所有表

show tables;

查詢表結構

desc 表名;

舉例：

查詢建表語句

show create table 表名;

DDL-表操作-修改

添加字段

alter table 表名 add 字段名 類型(長度) [comment 注釋] [約束];

修改數據類型

alter table 表名 modify 字段名 新數據類型(長度);

修改字段名和字段類型

alter table 表名 change 舊字段名 新字段名 類型(長度) [comment 注釋] [約束];

刪除字段

alter table 表名 drop 字段名;

刪除表=》新建表=》遷移表數據，最終完成修改表名（如果原表有任何依賴（如視圖、索引、觸發器等），這些依賴都需要手動重新創建。）

alter table 表名 rename to 新表名;

修改表名（所有與原表相關的對象（如視圖、索引、觸發器等）都將自動更新以引用新表名）

rename table 表名 to 新表名;

使用圖形化界面進行操作

轉到DDL：自動生成建表語句

DDL-表操作-刪除

刪除表

drop table [if exists] 表名;

刪除指定表(數據)，并重新創建該表(結構)

truncate table 表名;

注意事項：

在刪除表時，表中的全部數據也會被刪除

約束：

概念：約束是作用于表中字段上的規則，用于限制存儲在數據表中的數據。
目的：保證數據庫中數據的正確性、有效性、完整性

設計對應的表

對應的SQL語句

#創建 基本語法（約束）
create table tb_user
(id       int primary key auto_increment comment 'ID,唯一標識',username varchar(20) not null unique comment '用戶名',name     varchar(10) not null comment '姓名',age      int comment '年齡',gender   char(1) default '男' comment '性別'
) comment '用戶表';

創建成功，測試插入數據。

數據類型：

MySQL中的數據類型有很多，主要分為三類：數值類型、字符串類型、日期時間類型

數值類型

類型	大小(byte)	有符號(SIGNED)范圍	無符號(UNSIGNED)范圍	描述
`tinyint`	1	(-128，127)	(0，255)	小整數值
`smallint`	2	(-32768，32767)	(0，65535)	大整數值
`mediumint`	3	(-8388608，8388607)	(0，16777215)	大整數值
`int`	4	(-2147483648，2147483647)	(0，4294967295)	大整數值
`bigint`	8	(-2^63，263-1)	(0，2^64-1)	極大整數值
`float`	4	(-3.402823466 E+38，3.402823466351 E+38)	0 和 (1.175494351 E-38，3.402823466 E+38)	單精度浮點數值
`double`	8	(-1.7976931348623157 E+308，1.7976931348623157 E+308)	0 和 (2.2250738585072014 E-308，1.7976931348623157 E+308)	雙精度浮點數值
`decimal`				小數值(精度更高)

tinyint unsigned：表示無符號的tinyint類型，例如：用戶的年齡 age tinyint unsigned

float(5,2)：5表示整個數字長度，2 表示小數位個數
double(5,2)：5表示整個數字長度，2 表示小數位個數，例如：成績（分值為999.9~0） score double(4,1)
decimal(5,2)：5表示整個數字長度，2 表示小數位個數

字符串類型

類型	大小	描述
`char`	0-255 bytes	定長字符串
`varchar`	0-65535 bytes	變長字符串
`tinyblob`	0-255 bytes	不超過255個字符的二進制數據
`tinytext`	0-255 bytes	短文本字符串
`blob`	0-65 535 bytes	二進制形式的長文本數據
`text`	0-65 535 bytes	長文本數據
`mediumblob`	0-16 777 215 bytes	二進制形式的中等長度文本數據
`mediumtext`	0-16 777 215 bytes	中等長度文本數據
`longblob`	0-4 294 967 295 bytes	二進制形式的極大文本數據
`longtext`	0-4 294 967 295 bytes	極大文本數據

char(10)：最多只能存10個字符，不足10個字符，占用10個字符空間，例如存儲AB占用十個字符空間，性能高，浪費空間
varchar(10)：最多只能存10個字符，不足10個字符，按照實際長度存儲，例如存儲ABC占用三個字符空間，性能低，節省空間

手機號：phone char(11)

用戶名：username varchar(20)

日期時間類型

類型	大小(byte)	范圍	格式	描述
`date`	3	1000-01-01 至 9999-12-31	YYYY-MM-DD	日期值
`time`	3	-838:59:59 至 838:59:59	HH:MM:SS	時間值或持續時間
`year`	1	1901 至 2155	YYYY	年份值
`datetime`	8	1000-01-01 00:00:00 至 9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和時間值
`timestamp`	4	1970-01-01 00:00:01 至 2038-01-19 03:14:07	YYYY-MM-DD HH:MM:SS	混合日期和時間值，時間戳

用戶生日：birthday data

操作時間：updata_time datatime

案例

根據頁面原型/需求創建表（設計合理的數據類型、長度、約束）

字段限制說明

根據需求文檔使用IDEA創建員工表

設計表結構的基本流程

注意事項：

create_time：記錄的是當前這條數據插入的時間。
update_time：記錄當前這條數據最后更新的時間。

多表設計

暫時略

數據庫操作

數據庫操作-DML

DML英文全稱是Data Manipulation Language（數據操作語言），用來對數據庫中表的數據記錄進行增、刪、改操作。

添加數據（INSERT）

指定字段添加數據

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

全部字段添加數據

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

批量添加數據（指定字段）

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

批量添加數據（全部字段）

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

示例：

# DML:插入數據-insert
# 1.為table_emp表的username,name,gender 字段插入值
insert into table_emp(username, name, gender, create_time, update_time)
values ('paidaxing', '派大星', 1, now(), now());# 2.為table_emp表的 所有字段插入值
insert into table_emp(id, username, password, name, gender, image, job, entry_date, create_time, update_time)
values (null, 'haimian', '123', '海綿寶寶', 1, '1.jpg', 1, '2000-02-12', now(), now());insert into table_emp
values (null, 'pilaoban', '321', '痞老板', '1', '3.jpg', 4, '2020-03-23', now(), now());insert into table_emp
values (null, 'pilaoban3', default, '痞老板3', '3', '4.jpg', 2, '2020-01-13', now(), now());
# 3.批量為 為table_emp表的username,name,gender字段插入數據
insert into table_emp(username, name, gender, create_time, update_time)
VALUES ('xielaoban2', '蟹老板2', 1, now(), now()),('paofu', '泡芙阿姨', 2, now(), now());

注意事項：

插入數據時，指定的字段順序需要與值的順序是一一對應的。
字符串和日期型數據應該包含在引號中。
插入的數據大小，應該在字段的規定范圍內。
插入數據時，若數據庫已經選擇了默認值，values對應值為default
插入數據時，自增主鍵values對應的值為null

修改數據（UPDATE）

語法格式：

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

示例：

#更新數據
# 1.將table_emp表中的ID為1的員工 姓名name字段更新為'張三'
update table_emp
set name='張四',update_time=now()
where id = 1;# 2.將table_emp表中所有員工的入職日期，更新為'2010-01-01'
update table_emp
set entry_date = '2010-01-01',update_time=now();

注意事項：

修改語句的條件可以有，也可以沒有，如果沒有條件，則會修改整張表的所有數據。

刪除數據（DELETE）

語法格式：

delete from 表名 [where 條件]；
truncate table 表名 或者truncate 表名；

示例：

#DML 刪除數據
# 1.刪除table_emp表中ID為1的員工
delete
from table_emp
where id = 1;# 2.刪除table_emp表中的所有員工
delete
from table_emp;truncate table table_emp;

注意事項：

delete語句的條件可以有，也可以沒有，如果沒有條件，則會刪除整張表的所有數據。
delete語句不能刪除某個字段的值（如果要操作，可以使用update，將該字段的值設置為null）。
truncate table 表名或者truncate 表名；直接刪除表中的所有數據，且IDEA中沒有警告提示。

數據庫操作-DQL

DQL英文全稱是Data Query Language（數據庫查詢語言），用來查詢數據庫表中的記錄。
關鍵字：SELECT

#語法格式：

select
[all|distinct]
<目標列的表達式1> [別名],
<目標列的表達式2> [別名].......
from <表名或視圖名> [別名] ,<表名或視圖名> [別名]......
[where <條件表達式>]
[group by <列名>
[having <條件表達式>]]
[order by <列名> [asc|desc]]
[limit <數字或者列表>];

#簡化版語法：

select *| 列名 from 表 where 條件

DQL-基本查詢

DQL-數據準備

-- 員工管理(帶約束)
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 '性別, 說明: 1 男, 2 女',image       varchar(300) comment '圖像',job         tinyint unsigned comment '職位, 說明: 1 班主任,2 講師, 3 學工主管, 4 教研主管',entrydate   date comment '入職時間',create_time datetime         not null comment '創建時間',update_time datetime         not null comment '修改時間'
) comment '員工表';-- 準備測試數據
INSERT INTO tb_emp (id, username, password, name, gender, image, job, entrydate, create_time, update_time)
VALUES (1, 'jinyong', '123456', '金庸', 1, '1.jpg', 4, '2000-01-01', '2022-10-27 16:35:33', '2022-10-27 16:35:35'),(2, 'zhangwuji', '123456', '張無忌', 1, '2.jpg', 2, '2015-01-01', '2022-10-27 16:35:33', '2022-10-27 16:35:37'),(3, 'yangxiao', '123456', '楊逍', 1, '3.jpg', 2, '2008-05-01', '2022-10-27 16:35:33', '2022-10-27 16:35:39'),(4, 'weiyixiao', '123456', '韋一笑', 1, '4.jpg', 2, '2007-01-01', '2022-10-27 16:35:33', '2022-10-27 16:35:41'),(5, 'changyuchun', '123456', '常遇春', 1, '5.jpg', 2, '2012-12-05', '2022-10-27 16:35:33', '2022-10-27 16:35:43'),(6, 'xiaozhao', '123456', '小昭', 2, '6.jpg', 3, '2013-09-05', '2022-10-27 16:35:33', '2022-10-27 16:35:45'),(7, 'jixiaofu', '123456', '紀曉芙', 2, '7.jpg', 1, '2005-08-01', '2022-10-27 16:35:33', '2022-10-27 16:35:47'),(8, 'zhouzhiruo', '123456', '周芷若', 2, '8.jpg', 1, '2014-11-09', '2022-10-27 16:35:33', '2022-10-27 16:35:49'),(9, 'dingminjun', '123456', '丁敏君', 2, '9.jpg', 1, '2011-03-11', '2022-10-27 16:35:33', '2022-10-27 16:35:51'),(10, 'zhaomin', '123456', '趙敏', 2, '10.jpg', 1, '2013-09-05', '2022-10-27 16:35:33', '2022-10-27 16:35:53'),(11, 'luzhangke', '123456', '鹿杖客', 1, '11.jpg', 2, '2007-02-01', '2022-10-27 16:35:33', '2022-10-27 16:35:55'),(12, 'hebiweng', '123456', '鶴筆翁', 1, '12.jpg', 2, '2008-08-18', '2022-10-27 16:35:33', '2022-10-27 16:35:57'),(13, 'fangdongbai', '123456', '方東白', 1, '13.jpg', 1, '2012-11-01', '2022-10-27 16:35:33', '2022-10-27 16:35:59'),(14, 'zhangsanfeng', '123456', '張三豐', 1, '14.jpg', 2, '2002-08-01', '2022-10-27 16:35:33','2022-10-27 16:36:01'),(15, 'yulianzhou', '123456', '俞蓮舟', 1, '15.jpg', 2, '2011-05-01', '2022-10-27 16:35:33', '2022-10-27 16:36:03'),(16, 'songyuanqiao', '123456', '宋遠橋', 1, '16.jpg', 2, '2010-01-01', '2022-10-27 16:35:33','2022-10-27 16:36:05'),(17, 'chenyouliang', '12345678', '陳友諒', 1, '17.jpg', null, '2015-03-21', '2022-10-27 16:35:33','2022-10-27 16:36:07'),(18, 'zhang1', '123456', '張一', 1, '2.jpg', 2, '2015-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:09'),(19, 'zhang2', '123456', '張二', 1, '2.jpg', 2, '2012-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:11'),(20, 'zhang3', '123456', '張三', 1, '2.jpg', 2, '2018-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:13'),(21, 'zhang4', '123456', '張四', 1, '2.jpg', 2, '2015-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:15'),(22, 'zhang5', '123456', '張五', 1, '2.jpg', 2, '2016-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:17'),(23, 'zhang6', '123456', '張六', 1, '2.jpg', 2, '2012-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:19'),(24, 'zhang7', '123456', '張七', 1, '2.jpg', 2, '2006-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:21'),(25, 'zhang8', '123456', '張八', 1, '2.jpg', 2, '2002-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:23'),(26, 'zhang9', '123456', '張九', 1, '2.jpg', 2, '2011-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:25'),(27, 'zhang10', '123456', '張十', 1, '2.jpg', 2, '2004-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:27'),(28, 'zhang11', '123456', '張十一', 1, '2.jpg', 2, '2007-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:29'),(29, 'zhang12', '123456', '張十二', 1, '2.jpg', 2, '2020-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:31');

語法：

查詢多個字段：

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

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

select * from 表名;

設置別名：

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

去除重復記錄：

select distinct 字段列表 from 表名;

示例：

DQL-條件查詢

語法：

條件查詢

select 字段列表 from 表名 where 條件查詢;

比較運算符

邏輯運算符

示例：

--  =================== 條件查詢 ======================
-- 1. 查詢 姓名 為 楊逍 的員工
select * from tb_emp where name='楊逍';-- 2. 查詢在 id小于等于5 的員工信息
select * from tb_emp where id <= 5;-- 3. 查詢 沒有分配職位 的員工信息  -- 判斷 null , 用 is null
select * from tb_emp where job is null;-- 4. 查詢 有職位 的員工信息  -- 判斷 不是null , 用 is not null
select * from tb_emp where job is not null ;
-- 5. 查詢 密碼不等于 '123456' 的員工信息
select * from tb_emp where password != '123456';
select * from tb_emp where password <> '123456';-- 6. 查詢入職日期 在 '2000-01-01' (包含) 到 '2010-01-01'(包含) 之間的員工信息
select * from tb_emp where entrydate between '2000-01-01' and '2010-01-01' order by entrydate;-- 7. 查詢 入職時間 在 '2000-01-01' (包含) 到 '2010-01-01'(包含) 之間 且 性別為女 的員工信息
select * from tb_emp where entrydate between '2000-01-01' and '2010-01-01' and gender = 2;
select * from tb_emp where entrydate between '2000-01-01' and '2010-01-01' && gender = 2;-- 8. 查詢 職位是 2 (講師), 3 (學工主管), 4 (教研主管) 的員工信息
select * from tb_emp where job in (2, 3, 4) order by job desc ;-- 9. 查詢姓名為兩個字的員工信息
select * from tb_emp where name like '__';-- 10. 查詢姓 '張' 的員工信息  ---------> 張%
select * from tb_emp where name like '張%';-- 11. 查詢姓名中包含 '三' 的員工信息
select * from tb_emp where name like '%三%';

DQL-分組查詢

聚合函數
- 介紹：將一列數據作為一個整體，進行縱向計算，不對null值進行運算
- 語法：
```
select 聚合函數(字段列表) from 表名;
```

注意事項：

null值不參與所有聚合函數運算
統計數量可以使用：count(*) count(字段) count(常量) ，推薦使用count(*)

分組查詢

語法：

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

where與having區別
1. 執行時機不同：where是分組之前進行過濾，不滿足于where條件，不參與分組；而having是分組之后對結果進行過濾。
2. 判斷條件不同：where不能對聚合函數進行判斷，而having可以。

注意事項：

分組之后，查詢的字段一般為聚合函數和分組字段，查詢其他字段無任何意義
執行順序：where > 聚合函數 > having

示例：

DQL-排序查詢

語法：

條件查詢

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

排序方式

ASC：升序（默認值）
DESC：降序

注意事項：

如果是多字段排序，當第一個字段值相同時，才會根據第二個字段進行排序

示例：

DQL-分頁查詢

語法：

分頁查詢

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

注意事項：

起始索引從0開始，
$起始索引 = （查詢頁碼 ? 1 ） ? 每頁顯示記錄數$

分頁查詢是數據庫的方言，不同的數據庫有不同的實現，MySQL中是limit

如果查詢的是第一頁數據，起始索引可以省略，直接簡寫為limit 10

示例：

DQL-案例1

根據需求完成員工管理的條件分頁查詢

頁面開發規則

查詢員工

1.1 根據輸入的員工姓名、員工性別、入職時間搜索滿足條件的員工信息。

1.2 其中員工姓名，支持模糊匹配；性別進行精確查詢；入職時間進行范圍查詢。

1.3 支持分頁查詢。

1.4 并對查詢的結果，根據最后修改時間進行倒序排序。

示例：

小結：

基本查詢

條件查詢

分組查詢

分頁查詢

多表設計-概述

項目開發中，在進行數據庫表結構設計時，會根據也無需求以及業務模塊之間的關系，分析并設計表結構，由于業務之間相互關聯，所以各個表之間也存在著各種聯系，基本上分為三種：

一對多（多對一）
多對多
一對一

一對多

需求

根據頁面原型及需求文檔，完成部門及員工模塊的表結構設計。

一對多關系實現：在數據表中多的一方，添加字段，來關聯1的一方的主鍵

多表問題分析

現象

部門數據可以直接刪除，然而還有部分員工歸屬于該部門下，此時就出現了數據的不完整性、不一致問題

問題分析

目前上述的兩張表，在數據庫層面，并未建立關聯，所以是無法保證數據的一致性和完整性。

外鍵約束

外鍵語法

-- 創建表時指定
create table 表名(字段名 數據類型;...[constraint] [外鍵名稱] foreign key(外鍵字段名) references 主表(字段名)
);
-- 建完表后，添加外鍵
alter table 表名 add constraint 外鍵名稱 foreign key(外鍵字段名) references 主表(字段名);

禁止使用物理外鍵

物理外鍵

概念：使用foreign key定義外鍵關聯另外一張表
缺點：
影響增、刪、改的效率（需要檢查外鍵關系）
僅用于單點數據庫，不適用于分布式、集群場景
容易引發數據庫的死鎖問題，消耗性能

推薦使用邏輯外鍵

邏輯外鍵

概念：在業務邏輯中，解決外鍵關聯
通過邏輯外鍵，就可以很方便的解決上述問題

一對一

一對一

案例：用戶與身份信息的關系
關系：一對一關系，多用于單表拆分，將一表的基礎字段放在一張表中，其他字段放在另一個表中，以提升操作效率
實現：在任意一方加入外鍵，關聯另外一方的主鍵，并且設置外鍵為位于的（UNIQUE）

單表

拆分

多對多

多對多

案例：學生與課程的關系
關系：一個學生可以選修多門課程，一門課程也可以供多個學生選擇
實現：建立第三張中間表，中間表至少包含兩個外鍵，分別關聯兩方主鍵

案例

需求

參考資料中提供的《蒼穹外賣_管理后臺》頁面原型，設計分類管理、菜單管理、套餐管理模塊的表結構。

步驟

閱讀頁面原型及需求文檔，分析各個模塊涉及到的表結構，及表結構之間的關系
根據頁面原型及需求文檔，分析各個表結構中具體的字段及約束

閱讀頁面原型及需求文檔，分析各個模塊涉及到的表結構，及表結構之間的關系

根據頁面原型及需求文檔，分析各個表結構中具體的字段及約束

小結

1、一對多

在多的一方添加外鍵，關聯另一方的外鍵

2、一對一

任意一方，添加外鍵，關聯另一方的主鍵

3、多對多

通過中間表來維護，中間表的兩個外鍵，分別關聯另外兩張表的主鍵

多表查詢

數據準備

將資料中準備好的多表查詢數據準備的SQL腳本導入數據庫中

-- 部門管理
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 '創建時間',update_time datetime    not null comment '修改時間'
) comment '部門表';-- 員工管理(帶約束)
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 '性別, 說明: 1 男, 2 女',image       varchar(300) comment '圖像',job         tinyint unsigned comment '職位, 說明: 1 班主任,2 講師, 3 學工主管, 4 教研主管',entrydate   date comment '入職時間',dept_id     int unsigned comment '部門ID',create_time datetime         not null comment '創建時間',update_time datetime         not null comment '修改時間'
) comment '員工表';insert into tb_dept (id, name, create_time, update_time)
values (1, '學工部', now(), now()),(2, '教研部', now(), now()),(3, '咨詢部', now(), now()),(4, '就業部', now(), now()),(5, '人事部', now(), now());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', '張無忌', 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', '紀曉芙', 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', 1, '2007-02-01', 1, now(), now()),(12, 'hebiweng', '123456', '鶴筆翁', 1, '12.jpg', 1, '2008-08-18', 1, now(), now()),(13, 'fangdongbai', '123456', '方東白', 1, '13.jpg', 2, '2012-11-01', 2, 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', '宋遠橋', 1, '16.jpg', 2, '2010-01-01', 2, now(), now()),(17, 'chenyouliang', '123456', '陳友諒', 1, '17.jpg', NULL, '2015-03-21', NULL, now(), now());

概述

介紹

多表查詢：指從多張表中查詢數據
笛卡爾積：笛卡爾乘積是指在數學中，兩個集合（A集合和B集合）的所有組合情況。（在多表查詢時，需要消除無效的笛卡爾積）

笛卡爾積

消除無效的笛卡爾積

分類

連接查詢
內連接：相當于查詢A、B交集部分數據
外連接
左外連接：查詢左表所有數據（包括兩張表交集部分數據）
右外連接：查詢右表所有數據（包括兩張表交集部分數據）

子查詢

內連接（A∩B）

語法

隱式內連接：select 字段列表 from 表1, 表2 where 條件...
顯式內連接：select 字段列表 from 表1 [inner] join 表2 on 連接條件...

示例

起別名

-- 起別名
select e.name, d.name
from tb_emp e,tb_dept d
where e.dept_id = d.id;

外連接

語法

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

示例

開發中常用左外連接，右外連接可以用左外連接來替換

子查詢

概述

介紹：SQL語句中嵌套select語句，稱為嵌套查詢，又稱為子查詢。
形式：select * from t1 where column1 = (select column1 from t2 ...);
子查詢外部的語句可以是insert、update、delete、select的任何一個，最常見的是select。

標量子查詢

標量子查詢

子查詢的結果是單個值（數字、字符串、日期等），最簡單的形式
常用操作符：=、<>、>、>=、<、<=

示例

-- 標量子查詢
-- A. 查詢“教研部”的所有員工信息
-- a. 查詢 教研部 的部門ID - tb_dept
select id
from tb_dept
where name = '教研部';
-- b. 再查詢該部門ID下的員工信息 - tb_emp
select *
from tb_emp
where dept_id = 2;-- 將a. b. 合并為一條SQL
select *
from tb_emp
where dept_id = (select idfrom tb_deptwhere name = '教研部');-- B. 查詢在"東方白"入職后的員工信息
-- a. 查詢 方東白 的入職時間
select entrydate
from tb_emp
where name = '方東白';
-- b. 查詢 方東白 入職之后的員工信息
select *
from tb_emp
where entrydate > '2012-11-01';-- 將a. b. 合并為一條SQL
select *
from tb_emp
where entrydate > (select entrydatefrom tb_empwhere name = '方東白');

列子查詢

列子查詢

子查詢返回的結果是一列（可以是多行）
常用的操作符：in、not in等

示例

-- 列子查詢
-- A. 查詢 教研部 和 咨詢部 的所有員工信息
-- a. 查詢 教研部 和 咨詢部 的部門ID - tb_dept
select id
from tb_dept
where name = '教研部'or name = '咨詢部';-- b. 根據部門ID查詢該部門下的員工信息 - tb_emp
select *
from tb_emp
where dept_id in (2, 3);select *
from tb_emp
where dept_id in (select idfrom tb_deptwhere name = '教研部'or name = '咨詢部');

行子查詢

行子查詢

子查詢的結果可以是一行（可以是多列）
常用操作符：=、<>、in、not in

示例

-- 行子查詢
-- A. 查詢與“韋一笑”的入職日期 及 職位都相同的員工信息
-- a. 查詢與“韋一笑”的入職日期 及 職位
select entrydate, job
from tb_emp
where name = '韋一笑';
-- b. 查詢與其入職日期 及 職位都相同的員工信息
select *
from tb_emp
where entrydate = '2007-01-01'and job = 2;-- 合并為一條SQL
select *
from tb_emp
where entrydate = (select entrydatefrom tb_empwhere name = '韋一笑')and job = (select jobfrom tb_empwhere name = '韋一笑');-- 提高SQL效率
select *
from tb_emp
where (entrydate, job) = ('2007-01-01', 2);select *
from tb_emp
where (entrydate, job) = (select entrydate, jobfrom tb_empwhere name = '韋一笑');

表子查詢

表子查詢

子查詢返回的結果是多行多列，常作為臨時表
常用的操作符：in

示例

-- 表子查詢
-- A. 查詢入職日期是"2006-01-01"之后的員工信息，及其部門名稱
-- a. 查詢入職日期是"2006-01-01"之后的員工信息
select *
from tb_emp
where entrydate >= '2006-01-01';
-- b. 查詢這部分員工信息及其部門名稱
select e.*, d.name
from (select *from tb_emp where entrydate >= '2006-01-01') e,tb_dept d
where e.dept_id = d.id;

案例

根據需求，完成多表查詢的SQL語句的編寫

將資料中準備好的多表查詢的數據準備的SQL腳本導入數據庫中。

數據準備

-- 分類表
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 '狀態 0 禁用，1 啟用',create_time datetime not null comment '創建時間',update_time datetime not null comment '更新時間'
) 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 '菜品價格',image varchar(300) not null comment '菜品圖片',description varchar(200) comment '描述信息',status tinyint unsigned not null default 0 comment '狀態, 0 停售 1 起售',create_time datetime not null comment '創建時間',update_time datetime not null comment '更新時間'
) 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 '套餐價格',image varchar(300) not null comment '圖片',description varchar(200) comment '描述信息',status tinyint unsigned not null default 0 comment '狀態 0:停用 1:啟用',create_time datetime not null comment '創建時間',update_time datetime not null comment '更新時間'
)comment '套餐' ;-- 套餐菜品關聯表
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 '份數'
)comment '套餐菜品中間表';-- ================================== 導入測試數據 ====================================
-- 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, '傳統主食', 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, '商務套餐', 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, '經典川菜', 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, '新鮮時蔬', 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', '還是小時候的味道', 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', '優質面粉', 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,'老壇酸菜魚', 5, 56.00, 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/4a9cefba-6a74-467e-9fde-6e687ea725d7.png', '原料：湯，草魚，酸菜', 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,'經典酸菜鮰魚', 5, 66.00, 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/5260ff39-986c-4a97-8850-2ec8c7583efc.png', '原料：酸菜，江團，鮰魚', 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,'蜀味水煮草魚', 5, 38.00, 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/a6953d5a-4c18-4b30-9319-4926ee77261f.png', '原料：草魚，湯', 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,'清蒸鱸魚', 5, 98.00, 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/c18b5c67-3b71-466c-a75a-e63c6449f21c.png', '原料：鱸魚', 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,'剁椒魚頭', 5, 66.00, 'https://reggie-itcast.oss-cn-beijing.aliyuncs.com/13da832f-ef2c-484d-8370-5934a1045a06.png', '原料：鰱魚，剁椒', 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, '商務套餐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, '商務套餐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. 查詢價格低于 10元的菜品的名稱、價格及其菜品的分類名稱 .
– 2. 查詢所有價格在 10元(含)到50元(含)之間且狀態為’起售’的菜品名稱、價格及其菜品的分類名稱 (即使菜品沒有分類 , 也需要將菜品查詢出來).
– 3. 查詢每個分類下最貴的菜品, 展示出分類的名稱、最貴的菜品的價格 .
– 4. 查詢各個分類下狀態為 ‘起售’ , 并且該分類下菜品總數量大于等于3 的分類名稱 .
– 5. 查詢出 “商務套餐A” 中包含了哪些菜品（展示出套餐名稱、價格, 包含的菜品名稱、價格、份數）.
– 6. 查詢出低于菜品平均價格的菜品信息 (展示出菜品名稱、菜品價格).

案例解決示例

-- 1. 查詢價格低于 10元 的菜品的名稱 、價格 及其 菜品的分類名稱 .
select d.name, price, c.name
from dish d,category c
where d.category_id = c.idand price < 10;-- 2. 查詢所有價格在 10元(含)到50元(含)之間 且 狀態為'起售'的菜品名稱、價格 及其 菜品的分類名稱 (即使菜品沒有分類 , 也需要將菜品查詢出來).
select d.name, price, c.name
from dish dleft joincategory c on d.category_id = c.id
where price between 10 and 50and d.status = 1;-- 3. 查詢每個分類下最貴的菜品, 展示出分類的名稱、最貴的菜品的價格 .
select c.name, d.name, max(price)
from dish d,category c
where d.category_id = c.id
group by c.name;-- 4. 查詢各個分類下 狀態為 '起售' , 并且 該分類下菜品總數量大于等于3 的 分類名稱 .
select c.name, count(*)
from dish d,category c
where d.category_id = c.idand c.status = 1
group by c.name
having count(*) >= 3;-- 5. 查詢出 "商務套餐A" 中包含了哪些菜品 （展示出套餐名稱、價格, 包含的菜品名稱、價格、份數）.
select s.name, s.price, d.name, d.price, copies
from setmeal s,setmeal_dish sd,dish d
where s.id = sd.setmeal_idand sd.dish_id = d.idand s.name = '商務套餐A';-- 6. 查詢出低于菜品平均價格的菜品信息 (展示出菜品名稱、菜品價格).
-- 表：dish
-- SQL:
-- a.計算 菜品的平均價格
select avg(price)
from dish;
-- b. 查詢出低于菜品平均價格的菜品信息
select *
from dish
where price < 37.736842;
-- c.合并SQL
select *
from dish
where price < (select avg(price) from dish);

小結

內連接

隱式：

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

顯示：

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

外連接

左外：

select 字段列表 from 表1 left join 表2 on 條件...;

右外：

select 字段列表 from 表1 right join 表2 on 條件...;

子查詢
- 標量子查詢
- 列子查詢
- 行子查詢
- 表子查詢

事務

思考

場景

學工部整個部門解散了，該部門及部門下的員工都需要刪除了。

操作
-- 刪除學工部
delete from tb_dept where id = 1;
-- 刪除學工部的員工
delete from tb_emp where dept_id = 1;
問題

如果刪除部門成功了，而刪除該部門的員工時失敗了，就造成了數據的不一致

介紹&操作

介紹

概念

事物是一組操作的集合，它是一個不可分割的工作單位。事務會把所有的操作作為一個整體一起向系統提交或撤銷操作請求，即這些操作要么同時成功，要么同時失敗

注意事項

默認MySQL的事務是自動提交的，也就是說，當執行一條DML語句，MySQL會立即隱式的提交事務

操作

事務控制

開啟事務：start transaction; / begin;
提交事務：commit
回滾事務：rollback;

四大特性（ACID）

小結

事務介紹

一組操作的集合，這組操作要么全部成功，要么全部失敗

事務操作

start transaction / begin;
commit;
rollback;

事務四大特性
- 原子性
- 一致性
- 隔離性
- 持久性

數據庫優化

索引

介紹

概念

索引（index）是幫助數據庫高效獲取數據的數據結構

優缺點

優點

提高數據的查詢效率，降低數據庫的IO成本
通過索引列對數據進行排序，降低數據排序的成本，降低CPU消耗

缺點

索引會占用存儲空間
索引會大大提高了查詢效率，同時也降低了insert、update、delete的效率

結構

MySQL數據庫支持的索引結構有很多，如：Hash索引、B+Tree索引、Full-Text索引等。我們平時說的索引，如果沒有特別指明，都是默認的B+Tree結構組織的索引。

思考：存在什么問題？

大數據量情況下，層級深，檢索速度慢

B+Tree（多路平衡搜索樹）

每一個節點，可以存儲多個key（有n個key，就有n個指針）。
所有的數據都存儲在子葉節點，非子葉節點僅用于索引數據。
子葉節點形成了一顆雙向鏈表，便于數據的排序及區間范圍查詢。

語法

創建索引

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

查看索引
```
show index from 表名;
```
刪除索引
```
drop index 索引名 on 表名;
```

示例

-- 創建：為tb_emp表的name字段建立一個索引
create index idx_emp_name on tb_emp(name);
-- 查詢：查詢tb_emp表的索引信息
show index from tb_emp;
-- 刪除：刪除tb_emp表中name字段的索引
drop index idx_emp_name on tb_emp;

注意事項

主鍵字段，在建表時，會自動創建主鍵索引。
添加唯一約束，時數據庫實際上會添加唯一索引。

小結

介紹

索引是幫助數據庫高效獲取數據的數據結構
結構

MySQL數據庫中默認的索引結構是B+Tree

語法

-- 創建
create [unique] index 索引名 on 表名(字段名,...);
-- 查詢
show index from 表名;
-- 刪除
drop index 索引名 on 表名;

[外鏈圖片轉存中…(img-ISDCQVyK-1700729341869)]

小結

事務介紹

一組操作的集合，這組操作要么全部成功，要么全部失敗

事務操作

start transaction / begin;
commit;
rollback;

事務四大特性
- 原子性
- 一致性
- 隔離性
- 持久性

數據庫優化

索引

介紹

概念

索引（index）是幫助數據庫高效獲取數據的數據結構

[外鏈圖片轉存中…(img-6jYDMBfT-1700729341870)]

優缺點

優點

提高數據的查詢效率，降低數據庫的IO成本
通過索引列對數據進行排序，降低數據排序的成本，降低CPU消耗

缺點

索引會占用存儲空間
索引會大大提高了查詢效率，同時也降低了insert、update、delete的效率

結構

[外鏈圖片轉存中…(img-biITHPma-1700729341870)]

思考：存在什么問題？

大數據量情況下，層級深，檢索速度慢

B+Tree（多路平衡搜索樹）

[外鏈圖片轉存中…(img-gS4x8a7D-1700729341871)]

每一個節點，可以存儲多個key（有n個key，就有n個指針）。
所有的數據都存儲在子葉節點，非子葉節點僅用于索引數據。
子葉節點形成了一顆雙向鏈表，便于數據的排序及區間范圍查詢。

語法

創建索引

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

查看索引
```
show index from 表名;
```
刪除索引
```
drop index 索引名 on 表名;
```

示例

-- 創建：為tb_emp表的name字段建立一個索引
create index idx_emp_name on tb_emp(name);
-- 查詢：查詢tb_emp表的索引信息
show index from tb_emp;
-- 刪除：刪除tb_emp表中name字段的索引
drop index idx_emp_name on tb_emp;

注意事項

主鍵字段，在建表時，會自動創建主鍵索引。
添加唯一約束，時數據庫實際上會添加唯一索引。

小結

介紹

索引是幫助數據庫高效獲取數據的數據結構
結構

MySQL數據庫中默認的索引結構是B+Tree

語法

-- 創建
create [unique] index 索引名 on 表名(字段名,...);
-- 查詢
show index from 表名;
-- 刪除
drop index 索引名 on 表名;

MySQL數據庫_01

Web后端開發_02

數據庫介紹

什么是數據庫？

數據庫產品

數據庫設計

MySQL概述

安裝、配置

MySQL安裝：

MySQL-企業開發使用方式

數據模型

SQL簡介

通用語法

SQL分類

數據庫設計-DDL

數據庫

MySQL客戶端工具-圖像化工具

表（創建、查詢、修改、刪除）

DDL-表操作-創建

DDL-表操作-查詢

DDL-表操作-修改

DDL-表操作-刪除

約束：

數據類型：

數值類型

字符串類型

日期時間類型

案例

多表設計

數據庫操作

數據庫操作-DML

添加數據（INSERT）

修改數據（UPDATE）

刪除數據（DELETE）

數據庫操作-DQL

DQL-基本查詢

DQL-條件查詢

DQL-分組查詢

DQL-排序查詢

DQL-分頁查詢

DQL-案例1

小結：

多表設計-概述

一對多

多表問題分析

外鍵約束

一對一

多對多

案例

小結

多表查詢

概述

內連接（A∩B）

外連接

子查詢

分類

標量子查詢

列子查詢

行子查詢

表子查詢

案例

數據準備

案例需求

案例解決示例

小結

事務

思考

介紹&操作

介紹

操作

四大特性（ACID）

小結

數據庫優化

索引

介紹

優缺點

結構

語法

小結

小結