数据库ms SQL常用语句
truncate table test --删除表中的所有数据
alter table test alter column bb varchar(10) --修改表中字段的定义
alter table aa add constraint fk foreign key([aa]) references bb([aa]) --外键约束
alter table bb add constraint pr primary key(aa) --主键
exec sp_addtype hezhiwu,'varchar(11)','not null'
--创建一个用户定义数据类型，名为hezhiwu，基于varchar数据类型，该列不能为null
exec sp_addtype hhh,'int','not null'
sp_droptype hhh --删除此类型
create table abc
(
aa int not null primary key identity(1,1), --定义标识字段
bb char(4)
)
alter table table1113 add columncc int --增加字段columncc
alter table table1113 add constraint df default '我是默认值' for columnb --为某一个字段添加default约束
alter table table1113 drop column columnb --删除某一个字段，删除前要先删除约束
alter table table1113 drop constraint df --删除df的约束
数据类型smallint 表示-32768至32768，即相当于VB的int数据类型
update table1113 set columnd=columnd+1 where columnid=2
dbcc sqlperf(logspace)
sp_helpdb @dbname=zw
dbcc shrinkdatabase(zw) --压缩zw数据库
create table table1113d
(
aa int not null primary key clustered identity --clustered聚簇索引，nonclustered非聚簇索引，默认为聚簇索引
)
create table table1113e
(
aa char(20) constraint un unique --定义unique约束
)
alter table table1113e add constraint una unique(bb) --在字段bb中添加unique约束
alter table table1113e add constraint unn unique nonclustered(cc) --在字段bb中添加非聚簇unique约束
create default myname as '我是值' --创建默认值
exec sp_bindefault myname,'table.bb' --将默认值对象myname绑定到表table中的bb字段上
exec sp_unbindefault 'tableadf.bb' --删除表tableadf.bb列的默认值绑定
drop default mynameb --删除默认值对象，要把全部例绑定删除之后，才可以删除默认值对象
create rule mynamb as @aa>100 --创建规则
create rule aabc as @aa like 'abc%' --规则也可以使用通配符
create rule fkalf as @dskf in (1,3,5,7,9) --另一种创建规则的方法
exec sp_bindrule mynamb,'tablename.columnbb' --把规则绑定到表tablename的columnbb列上
exec sp_unbindrule 'tablename.columnbb' --解除绑定的规则
drop rule mynamb --删除规则mynamb对象
sp_helpindex @objname='gxa' --查看表gxa的索引
create unique index mynameaab on gxa(bb) --创建基于“表gxa中bb列”的唯一非聚集索引mynameaab
drop index gxa.mynameaab --删除表gxa中，对象名为mynameaab的索引，注意：gxa是表，mynameaab是索引对象，不是列
sp_helptext @objname='viewa' --查看视图viewa的创建语句，规则，存储过程，触发器等
---------创建加密视图--------------------
create view viewc
with encryption
as
select id from adfadf
----------------------------
create view viewname (列名) as select aaa+'-'+bbb from tablename --当select没有列名时，则可以这样创建视图
sp_rename viewname,newviewname --重命名视图名称，还可以重命名存储过程，索引等……
-----修改视图的定义-------
alter view view_name [(column[,...n]]
with encryption
as
select_statement
[with check option]
example:
alter view newviewname with encryption as select id from tablename
--------------------------
drop view 视图名 --删除视图
exec sp_addtype typename,'char(50)',null --添加自定义数据类型，允许为空
--------------SQL变量的使用-------------------------------
declare @var1 char(20),@var2 int /* 定义变量 */
set @var1='this a test' /* set赋值 */
select @var2=100 /* select赋值 */
select @var1,@var2 /* 输出变量 */
------------------------------------------------------
select distinct aa,bb from aa --删除重复的行，不是删除字段aa重复的记录，distinct要放在字段的最前面
select aa,bb into tablename from aa where aa='abc' --把查询结果保存为一个表
select 新列名=columnname + '连接' from hahaa --另一种写法
-----空值判断--------
is null
is not null
----------------------
-----------compute子句-----------------------------------------------------------------
select * from tablename compute sum(aa) --显示tablename的所有数据，并显示字段aa的总和
等同于
select * from tablename
select avg(aa) from tablename
select * from tablename where aa<10 compute avg(bb) --显示aa<10的所有数据和aa<10记录中，字段bb的平均值
------------------------------------------------------------------------------------------------------------
select * from sysobjects where xtype='u' --查询此数据库所有的表
-------------------------------------------------------------
use master
select * from sysdatabases --查询所有的数据库
-------------------------------------------------------------
begin ... end --如果while等语句的后面接的不是一条语句时，就要用begin ... end
rand() --返回0到1之间的随机浮点数
str --将数字转化成字符串，如：str(123)
char(num) --返回num对应的ASCII码，如：char(65)返回大写A
substring(expression,start,length)
--从express的第start个字符处返回length个字符,如：substring('abcdef',2,3) 返回bcd
len(string_expression) --返回字符串的个数，不包含尾随空格
SELECT REPLACE('abcdefghicde','cde','xxx') --用xxx代替cde，结果返回abxxxfghixxx
replicate ( character_expression , integer_expression )
--以指定的次数重复字符表达式，如： replicate('ab',4)返回abababab
reverse('字符串') --返回反转字符串'串符字'
space(n) --返回n个重复的空格，其中n为整数
STUFF ( character_expression , start , length , character_expression )
--删除指定长度的字符并在指定的起始点插入另一组字符。
--如：print stuff('abcdefg',2,5,'XXX') 返回aXXXg
UPPER/LOWER --转化成大写字母，转化成小写字母
col_name(table_id,column_id) --返回指定表及列标识对应的列名
col_length('table_name','column_name') --返回指定列的定义长度
db_id(['database_name']) --返回数据库标识
db_name(['database_name']) --返回数据库名称
host_name() --返回主机名
user_name(user_id) --返回用户名称
getdate() --返回系统当前的日期和时间
dateadd(datepart,number,date)
--返回date值加上datepart和number参数指定的时间间隔
--select dateadd(d,1,'2006-11-16') 返回2006-11-17
datediff(datepart,date1,date2)
--返回date1和date2的时间间隔
--datediff(d,'2006-11-16','2007-11-16') 返回365
datename(datepart,date)
--返回日期中指定部分的对应字符串
--ddatename(d,'2006-11-16')返回16，其中16是字符串型
datepart(datepart,date)
--返回日期中指定部分的对应字符串
--datepart(d,'2006-11-16')返回16，其中16是数字型
--------------------------------------------------------------------------
declare @uid varchar(20)
declare @num int
select @uid=uid,@num=num from taa where uid='idname'
--把uid为idname的字段uid和num记录赋值给@uid和@num变量
-------------------------------------------------------------------------
-------------function函数的定义--------------------------------------------------------------
create function mynameab(@vara int) returns varchar(20)
begin
declare @stra varchar(20)
set @stra=''
if @vara<10
begin
set @stra='参数小于10'
end
else
begin
set @stra='参数大于或等于10'
end
return @stra
end
print dbo.mynameab(82)
------------------------------------------------------------------------------------
**************************存储过程procedure***************************************
create proc pa as select * from proca --定义无参数的存储过程
exec pa --执行存储过程
------------------------创建带参数的存储过程--------------------------------------------
create proc pbb
(
@num int,
@str varchar(20),
@uid int
)
as
select * from proca where bb<@num and aa like '%'+@str+'%' and uid<@uid
exec pbb 50,'adf',100 --执行带参数的存储过程，其中参数一一对应as之前定义的参数，不是SQL语句定义时的参数
------------------------------------------------------------------------------------------------------------
-------------output关键字的用法----------------------------------------------------------------------------------
create proc pdd
(
@a int,
@b int,
@c int output
)
as
select @c=@a+@b+5
declare @cc int
exec pdd 2,3,@cc output --注意@cc后面有output关键字
print @cc --返回10
-------------------------------------------------------------------------------------------------
--------------修改存储过程alter proc--------------------------------------
alter proc pdd
(
@a int,
@b int
)
as
select @a+@b
-------------------------------------------------------------------------
**************************************************************************************************
----------------定义表别名---------------------------------------------
select a.text,a.id from syscomments a --把syscomments定义别名a
------------------------------------------------------------------------
-----------------查看存储过程的源代码----------------------------------------------
select sysobjects.id , syscomments.text
from sysobjects inner join syscomments on sysobjects.id=syscomments.id where sysobjects.type='p' and sysobjects.name='pdd'
go
sp_helptext pdd --第二种方式
---------------------------------------------------------------
grant execute on pdd to gh --将执行该存储过程pdd的权限授予gh角色（用户） pdd存储过程名，gh角色、用户名
*****************************触发器trigger****************************************************
create trigger tria on aabb
with encryption
for delete,insert,update
as
print '表aabb执行了delete或insert或update操作!'
------------------------------------------------------------------------------
create trigger trib on aabb
for update
as
if update(aa)
print '表aabb的aa列被更新'
---------------------------------------------------------------------------------
当向表testa插入记录时，触发器被触发，在表testb中也插入刚才testa表中插入的记录
并在testb表中的dd中插入时间
create table testa
(
uid varchar(20),
con varchar(20)
)
go
create table testb
(
uid varchar(20),
con varchar(20),
dd varchar(20)
)
go
create trigger testatrig on testa for insert
as
declare @aa varchar(20)
declare @bb varchar(20)
select @aa=uid,@bb=con from testa
insert into testb values(@aa,@bb,getdate())
-----------------------------------------------------------------------------
create trigger nodel on testb for delete
as
print '对不起，不可以删除testb表中的数据'
rollback transaction --回滚
--注意：truncate不能触发，即是用truncate table仍然可以删除记录
-------------------------------------------------------------------------------
alter table tt79 disable trigger tt79tri --在tt79表中，禁用(disable)触发器tt79tri
alter table tt79 enable trigger tt79tri --在tt79表中，启用（enable）触发器tt79tri
**********************************************************************************************
----------------------------------------------------------------------------------------------
select * from aa where column > any (select num from bb)
--(在aa表中，column字段的值) 大于 (select num from bb 结果集中最小的值) 即可进入结果集
--如果是all即大于最大的值，即和any相反
select * from aa where column > any (select num from bb)
等价于
select * from aa where column > (select min(num) from bb)
select * from aa where column > all (select num from bb)
等价于
select * from aa where column > (select max(num) from bb)
in相当于 =any
select * from anya where aa =any (select aa from anyb) --可以把=any换成in
--------------------------------------------------------------------------------------------------
************************ check约束可以用like和通配符 *********************************************************
------------------------------------------------------------------------------
create table aabbcc
(
aa int,
bb varchar(20),
constraint dfl check(bb is null) --bb列一定要为null
)
insert into aabbcc(aa,bb) values(12,'fadf') --不能插入，因为bb不为null
insert into aabbcc(aa) values(12) --能插入，因为bb为null
-------------------------------------------------------------------------------
create table aabbcc
(
aa int,
bb varchar(20),
constraint dfl check(bb like 'abc%') --bb列要abc开头的字母才能插入
)
insert into aabbcc(bb) values('aabb') --不能插入，因为不是abc开头的字符串
insert into aabbcc(bb) values('abcany') --能插入，因为以abc开头
-----------------------------------------------------------------------------------------------
create table aabbcc
(
aa int,
bb varchar(20),
constraint dfl check(bb like '[a-Z][a-Z][0-9][0-9]') --四个字符，前二位为字母，后二位为数字，共四个字符
)
insert into aabbcc(aa,bb) values(123,'Ad12') --能插入
insert into aabbcc(aa,bb) values(123,'A812') --不能插入
insert into aabbcc(aa,bb) values(123,'Ad122') --不能插入
--------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------
create table aabbcc
(
mm varchar(20),
constraint mail check(mm like '%@%') --字符串必须含有@才能插入
)
insert into aabbcc(mm) values('abcd') --不能插入，与check约束冲突
insert into aabbcc(mm) values('adf@') --能插入
insert into aabbcc(mm) values('@') --能插入
insert into aabbcc(mm) values('@fad') --能插入
---------------------------------------------------------------------------------
-------------------------------------------------------------------------------------
create table aabbcc
(
mm int,
constraint nuk check(mm between 10 and 20) --只能插入大于10，小于20的数字
)
insert into aabbcc(mm) values(12) --能插入
insert into aabbcc(mm) values(9) --不能插入
insert into aabbcc(mm) values(20) --能插入
insert into aabbcc(mm) values(21) --不能插入
--------------------------------------------------------------------------------------
-------------------------------------------------------
create table aa (aa int) --创建表
insert into aa(aa) values(10) --插入一条记录
alter table aa add constraint con check(aa>50) --不能添加验证的约束，该句不能成功执行
alter table aa with nocheck add constraint con check(aa>50) --可以添加未经验证的约束
insert into aa(aa) values(10) --些时不能插入小于或等于50的数据
alter table aa nocheck constraint con --禁用约束nocheck
insert into aa(aa) values(10) --此时可以插入记录，因为禁用nocheck了约束
alter table aa check constraint con --启用check约束
insert into aa(aa) values(10) --因为启用了约束，所以不能插入
---------------------------------------------------------------------------------------
**************************************************************************************************
默认值可以这样写：default('aaa')或default(getdate())
----------------------------------------------------------------
对计算列使用表达式
下例显示如何使用表达式 ((low + high)/2) 计算 myavg 计算列。
CREATE TABLE mytable
(
low int,
high int,
myavg AS (low + high)/2 --计算列
)
----------------------------------------------------------------------
DATALENGTH 返回任何表达式所占用的字节数。如：print datalength(1.1)