一些常用的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 [email protected]
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 '[email protected]+' is not null and '[email protected]+' <>''無''')
if @@RowCount>0
set @[email protected]+','[email protected]
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
![Oracle的基本操作[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)