MySQL的基礎語法1（增刪改查、DDL、DML、DQL和DCL）

一、基本介紹

二、SQL通用語法

三、SQL分類(DDL、DML、DQL、DCL)

1.DDL

?1.1數據庫操作

1.2表操作

1.2.1表操作-查詢創建

1.2.2表操作-數據類型?

1）數值類型

2）字符串類型?

?3）日期時間類型?編輯

?4）表操作-案例

?1.2.3表操作-修改

1.2.4表操作-刪除

2.DML?

2.1添加數據

?2.2修改數據

2.3刪除數據?

3.DQL

3.1基本語法以及執行順序

3.2基礎查詢

3.3 條件查詢

1). 語法

2). 條件常用的比較運算符如下:

3）常用的邏輯運算符如下:

4）案例

3.4聚合函數?

1). 介紹

2). 常見的聚合函數如下

?3). 語法

4）案例

3.5 分組查詢

?3.6 排序查詢

3.7分頁查詢

4.DCL

4.1?管理用戶

1)基本語法

2）案例

4.2 權限控制

1）常見權限

?編輯?2）基本語法

3）案例

一、基本介紹

SQL: 全稱 Structured Query Language，結構化查詢語言。操作關系型數據庫的編程語言，定義了一套操作關系型數據庫統一標準。

數據模型如下圖：

二、SQL通用語法

1). SQL語句可以單行或多行書寫，以分號結尾。

2). SQL語句可以使用空格/縮進來增強語句的可讀性。

3). MySQL數據庫的SQL語句不區分大小寫，關鍵字建議使用大寫。

4). 注釋：

? ? ? ? ? ? ? ? ? ? ? ? ? 單行注釋：-- 注釋內容或 # 注釋內容

? ? ? ? ? ? ? ? ? ? ? ? ? 多行注釋：/* 注釋內容 */

三、SQL分類(DDL、DML、DQL、DCL)

SQL語句，根據其功能，主要分為四類：DDL、DML、DQL、DCL。

1.DDL

Data Definition Language，數據定義語言，用來定義數據庫對象(數據庫，表，字段) 。

?1.1數據庫操作

注意：語句必須以分號結束1). 查詢所有數據庫
show databases ;2). 查詢當前數據庫select database() ;3). 創建數據庫create database [ if not exists ] 數據庫名 [ default charset 字符集 ] [ collate 排序
規則 ] ;
注意：在同一個數據庫服務器中，不能創建兩個名稱相同的數據庫，否則將會報錯。4). 刪除數據庫drop database [ if exists ] 數據庫名 ;
注意：如果刪除一個不存在的數據庫，將會報錯。此時，可以加上參數 if exists ，如果數據庫存在，再
執行刪除，否則不執行刪除。5). 切換數據庫
use 數據庫名 ;
解釋：我們要操作某一個數據庫下的表時，就需要通過該指令，切換到對應的數據庫下，否則是不能操作的。

1.2表操作

1.2.1表操作-查詢創建


1). 查詢當前數據庫所有表
show tables;比如,我們可以切換到sys這個系統數據庫,并查看系統數據庫中的所有表結構。
use sys;
show tables;2). 查看指定表結構
desc 表名 ;
通過這條指令，我們可以查看到指定表的字段，字段的類型、是否可以為NULL，是否存在默認值等信
息。3). 查詢指定表的建表語句
show create table 表名 ;
通過這條指令，主要是用來查看建表語句的，而有部分參數我們在創建表的時候，并未指定也會查詢
到，因為這部分是數據庫的默認值，如：存儲引擎、字符集等。4). 創建表結構
CREATE TABLE  表名(字段1  字段1類型 [ COMMENT  字段1注釋 ],字段2  字段2類型 [COMMENT  字段2注釋 ],字段3  字段3類型 [COMMENT  字段3注釋 ],......字段n  字段n類型 [COMMENT  字段n注釋 ] 
) [ COMMENT  表注釋 ] ;注意: [...] 內為可選參數，最后一個字段后面沒有逗號

示例：創建以下表格形式：?

id	name	age	gender
1	橙	18	男
2	桃	19	女

代碼如下：

create table tb(id int comment '編號',name varchar(50) comment '姓名',age int comment '年齡',gender varchar(1) comment '性別') comment '用戶表';

1.2.2表操作-數據類型?

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

1）數值類型

類型	大小	有符號（SIGNED）范圍	無符號（UNSIGNED）范圍	描述
TINYINT	1byte	(-128, 127)	(0, 255)	小整數值
SMALLINT	2bytes	(-32768, 32767)	(0, 65535)	大整數值
MEDIUMINT	3bytes	(-8388608,8388607)	(0, 16777215)	大整數值
INT/INTEGER	4bytes	(-2147483648,2147483647)	(0,4294967295)	大整數值
BIGINT	8bytes	(-2^63, 2^63-1)	(0, 2^64-1)	極大整數值
FLOAT	4bytes	(-3.402823466 E+38, 3.402823466351 E+38)	0 和 (1.175494351 E-38, 3.402823466 E+38)	單精度浮點數
DOUBLE	8bytes	(-1.7976931348623157 E+308, 1.7976931348623157 E+308)	0 和 (2.2250738585072014 E-308, 1.7976931348623157 E+308)	雙精度浮點數
DECIMAL	依賴于 M (精度) 和 D (標度) 的值	依賴于 M (精度) 和 D (標度) 的值	小數值 (精確定點數)

如: 1). 年齡字段 -- 不會出現負數, 而且人的年齡不會太大age tinyint unsigned2). 分數 -- 總分100分, 最多出現一位小數score double(4,1)

2）字符串類型?

char 與 varchar 都可以描述字符串，char是定長字符串，指定長度多長，就占用多少個字符，和字段值的長度無關。而varchar是變長字符串，指定的長度為最大占用長度 。相對來說，char的性能會更高些。?

如： 1). 用戶名 username ------> 長度不定, 最長不會超過50username varchar(50)2). 性別 gender ---------> 存儲值, 不是男,就是女gender char(1)3). 手機號 phone --------> 固定長度為11phone char(11)

?3）日期時間類型

如: 1). 生日字段 birthdaybirthday date2). 創建時間 createtimecreatetime  datetime

?4）表操作-案例

設計一張員工信息表，要求如下：

????????????????1. 編號（純數字）

????????????????2. 員工工號 (字符串類型，長度不超過10位)

????????????????3. 員工姓名（字符串類型，長度不超過10位）

????????????????4. 性別（男/女，存儲一個漢字）

????????????????5. 年齡（正常人年齡，不可能存儲負數）

????????????????6. 身份證號（二代身份證號均為18位，身份證中有X這樣的字符）

???????????????? 7. 入職時間（取值年月日即可）?

 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 '身份證號',entrydate date comment '入職時間') comment '員工表';

?1.2.3表操作-修改

1). 添加字段
ALTER TABLE 表名 ADD  字段名  類型 (長度)  [ COMMENT 注釋 ]  [ 約束 ];案例: 
為emp表增加一個新的字段”昵稱”為nickname，類型為varchar(20)
ALTER TABLE emp ADD nickname varchar(20)  COMMENT '昵稱';2). 修改數據類型
ALTER TABLE 表名 MODIFY  字段名  新數據類型 (長度);3). 修改字段名和字段類型
ALTER TABLE 表名 CHANGE  舊字段名  新字段名  類型 (長度)  [ COMMENT 注釋 ]  [ 約束 ];案例:
將emp表的nickname字段修改為username，類型為varchar(30)
ALTER TABLE emp CHANGE  nickname  username varchar(30)  COMMENT '昵稱';4). 刪除字段
ALTER TABLE 表名 DROP  字段名;案例:
將emp表的字段username刪除
ALTER TABLE emp DROP  username;5). 修改表名
ALTER TABLE 表名 RENAME TO  新表名;案例:
將emp表的表名修改為 employee
ALTER TABLE emp RENAME TO employee;

1.2.4表操作-刪除

1). 刪除表
DROP  TABLE [ IF  EXISTS ]  表名;可選項 IF EXISTS 代表，只有表名存在時才會刪除該表，表名不存在，則不執行刪除操作(如果不
加該參數項，刪除一張不存在的表，執行將會報錯)。案例:
如果tb_user表存在，則刪除tb_user表
DROP  TABLE IF  EXISTS  tb_user;2). 刪除指定表, 并重新創建表
TRUNCATE  TABLE 表名;注意: 在刪除表的時候，表中的全部數據也都會被刪除。

?(從下面開始使用DataGrip圖形化操作界面)

2.DML?

DML英文全稱是Data Manipulation Language(數據操作語言)，用來對數據庫中表的數據記錄進行增、刪、改操作。添加數據（INSERT）、修改數據（UPDATE）、?刪除數據（DELETE）?

2.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');2). 給全部字段添加數據
INSERT INTO 表名 VALUES (值1, 值2, ...);案例：插入數據到employee表，具體的SQL如下：
insert into employee values(2,'2','張無忌','男',18,'123456789012345670','2005-01-01');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');注意事項: ? 插入數據時，指定的字段順序需要與值的順序是一一對應的。? 字符串和日期型數據應該包含在引號中。? 插入的數據大小，應該在字段的規定范圍內。

?2.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';注意事項:修改語句的條件可以有，也可以沒有，如果沒有條件，則會修改整張表的所有數據。

2.3刪除數據?

刪除數據的具體語法為：
DELETE  FROM  表名  [ WHERE  條件 ] ;案例:A. 刪除gender為女的員工
delete from employee where gender = '女';B. 刪除所有員工
delete from employee;注意事項:? DELETE 語句的條件可以有，也可以沒有，如果沒有條件，則會刪除整張表的所有數
據。? DELETE 語句不能刪除某一個字段的值(可以使用UPDATE，將該字段值置為NULL即
可)。? 當進行刪除全部數據操作時，datagrip會提示我們，詢問是否確認刪除，我們直接點擊
Execute即可。

3.DQL

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

查詢關鍵字: SELECT

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

3.1基本語法以及執行順序

DQL 查詢語句，語法結構如下：SELECT字段列表
FROM表名列表
WHERE條件列表
GROUP  BY分組字段列表
HAVING分組后條件列表
ORDER BY排序字段列表
LIMIT分頁參數將上面的完整語法進行拆分，分為以下幾個部分：
基本查詢（不帶任何條件）
條件查詢（WHERE）
聚合函數（count、max、min、avg、sum）
分組查詢（group by）
排序查詢（order by）
分頁查詢（limit）

開始之前完成準備工作，代碼如下：

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', '柳巖', '女', 20, '123456789012345678', '北京', '2000-01-01'),(2, '00002', '張無忌', '男', 18, '123456789012345670', '北京', '2005-09-01'),(3, '00003', '韋一笑', '男', 38, '123456789712345670', '上海', '2005-08-01'),(4, '00004', '趙敏', '女', 18, '123456757123845670', '北京', '2009-12-01'),(5, '00005', '小昭', '女', 16, '123456769012345678', '上海', '2007-07-01'),(6, '00006', '楊逍', '男', 28, '12345678931234567X', '北京', '2006-01-01'),(7, '00007', '范瑤', '男', 40, '123456789212345670', '北京', '2005-05-01'),(8, '00008', '黛綺絲', '女', 38, '123456157123645670', '天津', '2015-05-01'),(9, '00009', '范涼涼', '女', 45, '123156789012345678', '北京', '2010-04-01'),(10, '00010', '陳友諒', '男', 53, '123456789012345670', '上海', '2011-01-01'),(11, '00011', '張士誠', '男', 55, '123567897123465670', '江蘇', '2015-05-01'),(12, '00012', '常遇春', '男', 32, '123446757152345670', '北京', '2004-02-01'),(13, '00013', '張三豐', '男', 88, '123656789012345678', '江蘇', '2020-11-01'),(14, '00014', '滅絕', '女', 65, '123456719012345670', '西安', '2019-05-01'),(15, '00015', '胡青牛', '男', 70, '12345674971234567X', '西安', '2018-04-01'),(16, '00016', '周芷若', '女', 18, null, '北京', '2012-06-01');

3.2基礎查詢

1). 查詢多個字段SELECT   字段1, 字段2, 字段3 ...  FROM   表名 ; 1SELECT  *  FROM   表名 ;
注意 : * 號代表查詢所有字段，在實際開發中盡量少用（不直觀、影響效率）。2). 字段設置別名
SELECT   字段1  [ AS  別名1 ] , 字段2  [ AS  別名2 ]   ...  FROM   表名; 1
SELECT   字段1  [ 別名1 ] , 字段2  [ 別名2 ]   ...  FROM   表名;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; 1
select * from emp;C. 查詢所有員工的工作地址,起別名
select workaddress as '工作地址' from emp; 
-- as可以省略
select workaddress '工作地址' from emp;D. 查詢公司員工的上班地址有哪些(不要重復)
select distinct workaddress '工作地址' from emp;

3.3 條件查詢

1). 語法

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

2). 條件常用的比較運算符如下:

3）常用的邏輯運算符如下:

4）案例

案例: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';   --17個下劃線

3.4聚合函數?

1). 介紹

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

2). 常見的聚合函數如下

?3). 語法

SELECT  聚合函數(字段列表)  FROM   表名 ;注意 : NULL值是不參與所有聚合函數運算的。

4）案例

A. 統計該企業員工數量select count(*) from emp; -- 統計的是總記錄數
select count(idcard) from emp; -- 統計的是idcard字段不為null的記錄數對于count聚合函數，統計符合條件的總記錄數，還可以通過 count(數字/字符串)的形式進行統計
查詢，比如：select count(1) from emp;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 = '西安';

3.5 分組查詢

1). 語法

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

2). where與having區別

????????執行時機不同：where是分組之前進行過濾，不滿足where條件，不參與分組；而having是分組之后對結果進行過濾。

????????判斷條件不同：where不能對聚合函數進行判斷，而having可以。

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

3）案例

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(*) address_count from emp where age < 45 group by 
workaddress having address_count >= 3; --分組之后在，在過濾大于等于3的工作地址D. 統計各個工作地址上班的男性及女性員工的數量
select workaddress, gender, count(*) '數量' from emp group by gender , workaddress ;

?3.6 排序查詢

1). 語法

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

2). 排序方式

 ASC : 升序(默認值)DESC: 降序注意事項：? 如果是升序, 可以不指定排序方式ASC ;? 如果是多字段排序，當第一個字段值相同時，才會根據第二個字段進行排序 ;

3）案例

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;

3.7分頁查詢

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

1). 語法

 SELECT  字段列表  FROM   表名  LIMIT  起始索引, 查詢記錄數 ;注意事項:? 起始索引從0開始，起始索引 = （查詢頁碼 - 1）* 每頁顯示記錄數。? 分頁查詢是數據庫的方言，不同的數據庫有不同的實現，MySQL中是LIMIT。? 如果查詢的是第一頁數據，起始索引可以省略，直接簡寫為 limit 10。

2）案例

A. 查詢第1頁員工數據, 每頁展示10條記錄select * from emp limit 0,10;
select * from emp limit 10;B. 查詢第2頁員工數據, 每頁展示10條記錄 --------> (頁碼-1)*頁展示記錄數
select * from emp limit 10,10;

4.DCL

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

4.1?管理用戶

1)基本語法

1). 查詢用戶
select * from mysql.user;2). 創建用戶
CREATE USER '用戶名'@'主機名' IDENTIFIED BY '密碼';3). 修改用戶密碼
ALTER USER '用戶名'@'主機名' IDENTIFIED WITH mysql_native_password BY '新密碼' ;4). 刪除用戶
DROP USER '用戶名'@'主機名' ; 注意事項:? 在MySQL中需要通過用戶名@主機名的方式，來唯一標識一個用戶。? 主機名可以使用 % 通配。? 這類SQL開發人員操作的比較少，主要是DBA（ Database Administrator 數據庫
管理員）使用。

2）案例

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';

4.2 權限控制

1）常見權限

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

?2）基本語法

1). 查詢權限
SHOW GRANTS FOR '用戶名'@'主機名' ;2). 授予權限
GRANT 權限列表 ON 數據庫名.表名 TO '用戶名'@'主機名';3). 撤銷權限
REVOKE 權限列表 ON 數據庫名.表名 FROM '用戶名'@'主機名';注意事項：
? 多個權限之間，使用逗號分隔
? 授權時， 數據庫名和表名可以使用 * 進行通配，代表所有。

3）案例

A. 查詢 'heima'@'%' 用戶的權限
show grants for 'heima'@'%';B. 授予 'heima'@'%' 用戶itcast數據庫所有表的所有操作權限
grant all on itcast.* to 'heima'@'%';C. 撤銷 'heima'@'%' 用戶的itcast數據庫的所有權限
revoke all on itcast.* from 'heima'@'%';