資料庫基礎，看完這篇就夠了！

2022 年 10 月 2 日
筆記
資料庫

轉載請註明出處❤️

作者：測試蔡坨坨

原文鏈接：caituotuo.top/747a74ea.html

你好，我是測試蔡坨坨。

對於測試同學來說，除了知道測試基礎知識外，還需要掌握一些測試基本技能，主要有Linux、資料庫、電腦網路等，在此之前我們已經討論過Linux基礎知識以及在實際工作中的應用，可參考往期文章「學會 Linux，看完這篇就行了！」。

今天，我們就來聊一聊資料庫，資料庫是大學本科電腦系核心課程之一，其重要性不言而喻。

資料庫在面試中基本屬於必考內容，最多的就是手寫SQL或口述SQL，面試官會給你出一個場景，比如班級、分數、課程之類的，一般考察表查詢語句居多，例如多表查詢、連接查詢、子查詢等。所以，當你準備轉行踏入IT行業的時候，就首先需要掌握資料庫。

本文主要科普作為一枚測試應該知道的資料庫理論基礎知識，知道這些不僅可以在面試時加分，而且可以加深你對資料庫的理解，而不是僅僅停留在只會寫幾個SQL上面。

測試人員對於資料庫理論知識的學習，肯定不需要像開發那麼深入，但是一些基本的內容需要知道並掌握，簡單來說，資料庫基礎，看完接下來的文章並搞明白就完全夠用了。

當然，關於資料庫進階知識，比如資料庫索引、事務、資料庫三大範式、資料庫調優、存儲過程等內容也會在後續的文章中與大家討論。

作為測試，資料庫在日常工作中的權重佔比還是比較大的，主要有以下幾個應用場景：

項目部署及部署完後數據的準備

開發配置好環境，但是沒有連接資料庫，就需要我們自己新建資料庫並連接。

已經建好資料庫，但是沒有創建數據表，就需要我們自己創建數據表。

已經創建好資料庫和數據表，但是數據表中沒有數據，就需要我們自己添加數據。

數據表中有數據，但是數據量不夠，開發只提供一兩條樣例數據，測試就需要大量造數據。

項目的後台管理沒有註冊功能，就需要我們自己手動向數據表中插入用戶名和密碼。
在前端頁面增刪改查，查看資料庫是否做了相應更新，核對數據存儲的準確性

舉栗1：在CRM項目中，新建客戶以後，在資料庫的表中查看是否與新建的客戶資訊一致。

舉栗2：支付交易產生的訂單可以從資料庫中查看訂單是否真實存儲，數據資訊是否一致。
對數據直接操作來滿足測試用例所需的極限場景

比如有些場景像CRM項目中的新建客戶功能，我們只是要驗證一個輸入框的邊界值，但是卻需要在前端頁面一直新建，一直提交保存，每次新建都需要填寫暫時不需要測試的必填項，太麻煩，就可以直接去修改數據表中對應的欄位值。
性能測試或自動化測試通過腳本產生大量數據時，查看資料庫是否批量有效存儲
通過操作資料庫優化測試用例，提升測試效率
前端輸入框欄位報錯時，可能是資料庫參數類型設置有誤或欄位長度不夠，就可以打開資料庫查看欄位參數類型和長度是否正確
性能測試，通過優化SQL語句或表結構來提高系統的性能，例如慢查詢等
造數據場景，構造某些用例的前置條件

舉栗1：統計年盈利額，需要1~12月都需要數據，不可能一個需求測一年吧，就可以在資料庫中直接插入數據。

舉栗2：手機號註冊時，通過改資料庫表欄位非唯一狀態來重複使用一個手機號，進行反覆註冊。

舉栗3：通過修改金額、價格等用來做支付測試，比如原本需要100￥，就可以通過修改數據為0.01￥。

舉栗4：會員積分，就可以在資料庫直接修改積分，看是否達到會員。
理解如何通過介面操作資料庫
做測試結構分析時，可以通過資料庫搞清楚數據流向，哪個表放哪個欄位什麼時候在哪裡展示

and so on ……

以上，僅列舉了一些日常工作中比較常見的場景，供大家參考。

同時歡迎評論區補充哦~

緊接著，就來介紹一下資料庫以及常用的SQL語句。

認識資料庫

什麼是資料庫？

資料庫的英文單詞：Database ，簡稱DB。

資料庫方向的崗位叫DBA（Database Administrat），也就是資料庫管理員，專門和資料庫打交道的，屬於運維工程師的一個分支，主要負責業務資料庫從設計、測試到部署交付的全生命周期管理。

資料庫實際上就是一個文件集合，是一個存儲數據的倉庫，本質就是一個文件系統，資料庫是按照特定的格式把數據存儲起來，用戶可以對存儲的數據進行增刪改查操作。

簡單概括，資料庫就是用於存儲和管理數據的倉庫。

具體來說，就是前端頁面用戶輸入的數據通過介面傳給後端，然後存儲到資料庫中，同時也支援從資料庫中取數據傳給前端頁面做一個展示。

資料庫的兩大分類

關係型資料庫：是建立在關係模型基礎上的資料庫，比如MySQL、Oracle、SQL Server、DB2、PostgreSQL等，還有一些國產的資料庫比如達夢資料庫、神通資料庫、人大金倉資料庫等。
非關係型資料庫（NO SQL）：通常指數據之間無關係的資料庫，比如MongoDB、Redis，以鍵值對的方式存儲。

新手入門該學習哪個資料庫？

上面說了這麼多資料庫，而目前企業中使用最多的就是MySQL和Oracle資料庫，後者因為是收費的，所以互聯網公司尤其是中小型企業使用最多的就是MySQL資料庫，資料庫的學習其實和程式語言一樣，當你熟練掌握其中一種時，其他的也就分分鐘上手了。

因此，推薦初學者從MySQL資料庫開始學習。

MySQL簡介

MySQL是一個關係型資料庫管理系統，由瑞典MySQL AB公司開發。
世界上最流行的幾款資料庫之一。
優點：是一款輕量級資料庫、免費、開源、適用於中大型網站。
MySQL默認埠號：3306。

資料庫伺服器、資料庫和表的關係

所謂安裝資料庫伺服器，只是在機器上裝一個資料庫管理系統（比如： MySQL、Oracle、SQL Server），用來管理多個資料庫，一般開發人員會針對每一個應用創建一個資料庫。
為保存應用中實體的數據，一般會在資料庫創建多個表，以保存程式中實體的數據。

Xshell、Xftp、Navicat

PS：Xshell或Xftp連接Linux伺服器默認埠：22

SQL介紹

Structured Query Language：結構化查詢語言。

SQL是專門為資料庫而建立的操作命令集，是一種功能齊全的資料庫語言。在使用它時，只需要發出「做什麼」命令，「怎麼做」是不用使用者考慮的。

SQL語法特點

不區分大小寫。
關鍵字、欄位名、表名需要用空格或逗號隔開。
每一個SQL語句是用分號結尾。
語句可以寫一行也可以分開寫多行。

如何自學資料庫

以上說了這麼多資料庫如何重要以及資料庫是什麼，那麼應該如何自學呢？接下來就是乾貨來啦！

幾個資料庫相關的影片教程和兩門價值幾百的極客時間語音課，關注公粽號：測試蔡坨坨，回復關鍵詞：資料庫，即可獲取。學完這些基本的SQL語句理論知識就沒什麼問題了，接下來時間就是反覆練習，達到能夠熟練編寫的程度就行了。

在哪裡練習？想要練習資料庫需要首先需要有數據的環境，有以下兩種方法：

本地安裝一個資料庫，例如MySQL
推薦在線練習SQL網站：www.nowcoder.com/ta/sql

可以在線練習SQL實戰，會根據你的輸入實時判斷對錯，不會的話還可以參考別人寫的SQL語句。

資料庫常用操作命令

1. MySQL登錄

mysql -uroot -p密碼

mysql -uroot -p（回車）
Enter password: （輸入密碼）

mysql -hip -P埠 -uroot -p

mysql --host=ip --user=root --password=密碼

2. MySQL退出

exit
quit

3. 創建、查看、刪除、使用資料庫

創建資料庫命令
- 創建資料庫：
  
  格式：create database [資料庫名稱];
  
  例如：
```
create database caituotuo;
```
- 創建資料庫，並指定字符集：
  
  格式：create database [資料庫名稱] character set [字符集名];
  
  例如：
```
create database caituotuo character set utf8;
```
- 創建資料庫，並指定字符集、排序規則：
  
  格式：create database [資料庫名稱] character set [字符集名] collate [排序規則];
  
  例如：
```
create database caituotuo character set utf8 collate utf8_general_ci;
```
查看資料庫命令
- 顯示所有資料庫：
```
show databases;
```
- 模糊查詢資料庫：
```
show databases like '%tuotu%';
```
- 查看查詢某個資料庫的創建語句、字符集：
  
  格式：show create database [資料庫名稱];
  
  例如：
```
show create database caituotuo;
```
刪除資料庫命令

格式：drop database [資料庫名稱];

例如：
```
drop database caituotuo;
```
選中某個資料庫

格式：use [資料庫名稱];

例如：
```
use caituotuo;
```
PS：這個命令可以不加分號。

4. 創建、查看、刪除、修改數據表

數據在資料庫中的存儲方式

表中列的數據類型——數值型

類型	大小	用途
TINYINT	1位元組	小整數值
SMALLINT	2位元組	大整數值
MEDIUMINT	3位元組	大整數值
INT或INTEGER	4位元組	大整數值
BIGINT	8位元組	極大整數值
FLOAT	4位元組	單精度浮點數值
DOUBLE	8位元組	雙精度浮點數值
DECIMAL	對於DECIMAL(M,D)，如果M>D則為M+2，否則為D+2	小數值

有符號和無符號（UNSIGNED）

在電腦中，可以區分正負的類型，稱為有符號類型。

無正負的類型，稱為無符號類型。

簡單的理解為就是，有符號值可以表示負數、0、正數，無符號值只能為0或者正數。

FLOAT、DOUBLE、DECIMAL

FLOAT(10,2)：總長度為10，小數點後有2位。

DOUBLE和DECIMAL也類似。

超出範圍會四捨五入。

表中列的數據類型——字元型

CHAR：定長字元串。CHAR(4) — ' d'
VARCHAR：變長字元串。VARCHAR(4) — 'd'
CHAR的查詢效率要高於VARCHAR。

表中列的數據類型——日期型

類型
DATE
TIME
YEAR
DATETIME
TIMESTAMP

TIMESTAMP和DATETIME的異同：

相同點：兩者都可以用來表示YYYY-MM-DD HH:MM:SS類型的日期。
不同點：
- 兩者的存儲方式不一樣：
  - 對於TIMESTAMP，它把客戶端插入的時間從當前時區轉化為UTC（世界標準時間）進行存儲。查詢時，又將其轉化為客戶端當前時區進行返回。（PS：中國時區為+8區）
  - 而對於DATETIME，不做任何改變，基本上是原樣輸入和輸出。
- 兩者所能存儲的時間範圍不一樣：
  - TIMESTAMP所能存儲的時間範圍為：’1970-01-01 00:00:01.000000’到’2038-01-19 03:14:07.999999′
  - DATETIME所能存儲的時間範圍為：’1000-01-01 00:00:00.000000’到’9999-12-31 23:59:59.999999′

數據表操作命令

創建表

創建表基礎命令
```
-- 格式：
CREATE TABLE [表名] ([列名 1 ] [列類型],[列名 2 ] [列類型],[列名 3 ] [列類型] );
-- 例如：
CREATE TABLE STU (ID INT,NAME VARCHAR ( 20 );AGE INT );
```
注意：
- 創建表前，要先選中某個資料庫（use [資料庫名稱]）；
- 列和列之間用逗號隔開，列內用空格隔開；
- 創建表時，要根據需保存的數據創建相應的列，並根據要存儲數據的類型定義相應的列類型。

約束條件

約束條件就是給列加一些約束，使該欄位存儲的值更加符合我們的預期。

約束條件	含義
UNSIGNED	無符號，值從0開始，無負數
ZEROFILL	零填充，當數據的顯示長度不夠的時候可以使用填補0的效果填充至指定長度，欄位會自動添加UNSIGNED
NOT NULL	非空約束，表示該欄位的值不能為空
DEFAULT	表示如果插入數據時沒有給該欄位賦值，那麼就使用默認值
PRIMARY KEY	主鍵約束，表示唯一標識，不能為空，且一個表只能有一個主鍵
AUTO_INCREMENT	自增長，只能用於數值列，默認起始值從1開始，每次增長1
UNITQUE KEY	唯一值，表示該欄位下的值不能重複，可以為空，可以有多個
COMMENT	描述

注意：

如果一列同時有UNSIGNED、ZEROFILL、NOT NULL這幾個約束，UNSIGNED、ZEROFILL必須在NOT NULL前面，否則會報錯。

例子：

create table user_info_tab(
user_id int not null auto_increment,
user_name char(10),
password varchar(10),
user_nick varchar(10),
card_num bigint,
primary key(user_id)
);

查看錶

顯示當前資料庫中所有表的名字

格式：
```
show tables;
```
顯示某張表每一列的屬性（列名、數據類型、約束）

格式：desc [數據表名稱];

例如：
```
desc user_info_tab;
```

刪除表

格式：drop table [數據表名稱];

例如：

drop table hesheng;

修改表

向數據表中添加一列

格式：ALTER TABLE [數據表名稱] ADD [列名] [列的數據格式] [約束];

例如：
```
ALTER TABLE user_info_tab ADD phone VARCHAR(11);
```
PS：
- 默認自動添加到數據表欄位的末尾；
- 如果要加在第一列在最後加個FIRST；
- 如果要加在某一列的後面，在最後面加個AFTER某一列列名。
刪除數據表的某一列

格式：ALTER TABLE [數據表名稱] DROP [列名];
修改列的類型和名稱

ALTER TABLE [表名] MODIFY [列名] [數據格式];（列名不變，其他要變）

ALTER TABLE [表名] CHANGE [舊列名] [新列名] [數據格式];（列名也要改變）

5.數據表的增刪改查

增（insert）

-- 插入數據
INSERT INTO user_info ( user_id, user_name, PASSWORD, user_nick, card_num )
VALUES
	( 1, 'zhangsan', 'abc123', 'zhangsanfeng', 124567894651329785 ),
	( 2, 'lisi', '122bbb', 'limochou', 124567894651324567 ),
	( 3, 'wangwu', '123aaa', 'wangbaiwan', 214567894651324567 ),
	( 4, 'liuqi', '12aaa', 'liuchuanfeng', 214563356651324567 ),
	( 5, 'zhangliu', '12aaa', 'zhangwuji', 214563356658966567 );

刪（delete）

語法：

delete from 表名 where 條件
delete語句不能刪除某一列的值。（可以使用 update 表名 set username = “” where userid = 1）
使用delete語句僅刪除符合where條件的行的數據，不刪除表中其他行和表本身。
truncate user_info_table（直接把數據清空掉）

drop和delete的區別：

drop是刪除資料庫、數據表、數據表中的某一列。
delete是刪除某一行數據。

改（update）

語法：

update [表名] set [列名]=[新值] where [列名]=[某值];
update語法可以新增、更新原有錶行中的各列。
set子句指示要修改哪些列和要給予哪些值。
where子句指定應更新哪些行。如果沒有where子句，則更新所有的行。

update user_info set username = "poopoo" where userid = 1;

查（select）

文末練習題中會給出查詢語句相關示例。

6. 數據表的排序、聚合命令、分組

排序（order by）

使用order by子句，對查詢結果進行排序。
order by 指定排序的列 asc（升序）/desc（降序）。
order by 子句一般位於select語句的結尾。

SELECT product_name,weight FROM products_info ORDER BY weight DESC;

聚合命令

distinct：對某一列數據去重。

語句：select distinct 列名 from 表名; — 顯示此列不重複的數據
count：統計總行數。
- count(*)：包括所有列，返回表中的總行數，在統計結果的時候，不會忽略值為Null的行數。
- count(1)：包括所有列，1表示一個固定值，沒有實際含義，在統計結果的時候，不會忽略列值為Null的行數，和count(*)的區別是執行效率不同。
- count(列名)：只包括列名指定列，返回指定列的行數，在統計結果的時候，不統計列值為Null，即列值為Null的行數不統計在內。
- count(distinct 列名)：返回指定列的不重複的行數，在統計結果的時候，會忽略列值為NULL的行數（不包括空字元和0），即列值為NULL的行數不統計在內。
- count(*)、count(1)、count(列名)執行效率比較：
  - 如果列為主鍵，count(列名)優於count(1)
  - 如果列不為主鍵，count(1)優於count(列名)
  - 如果表中存在主鍵，count(主鍵列名)效率最優
  - 如果表中只有一列，則count(*)效率最優
  - 如果表中有多列，且不存在主鍵，則count(1)效率優於count(*)
MAX：最大值
MIN：最小值
AVG：平均值

SUM：求和

select max(列名) from 表名;
select min(列名) from 表名;
select avg(列名) from 表名;
select sum(列名) from 表名;
- 也可以跟where子句

limit

語法：
- select * from 表名 limit m,n;
- 其中m是指從哪行開始，m從0取值，0表示第一行。
- n是指從第m+1條開始，取n條。
- select * from 表名 limit 0,2（從第一行開始，顯示兩行結果）
- 如果只給定一個參數，它表示返回最大的行數目：
  
  select * from table limit 5;查詢前5行
- limit n 等價於 limit 0,n

分組

語法：GROUP BY

使用group by子句對列進行分組。
還可以使用having子句過濾，having通常跟在group by後，它作用於組。
不加having過濾：select 列名,聚合函數 from 表名 where 子句 group by 列名;
加上having過濾：select 列名,聚合函數 from 表名 where 子句 group by 列名 having 聚合函數過濾條件;
注意：使用group by後只能展示分組的列名+聚合函數結果，因為其餘列已經基於分組這一列合併。

select sum(price), count(user_id), product_id from order_info_table group by product_id having count(user_id) > 2;

7. 數據表的連接查詢、子查詢

兩張表連接查詢

INNER JOIN（內連接）：獲取兩個表中欄位匹配關係的行的所有資訊。

語法：SELECT * FROM [表名] a INNER JOIN [表名] b ON a.[列名] = b.[列名];

例如：

SELECT * FROM user_info_table a INNER JOIN order_info_table b ON a.user_id = b.user_id;
SELECT * FROM user_info_table a INNER JOIN order_info_table b ON a.user_id = b.user_id WHERE b.user_id IS NULL;

LEFT JOIN（左連接）：以左表為基準，獲取左表所有行的資訊，即使右表沒有對應匹配的行的資訊。右表沒有匹配的部分用NULL代替。

語法：SELECT * FROM [表名] a LEFT JOIN [表名] b ON a.[列名] = b.[列名];

SELECT * FROM products_info a LEFT JOIN suppliers_info b ON a.supplier_id = b.supplier_id;

RIGHT JOIN（右連接）：與左連接相反，以右表為基準，用於獲取右表所有記錄，及時左表沒有對應匹配的行的所有資訊，左表沒有匹配的部分用NULL代替。

語法：SELECT * FROM [表名] a RIGHT JOIN [表名] b ON a.[列名] = b.[列名];

SELECT * FROM products_info a RIGHT JOIN suppliers_info b ON a.supplier_id = b.supplier_id;

子查詢（嵌套查詢）

嵌套在其他查詢中的查詢。
語句：select 列名1 from 表1 where 列名2 in (select 列名2 from 表2 where 列名3 = 某某某);
注意：一般在子查詢中，程式先運行嵌套在最內層的語句，再運行外層。因此在寫子查詢語句時，可以先測試一下內層的子查詢語句是否輸出了想要的內容，再一層一層往外測試，增加子查詢的正確率。

其他注意事項：資料庫名和表名在Windows中是大小寫不敏感的，但是在大多數類型的UNIX系統中大小寫是敏感的。

筆試面試題

一、創建如下要求的表格，並完成相應的題目。

-- 創建表
CREATE TABLE user_info (
	user_id INT NOT NULL AUTO_INCREMENT,
	user_name CHAR ( 10 ),
	password VARCHAR ( 10 ),
	user_nick VARCHAR ( 10 ),
	card_num BIGINT,
	PRIMARY KEY ( user_id ) 
);

-- 插入數據
INSERT INTO user_info ( user_id, user_name, PASSWORD, user_nick, card_num )
VALUES
	( 1, 'zhangsan', 'abc123', 'zhangsanfeng', 124567894651329785 ),
	( 2, 'lisi', '122bbb', 'limochou', 124567894651324567 ),
	( 3, 'wangwu', '123aaa', 'wangbaiwan', 214567894651324567 ),
	( 4, 'liuqi', '12aaa', 'liuchuanfeng', 214563356651324567 ),
	( 5, 'zhangliu', '12aaa', 'zhangwuji', 214563356658966567 );

-- user_nick長度不夠，修改user_nick的長度再重新插入數據
ALTER TABLE user_info MODIFY user_nick VARCHAR ( 20 );

-- 創建訂單表
CREATE TABLE order_info (
	order_id INT UNSIGNED ZEROFILL NOT NULL,
	price DECIMAL ( 10, 2 ) NOT NULL,
	order_status VARCHAR ( 30 ) NOT NULL,
	product_id INT NOT NULL,
	created datetime DEFAULT "2019-01-01 00:00:00",
	user_id INT NOT NULL,
PRIMARY KEY ( order_id ) 
);

-- 插入數據
INSERT INTO order_info
VALUES
	( 1, 4.99, 'pay', 1001, '2019-09-25 10:25:26', 1 ),
	( 2, 9.99, 'nopay', 1002, '2019-09-26 10:25:26', 1 ),
	( 3, 4.99, 'pay', 1001, '2019-09-25 10:25:26', 2 ),
	( 4, 9.99, 'nopay', 1002, '2019-09-24 10:25:26', 2 ),
	( 5, 19.99, 'pay', 1003, '2019-09-26 10:25:26', 2 ),
	( 6, 4.99, 'pay', 1001, '2019-09-25 10:25:26', 3 ),
	( 7, 4.99, 'pay', 1001, '2019-09-25 10:25:26', 4 ),
	( 8, 9.99, 'pay', 1002, '2019-09-25 10:25:26', 4 ),
	( 9, 19.99, 'pay', 1003, '2019-09-26 10:25:26', 4 ),
	( 10, 29.99, 'pay', 1002, '2019-09-26 10:25:26', 6 );

-- where子句小練習
-- 1. 滿足價格大於等於9的所有資訊
SELECT * FROM order_info WHERE price >= 9;

-- 2. 查找滿足product_id在1002和1003之間的
SELECT * FROM order_info WHERE product_id BETWEEN 1002 AND 1003;

-- 3. 查找user_id在1、3、5這三個數內的資訊
SELECT * FROM order_info WHERE user_id IN (1,3,5);

-- 4. 查找訂單狀態是已支付的資訊
SELECT * FROM order_info WHERE order_status = 'pay';

-- 5. 查找用戶名類似於已li開頭的資訊
SELECT * FROM user_info WHERE user_name LIKE 'li%';

-- 6. 查找用戶名中第二個字母是h的資訊
SELECT * FROM user_info WHERE user_name LIKE '_h%';

-- 7. 查找用戶名中第二個字母不是h的資訊
SELECT * FROM user_info WHERE user_name NOT LIKE '_h%';

-- 8. 查找用戶名中最後一個字母以i結尾的資訊
SELECT * FROM user_info WHERE user_name LIKE '%i';

-- 9. 查找價格大於8，並且訂單狀態是已支付的所有資訊
SELECT * FROM order_info WHERE price > 8 AND order_status = 'pay';

-- 10.查找用戶表中user_nick為null的資訊
SELECT * FROM user_info WHERE user_nick IS NULL;

-- 11.查找用戶表中user_nick為 not null的資訊
SELECT * FROM user_info WHERE user_nick IS NOT NULL;

-- 聚合函數練習
-- 1. 查找訂單表中最大的價格，查找訂單表中最小的價格
SELECT MAX(price),MIN(price) FROM order_info;

-- 2. 查找訂單表中user_id=2的最小价格
SELECT MIN(price) FROM order_info WHERE user_id = 2;

-- 3. 分別列出訂單表中user_id=2的最小价格和最大價格
SELECT MIN(price),MAX(price) FROM order_info WHERE user_id = 2;

-- 4. 分別列出訂單表中user_id=2的最小价格和最大價格，並把最小价格的展示結果的列名改為"min_price"
SELECT MIN(price) AS min_price,MAX(price) FROM order_info WHERE user_id = 2;

-- 5. 求訂單表的價格的平均值，求訂單表中user_id=2的價格的平均值
SELECT AVG(price) FROM order_info;
SELECT AVG(price) FROM order_info WHERE user_id = 2;

-- 6. 分別列出訂單表中user_id=2的價格的平均值、最小值、最大值
SELECT AVG(price),MIN(price),MAX(price) FROM order_info WHERE user_id = 2;

-- 7. 求訂單表中user_id=1的價格的總和
SELECT SUM(price) FROM order_info WHERE user_id = 1;

-- 8. 求訂單表中user_id=1或者user_id=3的價格總和
SELECT SUM(price) FROM order_info WHERE user_id = 1 OR user_id = 3;

-- 分組練習
-- 1.首先篩選狀態為已支付的訂單，然後按照user_id分組，分組後每一組對支付金額進行求和，最終展示user_id和對應組求和金額
SELECT user_id,SUM(price) FROM order_info WHERE order_status = 'pay' GROUP BY user_id;

-- 2.首先篩選狀態為支付的訂單，然後按照user_id分組，分組後每一組對支付金額進行求和，再過濾求和金額大於10的，最終展示user_id和對應組的求和金額
SELECT user_id,SUM(price) FROM order_info WHERE order_status = 'pay' GROUP BY user_id HAVING SUM(price) > 10;

-- 數據表連接查詢和子查詢練習
-- 1.查詢訂單表中的價格大於10元的用戶的昵稱（小提示：用戶昵稱在用戶表中，訂單價格在訂單表中）
SELECT a.user_nick FROM user_info a INNER JOIN order_info b ON a.user_id = b.user_id WHERE b.price > 10;
SELECT user_nick FROM user_info WHERE user_id IN (SELECT user_id FROM order_info WHERE price > 10);

-- 2.查詢用戶名以l開頭的用戶買過的所有訂單id和對應價格（小提示：訂單id和對應價格在訂單表中，用戶名在用戶表中）
SELECT o.order_id,o.price FROM order_info o WHERE o.user_id IN (SELECT user_id FROM user_info u WHERE u.user_name LIKE 'l%');

二、創建如下要求的表格，並完成相應的題目。

-- 1.按照表定義創建商品表+供應商表
-- 2.按照表數據插入所有數據

-- 創建商品表
CREATE TABLE products_info (
	product_id INT UNSIGNED ZEROFILL NOT NULL,
	product_name VARCHAR ( 30 ) DEFAULT 'water',
	classification VARCHAR ( 30 ),
	weight FLOAT ( 10, 2 ),
	supplier_id INT,
PRIMARY KEY ( product_id ) 
);

-- 向商品表插入數據
INSERT INTO products_info
VALUES
	( 1001, 'water', 'Daily_Necessities', 0.55, 1 ),
	( 1002, 'soap', 'Daily_Necessities', 0.15, 2 ),
	( 1003, 'paper', 'Daily_Necessities', 0.12, 3 ),
	( 1004, 'pingpangqiu', 'sports', 0.05, 4 ),
	( 1005, 'yumaoqiu', 'sports', 0.04, 6 );

-- 創建供應商表
CREATE TABLE suppliers_info (
	supplier_id INT NOT NULL AUTO_INCREMENT,
	supplier_name VARCHAR ( 30 ),
	Registration_number INT,
	contacts VARCHAR ( 30 ),
	contacts_phone_num CHAR ( 11 ),
PRIMARY KEY ( supplier_id ) 
);

-- 向供應商表插入數據
INSERT INTO suppliers_info
VALUES
	( 1, 'nongfushanquan', 1000001, 'mayun', '18026156362' ),
	( 2, 'jiajieshi', 1000002, 'liuqiangdong', '13826156363' ),
	( 3, 'qingfeng', 1000003, 'mahuateng', '18926156364' ),
	( 4, '361du', 1000004, 'renzhenfei', '18226156365' ),
	( 5, 'anta', 1000005, 'leijun', '18626156366' );

-- 3.修改供應商id為4的供應商名稱為『hongshuangxi』
UPDATE suppliers_info SET supplier_name = 'hongshuangxi' WHERE supplier_id = 4;

-- 4.查詢商品重量大於0.10的商品的名稱
SELECT product_name FROM products_info WHERE weight > 0.10;

-- 5.查詢商品名稱以字母p開頭的商品的所有資訊
SELECT * FROM products_info WHERE product_name like 'p%';

-- 6.查詢商品重量大於0.10,小於0.20的商品名稱
SELECT product_name FROM products_info WHERE weight > 0.10 AND weight < 0.20;

-- 7.按照商品分類統計各自的商品總個數，顯示每個分類和其對應的商品總個數
SELECT classification,COUNT(classification) FROM products_info GROUP BY classification;

-- 8.將所有商品的名稱按照商品重量由高到低顯示
SELECT product_name,weight FROM products_info ORDER BY weight DESC;

-- 9.顯示所有商品的資訊，在右邊顯示有供應商的商品對應的供應商資訊
SELECT * FROM products_info a LEFT JOIN suppliers_info b ON a.supplier_id = b.supplier_id;

-- 10.顯示重量大於等於0.15的商品的供應商的聯繫人和手機號
SELECT s.contacts,s.contacts_phone_num FROM suppliers_info s INNER JOIN products_info p ON s.supplier_id = p.supplier_id and p.weight >= 0.15;

Tags: 資料庫