一些sqlserver小知识
时间:2017-04-17 12:18 来源:linux.it.net.cn 作者:IT
1、往表中插入多条数据使用union关键字
insert into Users([name],password,nickName)
select 'aaa','bbbb','cccc' union
select 'dfdf','dfdf','dfdsf' union
select 'edtrer','dfgg','dfgfg'
使用 union all时将把以下和union数据相同的行覆盖了
2、delete truncate drop的区别
delete时不写条件将把表中的数据都删了,删除一条往日志文件里写一条记录,表行数很多速度会很慢
truncate table就把表清空了,速度快
drop删除表时没有这个表将会报错
delete删除表时没有这个表将会显示影响行数为0
3、关于日期的函数
getDate()获得当前日期
dateAdd(yyyy,2,'2008-11-10') 将指定日期加2年,也可以加月mm,加天dd等
dateDiff(yyyy,'2005-10-5','2007-10-5')返回两个日期的相隔的年月或日
dateName(dw,'2008-11-4')获得星期几(也可用w)结果为星期二
datePart(day,'2007-12-21')获得日期的指定整数形式
与datePart类似还有
Day()、Month()、Year()函数
4、一些常用的sqlServer函数
len('df大幅度') 获得实际长度5
datalength('df大幅度')获得字节数8
charindex('mn','mmmmn')获得第一个字符串在第二个字符串中的起始位置4
lower() 返回字符串的小写
upper() 返回字符串的大写
ltrim() 清除字符串左边的空格
rtrim() 清除字符串右边的空格
right('sdfd',2) 从字符串右边截取指定数目的字符
left('dfds',2) 从字符串左边截取指定数目的字符
replace('dfdsfd','d','a') 将字符串中字符d都替换为字符a
stuff('addfgdg',2,3,'AAAAA') 从字符串第二个位置开始截取3个字符用AAAAA代替
numeric(5,3)
decimal(5,3) 这两个功能一样,精度为5,即总位数是5,小数位数为3
cast(123 AS varchar(5)) 转换函数,将整数转化为字符串
convert(int,'123') 将字符串转化为整数
convert(varchar(10),getdate(),121) 使用了样式,获得日期的年月日,没有时分秒
isnull(classess,'未分班') 将所有classess列为null的用未分班代替
5、一些常用的存储过程
sp_help users 列出表结构
sp_tables 查出当前库里的所有表
xp_cmdshell 'mkdir d:\bb' 创建文件夹
EXEC sp_addlogin 'adminMaster','theMaster' --创建sqlserver登陆帐号
--在master库下
EXEC sp_grantdbaccess 'adminMaster','adminMaster'--创建数据库用户
USE bbsDB
--给用户赋权限，对主贴表bbsTopic和回帖表bbsReply有查看、删帖的权限
GRANT select,delete ON bbsTopic TO adminMaster
GRANT select,delete ON bbsTopic TO adminMaster
--对用户表bbsUsers有修改权限
GRANT update ON bbsUsers TO adminMaster
scope_identity() 获得真正的最后一次插入的数据的标识值,因为多用户在insert时,别人也有可能insert
@@identity 获得最后一次插入的数据的标识值
sp_who 查询出有几个用户连着数据库
kill spid这个列的值 删除占用资源的用户
print @@IDENTITY --在多各用户都执行插入时，有可能得到的不是真正的标识值
print @@MAX_CONNECTIONS --可以创建同时连接的最大数目
print @@ROWCOUNT --受上一个SQL语句的影响行数
print @@SERVERNAME --本地服务器的名称
print @@TIMETICKS --当前计算机每刻度的微秒数
print @@TRANCOUNT --当前打开的事物数
print @@VERSION --SQL Server的版本
ups 不间断电源
6、建库、建表、加约束
在master库下
if exists(select * from sysdatabases where name='mytest')
drop database mytest
create database mytest
on
(
name='mytest',
filename='e:\test.mdf'
)
在mytest库下
if exists(select * from sysobjects where name='stu')
drop table stu
create table stu
(
id int identity,
name varchar(50),
calsses varchar(50)
)
加约束
alter table stuInfo
add constraint ck_stuNo check(stuNo like 's253__') 加check约束
alter table stu
add constraint df_name default('aaa') for [name] 加默认约束
alter table stu
add constraint uq_name(name) 加唯一约束
alter table stu
add constraint pk_id primary key 加主键约束
alter table stu
add constraint fk_id foreign key(外键列) references 主键表(主键列) 加外键约束
7、小知识
SET NOCOUNT ON --不显示SQL语句影响的行数
往标识列插入显示值必须
set identity_insert 表名 on
set identity_insert 表名 off 关闭插入显示值
修改表中某一列的数据类型
alter table 表名
alter column 列名 类型(大小)
往表中插入一列
alter table 表名
add 列名 类型(大小)
8、表变量
declare @myTable table
(
id int identity,
orderid varchar(20),
customerid varchar(20)
)
9、执行内容是字符串的sql语句
declare @strsql varchar(800)
set @strsql='select * from orders'
exec(@strsql)
case when then的用法:
CASE WHEN writtenExam IS NULL THEN '缺考'
ELSE CAST(labExam AS VARCHAR)
END
10、事物
BEGIN TRANSACTION
DECLARE @userID int,@sumError int
SET @sumError = 0
SELECT @userID = UID FROM bbsUsers WHERE Uname = '可卡因'
--可卡因发帖
INSERT INTO bbsTopic(TsID,TuID,Tface,Ttopic,Tcontents,Ttime,TclickCount,Tstate,TlastReply)
VALUES(2,@userID,4,'.NET配置问题','我的',GETDATE(),200,0,'2009-1-8')
SET @sumError = @sumError + @@ERROR
--发贴之后.NET版块表加1
IF(@sumError = 0)
UPDATE bbsSection SET StopicCount = StopicCount + 1 WHERE Sname LIKE '%.NET%'
SET @sumError = @sumError + @@ERROR
--判断错误是否是0，不是发帖不成功
IF(@sumError <> 0)
BEGIN
print '您所发的帖子不成功!'
ROLLBACK TRANSACTION
END
--错误号不是0说明发帖成功
ELSE
BEGIN
print '发帖成功'
COMMIT TRANSACTION
END
事物会锁定表,独占此表,只有执行完后,才能对此表进行操作
11、索引
if exists(select * from sysobjects where name='myIndex')
drop index myIndex
create nonclustered myIndex --非聚集索引
on 表名(列名)
12、视图
IF EXISTS(SELECT * FROM sysobjects WHERE name = 'myview')
DROP VIEW myview
GO
CREATE VIEW myview
AS
(
SELECT * from orders
)
视图不存放数据,仅仅是映射,即引用原表中的数据而已
对视图执行update语句,原表中的数据会改变,而虚拟表不会
13、触发器
IF EXISTS(SELECT * FROM sysobjects WHERE name = 'trig_bank')
DROP TRIGGER trig_bank
GO
CREATE TRIGGER trig_bank
ON bank
FOR INSERT
AS
......
往bank表中插入数据时执行触发器的begin到end
14、存储过程
F EXISTS(SELECT * FROM sysobjects WHERE name = 'proc_stu')
DROP PROCEDURE proc_stu
GO
CREATE PROCEDURE proc_stu
@notpassnum int OUTPUT,--输出参数，未通过人数
@writtenPass int = 60, --输入参数笔试及格线
@labPass int =60 --输入参数，机试及格线
AS
......
15、创建一个自定义函数
CREATE FUNCTION fun_myfun
(
@num int --参数
)
returns int --返回类型
AS --begin和end不能省
BEGIN
SET @num = @num + 1
RETURN @num --返回值
END
SELECT dbo.fun_myfun(5) --调用函数
16、往一个表里插入数据的两种方法
<1>select A.列,A.列 into B --B表事先不存在
from A
<2>insert into B(列,列)
select 列,列 from A --B表事先要存在,并且数据类型和个数都要匹配
17、char和varchar的区别
char:若定义一个字符大小为10,若只写一个字符1,在1后就有9个空格
varchar:若定义一个字符大小为10,若只写一个字符1,它就只占一个字符的位置
所以varchar可节省磁盘空间
但是要想吧字符1改成1111,就必须吧原来的往后移动,腾出空间,所以效率低
一般：50个字符以内用char
50个字符以上用varchar
update 表名 set 列名=null --将这个列的数据都置为null
18、在结果集中改变列名的三种方法
<1>select codeId as '学员编号' --可不加引号
<2>select codeId '学员编号' --因为as可以省略
<3>select '学员编号'=codeId
查询是按百分比提取用关键字percent
select top 20 percent * from student
应该是<1>where <2>group by <3>having的使用顺序
where和having都是过滤条件
区别是条件中有聚合函数时,只能用having,否则用where
(责任编辑：IT)
| 1、往表中插入多条数据使用union关键字 insert into Users([name],password,nickName) select 'aaa','bbbb','cccc' union select 'dfdf','dfdf','dfdsf' union select 'edtrer','dfgg','dfgfg' 使用 union all时将把以下和union数据相同的行覆盖了 2、delete truncate drop的区别 delete时不写条件将把表中的数据都删了,删除一条往日志文件里写一条记录,表行数很多速度会很慢 truncate table就把表清空了,速度快 drop删除表时没有这个表将会报错 delete删除表时没有这个表将会显示影响行数为0 3、关于日期的函数 getDate()获得当前日期 dateAdd(yyyy,2,'2008-11-10') 将指定日期加2年,也可以加月mm,加天dd等 dateDiff(yyyy,'2005-10-5','2007-10-5')返回两个日期的相隔的年月或日 dateName(dw,'2008-11-4')获得星期几(也可用w)结果为星期二 datePart(day,'2007-12-21')获得日期的指定整数形式 与datePart类似还有 Day()、Month()、Year()函数 4、一些常用的sqlServer函数 len('df大幅度') 获得实际长度5 datalength('df大幅度')获得字节数8 charindex('mn','mmmmn')获得第一个字符串在第二个字符串中的起始位置4 lower() 返回字符串的小写 upper() 返回字符串的大写 ltrim() 清除字符串左边的空格 rtrim() 清除字符串右边的空格 right('sdfd',2) 从字符串右边截取指定数目的字符 left('dfds',2) 从字符串左边截取指定数目的字符 replace('dfdsfd','d','a') 将字符串中字符d都替换为字符a stuff('addfgdg',2,3,'AAAAA') 从字符串第二个位置开始截取3个字符用AAAAA代替 numeric(5,3) decimal(5,3) 这两个功能一样,精度为5,即总位数是5,小数位数为3 cast(123 AS varchar(5)) 转换函数,将整数转化为字符串 convert(int,'123') 将字符串转化为整数 convert(varchar(10),getdate(),121) 使用了样式,获得日期的年月日,没有时分秒 isnull(classess,'未分班') 将所有classess列为null的用未分班代替 5、一些常用的存储过程 sp_help users 列出表结构 sp_tables 查出当前库里的所有表 xp_cmdshell 'mkdir d:\bb' 创建文件夹 EXEC sp_addlogin 'adminMaster','theMaster' --创建sqlserver登陆帐号 --在master库下 EXEC sp_grantdbaccess 'adminMaster','adminMaster'--创建数据库用户 USE bbsDB --给用户赋权限，对主贴表bbsTopic和回帖表bbsReply有查看、删帖的权限 GRANT select,delete ON bbsTopic TO adminMaster GRANT select,delete ON bbsTopic TO adminMaster --对用户表bbsUsers有修改权限 GRANT update ON bbsUsers TO adminMaster scope_identity() 获得真正的最后一次插入的数据的标识值,因为多用户在insert时,别人也有可能insert @@identity 获得最后一次插入的数据的标识值 sp_who 查询出有几个用户连着数据库 kill spid这个列的值 删除占用资源的用户 print @@IDENTITY --在多各用户都执行插入时，有可能得到的不是真正的标识值 print @@MAX_CONNECTIONS --可以创建同时连接的最大数目 print @@ROWCOUNT --受上一个SQL语句的影响行数 print @@SERVERNAME --本地服务器的名称 print @@TIMETICKS --当前计算机每刻度的微秒数 print @@TRANCOUNT --当前打开的事物数 print @@VERSION --SQL Server的版本 ups 不间断电源 6、建库、建表、加约束 在master库下 if exists(select * from sysdatabases where name='mytest') drop database mytest create database mytest on ( name='mytest', filename='e:\test.mdf' ) 在mytest库下 if exists(select * from sysobjects where name='stu') drop table stu create table stu ( id int identity, name varchar(50), calsses varchar(50) ) 加约束 alter table stuInfo add constraint ck_stuNo check(stuNo like 's253__') 加check约束 alter table stu add constraint df_name default('aaa') for [name] 加默认约束 alter table stu add constraint uq_name(name) 加唯一约束 alter table stu add constraint pk_id primary key 加主键约束 alter table stu add constraint fk_id foreign key(外键列) references 主键表(主键列) 加外键约束 7、小知识 SET NOCOUNT ON --不显示SQL语句影响的行数 往标识列插入显示值必须 set identity_insert 表名 on set identity_insert 表名 off 关闭插入显示值 修改表中某一列的数据类型 alter table 表名 alter column 列名 类型(大小) 往表中插入一列 alter table 表名 add 列名 类型(大小) 8、表变量 declare @myTable table ( id int identity, orderid varchar(20), customerid varchar(20) ) 9、执行内容是字符串的sql语句 declare @strsql varchar(800) set @strsql='select * from orders' exec(@strsql) case when then的用法: CASE WHEN writtenExam IS NULL THEN '缺考' ELSE CAST(labExam AS VARCHAR) END 10、事物 BEGIN TRANSACTION DECLARE @userID int,@sumError int SET @sumError = 0 SELECT @userID = UID FROM bbsUsers WHERE Uname = '可卡因' --可卡因发帖 INSERT INTO bbsTopic(TsID,TuID,Tface,Ttopic,Tcontents,Ttime,TclickCount,Tstate,TlastReply) VALUES(2,@userID,4,'.NET配置问题','我的',GETDATE(),200,0,'2009-1-8') SET @sumError = @sumError + @@ERROR --发贴之后.NET版块表加1 IF(@sumError = 0) UPDATE bbsSection SET StopicCount = StopicCount + 1 WHERE Sname LIKE '%.NET%' SET @sumError = @sumError + @@ERROR --判断错误是否是0，不是发帖不成功 IF(@sumError <> 0) BEGIN print '您所发的帖子不成功!' ROLLBACK TRANSACTION END --错误号不是0说明发帖成功 ELSE BEGIN print '发帖成功' COMMIT TRANSACTION END 事物会锁定表,独占此表,只有执行完后,才能对此表进行操作 11、索引 if exists(select * from sysobjects where name='myIndex') drop index myIndex create nonclustered myIndex --非聚集索引 on 表名(列名) 12、视图 IF EXISTS(SELECT * FROM sysobjects WHERE name = 'myview') DROP VIEW myview GO CREATE VIEW myview AS ( SELECT * from orders ) 视图不存放数据,仅仅是映射,即引用原表中的数据而已 对视图执行update语句,原表中的数据会改变,而虚拟表不会 13、触发器 IF EXISTS(SELECT * FROM sysobjects WHERE name = 'trig_bank') DROP TRIGGER trig_bank GO CREATE TRIGGER trig_bank ON bank FOR INSERT AS ...... 往bank表中插入数据时执行触发器的begin到end 14、存储过程 F EXISTS(SELECT * FROM sysobjects WHERE name = 'proc_stu') DROP PROCEDURE proc_stu GO CREATE PROCEDURE proc_stu @notpassnum int OUTPUT,--输出参数，未通过人数 @writtenPass int = 60, --输入参数笔试及格线 @labPass int =60 --输入参数，机试及格线 AS ...... 15、创建一个自定义函数 CREATE FUNCTION fun_myfun ( @num int --参数 ) returns int --返回类型 AS --begin和end不能省 BEGIN SET @num = @num + 1 RETURN @num --返回值 END SELECT dbo.fun_myfun(5) --调用函数 16、往一个表里插入数据的两种方法 <1>select A.列,A.列 into B --B表事先不存在 from A <2>insert into B(列,列) select 列,列 from A --B表事先要存在,并且数据类型和个数都要匹配 17、char和varchar的区别 char:若定义一个字符大小为10,若只写一个字符1,在1后就有9个空格 varchar:若定义一个字符大小为10,若只写一个字符1,它就只占一个字符的位置 所以varchar可节省磁盘空间 但是要想吧字符1改成1111,就必须吧原来的往后移动,腾出空间,所以效率低 一般：50个字符以内用char 50个字符以上用varchar update 表名 set 列名=null --将这个列的数据都置为null 18、在结果集中改变列名的三种方法 <1>select codeId as '学员编号' --可不加引号 <2>select codeId '学员编号' --因为as可以省略 <3>select '学员编号'=codeId 查询是按百分比提取用关键字percent select top 20 percent * from student 应该是<1>where <2>group by <3>having的使用顺序 where和having都是过滤条件 区别是条件中有聚合函数时,只能用having,否则用where (责任编辑：IT) |