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
Tags:

VirMach 便宜 VPS

QNews

QNews