Mysql基礎（二）

標籤：相關   roc   sid   開始   單表   mysql資料庫   ann   1.5   auto   

學習路線：資料約束-> 資料庫的設計過程-> 預存程序的相關知識-> 觸發器-> 許可權管理

(一)資料約束

1.1、預設值的設定

建立員工表emp 將預設地址設定為‘中國‘
mysql> create table emp(    -> id int,    -> name varchar(20),    -> address varchar(20) default ‘中國‘    -> );
預設地址可以為null，在沒有對該欄位插入值的時候，mysql會自動使用預設值對其進行賦值

 

1.2、非空限定

建立學生表student並且設定學生的姓名欄位不可為空，即姓名必須賦值且不能賦值為null
mysql> create table student(    -> sid int,    -> sname varchar(20) not null    -> );

 

1.3、唯一值限定

建立學生表，設定學生id是唯一的，唯一欄位可以插入一個或者多個null，此時mysql不報錯，但是如果有重複的編號將不能插入且報錯
mysql> create table student(    -> id int unique,    -> name varchar(10)    -> );

1.4、主鍵（非空且唯一）

設定員工的編號sid欄位為主鍵，非空且唯一的
mysql> create table emp(    -> sid int primary key,    -> sname varchar(20)    -> );
通常情況下表都會有一個主鍵，用來區別各個記錄，一般情況下用id

 

1.5、自增長 實現自動遞增（編號類）

建立學生表，並且將欄位sid設定為主鍵自增長，此時在對錶插入記錄的時候，sid可以不賦值，會自動遞增
mysql> create table student(    -> sid int primary key auto_increment,    -> sname varchar(20)    -> );
在刪除的時候，delete from不影響自增長約束，truncate table 會把自增長約束一起刪除

 

1.6、外鍵

作用：用來約束兩個表的資料，解決資料冗餘的問題

例如：有兩個表，一個是學生資訊表student(編號，姓名，所在院)，一個是學院資訊表coll(院系編號，院系名稱)

建立學院表
mysql> create table coll(    -> cid int primary key,    -> cname varchar(20)    -> );
建立學生表 部分欄位參照主表學院表

mysql> create table student(
-> sid int,
-> sname varchar(20),
-> collid int,
-> constraint stu_coll_fk foreign key(collid) references coll(cid)  );

　　　　       外鍵名稱                   外鍵               參照的表以及欄位

註：主表、副表之間的區別：

1、被約束的表為副表，約束別人的表為主表，外鍵是設定在副表上的

2、主表的參考欄位通用為主鍵

3、添加資料順序：主-》副

4、修改資料順序：副-》主

5、刪除資料順序：副-》主

 

外鍵的約束

-- 員工表

CREATE TABLE employee(

         id INT PRIMARY KEY,

         empName VARCHAR(20),

         deptName VARCHAR(20) -- 部門名稱);

INSERT INTO employee VALUES(1,‘張三‘,‘軟體開發部‘);

INSERT INTO employee VALUES(2,‘李四‘,‘軟體開發部‘);

INSERT INTO employee VALUES(3,‘王五‘,‘應用維護部‘);

SELECT * FROM employee;

-- 添加員工，部門名稱的資料冗餘高

INSERT INTO employee VALUES(4,‘陳六‘,‘軟體開發部‘);

-- 解決資料冗餘高的問題：給冗餘的欄位放到一張獨立表中

-- 獨立設計一張部門表

CREATE TABLE dept(

         id INT PRIMARY KEY,

         deptName VARCHAR(20)

)

DROP TABLE employee;

-- 修改員工表

CREATE TABLE employee(

         id INT PRIMARY KEY,

         empName VARCHAR(20),

         deptId INT,-- 把部門名稱改為部門ID

         -- 聲明一個外鍵約束

         CONSTRAINT emlyee_dept_fk FOREIGN KEY(deptId) REFERENCES dept(id) ON UPDATE CASCADE ON DELETE CASCADE  -- ON CASCADE UPDATE ：級聯修改

         --           外鍵名稱                  外鍵               參考表(參考欄位)

)

INSERT INTO dept(id,deptName) VALUES(1,‘軟體開發部‘);

INSERT INTO dept(id,deptName) VALUES(2,‘應用維護部‘);

INSERT INTO dept(id,deptName) VALUES(3,‘秘書部‘);

INSERT INTO employee VALUES(1,‘張三‘,1);

INSERT INTO employee VALUES(2,‘李四‘,1);

INSERT INTO employee VALUES(3,‘王五‘,2);

INSERT INTO employee VALUES(4,‘陳六‘,3);

 

-- 問題: 該記錄業務上不合法，員工插入了一個不存在的部門資料

INSERT INTO employee VALUES(5,‘陳六‘,4); -- 違反外鍵約束： Cannot add or update a child row: a foreign key constraint fails (`day16`.`employee`, CONSTRAINT `emlyee_dept_fk` FOREIGN KEY (`deptId`) REFERENCES `dept` (`id`))

 

-- 1）當有了外鍵約束，添加資料的順序： 先添加主表，再添加副表資料

-- 2）當有了外鍵約束，修改資料的順序： 先修改副表，再修改主表資料

-- 3）當有了外鍵約束，刪除資料的順序： 先刪除副表，再刪除主表資料

-- 修改部門(不能直接修改主表)

UPDATE dept SET id=4 WHERE id=3;

-- 先修改員工表

UPDATE employee SET deptId=2 WHERE id=4;

-- 刪除部門

DELETE FROM dept WHERE id=2;

-- 先刪除員工表

DELETE FROM employee WHERE deptId=2;

SELECT * FROM dept;

SELECT * FROM employee;

 

1.7、級聯操作

 

問題： 當有了外鍵約束的時候，必須先修改或刪除副表中的所有關聯資料，才能修改或刪除主表！但是，我們希望直接修改或刪除主表資料，從而影響副表資料。可以使用級聯操作實現！！！        級聯修改： ON UPDATE CASCADE        串聯刪除： ON DELETE CASCADE                    CREATE TABLE employee(    id INT PRIMARY KEY,    empName VARCHAR(20),    deptId INT,-- 把部門名稱改為部門ID    -- 聲明一個外鍵約束    CONSTRAINT emlyee_dept_fk FOREIGN KEY(deptId) REFERENCES dept(id) ON UPDATE CASCADE ON DELETE CASCADE  -- ON CASCADE UPDATE ：級聯修改    --           外鍵名稱                  外鍵               參考表(參考欄位))注意： 級聯操作必須在外鍵基礎上使用-- 級聯修改（修改）-- 直接修改部門UPDATE dept SET id=5 WHERE id=4;-- 串聯刪除-- 直接刪除部門 DELETE FROM dept WHERE id=1; 

(二)資料庫的設計

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

                   第一範式： 要求表的每個欄位必須是不可分割的獨立單元，即每個欄位都是最小的不可分的

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

                                               emp（員工）: 編號  姓名 部門   訂單  --違反第二範式

                                               員工表：編號  員工 部門名 

                                               訂單表：  訂單編號  訂單名稱             -- 符合第二範式                     

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

                                               員工表： 編號（主鍵） 姓名  部門編號  部門名 --符合第二範式，違反第三範式                                                                                                                                                             （資料冗餘高）

                                               員工表：員工編號（主鍵） 員工姓名  部門編號    --符合第三範式（降低資料冗餘）

                                               部門表：部門編號  部門名

(三)預存程序的相關知識

首先，預存程序類似於函數（方法），只是這裡面存放的是我們要實現的sql語句

預存程序的特點:執行效率高，它是在伺服器端執行的。缺點是移植性差。

文法:

mysql> delimiter $        -- 聲明預存程序的結束符mysql> create procedure test()　　-- 預存程序的名稱設為test    -> begin　　　　　　　　　　　　-- 開始    -> select *from student;  　　-- sql語句，可以寫多個sql語句放在這裡    -> end $　　　　　　　　　　　　-- 結束Query OK, 0 rows affected (0.24 sec)

-- 調用預存程序

mysql> call test();

-- 3.1 帶有輸入參數的預存程序
-- 需求：傳入一個員工的id，查詢員工資訊
DELIMITER $
CREATE PROCEDURE pro_findById(IN eid INT) -- IN: 輸入參數
BEGIN
SELECT * FROM employee WHERE id=eid;
END $

-- 調用
CALL pro_findById(4);

-- 3.2 帶有輸出參數的預存程序
DELIMITER $
CREATE PROCEDURE pro_testOut(OUT str VARCHAR(20)) -- OUT：輸出參數
BEGIN
-- 給參數賦值
SET str=‘helljava‘;
END $

-- 刪除預存程序
DROP PROCEDURE pro_testOut;
-- 調用
-- 如何接受返回參數的值？？
-- ***mysql的變數******
-- 全域變數（內建變數）：mysql資料庫內建的變數 （所有串連都起作用）
-- 查看所有全域變數： show variables
-- 查看某個全域變數： select @@變數名
-- 修改全域變數： set 變數名=新值
-- character_set_client: mysql伺服器的接收資料的編碼
-- character_set_results：mysql伺服器輸出資料的編碼

-- 會話變數： 只存在於當前用戶端與資料庫伺服器端的一次串連當中。如果串連斷開，那麼會話變數全部丟失！
-- 定義會話變數: set @變數=值
-- 查看會話變數： select @變數

-- 局部變數： 在預存程序中使用的變數就叫局部變數。只要預存程序執行完畢，局部變數就丟失！！

-- 1)定義一個會話變數name, 2)使用name會話變數接收預存程序的傳回值
CALL pro_testOut(@NAME);
-- 查看變數值
SELECT @NAME;

-- 3.3 帶有輸入輸出參數的預存程序
DELIMITER $
CREATE PROCEDURE pro_testInOut(INOUT n INT) -- INOUT： 輸入輸出參數
BEGIN
-- 查看變數
SELECT n;
SET n =500;
END $

-- 調用
SET @n=10;

CALL pro_testInOut(@n);

SELECT @n;

-- 3.4 帶有條件判斷的預存程序
-- 需求：輸入一個整數，如果1，則返回“星期一”,如果2，返回“星期二”,如果3，返回“星期三”。其他數字，返回“錯誤輸入”;
DELIMITER $
CREATE PROCEDURE pro_testIf(IN num INT,OUT str VARCHAR(20))
BEGIN
IF num=1 THEN
SET str=‘星期一‘;
ELSEIF num=2 THEN
SET str=‘星期二‘;
ELSEIF num=3 THEN
SET str=‘星期三‘;
ELSE
SET str=‘輸入錯誤‘;
END IF;
END $

CALL pro_testIf(4,@str);

SELECT @str;

-- 3.5 帶有迴圈功能的預存程序
-- 需求： 輸入一個整數，求和。例如，輸入100，統計1-100的和
DELIMITER $
CREATE PROCEDURE pro_testWhile(IN num INT,OUT result INT)
BEGIN
-- 定義一個局部變數
DECLARE i INT DEFAULT 1;
DECLARE vsum INT DEFAULT 0;
WHILE i<=num DO
SET vsum = vsum+i;
SET i=i+1;
END WHILE;
SET result=vsum;
END $

DROP PROCEDURE pro_testWhile;

CALL pro_testWhile(100,@result);

SELECT @result;

USE day16;

-- 3.6 使用查詢的結果賦值給變數（INTO）
DELIMITER $
CREATE PROCEDURE pro_findById2(IN eid INT,OUT vname VARCHAR(20) )
BEGIN
SELECT empName INTO vname FROM employee WHERE id=eid;
END $

CALL pro_findById2(1,@NAME);

SELECT @NAME;

(四)觸發器

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

-- 需求： 當向員工表插入一條記錄時，希望mysql自動同時往日誌表插入資料-- 建立觸發器(添加)CREATE TRIGGER tri_empAdd AFTER INSERT ON employee FOR EACH ROW    -- 當往員工表插入一條記錄時     INSERT INTO test_log(content) VALUES(‘員工表插入了一條記錄‘);     -- 插入資料INSERT INTO employee(id,empName,deptId) VALUES(7,‘紮古斯‘,1);INSERT INTO employee(id,empName,deptId) VALUES(8,‘紮古斯2‘,1);-- 建立觸發器(修改)CREATE TRIGGER tri_empUpd AFTER UPDATE ON employee FOR EACH ROW    -- 當往員工表修改一條記錄時     INSERT INTO test_log(content) VALUES(‘員工表修改了一條記錄‘);      -- 修改 UPDATE employee SET empName=‘eric‘ WHERE id=7; -- 建立觸發器(刪除)CREATE TRIGGER tri_empDel AFTER DELETE ON employee FOR EACH ROW    -- 當往員工表刪除一條記錄時     INSERT INTO test_log(content) VALUES(‘員工表刪除了一條記錄‘);   -- 刪除 DELETE FROM employee WHERE id=7;

 

(五)許可權管理

-- mysql資料庫許可權問題：root ：擁有所有許可權（可以幹任何事情） -- 許可權賬戶，只擁有部分許可權（CURD）例如，只能操作某個資料庫的某張表 -- 如何修改mysql的使用者密碼？ -- password: md5加密函數(單向加密) SELECT PASSWORD(‘root‘); -- *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B --  mysql資料庫，使用者配置 : user表USE mysql;SELECT * FROM USER;-- 修改密碼UPDATE USER SET PASSWORD=PASSWORD(‘123456‘) WHERE USER=‘root‘;-- 分配許可權賬戶GRANT SELECT ON isole.emp TO ‘isole‘@‘localhost‘ IDENTIFIED BY ‘123456‘;GRANT DELETE ON isole.emp TO ‘isole‘@‘localhost‘ IDENTIFIED BY ‘123456‘;

 

Mysql基礎（二）