Datawhale MySQL 訓練營 Task2 查詢語句

目錄

• MySQL 管理
  • MySQL 用戶管理 參考
  • 數據庫管理
• SQ查詢語句
  • 1. 導入示例數據庫，教程 MySQL導入示例數據庫
  • 2. 查詢語句 SELECT
  • 3. 篩選語句 WHERE ，過濾
  • 4. 分組語句 GROUP BY
  • 5. 排序語句 ORDER BY
  • 6. 函數
  • 作業
• 總結

MySQL 管理

MySQL版本 8.0.15

MySQL 用戶管理 參考

# root 用戶登錄
mysql -u root -p;# 添加用戶
use mysql;
create user test@localhost identified by 'test123';# 授予用戶全部權限GRANT ALL on *.* to 'test'@'localhost' WITH GRANT OPTION;

數據庫管理

• 創建數據庫

CREATE DATABASE db_example;

• 切換數據庫

use db_example;

• 刪除數據庫

drop database <數據庫名>;

SQ查詢語句

注：SQL 不區分大小寫，建議關鍵詞大寫增加可讀性；SQL 是 0-base 的語句

1. 導入示例數據庫，教程 MySQL導入示例數據庫

參考 https://www.yiibai.com/mysql/how-to-load-sample-database-into-mysql-database-server.html

2. 查詢語句 SELECT

• 從表 tablex 中查詢某一列或者某幾列：

SELECTcolumn0,column1,...clomunn
FROM table_x；

注： SELECT * FROM tabel 可以查詢所有列， * 為通配符

• 去重語句 DISTINCT

SELECT DISTINCT vend_id
FROM Products;

DISTINCT 列名 只返回 vend_id 這一列不同的值

• 前N個語句， LIMIT
  在 MySQL 中返回前 N 行

SELECT columns
FROM table
LIMIT N;

• 從第M行開始的N行數據, LIMIT N offset M

  MySQL 和 MariaDB 支持簡化版本的 LIMIT M,N 語句，逗號前面對應 OFFSET, 逗號后面對應 LIMIT

SELECT columns
FROM table
LIMIT N OFFSET M;

• CASE...END判斷語句

3. 篩選語句 WHERE ，過濾

SELECT prod_name, prod_price
FROM Products
WHERE prod_price = 3.49;

• 語句解釋: 篩選出 prod_price = 3.49 的prod_name
• 運算符
  

SELECT prod_name, prod_price
FROM Products
WHERE prod_price BETWEEN 5 AND 10;

BETWEEN 需要AND 連接上下限；

空值檢查： IS NULL 語句

• 操作符
  邏輯操作符： AND 連接兩個條件 / OR連接兩個條件； AND OR 一起用的時候 AND優先級更高，應該用（）對操作符分組，（）優先級更高

IN 操作符： 指定條件范圍,() 內的被篩選出來，與 OR 有同樣的作用

NOT 操作符： 否定后面的條件

• 通配符 用來匹配值的一部分的特殊字符； 可以和字面值組合成搜索模式

LIKE 謂詞

% 通配符：表示任何字符出現任何次數
_ 通配符： 匹配單個字符
[] 通配符： 指定字符集匹配一個字符

SELECT prod_name, prod_price
FROM Products
WHERE vend_id IN ( 'DLL01', 'BRS01' )

4. 分組語句 GROUP BY

• GROUP BY 語句根據一個或多個列對結果集進行分組
• 聚集函數：匯總表中的數據的函數:平均、最大、最小、計數、求和

示例： 統計 prod_price 的平均值保存為 avg_price

SELECT COUNT(*) AS num_items,
MIN(prod_price) AS price_min,
MAX(prod_price) AS price_max,
AVG(prod_price) AS price_avg
FROM Products;

• HAVING子句，類似 WHERE，但是WHERE過濾行但是 HAVING 過濾分組，HAVING 支持所有的WHERE的操作符，還支持聚集操作

5. 排序語句 ORDER BY

• 語句解釋: 根據 BY 對取出的列進行排序

SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY prod_price, prod_name;

ORDER BY 后面可以是非檢索的列，可以是其他列； ORDER BY 應該保證是 SELECT 語句中的最后一條子句(也必須放在 )； 使用選擇的列（這里的 prod_id, prod_price, prod_name）排序可以用它們的順序進行排列如 ORDER BY 2,3

• 正序、逆序
  默認正序，可以使用ASC關鍵字，一般用不上
  逆序使用 DESC 關鍵字；直接位于 DESC 前面的那一列將會按照逆序列排列

SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY prod_price DESC, prod_name;

6. 函數

時間函數： 跟具體的DBMS 有關
SQL 數值函數
![image](http://media.alearner.cn/PictureRepo/Note/blog20190227SQLFuncNum.PNG)
SQL字符串函數
![image](http://media.alearner.cn/PictureRepo/Note/blog20190227SQLFuncString.PNG)
另外 MySQL 還提供了豐富的函數，[查表](http://www.runoob.com/mysql/mysql-functions.html)

作業

1. 編寫一個 SQL 查詢，查找 email 表中所有重復的電子郵箱。
   根據以上輸入，你的查詢應返回以下結果：

-- 創建表
CREATE TABLE email (
ID INT NOT NULL PRIMARY KEY,
Email VARCHAR(255)
)-- 插入數據
INSERT INTO email VALUES('1','a@b.com');
INSERT INTO email VALUES('2','c@d.com');
INSERT INTO email VALUES('3','a@b.com');-- 查詢
SELECT Email
FROMemail
GROUP BYEmail
HAVING COUNT(*) > 1

結果：

1. 項目二：查找大國（難度：簡單）
   創建如下 World 表

+-----------------+------------+------------+--------------+---------------+
| name            | continent  | area       | population   | gdp           |
+-----------------+------------+------------+--------------+---------------+
| Afghanistan     | Asia       | 652230     | 25500100     | 20343000      |
| Albania         | Europe     | 28748      | 2831741      | 12960000      |
| Algeria         | Africa     | 2381741    | 37100000     | 188681000     |
| Andorra         | Europe     | 468        | 78115        | 3712000       |
| Angola          | Africa     | 1246700    | 20609294     | 100990000     |
+-----------------+------------+------------+--------------+---------------+

如果一個國家的面積超過300萬平方公里，或者(人口超過2500萬并且gdp超過2000萬)，那么這個國家就是大國家。
編寫一個SQL查詢，輸出表中所有大國家的名稱、人口和面積。
例如，根據上表，我們應該輸出:

+--------------+-------------+--------------+
| name         | population  | area         |
+--------------+-------------+--------------+
| Afghanistan  | 25500100    | 652230       |
| Algeria      | 37100000    | 2381741      |
+--------------+-------------+--------------+

CREATE TABLE World (
name VARCHAR(50) NOT NULL,
continent VARCHAR(50) NOT NULL,
area INT NOT NULL,
population INT NOT NULL,
gdp INT NOT NULL
);-- 插入數據
INSERT INTO World VALUES( 'Afghanistan', 'Asia',652230,25500100,20343000);
INSERT INTO World VALUES( 'Albania', 'Europe' ,28748,2831741,12960000);
INSERT INTO World VALUES( 'Algeria', 'Africa' ,2381741,37100000,188681000);
INSERT INTO World VALUES( 'Andorra' , 'Europe' ,468,78115,3712000);
INSERT INTO World VALUES( 'Angola' , 'Africa' ,1246700,20609294,100990000);-- 查詢SELECTname,population,area
FROMworld
WHERE area>3000000 OR (population > 2500000 AND gdp > 20000000)

結果：

總結

今天好好學習了一下 SQL 的查詢、篩選、分組、排序和函數， 總的來說比較簡單。
文中主要圖表和代碼參考 《SQL 必知必會》，人民郵電出版社出版，[美]Ben Forta 著，鐘鳴，劉曉霞譯。