简单的 SELECT 语句

1. SELECT入门

我们选择SELECT语句作为我们的第一条SQL语句，因为查询是数据库最常用的功能，能帮助我们更快的进入SQL学习中。

SELECT 语句是用于从数据库中选取数据，并将结果存储在一个临时结果表中，称为结果集。结果集实际上也是一种包含行与列的表，只不过是未持久化的，即临时表。

演示数据库

在本教程中，我们使用lintcode样本数据库作为我们案例查询表。

下面是选自”courses“表的数据：

1.1 SELECT * 实例

下面的 SQL 语句从 课程表courses中选取所有列：

SELECT * FROM courses;

其中

* 表示所有列，是一种便捷式写法

执行输出结果

mysql> SELECT * FROM courses;
+----+-------------------------+---------------+------------+------------+
| id | name                    | student_count | created_at | teacher_id |
+----+-------------------------+---------------+------------+------------+
|  1 | Advanced Algorithms     |           880 | 2020-06-01 |          4 |
|  2 | System Design           |          1350 | 2020-07-18 |          3 |
|  3 | Django                  |           780 | 2020-02-29 |          3 |
|  4 | Web                     |           340 | 2020-04-22 |          4 |
|  5 | Big Data                |           700 | 2020-09-11 |          1 |
|  6 | Artificial Intelligence |          1660 | 2018-05-13 |          3 |
|  7 | Java P6+                |           780 | 2019-01-19 |          3 |
|  8 | Data Analysis           |           500 | 2019-07-12 |          1 |
| 10 | Object Oriented Design  |           300 | 2020-08-08 |          4 |
| 12 | Dynamic Programming     |          2000 | 2018-08-18 |          1 |
+----+-------------------------+---------------+------------+------------+
10 rows in set (0.00 sec)

SELECT * 总结

练习题： 写下你的第一行SQL语句

题目描述: 从演示数据库中的另一个表teachers中通过SELECT *获取所有数据

-- write your sql here
SELECT * FROM teachers;

目标输出结果

mysql> SELECT * FROM teachers;
+----+------------------+---------------------------+-----+---------+
| id | name             | email                     | age | country |
+----+------------------+---------------------------+-----+---------+
|  1 | Eastern Heretic  | [e-mail] |  20 | UK      |
|  2 | Northern Beggar  | [e-mail]    |  21 | CN      |
|  3 | Western Venom    | [e-mail]     |  28 | USA     |
|  4 | Southern Emperor | [e-mail]   |  21 | JP      |
|  5 | Linghu Chong     | NULL                      |  18 | CN      |
+----+------------------+---------------------------+-----+---------+
5 rows in set (0.00 sec)

1.2 SELECT COLUMN 实例

下面的 SQL 语句从课程表courses中选取 "name" 列：

SELECT name FROM courses;

执行输出结果

mysql> SELECT name FROM courses;
+-------------------------+
| name                    |
+-------------------------+
| Advanced Algorithms     |
| System Design           |
| Django                  |
| Web                     |
| Big Data                |
| Artificial Intelligence |
| Java P6+                |
| Data Analysis           |
| Object Oriented Design  |
| Dynamic Programming     |
+-------------------------+
10 rows in set (0.00 sec)

练习题: SELECT COLUMN

题目描述: 从另一个表teachers选取所有教师姓名name的列

-- write your sql here
SELECT * FROM teachers;

目标输出结果

mysql> SELECT name FROM courses;
+-------------------------+
| name                    |
+-------------------------+
| Advanced Algorithms     |
| System Design           |
| Django                  |
| Web                     |
| Big Data                |
| Artificial Intelligence |
| Java P6+                |
| Data Analysis           |
| Object Oriented Design  |
| Dynamic Programming     |
+-------------------------+
10 rows in set (0.01 sec)

1.3 SELECT COLUMN,COLUMN 实例

下面的 SQL 语句从课程表 courses 表中选取课程名称 name 和课程开课日期 created_at 的列：

SELECT name, created_at FROM courses;

执行输出结果

mysql> SELECT name, created_at FROM courses;
+-------------------------+------------+
| name                    | created_at |
+-------------------------+------------+
| Advanced Algorithms     | 2020-06-01 |
| System Design           | 2020-07-18 |
| Django                  | 2020-02-29 |
| Web                     | 2020-04-22 |
| Big Data                | 2020-09-11 |
| Artificial Intelligence | 2018-05-13 |
| Java P6+                | 2019-01-19 |
| Data Analysis           | 2019-07-12 |
| Object Oriented Design  | 2020-08-08 |
| Dynamic Programming     | 2018-08-18 |
+-------------------------+------------+
10 rows in set (0.00 sec)

练习题 SELECT COLUMN, COLUMN

题目描述: 从课程表 courses 获取课程名称 name 和上课学生人数 student_count 的列

-- write your sql here
SELECT name, student_count
FROM courses;

目标输出结果

mysql> SELECT name, student_count
    -> FROM courses;
+-------------------------+---------------+
| name                    | student_count |
+-------------------------+---------------+
| Advanced Algorithms     |           880 |
| System Design           |          1350 |
| Django                  |           780 |
| Web                     |           340 |
| Big Data                |           700 |
| Artificial Intelligence |          1660 |
| Java P6+                |           780 |
| Data Analysis           |           500 |
| Object Oriented Design  |           300 |
| Dynamic Programming     |          2000 |
+-------------------------+---------------+
10 rows in set (0.00 sec)

1.4 总结

语法

SELECT * FROM table_name;
- 或
SELECT column_name(,column_name）
FROM table_name;

其中

* 是省略写法，表示查询所有表名

table_name对应我们需要查询的表名

column_name对应我们需要查询表明的具体列名

SELECT后面的列名可以有多个，彼此用逗号(,)隔开，即column_name, column_name

2 SELECT WHERE

到目前为止，我们学习的都是从表中查询指定列的数据，返回指定列的所有行数据。但是在大多数情况下，我们希望使用某种方式过滤掉不感兴趣的行，这时我们可以使用WHERE子句来帮助我们获取感兴趣的行。

WHERE 子句用于过滤记录。

2.1 SELECT WHERE 实例

下面的 SQL 语句从 "courses" 表中选取课程名为 "System Design" 的课程：

SELECT * FROM courses WHERE name = 'System Design';

执行输出结果

mysql> SELECT * FROM courses WHERE name = 'System Design';
+----+---------------+---------------+------------+------------+
| id | name          | student_count | created_at | teacher_id |
+----+---------------+---------------+------------+------------+
|  2 | System Design |          1350 | 2020-07-18 |          3 |
+----+---------------+---------------+------------+------------+
1 row in set (0.01 sec)

2.2 总结

语法

SELECT column_name[,column_name]
FROM table_name
WHERE column_name operator value;

其中

table_name对应查询表的名称

column_name对应指定列的名称，或者是多列，用逗号(,)分隔开

operator为操作符，可以是等于=、小于<、大于>、不等于<>或!=，以及其他操作符，我们将在后续课程学习

练习题 SELECT WHERE Ⅰ

题目描述: 在课程表courses中查询课程名称为Artificial Intelligence的课程信息

-- write your sql here
SELECT *
FROM courses
WHERE name = 'Artificial Intelligence';

目标输出结果

mysql> SELECT *
    -> FROM courses
    -> WHERE name = 'Artificial Intelligence';
+----+-------------------------+---------------+------------+------------+
| id | name                    | student_count | created_at | teacher_id |
+----+-------------------------+---------------+------------+------------+
|  6 | Artificial Intelligence |          1660 | 2018-05-13 |          3 |
+----+-------------------------+---------------+------------+------------+
1 row in set (0.00 sec)

练习题 SELECT WHERE Ⅱ

题目描述: 在课程表中查询学生上课人数student_count大于1000的所有课程名称

-- write your sql here
SELECT *
FROM courses
WHERE student_count > 1000;

目标输出结果

mysql> SELECT *
    -> FROM courses
    -> WHERE student_count > 1000;
+----+-------------------------+---------------+------------+------------+
| id | name                    | student_count | created_at | teacher_id |
+----+-------------------------+---------------+------------+------------+
|  1 | Advanced Algorithms     |           880 | 2020-06-01 |          4 |
|  2 | System Design           |          1350 | 2020-07-18 |          3 |
|  3 | Django                  |           780 | 2020-02-29 |          3 |
|  4 | Web                     |           340 | 2020-04-22 |          4 |
|  5 | Big Data                |           700 | 2020-09-11 |          1 |
|  6 | Artificial Intelligence |          1660 | 2018-05-13 |          3 |
|  7 | Java P6+                |           780 | 2019-01-19 |          3 |
|  8 | Data Analysis           |           500 | 2019-07-12 |          1 |
| 10 | Object Oriented Design  |           300 | 2020-08-08 |          4 |
| 12 | Dynamic Programming     |          2000 | 2018-08-18 |          1 |
+----+-------------------------+---------------+------------+------------+
10 rows in set (0.00 sec)