增加删除字段修改字段名,修改表结构,非常用SQL语句技巧总结

时间:2021-11-17 14:36:18

1.为数据表添加一个新字段

Alter TABLE [dbo].[CustomerBackupConfig] Add [Stamp] [timestamp] NULL
GO

 

2.为数据表添加两个新字段

Alter Table tblStates Add [TaxRate] money not null default 0,
[Transit] int not null default 0
GO

 

3.为数据表删除一个字段

Alter Table [dbo].[tblOrder] Drop Column [CookieID]
GO

 

4.修改数据表一个字段的定义

Alter Table [dbo].[tblOrder] Alter Column [CookieID] int not null
GO

4.1 修改字段名

EXEC sp_rename '[dbo].[Table_1].[filedName_Old]', 'filedName_New', 'COLUMN';

4.2 更改当前数据库中用户创建对象(如表、列或用户定义数据类型)的名称

sp_rename [ @objname = ] 'object_name' ,[ @newname = ] 'new_name',分类信息; [ , [ @objtype =] 'object_type' ]
如:
EXEC sp_rename 'newname','PartStock'

 

 

5.删除数据表

Drop Table tblTaxRate
GO

 



6.为数据表去掉一个外键关联,然后再添加一个外键关联

Alter TABLE [dbo].[OrderPackageOption] Drop CONSTRAINT [FK_OrderPackageOption_OrderPackage]


ALTER TABLE [dbo].[OrderPackageOption] ADD
CONSTRAINT [FK_OrderPackageOption_OrderPackage] FOREIGN KEY
(
[OrderPackageID]
)
REFERENCES [dbo].[OrderPackage] (
[OrderPackageID]
)
GO

 



7. 为数据表添加两个外键关联,并且设置其中之一不检查现有数据是否符合关联要求。

ALTER TABLE [dbo].[Account] ADD
CONSTRAINT [FK_Account_State] FOREIGN KEY
(
[StateID]
)
REFERENCES [dbo].[State] (
[StateID]
),
CONSTRAINT [FK_Account_TimeZones] FOREIGN KEY
(
[TimeZoneID]
)
REFERENCES [dbo].[TimeZones] (
[timeid]
)
GO

Alter table [dbo].[Account] NoCheck Constraint [FK_Account_TimeZones]
GO

 



8. 增加外键时不检查当前数据

ALTER TABLE [dbo].[Account] With NoCheck
ADD CONSTRAINT [FK_Account_AccountType]
FOREIGN KEY
(
[TypeID]
)
REFERENCES [dbo].[AccountType] (
[TypeID]
)
GO

 



9.为了对数据表进行批量的数据导入,需要将identity_insert先打开,执行完后在关闭。其目的是避开自增列的检查,顺利执行插入操作。

set identity_insert [dbo].[PSTNUsageNotes] on
--在这里执行对PSTNUsageNotes表的批量插入操作
set identity_insert [dbo].[PSTNUsageNotes] off

 



10.暂时关闭约束检查,等数据操作完成后再打开。

ALTER TABLE [Usage] NOCHECK CONSTRAINT [FK_USAGE_REFERENCE_PSTN1]
ALTER TABLE [Usage] NOCHECK CONSTRAINT [FK_USAGE_REFERENCE_PSTN2]
-- 在这里执行有可能破坏外键约束的操作,如强行删除数据等
ALTER TABLE [Usage] CHECK CONSTRAINT [FK_USAGE_REFERENCE_PSTN1]
ALTER TABLE [Usage] CHECK CONSTRAINT [FK_USAGE_REFERENCE_PSTN2]

 



11. 数据表的列重命名

exec sp_rename 'dbo.LocationIncidentalServiceRelationship.UsageAllowedWithouReservation','UsageAllowedWithoutReservation','column'

 



12. 检查A数据库中有哪些存储过程在B数据库中不存在

select name from Jaguar.dbo.sysobjects where xtype='P' and name not in
(
select name from JaguarTest.dbo.sysobjects where xtype='P' )

 



13. 在全部用户表和存储过程中寻找包含某段文字的用户表和存储过程

select OBJECT_NAME(id) AS name,[name] as content,xtype from sysobjects
where [name] like '%IncidentalService%' And (xtype='U' or xtype='P')

 


类型说明:

 
xtype type
C CHECK   约束
D 默认值或   DEFAULT   约束
F FOREIGN   KEY   约束
FN 标量函数
IF 内嵌表函数
P 存储过程
RF 复制筛选存储过程
S 系统表
TF 表函数
TR 触发器
U 用户表
V 视图
X 扩展存储过程
L 日志

 

sp_help 显示表的一些基本情况

sp_help 'object_name';   
如:
EXEC sp_help 'PartStock';

 

列出数据库里所有的表名

select name from sysobjects where type='U'

判断表的存在性:

select count(*) from sysobjects where type='U' and name='你的表名'

判断字段的存在性:

select count(*) from syscolumns where id=(select id from sysobjects where type='U' and name='你的表名') and name = '你要判断的字段名'

列出表里的所有的字段名:

select name from syscolumns where id=object_id('TableName')

判断某一表PartStock中字段PartVelocity是否存在

if exists(select * from syscolumns where id=object_id('[dbo].[CFFund]') and name='CFInvestmentMaxAmount') 
begin
alter table CFFund alter column CFInvestmentMaxAmount decimal(18,4) NULL;
end

即:

if exists (select * from syscolumns where id=object_id('PartStock') and name='PartVelocity')
print 'PartVelocity exists'
else
print 'PartVelocity not exists'

一个小例子:
假设要处理的表名为: tb

--判断要添加列的表中是否有主键
if exists(select 1 from sysobjects where parent_obj=object_id('tb') and xtype='PK')
begin
print '表中已经有主键,列只能做为普通列添加'
--添加int类型的列,默认值为0
alter table tb add 列名 int default 0
end
else
begin
print '表中无主键,添加主键列'
--添加int类型的列,默认值为0
alter table tb add 列名 int primary key default 0
end

 

列示type、vender、pcs字段,以type字段排列,case可以方便地实现多重选择,类似select中的case。

select 
type,
sum(case vender when A then pcs else 0 end),
sum(case vender when C then pcs else 0 end),
sum(case vender when B then pcs else 0 end)
from tablename group by type

 

初始化表table1(快速清除海量表数据,快速删除表数据,海量数据快速删除)

TRUNCATE TABLE tableName

 

几个高级查询运算词
  UNION 运算符
  UNION 运算符通过组合其他两个结果表(例如 TABLE1 和 TABLE2)并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时(即 UNION ALL),不消除重复行。两种情况下,派生表的每一行不是来自 TABLE1 就是来自 TABLE2。
  EXCEPT 运算符
  EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL),不消除重复行。
  INTERSECT 运算符
  INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL),不消除重复行。
  注:使用运算词的几个查询结果行必须是一致的。

 

between的用法,between限制查询数据范围时包括了边界值(包括左边边界,但不包括右边边界),not between不包括:

select * from table1 where times between time1 and time2
select * from table1 where times not between time1 and time2

两张关联表,删除主表中已经在副表中没有的信息:

delete from table1 where not exists (
select * from table2 where table1.field1=table2.field1
)

复制表(只复制结构,源表名:a 新表名:b) (Access可用):

法一:select * into b from a where 1<>1
法二:
select top 0 * into b from a

拷贝表(拷贝数据,源表名:a 目标表名:b) (Access可用):

insert into b(a, b, c) select d,e,f from b;

跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用):

insert into b(a, b, c) select d,e,f from b in ‘具体数据库’ where 条件

例子:..
from b in "&Server.MapPath(".")&"\data.mdb" &" where..

 

创建数据库:

CREATE DATABASE database name

删除数据库:

drop database dbname

备份sql server:

--- 创建备份数据的 device
USE master
EXEC sp_addumpdevice disk, testBack, c:\mssql7backup\MyNwind_1.dat
--- 开始 备份
BACKUP DATABASE pubs TO testBack

 

创建新表:

create table tabname(col1 type1 [not null] [primarykey],col2 type2 [not null],..)

根据已有的表创建新表:

create table tab_new like tab_old (使用旧表创建新表)

create table tab_new as select col1,col2… from tab_old definition only

删除新表:

drop table tabname

增加一个列:

Alter table tabname add column col type

    注:列增加后将不能删除。DB2中列加上后数据类型也不能改变,唯一能改变的是增加varchar类型的长度。

添加主键:

Alter table tabname add primary key(col)

删除主键:

Alter table tabname drop primary key(col)

创建索引:

create [unique] index idxname on tabname(col….)

删除索引:

drop index idxname

    注:索引是不可更改的,想更改必须删除重新建。

创建视图:

create view viewname as select statement

删除视图:

drop view viewname

 

 

几个简单的SQL语句:

总数:select count * as totalcount from table1
求和:
select sum(field1) as sumvalue from table1
平均:
select avg(field1) as avgvalue from table1
最大:
select max(field1) as maxvalue from table1
最小:
select min(field1) as minvalue from table1

 

 

随机读取若干条记录

Access语法:select top 10 * from 表名 order by Rnd(id)

Sql server:
select top n * from 表名 order by newid()

mysql:
select * from 表名 order by rand() Limit n

日程安排提前五分钟提醒

select * from 日程安排 where datediff(minute,f开始时间,getdate())>5

包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表

(select a from tableA ) except (select a from tableB) except (select a from tableC)

随机取出10条数据

select top 10 * from tablename order by newid()

 

写入数据并返回标识ID

SET IDENTITY_INSERT ON INSERT INTO (xxxx) values(xxxx) SET IDENTITY_INSERT OFF