第一课 了解SQL
1.1 数据库基础
数据库:保存有组织的数据的容器(通常是一个文件或一组文件),注意不要把数据库软件与数据库勿用,通常数据库软件应称为数据库管理系统(DBMS)。
表:存储某种特定类型数据的结构化清单,每个表都有唯一的名字来标识自己。
模式:关于数据库和表的布局以及特性的信息。
列:表中的一个字段。所有表都是由一个或多个列组成的。
数据类型:所允许的数据的类型,每个表列都有相应的数据类型,它限制(或允许)该列所存储的数据。
行:表中的一个记录被称为行,表的数据是按行存储的。
主键(primary key):主键是一个或一组列,它的值可以唯一标识表中的每一行,虽然并非所有的表都需要主键,但是最好在创建任何表的时候都定义主键。
列可以当做主键的条件
- 任意两行不具有相同的主键值;
- 每一行都必须具有一个主键值;
- 主键列中的值不允许修改或更新;
- 主键值不能重(chong三声)用(如果某行从表中删除,它的主键不能赋给以后的新行)。
1.2 什么是SQL
SQL是结构化查询语言(Structured Query Language)的缩写,是一种专门用来与数据库沟通的语言。与其他语言不通,SQL中只有很少的次,这是有意而为的,设计SQL的目的是很好地完成一项任务:提供一种从数据库中读写数据的简单有效的方法。
SQL有如下的优点:
- SQL不是某个特定数据库供应商专有的语言,几乎所有重要的DBMS都支持SQL。
- 简单易学,语句全部由具有很强描述性的英语单词组成。
- 强而有力,灵活使用其语言元素可以进行非常复杂和高级的数据库操作。
书中使用的数据会在接下来的实践过程用到,直接去官网下载即可:http://www.forta.com/books/0672336073/
第2课 检索数据
这一节介绍如何使用SELECT语句从表中检索一个或多个数据列
2.1 SELECT语句
SQL语句是由简单的英文单词构成的,这些单词称为关键字(关键字不能作为表或列的名称),而SELECT语句是最经常使用的SQL语句,它的用途是从一个或多个表中检索信息。
为了使用SELECT检索表数据,必须至少给出两条信息——想选择什么,以及从什么地方选择。
2.2 检索单个表
mysql> SELECT prod_name FROM Products;
+---------------------+
| prod_name |
+---------------------+
| Fish bean bag toy |
| Bird bean bag toy |
| Rabbit bean bag toy |
| 8 inch teddy bear |
| 12 inch teddy bear |
| 18 inch teddy bear |
| Raggedy Ann |
| King doll |
| Queen doll |
+---------------------+
9 rows in set (0.00 sec)
上述语句利用SELECT语句,从Products表中检索了一个名为prod_name的列。
列名卸载SELECT关键字之后,FROM则指出从哪个表中检索数据。
注意事项:
- SQL关键字不区分大小写,但是,关键字最好使用大写。
- SQL语句中所有的换行都被忽略。
- 用分号结束SQL语句。
2.3 检索多个列
mysql> SELECT prod_id,prod_name,prod_price
-> FROM Products;
+---------+---------------------+------------+
| prod_id | prod_name | prod_price |
+---------+---------------------+------------+
| BNBG01 | Fish bean bag toy | 3.49 |
| BNBG02 | Bird bean bag toy | 3.49 |
| BNBG03 | Rabbit bean bag toy | 3.49 |
| BR01 | 8 inch teddy bear | 5.99 |
| BR02 | 12 inch teddy bear | 8.99 |
| BR03 | 18 inch teddy bear | 11.99 |
| RGAN01 | Raggedy Ann | 4.99 |
| RYL01 | King doll | 9.49 |
| RYL02 | Queen doll | 9.49 |
+---------+---------------------+------------+
9 rows in set (0.00 sec)
和前一个句子相同,不过这句指定了三个列名,列名之间用逗号隔开。
2.4 检索所有列
mysql> SELECT * FROM Products;
+---------+---------+---------------------+------------+-----------------------------------------------------------------------+
| prod_id | vend_id | prod_name | prod_price | prod_desc |
+---------+---------+---------------------+------------+-----------------------------------------------------------------------+
| BNBG01 | DLL01 | Fish bean bag toy | 3.49 | Fish bean bag toy, complete with bean bag worms with which to feed it |
| BNBG02 | DLL01 | Bird bean bag toy | 3.49 | Bird bean bag toy, eggs are not included |
| BNBG03 | DLL01 | Rabbit bean bag toy | 3.49 | Rabbit bean bag toy, comes with bean bag carrots |
| BR01 | BRS01 | 8 inch teddy bear | 5.99 | 8 inch teddy bear, comes with cap and jacket |
| BR02 | BRS01 | 12 inch teddy bear | 8.99 | 12 inch teddy bear, comes with cap and jacket |
| BR03 | BRS01 | 18 inch teddy bear | 11.99 | 18 inch teddy bear, comes with cap and jacket |
| RGAN01 | DLL01 | Raggedy Ann | 4.99 | 18 inch Raggedy Ann doll |
| RYL01 | FNG01 | King doll | 9.49 | 12 inch king doll with royal garments and crown |
| RYL02 | FNG01 | Queen doll | 9.49 | 12 inch queen doll with royal garments and crown |
+---------+---------+---------------------+------------+-----------------------------------------------------------------------+
9 rows in set (0.00 sec)
给定通配符(*),则返回表中的所有列,列的顺序一般是列在表定义中出现的物理顺序。
一般来说,除非确实需要表中的每一列,否则最好别使用*通配符,虽然说这能够让你自己省事(不用明确列出所需要的列),但是检索不需要的列,通常会降低检索和应用程序的性能。
不过,通配符也有一个优点,就是可以检索名字未知的列。
2.5 检索不同的值
mysql> SELECT vend_id FROM Products;
+---------+
| vend_id |
+---------+
| BRS01 |
| BRS01 |
| BRS01 |
| DLL01 |
| DLL01 |
| DLL01 |
| DLL01 |
| FNG01 |
| FNG01 |
+---------+
9 rows in set (0.00 sec)s
表中所有的供货商ID,但是,明明只有三个产品供货商却返回了9行。
有时我们会希望去除重复行只检索到不同值,办法是使用Distinct:
mysql> SELECT DISTINCT vend_id FROM Products;
+---------+
| vend_id |
+---------+
| BRS01 |
| DLL01 |
| FNG01 |
+---------+
3 rows in set (0.00 sec)
DISTINCT关键词是作用于所有的列的,不仅仅是跟在其后的那一列,因此,只有在检索结果中出现各列完全相同的行时,DISTINCT才会将重复的行舍弃。
2.6 限制结果
SQL语句返回的是指定表中所有匹配的行(很可能是每一行),不过,一些情况下我们可能只想要返回第一行或一定数量的行,这是可行的,但是在不同的数据库中,这一SQL的实现语句有所不同。
在SQL Server和Access中使用TOP来限制最多返回多少行:
SELECT TOP 5 prod_name FROM Products;
DB2语句则是:
SELECT prod_name FROM Products FETCH FIRST 5 ROWS ONLY;
ORACLE则基于ROWNUM(行计数器)来计算行:
SELECT prod_name FROM Products WHERE ROWNUM <=5;
而在MySQL、MariaDB、PostgreSQL或者SQLite中,使用LIMIT子句:
SELECT prod_name FROM Products LIMIT 5;
上述代码均是在不同的数据库中选出检索结果的前五行。
我使用的是MySQL,结果如下:
mysql> SELECT prod_name FROM Products LIMIT 5;
+---------------------+
| prod_name |
+---------------------+
| Fish bean bag toy |
| Bird bean bag toy |
| Rabbit bean bag toy |
| 8 inch teddy bear |
| 12 inch teddy bear |
+---------------------+
5 rows in set (0.00 sec)
LIMIT 5指示返回不超过5行的数据,如果想要得到指定的5行而非前5行,可以使用OFFSET关键字指定:
mysql> SELECT prod_name FROM Products LIMIT 5 OFFSET 5;
+--------------------+
| prod_name |
+--------------------+
| 18 inch teddy bear |
| Raggedy Ann |
| King doll |
| Queen doll |
+--------------------+
4 rows in set (0.00 sec)
OFFSET后的数字表示从这一行开始计算,上述语句的含义是从第5行开始向后取5行。
由于表中一共只有9行数据,而MySQL和Python类似,是从0开始计算行的,所以从5行开始往下取的话,就只能取到4行而已。
这个语句也可以简写:
mysql> SELECT prod_name FROM Products LIMIT 5,5;
+--------------------+
| prod_name |
+--------------------+
| 18 inch teddy bear |
| Raggedy Ann |
| King doll |
| Queen doll |
+--------------------+
4 rows in set (0.00 sec)
如上,LIMIT后第一个数字表示从哪行开始,第二个数字表示查几行。
2.7 使用注释
mysql> SELECT prod_name -- 注释:软身高1.29米
-> FROM Products;
+---------------------+
| prod_name |
+---------------------+
| Fish bean bag toy |
| Bird bean bag toy |
| Rabbit bean bag toy |
| 8 inch teddy bear |
| 12 inch teddy bear |
| 18 inch teddy bear |
| Raggedy Ann |
| King doll |
| Queen doll |
+---------------------+
9 rows in set (0.00 sec)
上述语句使用了行内注释,必须注意的是“–”前后都要有空格。
另一种很少得到支持,在其他语言中比较常用的行内注释:
# 注释:软身高1.29米
SELECT prod_name FROM Products;
也可以进行多行注释,注释可以在脚本的任何位置停止或开始:
/* 软!身高!129!
SELECT prod_name vent_id
FROM Products;*/
SELECT prod_name FROM Products;
注释内容全部都是本人的恶趣味,请不要在意。
第3课 排序检索数据
这一课讲授如何使用SELECT语句的ORDER BY子句,根据需要排序检索出的数据。
3.1 排序数据
mysql> SELECT prod_name FROM Products;
+---------------------+
| prod_name |
+---------------------+
| Fish bean bag toy |
| Bird bean bag toy |
| Rabbit bean bag toy |
| 8 inch teddy bear |
| 12 inch teddy bear |
| 18 inch teddy bear |
| Raggedy Ann |
| King doll |
| Queen doll |
+---------------------+
9 rows in set (0.00 sec)
上述语句返回了数据库中的单列,但是并没有排序。
子句:SQL语句由子句构成,有些子句是必须的,有些则是可选的。一个子句通常由一个关键字加上所提供的数据组成。子句的例子有我们在前一刻看到的SELECT语句的FROM子句。
为了对SELECT语句检索出的数据进行排序,可以使用ORDER BY子句,取一个或多个列的名字:
mysql> SELECT prod_name FROM Products ORDER BY prod_name;
+---------------------+
| prod_name |
+---------------------+
| 12 inch teddy bear |
| 18 inch teddy bear |
| 8 inch teddy bear |
| Bird bean bag toy |
| Fish bean bag toy |
| King doll |
| Queen doll |
| Rabbit bean bag toy |
| Raggedy Ann |
+---------------------+
9 rows in set (0.00 sec)
可以看到数据按照prod_name的字符值顺序排列。
大多数情况下,ORDER BY使用的都是显示出来的列,但是,没有被选中的列其实也可以用来排序:
mysql> SELECT prod_name FROM Products ORDER BY prod_id;
+---------------------+
| prod_name |
+---------------------+
| Fish bean bag toy |
| Bird bean bag toy |
| Rabbit bean bag toy |
| 8 inch teddy bear |
| 12 inch teddy bear |
| 18 inch teddy bear |
| Raggedy Ann |
| King doll |
| Queen doll |
+---------------------+
9 rows in set (0.00 sec)
实际上是按照没被选中的prod_id列排序的。
3.2 按多个列排序
很多时候,我们需要按照不止一个列进行排序,例如:显示生物名单,可能希望按照种族和身高排列(首先按种族排列,然后再在每个种族中按照身高排序,譬如软在“水母”种族中是排序在最后的。)如果有多个行的数据在用于排序的列上有相同的值,那么同时按照多个列进行排序就显得很有用。
要按多个列排序,简单指定列名,列名之间用逗号分开即可。
mysql> SELECT prod_id,prod_price,prod_name
-> FROM Products
-> ORDER BY prod_price,prod_name;
+---------+------------+---------------------+
| prod_id | prod_price | prod_name |
+---------+------------+---------------------+
| BNBG02 | 3.49 | Bird bean bag toy |
| BNBG01 | 3.49 | Fish bean bag toy |
| BNBG03 | 3.49 | Rabbit bean bag toy |
| RGAN01 | 4.99 | Raggedy Ann |
| BR01 | 5.99 | 8 inch teddy bear |
| BR02 | 8.99 | 12 inch teddy bear |
| RYL01 | 9.49 | King doll |
| RYL02 | 9.49 | Queen doll |
| BR03 | 11.99 | 18 inch teddy bear |
+---------+------------+---------------------+
9 rows in set (0.00 sec)
可以看到,数据是按照prod_price、prod_name的顺序进行排序的,首先只按照prod_price排序,只有当出现prod_price的值相同的情况时,才按照prod_name进行排序。
3.3 按列位置排序
除了可以用列名指出排序顺序之外,ORDER BY还支持按照相对列位置进行排序:
mysql> SELECT prod_id,prod_price,prod_name
-> FROM Products
-> ORDER BY 2,3
-> ;
+---------+------------+---------------------+
| prod_id | prod_price | prod_name |
+---------+------------+---------------------+
| BNBG02 | 3.49 | Bird bean bag toy |
| BNBG01 | 3.49 | Fish bean bag toy |
| BNBG03 | 3.49 | Rabbit bean bag toy |
| RGAN01 | 4.99 | Raggedy Ann |
| BR01 | 5.99 | 8 inch teddy bear |
| BR02 | 8.99 | 12 inch teddy bear |
| RYL01 | 9.49 | King doll |
| RYL02 | 9.49 | Queen doll |
| BR03 | 11.99 | 18 inch teddy bear |
+---------+------------+---------------------+
9 rows in set (0.00 sec)
可以看到和前文使用两列prod_price、prod_name排序的效果是一样的。
这里的ORDER BY 后面的数字,指的是在SELECT清单中的相对位置,即prod_price和prod_name。
3.4 指定排序方向
数据排序并不限于升序,只不过,在没有给定条件的情况下,排序是默认按照升序的。
为了进行降序排序,指定DESC关键字:
mysql> SELECT prod_id,prod_price,prod_name
-> FROM Products
-> ORDER BY prod_price DESC;
+---------+------------+---------------------+
| prod_id | prod_price | prod_name |
+---------+------------+---------------------+
| BR03 | 11.99 | 18 inch teddy bear |
| RYL01 | 9.49 | King doll |
| RYL02 | 9.49 | Queen doll |
| BR02 | 8.99 | 12 inch teddy bear |
| BR01 | 5.99 | 8 inch teddy bear |
| RGAN01 | 4.99 | Raggedy Ann |
| BNBG01 | 3.49 | Fish bean bag toy |
| BNBG02 | 3.49 | Bird bean bag toy |
| BNBG03 | 3.49 | Rabbit bean bag toy |
+---------+------------+---------------------+
9 rows in set (0.00 sec)
如果对多个列排序的话,可以分别对每列指定升降序:
mysql> SELECT prod_id,prod_price,prod_name
-> FROM Products
-> ORDER BY prod_price DESC,prod_name;
+---------+------------+---------------------+
| prod_id | prod_price | prod_name |
+---------+------------+---------------------+
| BR03 | 11.99 | 18 inch teddy bear |
| RYL01 | 9.49 | King doll |
| RYL02 | 9.49 | Queen doll |
| BR02 | 8.99 | 12 inch teddy bear |
| BR01 | 5.99 | 8 inch teddy bear |
| RGAN01 | 4.99 | Raggedy Ann |
| BNBG02 | 3.49 | Bird bean bag toy |
| BNBG01 | 3.49 | Fish bean bag toy |
| BNBG03 | 3.49 | Rabbit bean bag toy |
+---------+------------+---------------------+
9 rows in set (0.00 sec)
可以看到这里对price降序,对name升序。
对多个列进行降序排序,必须为每个列单独指定DESC。