[SQL]SQL server 常用代码

时间:2020-04-01作者:klpeng分类:IT综合浏览:2236评论:0

判断数据库是否存在

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 '累加的次数'

例图:
[SQL]SQL server 常用代码

创建管理触发器(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

[SQL]SQL server 常用代码
禁止删除和修改数据库内的表

create trigger tri_chufa on database
for drio_table,alter_table as
--回滚
rollback
打赏
文章版权声明:除非注明,否则均为彭超的博客原创文章,转载或复制请以超链接形式并注明出处。
相关推荐

发表评论:

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。

猜你喜欢