sql必知必会笔记

• sql关键字作为保留字使用，不能用于表或者列的名称

• sql语句不区分大小写

• sql语句中空格会被忽略

• select列名之间用逗号分隔

• 少用*号检索，会降低性能

• distinct关键字作用于所有的列

• 限制结果语法

  • top 5 关键字 sqlserver和access中使用

  • fetch first 5 rows only DB2中使用

  • rownum Oracle中使用

  • limit 关键字 mysql、postgresql、sqlite中使用

• offset关键字，代表偏移，limit 4 offset 5 标识从第6行器的4行数据，缩写为limit 5,4

• 第一个被检索的行是第0行，而不是第一行

• 注释

  • #、--、/**/

• sql语句由子句构成，一个子句通常由一个关键字加上所提供的数据组成

• order by 子句应该是select语句中的最后一条子句，如果他不是最后的子句，将会报错。order by中的列并不一定必须是检索的列

• order by多列排序按顺序进行

• order by中的列可以根据相对位置，而不是列名来进行

  select prod_id ,prod_price,prod_name from products order by 2,3;

order by中的2代表select检索的第二个列prod_name,3代表检索列的第三个列

过滤数据

• sql过滤和应用过滤，最好使用sql过滤，减轻应用的性能消耗

• 

   

• 字符串类型用单引号，数值型不用引号

• between...and...语法，between匹配范围中的所有值，包括指定的开始值和结束值

• NULL控制，与字段包含0、空字符串、空格不同

  检查是否是NULL值，需要使用is null子句来进行判断

• and条件，检索满足所有给定条件的行

• or条件，检索满足任一给定条件的行，许多DBMS在第一个条件满足的情况下，就不在计算第二个条件了

• and和or同时使用，都应该使用圆括号明确的分组操作符。

• and和or，不适用括号的情况下，按照默认的求值顺序，在处理or操作符前，优先处理

  and操作符，也就是and优先级高于or

• in子句功能与or相当，但是执行的会更快

• not操作符有且只有一个功能，那就是否定其后所跟的任何条件。not关键字可以用在要过滤的列前，而不紧是在其后

通配符搜索

• 为了在子句中使用通配符，必须使用like操作符。

• 通配符搜索只能用于文本字段，非文本数据类型字段不能使用通配符搜索

  % 匹配 0个、1个、或者多个

  _ 匹配单个字符

  [] 中括号，匹配集合内的一个字符

• 需要注意NULL，%不会匹配值为NULL的行

• 通配符搜索比较耗费时长，不要过度使用通配符，尽量不要将通配符放于开始处。把通配符放于开始处，搜索起来是最慢的。

计算字段

• 在sql语句内可完成的许多转换和格式化工作都可以直接在客户端应用程序内完成。但一般来说，在数据库服务器上完成这些操作比在客户端中完成要快的多。

• 拼接concatenat，某些DBMS中，此操作符可以使用加好+或者两个竖杠||来表示

以上三种表示方式

• 空格处理函数 trim函数，去掉字符串左右两边的空格

• rtrim函数，取消字符串右边的空格，ltrim去掉字符串左边的空格

• 别名alias关键字，AS

• sql支持基本算术操作符

  

   

• 

   

函数

• 不同的DBMS所使用的同一个功能的函数的名称和语法可能不同，这点需要注意，可移植性的问题。

• 大多数DBMS支持文本函数、数值函数、日期和时间函数、系统函数

trim()函数，去除空格

upper()函数，将字符串转为大写

lower()函数，将字符串转换为小写

length()函数，返回字符串长度

left()函数，返回字符串左边的字符

right()函数，返回字符串右边的字符

soundex()函数，返回字符串的soundex的值

soundex，是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。可以理解为根据发音搜索

abs()函数，返回一个数的绝对值

cos()函数，返回一个角度的余弦

exp()函数，返回一个数的指数值

pi()函数，返回圆周率

sin()函数，返回一个角度的正弦

sqrt()函数，返回一个数的平方根

tan()函数，返回一个角度的正切

汇总

聚合函数,聚集函数 aggregate function

对某些行运行的函数，计算并返回一个值

avg()函数，返回某列的平均值

count()函数，返回某列的行数

max()函数，返回某列的最大值

min()函数，返回某列的最小值

sum()函数，返回某列值之和

avg()函数，max()函数，min()函数，sum()函数 都会忽略列值为NULL的行

count()函数

使用count(*),对表中行的数据进行计数，不管表列中包含的值是空值NULL还是非空值。

使用count(column)对特定列中具有值的行进行计数，忽略NULL值。

聚合函数 对所有行执行计算，指定ALL参数或者不指定参数，因为ALL是默认行为

只包含不同的值，指定distinct参数

分组

• 使用分组可以将数据分为多个逻辑组，对每个组进行聚集计算

• 分组是使用select语句中的group by子句建立的

• group by子句指示DBMS分组数据，然后对每个组而不是整个结果集进行聚集。

  group by子句可以包含任意数据的列，因而可以对分组进行嵌套更细致的进行数据分组。

  如果在group by子句中嵌套了分组，数据将在最后指定的分组上进行汇总。换句话说，在建立分组时，指定的所有列都一起计算(所以不能从个别的列取回数据)

  group by子句中列出的每一列都必须是检索列或有效的表达式(但不能是聚集函数).如果在select中使用表达式，则必须在group by子句中指定相同的表达式，不能使用别名

  大多数sql实现不允许group by列带有长度可变的数据类型(如文本或备注型字段)

  除聚集计算语句外，select语句中的每一列都必须在group by子句中给出。

  如果分组列中包含具有NULL值的行，则NULL将作为一个分组返回，如果列中有多行NULL值，他们将分为一组。

  group by子句必须出现在where子句之后，order by子句之前

• where过滤指定的是行，而having过滤支持分组。having支持所有的where操作符，也就是说where子句可以使用having子句来替代。

  where在数据分组前进行过滤，而having在数据分组后进行过滤。

  

   

联结

select a1,b1,c1 from A1,B1 where A1.id=B1.id等价于

• select a1,b1,c1 from A1 inner join B1 on A1.id=B1.id

• 简单查询和标准查询上面两个依次是

• 联结分为 内连接、自连接、自然联结、外连接

组合

• union必须由两条或者两条以上的select语句组成，语句之间用关键字union分隔

• union中的每个查询必须包含相同的列、表达式或者聚集函数（不过，各个列需要以相同的次序列出）

• 列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含转换的类型

• union默认会从结果集中自动去除重复的行，如果想返回有的匹配行，可以使用union all而不是union

• 还有两种比较少用的联合，except和intersect

插入

• insert into table1(col1,col2) select c1,c2 from t2

• select * into newtable from table1

• create table tnew as select * from t1

更新

update语句中可以使用子查询，使用select，有的DBMS也支持from子句

删除某个列值，可以设置它为NULL，NULL表示没有值，而空字符串‘’，是一个值，这点需要注意

删除

某些DBMS中，from关键字是可选的。但是即时不需要，也最好提供，这样将保证sql代码的可移植性

delete不需要列名或通配符，delete删除整行而不是删除列，要删除指定的列，请使用update语句

删除表中所有行，不要使用delete。使用truncate table，它效率更好，速度更快(因为不记录数据的变动)

在使用update或者delete语句之前，应该先用select进行测试，保证过滤数据的正确性

如果DBMS允许数据库管理员施加约束，防止执行不带where子句的update或者delete语句，这应该使用他

更新表

alter table 语句

删除表

drop table语句

重命名表

rename语句，不同的数据库实现不同，sqlserver使用sp_rename存储过程，sqlite使用alter table语句

视图

• 视图是虚拟的表。与包含数据的表不一样，视图只包含使用时动态检索数据的查询

• 作为视图，它不包含任何列或数据，包含的是一个查询。视图仅仅是用来查看存储在别处数据的一种设施

• 与表一样，视图必须唯一命名(不能给视图取与别的视图或表相同的名字)

• 视图可以嵌套，不过嵌套视图可能会严重降低查询的性能，使用之前，应对其进行测试

• 视图不能有索引，也不能有关联的触发器或默认值

创建视图create view 删除视图 drop view

• 最常见的视图应用是 隐藏复杂的sql，通常设计联结。另一个常见用途是重新格式化检索出的数据。

• 视图为虚拟的表。它们包含的不是数据而是根据需要检索数据的查询。视图提供了一种封装select语句的层次，可用来简化数据处理，重新格式化或保护基础数据。

存储过程

存储过程简单来说就是为以后使用而保存的一条或多条sql语句，可以将其视为批文件，虽然他们的作用不仅限于批处理

• 存储过程通常以编译过的形式存储，所以DBMS处理命令所需的工作量少，提高了性能

存储过程的执行 使用关键字execute

方式如下：execute procedureName(args)

事务

• 多数DBMS的事务实现没有明确标识事务处理在何处结束。事务一直存在，直到被中断。通常commit用于保存更改，rollback用于撤销。

游标

游标cursor是一个存储在DBMS服务器上的数据库查询，他不是一条select语句，而是被语句检索出来的结果集。

创建游标declare cursor CName

打开游标 open cursor CName

关闭游标 close CName

索引

创建索引 create index iName on t1(col1)

触发器

触发器是特殊的存储过程，他在特定的数据库活动发生时自动执行。

触发器可以与特定表上的insert、update和delete操作相关联。

与存储过程不一样，触发器与单个的表相关联

创建触发器 create trigger trigName on t1