（自留）常用sql语句样例——sqlserver2008

--建表
--create table ENROLLS
--(
-- SNO decimal(6, 0) not null,
-- CNO nchar(5) not null unique,
-- GRADE int,
-- primary key(SNO, CNO),
-- foreign key(SNO) references STUDENTS(SNO),
-- foreign key(CNO) references COURSES(CNO),
-- check ((GRADE is null) or (GRADE between 0 and 100))
--)


--按默认列插入
--insert into STUDENTS values(990027, '胡伟', 22, '男', '湖南')


--指定列插入
--insert into COURSES(CNO, CNAME, CREDIT) values('C1', '数据库', 3)


--删除
--delete from TEACHING where TNO = 2133
--truncate table ENROLLS   --清数据不清结构
--drop table ...
--drop database ..


--更新
--update TEACHING set TNO = 1420 where CNO = 'C1'


--选择前几，前%几的数据
--select top 3 * from TEACHING
--select top 50 percent * from TEACHING


--通配符%
--select * from TEACHING where TNO like '1%'
--select * from TEACHING where TNO like '%1'
--select * from TEACHING where TNO like '%1%'
--select * from TEACHING where TNO not like '%1%'


--通配符_
--select * from TEACHING where TNO like '_481'
--select * from TEACHING where TNO like '_4_1'


--通配符[charlist]
--select * from DimCustomer where FirstName like '[ALN]%'
--select * from DimCustomer where FirstName like '%[!ALN]%' --or [^ALN]


--in 选多个值
--select * from DimCustomer where FirstName in ('Jon', 'Ian')


--between .. and .. 查找之间 可为数值、文本、日期等， sql server中between包含查找的两边值
--select * from DimCustomer where FirstName between 'Amanda' and 'Christy' 
--select * from DimCustomer where FirstName not between 'Amanda' and 'Christy' 


--join ... on   
--left join 左表全右表匹配无则null
--right join 右表全左表匹配无则null
--inner join 只返回联接行，无null
--full join 返回所有行，匹配的同行，无匹配的单行
--select * from COURSES inner join ENROLLS on COURSES.CNO = ENROLLS.CNO


--union 将列结果合并
--select TNAME, AGE from TEACHERS
--union
--select SNAME, AGE from STUDENTS


--复制并新建表,into的表需未建立,只复制数据不复制结构
--select SNO into STUDENTS_BACKUP from STUDENTSwode


--约束unique 不能有重复,可有null(可多个), primary key(只能一个)
--create table test_unique
--(
-- Id_p int not null unique,  --单个约束，不能有重复Id_p      -- Id_p int not null primary key,
-- LastName varchar(255) not null,
-- FirstName varchar,
-- constraint uc_1 unique(Id_p, LastName)  --多对约束，不能有重复对(Id_p, LastName) uc_1:自定义的约束名  -- constraint pk_1 unique(Id_p, LastName) 多对约束
--)


--alter unique 
--alter table test_unique add constraint uc_2 unique (Id_p)
--alter table test_unique drop constraint uc_2


--alter primary key
--alter table test_unique add primary key(Id_p)
--alter table test_unique add constraint pk_0 primary key (Id_p)
--alter table test_unique drop constraint pk_0


--foreign key a表的外键只能与b表类型完全相同的主键绑, 绑定后a表的绑定列只能有b表主键列的值
--create table ***( ..... foreign key(CNO) references COURSES(CNO)) or (CNO nchar(5) not null unique foreign key references COURSES(CNO))


--alter foreign key
--alter table ENROLLS add foreign key(CNO) references COURSES(CNO)
--alter table ENROLLS drop constraint FK__ENROLLS__CNO__1ED998B2


--check 值约束
--create table *** (check (Id_p > 0)) or (check (Id_p > 0 and Id_o > 0)) or (constraint CK_0 check(Id_p > 0))


--alter check  添加约束时，表内必须已满足约束条件
--alter table ENROLLS0 add constraint ck_1 check (GRADE = NULL or GRADE > 0)
--alter table ENROLLS0 drop constraint CK__ENROLLS0__4BAC3F29


--alter default
--alter table ENROLLS add constraint df_0 default('C2') for CNO
--alter table ENROLLS drop constraint df_0


--index
--CREATE index index_2 on ENROLLS(GRADE DESC)
--drop index ENROLLS.index_2


--alter 列
--alter table enrolls add col_test nvarchar(10)  --添加列
--alter table enrolls drop column col_date, col_date0  --删除列
--alter table enrolls alter column col_date nvarchar(10)  --改类型


--主键自增
--create table test_table
--(
-- id int primary key identity(10, 2),   以10开始每次递增2, 未设置则默认以1开始每次递增1
-- value int
--)


--视图
--create view test_view as
--select ZKHPID0, ZKTJRQ0 from BH1WZZK0
--where ZKTJRQ0 like '2008%'


--改表名
--use test
--go
--exec sp_rename 'dbo.COURESES', 'COURSES' 
--go


--改表列名
--use test
--go
--exec sp_rename 'TEACHING.SNUN', 'SNUM' 
--go


--declare test_Cursor cursor scroll for --scroll 可向任意方向取， 默认为forward_only，只能向下
--select Student_grade from score1 


--open test_Cursor
--declare @data int


----取下一行
--fetch next from test_Cursor into @data
--print @data
----取最后一行
--fetch last from test_Cursor into @data
--print @data
----取第一行
--fetch first from test_Cursor into @data
--print @data
----取上一行
--fetch prior from test_Cursor into @data
--print @data
----取第三行
--fetch absolute 3 from test_Cursor into @data
--print @data
----取相对目前来说的上x行
--fetch relative -1 from test_Cursor into @data
--print @data


--close test_Cursor
--deallocate test_Cursor


--判断NULL不能 = null  要用 is null


--多行变一列，中间接',', stuff去首部, for xml path('')多行变一列
--select SEX, stuff((select distinct ',' + ltrim(STR(AGE)) from STUDENTS b where a.SEX = b.SEX for xml path('')), 1, 1, '') as age
--from STUDENTS a group by SEX


--select AGE, STUFF((select ',' + rtrim(SNAME) from STUDENTS b where b.AGE = a.AGE for xml path('')), 1, 1, '') as SNAME
--from STUDENTS a group by age