数据库知识是每个计算机学生都学过的课程,不过对我来说,在课程结束之后,学到的数据库知识大多都还给老师了,除非在项目中用用到,否则平时接触的机会也不多。偶然看到做Java的同学桌上的这本《SQL必知必会》,拿来翻翻,认为内容不错,适合温习DBMS的标准语法,也挺适合入门使用,讲的非常清楚。当然,这本书的作用也只是温习语法了,更高级或者更底层的数据库知识本书并没有提到,要了解只能去找别的书或者拿出教科书来看了。
本篇笔记和书的结构一样,按照书的目录安排,但是内容和表述并不一定按照书的原文来,可能会根据自己的感想而修改,所以并不是书本的原文摘抄,对于没接触过DBMS的人的可读性或许较差。
第 1 章 了解 SQL
1.1 数据库
数据库是保存有组织的数据的容器。这里“有组织”三个字比较有意思,是数据库较为底层的数据组织结构,在这本书中并不会涉及。
可以简单的将数据库想象成一个文件柜,将文件柜中的不同数据保存在其中。
注意,这里有两个概念应该分清楚,即“数据库(DB)”和“数据库管理系统(DBMS)”:
- 数据库(Database, DB): 存放数据的容器
- 数据库管理系统(Database Management System):管理数据库中数据的系统,例如常用的增删改查都是通过这个系统来操作数据库这个容器的。
1.1.2 表
表相当于文件柜中的柜子,每个柜子中存储的都是不同的文件,而且对文件的组织结构都是不同的,即每个柜子都有自己的数据结构。
更具体的举例如下:学生表是一个柜子,班级表是另一个柜子。学生表柜子中的文件包含学生的学号,姓名,性别等字段;班级表中包含班级代号,班级名,学生人数等字段。这些不同的字段可以视作每个柜子中文件的数据结构。
同一个数据库中的表名应该是唯一的,不同数据库中表名可以相同。
1.1.3 列和数据类型
列是表中的一个字段,例如学生姓名、学号、性别等字段都是列。
保存不同数据的列可以是不同的类型,例如姓名是字符串类型、年龄是数值类型、入学日期是日期类型等等。
注意:不同DBMS对于数据类型的支持可能不同,甚至同一中数据类型在不同DBMS中的名称也不同。
1.1.4 行
行就是表中的一个记录,也就是保存的一个数据。
1.1.5 主键
每个表中都应该有一个列用来唯一表示每一行的数据,例如每个人的身份证可以唯一的表示一个人,每个学生的学号可以唯一的表示每个学生,此种类型的列可以作为主键。
注意:
- 每个表都应该定义主键
- 不仅一个列可以作为主键,多个列的组合也可以作为主键,只要多个列的组合不同。
1.2 什么是 SQL
SQL发音为S-Q-L或者sequel, 全名是 Structured Query Language,翻译后是结构化查询语言,用来操作数据库,可以通过简单的关键字的组合对数据库中的数据进行复杂的操作,例如复杂的增删改查。
以上概念对于初学者可能有些抽象,但是在学习并操作之后就会有比较具体的感受了。所以说还是要多练习。
第 2 章 检索数据
2.1 SELECT 关键字
要从数据库中查询数据,需要用到 SELECT 关键字。而且必须知道要查什么和从哪里查,具体来说是从哪个表中查哪个列(字段)的数据。例如从商品表中查询保存的商品名:
SELECT prod_name FROM Product
即可输出Product表中保存的所有prod_name的值。
注意:
- SQL语句不分大小写,但是一般习惯将关键字大写,列名小写,表名开头大写。
- SQL可以分成多行来写,每行一个关键字,这样更加明确,例如上面的例子可以这样分成多行。
SELECT prod_name
FROM Product
2.3 查出多个列的数据
上一节的例子中,只查到了商品名称prod_name列的数据,如果还想查其他商品属性,例如商品价格prod_price,可以用逗号将多个列名隔开:
SELECT prod_name, prod_price
FROM Product
这样即可查出所有商品的名字和价格。
2.4 检索所有的列
可以使用通配符 * 查询出一个表中所有的列的数据
SELECT *
FROM Product
但是要注意的是这样的操作性能不好。
2.5 检索不同的值
如果想使输出的结果中没有重复值,则可以用 DISTINCT
关键字来修饰 SELECT
,例如想查询表中包含的不同的商品价格:
SELECT DISTINCT prod_price
FROM Product
则输出的结果中每个值都不一样,即不包含相同的价格。
2.6 限制查询的结果
如果想查询第一行或者某几行,则可以进行限制,但是不同DBMS的实现不同,例如在 SQL Server中用 TOP 关键字指定查询前 n 行的数据,例如:
SELECT TOP 5 prod_name
FROM Product
这样即可查出前5个商品名称。
2.7 注释的写法
SQL语言中的注释有两种写法:
- 行内注释, 用 两个连续的横岗开头,例如:
SELECT TOP 5 prod_name -- 这是注释,查询前5条商品名称
FROM Product
- 换行的注释,和典型的高级程序设计语言一样,使用
/* */
来将注释括起来,例如:
/*
这是注释,查询前5条商品名称
*/
SELECT TOP 5 prod_name
FROM Product
第 3 章 将检索结果排序输出
当不指定查询出的数据显示顺序的时候,检索出的数据是无序的,而且并不一定是按照数据插入的时间确定的。当要指定输出的顺序时,可以使用ORDER BY
关键字来指定,并可以指定按照字母的升序或者降序来排列,其中升序用ASC表示(ASCENDING),降序用DESC(DESENDING)来表示。
例如指定按照货品名来排序:
SELECT prod_name
FROM Product
ORDER BY prod_price DESC
则输出的商品名是按照商品价格从大到小排序的。
3.2 按照多个列(字段)排序
以上面的例子为例,加入有多个商品的价格都相同,那么这些价格相同的商品应该怎么排序呢?是否可以指定呢?答案是可以指定,方法就是在第一个列名后再指定第二个列名,也就是说在第一个列名相同时,按照第二个列名来排序。同理,多个列名依次排序。例如相同的商品名称就按照商品id来降序排序。
SELECT prod_name
FROM Product
ORDER BY prod_price, prod_id DESC
注意:排序语句 ORDER BY
应该放在整个查询语句的最后,否则会报错。
第 4 章 过滤数据,指定检索条件
前面介绍的检索语句很少用,因为它会将某个列的数据全部检索出来,如果要按照某个条件来检索数据,例如查询价格低于100的商品名称都有哪些,则要按照价格条件进行查询,条件要用WHERE
关键字来指定,例如上面的例子用SQL语言表示为:
SELECT prod_name
FROM Product
WHERE prod_price < 100
4.1 操作符
上面的例子仅表示了小于的含义,当然还有大于、等于、不等于、在某个数值段内、不为NULL等条件。许多DBMS都有各自特有的操作符。
更多例子:
BETWEEN AND
操作符,查询某个数值段内的数据,例如查询价格在50到100以内的商品名
SELECT prod_name
FROM Product
WHERE prod_price BETWEEN 50 AND 100
- 查询降价记录为
NULL
的字段
SELECT prod_name
FROM Product
WHERE prod_price_reduction IS NULL
更多条件见书本。
第 5 章 高级数据过滤
本章主要讲的是多个条件的组合,主要涉及到下面几个操作符。
AND
操作符,用来连接多个查询条件,取多个条件的交集,例如查询名称是玩具车并且价格低于100的商品id:
SELECT prod_id
FROM Product
WHERE prod_name = '玩具车' AND prod_price < 100
OR
操作符,用来连接多个查询条件,取多个条件的并集,例如查询名称是玩具车或者玩具飞机的商品id:
SELECT prod_id
FROM Product
WHERE prod_name = '玩具车' OR prod_name = '玩具飞机'
注意:在组合多个 AND 和 OR 操作符的时候,要注意条件的顺序,因为 AND 会比 OR 得到更优先的处理,这样有时候得到的结果不是我们所期望的,所以应该用括号多个操作符进行明确的分组,例如:
SELECT prod_id
FROM Product
WHERE prod_name = '玩具车' OR prod_name = '玩具飞机' AND prod_price < 100
我们期望通过以上代码得到玩具车和玩具飞机中价格少于100的产品id 。
然而上面的代码实际得到的结果是 商品名是玩具车的产品id 和 商品名是玩具飞机而且价格 < 100的商品id的组合。
如果要得到本来期望得到的结果,则要用括号将上面的条件分组,如下:
SELECT prod_id
FROM Product
WHERE (prod_name = '玩具车' OR prod_name = '玩具飞机') AND prod_price < 100
IN
操作符,查询满足几个条件其中之一的数据,相当于OR
操作符的简写,例如查找名称是玩具车或者玩具飞机的商品id除了用OR
来做之外,还可以用IN
来做,如下:
SELECT prod_id
FROM Product
WHERE prod_name IN ('玩具车', '玩具飞机')
NOT
操作符,查询不是某个条件的数据,也可以用!=
来做,只是这样语义化更好,例如查询商品名不是玩具车的商品id:
SELECT prod_id
FROM Product
WHERE NOT prod_name = 玩具车
第 6 章 通配符
之前查询目标都是具有明确条件的,当想要查找名称中包含某些字符串的商品名时就要使用通配符了。要用LIKE
关键字和通配符 %,_,[]
等配合使用。其中%
可以代替任意数量的字符,_
用来代替一个字符。
这里的内容和正则表达式相似,但是不完全一样。
具体实例如下:
- 使用
%
通配符来匹配多个字符
查找以'Fish'开头的商品名:
SELECT prod_name
FROM Product
WHERE NOT prod_name LIKE 'Fish%'
查找名称中间有'Fish'的商品名:
SELECT prod_name
FROM Product
WHERE NOT prod_name LIKE '%Fish%'
查找名称中F开头h结尾的商品名:
SELECT prod_name
FROM Product
WHERE NOT prod_name LIKE 'F%h'
- 使用
_
通配符来匹配一个字符
SELECT prod_name
FROM Product
WHERE NOT prod_name LIKE 'F__h'
- 使用
[]
来匹配中括号里的其中某个字符,查询以a或b或c开头的字符串
SELECT prod_name
FROM Product
WHERE NOT prod_name LIKE '[abc]%'
注意:通配符容易引起性能问题,所以不要滥用。如果确实要使用,注意不要将通配符放在查询的开始处。
相同功能的通配符在不同DBMS中可能会不一样。例如%在Access中是*。
第7章 使用计算属性
上面的例子中,我们直接将查询到的数据输出,最多也就是排了个序,但是我们还可以对数据进行更多的操作,例如将两个查询到的字段连接起来,具体如将商品名和价格连接起来进行输出,形成如下面的例子:玩具飞机-199。还可以对查询出来的数值数据进行求和操作然后输出。
- 连接不同的字段,用
+
号进行连接,这个语法类似于Java和JavaScript的字符串连接操作,例如:
SELECT prod_name + ' - ' + prod_price
FROM Product
输出类似下面:
玩具飞机 - 199
玩具车 - 99
- 还可以对连接过后形成的结果取个别名以便于后续操作,使用
AS
关键字进行,例如对上面的结果取个别名为 prod_shot,可以这样写:
SELECT prod_name + ' - ' + prod_price AS prod_shot
FROM Product
prod_shot
-------------
玩具飞机 - 199
玩具车 - 99
-
对数值执行算数运算,例如单价*数量算出总价:
SELECT prod_count, prod_price, prod_count * prod_price AS expend_price FROM Product
则会输出如下:
prod_count prod_price expend_price ---------- ---------- ------------ 10 15 150 2 20 40
-
对列使用函数
可以对列使用函数以对查询的结果进行处理,例如取删除左右空格后的结果,可以通过
TRIM()
函数来做:SELECT TRIM(prod_name) + ' - ' + prod_price AS prod_shot FROM Product
则prod_name会被去除两侧空格后再进行拼接。
更多的函数还有RTRIM
删除右边的空格,更多的函数还有LTRIM
删除左边的空格等等。
第 8 章 使用函数处理数据
上章提到了删除空格的三个函数,其实还有更多使用的函数可供使用,包括文本处理函数、日期时间处理函数以及数值处理函数等。分别取典型举例如下:
-
文本处理函数
包括
UPPER
来使字符串转换成大写,LENGTH
返回字符串的长度,SOUNDEX
找到发音相似的字符串在内的等等字符串处理函数。其中上面提到的第三个函数
SOUNDEX
比较有意思。假如想查找'Michaelle',但是在检索时写成了'Michael',则按照上面的方法是检索不到的。但是这两个词语的发音相似,可以使用SOUNDEX
关键字来查找如下:SELECT prod_name FROM Product WHERE SOUNDEX(prod_name) = SOUNDEX('Michael')
则通过上面的代码可以成功找到 'Michaelle'。
-
日期处理函数
举例:查找生产日期是2012年的商品名称,使用
DATEPART
函数来提取日期中的特定部分:SELECT prod_name FROM Product WHERE DATEPART('yyyy', prod_date) = 2012
-
数值处理函数
主要是对数值进行一些操作,包括
ABS COS EXP PI SIN TAN SQRT
等函数。
第 9 章 汇总函数
- 有时的需求是并不需要知道具体的数据都是哪些,而只是需要知道数据的统计量,例如平均数,总数,最大值,最小值等数据,都可以用函数来解决。以平均数函数为例,查询所有商品价格的平均数:
SELECT AVG(prod_price) AS avg_price
FROM Product
则输出结果为所有商品价格的平均值。
- 与上面的同理还有
COUNT MAX MIN SUM
。 - 可以结合
DISTINCT
关键字指定只对所有不同的数据进行统计,例如:
SELECT AVG(DISTINCT prod_price) AS avg_price
FROM Product
此时只对不同的值统计平均数,而不是对所有数据计算平均值。
-
可以同时使用多个统计函数
SELECT AVG(DISTINCT prod_price) AS avg_price, MAX(DISTINCT prod_price) AS max_price, SUM(DISTINCT prod_price) AS sum_price FROM Product
注意:使用此种函数得到结果要比先读取所有数据之后再统计的速度要快。
第 10 章 分组数据
加入我们想按照某个列(字段)对查询出来的数据进行分组,可以直接使用GROUP BY
语句进行,假如想对分出来的组进行过滤,则可以使用HAVING
关键字进行。
这里组的概念比较抽象,举个例子就容易明白了。假如想计算每个id的数量,则使用select * from xxx
是无法做到的,这个时候就要用到分组了:
SELECT prod_id, COUNT(*) as num_prods
FROM Product
GROUP BY prod_id
输出:
prod_id num_prods
-------- ----------
1 5
3 100
2 55
这样会对每个id计算count,而不是对整个表计算一下count
当想只显示 count 大于 50 的id及其count时,可以使用 HAVING
关键字来过滤:
SELECT prod_id, COUNT(*) as num_prods
FROM Product
GROUP BY prod_id
HAVING COUNT(*) > 50
输出:
prod_id num_prods
-------- ----------
3 100
2 55
则只会显示 count > 50 的结果。
别忘了此时仍然可以使用ORDER BY
对每个group进行排序,例如:
SELECT prod_id, COUNT(*) as num_prods
FROM Product
GROUP BY prod_id
HAVING COUNT(*) > 50
ORDER BY num_prods
输出:
prod_id num_prods
-------- ----------
2 55
3 100
第 11 章 使用子查询
子查询的概念:子查询可以简单的说是查询中嵌套的查询,将一个查询得到的结果作为另外一个查询的条件,看个例子:
SELECT cust_name, cust_contact
FROM Customers
WHERE cust_id IN (SELECT cust_id
FROM Orders
WHERE order_num IN(SELECT order_num
FROM OrderItems
WHERE prod_id = 'RGAN01'
)
)
看到上面有三个select语句,而且这三个select并不是独立的,是相互嵌套的,可以将其视为 3 层。
检索结果是从内向外进行计算的,也就是说计算顺序是这样的:最里面的select返回的结果作为次内部的where关键字的条件,这样依次向外,最后返回最外层查询的结果。
要注意的是:除最外层的查询语句之外,所有的查询语句只能查询单个列,也就是说返回值只能是单个列。