一、CRUD

CRUD操作是指對數(shù)據(jù)庫進(jìn)行創(chuàng)建（Create）、讀取（Read）、更新（Update）和刪除（Delete）等操作的過程。

1.1 數(shù)據(jù)庫操作

創(chuàng)建、選擇、刪除數(shù)據(jù)庫

# 創(chuàng)建
CREATE DATABASE database_name DEFAULT CHARACTER SET utf8;
# 選擇
USE database_name;
# 查看數(shù)據(jù)庫
SHOW database_name;
# 刪除
DROP DATABASE database_name;

1.2 表操作

1.2.1 五大約束

1）非空約束（Not Null）
非空約束用于限制列中的值不能為空。這樣可以確保表中的特定列不包含空值。

2）自增約束（auto_increment）
可以指定初始值，沒有指定默認(rèn)是0；插入節(jié)點的時候會自增。

3）唯一約束（Unique）
唯一約束用于確保表中的某個列或一組列的取值是唯一的，但可以包含空值。一個表可以有多個唯一約束。

4）主鍵約束（Primary Key ）：
主鍵約束用于標(biāo)識表中的唯一記錄。一個表只能有一個主鍵，主鍵列不能包含重復(fù)值或空值。

5）外鍵約束（Foreign Key ）
外鍵約束用于維護(hù)表之間的關(guān)聯(lián)關(guān)系。它定義了一個或多個列與另一個表的主鍵或唯一鍵之間的關(guān)系。外鍵約束可以防止無效引用和維護(hù)數(shù)據(jù)的完整性。

1.2.2 創(chuàng)建表

# 創(chuàng)建表
CREATE TABLE table_name (column_name column_type);
# 顯示表的創(chuàng)建過程
SHOW CREATE TABLE table_name ;
# 獲取指定表的結(jié)構(gòu)信息
DESC table_name ；

例子

CREATE TABLE IF NOT EXISTS `schedule` (`id` INT AUTO_INCREMENT COMMENT '編號',`course` VARCHAR(100) NOT NULL COMMENT '課程',`teacher` VARCHAR(40) NOT NULL COMMENT '講師',PRIMARY KEY (`id`)
) ENGINE=innoDB DEFAULT CHARSET=utf8 COMMENT = '課程表';SELECT * FROM `schedule`;

NOT NULL表示輸入字段不能為NULL，ENGINE指定引擎，DEFAULT CHARSET指定默認(rèn)編碼方式

1.2.3 修改表

# ALTER 修改表
# 添加列:table_name是要修改的表名，column_name是要添加的列名，datatype是列的數(shù)據(jù)類型
ALTER TABLE table_name ADD column_name datatype;
# 修改列
ALTER TABLE table_name MODIFY column_name datatype;
# 刪除列
ALTER TABLE table_name DROP column_name;

例子

ALTER TABLE `schedule` ADD `begin_time` DATE DEFAULT '2022-10-24';
# 用于修改表schedule中的列begin_time的數(shù)據(jù)類型為DATE，并通過AFTER指定其放置在列id之后。
ALTER TABLE `schedule` MODIFY `begin_time` DATE AFTER `id`;  
ALTER TABLE `schedule` DROP `begin_time`;

1.2.3 刪除表

# 刪除表
DROP TABLE table_name ;
# 截斷表
TRUNCATE TABLE table_name;
# 清空表
DELETE FROM table_name;

1.2.4 表數(shù)據(jù)的增刪改查

# 插入數(shù)據(jù)
INSERT INTO table_name ( field1, field2,...fieldN )VALUES( value1, value2,...valueN );
# 刪除數(shù)據(jù)
DELETE FROM table_name [WHERE condition];# 修改數(shù)據(jù)
UPDATE tableName SET field=new_value [WHERE condition]# 查詢數(shù)據(jù)
SELECT field... FROM table_name [WHERE condition]

1.2.5 去重方式

1）GROUP BY
GROUP BY 是一個在 SQL 查詢中使用的子句，用于將結(jié)果集按照一個或多個列進(jìn)行分組，并對每個分組執(zhí)行聚合函數(shù)計算。

SELECT 列1, 列2, ... 
FROM 表名
WHERE 條件
GROUP BY 列1, 列2, ...

例子，假設(shè)有一張名為 orders 的表，包含以下幾個列：order_id, customer_id, product_id, 和 order_amount。我們想要按照 customer_id 進(jìn)行分組，并計算每個客戶的訂單總金額?？梢允褂?GROUP BY 來實現(xiàn)這個目標(biāo)：

SELECT customer_id, SUM(order_amount) as total_amount
FROM orders
GROUP BY customer_id

查詢結(jié)果將按照 customer_id 進(jìn)行分組，并計算每個客戶的訂單總金額。SUM() 函數(shù)用于求和 order_amount 列的值。示例輸出：

+-------------+--------------+
| customer_id | total_amount |
+-------------+--------------+
|     1       |    1500      |
|     2       |    2500      |
|     3       |    1800      |
+-------------+--------------+

2）SELECT DISTINCT
SELECT DISTINCT 語句用于檢索指定列中的唯一值，即去除重復(fù)的值。它返回列中不同的值，每個值只出現(xiàn)一次。

SELECT DISTINCT 列
FROM 表名;

例如，假設(shè)有一張名為 employees 的表，包含以下幾個列：employee_id, first_name, last_name, 和 department。我們希望獲取所有不同的部門名稱?？梢允褂?SELECT DISTINCT 來實現(xiàn)這個目標(biāo)：

SELECT DISTINCT department
FROM employees;

查詢結(jié)果將返回 employees 表中去重后的部門名稱。示例輸出：

+---------------+
|  department   |
+---------------+
|   Sales       |
|   Marketing   |
|   HR          |
|   Finance     |
+---------------+

二、高級查詢

結(jié)合實例，介紹高級查詢

SET FOREIGN_KEY_CHECKS=0;-- ----------------------------
-- Table structure for class
-- ----------------------------
DROP TABLE IF EXISTS `class`;
CREATE TABLE `class` (`cid` int(11) NOT NULL AUTO_INCREMENT,`caption` varchar(32) NOT NULL,PRIMARY KEY (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;-- ----------------------------
-- Records of class
-- ----------------------------
INSERT INTO `class` VALUES ('1', '1908班');
INSERT INTO `class` VALUES ('2', '2004班');
INSERT INTO `class` VALUES ('3', '2101班');
INSERT INTO `class` VALUES ('4', '2109班');-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (`cid` int(11) NOT NULL AUTO_INCREMENT,`cname` varchar(32) NOT NULL,`teacher_id` int(11) NOT NULL,PRIMARY KEY (`cid`),KEY `fk_course_teacher` (`teacher_id`),CONSTRAINT `fk_course_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('1', '數(shù)學(xué)', '1');
INSERT INTO `course` VALUES ('2', '語文', '2');
INSERT INTO `course` VALUES ('3', '英語', '4');
INSERT INTO `course` VALUES ('4', '理綜', '2');
INSERT INTO `course` VALUES ('5', '文綜', '1');-- ----------------------------
-- Table structure for score
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (`sid` int(11) NOT NULL AUTO_INCREMENT,`student_id` int(11) NOT NULL,`course_id` int(11) NOT NULL,`num` int(11) NOT NULL,PRIMARY KEY (`sid`),KEY `fk_score_student` (`student_id`),KEY `fk_score_course` (`course_id`),CONSTRAINT `fk_score_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`cid`),CONSTRAINT `fk_score_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`)
) ENGINE=InnoDB AUTO_INCREMENT=58 DEFAULT CHARSET=utf8;-- ----------------------------
-- Records of score
-- ----------------------------
INSERT INTO `score` VALUES ('1', '1', '1', '10');
INSERT INTO `score` VALUES ('2', '1', '2', '59');
INSERT INTO `score` VALUES ('3', '4', '5', '100');
INSERT INTO `score` VALUES ('4', '5', '4', '67');
INSERT INTO `score` VALUES ('5', '2', '2', '66');
INSERT INTO `score` VALUES ('6', '2', '1', '58');
INSERT INTO `score` VALUES ('8', '2', '3', '68');
INSERT INTO `score` VALUES ('9', '2', '4', '99');
INSERT INTO `score` VALUES ('10', '3', '1', '77');
INSERT INTO `score` VALUES ('11', '3', '2', '66');
INSERT INTO `score` VALUES ('12', '3', '3', '87');
INSERT INTO `score` VALUES ('13', '3', '4', '99');
INSERT INTO `score` VALUES ('14', '4', '1', '79');
INSERT INTO `score` VALUES ('15', '4', '2', '11');
INSERT INTO `score` VALUES ('16', '4', '3', '67');
INSERT INTO `score` VALUES ('17', '4', '4', '100');
INSERT INTO `score` VALUES ('18', '5', '1', '79');
INSERT INTO `score` VALUES ('19', '5', '2', '11');
INSERT INTO `score` VALUES ('20', '5', '3', '67');
INSERT INTO `score` VALUES ('21', '5', '5', '100');
INSERT INTO `score` VALUES ('22', '6', '1', '9');
INSERT INTO `score` VALUES ('23', '6', '2', '100');
INSERT INTO `score` VALUES ('24', '6', '3', '67');
INSERT INTO `score` VALUES ('25', '6', '4', '100');
INSERT INTO `score` VALUES ('26', '7', '1', '9');
INSERT INTO `score` VALUES ('27', '7', '2', '100');
INSERT INTO `score` VALUES ('28', '7', '3', '67');
INSERT INTO `score` VALUES ('29', '7', '4', '88');
INSERT INTO `score` VALUES ('30', '8', '1', '49');
INSERT INTO `score` VALUES ('31', '8', '2', '100');
INSERT INTO `score` VALUES ('32', '8', '3', '67');
INSERT INTO `score` VALUES ('33', '8', '4', '88');
INSERT INTO `score` VALUES ('34', '9', '1', '91');
INSERT INTO `score` VALUES ('35', '9', '2', '88');
INSERT INTO `score` VALUES ('36', '9', '5', '67');
INSERT INTO `score` VALUES ('37', '9', '4', '22');
INSERT INTO `score` VALUES ('38', '10', '1', '90');
INSERT INTO `score` VALUES ('39', '10', '2', '77');
INSERT INTO `score` VALUES ('40', '10', '3', '43');
INSERT INTO `score` VALUES ('41', '10', '4', '87');
INSERT INTO `score` VALUES ('42', '11', '1', '90');
INSERT INTO `score` VALUES ('43', '11', '2', '77');
INSERT INTO `score` VALUES ('44', '11', '5', '43');
INSERT INTO `score` VALUES ('45', '11', '4', '87');
INSERT INTO `score` VALUES ('46', '12', '1', '90');
INSERT INTO `score` VALUES ('47', '12', '2', '77');
INSERT INTO `score` VALUES ('48', '12', '3', '43');
INSERT INTO `score` VALUES ('49', '12', '5', '87');
INSERT INTO `score` VALUES ('50', '13', '3', '87');
INSERT INTO `score` VALUES ('51', '14', '2', '33');
INSERT INTO `score` VALUES ('52', '15', '3', '22');
INSERT INTO `score` VALUES ('53', '15', '5', '11');
INSERT INTO `score` VALUES ('54', '13', '1', '99');
INSERT INTO `score` VALUES ('55', '13', '2', '99');
INSERT INTO `score` VALUES ('56', '13', '4', '67');
INSERT INTO `score` VALUES ('57', '13', '5', '87');-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (`sid` int(11) NOT NULL AUTO_INCREMENT,`gender` char(1) NOT NULL,`class_id` int(11) NOT NULL,`sname` varchar(32) NOT NULL,PRIMARY KEY (`sid`),KEY `fk_class` (`class_id`),CONSTRAINT `fk_class` FOREIGN KEY (`class_id`) REFERENCES `class` (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('1', '男', '1', '羅誼');
INSERT INTO `student` VALUES ('2', '女', '1', '戴巧');
INSERT INTO `student` VALUES ('3', '男', '1', '葉黎');
INSERT INTO `student` VALUES ('4', '男', '3', '邵柴');
INSERT INTO `student` VALUES ('5', '女', '1', '韓琪');
INSERT INTO `student` VALUES ('6', '男', '3', '尹伸');
INSERT INTO `student` VALUES ('7', '女', '2', '孫燕');
INSERT INTO `student` VALUES ('8', '男', '2', '廖寬');
INSERT INTO `student` VALUES ('9', '男', '2', '孫行');
INSERT INTO `student` VALUES ('10', '女', '2', '宋賢');
INSERT INTO `student` VALUES ('11', '男', '2', '譚國興');
INSERT INTO `student` VALUES ('12', '女', '3', '于怡瑤');
INSERT INTO `student` VALUES ('13', '男', '4', '文樂逸');
INSERT INTO `student` VALUES ('14', '男', '4', '鄒樂和');
INSERT INTO `student` VALUES ('15', '女', '5', '鄧洋洋');
INSERT INTO `student` VALUES ('16', '男', '5', '秦永福');-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (`tid` int(11) NOT NULL AUTO_INCREMENT,`tname` varchar(32) NOT NULL,PRIMARY KEY (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES ('1', '王天一老師');
INSERT INTO `teacher` VALUES ('2', '謝小二老師');
INSERT INTO `teacher` VALUES ('3', '廖阿三老師');
INSERT INTO `teacher` VALUES ('4', '吳啟四老師');
INSERT INTO `teacher` VALUES ('5', '謝飛五老師');

2.1 基礎(chǔ)查詢

-- 全部查詢
SELECT * FROM student;
-- 只查詢部分字段
SELECT `sname`, `class_id` FROM student;
-- 別名 列明 不要用關(guān)鍵字
SELECT `sname` AS '姓名' , `class_id` AS '班級ID'
FROM student;
-- 把查詢出來的結(jié)果的重復(fù)記錄去掉
SELECT distinct `class_id` FROM student;

2.2 條件查詢

-- 查詢姓名為 鄧洋洋 的學(xué)生信息
SELECT * FROM `student` WHERE `name` = '鄧洋洋';
-- 查詢性別為 男，并且班級為 2 的學(xué)生信息
SELECT * FROM `student` WHERE `gender`="男" AND `class_id`=2;

2.3 范圍查詢

-- 查詢班級id 1 到 3 的學(xué)生的信息
SELECT * FROM `student` WHERE `class_id` BETWEEN 1 AND 3;

2.4 判空查詢

SELECT * FROM `student` WHERE `class_id` IS NOT NULL; #判斷不為空
SELECT * FROM `student` WHERE `class_id` IS NULL; #判斷為空
SELECT * FROM `student` WHERE `gender` <> ''; #判斷不為空字符串
SELECT * FROM `student` WHERE `gender` = ''; #判斷為空字符串

2.5 模糊查詢

使用 LIKE 關(guān)鍵字，"%"代表任意數(shù)量的字符，”_”代表占位符

-- 查詢名字為 m 開頭的學(xué)生的信息
SELECT * FROM `teacher` WHERE `tname` LIKE '謝%';
-- 查詢姓名里第二個字為 小 的學(xué)生的信息
SELECT * FROM `teacher` WHERE `tname` LIKE '_小%';

2.6 分頁查詢

分頁查詢主要用于查看第N條 到 第M條的信息，通常和排序查詢一起使用
使用LIMIT 關(guān)鍵字，第一個參數(shù)表示從條記錄開始顯示，第二個參數(shù)表示要顯示的數(shù)目。表中默認(rèn)第一條記錄的參數(shù)為0。

-- 查詢第二條到第三條內(nèi)容
SELECT * FROM `student` LIMIT 1,2;

2.7 查詢后排序

ORDER BY field

# ASC：升序, DESC：降序
SELECT * FROM `score` ORDER BY `num` ASC;
-- 按照多個字段排序
SELECT * FROM `score` ORDER BY `course_id` DESC,`num` DESC;

2.8 聚合查詢

聚合函數(shù)	描述
sum()	計算某列的總和
avg()	計算某列的平均值
max()	計算某列的最大值
min()	計算某列的最小值
count()	計算某列的行數(shù)

SELECT sum(`num`) FROM `score`;
SELECT avg(`num`) FROM `score`;
SELECT max(`num`) FROM `score`;
SELECT min(`num`) FROM `score`;
SELECT count(`num`) FROM `score`;

2.9 分組查詢

-- 可以把查詢出來的結(jié)果根據(jù)某個條件來分組顯示
SELECT `gender` FROM `student` GROUP BY `gender`;
-- 分組加group_concat
SELECT `gender`, group_concat(`sname`) as name FROM `student` GROUP BY `gender`;
-- 分組加聚合
SELECT `gender`, count(*) as num FROM `student` GROUP BY `gender`;
-- 分組加條件
SELECT `gender`, count(*) as num FROM `student` GROUP BY `gender` HAVING num > 6;

2.10 聯(lián)表查詢

2.10.1 INNER JOIN
只取兩張表有對應(yīng)關(guān)系的記錄

SELECT
cid
FROM
`course`
INNER JOIN `teacher` ON course.teacher_id =
teacher.tid;

2.10.2 LEFT JOIN
在內(nèi)連接的基礎(chǔ)上保留左表沒有對應(yīng)關(guān)系的記錄

SELECT
course.cid
FROM
`course`
LEFT JOIN `teacher` ON course.teacher_id = teacher.tid;

2.10.3 RIGHT JOIN
在內(nèi)連接的基礎(chǔ)上保留右表沒有對應(yīng)關(guān)系的記錄

SELECT
course.cid
FROM
`course`
RIGHT JOIN `teacher` ON course.teacher_id =
teacher.tid;

2.11 子查詢/合并查詢

2.11.1 單行子查詢

SELECT * from course WHERE teacher_id = (SELECT tid FROM teacher WHERE tname = '謝小二老師')

2.11.2 多行子查詢
多行子查詢即返回多行記錄的子查詢.

IN 關(guān)鍵字：運算符可以檢測結(jié)果集中是否存在某個特定的值，如果檢測成功就執(zhí)行外部的查詢。

EXISTS 關(guān)鍵字：內(nèi)層查詢語句不返回查詢的記錄。而是返回一個真假值。如果內(nèi)層查詢語句查詢到滿足條件的記錄，就返回一個真值（ true ），否則，將返回一個假值（ false ）。當(dāng)返回的值為 true 時，外層查詢語句將進(jìn)行查詢；當(dāng)返回的為false時，外層查詢語句不進(jìn)行查詢或者查詢不出任何記錄。

ALL 關(guān)鍵字：表示滿足所有條件。使用 ALL 關(guān)鍵字時，只有滿足內(nèi)層查詢語句返回的所有結(jié)果，才可以執(zhí)行外層查詢語句。

ANY 關(guān)鍵字：允許創(chuàng)建一個表達(dá)式，對子查詢的返回值列表，進(jìn)行比較，只要滿足內(nèi)層子查詢中的，任意一個比較條件，就返回一個結(jié)果作為外層查詢條件。

在 FROM 子句中使用子查詢：子查詢出現(xiàn)在 from 子句中，這種情況下將子查詢當(dāng)做一個臨時表使用。

select * from student where class_id in (select cid from course where teacher_id = 2);select * from student where exists(select cid from course where cid = 5);select student_id,sname 
FROM (SELECT * FROM score WHERE course_id = 5 OR course_id = 2) AS A 
LEFT JOIN student ON A.student_id = student.sid;

三、視圖

視圖 view 不是表，是一種虛表，沒有實體，并不實際存儲數(shù)據(jù)，其內(nèi)容由查詢 select 定義。用來創(chuàng)建視圖的表稱為基表，通過視圖，可以展現(xiàn)基表的部分?jǐn)?shù)據(jù)。

視圖的優(yōu)點：
1）簡單：使用視圖的用戶完全不需要關(guān)心后面對應(yīng)的表的結(jié)構(gòu)、關(guān)聯(lián)條件和篩選條件，對用戶來說已經(jīng)是過濾好的復(fù)合條件的結(jié)果集。

2）安全：使用視圖的用戶只能訪問他們被允許查詢的結(jié)果集，對表的權(quán)限管理并不能限制到某個行某個列，但是通過視圖就可以簡單的實現(xiàn)。

3）數(shù)據(jù)獨立：一旦視圖的結(jié)構(gòu)確定了，可以屏蔽表結(jié)構(gòu)變化對用戶的影響，源表增加列對視圖沒有影響；源表修改列名，則可以通過修改視圖來解決，不會造成對訪問者的影響。

CREATE VIEW 視圖名 AS SELECT 語句

例如，查詢 ‘?dāng)?shù)學(xué)’ 課程比 ‘語文’ 課程成績高的所有學(xué)生的學(xué)號

CREATE VIEW view_test1 AS SELECT A.student_id FROM 
((SELECT student_id,num FROM score WHERE course_id = (SELECT cid FROM course WHERE cname = '數(shù)學(xué)') ) AS AINNER JOIN(SELECT student_id,num FROM scoreWHERE course_id = (SELECT cid FROM course WHERE cname = '語文') ) AS BON A.student_id = B.student_id
)
WHERE A.num > B.num;	SELECT * FROM view_test1;

作用：

1）權(quán)限管理。可以隱藏表結(jié)構(gòu)；視圖的表結(jié)構(gòu)可以開放給用戶，但不會開放基表的表結(jié)構(gòu)。即某些表對用戶屏蔽，但是可以給該用戶通過視圖來對該表操作。

2）可復(fù)用。減少重復(fù)語句書寫；類似程序中函數(shù)的作用。

3）節(jié)約資源。一些列表的關(guān)聯(lián)查詢構(gòu)成的數(shù)據(jù)包比較大，而使用 select … from view構(gòu)成的數(shù)據(jù)包就小多了。

4）重構(gòu)利器。假如因為某種需求，需要將 user 拆成表 usera 和表 userb；如果應(yīng)用程序使用 sql 語句：select * from user那就會提示該表不存在；若此時創(chuàng)建視圖

create view user as select a.name,a.age,b.sex 
from usera as a, userb as b
where a.name=b.name; 

則只需要更改數(shù)據(jù)庫結(jié)構(gòu)，而不需要更改應(yīng)用程序。

5）邏輯更清晰，屏蔽查詢細(xì)節(jié)，關(guān)注數(shù)據(jù)返回。

四、觸發(fā)器

觸發(fā)器（trigger）是一種對表執(zhí)行某操作后會觸發(fā)執(zhí)行其他命令的機(jī)制。

4.1 要素

監(jiān)視對象：table
監(jiān)視事件：insert、update、delete
觸發(fā)時間：before ，after
觸發(fā)事件：insert、update、delete

4.2 語法

-- 創(chuàng)建觸發(fā)器
CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW
BEGINtrigger_body
END-- trigger_time: { BEFORE | AFTER }
-- trigger_event: { INSERT | UPDATE | DELETE }
-- trigger_order: { FOLLOWS | PRECEDES }
-- trigger_body：tbl_name表更新前(OLD, 列名)，表更新后(NEW, 列名)-- 確認(rèn)觸發(fā)器
SHOW TRIGGERS-- 刪除觸發(fā)器
SHOW TRIGGER trigger_name

4.3 例子

DELIMITER 是在 MySQL 數(shù)據(jù)庫中使用的一個關(guān)鍵字，用于指定 SQL 語句的分隔符。它的作用是告訴 MySQL 解析器在遇到指定的分隔符時，將整個語句作為一個整體進(jìn)行處理，而不會將其中的分號視為語句的結(jié)束。通常指定 $$ 或 ||

準(zhǔn)備

-- 創(chuàng)建觸發(fā)器
CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW
BEGINtrigger_body
END-- trigger_time: { BEFORE | AFTER }
-- trigger_event: { INSERT | UPDATE | DELETE }
-- trigger_order: { FOLLOWS | PRECEDES }
-- trigger_body：tbl_name表更新前(OLD, 列名)，表更新后(NEW, 列名)-- 確認(rèn)觸發(fā)器
SHOW TRIGGERS-- 刪除觸發(fā)器
SHOW TRIGGER trigger_name

DROP TABLE IF EXISTS `goods`;
DROP TABLE IF EXISTS `order`;CREATE TABLE `goods` (`gid` INT PRIMARY KEY auto_increment,`name` VARCHAR (32),`num` SMALLINT DEFAULT 0
);CREATE TABLE `order` (`id` INT PRIMARY KEY auto_increment,`gid` INT,`quantity` SMALLINT COMMENT '下單數(shù)量'
);DROP TRIGGER if EXISTS `trig_order_1`;
DROP TRIGGER if EXISTS `trig_order_2`;

需求1：客戶新建訂單購買的數(shù)量，商品表的庫存數(shù)量自動改變

delimiter // 
CREATE TRIGGER `trig_order_1` AFTER INSERT ON `order` FOR EACH ROW
BEGINUPDATE goods SET num = num - new.quantity WHERE gid = new.gid;
END //
delimiter ; -- 測試1：新建訂單
INSERT INTO `order` VALUES (NULL, 1, 2);
INSERT INTO `order` VALUES (NULL, 2, 2);
INSERT INTO `order` VALUES (NULL, 3, 2);SELECT * FROM `order`;

需求2：客戶修改訂單購買的數(shù)量，商品表的庫存數(shù)量自動改變

delimiter // EACH ROW
BEGINUPDATE goods SET num = num + old.quantity - new.quantity WHERE gid = new.gid;
END //
delimiter ; 
INSERT INTO `goods` VALUES (NULL, 'cat', 10);
INSERT INTO `goods` VALUES (NULL, 'dog', 10);
INSERT INTO `goods` VALUES (NULL, 'pig', 10);-- 測試2：修改訂單
UPDATE `order` SET quantity = quantity + 2 WHERE gid = 1;SELECT * FROM `goods`;

五、權(quán)限管理

5.1 創(chuàng)建用戶

CREATE USER username@host IDENTIFIED BY password; 

host 指定該用戶在哪個主機(jī)上可以登陸，如果是本地用戶可用localhost ，如果想讓該用戶可以從任意遠(yuǎn)程主機(jī)登陸，可以使用通配符 % ;

5.2 授權(quán)

5.2.1 對表授權(quán)

GRANT privileges ON database_name.table_name TO 'username'@'host' WITH GRANT OPTION;

privileges ：用戶的操作權(quán)限，如 SELECT ， INSERT ，UPDATE 等，如果要授予所的權(quán)限則使用ALL；

databasename.tablename 如果是.表示任意數(shù)據(jù)庫以及任意表；

WITH GRANT OPTION 這個選項表示該用戶可以將自己擁有的權(quán)限授權(quán)給別人。注意：經(jīng)常有人在創(chuàng)建操作用戶的時候不指定WITH GRANT OPTION 選項導(dǎo)致后來該用戶不能使用 GRANT 命令創(chuàng)建用戶或者給其它用戶授權(quán)。
如果不想這個用戶有這個 grant 的權(quán)限，則不要加該 WITHGRANT OPTION 選項；

5.2.2 對視圖授權(quán)

GRANT SELECT, SHOW VIEW ON database_name.view_name TO 'username'@'host';

5.2.3 刷新權(quán)限

-- 修改權(quán)限后需要刷新權(quán)限
FLUSH PRIVILEGES;