MySQL概述

個人學習MySQL整理的複習資料，課程是千峰教育的MySQL網課： https://www.bilibili.com/video/av61707377

我的CSDN部落格： https://blog.csdn.net/weixin_43547314

MySQL文法分類

DDL：Data Definition Language——資料定義語言

定義資料庫對象

主要關鍵字：create，drop，alter
DML：Data Manipulation Language——資料操作語言

對資料庫表裡的資料進行增删改

主要關鍵字：insert，delete，update
DQL：Data Query Language——資料查詢語言

用來查詢資料庫中的記錄

主要關鍵字：select，where
DCL：Data Control Language——資料控制語言

定義資料庫通路權限和安全級别、建立使用者

主要關鍵字：grant， revoke

MySQL資料類型

資料類型	說明
int	整數型
double	小數類型 eg: score double(5,2)表示最大5位數，小數點後保留2位
date	日期隻包含年月日，yyyy-MM-dd
datetime	日期包含年月日時分秒，yyyy-MM-dd HH:mm:ss
timestamp	時間戳類型格式和datetime一樣，使用時不指派或指派null則預設使用系統時間指派
varchar	字元串 varchar(長度)

DDL文法

操作資料庫

建立

-- 建立資料庫：
	create database 資料庫名;
-- 建立資料庫，判斷不存在，再建立：
	create database if not exists 資料庫名稱;
-- 建立資料庫，并指定字元集：
	create database 資料庫名稱 character set 字元集名;

查詢

-- 查詢所有資料庫名稱：
	show databases;
-- 查詢某個資料庫的字元集、建立語句：
	show create database 資料庫名稱;

修改

-- 修改資料庫的字元集：
	alter database 資料庫名稱 character set 字元集名稱;

删除

-- 删除資料庫：
	drop database 資料庫名稱;
-- 判斷資料是否存在，如果存在，删除：
	drop database if exists 資料庫名稱;

使用資料庫

-- 查詢目前正在使用的資料庫名稱：
	select database();
-- 使用資料庫：
	use 資料庫名稱;

操作表

建立

-- 在db1資料庫中建立一個表：
	use db1;
	create table 表名(列名1 資料類型1, 列名2 資料類型2,···, 列名n 資料類型n)
-- 複制表：
	create table 表名 like 被複制的表名;

查詢

-- 查詢所有表：
	show tables;
-- 查詢表結構：
	desc 表名;

修改

-- 修改表名：
	alter table 表名 rename to 新表名;
-- 修改表的字元集：
	alter table 表名 character set 字元集名稱;
-- 添加一列：
	alter table 表名 add 列名 資料類型;
-- 修改列名稱、類型：
	alter table 表名 change 列名 新列名 新資料類型;
	alter table 表名 modify 列名 新資料類型;
-- 删除列：
	alter table 表名 drop 列名;

删除

-- 删除表：
	drop table 表名;
	drop table if exists 表名;

DML文法

添加資料

-- 在表中添加一條資料：
	insert into 表名(列名1, 列名2,···, 列名n) values(值1, 值2,···, 值n);

注意：

列名和值需要相對應。
如果直接用：

insert into 表名 values(值1, 值2, ···, 值2)

預設給所有列添加值
除了int，其他全都得用引号括起來，單雙都可。

删除資料

-- 删除一個表中的某一項：
	delete from 表名 where 列名 = 值1;
-- 清空表：
	delete from 表名;    -- 效率低
	TRUNCATE TABLE 表名; -- 效率高

修改資料

-- 修改一個表中的資料：
	update 表名 set 列名1 = 值1, 列名2 = 值2,···,列名n = 值n where 條件

注意：

不加條件所有資料都周遊一次

DQL文法

select
	字段清單 
from
	表名清單 
where
	條件清單 
group by
	分組字段 
having
	分組之後的條件 
order by
	排序 
limit
	分頁限定

基礎查詢

-- 去除重複結果：
	select distinct 列名 from 表名;
-- 起别名：
	select 列名 as 别名 from 表名;  -- as可省略

條件查詢

運算符：

比較運算符	說明
>、<、<=、>=、=、<>	SQL中沒有==，<>表示不等于，!=是mysql的方言，在SQL中不通用。
between···and···	between 100 and 200 → [100,200]，兩邊都可取到。
in(集合)	集合表示多個值，用逗号分隔，相當于python中的集合{}換成了()。
like ‘張%’ 和 like’張_’	模糊查詢，_表示單個任意字元，%表示任意多個字元。
is null	SQL中不能用=null，is null起到=null的作用。
and或&&	與，and通用，&&不通用。
or或\|\|	或
not或!	非

排序查詢

-- 升序查詢：
	select * from 表名 order by 列名 ASC;
-- 降序查詢：
	select * from 表名 order by 列名 DESC;
-- 多條件排序查詢：
	select * from 表名 order by 列名1 排序方式, 列名2 排序方式;

聚合函數

聚合函數将一列資料作為一個整體，進行縱向計算，預設排除null值。

count：計數
max：求最大值
min：求最小值
sum：求和
avg：求平均值

-- 文法：
	select 聚合函數(列名) from 表名;
-- 不排除值為null的資料：
	select count(ifnull(列名,替換null的值)) from 表名;

分組查詢

把擁有相同特征的一類資料劃分為一組進行操作。

以查詢學生資訊為例：

-- 按照性表分組，分别查詢男、女學生的平均分。
	select sex, avg(score) from student group by sex;
-- 按照性表分組，分别查詢男、女學生的平均分，低于70分的不計。
	select sex, avg(score) from student where math > 70 group by sex;
-- 按照性表分組，分别查詢男、女學生的平均分，低于70分的不計；查詢結果中少于2人的組不計。
	select sex, avg(score) from student where math > 70 group by sex having count(name);

where和having如何區分：

where在分組前限定，如果不滿足條件，則不參與分組。不可以跟聚合函數。

having在分組後限定，如果不滿足條件，則不會被輸出。可以跟聚合函數。

分頁查詢

-- 每頁顯示三條記錄：
	select * from 表名 (目前頁碼-1)×每頁顯示條數, 每頁顯示條數;

分頁查詢是“方言”，隻有MySQL中才有limit這種用法。

多表查詢

文法：

select
	列名清單
from
	表名清單
where
	······

結果中有多餘的笛卡爾積。

消除方法：

内連接配接查詢：

注意：從那些表中查詢、條件是什麼、查詢哪些字段
1. 隐式内連接配接：
  
  用where消除所有的備援資訊 eg：where xx = xx;
2. 顯式内連接配接：
  
  select 字段清單 from 表名 [inner] join 表名 on 條件;
外連接配接查詢：
1. 左外連接配接：
  
  select 字段清單 from 表1 left [outer] join 表2 on 條件;
  
  查詢的是左表所有資料以及交集部分。
2. 右外連接配接：
  
  select 字段清單 from 表1 right [outer] join 表2 on 條件;
  
  查詢的是左表所有資料以及交集部分。
子查詢：

查詢中嵌套查詢。加個括号就行，跟加法交換律加括号差不多。
1. 子查詢的結果是單行單列的：
  
  子查詢可以作為條件用運算符去判斷。隻能使用>、<、=、>=、<=、<>
2. 子查詢的結果是多行單列的：
  
  子查詢可以作為條件，使用運算符in來判斷。
3. 子查詢的結果是多行多列的：
  
  子查詢可以作為一張虛拟表參與查詢。

DCL文法

管理使用者

-- 切換到mysql資料庫：
	use mysql;

添加使用者

create user '使用者名'@'主機名' identified by '密碼';

删除使用者

DROP USER '使用者名'@'主機名';

修改使用者密碼

UPDATE USER SET PASSWORD = PASSWORD('新密碼') WHERE USER = '使用者名';
UPDATE USER SET PASSWORD = PASSWORD('abc') WHERE USER = 'lisi';

查詢使用者：
```
select * from user;
           
```

權限管理

查詢權限

SHOW GRANTS FOR '使用者名'@'主機名';

授予權限

grant 權限清單 on 資料庫名.表名 to '使用者名'@'主機名';
-- 給張三使用者授予所有權限，在任意資料庫任意表上

撤銷權限

revoke 權限清單 on 資料庫名.表名 from '使用者名'@'主機名';

限制

限制是指對表中的資料進行限定，保證資料的正确性、有效性和完整性。

限制的分類：

主鍵限制：primary key
非空限制：not null
唯一限制：unique
外鍵限制：foreign key

非空限制

該列的值不能為空。

-- 在建立表時添加非空限制：
	create table stu(id int, 
                     name varchar(20) not null -- 添加了非空限制
                    );
-- 删除name的非空限制：
	alter table stu modify name varchar(20);
-- 建立表後對name添加非空限制：
	alter table stu modify name varchar(20) not null;

唯一限制

該列的值不能重複，但尋在沒有添加非空限制的情況下可以都為null。

-- 建立表時添加唯一限制：
	create table stu(id int, 
                     phone_number varchar(20) unique -- 添加了唯一限制
                     );
-- 删除phone_number的唯一限制：
  × alter table stu modify phone_number varchar(20); -- 無法删除唯一限制
  √ alter table stu drop index phone_number;         -- 可以删除唯一限制
-- 建立表後對phone_number添加唯一限制：
	alter table stu modify phone_number varchar(20) unique; -- 隻有表中的phone_number沒有重複的值，才能執行成功。

主鍵限制

該列的值非空且唯一。

-- 在建立表時添加主鍵限制：
	create table stu(id int primary key, -- 添加了主鍵限制
                     name varchar(20)
                    );
-- 删除id的主鍵限制：
  × alter table stu modify id int;    -- 無法删除主鍵限制
  √ alter table stu drop primary key; -- 可以删除主鍵限制
-- 建立表後對id添加主鍵限制：
	alter table stu modify id int primary key; -- 隻有表中的id沒有重複的值，才能執行成功。
-- 建立聯合主鍵：
	create table 表名(id1 int,
                     id2 int,
                     primary key(id1, id2)
                    );

自動增長

自動增長隻跟上一條資料有關。

-- 在建立表的時候，添加主鍵限制并完成主鍵自動增長：
	create table stu(id int primary key auto_increment, -- 添加了主鍵限制并實作自動增長
                     name varchar(20)
                    );
-- 删除id的自動增長：
	alter table stu modify id int;
-- 建立表後對id添加自動增長：
	alter table stu modify id int auto_increment;

外鍵限制

讓表與表産生關系，進而保證資料的正确性。

-- 添加一個部門表：
	create table department(id int,
                            loc varchar(20)
                           );
-- 在建立表的時候，添加外鍵限制：
	create employee(e_id int,
                    name varchar(20),
                    dep_id int constraint emp_dep_fk foreign key (dep_id) references department(id)
                   )
-- 删除外鍵：
	alter table employee drop foreign key emp_dep_fk;
-- 建立表後添加外鍵：
	alter table employee add constraint emp_dep_fk foreign key (dep_id) references department(id);

級聯操作允許主表更的時候，從表跟着更改。

-- 添加外鍵，設定級聯更新：
	alter table employee add constraint emp_dep_fk foreign key (dep_id) references department(id) on update cascade;
	-- department表中的id改變後employee表中的dep_id跟着變
-- 設定級聯删除：
	alter table employee add constraint emp_dep_fk foreign key (dep_id) references department(id) on delete cascade;
	-- department 表中的某一id被删除後employee表中帶有該id的資料都被删除

資料庫設計

多表關系

一對一關系：

A和B，一個A隻能對應一個B，一個B也隻能對應一個A，AB互為充要條件。如：人和身份證。

實作：在任意一方添加
唯一 外鍵指向另一方的主鍵
一對多 or 多對一：

A和B，一個A有多個B，一個B隻能對應一個A，A是B的必要不充分條件。如：部門和員工。

實作：在多的一方建立外鍵，指向一的一方的主鍵
多對多：

A和B，一個A有多個B，一個B有多個A，AB互為既不必要也不充分條件。如：學生和選修課。

實作：建立一張中間表，中間表至少包含兩個字段，這兩個字段作為第三章表的外鍵，分别指向兩張表的主鍵

設計資料庫的範式

第一範式（1NF）

每一列都是不可分割的原子資料項

第二範式（2NF）

在1NF基礎上，非碼屬性必須完全依賴主碼。（在1NF基礎上消除非主屬性對主碼的部分函數依賴）

函數依賴：A–>B，如果通過A屬性或屬性組的值，可以确定唯一B屬性的值，則稱B依賴于A。

eg：學号–>姓名 (學号, 課程名)–>分數

完全函數依賴：A–>B，如果A是一個屬性組，則B屬性值的确定需要依賴于A屬性組中所有的屬性值。

eg：(學号, 課程名)–>分數

部分函數依賴：A–>B，如果A是一個屬性組，則B的屬性值的确定隻需要依賴于A屬性組中某一些值即可。

eg：(學号, 課程名)–>姓名

傳遞函數依賴：A–>B，B–>C，如果通過A屬性的值可以确定唯一B屬性的值，再通過B屬性的值可以确定唯一C屬性的值，則稱C傳遞函數依賴于A。

eg：學号–>系名，系名–>系主任

碼：如果在一張表中，一個屬性或屬性組被其他所有屬性所完全依賴，則稱這個屬性或屬性組為該表的碼。

主屬性：碼屬性組中的所有屬性。

非主屬性：除了碼屬性組的屬性。

第三範式（3NF）

在2NF基礎上，任何非主屬性不依賴于其他非主屬性。（在2NF基礎上消除傳遞依賴）

資料庫的備份和還原

指令行

備份：

mysqldump -u使用者名 -p密碼資料庫名稱 > 儲存路徑/檔案名.sql

還原：

建立資料庫
使用資料庫
執行檔案。source 檔案路徑/檔案名.sql

GUI工具

備份：

SQLYog右鍵導出。

還原：

SQLYog右鍵執行SQL檔案。

事務

介紹

概念：如果一個包含多個步驟的業務操作，被事務管理，那麼這些操作要麼同時成功，要麼同時失敗。

操作：

1. 開啟事務：start transaction；
2. 復原：rollback；
3. 送出：commit；

MySQL事務預設自動送出，手動送出需要先開啟事務，後送出。

查詢送出方式：

select @@autocommit; -- 1代表自動送出，0代表手動送出。

修改送出方式：

set @@autocommit = 0;

特征

原子性：不可分割的最小操作機關，要麼同時成功，要麼同時失敗。
持久性：當事務送出或者復原後，資料庫會持久化的儲存資料。
隔離性：多個事務之間互相獨立。
一緻性：事務操作前後資料總量不變。

隔離級别

概念：

多個事務之間是隔離的，互相獨立的，但是如果多個事務操作同一批資料，則會引發一些問題。設定不同的隔離級别就可以解決這些問題。

存在的問題：

1. 髒讀：一個事務讀取到另一個事務沒有送出的資料。
2. 不可重複讀（虛讀）：在同一個事務中，兩次讀取到的資料不一樣。
3. 幻讀：一個事務操作（DML）資料表中所有記錄，另一個事務添加了一條資料，則第一個事務查詢不到自己的修改。

隔離級别：（MySQL預設repeatable read）

read uncommitted：讀未送出

問題：髒讀、不可重複讀、幻讀
read committed：讀已送出

問題：不可重複讀、幻讀
repeatable read：可重複讀

問題：幻讀
serializable：串行化

問題：沒有問題

注意：隔離級别從小大大安全性越來越高，但是效率越來越低。

資料庫查詢隔離級别：

select @@tx_isolation;

資料庫設定隔離級别：

set global transaction isolation level 隔離級别;

MySQL從入門到上手最全整理MySQL概述DDL文法DML文法DQL文法DCL文法限制資料庫設計資料庫的備份和還原事務