SQLServer基础语法实例应用（三）

--> 测试数据：[a]
if object_id('[a]') is not null drop table [a]
go
create table [a]([ID] int,[Name] varchar(6))
insert [a]
select 1,'张三' union all
select 2,'李四' union all
select 3,'王五' union all
select 4,'赵六' union all
select 5,'孙七'
-->查询语句（按姓氏笔画排序）
 
select * from a Order By [Name] Collate Chinese_PRC_Stroke_ci_as
 
/*
ID          Name
----------- ------
3           王五
5           孙七
1           张三
2           李四
4           赵六
 
(5 行受影响)
*/
 
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
 
/*
drive MB 可用空间
----- -----------
C     168
D     130379
E     57714
 
(3 行受影响)
 
*/
 
5.比较A,B表是否相等:

--> 测试数据：[a]
if object_id('[a]') is not null drop table [a]
go
create table [a]([ID] int,[Name] varchar(6))
insert [a]
select 1,'张三' union all
select 2,'李四' union all
select 3,'王五' union all
select 4,'赵六' union all
select 5,'孙七'
-->测试数据[b]
select * into [b] from [a]
 
if (select checksum_agg(binary_checksum(*)) from A)
     =
    (select checksum_agg(binary_checksum(*)) from B)
print '相等'
else
print '不相等'
/*
相等
*/

select name from syscolumns where id=object_id('a')
select name from syscolumns where id in (select id from sysobjects where type = 'u' and name = 'a')
 
/*
name
----------------------------------
ID
Name
 
(2 行受影响)
 
name
-----------------------------------
ID
Name
 
(2 行受影响)
*/