SQL简介

Structured Query Language 结构化查询语言

三种基本操作是：update，insert，delete。

基本数据类型

ANSI 规定的数据类型

分为CHARACTER和NUMERIC两种。

CHARACTER

CHARACTER(n), CHAR(n) 定长字符串

CHARACTER Varying 变长字符串

CHAR Varying 变长字符串

NUMERIC

NUMERIC(p,s) p表示精度，s表示小数点后保留的数字个数

INTERGER, INT, SMALLINT

FLOAT

REAL

DOUBLE PRECISION

Oracle公司的数据类型

字符类型

CHAR(n) 定长字符串

VARCHAR 变长字符串

LONG 文本字符串，变长，用来储存含有大量字符的文档数据

数字类型

NUMBER(p,s)

这里p表示精度而s表示小数点后位数。如果s是负数则表示对小数点左侧s位进行舍入。

例如对于数字7456123.89这个数字进行如下操作得到相应的结果：

  • NUMBER: 7456123.89 默认不做更改
  • NUMBER(*,1): 7456123.9 小数点后保留一位
  • NUMBER(9): 7456124 s的缺省值为0，这里得到底值
  • NUMBER(9,2): 7456123.89 保留两位
  • NUMBER(9,1): 7456123.9 保留一位
  • NUMBER(6): ERROR 数字的整数部分已近超过6位，这里的结果不能接受
  • NUMBER(7,-2): 7456100 对小数点左侧两位的整数进行舍入

SQL基本语句

创建表

格式：

CREATE TABLE *name(
    *colname *datatype [NOT NULL]
    ...
    [PRIMARY KEY(*colname ...)]
);

格式中，[]表示可选，*表示是用户自己需要填入的参数。

例如：

CREATE TABLE orders(
    ordno INTEGER NOT NULL,
    month CHAR(3),
    cid CHAR(4),
    aid CHAR(3),
    pid CHAR(3),
    qty INTEGER,
    PRIMARY KEY(ordno)
);

SELECT语句

格式：

SELECT *colname ...
FROM *tablename ...
WHERE *condition ;

select后面的是结果中要投影的列，如果from后面有多个表，就在这些表的笛卡尔积中选择符合条件的行。

select *表示列全选。

distinct关键字表示投影之后去掉重复的行，如果没有这个关键字那么重复的行也会保留。

例如：

SELECT distinct pid FROM orders WHERE cid=='c01';

表和列的别名

as关键字可以给列起别名。如果是表的别名，as也可以省去。

例如：

select distinct cname, aname
from customers c, orders o, agents a
where c.cid==o.cid and o.aid==a.aid;

查询的实现是for循环，如果from后面有多个表就是嵌套的for循环，效率不高。所以我们又引入子查询。

INTERSECT

将两个表相交。可以连接两个select的结果形成一个新的结果，表示两个结果的交集。

子查询

子查询分为相关子查询和独立子查询。相关子查询是指，内层子查询用到了外层查询的表和别名，和外层查询相关，而独立子查询则无关。

由于查询的具体实现形式的制约，只有内层的子查询可以使用外层查询的表和别名，而外层不可以使用子查询的表和别名。

IN

格式： expr (not) in [子查询]

整个in 表达式返回布尔值，可以作为查询条件使用。

例如：

select ordno from orders
where (cid,aid) in (
    select cid,aid
    from customers c, agents a
    where c.city=='Duluth' and a.city=='New York'
);

量化比较

some(any) all，他们本身是量化词，所以需要和< >等运算一起使用。some和all后面加上子查询，表示将子查询的结果转换成一个迭代器。然后对于结果中的每一个行迭代：

  • all表示只有当迭代所有行都满足表达式条件（带入判断表达式中都返回True）时才返回True。
  • some（any一样）表示当任意一个子查询结果中的行可以满足表达式为True时就返回True。
select * from customers
where discnt == some(
    select discnt from customers
    where city in {'Dalas','Boston'}
);

另外：

  • <>any 等同于 not in
  • ==some 等同于 in

EXISTS

格式： (not) exists[子查询]，当子查询返回非空集合的时候返回True，而子查询结果为空集的时候返回False。

使用的时候发现，exists通常要在子查询中涉及到上层的表，也就是通常是关联子查询。

not exist 能用来实现关系运算中的差运算。

select distinct c.cname
from customers c
where not exists(
    select *
    from orders o
    where o.cid=c.cid and o.aid=='a05'
);

使用exists的时候往往只关注子查询的结果是不是空，所以对于子查询中select哪些列，一般都是无所谓的。

BETWEEN

expr (not) between expr1 and expr2;

表示一个“或”的范围。如果expr=expr1或者expr=expr2都返回True。

IS NULL

colname is (not) null如果这个属性值为null就返回True，否则返回False。

LIKE

colname (not) like val1 (escape val2)

如果属性和val1的模式匹配就返回True。其中val1是模板，而val2是转义指示字符。

一般情况下，_(下划线)表示任意一个字符，而%(百分号)表示任意一段字符串（可以为空）。

escape 的意思是，将指定的字符当做转义的标志使用。详见例子：

cname like "Tip\_%" escape "\"; # 匹配以 Tip_ 开头的字符串（\_表示一个_）

cname like "__\%%" escape"\"; # 匹配正则表达式 ._%.*?

cname like 'ab\\%' escape'\'; # 匹配正则表达式 ab\.*?

Intersect, Union, Except

这三个运算都是对子查询结果之间的运算。

subquery1 Union (all) subquery2 返回两个子查询结果的并集。

subquery1 Intersect (all) subquery2 返回两个子查询结果的交集。

subquery1 Except (all) subquery2 返回subquery1-subquery2的差。

有all关键字的时候意味着结果中重复的行都会得到保留，而没有all关键字意味着结果会将重复的行只保留一个。

From

From关键字后面可以跟一个tableref关键字表示select查找的大范围。

tableref既可以是一个表名，也可以是子查询。另外tableref除了可以指定表的别名，也可以指定表中列的别名。比如”agents a(agent_id, agent_name, city, per)”就是说将agents表取别名a并且把列的名字按顺序改为agent_id, agent_name, city, per。如果是子查询也可以给这个子查询结果指定别名。

join

join关键字就相当于关系运算中的theta连接。使用的格式如下：

select cname, aname
from customers join agents on customers.city=agents.city;

on后面就是对应theta连接的条件。

SQL集合函数

SQL中有五个对(行的)集合运算的函数。

COUNT

参数可以是一个或多个属性，或者是*表示指定表的全部列。返回值是行的数量。

另外在count的括号里加入distinct关键字也可以表示查找不同属性的行的数量，会把相同属性的只算一次。

例如：

select count(distinct city)
from customers;

select cid from customers
where discnt<all(select max(discnt) from customers);

count如果和groupby一起使用，可以很快找到数量上满足要求的列，例如亲属大于两个的员工。但是要注意groupby的依据关键字应该是表的键，例如不能是员工姓名，而应该是员工号。

SUM

参数必须是类型为数字的列。返回所有行在这一列上的数值总和。

例如

select sum(qty) as p03all
from orders
where pid='p03';

AVG

参数必须是类型为数字的列。返回所有行在这一列上的数值的算术平均值。

MAX/MIN

参数可以是类型为数字和字符的列。返回最大值或最小值。

例如：

select cid
from customers
where discnt<all(select max(discnt) from customers);

从上面的例子中可以看出，这五个集合函数都不能直接放在select的后面，原因是其实现要借助循环。

空值处理

空值不等于任何值。

count()作用在空值上时返回0，而其他四个函数返回空值。

Select行分组

group by

使用的格式就是直接在group by后面加上作为分组依据的若干行即可。

例如：

select pid, sum(qty) as total
from orders
group by pid;

使用的时候有几个需要注意的点：

  • select后面包含的这些列在每一组中都只有唯一值。
  • select后面必须包含作为分组依据的行。

错误示例1：

select pid, cid, sum(qty) as total
from orders
group by pid;

这个例子中select的cid列在每一组中不一定是唯一的值，因此会出错。

错误示例2：

select pid, sum(qty)
from orders
group by pid, cid;

这个例子中cid作为分组依据，却没有在select中出现，也是不对的。

having

having用来对分组得到的组进行筛选。

Select执行顺序

select的执行顺序如下：

  1. 对from关键字后面的多个表进行笛卡尔积。
  2. 按照where的条件删除不符合条件的行。
  3. 按照group by进行分组。
  4. 删去不满足having后面条件的组。
  5. 根据select后面的若干列投影，形成查询结果表。
  6. 根据order by后面的列进行排序。

例如：

select dep, job, avg(sal) --step5
from empl --step1
where job<>'M' --step2
group by dep, job --step3
having avg(sal)>28000 --step4
order by avg(sal) desc; --step6 desc表示降序

做题技巧

除法的实现

除法需要我们自己实现。通常除法的使用场景是选出“所有xxx”的，比如订购了所有来自NewYork的供货商商品的客户。要通过两个嵌套的Not Exists实现，思路是找到订购了所有的，就是找不存在“在NewYork却不存在和他之间有订单”这种情况的供应商的客户。最外层的表和第一层Not Exist的表都会在第二层Not Exists中使用，所以最好也起个别名。例如这个题的答案就是

select cid
from customers c
where not exists(
    select *
    from agents a
    where a.city="New York" and not exists(
        select *
        from orders o
        where o.cid=c.cid and o.aid=a.aid
    )
)

求最大最小的另一思路

使用>=ALL或者<=ALL也可以筛选出最大和最小值。

每一类取代表性的一行

比如找到每个客户金额最高的订单。可以通过group by和max找到每个客户和他最高金额订单的金额，然后在用连接或者in，将这些订单的其他信息找到。

对组规模进行筛选

比如找到订单数大于10的商品。可以用group by依照商品对订单分组，然后having count(*)>10就可以依照组内行数进行筛选了。

SQL表格操作

insert

格式：

insert into tablename(colname1,colname2,...)
values
(value1,value2,...);

需要注意的是值和上面的列排序编号对应。

另外，insert也可以实现将另一个相容表中的行（或者查询结果）插入到这个表中，直接在tablename加子查询就可以了。

update

格式：

update tablename
set colname = expr/null/subquery
where search-condition; --可无where条件

作用就是通过where筛选出要更改的行然后用set对列进行重新赋值。

例如下面这个可以通过已有表的信息更新另一个表的内容：

update swcusts
set discnt=(
    select discnt
    from customers
    where cid=swcusts.cid --对应行的更新
);

delete

格式：

delete from tablename
where search-condition; --可无where条件

where筛选出要删除的行然后delete将它们从表中删除。

例如：

delete
from agents
where aid in (
    select aid 
    from orders
    group by aid 
    having sum(dollars)<600
);

到此为止第三章SQL基础查询语言就讲完了。