數(shù)據(jù)庫操作快速學(xué)習(xí)

MySQL基礎(chǔ)

一、DDL 操作

1.操作數(shù)據(jù)庫

1.創(chuàng)建數(shù)據(jù)庫

-- 創(chuàng)建數(shù)據(jù)庫 --
create database 數(shù)據(jù)庫名 character set utf8;

2.查看所有數(shù)據(jù)庫

-- 查看所有數(shù)據(jù)庫 --
show databases;

3.查看數(shù)據(jù)庫結(jié)構(gòu)

-- 查看數(shù)據(jù)庫結(jié)構(gòu) --
show create database 數(shù)據(jù)庫名;

4.刪除數(shù)據(jù)庫

-- 刪除數(shù)據(jù)庫 --
drop database 數(shù)據(jù)庫名;

5.修改數(shù)據(jù)庫字符集

-- 修改數(shù)據(jù)庫字符集 --
alter database 數(shù)據(jù)庫名 character set 字符集;

6.使用數(shù)據(jù)庫

-- 使用數(shù)據(jù)庫 --
use 數(shù)據(jù)庫名;

7.查詢當(dāng)前使用到數(shù)據(jù)庫

-- 查詢當(dāng)前使用的數(shù)據(jù)庫 --
select database();

2.操作表

1.常用的表數(shù)據(jù)類型

關(guān)于大文件：

一般數(shù)據(jù)庫很少寸文件的內(nèi)容，一般存儲(chǔ)文件的路徑。

2.約束

作用：保證用戶從插入的數(shù)據(jù)是符合規(guī)范的。

• 唯一：唯一約束，字段不能出現(xiàn)重復(fù)的數(shù)據(jù)。

• 非空：字段數(shù)據(jù)不能為空，必須要有數(shù)據(jù)。

• 主鍵：主鍵約束(非空+唯一)，一般用于字段id。

主鍵可以增加自動(dòng)增長 auto_increment ，主鍵自增。

3.查看所有表

-- 查看表 --
show tables;

4.查看表定義結(jié)構(gòu)

-- 查看表結(jié)構(gòu) --
desc 表名;

5.查看表創(chuàng)建結(jié)構(gòu)

-- 查看表結(jié)構(gòu) --
show create table 表名;

6.修改表

-- 增加一列：add --
alter table 表名 add 字段 類型 約束;
-- 修改列的名稱：change --
alter table 表名 change 舊列名 新列名 類型 約束;
-- 修改列的類型約束：modify --
alter table 表名 modify 字段 類型 約束;
-- 刪除一列:drop --
alter table 表名 drop 列名;
-- 修改表名 --
rename table 舊表名 to 新表名;

7.刪除表

drop table 表名;

二、DML操作表記錄

1. 插入數(shù)據(jù)

方式一：插入指定的列

insert into 表(列,列...) values(值,值...);

如果沒有把列寫出來，則自動(dòng)賦值null

方式二：插入所有列

insert into 表 values(值,值...);

主鍵自增的話可以賦值為null，其他所有字段必須賦值。

2. 更新記錄

 update 表名 set 字段=值... where 條件;

3. 刪除記錄

 可以使用2種方式刪除

• 使用 delete

• 使用 truncate 

delete 和 truncate的區(qū)別：

delete：刪除表中的數(shù)據(jù)，表結(jié)構(gòu)還在，記錄可以找回。

truncate：直接把表drop掉，再創(chuàng)建一個(gè)同樣的新表。

-- 刪除表數(shù)據(jù) --
delete table 表名 where 條件;;
-- 刪除整張表再重新創(chuàng)建表 --
truncate table 表名;  

在工作中的刪除：

• 物理刪除：真正刪除，數(shù)據(jù)不在。

• 邏輯刪除：假刪除，數(shù)據(jù)還在，做個(gè)標(biāo)記，1啟用，0禁用。
  

三、 DQL查詢表記錄

1. 單表查詢

1.1 簡單查詢

-- 條件查詢 --
select * from 表名 where 條件; 
-- 查詢某張表特定列 --
select 字段，字段... from 表名 where 條件;
-- 去重查詢(如果有重復(fù)的數(shù)據(jù)只留1個(gè)) --
select distinct 字段 from 表名 where 條件;
-- 別名查詢 (as可省略)--
select 字段 as 別名 from 表名 where 條件;
-- 運(yùn)算查詢(+,-,*,/等列運(yùn)算) --
select 字段 = 字段-2 from 表名 where 條件;

注意：去重查詢distinct前面不可有字段，否則會(huì)報(bào)錯(cuò)。

如果要查詢多個(gè)字段的去重查詢，則：

-- 多個(gè)字段去重查詢(如果有重復(fù)的數(shù)據(jù)只留1個(gè)) --
select distinct 字段,字段... from 表名 where 條件;

1.2 條件查詢

2.排序查詢

使用 order by 排序子句

排序語法：

• asc 升序

• desc 降序 （默認(rèn)）
  

2.1 單列排序

語法：只按照某個(gè)字段進(jìn)行排序。

如果有條件，要寫在排序前面。

-- 單列排序 --
select * from 表名 order by 字段 desc；

2.2 組合排序

同時(shí)對(duì)多個(gè)字段進(jìn)行排序，如果第1個(gè)的字段值相等，則按照第2個(gè)字段排序。

-- 組合排序 --
select * from 表名 order by 字段1 desc,字段2 asc;

3. 聚合函數(shù)

常用聚合函數(shù)：

語法：

聚合函數(shù)是縱向查詢，它是對(duì)一列的值進(jìn)行計(jì)算，返回一個(gè)結(jié)果值，聚合函數(shù)會(huì)忽略空值null。

-- 聚合函數(shù)語法 --
select 聚合函數(shù)(字段) from 表名 where 條件;

注意：

聚合函數(shù)會(huì)忽略null值，如果如果在統(tǒng)計(jì)個(gè)數(shù)的時(shí)候計(jì)算其他數(shù)值，則要把null的也加進(jìn)去，需要使用 ifnull(字段,默認(rèn)值) 函數(shù)，如果值不為null，則取原來的數(shù)，如果為null，則取默認(rèn)值。

-- 有null值的函數(shù) --
select 聚合函數(shù)(ifnull(字段,默認(rèn)值)) from 表名 where 條件;

4. 分組查詢

是指使用 group by 語句對(duì)查詢信息進(jìn)行分組。

如何分組的？

將分組字段中相同內(nèi)容作為一組，并返回每組的第一條數(shù)據(jù)，比如將性別分兩組。

目的：

分組就是為了統(tǒng)計(jì)，不然沒啥意義，一般要和聚合函數(shù)一起使用。

語法：

-- 分組查詢 --
select 字段1，字段2...  from 表名 [where  條件] group by 字段 [having 條件];

注意：

having是對(duì)分組之后的結(jié)果進(jìn)行篩選，要寫在后面。

where 和having的區(qū)別？【面試】

where：對(duì)查詢結(jié)果進(jìn)行分組前將不符合的行去掉，即先過濾再分組。

having：作用是篩選滿足條件的組，即在分組后進(jìn)行數(shù)據(jù)過濾，先分組再過濾。

前者where后面不可使用聚合函數(shù)，

后者后面可以使用聚合函數(shù)。

5.分頁查詢

limit 是限制的意思，作用是限制查詢記錄的條數(shù)，作為分頁查詢。

語法：

-- 分頁查詢 --
select * from 表名 limit 起始行數(shù),查詢條數(shù);

規(guī)律：

-- 規(guī)律 --
select * from 表名 limit (頁碼-1)*每頁顯示的總條數(shù),每頁顯示的總條數(shù);

MySQL進(jìn)階

1.外鍵約束

外鍵約束作用：維護(hù)多表之間的關(guān)系。

外鍵：一張從表中的某個(gè)字段引用主表的主鍵。

主表：約束別人。

從表：使用別人的數(shù)據(jù)被別人約束。

外鍵的語法

添加外鍵

1.創(chuàng)建表的時(shí)候添加外鍵約束

-- 添加外鍵 --
create table 表名(
...
...
constraint 外鍵名 foreign key(外鍵字段名) references 主表名(主鍵)
)

2.給已有表添加外鍵

-- 添加外鍵 --
alter table 表名 add constraint 外鍵名 foreign key(外鍵字段名) references 主表名(主鍵);

注意：為已有的表設(shè)置外鍵約束，那么外 鍵字段中的值一定要合法，不能是非法數(shù)據(jù)，否則添加外鍵約束失敗。（也就是說，外鍵的值是主鍵的值才可以成功設(shè)置外鍵）

刪除外鍵

-- 刪除外鍵 --
alter table 表名 drop foreign key 外鍵約束名；

注意：外鍵約束名是創(chuàng)建外鍵時(shí)的約束名而不是外鍵字段。

外鍵級(jí)聯(lián)

什么是級(jí)聯(lián)操作？

在修改和刪除主表的主鍵時(shí)，同時(shí)更新或刪除副表的外鍵值。

（主鍵修改，外鍵也會(huì)跟著修改）

• on update cascade 級(jí)聯(lián)更新，主鍵發(fā)生更新時(shí)，外鍵也會(huì)更新

• on delete cascade 級(jí)聯(lián)刪除，主鍵發(fā)生刪除時(shí)，外鍵也會(huì)刪除

在外鍵約束后添加即可：

-- 設(shè)置外鍵約束，并同時(shí)設(shè)置外鍵級(jí)聯(lián)操作 --
alter table 表名 add constraint 外鍵名 foreign key(外鍵字段名) references 主表名(主鍵) on update cascade on delete cascade;

也就是說，主表在進(jìn)行更新或者刪除記錄時(shí)，從表外鍵的記也會(huì)自動(dòng)更新或刪除。

2. 多表間關(guān)系

分析：下訂單(t_order)-->誰下(t_user)-->買了啥(t_product)

老師和學(xué)生，部門和員工等。

設(shè)計(jì)表有三種關(guān)系：

• 一對(duì)多

• 多對(duì)多

• 一對(duì)一
  

一對(duì)多（1:n）：

例如：班級(jí)和學(xué)生，部門和員工，客戶和訂單。

一的一方：班級(jí)、部門、客戶

多的一方：學(xué)生、員工、訂單

一對(duì)多建表原則：

在從表（多的一方）創(chuàng)建一個(gè)字段，字段作為外鍵指向主表（一方）的主鍵。

多對(duì)多（m:n）:

例如：老師和學(xué)生、學(xué)生和課程、用戶和角色

一個(gè)老師可以有多個(gè)學(xué)生，一個(gè)學(xué)生有多個(gè)老師

一個(gè)學(xué)生可以選多門課程，一門課程可以由多個(gè)學(xué)生選擇

一個(gè)用戶可以有多個(gè)角色，一個(gè)角色也可以有多個(gè)用戶

多對(duì)多關(guān)系建表原則：

需要?jiǎng)?chuàng)建第三張表，中間表至少有2個(gè)字段，這2個(gè)字段分別作為外鍵指向各自一方的主鍵。

一對(duì)一（1:1）：

這個(gè)沒啥好說的。

兩種建表原則：

• 外鍵唯一：主表的主鍵和從表的外鍵，形成主外鍵關(guān)系，外鍵unique

• 外鍵是主鍵：主表的主鍵和從表的主鍵，形成主外鍵關(guān)系
  

一般在開發(fā)中創(chuàng)建一張表即可。

3.多表查詢【重點(diǎn)】

1. 交叉查詢（連接查詢）

交叉查詢把若干張表沒有條件的連接在一起

-- 連接查詢 --
select .... from 表1,表2;
//或者選擇性的選擇要顯示的字段
select 表1.*,表2.某個(gè)字段 from 表1,表2;

左表的每條數(shù)據(jù)和右表的每條數(shù)據(jù)組合，效果稱為笛卡爾積。

注意：

交叉查詢其實(shí)是一種錯(cuò)誤，大部分?jǐn)?shù)據(jù)是無用數(shù)據(jù)，叫笛卡爾積，所以交叉查詢不是重點(diǎn)。

2. 內(nèi)連接查詢【重點(diǎn)】

交叉查詢的結(jié)果不是我們想要的，去除不想要的記錄，是通過條件過濾。

通常要查詢的多個(gè)表之間都存在關(guān)聯(lián)關(guān)系，那么就通過關(guān)聯(lián)關(guān)系(主外鍵關(guān)系)   去除笛卡爾積。

內(nèi)連接查詢出來的數(shù)據(jù)一定是有關(guān)聯(lián)的，查詢出的數(shù)據(jù)是公共部分，滿足連接條件(主外鍵關(guān)系)的部分，沒有關(guān)聯(lián)的數(shù)據(jù)是查不出的。

隱式內(nèi)連接查詢

-- 隱式內(nèi)連接查詢（一般外鍵字段=主鍵的值） --
select .... from 表1,表2 where 條件

顯式內(nèi)連接查詢

-- 顯式內(nèi)連接查詢 ，如果有其他條件，后面可加 where --
select .... from 表1 inner join 表2 on 條件;

提示：inner可以省略不寫，只寫join也可。

注意：條件為  

 表1.主鍵 = 表2.外鍵 ;

小結(jié)：

使用內(nèi)連接的關(guān)鍵點(diǎn)：

• 使用主外鍵關(guān)系作為條件去除無用信息，得到想要的數(shù)據(jù)。

• 顯式內(nèi)連接里面，on只能用主外鍵作為條件，如果有其他條件后面加where。

3. 外連接查詢【重點(diǎn)】

左連接查詢： 

在內(nèi)連接的基礎(chǔ)上保證左邊表的數(shù)據(jù)全部顯示

-- 左連接 --
select .... from 左表 left join 右表 on 關(guān)聯(lián)條件;

右連接查詢：

在內(nèi)連接的基礎(chǔ)上保證右邊表的數(shù)據(jù)全部顯示

-- 右連接 --
select .... from 左表 right join 右表 on 關(guān)聯(lián)條件;

連接查詢是以內(nèi)連接為基礎(chǔ)上的拓展。

小結(jié)：

• 內(nèi)連接：查詢公共部分，滿足連接條件的部分

• 左外連接：以左表為主表，查詢出左表的所有數(shù)據(jù)，再通過連條件匹配出右邊表的數(shù)據(jù)，如果滿足連接條件，展示右邊的數(shù)據(jù)，如果不滿足，右邊的數(shù)據(jù)通過 null 代替。

• 右外連接：以右表為主表，查詢出右表的所有數(shù)據(jù)，再通過連接條件匹配出左邊表的數(shù)據(jù)，如果滿足連接條件則展示左表的數(shù)據(jù)，如果不滿足，左邊的數(shù)據(jù)通過 null 代替。
  

4. 子查詢【重點(diǎn)】

什么是子查詢？

一個(gè)查詢語句里至少包含2個(gè)select。

• 個(gè)查詢語句的結(jié)果作為另一個(gè)查詢語句的條件

• 查詢語句的嵌套，內(nèi)部查詢成為子查詢

• 子查詢要使用括號(hào)

子查詢結(jié)果有三種情況：

1. 子查詢的結(jié)果是一個(gè)值

2. 子查詢的結(jié)果是單列多行

3. 子查詢的結(jié)果是多行多列

子查詢結(jié)果是一個(gè)值的時(shí)候

子查詢結(jié)果作為父查詢的查詢條件

-- 結(jié)果為一個(gè)值的子查詢 --
select .... from 表 where 條件字段[= > < <>] (子查詢);

子查詢結(jié)果是單列多行的時(shí)候

結(jié)果集類似一個(gè)數(shù)組，父查詢使用 in 運(yùn)算符

-- 結(jié)果為單列多行的子查詢 --
select .... from 表 where 條件字段 in (子查詢);

也就是說條件字段是否在子查詢結(jié)果集中作為父查詢條件。

子查詢結(jié)果是多行多列的時(shí)候

 結(jié)果集是多行多列，肯定在 from 后面作為表

子查詢作為表需要取別名，否則無法訪問表中字段。

-- 結(jié)果為多行多列的子查詢 --
select .... from (子查詢) as 表別名 where 條件;
-- 如果有多個(gè)表作為子查詢 --
select a.*.b.* from (子查詢1) a,(子查詢2) b where a.id=b.id;

子查詢小結(jié)：

子查詢我們要考慮的是子查詢的結(jié)果是一個(gè)值還是多個(gè)值，然后根據(jù)值來選擇作為什么條件。

• 單值，作為 where 后的條件

• 單列多行，作為 where 后條件，用 in

• 多行多列，放在 from 后作為虛擬表，取別名
  

5.事務(wù)

5.1 什么是事務(wù)？

事務(wù)是指邏輯上的操作，要么全部成功，要么全部失敗。

5.2 事務(wù)的作用？

保證一組操作全部成功或者失敗。

5.3 MySQL進(jìn)行事務(wù)管理

• 自動(dòng)事務(wù)管理（MySQL默認(rèn)）

• 手動(dòng)事務(wù)管理
  

自動(dòng)事務(wù)管理

一條sql語句就是一個(gè)事務(wù)（自動(dòng)開啟事務(wù)，自動(dòng)提交事務(wù)）。

手動(dòng)事務(wù)管理

• start transaction ; 開啟事務(wù)

• commit ; 提交事務(wù)

• rollback ; 回滾事務(wù) 
  

假設(shè)拋出異常就回滾事務(wù)即可。

一句話：沒問題就提交，有問題就回滾。

查詢事務(wù)「了解」

-- 查詢事務(wù)是否開啟 --
show variables like '%commit%';
-- 設(shè)置自動(dòng)事務(wù)開關(guān)  0:off 1:on --
set autocommit = 0;

5.4 回滾點(diǎn)「了解」

什么是回滾點(diǎn)？

在某些成功的操作之后，后續(xù)的操作可能成功可能失敗，但是不管成功還是失敗，前面的操作都已經(jīng)成功，可以在當(dāng)前的成功位置設(shè)置一個(gè)回滾點(diǎn)。可供后續(xù)失敗操作返回到該位置，而不是返回所有操作。

回滾點(diǎn)的操作：

-- 設(shè)置回滾點(diǎn) --
savepoint 名字;
-- 回到回滾點(diǎn) --
rollback to 名字;

回滾點(diǎn)小例子：

-- 開啟事務(wù) --
start transaction ;

-- sql語句操作 --
update....

-- 設(shè)置回滾點(diǎn) --
savepoint 名字;

-- sql語句操作(假設(shè)這里sql有問題異常) --
update....

-- 回到回滾點(diǎn) --
rollback to 名字;

-- 提交事務(wù) --
commit ;

注意：

• 建議手動(dòng)開啟事務(wù)，用一次就開啟一次

• 開啟事務(wù)后，要么commit，要么rollback

• 一旦事務(wù)提交或者回滾，當(dāng)前事務(wù)就結(jié)束了

• 回滾到指定回滾點(diǎn)，這時(shí)候的事務(wù)是沒有結(jié)束的
  

總結(jié)：

設(shè)置回滾點(diǎn)可以讓我們在失敗的時(shí)候回到回滾點(diǎn)，而不是回到事務(wù)開啟的時(shí)候。

5.5 事務(wù)特性和隔離級(jí)別

事務(wù)特性【面試題】

• 原子性（Atomicity）

• 一致性（Consistency)

• 持久性（Durability）

• 隔離性（IsoIation）
  

原子性：是指事務(wù)是一個(gè)不可分割的單位，事務(wù)中的操作要么全成功，要么全失敗。

一致性：事務(wù)前后數(shù)據(jù)的完整性必須保持一致。

持久性：是指一個(gè)事務(wù)一旦被提交，它對(duì)數(shù)據(jù)庫中的數(shù)據(jù)是永久性的。

隔離性：是指多個(gè)用戶并發(fā)操作數(shù)據(jù)庫時(shí)，一個(gè)用戶的事務(wù)不能被其他用戶的事務(wù)所干擾，多個(gè)并發(fā)事務(wù)直接數(shù)據(jù)要相互隔離，事務(wù)直接互不干擾。

事務(wù)的隔離級(jí)別

可以設(shè)置事務(wù)的隔離級(jí)別解決讀的問題。

不考慮隔離級(jí)別會(huì)引出一些問題:

• 臟讀

• 不可重復(fù)讀

• 幻讀
  

事務(wù)在操作時(shí)的理想狀態(tài)：所有事物之間保持隔離，互不影響。因?yàn)椴l(fā)操作，多個(gè)用戶同時(shí)訪問同一個(gè)數(shù)據(jù)，可能引發(fā)并發(fā)訪問問題

以上的問題可以用隔離級(jí)別解決。

事務(wù)的四個(gè)隔離級(jí)別

MySQL默認(rèn)隔離級(jí)別是可重復(fù)讀。

隔離界別越高，安全性越高，性能越差。

設(shè)置隔離級(jí)別

-- 設(shè)置隔離級(jí)別 --
set session transaction isolation level 隔離級(jí)別 ;

查詢當(dāng)前事務(wù)的隔離級(jí)別 

-- 查詢當(dāng)前事務(wù)隔離級(jí)別 --
select @@tx_isolation;

演示數(shù)據(jù)庫安全性問題的發(fā)生

解決子臟讀問題：

設(shè)置隔離級(jí)別為讀已提交read committed

-- 解決【臟讀】的隔離級(jí)別 --
set session transaction isolation level read committed ;

解決不可重復(fù)讀

-- 解決【不可重復(fù)讀】的隔離級(jí)別 --
set session transaction isolation level repeatable read ;

串行化隔離級(jí)別

-- 【串行化】的隔離級(jí)別 --
set session transaction isolation level Serializable;

也就是數(shù)據(jù)的讀，如果B事務(wù)正在操作未提交，要等B事務(wù)提交事務(wù)后，A事務(wù)才可進(jìn)行查詢到數(shù)據(jù)，也就是串行化隔離級(jí)別。

一般使用MySQL默認(rèn)的第3級(jí)可重復(fù)度的級(jí)別就行。

6.數(shù)據(jù)庫的備份和還原

命令行方式

備份

-- 備份 --
mysqldump -u用戶名 -p密碼 數(shù)據(jù)庫 > 文件路徑/數(shù)據(jù)庫名.sql;

還原 （要先創(chuàng)建數(shù)據(jù)庫）

-- 還原導(dǎo)入 --
source 文件路徑;

Navicat方式

這個(gè)方式就不說了嗷。

7. 數(shù)據(jù)庫三大范式

建立科學(xué)的、規(guī)范的數(shù)據(jù)庫需要滿足一些規(guī)則來優(yōu)化數(shù)據(jù)的設(shè)計(jì)和存儲(chǔ)，成為范式。

第一范式：

數(shù)據(jù)庫表的每一列都是不可分割的原子數(shù)據(jù)項(xiàng)，不能集合、數(shù)組等非原子數(shù)據(jù)項(xiàng)。簡而言之，每一列不可再拆分，成為原子性。

遵循第一范式，需要什么字段的數(shù)據(jù)就查詢什么數(shù)據(jù)（方便查詢）。

第二范式：

在滿足第一范式的前提下，表中的每一個(gè)字段都完全依賴于主鍵。

簡而言之：

• 一張表只描述一件事情

• 表中的每一個(gè)字段都依賴主鍵
  

第三范式：

在滿足第二范式的前提下，表中每一字段都直依賴于主鍵，而不是通過其他的列來間接依賴主鍵。

這個(gè)意思：（方便修改數(shù)據(jù)）

完結(jié)。