一 SQL#
全稱為Structured query Language：結構化查詢語言。每種資料庫語句，都有屬於自己的方言，但基本的命令不變。
mysql 的語句不區分大小寫，但是建議
關鍵字寫為大寫
- DDL：操作資料庫、表
- DML：增刪改表的資料
- DQL：查詢表的資料
- DCL：授權
二 資料類型#
資料類型整體分為三大類，數值類型，字串類型，日期類型
1 數值類型：
| 類型 | 大小 (位元組) | 說明 |
|---|---|---|
| int/integer | 4 | 數字 |
| bigint | 8 | 數字 |
| smallint | 2 | 數字 |
| tinyint | 1 | 布林值 |
| double | 8 | 近似儲存 |
| double(M,D) | 8 | 近似儲存 |
| decimal(M,D) | 8 | 精確儲存 |
這裡 M 表示最長有幾位，D 為小數點後幾位
2 字串類型：
| 類型 | 大小 (位元組) | |
|---|---|---|
| char | 0-155 | 定長字串，如果太短會補充空格。 優點：查詢速度快，缺點：浪費空間 |
| varchar | 0-65535 | 變長字串 優點：節省空間，缺點：稍微慢點 |
| blob | 0-65535 | 儲存二進位資料 |
| mediumblob | 16M | 儲存二進位資料 |
| longblob | 4G | 儲存二進位資料 |
| text | 0-65535 | 長文本 |
| mediumtext | 16M | 長文本 |
| longtext | 4G | 長文本 |
MYSQL 中 == 每條記錄 == 最大位元組為 64k (數字，字串)，文本，二進位檔案不包含在內
這個每條記錄表示的是一行資料，比如 id+name+age+address 合起來
3 日期類型：
| 類型 | 長度 (位元組) | 格式 | 用途 |
|---|---|---|---|
| date | 3 | YYYY-MM-DD | 日期值 |
| time | 3 | HH:MM | 時間值 |
| year | 1 | YYYY | 年 |
| datetime | 8 | YYYY-MM-DD HH:MM | 時間日期 |
| timestamp | 4 | YYYYMMDDHHMMSS | 時間戳 |
三 DDL 語法#
對資料庫，以及表的操作
1 操作資料庫#
- 創建資料庫：
# 創建資料庫
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 資料庫名稱;
2 操作表#
- 創建表
create table 表名(
列名1 資料類型1,
列名2 資料類型2,
列名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 語法#
對表中內容的操作，是我們最常用的語句
1 添加資料#
添加資料：
# 全部列都添加
insert into stu(列名1,列名2....列名n) values (資料1,資料2...資料n);
# 如果全部添加資料，可以不寫列名
insert into stu values (資料1,資料2...資料n);
# 如果不需要增加哪一行則可以不書寫
# 如果想設置預設值可寫為default
2 刪除資料#
# 刪除資料
delete from 表名 where 條件;
# 清空表
delete from 表名;
# 直接刪除表，然後創建一個同名的表，速度更快
truncate table 表名;
3 修改資料#
# 修改資料
update 表名 set 列名1 = 值1,列名2 = 值2, .... where 條件
# 不加where的條件即表示，修改表中所有的指定的資料
update 表名 set 列名1 = 值1,列名2 = 值2, ....
五 DQL 語法#
查詢表中的資料，書寫順序如下：
select 字段列表
from 表名列表
where 條件列表
group by 分組條件
having 對排序後的資料進行篩選
order by 進行排序的列
limit 進行分頁，limit是mysql的方言
1 基本查詢#
# 查詢全部
select * from 表名;
# 查詢指定的列
select 列名1,列名2... from 表名;
# 查詢並去重
select distinct 列名1,列名2... from 表名;
# 兩列進行計算
select 列1+列2.... from 表名;
# 上面如果有null值則無法進行正常計算，因此可以使用if null關鍵字
select math + if null(english,0) from 表名;
# 查詢起別名
select 列1 as 別名, 列2 as 別名..... from 表名;
[[省略as也可以]]
select 列1 別名, 列2 別名....... from 表名;
2 條件查詢#
即語句後面加上where字段，然後書寫查詢的條件
| 比較運算符 | 說明 |
|---|---|
| >、<、<=、>=、=、<> | <>表示不等於，在 mysql 中也可以使用!= |
| between...and | 在一個範圍之內，例如：between 100 and 200 即表示：[100，200] |
| in (集合) | 集合表示多個值，使用逗號分隔 |
| like ' 張 %' | 佔位符：_表示單個任意字符；%表示多個任意字符；模糊查詢 |
| is NULL | 查詢某一列為 null 的值，注意：不能寫 = null |
| is not NULL | 查詢不為 NULL 的值 |
| 邏輯運算符 | 說明 |
|---|---|
| and 或 && | 與，SQL 中建議寫前者，後者並不通用 |
| **or 或 | |
| not 或 ！ | 非 |
3 排序查詢#
即在order by後加排序
| 關鍵字 | 說明 |
|---|---|
| 不指定，預設升序 | |
| ASC | 升序 |
| DESC | 降序 |
語句：
多條件排序，就是當第一個條件一樣時，進行第二排序
select * from student order by 數學成績, 英語成績;
4 聚合函數#
聚合函數：將一列資料作為一個整體，進行縱向的計算，即對列進行計算
| 關鍵字 | 說明 |
|---|---|
| count（列名）： | 計算個數 |
| max（列名）： | 計算最大值 |
| min（列名）： | 計算最小值 |
| sum（列名）： | 計算和 |
| avg（列名）： | 計算平均值 |
注意：聚合函數會排除null值，如果統計個數，建議不用有空的列
使用：
計算全部人數：
select count(name) from student;
5 分組查詢#
即在group by後加分組
如果進行分組，則查詢select出來的結果肯定是一個整體，因此不能具體到某一列什麼的，所以，只能寫：分組本身的字段，聚合函數
比如需要查男生，與女生各自的數學平均分，以及各自的人數：
select sex,avg(math) ,count(id) from studnet group by sex;
如果需要在分組後限制則需要having進行篩選
比如需要，篩選出查詢後，人數大於 3 人的組：
select sex,avg(math) ,count(id) from studnet group by sex having count(id)>3;
6 分頁查詢#
分頁查詢：如果查詢的結果非常多，我們想分頁進行展示，這就是分頁查詢，在limit字段書寫條件
limit 開始的索引,每頁查詢的條數
如果需要查詢前三個人，且第一頁顯示 3 個
select * from student limit 0,3; [[第1頁]]
select * from student limit 3,3; [[第2頁]]
select * from student limit 6,3; [[第3頁]]
*因此我們看到一個規律：開始的索引 =（頁數 - 1）每頁顯示的條數
7 多表查詢#
其語法與單表查詢，基本一致，但是意義不同，首先要介紹下笛卡爾積
- 笛卡爾積：就是幾個東西的所有的組合情況，在資料庫中如果查詢多個表，就行將多個表的資料進行求笛卡爾積，也就是把所有的組合情況打印出來
假設有 A，B 兩張表，A 有 x 個資料，B 有 y 個資料，那麼查詢出來就會有x*y個資料
select * from A, B;
注意：直接進行這樣查詢，會有很多無用的資料，因此下面介紹如何消除無用的笛卡爾積
1 內連接查詢
- 隱式內連接：直接在
where條件語句指定，兩表的關聯字段相等即可
select * from 表1,表2 where 表1.id = 表2.id;
- 顯式內連接：
select 查詢字段 from 表名1 inner join 表名2 on 表1.id = 表2.id;
select 查詢字段 from 表名1 join 表名2 on 表1.id = 表2.id; [[inner可以省略]]
2 外連接查詢
- 左外連接：查詢左表的所有資料，以及其交集部分（一般用左外連接）
select 查詢字段 from 表名1 lift join 表名2 on 條件;
- 右外連接：查詢右表的所有資料，以及其交集部分
select 查詢字段 from 表名1 right join 表名2 on 條件;
3 子查詢
概念：即查詢的嵌套，稱嵌套的查詢是外面的查詢的子查詢
- 1 子查詢的結果是單行單列的
使用運算符進行判斷的都是這種，比如這裡，查詢最高成績的結果肯定是單個
# 查詢成績最高的人的資訊
-- 1 成績最高是多少
select max (salary) from emp; -- 90分
-- 2 查詢成績為最高的人的資訊
select * from emp where salary = 90
# 其實可以嵌套來寫
select * from emp where salary =(select max (salary) from emp);
- 2 子查詢是多行單列的
使用運算符in來進行接收
# 查詢所有2班與3班的學生資訊
-- 1 查詢2班與3班對應的班級編號
select id
from dept
where name= 1班 or name = 2班; -- 1 ,2
-- 2 查詢班級編號對應的學生資訊,or或者in
select *
from emp
where dept_id = 1 or dept_id = 2;
select *
from emp
where dept_id in (1,2);
# 可以進行嵌套的寫
select *
from emp
where dept_id
in(select id from dept where name= 1班 or name = 2班);
- 3 子查詢是多行多列的
多行多列就相當於一個虛擬的表
# 查詢所有的學生 入學日期是2020年1月1日之後的資訊和 班級資訊
# 普通查詢
select *
from emp t1, dept t2
where t1.id = t2.id and t1.date>2020-1-1;
# 嵌套查詢
select *
from dept t1, (select * from emp where emp.date >2020-1-1) t2
where t1.id = t2.id
六 DCL 管理用戶#
1 用戶管理#
1 添加用戶
create user 用戶名 identified by 密碼
create user 用戶名 @ 主機名 identified by 密碼
# 主機名表示此用戶只能在此主機登錄
# 如果不寫預設就是如下的形式，什麼機器都能登錄
create user '用戶名' @ '%' identified by 密碼
2 修改用戶密碼
# 方式一
update user set password = password('新密碼') where user = '用戶名';
# 方式二
set password for '用戶名'@ '主機名' = password('新密碼');
3 忘記 root 用戶密碼
# cmd 執行如下命令，關閉mysql服務
net stop mysql
# 使用無驗證方式啟動mysql的服務
mysqld --skip -grant -tables
# 打開新的窗口,輸入如下命令，即可無驗證進入，然後修改密碼
mysql
4 刪除用戶
drop user '用戶名' @ '主機名'
drop user '用戶名'
5 查詢用戶
在mysql下載好的時候，系統自帶的一個資料庫叫mysql，其中有張表叫user，裡面就存放了用戶的資訊，那麼查詢就是
use mysql;
select * from user;
2 權限管理#
1 查詢權限
show grants for '用戶名' @ '主機名';
show grants for '用戶名' @ '%';
2 授予權限
# 一個一個權限授予
grant 權限列表 on 資料庫名.表名 to '用戶名' @ '主機名';
# 權限列表：select，update，delete,inseat
# 一次授予全部權限
grant all on *.* to '用戶名' @ '主機名';
3 撤銷權限
# 一個一個權限撤銷
revoke 權限列表 on 資料庫名.表名 from '用戶名' @ '主機名';
# 一次全部撤銷
revoke all on *.* from '用戶名' @ '主機名';
七 約束#
顧名思義規範儲存的數。能存什麼，不能存什麼，有如下三大類：
1 實體完整性約束：
| 約束 | 說明 |
|---|---|
| primary key | 主鍵約束 |
| unique | 唯一約束，唯一，但可為 null |
| auto_increment | 自增，配合主鍵約束使用 |
2 域完整性約束
| 約束 | 說明 |
|---|---|
| not null | 非空 |
| default | 預設值約束 |
| 類型 | 類型約束 |
3 引用完整性約束：
也稱外鍵約束，優點能保證資料庫完整性，缺點速度慢，性能低。語法：
# 創建表時候增加
constraint foreign key (外鍵字段) references 另一張表名稱(另一張表的主鍵)
# 創建表後添加外鍵
alter table 表名 add constraint FK_ID foreign key(你的外鍵字段名) REFERENCES 外表表名(對應的表的主鍵字段名);
4 刪除約束：
- 刪除非空約束：
# 改名字，類型以及約束
alter table 表名 change 列名 新列名 新資料類型 新的約束;
# 只改列的資料類型
alter table 表名 modify 列名 新資料類型 新的約束
# 即重新指定列的屬性，去掉了約束
alter table stu modify id int;
- 刪除唯一約束：
alter table stu drop index 列名;
- 刪除主鍵約束：
alter table stu drop primary key;
八 範式#
範式：範式就是規範，即設計資料庫需要遵循的規範
1 範式的分類
| 範式 | 說明 |
|---|---|
| 第一範式（1NF） | 每一列都是不可分割的原子資料項 |
| 第二範式（2NF） | 在 1NF 的基礎上，消除非主屬性對主碼的部分函數依賴 |
| 第三範式（3NF） | 在 2NF 的基礎上，消除傳遞依賴 |
| 第四範式（4NF） | 在 3NF 的基礎上， |
| 第五範式（5NF） | 在 4NF 的基礎上， |
| 第六範式（6NF） | 在 5NF 的基礎上， |
2 函數依賴
- 完全函數依賴：可以通過前面的確定唯一的後面的
A-->B或者(A,B)-->C - 部分函數依賴：
(A,B)-->C但是A-->C，因此，是部分函數依賴 - 傳遞函數依賴：
A-->B並且B-->C因此A-->C這就是傳遞依賴 - 碼：如果在一個表中，一個屬性，或者屬性組被其他所有屬性完全依賴，則個屬性就稱為該表的碼
- 主屬性：碼屬性組中的所有屬性
- 非主屬性：除過碼屬性組的其他屬性
九 資料庫的備份與還原#
將資料庫保存為 sql 檔案，以及導入 sql 檔案
# 備份：
mysql dump -u用戶名 -p密碼 資料庫名稱 > D:\\xxx.sql;
# 還原,需要有個資料庫，
create database xxx;
use xxx;
source D:\\xxx.sql
十 事務#
事務就是把這些操作整合為一個整體，要麼都執行，要麼都不執行，如果事務期間出現異常那麼就會回滾，回到事務開始的地方，如果沒有問題，就提交事務。
每當執行一個 sql 語句的時候，就會默認開啟一個事務，而我們自己開啟事務的原因就是打破這個默認的單行語句事務，讓多行語句成為原子操作。
開啟事務
| 解釋 | 命令 |
|---|---|
| 開啟事務 | start transaction，begin，set uncommit = 0 這三種方式 |
| 回滾 | rollback |
| 提交 | commit |
查看，設置是否自動提交：
# 查看是否為自動提交
select @@autocommit ;
# 如果結果為1-----自動提交，mysql預設
# 如果結果為0-----手動提交，oracle預設
# 設置自動提交
set @@autocommit;
事務的四大特徵
1 原子性：事務是不可分割的最小單位，即是一個整體，要麼同時成功，要麼同時失敗，即事務是整體進行運行
2 一致性：事務的一致性，事務操作前後，事務的總量是不變的，比如我們轉帳，轉帳前後金額的總量是不變的
3 隔離性：多個事務之間，相互獨立，但是有一些的相互影響，需要事務的隔離級別進行限制
2 持久性：當事務提交或者回滾後，資料會持久的進行保存
事務的隔離級別
多個事務之間是隔離的，是相互獨立的，但是多個事務操作同一批資料會引發一些問題，設置不同的隔離級別進行解決這些問題:
1. 髒讀：一個事務，讀取到另一個事務沒有提交的資料
2. 不可重複度（虛讀）：在同一個事務中，兩次讀取到的資料不同，因為在讀的時候，有人修改了資料。
3. 幻讀：一個事務操作（DML：增刪改）資料表中的所有記錄，另一個事務添加了一條資料，則第一個事務無法查看到自己操作的結果，彷彿出現了幻覺，但 mysql 做了處理，不會出現幻讀。
隔離級別
| 隔離級別 | 說明 | 髒讀 | 不可重複讀 | 幻讀 |
|---|---|---|---|---|
| read uncommited | 讀未提交 | √ | √ | √ |
| read commited | 讀已提交 (Oracle 預設) | √ | √ | |
| repeatable read | 可重複讀 (MySql 預設) | √ | ||
| serializable | 串行化 |
隔離級別從小到大，安全性越來越高，但是效率越來越低
查詢隔離級別
select @@tx_isolation
# 結果如下：mysql預設
# +-----------------+
# | @@tx_isolation |
# +-----------------+
# | REPEATABLE-READ |
# +-----------------+
設置隔離級別
# 全局設置
set global transaction isolation level 隔離級別字串;
# s
set session transaction isolation level 隔離級別字串;
十二 視圖#
虛擬表，但和真實表一樣
優點：簡單，安全性高，用戶只能看到視圖內的資料
缺點：
- 視圖資料來源於資料庫，如果資料庫資料變化則跟著變化
- 沒有優化查詢性能
- 如果視圖有如下內容則不會更新
- 聚合函數的結果
- distinct 去重的結果
- group by 分組的結果
- having 篩選的結果
- union，union all 合併的結果
創建視圖：
create view 自己起視圖名 as 資料庫查詢語句
# 如果有此名稱的視圖則替換，可以作為修改語句
create or replace view 自己起視圖名 as 資料庫查詢語句
# 修改視圖
alter view 視圖名 as 查詢語句;
# 刪除視圖，視圖的刪除不會影響到原表
drop view 視圖名;
對視圖的操作也可以更新到資料庫，但是一般不這樣做。比如，視圖的資料來源是一張表的全部內容，而像連接查詢的結果作為視圖的資料源，則不可以進行修改，很容易理解。