[SQL]SQL server 常用代码

判断数据库是否存在

USE eshop   --选取数据库
GO
IF(EXISTS(SELECT*FROM sysdatabases WHERE name='eshop'))   --判断eshop是否存在
DROP DATABASE eshop						--删除
GO

新建数据库和日志

  --新建数据
CREATE DATABASE eshop
ON PRIMARY 
(
NAME=eshop,
FILENAME='E:\SQLDemo\eshop\eshop.mdf',
SIZE=5MB,
MAXSIZE=UNLIMITED,
FILEGROWTH=1MB
)
--新建日志
LOG ON
(
NAME=eshop_log,
FILENAME='E:\SQLDemo\eshop\eshop_log.ldf',
SIZE=1MB,
MAXSIZE=2MB,
FILEGROWTH=10%
)

报告和查询

sp_helpdb eshop    --报告

select * from sys.databases where name='eshop'   --新建查询

修改数据库

ALTER DATABASE eshop_new MODIFY NAME=eshop   --修改逻辑名  eshop_new改为eshop

GO
ALTER DATABASE eshop SET SINGLE_USER  --SINGLE_USER单用户访问   	MULTI_USER多用户访问  

--打开数据库
USE eshop 
GO        
--修改表，添加列
ALTER TABLE biao ADD lieID SMALLDATETIME NULL

--修改数据库类型
ALTER TABLE biao ALTER COLUMN lieID DATETIME

--删除列
ALTER TABLE biao DROP COLUMN lieID

--删除表
DROP TABLE biao
1.detete from 表名 ----逐行删除，不会重置主键自增
2.truncate table 表名   --删除整个表，主键自增会重新计算，从1开始

--按条件修改表中的数据
 update biao set lieID='数据' where lieID=2;
--lieID+=1  自增1        去掉where语句修改表内所有数据

--按条件删除表中的数据

DELETE FROM biao WHERE lieID> 20 

--修改列所有数据为当前时间
update product set lieID=getdate()

--按条件修改 
update product set lieID=1888 where ProID=10101

添加新数据

--添加数据
INSERT INTO biao(ziduan1,ziduan2,ziduan3)VALUES('duiying1','duiying2','duiying3')
--写出表中"字段"，并写入"对应"类型数据

--添加多行数据
INSERT INTO biao(ziduan1,ziduan2,ziduan3)VALUES('duiying1','duiying2','duiying3'),('duiying4','duiying5','duiying6')

--插入其他表查询的统计数据

INSERT INTO beicharu SELECT biao1.lieID,SUM(biao2.lieID) FROM biao1,biao2 WHERE biao1.waijian=biao2.waijian GROUP BY biao1.fenzu

脱机&联机

USE master
go
alter database eshop set offline       --脱机offline   联机online

分离&附加

--分离
EXEC sp_detach_db eshop   

--附加
EXEC sp_attach_db @dbname='eshop',    
@filename1='E:\SQLDemo\eshop\eshop.mdf', 
@filename2='E:\SQLDemo\eshop\eshop_log.ldf'

修改 扩充 新建

修改数据库属性
ALTER DATABASE eshop
MODIFY FILE 
(
NAME=eshop,				
size=10MB,			
FILEGROWTH=8MB
)	

扩充日志文件
ALTER DATABASE eshop
MODIFY FILE 
(
NAME=eshop_log,				
size=10MB,			
FILEGROWTH=15%
)	

--新建次要文件
ALTER DATABASE eshop
ADD FILE
(
  NAME=eshop1,
  FILENAME='E:\SQLDemo\eshop\eshop1.ndf',
  size=8MB,
  FILEGROWTH=3MB
)

数据库文件收缩

数据库收缩
DBCC SHRINKDATABASE (eshop,65)	--收缩数据库到65%

文件收缩
DBCC SHRINKFILE (eshop,7)		--将文件收缩到7兆

新建表

--新建
CREATE TABLE product
(
ProID int NOT NULL,					--列名，数据类型(int,varchar(50),smallmoney,datetime )，允许null值(是null，否not null)
)		

--判断是否存在，存在删除表
USE master
GO
IF EXISTS(SELECT * FROM sysobjects WHERE name='product')
DROP TABLE product

--创建新表时设置标识种子6000，自增为1
CREATE TABLE biao
(
name int NOT NULL IDENTITY(6000,1)
)

数据约束管理

• 添加主键

USE eshop 
GO  
ALTER TABLE biao
ADD CONSTRAINT PK_biao PRIMARY KEY (lieID)

• 删除主键

ALTER TABLE biao
DROP CONSTRAINT PK_biao

• 添加外键约束

ALTER TABLE biao
ADD CONSTRAINT FK_waibiao_zhubiao FOREIGN KEY(lieID) REFERENCES zhubiao(lieID)
--外键_主键

• 定义唯一约束

ALTER TABLE biao
ADD CONSTRAINT QU_lieID        
UNIQUE([lieID])

• 删除唯一约束

ALTER TABLE biao
DROP CONSTRAINT FK_waibiao_zhubiao

• 添加默认约束

ALTER TABLE class 
ADD CONSTRAINT DF_class  DEFAULT '电子信息工程学院' FOR DeptName

• 定义检查约束

alter table eshop
add constraint CK_eshop_ziduan CHECK(ziduan ='男' or zhubiao = '女')
--删除检查约束同上

• 删除约束

ALTER TABLE biao
DROP CONSTRAINT FK_waibiao_zhubiao 

索引操作

• 聚集索引

--ps：当表已经设置了主键或唯一值，系统自动为表创建索引
CREATE CLUSTERED INDEX IX_suoyinname ON biao(ziduan)

• 非聚集索引

CREATE NONCLUSTERED INDEX IX_suoyinname ON biao(ziduan)

• 禁用索引

ALTER INDEX IX_suoyinname ON biao DISABLE

• 启用索引

ALTER INDEX IX_suoyinname ON biao REBUILD

• 显示存储过程的定义文本

SP_HELPTEXT biao

储存过程操作

• 无参存储过程，用于直接执行存储的代码

create procedure guoname
AS
select ziduan from biao
--无参调用
EXEC guoname

• 带参存储过程

create procedure guoname
@bl1 nvarchar(50),@bl2 float
AS
select ziduan from biao where lie1 = @bl1,lie2 = @bl2
--带参调用
EXEC guoname @lie1 = '我是参数',@bl2 = '66'

• 输入和输出存储过程

create procedure guoname
@bl1 nvarchar(50),@bl2 float output
AS
select @bl2 = ziduan from biao where lie1 = @bl1
--输入和输出调用
declare @bl2 float
exec guoname '1501',@bl2 output
SELECT @bl2 as 总成绩

• 修改储存过程

alter procedure guoname
AS
select ziduan from biao

--查询储存过程
SP_HEPLTEXT guoname
--删除储存过程
DROP PROCEDURE guoname

流程控制语句

begin
...语句块
end

• 多分支流程语句

select lie1 as 列表1 ,lie2 as 列表2 ,分类条件=
	case lie3
		when 200 then '等于200'
		when 300 then '等于300'
		else '啥也不是'
	end
from biao	

• goto，return语句

if 1=2
	goto code1
else
	goto code2
code1:
	begin
		print '等于'
		return
	end
code2:
	begin
		print '不等于'
		return
	end

• 循环语句

--定义变量
declare @a int, @b int ,@c int 	
--赋值
set @a=20202
set @b=0
set @c=0				
--当条件小于3000	
while(select lieID from biao where lie1=@a)<3000   
	begin
		--修改表内数据，直到不符合循环条件
		update biao set lieID= lieID+50 where lie1=@a	
		--记录次数
		set @c=@c+1						
	end
--查询最后的结果
select @b=lieID from biao where lie1=@a
select @a as '编号',@b as '最后的价格' ,@c as '累加的次数'

例图：

创建管理触发器(DML&DDL)

• ps：用于数据库变动时被动触发
• 创建触发器

create trigger chufa on biao
after insert as print '成功在biao添加一条记录'

create trigger chufa on biao
instead of insert as print '禁止在biao添加记录'

create trigger chufa on biao
--delete禁止删除，update禁止修改
after update as 
if update(lieID)
	begin
		print '禁止修改biao表内的lieID'
		--数据库修改未储存时，可恢复到之前状态
		rollback    
	end

将新添加的数据插入到新表中

create trigger tri_chufa_insert on biao
for insert as
begin
	declare @name varchar(50),@address VARCHAR(50)
	select @name = inserted.lie1,@address = inserted.lie2 
	from inserted insert into xinbiao (lie1,lie2)
	values(@name,@address)
end

禁止删除和修改数据库内的表

create trigger tri_chufa on database
for drio_table,alter_table as
--回滚
rollback