關(guān)于MySQL系列的一些常用知識(shí)點(diǎn)

下文內(nèi)容主要給大家?guī)黻P(guān)于MySQL系列的一些常用知識(shí)點(diǎn)，所講到的知識(shí)，與書籍不同，都是億速云專業(yè)技術(shù)人員在與用戶接觸過程中，總結(jié)出來的，具有一定的經(jīng)驗(yàn)分享價(jià)值，希望給廣大讀者帶來幫助。

1、SQL語句分類

DDL數(shù)據(jù)定義語言：create / drop / alter

DML數(shù)據(jù)操作語句：insert / delete /update / truncate

DQL數(shù)據(jù)查詢語言：select / show

2、數(shù)據(jù)約束

2.1、什么是數(shù)據(jù)約束

對(duì)表中的列值數(shù)據(jù)進(jìn)行約束

2.2、默認(rèn)值

作用： 當(dāng)用戶對(duì)使用默認(rèn)值的字段不插入值的時(shí)候，就使用默認(rèn)值。

注意：對(duì)默認(rèn)值字段可以插入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、唯一

作用： 對(duì)字段的值不能重復(fù)

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

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

2.5、主鍵

作用： 非空+唯一

注意：

1）通常情況下，每張表都會(huì)設(shè)置一個(gè)主鍵字段。用于標(biāo)記表中的每條記錄的唯一性。

2）建議不要選擇表的包含業(yè)務(wù)含義的字段作為主鍵，建議給每張表獨(dú)立設(shè)計(jì)一個(gè)非業(yè)務(wù)含義的id字段。

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

2.6、自增長(zhǎng)

作用： 自動(dòng)遞增

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,-- 自增長(zhǎng)，從0開始  ZEROFILL 零填充
	NAME NVARCHAR(20),
	Gender NVARCHAR(2) 
)


DELETE FROM T_Persons; -- 不能影響自增長(zhǎng)約束
TRUNCATE TABLE T_Persons;-- 可以影響自增長(zhǎng)約束

2.7、外鍵

作用：約束兩種表的數(shù)據(jù)

語法：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）被約束的表稱為副表，約束別人的表稱為主表，外鍵設(shè)置在副表上的?。。?/p>

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

3）添加數(shù)據(jù)： 先添加主表，再添加副表

4）修改數(shù)據(jù)： 先修改副表，再修改主表

5）刪除數(shù)據(jù)： 先刪除副表，再刪除主表

2.8、級(jí)聯(lián)操作

級(jí)聯(lián)修改： ON UPDATE CASCADE

級(jí)聯(lián)刪除： ON DELETE CASCADE

注意： 級(jí)聯(lián)操作必須在外鍵基礎(chǔ)上使用

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、數(shù)據(jù)庫三大范式和MySQL變量

3.1、三大范式

設(shè)計(jì)原則： 建議設(shè)計(jì)的表盡量遵守三大范式。

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

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

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

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

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

按照我個(gè)人理解：

第一范式，是對(duì)“列”進(jìn)行約束，每個(gè)字段是不可分割的獨(dú)立單元；

第二范式，是對(duì)“表”進(jìn)行約束，每張表只表達(dá)一個(gè)意思；

第三范式，是對(duì)“表與表之間的關(guān)系”進(jìn)行約束，副表只能包含主表的primary key。

3.2、MySQL變量

MySQL變量包括：全局變量、會(huì)話變量、局部變量

全局變量（內(nèi)置變量）：mysql數(shù)據(jù)庫內(nèi)置的變量 （所有連接都起作用）

查看所有全局變量： show variables

查看某個(gè)全局變量： select @@變量名

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

全局變量中的兩個(gè)值：character_set_client和character_set_results?？梢圆樵僑HOW VARIABLES LIKE 'character_%';

character_set_client: mysql服務(wù)器的接收數(shù)據(jù)的編碼

character_set_results：mysql服務(wù)器輸出數(shù)據(jù)的編碼

會(huì)話變量： 只存在于當(dāng)前客戶端與數(shù)據(jù)庫服務(wù)器端的一次連接當(dāng)中。如果連接斷開，那么會(huì)話變量全部丟失！

定義會(huì)話變量: set @變量=值

查看會(huì)話變量： select @變量

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

4、存儲(chǔ)過程

4.1、什么是存儲(chǔ)過程

存儲(chǔ)過程，是帶有邏輯的sql語句。

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

4.2、存儲(chǔ)過程特點(diǎn)

1）執(zhí)行效率非常快！存儲(chǔ)過程是在數(shù)據(jù)庫的服務(wù)器端執(zhí)行的?。?！

2）移植性很差！不同數(shù)據(jù)庫的存儲(chǔ)過程是不能移植。

4.3、存儲(chǔ)過程語法

語法：

刪除存儲(chǔ)過程：DROP PROCEDURE 存儲(chǔ)過程名稱;

參數(shù)：

IN：   表示輸入?yún)?shù)，可以攜帶數(shù)據(jù)帶存儲(chǔ)過程中

OUT： 表示輸出參數(shù)，可以從存儲(chǔ)過程中返回結(jié)果

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

4.3.1、不帶參數(shù)的存儲(chǔ)過程

-- 創(chuàng)建存儲(chǔ)過程
DELIMITER $  -- 聲明存儲(chǔ)過程的結(jié)束符
CREATE PROCEDURE sp_findAll()
BEGIN
	SELECT * FROM T_Persons;
END $

-- 執(zhí)行存儲(chǔ)過程
CALL sp_findAll(); -- CALL 存儲(chǔ)過程名稱(參數(shù));

4.3.2、帶有輸入?yún)?shù)的存儲(chǔ)過程

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

-- 執(zhí)行存儲(chǔ)過程
CALL sp_findById(2);

4.3.3、帶有輸出參數(shù)的存儲(chǔ)過程

-- 創(chuàng)建存儲(chǔ)過程
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 $

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

4.3.4、帶有輸入輸出參數(shù)的存儲(chǔ)過程

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

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

4.3.5、帶有條件判斷的存儲(chǔ)過程

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

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

4.3.6、帶有循環(huán)功能的存儲(chǔ)過程

-- 創(chuàng)建存儲(chǔ)過程
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 $

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

SELECT @result;

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

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

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

SELECT @vname;

5、觸發(fā)器

當(dāng)操作了某張表時(shí)，希望同時(shí)觸發(fā)一些動(dòng)作/行為，可以使用觸發(fā)器完成??！

語法：

DELIMITER $
CREATE TRIGGER 觸發(fā)器的名字 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('插入了一條數(shù)據(jù)');
END $	

6、MySQL用戶權(quán)限

MySQL數(shù)據(jù)庫的所有用戶都存儲(chǔ)在mysql.user表內(nèi)

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

而權(quán)限賬戶，只擁有部分權(quán)限（CURD）例如，只能操作某個(gè)數(shù)據(jù)庫的某張表

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

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

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

USE mysql;
SELECT * FROM USER;

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

2）分配權(quán)限賬戶

GRANT 權(quán)限 ON 數(shù)據(jù)庫名.表名 TO '用戶名'@'IP地址' IDENTIFIED BY '密碼';

如果IP地址處用“%”，則表示本機(jī)和遠(yuǎn)程都可以。

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、備份和恢復(fù)

7.1、備份

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

7.2、恢復(fù)

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

注意:不需要登錄

對(duì)于以上關(guān)于MySQL系列的一些常用知識(shí)點(diǎn)，如果大家還有更多需要了解的可以持續(xù)關(guān)注我們億速云的行業(yè)推新，如需獲取專業(yè)解答，可在官網(wǎng)聯(lián)系售前售后的，希望該文章可給大家?guī)硪欢ǖ闹R(shí)更新。