Java學習筆記--sql語句

首先明確，sql語句是針對Mysql資料庫程式設計的特殊語句。
1.資料約束（約束使用者操作表資料的行為）
1.1預設值約束
關鍵字：default;
注意：當前欄位沒有插入預設值的時候，預設值才會起作用。

    CREATE TABLE test(
        NAME VARCHAR(20),
        gender VARCHAR(2) DEFAULT '男'
    );

1.2非空約束(某欄位一定要有值)
關鍵字：NOT NULL
注意：約束欄位不能不插入值，也不能插入NULL

    CREATE TABLE test(
    NAME VARCHAR(20) NOT NULL,
    gender VARCHAR(2)
);

1.3唯一約束(被約束的欄位不能出現重複的值)
關鍵字：UNIQUE
注意：唯一約束可以多次插入null

CREATE TABLE test(
    id INT UNIQUE,
    NAME VARCHAR(20)
);

1.4主鍵約束（唯一+非空）
關鍵字：PRIMARY KEY
注意：– 1）通常情況下，我們會給每張表都會設定一個主鍵欄位，用來標記記錄的唯一性
– 2）但是不建議把業務含義欄位作為主鍵，因為隨著業務的變化，業務欄位可能會出現重複。
– 3）建議給每張張獨立新增一個叫id的欄位，把這個id欄位設定成主鍵，用來作為記錄的唯一性

CREATE TABLE test(
    id INT PRIMARY KEY,
    NAME VARCHAR(20)
);  

1.5自增長約束（初始值為0，每次遞增1）
關鍵字：AUTO_INCREMENT
注意：使用truncate table刪除資料的時候，可以把自增長的初始值置為0

CREATE TABLE test(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20)
)

1.6外來鍵約束(約束兩種表的情況)
關鍵字：FOREIGN KEY REFERENCES
注意：約束別人的表叫主表，被約束的表叫附表
插入資料： 先插入主表的資料，再插入副表資料
修改資料： 先修改主表資料，再修改副表資料
刪除資料：先刪除副表資料，再刪除主表資料

    CREATE TABLE employee(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20),
    deptId INT,
    CONSTRAINT employee_dept_fk FOREIGN KEY(deptId) REFERENCES dept(id)
);
CREATE TABLE dept(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20)
);

1.7級聯技術（當有了外來鍵的時候，我們希望修改或刪除資料的時候，修改或刪除主表的資料同時也能夠影響附表的資料）
關鍵字：ON * CASCADE

    CREATE TABLE employee(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20),
    deptId INT,
    -- 新增級聯修改： ON UPDATE CASCADE
    -- 新增級聯刪除： ON DELETE CASCADE 
    CONSTRAINT employee_dept_fk FOREIGN KEY(deptId) REFERENCES dept(id) ON UPDATE CASCADE ON DELETE CASCADE 

);

2.資料庫設計
2.1三大正規化
1.要求表的每個欄位必須獨立的不可分割的單元
2.在第一正規化的基礎上，要求表的除主鍵以外的欄位都和主鍵有依賴關係的。一張表應該只表達一個意思！
3.在第二正規化基礎上，要求表的除主鍵外的欄位都只能和主鍵有直接決定的依賴關係。
3.多表查詢
3.1交叉連線查詢
後果：會產生笛卡爾效應

SELECT employee.name,dept.name FROM employee,dept;

3.2內連結查詢

    SELECT e.name,d.name
    FROM employee e,dept d
    WHERE e.deptId=d.id;
SELECT e.name,d.name
    FROM employee e
    INNER JOIN dept d
    ON e.deptId=d.id;

3.3左外連結查詢

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

3.4右外連結查詢

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

3.5自連結查詢

SELECT e.name AS '員工',b.name AS '上司'
    FROM employee e
    LEFT OUTER JOIN employee b
    ON e.bossId=b.id;

4.mysql儲存過程
4.1mysql儲存過程說通俗點就是sql程式設計
4.2儲存過程的特點
1）儲存過程儲存到資料庫伺服器端，通過資料庫客戶端工具呼叫儲存過程
2）儲存過程的效率會非常高！因為儲存過程是在資料庫伺服器端執行。
3）儲存過程的移植性非常差的！
4.3語法

    DELIMITER 結束符號
CREATE PROCEDURE 儲存過程名稱 (形式引數列表) 
BEGIN
    多個sql語句
END 結束符號
CALL 儲存過程名稱(實際引數列表);

引數型別：
IN： 輸入引數，可以攜帶資料到儲存過程中
OUT: 輸出引數，可以攜帶資料到儲存過程外面。
INOUT: 輸入輸出引數。
--

mysql資料庫三種變數：
    -- 1）全域性變數。mysql內建的變數，mysql程式關閉的時候全域性變數才會失效！！
            -- show variables:產看全域性變數
            -- character_set_client: mysql接收的客戶端的資料編碼
            -- character_set_results： mysql使用什麼編碼輸出給客戶端資料
            -- 檢視某個全域性變數： select @@變數名
            -- 修改某個全域性變數： set @@變數名=值
    -- 2) 會話變數。變數只在某次登入的會話中有效！退出連線，會話變數資料失效！！   
            -- 檢視某個會話變數： select @變數名
            -- 修改/定義某個會話變數： set @變數名=值 
            -- 案例：演示查詢和更改會話變數
    -- 3) 區域性變數：在儲存過程中定義的變數。儲存過程結束區域性變數失效！！
            -- 檢視某個區域性變數： select 變數名
            -- 修改某個區域性變數： set 變數名=值 
            -- 定義某個區域性變數： declare 變數名 資料型別;  

DELIMITER $
CREATE PROCEDURE pro_testByIf(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_testByIf(5,@str);
SELECT @str;

5.觸發器
1定義：當往員工表插入/修改/刪除一條資料的時候，同時往日誌表記錄下來，這時就要使用觸發器完成。
2語法：關鍵字–TEIGGER

    建立觸發器
    CREATE TRIGGER tri_empAdd AFTER INSERT ON employee FOR EACH ROW
    INSERT INTO emp_log(content) VALUES('員工被插入了一條記錄');

刪除觸發器
DROP TRIGGER tri_empAdd;