柚子快報(bào)邀請(qǐng)碼778899分享：數(shù)據(jù)庫(kù) MySQL詳解

http://yzkb.51969.com/

一、MySQL介紹

1.1 引言

之前在學(xué)習(xí)JavaSE基礎(chǔ)時(shí)，存儲(chǔ)數(shù)據(jù)的方式有哪些？

Java程序存儲(chǔ)數(shù)據(jù)（變量，對(duì)象，數(shù)組，集合），數(shù)據(jù)都會(huì)被存儲(chǔ)在Java內(nèi)存中，屬于瞬時(shí)狀態(tài)存儲(chǔ)?；贗O的方式，將Java內(nèi)存中的數(shù)據(jù)，持久化到本地的一個(gè)文件中，保存到硬盤上，屬于持久狀態(tài)存儲(chǔ)。

上述的存儲(chǔ)方式存在什么問(wèn)題呢？

基于內(nèi)存存儲(chǔ)數(shù)據(jù)，明顯不安全，JVM一停，數(shù)據(jù)沒(méi)了，非常不安全。如果采用IO的形式，存儲(chǔ)到本地文件

沒(méi)有數(shù)據(jù)類型的區(qū)分，會(huì)導(dǎo)致操作持久化的數(shù)據(jù)很麻煩。存儲(chǔ)的數(shù)據(jù)量級(jí)比較小，如果將1個(gè)G的數(shù)據(jù)存到一個(gè)文件里，很難維護(hù)。沒(méi)有訪問(wèn)的安全限制。沒(méi)有做備份，和誤操恢復(fù)的能力。想查詢某一個(gè)數(shù)據(jù)，但是很難找。

1.2 數(shù)據(jù)庫(kù)

數(shù)據(jù)庫(kù)是按照數(shù)據(jù)結(jié)構(gòu)來(lái)組織，存儲(chǔ)和管理數(shù)據(jù)的倉(cāng)庫(kù)。是一個(gè)長(zhǎng)期存儲(chǔ)在計(jì)算機(jī)內(nèi)的，有組織的，有共享的統(tǒng)一管理的數(shù)據(jù)集合。

數(shù)據(jù)庫(kù)的分類其他非常多，咱們現(xiàn)在只需要關(guān)注兩種即可：

關(guān)系型數(shù)據(jù)庫(kù)：Oracle、MySQL、SQL Server、PostgreSQL、DB2，這些都是以表格Table的形式存儲(chǔ)，多表格之間建立關(guān)聯(lián)關(guān)系，通過(guò)分類，合并，連接，選取以及一些運(yùn)算進(jìn)行訪問(wèn)。非關(guān)系型數(shù)據(jù)庫(kù)：Redis、Elasticsearch、MongoDB、HBase等等都屬于非關(guān)系型數(shù)據(jù)庫(kù)，他們的存儲(chǔ)方式各有各的道。

1.3 MySQL介紹

MySQL屬于 關(guān)系型數(shù)據(jù)庫(kù) ，由瑞典的MySQL AB公司開(kāi)發(fā)，屬于Oracle旗下的產(chǎn)品。MySQL依然是最主流的關(guān)系型數(shù)據(jù)庫(kù)之一，在WEB應(yīng)用方面，MySQL是最好的關(guān)系型數(shù)據(jù)庫(kù)之一。

MySQL官網(wǎng)：https://www.mysql.com/

現(xiàn)在關(guān)系型數(shù)據(jù)庫(kù)的種類還是比較多的，每種數(shù)據(jù)庫(kù)的操作方式會(huì)有一些不同，但是大方向上基本都是一樣的，關(guān)系型數(shù)據(jù)庫(kù)，后期都基于SQL語(yǔ)句做基本交互，每個(gè)數(shù)據(jù)庫(kù)都是基于SQL99規(guī)范。但是一些細(xì)節(jié)內(nèi)容可能會(huì)有一些不同，但是大方向基本沒(méi)有特別大的區(qū)別。

二、MySQL下載&安裝

MySQL現(xiàn)在的主流版本就是5.7和8.0，如果你電腦上已經(jīng)有這兩個(gè)版本的任意版本，不要卸載再安裝，成本蠻高的。

2.1 安裝MySQL 5.7（別跳過(guò)）

首先去官網(wǎng)下載MySQL 5.7的安裝包。

下載好一個(gè)Windows環(huán)境下的安裝包，不需要做太多的額外操作，基本就是傻瓜式安裝，不停的下一步下一步下一步下一步…………

到這，MySQL5.7就安裝好了。同時(shí)可以測(cè)試一下鏈接效果。

也可以配置一下環(huán)境變量，找一下MySQL的默認(rèn)安裝地址

我沒(méi)指定安裝地址，默認(rèn)就是這：C:\Program Files\MySQL\MySQL Server 5.7\bin

將這個(gè)路徑配置到環(huán)境變量的Path里。

配置好之后， 重新打開(kāi)一個(gè)cmd窗口?。。。?！ 鏈接

2.2 安裝MySQL 8.0

看8.0之前，看一下5.7的安裝，流程基本都是一致的。

跟5.7一樣，去逛網(wǎng)下載MySQL的安裝包，這次下載的是8.0的版本

下載好之后，直接打開(kāi)即可。

這次安裝流程一致，只是安裝的服務(wù)是8.0的，沒(méi)貼圖的，就是和MySQL5.7一樣的操作

安裝成功后，測(cè)試一下鏈接。

環(huán)境變量和MySQL5.7的方式是一樣的。

重新打開(kāi)一個(gè)cmd窗口?。。。。℃溄?/p>

三、SQL語(yǔ)言

3.1 概念

SQL（Structured Query Language）結(jié)構(gòu)化查詢語(yǔ)言。SQL用于對(duì)存儲(chǔ)數(shù)據(jù)，更新，查詢和管理關(guān)系型數(shù)據(jù)庫(kù)的程序設(shè)計(jì)語(yǔ)言。

通常執(zhí)行對(duì)數(shù)據(jù)庫(kù)的增刪改查，簡(jiǎn)稱C（Create）R（Read）U（Update）D（Delete）

在MySQL中有一點(diǎn)：

對(duì)于數(shù)據(jù)庫(kù)的操作，需要進(jìn)入的MySQL環(huán)境下進(jìn)行指令輸入，屬于完一個(gè)語(yǔ)句，需要使用 ; 進(jìn)行結(jié)尾。

3.2 基本操作

在做基本操作之前，先要對(duì)MySQL的整體結(jié)構(gòu)有一個(gè)了解。

一個(gè)MySQL服務(wù)下會(huì)有很多個(gè)庫(kù)，其中MySQL中會(huì)自帶4個(gè)庫(kù)，下面綠色的標(biāo)識(shí)。

這四個(gè)不要?jiǎng)?。要玩的話，需要自己主?dòng)的去創(chuàng)建數(shù)據(jù)庫(kù)。

開(kāi)始操作，首先用doc窗口，或者用MySQL提供的命令窗口去進(jìn)入到MySQL環(huán)境。

查看MySQL中所有的數(shù)據(jù)庫(kù)

mysql> show databases; # 顯示當(dāng)前MySQL中包含的所有數(shù)據(jù)庫(kù)

數(shù)據(jù)庫(kù)名稱描述information_schema信息數(shù)據(jù)庫(kù)，保存著關(guān)乎所有數(shù)據(jù)庫(kù)的信息（元數(shù)據(jù)）。mysql核心數(shù)據(jù)庫(kù)，主要負(fù)責(zé)存儲(chǔ)數(shù)據(jù)庫(kù)的用戶，權(quán)限設(shè)置，關(guān)鍵字等等。performance_schema性能優(yōu)化的數(shù)據(jù)庫(kù)，性能優(yōu)化的引擎等等都在里面。sys系統(tǒng)數(shù)據(jù)庫(kù)存儲(chǔ)元數(shù)據(jù)信息的庫(kù)，可以了解系統(tǒng)瓶頸的問(wèn)題。

創(chuàng)建自定義的數(shù)據(jù)庫(kù)

mysql> create database 數(shù)據(jù)庫(kù)名稱;

mysql> create database 數(shù)據(jù)庫(kù)名稱 character set utf8mb4;

mysql> create database if not exists 數(shù)據(jù)庫(kù)名稱;

查看數(shù)據(jù)庫(kù)創(chuàng)建信息

mysql> show create database 數(shù)據(jù)庫(kù)名稱; # 查看數(shù)據(jù)庫(kù)創(chuàng)建時(shí)的基本信息

修改數(shù)據(jù)庫(kù)（了解）

mysql> alter database 數(shù)據(jù)庫(kù)名稱 character set gbk; # 修改數(shù)據(jù)庫(kù)的字符集

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

mysql> drop database 數(shù)據(jù)庫(kù)名稱;

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

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

查看當(dāng)前所使用的數(shù)據(jù)庫(kù)

mysql> select database(); # 查看當(dāng)前使用的是哪個(gè)數(shù)據(jù)庫(kù)

四、客戶端工具

這里就帶領(lǐng)大家安裝一個(gè)Navicat工具。

類似其他的客戶端工具就不帶領(lǐng)大家去玩了，咱們就接觸Navicat就足夠了。

安裝OK之后，直接基于Navicat對(duì)MySQL做一些基本的操作

指定上各種連接信息后，會(huì)發(fā)現(xiàn)Navicat無(wú)法正常連接，原因是因?yàn)镸ySQL升級(jí)到8.0版本之后，采用的加密方式是caching_sha2_password，這種加密導(dǎo)致8.0的版本用遠(yuǎn)程工具無(wú)法正常的鏈接，需要將現(xiàn)在用的root用戶的密碼加密方式，修改為mysql_native_password的方式。

用黑窗口，連接上MySQL服務(wù)，在內(nèi)部執(zhí)行下面的指令

mysql> alter user 'root'@'localhost' identified with mysql_native_password by '密碼';

鏈接數(shù)據(jù)庫(kù)成功后，會(huì)看到這個(gè)界面

五、DQL操作（重要）

5.1 數(shù)據(jù)庫(kù)表的基本結(jié)構(gòu)

關(guān)系結(jié)構(gòu)數(shù)據(jù)庫(kù)的是以表格（Table）進(jìn)行數(shù)據(jù)的存儲(chǔ)，表格由 行 和 列 組成。

這里提供了一套test庫(kù)的表和測(cè)試數(shù)據(jù)，后面操作都基于這幾張表來(lái)玩。

構(gòu)建好test庫(kù)，將課程資料中提供的test.sql文件，直接拖拽到表的位置

導(dǎo)入之后，關(guān)閉，需要在表的位置按F5，刷新一波。

5.2 基本查詢

語(yǔ)法：SELECT 列名 FROM 表名

關(guān)鍵字描述SELECT指定要查詢的列FROM指定要查詢的表

5.2.1 查詢部分列

-- 查詢員工表中的員工id，名稱，郵件。

-- MySQL中可以忽略列名的大小寫，表名也可以忽略。

SELECT

employee_id,first_name,last_name,email

FROM

t_employees;

5.2.2 查詢所有列

在公司中開(kāi)發(fā)的過(guò)程中，哪怕你有200個(gè)列，需要查詢200個(gè)，你也必須寫200個(gè)列。 在自己玩的時(shí)候，為了提升學(xué)習(xí)的速度，可以通過(guò) * 來(lái)代表所有的列。

*這個(gè)關(guān)鍵字，可以放在SELECT的后面，代表所有的列。

-- 查詢?nèi)康牧?/p>

SELECT

*

FROM

t_employees;

5.2.3 對(duì)列中的數(shù)據(jù)進(jìn)行運(yùn)算

查詢員工表中，員工的名稱，以及所有員工的年薪是多少（12個(gè)月的薪資）

-- 查詢員工表中，員工的名稱，以及所有員工的年薪是多少（12個(gè)月的薪資）

SELECT

first_name,last_name,salary * 12

FROM

t_employees;

算術(shù)運(yùn)算符描述+兩列做加法運(yùn)算-兩列做減法運(yùn)算*兩列做乘法運(yùn)算/兩列做除法運(yùn)算

Ps：%符號(hào)，不是取模，在MySQL中，%屬于一個(gè)通配符。

5.2.4 列的別名

查詢員工表中，員工的名稱，以及所有員工的年薪是多少（12個(gè)月的薪資），同時(shí)給返回的虛擬表的列設(shè)置中文名稱

可以在查詢的列后面，追加上as關(guān)鍵字，然后指定返回的列的別名

也可以簡(jiǎn)寫，不寫as，直接在查詢的列后面追加列的別名

SELECT

first_name as "名", last_name as "姓", salary * 12 年薪

FROM

t_employees;

5.2.5 查詢結(jié)果去重

通過(guò)員工表，查詢部門ID。

-- 通過(guò)員工表，查詢部門ID

SELECT

department_id

FROM

t_employees

通過(guò)員工表，查詢部門ID,不查看重復(fù)數(shù)據(jù)，去重。

DISTINCT放在所有列名的最前面，會(huì)幫你把數(shù)據(jù)重復(fù)的行，去掉。

-- 通過(guò)員工表，查詢部門ID,不查看重復(fù)數(shù)據(jù)，去重.

SELECT

DISTINCT department_id

FROM

t_employees

5.3 排序查詢

在執(zhí)行SELECT查詢后，查詢到的結(jié)果一般是跟表中的結(jié)果順序一致的。

如果需要基于一些列做排序，可以使用MySQL提供的order by 操作

語(yǔ)法： SELECT 列名 FROM 表名 ORDER BY 排序列 [排序規(guī)則] , 排序列 [排序規(guī)則] …………

排序規(guī)則描述ASC（默認(rèn)規(guī)則）對(duì)前面排序列做升序排序DESC對(duì)前面排序列做降序排序

查詢員工的編號(hào)，姓，薪資。 按照薪資的高低做降序排序

# 查詢員工的編號(hào)，姓，薪資。 按照薪資的高低做降序排序

SELECT

employee_id,last_name, salary

FROM

t_employees

ORDER BY

salary DESC

查詢員工的姓，工資，入職時(shí)間。 優(yōu)先按照工資做降序，再根據(jù)入職時(shí)間做升序

-- 查詢員工的姓，工資，入職時(shí)間。 優(yōu)先按照工資做降序，再根據(jù)入職時(shí)間做升序

-- 運(yùn)行選中的SQL的快捷鍵 ，Ctrl + Shift + R

SELECT

last_name 姓, salary 工資, hire_date 入職時(shí)間

FROM

t_employees

ORDER BY

salary DESC,

hire_date

5.4 條件查詢

語(yǔ)法：select 列明 from 表名 where 條件

關(guān)鍵字描述where在查詢結(jié)果中，篩選符合條件的查詢結(jié)果，條件為布爾表達(dá)式

5.4.1 等值判斷（=）

注意：與Java不中，Java中是==，而在MySQL中是=

查詢薪資是11000的員工信息。（查詢員工的編號(hào)，名稱，薪資）

select

employee_id,first_name ,salary

from

t_employees

where

salary = 11000;

5.4.2 不等值判斷（<、>、<=、>=、!=、<>）

其中，前四個(gè)沒(méi)啥說(shuō)的，常規(guī)的大于，小于之類的判斷。

其中 != 和 <> 都代表不等于的意思。

查詢薪資是大于11000員工信息。（查詢員工的編號(hào)，名稱，薪資）

select

employee_id,first_name ,salary

from

t_employees

WHERE

salary > 11000;

5.4.3 邏輯判斷（and，or，not）

類似Java中的 && ，|| ，!

其中 and 左右的條件都需要滿足才可以。

其中 or 左右的條件，滿足其一就可以查詢到。

其中 not 會(huì)將條件取反

查詢薪資是11000大洋，并且提成是0.3的員工信息（查詢員工的編號(hào)，名稱，薪資，提成）

select

employee_id,first_name ,salary ,commission_pct

from

t_employees

where

salary = 11000 and commission_pct = 0.3;

5.4.4 區(qū)間查詢（between 數(shù)值 and 數(shù)值）

這個(gè)between and其實(shí)就相當(dāng)于 用大于等于和小于等于的組合。

包含邊界的。

需要指定好字段值的邊界。并且需要左側(cè)的數(shù)值小，右側(cè)的數(shù)值大。不然無(wú)法查詢到結(jié)果。

查詢員工的薪資在5000~10000之間的員工信息。（查詢員工的編號(hào)，名稱，薪資）

select

employee_id,first_name ,salary

from

t_employees

where

salary between 5000 and 10000;

5.4.5 NULL 值判斷（is null、is not null）

當(dāng)對(duì)某個(gè)列判斷是否是NULL值時(shí)，不能使用 = 或者 != 之類的方式。

必須采用 字段 is null 、 字段 is not null 的方式

查詢沒(méi)有提成的員工信息（查詢員工的編號(hào)，名稱，薪資，提成）

select

employee_id,first_name ,salary,commission_pct

from

t_employees

where

commission_pct is not null;

5.4.6 字段多值判斷（in）

正常如果涉及到了某一個(gè)字段，可以為多個(gè)值的匹配條件時(shí)。

正常可能需要 字段 = xxx or 字段 = yyy or 字段 = zzz。 寫著成本太高。

可以采用in來(lái)實(shí)現(xiàn)。

字段 in (xxx,yyy,zzz)

查詢員工屬于60，70，80，90號(hào)部門的員工信息。（查詢員工的編號(hào)，名稱，薪資，部門編號(hào)）

select

employee_id,first_name ,salary,department_id

from

t_employees

where

department_id in (60,70,80,90);

5.4.7 模糊查詢（like）

模糊查詢一般是來(lái)匹配字符串的。

其中有兩個(gè)關(guān)鍵字。 % _

字段 like ‘s%’ ：查詢出這個(gè)字段中以s開(kāi)頭的數(shù)據(jù)。

字段 like ‘s_’ ：查詢出s開(kāi)頭，并且后面只有一個(gè)字符

%：代表任意長(zhǎng)度的任意字符

_：代表單個(gè)任意字符

Ps：這兩個(gè)特殊字符只能配合like使用

查詢名字以L開(kāi)頭的員工信息（查詢員工的編號(hào)，名稱，薪資）

select

employee_id,first_name ,salary

from

t_employees

where

first_name like 'L%'

查詢名字以L開(kāi)頭，但是名字長(zhǎng)度為4個(gè)字符的員工信息（查詢員工的編號(hào)，名稱，薪資）

select

employee_id,first_name ,salary

from

t_employees

where

first_name like 'L___'

5.4.8 分支結(jié)構(gòu)查詢（case when then else end）

語(yǔ)法：這里的分支結(jié)構(gòu)查詢一般是放在select后面的特殊操作。

case

when 條件1 then 結(jié)果1

when 條件2 then 結(jié)果2

when 條件3 then 結(jié)果3

………………

else 結(jié)果

end

可以認(rèn)為是case開(kāi)頭，end結(jié)尾，中間寫when then的內(nèi)容

就是類似Java中的switch

查詢員工信息，并根據(jù)薪資范圍，體現(xiàn)出員工的薪資等級(jí)

salary 0 ~ 4000 = E

salary 4000 ~ 6000 = D

salary 6000 ~ 8000 = C

salary 8000 ~ 10000 = B

salary 10000 ~ …… = A

select

employee_id,first_name ,salary ,

case

when salary >= 0 and salary < 4000 then 'E'

when salary >= 4000 and salary < 6000 then 'D'

when salary >= 6000 and salary < 8000 then 'C'

when salary >= 8000 and salary < 10000 then 'B'

else 'A'

end as salary_level

from

t_employees;

5.4.9 邏輯判斷（if、ifnull）

這里的if，和ifnull類似于Java中調(diào)用方法實(shí)現(xiàn)某個(gè)功能。

if(條件表達(dá)式,結(jié)果1,結(jié)果2)：條件表達(dá)式為true，返回結(jié)果1，否則返回結(jié)果2

ifnull(值,結(jié)果)：如果值為NULL，返回結(jié)果，如果值不為NULL，返回值本身。

查詢員工信息，如果薪資大于10000，返回小康生活，否則，返回勉強(qiáng)生存（查詢員工的編號(hào)，名稱，薪資）

select

employee_id,first_name ,salary,if(salary > 10000,'小康生活','勉強(qiáng)生存')

from

t_employees;

查詢員工號(hào)信息，如果有提成，返回提升的比例，如果沒(méi)有，返回吃底薪。（查詢員工的編號(hào)，名稱，薪資）

select

employee_id,first_name ,salary,ifnull(commission_pct,'吃底薪')

from

t_employees;

5.5 時(shí)間函數(shù)查詢

這里是針對(duì)MySQL中的時(shí)間類型提供的一些函數(shù)以及操作。

下面會(huì)玩一些關(guān)于時(shí)間函數(shù)的操作。

時(shí)間函數(shù)描述sysdate()返回當(dāng)前系統(tǒng)時(shí)間，返回年-月-日 時(shí):分:秒now()返回當(dāng)前系統(tǒng)時(shí)間，返回年-月-日 時(shí):分:秒curdate()返回當(dāng)前系統(tǒng)的年月日curtime()返回當(dāng)前系統(tǒng)的時(shí)分秒week(date)返回指定日期是今年的第幾周year(date)返回指定日期的年month(date)返回指定日期的月day(date)返回指定日期的日hour、minute、second(date)返回指定日期的時(shí)、分、秒

5.6 字符串函數(shù)查詢

針對(duì)字符串的一些函數(shù)。

函數(shù)描述concat(str1,str2,str…)將多個(gè)字符串拼接到一起……upper、lower(str)upper是將字符串切換為大寫、lower將字符串切換為小寫substring(str,num,len)將字符串從num位置開(kāi)啟截取len個(gè)內(nèi)容，num從1開(kāi)始insert(str,pos,len,newstr)將str中指定的pos位置開(kāi)始len長(zhǎng)度的內(nèi)容替換為newStrreplace(str,from_str,to_str)將str中所有的from_str，替換為to_strdate_format(date,format)將時(shí)間類型date，根據(jù)format的格式轉(zhuǎn)換為varcharstr_to_date(str,format)將字符串類varchar，根據(jù)format的格式轉(zhuǎn)換為date

format：%Y-%m-%d %H:%i:%s 表示 yyyy-MM-dd HH:mm:ss

5.7 聚合函數(shù)查詢

針對(duì)多行數(shù)據(jù)做的一個(gè)統(tǒng)計(jì)。

一般有五種需要咱們掌握

語(yǔ)法： select 聚合函數(shù)(列名) from 表名;

函數(shù)名描述sum(列名)統(tǒng)計(jì)當(dāng)前列所有數(shù)據(jù)的總和max(列名)獲取到當(dāng)前列中的最大值min(列名)獲取到當(dāng)前列中的最小值avg(列名)獲取到當(dāng)前列中所有數(shù)據(jù)的平均值count(列名)獲取當(dāng)前數(shù)據(jù)的總條數(shù)

基于上述的聚合函數(shù)，針對(duì)薪資做一個(gè)統(tǒng)計(jì)，分別統(tǒng)計(jì)薪資總和，薪資最大值，薪資最小值，薪資的平均值，當(dāng)前員工的個(gè)數(shù)。

一般都是針對(duì)數(shù)值類型的字段做具體的統(tǒng)計(jì)，幾乎不會(huì)對(duì)字符串做什么統(tǒng)計(jì)操作，count除外。

select

sum(salary) 薪資總和, max(salary) 薪資最大值, min(salary) 薪資最小值,avg(salary) 薪資平均值, count(*) 員工的個(gè)數(shù)

from

t_employees;

5.8 分組查詢

語(yǔ)法：select 列名 from 表名 where 條件 group by 列名

查詢各部門的人數(shù)（查詢部門編號(hào)，部門對(duì)應(yīng)的人數(shù)）

select

department_id 部門編號(hào), count(*) 部門人數(shù)

from

t_employees

group by department_id;

查詢各個(gè)部門的平均工資（查詢部門編號(hào)，部門的平均工資）

select

department_id 部門編號(hào), avg(salary) 平均工資

from

t_employees

group by department_id;

查詢各個(gè)部門、各個(gè)崗位的人數(shù)（部門編號(hào)，崗位信息，人數(shù)）

1、根據(jù)department_id分組。

2、根據(jù)job_id分組

3、做count統(tǒng)計(jì)查詢

select

department_id 部門編號(hào), job_id 崗位信息, count(*) 人數(shù)

from

t_employees

group by department_id,job_id;

# 查詢各個(gè)部門、各個(gè)崗位，薪資大于5000的人數(shù)（部門編號(hào)，崗位信息，人數(shù)）

select

department_id 部門編號(hào), job_id 崗位信息, count(*) 人數(shù)

from

t_employees

where

salary > 5000

group by department_id,job_id;

5.9 分組過(guò)濾查詢

如果需要在做過(guò)分組之后，再次對(duì)接過(guò)做二次篩選，需要使用having的方式編寫條件

語(yǔ)法： select 列名 from 表名 where 條件 group by 列名 having 條件 order by 列名

查詢各個(gè)部門、各個(gè)崗位的人數(shù)（部門編號(hào)，崗位信息，人數(shù)）,只查看人數(shù)大于10個(gè)的

select

department_id 部門編號(hào), job_id 崗位信息, count(*) 人數(shù)

from

t_employees

group by department_id,job_id

having 人數(shù) > 10;

查詢各個(gè)部門中，平均薪資大于10000的部門信息（部門編號(hào)，平均薪資）

select

department_id 部門編號(hào),avg(salary) avg_salary

from

t_employees

group by department_id

having avg_salary > 10000;

# 查詢各個(gè)部門中，所有員工平均薪資大于10000的部門信息（部門編號(hào)，平均薪資）,在根據(jù)平均薪資做排序

select

department_id 部門編號(hào),avg(salary) avg_salary

from

t_employees

group by department_id

having avg_salary > 10000

order by avg_salary asc;

5.10 限定查詢

比如查詢時(shí)，查詢到了上千條的數(shù)據(jù)，但是暫時(shí)只需要前5條，可以基于limit只獲取前5條數(shù)據(jù)返回。

采用limit幫助咱們實(shí)現(xiàn)。 limit是MySQL特有的一個(gè)關(guān)鍵字。

語(yǔ)法：select 列名 from 表名 where 條件 group by 列名 having 條件 order by 列名 limit 起始行,行數(shù)

起始行（offset）：你返回的數(shù)據(jù)，從第幾行開(kāi)始， 從第0行開(kāi)始。

行數(shù)（size）：一共返回幾行數(shù)據(jù)

limit 0,5：從第一行數(shù)據(jù)開(kāi)始，往下一共返回5條。

limit的第一個(gè)offset可以省略不寫，不寫代表寫的是0。

查詢表中薪資最高的前5名員工的所有信息。

薪資最高的話，需要基于salary做降序排序?；趌imit，只返回前5條數(shù)據(jù)。

select

*

from

t_employees

order by salary desc

limit 0,5

查詢表中薪資最高的6~10名員工的所有信息。

select

*

from

t_employees

order by salary desc

limit 5,5

limit很多時(shí)候可以同于分頁(yè)操作，比如需要分頁(yè)時(shí)

可以采用limit，在數(shù)據(jù)庫(kù)中查詢到不同頁(yè)數(shù)的數(shù)據(jù)，給頁(yè)面返回，讓頁(yè)面展示數(shù)據(jù)即可。

5.11 子查詢

查詢語(yǔ)句返回的結(jié)果，可以再次看成一張表去操作。

select 列名 from 表名 where 條件（子查詢操作替代一些值）

select 列名 from （子查詢的結(jié)果集） where 條件

5.11.1 子查詢作為查詢字段某個(gè)值

查詢薪資和Bruce工資一致的員工信息。

通過(guò)查詢可以得到Bruce的工資信息

再基于另一個(gè)查詢，將前面的Bruce的薪資結(jié)果作為條件判斷的一環(huán)，從而實(shí)現(xiàn)利用子查詢得到結(jié)果

1、查詢Bruce的工資信息，返回 單列單行

select salary from t_employees where first_name = ‘Bruce’;

2、基于Bruce的工資信息查詢與其薪資一致的員工信息

select

*

from

t_employees

where

salary = (select salary from t_employees where first_name = 'Bruce');

5.11.2 子查詢作為 字段多值判斷的值

因?yàn)樽侄味嘀蹬袛嗍遣捎胕n的方式去做條件篩選。 一個(gè)字段需要多個(gè)值。

在利用子查詢時(shí)，應(yīng)當(dāng)返回 單列多行 數(shù)據(jù)。

查詢last_name為King同一部門的員工信息

1、查詢last_name為King的部門信息。

select department_id from t_employees where last_name = ‘King’;

2、基于上述查詢返回的結(jié)果，查看同部門的員工信息

select

*

from

t_employees

where

department_id in (select department_id from t_employees where last_name = 'King');

5.11.3 子查詢作為 一張表操作。

可以直接基于子查詢返回的結(jié)果集做二次篩選。

查詢員工表中，工資排名前5名的員工。

這個(gè)查詢可以直接使用order by 排序，然后基于limit做篩選。

就為了使用，搞兩個(gè)SQL實(shí)現(xiàn)，利用子查詢來(lái)玩。

1、查詢員工的信息，基于salary做降序排序。

select * from t_employees order by salary desc;

2、將上述的結(jié)果基于limit篩選出前5條。

select

*

from

(select * from t_employees order by salary desc) as temp

limit 5

5.11.4 子查詢的ALL、ANY（了解）

之前在給字段做=判斷時(shí)，子查詢必須返回單列單行的數(shù)據(jù)，不然報(bào)錯(cuò)。

其實(shí)在做=判斷時(shí)，即便返回了單列多行數(shù)據(jù)，也可以采用ALL、ANY關(guān)鍵字解決問(wèn)題。

查詢工資高于60部門員工的，所有人的信息。

select salary from t_employees where department_id = 60;

1、查詢比60部門所有員工薪資都高的員工信息。

select

*

from

t_employees

where

salary > ALL (select salary from t_employees where department_id = 60);

2、查詢比60部門任意一名員工薪資高的員工信息。

select

*

from

t_employees

where

salary > ANY (select salary from t_employees where department_id = 60);

5.12 合并查詢（了解）

將兩個(gè)select操作的結(jié)果整合為一個(gè)結(jié)果集返回。一般是為了規(guī)避掉一些查詢方式導(dǎo)致的效率較低，可以采用合并查詢來(lái)提升效率。

語(yǔ)法：select 列名 from 表名1 union select 列名 from 表名2; （會(huì)去重）

語(yǔ)法：select 列名 from 表名1 union all select 列名 from 表名2; （不會(huì)去重）

在使用union 或者 union all時(shí)，需要保證兩個(gè)select的列的個(gè)數(shù)是一致的。

哪怕兩次Select的列不是一個(gè)東西，但是個(gè)數(shù)一致，那就基于第一個(gè)select的列，作為返回的基準(zhǔn)，將結(jié)果合并到一起。

5.13 表連接查詢

表連接可以將多張表關(guān)聯(lián)到一起，返回需要的內(nèi)容

語(yǔ)法：select 列名 from 表1 連接方式 表2 on 連接條件 連接方式 表3 on 連接條件 …………

內(nèi)連接語(yǔ)法： select 列名 from 表1 inner join 表2 on 連接條件

外連接：

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

內(nèi)連接查詢操作。

內(nèi)連接查詢，針對(duì)哪些 連接條件 無(wú)法滿足的數(shù)據(jù)，會(huì)直接篩選掉。

查詢當(dāng)前員工名稱以及對(duì)應(yīng)的部門名稱。

# 顯示內(nèi)連接

select

e.first_name , e.last_name , d.department_name

from

t_employees as e

inner join t_departments as d on e.department_id = d.department_id;

# 隱示內(nèi)連接

select

e.first_name , e.last_name , d.department_name

from

t_employees e, t_departments d

where

e.department_id = d.department_id;

查詢員工的名稱，部門的名稱，部門所在國(guó)家的信息（三張表聯(lián)查）

# 查詢員工的名稱，部門的名稱，部門所在國(guó)家的信息（三張表聯(lián)查)

# 顯示內(nèi)連接

select

e.first_name , e.last_name ,d.department_name,l.city

from

t_employees e

inner join t_departments d on e.department_id = d.department_id

inner join t_locations l on l.location_id = d.location_id;

# 隱示內(nèi)連接

select

e.first_name , e.last_name ,d.department_name,l.city

from

t_employees e , t_departments d ,t_locations l

where

e.department_id = d.department_id

and d.location_id = l.location_id;

外連接查詢

還是基于前面玩的查詢，查詢當(dāng)前員工名稱以及對(duì)應(yīng)的部門名稱。

因?yàn)橹坝玫膬?nèi)連接的方式，導(dǎo)致一個(gè)員工

發(fā)現(xiàn)，最后的部門Id是一個(gè)NULL，導(dǎo)致后續(xù)查詢時(shí)，并沒(méi)有這個(gè)員工的信息返回。

現(xiàn)在可以采用外鏈接的方式，來(lái)解決這個(gè)問(wèn)題。

外鏈接前面說(shuō)過(guò)左外的語(yǔ)法，和右外的語(yǔ)法。

其實(shí)就是將左邊的表，或者是右邊的表作為基準(zhǔn)表，基準(zhǔn)表回返回全部的數(shù)據(jù)，無(wú)論連接條件是否滿足。

# 查詢當(dāng)前 所有 員工名稱以及對(duì)應(yīng)的部門名稱。

select

e.first_name , e.last_name ,d.department_name

from

t_employees e left outer join t_departments d

on e.department_id = d.department_id;

注意，左外和右外是指定哪張表是基準(zhǔn)表，基準(zhǔn)表的數(shù)據(jù)會(huì)全部返回，匹配不到內(nèi)容的位置，會(huì)用NULL填充上。

5.14 常見(jiàn)錯(cuò)誤

六、DML操作（重要）

關(guān)于DML，只需要保證可以完成三個(gè)操作即可。

6.1 新增（insert）

語(yǔ)法1：insert into 表名 (列1,列2,列3,列4…) values (值1,值2,值3,值4…);

語(yǔ)法2：insert into 表名 values (值1,值2,值3,值4…);

語(yǔ)法3：insert into 表名 (列1,列2,列3,列4…) values (值1,值2,值3,值4…),(值1,值2,值3,值4…)……;（批量添加）

MySQL中最長(zhǎng)見(jiàn)的三個(gè)數(shù)據(jù)類型，數(shù)值，字符串，時(shí)間。

給t_jobs表添加一條數(shù)據(jù)。 記得確保前面給的列數(shù)，和后面的值的個(gè)數(shù)需要保持一致。

# 給t_jobs表添加一條數(shù)據(jù)

insert into

t_jobs

(job_id,job_title,min_salary,max_salary)

values

('運(yùn)營(yíng)','運(yùn)營(yíng)',500,50000);

給t_employees表添加一條數(shù)據(jù)，采用語(yǔ)法2的方式。 需要確保表中的所有字段的值都要給上。

針對(duì)時(shí)間類型數(shù)據(jù)的添加，在MySQL端可以直接輸入字符串的方式，MySQL可以幫助咱們做一些數(shù)據(jù)類型的轉(zhuǎn)換。

# 給t_employees表添加一條數(shù)據(jù)，采用語(yǔ)法2的方式

insert into

t_employees

values

(100001,'張','三','123@123.com','18888888888','1999-11-11','程序員',5000,0.20,100,90);

給t_jobs表中基于一條SQL語(yǔ)句追加多行數(shù)據(jù)。而且可以看到受影響行數(shù)的信息。

insert into

t_jobs

(job_id,job_title,min_salary,max_salary)

values

('A','做A',5000,8000),

('B','做B',5000,8000),

('C','做C',5000,8000);

6.2 修改（update）

語(yǔ)法：update 表名 set 列1 = 新值1,列2 = 新值2 …… [where 條件];

正常修改語(yǔ)句從語(yǔ)法的角度講，可以不追加where條件，但是咱們?cè)賹?shí)際操作時(shí)， 一定要追加上條件，不然整張表都要被修改成一模一樣的數(shù)據(jù)。

修改job_id標(biāo)識(shí)為A的數(shù)據(jù)，將薪資最小和最大調(diào)整為3000，5000

update

t_jobs

set

min_salary = 3000,

max_salary = 5000

where

job_id = 'A';

如果返回的受影響行數(shù)為1，說(shuō)明當(dāng)前標(biāo)識(shí)為A的這行數(shù)據(jù)發(fā)生了變化。

如果返回的受影響行數(shù)為0，不代表SQL執(zhí)行失敗，只是單純的這行數(shù)據(jù)沒(méi)變化。

6.3 刪除（delete）

語(yǔ)法：delete from 表名 [where 條件];

從刪除語(yǔ)句的語(yǔ)法來(lái)說(shuō)，where條件可以不寫，但是如果不寫，就相當(dāng)于清空了整張表。正常業(yè)務(wù)情況下是需要追加where。

將之前添加進(jìn)去的測(cè)試數(shù)據(jù)，全部都刪除

我這里是針對(duì)t_jobs和t_employees表追加了數(shù)據(jù)，現(xiàn)在都干掉~(yú)

# 刪除t_jobs表中的，標(biāo)識(shí)為A，B，C，程序員，運(yùn)營(yíng)這5條數(shù)據(jù)

delete from

t_jobs

where

job_id in ('A','B','C','程序員','運(yùn)營(yíng)');

# 刪除t_employees表中標(biāo)識(shí)為100001，100002，100003，100004的員工信息干掉

delete from

t_employees

where

employee_id in (100001,100002,100003,'100004');

6.4 常見(jiàn)問(wèn)題

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

7.1 MySQL中的數(shù)據(jù)類型

7.1.1 數(shù)值類型

跟Java中的幾乎是一致的，但是比Java會(huì)更豐富一些，不過(guò)大類別依然是整形和浮點(diǎn)型

浮點(diǎn)型的數(shù)值中，所有的M，D，都必須保證M >= D

類型大小范圍描述tinyint1byte（-128~127）小整數(shù)，映射Java中的Bytesmallint2byte…………小整數(shù)，映射Java中的Shortint4byte…………默認(rèn)整數(shù)，映射Java中的Integerbigint8byte…………長(zhǎng)整數(shù)，映射Java中的Longfloat(10,2)4byte…………單精度浮點(diǎn)類型，映射Java中的Floatdouble(10,2)8byte…………雙精度浮點(diǎn)類型，映射Java中的Doubledecimal(M,D)看下面~…………映射Java中的BigDecimal

decimal存儲(chǔ)的空間大?。?/p>

每9位數(shù)最多占用4字節(jié)，整數(shù)和小數(shù)要分開(kāi)算，如果小于9位數(shù)，基于下述表格計(jì)算

位數(shù)大小001–213–425–637–94

如果指定的 DECIMAL(18,9)，這個(gè)數(shù)值是小數(shù)點(diǎn)前，占用4字節(jié)，小數(shù)點(diǎn)后，也占用4字節(jié)。一共占用8字節(jié)。

如果指定的 DECIMAL(20,6)，這個(gè)數(shù)值是小數(shù)點(diǎn)前有14位，9位占用4子節(jié)，剩下5位占用3字節(jié)。小數(shù)點(diǎn)后的6位，占用3字節(jié)。一共占用10字節(jié)。

7.1.2 時(shí)間類型

MySQL中的時(shí)間類型比Java中要豐富一些。Java中有Date，LocalDate之類的，MySQL相對(duì)更多一些。

類型大小范圍格式描述date3byte用到死！yyyy-MM-dd存儲(chǔ)日期time3byte夠用！HH:mm:ss存儲(chǔ)時(shí)間datetime8byte用到死！yyyy-MM-dd HH:mm:ss存儲(chǔ)日期和時(shí)間timestamp4byte1970-01-01 00:00:00~2038-01-19 03:14:07yyyy-MM-dd HH:mm:ss存儲(chǔ)日期和時(shí)間

datetime和timestamp的區(qū)別：

存儲(chǔ)的大小不一樣。

datetime占用8字節(jié)。timestamp占用4字節(jié)。 存儲(chǔ)的范圍不一樣。

datetime可以持續(xù)到9999年。timestamp是從1970年~2038年。 存儲(chǔ)的時(shí)區(qū)問(wèn)題。

datetime存儲(chǔ)時(shí)，不考慮時(shí)區(qū)，存儲(chǔ)什么就是什么，取的時(shí)候也就是什么。timestamp存儲(chǔ)時(shí)，會(huì)將時(shí)間從當(dāng)前時(shí)區(qū)轉(zhuǎn)換為UTC進(jìn)行存儲(chǔ)，查詢時(shí)，將其再轉(zhuǎn)換為當(dāng)前客戶端的時(shí)區(qū)。

其次，在5.6.5版本后，datetime和timestamp對(duì)于行級(jí)數(shù)據(jù)變化，自動(dòng)更新為當(dāng)前系統(tǒng)時(shí)間，都是支持的。（后面創(chuàng)建表會(huì)演示這個(gè)操作）

7.1.3 字符串類型

MySQL中的字符串類型那就多了，Java中就String。

類型描述char(長(zhǎng)度)定長(zhǎng)字符串varchar(長(zhǎng)度)變長(zhǎng)字符串text大文本類型blob以二進(jìn)制的形式存儲(chǔ)大文本數(shù)據(jù)

char和varchar核心區(qū)別：

char指定好長(zhǎng)度后，無(wú)論存儲(chǔ)多少數(shù)據(jù)，就占這個(gè)長(zhǎng)度的大小。varchar指定好長(zhǎng)度后，你寫入了多大的數(shù)據(jù)，他就占用多少大小。

7.2 創(chuàng)建表

創(chuàng)建表的語(yǔ)法：

create table 表名(

列名 數(shù)據(jù)類型 [約束],

列名 數(shù)據(jù)類型 [約束],

…………

列名 數(shù)據(jù)類型 [約束] -- 最后一行不要在后面追加逗號(hào)

);

創(chuàng)建一張學(xué)生表，student。表名和列名如果存在多個(gè)單詞，用_分開(kāi)，不要出現(xiàn)大寫字母。

列名數(shù)據(jù)類型約束描述student_idbigint無(wú)學(xué)員編碼student_namevarchar(32)無(wú)學(xué)員名稱student_genderchar(1)無(wú)學(xué)員性別student_birthdaydate無(wú)學(xué)員生日

創(chuàng)建學(xué)生表的SQL語(yǔ)句

create table student(

student_id bigint comment '學(xué)員編號(hào)',

student_name varchar(32) comment '學(xué)員名稱',

student_gender char(1) comment '學(xué)員性別',

student_birthday date comment '學(xué)員生日'

)comment '學(xué)生表';

Ps：如果構(gòu)建完之后，通過(guò)Navicat查看注釋信息時(shí)，發(fā)現(xiàn)有亂碼，需要設(shè)置系統(tǒng)的一些內(nèi)容，如果沒(méi)亂碼，你不需要做任何事情?。。?！

1、打開(kāi)cmd，輸入chcp，如果是936，那就開(kāi)始設(shè)置當(dāng)前操作系統(tǒng)的語(yǔ)言

2、更改系統(tǒng)區(qū)域設(shè)置

3、修改完畢后，需要重啟電腦才可以生效?。?可以在重啟后，再次打開(kāi)cmd，查看chcp的返回內(nèi)容

7.3 表的其他操作（了解）

語(yǔ)法：alter table 表名 操作

7.3.1 添加一個(gè)列

語(yǔ)法：alter table 表名 add 列名 類型;

給student表追加一個(gè)student_phone的列。類型是varchar(16）

# 添加一列

alter table

student

add

student_phone varchar(16);

7.3.2 修改表中的列

語(yǔ)法：alter table 表名 modify 列名 類型;

將student表中的student_phone的列中的varchar長(zhǎng)度設(shè)置為11。

# 修改一列

alter table

student

modify

student_phone varchar(11) comment '學(xué)生手機(jī)號(hào)';

7.3.3 刪除表中的列

語(yǔ)法：alter table 表名 drop 列名;

將前面用navicat隨便添加的列刪掉。刪除ssss這個(gè)列。

# 刪除一列

alter table

student

drop

ssss;

7.3.4 修改表名

語(yǔ)法：alter table 表名 rename 新表名;

將student表修改為table_student

# 修改表名

alter table

student

rename

table_student;

7.3.5 修改列名和類型

語(yǔ)法：alter table 表名 change 舊列名 新列明 類型;

將table_student表中的student_gender修改為student_sex，并且類型指定為tinyint

# 修改列的名稱和類型

alter table

table_student

change

student_gender

student_sex tinyint;

7.3.6 清空表數(shù)據(jù)

這個(gè)操作的語(yǔ)法跟前面完全不同。單獨(dú)的語(yǔ)法。

這個(gè)清空表不是單獨(dú)的刪除數(shù)據(jù)，而是直接將表摧毀掉，再重新創(chuàng)建一張一模一樣的表。

語(yǔ)法：truncate 表名;

直接將table_student中的數(shù)據(jù)直接干掉。

# 摧毀表,重新創(chuàng)建

truncate table_student;

7.3.7 刪除表

這個(gè)跟前面的操作也不一樣。

語(yǔ)法：drop table 表名;

刪除table_student表。

drop table table_student;

7.4 常見(jiàn)問(wèn)題

八、約束

問(wèn)題：往一張表中插入完全一致的數(shù)據(jù)，可行不。

從功能的維度來(lái)說(shuō)，完全沒(méi)有問(wèn)題。但是從業(yè)務(wù)方向來(lái)考慮，兩個(gè)完全一模一樣的數(shù)據(jù)沒(méi)有什么意義。

從行數(shù)據(jù)的維度來(lái)考慮，需要保證 實(shí)體完整性約束 。需要確保每行數(shù)據(jù)不重復(fù)，數(shù)據(jù)唯一。

8.1 主鍵約束（重要）

primary key 主鍵的約束，標(biāo)識(shí)表中的一行數(shù)據(jù)，當(dāng)前指定的主鍵列的 值不可以重復(fù)，并且不能為NULL

主鍵在表中只能值有一個(gè)，指定多個(gè)主鍵，就會(huì)報(bào)錯(cuò)。

create table student(

student_id bigint primary key comment '學(xué)員編號(hào)' ,

student_name varchar(32) comment '學(xué)員名稱',

student_gender char(1) comment '學(xué)員性別',

student_birthday date comment '學(xué)員生日'

)comment '學(xué)生表';

insert into

student

values

(1,'李四','男','2001-11-11');

Ps：一般咱們推薦給表添加一個(gè)主鍵約束，一般情況下，可以針對(duì)有意義數(shù)據(jù)設(shè)置主鍵約束，也可以單獨(dú)搞一個(gè)列來(lái)作為主鍵（推薦是有序自增的最好）

這里推薦主鍵最好是自增的，這樣后期在做一些范圍查詢的時(shí)候效率會(huì)更好。

現(xiàn)在student_id是主鍵，每次咱們需要自己指定1，2，3，4，5，6，7…………很麻煩。

可以給主鍵設(shè)置一個(gè)自動(dòng)增長(zhǎng)的效果。

auto_increment 放在約束后面即可，就配合主鍵使用，其他列不用。

create table student(

student_id bigint primary key auto_increment comment '學(xué)員編號(hào)' ,

student_name varchar(32) comment '學(xué)員名稱',

student_gender char(1) comment '學(xué)員性別',

student_birthday date comment '學(xué)員生日',

student_phone varchar(11) unique comment '學(xué)員手機(jī)號(hào)'

)comment '學(xué)生表';

insert into

student

values

(NULL,'李四','男','2001-11-12','18888888855');

8.2 唯一約束

unique 唯一約束。標(biāo)識(shí)表中的一行數(shù)據(jù)，當(dāng)前指定的唯一約束的列的 值不允許重復(fù)，可以為NULL

唯一約束可以在一張表中指定多個(gè)列。

允許為NULL，并且，多個(gè)NULL可以同時(shí)存在。

create table student(

student_id bigint primary key comment '學(xué)員編號(hào)' ,

student_name varchar(32) comment '學(xué)員名稱',

student_gender char(1) comment '學(xué)員性別',

student_birthday date comment '學(xué)員生日',

student_phone varchar(11) unique comment '學(xué)員手機(jī)號(hào)'

)comment '學(xué)生表';

insert into

student

values

(1,'李四','男','2001-11-12','18888888888');

域完整性約束：限制列的數(shù)據(jù)完整性。

8.3 非空約束

not null ，在列的后面指定上這個(gè)約束即可。

給學(xué)生生日指定為非空！

create table student(

student_id bigint primary key auto_increment comment '學(xué)員編號(hào)' ,

student_name varchar(32) comment '學(xué)員名稱',

student_gender char(1) comment '學(xué)員性別',

student_birthday date not null comment '學(xué)員生日',

student_phone varchar(11) unique comment '學(xué)員手機(jī)號(hào)'

)comment '學(xué)生表';

insert into

student

values

(NULL,'王五','男',NULL,'18888888434');

8.4 默認(rèn)值約束

default 默認(rèn)值 ，當(dāng)前列如果沒(méi)有指定任何的值，會(huì)采用這個(gè)默認(rèn)值填充。

給學(xué)員名稱列設(shè)置默認(rèn)值，如果沒(méi)指定姓名就叫 ‘張三’。

測(cè)試默認(rèn)值時(shí)，需要采用下述方式使用insert語(yǔ)句，跳過(guò)student_name字段的賦值。

create table student(

student_id bigint primary key auto_increment comment '學(xué)員編號(hào)' ,

student_name varchar(32) default '張三' comment '學(xué)員名稱',

student_gender char(1) comment '學(xué)員性別',

student_birthday date not null comment '學(xué)員生日',

student_phone varchar(11) unique comment '學(xué)員手機(jī)號(hào)'

)comment '學(xué)生表';

drop table student;

insert into

student

(student_gender,student_birthday,student_phone)

values

('男','2011-11-11','18888888433');

8.5 檢查約束

check (檢查約束要求) ，這個(gè)是MySQL8.x提供的約束功能，約束某一個(gè)列的值滿足一定的條件要求

給student表中的性別字段，指定一個(gè)檢查約束，要求添加的值只能是男、女。不允許添加其他內(nèi)容。

-- 是單獨(dú)指定約束信息

create table student(

student_id bigint primary key auto_increment comment '學(xué)員編號(hào)' ,

student_name varchar(32) default '張三' comment '學(xué)員名稱',

student_gender char(1) comment '學(xué)員性別',

student_birthday date not null comment '學(xué)員生日',

student_phone varchar(11) unique comment '學(xué)員手機(jī)號(hào)',

constraint student_gender_check check (student_gender = '男' or student_gender = '女')

)comment '學(xué)生表';

-- 在字段后直接編寫。

create table student(

student_id bigint primary key auto_increment comment '學(xué)員編號(hào)' ,

student_name varchar(32) default '張三' comment '學(xué)員名稱',

student_gender char(1) check (student_gender = '男' or student_gender = '女') comment '學(xué)員性別',

student_birthday date not null comment '學(xué)員生日',

student_phone varchar(11) unique comment '學(xué)員手機(jī)號(hào)'

)comment '學(xué)生表';

-- 測(cè)試

insert into

student

(student_gender,student_birthday,student_phone)

values

('彎','2011-11-11','18888888435');

8.6 數(shù)值的約束

UNSIGNED，數(shù)值的約束，保證這個(gè)數(shù)值必須是正數(shù)，不允許存儲(chǔ)負(fù)數(shù)。

同時(shí)取值范圍還可以增加接近一倍。

比如tinyint，存儲(chǔ)范圍是-128到127，如果追加上了UNSIGNED，就可以標(biāo)識(shí)0到255。

# 添加unsigned約束后，數(shù)值就不允許存儲(chǔ)負(fù)數(shù)了。

create table yyy(

id tinyint unsigned

);

insert into yyy values (-1);

zerofill，根據(jù)指定的數(shù)值長(zhǎng)度，如果當(dāng)前數(shù)值的長(zhǎng)度不滿足指定的長(zhǎng)度，會(huì)自動(dòng)在前面填充0。

可以在數(shù)值類型后面追加這個(gè)約束。

8.7 常見(jiàn)錯(cuò)誤

九、事務(wù)（重要）

9.1 轉(zhuǎn)賬操作

生活當(dāng)眾，轉(zhuǎn)賬是從一方扣錢，另一方加錢，采用數(shù)據(jù)庫(kù)來(lái)模擬一下這個(gè)操作。

優(yōu)先準(zhǔn)備好轉(zhuǎn)賬的環(huán)境。

# 創(chuàng)建一個(gè)賬戶表

create table account(

id bigint primary key auto_increment comment '賬戶標(biāo)識(shí)',

name varchar(16) not null comment '賬戶名稱',

money bigint not null comment '賬戶余額'

)comment '賬戶表';

# 添加兩個(gè)賬戶信息，張三、李四，一人1000大洋

insert into

account

(name,money)

values

('張三',1000),('李四',1000);

# 模擬張三給李四轉(zhuǎn)500大洋

update account set money = money - 500 where id = 1;

update account set money = money + 500 where id = 2;

轉(zhuǎn)賬分為兩個(gè)操作，需要先給一個(gè)用戶扣錢，再給一個(gè)用戶加錢。

第一個(gè)扣錢的操作成功了，但是因?yàn)橐恍┢渌脑颍热绶?wù)器斷點(diǎn)，或者是一些異常情況，導(dǎo)致第二個(gè)加錢的SQL執(zhí)行失敗。整個(gè)轉(zhuǎn)賬的業(yè)務(wù)其實(shí)是失敗的，但是第一個(gè)的錢扣了，沒(méi)了。。。。。

正常應(yīng)當(dāng)是，這 兩個(gè)操作要么都執(zhí)行成功，要么都執(zhí)行失敗。

上述的轉(zhuǎn)賬問(wèn)題，咱們就可以基于事務(wù)來(lái)解決。

事務(wù)可以看做是一個(gè)最小的執(zhí)行單位，一個(gè)事務(wù)可以由一條或者多條SQL語(yǔ)句組成。 一個(gè)事務(wù)操作，所有的SQL語(yǔ)句要么都執(zhí)行成功，要么都執(zhí)行失敗。

9.2 事務(wù)操作

首先MySQL他模式就是開(kāi)啟事務(wù)的，但是這個(gè)事務(wù)每執(zhí)行一個(gè)DML語(yǔ)句，都會(huì)自動(dòng)的提交結(jié)束。

通過(guò)幾個(gè)操作來(lái)實(shí)現(xiàn)自己對(duì)事務(wù)的控制。

開(kāi)啟事務(wù)：

1、因?yàn)镸ySQL默認(rèn)自動(dòng)結(jié)束事務(wù)的，咱們可以自己關(guān)閉這種自動(dòng)結(jié)束的操作。

2、也可以通過(guò)單獨(dú)的指令，來(lái)指定事務(wù)開(kāi)始和結(jié)束的位置。

set AutoCommit = 0; -- 默認(rèn)情況，值是1，代表每次自動(dòng)結(jié)束事務(wù)，設(shè)置為0，代表需要咱們手動(dòng)的結(jié)束事務(wù)。

begin; -- 開(kāi)啟手動(dòng)結(jié)束事務(wù)操作，需要在執(zhí)行完SQL語(yǔ)句后，自己去執(zhí)行結(jié)束事務(wù)的指令

start transaction; -- 跟begin是一樣的。。。。

結(jié)束事務(wù)：

1、如果開(kāi)啟事務(wù)的所有SQL語(yǔ)句，執(zhí)行沒(méi)有問(wèn)題，可以直接提交事務(wù)。

2、如果開(kāi)啟事務(wù)后執(zhí)行的SQL語(yǔ)句存在問(wèn)題，可以執(zhí)行回滾事務(wù)操作。

commit; -- 提交事務(wù)，事務(wù)中的操作都會(huì)落到磁盤當(dāng)眾

rollback; -- 回滾事務(wù)，事務(wù)中的操作全部都會(huì)恢復(fù)到事務(wù)開(kāi)啟之前的狀態(tài)。

將前面的轉(zhuǎn)賬操作，基于事務(wù)控制，解決前面的問(wèn)題。

# 模擬張三給李四轉(zhuǎn)500大洋

set AutoCommit = 1; -- 關(guān)閉自動(dòng)結(jié)束事務(wù)，需要遇到commit或者rollback才會(huì)結(jié)束這個(gè)事務(wù)

begin; -- 手動(dòng)開(kāi)啟了一個(gè)事務(wù)，需要遇到commit或者rollback才會(huì)結(jié)束這個(gè)事務(wù)。

start transaction; -- 也是手動(dòng)開(kāi)啟一個(gè)事務(wù)。

update account set money = money - 500 where id = 1;

update account set money = money + 500 where id = 2;

commit;

rollback;

-- 事務(wù)結(jié)束有兩種方式。commit，提交。 rollback，回滾。

9.3 事務(wù)的基本實(shí)現(xiàn)原理

9.4 事務(wù)的特性

原子性（Atomicity） 事務(wù)是一個(gè)最小的執(zhí)行單位，一次事務(wù)的操作要么都成功，要么都失敗。一致性（Consistency） 表示一個(gè)事務(wù)內(nèi)有一個(gè)操作失敗時(shí)，所有的更改過(guò)的數(shù)據(jù)都必須回滾到修改前的狀態(tài)。隔離性（Isolation） 事務(wù)查看數(shù)據(jù)操作時(shí)數(shù)據(jù)所處的狀態(tài)，要么是另一個(gè)并發(fā)事務(wù)修改之前的狀態(tài)，要么是另一個(gè)事務(wù)修改它之后的狀態(tài)，事務(wù)不會(huì)查看中間狀態(tài)的數(shù)據(jù)。持久性（Durablility） 事務(wù)正常提交后，會(huì)將數(shù)據(jù)落到磁盤中，影響是永久了。

9.5 事務(wù)并發(fā)問(wèn)題

臟讀：一個(gè)事務(wù)中，讀取到了另一個(gè)事務(wù)未提交的數(shù)據(jù)。（這個(gè)問(wèn)題必須要解決）不可重復(fù)讀：一個(gè)事務(wù)中，多次查詢同一個(gè)數(shù)據(jù)，結(jié)果不一致。原因是其他事務(wù)中對(duì)這個(gè)數(shù)據(jù)修改了，并且提交事務(wù)了?；米x：一個(gè)事務(wù)中，多次查詢同一個(gè)數(shù)據(jù)，結(jié)果不一致。原因是其他事務(wù)中對(duì)某個(gè)數(shù)據(jù)進(jìn)行了增刪，并且提交事務(wù)了。

為了復(fù)現(xiàn)上述的問(wèn)題，優(yōu)先將MySQL的事務(wù)隔離級(jí)別調(diào)低

-- 為了查看到所有問(wèn)題的效果，這里優(yōu)先將事務(wù)的隔離級(jí)別設(shè)置為最低等級(jí)。READ-UNCOMMITTED。

set global transaction_isolation = 'READ-UNCOMMITTED';

-- 因?yàn)樵O(shè)置的是全局的事務(wù)隔離級(jí)別，設(shè)置完畢后，關(guān)閉連接，重新打開(kāi)，才會(huì)生效。

select @@transaction_isolation;

復(fù)現(xiàn)臟讀效果：

復(fù)現(xiàn)不可重復(fù)讀效果：

復(fù)現(xiàn)幻讀的效果：

9.6 事務(wù)的隔離級(jí)別

事務(wù)的隔離級(jí)別就是用來(lái)解決前面9.5聊到的事務(wù)并發(fā)的三個(gè)問(wèn)題的。

READ-UNCOMMITTED（讀未提交）：可以讀取到未提交事務(wù)的數(shù)據(jù)。

（一個(gè)問(wèn)題都不能解決） READ-COMMITTED（讀已提交）：可以讀取到已經(jīng)提交事務(wù)的數(shù)據(jù)。

（只能解決臟讀）Oracle默認(rèn)隔離級(jí)別是READ-COMMITTED REPEATABLE-READ（可重復(fù)讀）：會(huì)讓一次事務(wù)多次查詢同一數(shù)據(jù)結(jié)果一致（修改導(dǎo)致）。

（可以解決臟讀和不可重讀）MySQL默認(rèn)隔離級(jí)別是可重復(fù)讀REPEATABLE-READ SERIALIZABLE（串行化）：上鎖，所有問(wèn)題都能解決。

（可以解決所有問(wèn)題）

為了解決上面說(shuō)道的各種問(wèn)題，這里可以設(shè)置事務(wù)的隔離級(jí)別，然后查看效果

查看事務(wù)的隔離級(jí)別的方式：

select @@transaction_isolation;

設(shè)置事務(wù)的隔離級(jí)別，可以設(shè)置全局的，也可以針對(duì)當(dāng)前連接設(shè)置。

-- 全局的事務(wù)隔離級(jí)別設(shè)置。（設(shè)置完，需要關(guān)閉連接，重新打開(kāi)）

set global transaction_isolation = 'SERIALIZABLE';

-- 當(dāng)前會(huì)話的事務(wù)隔離級(jí)別設(shè)置。

set session transaction_isolation = 'SERIALIZABLE';

十、權(quán)限控制DCL操作（了解）

DCL就是Data Control Language，一般就是對(duì)于用戶的權(quán)限做一些授權(quán)操作之類的內(nèi)容。 直接構(gòu)建用戶，基于用戶操作對(duì)應(yīng)庫(kù)表的權(quán)限。

10.1 用戶的操作

創(chuàng)建用戶： create user 用戶名@IP地址 identified by 密碼;

這里的IP地址，是指定Host列，也就是當(dāng)前用戶可以基于哪個(gè)IP地址連接當(dāng)前MySQL

創(chuàng)建一個(gè)用戶，用戶名是zheng，密碼是zheng，采用默認(rèn)的IP，%。

#創(chuàng)建一個(gè)用戶，用戶名是zheng，密碼是zheng。

create user 'zheng' identified by 'zheng';

create user 'zhang'@'%' identified by 'zhang';

刪除用戶：drop user 用戶名;

刪除上面的用戶zhang。

# 刪除上面的用戶zhang。

drop user 'zhang';

Ps：創(chuàng)建完畢的用戶，密碼插件默認(rèn)是caching_sha2_password，正常黑窗口是可以連接的。我提供的Navicat版本比較低，所以無(wú)法連接，需要將密碼插件修改為mysql_native_password。

10.2 授權(quán)

給用戶賦予操作指定庫(kù)和表的權(quán)限。

語(yǔ)法：grant all on 庫(kù)名.表名 to 用戶名;

all代表賦予這個(gè)庫(kù)和表的所有操作權(quán)限。

庫(kù)名和表名如果想賦予全部的，使用*代替。

賦予zheng用戶，可以操作test庫(kù)下所有表的所有操作權(quán)限。

# 賦予zheng用戶，可以操作test庫(kù)下所有表的所有操作權(quán)限。

grant all on test.* to 'zheng';

發(fā)現(xiàn)賦予權(quán)限后，依然無(wú)法連接，原因是zheng用戶默認(rèn)的密碼插件是caching_sha2_password。咱們的Navicat版本低，無(wú)法識(shí)別，通過(guò)alter語(yǔ)句修改一下插件信息

alter user 'zheng'@'%' identified with mysql_native_password by 'zheng';

正常連接后，可以看到具體的賦予權(quán)限的庫(kù)表信息。

10.3 撤銷授權(quán)

將前面賦予的權(quán)限撤銷掉。

語(yǔ)法：revoke all on 庫(kù)名.表名 from 用戶名;

撤銷掉對(duì)zheng用戶賦予的test庫(kù)中所有表的所有權(quán)限

# 撤銷掉對(duì)zheng用戶賦予的test庫(kù)中所有表的所有權(quán)限

revoke all on test.* from 'zheng';

十一、視圖（了解）

11.1 視圖介紹

視圖（View），是一張?zhí)摂M表，在本地磁盤是沒(méi)有存儲(chǔ)的。

視圖是從一張表或者多張表中查詢出來(lái)的結(jié)果，作用和真實(shí)的表是一樣的，包含一系列帶有行和列的數(shù)據(jù)。

在視圖中，用戶可以通過(guò)select語(yǔ)句查詢視圖里的數(shù)據(jù)，也可以基于insert，update，delete按修改視圖的數(shù)據(jù)，但是修改視圖的本質(zhì)就是在修改原始表。一般不允許做修改視圖的操作。

視圖的核心作用是可以將一些非常復(fù)雜的查詢邏輯封裝到一個(gè)視圖里，同時(shí)也可以將一些敏感數(shù)據(jù)規(guī)避。

視圖的本質(zhì)，就是一個(gè)SQL。

11.2 視圖語(yǔ)法

構(gòu)建視圖語(yǔ)法：create view 視圖名 as (查詢語(yǔ)句);

視圖的構(gòu)建很簡(jiǎn)單，只要有對(duì)應(yīng)的查詢語(yǔ)句即可。

但是記住，視圖無(wú)法提升你的查詢效率，視圖的本質(zhì)就是一個(gè)查詢語(yǔ)句。

視圖的出現(xiàn)是為了剛方便咱們的操作。。

11.3 視圖構(gòu)建

將之前玩的employee表中薪資大于5000的信息數(shù)據(jù)封裝為一個(gè)視圖

薪資大于5000，就是where salary > 5000。

查詢SQL搞定，直接封裝視圖即可。

# 將之前玩的employee表中薪資大于5000的信息數(shù)據(jù)封裝為一個(gè)視圖

select * from t_employees where salary > 5000;

# 構(gòu)建為視圖

create view v_emp_salary_gt_fivethousand as (select * from t_employees where salary > 5000);

11.4 視圖使用

視圖的使用和正常操作表是一樣的。

前面構(gòu)建好的v_emp_salary_gt_fivethousand就可以直接查詢

# 查詢視圖

select first_name,last_name from v_emp_salary_gt_fivethousand;

視圖是可以修改的，但是修改的不是視圖，修改的是視圖映射的原表數(shù)據(jù)。

Ps：能改，但是別改。記住，視圖就是用來(lái)查詢的，別用于寫操作。

11.5 刪除視圖

刪除視圖跟刪除表一樣。

語(yǔ)法：drop view 視圖名;

# 刪除視圖

drop view v_emp_salary_gt_fivethousand;

十二、同義詞（了解）

MySQL不支持同義詞synonym這個(gè)關(guān)鍵字。

Oracle中支持synonym的構(gòu)建。

在Oracle中是給對(duì)應(yīng)的表，視圖，存儲(chǔ)過(guò)程起個(gè)別名，訪問(wèn)起來(lái)更方便一些。

因?yàn)樵贛ySQL8.0中，提供了一個(gè)構(gòu)建同義詞數(shù)據(jù)庫(kù)的存儲(chǔ)函數(shù)。

將一些名字比較惡心的數(shù)據(jù)庫(kù)，起個(gè)別名，用于做一些查詢操作。

語(yǔ)法：call sys.create_synonym_db(‘原庫(kù)’,‘同義詞庫(kù)’);

# 給test庫(kù)搞一個(gè)同義詞

call sys.create_synonym_db('test','t');

構(gòu)建完畢之后，會(huì)出現(xiàn)一個(gè)t數(shù)據(jù)庫(kù)，在t數(shù)據(jù)庫(kù)中，會(huì)將test庫(kù)中的所有表和視圖，全部生成為視圖存戶到t庫(kù)中。

構(gòu)建完畢同義詞庫(kù)中的視圖，如果你做了修改和刪除數(shù)據(jù)的操作，那修改和刪除的是原庫(kù)中的數(shù)據(jù)。

不過(guò)刪除視圖無(wú)所謂，視圖刪除了，和原表沒(méi)關(guān)系。

十三、索引

索引可以提升查詢是的效率。（合理的運(yùn)用）

索引是給表中具體的列追加一個(gè)索引。

索引并不是什么列都適合添加的。

一般需要對(duì)經(jīng)常被查詢的列添加索引，而且這個(gè)列的值不能過(guò)于重復(fù)。如果列的長(zhǎng)度特別大的就不太適合添加索引。

查看表中的索引信息。

語(yǔ)法：show index from 表名;

通過(guò)上述語(yǔ)句，查看一下t_employees表中的索引信息

添加索引信息

語(yǔ)法：create [索引類型] index 索引名稱 on 表名(列);

給員工表中的手機(jī)號(hào)字段，追加上一個(gè)普通索引。

# 給員工表中的手機(jī)號(hào)字段，追加上一個(gè)普通索引。

create index index_emp_phone on t_employees(phone_number);

給員工表中的郵箱字段追加一個(gè)唯一索引。

# 給員工表中的郵箱字段追加一個(gè)唯一索引。

create unique index index_unique_emp_email on t_employees(email);

經(jīng)常有一種操作，在查詢某張表時(shí)，經(jīng)常用這種條件where a = ？ and b = ？ ……

這種查詢可以做一個(gè)優(yōu)化，可以將a列和b列創(chuàng)建一個(gè)聯(lián)合（復(fù)合、多列）索引。

語(yǔ)法：create [索引類型] index 索引名稱 on 表名(列1,列2);

這種聯(lián)合索引的查詢效率，比起單獨(dú)給列1和列2分別構(gòu)建索引的查詢效率要快。

將員工表中的first_name和last_name組合一個(gè)聯(lián)合索引。

# 將員工表中的first_name和last_name組合一個(gè)聯(lián)合索引。

create index index_emp_name on t_employees(first_name,last_name);

刪除索引操作。

語(yǔ)法：drop index 索引名稱 on 表;

將員工表中的email的唯一索引刪除掉。

# 將員工表中的email的唯一索引刪除掉。

drop index index_unique_emp_email on t_employees;

創(chuàng)建表的時(shí)候，也可以指定索引信息（了解一小下）

語(yǔ)法：

create table 表名(

列1 數(shù)據(jù)類型…………,

index(列名) # 構(gòu)建索引的方式

);

十四、綜合練習(xí)

后期針對(duì)MySQL的操作，其實(shí)90%以上都是做Select查詢操作。

現(xiàn)在就用Oracle提供的三張經(jīng)典表來(lái)做練習(xí)

emp（員工表）、dept（部門表）、salgrade（工資等級(jí)表）

14.1 外鍵約束的補(bǔ)充（了解）

在做真正的練習(xí)之前，優(yōu)先搞定一下之前一致沒(méi)聊的外鍵約束。

外鍵其實(shí)就是做表與表之前關(guān)聯(lián)的一個(gè)約束。

現(xiàn)在有員工表，也有部門表。

一個(gè)部門下可以有多個(gè)員工。一個(gè)員工只能屬于一個(gè)部門。

就需要在一得一方，也就是員工表中，有一個(gè)外鍵，也就是deptno這個(gè)列，他的作用是和dept部門表做一個(gè)關(guān)聯(lián)。當(dāng)然，也可以給這個(gè)員工表中的deptno追加一個(gè)外鍵約束（不推薦）。

也給emp額外追加上外鍵約束。

語(yǔ)法：alter table 表名1 add constraint 外鍵名稱 foreign key (列1) references 表名2(列2);

# 給員工表追加上deptno的外鍵約束

alter table emp add constraint fk_emp_dept foreign key (deptno)

references dept(deptno);

14.2 查詢操作練習(xí)

14.2.1 查詢每個(gè)部門最高薪水的人員名稱

需要查詢的內(nèi)容都處于員工表中。

需要查詢員工的名稱，部門，薪資。

首先先完成查詢每個(gè)部門的最高薪資。 需要利用聚合函數(shù)的max，并且對(duì)部門編號(hào)做一個(gè)分組。

然后將部門最高信息查詢返回的虛擬表與emp表做表連接，查詢出最終結(jié)果

# 首先先完成查詢每個(gè)部門的最高薪資。 需要利用聚合函數(shù)的max，并且對(duì)部門編號(hào)做一個(gè)分組。

select deptno,max(sal) as maxsal from emp group by deptno;

# 將前面查詢到的部門最高薪資，和當(dāng)前的emp表做一個(gè)表連接操作，查詢出每個(gè)部門薪資最高的員工名稱

select

e.ename,e.deptno,m.maxsal

from

emp e inner join (select deptno,max(sal) as maxsal from emp group by deptno) m

on e.deptno = m.deptno and e.sal = m.maxsal;

14.2.2 查詢哪些員工薪資在部門的平均薪資之上

首先，依然還是在員工表中查詢。

需要查詢員工名稱，員工薪資，部門編號(hào)，部門平均薪資

首先需要先將各個(gè)部門的平均薪資查詢出來(lái)，根據(jù)聚合函數(shù)avg以及對(duì)部門編號(hào)分組查詢。

然后將查詢的平均薪資和員工表做一個(gè)關(guān)聯(lián)。

-- 查詢哪些員工薪資在部門的平均薪資之上

-- 需要查詢員工名稱，員工薪資，部門編號(hào)，部門平均薪資

# 首先需要先將各個(gè)部門的平均薪資查詢出來(lái)，根據(jù)聚合函數(shù)avg以及對(duì)部門編號(hào)分組查詢。

select deptno,avg(sal) avgsal from emp group by deptno;

# 將員工表和上述查詢部門平均薪資的表關(guān)聯(lián)到一起，條件是部門編號(hào)一致 and 員工薪資大于平均薪資

select

e.ename,e.sal,e.deptno,a.avgsal

from

emp e inner join (select deptno,avg(sal) avgsal from emp group by deptno) a

on e.deptno = a.deptno and e.sal > a.avgsal;

14.2.3 查詢每個(gè)部門的平均薪資等級(jí)

首先這里需要查詢員工表和薪資等級(jí)表。

需要查詢部門編號(hào)以及部門的平均薪資等級(jí)

首先需要查詢所有員工的薪資等級(jí)是多少。

直接將上述的查詢結(jié)果作為一個(gè)from的虛擬表，直接對(duì)部門做分組，針對(duì)薪資等級(jí)做avg平均值即可

-- 查詢每個(gè)部門的平均薪資等級(jí)

-- 需要查詢部門編號(hào)以及部門的平均薪資等級(jí)

# 首先需要查詢所有員工的薪資等級(jí)是多少。

select

e.deptno,s.grade

from

emp e inner join salgrade s

on e.sal between s.losal and hisal

# 可以將上述的查詢結(jié)果，基于deptno做分組，然后將grade做一個(gè)avg算平均數(shù)。

select

es.deptno,avg(es.grade)

from

(select e.deptno,s.grade from emp e inner join salgrade s on e.sal between s.losal and hisal) es

group by deptno;

14.2.4 查詢平均薪資最高的部門名稱

首先需要查詢員工表和部門表

需要查詢出來(lái)部門的名稱和平均薪資

首先平均薪資單獨(dú)的emp表就可以查詢出來(lái)，再基于排序和limit，就可以只查詢出平均薪資最高的部門

然后將上述查詢結(jié)果的內(nèi)容，和dept表做表連接，查詢出薪資最高的部門信息

-- 查詢平均薪資最高的部門名稱

-- 需要查詢出來(lái)部門的名稱和平均薪資

# 首先平均薪資單獨(dú)的emp表就可以查詢出來(lái)，再基于排序和limit，就可以只查詢出平均薪資最高的部門

select deptno,avg(sal) as avgsal from emp group by deptno order by avgsal desc limit 1

# 基于上述查詢的結(jié)果，和dept表做一個(gè)表連接操作

select

d.dname,da.avgsal

from

dept d inner join (select deptno,avg(sal) as avgsal from emp group by deptno order by avgsal desc limit 1) da

on d.deptno = da.deptno;

上述SQL可以查詢出emp，dept表中的正確結(jié)構(gòu)，但是如果多個(gè)部門的平均薪資一致，并且都是最高薪資，那上述查詢就有問(wèn)題了。

上述的limit方式不太合適了。

第一步：查詢出平均薪資的最大值。

第二步：查詢出和平均薪資最大值一致的部門編號(hào)。

第三部：基于部門編號(hào)查詢出部門的名稱。

# 上述的limit看著不錯(cuò)，但是存在問(wèn)題。

# 第一步：查詢出平均薪資的最大值。

select avg(sal) as avgsal from emp group by deptno order by avgsal desc limit 1;

# 第二步：查詢出和平均薪資最大值一致的部門編號(hào)。

select deptno,avg(sal) as maxavgsal from emp group by deptno having maxavgsal = (select avg(sal) as avgsal from emp group by deptno order by avgsal desc limit 1);

# 第三部：基于部門編號(hào)查詢出部門的名稱。

select

d.dname,avg(sal) as maxavgsal

from emp e inner join dept d on e.deptno = d.deptno

group by d.dname

having maxavgsal = (select avg(sal) as avgsal from emp group by deptno order by avgsal desc limit 1);

14.2.5 查詢薪水比自己領(lǐng)導(dǎo)還高的員工信息。

首先需要查詢emp表，但是需要兩張emp一個(gè)作為普通員工信息，一個(gè)作為領(lǐng)導(dǎo)信息

查詢員工名稱和員工薪資，領(lǐng)導(dǎo)名稱和領(lǐng)導(dǎo)薪資

直接將兩張emp表做連接，連接的條件是員工表中的mgr，與領(lǐng)導(dǎo)表中的empno作為條件。

-- 查詢薪水比自己領(lǐng)導(dǎo)還高的員工信息。

-- 查詢員工名稱和員工薪資，領(lǐng)導(dǎo)名稱和領(lǐng)導(dǎo)薪資

# 直接將兩張emp表做連接，連接的條件是員工表中的mgr，與領(lǐng)導(dǎo)表中的empno作為條件。

select

e.ename,e.sal,me.ename,me.sal

from

emp e inner join emp me on e.mgr = me.empno

where

e.sal > me.sal;

14.2.6 查詢比普通員工的最高薪資還要高的領(lǐng)導(dǎo)名稱

首先查詢的依然是emp表。

查詢出領(lǐng)導(dǎo)的名稱和薪資即可。

要先分出來(lái)哪些是普通員工，哪些是領(lǐng)導(dǎo)。 領(lǐng)導(dǎo)的empno都在mgr字段上。

分成三步查詢。

1、先查詢出所有的領(lǐng)導(dǎo)的empno，只需要查詢mgr字段即可，做個(gè)去重。

2、再基于上面查詢出來(lái)的領(lǐng)導(dǎo)的empno，篩選出普通員工，查詢出普通員工中的最高薪資。

3、再查詢領(lǐng)導(dǎo)信息，薪資大于普通員工的最高薪資的領(lǐng)導(dǎo)信息查詢出來(lái)。

-- 查詢比普通員工的最高薪資還要高的領(lǐng)導(dǎo)名稱

-- 1、先查詢出所有的領(lǐng)導(dǎo)的empno，只需要查詢mgr字段即可，做個(gè)去重。

select distinct mgr from emp where mgr is not null;

-- 2、再基于上面查詢出來(lái)的領(lǐng)導(dǎo)的empno，篩選出普通員工，查詢出普通員工中的最高薪資。

select max(sal) as maxsal from emp where empno not in (select distinct mgr from emp where mgr is not null);

-- 3、再查詢領(lǐng)導(dǎo)信息，薪資大于普通員工的最高薪資的領(lǐng)導(dǎo)信息查詢出來(lái)。

select

ename,sal

from

emp

where

empno in (select distinct mgr from emp where mgr is not null)

and sal > (select max(sal) as maxsal from emp where empno not in (select distinct mgr from emp where mgr is not null));

14.2.7 查詢每個(gè)薪資等級(jí)有多少個(gè)員工

首先必然需要查詢員工表以及薪資等級(jí)表的關(guān)聯(lián)操作。

查詢出薪資等級(jí)和對(duì)應(yīng)的員工個(gè)數(shù)即可。

分成兩步操作：

1、先基于emp和salgrade表查詢出每位員工的薪資等級(jí)

2、在上述的基礎(chǔ)上，再根據(jù)grade字段進(jìn)行分組，查詢count即可。

-- 查詢每個(gè)薪資等級(jí)有多少個(gè)員工

# 1、先基于emp和salgrade表查詢出每位員工的薪資等級(jí)

select

e.ename,s.grade

from

emp e inner join salgrade s on e.sal between s.losal and s.hisal;

# 2、在上述的基礎(chǔ)上，再根據(jù)grade字段進(jìn)行分組，查詢count即可。

select

s.grade,count(1)

from

emp e inner join salgrade s on e.sal between s.losal and s.hisal

group by s.grade

order by s.grade;

14.2.8 查詢出入職時(shí)間早于其領(lǐng)導(dǎo)的員工信息和部門信息

首先需要查詢emp表兩張，同時(shí)還要查詢出對(duì)應(yīng)的部門信息，還要關(guān)聯(lián)部門表。

需要查詢出員工名稱，部門名稱，領(lǐng)導(dǎo)名稱，領(lǐng)導(dǎo)部門

分成兩步操作：

1、查詢出員工及其領(lǐng)導(dǎo)信息，并且追加上一個(gè)判斷，員工的入職時(shí)間，要早于領(lǐng)導(dǎo)的入職時(shí)間

2、再上述的基礎(chǔ)上，再額外關(guān)聯(lián)兩張部門表，查詢出對(duì)應(yīng)的部門名稱即可。

-- 查詢出入職時(shí)間早于其領(lǐng)導(dǎo)的員工信息

# 需要查詢出員工名稱，部門名稱，領(lǐng)導(dǎo)名稱，領(lǐng)導(dǎo)部門

# 1、查詢出員工及其領(lǐng)導(dǎo)信息，并且追加上一個(gè)判斷，員工的入職時(shí)間，要早于領(lǐng)導(dǎo)的入職時(shí)間

select

e.ename 員工名稱,m.ename 領(lǐng)導(dǎo)名稱

from

emp e inner join emp m on e.mgr = m.empno

where

e.hiredate < m.hiredate;

# 2、再上述的基礎(chǔ)上，再額外關(guān)聯(lián)兩張部門表，查詢出對(duì)應(yīng)的部門名稱即可。

select

e.ename 員工名稱,d.dname 員工部門,m.ename 領(lǐng)導(dǎo)名稱 ,md.dname 領(lǐng)導(dǎo)部門

from

emp e inner join emp m on e.mgr = m.empno

inner join dept d on e.deptno = d.deptno

inner join dept md on m.deptno = md.deptno

where

e.hiredate < m.hiredate;

14.2.9 查詢出至少有5位員工的部門信息

首先需要查詢emp和dept表，需要兩張表做一個(gè)關(guān)聯(lián)。

需要查詢部門的編號(hào)，部門的名稱，部門的員工人數(shù)。

分成兩步走：

1、關(guān)聯(lián)員工和部門表，查詢出部門信息和部門的員工人數(shù)。

2、在上述的基礎(chǔ)上，篩選出員工人數(shù)大于5個(gè)的部門信息。

-- 查詢出至少有5位員工的部門信息

# 需要查詢部門的編號(hào)，部門的名稱，部門的員工人數(shù)。

# 分成兩步走：

# 1、關(guān)聯(lián)員工和部門表，查詢出部門信息和部門的員工人數(shù)。

select

d.deptno,d.dname,count(1)

from

emp e inner join dept d on e.deptno = d.deptno

group by d.deptno;

# 2、在上述的基礎(chǔ)上，篩選出員工人數(shù)大于5個(gè)的部門信息。

select

d.deptno,d.dname,count(1) as empcount

from

emp e inner join dept d on e.deptno = d.deptno

group by d.deptno

having empcount >= 5

14.2.10 查詢出薪資高于公司薪資的平均水平的員工名稱，所在部門，上級(jí)領(lǐng)導(dǎo)名稱，員工名稱的薪資等級(jí)

首先這里需要所有表都參與。emp表查詢出員工和領(lǐng)導(dǎo)的信息，所在部門需要dept表，員工薪資水平需要salgrade表。

需要查詢員工名稱，所在部門，上級(jí)領(lǐng)導(dǎo)名稱，員工名稱的薪資水平

分成四步走：

1、查詢出員工信息和所在部門的信息。

2、在上述的基礎(chǔ)上篩選出薪資高于公司平均水平的員工。

3、在上述的基礎(chǔ)上再追加查詢員工的領(lǐng)導(dǎo)名稱。

4、在上述的基礎(chǔ)上，再追加查詢員工的薪資水平。

-- 查詢出薪資高于公司薪資的平均水平的員工名稱，所在部門，上級(jí)領(lǐng)導(dǎo)名稱，員工名稱的薪資水平

# 需要查詢員工名稱，所在部門，上級(jí)領(lǐng)導(dǎo)名稱，員工名稱的薪資水平

# 分成四步走：

# 1、查詢出員工信息和所在部門的信息。

select

e.ename,d.dname

from

emp e inner join dept d on e.deptno = d.deptno;

# 2、在上述的基礎(chǔ)上篩選出薪資高于公司平均水平的員工。

select

e.ename,d.dname

from

emp e inner join dept d on e.deptno = d.deptno

where e.sal > (select avg(sal) from emp);

# 3、在上述的基礎(chǔ)上再追加查詢員工的領(lǐng)導(dǎo)名稱。

select

e.ename 員工名稱,d.dname 員工部門, m.ename 領(lǐng)導(dǎo)名稱

from

emp e inner join dept d on e.deptno = d.deptno

left join emp m on e.mgr = m.empno

where e.sal > (select avg(sal) from emp);

# 4、在上述的基礎(chǔ)上，再追加查詢員工的薪資水平。

select

e.ename 員工名稱,d.dname 員工部門, m.ename 領(lǐng)導(dǎo)名稱,s.grade 員工薪資等級(jí)

from

emp e inner join dept d on e.deptno = d.deptno

left join emp m on e.mgr = m.empno

inner join salgrade s on e.sal between s.losal and s.hisal

where e.sal > (select avg(sal) from emp);

14.2.11 查詢與’SCOTT’從事相同工作的員工名稱和部門名稱

首先需要查詢emp和dept的關(guān)聯(lián)查詢，同時(shí)需要子查詢來(lái)找到’SCOTT’的工作作為條件篩選的值

需要查詢員工名稱和部門名稱

分成兩步走

1、正常的查詢出員工的名稱和所在的部門

2、基于’SCOTT’從事的工作篩選出對(duì)應(yīng)的員工信息

-- 查詢與'SCOTT'從事相同工作的員工名稱和部門名稱

# 需要查詢員工名稱和部門名稱

# 分成兩步走

# 1、正常的查詢出員工的名稱和所在的部門

select

e.ename,d.dname

from

emp e inner join dept d on e.deptno = d.deptno;

# 2、基于'SCOTT'從事的工作篩選出對(duì)應(yīng)的員工信息

select

e.ename,d.dname

from

emp e inner join dept d on e.deptno = d.deptno

where

e.job = (select job from emp where ename = 'SCOTT') and ename != 'SCOTT';

14.2.12 查詢沒(méi)有員工的部門信息（exists）

首先，查詢時(shí)其實(shí)依然是emp和dept表之間的一個(gè)聯(lián)系。需要查詢出員工表中不存在的deptno，但是dept表存在的部門信息。

exists是可以追加到條件里的。

語(yǔ)法：where exists (查詢)

上述語(yǔ)法中，如果 (查詢) 有結(jié)果，相當(dāng)于條件滿足，會(huì)返回當(dāng)前數(shù)據(jù)，如果 (查詢) 沒(méi)有結(jié)果，相當(dāng)于不滿足條件，不會(huì)返回當(dāng)前結(jié)果。

在exists前，可以追加not，效果就是取反的效果了。

可以在查詢部門時(shí)，基于emp表的查詢結(jié)合exists來(lái)做到，如果查詢有員工信息，返回true，不返回當(dāng)前結(jié)果，如果返回false，希望返回結(jié)果。

select

d.deptno,d.dname,d.loc

from

dept d

where

not exists (select empno from emp e where e.deptno = d.deptno);

14.2.13 查詢部門的平均薪資，以2000作為點(diǎn)，返回’大于2000’或者’小于2000’或者’等于2000’的結(jié)果

首先查詢平均薪資，必然會(huì)用到員工表和部門表兩個(gè)信息。

查詢部門編號(hào)，以及部門的薪資導(dǎo)致是大于，小于，等于2000的結(jié)果。

需要使用到case when then end這種操作。

分成兩步操作

1、查詢每個(gè)部門的平均薪資。

2、基于上述查詢，將平均薪資的返回結(jié)果替換為需求中的大于，小于，等于2000的結(jié)果。

-- 查詢部門的平均薪資，以2000作為點(diǎn)，返回'大于2000'或者'小于2000'或者'等于2000'的結(jié)果

# 查詢部門編號(hào)，以及部門的薪資導(dǎo)致是大于，小于，等于2000的結(jié)果。

# 分成兩步操作

# 1、查詢每個(gè)部門的平均薪資。

select

d.deptno,

avg(e.sal)

from

emp e inner join dept d on e.deptno = d.deptno

group by d.deptno;

# 2、基于上述查詢，將平均薪資的返回結(jié)果替換為需求中的大于，小于，等于2000的結(jié)果。

select

d.deptno,

avg(e.sal) 平均薪資,

case

when avg(e.sal) = 2000 then '等于2000'

when avg(e.sal) > 2000 then '大于2000'

else '小于2000'

end as 是否大于2000

from

emp e inner join dept d on e.deptno = d.deptno

group by d.deptno;

14.3 常見(jiàn)錯(cuò)誤

柚子快報(bào)邀請(qǐng)碼778899分享：數(shù)據(jù)庫(kù) MySQL詳解

http://yzkb.51969.com/

推薦閱讀