MySQL面試考點整理

一、内外連接配接

内連接配接: 隻連接配接比對的行
左外連接配接: 包含左邊表的全部行（不管右邊的表中是否存在與它們比對的行），以及右邊表中全部比對的行
右外連接配接: 包含右邊表的全部行（不管左邊的表中是否存在與它們比對的行），以及左邊表中全部比對的行
全外連接配接: 包含左、右兩個表的全部行，不管另外一邊的表中是否存在與它們比對的行。
交叉連接配接: 生成笛卡爾積－它不使用任何比對或者選取條件，而是直接将一個資料源中的每個行與另一個資料源的每個行都一一比對

drop table users;
drop table address;

create table users(
    id int primary key auto_increment,
    `name` varchar(30)
);

create table address(
    id int primary key auto_increment,
    uid int,
    city varchar(30)
 #   foreign key(uid) references users(id)
);

insert into users (`name`) values("張三");
insert into users (`name`) values("李四");
insert into users (`name`) values("王五");

insert into address (`uid`,`city`) values(1,"上海");
insert into address (`uid`,`city`) values(2,"北京");
insert into address (`uid`,`city`) values(5,"武漢");

select `name`,city from users inner join address on users.id=address.uid;#内連接配接
select `name`,city from users left join address on users.id=address.uid;#左外連接配接
select `name`,city from users right join address on users.id=address.uid;#右外連接配接
select `name`,city from users full join address;#全連接配接
select `name`,city from users cross join address;#交叉連接配接，類似于笛卡兒積

1.内連接配接(inner join)——左右兩邊都比對才連接配接

select `name`,city from users inner join address on users.id=address.uid;

2.左外連接配接(left join)——左邊的表全部都儲存下來，右邊可以為空

select `name`,city from users left join address on users.id=address.uid;

3.右外連接配接(right join)——右邊的表全部都儲存下來，左邊可以為空

select `name`,city from users right join address on users.id=address.uid;

4.全連接配接與交叉連接配接——兩個都是與笛卡兒積類似，都兩兩相連

二、資料庫事務的四大特性(ACID)

1.原子性（Atomicity）

原子性是指事務是一個不可分割的工作機關，事務中的操作要麼都發生，要麼都不發生。

2.一緻性（Consistency）

如果事務執行之前資料庫是一個完整的狀态，那麼事務結束後，無論事務是否執行成功，資料庫仍然是一個完整的狀态。

資料庫的完整狀态：當一個資料庫中的所有的資料都符合資料庫中所定義的所有限制，此時可以稱資料庫是一個完整的狀态。

拿轉賬來說，假設使用者A和使用者B兩者的錢加起來一共是5000，那麼不管A和B之間如何轉賬，轉幾次賬，事務結束後兩個使用者的錢相加起來應該還得是5000，這就是事務的一緻性。

3.隔離型（Isolation）

多個使用者并發通路資料庫時，一個使用者的事務不能被其他使用者的事務所幹擾，多個并發事務之間資料要互相隔離。

4.持久性（Durability）

指一個事務一旦被送出，他對資料庫的影響是永久性的。

三、事務級别

1.不同僚務級别可能帶來的問題

髒讀

髒資料所指的就是未送出的資料。也就是說，一個事務正在對一條記錄做修改，在這個事務完成并送出之前，這條資料是處于待定狀态的（可能送出也可能復原），這時，第二個事務來讀取這條沒有送出的資料，并據此做進一步的處理，就會産生未送出的資料依賴關系。這種現象被稱為髒讀。

salary=1000

T1	T2
read(salary)——1000
write(salary+2000)——3000
read(salary)——3000
rollback

不可重複讀

一個事務先後讀取同一條記錄，而事務在兩次讀取之間該資料被其它事務所修改，則兩次讀取的資料不同，我們稱之為不可重複讀。





commit

幻讀

一個事務按相同的查詢條件重新讀取以前檢索過的資料，卻發現其他事務插入了滿足其查詢條件的新資料，這種現象就稱為幻讀。


select * from users where age between 10 and 30;
insert into users( `name` , `age` ) values("Bob",15);

髒讀與不可重複讀的差別
1. 髒讀是讀取了未送出的資料。一個事務正在對一條記錄做修改，在這個事務完成并送出之前，這條資料是處于待定狀态的（可能送出也可能復原），這時，第二個事務來讀取這條沒有送出的資料，并據此做進一步的處理，就會産生未送出的資料依賴關系。這種現象被稱為髒讀。
2. 不可重複讀是在兩次讀取之間有其它事務對資料做了操作。
不可重複讀與幻讀的差別
1. 不可重複讀的重點是修改，同樣的條件, 你讀取過的資料, 再次讀取出來發現值不一樣了。
2. 幻讀的重點在于新增或者删除 (資料條數變化)。同樣的條件, 第1次和第2次讀出來的記錄數不一樣。

2.事務的隔離級别

√: 可能出現 ×: 不會出現


Read uncommitted(讀未送出)	√
Read committed(讀送出)	×
Repeatable read(重複讀)
Serializable(序列化)

四、三大範式

第一範式（1NF）：資料表中的每一列（每個字段）必須是不可拆分的最小單元，也就是確定每一列的原子性；
第二範式在第一範式的基礎上更進一層，第二範式需要確定資料庫表中每一列都和主鍵相關，而不能隻與主鍵的某一部分相關（主要針對聯合主鍵而言）。也就是說在一個資料庫表中，一個表中隻能儲存一種資料，不可以把多種資料儲存在同一張資料庫表中。
第三範式需要確定資料不能存在傳遞關系，即每個屬性都跟主鍵有直接關系而不是間接關系。

簡而言之：

第1範式：每個表中都有1列，并且該列是不可拆分的最小單元

第2範式：1張表隻描述一件事情。如包含使用者-訂單-商品資訊表，應該拆分為3個表。

第3範式：用外鍵做表的關聯。比如：

Student表（學号，姓名，年齡，性别，所在院校，院校位址，院校電話）
就存在上述關系：學号--> 所在院校 --> (院校位址，院校電話)
這樣的表結構，我們應該拆開來：（學号，姓名，年齡，性别，所在院校）--（所在院校，院校位址，院校電話）

參考連結：
https://www.cnblogs.com/1906859953Lucas/p/8299959.html

五、五大限制

1.primary KEY:設定主鍵限制；

2.UNIQUE：設定唯一性限制，不能有重複值；

3.DEFAULT 預設值限制，height DOUBLE(3,2)DEFAULT 1.2 # height不輸入是預設為1,2

4.NOT NULL：設定非空限制，該字段不能為空；

5.FOREIGN key :設定外鍵限制。

六、常考文法

假設orders表中有如下資料：

{

cust_id: "abc123",

ord_date: ISODate("2012-11-02T17:04:11.102Z"),

status: 'A',

price: 50,

items: [ { sku: "xxx", qty: 25, price: 1 }, { sku: "yyy", qty: 25, price: 1 } ]

}

1.統計orders表中記錄數

db.orders.aggregate( [
   {
     $group: {
        _id: null,
        count: { $sum: 1 }
     }
   }
] )

類似mysql：

SELECT COUNT(*) AS count   FROM orders

2.計算orders表中所有記錄的price之和

db.orders.aggregate( [
   {
     $group: {
        _id: null,
        total: { $sum: "$price" }
     }
   }
] )

SELECT SUM(price) AS total  FROM orders

3.計算cust_id相同的所有記錄的price之和

db.orders.aggregate( [
   {
     $group: {
        _id: "$cust_id",
        total: { $sum: "$price" }
     }
   }
] )

SELECT cust_id,SUM(price) AS total FROM orders GROUP BY cust_id

4.對(cust_id,ord_date)進行分組，并計算每組裡面的price之和

db.orders.aggregate( [
   {
     $group: {
        _id: {
           cust_id: "$cust_id",
           ord_date: {
               month: { $month: "$ord_date" },
               day: { $dayOfMonth: "$ord_date" },
               year: { $year: "$ord_date"}
           }
        },
        total: { $sum: "$price" }
     }
   }
] )

SELECT cust_id,ord_date,SUM(price) AS total FROM orders GROUP BY cust_id,ord_date

5.當cust_id相同的記錄數大于1時，查詢出該cust_id及其對應的記錄數

db.orders.aggregate( [
   {
     $group: {
        _id: "$cust_id",
        count: { $sum: 1 }
     }
   },
   { $match: { count: { $gt: 1 } } }
] )

SELECT cust_id,count(*) FROM orders GROUP BY cust_id HAVING count(*) > 1

6.對(cust_id,ord_date)進行分組，并計算每組裡面的price之和,傳回price之和大于250時的cust_id,ord_date以及price之和

db.orders.aggregate( [
   {
     $group: {
        _id: {
           cust_id: "$cust_id",
           ord_date: {
               month: { $month: "$ord_date" },
               day: { $dayOfMonth: "$ord_date" },
               year: { $year: "$ord_date"}
           }
        },
        total: { $sum: "$price" }
     }
   },
   { $match: { total: { $gt: 250 } } }
] )

SELECT cust_id,ord_date,SUM(price) AS total FROM orders GROUP BY cust_id,ord_date HAVING total > 250

7.計算status='A',且cust_id相同的記錄的price之和

db.orders.aggregate( [
   { $match: { status: 'A' } },
   {
     $group: {
        _id: "$cust_id",
        total: { $sum: "$price" }
     }
   }
] )

SELECT cust_id,SUM(price) as total FROM orders WHERE status = 'A' GROUP BY cust_id

8.計算status='A',且cust_id相同的記錄的price之和,并且隻傳回price之和大于250的記錄

db.orders.aggregate( [
   { $match: { status: 'A' } },
   {
     $group: {
        _id: "$cust_id",
        total: { $sum: "$price" }
     }
   },
   { $match: { total: { $gt: 250 } } }
] )

SELECT cust_id,SUM(price) as total FROM orders WHERE status = 'A' GROUP BY cust_id HAVING total > 250

9.對于每個唯一的cust_id，将與orders相關聯的相應訂單項order_lineitem的qty字段進行總計

db.orders.aggregate( [
   { $unwind: "$items" },
   {
     $group: {
        _id: "$cust_id",
        qty: { $sum: "$items.qty" }
     }
   }
] )

SELECT cust_id,SUM(li.qty) as qty FROM orders o,order_lineitem li WHERE li.order_id = o.id GROUP BY cust_id

10.統計(cust_id,ord_date)分組的數量

db.orders.aggregate( [
   {
     $group: {
        _id: {
           cust_id: "$cust_id",
           ord_date: {
               month: { $month: "$ord_date" },
               day: { $dayOfMonth: "$ord_date" },
               year: { $year: "$ord_date"}
           }
        }
     }
   },
   {
     $group: {
        _id: null,
        count: { $sum: 1 }
     }
   }
] )

SELECT COUNT(*) FROM (SELECT cust_id, ord_date FROM orders GROUP BY cust_id, ord_date) as DerivedTable

https://www.cnblogs.com/ilikeballs/p/4341383.html https://blog.csdn.net/qq_33862644/article/details/79692652 https://www.cnblogs.com/zhoujie/p/mongo1.html