我的MySQL資料庫學習筆記

標籤：left join   資料類型   並發控制   兩種   sso   區別   全域   get   查看   

一、操作資料庫的基本語句

cmd進入mysql：mysql -uroot -p 
建立資料庫：CREATE DATABASE 庫名; 
建立資料表：同sqlite； 
查看資料庫：SHOW DATABASES; 
查看資料表：SHOW TABLES; 
進入資料庫：USE 庫名； 
查看庫建立語句：SHOW CREATE DATABASE 庫名； 
查看錶建立語句：SHOW CREATE TABLE 表名； 
查看錶結構：SHOW COLUMNS FROM 表名；或DESC 表名 
記錄的增刪查改與sqlite一樣。資料類型不再贅述。

二、約束和修改資料庫結構

2.1 約束 
非空約束：NOT NULL 
主鍵約束：PRIMARY KEY(一個表中只能有一個) 
唯一約束：UNIQUE KEY 
預設約束：DEFAULT 
檢查約束：CHECK（用的較少） 
外鍵約束：FOREIGN KEY

/*外鍵約束,格式:FOREIGN KEY REFERENCES 關聯的表名(欄位名),注意如下幾點：1.參考段需有索引，provinces的id欄位為主鍵約束，自動有索引2.外鍵段pid不建立索引，系統也會自動添加索引3.參考段若為整型，那麼整數型別，有無符號均要一樣。若為字元型則無要求*/CREATE TABLE users1 (id SMALLINT PRIMARY KEY AUTO INCREMENT,pid INT FOREIGN KEY REFERENCES provinces(id))

• 1
• 2
• 3
• 4
• 5
• 6
• 7

註：外鍵約束只有在資料庫引擎為InnoDB時有效！！！

2.2修改資料庫結構語句

2.2.1 表欄位 
添加欄位：ALTER TABLE users2 ADD id SMALLINT UNSIGNED; 
刪除欄位：ALTER TABLE users2 DROP id ； 
要大量操作則在末尾加上“，DROP col_name…” 
添加欄位到某指定位置：ALTER TABLE users2 ADD id SMALLINT UNSIGNED AFTER 列名（或FIRST，插到開頭）;

2.2.2 約束 
添加主鍵約束：ALTER TABLE users2 ADD CONSTRAINT PK_users2_id PRIMARY KEY(id); 
添加唯一約束：ALTER TABLE users2 ADD UNIQUE (username); 
添加外鍵約束: ALTER TABLE users2 ADD FOREIGN KEY (pid) REFERENCES provinces(id); 
添加刪除預設約束： ALTER TABLE users2 ALTER age SET DEFAULT 15; ALTER TABLE users2 ALTER age DROP DEFAULT; 
刪除主鍵約束： ALTER TABLE users2 DROP PRIMARY KEY; 
刪除唯一約束:ALTER TABLE users2 DROP INDEX(KEY) index_name; 
刪除外鍵約束：ALTER TABLE users2 DROP FOREIGN KEY users2_ibfk_1;

2.2.3 列 
修改列定義：ALTER TABLE tab_name MODIFY [COLUMN] col_name column_definition [FIRST|AFTER col_name]; 
舉例： ALTER TABLE users2 MODIFY id SMALLINT UNSIGNED NOT NULL FIRST;（此時可對資料類型修改，由大範圍變小會導致某些資料丟失） 
修改列名稱：ALTER TABLE tab_name CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name];

2.2.4 修改表名 
（1）ALTER TABLE tab_name RENAME [AS|TO] new_tab_name(單次更名) 
（2）RENAME TABLE tab_name TO new_tab_name[,tab_name2 TO new_tab_name2…];(批量更名) 
注意：列名，表名可能被其他引用或建立了索引，改名後可能會導致視圖，預存程序無法正常工作，慎用！！！

三、記錄操作

INSERT:

1. INSERT [INTO] tab_name [(col_name,…)] {VALUES|VALUE} ({expr|DEFAULT},…),(…),…(插入一條資料)
2. INSERT [INTO] tab_name SET col_name={expr|DEFAULT},…（插入某欄位資料）
3. INSERT [INTO] tab_name [(col_name,…)] SELECT…(從其他表選取記錄添加)

UPDATE

1. 單表更新 
   UPDATE [LOW_PRIORITY] [IGNORE] table_references SET col_name1={expr|DEFAULT} [,col_name2={expr|DEFAULT} ]…WHERE where_condition
2. 多表更新，詳見下節

DELETE

1. 單表刪除 
   DELETE FROM tab_name [WHERE where_condition]
2. 多表刪除，詳見下節

SELECT

• SELECT select_expr [,select_expr] 
  [ 
  FROM table_references 
  [WHERE where_condition] 
  [GROUP BY {col_name|position} [ASC|DESC],…] 
  [HAVING where_condition] 
  [ORDER BY {col_name|position|expr} [ASC|DESC],…] 
  [LIMIT {[offset,] row_count|row_count OFFSET offset}] 
  ]

四、子查詢與串連

4.1子查詢簡介 
子查詢是指出現在其他SQL語句中的SELECT 子句。 
例如：SELECT * FROM t1 WHERE col1=(SELECT col2 FROM t2);其中SELECT * FROM t1稱為Outer Query/Outer Statement,SELECT col2 FROM t2稱為子查詢；

• 子查詢嵌套在查詢內部，且必須始終存在於圓括弧內（）
• 子查詢可包括多個關鍵字或條件，如DISTINCT,GROUP BY,ORDER BY,LIMIT,函數等。
• 子查詢的外層查詢可以是：SELECT,INSERT,UPDATE,DELETE,SET或DO
• 子查詢可以返回標量，一行，一列，或子查詢

4.2 使用子查詢 
4.2.1 使用比較符 = ,>,<,>=,<=,!=,<>,<=> 
如： SELECT * FROM tdb_goods WHERE goods_price>=(SELECT ROUND(AVG(goods_price),2) FROM tdb_goods);

4.2.2用ANY,SOME,ALL修飾比較符，用於比較子查詢返回的多條資料時,ANY與SOME等價，只要滿足一條資料，ALL要滿足所有資料。 
如：SELECT goods_name FROM tdb_goods WHERE goods_price >= ALL(SELECT goods_price FROM tdb_goods WHERE goods_cate =’超極本’); 

4.2.3使用[NOT] IN的子查詢 
=ANY與IN等價 
!=ALL或<>ALL與NOT IN等價

4.2.4 將查詢結果寫入資料表 
INSERT [INTO] tab_name [(col_name,…)] SELECT… 
如： INSERT tdb_goods_cates(cate_name) SELECT goods_cate FROM tdb_goods GROUPBY goods_cate;

4.3多表更新 
UPDATE table-references SET col_name1={expr1|DEFAULT}[,col_name2={expr2|DEFAULT}]…[WHERE where_condition]

4.4多表更新之一步更新（將建立表，查詢結果寫入建立表，多表更新合成一步） 
第一步，第二步合為一步： 
CREATE TABLE [IF NOT EXISTS] tab_name[(create_definition,…)] select_statement 
如：

CREATE TABLE tdb_goods_brands (brand_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,brand_name VARCHAR(40) NOT NULL)SELECT brand_name FROM tdb_goods GROUP BY brand_name;

• 1
• 2

第三步：

UPDATE tdb_goods AS  g  INNER JOIN tdb_goods_brands AS b ON g.brand_name = b.brand_name SET g.brand_name = b.brand_id;

• 1
• 2

（由於兩表有同名欄位brand_name，不能像之前那樣直接=）

4.3串連 
table_references{[INNER|CROSS]JOIN|{LEFT|RIGHT[OUTER]JOIN}table_references ON conditional_expr 
當conditional_expr中兩表比較的欄位重名，則可設別名 
（1）table_references tab_name [[AS] alias]|table_subquery [AS] alias 
資料表可以使用tab_name AS alias_name或tab_name alias_name賦予別名，見上 
（2）table_subquery可以作為子查詢使用在FORM字句中，這樣的子查詢必須為其賦予 
串連分為： 
（1）內串連，顯示左右表符合串連條件的記錄 
（2）左串連，顯示左表全部記錄和右表符合串連條件的記錄 
（3）右串連，顯示右表全部記錄和左表符合串連條件的記錄 
多表串連 
如：SELECT goods_id,goods_name,cate_name,brand_name,goods_price FROM tdb_goods AS g INNER JOIN tdb_goods_cates AS c ON g.cate_id=c.cate_id INNER JOIN tdb_goods_brands AS b ON g.brand_id=b.brand_id; 
關於串連的幾點說明： 
? A LEFT JOIN B join_condition

• 資料表B的結果集依賴資料表A。
• 資料表A的結果集根據左串連條件依賴所有資料表（B表除外）。
• 左外串連條件決定如何檢索資料表B（在沒有指定WHERE條件的情況下）
• 如果資料表A的某條記錄符合WHERE條件，但是在資料表B不存在符合串連條件的記錄，將產生一個所有列為空白的額外的B行。

? 如果使用內串連尋找的記錄在串連資料表中部存在，並且在WHERE子句中嘗試一下操作：col_name IS NULL時，如果col_name被定義為NOT NULL，MySQL將在找到符合串連條件的記錄後停止搜尋更多的行。

4.4無限極分類表設計 
（1）無限分類的資料表設計 
CREATE TABLE tdb_goods_types(type_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,type_name VARCHAR(20) NOT NULL, parent_id SMALLINT UNSIGNED NOT NULL DEFAULT 0); 
插入資料後： 
 
（2）尋找所有分類及其父類

SELECT s.type_id,s.type_name,p.type_name FROM tdb_goods_types AS s LEFT JOIN tdb_goods_types AS  p ON s.parent_id = p.type_id;

• 1
• 2
• 3
• 4

s.parent_id = p.type_id的順序可變，LEFT JOIN左右順序絕對不能變，表示顯示哪張表的全部，要顯示全的欄位用左表.xx

4.5多表刪除 
重複資料刪除資料中id較大的

DELETE t1 FROM tdb_goods AS t1 LEFT JOIN (SELECT goods_id,goods_name FROM tdb_goods GROUP BY goods_name HAVING count(goods_name) >= 2 ) AS t2  ON t1.goods_name = t2.goods_name  WHERE t1.goods_id > t2.goods_id;

• 1
• 2
• 3
• 4

五、運算子和函數

由於做過紙質筆記，暫時不再介紹，可直接百度 
自訂函數的必要條件： 
1.參數 
2.傳回值 
函數都有傳回值，不一定有參數 
基本語句： 
CREATE FUNCTION function_name RETURNS {STRING|INTEGER|REAL|DECIMAL} routine_body 
routine_body——>函數體

• 函數體由合法的sql語句組成
• 函數體可以是簡單的SELECT或INSERT語句
• 函數體如果為複合結構則使用BEGIN…END語句

• 複合結構可以包括聲明，迴圈，控制結構

  如：CREATE FUNCTION f1() RETURNS VARCHAR(30) RETURN DATE_FORMAT(NOW(),’%Y年%m月%d日%H點:%i分:%s秒’); 
  為什麼我直接SELECT DATE_FORMAT(…)返回正確，SELECT f1()返回結果裡：和中文變成了？號 視頻裡f1()返回正確！！！

  建立具有複合結構函數體的函數（以插入資料為例） 
  錯誤示範： 
  CREATE FUNCTION adduser(username VARCHAR(20)) RETURNS INT UNSIGNED RETURN INSERT test(username) VALUES(username); 
  因為insert返回的結果根本不是int型 
  正確示範： 
  1.DELIMITER // （修改分隔字元；為//，其他均可） 
  2.把LAST_INSERT_ID()作為傳回值 
  3.由於此時有insert語句和LAST_INSERT_ID()函數，需要用複合結構 
  4.最終語句如下：

CREATE FUNCTION adduser(username VARCHAR(20)) RETURNS INT UNSIGNED BEGIN INSERT test(username) VALUES(username) ;RETURN LAST_INSERT_ID() ;END // **ps:注意這兩個；號，缺一不可，且只能用；號**

• 1
• 2
• 3
• 4
• 5
• 6
• 7

刪除自訂函數 
DROP FUNCTION [IF_EXISTS] fun_name

六、MySQL預存程序

6.1 什麼是預存程序和如何建立

SQL命令執行流程： 
SQL指令——>MySQL引擎_分析_>文法正確——>可識別命令_執行_>執行結果_返回_>用戶端

Mysql儲存過程是一組為了完成特定功能的SQL語句集，經過編譯之後儲存在資料庫中，當需要使用該組SQL語句時使用者只需要通過指定儲存過程的名字並給定參數就可以調用執行它，從而省去分析文法等步驟，提高效率

語句： 
CREATE [DEFINER={user|CURRENT_USER}] 
PROCEDURE sp_name([proc_parameter[,…]]) 
[characteristic…]routine_body

其中proc_parameter–>[IN|OUT|INOUT] param_name type 
IN,表示該參數的值必須在調用預存程序時指定 
OUT，表示該參數的值可以被預存程序改變，並且可以返回 
INOUT，表示該參數的值在調用預存程序時指定，並且可以被改變和返回 
過程體：

• 過程體由合法的SQL語句構成
• 過程體可以是任意增刪改查，多表串連等SQL語句
• 過程體如果為複合結構則使用BEGIN…END語句
• 過程體可以包括聲明，迴圈，控制結構

舉例： 
建立預存程序： 
CREATE PROCEDURE spl() SELECT VERSION(); 
使用： 
CALL spl;或者CALL spl();（由於建立的時候沒有指定參數，所以兩種方法均可，如果指定了參數，必須第二種）

6.2建立帶有IN型別參數的預存程序

DELIMITER //CREATE PROCEDURE removeUserById(IN id INT UNSIGNED)BEGINDELETE FROM users WHERE id=id;END//CALL removeUserById(1);

• 1
• 2
• 3
• 4
• 5
• 6

注意：我們的本意是刪除users表中id=1的記錄，但結果是記錄全被刪除，這是因為我們設定的參數和id欄位重名，在WHERE語句中，左邊id是欄位名，右邊id是條數，但系統會誤認為兩邊id都是欄位名！！！ 
修改： 
（1） DROP PROCEDURE removeUserById;(刪除預存程序) 
（2）重複上面操作，修改參數名不為id即可

6.3建立帶有IN,OUT型別參數的預存程序 
由於要用到MySQL變數，做下簡單介紹： 
mysql變數的術語分類：

1.使用者變數：以”@”開始，形式為”@變數名”

使用者變數跟mysql用戶端是綁定的，設定的變數，只對目前使用者使用的用戶端生效

2.全域變數：定義時，以如下兩種形式出現，set GLOBAL 變數名 或者 set @@global.變數名

對所有用戶端生效。只有具有super許可權才可以設定全域變數

3.會話變數：只對串連的用戶端有效。

4.局部變數：作用範圍在begin到end語句塊之間。在該語句塊裡設定的變數

declare語句專門用於定義局部變數。set語句是設定不同類型的變數，包括會話變數和全域變數

CREATE PROCEDURE p8 ()   BEGIN   DECLARE a INT;   DECLARE b INT;   SET a = 5;   SET b = 5;   INSERT INTO t VALUES (a);   SELECT s1 * a FROM t WHERE s1 >= b;   END; //

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9

以建立刪除指定行數，返回剩餘行數的預存程序為例：

CREATE PROCEDURE removeUserAndReturnUserNums(IN p_id INT UNSIGNED,OUT userNum INT UNSIGNED)BEGINDELETE FROM users WHERE  _id=p_id;SELECT count(_id) FROM users INTO userNum;END//CALL removeUserAndReturnUserNums(1,@nums);SELECT @nums;

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10

6.4建立帶有多個OUT型別參數的預存程序 
用到ROW_COUNT()函數，返回上一操作影響的記錄條數 
以建立刪除指定記錄，並返回刪除記錄數和剩餘記錄數的預存程序為例

DELIMITER //CREATE PROCEDURE removeUserByNameAndReturnInfos(IN p_name VARCHAR(10) ,OUT deleteUsers SMALLINT UNSIGNED,OUT userCounts SMALLINT UNSIGNED)BEGINDELETE FROM test WHERE username=p_name;SELECT ROW_COUNT() INTO deleteUsers;SELECT COUNT(id) FROM test INTO userCounts;END//DELIMITER ;CALL removeUserByNameAndReturnInfos(‘a‘,@a,@b);SELECT @a,@b;+------+------+| @a   | @b   |+------+------+|    1 |    7 |+------+------+//表中有8條記錄，只有1條username=a

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21

6.5預存程序與自訂函數的區別

• 預存程序實現的功能要複雜一些，而函數的針對性更強
• 預存程序可以返回多個值，函數只能有一個傳回值
• 預存程序一般獨立的來執行，而函數可以作為其他SQL語句的組成部分出現

實際開發中函數用的比較少，預存程序用的更多

七、MySQL儲存引擎

MySQL可以將資料以不同的技術儲存在檔案（記憶體）中，這種技術稱為儲存引擎。 
每一種儲存引擎使用不同的儲存機制、索引技巧、鎖定水平，最終提供廣泛且不同的功能。

先介紹幾個名詞：

• 並發控制 
  當多個串連對記錄進行修改時保證資料的一致性和完整性
• 鎖（類似Java裡的鎖，可以對某張表，某條記錄加鎖） 
  共用鎖定（讀鎖）：在同一時間段內，多個使用者可以讀取同一個資源，讀取過程中資料不會發生任何變化 
  獨佔鎖定（寫鎖）：在任何時候只能有一個使用者寫入資源，當進行寫鎖時會阻塞其他的讀鎖或者寫鎖操作
• 鎖顆粒 
  表鎖，是一種開銷最小的鎖策略，一個表只有一個 
  行鎖，是一種開銷最大的鎖策略，一個表可以有多個甚至每行一個鎖
• 事務 
  事務用於保證資料庫的完整性
• 事務特性 
  原子性 
  一致性 
  隔離性 
  持久性
• 外鍵 
  是保證資料一致性的策略
• 索引 
  是對資料表中一列或多列的值進行排序的一種結構

MySQL支援的儲存引擎：

• MyISAM
• InnoDB
• Memory
• CSV
• Archive 
  

修改儲存引擎的方法

• 通過修改mysql設定檔實現，5.5以上預設InnoDB 
  -default-storage-engine=engine

• 通過建立資料表命令實現 
  -CREATE TABLE table_name( 
  … 
  )ENGINE=engine;

• 通過修改資料表命令實現 
  -ALTER TABLE table_name ENGINE[=] engine_name;

八、MySQL圖形化管理工具

由於資料庫實戰裡用到Navicat，具體操作還請自行百度

我的MySQL資料庫學習筆記