SQL笔记及模板

SQL笔记及模板

1. DDL

1.1 数据类型

1.1.1 通用类型

数据类型	描述
char(n)	定长字符/字符串，长度固定为 n
varchar(n)	可变长字符串，串长最大为 n
int	整数类型
smallint	小整数类型
numeric(p, d)	定点数，共 p 位（包括符号位），有 d 位在小数点右侧
real, double precision	浮点数与双精度浮点数，精度与机器相关
float(n)	精度至少为 n 位的浮点数

1.1.2 不同数据库平台的名称

数据类型	Access	SQLServer	Oracle	MySQL	PostgreSQL
boolean	Yes/No	Bit	Byte	N/A	Boolean
integer	Number(integer)	int	Number	Int Integer	Int Integer
float	Number(single)	Float Real	Number	Float	Numeric
currency	Currency	Money	-	-	Money
string(fixed)	N/A	Char	Char	Char	Char
string(variable)	Text(<256) Memo(65k+)	Varchar	Varchar Varchar2	Varchar	Varchar
binary object	OLE Object Memo	Binary(fixed up to 8k) Varbinary(<8k) Image(<2GB)	Long Raw	Blob Text	Binary Varbinary

1.2 构建sql关系的基础操作

1.2.1 定义sql关系 create table

create table 表名
    (属性1 类型1，
     属性2 类型2，
     完整性约束; --如 primary key (属性名))等

1.2.2 完整性约束

• primary key($A_{j1},A_{j2},...,A_{jm}$)：属性($A_{j1},A_{j2},...,A_{jm}$)构成该关系的主码，主码属性必须非空且唯一，并且不存在任意两元组的主键取值相同。
• foreig key($A_{k1},A_{k2},...,A_{km}$) references：表示关系中任意元组在属性($A_{k1},A_{k2},...,A_{km}$) 上的取值必须对应于关系 s 中某元组在主码属性上的取值。
• not null：写在某一属性的定义后，表明该属性不允许空值。

1.2.3 插入 insert

insert into 表名
    value (值1, 值2, ..., 值3);

   值被给出的顺序遵循对应属性在关系模式中给出的顺序

1.2.4 删除某表中所有元组 drop / delete

delete from 表名; 
drop table 表名;

   drop 会删除表中所有元组及该表的模式，而 delete 仅删除其中所有的元组

1.2.5 为已有关系增加/删除属性 alter

• 增加属性

alter table 表名 add 属性名 属性类型;

   关系中原有元组在新属性上的值默认为 null

• 删除属性

alter table 表名 drop 属性名

1.3 SQL 查询的基本结构 select

select 结果中所需的属性列表
from 查询的值所需要访问的关系列表
where 被查询属性在关系上的需满足的条件

   若省略 where 子句，则默认条件为 true，select * 表示选择所有属性。

1.3.1 更名 as

• 对结果关系中属性

select 所需属性-旧名 as 所需属性-新名
from 表名

• 对所需访问的关系

select 所需属性
from 表名 as 新表名

• 对于函数所得的结果

select avg(属性) as avg_attribute

   可以将长的关系名称简写；或是对同一个关系赋不同的名称，相当于是同一关系的两个不同拷贝，使得某一关系可以对自己做笛卡尔积。这些被重命名的新标识符被称为相关名称或表别名、相关变量、元组变量。

1.3.2 排序 order by

select 所需属性
from 表名
where 限制条件
order by 属性名1 desc, 属性名2 asc, 属性名3;

   默认使用升序，可以用 desc 表示降序，asc 显式地表示升序。

1.3.3 where 子句谓词 between

where 属性 between a and b; --等价于 属性 >= a and 属性 <= b

   not between 同理。

1.3.4 属性列表间比较

where (属性1, 属性2) <= (限制值1, 限制值2)

整个元组匹配限制值时返回真，即当 属性1 <= 限制值1 且 属性2 <= 限制值2 时为真

1.3.5 去除重复元素 distinct

select distinct 属性

若两元组对应属性值非空且相等或都为空，则保留它的一个拷贝。上述对待空值的方式与谓词中对待空值的方式不同，此处 null = null 的结果返回的是 true，而在谓词中会返回 unknown。

1.4 连接

1.4.1 自然连接 natura join

select 属性
from 表1 natural join 表2

   连接两关系模式中都出现的属性上取值相同的元组对。还有一种 SQL 提供的自然连接构造形式

select 属性
from 表1 join 表2 using (需要限制的属性名);

   只要指定属性上的元组对取值相等即可视为匹配。

1.5 集合运算

1.5.1 并 union

select ......
union --或是 union all
select ......

   两个select所得的结果必须拥有相同的列，且每列的顺序及类型都必须相同。求两个查询结果的并集。union 所得结果会自动去重，union all 所得结果会保留重复元素。

1.5.2 交 intersect

select ......
intersect --或是 intersect all
select ......

   求两个查询结果的交集，其余同union。

1.5.3 差 except

select ......
except --或是 except all
select ......

   输出第一个输入中存在，而第二个输入中不存在的元组。有些 SQL 使用 minus 替代except

1.6 空值

   SQL 将涉及空值的任何比较运算的结果视为 unknown，因此 and, or, not 的布尔运算被扩展到可以处理该状态。

• and：true and unknown 的结果是 unknown，false and unknown 的结果是 false，unknown and unknown 的结果是 unknown。
• or：true or unknown 的结果是 true，false or unknown 的结果是false，unknown or unknown 的结果是 unknown。
• not：not unknown 的结果是unknown。
     当 where 子句对一个元组计算的结果为 false 或 unknown，则该元组不能被加入到结果的集合中。

select ......
where 属性 is null -- is not null

   判断该关系中该属性为空/非空的元组。is unknown 和 is not unknown 同理，用于判断一表达式结果是否为 unknown。

1.7 基本聚集

1.7.1 常见聚集函数

   聚集函数是以值的一个集合（集或多重集）为输入，返回单个值的函数。包括：avg, min, max, sum, count。除了 count(*) 以外的所有聚集函数都忽略输入的空值，空集的 count 运算值为 0，其他聚集运算在输入为空集时返回一空值。

1.7.2 分组聚集 group by

select ......
group by 属性1, 属性2 

   使用 group by 时，select 子句中列出的属性，除了出现在 group by 当中的属性，其余属性必须在聚集函数内部。group by 是先排序后分组的。

1.7.3 having 子句

select ......
group by 属性
having 条件

   筛选符合条件的分组。但这个条件必须是整个分组所共有的，比如说由聚合函数对组内某一属性运算后所得的结果，或是 group by 中所列出的属性。运算顺序为 from -> where -> group by -> having -> select。因此在 having 子句中不可使用 select 中的别名。在不搭配group by 的情况下，having 等价于 where。

1.8 嵌套子查询

   from 也支持嵌套子查询的操作。某些 SQL 实现要求对每一个子查询结果关系都给一个名字，即使该名字从不被引用；Oracle 允许对子查询结果关系命名（省略关键字 as），但不允许对关系中的属性重命名。

1.8.1 集合成员资格 in/not in

select ......
where 属性 in 集合 --或 not in，集合可以是直接由括号枚举，或是由 select 子句查询

   测试元组是否为该集合中的成员。

1.8.2 集合的比较 some/all

select ......
where 属性 < some 集合 --或 all

   some 表示至少比集合中某一个要大/小，all 表示比集合中所有都大/小。= some 等价于 in，而 <> all 等价于 not in。

1.8.3 空关系测试 exists

select 属性
from 表1 as A
where 条件1 and 条件2 and 
    exists (select * --或 not exists    
        from 表1 as B
        where 条件3 and A.属性1 = B.属性1);

   exists 结构表示在作为参数的子查询非空时返回 true。上述范例中，子查询使用了来自外层查询的一个相关名称，这种称为相关子查询。

1.8.4 重复元组存在性测试 unique

select ......
where unique (子查询) --not unique

   若子查询结果中不含重复元组，则返回 true，反之返回 false。但实际上 mysql 不支持这一结构quq。

1.8.5 with 子句

with 临时关系名 as (子查询)

select 属性
from 临时关系

   with 定义了一临时关系，仅对包含 with 子句的查询有效。作用是使得逻辑更加清晰，并且允许在一个查询内的多个地方使用视图定义。

1.8.6 标量子查询

select (select count(*)
        from ......) as 属性名
from ......

   SQL 允许子查询出现在返回单个值的表达式能出现的任何地方，只要该子查询只返回包含单个属性的单个元组，这样的子查询称为标量子查询。标量子查询可以出现在 select、where 和 having 子句中。

1.9 修改

1.9.1 删除 delete from

delete from 表名
where 条件

   用于删除整个元组。若忽略 where 则将删除所有元组，并保留原关系。

1.9.2 插入 insert into

insert into 表名
    value (属性值1, 属性值2, ......)
--以上写法属性值顺序必须和关系中的一致

insert into 表名(属性1, 属性2, ......)
    value(属性值1, 属性值2, ......)
--以上写法只需与 insert 后指定顺序一致即可

insert into 表1
    select 属性1, 属性2, ......
    from 表2
    where 条件

   其中，未赋值的属性将被默认为空值。

1.9.3 更新 update

update 表名
set 属性1 = 新值
where 条件

   当条件存在时，符合条件的元组将被更新；若无 where 则更新整个关系。

1.9.4 case

case
    when 条件1 then 结果1
    when 条件2 then 结果2
    ...
    else
end

   同一般编程语言中的 case。

x.x 部分函数表

函数名	作用	备注
extract(field from d)	从 data 或 time 值 d 中提取出单独的 field	field = year / month / day / hour / minute / second / timezone_hour / timezone_minute(时区信息)
current_time	返回当前时间
current_time	返回当前时间	带时区
localtime	返回当前时区	不带时间
current_timestamp	时间+日期	带时区
localtimestamp	时间+日期	不带时区
upper(s)	将字符串 s 转换为大写
lower(s)	将字符串 s 转换为小写
trim(s)	去除字符串 s 后的空格
like/not like s	匹配/不匹配模式 s 的字符串	% 表匹配任意子串，_ 表匹配任意一个字符，\ 表转义
avg(s)	平均值	输入必须为一数字集
sum(s)	总和	输入必须为一数字集
count(s)	计数	SQL 不允许在 count(*) 时使用 distinct
max(s)	最大值
min(s)	最小值

?. 杂七杂八

1. SQL 查询的含义可以理解为：将 from 子句中列出来的关系产生笛卡尔积 -> 应用 where 子句中指定的谓词 -> 输出 select 子句中指定的属性/表达式的结果
2. 在 SQL FROM 子句中最多可指定 256 个表或视图
3. MySQL 和 SQL Sever 在匹配字符串时不区分大小写
4. 串联两字符串用 ‘||’
5. 作用域规则：在一个子查询中只能使用该子查询中本身定义的，或者在包含此子查询的任何查询中定义的相关名称；若一个相关名称即在此子查询本身中定义，又在包含该子查询的查询中定义，则子查询中的定义有效，类似于局部变量。

参考书籍

《数据库系统概念 原书第6版》