MySQL學習筆記（四）—

1.?DML

1.1 添加數據

1.1.1 給指定字段添加數據

1.1.2 給全部字段添加數據

1.1.3?批量添加數據

1.2 修改數據

1.3 刪除數據

2. DQL

2.1 基本語法

2.2 基礎查詢

2.2.1 查詢多個字段

2.2.2 字段設置別名

2.2.3 去除重復記錄

2.3 條件查詢

2.4 聚合函數

2.5 分組查詢

2.6?排序查詢

2.7 分頁查詢

2.8 案例

2.9 執行順序

3. DCL

3.1 管理用戶

3.2 權限控制

1.?DML

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

添加數據（INSERT）
修改數據（UPDATE）
刪除數據（DELETE）

1.1 添加數據

1.1.1 給指定字段添加數據

INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...);

案例 :

給employee表所有的字段添加數據；

insert into employee(id,workno,name,gender,age,idcard,entrydate)
values(1,'1','Itcast','男',10,'123456789012345678','2000-01-01');

插入數據完成之后，我們有兩種方式，查詢數據庫的數據：

A. 方式一
- 在左側的表名上雙擊，就可以查看這張表的數據。

B. 方式二
- 可以直接一條查詢數據的SQL語句, 語句如下:
- ```
select * from employee;
```

案例：

給employee表所有的字段添加數據

執行如下 SQL ，添加的年齡字段值為 -1 。

insert into employee(id,workno,name,gender,age,idcard,entrydate)
values(1,'1','Itcast','男',-1,'123456789012345678','2000-01-01');

執行上述的 SQL 語句時，報錯了，具體的錯誤信息如下：

因為 employee 表的 age 字段類型為 tinyint ，而且還是無符號的 unsigned ，所以取值只能在 0-255 之間。

1.1.2 給全部字段添加數據

INSERT INTO 表名 VALUES (值1, 值2, ...);

案例：

插入數據到employee表，具體的SQL如下：

insert into employee values(2,'2','張無忌','男',18,'123456789012345670','2005-01-
01');

1.1.3?批量添加數據

INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...), (值1, 值2, ...), (值
1, 值2, ...) ;

INSERT INTO 表名 VALUES (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...) ;

案例：

批量插入數據到employee表，具體的SQL如下：

insert into employee values(3,'3','韋一笑','男',38,'123456789012345670','2005-01-
01'),(4,'4','趙敏','女',18,'123456789012345670','2005-01-01');

注意事項:

插入數據時，指定的字段順序需要與值的順序是一一對應的。
字符串和日期型數據應該包含在引號中。
插入的數據大小，應該在字段的規定范圍內。

1.2 修改數據

修改數據的具體語法為 :

UPDATE 表名 SET 字段名1 = 值1 , 字段名2 = 值2 , .... [ WHERE 條件 ] ;

案例 :

A. 修改id為1的數據，將name修改為itheima

update employee set name = 'itheima' where id = 1;

B. 修改id為1的數據, 將name修改為小昭, gender修改為女

update employee set name = '小昭' , gender = '女' where id = 1;

C. 將所有的員工入職日期修改為 2008-01-01

update employee set entrydate = '2008-01-01';

注意事項:

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

1.3 刪除數據

刪除數據的具體語法為：

DELETE FROM 表名 [ WHERE 條件 ] ;

案例 :

A. 刪除gender為女的員工

delete from employee where gender = '女';

B. 刪除所有員工

delete from employee;

注意事項:

DELETE 語句的條件可以有，也可以沒有，如果沒有條件，則會刪除整張表的所有數據。
DELETE 語句不能刪除某一個字段的值(可以使用UPDATE，將該字段值置為NULL即可)。
當進行刪除全部數據操作時，datagrip會提示我們，詢問是否確認刪除，我們直接點擊執行?即可。

2. DQL

DQL 英文全稱是 Data Query Language( 數據查詢語言 ) ，數據查詢語言，用來查詢數據庫中表的記錄。

查詢關鍵字 :

SELECT

在一個正常的業務系統中，查詢操作的頻次是要遠高于增刪改的，當我們去訪問企業官網、電商網站，在這些網站中我們所看到的數據，實際都是需要從數據庫中查詢并展示的。而且在查詢的過程中，可能還會涉及到條件、排序、分頁等操作。

那么，本小節我們主要學習的就是如何進行數據的查詢操作。我們先來完成如下數據準備工作 :

create table emp(id int comment '編號',workno varchar(10) comment '員工工號',name varchar(10) comment '姓名',gender char(1) comment '性別',age tinyint unsigned comment '年齡',idcard char(18) comment '身份證號碼',workaddress varchar(50) comment '工作地址',entrydate date comment '入職時間'
) comment '員工表';insert into emp (id, workno, name, gender, age, idcard, workaddress, entrydate) values (1, '00001', '柳巖666', '女', 20, '123456789012345678', '北京', '2000-01-01');
insert into emp (id, workno, name, gender, age, idcard, workaddress, entrydate) values (2, '00002', '張無忌', '男', 18, '123456789012345670', '北京', '2005-09-01');
insert into emp (id, workno, name, gender, age, idcard, workaddress, entrydate) values (3, '00003', '韋一笑', '男', 38, '123456789712345670', '上海', '2005-08-01');
insert into emp (id, workno, name, gender, age, idcard, workaddress, entrydate) values (4, '00004', '趙敏', '女', 18, '123456757123845670', '北京', '2009-12-01');
insert into emp (id, workno, name, gender, age, idcard, workaddress, entrydate) values (5, '00005', '小昭', '女', 16, '123456769012345678', '上海', '2007-07-01');
insert into emp (id, workno, name, gender, age, idcard, workaddress, entrydate) values (6, '00006', '楊逍', '男', 28, '12345678931234567X', '北京', '2006-01-01');
insert into emp (id, workno, name, gender, age, idcard, workaddress, entrydate) values (7, '00007', '范瑤', '男', 40, '123456789212345670', '北京', '2005-05-01');
insert into emp (id, workno, name, gender, age, idcard, workaddress, entrydate) values (8, '00008', '黛綺絲', '女', 38, '123456157123645670', '天津', '2015-05-01');
insert into emp (id, workno, name, gender, age, idcard, workaddress, entrydate) values (9, '00009', '范涼涼', '女', 45, '123156789012345678', '北京', '2010-04-01');
insert into emp (id, workno, name, gender, age, idcard, workaddress, entrydate) values (10, '00010', '陳友諒', '男', 53, '123456789012345670', '上海', '2011-01-01');
insert into emp (id, workno, name, gender, age, idcard, workaddress, entrydate) values (11, '00011', '張士誠', '男', 55, '123567897123465670', '江蘇', '2015-05-01');
insert into emp (id, workno, name, gender, age, idcard, workaddress, entrydate) values (12, '00012', '常遇春', '男', 32, '123446757152345670', '北京', '2004-02-01');
insert into emp (id, workno, name, gender, age, idcard, workaddress, entrydate) values (13, '00013', '張三豐', '男', 88, '123656789012345678', '江蘇', '2020-11-01');
insert into emp (id, workno, name, gender, age, idcard, workaddress, entrydate) values (14, '00014', '滅絕', '女', 65, '123456719012345670', '西安', '2019-05-01');
insert into emp (id, workno, name, gender, age, idcard, workaddress, entrydate) values (15, '00015', '胡青牛', '男', 70, '12345674971234567X', '西安', '2018-04-01');
insert into emp (id, workno, name, gender, age, idcard, workaddress, entrydate) values (16, '00016', '周芷若', '女', 18, null, '北京', '2012-06-01');

準備完畢后，我們就可以看到 emp 表中準備的 16 條數據。接下來，我們再來完成 DQL 語法的學習。

2.1 基本語法

DQL 查詢語句，語法結構如下：

SELECT
字段列表
FROM
表名列表
WHERE
條件列表
GROUP BY
分組字段列表
HAVING
分組后條件列表
ORDER BY
排序字段列表
LIMIT
分頁參數

我們在講解這部分內容的時候，會將上面的完整語法進行拆分，分為以下幾個部分：

基本查詢（不帶任何條件）
條件查詢（WHERE）
聚合函數（count、max、min、avg、sum）
分組查詢（group by）
排序查詢（order by）
分頁查詢（limit）

2.2 基礎查詢

2.2.1 查詢多個字段

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

SELECT 字段1, 字段2, 字段3 ... FROM 表名 ;

SELECT * FROM 表名 ;

注意 :

* 號代表查詢所有字段，在實際開發中盡量少用（不直觀、影響效率）。

2.2.2 字段設置別名

SELECT 字段1 [ AS 別名1 ] , 字段2 [ AS 別名2 ] ... FROM 表名;

SELECT 字段1 [ 別名1 ] , 字段2 [ 別名2 ] ... FROM 表名;

2.2.3 去除重復記錄

SELECT DISTINCT 字段列表 FROM 表名;

案例：

A. 查詢指定字段 name, workno, age并返回

select name,workno,age from emp;

B. 查詢返回所有字

select id ,workno,name,gender,age,idcard,workaddress,entrydate from emp;

select * from emp;

C. 查詢所有員工的工作地址,起別名

select workaddress as '工作地址' from emp;

-- as可以省略
select workaddress '工作地址' from emp;

D. 查詢公司員工的上班地址有哪些(不要重復)

select distinct workaddress '工作地址' from emp;

2.3 條件查詢

1). 語法

SELECT 字段列表 FROM 表名 WHERE 條件列表 ;

2). 條件

常用的比較運算符如下 :

比較運算符	功能
>	大于
>=	大于等于
<	小于
<=	小于等于
=	等于
<> 或 !=	不等于
BETWEEN ... AND ...	在某個范圍之內 ( 含最小、最大值 )
IN(...)	在in之后的列表中的值，多選一
LIKE 占位符	模糊匹配(_匹配單個字符, %匹配任意個字符)
IS NULL	是NULL

常用的邏輯運算符如下 :

邏輯運算符	功能
AND 或 &&	并且 (多個條件同時成立)
OR 或 \|\|	或者 (多個條件任意一個成立)
NOT 或 !	非 , 不是

案例 :

A. 查詢年齡等于 88 的員工

select * from emp where age = 88;

B. 查詢年齡小于 20 的員工信息

select * from emp where age < 20;

C. 查詢年齡小于等于 20 的員工信息

select * from emp where age <= 20;

D. 查詢沒有身份證號的員工信息

select * from emp where idcard is null;

E. 查詢有身份證號的員工信息

select * from emp where idcard is not null;

F. 查詢年齡不等于 88 的員工信息

select * from emp where age != 88;
select * from emp where age <> 88;

G. 查詢年齡在15歲(包含) 到 20歲(包含)之間的員工信息

select * from emp where age >= 15 && age <= 20;
select * from emp where age >= 15 and age <= 20;
select * from emp where age between 15 and 20;

H. 查詢性別為女且年齡小于 25歲的員工信息

select * from emp where gender = '女' and age < 25;

I. 查詢年齡等于18 或 20 或 40 的員工信息

select * from emp where age = 18 or age = 20 or age = 40;
select * from emp where age in(18,20,40);

J. 查詢姓名為兩個字的員工信息 _ %
- _匹配單個字符
- %匹配任意個字符

select * from emp where name like '__';

K. 查詢身份證號最后一位是X的員工信息

select * from emp where idcard like '%X';
select * from emp where idcard like '_________________X';

2.4 聚合函數

1). 介紹

將一列數據作為一個整體，進行縱向計算。

2). 常見的聚合函數

函數	功能
count	統計數量
max	最大值
min	最小值
avg	平均值
sum	求和

3). 語法

SELECT 聚合函數(字段列表) FROM 表名 ;

注意 :

NULL值是不參與所有聚合函數運算的。

案例：

A. 統計該企業員工數量

select count(*) from emp; -- 統計的是總記錄數
select count(idcard) from emp; -- 統計的是idcard字段不為null的記錄數

對于 count 聚合函數，統計符合條件的總記錄數，還可以通過 count( 數字 / 字符串 ) 的形式進行統計查詢，比如：

select count(1) from emp;

對于count(*) 、count(字段)、 count(1) 的具體原理，我們在進階篇中SQL優化部分會詳細講解，此處大家只需要知道如何使用即可。

B. 統計該企業員工的平均年齡

select avg(age) from emp;

C. 統計該企業員工的最大年齡

select max(age) from emp;

D. 統計該企業員工的最小年齡

select min(age) from emp;

E. 統計西安地區員工的年齡之和

select sum(age) from emp where workaddress = '西安';

2.5 分組查詢

1). 語法

SELECT 字段列表 FROM 表名 [ WHERE 條件 ] GROUP BY 分組字段名 [ HAVING 分組后過濾條件 ];

2). where 與 having 區別

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

注意事項:

分組之后，查詢的字段一般為聚合函數和分組字段，查詢其他字段無任何意義。
執行順序: where > 聚合函數 > having 。
支持多字段分組, 具體語法為 : group by columnA,columnB

案例 :

A. 根據性別分組 , 統計男性員工和女性員工的數量

select gender, count(*) from emp group by gender;

B. 根據性別分組 , 統計男性員工和女性員工的平均年齡

select gender, avg(age) from emp group by gender;

C. 查詢年齡小于45的員工 , 并根據工作地址分組 , 獲取員工數量大于等于3的工作地址

select workaddress, count(*) as address_count from emp while age < 45 group by workaddress having address_count >= 3 ;

D. 統計各個工作地址上班的男性及女性員工的數量

select workaddress, gender, count(*) as '數量' from emp group by gender, workaddress;

2.6?排序查詢

排序在日常開發中是非常常見的一個操作，有升序排序，也有降序排序。

1). 語法

SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1 , 字段2 排序方式2 ;

2). 排序方式

ASC : 升序(默認值)
DESC: 降序

注意事項：

如果是升序, 可以不指定排序方式ASC ;
如果是多字段排序，當第一個字段值相同時，才會根據第二個字段進行排序 ;

案例 :

A. 根據年齡對公司的員工進行升序排序

select * from emp order by age asc;
select * from emp order by age;

B. 根據入職時間, 對員工進行降序排序

select * from emp order by entrydate desc;

C. 根據年齡對公司的員工進行升序排序 , 年齡相同 , 再按照入職時間進行降序排序

select * from emp order by age asc , entrydate desc;

2.7 分頁查詢

分頁操作在業務系統開發時，也是非常常見的一個功能，我們在網站中看到的各種各樣的分頁條，后臺都需要借助于數據庫的分頁操作。

1). 語法

SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查詢記錄數 ;

注意事項:

起始索引從0開始，起始索引 = （查詢頁碼 - 1）* 每頁顯示記錄數。
分頁查詢是數據庫的方言，不同的數據庫有不同的實現，MySQL中是LIMIT。
如果查詢的是第一頁數據，起始索引可以省略，直接簡寫為 limit 10。

案例 :

A. 查詢第1頁員工數據, 每頁展示10條記錄

select * from emp limit 0,10;
select * from emp limit 10;

B. 查詢第2頁員工數據, 每頁展示10條記錄 --------> (頁碼-1)*頁展示記錄數
- （2 - 1）* 10 = 10

select * from emp limit 10,10;

2.8 案例

1). 查詢年齡為 20,21,22,23 歲的員工信息。

select * from emp where gender = '女' and age in(20,21,22,23);

2). 查詢性別為男，并且年齡在 20-40 歲 ( 含 ) 以內的姓名為三個字的員工。

select * from emp while gender = '男' and ( age between 20 and 40 ) and name like '___';

3). 統計員工表中 , 年齡小于 60 歲的 , 男性員工和女性員工的人數。

select gender, count(*) from emp where age < 60 group by gender;

4). 查詢所有年齡小于等于 35 歲員工的姓名和年齡，并對查詢結果按年齡升序排序，如果年齡相同按入職時間降序排序。

select name , age from emp where age <= 35 order by age asc , entrydate desc;

5). 查詢性別為男，且年齡在 20-40 歲 ( 含 ) 以內的前 5 個員工信息，對查詢的結果按年齡升序排序，

年齡相同按入職時間升序排序。

select * from emp where gender = '男' and age between 20 and 40 order by age asc, entrydate asc limit 0, 5 ;

前五個員工就是查詢第一頁的員工，每一頁員工展示 5 條數據

2.9 執行順序

在講解 DQL 語句的具體語法之前，我們已經講解了 DQL 語句的完整語法，及編寫順序，接下來，我們要來說明的是DQL 語句在執行時的執行順序，也就是先執行那一部分，后執行那一部分。

驗證：

查詢年齡大于15的員工姓名、年齡，并根據年齡進行升序排序。

select name , age from emp where age > 15 order by age asc;

在查詢時，我們給emp表起一個別名 e，然后在select 及 where中使用該別名。

select e.name , e.age from emp as e where e.age > 15 order by age asc;

執行上述 SQL 語句后，我們看到依然可以正常的查詢到結果，此時就說明： from 先執行 , 然 where 和 select 執行。那 where 和 select 到底哪個先執行呢 ?

此時，此時我們可以給 select 后面的字段起別名，然后在 where 中使用這個別名，然后看看是否可

以執行成功。

select e.name as ename , e.age as eage from emp as e where eage > 15 order by age asc;

執行上述 SQL 報錯了：

由此我們可以得出結論 : from 先執行，然后執行 where ，再執行 select 。

接下來，我們再執行如下 SQL 語句，查看執行效果：

select e.name as ename , e.age as eage from emp as e where e.age > 15 order by eage asc;

結果執行成功。那么也就驗證了 : order by 是在 select 語句之后執行的。

綜上所述，我們可以看到 DQL 語句的執行順序為：

from ... where ... group by ... having ... select ... order by ... limit ...

3. DCL

DCL 英文全稱是 Data Control Language ( 數據控制語言 ) ，用來管理數據庫用戶、控制數據庫的訪

問權限。

3.1 管理用戶

1). 查詢用戶

select * from mysql.user;

查詢的結果如下 :

其中 Host? 代表當前用戶訪問的主機 , 如果為? localhost, 僅代表只能夠在當前本機訪問，是不可以遠程訪問的。 User? 代表的是訪問該數據庫的用戶名。 在?MySQL?中需要通過?Host?和?Use r來唯一標識一個用戶 。

2). 創建用戶

CREATE USER '用戶名'@'主機名' IDENTIFIED BY '密碼';

3). 修改用戶密碼

ALTER USER '用戶名'@'主機名' IDENTIFIED WITH mysql_native_password BY '新密碼' ;

4). 刪除用戶

DROP USER '用戶名'@'主機名' ;

注意事項:

在MySQL中需要通過用戶名@主機名的方式，來唯一標識一個用戶。
主機名可以使用 % 通配。
這類SQL開發人員操作的比較少，主要是DBA（ Database Administrator 數據庫管理員）使用。

案例：

A. 創建用戶itcast, 只能夠在當前主機localhost訪問, 密碼123456;

create user 'itcast'@'localhost' identified by '123456';

B. 創建用戶heima, 可以在任意主機訪問該數據庫, 密碼123456;

create user 'heima'@'%' identified by '123456';

C. 修改用戶heima的訪問密碼為1234;

alter user 'heima'@'%' identified with mysql_native_password by '1234';

D. 刪除 itcast@localhost 用戶

drop user 'itcast'@'localhost';

3.2 權限控制

MySQL 中定義了很多種權限，但是常用的就以下幾種：

權限	說明
ALL, ALL PRIVILEGES	所有權限
SELECT	查詢數據
INSERT	插入數據
UPDATE	修改數據
DELETE	刪除數據
ALTER	修改表
DROP	刪除數據庫/表/視圖
CREATE	創建數據庫/表

上述只是簡單羅列了常見的幾種權限描述，其他權限描述及含義，可以直接參考 官方文檔 。

1). 查詢權限

SHOW GRANTS FOR '用戶名'@'主機名' ;

2). 授予權限

GRANT 權限列表 ON 數據庫名.表名 TO '用戶名'@'主機名';

3). 撤銷權限

REVOKE 權限列表 ON 數據庫名.表名 FROM '用戶名'@'主機名';

注意事項：

多個權限之間，使用逗號分隔
授權時，數據庫名和表名可以使用 * 進行通配，代表所有。

案例 :

A. 查詢 'heima'@'%' 用戶的權限

show grants for 'heima'@'%';

B. 授予 'heima'@'%' 用戶itcast數據庫所有表的所有操作權限

grant all on itcast.* to 'heima'@'%';

C. 撤銷 'heima'@'%' 用戶的itcast數據庫的所有權限

revoke all on itcast.* from 'heima'@'%';