一些常用的SQL
--获得表的列数
select count(1) from syscolumns where id=object_id('tableName')
--修改表名,把表a改为b
exec sp_rename 'a','b'
--关闭自动增长列
set identity_insert Jxt_DeviceManage_Config on
--关闭外键约束
ALTER TABLE tableName NOCHECK CONSTRAINT ALL
--启用外键约束
ALTER TABLE tableName CHECK CONSTRAINT ALL
SELECT Sex=CASE(sex) WHEN '1' THEN '男' ELSE '女' END FROM tableName
遍历一棵树下所有子节点(no是PK,parentNo是父节点,tblStall是表名):
CREATE FUNCTION funGetStallChildID(@id INT) --用于遍历子节点的函数
RETURNS @re TABLE(tid INT)
AS
BEGIN
INSERT INTO @re SELECT [no] FROM tblStall WHERE parentNo=@id
WHILE @@ROWCOUNT>0
INSERT INTO @re SELECT a.[no]
FROM tblStall a INNER JOIN @re b ON a.parentNo=b.tid
WHERE a.[no] NOT IN(SELECT tid FROM @re)
RETURN
END
关于课程表的存储过程:
CREATE TABLE Curriculum(
ID int IDENTITY(1,1),
UnitCode varchar(20) NOT NULL, --单位编码
GradeCode varchar(20) NOT NULL, --年级编码
ClassCode varchar(20) NOT NULL, --班级编码
WeekDay smallint NOT NULL, --星期几
Serial int NOT NULL, --节次
Subject varchar(10) NOT NULL, --科目编码
CONSTRAINT PK407 PRIMARY KEY NONCLUSTERED (ID)
)
insert into Curriculum(UnitCode,GradeCode,ClassCode,WeekDay,Serial,Subject)
select 1,1,1,1,1,'语文'
insert into Curriculum(UnitCode,GradeCode,ClassCode,WeekDay,Serial,Subject)
select 1,1,1,1,2,'数学'
insert into Curriculum(UnitCode,GradeCode,ClassCode,WeekDay,Serial,Subject)
select 1,1,1,1,3,'物理'
insert into Curriculum(UnitCode,GradeCode,ClassCode,WeekDay,Serial,Subject)
select 1,1,1,1,4,'化学'
insert into Curriculum(UnitCode,GradeCode,ClassCode,WeekDay,Serial,Subject)
select 1,1,1,2,1,'政治'
insert into Curriculum(UnitCode,GradeCode,ClassCode,WeekDay,Serial,Subject)
select 1,1,1,2,2,'生物'
insert into Curriculum(UnitCode,GradeCode,ClassCode,WeekDay,Serial,Subject)
select 1,1,1,2,3,'历史'
insert into Curriculum(UnitCode,GradeCode,ClassCode,WeekDay,Serial,Subject)
select 1,1,1,2,4,'地理'
insert into Curriculum(UnitCode,GradeCode,ClassCode,WeekDay,Serial,Subject)
select 1,1,1,3,1,'体育'
insert into Curriculum(UnitCode,GradeCode,ClassCode,WeekDay,Serial,Subject)
select 1,1,1,3,2,'音乐'
insert into Curriculum(UnitCode,GradeCode,ClassCode,WeekDay,Serial,Subject)
select 1,1,1,3,3,'美术'
insert into Curriculum(UnitCode,GradeCode,ClassCode,WeekDay,Serial,Subject)
select 1,1,1,3,4,'英语'
insert into Curriculum(UnitCode,GradeCode,ClassCode,WeekDay,Serial,Subject)
select 1,1,1,4,1,'劳动'
declare @s varchar(8000)
set @s = ''
select
@s = @s + ',['+a.item+'] = max(case when WeekDay = '+rtrim(WeekDay)+' and Serial = '+rtrim(Serial)+' then Subject end)'
from
(select
WeekDay,
Serial,
('星期'+rtrim(WeekDay)+'第'+rtrim(Serial)+'节') as item
from
Curriculum
group by
WeekDay,Serial) a
order by
a.WeekDay,a.Serial
set @s = 'select ''单位''+GradeCode+''年级''+ClassCode+''班'' as 班 ' + @s + ' from Curriculum group by UnitCode,GradeCode,ClassCode'
exec(@s)
select * from Curriculum
按季度显示销售量:
CREATE TABLE Pivot
( Year SMALLINT,
Quarter TINYINT,
Amount DECIMAL(2,1) )
INSERT INTO Pivot VALUES (1990, 1, 1.1)
INSERT INTO Pivot VALUES (1990, 2, 1.2)
INSERT INTO Pivot VALUES (1990, 3, 1.3)
INSERT INTO Pivot VALUES (1990, 4, 1.4)
INSERT INTO Pivot VALUES (1991, 1, 2.1)
INSERT INTO Pivot VALUES (1991, 2, 2.2)
INSERT INTO Pivot VALUES (1991, 3, 2.3)
INSERT INTO Pivot VALUES (1991, 4, 2.4)
GO
SELECT Year,
SUM(CASE Quarter WHEN 1 THEN Amount ELSE 0 END) AS Q1,
SUM(CASE Quarter WHEN 2 THEN Amount ELSE 0 END) AS Q2,
SUM(CASE Quarter WHEN 3 THEN Amount ELSE 0 END) AS Q3,
SUM(CASE Quarter WHEN 4 THEN Amount ELSE 0 END) AS Q4
FROM Pivot
GROUP BY Year
关于时间格式的一些写法
IF EXISTE(SELECT domain_name FROM information_schema.domains
WHERE domain_schema='dbo'AND domain_name='member_no')
EXEC sp_droptype member_no
exec sp_addtype mytime,'varchar(20)','null','default right(char(8),getdate(),120)'
create rule rule_zip as
@x like '[0-9][0-9][0-9][0-9][0-9][0-9]'
CREATE RULE time_rule AS @time like 'yyyy-mm-dd' or len(@time)=0
EXEC sp_addtype type_time,'Datetime'
EXEC sp_bindrule 'time_rule','type_time'
CREATE TABLE SSSSS(SID INT NOT NULL IDENTITY PRIMARY KEY,Times type_time)
insert into SSSSS values('2001-02-03')
exec sp_addtype type_zip ,'char(6)','not null'
exec sp_bindrule 'rule_zip' ,'type_zip','futureonly'
exec sp_addtype N'spbh',N'char(8)',N'NOT NULL'
--创建规则,保证自定义类型的长度
CREATE RULE RULE_spbh AS
len(@spbh)=8 --只能是8位
and @spbh not like '%[^0-9]' --只能包含数字
go
--将规则绑定到自定义数据类型
exec sp_bindrule N'RULE_spbh',N'spbh'
go
--创建表
Create table kkkkkkkkk(
spbh spbh,
spmc varchar(20) not null,
spgg varchar(20) not null,
splx varchar(20) not null,
pfdj numeric(7,2) not null,
kcsl int default(0) not null)
go
其中我想让spbh为自增列
--用自定义函数
--创建自定义
create function f_nid()
returns char(8)
as
begin
return(right(100000001+isnull((select max(spbh) from kcgl),0),8))
end
go
--建表语句改为:
Create table kkkkk2(
spbh spbh default dbo.f_nid(),
spmc varchar(20) not null,
spgg varchar(20) not null,
splx varchar(20) not null,
pfdj numeric(7,2) not null,
kcsl int default(0) not null)
--插入记录时使用:
insert kcgl(spmc,...kcsl) values('ff',...5)
select BuyTime,cast(DATEPART(yy,BuyTime) as varchar(4))+'-'+cast(DATEPART(m,BuyTime)
as varchar(2))+'-'+cast(DATEPART(d,BuyTime) as varchar(2)) as 'Time' from aa22
DECLARE @str_today char(10)
SET @str_today=replace(Convert(char(10),getdate(),120),'-','.')
print @str_today
Select GetDate()
Select Convert(varchar(12),GetDate(),112)
create table aaaaa(a varchar(20),b varchar(20),c varchar(20))
insert into aaaaa values ('1','1',null)
insert into aaaaa values ('2','3','无')
insert into aaaaa values ('3','5','无')
select object_id('aaaaa')
declare @ColName varchar(20)
declare @s nvarchar(200)
set @s=''
DECLARE Detail_Cursor Cursor Fast_Forward For
select Name from syscolumns where id=object_id('aaaaa')
OPEN Detail_Cursor
FETCH Next From Detail_Cursor Into @ColName
WHILE @@fetch_Status=0
BEGIN
exec('select top 1 * from aaaaa where '+@ColName+' is not null and '+@ColName+' <>''无''')
if @@RowCount>0
set @s=@s+','+@ColName
FETCH Next From Detail_Cursor Into @ColName
END
CLOSE Detail_Cursor
DEALLOCATE Detail_Cursor
print @s
set @s='select '+stuff(@s,1,1,'') +' from aaaaa'
exec (@s)
select IDENTITY(int,1,1) as tid , * into #t1 from employee
select * from #t1 where tid>=5 and tid<=25
drop table #t1
CREATE RULE time_rule AS
@time like 'yyyy-mm-dd' or len(@time)=0