首页 诗词 字典 板报 句子 名言 友答 励志 学校 网站地图
当前位置: 首页 > 教程频道 > 数据库 > SQL Server >

惯用T-sql

2012-10-08 
常用T-sql1.create tablecreate table dbo.app_param( sn int identity(1,1) not null, type nvarchar(50)

常用T-sql


1.create table
create table dbo.app_param(
sn int identity(1,1) not null,
type nvarchar(50) not null,
module nvarchar(50) not null,
ckey nvarchar(50) not null,
cvalue nvarchar(50) not null,
remark nvarchar(50) null,
updated_user nvarchar(20) null,
created_datetime datetime null,
updated_datetime datetime null,
constraint PK_app_param primary key nonclustered(sn asc)
)


2.create index
create unique clustered index IX_app_param on dbo.app_param
(
type asc,
module asc,
ckey asc
)


3.create foreign key
alter table dbo.app_config add constraint FK_app_config_call_center
foreign key( country_code, call_center_code)
references dbo.call_center(country_code, call_center_code)

4.set null

5. add column
alter table employee add subsidy_status smallint default 1 null
alter table employee_comp_detail add subsidy decimal(16,2) null

6.three common lines
updated_user nvarchar(20) null,
created_datetime datetime default getdate() not null,
updated_datetime datetime default getdate() not null,

7. rename column
exec sp_rename 'kpi_callcenter.active', 'is_active', 'column';

8. change column type
alter table call_center_incentive alter column is_active smallint

9.drop column
alter table employee drop column employee_name_th

10.drop PK
ALTER TABLE dbo.doc_exc DROP CONSTRAINT my_constraint

11.drop index
DROP index anp_comm_rating.IX_anp_comm_rating

7. rename table
exec sp_rename 'table1', 'table2';

8.dump_history_log table

create table if_salespermonth_dump(
employee_id nvarchar(20) null,
month_year nvarchar(20) null,
sales_per_month nvarchar(20) null
)

create table if_salespermonth_history(
sn int identity(1,1) not null,
employee_id nvarchar(20) null,
month_year nvarchar(20) null,
sales_per_month nvarchar(20) null,
created_datetime datetime default getdate() not null,
constraint PK_if_salespermonth_history primary key clustered(sn asc)
)

CREATE TABLE [dbo].if_salespermonth_log(
[sn] [int] IDENTITY(1,1) NOT NULL,
employee_id nvarchar(20) null,
month_year nvarchar(20) null,
sales_per_month nvarchar(20) null,
[transaction_date] [datetime] NOT NULL,
[batch_no] [int] NOT NULL,
[execution_date] [datetime] NOT NULL,
[error_type] [nvarchar](10) NOT NULL,
[error_msg] [nvarchar](200) NULL,
[updated_user] [nvarchar](20) NULL,
[created_datetime] [datetime] default getdate() NOT NULL,
[updated_datetime] [datetime] default getdate() NOT NULL,
CONSTRAINT [PK_if_salespermonth_log] PRIMARY KEY CLUSTERED (sn asc)
)



11.建表的4行
sn int identity(1,1) not null,
updated_user nvarchar(20) null,
created_datetime datetime default getdate() not null,
updated_datetime datetime default getdate() not null,
constraint PK_c_commission_rate primary key nonclustered(sn asc)


13.生成数据字典的sql:select tname,colid,cname,ctype,length =

case ctype
when 'nvarchar' then length/2
when 'nchar' then length/2
else length
end,


xprec,xscale,isnullable from all_col where tname not like 'if%' and tname not like 'cn%'
order by 1,2

select * from all_col order by 1,2


select row_number() over (partition by employee_id order by program_id) as row_num,
*
from employee_program

?

) as k pivot (max(score) for score_type in([0],[1],[2])) as t

?

18. convert


? select CONVERT(varchar, getdate(),103)

?

?

?

delete t
??? ??? ??? from (select a.seller_id,a.employee_id,
??? ??? ??? row_number() over (partition by a.seller_id order by a.seller_id) as r
??? ??? ??? from if_employee_info_dump a) t
??? ??? ??? where t.r > 1

?

?

?

?

热点排行