Mysql基礎學習第二天

碼小農的幸福生活發表於2022-03-05
MySql

Mysql基礎學習第二天

函式

  • 函式：是指一段可以直接被另一段程式呼叫的程式或程式碼。

    • 字串函式
    • 數值函式
    • 日期函式
    • 流程函式

  • 字串函式

    • MySQL內建很多字串函式，常用的幾個如下：

      函式 功能
      CONCAT(S1,S2,...,Sn) 字串拼接，將S1, S2, ... Sn拼接成-個字串
      LOWER(str) 將字串str全部轉為小寫
      UPPER(str) 將字串str全部轉為大寫
      LPAD(str,n,pad) 左填充，用字串pad對str的左邊進行填充，達到n個字串長度
      RPAD(str,n,pad) 右填充，用字串pad對str的右邊進行填充，達到n個字串長度
      TRIM(str) 去掉字串頭部和尾部的空格
      SUBSTRING(str,start,len) 返回從字串str從start位置起的len個長度的字串

  • 數值函式

    • 常見的數值函式如下：

      函式 功能
      CEIL(x) 向上取整
      FLOOR(x) 向下取整
      MOD(x,y) 返回x/y的模
      RAND() 返回0~1內的隨機數
      ROUND(x,y) 求引數x的四捨五入的值，保留y位小數

  • 日期函式

    • 常見的日期函式如下：

      函式 功能
      CURDATE() 返回當前日期
      CURTIME() 返回當前時間
      NOW() 返回當前日期和時間
      TEAR(date) 獲取指定date的年份
      MONTH(date) 獲取指定date的月份
      DAY(date) 獲取指定date的日期
      DATE_ADD(date,INTERVAL expr type) 返回一個日期/時間值加上一個時間間隔expr後的時間值
      DATEDIFF(date1,date2) 返回起始時間date1和結束時間date2之間的天數

  • 流程函式

    • 流程函式也是很常用的一類函式,可以在SQL語句中實現條件篩選,從而提高語句的效率。

      函式 功能
      IF(value,t,f) 如果value為true,則返回t，否則返回f
      IFNULL(value1, value2) 如果value1不為空，返回value1,否則返回value2
      CASE WHEN [val1] THEN [res1] ... ELSE[default] END 如果val1為true,返回res1, .. 否則返回default預設值
      CASE [expr] WHEN [val1] THEN[res1] ... ELSE[default] END 如果expr的值等於val1,返回res1，.. 否則返回default預設值

約束

  • 概念：概念:約束是作用於表中欄位上的規則，用於限制儲存在表中的資料。

  • 目的：保證資料庫種資料的正確、有效性和完整性。

  • 分類：

    約束 描述 關鍵字
    非空約束 限制該欄位的資料不能為null NOT NULL
    唯一約束 保證該欄位的所有資料都是唯一、 不重複的 UNIQUE
    主鍵約束 主鍵是一行資料的唯一標識， 要求非空且唯一 PRIMARY KEY
    預設約束 儲存資料時，如果未指定該欄位的值，則採用預設值 DEFAULT
    檢查約束（8.0.16版本之後） 保證欄位值滿足某一個條件 CHECK
    外來鍵約束 用來讓兩張表的資料之間建立連線，保證資料的一致性和完整性 FOREING KEY

  • 案例 根據需求，完成表結構的建立

    欄位名 欄位含義 欄位型別 約束條件 約束關鍵字
    id ID唯一標識 INT 主鍵，並且自動增長 PRIMARY KEY,AUTO_INCREMENT
    name 姓名 VARCHAR(10) 不為空，並且唯一 NOT NULL,UNIQUE
    age 年齡 INT 大於0，並且小於等於120 CHECK
    status 狀態 CHAR(1) 如果沒有指定該值，預設為1 DEFAULT
    gender 性別 CHAR(1) 無 
    CREATE TABLE user(
    id int primary key auto_increment COMMENT '主鍵',
    name varchar(10) not null unique COMMENT '姓名',
    age int check ( age > 0 and age <= 120 ) COMMENT '年齡',
    status char(1) DEFAULT '1' COMMENT '狀態',
    gender char(1) COMMENT '性別'
)COMMENT '使用者基本資訊';

    • 外來鍵約束

      • 外來鍵用來讓兩張表的資料之間建立連線，從而保證資料的一致性和完整性。

        1、新增外來鍵
CREATE TABLE 表名(
	欄位名 資料型別,
    .....
    [CONSTRAINT] [外來鍵名稱] FOREIGN KEY (外來鍵欄位名) REFERENCES 主表(主表列名)
)

ALTER TABLE 表名 ADD CONSTRAINT 外來鍵名稱 FOREIGN KEY(外來鍵欄位名) REFERENCES 主表(主表列名);

2、修改外來鍵
ALTER TABLE 表名 DROP FOREIGN KEY 外來鍵名稱;

      • 刪除/更新行為

        行為 說明
        NO ACTION (預設) 當在父表中刪除/更新對應記錄時，首先檢查該記錄是否有對應外來鍵，如果有則不允許刪除/更新。(與 RESTRICT一致)
        RESTRICT 當在父表中刪除/更新對應記錄時，首先檢查該記錄是否有對應外來鍵，如果有則不允許刪除/更新。(與 NO ACTION一致)
        CASCADE 當在父表中刪除/更新對應記錄時，首先檢查該記錄是否有對應外來鍵，如果有， 則也刪除/更新外來鍵在子表中的記錄。
        SET NULL 當在父表中刪除對應記錄時，首先檢查該記錄是否有對應外來鍵，如果有則設定子表中該外來鍵值為null(這就要求該外來鍵允許取null)。
        SET DEFAULT 父表有變更時，子表將外來鍵列設定成一個預設的值(Innodb不支援) 
        1、語法

ALTER TABLE 表名 ADD CONSTRAINT 外來鍵名稱 FOREIGN KEY (外來鍵欄位) REFERENCES 主表名(主表欄位名) ON UPDATE CASCADE ON DELETE CASCADE;

多表查詢

  • 多表關係
  • 多表查詢概述
  • 內連線
  • 外連線
  • 自連線
  • 子查詢
  • 多表查詢案例

多表關係

  • ​ 概述：專案開發中，在進行資料庫表結構設計時，會根據業務需求及業務模組之間的關係,分析並設計表結構,由於業務之間相互關聯,所以各個表結構之間也存在著各種聯絡，基本上分為三種:

    • 一對多（多對一）

      • ​ 案例：部門 與 員工的關係

      • ​ 關係：一個部門對應多個員工, 一個員工對應一個部門

      • ​ 實現：在多的一方建立外來鍵,指向一的一方的主鍵

    • 多對多

      • ​ 案例：學生 與 課程的關係

      • ​ 關係：一個學生可以選修多門課程，一門課程也可以供多個學生選擇

      • ​ 實現：建立第三張中間表，中間表至少包含兩個外來鍵,分別關聯兩方主鍵

    • 一對一

      • ​ 案例：使用者 與 使用者 詳細關係
      • ​ 關係：一對一關係,多用於單表拆分，將一張表的基礎欄位放在一張表中，其他詳情欄位放在另-張表中，以提升操作效率
      • ​ 實現：在任意一方加入外來鍵，關聯另外一方的主鍵，並且設定外來鍵為唯一的(UNIQUE)

多表查詢概述

  • 概述：指從多張表中查詢資料

  • 笛卡爾積：笛卡爾乘積是指在數學中，兩個集合A集合和B集合的所有組合情況。(在多表查詢時，需要消除無效的笛卡爾積)

  • 多表查詢分類

  • 連線查詢

    • 內連線：相當於查詢A 、B 的交集部分資料
    • 外連線：
      • 左外連線：查詢 左表 所有資料，以及兩張表交集部分資料
      • 右外連線：查詢 右表 所有資料，以及兩張表交集部分資料
    • 自連線：當前表與自身的連線查詢，自連線必須使用表別名

  • 子查詢

    • 標量子查詢：
    • 列子查詢：
    • 行子查詢：
    • 表子查詢：

  • 連線查詢-內連線

    1、隱式內連線
SELECT 欄位列表 FROM 表1, 表2 WHERE 條件 ... ;

2、顯式內連線
SELECT 欄位列表 FROM 表1 [INNER] JOIN 表2 ON 連線條件 ... ;

  • 連線查詢-外連線

    1、左外連線
SELECT 欄位列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 條件 ...;

2、右外連線 
SELECT 欄位列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 條件 ...;

  • 連線查詢-自連線

    1、自連線
SELECT 欄位列表 FROM 表A 別名A JOIN 表A 別名B ON 條件 ...;

自連線查詢，可以是內連線查詢，也可以是外連線查詢。

  • 聯合查詢-union union all

    SELECT 欄位列表 FROM 表A ...
UNION [ALL]
SELECT 欄位列表 FROM 表B ...

對於union查詢，就是把多次查詢的結果合併起來,形成一個新的查詢結果集。
注意：
	1、對於聯合查詢的多張表的列數必須保持一致，欄位型別也需要保持一致。
	2、union all會將全部的資料直接合並在一起，union 會對合並之後的資料去重。

  • 子查詢

    • 概念：SQL語句中巢狀SELECT語句，稱為巢狀語句，又稱子查詢。
    SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);

子查詢外部的語句可以是 INSERT / UPDATE / DELETE / SELECT 的任何一個。
    • 根據子查詢結果不同，分為：
      • 標量子查詢（子查詢結果為單個值）
      • 列子查詢 （子查詢結果為一列）
      • 行子查詢 （子查詢結果為一行）
      • 表子查詢 （子查詢結果為多行多列）

  • 標量子查詢

    • 子查詢返回的結果是單個值（陣列、字串、日期等），最簡單的形式，這種子查詢成為標量子查詢。
    • 常用的操作符： = <> > >= < <=

  • 列子查詢

    • 子查詢返回的結果是一列(可以是多行) ,這種子查詢稱為列子查詢。

    • 常用的操作符：IN 、NOT IN 、ANY 、SOME 、ALL

      操作符 描述
      IN 在指定的集合範圍之內，多選一
      NOT IN 不在指定的集合範圍之內
      ANY 子查詢返回列表中，有任意一個滿足即可
      SOME 與ANY等同，使用SOME的地方都可以使用ANY
      ALL 子查詢返回列表的所有值都必須滿足

  • 行子查詢

    • 子查詢返回的結果是一行(可以是多列) , 這種子查詢稱為行子查詢。
    • 常用操作符：= 、<> 、IN 、NOT IN

  • 表子查詢

    • 子查詢返回的結果是多行多列，這種子查詢稱為表子查詢。
    • 常用操作符：IN

相關文章