接上篇：MySQL數(shù)據(jù)庫基礎(chǔ)（一）：數(shù)據(jù)庫安裝和DataGrip安裝

六、DDL數(shù)據(jù)庫操作

1、MySQL的組成結(jié)構(gòu)

注：我們平常說的MySQL，其實(shí)主要指的是MySQL數(shù)據(jù)庫管理軟件。

一個(gè)MySQL DBMS可以同時(shí)存放多個(gè)數(shù)據(jù)庫，理論上一個(gè)項(xiàng)目就對(duì)應(yīng)一個(gè)數(shù)據(jù)庫。如博客項(xiàng)目blzhujianog數(shù)據(jù)庫、商城項(xiàng)目shop數(shù)據(jù)庫、微信項(xiàng)目wechat數(shù)據(jù)庫。

一個(gè)數(shù)據(jù)庫中還可以同時(shí)包含多個(gè)數(shù)據(jù)表，而數(shù)據(jù)表才是真正用于存放數(shù)據(jù)的位置。（類似我們Office軟件中的Excel表格），理論上一個(gè)功能就對(duì)應(yīng)一個(gè)數(shù)據(jù)表。如博客系統(tǒng)中的用戶管理功能，就需要一個(gè)user數(shù)據(jù)表、博客中的文章就需要一個(gè)article數(shù)據(jù)表、博客中的評(píng)論就需要一個(gè)message數(shù)據(jù)表。

一個(gè)數(shù)據(jù)表又可以拆分為多個(gè)字段，每個(gè)字段就是一個(gè)屬性。

一個(gè)數(shù)據(jù)表除了字段以外，還有很多行，每一行都是一條完整的數(shù)據(jù)（記錄）。

2、數(shù)據(jù)庫的基本操作

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

普及英語小課堂：

創(chuàng)建 => create

數(shù)據(jù)庫 => database

創(chuàng)建 + 數(shù)據(jù)庫 = create database

數(shù)據(jù)庫名稱(字母+數(shù)字+下劃線組成，以字母開頭，不能出現(xiàn)中文以及特殊字符)

基本語法：

特別注意：在MySQL中，當(dāng)一條SQL語句編寫完畢后，一定要使用分號(hào);進(jìn)行結(jié)尾，否則系統(tǒng)認(rèn)為這條語句還沒有結(jié)束。

案例：創(chuàng)建數(shù)據(jù)庫的相關(guān)案例

擴(kuò)展：編碼格式，常見的gbk（中國的編碼格式）與utf8（國際通用編碼格式）

latin1 256個(gè)字符

國內(nèi)漢字無法通過256個(gè)字符進(jìn)行描述，所以國內(nèi)開發(fā)了自己的編碼格式gb2312，升級(jí)gbk

中國臺(tái)灣業(yè)開發(fā)了一套自己的編碼格式big5

很多項(xiàng)目并不僅僅只在本地使用，也可能支持多國語言，標(biāo)準(zhǔn)化組織開發(fā)了一套通用編碼utf8，后來5.6版本以后又進(jìn)行了升級(jí)utf8mb4

編寫SQL語句是一個(gè)比較細(xì)致工作，不建議大家直接在終端中輸入SQL語句，可以先把你要寫的SQL語句寫入一個(gè)記事本中，然后拷貝執(zhí)行。

② 查詢數(shù)據(jù)庫

英語小課堂：

顯示 => show

數(shù)據(jù)庫 => database

顯示 + 所有數(shù)據(jù)庫 = show databases;

基本語法：顯示所有數(shù)據(jù)庫

③ 刪除數(shù)據(jù)庫

英語小課堂：

刪除 => drop

數(shù)據(jù)庫 => database

刪除 + 數(shù)據(jù)庫 = drop database

數(shù)據(jù)庫名稱; 基本語法：

案例：刪除db_itheima數(shù)據(jù)庫

④ 選擇數(shù)據(jù)庫

從數(shù)據(jù)庫列表中查找需要使用的數(shù)據(jù)庫

格式：

查看正在使用的數(shù)據(jù)庫（8.0以后版本需要基于select查詢來獲取當(dāng)前數(shù)據(jù)庫）

3、小結(jié)

創(chuàng)建數(shù)據(jù)庫：CREATE DATABASE 數(shù)據(jù)庫名；

查看數(shù)據(jù)庫：SHOW DATABASES;

刪除數(shù)據(jù)庫：DROP DATABASE 數(shù)據(jù)庫名； 使用數(shù)據(jù)庫：USE 數(shù)據(jù)庫名；

七、DDL數(shù)據(jù)表操作

特別注意：創(chuàng)建數(shù)據(jù)表必須有一個(gè)前提，首先要明確選擇某一個(gè)數(shù)據(jù)庫。

1、數(shù)據(jù)表的基本操作

☆ 數(shù)據(jù)表的創(chuàng)建

英語小課堂：

創(chuàng)建 => create

數(shù)據(jù)表 => table

創(chuàng)建 + 數(shù)據(jù)表 = create table 數(shù)據(jù)表名稱

基本語法：

案例：創(chuàng)建一個(gè)admin管理員表，擁有3個(gè)字段（編號(hào)、用戶名稱、用戶密碼）

use在MySQL中的含義代表選擇，use 數(shù)據(jù)庫名稱相當(dāng)于選擇指定的數(shù)據(jù)庫。而且use比較特殊，其選擇結(jié)束后，其尾部可以不加分號(hào)；但是強(qiáng)烈建議所有的SQL語句都要加分號(hào)，養(yǎng)成一個(gè)好習(xí)慣。

tinyint ：微整型，范圍-128 ~ 127，無符號(hào)型，則表示0 ~ 255 表示字符串類型可以使用char與varchar，char代表固定長度的字段，varchar代表變化長度的字段。

案例：創(chuàng)建一個(gè)article文章表，擁有4個(gè)字段（編號(hào)、標(biāo)題、作者、內(nèi)容）

text ：文本類型，一般情況下，用varchar存儲(chǔ)不了的字符串信息，都建議使用text文本進(jìn)行處理。

varchar存儲(chǔ)的最大長度，理論值65535個(gè)字符。但是實(shí)際上，有幾個(gè)字符是用于存放內(nèi)容的長度的，所以真正可以使用的不足65535個(gè)字符，另外varchar類型存儲(chǔ)的字符長度還和編碼格式有關(guān)。1個(gè)GBK格式的占用2個(gè)字節(jié)長度，1個(gè)UTF8格式的字符占用3個(gè)字節(jié)長度。GBK = 65532~65533/2，UTF8 = 65532~65533/3

☆ 查詢已創(chuàng)建數(shù)據(jù)表

英語小課堂：

顯示 => show

數(shù)據(jù)表 => table

顯示所有數(shù)據(jù)表（當(dāng)前數(shù)據(jù)庫）

顯示數(shù)據(jù)表的創(chuàng)建過程（編碼格式、字段等信息）

☆ 修改數(shù)據(jù)表信息

① 數(shù)據(jù)表字段添加

英語小課堂：

修改 => alter

數(shù)據(jù)表 => table

基本語法：

案例：在tb_article文章表中添加一個(gè)addtime字段，類型為date(年-月-日)

② 修改字段名稱或字段類型

修改字段名稱與字段類型（也可以只修改名稱）

mysql> alter table tb_admin change username user varchar(40);mysql> desc tb_admin;

僅修改字段的類型

③ 刪除某個(gè)字段

④ 修改數(shù)據(jù)表名稱

☆ 刪除數(shù)據(jù)表

英語小課堂：

刪除 => drop

數(shù)據(jù)表 => table

2、字段類型詳解

① 整數(shù)類型

② 浮點(diǎn)類型 浮點(diǎn)類型（精度失真情況）和定點(diǎn)類型（推薦使用定點(diǎn)類型）

decimal(10,2) ：代表這個(gè)數(shù)的總長度為10 = 整數(shù)長度 + 小數(shù)長度，2代表保留2位小數(shù)

③ 日期類型

④ 文本

八、DML數(shù)據(jù)操作語言

1、DML包括哪些SQL語句

insert插入、update更新、delete刪除

2、數(shù)據(jù)的增刪改（重點(diǎn)）

英語小課堂： 增加：insert 刪除：delete 修改：update

☆ 數(shù)據(jù)的增加操作

基本語法：

特別注意：在SQL語句中，除了數(shù)字，其他類型的值，都需要使用引號(hào)引起來，否則插入時(shí)會(huì)報(bào)錯(cuò)。

第一步：準(zhǔn)備一個(gè)數(shù)據(jù)表

unsigned代表無符號(hào)型，只有0到正數(shù)。tinyint unsigned無符號(hào)型，范圍0 ~ 255 enum枚舉類型，多選一。只能從給定的值中選擇一個(gè)

第二步：使用insert語句插入數(shù)據(jù)

第三步：批量插入多條數(shù)據(jù)

☆ 數(shù)據(jù)的修改操作

基本語法：

特別說明：如果在更新數(shù)據(jù)時(shí)，不指定更新條件，則其會(huì)把這個(gè)數(shù)據(jù)表的所有記錄全部更新一遍。

案例：修改username='馬鵬'這條記錄，將其性別更新為男，家庭住址更新為廣東省深圳市

案例：今年是2020年，假設(shè)到了2021年，現(xiàn)在存儲(chǔ)的學(xué)員年齡都差1歲，整體進(jìn)行一次更新

☆ 數(shù)據(jù)的刪除操作

基本語法：

案例：刪除tb_user表中，id=1的用戶信息

delete from與truncate清空數(shù)據(jù)表操作

delete from與truncate區(qū)別在哪里？

• delete：刪除數(shù)據(jù)記錄

• 數(shù)據(jù)操作語言（DML）

• 刪除大量記錄速度慢，只刪除數(shù)據(jù)，主鍵自增序列不清零，100 => 新插入 => 101

• 可以帶條件刪除

• truncate：刪除所有數(shù)據(jù)記錄

• 數(shù)據(jù)定義語言（DDL）

• 清里大量數(shù)據(jù)速度快，主鍵自增序列清零, 100 => 新插入 => 1

• 不能帶條件刪除

九、SQL約束

1、主鍵約束

1、PRIMARY KEY 約束唯一標(biāo)識(shí)數(shù)據(jù)庫表中的每條記錄。

2、主鍵必須包含唯一的值。

3、主鍵列不能包含 NULL 值。

4、每個(gè)表都應(yīng)該有一個(gè)主鍵，并且每個(gè)表只能有一個(gè)主鍵。 主鍵約束：要求主鍵這一列必須是唯一的、非空，另外一定要特別注意：一個(gè)表可以沒有主鍵，但是如果有主鍵只能有且僅有一個(gè)主鍵。

遵循原則：

1）主鍵應(yīng)當(dāng)是對(duì)用戶沒有意義的（沒創(chuàng)建一個(gè)數(shù)據(jù)表默認(rèn)都應(yīng)該擁有一個(gè)id字段）

2）永遠(yuǎn)也不要更新主鍵。

3）主鍵不應(yīng)包含動(dòng)態(tài)變化的數(shù)據(jù)，如時(shí)間戳、創(chuàng)建時(shí)間列、修改時(shí)間列等。（1-無窮大）

4） 主鍵應(yīng)當(dāng)由計(jì)算機(jī)自動(dòng)生成。 創(chuàng)建主鍵約束：創(chuàng)建表時(shí)，在字段描述處，聲明指定字段為主鍵

刪除主鍵約束：如需撤銷 PRIMARY KEY 約束，請(qǐng)使用下面的 SQL

補(bǔ)充：自動(dòng)增長 => auto_increment，從1開始，依次遞增

我們通常希望在每次插入新記錄時(shí)，數(shù)據(jù)庫自動(dòng)生成字段的值。 我們可以在表中使用 auto_increment（自動(dòng)增長列）關(guān)鍵字，自動(dòng)增長列類型必須是整型，自動(dòng)增長列必須為鍵(一般是主鍵)。

下列 SQL 語句把 "Persons" 表中的 "Id" 列定義為 auto_increment 主鍵

向persons添加數(shù)據(jù)時(shí)，可以不為Id字段設(shè)置值，也可以設(shè)置成null，數(shù)據(jù)庫將自動(dòng)維護(hù)主鍵值：

運(yùn)行效果：

特別說明：如果某個(gè)列設(shè)置為自動(dòng)增長，則這一列必須設(shè)置為主鍵，否則系統(tǒng)會(huì)報(bào)錯(cuò)。

2、非空約束

NOT NULL 約束強(qiáng)制列不接受 NULL 值。

NOT NULL 約束強(qiáng)制字段始終包含值。這意味著，如果不向字段添加值，就無法插入新記錄或者更新記錄。 下面的 SQL 語句強(qiáng)制 "id" 列和 "last_name" 列不接受 NULL 值：

3、唯一約束

UNIQUE 約束唯一標(biāo)識(shí)數(shù)據(jù)庫表中的每條記錄。

UNIQUE 和 PRIMARY KEY 約束均為列或列集合提供了唯一性的保證。

PRIMARY KEY 擁有自動(dòng)定義的 UNIQUE 約束。

注：雖然唯一約束與主鍵約束在描述上功能類似，但是其實(shí)還是很多區(qū)別的。

相同點(diǎn)：都可以表示唯一性，唯一標(biāo)識(shí)數(shù)據(jù)表中的每一條記錄（一行）

不同點(diǎn)：

① 插入數(shù)據(jù)的來源不同 主鍵大多數(shù)都是自動(dòng)編號(hào)結(jié)合primary key 唯一大多數(shù)都是手工設(shè)置（不一定都是數(shù)字類型，如身份證號(hào)也可以表示唯一）

② 主鍵必須非空字段，但是唯一約束不要求列必須是非空的，這一列也可以出現(xiàn)null空值

③ 一個(gè)數(shù)據(jù)表中如果有主鍵，有且僅有1個(gè)主鍵。但是在一個(gè)數(shù)據(jù)表中可以擁有多個(gè)唯一約束 所以數(shù)據(jù)庫考察中，經(jīng)常會(huì)問：主鍵約束與唯一約束區(qū)別

主鍵一定滿足唯一約束，但是唯一約束不一定是主鍵。

請(qǐng)注意：

每個(gè)表可以有多個(gè) UNIQUE 約束，但是每個(gè)表只能有一個(gè) PRIMARY KEY 約束。

4、默認(rèn)值約束

default 默認(rèn)值

5、外鍵約束(了解)

外鍵約束（多表關(guān)聯(lián)使用）

比如：有兩張數(shù)據(jù)表，這兩個(gè)數(shù)據(jù)表之間有聯(lián)系，通過了某個(gè)字段可以建立連接，這個(gè)字段在其中一個(gè)表中是主鍵，在另外一張表中，我們就把其稱之為外鍵。

6、小結(jié)

① 主鍵約束：唯一標(biāo)示，不能重復(fù)，不能為空。

1）主鍵應(yīng)當(dāng)是對(duì)用戶沒有意義的，一般都叫id

2）永遠(yuǎn)也不要更新主鍵，自動(dòng)編號(hào)

3）主鍵不應(yīng)包含動(dòng)態(tài)變化的數(shù)據(jù)，如時(shí)間戳、創(chuàng)建時(shí)間列、修改時(shí)間列等。

4）主鍵應(yīng)當(dāng)由計(jì)算機(jī)自動(dòng)生成，一般配合auto_increment自動(dòng)增長

自動(dòng)增長：

我們可以在表中使用 auto_increment（自動(dòng)增長列）關(guān)鍵字，自動(dòng)增長列類型必須是整型，自動(dòng)增長列必須為鍵(一般是主鍵)。

② 非空約束：

NOT NULL 約束強(qiáng)制列不接受 NULL 值。

③ 唯一約束：

UNIQUE 約束唯一標(biāo)識(shí)數(shù)據(jù)庫表中的每條記錄。

UNIQUE 和 PRIMARY KEY 約束均為列或列集合提供了唯一性的保證。

PRIMARY KEY 擁有自動(dòng)定義的 UNIQUE 約束。

④ 默認(rèn)值約束 default 默認(rèn)值

⑤ 外鍵約束： 多表關(guān)聯(lián)字段、在一個(gè)表中是主鍵，在另外一個(gè)表中是非主鍵（則這個(gè)字段一般就稱之外鍵）

十、DQL數(shù)據(jù)查詢語言

1、數(shù)據(jù)集準(zhǔn)備

插入數(shù)據(jù)：

DataGrip軟件關(guān)鍵字替換，可以使用Ctrl + R快捷鍵

2、select查詢

3、簡單查詢

4、條件查詢（where子句）

☆ 比較查詢

☆ 范圍查詢

☆ 邏輯查詢

☆ 模糊查詢

☆ 非空查詢

5、排序查詢（order by子句）

6、聚合查詢

之前我們做的查詢都是橫向查詢，它們都是根據(jù)條件一行一行的進(jìn)行判斷，而使用聚合函數(shù)查詢是縱向查詢，它是對(duì)一列的值進(jìn)行計(jì)算，然后返回一個(gè)單一的值；另外聚合函數(shù)會(huì)忽略空值。 今天我們學(xué)習(xí)如下五個(gè)聚合函數(shù)：

案例演示：

7、分組查詢與having子句

☆ 分組查詢介紹

分組查詢就是將查詢結(jié)果按照指定字段進(jìn)行分組，字段中數(shù)據(jù)相等的分為一組。

分組查詢基本的語法格式如下：

GROUP BY 列名 [HAVING 條件表達(dá)式] [WITH ROLLUP]

說明:

• 列名: 是指按照指定字段的值進(jìn)行分組。

• HAVING 條件表達(dá)式: 用來過濾分組后的數(shù)據(jù)。

• WITH ROLLUP：在所有記錄的最后加上一條記錄，顯示select查詢時(shí)聚合函數(shù)的統(tǒng)計(jì)和計(jì)算結(jié)果

☆ group by的使用

group by可用于單個(gè)字段分組，也可用于多個(gè)字段分組

① group by可以實(shí)現(xiàn)去重操作

② group by的作用是為了實(shí)現(xiàn)分組統(tǒng)計(jì)（group by + 聚合函數(shù)）

☆ group by + 聚合函數(shù)的使用

☆ group_concat()函數(shù)

group_concat()是MySQL中自帶的一個(gè)函數(shù)，這個(gè)函數(shù)主要用于分組查詢中。其主要功能可以某個(gè)分組中的數(shù)據(jù)中的某列（字段）值進(jìn)行concat拼接操作。 案例：統(tǒng)計(jì)男分組中，一共有哪些人，字段值與字段值之間使用逗號(hào)隔開。

☆ with rollup回溯統(tǒng)計(jì)

with rollup主要應(yīng)用于group by分組中，代表對(duì)所有分組數(shù)據(jù)進(jìn)行回溯統(tǒng)計(jì)。

但是我們希望在以上分組的基礎(chǔ)上，在添加一行，沒有分組，只有一個(gè)具體的值。這個(gè)值是以上兩個(gè)分組的總記錄數(shù)。

8、having的使用

having作用和where類似都是過濾數(shù)據(jù)的，但是兩者之間的執(zhí)行順序不同

① where子句 ② group by子句 ③ having子句

第一種情況：如果只是簡單的查詢操作（沒有g(shù)roup by的情況），大部分時(shí)間having是可以直接替代where子句

select * from product where price > 800;

以上語句等價(jià)于

select * from product having price > 800;

第二種情況：

案例演示：

9、limit子句的使用

應(yīng)用場景：限制查詢與分頁查詢

限制查詢：主要限制數(shù)據(jù)查詢的數(shù)量（獲取數(shù)據(jù)表中的前3條數(shù)據(jù)）

select * from 數(shù)據(jù)表 limit 查詢數(shù)量;

分頁查詢：實(shí)際特別接近，因?yàn)橹灰蟹猪摰牡胤剑讓?00%都是使用limit子句實(shí)現(xiàn)的

分頁查詢?cè)陧?xiàng)目開發(fā)中常見，由于數(shù)據(jù)量很大，顯示屏長度有限，因此對(duì)數(shù)據(jù)需要采取分頁顯示方式。例如數(shù)據(jù)共有30條，每頁顯示5條，第一頁顯示1-5條，第二頁顯示6-10條。

格式：

10、小結(jié)