關于MySQL系列的一些常用知識點

下文內容主要給大家帶來關于MySQL系列的一些常用知識點，所講到的知識，與書籍不同，都是億速云專業技術人員在與用戶接觸過程中，總結出來的，具有一定的經驗分享價值，希望給廣大讀者帶來幫助。

1、SQL語句分類

DDL數據定義語言：create / drop / alter

DML數據操作語句：insert / delete /update / truncate

DQL數據查詢語言：select / show

2、數據約束

2.1、什么是數據約束

對表中的列值數據進行約束

2.2、默認值

作用： 當用戶對使用默認值的字段不插入值的時候，就使用默認值。

注意：對默認值字段可以插入null。

CREATE TABLE T_Persons(
	Id INT,
	NAME NVARCHAR(20),
	Gender NVARCHAR(2) DEFAULT '男'
)

2.3、非空

作用： 限制字段必須賦值

注意：1）非空字符必須賦值；2）非空字符不能賦null。

CREATE TABLE T_Persons(
	Id INT NOT NULL,
	NAME NVARCHAR(20),
	Gender NVARCHAR(2) 
)

2.4、唯一

作用： 對字段的值不能重復

注意：1）唯一字段可以插入null；2）唯一字段可以插入多個null

CREATE TABLE T_Persons(
	Id INT UNIQUE,
	NAME NVARCHAR(20),
	Gender NVARCHAR(2) 
)

2.5、主鍵

作用： 非空+唯一

注意：

1）通常情況下，每張表都會設置一個主鍵字段。用于標記表中的每條記錄的唯一性。

2）建議不要選擇表的包含業務含義的字段作為主鍵，建議給每張表獨立設計一個非業務含義的id字段。

CREATE TABLE T_Persons(
	Id INT PRIMARY KEY,
	NAME NVARCHAR(20),
	Gender NVARCHAR(2) 
)

2.6、自增長

作用： 自動遞增

CREATE TABLE T_Persons(
	Id INT AUTO_INCREMENT,
	NAME NVARCHAR(20),
	Gender NVARCHAR(2) 
)

------------------------------------------------------------

CREATE TABLE T_Persons(
	Id INT(4) ZEROFILL PRIMARY KEY AUTO_INCREMENT,-- 自增長，從0開始  ZEROFILL 零填充
	NAME NVARCHAR(20),
	Gender NVARCHAR(2) 
)


DELETE FROM T_Persons; -- 不能影響自增長約束
TRUNCATE TABLE T_Persons;-- 可以影響自增長約束

2.7、外鍵

作用：約束兩種表的數據

語法：CONSTRAINT 外鍵名 FOREIGN KEY(副表的外鍵字段) REFERENCES 主表(主表的主鍵)

-- 部門表（主表）
CREATE TABLE T_Department(
	Id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20)
)

-- 員工表（副表/從表）
CREATE TABLE T_Employee(
	Id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(10),
	DepartmentId INT,
	CONSTRAINT employee_department_fk FOREIGN KEY(DepartmentId) REFERENCES T_Department(Id)
)

注意：

1）被約束的表稱為副表，約束別人的表稱為主表，外鍵設置在副表上的！！！

2）主表的參考字段通常為主鍵！

3）添加數據： 先添加主表，再添加副表

4）修改數據： 先修改副表，再修改主表

5）刪除數據： 先刪除副表，再刪除主表

2.8、級聯操作

級聯修改： ON UPDATE CASCADE

級聯刪除： ON DELETE CASCADE

注意： 級聯操作必須在外鍵基礎上使用

CREATE TABLE T_Employee(
	Id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(10),
	DepartmentId INT,
	CONSTRAINT employee_department_fk FOREIGN KEY(DepartmentId) REFERENCES T_Department(Id) ON UPDATE CASCADE ON DELETE CASCADE
)

3、數據庫三大范式和MySQL變量

3.1、三大范式

設計原則： 建議設計的表盡量遵守三大范式。

第一范式： 要求表的每個字段必須是不可分割的獨立單元。

如果在T_Persons表中的name字段中存儲“李東華|李葉蛾”，就會違反第一范式。

如果在T_Persons表中的name字段中存儲“李東華”，在oldname字段中存儲“李葉蛾”，就會符合第一范式。

第二范式： 在第一范式的基礎上，要求每張表只表達一個意思。表的每個字段都和表的主鍵有依賴。

第三范式： 在第二范式基礎，要求每張表的主鍵之外的其他字段都只能和主鍵有直接決定依賴關系。

按照我個人理解：

第一范式，是對“列”進行約束，每個字段是不可分割的獨立單元；

第二范式，是對“表”進行約束，每張表只表達一個意思；

第三范式，是對“表與表之間的關系”進行約束，副表只能包含主表的primary key。

3.2、MySQL變量

MySQL變量包括：全局變量、會話變量、局部變量

全局變量（內置變量）：mysql數據庫內置的變量 （所有連接都起作用）

查看所有全局變量： show variables

查看某個全局變量： select @@變量名

修改全局變量： set 變量名=新值

全局變量中的兩個值：character_set_client和character_set_results。可以查詢SHOW VARIABLES LIKE 'character_%';

character_set_client: mysql服務器的接收數據的編碼

character_set_results：mysql服務器輸出數據的編碼

會話變量： 只存在于當前客戶端與數據庫服務器端的一次連接當中。如果連接斷開，那么會話變量全部丟失！

定義會話變量: set @變量=值

查看會話變量： select @變量

局部變量： 在存儲過程中使用的變量就叫局部變量。只要存儲過程執行完畢，局部變量就丟失！！

4、存儲過程

4.1、什么是存儲過程

存儲過程，是帶有邏輯的sql語句。

之前的sql沒有條件判斷，沒有循環；存儲過程帶上流程控制語句（if  while）。

4.2、存儲過程特點

1）執行效率非常快！存儲過程是在數據庫的服務器端執行的！！！

2）移植性很差！不同數據庫的存儲過程是不能移植。

4.3、存儲過程語法

語法：

刪除存儲過程：DROP PROCEDURE 存儲過程名稱;

參數：

IN：   表示輸入參數，可以攜帶數據帶存儲過程中

OUT： 表示輸出參數，可以從存儲過程中返回結果

INOUT： 表示輸入輸出參數，既可以輸入功能，也可以輸出功能

4.3.1、不帶參數的存儲過程

-- 創建存儲過程
DELIMITER $  -- 聲明存儲過程的結束符
CREATE PROCEDURE sp_findAll()
BEGIN
	SELECT * FROM T_Persons;
END $

-- 執行存儲過程
CALL sp_findAll(); -- CALL 存儲過程名稱(參數);

4.3.2、帶有輸入參數的存儲過程

-- 創建存儲過程
DELIMITER $
CREATE PROCEDURE sp_findById(IN pid INT)
BEGIN
	SELECT * FROM T_Persons WHERE Id=pid;
END $

-- 執行存儲過程
CALL sp_findById(2);

4.3.3、帶有輸出參數的存儲過程

-- 創建存儲過程
DELIMITER $
CREATE PROCEDURE sp_findCount(OUT str VARCHAR(20),OUT num INT)
BEGIN
	SET str = 'hello world';
	SELECT COUNT(Id) INTO num FROM T_Persons;
END $

-- 執行存儲過程
CALL sp_findCount(@str,@num);
-- 查看存儲過程的OUT類型的結果
SELECT @str,@num;

4.3.4、帶有輸入輸出參數的存儲過程

-- 創建存儲過程
DELIMITER $
CREATE PROCEDURE sp_testInOut(INOUT n INT)
BEGIN
	SELECT n;
	SET n = 500;
END $

-- 執行存儲過程
SET @n = 10;
CALL sp_testInOut(@n);
SELECT @n;

4.3.5、帶有條件判斷的存儲過程

-- 創建存儲過程
DELIMITER $
CREATE PROCEDURE sp_testIf(IN num INT,OUT str VARCHAR(20))
BEGIN
	IF num = 1 THEN
		SET str = '星期一'; -- 注意用SET進行賦值
	ELSEIF num = 2 THEN
		SET str = '星期二';
	ELSEIF num = 3 THEN
		SET str = '星期三';
	ELSE
		SET str = '輸入錯誤！';
	END IF;-- 注意這里有分號結尾
END $

-- 執行存儲過程
CALL sp_testIf(4,@str);
SELECT @str;

4.3.6、帶有循環功能的存儲過程

-- 創建存儲過程
DELIMITER $
CREATE PROCEDURE sp_testWhile(IN num INT,OUT result INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	DECLARE iResult INT DEFAULT 0;
	WHILE i <= num DO
		SET iResult = iResult + i;
		SET i = i + 1;
	END WHILE;
	SET result = iResult;
END $

-- 執行存儲過程
CALL sp_testWhile(100,@result);

SELECT @result;

4.3.7、使用查詢的結果賦值給變量（INTO）

-- 創建存儲過程
DELIMITER $
CREATE PROCEDURE sp_testINTO(IN pid INT,OUT vname VARCHAR(20))
BEGIN
	SELECT NAME INTO vname FROM T_Persons WHERE Id=pid;
END $

-- 執行存儲過程
CALL sp_testINTO(2,@vname);

SELECT @vname;

5、觸發器

當操作了某張表時，希望同時觸發一些動作/行為，可以使用觸發器完成！！

語法：

DELIMITER $
CREATE TRIGGER 觸發器的名字 AFTER INSERT/UPDATE/DELETE ON 表名 FOR EACH ROW
BEGIN
	-- SQL語句
END $

示例：

CREATE TABLE T_Logs(
	Id INT PRIMARY KEY AUTO_INCREMENT,
	Content VARCHAR(20) 
)

DELIMITER $
CREATE TRIGGER tri_Add AFTER INSERT ON T_Persons FOR EACH ROW
BEGIN
	INSERT INTO T_Logs(Content) VALUES('插入了一條數據');
END $	

6、MySQL用戶權限

MySQL數據庫的所有用戶都存儲在mysql.user表內

其中，root用戶擁有所有權限（可以干任何事情）；

而權限賬戶，只擁有部分權限（CURD）例如，只能操作某個數據庫的某張表

1）如何修改mysql用戶的密碼？

UPDATE USER SET PASSWORD=PASSWORD('root') WHERE USER='root';

在mysql中，密碼password是經md5加密的(單向加密)

USE mysql;
SELECT * FROM USER;

SELECT PASSWORD('root'); -- *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B

2）分配權限賬戶

GRANT 權限 ON 數據庫名.表名 TO '用戶名'@'IP地址' IDENTIFIED BY '密碼';

如果IP地址處用“%”，則表示本機和遠程都可以。

GRANT SELECT ON testdb.T_Persons TO 'rk'@'localhost' IDENTIFIED BY '123456';
GRANT DELETE ON testdb.T_Persons TO 'rk'@'localhost' IDENTIFIED BY '123456';

7、備份和恢復

7.1、備份

mysqldump -u root -p testdb > c:/bak.sql

7.2、恢復

mysql -u root -p testdb < c:/bak.sql

注意:不需要登錄

對于以上關于MySQL系列的一些常用知識點，如果大家還有更多需要了解的可以持續關注我們億速云的行業推新，如需獲取專業解答，可在官網聯系售前售后的，希望該文章可給大家帶來一定的知識更新。