13个“番茄”搞定SQL

学习SQL的材料是《SQL必知必会》这本小册子,小册子浓缩了SQL的精华,很适合从未接触过SQL的初学者学习。标题中的“番茄”指的是番茄工作时间,不过我的单个番茄工作时间为45分钟,阅读完册子的核心部分加上做笔记总共花费了13个番茄时间。


一、什么是SQL?

SQL是structured query language(结构化查询语言)的缩写,是一种专门用来与数据库沟通的语言。

SQL优点:

  • 几乎所有的DBMS都支持SQL
  • 简单易学
  • 可以进行非常复杂和高级的数据库操作

四个重要概念

    • 用来存储某种特定类型的数据的结构化清单,存储在表中的数据是相同类型的
    • 表中的一个字段,一个或多个列组成表
    • 表中的一个记录,也即是数据库记录(record)
  • 主键(primary key)
    • 表的行或列中唯一标识自己的值,如顾客ID、身份证号

二、检索数据-SELECT语句

首先明确要选择什么(select what?),从什么地方(where?)选择;SQL不区分大小写;多条SQL语句必须以(;)分隔开来。

例如,检索一个列:

1
2
select prod_name
from Products;

也可以检索多个列:

1
2
select prod_id, prod_name, prod_price
from Products;

也可以用通配符(*)来检索所有列

1
2
select *
from Products;

检索列下面不等的值

1
2
3
select distinct vend_id
form Products;
# distinct告诉DBMS只返回不同vend_id行,注意distinct作用与所有的列

返回特定的行

1
2
3
select top 5 prod_name
from Product;
# 返回Products列的前5行

当然,不同的DBMS有不同的语法规则,但是基本的语句往往是可以移植的,复杂的语句就不行。其实SQL很好理解的,看查询语句就跟学习简单得英语语法一样,并且词汇量还不大,转来转去就那么几个关键词。

三、排序检索数据-ORDER BY

按照单个列进行排列

1
2
3
4
5
select prod_name
from Products
order by prod_name;
# 对返回的结果以prod_name的顺序进行排列;
# order by应当保证是select语句的中的最后一条子句

按照多个列进行排列

1
2
3
4
select prod_id, prod_price, prod_name
from Products
order by prod_price, prod_name
# 对返回的结果以prod_price, prod_name进行排列

按位置进行排序

1
2
3
4
select prod_id, prod_price, prod_name
from Products
order by 2, 3;
# 表现先按第二个列再按第三个列进行排序,这个好处在于不用输入列名

按照指定顺序进行排列,SQL默认返回的结果是升序排列,有的时候我们需要返回的结果能够按照降序或者升序来进行排列,在SQL语句中可以通过升序(asc)和降序(desc)语句实现

1
2
3
4
select prod_id, prod_price, prod_name
from Products
order by prod_price desc;
# 根据价格的降序进行排列

也可以对指定列进行升序或者降序排列

1
2
3
4
select prod_id, prod_price, prod_name
from Products
order by prod_price desc, prod_name;
# 表示prod_price降序排列,prod_name升序排列

四、过滤数据

1. SELECT WHERE

在select语句中,数据根据where子句中指定的搜索条件进行过滤,可以进行单个值、范围值、不匹配值、空值检查。另外还可以通过AND和OR组合多个条件进行过滤,不过AND和OR条件得组合需要用括号分隔加以分组。

1
2
3
select ...
from ...
where ...;
操作符说明检查
=等于
<>或!=不等于不匹配检查
<小于
<=小于等于
!<不小于
>大于
>=大于等于
!>不大于
between在指定的两个值之间范围值检查
is null为null值空值检查

2. NOT和IN操作符

where子句中的NOT操作符有且只有一个功能,那就是否定后面的条件,NOT也从不单独使用

1
2
select ...
where not ...;

IN操作符取一组由逗号分隔、括在圆括号中间的合法值,IN操作符有诸多优点:

  • 清楚、直观
  • 运行更快
  • 可以包含其他SELECT语句
1
2
3
select prod_name, prod_price
from Products
where ven_id in ('DLL01', 'BRS01');

3. 使用通配符(wildcard)过滤

主要介绍三种通配符,搭配where使用可以进行快速搜索,但是不要过多使用

百分号%通配符

1
2
3
4
5
6
7
select prod_id, prod_name
from products
where prod_name like 'Fish%'
# 执行这条语句时,将检索任意以Fish开头的词
# 注意区分大小写,'fish%''Fish%'搜索出来的结果是不同的
# 如果搜索条件为%Fish%,则表示匹配任何位置上包含Fish的值
# %还可以匹配给定位置的0个或者多个字符,如'F%y''F%y%'

下划线_通配符的用途与%一样,不过它只能匹配单个字符,而不是多个,如果需要匹配多个则需要多添加一个下划线__

1
2
3
4
select prod_id, prod_name
from products
where prod_name like '__ inch teddy bear'
# 注意匹配的是两个通配符

方括号[]通配符用来指定一个字符集,它必须匹配指定位置的一个字符

1
2
3
4
# 找出所有J或M开头的结果
select ...
from ...
where ... like '[JM]%'
1
2
3
4
# 用前缀字符^(脱字号)找出所有J或M开头以外的结果
select ...
from ...
where ... like '[^JM]%'

四、计算字段

这里介绍两种SQL语言中计算字段的手段,分别是拼接、算术计算

首先是拼接,假如表中包含姓名和地址信息,且需要在格式化的名称中列出供应商的名字,

1
2
3
4
select name + '('+ address + ')'
from people
order by name;
# 加号‘+’也可以换成‘||’,两者输出的结果相同

输出的结果

1
2
zhangsan (hunan)
lisi (hubei)

我们发现上述返回的结果并不理想,空格和括号并不是我们想要的,如果需要返回的结果没有空格,可以使用SQL的RTRIM()函数去掉字符串右边的所有空格来完成,同理,LTRIM()则是去掉字符串左边所有的空格,TRIM()去掉字符串左右两边所有空格。

1
2
3
select rtrim(name) + '('+ rtrim(address) + ')'
from people
order by name;

输出的结果

1
2
zhangsan(hunan)
lisi(hubei)

第二种则是对检索出来的数据进行算术计算,

1
2
3
4
5
6
7
8
9
select prod_id,
quantity,
item_price,
quantity * item_price as expanded_price
from order_items
where order_num = 2000;
# 从order_item中选择若干字段,并以其中字段计算得来的结果作为
# 新的计算列expand_price
# as是一种别名用法

五、处理数据

对一个值或多个值操作

常用的文本处理函数

函数说明
LEFT()返回字符串左边的字符
LENGTH()返回字符串的长度
LOWER()将字符串转换为小写
LTRIM()去掉字符串左边的空格
RIGHT()返回字符串右边的字符
RTRIM()去掉字符串右边的空格
SOUNDEX()返回与字符串发音类似的字符
UPPER()将字符串转换为大写

常用的数值处理函数还有ABS()、EXP()、PI()、SIN()、COS()等等,上述两种数值处理函数均针对一个数值进行的操作,如果要针对表中的一列进行操作则需要AVG()、COUNT()、SUM()、MAX()、MIN()等等函数,这几个函数在进行操作时都会忽略空的行(NULL)。值得注意的是COUNT()函数的用法,COUNT(*)对表中的行数进行计数,不管表中包含的是空值还是非空值,而COUNT(column)则只对非空值的行计数。

数据分组

数据分组用到两个语句,分别是GROUP BYHAVING

①有必要说清楚分组GROUP BY与排序ORDER BY的区别,分组是将输出的结果以分组的形式呈现,而排序则是让输出结果按照顺序进行排列,换言之,分组的结果不一定是按照顺序排列的。

wherehaving的功能也差不多,区别在于where是在数据分组前进行过滤,而having是在数据分组后进行过滤的。

六、查询数据

在SQL中,查询数据主要有子查询、内联结、外联结和组合查询等方法。子查询是通过嵌套查询语句来实现的,子查询语句只能返回一个列,不论是可读性还是调试性,子查询都有明显的缺陷,不推荐使用子查询。

相比子查询,另外一种查询方式——联结查询则是一种更方便的查询方法。

联结查询

如果我们手头上有两个表,这两个表中存在相同的列,在多个表中检索出数据则需要通过内联结。

1
2
3
4
5
6
7
8
9
10
11
12
# 创建表与表之间的联结
select vend_name, prod_name, prod_price
from vendors, products
where vendors.ven_id=products.ven_id;
# 完全限定列名的用法
# 下面的语句返回的结果跟上面的语句执行的结果一样
# 不同的是,两个表之间的关系是以INNER JOIN联结的
# ON子句的效果与where子句的效果一样
select vend_name, prod_name, prod_price
from vendors INNER JOIN products
ON vendors.ven_id=products.ven_id;

除了内联结之外,SQL中还有外联结、左(右)外联结和全外联结几种模式,这里就不一一讨论了。

组合查询

当查询时需要多个where select限定语句时,只需在语句之间添加关键字UNION即可,简单一点理解就是取两个或多个查询结果的交集,所以这就要求where select返回的结果必须具有相同的格式。

1
2
3
4
5
6
7
select ...
from ...
where ...
UNION
select ...
from ...
where ...

六、插入、更新、删除数据

INSERT插入数据,不管是插入完整的行,插入行的一部分还是插入某些查询的结果,values值的个数必须与行数要相等。

1
2
insert into customers(..,..)
values('','','');
1
2
3
4
# 从一个表复制到另一个表
select *
into custcopy
from customers;

更新和删除数据分别用到UPDATEDELETE语句,比如要更新某个客户的电子邮件

1
2
3
4
# 更新customers表下ID为10003客户的电子邮件
update customers
set cust_email = "kim@store.com"
where cust_id = '10003'

用SQL语句删除数据也是很简单的,比如删除掉某一行客户的信息,记住delete从表中删除行,但是并不删除表本身。记住一条重要的原则,使用delete语句的时候一定要配合where子句使用,防止误操作。

1
2
delete from customers
where cust_id = '10003'

七、创建表

使用CREATE TABLE语句创建表,创建表的时候必须给出下列信息:

  • 表的名字
  • 列的名字和定义,用逗号分隔

比如,

1
2
3
4
5
6
create table products
(prod_id char(10) not null,
vend_id char(10) not null,
....
);
# not null指的是在插入或更新行时,该列必须有值

到此,SQL的基础知识就差不多总结结束了,这篇文章简单介绍了如何检索、排序、过滤数据,以及如何针对数据进行简单操作,可以说十分小白。不过作为一个在写这篇笔记之前几乎没有接触过数据库语言的菜鸟,错误在所难免,本文章不建议给初学者作为学习参考,仅作个人防止遗忘,要学习SQL还是去看看《SQL必知必会》这本小册子吧。

觉得还不错?帮我赞助点域名费吧:)