在網上找到一個不錯的 MYSQL 教學，把整個主要功能做了一次紮實的盤整，以下是我做的一些筆記跟內容：

教程

代碼 Repo

定義

DDL: 数据定义语言(Data Definition Language)

DDL 用于定义数据库结构和模式，包括创建、修改和删除数据库、表、视图、索引等数据库对象。它不涉及实际数据的操作，而是定义了数据的结构。在MySQL中，常见的DDL命令包括CREATE、ALTER和DROP等。

DML: 数据操作语言(Data Manipulation Language)

DML用于操作数据库中的数据，包括插入、更新和删除数据记录。它允许用户查询和修改数据库中的实际数据。在MySQL中，常见的DML命令包括SELECT、INSERT、UPDATE和DELETE等。

DQL: 数据查询语言(Data Query Language)

DQL用于查询数据库中的数据，但不对数据进行修改。它主要包括SELECT命令，允许用户从数据库中检索所需的数据。在MySQL中，SELECT命令是最常用的DQL命令。

DCL: 数据控制语言(Data Control Language)

DCL用于授权和权限管理，确定哪些用户有权访问数据库中的数据和对象，以及在何种方式访问。在MySQL中，常见的DCL命令包括GRANT和REVOKE，用于授予和撤销用户对数据库对象的权限。

基礎操作

# create table
create table player (
        id INT,
	name VARCHAR(100),
	level INT,
	exp INT,
	gold DECIMAL(10, 2)
)
# 描述 table
DESC player

# 基本操作
alter table player modify column name VARCHAR(200)
alter table player rename column name to nick_name
alter table player add COLUMN last_login datetime
alter table player drop column last_login
insert into player (id, nick_name) values (2, 'hello');
insert into player (id, nick_name) values (3, 'hello');
insert into player (id, nick_name) values (5, 'hello'), (6, 'aaa');
update player set level = 1 WHERE nick_name = 'hello'
SELECT * FROM player where level > 1 AND (level < 5 OR exp > 1) and exp < 5
SELECT * FROM player where level in (1, 3, 5)
SELECT * FROM player where level BETWEEN 1 AND 10
SELECT * FROM player where name LIKE '王%'
SELECT * FROM player where name LIKE '王_' #批配一字符

Regexp

#REGEXP
# . 任一字符
# ^ 開頭
# $ 結尾
# [abc] 其中任一
# [a-z] 範圍任一
# A|B A或B

SELECT * FROM player where name REGEXP '^王.$'
SELECT * FROM player where name REGEXP '王'
SELECT * FROM player where name REGEXP '王.$'
SELECT * FROM player where name REGEXP '[王张]'
SELECT * FROM player where name REGEXP '王|张'

SELECT * FROM player where email REGEXP 'net$'
SELECT * FROM player where email is NULL
SELECT * FROM player ORDER BY level DESC, exp ASC # 先以 level -> exp

GROUP BY

# AVG
# COUNT
# MAX
# MIN
# SUM
SELECT COUNT(*) FROM player

SELECT sex, COUNT(*) FROM player group by sex
SELECT level, COUNT(level) FROM player group by level
SELECT level, COUNT(level) FROM player group by level HAVING count(level) > 4 ORDER BY count(level) DESC

差聯集

SELECT DISTINCT sex from player

# union only
select * from player where level BETWEEN 1 and 3
UNION
select * from player where exp BETWEEN 1 and 3;

# union all
select * from player where level BETWEEN 1 and 3
UNION ALL
select * from player where exp BETWEEN 1 and 3;

# intersect
select * from player where level BETWEEN 1 and 3
INTERSECT
select * from player where exp BETWEEN 1 and 3;

# except
select * from player where level BETWEEN 1 and 3
EXCEPT
select * from player where exp BETWEEN 1 and 3;

子查詢

select level,
ROUND((SELECT AVG(level) from player)) as average,
level - ROUND((SELECT AVG(level) from player)) as diff
 from player

migrate / exists

# migrate, insert
create TABLE new_player2 SELECT * from player where level < 5
SELECT EXISTS(select * from player where level > 3)

JOIN

# inner join 只返回兩表中都有的數據
select * from player
inner join equip
on player.id = equip.player_id
# 也可以用, where 意思一樣
select * from player, equip
where player.id = equip.player_id # 要有條件不然會產生笛卡爾積 雙表數據都取出

# left join 返回左表中所有數據+右表批配數據 右表沒有則NULL填充
select * from player
left join equip
on player.id = equip.player_id

# right join 返回右表中所有數據+左表批配數據 左表沒有則NULL填充
select * from player
right join equip
on player.id = equip.player_id

建立索引

CREATE INDEX index_name
ON table_name (column1, column2, ...);

CREATE INDEX idx_employee_id
ON employees (employee_id);
# 创建一个名为 "idx_employee_id" 的索引，以加速对 "employee_id" 列的检索

CREATE INDEX idx_name_department
ON employees (first_name, last_name, department);
# 创建一个名为 "idx_name_department" 的索引，它包括 "first_name"、"last_name" 和 "department" 列。复合索引可用于优化多列的查询。

單索引只會作用在單獨條件下就會有索引，舉例來說

CREATE INDEX idx_employee_id
ON employees (employee_id);

其 select 須為：

select * from employees where employee_id = 1 # 作用

複合索引則作用在建立條件下:

CREATE INDEX idx_name_department
ON employees (first_name, last_name, department);

其 select 須為：

select * from employees where first_name = 'a' AND last_name = 'b' AND department = 'c' # 作用

如果沒有滿足則不會作用

  • 普通索引（B-tree index）
  • 唯一索引（unique index）
  • 主鍵索引（primary key index）
  • 全文索引（full-text index）
CREATE INDEX my_index
ON my_table (id, name);
CREATE UNIQUE INDEX my_index
ON my_table (id, name);
CREATE PRIMARY KEY my_index
ON my_table (id, name);
CREATE FULLTEXT INDEX my_index
ON my_table (content);

視圖

視圖是一種虛擬表，它不會存儲任何數據，而是對現有表的一種查看。視圖可以用來組織和過濾數據，也可以用來限制用戶對數據的訪問。

視圖的主要用途包括：

  • 組織和過濾數據：視圖可以用來組織和過濾數據，使其更容易理解和使用。例如，可以創建一個視圖來包含來自不同表的數據，或者可以創建一個視圖來過濾掉不想要的數據。
  • 限制用戶對數據的訪問：視圖可以用來限制用戶對數據的訪問。例如，可以創建一個只允許用戶查看數據的視圖，而不允許用戶更改數據。
  • 提高查詢性能：視圖可以提高查詢性能，因為它可以用來預先計算查詢結果。例如，可以創建一個視圖來包含來自不同表的數據，這樣就可以在查詢數據時使用該視圖，而不需要每次都從不同表中查詢數據。
create view top10 as select * from player order by level desc limit 10;