MySQL基礎2

標籤：

1. 課程回顧
mysql基礎
1）mysql儲存結構： 資料庫 -> 表 -> 資料 sql語句
2）管理資料庫：
增加： create database 資料庫 default character utf8;
刪除： drop database 資料庫;
修改： alter database 資料庫 default character gbk;
查詢： show databases / show create database 資料庫;
3) 管理表：
選擇資料庫：use 資料庫;
增加： create table 表(欄位名1 欄位類型,欄位名2 欄位類型......);
刪除： drop table 表;
修改：
添加欄位： alter table 表 add [column] 欄位名 欄位類型;
刪除欄位: alter table 表 drop [column] 欄位名;
修改欄位類型： alter table 表 modify 欄位名 新的欄位類型;
修改欄位名稱 ： alter table 表 change 舊欄位名 新欄位名 欄位類型;
修改表名稱： alter table 表 rename [to] 新表名;
查詢：
show tables / desc student;
4) 管理資料:
增加： insert into 表(欄位1，欄位2,。。。) values(值1，值2.。。。。);
刪除： delete from 表 where 條件;
修改： update 表 set 欄位1=值1，欄位2=值2...... where 條件;
查詢：
4.1）所有欄位： select * from 表;
4.2）指定欄位： select 欄位1,欄位2.... from 表;
4.3)指定別名： select 欄位1 as 別名 from 表;
4.4 )合并列： select (欄位1+欄位2) from 表;
4.5)去重： select distinct 欄位 from 表;
4.6)條件查詢：
a)邏輯條件 ：and(與) or（或）
select * from 表 where 條件1 and/or 條件2
b）比較條件： > < >= <= = <> between and(在。。。之間)
select * from 表 where servlet>=90;
c）判空條件：
判斷null： is null / is not null
判斷Null 字元串： =‘‘ / <>‘‘
d）模糊條件： like
%: 替換任意個字元
_: 替換一個字元
4.7 分頁查詢：limit 起始行,查詢行數
起始行從0開始
4.8 排序： order by 欄位 asc/desc
asc: 正序，順序
desc：反序，倒序
4.9 分組查詢：group by 欄位
4.10: 分組後篩選： having 條件

SQL語句的分類：
DDL： 資料定義語言 (Data Definition Language)
create / drop / alter
DML：資料動作陳述式
insert / delete /update / truncate
DQL： 資料查詢語言：
select / show

今天的目標：
大綱：
1）資料約束
2）資料庫設計（表設計）
3）預存程序
4）觸發器
5）mysql許可權問題
2 資料約束
2.1什麼資料約束
對使用者動作表的資料進行約束
2.2 預設值
作用： 當使用者對使用預設值的欄位不插入值的時候，就使用預設值。
注意：
1）對預設值欄位插入null是可以的。
2）對預設值欄位可以插入非null
-- 1.1 預設值
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.3 非空
作用： 限制欄位必須賦值
注意：
1）非Null 字元必須賦值
2）非Null 字元不能賦null
-- 1.2 非空
-- 需求： gender欄位必須有值（不為null）
CREATE TABLE student(
id INT,
NAME VARCHAR(20),
gender VARCHAR(2) NOT NULL -- 非空
)

-- 非空欄位必須賦值
INSERT INTO student(id,NAME) VALUES(1,‘李四‘);
-- 非Null 字元不能插入null
INSERT INTO student(id,NAME,gender) VALUES(1,‘李四‘,NULL);

2.4 唯一
作用： 對欄位的值不能重複
注意：
1）唯一欄位可以插入null
2）唯一欄位可以插入多個null

-- 1.3 唯一
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‘);

2.5 主鍵
作用： 非空+唯一
注意：
1）通常情況下，每張表都會設定一個主鍵欄位。用於標記表中的每條記錄的唯一性。
2）建議不要選擇表的包含業務含義的欄位作為主鍵，建議給每張表獨立設計一個非業務含義的 id欄位。
-- 1.4 主鍵（非空+唯一）
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

2.6 自增長
作用： 自動遞增

-- 1.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;

2.7 外鍵
作用：約束兩種表的資料

出現兩種表的情況：
解決資料冗餘高問題： 獨立出一張表
例如： 員工表 和 部門表
問題出現：在插入員工表資料的時候，員工表的部門ID欄位可以隨便插入！！！！！

使用外鍵約束：約束插入員工表的部門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）添加資料： 先添加主表，再添加副表
4）修改資料： 先修改副表，再修改主表
5）刪除資料： 先刪除副表，再刪除主表

-- 1.6 外鍵約束
-- 員工表
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;

2.8 級聯操作
問題： 當有了外鍵約束的時候，必須先修改或刪除副表中的所有關聯資料，才能修改或刪除主表！但是，我們希望直接修改或刪除主表資料，從而影響副表資料。可以使用級聯操作實現！！！

級聯修改： 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;

3 資料庫設計
3.1 引入
需求分析 - 需求分析師 -》 原始需求- > 抽取業務模型
圖書模型：圖書名稱，版本號碼，作者
學生模型： 學號，學生姓名 手機號碼
......
角色：學生 老師，圖書管理員
《需求說明書》
需求設計 -
概要設計：
抽取實體：業務模型 -> 實體模型（java 類 c++類）記憶體
class Book{ name, bookNo,author }
資料庫設計:
業務模型/實體模型 - > 資料模型 (硬碟)

資料庫表設計
問題： 如何設計？
詳細設計
類詳細，屬性和方法

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

第一範式： 要求表的每個欄位必須是不可分割的獨立單元
student : name -- 違反第一範式
張小名|狗娃
sutdent ： name old_name --符合第一範式
張小名 狗娃

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

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

員工表：員工編號 員工姓名 部門名稱

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

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

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

員工表：員工編號（主鍵） 員工姓名 部門編號 --符合第三範式（降低資料冗餘）
部門表：部門編號 部門名
4 關聯查詢(多表查詢)
-- **************二、關聯查詢（多表查詢）****************----
-- 需求：查詢員工及其所在部門(顯示員工姓名，部門名稱)
-- 2.1 交叉串連查詢（不推薦。產生笛卡爾乘積現象：4 * 4=16，有些是重複記錄）
SELECT empName,deptName FROM employee,dept;

-- 需求：查詢員工及其所在部門(顯示員工姓名，部門名稱)
-- 多表查詢規則：1）確定查詢哪些表 2）確定哪些哪些欄位 3）表與表之間串連條件 (規律：串連條件數量是表數量-1)
-- 2.2 內串連查詢：只有滿足條件的結果才會顯示(使用最頻繁)
SELECT empName,deptName -- 2）確定哪些哪些欄位
FROM employee,dept -- 1）確定查詢哪些表
WHERE employee.deptId=dept.id -- 3）表與表之間串連條件

-- 內串連的另一種文法
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;

-- 需求： 查詢每個部門的員工
-- 預期結果：
-- 軟體開發部 張三
-- 軟體開發部 李四
-- 應用維護部 王五
-- 秘書部 陳六
-- 總經辦 null
-- 2.2 左[外]串連查詢： 使用左邊表的資料去匹配右邊表的資料，如果符合串連條件的結果則顯示，如果不符合串連條件則顯示null
-- （注意： 左外串連：左表的資料一定會完成顯示！）
SELECT d.deptName,e.empName
FROM dept d
LEFT OUTER JOIN employee e
ON d.id=e.deptId;

-- 2.3 右[外]串連查詢: 使用右邊表的資料去匹配左邊表的資料，如果符合串連條件的結果則顯示，如果不符合串連條件則顯示null
-- （注意： 右外串連：右表的資料一定會完成顯示！）
SELECT d.deptName,e.empName
FROM employee e
RIGHT OUTER JOIN dept d
ON d.id=e.deptId;

-- 2.4 自串連查詢
-- 需求：查詢員工及其上司
-- 預期結果：
-- 張三 null
-- 李四 張三
-- 王五 李四
-- 陳六 王五
SELECT e.empName,b.empName
FROM employee e
LEFT OUTER JOIN employee b
ON e.bossId=b.id;

5 預存程序
5.1 什麼是預存程序
預存程序，帶有邏輯的sql語句
之前的sql沒有條件判斷，沒有迴圈
預存程序帶上流程式控制制語句（if while）
5.2 預存程序特點
1）執行效率非常快！預存程序是在資料庫的伺服器端執行的！！！
2）移植性很差！不同資料庫的預存程序是不能移植。

5.3 預存程序文法
-- 建立預存程序
DELIMITER $ -- 聲明預存程序的結束符
CREATE PROCEDURE pro_test() --預存程序名稱(參數列表)
BEGIN -- 開始
-- 可以寫多個sql語句; -- sql語句+流程式控制制
SELECT * FROM employee;
END $ -- 結束 結束符

-- 執行預存程序
CALL pro_test(); -- CALL 預存程序名稱(參數);

參數：
IN： 表示輸入參數，可以攜帶資料帶預存程序中
OUT： 表示輸出參數，可以從預存程序中返回結果
INOUT： 表示輸入輸出參數，既可以輸入功能，也可以輸出功能

-- **************三、預存程序*******************-
-- 聲明結束符
-- 建立預存程序
DELIMITER $
CREATE PROCEDURE pro_test()
BEGIN
-- 可以寫多個sql語句;
SELECT * FROM employee;
END $

-- 執行預存程序
CALL pro_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;

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

例如： 當向員工表插入一條記錄時，希望同時往日誌表插入資料

-- 需求： 當向員工表插入一條記錄時，希望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;

7 mysql許可權問題
-- ***********五、mysql許可權問題****************
-- 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 day16.employee TO ‘eric‘@‘localhost‘ IDENTIFIED BY ‘123456‘;
GRANT DELETE ON day16.employee TO ‘eric‘@‘localhost‘ IDENTIFIED BY ‘123456‘;

MySQL基礎2