sql必知必会

SQL是目前使用最为广泛的数据库语言之一。本思维导图而是从实践出发，由浅入深地讲解了广大读者所必需的SQL知识，适用于各种主流数据库。实例丰富，便于查阅。涉及不同平台上数据的排序、过滤和分组，以及表、视图、联结、子查询、游标、存储过程和触发器等内容，通过本图读者可以系统地学习到SQL的知识和方法。干货满满，赶快收藏学起来吧！

sql

sql语句由子句构成，子句有必须的也有可选的。子句通常是关键字和所提供的数据组成。

如，select语句的from子句

规则

1一条语句结束需要分号（有的dbms不需要，但出于规范考虑）

2sql关键字没有大小写，但是有的dbms的变量名区分大小写

3空格会被忽略，仅起到方便阅读作用

4sql语句一般返回无格式数据

5字符串需要用引号括起，数值不用

7在查询中涵盖多个表时，要使用完全限定列名

在列名前加上表名，中间用句点隔开

语句

select (子句1) 列名 (子句2) from 表名 (子句3)

从一个或多个表中检索信息

需要至少两个条件：1要什么。2从什么地方。

若要检索多个列，列名之间需用逗号隔开。

select语句可以不适用from和列名，而是直接提供数据

子句1

topx

可以限定返回前x行

*（通配符）

可以一次性检索所有列

使用*会降低检索效率

distinct

只返回不同的值

如果同时检索多列，则多列组合的值一样时，才会省略，而非以某一列为省略的标准

+

将多个输出或指定的值拼接

子句2

as

为输出的列创建列别名

如果新的列名由多个单词组成（包含空格）则需要用引号括起

跟在每个列名的后面

算术操作符

可以对多个列内的值进行四则运算

操作符

优先级等同于一般四则运算，可以使用括号提高有限级

+(加)

-(减)

*(乘)

/(除)

在列名之间加入运算符号

intu 新表名

创建新表

子句3

order by

对指定列进行字母排序

必须是最后一个子句

同时指定多个列，可以同时对多个列排序，以写在前面的为主

如果指定的前一个列没有重复，则指定后面的列无意义

在某个指定项之后加desc，则该列倒序排序

desc全拼descending。两者都可以用。asx（asxending）为升序，也可以用，不过默认就是升序所以没必要

a与A是否相同，a位于B之前还是Z之后，取决于字典设置。

许多dbms允许管理员更改字典设置

where

过滤检索出的数据

操作符

并非所有dbms都支持一样的操作符 还有一些功能重复的操作符没写

=(等于)

!=(不等于)

<(小于)

!>（不大于）

!<(不小于)

>(大于)

between(在指定的两个值之间)

两个值用and分隔，低端值在前

is null(为null值)

null值只能通过此操作符过滤

and(同时满足前后两项)

or(满足前后两项中任意一项)

优先级：()>and>or

in(取特定值)

在括号中描述要取的值，多个值用逗号分开 in比or的有点：1运行速度更快 2与and同用时求职顺序更明了 3条件很多事，in更直观 4in可以包含其他select语句，更动态的建立where子句。第11课细讲

not(去除特定值)

功能类似'!='，但在复杂语句中更有用。便于和in配合

like

相比于where，会消耗更多的检索时间 先用where可以减少like检索的数据量

检索包含某字段的值

通配符

%(任意个任意字符)

_(一个任意字符)

在通配符的任意位置指定字符，表示检索指定位置有指定字符的值

[](指定字符可以是方括号内任意单个字符)

1只能匹配单个字符 2在方括号内加^，表示否定，与not[]的结果相同，但可以简化语法

like只能检索字符串

group by

对指定列进行分组

可以包含多个列更细致的分组，最后的结果汇总在一列上

指定的列必须是有效的，不能使用聚集函数，不能指定长度可变的列

null值将作为一个分组返回

select中的每一列都要在group by中给出。

加上all，会显示由于where导致本来不会出现的分组，这是聚合 函数结果的列，在那一组会显示null

haveing

过滤检索出的分组

对聚集值进行过滤，操作等同于where

过滤where过滤后的值，或者说过滤分组之后的值

如果没有group up子句，会被当做where执行？

子查询

往往可以用联结代替

将多个查询语句合一

作为子查询的的语句，只能检索一列

联结

在一个语句中查询多个表的列

在from中指定多个表

sql本身不限定联结的表数，但大多dbms都有限制

虽然联结两个表的列不一定是主键，但必须没有重复的值

如果不用where将两个表的行一一对应，返回的结果将是两个表行数的积

通常用where指定两表主键一一对应，不符合条件的行将不会出现

inner join(内联结)

联结的值一一对应

或者说两个等价的主键一一对应

除了普通的写法外，可以在from中用inner join指定两个表，不使用where而是on指定条件

self-join(自联结)

使用子查询时，与主查询语句使用同一个表

natural join(自然联结)

没有相同列的联结结果

往往联结的表由相同的列，通过指定列而不是使用*的方式，避免出现相同的列。一般的联结都是自然联结

outer join(外联结)

某个表的联结列每行都要显示一遍

在联结中由于联结的值要一一对应，而某个表中没有某些行，所以某些行不会被输出，外联结规定必须将某表的联结列所有行都输出一遍，这时另一个表里由于没有对应行，所以返回null

right outer join(输出右边表的所有列)

left outer join(输出左边表的所有列)

full outer join(输出两边表的所有列)

联结的种类

可以同时使用多个联结

联结的类型并非非彼即此，可以同时是多种联结

很多时候两个语句能达到同样的 目的，但是不同的dbms处理两个 语句的速度不同，不过往往联结 更快

union(复合查询)

同时执行两个select语句并将其在一个表中显示

sql没有规定union的使用次数，但dbms可能会有规定

各个select语句中的列必须相同，并且不同表的相同列数据类型至少是可以隐含转换的

使用union all可以显示重复行

这是where不具备的功能

只能在最后使用一条order by子句

insert intu 表1+

values(列1,列2,列3……)

加入数行内容

加入的值，必须按表中列的顺序给出

没有的值填null（如果列允许的话）

select(列1,列2,列3……) from 表2

从别的表引入数据

子主题

注意事项

1也可以在表后括号指定列的顺序

这样可以保证代码持续使用，不会因为表中列的次序改变而失效，并且可以只插入部分列，未插入的列必须可以为null

2主键不能重复

3数据的插入与列的排列次序有关，与列名无关

delete from 表

删除整行数据

如果联结了外键，并且是必要的，则该行数据不能被删除

update 表 set 列=值

更新表中数据

通过将值设为null来删除数据

不分dbms可以使用from子句，请查阅说明

不使用where子句则默认所有行

create table 表 (列1 格式,列2 格式…… )

创建表

格式后面可写null或not null，大部分dbms默认为null

null后面加default 值，可以制定默认值

许多人喜欢规定not null然后加上默认值，而不是null

altel table 表

更改表的定义

不同的dbms对这条语句的权限有不同的规定。对增加列的数据类型，是否允许删除或更改现有列，是否允许重命名，对已有数据列的更改是否有限制

由于不同的dbms语法不同，所以本书对本语句未作详细阐述

(增加列)add 列 格式

(删除列)drop column 列

增加约束add constratnt

primary key(列)主键

foreign key(列) references 表(列)外键

uniqeu唯一

check(条件)检查

约束

null后可添加约束

主键primary key

外键references 表(列)

唯一uniqeu

检查check(条件)

drop table 表

删除表

create view 视图名 as selec语句

创建视图

不同的dbms有不同的规定。有些把视图视为只读，有些要求每个列都有名字，有些不能删除表在视图中的内容，有些禁止order by，允许的嵌套层数不同

创建视图之后可以将其当一个正常的表用，简化了联结等操作

BEGIN TRANSACTION 代码1，代码2…… COMMIT TRANSACTION

事务处理

中间的代码必须全部执行或全不执行

可以在代码后设置保留点 save transaction 保留点名 然后在其他语句后加入 IF @@ERROR <> 0 ROLLBACK TRANSACTION 保留点; 来检查是否出错，是否需要返回保留点。 可以设置多个保留点，所以要描述需要回到哪个保留点

游标

不同的dbms规定：1能标记为只读或不分只读2可以执行定向操作3规定范围4使数据在游标打开的时候不会变化

declare 游标名 cursor for select语句(创建语句)

open cursor 游标名(打开游标)

declare 变量1 格式,变量2 格式…… open 游标名 fetch next from 游标名 into 变量1 格式,变量2 格式…… while @@fetch_status=0 begin fetch next from 游标名 into 变量1 格式,变量2 格式…… end close custcursor

close 游标名 deallocate cursor 游标名(关闭游标)

create index 索引名 on 表(列)

创建索引

create trigger 列 on 表 for 操作1,操作2…… as 操作 执行

触发器

执行后可跟条件

函数

与语句相反，几乎所有的dbms支持的函数都不一样

trim()

去除值中的空格

rtrim可以只去右边空格，ltrim可以只去左边空格。

upper()

将值转为大写

lower()

将值转为小写

文本处理类

datepart()

日期检索

getdata(返回成分,日期)

示例 SELECT order_ num FROM Orders WHERE DATEPART( yy, order_ date) = 2012;

显示当前的日期和时间

日期函数

DBMS 提供 的 功能 远 不止 简单 的 日期 成分 提取。 大多数 DBMS 具有 比较 日期、 执行 基于 日期 的 运算、 选择 日期 格式 等 的 函数。 但是， 可以 看到， 不同 DBMS 的 日期- 时间 处理 函数 可能 不同。 关于 具体 DBMS 支持的日期- 时间处理函数， 请参阅 相应 的 文档。

convert()

数据类型转换

soundex()

根据发音模糊搜索？反正我不会读233

left(x,y)

返回左边列的值？

substring()

提取字符串的组成部分

不知道怎么用

len()

也可使用datalength()和lendth()

返回字符串的长度

abs()

绝对值

cos()

余弦

exp()

返回指数数值

pi()

圆周率

sin()

正弦

sqrt()

平方根

tan()

正切

数值处理

avg()

返回平均值

count()

返回行数

max()

返回最大值

在处理文本时，返回最后一行

min()

返回最小值

在处理文本时返回第一行

sum()

返回的和

聚集函数

需要汇总数据，而不需要数据本身 除了count(*)以外都忽略null

这五个函数可以通过在括号中输入distinct的方式，只处理不同的值

除了distinct和all以外不同的dbms还可能支持不同的参数。 distinct也可以用于别的一些函数？

sql sever

表

某种特定类型数据的结构化清单

表中的数据是同一类型的数据。如记录顾客的数据和记录订单的数据，就应该放在两个表里。

在同一个数据库，每个表都有一个唯一的名字表的模式（schma）定义了表的特性，如：数据在表中如何存储、各部分信息如何命名、存储什么样的数据、数据如何分解，是关于数据库和表的布局及特性的信息。

表的模式既可以表述特定的表，也可以描述某库中所有表

列

表中的一个字段。表是由列组成的

每个列都有数据类型（datatype），它限制该列中存储的数据。数据类型的不同会产生不兼容，不同的dbms的数据类型的名称也不尽相同。

数据类型在优化磁盘使用方面起了很大作用。

行

表中的一个记录

也叫数据库记录或记录

主键

虽然主键非必须，但从规范上来说，所有的表均应有主键

某一列的所有值，均能代表一行数据

主键的满足条件

1每一行的值都不相同

2主键列中的值不允许修改

3主键列不能有null值

4主键的值不能重复使用

主键可以由多个列组成，此时单一列的主键值可以重复，但是组合值不能重复

数据库软件

负责创建和操作数据库的容器

应称为数据库管理系统（dbms）

不同的软件用法各不相同

保存有组织的数据的容器。

可以应用在生活方方面面，如网站用户名和密码，通讯录等。

主题

子主题

子主题

子主题

子主题

注释

可以通过特定的方法使指定的内容不被运行

这么做的原因往往有

1对复杂的语句进行说明

2写一些其他不属于代码的东西

如程序员的联系方式，对程序框架和思路的说明

3暂时停止执行某些代码

方法

1'--'两个以上的连字符，从此处开始整行内容都是注释

2'#'在某行开头使用#，整行内容都是注释

不如上一个，往往不用

3/*xxx*/ 在两组符号中间的都是注释

凡例

1左边为理论，右边为写法

2名词均框住

3注释表示一些不重要的追述或不属于理论的说明