查詢 語法: SELECT 標識選擇哪些列 FROM 標識從哪個表中選擇 去重(Distinct) 在SELECT語句中使用關鍵字DISTINCT去除重覆行 SELECT DISTINCT department_id FROM employees; 過濾(Where) 語法: SELECT 欄位1, ...
查詢
- 語法:
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
編寫的複合語句。