主頁 > 資料庫 > MySQL筆記

MySQL筆記

2020-09-11 15:30:39 資料庫

目錄

  • 著名資料庫（了解）
  • 概念
  • 組成（了解即可）
  • 注意點
  • DDL操作
    • 操作資料庫
    • 資料型別（列型別）
    • 操作表
  • DML（Data Manipulation Language）
  • DCL（Data Control Language）
  • DQL（Data Query Language）
  • 編碼
  • 備份與恢復
  • 約束
    • 主鍵約束
    • 非空約束和唯一約束
    • 外鍵
  • 多表查詢
    • 合并結果集
    • 連接查詢（一次查詢多張表）
  • 視圖
  • 存盤程序（類似函式）
  • 游標
  • 觸發器
  • 事務處理
  • 索引

著名資料庫（了解）

公司 資料庫軟體
IBM DB2
微軟 SQL Server、Access[1]
Oracle Oracle、MySQL
Sybase Sybase

概念

  1. RDBMS（Ralational DataBase Management System）
    = Manager + Database
  2. Database = n tables
  3. table = 表結構 + 表關系

組成（了解即可）

  1. 資料查詢語言（DQL:Data Query Language）：其陳述句也稱為“資料檢索陳述句”，用以從表中獲得資料，確定資料怎樣在應用程式給出，保留字SELECT是DQL（也是所有SQL）用得最多的動詞，其他DQL常用的保留字有WHERE，ORDER BY，GROUP BY和HAVING，這些DQL保留字常與其他型別的SQL陳述句一起使用，
  2. 資料操作語言（DML：Data Manipulation Language）：其陳述句包括動詞INSERT，UPDATE和DELETE，它們分別用于添加，修改和洗掉表中的行，也稱為動作查詢語言，
  3. 事務處理語言（TPL）：它的陳述句能確保被DML陳述句影響的表的所有行及時得以更新，TPL陳述句包括BEGIN TRANSACTION，COMMIT和ROLLBACK，
  4. 資料控制語言（DCL）：它的陳述句通過GRANT或REVOKE獲得許可，確定單個用戶和用戶組對資料庫物件的訪問，某些RDBMS可用GRANT或REVOKE控制對表單個列的訪問，
  5. 資料定義語言（DDL）：其陳述句包括動詞CREATE和DROP，在資料庫中創建新表或洗掉表（CREAT TABLE 或 DROP TABLE）；為表加入索引等，
  6. 指標控制語言（CCL）：其陳述句，像DECLARE CURSOR，FETCH INTO和UPDATE WHERE CURRENT用于對一個或多個表單行的操作，

注意點

  1. MySQL中不區分大小寫，但建議關鍵字全大寫，資料庫、表名等全小寫，以提高可讀性
  2. MySQL進行WHERE條件匹配、ORDER BY排序時默認不區分大小寫，若需區分大小寫，可在字串前增加BINARY
  3. 清屏命令：Linux上system clear；Windows上system cls (需要MySQL 8以上)
  4. 后文MySQL陳述句中的中括號[]表示可選項，即可以省略

DDL操作

操作資料庫

  1. 顯示資料庫：SHOW DATABASES;
  2. 選擇資料庫：USE 表名;
  3. 創建資料庫：CREATE DATABASE [IF NOT EXISTS] 表名 [CHARSET=utf8]; （如果不寫IF陳述句，且資料庫已存在，則報錯，后面的表操作同理）
  4. 洗掉資料庫：DROP DATABASE [IF EXISTS] 表名;（如果不寫IF陳述句，且資料庫不存在，則報錯，后面的表操作同理）
  5. 修改資料庫編碼：ALTER DATABASE 表名 CHARACTER SET utf8;

資料型別（列型別）

必須為每一列指定資料型別

注意：MySQL中字串只能加單引號

資料型別 說明
int 整型
double(5,2) 浮點型，5表示最大總位數（不包括小數點），2表示小數點后位數
decimal 浮點型，不損失精度，表單中錢用得較多
char(255) 固定長度字符，長度不足時補足，超出長度報錯，最大支持255個位元組
varchar(65535) 變長字符，長度不足時不補足，超長報錯，最大支持65535 bytes，會額外使用1個位元組存盤長度資訊
text 不在SQL標準中（標準里的是clob），分tinytext (2^8^-1，255B)、text (2^16^-1，65k)、mediumtext (2^24^-1，16M)、longtext (2^32^-1，4G)
blob 用于存盤二進制資料，分tinyblob (2^8^-1)、blob (2^16^-1)、mediumblob (2^24^-1)、longblob (2^32^-1)
date 格式：YYYY-MM-DD
time 格式：hh:mm:ss
timestamp date + time
... ...

操作表

  1. 顯示表：SHOW TABLES;

  2. 顯示表結構：DESC 表名;

  3. 創建表

    • 當表列允許為空時，默認值為NULL，可以在創建表時通過DEFAULT關鍵字修改默認值（MySQL只支持常量，不支持函式）（建議設定默認值，而不是采用NULL列，特別是用于計算和資料分組的列）
    • MySQL具有多種引擎，如
      • InnoDB 默認引擎，支持事務處理，不支持全文搜索
      • MEMORY 功能上等同于MyISAM，但資料存盤在記憶體，速度很快（特別適合用于臨時表）
      • MyISAM 性能極高的引擎，支持全文本搜索，不支持事務處理
      • 其他引擎
    • 一個資料庫的不同表可以采用不同的引擎，但是外鍵不能跨引擎，即用一個引擎的表不能參考使用不同引擎的表的外鍵
    CREATE TABLE [IF NOT EXISTS] 表名(
	列名 列型別 [DEFAULT def_val1],
	列名 列型別 [DEFAULT def_val2]，
	...
	列名 列型別
)[ENGINE=InnoDB];

  4. 洗掉表：DROP TABLE [IF EXISTS] 表名;

  5. 修改表（前綴：ALTER TABLE 表名）：

    • 添加列

      ALTER TABLE 表名 ADD(
	列名 列型別,
	列名 列型別，
	...
	列名 列型別
);

    • 洗掉列：ALTER TABLE 表名 DROP 列名;

    • 修改列型別：ALTER TABLE 表名 MODIFY 列名 列型別;

    • 修改列名：ALTER TABLE 表名 CHANGE 原列名 新列名 列型別;

    • 修改表名稱：ALTER TABLE 表名 RENAME TO 新表名;

    • 應盡量在一開始就確定好表定義，避免后期對表進行大的改動

    • 復雜表結構的修改一般需要手動洗掉程序：

      1. 用新的列布局創建新表
      2. 用INSERT SELECT陳述句從舊表復制資料到新表，如有必要，可使用轉換函式和計算欄位
      3. 檢驗包含所需資料的新表
      4. 重命名舊表（如果確定，也可洗掉它）
      5. 用舊表名重命名新表；
      6. 根據需要，重新創建觸發器、存盤程序、索引和外鍵

  6. 更改表名：RENAME TABLE 舊表名1 TO 新表名1, 舊表名2 TO 新表名2, ...

DML（Data Manipulation Language）

  1. 插入一行record

    • INSERT INTO 表名(列名1, 列名2, 列名3, ...) VALUES(值1, 值2, 值3, ...) →不要求包含所有列及列順序，但值和列的順序必須對應

    • INSERT INTO 表名 VALUES(值1, 值2, 值3, ...) → 要求包含所有列，且按默認順序（不安全，不推薦）

    • 插入多個行：VALUES后面可以添加多個行，以逗號分隔
      即INSERT INTO 表名(列名1, 列名2, 列名3, ...) VALUES(值1, 值2, 值3, ...),(值a, 值b,...), ...

    • 插入檢索出的資料

      INSERT INTO tableName(col1, col2, ...)
SELECT colA,colB,...
FROM someTable
WHERE condition

  2. 修改資料

    • UPDATE 表名 SET 列名1=值1,列名2=值2,... WHERE 條件 （如果沒有WHERE部分，則會修改所有行！！）
    • 即使發生錯誤也繼續更新 ? 在UPDATE后面添加IGNORE關鍵字（否則多行更新時，一行發生錯誤，該操作所有的更改都會被取消）
    • 為洗掉某列的值，可將其設定為NULL（只要表定義允許）
    • 條件為boolean值，運算子有 =、!=、<>、<、>、<=、>=、BETWEEN ... AND ...（包含開始和結束值） 、IN(...)、IS NULL、NOT（MySQL中NOT只限用于IS NOT NULL、 IN、BETWEEN、EXISTS取反）、AND、OR （注意： 判斷是否為NULL不能用= NULL，而必須用IS NULL；AND優先級高于OR）

  3. 洗掉資料

    • DELETE FROM 表名 WHERE 條件（如果沒有WHERE，則洗掉表中所有資料！！）
    • 洗掉所有行 TRUNCATE 表名 （速度比DELETE快，因為TRUNCATE實際上是洗掉原表，創建新空表）

  4. 多用戶訪問時，資料的增刪改可能影響資料檢索速度，如果資料檢索最為重要，可通過在INSERT、UPDATE、DELETE后加上關鍵字LOW_PRIORITY降低它們的優先級

DCL（Data Control Language）

  1. 一個專案創建一個用戶，只對應一個資料庫，這個用戶只能對這個資料庫有權限，其他資料庫無法操作

  2. 創建用戶

    CREATE USER 用戶名@IP（或localhost) IDENTIFIED BY '密碼';
-- 用戶只能在指定的IP上登錄
CREATE USER 用戶名@'%' IDENTIFIED BY '密碼';
-- 用戶可以在任意IP上登錄

  3. 給用戶授權

    GRANT 權限1[, 權限2, ...] ON 資料庫.* TO 用戶名@地址
-- 例： GRANT SELECT, CREATE, ALTER, DROP, INSERT, UPDATE, DELETE ON mydb1.* TO user1@localhost;
GRANT ALL ON 資料庫.* TO 用戶名@IP地址

  4. 撤銷授權

    REVOKE 權限1[, 權限2, ...] ON 資料庫.* FROM 用戶名@IP地址;

  5. 查看權限 SHOW GRANTS ON 用戶名@IP地址;

  6. 洗掉用戶 DROP USER 用戶名@IP地址;

DQL（Data Query Language）

查詢不會修改資料庫表記錄

  1. 欄位（列）控制

    • 查詢所有列 SELECT * FROM 表名;

    • 查詢指定列 SELECT 列1[, 列2, ...] FROM 表名;

    • 完全重復的記錄只顯示一次 SELECT DISTINCT * | 列1[, 列2, ...] FROM 表名;

    • 列運算

      • 數值型別的列可以做加、減、乘、除運算，如SELECT *，salary*1.5 FROM mydb1;，非數值型別按0處理，NULL值與任何值運算都得到NULL

      • 字串連接用CONCAT(str1, str2, ...)函式(其他多數DBMS采用+或||連接字串)

      • NULL與任何值運算、連接字串都得NULL，處理NULL值可用IFNULL(列名, 默認值)替換NULL值為默認值

      • LTRIM(字串) 可以洗掉字串左側空格，RTRIM為右側，TRIM為兩側

      • 可用不加FROM的陳述句SELECT 列運算式 進行測驗運算，如

        SELECT 2*3;
SELECT Trim(' abc ');
SELECT Now(); -- 回傳當前日期和時間

      • 其他函式[2]

        1. 文本處理函式

          函式 說明
          Left(str, len) / Right(str, len) 回傳字串左側 / 右側len個字符
          Length(str) 回傳字串長度
          LTrim(str) / RTrim(str) / Trim(str) 洗掉字串左 / 右 / 兩側字串
          Soundex(str) 回傳str的soundex值（發音）
          Lower(str) / Upper(str) 轉換字串為小寫 / 大寫
          Substring(str, n, len) 回傳第n個字符（包含）開始的len個字符的字串，n為負值時表示倒數第n個
          Locate(substr, str) 回傳字串第一次出現在str中的位置

        2. 時間處理函式

          函式 說明
          CurDate() / CurTime() / Now() 回傳當前日期 / 時間 / 日期時間
          Date(datetime) / Time(datetime) 提取日期時間的日期部分 / 時間部分
          Year(dt) / Month(dt) / Day(dt) 回傳日期時間的年 / 月 / 日
          Hour(dt) / Minute(dt) / Second(dt) 回傳日期時間的時 / 分 / 秒
          DayOfWeek(dt) 回傳星期幾（星期日為1，星期六為7）
          DateDiff(end_dt, start_dt) 回傳兩日期時間差
          AddDate(dt,INTERVAL expr type) 增加一個expr type日期和時間
          AddTime(dt, expr) 增加一個expr(hh:mm:ss)時間

        3. 數值處理函式

          函式 說明
          Sin(x) / Cos(x) / Tan(x) 正弦值 / 余弦值 / 正切值
          Abs(x) 絕對值
          Pi() 圓周率
          Mod() 模運算，相當于%
          Rand() 回傳0~1之間的亂數
          Sqrt(x) 平方根
          Exp(x) 求自然常數e的x次方

      • 更多函式及用法可參閱：MySQL常用函式大全

    • 起別名：(AS 可以省略)，也可用于給表起別名

      SELECT 列名 (AS) 別名 FROM 表名; -- as關鍵字可以省略
-- 如SELECT salary*1.5 as 工資 FROM employees;

  2. 條件控制

    • 條件查詢 （添加WHERE條件）

    • 模糊查詢：通過LIKE關鍵字和_%通配符實作(_匹配一個字符；%匹配0到n個字符，但不匹配NULL)

      • 模糊搜索的時間開銷相比一般條件查詢更大
      • 不要過度使用通配符，若能用其他運算子實作，應使用其他運算子
      • 使用通配符時，除非絕對有必要，否則不要將通配符放置在搜索模式的開始處，這樣搜索起來最慢
      SELECT * FROM employees WHERE name LIKE '張%'; -- 匹配如張、張三、張某某
SELECT * FROM employees WHERE name LIKE '張__';  -- 匹配如張某某，但不匹配張三

    • 正則運算式：通過 REGEXP 匹配字串 實作

      • 與LIKE的區別：LIKE是整行匹配；REGEXP只要行內字串滿足匹配字串即可，若匹配字串首尾同時添加 ^ 和 $ 則變成整行匹配

      • 與多數其他軟體不同，匹配特殊字符，如.[]()|等，需要添加兩個反斜杠\\，如\\.，\\n，\\\（匹配 \ 本身）、\\1（反向參考）等（一個 \ 自己解釋一個，正則運算式庫解釋另一個）

      • 字符集

        類 說明
        [:alpha:] 任意字符（同[a-zA-Z]）
        [:digit:] 任意數字（同[0-9]）
        [:alnum:] 任意字符和數字（同[a-zA-Z0-9]）
        [:xdigit:] 任意十六進制數字（同[a-fA-F0-9]）
        [:lower:] 任意小寫字母（同[a-z]）
        [:upper:] 任意大寫字母（同[A-Z]）
        [:blank:] 空格和制表符（同[ \\t])
        [:space:] 包含空格在內的任意空白字符（同[\\f\\n\\r\\t\\v]）
        [:print:] 任意可列印字符
        [:graph:] 除空格外的任意可列印字符
        [:cntrl:] ASCII控制字符（ASCII 0-31和127）
        [:punct:] 即不在[:alnum:]，也不在[:cntrl:]中的字符

      • 正則運算式內容較多，用法可參見 正則運算式30分鐘入門教程

      • MySQL中可用SELECT '待匹配字串' REGEXP '匹配樣式字串' 簡單測驗（結果1為匹配，0為不匹配）

    • 排序：ORDER BY 列名1 [ASC/DESC], ... （ASC升序，DESC降序，省略時默認ASC）

      • 先按第一個條件排序，第一個條件相同時，按第二個條件，依此類推，

    • 聚合查詢：利用聚合函式縱向查詢某一列的非空行數COUNT、SUM、MAX、MIN、AVG
      注意：

      • COUNT(列名) 記錄該列非NULL的行數，若為COUNT(*) 則為包含NULL的所有行數；
      • 聚合查詢一般忽略NULL值
      • 函式引數可為 DISTINCT 列名，此時重復值只計算一次
      SELECT COUNT(salary) 計數,SUM(DISTINCT salary) 總和, MAX(salary) 最高, MIN(salary) 最低, AVG(salary) 平均 
FROM employees 
WHERE department = '財務部';

    • 分組查詢 GROUP BY -- 只用于查詢組資訊

      • 用法：SELECT 列名1[,列名2，...], 聚合函式 FROM 表名 GROUP BY 列名1[,列名2,...]`
      • 在GROUP BY前通過WHERE 列條件過濾行，之后則使用HAVING 關鍵字過濾組資訊（除位置外，HAVING用法與WHERE基本相同）
      • GROUP BY 列 可接WITH ROLLUP，增加一行匯總行
      • 結果不一定有序，最好結合ORDER BY
      -- 列出各個崗位里工資大于15000的人數，且滿足要求的崗位人數必須大于1人
SELECT job, COUNT(job) FROM employees 
WHERE salary > 15000 
GROUP BY job 
HAVING count(job) > 1;

    • MySQL方言之LIMIT（僅MySQL支持）

      • LIMIT [row - 1,] n 顯示從第row行（下標0為第一行）開始的n行資料

編碼

  1. 顯示編碼方式 SHOW VARIABLES LIKE 'char%';

  2. 設定編碼方式：

    • 若在互動視窗修改，則僅當次有效，退出mysql即恢復原樣；若在my.ini中修改，則為永久生效
    /* 互動視窗修改 */
SET character_set_client=utf8; -- 設定客戶端編碼方式
SET character_set_results=utf8; -- 設定mysql回傳的資料編碼格式

/* my.ini中修改 */
default-character-set=utf8 -- 修改默認字符集，包括client、results、server
character-set-server=utf8

備份與恢復

  1. 備份
    • 在cmd中使用mysqldump -u用戶 -p[密碼] 資料庫名> 保存路徑（含檔案名）
      如：mysqldump -uroot -p123 mydb1 > C:/a.sql
    • 只是保持資料庫內容，不保存資料庫本身，后期恢復時需要先CREATE DATABASE
  2. 恢復
    • 若資料庫不存在，需要先創建
    • 方式1：cmd輸入mysql -u用戶 -p 資料庫名 < 匯入sql檔案路徑（含檔案名）
      如：mysql -uroot -p123 mydb1 < C:/a.sql
    • 方式2：mysql視窗使用source 匯入的sql檔案路徑
      如：source C:/a.sql

約束

主鍵約束

  1. 含義：唯一標識一行記錄的列元素（不建議用自然主鍵，如身份證號，避免后期出錯）

  2. 特點：

    • 非空
    • 唯一
    • 可以被參考（外鍵）

  3. 使用方式

    -- 創建表時指定的兩種方式：
CREATE TABLE emp(
	empno INT PRIMARY KEY,
    ename VARCHAR(50)
)

CREATE TABLE emp(
	empno INT,
    ename VARCHAR(50)，
    PRIMARY KEY(empno)
)

-- 通過修改表指定/洗掉
ALTER TABLE emp ADD PRIMARY KEY(empno);
ALTER TABLE emp DROP PRIMARY KEY;

  4. 可以用多列作為主鍵，那么只要其組合唯一即可（在CREATE TABLE時PRIMARY KEY(列1, 列2, ...) )

  5. 自增長

    • 自動在前一個記錄的基礎上，該列數值加1（要求該列必須為INT型）

    • 主鍵設為AUTO_INCREMENT時，該行插入值可為NULL，系統自動根據上一次的值加1

    • 每張表只允許設定一個AUTO_INCREMENT列，且該列必須被索引（比如，通過使其成為主鍵）

    • 分布式系統中不建議自增長，可改用UUID

      CREATE TABLE emp(
	empno INT PRIMARY KEY AUTO_INCREMENT, 
    ename VARCHAR(50)
)

    • 查看最后插入的AUTO_INCREMENT值： SELECT last_insert_id()

非空約束和唯一約束

  1. 非空約束NOT NULL，唯一約束UNIQUE(允許為空用NULL，因為是默認值，也可省略)

    CREATE TABLE emp(
	empno INT PRIMARY KEY, 
    ename VARCHAR(50) NOT NULL UNIQUE
)

外鍵

  1. 關系模型

    • 一對一（較少見）
    • 一對多
    • 多對多

  2. 外鍵特點

    • 外鍵必須參考本表或另一張表的主鍵（多方參考一方）
    • 外鍵可以為空、可以重復，但所參考的值必須存在
    • 一張表中可以存在多個外鍵

  3. 建立方式

    • 一對多

      -- 創建時定義：
CREATE TABLE emp(
	empno INT PRIMARY KEY, -- 職員編號
    ename VARCHAR(50), -- 職員姓名
    dept_no INT, -- 所屬部門
    CONSTRAINT 自定義約束名 FOREIGN KEY(dept_no) REFERENCES dept(deptno); 
    -- 參考dept表的deptno列
)
-- 修改已有列：
ALTER TABLE emp 
ADD CONSTRAINT 自定義約束名 FOREIGN KEY(dept_no) REFERENCES dept(deptno);

    • 一對一

      CREATE TABLE husband(
	hid INT PRIMARY KEY AUTO_INCREMENT,
    hname VARCHAR(50)
)
CREATE TABLE wife(
	wid INT PRIMARY KEY AUTO_INCREMENT,
    wname VARCHAR(50),
    CONSTRAINT fk_wife_hus FOREIGN KEY(wid) REFERENCES husband(hid)
)
-- wid對應hid，由于wid為主鍵，故唯一非空，wid又為husband表外鍵（參考hid），故參考值必須存在，由此可實作一對一關系

    • 多對多
      需要借助第三張表作為關聯表，記錄兩張表主鍵的關系

      CREATE TABLE teacher(
	tid INT PRIMARY KEY AUTO_INCREMENT, -- 老師ID
    name VARCHAR(50)
)
CREATE TABLE student(
	sid INT PRIMARY KEY AUTO_INCREMENT,　-- 學生ID
    name VARCHAR(50)
)
CREATE TABLE tie(  -- 關聯表
    tid INT,
	sid INT,
    CONSTRAINT fk_tea FOREIGN KEY(tid) REFERENCES teacher(tid)
    CONSTRAINT fk_stu FOREIGN KEY(sid) REFERENCES student(sid)
)

多表查詢

對于復雜的多表查詢，可以先拆解成子問題，然后通過組合子問題逐步解決，以此減小查詢的難度

合并結果集

  • 用于將兩個結果集拼接在一起顯示

  • 要求結果集列數&列型別相同

  • 語法: （通過UNION [ALL] 拼接多個SELECT ... FROM ... 陳述句，不加ALL會對緊鄰的兩個結果集的行進行去重）

    CREATE TABLE ab(a INT, b VARCHAR(50));
CREATE TABLE cd(c INT, d VARCHAR(50));
...  -- 此處添加資料
SELECT * FROM ab UNION ALL SELECT * FROM cd;
SELECT a FROM ab UNION SELECT c FROM cd UNION ALL SELECT a FROM ab;

連接查詢（一次查詢多張表）

多表查詢注意使用完全限定列名（即表名.列名），主要是涉及外部查詢（參考其他表）、列名存在二義性時

  1. 內連接（從多張表的笛卡爾積[3]中篩選出滿足條件的表）

    • 方言：SELECT * FROM 表1 別名1, 表2 別名2 WHERE 別名1.xx = 別名2.xx
    • 標準（推薦）：SELECT * FROM 表1 別名1 INNER JOIN 表2 別名2 ON 別名1.xx = 別名2.xx
      • 大于兩張表連接時，每連接一張表，后面就用ON條件限定一次，若有WHERE條件，則放在所有連接之后
    • 自然（可讀性降低）：SELECT * FROM 表1 別名1 NATURAL JOIN 表2 別名2 （由系統自動根據兩表相同的列名連接）
    -- 查找滿足條件的所有員工的名稱、工資、及所在部門資訊
-- 方言
SELECT ename, salary, dname
FROM emp e, dept d
WHERE e.deptno = d.deptno
-- 標準
SELECT ename, salary, dname
FROM emp e INNER JOIN dept d
ON e.deptno = d.deptno
-- 自然
SELECT ename, salary, dname
FROM emp e NATURAL JOIN dept d

  2. 外連接

    • 左外連接
      • 左表無論是否滿足條件都會查詢出來（至少顯示1次），而右表只有滿足條件才能出來，左表中不滿足條件的記錄，右表部分均為NULL
      • SELECT * FROM 表1 別名1 LEFT OUTER JOIN 表2 別名2 ON 表1/別名1.xx = 表2/別名2.xx
    • 右外連接：SELECT * FROM 表1 別名1 RIGHT OUTER JOIN 表2 別名2 ON 表1/別名1.xx = 表2/別名2.xx
    • 全外連接
      • 左右表記錄無論是否滿足條件都至少顯示一次，不滿足條件的，另一部分補NULL
      • MySQL中不支持，但可以通過UNION連接左外連接和右外連接得到
    • 左外自然連接和右外自然連接（不需要寫ON條件）：NATURAL LEFT/RIGHT OUTER JOIN
    • 大于兩張表連接時，每連接一張表，后面就用ON條件限定一次，若有WHERE條件，則放在所有連接之后
    -- 查找所有員工的名稱、工資、及所在部門資訊（無論員工是否滿足條件）
SELECT ename, salary, dname
FROM emp e LEFT OUTER JOIN dept d -- 左外連接
ON e.deptno = d.deptno

-- 顯示所有部門的名稱，及部門職工名稱、工資（無論部門是否滿足條件）
SELECT ename, salary, dname
FROM emp e RIGHT OUTER JOIN dept d -- 右外連接
ON e.deptno = d.deptno

-- 全外連接
SELECT ename, salary, dname
FROM emp e LEFT OUTER JOIN dept d
ON e.deptno = d.deptno
UNION  -- 不加ALL以去重
SELECT ename, salary, dname
FROM emp e RIGHT OUTER JOIN dept d
ON e.deptno = d.deptno

  3. 子查詢（查詢中嵌入查詢，需要包裹在圓括號中）

    • SELECT陳述句出現在WHERE之后，作為條件存在

      • 單行單列：作為值存在，可用 =、!=、<>、<、>、<=、>=比較

        SELECT * FROM 表1 別名1 
WHERE 列1 [=、!=、>、<、>=、<=] (SELECT 列 FROM 表2 別名2 WHERE 條件);
 -- 例：查詢公司中工資最高的員工資訊
SELECT * FROM emp 
WHERE sal = (SELECT MAX(sal) FROM emp);

      • 多行單列：作為多個值存在，可在括號前加ALL、ANY、IN

        SELECT * FROM 表1 別名1 
WHERE 列1 [運算子 ALL/ANY、IN] (SELECT 列 FROM 表2 別名2 WHERE 條件);

      • 單行多列：作為一個物件存在

        SELECT * FROM 表1 別名1 
WHERE (列1,列2) IN (SELECT 列1,列2 FROM 表2 別名2 WHERE 條件);

    • SELECT陳述句出現在FROM之后，作為表存在（多行多列）

      SELECT * FROM 表1 別名1, (SELECT ...) 別名2 WHERE 條件

    • 對于復雜的子查詢，可通過由內而外逐步除錯，以避免出錯，以便于糾錯

視圖

  1. 視圖本身不包含任何列或資料，它包含的是一個SQL查詢，構成了一個虛擬的動態table

  2. 用處

    • 重用MySQL陳述句，簡化復雜的SQL操作
    • 保護資料，可以給用戶授予表特定部分的訪問權限，而不是整張表
    • 更改資料格式和表示

  3. 規則

    • 名稱唯一
    • 可嵌套（利用已有視圖構建新視圖）
    • 若視圖內外均有ORDER BY，視圖內ORDER BY將被覆寫
    • 視圖不能索引，不能有觸發器和默認值
    • 可與表一起使用

  4. 陳述句

    • 創建： CREATE [OR REPLACE] VIEW viewname AS command
    • 洗掉：DROP VIEW viewname
    • 查看創建視圖的陳述句：SHOW CREATE VIEW viewname

  5. 示例

    -- 查詢購買了特定產品的顧客資訊(簡化聯結)
CREATE VIEW productcustomers AS
SELECT cust_name, cust_contact, prod_id 
FROM customers, orders, orderitems
WHERE customers.cust_id = orders.cust_id AND orderitems.order_num = orders.order_num;

SELECT cust_name, cust_contact 
FROM productcustomers 
WHERE prod_id = 'TNT2';

-- 格式化欄位
CREATE view vendorlocations AS
SELECT concat(RTrim(vend_name), '(', RTrim(vend_country),')') 
FROM vendors 
ORDER BY vend_name;

-- 獲取顧客郵箱及對應的顧客資訊（過濾資料）
CREATE view customermaillist AS 
SELECT cust_id, cust_name, cust_email 
FROM customers 
WHERE cust_email IS NOT NULL;

  6. 更新資料（比較少用）

存盤程序（類似函式）

Example:

-- 名稱: ordertotal
-- 變數: onumber - 訂單號
-- 		taxable - 0代表不收稅，1代表收稅
-- 		ototal - 訂單總額
DROP PROCEDURE ordertotal; /* 洗掉原有PROCEDURE */
DELIMITER // /* 更改陳述句終止符（除\符號外均可），否則MySQL會錯誤處理存盤程序內的分號 */
CREATE PROCEDURE ordertotal(
    IN onumber INT,  /* 輸入引數 */
    IN taxable BOOLEAN,
    OUT ototal DECIMAL(8, 2) /* 輸出引數 */
) COMMENT '獲取訂單總額，選擇性加稅' /* 注釋，用于show procedure status */
BEGIN
    -- 定義區域變數total
    DECLARE total DECIMAL(8, 2);
    -- 定義稅率百分點
    DECLARE taxrate INT DEFAULT 6;
    
    -- 獲取訂單總額
    SELECT SUM(item_price*quantity)
    FROM orderitems
    WHERE order_num = onumber
    INTO total; /* 將值賦給total */
    
    -- 是否征稅
    IF taxable THEN
        SELECT total * taxrate / 100 + total INTO total;
    END IF;
    
    -- 將total保存到輸出變數ototal
    SELECT total INTO ototal;
END//
DELIMITER ;
CALL ordertotal(20005, 0, @total); /* 用CALL呼叫存盤程序，所有MySQL變數以@開始 */
SELECT @total; 
SHOW PROCEDURE STATUS LIKE 'ordertotal'; /* 顯示存盤程序資訊 */
SHOW CREATE PROCEDURE ordertotal; /* 顯示存盤程序內容 */

  1. 用處：某個完整操作需要根據情況選擇性執行多條陳述句中的部分陳述句時，利用存盤程序重用代碼

  2. 通過CALL 程序名(引數表);呼叫

  3. 存盤程序中IF用法：

    IF statement1 THEN
...
[ELSEIF statement2 THEN
...]
[ELSE
...]
END IF;

游標

Example:

/* 創建ordertotals表；游標遍歷訂單，計算每個訂單號對應的訂單總額；將訂單號和對應的訂單總額存入ordertotals表中 */
DELIMITER //
CREATE PROCEDURE processorders()
BEGIN
    -- 定義本地變數
    DECLARE done BOOLEAN DEFAULT 0; -- 標識游標是否到表末尾
    DECLARE o INT; -- 用于存盤訂單號
    DECLARE t DECIMAL(8, 2); -- 用于存盤訂單總額
    
    -- 定義游標(名稱：ordernumbers）
    DECLARE ordernumbers CURSOR
    FOR
    SELECT order_num FROM orders;
    
    -- 定義持續句柄
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
    /* 當MySQL到達02000的狀態（沒有更多行可訪問）時，設變數done為1 */
    
    -- 創建表以存盤結果
    CREATE TABLE IF NOT EXISTS ordertotals(order_num INT, total DECIMAL(8, 2));
    
    -- 打開游標
    OPEN ordernumbers;
    
    -- 遍歷所有行
    REPEAT
        -- 獲取訂單號
        FETCH ordernumbers INTO o;
        /* FETCH用于將所需列資料存盤到指定變數o，并自動切到下一行 */
        
        -- 獲取總額
        CALL ordertotal(o, 1, t);
        
        -- 將訂單號及總額插入表
        INSERT INTO ordertotals(order_num, total) 
        VALUES(o, t);
        
        -- 結束回圈
        UNTIL done END REPEAT;
        
    -- 關閉游標（以釋放記憶體，或等到END陳述句自動關閉）
    CLOSE ordernumbers;
END//
DELIMITER ;

/* SELECT * FROM ordertotals;
+-----------+---------+
| order_num | total   |
+-----------+---------+
|     20005 |  158.86 |
|     20009 |   40.78 |
|     20006 |   58.30 |
|     20007 | 1060.00 |
|     20008 |  132.50 |
|     20008 |  132.50 |
+-----------+---------+
*/
  1. 用處
    • 在檢索出來的資料中前進/后退一行或多行
    • 對行進行批處理
  2. MySQL中游標只能用于存盤程序
  3. 定義順序（否則出錯）：區域變數 ? 游標 ? 句柄
  4. FETCH用于將所需列資料存盤到指定變數，并自動切到下一行
  5. SQLSTATE錯誤代碼詳見：SQLSTATE values and common error codes、Error Messages and Common Problems

觸發器

  1. 僅限用于當DB資料發生增（INSERT）/刪（DELETE）/改（UPDATE）時發生回應，自動執行指定的一潭訓多條MySQL陳述句，其他MySQL陳述句不支持觸發器

  2. 格式：CREATE TRIGGER 觸發器名 BEFORE/AFTER INSERT/DELETE/UPDATE ON 表名 [FOR EACH ROW] 回應操作

  3. 創建觸發器需要4條資訊

    • 唯一的觸發器名（表內）
    • 觸發器發生時間（BEFORE/AFTER）
    • 觸發器應該回應的活動（INSERT/DELETE/UPDATE）
    • 觸發器關聯的表

  4. 每張表最多可創建6個觸發器（增/刪/改 & 之前/之后）

  5. 觸發器失敗：如果BEFORE觸發器失敗，則MySQL不會執行請求的操作；如果BEFORE觸發器或操作陳述句本身失敗，則不會執行AFTER觸發器（如果有的話）

  6. 可用BEGIN...END陳述句包裹多條觸發器回應陳述句

  7. MySQL 5之后的版本不支持輸出資料集（輸出SELECT的結果），需將其保存到相應的變數

  8. 示例

    -- 在每次向products表中插入一行資料后，輸出相應資訊到info變數
CREATE TRIGGER newproduct AFTER INSERT ON products 
FOR EACH ROW SELECT 'Product added' INTO @info;

-- 顯示資料庫下的觸發器
SHOW TRIGGERS;

-- 洗掉觸發器(觸發器不支持更新，更改觸發器內容需先洗掉后創建)
DROP TRIGGER newproduct;

  9. INSERT觸發器

    • INSERT觸發器內可參考一個名為NEW的虛擬表，訪問被插入的行
    • 在BEFORE INSERT觸發器內，NEW中的值可以被更新（允許更改被插入的值）
    • 對于AUTO_INCREMENT列，NEW在INSERT執行前該列為0，插入后為新的自動生成值
    -- 每插入一行后，獲取自動生成的訂單號到no變數
CREATE TRIGGER neworder AFTER INSERT ON orders
FOR EACH ROW SELECT NEW.order_num INTO @no;

  10. DELETE觸發器

  • DELETE觸發器內可參考一個名為OLD的虛擬表，訪問被洗掉的行
  • OLD中的值全部是只讀的，不能更改
-- 每次洗掉訂單一行記錄時存檔
DELIMITER //
CREATE TRIGGER deleteorder BEFORE DELETE ON orders
FOR EACH ROW
BEGIN /* 可利用BEGIN ... END執行一條/多條MySQL陳述句 */
    INSERT INTO archive_orders(order_num, order_date, cust_id) 
    VALUES(OLD.order_num, OLD.order_date, OLD.cust_id);
END//
DELIMITER ;

  1. UPDATE觸發器

    • UPDATE觸發器中，可以參考一個名為OLD的虛擬表訪問更新前的資料，參考NEW虛擬表訪問用于更新的資料）
    • OLD表資料只讀，不可更改
    • BEFORE UPDATE中，NEW表資料可以被修改（允許更改被插入的值）
    -- 保證州名縮寫總是大寫
CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors
FOR EACH ROW
SET NEW.vend_state = UPPER(NEW.vend_state);

  2. 補充

    • 觸發器中不支持CALL陳述句，即無法直接呼叫存盤程序
    • 只有表才支持觸發器，視圖不支持
    • BEFORE觸發器主要用于資料驗證和凈化

事務處理

索引

  1. Access為桌面型資料庫，主要用于局域網；其余的為C/S型（客戶端/服務器型） ??

  2. 函式在不同DBMS間可移植性較差，撰寫sql腳本時最好標注清楚 ??

  3. 笛爾積：{a, b, c} x {1, 2} = {a1, a2, b1, b2, c1, c2} ??

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/6052.html

標籤：MySQL

上一篇：DQL查詢陳述句

下一篇：為什么要避免大事務以及大事務如何解決？

標籤雲
其他(157675) Python(38076) JavaScript(25376) Java(17977) C(15215) 區塊鏈(8255) C＃(7972) AI(7469) 爪哇(7425) MySQL(7132) html(6777) 基礎類(6313) sql(6102) 熊猫(6058) PHP(5869) 数组(5741) R(5409) Linux(5327) 反应(5209) 腳本語言(PerlPython)(5129) 非技術區(4971) Android(4554) 数据框(4311) css(4259) 节点.js(4032) C語言(3288) json(3245) 列表(3129) 扑(3119) C++語言(3117) 安卓(2998) 打字稿(2995) VBA(2789) Java相關(2746) 疑難問題(2699) 细绳(2522) 單片機工控(2479) iOS(2429) ASP.NET(2402) MongoDB(2323) 麻木的(2285) 正则表达式(2254) 字典(2211) 循环(2198) 迅速(2185) 擅长(2169) 镖(2155) 功能(1967) .NET技术(1958) Web開發(1951) python-3.x(1918) HtmlCss(1915) 弹簧靴(1913) C++(1909) xml(1889) PostgreSQL(1872) .NETCore(1853) 谷歌表格(1846) Unity3D(1843) for循环(1842)

熱門瀏覽
  • GPU虛擬機創建時間深度優化

    **?桔妹導讀：**GPU虛擬機實體創建速度慢是公有云面臨的普遍問題，由于通常情況下創建虛擬機屬于低頻操作而未引起業界的重視，實際生產中還是存在對GPU實體創建時間有苛刻要求的業務場景。本文將介紹滴滴云在解決該問題時的思路、方法、并展示最終的優化成果。 從公有云服務商那里購買過虛擬主機的資深用戶，一 ......

    uj5u.com 2020-09-10 06:09:13 more
  • 可編程網卡芯片在滴滴云網路的應用實踐

    **?桔妹導讀：**隨著云規模不斷擴大以及業務層面對延遲、帶寬的要求越來越高，采用DPDK 加速網路報文處理的方式在橫向縱向擴展都出現了局限性。可編程芯片成為業界熱點。本文主要講述了可編程網卡芯片在滴滴云網路中的應用實踐，遇到的問題、帶來的收益以及開源社區貢獻。 #1. 資料中心面臨的問題 隨著滴滴 ......

    uj5u.com 2020-09-10 06:10:21 more
  • 滴滴資料通道服務演進之路

    **?桔妹導讀：**滴滴資料通道引擎承載著全公司的資料同步，為下游實時和離線場景提供了必不可少的源資料。隨著任務量的不斷增加，資料通道的整體架構也隨之發生改變。本文介紹了滴滴資料通道的發展歷程，遇到的問題以及今后的規劃。 #1. 背景 資料，對于任何一家互聯網公司來說都是非常重要的資產，公司的大資料 ......

    uj5u.com 2020-09-10 06:11:05 more
  • 滴滴AI Labs斬獲國際機器翻譯大賽中譯英方向世界第三

    **桔妹導讀：**深耕人工智能領域，致力于探索AI讓出行更美好的滴滴AI Labs再次斬獲國際大獎，這次獲獎的專案是什么呢？一起來看看詳細報道吧！ 近日，由國際計算語言學協會ACL（The Association for Computational Linguistics）舉辦的世界最具影響力的機器 ......

    uj5u.com 2020-09-10 06:11:29 more
  • MPP (Massively Parallel Processing)大規模并行處理

    1、什么是mpp？ MPP (Massively Parallel Processing)，即大規模并行處理，在資料庫非共享集群中，每個節點都有獨立的磁盤存盤系統和記憶體系統，業務資料根據資料庫模型和應用特點劃分到各個節點上，每臺資料節點通過專用網路或者商業通用網路互相連接，彼此協同計算，作為整體提供 ......

    uj5u.com 2020-09-10 06:11:41 more
  • 滴滴資料倉庫指標體系建設實踐

    **桔妹導讀：**指標體系是什么？如何使用OSM模型和AARRR模型搭建指標體系？如何統一流程、規范化、工具化管理指標體系？本文會對建設的方法論結合滴滴資料指標體系建設實踐進行解答分析。 #1. 什么是指標體系 ##1.1 指標體系定義 指標體系是將零散單點的具有相互聯系的指標，系統化的組織起來，通 ......

    uj5u.com 2020-09-10 06:12:52 more
  • 單表千萬行資料庫 LIKE 搜索優化手記

    我們經常在資料庫中使用 LIKE 運算子來完成對資料的模糊搜索，LIKE 運算子用于在 WHERE 子句中搜索列中的指定模式。 如果需要查找客戶表中所有姓氏是“張”的資料，可以使用下面的 SQL 陳述句： SELECT * FROM Customer WHERE Name LIKE '張%' 如果需要 ......

    uj5u.com 2020-09-10 06:13:25 more
  • 滴滴Ceph分布式存盤系統優化之鎖優化

    **桔妹導讀：**Ceph是國際知名的開源分布式存盤系統，在工業界和學術界都有著重要的影響。Ceph的架構和演算法設計發表在國際系統領域頂級會議OSDI、SOSP、SC等上。Ceph社區得到Red Hat、SUSE、Intel等大公司的大力支持。Ceph是國際云計算領域應用最廣泛的開源分布式存盤系統， ......

    uj5u.com 2020-09-10 06:14:51 more
  • es~通過ElasticsearchTemplate進行聚合~嵌套聚合

    之前寫過《es~通過ElasticsearchTemplate進行聚合操作》的文章，這一次主要寫一個嵌套的聚合，例如先對sex集合，再對desc聚合，最后再對age求和，共三層嵌套。 Aggregations的部分特性類似于SQL語言中的group by，avg，sum等函式,Aggregation ......

    uj5u.com 2020-09-10 06:14:59 more
  • 爬蟲日志監控 -- Elastc Stack（ELK）部署

    傻瓜式部署，只需替換IP與用戶 導讀： 現ELK四大組件分別為：Elasticsearch（核心）、logstash（處理）、filebeat（采集）、kibana（可視化） 下載均在https://www.elastic.co/cn/downloads/下tar包，各組件版本最好一致，配合fdm會 ......

    uj5u.com 2020-09-10 06:15:05 more
最新发布
  • day02-2-商鋪查詢快取

    功能02-商鋪查詢快取 3.商鋪詳情快取查詢 3.1什么是快取？ 快取就是資料交換的緩沖區（稱作Cache），是存盤資料的臨時地方，一般讀寫性能較高。 快取的作用： 降低后端負載 提高讀寫效率，降低回應時間 快取的成本： 資料一致性成本 代碼維護成本 運維成本 3.2需求說明 如下，當我們點擊商店詳 ......

    uj5u.com 2023-04-20 08:33:24 more
  • MySQL中binlog備份腳本分享

    關于MySQL的二進制日志（binlog），我們都知道二進制日志（binlog）非常重要，尤其當你需要point to point災難恢復的時侯，所以我們要對其進行備份。關于二進制日志（binlog)的備份,可以基于flush logs方式先切換binlog，然后拷貝&壓縮到到遠程服務器或本地服務器 ......

    uj5u.com 2023-04-20 08:28:06 more
  • day02-短信登錄

    功能實作02 2.功能01-短信登錄 2.1基于Session實作登錄 2.1.1思路分析 2.1.2代碼實作 2.1.2.1發送短信驗證碼 發送短信驗證碼： 發送驗證碼的介面為：http://127.0.0.1:8080/api/user/code?phone=xxxxx<手機號> 請求方式：PO ......

    uj5u.com 2023-04-20 08:27:27 more
  • 快取與資料庫雙寫一致性幾種策略分析

    本文將對幾種快取與資料庫保證資料一致性的使用方式進行分析。為保證高并發性能，以下分析場景不考慮執行的原子性及加鎖等強一致性要求的場景，僅追求最終一致性。 ......

    uj5u.com 2023-04-20 08:26:48 more
  • sql陳述句優化

    問題查找及措施 問題查找 需要找到具體的代碼，對其進行一對一優化，而非一直把關注點放在服務器和sql平臺 降低簡化每個事務中處理的問題，盡量不要讓一個事務拖太長的時間 例如檔案上傳時，應將檔案上傳這一步放在事務外面 微軟建議 4.啟動sql定時執行計劃 怎么啟動sqlserver代理服務-百度經驗 ......

    uj5u.com 2023-04-20 08:26:35 more
  • 云時代，MySQL到ClickHouse資料同步產品對比推薦

    ClickHouse 在執行分析查詢時的速度優勢很好的彌補了MySQL的不足，但是對于很多開發者和DBA來說，如何將MySQL穩定、高效、簡單的同步到 ClickHouse 卻很困難。本文對比了 NineData、MaterializeMySQL（ClickHouse自帶）、Bifrost 三款產品... ......

    uj5u.com 2023-04-20 08:26:29 more
  • sql陳述句優化

    問題查找及措施 問題查找 需要找到具體的代碼，對其進行一對一優化，而非一直把關注點放在服務器和sql平臺 降低簡化每個事務中處理的問題，盡量不要讓一個事務拖太長的時間 例如檔案上傳時，應將檔案上傳這一步放在事務外面 微軟建議 4.啟動sql定時執行計劃 怎么啟動sqlserver代理服務-百度經驗 ......

    uj5u.com 2023-04-20 08:25:13 more
  • Redis 報”OutOfDirectMemoryError“（堆外記憶體溢位)

    Redis 報錯“OutOfDirectMemoryError（堆外記憶體溢位) ”問題如下： 一、報錯資訊： 使用 Redis 的業務介面 ，產生 OutOfDirectMemoryError（堆外記憶體溢位），如圖： 格式化后的報錯資訊： { "timestamp": "2023-04-17 22: ......

    uj5u.com 2023-04-20 08:24:54 more
  • day02-2-商鋪查詢快取

    功能02-商鋪查詢快取 3.商鋪詳情快取查詢 3.1什么是快取？ 快取就是資料交換的緩沖區（稱作Cache），是存盤資料的臨時地方，一般讀寫性能較高。 快取的作用： 降低后端負載 提高讀寫效率，降低回應時間 快取的成本： 資料一致性成本 代碼維護成本 運維成本 3.2需求說明 如下，當我們點擊商店詳 ......

    uj5u.com 2023-04-20 08:24:03 more
  • day02-短信登錄

    功能實作02 2.功能01-短信登錄 2.1基于Session實作登錄 2.1.1思路分析 2.1.2代碼實作 2.1.2.1發送短信驗證碼 發送短信驗證碼： 發送驗證碼的介面為：http://127.0.0.1:8080/api/user/code?phone=xxxxx<手機號> 請求方式：PO ......

    uj5u.com 2023-04-20 08:23:11 more

Copyright © 2010-2020 有解無憂