sqlserver 基础知识大整理（强烈推荐之二）

        SQL-Structured Query Language
       
-----------------------------------------------------------------------------------------------
select user                                     --查询当前用户

select host_name()    --查询服务器的主机名称

create table dept
(did int primary key,
dname varchar(10))

drop table info
insert into dept    --结果集联合插入数据
select '1001','市场部'
union select '1002','学术部'
union select '1003','人事部'
union select '1004','公关部'

select * from info

create table job
(jid int primary key,
jname varchar(10))

insert into job    
select '2001','工人'
union select '2002','技术员'
union select '2003','领班'
union select '2004','管理员'

create table emp    --表里的on update与on delete为级连更新和级连删除  
(eid int primary key,    
ename varchar(10),
dno int references dept(did) on delete cascade on update no action,
jno int references job(jid) on delete no action on update cascade)

select * from emp where eid like '[0-8][0-10][0-8][0-12]'--这是模糊查询，[]里是指范围，[0-12]表示
      --可取范围是0，1，2；[0-3]指可取范围是0，1，2，3；like前加上not表示不在此范围内

create table info
(item varchar(10),
color varchar(10),
num int)

insert into info
select 'table','blue',124
union select 'table','red',223
union select 'chair','blue',101
union select 'chair','red',210
union select 'table','blue',226

select item,color,max(num) as allsum from info group by item,color --这条语句中group by子句中如没有出现color则出错，
--因为单行和聚合不能同时出现，除非依据选择列表中所有的单行进行分组
--当选择列表中存在单行和聚合时，除聚合外的单行必须出现在group by 后
--上述语句结果集分组依据：当item,color同时相同时才能分在同一个组里

select item,color,sum(num) as allsum from info group by item,color with CUBE
--cube 相当于一个统计，做交叉表，它在group by子句中指定，该语句的选择列表应包含维度列以及聚合函数表达式
--维度列是指group by后面依据分组的单行，选择列表为select后面的列.

select item,color,sum(num) as allsum from info group by item,color with ROLLUP
--另一种统计方法，其中group by后面第一列为统计列，不会按照其他的列进行分组统计，这是ROLLUP与CUBE统计的区别

if(EXISTS(select * from info where num=999)) --EXISTS 关键字引入一个子查询时，就相当于进行一次存在测试,

print 'ok'     --在 EXISTS 前加上 NOT 表示不存在的判定

create table goods1
(gid int ,
num int,
price numeric(8,3),
total as num*price)                             --计算列的表示方法。计算列是自动进行计算的，不需要出现在选择列表中，不用给它赋值

select *from info where num>3
order by item      
COMPUTE sum(num),count(num) by item             --COMPUTE子句生成合计作为附加的汇总列出现在结果集的最后。
      
--当COMPUTE后不带 by 子句时，select语句有两个结果集，第一个结果集是包含选择列表信息的所有明细行。的二个结果集有一行，其中包含COMPUTE子句中所指定的聚合函数的合计
--当COMPUTE后带 by 子句时（实际上就是添加计算依据列表），计算依据列表一定要与排序列表相匹配。计算依据列表select条件的 每个组 都有两个结果集
--每组第一个结果集是明细行集，包含该组选择列表的信息；每组第二个结果集只有一行，包含该组的COMPUTE子句中所指定的聚合函数的小计

create table station
(sid int primary key,
sname varchar(10))

create table line
(lid int primary key,
lname varchar(10),
in_sid int references station(sid),
out_sid int references station(sid))

alter table line add constraint ck check (in_sid<>out_sid)    --防止In与Out重复而做的约束

insert into station values(1001,'西安')
insert into station values(1002,'太原')
insert into station values(1003,'北京')
insert into station values(1004,'郑州')

insert into line values(1,'东线',1001,1002)
insert into line values(2,'南线',1002,1001)
insert into line values(3,'西线',1003,1004)
insert into line values(4,'北线',1004,1002)

drop table line
select *from line
select *from station

select line.lid,line.lname,s1.sname as in_sid,s2.sname as out_sid from line,
station s1,station s2 where line.in_sid=s1.sid and line.out_sid=s2.sid
--"station s1"与"station s2"为给station 表起别名

select sid,CASE sname                            --CASE 语句后要带上列名，多列的情况列名用逗号隔开,CASE 语句不改变数据库的物理结构，只改变显示方法
when '西安' then '长安'
when '郑州' then '开封'
else '不知道'
END as sname from station

select sid,CASE      --CASE 语句的另一种写法，结果同上
when sname='西安' then '长安'
when sname='郑州' then '开封'
else '不知道'
END as sname from station

create table shift
(wno int primary key,
sid int,
ex_num money,
ex_date datetime default getdate()
)
alter table shift add constraint ck_shift check(sid>=1 and sid<=4)

insert into shift values(1001,1,300,default)
insert into shift values(1002,2,100,default)
insert into shift values(1003,3,200,default)
insert into shift values(1004,4,340,default)
insert into shift values(1005,1,210,default)
insert into shift values(1006,2,320,default)

select sum(CASE sid when 1 then ex_num else 0 END) as s1,
sum(CASE sid when 2 then ex_num else 0 END) as s2,
sum(CASE sid when 3 then ex_num else 0 END) as s3,
sum(CASE sid when 4 then ex_num else 0 END) as s4
into newtab from shift     
       --用CASE语句显示shift表中不同流水号的ex_num的统计
select *from newtab

declare @a int,@b int     --定义变量a,b
set @a=10      --给变量赋值 
set @b=80     
set @a=@a+@b      --进行计算 
print @a      --在屏幕上输出计算后变量a的值

declare @person table(pid int,    --定义特殊的table型变量 person的方法。table型变量类似于C语言中的结构体。
pname varchar(10),age int)    --但执行时要连同它的定义一起选中执行

insert into @person values(1001,'accp',23)
insert into @person values(1002,'accp1',3)
insert into @person values(1003,'accp2',23)
select *from @person

create table #tab      --在创建表时在表名前加上一个"#"以创建局部临时表。一旦局部临时表与服务器脱离连接则不会存在。
(a int primary key,             --它可分为全局与局部的临时表，在创建表时在表名前加上两个"#"则可创建全局临时表，
b int)               --全局临时表与所有连接均断开后则不存在。
--在临时表中可以使用主键约束，但外键约束不起作用！在临时表中创建的Check约束起作用 

 
select object_id('info1')                         --获取数据库对象的唯一ID，用object_id('数据库对象名')函数。
--如果该对象存在则返回代表该对象的唯一标识符，否则返回NULL

select name from syscolumns where id=object_id('info') --查询数据库中指定的表有多少字段

select name from sysobjects where xtype='U'       --查询数据库中有多少张用户表

waitfor delay '00:00:03' select *from info        --到指定延迟后执行SQL语句

waitfor time '15:48:30' select *from info         --到指定时间执行SQL语句

USE master       --用While循环计算1到100的和     
declare @sum int,@i int
set @sum=0
set @i=0
while(@i<=100)
BEGIN
set @sum=@sum+@i
select @i=@i+1
END
print @sum
GO        --GO关键字标志着批处理的结束，用批处理可以减轻数据库服务器的负担

While(select AVG(price) from goods)<4000   --While循环与if语句嵌套的经典举例（解决货物涨价的基本控制语句）
BEGIN
 update goods set price=price*2
 if(select max(price) from goods)>3000
 break
 else
 continue
END

--类型转换函数有cast()与convert()两种。

select cast(1234 as varchar(10))                  --把int型数据转换成varchar类型。目标数据类型与源数据类型必须是SQL所支持的类型

select convert(varchar(10),1234)    --效果同上，用convert进行转换

create table emp(eid int primary key,
ename varchar(10),pwd varbinary(20))    --varbinary数据类型是用来设置加密数据的类型

insert into emp values(1001,'rose',cast('2004/3/27' as varbinary(20)))

select * from emp

select eid,ename,cast(pwd as varchar(10)) as pwd from emp

--子查询：当子查询的结果唯一的时候，可以使用比较运算符，也可以使用集合运算符，
--当查询结果不唯一时，能且只能使用集合运算符。
--关系运算符有:>,<,<=,>=,=,<>
--集合运算符有:IN,NOT IN

select count(distinct gid)  from goods            --SQL中去除重复记录用DISTINCT去除

SELECT 语句                                      
UNION [ALL]
SELECT 语句

--多条语句结果集用UNION关键字合并,合并后的结果不重复。
--要想不删除重复记录，用 ALL 关键字
--其中每个SELET语句必须具有相同的结构，列次序，数目，类型要相同。

SELECT empno,ename,sal from emp
 UNION
SELECT empno,ename,sal from emp1