mysql基本語法

1. DDL

Data Definition Language，資料定義語言，用來定義資料庫物件(資料庫，表，欄位) 。

 1.1 資料庫操作

• 查詢所有資料庫

show databases ;

• 查詢當前資料庫

select database() ;

• 建立資料庫

create database [ if not exists ] 資料庫名 [ default charset 字符集 ] [ collate 排序
規則 ] ;

• 刪除資料庫

drop database [ if exists ] 資料庫名 ;

• 切換資料庫

use 資料庫名 ;

 1.2 表操作

  1.2.1 表操作-查詢建立

• 查詢當前資料庫所有表

show tables;

• 看指定表結構

desc 表名 ;

• 查詢指定表的建表語句

show create table 表名 ;

• 建立表結構

CREATE TABLE 表名(

    欄位1 欄位1型別 [ COMMENT 欄位1註釋 ],
    欄位2 欄位2型別 [COMMENT 欄位2註釋 ],
    欄位3 欄位3型別 [COMMENT 欄位3註釋 ],
    ......
    欄位n 欄位n型別 [COMMENT 欄位n註釋 ]

) [ COMMENT 表註釋 ] ;

注意: [...] 內為可選引數，最後一個欄位後面沒有逗號

 比如，我們建立一張表 tb_user ，對應的結構如下，那麼建表語句為：

create table tb_user(

    id int comment '編號',
    name varchar(50) comment '姓名',
    age int comment '年齡',
    gender varchar(1) comment '性別'

) comment '使用者表';

  1.2.2 表操作-資料型別

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

• 數值型別
  

  

如:
1). 年齡欄位 -- 不會出現負數, 而且人的年齡不會太大

 age tinyint unsigned

2). 分數 -- 總分100分, 最多出現一位小數

 score double(4,1)

• 字串型別
  

  

  char 與 varchar 都可以描述字串，char是定長字串，指定長度多長，

就佔用多少個字元，和欄位值的長度無關 。而varchar是變長字串，指定的長

度為最大佔用長度 。相對來說，char的效能會更高些。

如：
1). 使用者名稱 username ------> 長度不定, 最長不會超過50

  username varchar(50)

2). 性別 gender ---------> 儲存值, 不是男,就是女

  gender char(1)

3). 手機號 phone --------> 固定長度為11

  phone char(11)

• 日期時間型別
  

  

如:
1). 生日欄位 birthday

  birthday date

2). 建立時間 createtime

  createtime datetime

  1.2.3 表操作-修改

• 新增欄位

ALTER TABLE 表名 ADD 欄位名 型別 (長度) [ COMMENT 註釋 ] [ 約束 ];

案例: 為emp表增加一個新的欄位”暱稱”為nickname，型別為varchar(20)

ALTER TABLE emp ADD nickname varchar(20) COMMENT '暱稱';

• 修改資料型別

ALTER TABLE 表名 MODIFY 欄位名 新資料型別 (長度);

• 修改欄位名和欄位型別

ALTER TABLE 表名 CHANGE 舊欄位名 新欄位名 型別 (長度) [ COMMENT 註釋 ] [ 約束 ];

案例: 將emp表的nickname欄位修改為username，型別為varchar(30)

ALTER TABLE emp CHANGE nickname username varchar(30) COMMENT '暱稱';

• 刪除欄位

ALTER TABLE 表名 DROP 欄位名;

案例: 將emp表的欄位username刪除

ALTER TABLE emp DROP username;

• 修改表名

ALTER TABLE 表名 RENAME TO 新表名;

案例: 將emp表的表名修改為 employee

ALTER TABLE emp RENAME TO employee;

  1.2.4 表操作-刪除

• 刪除表

DROP TABLE [ IF EXISTS ] 表名;

• 刪除指定表, 並重新建立表

TRUNCATE TABLE 表名;

注意: 在刪除表的時候，表中的全部資料也都會被刪除。

2. DML

DML英文全稱是Data Manipulation Language(資料操作語言)，用來對數

據庫中表的資料記錄進行增、刪、改操作。

• 新增資料（INSERT）
  
  
• 修改資料（UPDATE）
  
  
• 刪除資料（DELETE）

 2.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');

• 給全部欄位新增資料

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

• 批次新增資料

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

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

注意事項:

• 插入資料時，指定的欄位順序需要與值的順序是一一對應的。
  
  
• 字串和日期型資料應該包含在引號中。
  
  
• 插入的資料大小，應該在欄位的規定範圍內。
  
  

 2.2 修改資料

• 修改資料

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

案例:

1. 修改 id 為 1 的資料，將 name 修改為 dog

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

2. 修改 id 為 1 的資料, 將 name 修改為小王, gender 修改為女

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

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

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

注意事項:

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

有資料。

 2.3 刪除資料

• 刪除資料

DELETE FROM 表名 [ WHERE 條件 ] ;

案例:

1. 刪除 gender 為女的員工

delete from employee where gender = '女';

2. 刪除所有員工

delete from employee;

注意事項:

• DELETE 語句的條件可以有，也可以沒有，如果沒有條件，則會刪除整張表的所有資料。
  
  
• DELETE 語句不能刪除某一個欄位的值(可以使用UPDATE，將該欄位值置為NULL即可)。
  
  

3. DQL

DQL英文全稱是Data Query Language(資料查詢語言)，資料查詢語言，

用來查詢資料庫中表的記錄。

                 查詢關鍵字: SELECT

在一個正常的業務系統中，查詢操作的頻次是要遠高於增刪改的，當我們去

訪問企業官網、電商網站，在這些網站中我們所看到的資料，實際都是需要

從資料庫中查詢並展示的。而且在查詢的過程中，可能還會涉及到條件、排

序、分頁等操作。

 3.1 基本語法

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

SELECT

  欄位列表

FROM

  表名列表

WHERE

  條件列表

GROUP BY

  分組欄位列表

HAVING

  分組後條件列表

ORDER BY

  排序欄位列表

LIMIT

  分頁引數

 3.2 基礎查詢

• 查詢多個欄位

SELECT 欄位1, 欄位2, 欄位3 ... FROM 表名 ;

SELECT * FROM 表名 ;

注意 : * 號代表查詢所有欄位，在實際開發中儘量少用（不直觀、影響效率）。

• 欄位設定別名

SELECT 欄位1 [ AS 別名1 ] , 欄位2 [ AS 別名2 ] ... FROM 表名;

SELECT 欄位1 [ 別名1 ] , 欄位2 [ 別名2 ] ... FROM 表名;

• 除重複記錄

SELECT DISTINCT 欄位列表 FROM 表名;

案例:

1. 查詢指定欄位 name, workno, age並返回

select name,workno,age from emp;

2. 查詢返回所有欄位

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

select * from emp;

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

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

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

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

 3.3 條件查詢

• 語法：

SELECT 欄位列表 FROM 表名 WHERE 條件列表 ;

• 條件

常用的比較運算子如下:

  

常用的邏輯運算子如下:

  

案例:

1. 查詢年齡等於 88 的員工

select * from emp where age = 88;

2. 查詢年齡小於 20 的員工資訊

select * from emp where age < 20;

3. 查詢年齡小於等於 20 的員工資訊

select * from emp where age <= 20;

4. 查詢沒有身份證號的員工資訊

select * from emp where idcard is null;

5. 查詢有身份證號的員工資訊

select * from emp where idcard is not null;

6. 查詢年齡不等於 88 的員工資訊

select * from emp where age != 88;

select * from emp where age <> 88;

7. 查詢年齡在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;

8. 查詢性別為 女 且年齡小於 25歲的員工資訊

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

9. 查詢年齡等於18 或 20 或 40 的員工資訊

select * from emp where age = 18 or age = 20 or age =40;

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

10. 查詢姓名為兩個字的員工資訊

select * from emp where name like '_ _';

11. 查詢身份證號最後一位是 X 的員工資訊

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

 3.4 聚合函式

• 介紹

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

• 常見的聚合函式

  

• 語法

SELECT 聚合函式(欄位列表) FROM 表名 ;

注意 : NULL值是不參與所有聚合函式運算的。

案例:

1. 統計該企業員工數量

select count(*) from emp; -- 統計的是總記錄數

select count(idcard) from emp; -- 統計的是idcard欄位不為null的記錄數

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

select avg(age) from emp;

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

select max(age) from emp;

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

select min(age) from emp;

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

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

 3.5 分組查詢

• 語法

SELECT 欄位列表 FROM 表名 [ WHERE 條件 ] GROUP BY 分組欄位名 [ HAVING 分組後過濾條件 ];

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

注意事項:

• 分組之後，查詢的欄位一般為聚合函式和分組欄位，查詢其他欄位無任何意義。
  
  
• 執行順序: where > 聚合函式 > having 。
  
  
• 支援多欄位分組, 具體語法為 : group by column A,column B

案例：

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

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

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

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

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

select workadress count(*) address_count fron emp
where age < 45  
group by workaddress having address_count >= 3;

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

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

 3.6 排序查詢

• 語法

SELECT 欄位列表 FROM 表名 ORDER BY 欄位1 排序方式1 , 欄位2 排序方式2 ;

• 排序方式
  
  • ASC : 升序(預設值)
    
    
  • DESC: 降序

注意事項：

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

案例:

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

select * from emp order by age asc;

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

select * from emp order by entrydate desc;

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

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

 3.7 分頁查詢

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

• 語法

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

注意事項:

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

案例：

1. 查詢第1頁員工資料, 每頁展示10條記錄

select * from emp limit 0,10;

2. 查詢第2頁員工資料, 每頁展示10條記錄 --------> (頁碼-1)*頁展示記錄數

select * from emp limit 10,10;

 3.8 綜合案例

1. 查詢年齡為20,21,22,23歲的女員工資訊。

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

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

select * form emp where 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 * form emp where gender = '男' and age between 20 and 40 
order by age asc, entrydate asc limit 5;

 3.9 執行順序

在講解DQL語句的具體語法之前，我們已經講解了DQL語句的完整語法，

及編寫順序，接下來，我們要來說明的是DQL語句在執行時的執行順序，

也就是先執行那一部分，後執行那一部分。

  

4. DCL

DCL英文全稱是Data Control Language(資料控制語言)，用來管理資料庫使用者、控制資料庫的訪問許可權。

 4.1 管理使用者

• 查詢使用者

select * from mysql.user;

  

• 建立使用者

CREATE USER '使用者名稱'@'主機名' IDENTIFIED BY '密碼';

• 修改使用者密碼

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

• 刪除使用者

DROP USER '使用者名稱'@'主機名' ;

注意事項:

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

案例:

1. 建立使用者 itcast , 只能夠在當前主機localhost訪問, 密碼123456;

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

2. 建立使用者 xiaochou , 可以在任意主機訪問該資料庫, 密碼123456;

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

3. 修改使用者 xiaochou 的訪問密碼為1234;

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

4. 刪除 itcast@localhost 使用者

drop user 'itcast'@'localhost';

 4.2 許可權控制

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

  

• 查詢許可權

SHOW GRANTS FOR '使用者名稱'@'主機名' ;

• 授予許可權

GRANT 許可權列表 ON 資料庫名.表名 TO '使用者名稱'@'主機名';

• 撤銷許可權

REVOKE 許可權列表 ON 資料庫名.表名 FROM '使用者名稱'@'主機名';

注意事項：

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

案例:

1. 查詢 'xiaochou'@'%' 使用者的許可權

show grants for 'xiaochou'@'%';

2. 授予 'xiaochou'@'%' 使用者itcast資料庫所有表的所有操作許可權

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

3. 撤銷 'xiaochou'@'%' 使用者的itcast資料庫的所有許可權

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