数据库ms SQL常用语句

 

数据库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 ------------------------------------------------------------------------------------------------------------


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         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

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)