一些复杂的SQL及常用SQL
来源:互联网 发布:双语歌词制作软件 编辑:程序博客网 时间:2024/06/03 23:49
一些常用的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
- 一些复杂的SQL及常用SQL
- 一些常用的sql
- 一些略微复杂的sql语句
- sql server的一些常用 sql 语句
- 一些常用的SQL语句
- 一些常用的SQL语句
- 一些常用的SQL语句
- 一些SQL的常用函数
- Oracle常用的一些sql
- 一些常用的SQL语句
- 一些常用的SQL语句
- 常用的一些SQL语句
- 一些常用的动态SQL
- 一些常用的sql语句
- SQL一些常用的函数
- 一些常用的SQL语句
- SQL的一些常用语法
- 一些常用的sql语句
- Junit和ant集成及设计模式之组合模式
- 精妙SQL语句收集
- 通用技术」真的可以成为普通高中通用课程吗?
- 如何成为一名优秀的软件设计师
- Jdk1.5的新语法和一些java学习的小知识点
- 一些复杂的SQL及常用SQL
- 读林斌博士写好代码十个秘诀
- 话说机器人的“人来疯”现象!!!!
- 什么是xDSL?
- jsp页面不能正常显示,同时又不报错:
- IRM(InformationRightsManagement)即信息权限管理
- 写在每年开学的日子
- 第一次正式写Blog
- Tomcat5.0+mysql5.0.0-alpha配置xpetstore