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 | eastern.heretic@gmail.com | 20 | UK |
| 2 | Northern Beggar | northern.beggar@qq.com | 21 | CN |
| 3 | Western Venom | western.venom@163.com | 28 | USA |
| 4 | Southern Emperor | southern.emperor@qq.com | 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)
