sql & t-Sql 操作

sql和t-sql语句学习 本文的部分内容从网上查找得到的，并给出了原文地址。 欠你一刀
一 常用函数(function)

1 数据类型转换函数

CAST ( expression AS data_type ) -- 将某种数据类型的表达式显式转换为另一种数据类型
CONVERT (data_type[(length)], expression [, style])-- 将某种数据类型的表达式显式转换为另一种数据类型

2 统计函数

AVG -- 返回组中值的平均值。空值将被忽略。
COUNT--返回组中项目的数量。
MAX--返回表达式的最大值。
MIN--返回表达式的最小值。
SUM--返回表达式中所有值的和，或只返回 DISTINCT 值。SUM 只能用于数字列。空值将被忽略。

STDEV()
　　--STDEV()函数返回表达式中所有数据的标准差
　　--STDEVP()
　　--STDEVP()函数返回总体标准差　

　　VAR()
　　--VAR()函数返回表达式中所有值的统计变异数　　

　　VARP()
　　--VARP()函数返回总体变异数　

3 数学函数

(1) 取近似值函数

SQRT( float_expression )--返回给定表达式的平方根。
CEILING( numeric_expression )--返回大于或等于所给数字表达式的最小整数。
FLOOR( numeric_expression )--返回小于或等于所给数字表达式的最大整数。
ROUND(numeric_expression , length)--返回数字表达式并四舍五入为指定的长度或精度。
SIGN( numeric_expression )--返回给定表达式的正 (+1)、零 (0) 或负 (-1) 号。
ABS ( numeric_expression )--返回给定数字表达式的绝对值。
PI(), 返回 PI 的常量值。
RAND(), RAND( seed )返回 0 到1 之间的随机float 值。

(2)三角函数

SIN(float_expression)--返回以弧度表示的角的正弦
COS(float_expression)--返回以弧度表示的角的余弦
TAN(float_expression)--返回以弧度表示的角的正切
COT(float_expression)--返回以弧度表示的角的余切

(3)反三角函数

ASIN(float_expression)--返回正弦是FLOAT值的以弧度表示的角
ACOS(float_expression)--返回余弦是FLOAT值的以弧度表示的角
ATAN(float_expression)--返回正切是FLOAT值的以弧度表示的角
ATAN2(float_expression1,float_expression2)--返回正切是float_expression1/float_expres-sion2的以弧度表示的角
DEGREES(numeric_expression)--当给出以弧度为单位的角度时，返回相应的以度数为单位的角度。
RADIANS(numeric_expression)------对于在数字表达式中输入的度数值返回弧度值。
EXP(float_expression)--返回表达式的指数值
LOG(float_expression)--返回表达式的自然对数值
LOG10(float_expression)--返回表达式的以10为底的对数值
SQRT(float_expression)--返回表达式的平方根

4 字符串函数

ASCII ( character_expression )--返回字符表达式最左端字符的 ASCII 代码值。
CHAR ( integer_expression )--将 int ASCII 代码转换为字符的字符串函数。
LOWER ( character_expression )--将大写字符数据转换为小写字符数据后返回字符表达式。
UPPER ( character_expression )--返回将小写字符数据转换为大写的字符表达式。
STR ( float_expression [ , length [ , decimal ] ] ) --由数字数据转换来的字符数据。
LTRIM( character_expression )--删除起始空格后返回字符表达式。
RTRIM ( character_expression )--截断所有尾随空格后返回一个字符串。
LEFT ( character_expression , integer_expression ) --返回从字符串左边开始指定个数的字符。
RIGHT ( character_expression , integer_expression ) --返回字符串中从右边开始指定个数的 integer_expression 字符。
SUBSTRING ( expression , start , length )--截取字符串
CHARINDEX ( expression1 , expression2 [ , start_location ] ) --返回字符串中指定表达式的起始位置，没有返回0
PATINDEX ( '%pattern%' , expression ) -- 返回指定表达式中某模式第一次出现的起始位置；如果在全部有效的文本和字符数据类型中没有找到该模式，则返回零。
REPLICATE ( character_expression , integer_expression )-- 以指定的次数重复字符表达式。
REVERSE ( character_expression )-- 返回字符表达式的反转。
REPLACE ( 'string_expression1' , 'string_expression2' , 'string_expression3' )--用第三个表达式替换第一个字符串表达式中出现的所有第二个给定字符串表达式。
STUFF ( character_expression , start , length , character_expression )--删除指定长度的字符并在指定的起始点插入另一组字符。
SPACE ( integer_expression )-- 返回由重复的空格组成的字符串。

5 日期函数

　　DAY()--函数返回date_expression中的日期值
　　MONTH()--函数返回date_expression中的月份值
　　YEAR()--函数返回date_expression中的年份值
　　DATEADD( datepart , number, date )--函数返回指定日期date加上指定的额外日期间隔number产生的新日期
　　DATEDIFF( datepart , startdate , enddate )--函数返回两个指定日期在datepart方面的不同之处
　　DATENAME(datepart , date )------函数以字符串的形式返回日期的指定部分
DATEPART( datepart , date )--函数以整数值的形式返回日期的指定部分
GETDATE()------函数以DATETIME的缺省格式返回系统当前的日期和时间　　

6 系统函数

APP_NAME()------函数返回当前执行的应用程序的名称
COALESCE()-----函数返回众多表达式中第一个非NULL表达式的值
COL_LENGTH ( 'table' , 'column' ) ----函数返回表中指定字段的长度值
COL_NAME ( table_id , column_id )----返回数据库列的名称，该列具有相应的表标识号和列标识号。
DATALENGTH()-----函数返回数据表达式的数据的实际长度
DB_ID ( [ 'database_name' ] ) ------函数返回数据库的编号
DB_NAME(database_id)------函数返回数据库的名称
HOST_ID()-----函数返回服务器端计算机的名称
HOST_NAME()-----函数返回服务器端计算机的名称
IDENTITY ( data_type [ , seed , increment ] ) AS column_name --IDENTITY()函数只在SELECTINTO语句中使用用于插入一个identitycolumn列到新表中
ISDATE()----函数判断所给定的表达式是否为合理日期
ISNULL ( check_expression , replacement_value ) --函数将表达式中的NULL值用指定值替换
ISNUMERIC()----函数判断所给定的表达式是否为合理的数值
NEWID()----函数返回一个UNIQUEIDENTIFIER类型的数值
NULLIF ( expression , expression )--NULLIF函数在expression1与expression2相等时返回NULL值若不相等时则返回xpression1的值

[来源： http://stevieliu.blogchina.com/stevieliu/4720568.html]
[参考《SQL Server联机丛书》，略有修改]

二 SQL Server中各个系统表的作用

sysaltfiles 主数据库 保存数据库的文件
syscharsets 主数据库 字符集与排序顺序
sysconfigures 主数据库 配置选项
syscurconfigs 主数据库 当前配置选项
sysdatabases 主数据库 服务器中的数据库
syslanguages 主数据库 语言
syslogins 主数据库 登陆帐号信息
sysoledbusers 主数据库 链接服务器登陆信息
sysprocesses 主数据库 进程
sysremotelogins主数据库 远程登录帐号
syscolumns 每个数据库 列
sysconstrains 每个数据库 限制
sysfilegroups 每个数据库 文件组
sysfiles 每个数据库 文件
sysforeignkeys 每个数据库 外部关键字
sysindexs 每个数据库 索引
sysmenbers 每个数据库 角色成员
sysobjects 每个数据库 所有数据库对象
syspermissions 每个数据库 权限
systypes 每个数据库 用户定义数据类型
sysusers 每个数据库 用户

三 Transact_SQL

1 语法

语 句 功 能
(1) 数据操作
SELECT 从数据库表中检索数据行和列
INSERT 向数据库表添加新数据行
DELETE 从数据库表中删除数据行
UPDATE 更新数据库表中的数据
(2)数据定义
CREATE TABLE 创建一个数据库表
DROP TABLE 从数据库中删除表
ALTER TABLE 修改数据库表结构
CREATE VIEW 创建一个视图
DROP VIEW 从数据库中删除视图
CREATE INDEX 为数据库表创建一个索引
DROP INDEX 从数据库中删除索引
CREATE PROCEDURE 创建一个存储过程
DROP PROCEDURE 从数据库中删除存储过程
CREATE TRIGGER 创建一个触发器
DROP TRIGGER 从数据库中删除触发器
CREATE SCHEMA 向数据库添加一个新模式
DROP SCHEMA 从数据库中删除一个模式
CREATE DOMAIN 创建一个数据值域
ALTER DOMAIN 改变域定义
DROP DOMAIN 从数据库中删除一个域
(3)数据控制
GRANT 授予用户访问权限
DENY 拒绝用户访问
REVOKE 解除用户访问权限
(4)事务控制
COMMIT 结束当前事务
ROLLBACK 中止当前事务
SET TRANSACTION 定义当前事务数据访问特征
(5)程序化SQL
DECLARE 为查询设定游标
EXPLAN 为查询描述数据访问计划
OPEN 检索查询结果打开一个游标
FETCH 检索一行查询结果
CLOSE 关闭游标
PREPARE 为动态执行准备SQL 语句
EXECUTE 动态地执行SQL 语句
DESCRIBE 描述准备好的查询

 (6) 局部变量

declare @id char(10)
--set @id = '10010001'
select @id = '10010001'

(7)全局变量

---必须以@@开头

(8) IF ELSE

--举例：

declare @x int @y int @z int
select @x = 1 @y = 2 @z=3
if @x > @y
print 'x > y' --打印字符串'x > y'
else if @y > @z
print 'y > z'
else print 'z > y'

(9) CASE

--举例：

use pangu
update employee
set e_wage =
case
when job_level = ’1’ then e_wage*1.08
when job_level = ’2’ then e_wage*1.07
when job_level = ’3’ then e_wage*1.06
else e_wage*1.05
end

(10) WHILE CONTINUE BREAK

--举例：

declare @x int @y int @c int
select @x = 1 @y=1
while @x < 3
begin
print @x --打印变量x 的值
while @y < 3
begin
select @c = 100*@x + @y
print @c --打印变量c 的值
select @y = @y + 1
end
select @x = @x + 1
select @y = 1
end

(11) WAITFOR

--举例：

--例 等待1 小时2 分零3 秒后才执行SELECT 语句
waitfor delay ’01:02:03’
select * from employee
--例 等到晚上11 点零8 分后才执行SELECT 语句
waitfor time ’23:08:00’
select * from employee

2 常见用法举例

(1) SELECT

select *(列名) from table_name(表名) where column_name operator value
ex:(宿主)
select * from stock_information where stockid = str(nid)
stockname = 'str_name'
stockname like '% find this %'
stockname like '[a-zA-Z]%' --------- ([]指定值的范围)
stockname like '[^F-M]%' --------- (^排除指定范围)
--------- 只能在使用like关键字的where子句中使用通配符)
or stockpath = 'stock_path'
or stocknumber < 1000
and stockindex = 24
not stocksex = 'man'
stocknumber between 20 and 100
stocknumber in(10,20,30)
order by stockid desc(asc) --------- 排序，desc-降序，asc-升序
order by 1,2 --------- by列号
stockname = (select stockname from stock_information where stockid = 4)
--------- 子查询
--------- 除非能确保内层select只返回一个行的值，
--------- 否则应在外层where子句中用一个in限定符
select distinct column_name form table_name --------- distinct指定检索独有的列值，不重复
select stocknumber ,"stocknumber + 10" = stocknumber + 10 from table_name
select stockname , "stocknumber" = count(*) from table_name group by stockname
--------- group by 将表按行分组,指定列中有相同的值
having count(*) = 2 --------- having选定指定的组

select *
from table1, table2
where table1.id *= table2.id -------- 左外部连接，table1中有的而table2中没有得以null表示
table1.id =* table2.id -------- 右外部连接

select stockname from table1
union [all] ----- union合并查询结果集，all-保留重复行
select stockname from table2

(2) insert

insert into table_name (Stock_name,Stock_number) value ("xxx","xxxx")
value (select Stockname , Stocknumber from Stock_table2)---value为select语句

(3) update

update table_name set Stockname = "xxx" [where Stockid = 3]
Stockname = default
Stockname = null
Stocknumber = Stockname + 4

(4) delete

delete from table_name where Stockid = 3
truncate table_name ----------- 删除表中所有行，仍保持表的完整性
drop table table_name --------------- 完全删除表

(5) alter table

alter table database.owner.table_name add column_name char(2) null ..
sp_help table_name ---- 显示表已有特征
create table table_name (name char(20), age smallint, lname varchar(30))
insert into table_name select ----- 实现删除列的方法（创建新表）
alter table table_name drop constraint Stockname_default ---- 删除Stockname的default约束

四 MS-SQL数据库开发常用汇总

1 按姓氏笔画排序

Select * From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as

2 数据库加密

select encrypt('原始密码')
select pwdencrypt('原始密码')
select pwdcompare('原始密码','加密后密码') = 1--相同；否则不相同 encrypt('原始密码')
select pwdencrypt('原始密码')
select pwdcompare('原始密码','加密后密码') = 1--相同；否则不相同

3 取回表中字段

declare @list varchar(1000),@sql nvarchar(1000)
select @list=@list+','+b.name from sysobjects a,syscolumns b where a.id=b.id and a.name='表A'
set @sql='select '+right(@list,len(@list)-1)+' from 表A'
exec (@sql)

4 查看硬盘分区

EXEC master..xp_fixeddrives

5 比较A,B表是否相等
if (select checksum_agg(binary_checksum(*)) from A)
=
(select checksum_agg(binary_checksum(*)) from B)
print '相等'
else
print '不相等'

6 杀掉所有的事件探察器进程
DECLARE hcforeach CURSOR GLOBAL FOR SELECT 'kill '+RTRIM(spid)
FROM master.dbo.sysprocesses
WHERE program_name IN('SQL profiler',N'SQL 事件探查器')
EXEC sp_msforeach_worker '?'

7 记录搜索

(1) 开头到N条记录

Select Top N * From 表

(2) N到M条记录(要有主索引ID)

Select Top M-N * From 表 Where ID in (Select Top M ID From 表) Order by ID Desc

(3)N到结尾记录

Select Top N * From 表 Order by ID Desc

8 如何修改数据库的名称

sp_renamedb 'old_name', 'new_name'

9 获取当前数据库中的所有用户表

select Name from sysobjects where xtype='u' and status>=0

或者：

select * from information_schema.tables

10 获取某一个表的所有字段

select name from syscolumns where id=object_id('表名')

11 查看与某一个表相关的视图、存储过程、函数

select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like '%表名%'

12 查看当前数据库中所有存储过程

select name as 存储过程名称 from sysobjects where xtype='P'

13 查询用户创建的所有数据库

select * from master..sysdatabases D
where sid not in(select sid from master..syslogins where name='sa')

或者

select dbid, name AS DB_NAME from master..sysdatabases where sid ＜> 0x01

14 查询某一个表的字段和数据类型

select column_name,data_type from information_schema.columns
where table_name = '表名'

15 判断一个表是否存在

if exists(select 1 from sysobjects where name='要判断的表名' and xtype='U')
print '在'
else
print '不在'

或者

if objectproperty(object_id('要判断的表名'),'isusertable') is null
print '无此表'
else
print '有此表'

16 在存储过程中删除表的列

http://blog.csdn.net/scucj/archive/2006/07/14/919525.aspx

17 创建一个表和两个字段，并指定其中一个字段为自增的关键字

CREATE TABLE '+ @TABLENAME + ' (tableID BigInt identity(1,1) primary key,myUserID BigInt)'