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

什么數(shù)據(jù)約束 : 對用戶操作表的數(shù)據(jù)進行約束

1. 默認值

• 作用： 當用戶對使用默認值的字段不插入值的時候士飒，就使用默認值弛作。
• 注意：
  1）對默認值字段插入null是可以的辜王。
  2）對默認值字段可以插入非null

CREATE TABLE student(
    id INT,
    NAME VARCHAR(20),
    address VARCHAR(20) DEFAULT '廣州天河'  -- 默認值
)
DROP TABLE student;
-- 當字段沒有插入值的時候田绑，mysql自動給該字段分配默認值
INSERT INTO student(id,NAME) VALUES(1,'張三');
-- 注意：默認值的字段允許為null
INSERT INTO student(id,NAME,address) VALUE(2,'李四',NULL);
INSERT INTO student(id,NAME,address) VALUE(3,'王五','廣州番禺');

2. 非空

• 作用： 限制字段必須賦值
• 注意：
  1）非空字符必須賦值
  2）非空字符不能賦null

-- 需求： gender字段必須有值（不為null）
CREATE TABLE student(
    id INT,
    NAME VARCHAR(20),
    gender VARCHAR(2) NOT NULL -- 非空
)
-- 非空字段必須賦值
INSERT INTO student(id,NAME) VALUES(1,'李四');
-- 非空字符不能插入null
INSERT INTO student(id,NAME,gender) VALUES(1,'李四',NULL);

3. 唯一

• 作用： 對字段的值不能重復
• 注意：
  1）唯一字段可以插入null
  2）唯一字段可以插入多個null

CREATE TABLE student(
    id INT UNIQUE, -- 唯一
    NAME VARCHAR(20)
)
INSERT INTO student(id,NAME) VALUES(1,'zs');
INSERT INTO student(id,NAME) VALUES(1,'lisi'); -- ERROR 1062 (23000): Duplicate entry '1' for key 'id'  
INSERT INTO student(id,NAME) VALUES(2,'lisi');

4. 主鍵

• 作用： 非空+唯一
• 注意：
  1）通常情況下, 每張表都會設置一個主鍵字段; 用于標記表中的每條記錄的唯一性
  2）建議不要選擇表的包含業(yè)務含義的字段作為主鍵兽愤，建議給每張表獨立設計一個非業(yè)務含義的id字段

DROP TABLE student;
CREATE TABLE student(
    id INT PRIMARY KEY, -- 主鍵
    NAME VARCHAR(20)
)
INSERT INTO student(id,NAME) VALUES(1,'張三');
INSERT INTO student(id,NAME) VALUES(2,'張三');
-- INSERT INTO student(id,NAME) VALUES(1,'李四'); -- 違反唯一約束： Duplicate entry '1' for key 'PRIMARY'
-- insert into student(name) value('李四'); -- 違反非空約束： ERROR 1048 (23000): Column 'id' cannot be null

5. 自增長 : 自動遞增

CREATE TABLE student(
    id INT(4) ZEROFILL PRIMARY KEY AUTO_INCREMENT, -- 自增長孕蝉，從0開始  ZEROFILL 零填充
    NAME VARCHAR(20)
)
-- 自增長字段可以不賦值屡律，自動遞增
INSERT INTO student(NAME) VALUES('張三');
INSERT INTO student(NAME) VALUES('李四');
INSERT INTO student(NAME) VALUES('王五');
SELECT * FROM student;
-- 不能影響自增長約束
DELETE FROM student;
-- 可以影響自增長約束
TRUNCATE TABLE student;

6. 外鍵 : 約束兩種表的數(shù)據(jù)

• 若果出現(xiàn)兩種表的情況:
  1> 解決數(shù)據(jù)冗余高問題 -> 獨立出一張表; 例如 : 員工表和部門表
  2> 使用外鍵約束：約束插入員工表的部門ID字段值
  • 問題出現(xiàn)：在插入員工表數(shù)據(jù)的時候，員工表的部門ID字段可以隨便插入昔驱！
  • 解決辦法： 在員工表的部門ID字段添加一個外鍵約束

-- 部門表（主表）
CREATE TABLE dept(
    id INT PRIMARY KEY,
    deptName VARCHAR(20)
)
-- 修改員工表（副表/從表）
CREATE TABLE employee(
    id INT PRIMARY KEY,
    empName VARCHAR(20),
    deptId INT,-- 把部門名稱改為部門ID
    -- 聲明一個外鍵約束
    CONSTRAINT emlyee_dept_fk FOREIGN KEY(deptId) REFERENCES dept(id)
    --           外鍵名稱                  外鍵               參考表(參考字段)
)

• 注意 :
  1）被約束的表稱為副表疹尾，約束別人的表稱為主表上忍，外鍵設置在副表上的
  2）主表的參考字段通用為主鍵骤肛！
  3）添加數(shù)據(jù) : 先添加主表，再添加副表
  4）修改數(shù)據(jù) : 先修改副表窍蓝，再修改主表
  5）刪除數(shù)據(jù) : 先刪除副表腋颠，再刪除主表

級聯(lián)操作

• 問題 : 當有了外鍵約束的時候，必須先修改或刪除副表中的所有關聯(lián)數(shù)據(jù)吓笙，才能修改或刪除主表淑玫！但是，我們希望直接修改或刪除主表數(shù)據(jù)面睛，從而影響副表數(shù)據(jù)絮蒿。可以使用級聯(lián)操作實現(xiàn)叁鉴！

• 級聯(lián)操作 :

  • 級聯(lián)修改： ON UPDATE CASCADE
  • 級聯(lián)刪除： 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 ：級聯(lián)修改
    --           外鍵名稱                  外鍵               參考表(參考字段)
)
注意： 級聯(lián)操作必須在外鍵基礎上使用

數(shù)據(jù)庫設計

1. 引入

• 需求分析 - 需求分析師 -> 原始需求- > 抽取業(yè)務模型
  • 圖書模型：圖書名稱土涝，版本號，作者
  • 學生模型： 學號幌墓，學生姓名 手機號碼
  • ......
  • 角色：學生 老師但壮，圖書管理員
  • <需求說明書>

2. 需求設計

• 概要設計 :
  • 抽取實體 : 業(yè)務模型 -> 實體模型(java 類 c++類) 內(nèi)存class Book {name, bookNo, author}
  • 數(shù)據(jù)庫設計 : 業(yè)務模型/實體模型 - > 數(shù)據(jù)模型 (硬盤)
    • 數(shù)據(jù)庫表設計
• 詳細設計 : 類詳細，屬性和方法

三大范式

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

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

  • student : name -> 張小名|狗娃 -> 違反第一范式
  • sutdent : name, old_name -> 張小名, 狗娃 -> 符合第一范式

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

  • employee（員工）: 員工編號 員工姓名 部門名稱 訂單名稱-> 違反第二范式
  • 員工表 : 員工編號 員工姓名 部門名稱, 訂單表： 訂單編號 訂單名稱 -> 符合第二范式

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

  • 員工表 : 員工編號(主鍵), 員工姓名, 部門編號, 部門名-> 符合第二范式，違反第三范式(數(shù)據(jù)冗余高）
  • 員工表 : 員工編號(主鍵), 員工姓名, 部門編號, 部門表：部門編號, 部門名 -> 符合第三范式(降低數(shù)據(jù)冗余)

關聯(lián)查詢(多表查詢)

• 需求：查詢員工及其所在部門(顯示員工姓名舞肆，部門名稱)

  1. 交叉連接查詢（不推薦焦辅。產(chǎn)生笛卡爾乘積現(xiàn)象：4 * 4=16，有些是重復記錄） : SELECT empName,deptName FROM employee,dept;
  • 多表查詢規(guī)則：
    1）確定查詢哪些表
    2）確定哪些哪些字段
    3）表與表之間連接條件 (規(guī)律：連接條件數(shù)量是表數(shù)量-1)
  2. 內(nèi)連接查詢：只有滿足條件的結果才會顯示(使用最頻繁)

SELECT empName,deptName       -- 2）確定哪些哪些字段
    FROM employee,dept    -- 1）確定查詢哪些表
    WHERE employee.deptId=dept.id  -- 3）表與表之間連接條件

- 內(nèi)連接的另一種語法 : 

SELECT empName,deptName
    FROM employee
    INNER JOIN dept
    ON employee.deptId=dept.id;

- 使用別名

SELECT e.empName,d.deptName
    FROM employee e
    INNER JOIN dept d
    ON e.deptId=d.id;

• 需求： 查詢每個部門的員工

  • 預期結果：
    -- 軟件開發(fā)部 張三
    -- 軟件開發(fā)部 李四
    -- 應用維護部 王五
    -- 秘書部 陳六
    -- 總經(jīng)辦 null

• 左[外]連接查詢： 使用左邊表的數(shù)據(jù)去匹配右邊表的數(shù)據(jù)胆绊，如果符合連接條件的結果則顯示氨鹏，如果不符合連接條件則顯示null(注意： 左外連接：左表的數(shù)據(jù)一定會完成顯示)

SELECT d.deptName,e.empName
    FROM dept d
    LEFT OUTER JOIN employee e
    ON d.id=e.deptId;

• 右[外]連接查詢: 使用右邊表的數(shù)據(jù)去匹配左邊表的數(shù)據(jù)，如果符合連接條件的結果則顯示压状，如果不符合連接條件則顯示null(注意： 右外連接：右表的數(shù)據(jù)一定會完成顯示)

SELECT d.deptName,e.empName
    FROM employee e
    RIGHT OUTER JOIN dept d
    ON d.id=e.deptId;

• 自連接查詢 :
  • 需求：查詢員工及其上司
  • 預期結果：
    -- 張三 null
    -- 李四 張三
    -- 王五 李四
    -- 陳六 王五

SELECT e.empName,b.empName
    FROM employee e 
    LEFT OUTER JOIN employee b
    ON e.bossId=b.id;

存儲過程

1. 什么是存儲過程 : 存儲過程就是帶有邏輯的sql語句, 存儲過程帶上流程控制語句(if, for, while)
2. 存儲過程特點
   1）執(zhí)行效率非称偷郑快跟继！存儲過程是在數(shù)據(jù)庫的服務器端執(zhí)行的！
   2）移植性很差镣丑！不同數(shù)據(jù)庫的存儲過程是不能移植舔糖。
3. 存儲過程語法

-- 創(chuàng)建存儲過程
DELIMITER $                          -- 聲明存儲過程的結束符
CREATE PROCEDURE pro_test()          --存儲過程名稱(參數(shù)列表)
BEGIN                                -- 開始
    -- 可以寫多個sql語句;              -- sql語句+流程控制
    SELECT * FROM employee;
END $                                -- 結束 結束符

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

參數(shù)：
IN : 表示輸入?yún)?shù)，可以攜帶數(shù)據(jù)帶存儲過程中
OUT : 表示輸出參數(shù)莺匠，可以從存儲過程中返回結果
INOUT : 表示輸入輸出參數(shù)金吗，既可以輸入功能，也可以輸出功能

• 代碼示例:
  1. 示例 :

-- 聲明結束符
-- 創(chuàng)建存儲過程
DELIMITER $
CREATE PROCEDURE pro_test()
BEGIN
    -- 可以寫多個sql語句;
    SELECT * FROM employee;
END $
-- 執(zhí)行存儲過程
CALL pro_test();

2. 帶有輸入?yún)?shù)的存儲過程(傳入一個員工的id趣竣，查詢員工信息)

DELIMITER $
CREATE PROCEDURE pro_findById(IN eid INT)  -- IN: 輸入?yún)?shù)
BEGIN
    SELECT * FROM employee WHERE id=eid;
END $ 
-- 調(diào)用
CALL pro_findById(4);

3. 帶有輸出參數(shù)的存儲過程

DELIMITER $
CREATE PROCEDURE pro_testOut(OUT str VARCHAR(20))  -- OUT：輸出參數(shù)
BEGIN
        -- 給參數(shù)賦值
    SET str='hello_Java';
END $

4. 刪除存儲過程 : DROP PROCEDURE pro_testOut;
5. 帶有輸入輸出參數(shù)的存儲過程 :

DELIMITER $
CREATE PROCEDURE pro_testInOut(INOUT n INT)  -- INOUT： 輸入輸出參數(shù)
BEGIN
   -- 查看變量
   SELECT n;
   SET n =500;
END $
-- 調(diào)用
SET @n=10;
CALL pro_testInOut(@n);
SELECT @n;

6. 帶有條件判斷的存儲過程 : 輸入一個整數(shù): 如果是1則返回“星期一”, 如果2, 返回“星期二”, 如果3, 返回“星期三”摇庙。其他數(shù)字，返回“錯誤輸入”;

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;

7. 帶有循環(huán)功能的存儲過程 (輸入一個整數(shù)遥缕，求和卫袒。例如，輸入100单匣，統(tǒng)計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;

8. 使用查詢的結果賦值給變量（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的變量

  • 全局變量 (內(nèi)置變量) : mysql數(shù)據(jù)庫內(nèi)置的變量 (所有連接都起作用)
    • 查看所有全局變量 : show variables;
    • 查看某個全局變量 : select @@變量名
    • 修改全局變量 : set 變量名=新值
    • character_set_client : mysql服務器的接收數(shù)據(jù)的編碼
    • character_set_results : mysql服務器輸出數(shù)據(jù)的編碼
  • 會話變量 : 只存在于當前客戶端與數(shù)據(jù)庫服務器端的一次連接當中夕凝。如果連接斷開，那么會話變量全部丟失
    • 定義會話變量 : set @變量=值
    • 查看會話變量 : select @變量
  • 局部變量 : 在存儲過程中使用的變量就叫局部變量户秤。只要存儲過程執(zhí)行完畢码秉，局部變量就丟失！
  • 示例 :
    1. 定義一個會話變量name
    2. 使用name會話變量接收存儲過程的返回值 : CALL pro_testOut(@NAME);
    3. 查看變量值 : SELECT @NAME;

• 練習 : 編寫一個存儲過程, 如果學生的英語平均分小于等于70分鸡号，則輸出'一般'; 如果學生的英語平均分大于70分转砖，且小于等于90分，則輸出‘良好’; 如果學生的英語平均分大于90分膜蠢，則輸出‘優(yōu)秀’

DELIMITER $
CREATE PROCEDURE pro_testAvg(OUT str VARCHAR(20))
BEGIN 
      -- 定義局部變量堪藐，接收平均分
      DECLARE savg DOUBLE;
      -- 計算英語平方分
      SELECT AVG(english) INTO savg FROM student2;
      IF savg<=70 THEN
           SET str='一般';
      ELSEIF savg>70 AND savg<=90 THEN
           SET str='良好';
      ELSE
       SET str='優(yōu)秀';
      END IF;
END $
CALL pro_testAvg(@str);
SELECT @str;

觸發(fā)器

1. 觸發(fā)器作用 : 當操作了某張表時，希望同時觸發(fā)一些動作/行為挑围，可以使用觸發(fā)器完成礁竞！
   例如 : 當向員工表插入一條記錄時，希望同時往日志表插入數(shù)據(jù)

-- 觸發(fā)器
SELECT * FROM employee;

-- 日志表
CREATE TABLE test_log(
    id INT PRIMARY KEY AUTO_INCREMENT,
    content VARCHAR(100)
)

-- 需求： 當向員工表插入一條記錄時杉辙，希望mysql自動同時往日志表插入數(shù)據(jù)
-- 創(chuàng)建觸發(fā)器(添加)
CREATE TRIGGER tri_empAdd AFTER INSERT ON employee FOR EACH ROW    -- 當往員工表插入一條記錄時
     INSERT INTO test_log(content) VALUES('員工表插入了一條記錄');
     
-- 插入數(shù)據(jù)
INSERT INTO employee(id,empName,deptId) VALUES(7,'扎古斯',1);
INSERT INTO employee(id,empName,deptId) VALUES(8,'扎古斯2',1);

-- 創(chuàng)建觸發(fā)器(修改)
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;
 
-- 創(chuàng)建觸發(fā)器(刪除)
CREATE TRIGGER tri_empDel AFTER DELETE ON employee FOR EACH ROW    -- 當往員工表刪除一條記錄時
     INSERT INTO test_log(content) VALUES('員工表刪除了一條記錄');
  
 -- 刪除
 DELETE FROM employee WHERE id=7;
 SELECT * FROM employee;
 SELECT * FROM test_log;

mysql權限問題

• mysql數(shù)據(jù)庫權限問題：root ：擁有所有權限（可以干任何事情）
• 權限賬戶模捂，只擁有部分權限（CURD）例如，只能操作某個數(shù)據(jù)庫的某張表
• 如何修改mysql的用戶密碼蜘矢？
  • password: md5加密函數(shù)(單向加密) : SELECT PASSWORD('root'); -- *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B
• mysql數(shù)據(jù)庫狂男，用戶配置 : user表

USE mysql;
SELECT * FROM USER;

• 修改密碼 : UPDATE USER SET PASSWORD=PASSWORD('123456') WHERE USER='root';
• 分配權限賬戶 :

GRANT SELECT ON day16.employee TO 'eric'@'localhost' IDENTIFIED BY '123456';
GRANT DELETE ON day16.employee TO 'eric'@'localhost' IDENTIFIED BY '123456';

mysql備份和還原