SQL 基礎知識 |選擇語句 |資料庫管理

介紹

SELECT 語句是 SQL 中最常用的語句之一，用來選擇表格中的資料。本實驗室將學習 SELECT 以及如何將其應用到實際實踐中。

學習目標

• SELECT 基本文法
• 數學符號條件
• 和或
• 通配符
• 排序
• SQL內建函數與運算
• 子查詢與連接查詢

準備

開始之前，先下載相關資料表，並建立一個名為mysql_labex的資料庫（3個表：部門、員工、專案）。

啟動 MySQL 服務並以 root 身分登入。

cd ~/project
sudo service mysql start
mysql -u root

有兩個檔案create-database.sql和insert-data.sql，位於~/project/。

載入文件中的資料。需要在MySQL控制台輸入指令來建構資料庫：

source ~/project/create-database.sql
source ~/project/insert-data.sql

在資料庫操作語句中，最常用、也算最重要的就是SELECT查詢。在先前的實驗中，我們使用了 SELECT * FROM table_name;在許多地方都可以看到表格中的所有內容。 SELECT 可以與具有多種約束的關鍵字一起使用，這些約束包含多種功能。本實驗室將詳細介紹這些用途。

基本 SELECT 語句

SELECT語句的基本格式：

SELECT row name  FROM table name WHERE constraint;

如果要查詢表的所有內容，則查詢列名帶星號*，代表將查詢表中的所有列。大多數情況下，我們只需要查看一個表格的指定列，例如查看員工表的姓名和年齡：

USE mysql_labex;
SELECT name,age FROM employee;

MariaDB [mysql_labex]> SELECT name,age FROM employee;
+------+------+
| name | age  |
+------+------+
| Tom  |   26 |
| Jack |   24 |
| Rose |   22 |
| Jim  |   35 |
| Mary |   21 |
| Alex |   26 |
| Ken  |   27 |
| Rick |   24 |
| Joe  |   31 |
| Mike |   23 |
| Jobs | NULL |
| Tony | NULL |
+------+------+
12 rows in set (0.000 sec)

數學符號條件

SELECT 語句通常帶有 WHERE 約束，用於實現更準確的查詢。 WHERE 約束可以有數學符號（=、、>=、

SELECT name,age FROM employee WHERE age>25;

篩選年齡超過 25 歲的結果：

MariaDB [mysql_labex]> SELECT name,age FROM employee WHERE age>25;
+------+------+
| name | age  |
+------+------+
| Tom  |   26 |
| Jim  |   35 |
| Alex |   26 |
| Ken  |   27 |
| Joe  |   31 |
+------+------+
5 rows in set (0.000 sec)

或尋找名為 Mary 的員工的姓名、年齡和電話：

SELECT name,age,phone FROM employee WHERE name='Mary';

結果：

MariaDB [mysql_labex]> SELECT name,age,phone FROM employee WHERE name='Mary';
+------+------+--------+
| name | age  | phone  |
+------+------+--------+
| Mary |   21 | 100101 |
+------+------+--------+
1 row in set (0.000 sec)

“與”和“或”

WHERE之後可以有多個約束，根據這些條件的邏輯關係，我們可以使用OR和AND來連接：

篩選 - 年齡小於 25 歲，或年齡大於 30

SELECT name,age FROM employee WHERE age<25 OR age>30;

MariaDB [mysql_labex]> SELECT name,age FROM employee WHERE age<25 OR age>30;
+------+------+
| name | age  |
+------+------+
| Jack |   24 |
| Rose |   22 |
| Jim  |   35 |
| Mary |   21 |
| Rick |   24 |
| Joe  |   31 |
| Mike |   23 |
+------+------+
7 rows in set (0.000 sec)

篩選 - 年齡大於 25 歲，年齡小於 30

SELECT name,age FROM employee WHERE age>25 AND age<30;

如果我們需要包括 25 歲和 30 歲，請使用 年齡在 25 歲到 30 歲之間 :

MariaDB [mysql_labex]> SELECT name,age FROM employee WHERE age>25 AND age<30;
+------+------+
| name | age  |
+------+------+
| Tom  |   26 |
| Alex |   26 |
| Ken  |   27 |
+------+------+
3 rows in set (0.000 sec)

MariaDB [mysql_labex]> SELECT name,age FROM employee WHERE age BETWEEN 25 AND 30;
+------+------+
| name | age  |
+------+------+
| Tom  |   26 |
| Alex |   26 |
| Ken  |   27 |
+------+------+
3 rows in set (0.000 sec)

進入與不進入

關鍵字IN和NOT IN用來過濾一定範圍內的結果。例如，我們想要找 dpt3 或 dpt4 中的人：

SELECT name,age,phone,in_dpt FROM employee WHERE in_dpt IN ('dpt3','dpt4');

對於 NOT IN，例如在以下命令中，我們將取得不在 dpt1 或 dpt3 中的人員：

SELECT name,age,phone,in_dpt FROM employee WHERE in_dpt NOT IN ('dpt1','dpt3');

MariaDB [mysql_labex]> SELECT name,age,phone,in_dpt FROM employee WHERE in_dpt IN ('dpt3','dpt4');
+------+------+--------+--------+
| name | age  | phone  | in_dpt |
+------+------+--------+--------+
| Tom  |   26 | 119119 | dpt4   |
| Rose |   22 | 114114 | dpt3   |
| Rick |   24 | 987654 | dpt3   |
| Mike |   23 | 110110 | dpt4   |
| Tony | NULL | 102938 | dpt3   |
+------+------+--------+--------+
5 rows in set (0.000 sec)

MariaDB [mysql_labex]> SELECT name,age,phone,in_dpt FROM employee WHERE in_dpt NOT IN ('dpt1','dpt3');
+------+------+--------+--------+
| name | age  | phone  | in_dpt |
+------+------+--------+--------+
| Tom  |   26 | 119119 | dpt4   |
| Jack |   24 | 120120 | dpt2   |
| Mary |   21 | 100101 | dpt2   |
| Joe  |   31 | 110129 | dpt2   |
| Mike |   23 | 110110 | dpt4   |
| Jobs | NULL |  19283 | dpt2   |
+------+------+--------+--------+
6 rows in set (0.000 sec)

通配符

關鍵字LIKE在SQL語句中與通配符一起使用，通配符代表未知字元。 SQL 中的通配符是 _ 和 %。其中_代表未指定字符，%代表不定未指定字符。

例如，如果您只記得電話號碼的前四位是1101，而忘記了後兩位，則可以用兩個_通配符代替：

SELECT name,age,phone FROM employee WHERE phone LIKE '1101__';

這裡有以 1101 開頭的電話號碼：

MariaDB [mysql_labex]> SELECT name,age,phone FROM employee WHERE phone LIKE '1101__';
+------+------+--------+
| name | age  | phone  |
+------+------+--------+
| Joe  |   31 | 110129 |
| Mike |   23 | 110110 |
+------+------+--------+
2 rows in set (0.000 sec)

還有一種情況，例如你只記得名字的第一個字母，並且不知道名字的長度，那就用%通配符代替不定字符：

SELECT name,age,phone FROM employee WHERE name LIKE 'J%';

這裡有以J開頭的名字：

MariaDB [mysql_labex]> SELECT name,age,phone FROM employee WHERE name LIKE 'J%';
+------+------+--------+
| name | age  | phone  |
+------+------+--------+
| Jack |   24 | 120120 |
| Jim  |   35 | 100861 |
| Joe  |   31 | 110129 |
| Jobs | NULL |  19283 |
+------+------+--------+
4 rows in set (0.000 sec)

對結果進行排序

為了使查詢結果更有條理、更容易理解，我們可能需要按照一定的規則對它們進行排序。 ORDER BY 派上用場。預設情況下，ORDER BY是按升序排列的，透過使用ASC和DESC，我們也可以得到升序和降序的結果訂購。

例如我們將薪資降序排序，SQL語句：

SELECT name,age,salary,phone FROM employee ORDER BY salary DESC;

MariaDB [mysql_labex]> SELECT name,age,salary,phone FROM employee ORDER BY salary DESC;
+------+------+--------+--------+
| name | age  | salary | phone  |
+------+------+--------+--------+
| Jobs | NULL |   3600 |  19283 |
| Joe  |   31 |   3600 | 110129 |
| Ken  |   27 |   3500 | 654321 |
| Rick |   24 |   3500 | 987654 |
| Mike |   23 |   3400 | 110110 |
| Tony | NULL |   3400 | 102938 |
| Alex |   26 |   3000 | 123456 |
| Mary |   21 |   3000 | 100101 |
| Jim  |   35 |   3000 | 100861 |
| Rose |   22 |   2800 | 114114 |
| Jack |   24 |   2500 | 120120 |
| Tom  |   26 |   2500 | 119119 |
+------+------+--------+--------+
12 rows in set (0.000 sec)

SQL內建函數和計算

SQL允許對錶中的資料進行計算。在這方面，SQL 有五個內建函數來執行 SELECT 的結果：

Function:	COUNT	SUM	AVG	MAX	MIN
For:	count numbers	sum up	average	maximum value	minimum value

The COUNT function can be used for any data type (because it is only a count), while SUM and AVG functions can only calculate numeric data types. MAX and MIN can be used for numeric, string, or datetime data types.

For example, when we want to calculate the maximum and minimum value of salary, we use a statement like this:

SELECT MAX(salary) AS max_salary,MIN(salary) FROM employee;

You may have noticed a tiny detail. Use AS keyword can rename value. E.g. Max value is renamed into max_salary:

MariaDB [mysql_labex]> SELECT MAX(salary) AS max_salary,MIN(salary) FROM employee;
+------------+-------------+
| max_salary | MIN(salary) |
+------------+-------------+
|       3600 |        2500 |
+------------+-------------+
1 row in set (0.000 sec)

Subqueries

The SELECT statements discussed above all involve data in only one table, but sometimes you have to process multiple tables to get the information you need. For example, you want to know a few projects done by the department where the employee named "Tom" is located. Employee information is stored in the employee table, but the project information is stored in the project table.

We can use subqueries to deal with such situations:

SELECT of_dpt,COUNT(proj_name) AS count_project FROM project
WHERE of_dpt IN
(SELECT in_dpt FROM employee WHERE name='Tom');

MariaDB [mysql_labex]> SELECT of_dpt,COUNT(proj_name) AS count_project FROM project
    -> WHERE of_dpt IN
    -> (SELECT in_dpt FROM employee WHERE name='Tom');
+--------+---------------+
| of_dpt | count_project |
+--------+---------------+
| dpt4   |             2 |
+--------+---------------+
1 row in set (0.000 sec)

Subqueries can also be extended to three, four or more layers.

Join

When dealing with multiple tables, the subquery is only useful when the results are from the same table. However, if you need to display data in two or more tables, you must use the join operation.

The basic idea is to connect two or more tables as a new table to operate, as follows:

SELECT id,name,people_num
FROM employee,department
WHERE employee.in_dpt = department.dpt_name
ORDER BY id;

This result is the number of employees in each department, where employee id and name from the employee table, people_num from the department table:

MariaDB [mysql_labex]> SELECT id,name,people_num
    -> FROM employee,department
    -> WHERE employee.in_dpt = department.dpt_name
    -> ORDER BY id;
+----+------+------------+
| id | name | people_num |
+----+------+------------+
|  1 | Tom  |         15 |
|  2 | Jack |         12 |
|  3 | Rose |         10 |
|  4 | Jim  |         11 |
|  5 | Mary |         12 |
|  6 | Alex |         11 |
|  7 | Ken  |         11 |
|  8 | Rick |         10 |
|  9 | Joe  |         12 |
| 10 | Mike |         15 |
| 11 | Jobs |         12 |
| 12 | Tony |         10 |
+----+------+------------+
12 rows in set (0.000 sec)

Another connection statement format is to use the JOIN ON syntax. The statement is the same as:

SELECT id,name,people_num
FROM employee JOIN department
ON employee.in_dpt = department.dpt_name
ORDER BY id;

Result is the same.

Summary

In this lab we learned the basic use of SELECT statement:

• Basic syntax
• Mathmatical symbol conditions
• AND OR IN
• Wildcards
• Sort
• SQL built-in functions and calculations
• Sunqueries and connection queries

---

? Practice Now: SQL's SELECT Statement

---

Want to Learn More?

• ? Learn the latest MySQL Skill Trees
• ? Read More MySQL Tutorials
• ? Join our Discord or tweet us @WeAreLabEx

以上是SQL 基礎知識 |選擇語句 |資料庫管理的詳細內容。更多資訊請關注PHP中文網其他相關文章！