sqlserver常用知识

–
SQL Server(MSSQLSERVER)启动出现 17058的错误,双击服务点击登录,选择本地系统账户即可
–
SQL Server(MSSQLSERVER)启动出现 17058的错误,双击服务点击登录,选择本地系统账户即可

–删除主键
alter table 表名 drop constraint 主键名
eg:alter table test drop constraint id
–添加主键
alter table 表名 add constraint 主键名 primary key(字段名1,字段名2……)
eg:alter table computer_auto add constraint id primary key(id)

–添加非聚集索引的主键
alter table 表名 add constraint 主键名 primary key NONCLUSTERED(字段名1,字段名2……)

create index idx_zd on tablename(zd)

drop index idx_zd on tablename

–1、获取字符的ASCII码 ASCII
select ascii(‘a’)
–2、获取ASCII码对应的字符 Char
select char(97)
–3、获取字符的unicode编码 Unicode
select unicode(‘飞’)
–4、获取unicode编码对应的字符nchar
select nchar(39134)
–5、获取字符串第一次出现位置PatIndex
select patindex( ‘%天%’ , ‘今天好冷’ )–这里的百分号不能去掉
–6、生成空格字符串SPACE–生成任意多个空格组成的字符串
select space(70)
–7、按指定次数重复生成字符串REPLICATE
select replicate(‘sql’,3)
–8、截取字符串SUBSTRING
select substring( ‘jiequwo’,2,3)
–9、获取字符串长度LEN
select len(‘天下之大，无奇不有’)
–10、替换字符串内容STUFF
–stuff(source_character_expression，start，length，destination_character_expression)
select stuff(‘SqlServer’,2,3,’xjl’)
–11、指定位置搜索字符串中的内容CHARINDEX
select charindex(‘ql’,’sqlserver’)
–12、生成带分隔符的unicode字符串 QUOTENAME
–分隔符可以是单引号(‘)、左右方括号([])或者英文双引号(“)。
–如果不指定，则使用方括号。带有方括号的Unicode字符串如：[hello]。

select quotename(‘我是一个兵’) – 输出[我是一个兵]
select quotename(‘我是一个兵’,’”‘) – 输出”我是一个兵”

–13、转换浮点数字为字符串 STR

–str(float_expression[,length[,decimal]])
–float_espression：带小数点的近似数字(float)数据类型的表达式。
–length：总长度。它包括小数点、符号、数字以及空格。默认值为10.
–decimal：小数点后的位数。decimal必须小于或等于16.如果decimal大于16，
–则会截断结果，使其保持为小数点后只有16位。

select str(‘123.4’)
select str(123.456789)– 输出 123
–select len( convert(varchar,(select str(123.45678))))
select str(123.456789,7) – 输出 123 注意前面的是4个空格，因为不写小数点后保留多少位，所以自动填充空格
select str(123.456789,7,3) – 输出 123.457 最后一位 四舍五入了，总长度为7小数点后保留3位
–14、截取左边字符串 LEFT
select left(‘123456789’,3) –输出123
–15、截取右边字符串RIGHT
select right(‘123456789’,3)–输出789
–16、清除左边空格LTRIM
–17、清除右边空格RTRIM
–18、转换为小写字符串LOWER
select lower(‘A’)
–19、转换为大写字符串UPPER
SELECT UPPER(‘a’)
–20、反序字符串REVERSE
select reverse(‘abcd’)
–21、获取字符串字节数DATALENGTH
select datalength(‘今天是星期六！’)
–22、SOUNDEX 返回一个由四个字符组成的代码 (SOUNDEX)，用于评估两个字符串的相似性。
–select soundex(‘abcde’,’aed’)
–23、REPLACE 用另一个字符串值替换出现的所有指定字符串值。
select replace(‘今天不是很高兴’,’不是’,’’)
–24、DIFFERENCE 返回一个整数值,指示两个字符表达式的 SOUNDEX 值之间的差异。
–SELECT DIFFERENCE(‘abcde’,’abc’)–4

–查询某个表的所有字段信息
select *from sys.all_columns where object_id=(select object_id(‘test’))

–改成当前库
use code
–创建变量 指定要查看的表
declare @table_id int
set @table_id=object_id(‘test’)
–执行
dbcc showcontig(@table_id)

–查看数据库实例
select *from sys.databases
–查看数据库磁盘使用情况
exec sp_spaceused
– 查看某个数据对象的大小
EXEC sp_spaceused @objname;
eg:sp_spaceused ‘test’–表名
/查看数据库服务器名称-start-/
– 默认实例
SELECT @@SERVERNAME AS SERVERNAME; SELECT SERVERPROPERTY(‘servername’) AS ServerName
SELECT srvname AS ServerName FROM sys.sysservers; SELECT SERVERPROPERTY(‘MachineName’) AS ServerName
– 命名实例
SELECT SUBSTRING(@@SERVERNAME, 0, CHARINDEX(‘', @@SERVERNAME))AS SERVERNAME
SELECT SUBSTRING(CONVERT(VARCHAR(100),SERVERPROPERTY(‘servername’)), 0, CHARINDEX(‘',CONVERT(VARCHAR(100),SERVERPROPERTY(‘servername’)))) AS ServerName
SELECT SUBSTRING(srvname, 0, CHARINDEX(‘', srvname)) AS ServerName FROM sys.sysservers
SELECT SERVERPROPERTY(‘MachineName’) AS ServerName–括号中的参数可以换成其它
/查看数据库服务器名称-end-/
–查看数据库启动的相关参数
EXEC sp_configure

–查看服务器启动时间
SELECT CONVERT(VARCHAR(30), LOGIN_TIME,120) AS StartDateTime
FROM master..sysprocesses WHERE spid=1

–查看数据库的用户和进程信息
– 查看所有
EXEC sp_who
– 查看活动用户和进程
EXEC sp_who ‘active’

–查看所有数据库用户登录信息
EXEC sp_helplogins
–查看所有数据库用户所属的角色信息
EXEC sp_helpsrvrolemember
–查看链接服务器
EXEC sp_helplinkedsrvlogin

–查看表的相关信息
EXEC sp_help ‘TABLE_NAME’;

–查看数据库服务器各数据库日志文件的大小及利用率/状态
DBCC sqlperf(logspace)
–或
EXEC (‘DBCC SQLPERF(LOGSPACE)’)
–查看当前数据库的文件状态(包括库数据文件存放地址)
EXEC (‘DBCC showfilestats’)

–查看数据库存储过程
– 方法1
EXEC sp_stored_procedures
– 方法2
SELECT * FROM sys.procedures
– 方法3
SELECT * FROM sys.sysobjects WHERE xtype=’P’

–检查数据库完整性
DBCC checkdb()
eg:dbcc checkdb(‘code’)
– Tablock选项提高速度
DBCC checkdb() with tablock
–查看数据库所在机器操作系统参数（数据库版本等信息）
EXEC master..xp_msver

– 重新组织索引
ALTER INDEX [IndexName] ON [TableName]
REORGANIZE
WITH ( LOB_COMPACTION = ON )

– 批量生成重组索引
use test
go

select ‘DBCC INDEXDEFRAG(‘+db_name()+’,’+o.name+’,’+i.name + ‘);’
–,db_name(),
–o.name,
–i.name,
–i.*
from sysindexes i
inner join sysobjects o on i.id = o.id
where o.xtype = ‘U’
and i.indid >0
and charindex(‘WA_Sys’,i.name) = 0

–重建索引
ALTER INDEX [IndexName] ON [TableName]
REBUILD PARTITION = ALL
WITH (
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON,
ONLINE = OFF,
SORT_IN_TEMPDB = OFF
)

–1、查询当前数据库中的用户表
select * from sysobjects where xtype=’U’;

–2、获取SQL Server允许同时用户连接的最大数
SELECT @@MAX_CONNECTIONS;

–如果你想自己设置服务器的最大连接数，可以使用以下SQL
–exec sp_configure ‘show advanced options’, 1
–修改当前数据库用户的最大链接数
–exec sp_configure ‘user connections’, 500

–3、获取当前指定数据库的连接信息
SELECT * FROM master.dbo.sysprocesses WHERE dbid IN
(
SELECT dbid FROM master.dbo.sysdatabases
WHERE NAME=’YourDataBaseName’
)

–根据需要更改YourDataBaseName
–4、获取当前SQL服务器所有的连接详细信息 查询结果包含了：系统进程和用户进程。
–如果只是想查用户进程的话则需采用下面的方法
SELECT * FROM sysprocesses

–5、获取自上次启动 SQL Server服务 以来连接或试图连接的次数
SELECT @@CONNECTIONS

–6、查看当前数据库所支持的所有字段类型信息
select *from systypes

select *from syslanguages

/* sqlserver 常用的系统表

sysaltfiles    主数据库               保存数据库的文档
syscharsets    主数据库               字符集和排序顺序
sysconfigures  主数据库               配置选项
syscurconfigs  主数据库               当前配置选项

*sysdatabases 主数据库 服务器中的数据库
syslanguages 主数据库 语言
*syslogins 主数据库 登陆帐号信息
sysoledbusers 主数据库 链接服务器登陆信息
*sysprocesses 主数据库 进程
sysremotelogins主数据库 远程登录帐号

syscolumns     每个数据库             列
sysconstrains  每个数据库             限制
sysfilegroups  每个数据库             文档组
sysfiles       每个数据库             文档
sysforeignkeys 每个数据库             外部关键字
sysindexs      每个数据库             索引
sysmenbers     每个数据库             角色成员

*sysobjects 每个数据库 任何数据库对象
syspermissions 每个数据库 权限
*systypes 每个数据库 用户可定义数据类型
sysusers 每个数据库 用户

*/

/*

SQL语法当中 使用inner join 两张表关联之后，条件跟在on 后面和where后面，结果是否一致???
例如:

a inner join b on a.id=b.id and b.create_date>=’2017-05-17’

a inner join b on a.id=b.id where b.create_date>=’2017-05-17’

—> 在 AND 后面加条件是在INNER JOIN 连接的时候作为条件过滤
—> 在 WHERE 后面加条件是INNER JOIN 完成以后再对结果集进行过滤

*/

/sqlserver加密的方式***/
/1.【MD5】加密/

select substring(sys.fn_sqlvarbasetostr(HashBytes(‘MD5’,’123456’)),3,32)

/*2.pwdencrypt加密（不可逆）,pwdcompare进行匹配验证。
create table #temptable(iorder int, pswd varbinary(1024) )
go
insert into #temptable values(1, pwdencrypt(‘yang’))
insert into #temptable values(2, pwdencrypt(‘lian’))
insert into #temptable values(3, pwdencrypt(‘shan’))
go
select * from #temptable
go
– 比较数据是否相等

select * from #temptable
where pwdcompare(‘lian’, pswd)=1
go
drop table #temptable
go

上面的语句中，用到了二个函数：pwdencrypt和pwdcompare，这是二个SQLServer未公开的函数，
pwdencrypt实现对输入数据进行加密后返回二进制形式的加密内容，
而pwdcompare用于检查明文是否与加密的二进制数据内容相等，没有解密函数。
这二个函数主要是用于SQLServer内部自己调用。优点是调用方便，
缺点是这二个函数没有公开，就意味着可能改变，并且不兼容原来的，
在使用上存在风险。

*/

——————-判断数据库中的表是否存在—————————————-
–1.
if object_id(N’tablename’,N’U’) is not null
print ‘存在’
else
print ‘不存在’

–2.
if object_id(N’TEMP_TBL’,N’U’) is not null
print ‘存在’
else
print ‘不存在’

———–判断数据库中的临时表名是否存在——————————————-
–1.
if exists(select * from tempdb..sysobjects where id=object_id(‘tempdb..#department’))
PRINT ‘存在’
ELSE
PRINT’不存在’

–2.
if exists (select * from tempdb.dbo.sysobjects where
id = object_id(N’tempdb..#department’) and type=’U’)
print ‘存在’
else
print ‘不存在’

if exists(select *from tempdb..sysobjects where id=object_id(‘tempdb..#department’))
print ‘1’
else
print’2’

————-查询数据库中存储过程的创建时间——–
select
[name]
,create_date
,modify_date
FROM
sys.all_objects
where
type_desc = N’SQL_STORED_PROCEDURE’
order by create_date desc

—————case when 的两种用法———————————

–1) case zd when ‘’ then ‘’ else ‘’ end as zd1 from table
select CASE orders WHEN 1 THEN 999 ELSE orders END as orders FROM abc
–2)case when zd=’’ then ‘’ else ‘’ end as zd1 from table
select CASE WHEN orders=1 then 999 ELSE orders END as orders from abc

---

——查询表字段说明————–

create table t_introduction
(
fid int identity,
f1 varchar(50) null,–字段1
f2 varchar(50) null,–字段2
f3 varchar(50) null –字段3
)
–查询 右键 表–设计–列属性（说明）中的值
go
begin
declare @tName varchar(50)
set @tName=’t_introduction’
SELECT
A.name AS table_name,
B.name AS column_name,
C.value AS column_description
FROM sys.tables A
INNER JOIN sys.columns B ON B.object_id = A.object_id
LEFT JOIN sys.extended_properties C ON C.major_id = B.object_id
AND C.minor_id = B.column_id
WHERE A.name =@tName

end

–循环查询某数据库中表对应字段的属性说明值
go
begin
declare @databaseName varchar(50)
declare @tableName varchar(50)
declare cur_database cursor for
select name from master..sysdatabases order by name
open cur_database

fetch next from cur_database into @databaseName 

while @@fetch_status<>-1
begin

 declare cur_tablename cursor for
 select name,* from sysobjects  where type='U' order by name 
begin	 	  
  select
    A.name AS table_name,
    B.name AS column_name,
    C.value AS column_description
    FROM sys.tables A
    INNER JOIN sys.columns B ON B.object_id = A.object_id
    LEFT JOIN sys.extended_properties C ON C.major_id = B.object_id 
    AND C.minor_id = B.column_id
    WHERE A.name =@tableName
  fetch next from cur_solve into @name

end
close cur_solve
deallocate cur_solve

end
end

—–查询当前服务器中所有的数据库名称信息——
select *from master..sysdatabases

– 在code数据库下，查询当前数据库中所有的表名
select *from sysobjects where xtype=’u’–1）
select *from sys.tables –2）

–查询某个表中所有的字段信息—
select *from sys.all_columns where object_id=(select object_id(‘abc’))

—特殊用法 goto,运行下面的sql看看有什么结果 –

go
begin
declare @a int
declare @b int
declare @c int
set @a=1
set @b=2
set @c=4

if @c<>@a+@b goto  breakInfo--这个名称随意，但要和下文 breakInfo: 匹配
else
 print @c

breakInfo:
print ‘a+b<>3’
end

---

select Datename(weekday,’2019-02’+’-0’+Cast(‘9’ AS CHAR) )–查询2019年2月9日是星期几
select datename(WEEKDAY,’2019-02’+’-12’)
–当字符串符合 YYYY(年)-MM/M(月)–DD/D(日),这样的字符串格式时候，转换成日期格式就不会报错
–像这种：2019-10 这样的格式转换为日期格式就会提示失败

select datepart(dd,getdate())–获取当前日
select datepart(d,getdate())

select datepart(mm,getdate())–获取当前月
select datepart(m,getdate())–获取当前月份

select datepart(yy,getdate())–获取当前年

select datepart(wk,getdate())–获取当前是第多少周
select datepart(ww,getdate())

select datepart(qq,getdate())–季度

select datepart(y,getdate())–获取一年当中的第几天
select datepart(dy,getdate())

select DATEPART(dw,getdate())–4:星期三

select datepart(hh,getdate())–获取当前时间整时数

select datepart(mi,getdate())–获取当前时间整分数
select datepart(n,getdate())

select datepart(ss,getdate())–获取当前时间秒数
select datepart(s,getdate())

select datepart(ms,getdate())–获取当前时间毫秒数

---

use master
restore database [Wireless_KQTest] with norecovery
–方法后仍然显示正在还原
–则尝试 又操作一遍还原数据库，则可能不会报错而还原成功

–判断当前数据库中的某表是否存在
IF Object_id(‘table_name’,’u’) IS NOT NULL
print ‘表：table_name 存在!’

–判断某链接服务上的表是否存在
DECLARE @i int
exec(‘select ?=OBJECT_ID(N’’server_database.dbo.sever_table’’)’,@i OUTPUT) AT [server_name]
if @i is not null
print ‘链接服务器：server_name 上的数据库 server_database 中的表 sever_table 存在 ‘

SqlServer索引的创建、查看、删除
索引加快检索表中数据的方法，它对数据表中一个或者多个列的值进行结构排序，是数据库中一个非常有用的对象。

使用CREATE 语句创建索引
CREATE INDEX index_name ON table_name(column_name,column_name) include(score)

普通索引
CREATE UNIQUE INDEX index_name ON table_name (column_name) ;
eg:create unique index idx_id on computer_auto(id)

非空索引
CREATE PRIMARY KEY INDEX index_name ON table_name (column_name) ;

主键索引
使用ALTER TABLE语句创建索引

alter table table_name add index index_name (column_list) ;
alter table table_name add unique (column_list) ;
alter table table_name add primary key (column_list) ;–主键索引的创建方式

删除索引
drop index index_name on table_name ;
alter table table_name drop index index_name ;
alter table table_name drop primary key ;
********/

–创建索引
create –unique:唯一, clustered:聚集,nonclustered:非聚集
index idx_thing on test(thing)
create index idx_name on test(name)

create unique/clustered/nonclustered
index idx_id on computer_auto(id)

–查看索引–
–use database_name
EXEC Sp_helpindex test –EXEC Sp_helpindex table_name

–索引的修改
–eg:exec sp_rename N’test1.uniquenonclus1’,N’uniquenonclus2’,N’index’;
exec sp_rename N’test.idx_thing’,N’idx_things’,N’index’; –exec sp_rename N’表名.索引名’,N’新索引名’,N’index’;

–索引的删除(可行)
drop index test.index_name,test.index_thing

/*索引的创建

–#1使用企业管理器创建

–启动企业管理器–选择数据库——选在要创建索引的表——在表的下拉菜单中选择索引—在快捷菜单中选择新建索引–

–在新建索引对话框中单击“添加”按钮，弹出“从列表中选择列”，在该对话框中选择要添加到索引键的表列

–单击确定返回新建索引对话框，再点击确定完成索引的创建。

#2使用T-sql语句创建索引。

create index语句为给定表或视图创建一个改变物理顺序的聚集索引，也可以创建一个具有查询功能的非聚集索引。语法格式如下：

create [unique] [clustered] [nonclustered] index index_name

on {tabel/view} (column[dese/asc][….n])

注： [unique] [clustered] [nonclustered]表示要创建索引的类型，以此为唯一索引，聚集索引，和非聚集索引，
当省略unique选项时，建立非唯一索引.当省略clustered,nonclustered选项时.建立聚集索引，省略nonclustered选项时，
建立唯一聚集索引。

使用索引虽然可以提高系统的性能，增强数据检索速度，但它需要占用大量的物理存储空间，建立索引的一般原则如下：

（1）只有表的所有者可以在同一表中创建索引。
（2）每个表中只能创建一个聚集索引。
（3）每个表中最多可以创建249个非聚集索引。
（4）在经常查询的字段上建立索引。
（5）定义text,image,bit数据类型的列上不要建立索引。
（6）在外间上可以建立索引。
（7）在主键列上一定要建立索引。
（8）在那些重复的值比较多，查询较少的列上不要建立索引。

查看索引

#1使用企业管理器查看索引，步骤如下：

（1）启动SQL Server management Studio并连接到SQLServer 2008数据库

（2）选择指定的数据库,|展开要查看索引的表。

（3）右击该表，在弹出快捷菜单中选择‘设计’命令

（4）弹出‘表结构设计’对话框，右击该对话框，在弹出的快捷菜单中选择‘索引/键’命令

（5）打开“索引/键”对话框，在对话框左侧选中某个索引，在对话框的右侧就可以查看此索引的信息，并可以修改相关信息。

#2使用系统存储过程查看索引，语法格式如下：

use database_name

EXEC Sp_helpindex table_name

#3利用系统表查看索引信息

查看数据库中指定表的索引信息，可以利用该数据库中的系统表sysobjects和sysindexes进行查询，系统表sysobjects可以根据表明查找到索引表的ID号，再利用系统表sysindexes根据ID号查找到索引文件的相关信息。

索引的修改

#1使用企业管理器修改索引。

#2使用T-SQL语句更改索引名称，语法格式如下：

exec sp_rename N’表名.索引名’,N’索引名’,N’index’;

注：要对索引进行重命名时，需要修改的索引名格式必须为“表名.索引名”

索引的删除

#1使用企业管理器删除索引

#2使用T-SQL语句删除索引,语法格式如下：

drop Index table_name.index_name,…..n

注：drop index语句不能删除通过PRINARY KEY和UNIQUE约束创建的索引，若要删除该约束相应的索引，请使用带有DROP CONSTRAINT子句的ALTER TABLE.
*/

【任务最终的用法】
–1.profilter 保存到文件 D:\Sqlserver-Profilter任务追踪\1.trc
–2.提取表记录然后插入到新表
drop table TraceFileDataTable
SELECT * INTO TraceFileDataTable
FROM fn_trace_gettable(‘D:\Sqlserver-Profilter任务追踪\1.trc’, default);
GO
–3.查看sql执行日志耗时长的命令
select TextData,Duration,CPU from TraceFileDataTable
where EventClass=12 – 等于12表示BatchCompleted事件
and CPU<(0.4*Duration) –如果cpu的占用时间，小于执行sql语句时间的40%，说明该语句等待时间过长