mysql 8從入門到精通_資料分析mysql入門到精通（4)

子查詢

什麼是子查詢

  子查詢概念

子查詢：sub query

    子查詢是一種常用計算機語言SELECT-SQL語言中嵌套查詢下層的程式子產品。當一個查詢是另一個查詢的條件時，稱之為子查詢。

    子查詢：指在一條select語句中，嵌入了另外一條select語句，那麼被嵌入的select語句稱之為子查詢語句。

  主查詢概念

主查詢：主要的查詢對象，第一條select語句，确定的使用者所有擷取的資料目标(資料源)，以及要具體得到的字段資訊。

   子查詢和主查詢的關系

1、  子查詢是嵌入到主查詢中的；

2、  子查詢的輔助主查詢的：要麼作為條件，要麼作為資料源

3、  子查詢其實可以獨立存在：是一條完整的select語句

子查詢分類

按功能分

标量子查詢：子查詢傳回的結果是一個資料(一行一列)

    列子查詢：傳回的結果是一列(一列多行)

    行子查詢：傳回的結果是一行(一行多列)

    表子查詢：傳回的結果是多行多列(多行多列)

    Exists子查詢：傳回的結果1或者0(類似布爾操作)

按位置分

         Where子查詢：子查詢出現的位置在where條件中

         From子查詢：子查詢出現的位置在from資料源中(做資料源)

标量子查詢

概念

    标量子查詢：子查詢得到結果是一個資料(一行一列)

文法

    基本文法：select * from 資料源 where 條件判斷 =/<>(select 字段名 from 資料源 where 條件判斷); //子查詢得到的結果隻有一個值

列子查詢

概念

列子查詢：子查詢得到的結果是一列資料(一列多行)

文法

基本文法：

主查詢 where 條件 in (列子查詢);

行子查詢

概念

    行子查詢：子查詢傳回的結果是一行多列

行元素

    行元素：字段元素是指一個字段對應的值，行元素對應的就是多個字段：多個字段合起來作為一個元素參與運算，把這種情況稱之為行元素。

文法

基本文法：

主查詢 where 條件[(構造一個行元素)] = (行子查詢);

總結

已經學過三個子查詢：常見的三個子查詢

标量子查詢、列子查詢和行子查詢：都屬于where子查詢

表子查詢

概念

    表子查詢：子查詢傳回的結果是多行多列，表子查詢與行子查詢非常相似，隻是行子查詢需要産生行元素，而表子查詢沒有。

行子查詢是用于where條件判斷：where子查詢

表子查詢是用于from資料源：from子查詢

文法

基本文法：

Select 字段表 from (表子查詢) as 别名 [where] [group by] [having] [order by][limit];

Exists子查詢

概念

Exists子查詢：查詢傳回的結果隻有0或者1，1代表成立，0代表不成立

文法

    基本文法：where exists(查詢語句); //exists就是根據查詢得到的結果進行判斷：如果結果存在，那麼傳回1，否則傳回0

Where 1：永遠為真

子查詢中特定關鍵字的使用

In

主查詢 where 條件 in (列子查詢);

Any

任意一個

= any(列子查詢)：條件在查詢結果中有任意一個比對即可，等價于in

<>any(列子查詢)：條件在查詢結果中不等于任意一個

1 =any(1,2,3)  ===== true

1  <>any(1,2,3)  ===== true

Some

與any完全一樣：在國外，some與any的正面含義一緻，但是否定就大不相同：not any與not some

開發者為了讓對應的使用者不要在文法上糾結：重新設計了some

All

= all(列子查詢)：等于裡面所有

<>all(列子查詢)：不等于其中所有

All資料展示

如果對應的比對字段有NULL，那麼不參與比對

整庫資料備份與還原

整庫資料備份也叫SQL資料備份：備份的結果都是SQL指令

在Mysql中提供了一個專門用于備份SQL的用戶端：mysqldump.exe

應用場景

       SQL備份是一種mysql非常常見的備份與還原方式，SQL備份不隻是備份資料，還備份對應的SQL指令(表結構)：即便是資料庫遭到毀滅性的破壞(資料庫被删)，那麼利用SQL備份依然可以實作資料還原。

      SQL備份因為需要備份結構，是以産生的備份檔案特别大，是以不适合特大型資料備份，也不适合資料變換頻繁型資料庫備份。

應用方案

SQL備份

SQL備份用到的是專門的備份用戶端，是以還沒與資料庫伺服器進行連接配接。

基本文法：mysqldump/mysqldump.exe   -hPup  資料庫名字 [表1   [表2…]]  >  備份檔案位址

備份可以有三種形式：

1、  整庫備份(隻需要提供資料庫名字)

2、  單表備份：資料庫後面跟一張表

3、  多表備份：資料庫後跟多張表

檢視備份的成果

檢視備份檔案中的具體内容

資料還原

Mysql提供了多種方式來實作：兩種

Mysqldump備份的資料中沒有關于資料庫本身的操作，都是針對表級别的操作：當進行資料(SQL還原)，必須指定資料庫

1、  利用mysql.exe用戶端：沒有登入之前，可以直接用該用戶端進行資料還原

Mysql.exe –hPup 資料庫 < 檔案位置

2、  在SQL指令，提供了一種導入SQL指令的方式

Source SQL檔案位置;         //必須先進入到對應的資料庫

3、  人為操作：打開備份檔案，複制所有SQL指令，然後到mysql.exe用戶端中去粘貼執行。(不推薦)

使用者權限管理

使用者權限管理：在不同的項目中給不同的角色(開發者)不同的操作權限，為了保證資料庫資料的安全。

通常，一個使用者的密碼不會長期不變，是以需要經常性的變更資料庫使用者密碼來確定使用者本身安全(mysql用戶端使用者)

使用者管理

Mysql需要用戶端進行連接配接認證才能進行伺服器操作：需要使用者資訊。Mysql中所有的使用者資訊都是儲存在mysql資料庫下的user表中。

預設的，在安裝Mysql的時候，如果不選擇建立匿名使用者，那麼意味着所有的使用者隻有一個：root超級使用者

在mysql中，對用的使用者管理中，是由對應的Host和User共同組成主鍵來區分使用者。

User：代表使用者的使用者名

Host：代表本質是允許通路的用戶端(IP或者主機位址)。如果host使用%代表所有的使用者(用戶端)都可以通路

   建立使用者

理論上講可以采用兩種方式建立使用者：

1、  直接使用root使用者在mysql.user表中插入記錄(不推薦)

2、  專門建立使用者的SQL指令

基本文法：create user 使用者名 identified by ‘明文密碼’;

使用者：使用者名@主機位址

主機位址：’’ / ‘%’

檢視mysql.user表中是否存在新增的使用者

簡化版建立使用者(誰都可以通路，不需要密碼)

當使用者建立完成之後，使用者是否可以使用?

   删除使用者

注意：mysql中user是帶着host本身的(具有唯一性)

基本文法：drop user 使用者名@host;

   修改使用者密碼

Mysql中提供了多種修改的方式：基本上都必須使用對應提供的一個系統函數：password()，需要靠該函數對密碼進行加密處理。

1、  使用專門的修改密碼的指令

基本文法：set password for 使用者 = password(‘新的明文密碼’);

修改後的資料測試

2、  使用更新語句update來修改表

基本文法：update mysql.user set password =password(‘新的明文密碼’) where user = ‘’ and host= ‘’;

權限管理

在mysql中将權限管理分為三類：

1、  資料權限：增删改查(select\update\delete\insert)

2、  結構權限：結構操作(create\drop)

3、  管理權限：權限管理(createuser\grant\revoke)：通常隻給管理者如此權限

   授予權限：grant

将權限配置設定給指定的使用者

基本文法：grant 權限清單 on 資料庫/*.表名/* to 使用者;

權限清單：使用逗号分隔，但是可以使用all privileges代表全部權限

資料庫.表名：可以是單表(資料庫名字.表名)，可以是具體某個資料庫(資料庫.*)，也可以整庫(*.*)

使用者被配置設定權限以後不需要退出就可以看到效果

具體權限檢視：單表權限隻能看到資料庫中的一張表

取消權限：revoke

權限回收：将權限從使用者手中收回

基本文法：revoke 權限清單/allprivileges on 資料庫/*.表/* from 使用者;

權限回收，同樣不需要重新整理，使用者馬上就會感受到

   重新整理權限：flush

Flush：重新整理，将目前對使用者的權限操作，進行一個重新整理，将操作的具體内容同步到對應的表中。

基本文法：flush privileges;

密碼丢失的解決方案

如果忘記了root使用者密碼，就需要去找回或者重置root使用者密碼

1、  停止服務

2、  重新啟動服務：mysqld.exe –skip-grant-tables//啟動伺服器但是跳過權限

3、  目前啟動的伺服器沒有權限概念：非常危險，任何用戶端，不需要任何使用者資訊都可以直接登入，而且是root權限：新開用戶端，使用mysql.exe登入即可

4、  修改root使用者的密碼：指定使用者名@host

5、  趕緊關閉伺服器，重新開機服務

外鍵

外鍵概念

如果公共關鍵字在一個關系中是主關鍵字，那麼這個公共關鍵字被稱為另一個關系的外鍵。由此可見，外鍵表示了兩個關系之間的相關聯系。以另一個關系的外鍵作主關鍵字的表被稱為主表，具有此外鍵的表被稱為主表的從表。外鍵又稱作外關鍵字。

外鍵：foreign key

一張表(A)中有一個字段，儲存的值指向另外一張表(B)的主鍵

B：主表

A：從表

外鍵的操作

   增加外鍵

Mysql中提供了兩種方式增加外鍵

1、  方案1：在建立表的時候增加外鍵(類似主鍵)

基本文法：在字段之後增加一條語句

[constraint `外鍵名`] foreignkey(外鍵字段) references 主表(主鍵);

MUL：多索引，外鍵本身是一個索引，外鍵要求外鍵字段本身也是一種普通索引

2、  方案2：在建立表後增加外鍵

Alter table 從表 add[constraint `外鍵名`] foreign key(外鍵字段) references 主表(主鍵);

外鍵名字可以指定

修改&删除外鍵

外鍵不允許修改，隻能先删除後增加

基本文法：alter table 從表 drop foreign key 外鍵名字;

外鍵不能删除産生的普通索引，隻會删除外鍵自己

如果想删除對應的索引：alter table 表名 drop index 索引名字;

   外鍵基本要求

1、  外鍵字段需要保證與關聯的主表的主鍵字段類型完全一緻；

2、  基本屬性也要相同

3、  如果是在表後增加外鍵，對資料還有一定的要求(從表資料與主表的關聯關系)

4、  外鍵隻能使用innodb存儲引擎：myisam不支援

外鍵限制

外鍵限制：通過建立外鍵關系之後，對主表和從表都會有一定的資料限制效率。

   限制的基本概念

1、  當一個外鍵産生時：外鍵所在的表(從表)會受制于主表資料的存在進而導緻資料不能進行某些不符合規範的操作(不能插入主表不存在的資料)；

2、  如果一張表被其他表外鍵引入，那麼該表的資料操作就不能随意：必須保證從表資料的有效性(不能随便删除一個被從表引入的記錄)

   外鍵限制的概念

可以在建立外鍵的時候，對外鍵限制進行選擇性的操作。

基本文法： add foreign key(外鍵字段) references 主表(主鍵)  on 限制模式;

限制模式有三種：

1、  district：嚴格模式，預設的，不允許操作

2、  cascade：級聯模式，一起操作，主表變化，從表資料跟着變化

3、  set null：置空模式，主表變化(删除)，從表對應記錄設定為空：前提是從表中對應的外鍵字段允許為空

外鍵限制主要限制的對象是主表操作：從表就是不能插入主表不存在的資料

通常在進行限制時候的時候，需要指定操作：update和delete

常用的限制模式： on update cascade, on delete setnull，更新級聯，删除置空

更新模式

删除模式

限制作用

保證資料的完整性：主表與從表的資料要一緻

正是因為外鍵有非常強大的資料限制作用，而且可能導緻資料在背景變化的不可控。導緻程式在進行設計開發邏輯的時候，沒有辦法去很好的把握資料(業務)，是以外鍵比較少使用。

視圖基本操作

建立視圖

視圖的本質是SQL指令(select語句)

基本文法：create view 視圖名字 as select指令;     //可以是單表資料，也可以是連接配接查詢，聯合查詢或者子查詢

檢視視圖結構：視圖本身是虛拟表，是以關于表的一些操作都适用于視圖

Show tables/show create table[view]/desc 視圖名字；

使用視圖

視圖是一張虛拟表：可以直接把視圖當做“表”操作，但是視圖本身沒有資料，是臨時執行select語句得到對應的結果。視圖主要使用者查詢操作。

基本文法：select 字段清單 from 視圖名字 [子句];

修改視圖

修改視圖：本質是修改視圖對應的查詢語句

基本文法：alter view 視圖名字 as 新select指令;

删除視圖

基本文法：drop view 視圖名字;

事務安全

事務概念

事務(Transaction)是通路并可能更新資料庫中各種資料項的一個程式執行單元(unit)。事務通常由進階資料庫操縱語言或程式設計語言書寫的使用者程式的執行所引起。事務由事務開始(begintransaction)和事務結束(end transaction)之間執行的全體操作組成。

事務基本原理

基本原理：Mysql允許将事務統一進行管理(存儲引擎INNODB)，将使用者所做的操作，暫時儲存起來，不直接放到資料表(更新)，等到用于确認結果之後再進行操作。

事務在mysql中通常是自動送出的，但是也可以使用手動事務。

自動事務

自動事務：autocommit，當用戶端發送一條SQL指令(寫操作：增删改)給伺服器的時候，伺服器在執行之後，不用等待使用者回報結果，會自動将結果同步到資料表。

證明：利用兩個用戶端，一個用戶端執行SQL指令，另外一個用戶端檢視執行結果

自動事務：系統做了額外的步驟來幫助使用者操作，系統是通過變量來控制的。Autocommit

Show variables like ‘autocommit%’;

關閉自動事務：關閉之後系統就不在幫助使用者送出結果了

Set autocommit = Off;

檢視執行結果

一旦自動事務關閉，那麼需要使用者提供是否同步的指令

Commit：送出(同步到資料表：事務也會被清空)

Rollback：復原(清空之前的操作，不要了)

事務沒有送出的對比檢視：在執行事務端的用戶端中，系統在進行資料檢視的時候會利用事務日志中儲存的結果對資料進行加工

通常，我們不會關閉自動事務：這樣操作太麻煩。是以隻會在需要使用事務處理的時候，才會進行操作(手動事務)

手動事務

手動事務：不管是開始還是過程還是結束都需要使用者(程式員)，手動的發送事務操作指令來實作。

手動事務對應的指令：

1、  start transaction; //開啟事務：從這條語句開始，後面的所有語句都不會直接寫入到資料表(儲存在事務日志中)

2、  事務處理：多個寫指令構成

3、  事務送出：commit/rollback，到這個時候所有的事務才算結束

開啟事務

執行事務

将多個連續的但是是一個整體的SQL指令，逐一執行

1、  事務操作1：新增資料

2、  事務操作2：更新資料

送出事務

确認送出：commit，資料寫到資料表(清空)

復原操作：rollback，所有資料無效并清空

復原點

復原點：savepoint，當有一系列事務操作時，而其中的步驟如果成功了，沒有必要重新來過，可以在某個點(成功)，設定一個記号(復原點)，然後如果後面有失敗，那麼可以回到這個記号位置。

增加復原點：savepoint 復原點名字; //字母數字和下劃線構成

回到復原點：rollback to 復原點名字; //那個記号(復原點)之後的所有操作沒有了

注意：在一個事務進行中，如果有很多個步驟，那麼可以設定多個復原點。但是如果回到了前面的復原點，後面的復原點就失效；

1、  增加復原點操作

2、  出現錯誤步驟

3、  回到正确點：復原

事務特點

事務應該具有4個屬性：原子性、一緻性、隔離性、持久性。這四個屬性通常稱為ACID特性。

原子性(atomicity)。一個事務是一個不可分割的工作機關，事務中包括的諸操作要麼都做，要麼都不做。

事務從start transaction起到送出事務(commit或者rollback)，要麼所有的操作都成功，要麼就是所有的操作都失敗；

一緻性(consistency)。事務必須是使資料庫從一個一緻性狀态變到另一個一緻性狀态。一緻性與原子性是密切相關的。

資料表中的資料修改，要麼是所有操作一次性修改，要麼是根本不懂

隔離性(isolation)。一個事務的執行不能被其他事務幹擾。即一個事務内部的操作及使用的資料對并發的其他事務是隔離的，并發執行的各個事務之間不能互相幹擾。

    如果一個用戶端在使用事務操作一個資料(可能是一行/整表)的時候，另外一個用戶端不能對該資料進行操作

什麼時候是行被隔離？什麼時候是整表被隔離？

說明：如果條件中使用了索引(主鍵)，那麼系統是根據主鍵直接找到某條記錄，這個時候與其他記錄無關，那麼隻隔離一條記錄；反之，如果說系統是通過全表檢索(每一條記錄都去檢查：沒有索引)，被檢索的所有資料都會被鎖定(整表)

持久性(durability)。持久性也稱永久性(permanence)，指一個事務一旦送出，它對資料庫中資料的改變就應該是永久性的。接下來的其他操作或故障不應該對其有任何影響。