常用SQL资料
来源:互联网 发布:nginx 根路径转发 编辑:程序博客网 时间:2024/06/05 09:57
Transact-SQL具体可以参阅《Transact-SQL参考》(tsql.hlp)(简写《T-SQL》)
建意:
在写SQL Script时最好能将数据操作SQL的保留字用大写
注:
此处语法格式只是常用格式,并不是SQL标准格式,标准格式请参阅《T-SQL》
(在例子中的SQL无实际意义)
选择
SELECT
SELECT 可以选择指定的数据列
如:
SELECT * FROM sysobjects
SELECT [name] FROM syscolumns
当在SQL中存在系统保留字时应用“[]”引起,或在SQL中存在特殊字符也应用“[]”引起,
如:
SELECT [Object Name] FROM Objects
在使用别名时也应注意以上原则,别名使用可以用以下两种方法:
Column_name AS alias
Column_name alias
中间的AS可以省略
在SELECT中可以使用条件选择语法,参见下面的“条件”
如:
SELECT [name],xtype,CASE WHEN xtype=’U’ THEN ‘用户表’ ELSE CASE WHEN xtype=’S’ THEN ‘系统表’ END END AS 类型 FROM sysobjects
返回表:
name | xtype | 类型 |
syscolumns | S | 系统表 |
tabledefine | U | 用户表 |
将两个查询合成单独的返回表:
用UNION关键字
如SELECT A,B FROM Table1
UNOIN
SELECT C,D FROM Table2
说明:
在使用UNION时,若无ALL参数则默认将过虑相同的记录,
如:
Table1 | Table2 | |||||
ID | TF1 | VALUE1 | ID | TF2 | VALUE2 | |
1 | A | 10 | 5 | A | 10 | |
5 | B | 20 | 6 | D | 21 | |
2 | A | 30 | 3 | C | 31 | |
3 | C | 40 | 1 | B | 41 |
SELECT TF1,VALUE1 FROM Table1
UNION
SELECT TF2,VALUE2 FROM Table2
返回表:
返回表:
TF1 | VALUE1 |
A | 10 |
B | 20 |
A | 30 |
C | 40 |
D | 21 |
C | 31 |
B | 41 |
其中可以看出少了一个”TF2=A ,VALUE2=10”的记录
但用以下查询时
SELECT TF1,VALUE1 FROM Table1
UNION ALL
SELECT TF2,VALUE2 FROM Table2
返回表:
TF1 | VALUE1 |
A | 10 |
B | 20 |
A | 30 |
C | 40 |
A | 10 |
D | 21 |
C | 31 |
B | 41 |
刚此查询将返回所有记录
此问题可能会出现在报表统计上,如一个员工在不同日期内做了相同的产品与数据,但在使用非ALL方式进行合计时将会少合计一条记录
与INTO联用
SELECT …. INTO B FROM A
可以将A 表的指定数据存入B表中
应用类型:
备份数据表:
SELECT * INTO Table1_bak FROM Table1
创建新表
SELECT * INTO New_Table1 FROM Table1 WHERE 1<>1
SELECT TOP 0 * INTO New_Table1 FROM Table1
保存查询结果
SELECT Field1,Field2 INTO Result FROM Table1 WHERE ID>1000
创建新表并在新表中加入自动序号
一表有些表需要一个自动编号列来区别于各行
SELECT IDENTITY (INT,1,1) AS AutoId,* INTO new_Table1 FROM Table1
其中IDENTITY函数说明:
格式:
IDENTITY (<datatype> [seed,increment])
参数说明:
Datatype:数据类型,视记录数定类型,一般可以定INT型,具体可以参考SQL的极限参数
Seed:开始数值,即开始的基数,默认为1
Increment:增量,步长即数据间的间隔,默认为1
上面的SQL即表示,自动编号从1开始并每行加1
返回的表为:
AutoId | Field1 | Field2 |
1 | Hello | Joy |
2 | Hello | Tom |
3 | Hi | Lily |
4 | Hello | Lily |
注:
IDENTITY还可以在创建表时设置
格式:
IDENTITY ([seed, increment])
如:
创建表
CREATE TABLE Table1 (
AutoId int IDENTITY(1,1), 或 autoid int identity
Field1 nvarchar(30),
Field2 nvarchar(30)
)
修改表
ALTER TABLE Table1 ADD AutoId int IDENTITY (1,1)
在进行数据插入时应注意IDENTITY_INSERT这个属性的设置
当 SET IDENTITY_INSERT <table> ON 时,则不能进行隐式插入
如:
SET IDENTITY_INSERT Table1 ON
INSERT INTO Table1 SELECT (‘r1c1’,’r1c2’) --这样就会出错
必需使用:
INSERT INTO Table1 SELECT (1,’R1C1’,’R1C2’)
只能在SET IDENTITY_INSERT <table> OFF 时才允许隐式插入
如:
SET IDENTITY_INSERT Table OFF
必需使用:
INSERT INTO Table1 SELECT (‘r1c1’,’r1c2’)
否则
INSERT INTO Table1 SELECT (1,’R1C1’,’R1C2’) --这样就会出错
在使用隐式插入后可以用@@IDENTITY这个系统值来返回插入行的编号
INSERT INTO Table1 SELECT(‘R1C1’,’R1C2’)
返回表:
AutoID | Field1 | Field2 |
1 | R1C1 | R1C2 |
SELECT @@IDENTITY
返回值:
1
在应用程序中可以用以下方法做:
set recs=cnn.execute(“INSERT INTO Table1 SELECT(‘R1C1’,’R1C2’)”)
recordnum=cnn.execute(“SELECT @@IDENTITY”).fields(0).value
以上语句执行后recordnum的值将设置为最后一个自动编号
关联
用例:
Table1 | Table2 | |||||
ID | TF1 | VALUE1 | ID | TF2 | VALUE2 | |
1 | TFI1-1 | 10 | 5 | TFI2-1 | 11 | |
5 | TFI1-2 | 20 | 6 | TFI2-2 | 21 | |
2 | TFI1-3 | 30 | 3 | TFI2-3 | 31 | |
3 | TFI1-4 | 40 | 1 | TFI2-4 | 41 |
Table2
INNER JOIN
只显示两表一一对应的记录
SELECT * FROM Table1 INNER JOIN Table2 ON Table1.ID=Table2.ID ORDER BY Table1.ID
返回表:
ID | TF1 | VALUE1 | ID | TF2 | VALUE2 |
1 | TFI1-1 | 10 | 1 | TFI2-4 | 41 |
3 | TFI1-4 | 40 | 3 | TFI2-3 | 31 |
5 | TFI1-2 | 20 | 5 | TFI2-1 | 11 |
LEFT JOIN(LEFT OUTER JOIN)
显示左表所有记录与右表对应左表的记录,当在右表中无记录,则右表相应字段用NULL填充
SELECT * FROM Table1 LEFT JOIN Table2 ON Table1.ID=Table2.ID ORDER BY Table1.ID
返回表:
ID | TF1 | VALUE1 | ID | TF2 | VALUE2 |
1 | TFI1-1 | 10 | 1 | TFI2-4 | 41 |
2 | TFI1-3 | 30 | NULL | NULL | NULL |
3 | TFI1-4 | 40 | 3 | TFI2-3 | 31 |
5 | TFI1-2 | 20 | 5 | TFI2-1 | 11 |
RIGHT JOIN(LEFT OUTER JOIN)
显示右表所有记录与左表对应右表的记录,当在左表中无记录,则左表相应字段用NULL填充
SELECT * FROM Table1 LEFT JOIN Table2 ON Table1.ID=Table2.ID ORDER BY Table1.ID
返回表:
ID | TF1 | VALUE1 | ID | TF2 | VALUE2 |
NULL | NULL | NULL | 6 | TFI2-2 | 21 |
1 | TFI1-1 | 10 | 1 | TFI2-4 | 41 |
3 | TFI1-4 | 40 | 3 | TFI2-3 | 31 |
5 | TFI1-2 | 20 | 5 | TFI2-1 | 11 |
FULL JOIN(FULL OUTER JOIN)
显示左右两表所有记录,当左表无记录,则左表相应字段用NULL填充,当右表无记录则右表相关字段用NULL填充
SELECT * FROM Table1 LEFT JOIN Table2 ON Table1.ID=Table2.ID ORDER BY Table1.ID
返回表:
ID | TF1 | VALUE1 | ID | TF2 | VALUE2 |
1 | TFI1-1 | 10 | 1 | TFI2-4 | 41 |
2 | TFI1-3 | 30 | NULL | NULL | NULL |
3 | TFI1-4 | 40 | 3 | TFI2-3 | 31 |
5 | TFI1-2 | 20 | 5 | TFI2-1 | 11 |
NULL | NULL | NULL | 6 | TFI2-2 | 21 |
说明:
在进行多级关联的时候应该采用就近关联原则
如:
SELECT * FROM Table1 INNER JOIN Table2 INNER JOIN Table2-1 ON Table2.ID=Table2-1.ID ON Table1.ID=Table2.ID
即Table2与Table2-1关联
Table1与Table2关联
建意:
在写此类关联时,最好将基语句格式结构化
如:
SELECT *
FROM
Table1
INNER JOIN Table2
INNER JOIN Table2-1
ON Table2.ID=Table2-1.ID
ON Table1.ID=Table2.ID
WHERE
ID IN (1,2,3)
注:
在写完查询语句后,可以由“企业管理器”进行SQL语句的格式化,但这一过程出来的语句一定要进行测试,因为在他自动格式化时可能会把某些复杂的关系搞错
分组
GROUP BY
(没什么好说!!)
如:
SELECT A,B,SUM(D) FROM Table1 GROUP BY A,B ORDER BY A
注:
在进行GROUP BY 时应该注意GROUP BY 中字段的使用,
只要在同一查询语句中则所有未进行骤合操作的字段都需要被GROUP,
如上面的SQL中,字段A,与B都未被骤合,并字段A被排序,而字段D被骤合函数SUM进行汇总统计
因此字段A,B需要被GROUP 而D则不用
如:
SELECT A,B,SUM(D) FROM Table1 GROUP BY A,B,C ORDER BY C
在此查询中,虽然字段C没有被选择,但他被ORDER因此字段C也应该在GROUP的字段中
如:
SELECT A,B,SUM(D) FROM Table1 WHERE A IN (SELECT D FROM Table1 T1 WHERE NOT C IS NULL) GROUP BY A,B,C ORDER BY C
在此查询中字段A,B为选择字段,字段C为排序字段,但字段D虽然也在同一张表Table1中,但他在子查询中因此不用进行对D的GROUP
若要对聚合结果进行筛选则应该使用HAVING关键字,而不是WHERE关键字,
如:
SELECT A,B,SUM(D) FROM Table1 WHERE COUNT(*)>2 GROUP BY A,B ---这样将会出错,因为COUNT为一个聚合函数,在WHERE子句中不能对聚合函数进行筛选
应改为:
SELECT A,B,SUM(D) FROM Table1 GROUP BY A,B HAVING COUNT(*)>2
应用GROUP可以进行分类统计
相关的关键字为CUBE,ROLLUP但不建意使用这两个关键字,
在一般情况下,如果程序中的GRID有分类汇总功能,那相应的速度会比使用这两个关键字要快,
与这两个关键字一起使用的聚合函数为GROUPING(),即当进行项目分类汇总时GROUPING()将会返回1,反之则为0,为可以写统计标题时提供参考,
具体说明请参见《T-SQL》
具体实例在《SOMIC人力资源管理》中<部门人员汇总表>中有应用
条件
CASE WHEN
此组关键字的功能可以代替IF…THEN….ELSE或SELECT CASE
语法结构:
CASE [expression]
WHEN <condition> THEN result
[ELSE else_result ]
END
在查询中使用此语句时应尽量在END后加别名,
如:
SELECT [name],xtype,CASE WHEN xtype=’U’ THEN ‘用户表’ ELSE CASE WHEN xtype=’S’ THEN ‘系统
返回表:
name | xtype | 类型 |
syscolumns | S | 系统表 |
tabledefine | U | 用户表 |
详细用例请参考《纺织计件工资》中<人员-部门产量汇总表>
用此语句与SELECT用UNION联用能做行列换位
过程性语句应用
变量定义
在SQL中用户变量是以@打头的字串,系统变量用@@打头
如:
@i
@tmpStr
定义方法:
Declare @i int
Declare @tmpStr nvarchar(30)
在完成变量定义后最好进行初始设置,如
Set @i=0
Set tmpStr=’’
或
Select @i=0,@tmpStr=’’
在SQL中对变量的赋值应用SET或SELECT进行
游标定义
游标,可以将查询结果返回为游标类型
定义方法:
Declare cursor <CurName>
For <SQL SCRIPT>
如:
declare cursor GetName
for SELECT [name] FROM sysobjects
游标使用方法:
打开游标:
Open <CurName>
如:open GetName
检索游标:
Fetch [NEXT | PRIOR | FIRST | LAST] form <CurName> [into <valuename>…]
如:
Fetch next from GetName into @tmpName
当取值成功后,相应记录值会填充在@tmpName变量中,并@@FETCH_STATUS变量置为0,
若失败则@@FETCH_STATUS变量为-1
关闭游标
在使用完游标后关闭他,以便其他进程使用此游标
CLOSE <curname>
如:
Close GetName
删除游标
在使用完游标后,如不再需要应该删除已使用游标,
DEALLOCATE <curname>
如:
Deallocate GetName
- 常用SQL资料
- ] [数据库资料] SQL常用语句
- 常用资料
- 常用资料
- 常用资料
- SQL资料
- SQL [ 资料 ]
- SQL资料
- EBS系统管理常用SQL语句整理汇总(参考网上资料&其他人博客)
- 常用资料连接收藏
- MySql常用资料
- 编程常用资料
- c#常用的资料
- div+css常用资料
- 数据库常用资料
- 常用资料备份
- Web常用资料
- oracle常用资料
- Jakarta Commons:巧用类和组件1 (3)
- Oracle 常用技巧和脚本 选择自 yanlixin4csdn 的 Blog
- Asp.net(c#)实现多线程断点续传
- 结绳记事(提醒)
- Composition
- 常用SQL资料
- 七个经典心理题
- 空中英语(Success in friendship)
- CFML基础
- 高血压
- 新年了都,第一场雪
- 英语资源(不错 挺全)(转至魅族明的上古卷轴http://blog.csdn.net/mezzomin/)
- Oracle数据库的备份与恢复
- Oracle数据库中索引的维护