技術文章 » MySQL核心技術（基礎篇）

MySQL核心技術（基礎篇）

2020-08-09 10:17:12

2020年6月 Mark
Tool: SQLyog OR Navicat
本文參考視訊教學：「尚硅谷MySQL核心技術」，整理筆記如下。
本文主要目的快速地抓取MySQL基礎的主要知識點，全文都是手打的，沒有複製貼上，記錄可能有不恰當之處

文章目錄


DQL 查詢
DML 操作 增刪改
DDL 定義
TCL 事務控制語言

其中，MyISAM不支援事務，需要設定數據庫型別爲InnoDB（支援事務控制）
MyISAM增刪操作時，只有表鎖，會鎖住整個表格；而InnoDB可以採用行級鎖。
前者以類似於陣列方式儲存數據，存取速度很快；後者InnoDB以B+樹儲存。

DQL 查詢篇 （一）

語法 --不區分大小寫，’’、""通用，數據庫索引從1開始。

$.語句順序

# 語法執行順序，實際where在select之前執行。
SELECT __查詢列表
FROM __表1
JOIN __表2 ON __連線條件 # [INNER] LEFT RIGHT FULL CROSS [OUTER]
WHERE __篩選條件
GROUP BY __分組欄位
HAVING __分組後的欄位
ORDER BY __排序欄位 # [DESC/ASC]
LIMIT __ ;

1.基礎查詢 SELECT

SELECT VERSION();
SELECT IFNULL(fieldName,0) AS 結果; //手動將NULL值視爲0
SELECT 10*3; //30
SELECT CONCAT(field1,",",field2) AS cont;

支援!=，但是<>更加規範。（包容性）

2.條件查詢 WHERE

BETWEEN AND, IN --提高語句的簡潔度

SELECT DISTINCT * from table; # 去重

WHERE `name` like '__n_l%'; # 單字元 多字元 # 萬用字元
WHERE `name` like '_$%' ESCAPE '$'; # 跳脫 \_ \$

WHERE `id` BETWEEN 10 AND 20; # 閉區間 [10,20] 相當於>= <=
WHERE `job` IN ('A','B','C'); # 相當於 "="
IS NULL; IS NOT NULL; =200; 安全等於 <=>

and&& or|| not!

3.排序查詢 ORDER BY

ORDER BY
`salary` [ASC|DESC];
LENGTH(`name`);

函數篇：單行函數

CONCAT LENHTH IFNULL
… 組函數/分組函數/統計函數/聚合函數

1.字元函數 CONCAT SUBSTR

-- 中文：utf8三位元組 gbk二位元組
SELECT
LENGTH('john');
UPPER('john');
CONCAT('VON','_','JOHN');
SUBSTR('李莫愁愛上了陸展元',7); # 數據庫索引，從1開始
SUBSTR('李莫愁愛上了陸展元',1,3);
INSTR('楊不悔愛上了殷六俠', '不') AS output; # 返回索引2
TRIM("  123   "); #123
TRIM("a" FROM "aaaa123aaaaaa"); #123
LPAD('印刷術',5,'*'); # **印刷術 RPAD
REPLACE('張公子愛上了周芷若','周芷若','趙敏');

2.數學函數 ROUND

SELECT
ROUND(1.657, 2); # 1.66 # 四捨五入不管正負號
CEIL(1.02); # 2
FLOOR(9.99); # 9
TRUNCATE(1.699,1); # 1.6
MOD(10,-3); # 1 (取決於被除數) # MOD(a,b)和JAVA一樣 # 內部計算式：a - a/b*b

MOD(a,b)和JAVA一樣 # 內部計算式：a - a/b*b

3.日期函數 NOW

SELECT
NOW();
CURDATE(); //2020-6-26
CURTIME(); //10:46:56
YEAR(NOW()); //2020 MONTH MONTHNAME DAY HOUR MINIUTE SECOND
//-  STR_TO_DATE('2020-06-26','%Y-%m-%d') AS out;
//-  STR_TO_DATE('4-3 2020', '%c%d %Y');
//-  DATE_FORMAT(NOW(), '%Y日%m月%d日');
DATEDIFF(NOW(), '1995-1-1'); //相差天數

4.其他函數（簡單） VERSION

SELECT
VERSION();
USER();
DATABASE(); //SHOW databases;
PASSWORD("王世超"); # 密文形式
MD5();

函數篇：進階、分組函數

1.流程控制 IF CASE

IF(10>5, '大','小');
IF(pct IS NULLA, '沒獎金','有獎金') AS 備註;

//嵌入select [] from的中間
CASE [欄位名/變數/表達式]
WHEN [常數/條件表達式] THEN …
ELSE 值N
END AS new_id

SELECT borndate,

CASE
    WHEN SUBSTR(borndate, 1, 4)    BETWEEN 1988 AND 1992
    THEN SUBSTR(borndate, 1, 4)
    # ELSE 值N
END AS year_

FROM `beauty`;

2.分組函數 SUM COUNT

… 組函數/分組函數/統計函數/聚合函數

SUM(salary);
AVG();
MIN();
MAX();
COUNT(Field); //統計非空數
COUNT(*); //統計行數 COUNT(1)

以上分組函數,都是忽略NULL值

3.分組查詢 GROUP BY HAVING

WHERE + HAVING

# 分組算平均
SELECT AVG(salary), job_id
FROM employees
GROUP BY job_id;
# 二次查詢
SELECT COUNT(*), job_id
FROM employees
GROUP BY job_id # 按組COUNT(*)
HAVAING COUNT(*)>2;

多表查詢

1.多表查詢/連線查詢

笛卡爾集錯誤，解決：通過新增連線條件
多個表的查詢

<年代>
sql92
sql99 【推薦使用】

<型別>

  • 內連線
    • 等值連線
    • 非等值連線
    • 自連線 (INNER) JOIN
  • 外連線
    • 左外連線 LEFT JOIN
    • 右外 RIGHT JOIN
    • 全外 FULL JOIN
      • == 內+左外+右外
  • 交叉連線 CROSS JOIN
    • 類似於笛卡爾乘積

連線的兩種寫法，
一種是 FROM + , + WHERE (sql92寫法)
一種是 FROM + JOIN + ON
HAVING

2.子查詢 (SELECT)

或稱，外查詢

IN
ANY / SOME
ALL

EXISTS

# 查詢員工的員工號、姓名、工資，這些員工的工資大於部門平均工資
select E.employee_id, E.last_name 姓氏, E.salary 工資, E.department_id
from employees E
join (
	select round(avg(salary),2) ags, department_id
	from employees
	group by department_id
) P
on E.department_id = P.department_id
where salary > ags
order by 工資;

3.分頁查詢 LIMIT

LIMIT offset, size; # 這裏的索引從0開始。其他的是從1開始。
LIMIT (page-1)*size, size; # 分頁查詢

4.聯合查詢 UNION

SELECT
UNION # UNION ALL 可包含重複專案
SELECT
  • 適用場景
    • 對多個表進行查詢
    • 多表的查詢列數要一致、順序最好一致
    • 預設去重 # UNION ALL 可以取消去重

DQL 語言 ENDING

DML 操作篇 （二）

INSERT
UPDATA
DELETE

1.增加 INSERT

# 方式一 支援插入多行、支援子查詢
INSERT INTO table(id,name,sex,date,photo)
VALUES(12,'Mike','man','1996-6-9',NULL),
VALUES(13,'Mike2','female','1997-6-9',NULL);

INSERT INTO table(id,name,sex)
SELECT id,name,"man"
FROM boys WHERE id<3;

-- SELECT UNION SELECT;

# 方式二
INSERT INTO beauty
SET id=19, NAME='Jack';

2.修改 UPDATE

單表、多表修改

UPDATE table0
SET
WHERE

# 多表修改
UPDATE boys bo # 修改張無忌女友的手機號爲144
INNER JOIN beauty b
ON bo.id = b.boyfriend_id
SET b.phone = '144'
WHERE bo.boyName='張無忌';

3.刪除 DELETE/TRUNCATE

單表、多表刪除（行）

# 單表刪除（行）
DELETE FROM beauty # 刪除尾號爲9的電話號碼
WHERE phone LIKE "%9";

# 多表
DELETE b # 刪除b表中張無忌的女朋友資訊
FROM beauty b
JOIN boys bo
ON b.boyfriend_id = bo.id
WHERE b.boyName = "張無忌";

# 整表刪除，不能加WHERE
TRUNCATE TABEL boys;

區別5：TRUNCATE刪除後，自增長列增加數據時從0開始。
DELETE有返回值。
DELETE支援回滾。

綜合案例
增刪改

DDL 定義篇 （三）

庫、表管理

CREATE
ALTER
DROP

1.庫的管理

建立、修改、刪除

CREATE DATABASE IF NOT EXISTS myDB;

# RENAME DATABASE myDB TO newtable; # 不建議，容易引發數據丟失
ALTER DATABASE myDB CHARACTER SET gbk;

DROP DATABASE IF EXISTS myDB;

2. 表的管理

CREATE TABLE mytable(
    id INT, # 編號
    name VARCHAR(20),
    price DOUBLE
);

ALTER TABLE mytable
操作 COLUMN 列名 【列型別 約束】


# 刪除
DESC mytable;
DROP TABLE IF EXISTS mytable;
SHOW TABLES;

# 複製
CREATE TABLE copyTable1 LIKE mytable;

CREATE TABLE copyTable2
SELECT * FROM mytable
WHERE id<10;
  • 修改 COLUMN
    • 列名 CHANGE
    • 列的型別、約束 MODIFY
    • 新列 ADD
    • 刪除列 DROP
    • 表名 RENAME TO
    • DESC mytable;

3.數據型別

  • 數值型
    • 整型
    • 定點數 Decimal (精度較高)
    • 浮點數 Float、Double
  • 字元型
    • char、varchar
    • binary、varbinary
    • Enum(‘a’,‘b’,‘c’)、Set(‘a’,‘b’,‘c’,‘d’) # 建表欄位型別
    • TEXT、blob(較長的二進制)
  • 日期型
    • DATETIME
    • TIMESTAMP

4.常見約束

  • 約束

NOT NULL
DEFAULT
PRIMARY KEY 主鍵，不可重複、非空
UNIQUE
CHECK 檢查，mysql不支援
FORING KEY 限制兩個表的關係，從表中新增限制

  • 場景

建立表
修改表

CREATE TABLE tablename(
欄位名 欄位型別 列級約束，
欄位名 欄位型別，
表級約束
)

表級約束

CONSTRAINT PRIMARY KEY(ID), # 主鍵

主鍵 V.S. 唯一鍵

  • 標識列

自增長列
TRUNCATE TABLE tablename;
CREATE TABLE tablename(id INT PRIMARY KEY AUTO_INCREAMENT);

  • 外來鍵：刪除主表的記錄

級聯刪除:
alter table tab1 add constaint f1 foreign key(id) references major(id) on delete cascade;
級聯置空:
alter table tab1 add constaint f1 foreign key(id) references major(id) on delete set null;

TCL 事務篇 （四）

事務控制語言
事務：一組sql語句構成一個執行單元，全執行或者全不執行。
支援回滾。 錯誤/中斷時回滾。

事務控制語言

  • 事務的ACID屬性

atomicity 原子性 一個事務不可再分割
consistency 一致性 一致狀態到一致狀態
isolation 隔離性 事務互不幹 不乾擾，需要設定隔離級別
durability 永續性 提交後，永久地持久化到本地

  • 事務的建立

隱式 insert update delete
顯式 有明顯的開始和結束

  1. set autocommit=0; start transaction;

  2. select insert update delete 等事務語句
    – savepoint a; 設定回滾點

  3. commit;
    – rollback to a; 回滾事務

e.g.
~~

  • 併發事務/問題

髒讀 讀取了還沒提交的數據 「更新」
不可重複讀 多次讀取，結果不一樣
幻讀 插入的前後讀 「插入」

  • 事務的隔離級別

– isolation level
read uncommited 讀未提交
read commited oracle預設 讀已提交
repeatable read mysql預設 可重複讀 //不能解決幻讀問題
serializable 序列化 //無併發問題，但是效率低

select @@tx_isolation;
set transaction isolation level read commited;

—

9.檢視

簡化語句、提高重用性、安全性

  • 檢視

mysql5.1新特性，虛擬表（動態生成的數據）

create view 檢視名 as  -- 檢視
select ...; -- 查詢語句

desc 檢視名;
show create view 檢視名;

檢視，一般只能「查」，不能增刪改，不佔用物理空間。

不允許檢視更新的情況
join 常數檢視 用到了不可更新檢視 where後子查詢用到from表 – 詳再查

10.變數

系統變數
自定義使用者變數

  • 系統變數/對談
show global variables;
show global variables like '%char%';
select @@global.系統變數名=值;
set @@global.autocommit=0;

show variables; //session可不加
select @@tx_isolation;
set @@session.tx_isolation='read-uncommited';
  • 自定義變數 (不用限定型別) set

當前對談有效

set @name:='join';
select @name:='join';
select count(*) into @count from students;
  • 區域性變數 (需要限定型別)
begin
declare 變數名 型別 [default 值];
set 變數名:=值;
end

11.儲存過程和函數

  • 儲存過程
delimiter $
create procedure func()
begin
	sql language;
end $

call func()$

drop procedure func; --刪除過程

參數列表： IN name vat(10);

IN OUT INOUT
<set name gbk;>

  • 函數
delimiter $ -- 定義結束符 <todo>
create function 函數名(參數列表) returns 返回型別
begin
	函數體
end

select 函數名（參數列表）

12.流程控制結構

  • 條件語句
delimiter $ -- 定義結束符 預設是;分號
begin
case
when 條件1 then select 語句;
when 條件2 then select 語句2;
else select 語句3;
end case;
end $

delimiter $ -
begin
if 條件1 then 語句1;
elseif 條件2 then 語句2;
else 語句n;
end if;
end $
  • 回圈結構
while loop repeat
iterate leave  -- 相當於 continue  break

<while>
while 回圈條件 do
	回圈體
end while 標籤;

loop / end loop -- 沒有條件的死回圈
repeat / until / end repeat

end 2020-7-22