學習MySQL

有幸在本科期間學過SQL Server這門課程，但是由於年代實在過於久遠，加之曾經為了裝軟體把家裡的老電腦裝崩了，從此之後資料庫就給我留下了陰影，現在已經忘得差不多了。。。而MYSQL擁有安裝輕便、使用簡單的優點，還是先來學習一下這個吧！

筆記按照b站上的“一天學會MySQL”課程為順序，一天學完。。。是不太可能的了

一、基礎語法

1.登入資料庫

在命令提示符—管理員中開啟（CMD/Ctrl+R）

Users>mysql -uroot 使用者名稱 

Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 4
Server version: 5.7.20 MySQL Community Server (GPL) Copyright (c) 2000, 2017, Oracle and
/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may 
be trademarks of their respective owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

2.隨時退出資料庫

mysql>exit
bye

或

mysql>\q
bye

或者ctrl+z

mysql>^z
bye

語句中注意一定要寫“；”

3.在資料庫服務其中建立一個新的資料庫（test）

mysql>create database test;
Query OK, 1 row affected (0.01 sec)

檢視MYSQL已建立的所有的資料庫：

show databases;

4.刪除該某資料庫（ppt）：

drop database ppt；

5.使用資料庫伺服器中的某一資料庫（test）

mysql>use test;
Database changed

6.檢視某資料庫（test）中的所有資料表

mysql>show tables;
Empty set (0.00 sec)                     \*代表資料表為空

7.在某資料庫下建立一個新資料表（pet）**

注意;一定要加入“，”，MYSQL對大小寫不敏感

mysql> create table pet(
    -> name varchar(20),
    -> owner varchar(20),
    -> species varchar(20),
    -> sex char(1),
    -> birth date,
    -> death date);
Query OK, 0 rows affected (0.05 sec)

再次檢視是否建立成功：

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| pet            |
+----------------+
1 row in set (0.00 sec)

mysql常用資料型別
支援多種型別，大致可以分為三類：數值、日期/時間、字串（字元）型別。

為了不破壞教程的完整性，詳情可見mysql的常用資料型別

7.刪除資料表（以表user4為例）：

mysql> drop table user4;
Query OK, 0 rows affected (0.01 sec)

8.檢視建立好的數資料表（table名為pet）的結構

mysql> describe pet;

+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | varchar(20) | YES  |     | NULL    |       |
| owner   | varchar(20) | YES  |     | NULL    |       |
| species | varchar(20) | YES  |     | NULL    |       |
| sex     | char(1)     | YES  |     | NULL    |       |
| birth   | date        | YES  |     | NULL    |       |
| death   | date        | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.02 sec)

Field 欄位
Type 欄位型別
Null 是否允許為空
Key 表示約束相關
Default 預設值為空

9.檢視資料表（pet）內的記錄**

mysql> select * from pet;
Empty set (0.00 sec)

9.為已有資料表（student）增加屬性（增加列）
eg. sbirthday為屬性名，datetime為屬性型別

 ALTER TABLE **student** ADD COLUMN **sbirthday** **datetime**;

10.向資料表（pet）中新增資料記錄**

mysql> insert into pet                                                           \*注意這裡沒有“；”，否則可能會報錯
    -> values ('Puffball','Diane','hamster','f','1999-03-30',null);
Query OK, 1 row affected (0.01 sec)                                              \*建立成功

查詢：

mysql> select  * from pet;
+----------+-------+---------+------+------------+-------+
| name     | owner | species | sex  | birth      | death |
+----------+-------+---------+------+------------+-------+
| Puffball | Diane | hamster | f    | 1999-03-30 | NULL  |
+----------+-------+---------+------+------------+-------+
1 row in set (0.00 sec)

多次新增資料，需要多次重複上述兩條指令
再次檢視：

11.刪除資料表（pet）中的資料**

mysql> delete from pet where name='ufl';
Query OK, 1 row affected (0.02 sec)

刪除後：

（後續刪除了最後重複的一條資料）

12.修改資料表（pet）中的資料**

mysql> update set name='所有者' where owner='Harold';
Query OK, 1 row affected (0.01 sec)                             

13.修改資料表名稱（從user7→user6）**

 mysql>alter table user7 rename to user6;
Query OK, 0 rows affected (0.02 sec)

小總結：

• 增加 insert
• 刪除 delete
• 修改 unpdate
• 查詢 select

二、約束

1.主鍵約束 (primary key)

能夠唯一確定一張表中的一條記錄，也就是我們通過對某個欄位新增約束，就可以使該欄位不重複且不為空。

(1)在某一屬性上試用 primary key
在這裡，我們建立一個新的資料表 user 供使用

mysql> create table user(
    -> id int primary key,
    -> name varchar(20));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into user values('1','zhangsan');
Query OK, 1 row affected (0.01 sec)

再次輸入：不可

mysql>  insert into user values('1','zhangsan');
ERROR 1062 (23000):                                              \*Duplicateentry '1' fro key 'PRIMARY'  ————因為之前在id部分的設定，不可提交重複的內容

id輸入null：不可

mysql>  insert into user values(null,'zhangsan');
ERROR 1048 (23000): Unknown error 1048                           \*  Column 'id' cannot be null                

更改id：可

mysql>  insert into user values('2','zhangsan');
Query OK, 1 row affected (0.01 sec)                                           

(2)限定於多種屬性上：聯合主鍵
——只要聯合的主鍵值中某一個不重複即可通過（類似於非門+）；但聯合主鍵的任一欄位不可為空

eg.先建立一個新的表 user2；

mysql> create table user2(
    -> id int,
    -> name varchar(20),
    -> password varchar(20),
    -> primary key(id,name));                      \*聯合主鍵
Query OK, 0 rows affected (0.03 sec)

插入一條資訊：

mysql> insert into user2 values(1,'zhangsan','123');
Query OK, 1 row affected (0.01 sec)

重複插入同樣的資訊，報錯

 insert into user2 values(1,'zhangsan','123');
 ERROR 1062 (23000):

改變某一（id）資訊，重複插入，成功（說明只要一個成立即可）

mysql>  insert into user2 values(2,'zhangsan','123');
Query OK, 1 row affected (0.01 sec)

設定id為null：

mysql> insert into user2 values(null,'wanger','123');
ERROR 1048 (23000): Unknown error 1048                                       \*ERROR 1048 (23000):Column 'id' cannot be null 任一欄位不可為空

2.自增約束

與 1.主鍵約束primary key 配合使用，會自動管控相應的值，使其自動增長

eg.
建立新的表user3

mysql> create table user3(
    -> id int primary key auto_increment,
    -> name varchar(20));
Query OK, 0 rows affected (0.02 sec)

在指定欄位 name上加入資訊：

mysql> insert into user3 (name) values('zhangsan');
Query OK, 1 row affected (0.01 sec)

此時自動生成了id

再次插入一個name：

mysql> insert into user3 (name) values('zhangsan');
Query OK, 1 row affected (0.01 sec)

此時自動產生id=2

——倘若沒有在創立表格時新增約束，接下來該如何處理？

eg.建立新表user4，含id，name

mysql>  create table user4(
    -> id int,
    -> name varchar(20)
    -> );

在表（user4）中修改表結構，新增對某一屬性（id）的主鍵約束：

mysql> alter table user4 add primary key(id);
 Query OK, 0 rows affected (0.04 sec)

如果需要對多個屬性新增約束：

mysql>  alter table user4 add primary key(id,name);
Query OK, 0 rows affected (0.04 sec)

Modify：或者以修改表（user4）中的屬性（以修改id屬性為例）新增約束

mysql> alter table user4 modify id int primary key;
Query OK, 0 rows affected (0.03 sec)

檢視修改後的情況：（desc是describe命令的縮寫）

mysql> desc user4;

刪除主鍵約束：

mysql> alter table user4 drop primary key;
Query OK, 0 rows affected (0.04 sec)

所有主鍵約束均被刪除：

3.唯一約束

約束脩飾的欄位的值不能重複

eg.建立一個僅id和name屬性的資料表（user5）

Unique新增唯一約束：

mysql> alter table user5 add unique(name);
Query OK, 0 rows affected (0.02 sec)

或MODIFY新增：

mysql> alter table user5 modify name varchar(20) unique;

直接新增：

mysql>  create table user7(
    -> id int,
    -> name varchar(20) unique
    -> );

或

mysql>  create table user7(
    -> id int,
    -> name varchar(20) ，
       -> unique(name)
    -> );

效果：插入資料後，再次插入已設定屬性（name）下相同資料時報錯

mysql> insert into user5 values(1,'zhagnsan');
Query OK, 1 row affected (0.01 sec)

mysql> insert into user5 values(2,'zhagnsan');
ERROR 1062 (23000):

若同時在為多種屬性（id,name）新增唯一約束

mysql> create table user8(
    -> id int,
    -> name varchar(20),
    -> unique(id,name)                          \*兩種中有一種屬性滿足唯一約束條件即可通過，類似於“或”
    -> );

key標識變為MUL

eg.插入資料

mysql> insert into user8 values(1,'zhangsan');
Query OK, 1 row affected (0.01 sec)

mysql>  insert into user8 values(1,'zhangsan');                           \*重新輸入相同的id和name
ERROR 1062 (23000):

mysql> insert into user8 values(2,'zhangsan');                            \*改變id
Query OK, 1 row affected (0.01 sec)

mysql>  insert into user8 values(1,'zhangsn');                            \*改變name
Query OK, 1 row affected (0.01 sec)

刪除唯一約束：

mysql> alter table user8 drop index id;                                        \*在這裡，MUL與id一行
Query OK, 0 rows affected (0.02 sec)

4.NOT NULL非空約束

eg.建立一個在name上具有not null屬性的資料表user9

mysql> create table user9(
    -> id int,
    -> name varchar(20) not null
    -> );
Query OK, 0 rows affected (0.02 sec)

NULL欄產生了標記（name不能為空）：

嘗試新增資料：

mysql> insert into user9(id) values(1);                      \*name為空，不符合設定
ERROR 1364 (HY000):

mysql> insert into user9 values(1,'zhangsan');                                           
Query OK, 1 row affected (0.01 sec)

mysql> insert into user9(name) values('lisi');               \*id為空，不影響插入資料
Query OK, 1 row affected (0.01 sec)

5.預設約束

插入欄位值時，若未傳值，就會使用預設值
eg.建立一個屬性上具有預設約束的資料表user10

mysql> create table user10(
    -> id int,
    -> name varchar(20),
    -> age int default 10
    ->   );
Query OK, 0 rows affected (0.02 sec)

mysql> insert into user10 (id,name) values(1,'zhangsan');    \*未指定age，自動指定預設值
Query OK, 1 row affected (0.01 sec)

mysql> insert into user10 (id,name,age) values(2,'lisi',19); \*傳值age，則不會用到預設值
Query OK, 1 row affected (0.01 sec)

6.外來鍵約束

• 涉及兩種表：父表、子表（主表、副表）

eg.建立兩種表並進行關聯
父表：

mysql> create table classes(
    -> id int primary key,
    -> name varchar(20)
    -> );
Query OK, 0 rows affected (0.02 sec)

子表：

mysql>  create table students(
    ->  id int primary key,
    ->  name varchar(20),
    -> class_id int,
    -> foreign key(class_id) references classes(id)      \*表中的class_id屬性值必須在classes表內的id
    -> );
Query OK, 0 rows affected (0.02 sec)

子表中KEY型別出現MUL：

母表插入多條資料：

mysql> insert into classes values(1,'一班');
Query OK, 1 row affected (0.01 sec)

mysql>  insert into classes values(2,'二班');
Query OK, 1 row affected (0.01 sec)

mysql> insert into classes values(3,'三班');
Query OK, 1 row affected (0.01 sec)

mysql>  insert into classes values(4,'四班');
Query OK, 1 row affected (0.01 sec)

子表：

mysql> insert into students values(1001,'張三',1);
Query OK, 1 row affected (0.01 sec)

mysql> insert into students values(1002,'張三',2);
Query OK, 1 row affected (0.01 sec)

mysql> insert into students values(1003,'張三',3);
Query OK, 1 row affected (0.01 sec)

mysql> insert into students values(1004,'張三',4);
Query OK, 1 row affected (0.01 sec)

mysql>  insert into students values(1005,'lisi',5);   \*出現錯誤，表classes中沒有id=5
ERROR 1452 (23000): Unknown error 1452

• 主表classes中沒有的資料值，在副表中，是不可以使用的。
• 若母表已經被引用，則該記錄不可以被刪除
  eg.

mysql> delete from calsses where id=4；              \*若主表子表被引用class_id=4，則母表的id=4不能刪除
ERROR 1146 (42S02): Unknown error 1146

三、資料表設計

資料庫的三大設計正規化
正規化根據實際操作來選定

1. 第一正規化（1NF）

資料表中的所有欄位都是不可分割的原子值
eg.該例子中（1）的欄位值還可以繼續拆分，不滿足第一正規化
address-》cuntry | privence | city | details

mysql> create table student2(
    -> id int primary key,
    -> name varchar(20),
    -> address varchar(30)
    -> );
    
mysql>  insert into student2 values(1,'張三','中國四川省成都市武侯區武侯大道100號');
Query OK, 1 row affected (0.01 sec)

mysql>  insert into student2 values(2,'李四','中國四川省成都市武侯區京城大道200號');
Query OK, 1 row affected (0.01 sec)

mysql>  insert into student2 values(3,'李五','中國四川省成都市武侯區天府大道90號');               \*這裡的address值還可以繼續拆分
Query OK, 1 row affected (0.01 sec)

設計的越詳細，對於某些實際操作有益，但可能會增加一些複雜度

2. 第二正規化（2NF）

必須在滿足第一正規化的前提下。
第二正規化要求，除主鍵外的每一列都必須完全依賴於主鍵
如果要出現不完全依賴，只可能發生在聯合主鍵的情況下。

eg.訂單表.(1)除主鍵以外的其他列，只依賴於主鍵的部分欄位。

mysql>   create table myorder(
    -> product_id int,
    ->  customer_id int,
    ->  product_name varchar(20),
    -> customer_name varchar(20),
    -> primary key(product_id,customer_id)
    -> );

進行拆分，以滿足第二正規化：

mysql>   create table myorder(
    -> product_id int,
    ->  customer_id int,
    ->  product_name varchar(20),
    -> customer_name varchar(20),
    -> primary key(product_id,customer_id)
    -> );
    
mysql>   create table myorder(
    -> order_id int  primary key
    ->  product_id int，
    -> customer_id int
   );
mysql>   create table product(
   -> id int primary key;
   -> name varchar(20),
    );

mysql>   create table customer(
   -> id int primary key;
   -> name varchar(20),
    );

3. 第三正規化（3NF）

必須先滿足第二正規化，除開主鍵列的其他列之間不能有傳遞依賴關係。
eg.

create table myorder(
order_id int primary key,
product_id int,
customer_id int,
customer_name varchar(20);        \*customer id和name有傳遞依賴關係，所以該表不代表3NF
);

create table myorder(
order_id int primary key,
product_id int,
customer_id int
);
create table customer(
id int primary key,
name varchar(20),
phone varchar(15)
);

四、查詢練習

eg. 學生表、課程表、成績表、教師表
注意聯合主鍵的位置

1.查詢表（student）中的某些列（sname，ssex，class）：

其它列不會顯示

mysql> select sname, ssex, class from student;

2.DISTINCT查詢表（student）中的某屬性（class）下不重複的列

eg.查詢不同班級並列出

mysql> select distinct class from student;
+-------+
| class |
+-------+
| 95033 |
| 95031 |
+-------+
2 rows in set (0.01 sec)

3. 查詢表中數字在101~102間的所有記錄

eg.BETWEEN 從表(student)中查詢屬性值(class)介於某範圍（95032≤class≤95033）之間的值（在這裡class的值為varchar（20）也是適用的）

mysql> select *from  student  where  class  between  95032  and  95033 ;

·還可以直接使用運算子比較（表score，引數degree為int形式，範圍80≤degree≤100）

mysql>  select * from score where degree>80 and degree <100;

如果只需要顯示某些屬性，可以這麼寫：

mysql>  select sno,degree from score where degree>80 and degree <100;

4. 查詢在特定屬性值情況下的所有資料

eg.查詢表（score）中屬性（degree）為某些值（85,86,88）的結果

mysql>  select * from score where degree in(85,86,88);

5.查詢表中不滿足不同屬性下某值的結果

eg.查詢表（student）中屬性1（class）和屬性2（sex）為x（‘95031’） 和y （‘女’）的記錄

mysql>   select *  from student where class='95031' or sex='女'；

6.以某屬性為準按一定順序序查詢資料表中所有記錄

eg.降序查詢以class屬性為準的表student的資料

mysql>  select * from student order by class desc;

eg.升序：

select * from student order by class asc;

或者省略asc：

mysql>  select * from student order by class;

7.以A屬性升序，B屬性降序查詢score表中的所有記錄

eg.以屬性cno升序，且degreee降序，查詢表score的所有記錄

mysql>  select * from score order by cno as,degree desc;

8. COUNT查詢某屬性下的記錄個數

• 需用到統計 count
  eg.符合統計表student中屬性（class）為值A（‘95031’）的資料數

mysql>  select  count(*) from student where class='95031';

9. 檢視某屬性資料最值的資料資訊

eg.查詢表（score）中的某最值屬性（degree的最大值）對應的其它屬性資訊（學生學號sno）和（課程號cno）

mysql> select sno,cno from score where degree=(select max(degree) from score);

該指令屬於子查詢（1.找到最高分 2.找最高分的sno 和cno）
·有時可能會忘記表（score）所設定的屬性的類別，可以通過相應指令來檢視原始程式碼：

mysql> show create table score;

結果:

10. 排序的做法

eg. 顯示錶（score）中的某幾個屬性（sno cno degree三種）並按照某一屬性（degree）的升序排列

mysql> select sno，cno，degree from score order by degree;

eg.顯示倒序第一條
limit（x，y）y——查詢多少條，x——從第x條開始（起始號為0）

mysql> select sno，cno，degree from score order by degree desc limit 0,1;

11. 計算表中限定條件下某屬性值的平均值

eg.從表（score）中，在特定條件下（屬性cno為=‘3-105’）查詢屬性（degree）的平均值

select avg（degree）from score where cno=‘3-105’；

12. 計算表中限定條件下某屬性值的平均值（不同條件同時顯示）

eg.顯示在不同條件限制下（屬性cno包含的所有屬性值），表（score）中某屬性（degreee）的平均值

mysql>select cno，avg（degree）from score group by cno’；

13.查詢表中特定條件的平均數

複雜問題分段操作：
eg. 找出表（score）中至少有2名學生選修，並以3開頭的課程（cno）的平均分數（degree），並統計人數

mysql>select cno,avg(degree),count(*) from score group by cno
->having count (cno)>=2 and cno like 3%;

14.同時查詢多個表中的某些特定屬性對應的資料

eg.從多個表（student、score）中顯示多屬性（sname| cno，degree）對應的資料

mysql>select snme,cno,degree from student,score
->where student.sno=score.sno;                            \*有主鍵

eg.查詢所有學生的sname屬性（表student）,cname屬性（表course）和degree屬性（表score）列
並將sname列命名為stu_name

mysql>select sname as stu_name, cname ,degree from student,course,score
->where student.sno=score.sno and course.cno=score.cno;

eg. 查詢“95031”班（class限定）的學生（sno）每門課（score資料表）的分數

mysql>select * from score where sno in (select sno from student where class=‘95031’)                           \*資料表score的含有sutdent的資訊
->

eg. 查詢“95031”班（class限定）的學生（sno）每門課（score資料表）的平均分

select cno,avg(degree) from score where sno in (select sno from student where class=‘95031’)  group by  cno;                    

WHERE ·GROUPBY ·AVG · df·

eg.查詢選修課程“3-105”的成績高於“109“號同學"3-105"課程的所有記錄

select * from score where cno='3-105' and degree> (select degree from score where sno='109' and cno='3-105');               

eg.在表（student）中查詢學號（sno）為108,101的同學的出生日期（sbirthday）的年份

select year（sbirthday） from student where sno in（108,101）

eg.在表（student）中查詢學號（sno）為108,101的同學的出生日期（sbirthday）的年份相同的所有人的資訊
```
select * from student where year (sbirthday) in (select year（sbirthday） from student where sno in（108,101));

eg. 查詢選修課某課程(cno)的同學人數多於5人的教師姓名(tname)

select cno from score group by cno having count(*)>5

select* from teacher;
select tno from course where cno  in (select cno from score group by cno having count(*)>5);

select tname from teacher in(select tno from course where tno  in (select tno from course where cno  in (select cno from score group by cno having count(*)>5));

eg.查詢出“計算機系“ 和"電子工程系"且不同職稱的教師的tname，prof（並集-交集）
——Union求並集

select prof from teacher where depart="電子工程系"；

select* fromteacher where depart=“計算機系”and prof not in（select prof from teacher where depart="電子工程系"）     \*無分號
union
select* fromteacher where depart=“電子工程系" and prof not in（select prof from teacher where depart=“計算機系”）；

eg.查詢選修編號為“3-105”課程且成績 至少大於一個 選修編號為“3-245”的同學的所有資訊，並按Degree從高到低次序排序

• 至少一種\ ANY

select * from score where cno='3-105' and degree any(select degree from score where cno=‘3-245’)
->order by degree desc;

eg.找出取得課程"3-105"的成績，高於所有選修編號“3-245”成績的同學的所有資訊

• 且\ ALL的使用

select * from score
->where cno="3-105" 
->and degree>all(select from score where cno='345');

eg.查詢教師、同學的name sex 和birthday

select tname as name,tsex as sex,tbithday as bithday from teacher
->union
->select sname ,ssex,sbirhtday from student;           \*兩張表合一後表頭名按照tanme，tsex，tbithday排列（第二條指令預設與第一條同），需改變名稱

eg.？？？查詢成績<該課程平均成績的同學的成績表

cerate temporary table b select * from a;                                \*？？？？？？？
select * from score score where degree<(select avg(degree) from score b where a.cno=b.cno);

eg.所有任課老師的tnme 和deoart

select tname depart from teacher where tno in (select tno from course);

eg.查詢至少有2名男生的班號

select class from student where ssex='男' group by class having count(*)>1;`

eg.查詢student表中不姓“王的同學記錄”

• 模糊查詢 NOT LIKE 用到了正規表示式

select * from student where sname not like ''王%;                      \*   %萬用字元，表示任意字元，表示不是比較的重點

eg.查詢student表中每個學生的姓名（sname）和年齡（表中僅有date屬性）
select year(now())；

• NOW（）

select snme，year（now（））-year（sirthday） as ‘年齡’ from student；

eg.查詢student表中最大和最小的sbirthday日期值

• MAX MIN

select max(sbiethday) as '最大', min(sbiethday) as '最小'from student；  \*但是這裡的max和min是按照數字大小排列的

eg.以班號（屬性class）和年齡（屬性sbirthday）從大到小的順序查詢表（student）中的全部記錄

select * from student order by class desc,sbirthday;                \*屬性class相比sbirthday的優先順序更高

eg.查詢與Alice性別且同班同學的sname。

sleect * from student where 
->ssex=（select ssex from student where name=‘Alice’）
->and class=（select class from student where name=‘Alice’）；

eg.查詢所有選修“計算機導論”課程（course表）的“男”同學（student）的的成績表（score）

select * fromscore
->where cno=（select cno from course where cname=‘計算機導論’）
->and sno in (slect sno from student where ssex='男')；

eg.查詢所有同學的sno、cno和grade

• 新建一個表格，用來存放成績等級

mysql> create table grade(
    -> low int(3),
    -> upp int(3),
    -> grade char(1)
    -> );
mysql> insert into grade values(90,100,'A');
Query OK, 1 row affected (0.02 sec)

mysql> insert into grade values(80,89,'B');
Query OK, 1 row affected (0.01 sec)

mysql> insert into grade values(70,79,'C');
Query OK, 1 row affected (0.01 sec)

mysql> insert into grade values(60,69,'D');
Query OK, 1 row affected (0.01 sec)

mysql> insert into grade values(0,59,'E');
Query OK, 1 row affected (0.01 sec)

• 各表進行組合

mysql>select sno cno grade from score,grade where degree between low and upp;

五、連線查詢

eg.建立兩張表

內連線 inner join / join

將多表中有關聯的欄位進行等值連線
（表中資料通過某個欄位相對，查詢出相關記錄資料）

mysql>select * from person inner join card on person.cardId=card.id;
mysql>select * from person inner join card on person.cardId=card.id;     \*和上一條效果相同

![在這裡插入圖片描述](https://img-blog.csdnimg.cn/20201225204434848.png)

外連線

1. 左連線 left join / left outer join
   

左外連線，會將左側表中所有的資料提取出，而右側資料，若相等則顯示；不相等則補NULL

mysql>select * from person left join card on person.cardId=card.id;
mysql>select * from person left outer join card on person.cardId=card.id;          \*和上一條效果相同

2. 右連線 irght join right / outer join
   

mysql>select * from person right join card on person.cardId=card.id;
mysql>select * from person right outer join card on person.cardId=card.id;          \*和上一條效果相同

3. 完全外連線 fulll join / full outer join
   

mysql不支援full join

mysql>select * from person full join card on person.cardId=card.id;
mysql>select * from person full outer join card on person.cardId=card.id;          \*和上一條效果相同

ERROR 1054 (42S22): Unknown colum 'person.cardId' in 'on clause''

六、事務

mysql中，事務是一個最小的不可風格的工作單元。事務能夠保證一個業務的完整性。

eg.銀行轉賬：
a -> -100
update user set money=money-100 where name =‘a’;
b->+100
update user set money=money+100 where name =‘a’;

• 而在實際的程式中，如果只有一條語句執行成功，而另一條沒執行成功時，可能會發生問題
• 出現資料前後不一致
  update user set money=money-100 where name =‘a’;
  update user set money=money+100 where name =‘a’;
• 多條sql語句，可能會有同時成功的要求，要麼就同時失敗。

事務的四大特徵：
A. 原子性：事務是最小的單位，不可再分
C. 一致性：食物要求 ，同一事物中的sql語句，必須同時保證同時成功或者同時失敗。
I. 隔離性：事務1 和 事務2 之間是具有隔離性的。
D. 永續性：事務一旦結束（commit，rollback），就不可返回

MYSQL如何控制事務

建立一個表格：

       1.mysql預設是開啟事務（自動提交）

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set (0.01 sec)

• 作用：當執行一個sql語句時，效果會立即體現出來，且不可回滾
  回滾：ROLLBACK

mysql> rollback;
Query OK, 0 rows affected (0.01 sec)

而進行該操作之後表並未產生變化（插入操作並未撤銷）

而關閉mysql的自動提交（AUYOCOMMIT=0）後：

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            0 |
+--------------+
1 row in set (0.00 sec)

再次新增資料並使用ROLLBACK：
新增資料的操作（其實未提交，是臨時效果）被撤銷，提供了返回的機會：
若需要保留資料，在insert完成後使用COMMIT：

mysql> commit;

再次使用rollback，結果不變（永續性）

再次調回commit=1，自動提交

（1）手動開啟事務後的資訊撤回：採用 BEGIN可以使rollback生效

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql>  update user set money=money-100 where name='a';
Query OK, 1 row affected (0.00 sec)

mysql> update user set money=money+100 where name='b';
Query OK, 1 row affected (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.01 sec)

撤回前後：

（2）手動開啟事務後的資訊撤回：採用START TRANSACTION也可以有以上效果

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update user set money=money-100 where name='a';
Query OK, 1 row affected (0.00 sec)

mysql> update user set money=money+100 where name='b';
Query OK, 1 row affected (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

但若在rollback使用前輸入commit，則不會觸發rollback效果

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

事務的隔離性

1. read uncommitted；讀未提交的

• 若在兩個不同地方，都在進行操作，若事務a開啟之後，它的資料可以被其他事物讀取到 這樣會出現——髒讀

• 髒讀：一個事務讀到了另外一個事務未提交的資料，就叫髒讀，這在實際開發中是不允許出現的。

如果有事務a和事務b：

（1）a事務對資料進行操作，在操作的過程中，事務沒有被提交，但b可以看到a的操作結果

·檢視資料庫的隔離級別？

• 對於mysql 8.0：

mysql>select @@global.transaction_isolation;
mysql> select @@transaction_isolation;

• 對於mysql 5.x：

mysql> @@global.transaction_isolation；
mysql> @@tx_isolation;

預設隔離級別：

·修改資料庫的隔離級別

set global transaction isolation level read uncommitted;

隨後隔離性變為READ-UNCOMMITTED：

mysql>  select @@global.transaction_isolation;
+--------------------------------+
| @@global.transaction_isolation |
+--------------------------------+
| READ-UNCOMMITTED               |
+--------------------------------+
1 row in set (0.00 sec)

eg.轉賬：小明在淘寶店買商品：800元
小明→成都 ATM
淘寶店→ 廣州AMT

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> update user set money=money-800 where name='小明';
Query OK, 1 row affected (0.00 sec)

mysql>  update user set money=money+800 where name='淘寶店';
Query OK, 1 row affected (0.00 sec)

mysql> rollback;                                \*而另一方進行rollback操作，則錢沒了
Query OK, 0 rows affected (0.00 sec)

2. read committed;讀已提交的

• 將系統轉為read committed模式

mysql> set global transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)

 事務a和事務b同時操作一張表，事務a提交的資料，不能被事務b讀取到，就會造成幻讀。

eg.兩方共同使用資料庫，雖然只能讀到提交的資料，但還是會出現問題：另一方發現前後不一致

3. repeatable read; 可以重複讀

eg.兩方共用一個資料，前後插入資訊並提交：

A:

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql>insert into user values(6,'d',1000);
Query OK, 1 row affected (0.01 sec)
mysql> commit;
Query OK, 0 rows affected (0.01 sec)

B：

mysql>insert into user values(6,'d',1000);
ERROR 1062（23000）：Duplicate entry '6' for key 'primary';                  \*出現幻讀現象

4. serializable;單行化

序列化帶來的問題：效能差

set global transaction isolation level serializable;
select @@global.transaction_isolation;

eg.兩方共用一個資料，前後插入資訊並提交：

A:

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql>insert into user values(7,'f',100);
Query OK, 1 row affected (0.01 sec)
mysql> commit;
Query OK, 0 rows affected (0.01 sec)

B：

mysql> select * from user;                        \*看到了A更新後的表格
mysql>insert into user values(8,'oo',1000);



                                                            \命令執行時卡住

操作序列化，兩端不可同時進行寫入操作，進入等待狀態
A提交資料
A:

commit;

B立刻彈出結果
B：

Query OK, 1 row affected (7.76 sec)                                \*等待commit後

若超時則會彈出。此時B端需要重新插入：

ERROR 1064（4200）：you have an error in your SQL
syntax; check the manual that corresponds to your mysql server version fo the righr syntax to use near 'mysql'     \*超時

mysql>insert into user values(8,'oo',1000);
Query OK, 1 row affected (0.01 sec)                                

• 當表user被另外一個實務操作時，其它事務中的寫作錯做，是不可以進行的。
• 進入排隊狀態（序列化），直到A事務結束之後，B的寫入操作才能被執行（但需要再沒有等待超時的情況下）

效能對比	>	隔離級別高,	效能減弱（問題多）
READ-UNCIMMUTTED	READ-COMMITTED	REPEATABLE-READ	SWRIALIZABLE

課程來自一天學會MySQL資料庫？