MySQL基礎篇快速記憶和查詢

查詢

• 語法：

SELECT   標識選擇哪些列
FROM     標識從哪個表中選擇

去重(Distinct)

在SELECT語句中使用關鍵字DISTINCT去除重複行

SELECT DISTINCT department_id
FROM   employees;

過濾(Where)

語法：

SELECT 欄位1,欄位2
FROM 表名
WHERE 過濾條件

• 使用WHERE 子句，將不滿足條件的行過濾掉
• WHERE子句緊隨 FROM子句

排序(Order by)

使用 ORDER BY 子句排序

• ASC（ascend）: 升序
• DESC（descend）:降序

ORDER BY 子句在SELECT語句的結尾。

SELECT last_name, department_id, salary
FROM   employees
ORDER BY department_id, salary DESC;

分頁(Limit)

格式：

LIMIT [位置偏移量,] 行數

第一個“位置偏移量”引數指示MySQL從哪一行開始顯示，是一個可選引數，如果不指定“位置偏移量”，將會從表中的第一條記錄開始（第一條記錄的位置偏移量是0，第二條記錄的位置偏移量是1，以此類推）；第二個引數“行數”指示返回的記錄條數。

運算元據庫

CREATE DATABASE 資料庫名 IF NOT EXISTS ;  #建立
SHOW DATABASES; #檢視，加上括號是檢視當前
USE DATABASE; #使用/切換資料庫
ALTER DATABASE 資料庫名 CHARACTER SET 字符集;  #修改資料庫
DROP DATABASE IF EXISTS 資料庫名; #刪除資料庫

操作表

建立表(create)

CREATE TABLE [IF NOT EXISTS] 表名(
	欄位1, 資料型別 [約束條件] [預設值],
	欄位2, 資料型別 [約束條件] [預設值],
	欄位3, 資料型別 [約束條件] [預設值],
	……
	[表約束條件]
);

AUTO_INCREMENT： 自增

邊建立邊插入

CREATE TABLE dept80
AS 
SELECT  employee_id, last_name, salary*12 ANNSAL, hire_date
FROM    employees
WHERE   department_id = 80;

檢視錶(show)

SHOW CREATE TABLE 表名

修改表內容(alter)

ALTER TABLE 表名 ADD 欄位名 欄位型別 【FIRST|AFTER 欄位名】;  #追加一個列
ALTER TABLE 表名 MODIFY  欄位名1 欄位型別 【DEFAULT 預設值】【FIRST|AFTER 欄位名2】;  #修改一個列
ALTER TABLE 表名 CHANGE 列名 新列名 新資料型別;#重新命名一個列
ALTER TABLE 表名 DROP 欄位名 #刪除一個列

重新命名錶(rename)

RENAME TABLE 列名 TO 列名1;#方式一
ALTER table 列名 RENAME 列名1;#方式二

刪除表(drop)

DROP TABLE [IF EXISTS] 資料表1 [, 資料表2, …, 資料表n];

清空表(truncate,delete)

TRUNCATE TABLE 表名;
DELETE FROM table_name WHERE condition;

• 如果你需要刪除部分資料、希望可以回滾、或者希望產生更詳細的日誌，可以使用 DELETE。
• 如果你想快速刪除表中的所有資料而不保留表結構，且不需要支援回滾，可以使用 TRUNCATE。

約束

約束主要有五種，其中唯一約束和外來鍵約束的刪除通常需要透過查詢並刪除相應的約束名，而主鍵約束、自增約束、預設值約束可以透過對相應列的屬性進行重置來實現。

唯一約束（unique)

建表時

create table 表名稱(
	欄位名  資料型別,
    欄位名  資料型別  unique,  
    欄位名  資料型別  unique key,
    欄位名  資料型別
);
create table 表名稱(
	欄位名  資料型別,
    欄位名  資料型別,  
    欄位名  資料型別,
    [constraint 約束名] unique key(欄位名)
);

建表後指定唯一鍵約束

#欄位列表中如果是一個欄位，表示該列的值唯一。如果是兩個或更多個欄位，那麼複合唯一，即多個欄位的組合是唯一的
#方式1：
alter table 表名稱 add unique key(欄位列表); 

#方式2：
alter table 表名稱 modify 欄位名 欄位型別 unique;

複合唯一約束

create table 表名稱(
	欄位名  資料型別,
    欄位名  資料型別,  
    欄位名  資料型別,
    unique key(欄位列表) #欄位列表中寫的是多個欄位名，多個欄位名用逗號分隔，表示那麼是複合唯一，即多個欄位的組合是唯一的
);

刪除唯一約束

• 新增唯一性約束的列上也會自動建立唯一索引。
• 刪除唯一約束只能透過刪除唯一索引的方式刪除。
• 刪除時需要指定唯一索引名，唯一索引名就和唯一約束名一樣。
• 如果建立唯一約束時未指定名稱，如果是單列，就預設和列名相同；如果是組合列，那麼預設和()中排在第一個的列名相同。也可以自定義唯一性約束名。

SELECT * FROM information_schema.table_constraints WHERE table_name = '表名'; #檢視都有哪些約束

ALTER TABLE USER 
DROP INDEX uk_name_pwd;

外來鍵約束(Foreign key)

限定某個表的某個欄位的引用完整性。

比如：員工表的員工所在部門的選擇，必須在部門表能找到對應的部分。

• (從表的某個欄位)的資料型別必須與主表名(被參考欄位)的資料型別一致，邏輯意義也一樣
• (從表的某個欄位)的欄位名可以與主表名(被參考欄位)的欄位名一樣，也可以不一樣

（1）建表時

create table 主表名稱(
	欄位1  資料型別  primary key,
    欄位2  資料型別
);

create table 從表名稱(
	欄位1  資料型別  primary key,
    欄位2  資料型別,
    [CONSTRAINT <外來鍵約束名稱>] FOREIGN KEY（從表的某個欄位) references 主表名(被參考欄位)
);
-- FOREIGN KEY: 在表級指定子表中的列
-- REFERENCES: 標示在父表中的列

create table dept( #主表
	did int primary key,		#部門編號
    dname varchar(50)			#部門名稱
);

create table emp(#從表
	eid int primary key,  #員工編號
    ename varchar(5),     #員工姓名
    deptid int,				#員工所在的部門
    foreign key (deptid) references dept(did)   #在從表中指定外來鍵約束
    #emp表的deptid和和dept表的did的資料型別一致，意義都是表示部門的編號
);

說明：
（1）主表dept必須先建立成功，然後才能建立emp表，指定外來鍵成功。
（2）刪除表時，先刪除從表emp，再刪除主表dept

（2）建表後

一般情況下，表與表的關聯都是提前設計好了的，因此，會在建立表的時候就把外來鍵約束定義好。不過，如果需要修改表的設計（比如新增新的欄位，增加新的關聯關係），但沒有預先定義外來鍵約束，那麼，就要用修改表的方式來補充定義。

格式：

ALTER TABLE 從表名 ADD [CONSTRAINT 約束名] FOREIGN KEY (從表的欄位) REFERENCES 主表名(被引用欄位) [on update xx][on delete xx];

舉例：

alter table emp add foreign key (deptid) references dept(did);

刪除

SELECT * FROM information_schema.table_constraints WHERE table_name = 'emp';#先查詢約束名
alter table emp drop foreign key emp_ibfk_1;#再刪除

主鍵約束(primary)

用來唯一標識表中的一行記錄。相當於唯一約束+非空約束的組合,主鍵約束列不允許重複，也不允許出現空值。

實現方法和上述unique類似，將關鍵詞改為primary

create table temp(
	id int primary key,
    name varchar(20)
);

刪除主鍵約束

alter table 表名稱 drop primary key;

自增約束(auto_increment)

create table employee(
	eid int primary key auto_increment,
    ename varchar(20)
);

刪除

#alter table 表名稱 modify 欄位名 資料型別 auto_increment;#給這個欄位增加自增約束

alter table 表名稱 modify 欄位名 資料型別; #去掉auto_increment相當於刪除

預設值約束(Default)

給某個欄位/某列指定預設值，一旦設定預設值，在插入資料時，如果此欄位沒有顯式賦值，則賦值為預設值。

create table 表名稱(
	欄位名  資料型別  primary key,
    欄位名  資料型別  unique key not null,  
    欄位名  資料型別  unique key,
    欄位名  資料型別  not null default 預設值, 
);
create table 表名稱(
	欄位名  資料型別 default 預設值 ,
    欄位名  資料型別 not null default 預設值,  
    欄位名  資料型別 not null default 預設值,
    primary key(欄位名),
    unique key(欄位名)
);

刪除

alter table 表名稱 modify 欄位名 資料型別 ;#刪除預設值約束，也不保留非空約束

alter table 表名稱 modify 欄位名 資料型別  not null; #刪除預設值約束，保留非空約束

運算元據

插入資料(insert)

INSERT INTO 表名 VALUES (value1,value2,....);

INSERT INTO 表名(column1 [, column2, …, columnn]) VALUES (value1 [,value2, …, valuen]);#指定欄位

INSERT INTO table_name 
VALUES 
(value1 [,value2, …, valuen]),
(value1 [,value2, …, valuen]),
……
(value1 [,value2, …, valuen]);#同時插入多條

值列表中需要為表的每一個欄位指定值，並且值的順序必須和資料表中欄位定義時的順序相同。

插入查詢結果：

INSERT INTO 目標表名
(tar_column1 [, tar_column2, …, tar_columnn])
SELECT
(src_column1 [, src_column2, …, src_columnn])
FROM 源表名
[WHERE condition]

更新資料(update)

UPDATE table_name
SET column1=value1, column2=value2, … , column=valuen
[WHERE condition]

• 可以一次更新多條資料。
• 如果需要回滾資料，需要保證在DML前，進行設定：SET AUTOCOMMIT = FALSE;

刪除資料(delete)

DELETE FROM table_name [WHERE <condition>];

計算列（Always as)

CREATE TABLE tb1(
id INT,
a INT,
b INT,
c INT GENERATED ALWAYS AS (a + b) VIRTUAL
);#建立時計算
ALTER TABLE tb1 MODIFY c INT GENERATED ALWAYS AS (a + b) VIRTUAL;#修改時計算

流程控制

IF: 條件語句

LOOP：一般用於實現簡單的"死"迴圈
WHILE：先判斷後執行
REPEAT：先執行後判斷，無條件至少執行一次

LEAVE:用於跳出迴圈(break)

ITERATE: 用於返回開始處繼續迴圈(continue)

遊標: 遍歷選出的列表，每次使用按順序提供一行的值(迭代器)

觸發器：由事件來觸發某個操作，這些事件包括INSERT、UPDATE、DELETE事件

IF

IF 表示式1 THEN 操作1
[ELSEIF 表示式2 THEN 操作2]……
[ELSE 操作N]
END IF

Case

CASE 表示式
WHEN 值1 THEN 結果1或語句1(如果是語句，需要加分號) 
WHEN 值2 THEN 結果2或語句2(如果是語句，需要加分號)
...
ELSE 結果n或語句n(如果是語句，需要加分號)
END [case]（如果是放在begin end中需要加上case，如果放在select後面不需要）

LOOP

加上THEN LEAVE的結構怎麼表示
[loop_label:] LOOP
迴圈執行的語句
END LOOP [loop_label]

WHILE

[while_label:] WHILE 迴圈條件  DO
	迴圈體
END WHILE [while_label];

REPEAT

[repeat_label:] REPEAT
　　　　迴圈體的語句
UNTIL 結束迴圈的條件表示式
END REPEAT [repeat_label]

LEAVE

LEAVE 標記名

實現跳出迴圈(break)

ITERATE

ITERATE label

遊標

DECLARE cursor_name CURSOR FOR select_statement; #宣告遊標
OPEN cursor_name; #開啟遊標
FETCH cursor_name INTO var_name [, var_name] ... #使用遊標
CLOSE cursor_name;

觸發器(Trigger)

CREATE TRIGGER 觸發器名稱 
{BEFORE|AFTER} {INSERT|UPDATE|DELETE} ON 表名 
FOR EACH ROW 
觸發器執行的語句塊;

舉例:

DELIMITER //

CREATE TRIGGER before_insert
BEFORE INSERT ON test_trigger 
FOR EACH ROW
BEGIN
	INSERT INTO test_trigger_log (t_log)
	VALUES('before_insert');

END //

DELIMITER ;

檢視/刪除觸發器

SHOW TRIGGERS #檢視當前資料庫中全部觸發器的定義
SHOW CREATE TRIGGER 觸發器名 #檢視當前資料庫中某個觸發器的定義
SELECT * FROM information_schema.TRIGGERS; #從系統庫information_schema的TRIGGERS表中查詢“salary_check_trigger”觸發器的資訊。


DROP TRIGGER  IF EXISTS 觸發器名稱;#刪除觸發器

檢視

建立檢視(Create)

CREATE VIEW 檢視名稱 
AS 查詢語句

修改檢視(Alter)

方式1：使用CREATE OR REPLACE VIEW 子句修改檢視

CREATE OR REPLACE VIEW empvu80
(id_number, name, sal, department_id)
AS 
SELECT  employee_id, first_name || ' ' || last_name, salary, department_id
FROM employees
WHERE department_id = 80;

說明：CREATE VIEW 子句中各列的別名應和子查詢中各列相對應。

方式2：ALTER VIEW

修改檢視的語法是：

ALTER VIEW 檢視名稱 
AS
查詢語句

刪除檢視(Drop)

• 刪除檢視只是刪除檢視的定義，並不會刪除基表的資料。

• 刪除檢視的語法是：

  DROP VIEW IF EXISTS 檢視名稱;
  

  DROP VIEW IF EXISTS 檢視名稱1,檢視名稱2,檢視名稱3,...;
  

• 舉例：

  DROP VIEW empvu80;
  

• 說明：基於檢視a、b建立了新的檢視c，如果將檢視a或者檢視b刪除，會導致檢視c的查詢失敗。這樣的檢視c需要手動刪除或修改，否則影響使用。

變數、儲存過程與函式

變數(@)

系統變數

#檢視所有全域性變數
SHOW GLOBAL VARIABLES;

#檢視所有會話變數
SHOW SESSION VARIABLES;
或
SHOW VARIABLES;

#檢視滿足條件的部分系統變數。
SHOW GLOBAL VARIABLES LIKE '%識別符號%';

#檢視滿足條件的部分會話變數
SHOW SESSION VARIABLES LIKE '%識別符號%';

#檢視指定的系統變數的值
SELECT @@global.變數名;

#檢視指定的會話變數的值
SELECT @@session.變數名;
#或者
SELECT @@變數名;

舉例：

SHOW GLOBAL VARIABLES LIKE 'admin_%';

• 修改系統變數的值

有些時候，資料庫管理員需要修改系統變數的預設值，以便修改當前會話或者MySQL服務例項的屬性、特徵。具體方法：

方式1：修改MySQL配置檔案，繼而修改MySQL系統變數的值（該方法需要重啟MySQL服務）

方式2：在MySQL服務執行期間，使用“set”命令重新設定系統變數的值

#為某個系統變數賦值
#方式1：
SET @@global.變數名=變數值;
#方式2：
SET GLOBAL 變數名=變數值;


為某個會話變數賦值
#方式1：
SET @@session.變數名=變數值;
#方式2：
SET SESSION 變數名=變數值;

使用者變數

#方式1：“=”或“:=”
SET @使用者變數 = 值;
SET @使用者變數 := 值;

#方式2：“:=” 或 INTO關鍵字
SELECT @使用者變數 := 表示式 [FROM 等子句];
SELECT 表示式 INTO @使用者變數  [FROM 等子句];

SELECT @使用者變數#檢視使用者變數的值 （檢視、比較、運算等）

區域性變數

定義：可以使用DECLARE語句定義一個區域性變數

作用域：僅僅在定義它的 BEGIN ... END 中有效

位置：只能放在 BEGIN ... END 中，而且只能放在第一句

BEGIN
	#宣告區域性變數
	DECLARE 變數名1 變數資料型別 [DEFAULT 變數預設值];
	DECLARE 變數名2,變數名3,... 變數資料型別 [DEFAULT 變數預設值];

	#為區域性變數賦值
	SET 變數名1 = 值;
	SELECT 值 INTO 變數名2 [FROM 子句];

	#檢視區域性變數的值
	SELECT 變數1,變數2,變數3;
END

儲存過程(Procedure)

	關鍵字	呼叫語法	返回值	應用場景
儲存過程	PROCEDURE	CALL 儲存過程()	理解為有0個或多個	一般用於更新
儲存函式	FUNCTION	SELECT 函式()	只能是一個	一般用於查詢結果為一個值並返回時

此外，儲存函式可以放在查詢語句中使用，儲存過程不行。反之，儲存過程的功能更加強大，包括能夠執行對錶的操作（比如建立表，刪除表等）和事務操作，這些功能是儲存函式不具備的。

語法：

DELIMITER $  #為了避免與儲存過程中SQL語句結束符相沖突，需要使用DELIMITER改變儲存過程的結束符。
CREATE PROCEDURE 儲存過程名(IN|OUT|INOUT 引數名  引數型別,...)
[characteristics ...]
BEGIN
	sql語句1;
	sql語句2;

END $

類似於Java中的方法：

修飾符 返回型別 方法名(引數型別 引數名,...){

	方法體;
}

約束條件

characteristics 表示建立儲存過程時指定的對儲存過程的約束條件，其取值資訊如下：

LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'

• LANGUAGE SQL：說明儲存過程執行體是由SQL語句組成的，當前系統支援的語言為SQL。
• [NOT] DETERMINISTIC：指明儲存過程執行的結果是否確定。DETERMINISTIC表示結果是確定的。每次執行儲存過程時，相同的輸入會得到相同的輸出。NOT DETERMINISTIC表示結果是不確定的，相同的輸入可能得到不同的輸出。如果沒有指定任意一個值，預設為NOT DETERMINISTIC。
• { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }：指明子程式使用SQL語句的限制。
  • CONTAINS SQL表示當前儲存過程的子程式包含SQL語句，但是並不包含讀寫資料的SQL語句；
  • NO SQL表示當前儲存過程的子程式中不包含任何SQL語句；
  • READS SQL DATA表示當前儲存過程的子程式中包含讀資料的SQL語句；
  • MODIFIES SQL DATA表示當前儲存過程的子程式中包含寫資料的SQL語句。
  • 預設情況下，系統會指定為CONTAINS SQL。
• SQL SECURITY { DEFINER | INVOKER }：執行當前儲存過程的許可權，即指明哪些使用者能夠執行當前儲存過程。
  • DEFINER表示只有當前儲存過程的建立者或者定義者才能執行當前儲存過程；
  • INVOKER表示擁有當前儲存過程的訪問許可權的使用者能夠執行當前儲存過程。
  • 如果沒有設定相關的值，則MySQL預設指定值為DEFINER。
• COMMENT 'string'：註釋資訊，可以用來描述儲存過程。

呼叫

儲存過程有多種呼叫方法。儲存過程必須使用CALL語句呼叫，並且儲存過程和資料庫相關，如果要執行其他資料庫中的儲存過程，需要指定資料庫名稱，例如CALL dbname.procname。

CALL 儲存過程名(實參列表)

1、呼叫in模式的引數：

CALL sp1('值');

2、呼叫out模式的引數：

SET @name;
CALL sp1(@name);
SELECT @name;

3、呼叫inout模式的引數：

SET @name=值;
CALL sp1(@name);
SELECT @name;

儲存函式(Function)

語法格式：

CREATE FUNCTION 函式名(引數名 引數型別,...) 
RETURNS 返回值型別
[characteristics ...]
BEGIN
	函式體   #函式體中肯定有 RETURN 語句

END

呼叫：

SET @dept_id = 50;
SELECT count_by_id(@dept_id);

處理程式(Condition,handler)

定義條件是事先定義程式執行過程中可能遇到的問題，處理程式定義了在遇到問題時應當採取的處理方式，並且保證儲存過程或函式在遇到警告或錯誤時能繼續執行。這樣可以增強儲存程式處理問題的能力，避免程式異常停止執行。

定義條件使用DECLARE語句，語法格式如下：

DECLARE 錯誤名稱 CONDITION FOR 錯誤碼（或錯誤條件）

可以為SQL執行過程中發生的某種型別的錯誤定義特殊的處理程式。定義處理程式時，使用DECLARE語句的語法如下：

DECLARE 處理方式 HANDLER FOR 錯誤型別 處理語句

• 處理方式：處理方式有3個取值：CONTINUE、EXIT、UNDO。
  • CONTINUE：表示遇到錯誤不處理，繼續執行。
  • EXIT：表示遇到錯誤馬上退出。
  • UNDO：表示遇到錯誤後撤回之前的操作。MySQL中暫時不支援這樣的操作。
• 錯誤型別（即條件）可以有如下取值：
  • SQLSTATE '字串錯誤碼'：表示長度為5的sqlstate_value型別的錯誤程式碼；
  • MySQL_error_code：匹配數值型別錯誤程式碼；
  • 錯誤名稱：表示DECLARE ... CONDITION定義的錯誤條件名稱。
  • SQLWARNING：匹配所有以01開頭的SQLSTATE錯誤程式碼；
  • NOT FOUND：匹配所有以02開頭的SQLSTATE錯誤程式碼；
  • SQLEXCEPTION：匹配所有沒有被SQLWARNING或NOT FOUND捕獲的SQLSTATE錯誤程式碼；
• 處理語句：如果出現上述條件之一，則採用對應的處理方式，並執行指定的處理語句。語句可以是像“SET 變數 = 值”這樣的簡單語句，也可以是使用BEGIN ... END編寫的複合語句。