學習筆記系列開頭慣例發布一些尋親消息
鏈接：https://baobeihuijia.com/bbhj/contents/3/197161.html
-
數據庫創建：
CREATE DATABASE books； CREATE DATABASE IF NOT EXISTS books; -
更改字符集
ALTER DATABASE books CHARACTER SET gbk; -
庫的刪除
DROP DATABASE IF EXISTS books； -
表的創建
CREATE TABLE book(id INT,bName VARCHAR(20),price DOUBLE,authorId INT,publishDate DATETIME ); -
表的修改
# 改列名改列屬性 ALTER TABLE book CHANGE COLUMN publishdate pubDate DATETIME; # 改列屬性 ALTER TABLE book MODIFY COLUMN pubDate TIMESTAMP; # 改表名字 ALTER TABLE book RENAME TO books； ALTER TABLE book ADD COLUMN pubDate TIMESTAMP 【first/after 字段名】; ALTER TABLE book DROP COLUMN pubDate; -
表的刪除
DROP TABLE IF EXISTS book; -
表的復制
# 僅僅復制表的結構 CREATE TABLE author_copy LIKE author；# 復制表的結構+數據 CREATE TABLE author_copy2 SELECT * FROM author;# 僅僅復制某些字段 CREATE TABLE copy4 SELECT id,an_name FROM author WHERE 0;# 可以跨庫，只要寫成 庫名.表名 CREATE TABLE dept2 SELECT department_id, department_name from my_employees.departments; -
常見數據類型
# 整型 Tinyint/Smallint/Mediumint/Int/integer/bigint# 默認為有符號，大于范圍則插入臨界值 CREATE TABLE tab_int(t1 INT,t2 INT UNSIGNED# 長度不夠7用0來填充t3 INT(7) ZEROFILL );# 浮點小數 ：MD都可以省略，隨著插入的數據改變 float(M,D):M代表整數+小數部分長度，D代表小數部分長度 double(M,D)# 定點小數 ：MD都可以省略，M默認為10，D默認為0，精度更高 dec(M,D)# 短的字符型 char 不可變長，可以省略默認為1 不可超過最大字符數 效率高 varchar 可變 不可超過最大字符數 效率低# ENUM：枚舉，只能選擇列表中一個插入 e1 enum('a','b','c'); # set :選擇列表中一個或者多個插入 s1 set('a','b','c','d') # binary和varbinary 保存較短的二進制 # 長的字符型 text，blob（長的二進制）# 日期 date 1001-01-01 time 22：22：22 year 1001 datetime：1001-01-01 00：00：00，只插入年份會自動給時間 timestamp：和datetime表示一樣，但是會受當前的時區影響，更能反映真實時間INSERT INTO tab_date VALUES (NOW(),NOW()); -
常見約束
列級約束 - NOT NULL - DEFAULT：保證字段有默認值 - PRIMARY KEY:主鍵，唯一且非空 - UNIQUE:唯一但是可以為空CREATE TABLE stuinfo1(id INT PRIMARY KEY,stuName VARCHAR(20) NOT NULL,gender CHAR(1) CHECK(gender='男' OR gender ='女'),age INT DEFAULT 18,majorId INT,seat INT UNIQUE );表級約束 - CHECK：mysql沒效果 - PRIMARY KEY:主鍵，唯一且非空 - UNIQUE:唯一但是可以為空 - FOREIGN KEY:外鍵，該表格的該字段值來自于主表的關聯列的值 CREATE TABLE stuinfo1(id INT ,stuName VARCHAR(20) ,gender CHAR(1) ,age INT ,majorId INT,CONSTRAINT pk PRIMARY KEY(id),CONSTRAINT uq UNIQUE(seat),CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id) # 或者不起名PRIMARY KEY(id),UNIQUE(seat),FOREIGN KEY(majorid) REFERENCES major(id) ); -
主鍵和唯一的區別
-
外鍵：
- 關聯列必須是主鍵/唯一鍵
- 插入數據時，先插入主表再插入從表，刪除的時候先刪除從表再刪主表
-
修改約束
# 主鍵的增刪只需要一次，不需要每次motify都帶著 # 列級約束 ALTER TABLE stuinfo1 MODIFY COLUMN id INT PRIMARY KEY; ALTER TABLE stuinfo1 MODIFY COLUMN stuName VARCHAR(10) NOT NULL;# 表級約束 ALTER TABLE stuinfo1 ADD PRIMARY KEY(seat); # 必須先存在再添加 ALTER TABLE stuinfo1 ADD UNIQUE(seat); # 必須先存在再添加 ALTER TABLE stuinfo1 ADD FOREIGN KEY (majorId) REFERENCES major(id); ALTER TABLE stuinfo1 ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY (majorId) REFERENCES major(id); -
刪除約束
ALTER TABLE stuinfo1 MODIFY COLUMN id INT;# 刪除主鍵和唯一鍵的名字（主鍵起名也沒有效果）,列級約束無法刪除主鍵 ALTER TABLE stuinfo1 DROP PRIMARY KEY; ALTER TABLE stuinfo1 DROP INDEX seat;# 刪除外鍵 ALTER TABLE stuinfo1 DROP FOREIGN KEY fk_stuinfo_major; -
標識列
# 只有key才能設置標識列、只有有一個、只能是數值類型的CREATE TABLE tab_identify(id INT PRIMARY KEY AUTO_INCREMENT,NAME VATCHAR(20) ); # 無需手動增加 INSERT INTO tab_identify values(NULL,'JOIN');# 所有庫都會被修改 SHOW VARIABLES LIKE '%auto_increment%'; SET auto_increment_increment = 3; # 起始位置可以手動插入# 增刪標識列 ALTER TABLE tab MOTIFY COLUMN id INT PRIMARY KRY AUTO_INCREMENT; ALTER TABLE tab MOTIFY COLUMN id INT; -
事務
要么全部執行、要么全部不執行 # 四大特性：原子性、一致性、隔離性、持久性# 需要設置自動提交功能為OFF,只有當前事務設置有效 set autocommit = 0; start transaction; 僅限 sql語句不包含DDL語句 commit/rollback； 二選一，上述sql只是提交到了內存中，如果要執行那么就commit，如果不執行那就rollback- 臟讀：沒有提交 - 不可重復讀：更新 - 幻讀：插入# 查看隔離級別 select @@tx_isolation set session|global transaction isolation level read committed;# 四種隔離級別 read uncommitted:事務尚未提交，庫中的數據就已經修改了，當事務rollback的時候，這些臨時修改且被讀到的數據成為臟數據 read committed:可以避免臟讀（未提交就不會修改），但是該事務commit前后，另一個事務的讀取不可重復 repeatable_read:不管另一個事務是否提交，讀到什么就一直是什么,避免不可重復讀；但是另一個事務插入行之后，數據還是會變多 serializable：串行化，另一個事務的修改都會被阻塞# 設置保存點 SAVEPOINT a; ROLLBACK TO a; -
視圖
# 只保存了sql語句，沒有保存真實的數據 - 簡化sql，不必了解查詢細節 - 保護數據，提高安全性# 創建 CREATE VIEW avg_salary AS SELECT AVG(salary),department_id FROM employees GROUP BY department_id;# 使用 SELECT * FROM avg_salary;# 修改1 CREATE OR REPLACE VIEW myv3 AS SELECT AVG(salary),job_id FROM employees GROUP BY job_id;# 修改2 ALTER VIEW myv3 AS SELECT AVG(salary),job_id FROM employees GROUP BY job_id;# 刪除 DROP VIEW myv3;# 修改，原始表也會修改（要看視圖定義方式，有的可以更新有的不能） INSERT INTO myv3 VALUES('張飛','qq,com')； UPDATE myv3 SET NAME='吳京' WHERE email = 'qq,com'; DELETE FROM myv3 WHERE email = 'qq,com'; -
刪除帶外鍵的主表的方式
- 級聯刪除,主表內容刪除會把從表內容刪除 ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE CASCADE;- 級聯置空 ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE SET NULL; -
系統變量
系統變量：系統提供，屬于服務器層面 - 全局變量服務器每次啟動會將全局變量賦初始值，針對所有會話有效，重啟無效SHOW GLOBAL【SESSION】 VARIABLES;SHOW GLOBAL【SESSION】 VARIBLIES LIKE '%char%';SELECT @@global|session.系統變量名SET global|session.系統變量名 = VALUE;SET @@global|session.系統變量名 = VALUE;SELECT @@global.tx_isolation;SET @@global.autocommit = 0;- 會話變量：僅針對當前會話有效，換一個連接就無效了SHOW SESSION VARIABLES;SELECT @@SESSION.transaction_isolation；SELECT @@SESSION.transaction_isolation = read uncommitted； -
自定義變量
- 用戶變量：僅當前會話有用,要加@，不需要限定類型 SET @用戶變量名：=值; SELECT 字段 INTO @變量名 FROM 表； SELECT COUNT(*) INTO @count FROM emplyees; # 使用 SELECT @count;- 局部變量：作用于begin end中的第一句話，一般不用加@，需要限定類型 聲明： DECLARE 賦值：SET/SELECT 使用：SELECT SET @m = 1; SET @n = 2; SELECT @n: = 2；(加冒號) SET @sum= @m + @n; SELECT @sum;BEGIN DECLARE m INT DEFAULT 1; SET m = 2; SELECT @m = 3; SELECT m; END -
存儲過程和函數
- 提高代碼的重用 - 減少編譯次數 - 減少了逐步與服務器的連接次數# 創建 CREATE PROCEDURE 名字(參數) BEGIN一組合法的SQL語句 END參數模式 IN:參數可以作為輸入 OUT：參數作為返回值 INOUT：既需要輸入值，又可以返回值#### IN DELIMITER $ CREATE PROCEDURE myp1(IN beautyName VARCHAR(20)) BEGINSELECT bo.*FROM boys boRIGHT JOIN beauty bON b.boyfriend_id = bo.idWHERE b.name = beautyName; END $CALL myp1('柳巖');#### OUT DELIMITER $ CREATE PROCEDURE myp1(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20)，OUT userCP INT) BEGINSELECT bo.boyName, bo.userCP INTO boyName, INTO userCPFROM boys boRIGHT JOIN beauty bON b.boyfriend_id = bo.idWHERE b.name = beautyName; END $SET @boyName$ CALL myp1('小昭',@boyName)$select @boyName;#### INOUT SET @a=3; SET @b=4;DELIMITER $ CREATE PROCEDURE myp1(INOUT a INT,INOUT b INT) BEGINSET a = a*2;SET b = b*2; END $CALL myp1(@a,@b)$ SELECT @a$# 刪除 DROP PROCEDURE myp1;# 查看 SHOW CREATE PROCEDURE myp1；# 一般無法修改存儲過程
-
函數和存儲過程的區別
- 存儲過程可以0或多個返回、而函數必須有且僅有一個返回
# 無參 DELIMITER $ CREATE FUNCTION myf1() RETURNS INT BEGINDECLARE c INT DEFAULT 0;SELECT COUNT(*) INTO cFROM employees;RETURN c; END $ SELECT myf1() $# 有參 DELIMITER $ CREATE FUNCTION myf1(empName VARCHAR(20)) RETURNS DOUBLE BEGINSET @SAL=0;SELECT salary INTO @SALFROM employeesWHERE first_name = empName;RETURN @SAL; END $ SELECT myf1('cynthia') $# 實現兩數相加 DELIMITER $ CREATE FUNCTION test_fun1(num1 FLOAT, num2 FLOAT) RETURNS FLOAT BEGINDECLARE s FLOAT DEFAULT 0;SET s = num1 + num2;RETURN s; END $ SELECT test_fun1(2.3,1.7)$# 查看函數 SHOW CREATE FUNCTION myf1；# 刪除 DROP FUNCTION myf1； -
流程控制結構（順序/分支/循環）
一、分支 - if函數：任何位置 IF(表達式1，表達式2，表達式3)；- case：任何位置 DELIMITER $ CREATE PROCEDURE test(IN SCORE INT) BEGINCASEWHEN score>=90 AND score <=100 THEN SELECT 'A';WHEN score>=80 THEN SELECT 'B';WHEN score>=60 THEN SELECT 'C';ELSE SELECT 'D';END CASE; END$ SELECT test(20)$- if結構：只能放在begin end中 DELIMITER $ CREATE FUNCTION test(SCORE INT) RETURNS CHAR BEGINIF score>=90 AND score <=100 THEN RETURN 'A';ELSEIF score>=80 THEN RETURN 'B';ELSEIF score>=60 THEN RETURN 'C';ELSE RETURN 'D';END IF; END$ SELECT test(20)$二、循環 while/loop/repeat,必須放在begin end之間 CREATE PROCEDURE pro_while(IN insertCount INT) BEGINDECLARE i INT DEFAULT 1;WHILE i<=insertCount DOINSERT INTO boys VALUES(16,'zhangsan',200);SET i = i+1;END WHILE; END $ CALL pro_while(2);# 如果添加leave或者iterate，就必須添加名稱 # 添加leave CREATE PROCEDURE pro_while(IN insertCount INT) BEGINDECLARE i INT DEFAULT 1;a:WHILE i<=insertCount DOINSERT INTO boys VALUES(16,'zhangsan',200);SET i = i+1;IF i>=20 THEN LEAVE a;END IF;END WHILE a; END $ CALL pro_while(2)$# 添加iterate CREATE PROCEDURE pro_while(IN insertCount INT) BEGINDECLARE i INT DEFAULT 1;a:WHILE i<=insertCount DOIF MOD(i,2)!=0 THEN ITERATE a;END IF;INSERT INTO boys VALUES(16,'zhangsan',200);SET i = i+1;END WHILE a; END $ CALL pro_while(2)$
