（小白）MySQL基礎學習005 20201010

曆程

1. 子查詢

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

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

1. 主查詢

   主要的查詢對象，第一條select語句，确定的使用者所有擷取的資料目标（資料源），已經要具體得到的字段資訊

2. 子查詢與主查詢的關系

   子查詢是輔助，嵌入到主查詢中，子查詢可以獨立存在

3. 子查詢分類

   标量子查詢：結果是一個資料，一行一列

   列子查詢：傳回一列

   行子查詢：傳回一行

   表子查詢傳回多行多列

   Exists子查詢：傳回結果1或0

4. 按位置分

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

   from子查詢：子查詢出現的位置在from資料中（做資料源）

5. 标量子查詢

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

知道一個學生的名字，想知道他在哪個班級（班級名字）

1、通過學生找到班級ID

2、通過班級ID擷取班級名稱

select * from my_class where class_id=(select class_id from my_student where stu_name=“小江”);

需求決定主查詢，條件決定子查詢

1. 列子查詢

   select * from 資料源 where 條件判斷in(列子查詢)

   想擷取已經有學生在班的所有班級名字

   1、找出學生表中所有的班級ID

   2、找出班級表中對應的名字

列子查詢實作

select stu_name from my_class where class_id in (select class_id from my_student);

1. 行子查詢

   傳回結果為一行多列

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

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

   擷取班級年齡最大，且身高最高的學生

   1/求出班級年齡最大的值

   2、求出班級身高最高的值

   3、求出對應的學生

select * from my_student where (stu_age,stu_height)=(select max(stu_age),max(stu_height) from my_student);

1. 表子查詢，與行子查詢非常相似，隻是行子查詢需要産生行元素，而表子查詢沒有

   行子查詢where條件判斷，where子查詢

   表子查詢 from 資料源，from 子查詢

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

擷取每個班上最高身高的學生（一個）

select * from my_student group by class_id having stu_height =max (stu_height);

這樣是沒辦法得到的

正确的方法是：

1、将每個班最高的學生排在最前面：order by

2、再針對結果進行group by:保留每組第一個

select * from (select * from my_student order by stu_height desc) as temp group by class_id;

1. exists子查詢

   查詢傳回的結果隻有1或0

   where exists(查詢語句) 就是看結果是否存在，存在傳回1，不存在傳回0

   求出，有學生在的所有班級

   select * from my_class as c where exists(select stu_id from my_student as s where s.class_id = c.class_id);

   

2. 特定關鍵字

   In:

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

   any

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

   <>any：條件不等于任何一個

some

與any完全一樣，在國外some和any意思相同，但不定就完全不同了，not any 一點也不 not some一點點

all

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

<>all,不等于裡面所有

select * from my_student where class_id in (select class_id from my_class);

select * from my_student where class_id =any (select class_id from my_class);

select * from my_student where class_id <>any (select class_id from my_class);

這裡雖然是不等于，但相當于每一條資料隻要有一個不符合，就輸出，是以一樣的。

select * from my_student where class_id =all (select class_id from my_class);

select * from my_student where class_id <>all (select class_id from my_class);

查詢時如果資料為NULL，那麼此資料不會進行比對

1. 資料備份與還原

   mysql提供了一個專門用于備份sql的用戶端，mysqldump.exe

   

   就在bin目錄下

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

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

因為用到專門的用戶端，是以還沒與資料伺服器進行連接配接

mysqldump/mysqldump.exe -hPup 資料庫名字[表1【表2。。。】] >備份檔案位址

備份有三種形式：

1、整庫備份

mysqldump.exe -hlocalhost -P3306 -uroot -proot mydatabase2>c:/server/temp/mydatabase2.sql

我測試報錯，具體原因不知道

2、單表備份

3、多表備份

mysqldump -uroot -proot mydatabase2 my_student my_int >c:/server/temp/mydatabase2.sql

還是報錯！

1. 資料還原

   1、mysql.exe-hPup 資料庫 <檔案位置

   mysql -uroot -proot mydb < c:/server/temp/mydatabase2.sql

   2、source SQL 檔案位置//必須先進入到對應的資料庫

   source c:server/temp/mydatabase2.sql

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

2. 使用者管理

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

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

mysql使用者的資料，都是放在mysql中的user表中

select * from mysql.user\G

1. 建立使用者

   直接在mysql.user表中插入記錄，但不推薦

   專門建立使用者的指令：

   create user 使用者名 identified by 密碼;

   使用者：使用者@主機位址(host)

   主機位址：除了host，也可以是“”或%

   create user “user1”@"%" identified by “123456”;

select * from mysql.user\G;

發現user1已經建立成功了。

簡化建立：不限定用戶端ID，也沒有密碼

但這不安全，誰都可以通路

create user user2;

嘗試登入：

mysql -uuser2;

1. 删除使用者

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

   drop user 使用者名@host;

   

2. 修改使用者密碼

   提供了多種方式，但都需要系統函數password()

   需要靠該函數對密碼進行加密處理

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

   set password for 使用者=password(“新的明文密碼”)

   2、更新語句修改

   update mysql.user set password=password(“新的明文密碼”) where user =" and host="

   set password for ‘user1’@’%’ =password(‘654321’);

   

   

   123456無法登入，但654321可以登入，說明成功了

3. 權限管理

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

   1、資料權限：增删改查（select\update\delete\insert）一般隻給資料權限的更多

   2、結構權限：結構操作（create\drop）

   3、管理權限：權限管理（create user\grant\revoke）：通常隻給管理者如此權限

4. 授予權限：grant

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

   grant 權限清單on 資料庫/表名/to使用者

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

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

   grant select on mydatabase2.my_student to ‘user1’@’%’;

   

   

   用user1登陸，發現可以檢視對應的資料庫

   使用者被授權後，不需要退出，就可以檢視

5. 取消權限revoke

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

   revoke權限清單/allprivileges on 資料庫/*.表/*from 使用者

revoke select on mydatabase2.my_student from ‘user1’@’%’;

取消所有權限

revoke all privileges on mydb.my_student from ‘user1’@’%’;

回收權限同樣不需要重新整理或者重新登入。

1. 重新整理權限

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

   flush privileges;

   

   相當于把目前權限放到腳本裡去，雖然之前設定的時候就已經完成了權限更改，但重新整理相當于把權限設定寫進了腳本。

2. 密碼丢失的解決方案

   1、停止服務

   net stop mysql;

   我的電腦報錯，之前net就沒成功

   

   2、重新啟動服務：mysqld.exe-skip-grant-talbes//啟動伺服器便是路過權限

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

   4、修改root使用者密碼：

update mysql.user set password=password(“root”) where user=“root” and host=“localhost”;

1. 外鍵

   外鍵：froeign key

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

   B：主表

   A：從表

   增加外鍵：

   1:建立表的時候增加外鍵，類似主鍵

   字段後增加一條語句：

   constraint

   外鍵名

   froeign key[外鍵字段]references 主表（主鍵）

   這裡的

   外鍵名

   用的是反引号

   在esc下面的~

   這個按鍵

2：建立表後增加外鍵

alter table 從表 and [constraint

外鍵名

] froeign key[外鍵字段]references 主表（主鍵）

增加外鍵

create table my_foreign(

id int primary key auto_increment,

name varchar(10) not null,

class_id int,

foreign key(class_id) references my_class(class_id)

)charset utf8;

這裡的class_id 的KEY為MUL，為多索引的意思

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

desc my_student;

desc my_class;

修改my_student表，将class_id設為外鍵字段

alter table my_student add constraint

student_class_ibfk_1

foreign key (class_id) references my_class(class_id);

show create table my_student;

外鍵名字可以指定

1. 修改&删除外鍵

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

   alter table 從表 drop foreign key

   外鍵名字

   alter table my_student drop foreign key student_class_ibfk_1;

   

   這裡發現，雖然删除了外鍵，但my_student中class_id依然是MUL，外鍵建立會自動增加一個索引，但外鍵删除隻會删除自己，不能删除生成的普通索引。

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

外鍵基本要求：

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

2、基本屬性也要相同

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

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

1. 外鍵——限制

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

從表不能插入外鍵所在字段，主表不存在的資料

主表不能删除一個被引入的資料

insert into my_foreign values(null,“小明”,1);

insert into my_foreign values(null,“小李”,5);

這裡發現我兩條都加進去了，關鍵問題應該是我的引擎是myisam

delete from my_foreign where name=“小李”;

修改引擎：

alter table my_foreign ENGINE=innodb;

alter table my_class ENGINE=innodb;

alter table my_foreign add constraint

foreign_class_ibfk_1

foreign key (class_id) references my_class(class_id);

insert into my_foreign values(null,“小李”,5);

這時候就報了班級的錯誤！

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

   add froeign key(外鍵字段)references 主表（主鍵）on限制模式

   限制模式：

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

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

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

外鍵主要限制的是主表，從表主要不能插入主表不存在的資料

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

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

alter table my_student add foreign key(class_id) references my_class(class_id);

——限制

on update cascade,

on delete set null;

外鍵比較少使用

1. 視圖基本操作

   視圖的本質是SQL指令（select語句）

   基本文法：createview 視圖名字 as select 指令；

   //可以是意表資料，也可以是連接配接查詢，聯合查詢或子查詢

   create view student_class_v as select s.*,c.name from my_student as s left join my_class as c on s.class_id=c.class_id;

   報錯：

   

視圖本身是虛表，是以表的操作都适合于視圖

1. 使用視圖

   select 字段清單 from 視圖名字【子句】

2. 事務安全

   mysql允許統計事物管理，但隻能innodb引擎

   将使用者所做的操作，暫時儲存起來，直到最後确定才真正儲存

   通常事物是自動送出的，但也可以使用手動事物

3. 自動事務，使用者将一條SQL指令發到伺服器的時候，伺服器在執行之後，不用等待使用者回報結果，會自動将結果同步到資料表。

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

4. 檢視是否自動事務

   autocommit:

   show variables like “autocommit”;

   

   這是可以更改的！

5. 關閉自動事物；關閉之後系統就不在幫助使用者自動送出結果了

   set autocommit =off;

   

   這時候，如果我們添加資料，本地能看到本地的變化，但并沒有存儲到資料中，另外的用戶端是看不到資料更新的，一旦事物關閉，那麼需要使用者提供是否同步的指令

   commit:送出：同步到資料表，事務也會被清空

   rollback:復原（清空之前的操作，不要了）

一般不會關閉自動事物，隻會在需要事務處理的時候，才會進行手動事務

1. 手動事務：

   不管開啟、過程還是結束，都需要使用者（程式員），手動的發送事務操作指令來實作

   開啟事務：start transaction

   

   事務處理：

   執行事物：

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

   1、事務操作

   2、新增資料

   

   

   

   把小江的班級設為6

   

事務送出：commit

復原：rollback

復原點：

當一系列步驟中，如果後面失效了，但前面不需要復原的話，可以設定一個復原點。

增加復原點：

savepoint 復原點名字

復原到復原點：

rollback to復原點名字

在一個事務中，可以設定多個復原點，但是如果回到了前面的復原點，那麼 後面的復原點就失效了，相當于已經被清除了。

savepoint sp1;

update my_student set class_id =3 where stu_id=“stu001”;

此處我們假設要修改的是山姆，但我不小心寫成了夏洛的編号：

此時我們回到復原點：

rollback to sp1;

1. 事務特點：

   原子性、一緻性、帶離性、持久性

   

   隔離性，指的是目前使用者操作的資料，其它人是無法操作的

2. 系統變量

   mysql本質是一門程式設計語言，需要變量來儲存資料

   系統變量 ：

   系統變量有很多

   選擇系統變量的方法：

   select @@變量 @@變量稱為全局變量

   select @@autocommit

   

   修改系統變量：

   局部修改（會話級别）隻針對目前用戶端當次連接配接有效；

基本文法：set 變量名=新值

全局修改：針對所有的客戶，“所有時刻”都有效

基本文法：

set global變量名=值；

set @@global.變量名=值；

會話變量:

隻針對目前使用的用戶端有效

[email protected]變量名=值

set @name=“hello world”;

在mysql中沒有==來判斷是否等于，是以=号會有判斷和指派兩個功能，有時候就會在=指派的時候報錯，是以mysql提供了一個客戶的指派号：:=

set @age:=1;

mysql允許将查詢到的資料（隻能一行），放到變量中，一個變量對應一個字段值，mysql沒有數組

指派且檢視指派流程，[email protected]變量1=字段1，@變量2=字段2from資料表where 條件

select @name

select @name=stu_name,@age=stu_age from my_student limit 1;

以上是錯誤文法：

就是因為使用了=，系統當作了比較符号

select @name:=stu_name,@age:=stu_age from my_student limit 1;

隻指派，不看過程select字段1，字段2……from資料源where條件[email protected]變量[email protected]變量2

局部變量：

作用範圍在begin和end之間，

是用declare 生成的

文法：declare 變量名 資料類型[屬性]

1. if分支

   if在mysql中有兩種文法：

   1:在select查詢當中，當作一種查詢來進行判斷

   if(條件，為真結果，為假結果)

   mysql> select *,if(stu_age>17,“符合”,“不符合”) as judge from my_student where stu_age>17;

   

   select *,if(stu_age>17,“符合”,“不符合”) as judge from my_student;

   

2:用在複雜的語句塊中（函數/存儲過程/觸發器）

if 條件表達式 then

滿足條件要執行的語句

end if;

3：複合文法

if 條件表達式 then

滿足條件要執行的語句

else

不滿足條件要執行的語句

end if;

1. while循環

   while 條件 do

   循環執行的代碼

   End while;

   都需要在代碼塊裡執行

2. 結構辨別符

結構辨別符，為某些特定的結構進行命名，然後為的是在某些地方使用名字

基本文法：

辨別符名：while 條件

循環

End while[辨別符名];

辨別符存在，主要是為了循環體中使用循環控制，在mysql中沒有continue和break,但有自己的關鍵字替代：

iterate疊代，就是以下的代碼不執行，重新開始循環，相當于continue

leave離開，整個循環終止，相當于break

辨別名稱：while 條件 do

if 條件判斷 then

循環執行的代碼

iterate/continue 辨別名稱

循環執行的代碼

End while【辨別名稱】;

1. 函數

   系統函數/内置函數

   自字義函數

   無論是哪種函數，都是通過select 函數名(參數) 實作

2. 内置函數

   

   mysql> select char_length(“你好中國”),length(“你好中國”);

   

   select concat(“你好”,“中國”),instr(“你好中國”,“你”),instr(“你好中國”,“我”);

mysql> select lcase(“aBcD”),left(“你好中國”,2);

mysql> select ltrim(" a dbc "),mid(“你好中國”,2);

1. 時間函數

   

   mysql> select now(),curdate(),curtime();

   

   mysql> select datediff(“2010-10-10”,“1990-10-10”);

   

   mysql> select date_add(“2000-10-10”,interval 10 second);

   

   day/hour/minute/second

unix_timestamp:擷取時間戳

mysql> select from_unixtime(1234567890);

1. 數學函數

select abs(-1),ceiling(1.1),floor(1.1),pow(2,4),rand(),round(1.5);

1. 其它函數

select md5(“a”),version(),database(),uuid();