sqlserver dba

select * from sys.databases
--查看有关数据库文件的信息 
select * from sys.database_files;
--查看有关数据库组的信息。 
select * from sys.filegroups;
--查看数据库文件的基本信息和状态信息。
select * from sys.master_files;
--数据库和文件目录视图查看有关数据库的基本信息。
select * from sys.databases;
--查看当前数据库下所有表名
select * from sysobjects where xtype = 'U'
--查看当前数据库下目标表的所有字段名称
select * from syscolumns where id=OBJECT_ID('wjq')
--查看当前数据库设置（包库默认隔离级别等）
DBCC USEROPTIONS  
--查看端口号
exec sys.sp_readerrorlog 0,1,'listening'

use master
select * from sysobjects where xtype = 'u'
sp_helprole
sp_helpsrvrole
sp_helpuser
SP_HELPSRVROLEMEMBER 服务器角色 

use ESI_操作
--创建用户
create login lw with password=N'123',default_database=[ESI_操作]
exec sp_addlogin N'tony','123'
--使其成为当前数据库的合法用户
exec sp_grantdbaccess N'tony'
exec sp_grantdbaccess N'lw'

--授权
grant select on [202003dayair_aport_university_degree] to lw
grant delete on [202003dayair_aport_university_degree] to lw
grant update on [202003dayair_aport_university_degree] to lw
grant insert on [202003dayair_aport_university_degree] to lw
grant select,update,insert,delete on [202003dayair_aport_university_degree] to tony
grant create table,create view,create proc to tony
--禁止操作
deny select,insert to tony
deny create table,create view,create proc to tony
--删除所有授权信息
revoke select,insert,update,delete to tony
--删除用户
exec sp_dropuser'lw'
EXEC sp_revokedbaccess N'tony' --移除用户对数据库的访问权限
--或者
EXEC sp_droplogin N'tony' --删除登录用户
drop user lw
drop login lw

select name from sysusers where status = '2' and islogin = '1'
/**
--查看表字段名和类型
select sc.name,st.name from syscolumns sc,systypes st 
where sc.xtype = st.xtype and sc.id in 
(select id from sysobjects where xtype = 'U' and name='wjq')
**/

--收缩数据库日志文件大小
DBCC SHRINKFILE('拆分表_log',1)
--查看数据库下所有表扫描密度，碎片化情况
DBCC SHOWCONTIG
--查看数据库下表扫描密度，碎片化情况
DBCC SHOWCONTIG(表名)

--收缩日志文件
DBCC SHRINKFILE ('TestDB_log',1)

--删除存在连接数据库的过程
use master --换到其他数据库
ALTER DATABASE [TestDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
--首先将数据库改为单用户模式，WITH ROLLBACK IMMEDIATE提示切断所有其它连接到[TestDB]的数据库连接
/*
alter database [testdb] set multi_user --多用户
*/
drop database [TestDB]

--备份数据库，还原之后，表中的索引都在，表结构应该不发生改变。

--1.在查询分析器里执行查看实际执行集合、IO和TIME占用情况 首先打开IO和TIME统计开关
--先清空缓存
checkpoint
DBCC DROPCLEANBUFFERS 
DBCC FREEPROCCACHE 

SET  STATISTICS TIME ON
SET  STATISTICS IO ON
--sql语句
SET  STATISTICS TIME OFF
SET  STATISTICS IO OFF

-------------------备份还原-----------------

--备份数据库
BACKUP DATABASE testdb2 to disk='D:/db.bak' with format
go
--备份数据库日志
BACKUP LOG testdb2 to disk='D:/db_log.bak' with format
go

SELECT GETDATE()

--还原数据库备份
RESTORE DATABASE TESTDB2 FROM DISK='D:/db.bak' with replace,norecovery
--还原数据库日志文件，指定时间点。
RESTORE LOG testdb2 from disk='D:/db_log.bak' with RECOVERY,STOPAT=N'2020-03-25 17:12:48.787'

ALTER DATABASE testdb2 set OFFLINE WITH ROLLBACK IMMEDIATE

select * from sysobjects where xtype = 'u' 

ALTER DATABASE TESTDB2 SET ONLINE
--导入mdb
select * into [top] from   openrowset('Microsoft.ACE.OLEDB.12.0',  'C:\Users\admin\Desktop\esi0519.mdb'; 'admin';'',[top]) 

--数据库导入txt文件。
Bulk insert 要更新的06 from 'C:\Users\admin\Desktop\cza\要更新的06.txt' with (FieldterMinator ='||', RowterMinator= '\n') 

--数据库导出到txt文件
EXEC master..xp_cmdshell 'bcp "[zhihu]..[190802排名统计]" out D:\190802排名统计.txt -c -t ";|" -S "DESKTOP-QN4P9LC" -U sa -P 123456' 

/**

--常用函数
DECLARE @FullName       VARCHAR(100)
DECLARE @Alias          VARCHAR(20)

SET @FullName = 'Jeffcky Wang'
SET @Alias = ' "Superman" '
SELECT STUFF(@FullName, CHARINDEX(' ', @FullName), 1, @Alias) AS [FullName]

CHARINDEX ( expressionToFind , expressionToSearch [ , start_location ] )
--大小写敏感
select charindex('test','this Test is Test'COLLATE Latin1_General_CS_AS)
--大小写不敏感
select charindex('Test','this Test is Test'COLLATE Latin1_General_CI_AS)

Difference()：用于返回一个整数值，指示两个字符表达式的  SOUNDEX 值   之间的差异。（即两个字符串的相似度）那么什么是 SOUNDEX 值呢？先记着，下面轮到它了。
select difference('123456','整数')

Soundex()：用于返回指定字符串的 SOUNDEX 值。SOUNDEX是一种语音算法，利用英文字的读音计算近似值，值由四个字符构成，第一个字符为英文字母，后三个为数字。
select soundex('string')    -- 返回 S215

Lower()：用于返回指定英文字符串的小写形式的字符串。
select lower('ABC')        -- 返回 abc

Upper()：用于返回指定英文字符串的大写形式的字符串。
select upper('abc')        -- 返回 ABC

Ltrim()：用于返回删除前导空格之后的字符串。
Rtrim()：用于返回截断尾随空格之后的字符串。

Replace()：用第三个表达式替换第一个字符串表达式中出现的所有第二个给定字符串表达式
select replace('123abc456','abc','xxx') 

Unicode()：根据 Unicode 标准返回指定字符或字符串的第一个字符的整数值。
select unicode('a') 

NChar()：根据 Unicode 标准返回指定整数值代码的 Unicode 字符
select nchar(97)    -- 返回 a

Char()：将指定的 int 类型的值转换为 ASCII 代码。
select char(99)    -- 返回 c

ASCII()：返回指定字符或字符串的第一个字符的 ASCII 代码值。
select ascii('A')    -- 返回 65

Str()：返回一个指定数值的等效字符串
select len(str(123.10,20,5))    -- 返回 20

Space()：用于返回指定数值长度的空格字符串。
select '1'+space(3)+'2'        -- 返回 1   2

Reverse()：用于对指定字符串进行反转，按照单个字符进行反转。
select reverse('123')    -- 返回 321

Replicate()：用于返回一个对指定字符串重复指定次数后的字符串。
select replicate('123',3)    -- 返回 123123123

Quotename()：返回一个 Unicode 字符串，根据指定分隔符，返回一个有效的字符串标识符
select quotename('123','"')        -- 返回 "123"

Patindex()：返回指定字符串表达式中指定模式第一次出现的起始位置，未找到则返回0。
select Patindex('%abc%','123abc456abc789') 

Parsename()：返回对象名称的指定部分。
select parsename('server.dbo.dbTest.table',1) tablename,
       parsename('server.dbo.dbTest.table',2) dbname,
       parsename('server.dbo.dbTest.table',3) schemaname,
       parsename('server.dbo.dbTest.table',4) servername

Getdate()：获取当前日期时间。
select getdate()

Year()：获取指定日期表达式的年。
select year('2016-01-01')    -- 返回 2016

Month()：获取指定日期表达式的月。
select month('2016-01-01')    -- 返回 1

Day()：获取指定日期表达式的日。
select day('2016-01-01')    -- 返回 1

Isdate()：用于判断指定的字符串表达式是否为日期。
select isdate('10/30/2017')        -- 月/日/年        返回 1

Datename()：返回指定日期的指定日期的部分的字符串。
select datename(year,'2017-01-01 10:10:10')    -- 返回 2017
select datename(yyyy,'2017-01-01 10:10:10')    -- 返回 2017
select datename(month,'2017-01-01 10:10:10')    -- 返回 01
select datename(mm,'2017-01-01 10:10:10')    -- 返回 01 
select datename(quarter,'2017-05-02 10:10:10')        -- 表示一年的第几个季度        返回 2
select datename(weekday,'2017-02-02 10:10:10')        -- 表示一周的星期几        返回 星期四
select datename(week,'2017-02-02 10:10:10')        -- 表示一年的第几周        返回 5
select datename(dayofyear,'2017-02-02 10:10:10')        -- 表示一年的第几天        返回 33
select datename(hh,'2017-01-01 10:30:25')    -- 返回 10

Datepart()：返回指定日期的指定日期的部分的整数。
select datepart(quarter,'2017-05-02 10:10:10')        -- 表示一年的第几个季度        返回 2
select datepart(weekday,'2017-02-02 10:10:10')        -- 表示一周的星期几,即一周的第几天,星期日为一周的第一天    返回 5

Coalesce()：返回所有参数中的第一个非空（not null）表达式。
SELECT COALESCE(null,null,'123','abc')    -- 返回 123

ISNull()：判断指定的表达式一是否为空（null），如果为空则返回表达式二的值，否则返回表达式一的值，类似于C#中的三元运算符。
select isnull('','123')        -- 返回 空字符串
select isnull(null,'123')        -- 返回 123
select isnull('123','abc')        -- 返回 123
select isnull(null,null)    -- 返回 null

**/

/**
SQL Server一共有5个系统数据库：
master：记录SQL Server系统的所有系统级信息,例如：登陆账户信息、链接服务器和系统配置设置、记录其他所有数据库的存在、数据文件的位置、SQL Server的初始化信息等。如果master数据库不可用，则无法启动SQL Server。
msdb：用于SQL Server代理计划警报和作业。数据库定时执行某些操作、数据库邮件等。
model：用作SQL Server实例上创建的所有数据库的模板。对model 数据库进行的修改（如数据库大小、排序规则、恢复模式和其他数据库选项）将应用于以后创建的所有数据库。在model数据库中创建一张表，则以后每次创建数据库的时候都会有默认的一张同样的表。
tempdb：一个工作空间，用于保存临时对象或中间结果集。一个全局资源，可供连接到 SQL Server 实例的所有用户使用。每次启动 SQL Server 时都会重新创建 tempdb
Resource：一个只读数据库，包含 SQL Server 包括的系统对象。系统对象在物理上保留在 Resource 数据库中，但在逻辑上显示在每个数据库的 sys 架构中。Resource 数据库的物理文件名为 mssqlsystemresource.mdf 和 mssqlsystemresource.ldf。这些文件位于 <驱动器>:\Program Files\Microsoft SQL Server\MSSQL10_50.<instance_name>\MSSQL\Binn\ 中。每个 SQL Server 实例都具有一个（也是唯一的一个）关联的 mssqlsystemresource.mdf 文件，并且实例间不共享此文件。

数据库三层架构
一：界面层
界面层提供给用户一个视觉上的界面，通过界面层，用户输入数据、获取数据。界面层同时也提供一定的安全性，确保用户不用看到不必要的机密信息。
二：逻辑层
逻辑层是界面层和数据层的桥梁，它响应界面层的用户请求，执行任务并从数据层抓取数据，并将必要的数据传送给界面层。
三：数据层
数据层定义、维护数据的完整性、安全性，它响应逻辑层的请求，访问数据。这一层通常由大型的数据库服务器实现，如Oracle 、Sybase、MS SQl Server等。
三层架构的好处：
从开发角度和应用角度来看，三层架构比双层或单层结构都有更大的优势。三层结构适合群体开发，每人可以有不同的分工，协同工作使效率倍增。开发双层或单层应用时，每个开发人员都应对系统有较深的理解，能力要求很高，开发三层应用时，则可以结合多方面的人才，只需少数人对系统全面了解，从一定程度工降低了开发的难度。
三层架构属于瘦客户的模式，用户端只需一个较小的硬盘、较小的内存、较慢的CPU就可以获得不错的性能。相比之下，单层或胖客户对面器的要求太高。
三层架构的另一个优点在于可以更好的支持分布式计算环境。逻辑层的应用程序可以有多个机器上运行，充分利用网络的计算功能。分布式计算的潜力巨大，远比升级CPU有效。
三层架构的最大优点是它的安全性。用户端只能通过逻辑层来访问数据层，减少了入口点，把很多危险的系统功能都屏蔽了。
另外三层架构还可以支持如下功能：Remote Access(远程访问资料)，例如可透过Internet存取远程数据库；High Performance(提升运算效率)解决集中式运算(Centralize)及主从式架构(Client-Server)中，数据库主机的运算负担，降低数据库主机的Connection Load，并可藉由增加App Server处理众多的数据处理要求，这一点跟前面讲到的分布式计算提高运算能力是一个道理；Client端发出Request(工作要求)后，便可离线，交由App Server和DataBase Server共同把工作完成，减少Client端的等待时间。
**/

/**
--元数据的定义是“关于数据的数据”，但刚接触这个定义，还是不好理解元数据和数据的关系。这里给大家分享一句《DAMA数据管理知识体系指南》中的解释，元数据与数据的关系就像数据与自然界的关系，数据反映了真实世界的交易、事件、对象和关系，而元数据则反映了数据的交易、事件、对象和关系等。简单来说，只要能够用来描述某个数据的，都可以认为是元数据。
简言之，元数据就是关于数据的数据。

要实现企业元数据管理有两大基本步骤，一是创建和维护元数据，搞清楚要管理哪些元数据以及这些元数据在什么地方，以何种形态存储，他们之间有有着怎样的联系。二是建立元数据的模型（元模型），要抽象出企业的元模型，建立个元模型之间的逻辑关系。

通过元数据管理，将帮助企业人员清晰的看到企业有哪些数据，分别存放在什么位置，同时帮助理清企业的数据字典，快速查询和定位数据；
通过对数据的上下文关联信息，提升战略信息（如数据仓库、CRM、SCM等）的价值，从而帮助分析人员做出更有效的决策；
通过对数据的上下文背景、历史和起源进行完整的记录并文档化，帮助了解数据的流转流程，从而减少培训成本，降低员工流失的影响；
在变更管理过程中的不同层面上进行更好的影响分析，降低项目失败风险；
识别并减少冗余数据和流程，减少重复工作和对冗余、过期、不正确数据的试用；
为企业的数据治理、数据应用、数据服务打好基础。

**/

/**
数据库优化

1.1：增加次数据文件

         从SQL SERVER 2005开始，数据库不默认生成NDF数据文件，一般情况下有一个主数据文件(MDF)就够了，但是有些大型的数据库，由于信息很多，而且查询频繁，所以为了提高查询速度，可以把一些表或者一些表中的部分记录分开存储在不同的数据文件里

         由于CPU和内存的速度远大于硬盘的读写速度，所以可以把不同的数据文件放在不同的物理硬盘里，这样执行查询的时候，就可以让多个硬盘同时进行查询，以充分利用CPU和内存的性能，提高查询速度。 在这里详细介绍一下其写入的原理，数据文件(MDF、NDF)和日志文件(LDF)的写入方式是不一样的：

          数据文件：SQL Server按照同一个文件组里面的所有文件现有空闲空间的大小，按这个比例把新的数据分布到所有有空间的数据文件里，如果有三个数据文件A.MDF，B.NDF，C.NDF，空闲大小分别为200mb，100mb，和50mb，那么写入一个70mb的东西，他就会向ABC三个文件中一次写入40、20、10的数据，如果某个日志文件已满，就不会向其写入

         日志文件：日志文件是按照顺序写入的，一个写满，才会写入另外一个

由上可见，如果能增加其数据文件NDF,有利于大数据量的查询速度，但是增加日志文件却没什么用处。

1.2：设置文件自动增长(大数据量，小数据量无需设置)
        1)要设置成按固定大小增长，而不能按比例。这样就能避免一次增长太多或者太少所带来的不必要的麻烦。建议对比较小的数据库，设置一次增长50 MB到100 MB。对大的数据库，设置一次增长100 MB到200 MB。

        2)要定期监测各个数据文件的使用情况，尽量保证每个文件剩余的空间一样大，或者是期望的比例。

        3)设置文件最大值，以免SQL Server文件自增长用尽磁盘空间，影响操作系统。

        4)发生自增长后，要及时检查新的数据文件空间分配情况。避免SQL Server总是往个别文件写数据。

          因此，对于一个比较繁忙的数据库，推荐的设置是开启数据库自动增长选项，以防数据库空间用尽导致应用程序失败，但是要严格避免自动增长的发生。同时，尽量不要使用自动收缩功能。
1.3 数据和日志文件分开存放在不同磁盘上

            数据文件和日志文件的操作会产生大量的I/O。在可能的条件下，日志文件应该存放在一个与数据和索引所在的数据文件不同的硬盘上以分散I/O，同时还有利于数据库的灾难恢复。
优化②：表分区，索引分区 (优化①粗略的进行了表分区，优化②为精确数据分区)

为什么要表分区？

             当一个表的数据量太大的时候，我们最想做的一件事是什么？将这个表一分为二或者更多分，但是表还是这个表，只是将其内容存储分开，这样读取就快了N倍了

            原理：表数据是无法放在文件中的，但是文件组可以放在文件中，表可以放在文件组中，这样就间接实现了表数据存放在不同的文件中。能分区存储的还有：表、索引和大型对象数据 。
            
 2.1什么时候使用分区表：

     1、表的大小超过2GB。

     2、表中包含历史数据，新的数据被增加到新的分区中。

2.2表分区的优缺点

表分区有以下优点：

     1、改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度。

     2、增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可用;

     3、维护方便：如果表的某个分区出现故障，需要修复数据，只修复该分区即可;

     4、均衡I/O：可以把不同的分区映射到磁盘以平衡I/O，改善整个系统性能。

     缺点：

             分区表相关：已经存在的表没有方法可以直接转化为分区表。不过 Oracle 提供了在线重定义表的功能。
             
2.3表分区的操作三步走

2.31 创建分区函数

   CREATE PARTITION FUNCTION xx1(int)

   AS RANGE LEFT FOR VALUES (10000, 20000);

   注释：创建分区函数：myRangePF2，以INT类型分区，分三个区间，10000以内在A 区，1W-2W在B区，2W以上在C区.

2.3.2创建分区架构

    CREATE PARTITION SCHEME myRangePS2

     AS PARTITION xx1

     TO (a, b, c);

  注释：在分区函数XX1上创建分区架构：myRangePS2，分别为A,B,C三个区间

A,B,C分别为三个文件组的名称，而且必须三个NDF隶属于这三个组，文件所属文件组一旦创建就不能修改

2.3.3 对表进行分区

       常用数据规范--数据空间类型修改为：分区方案，然后选择分区方案名称和分区列列表，结果如图所示：

也可以用sql语句生成

       CREATE TABLE [dbo].[AvCache]( [AVNote] [varchar](300) NULL, [bb] [int] IDENTITY(1,1) ) ON [myRangePS2](bb);

--注意这里使用[myRangePS2]架构，根据bb分区

2.3.4查询表分区

       SELECT *, $PARTITION.[myRangePF2](bb) FROM dbo.AVCache   
2.3.5创建索引分区
CREATE NONCLUSTERED INDEX idx_on_othr_fileGroup ON Person.Address(Postalcode) ON Index_store

优化④：整理数据库碎片   
Scan Density [Best Count:Actual Count]-扫描密度[最佳值:实际值]：DBCC SHOWCONTIG返回最有用的一个百分比。这是扩展盘区的最佳值和实际值的比率。该百分比应该尽可能靠近100%。低了则说明有外部碎片。

                   Logical Scan Fragmentation-逻辑扫描碎片：无序页的百分比。该百分比应该在0%到10%之间，高了则说明有外部碎片。

解决方式：

      一是利用DBCC INDEXDEFRAG整理索引碎片

      二是利用DBCC DBREINDEX重建索引。                
**/

/**
分区表的话，表数据就会按照你指定的规则分放到不同的文件里，把一个大的数据文件拆分为多个小文件，还可以把这些小文件放在不同的磁盘下由多个cpu进行处理。这样文件的大小随着拆分而减小，还得到硬件系统的加强，自然对我们操作数据是大大有利的。
use ESI备份  
---创建数据库文件组
alter database ESI备份 add filegroup ByIdGroup1
alter database ESI备份 add filegroup ByIdGroup2
alter database ESI备份 add filegroup ByIdGroup3
alter database ESI备份 add filegroup ByIdGroup4
alter database ESI备份 add filegroup ByIdGroup5
alter database ESI备份 add filegroup ByIdGroup6
alter database ESI备份 add filegroup ByIdGroup7
alter database ESI备份 add filegroup ByIdGroup8
alter database ESI备份 add filegroup ByIdGroup9
alter database ESI备份 add filegroup ByIdGroup10

--add file

alter database ESI备份 add file 
(name=N'ById1',filename=N'D:\test\ById1.ndf',size=5Mb,filegrowth=5mb)
to filegroup ByIdGroup1
alter database ESI备份 add file 
(name=N'ById2',filename=N'D:\test\ById2.ndf',size=5Mb,filegrowth=5mb)
to filegroup ByIdGroup2
alter database ESI备份 add file 
(name=N'ById3',filename=N'D:\test\ById3.ndf',size=5Mb,filegrowth=5mb)
to filegroup ByIdGroup3
alter database ESI备份 add file 
(name=N'ById4',filename=N'D:\test\ById4.ndf',size=5Mb,filegrowth=5mb)
to filegroup ByIdGroup4
alter database ESI备份 add file 
(name=N'ById5',filename=N'D:\test\ById5.ndf',size=5Mb,filegrowth=5mb)
to filegroup ByIdGroup5
alter database ESI备份 add file 
(name=N'ById6',filename=N'D:\test\ById6.ndf',size=5Mb,filegrowth=5mb)
to filegroup ByIdGroup6
alter database ESI备份 add file 
(name=N'ById7',filename=N'D:\test\ById7.ndf',size=5Mb,filegrowth=5mb)
to filegroup ByIdGroup7
alter database ESI备份 add file 
(name=N'ById8',filename=N'D:\test\ById8.ndf',size=5Mb,filegrowth=5mb)
to filegroup ByIdGroup8
alter database ESI备份 add file 
(name=N'ById9',filename=N'D:\test\ById9.ndf',size=5Mb,filegrowth=5mb)
to filegroup ByIdGroup9
alter database ESI备份 add file 
(name=N'ById10',filename=N'D:\test\ById10.ndf',size=5Mb,filegrowth=5mb)
to filegroup ByIdGroup10

GO
BEGIN TRANSACTION

--创建分区函数
CREATE PARTITION FUNCTION [bgPartitionFun](int) AS RANGE LEFT
 FOR VALUES (N'1000000', N'2000000', N'3000000', N'4000000', N'5000000', N'6000000', N'7000000', N'8000000', N'9000000', N'10000000')

--创建分区方案
CREATE PARTITION SCHEME [bgPartitionSchema] AS PARTITION [bgPartitionFun] TO 
([PRIMARY], [ByIdGroup1], [ByIdGroup2], [ByIdGroup3], [ByIdGroup4], [ByIdGroup5], [ByIdGroup6], [ByIdGroup7], [ByIdGroup8], [ByIdGroup9], [ByIdGroup10])

--然后创建分区->选id->选择现有分区函数->选择现有分区方案->下一步->下一步

--创建分区索引
CREATE CLUSTERED INDEX [ClusteredIndex_on_bgPartitionSchema_635342971076448165] ON [dbo].[180926_lunwen] 
(
    [Id]
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [bgPartitionSchema]([Id])

--删除分区索引
DROP INDEX [ClusteredIndex_on_bgPartitionSchema_635342971076448165] ON [dbo].[BigOrder] WITH ( ONLINE = OFF )

COMMIT TRANSACTION

**/

--label 使用(这个可以看做是一个循环，没达到条件返回循环体)
  declare @x int 
  select @x = 1
  label:
  print @x
  select @x = @x+1
  while @x < 6
  goto label
  
  create table xs(
  sno char(8) not null,
  sex char(2) not null,
  bittime date default('1990-01-01'),
  class char(10) not null,
  adress char(50) null,
  TotalScore tinyint default(0) null,
  remarks varchar(200) null
  );
  --约束
  alter table xs add constraint pk_sno primary key(sno)
  alter table xs add constraint uq_sno unique(sno)
  alter table xs add constraint ck_score check(TOtalScore between 0 and 100)
  alter table xs drop constraint uq_sno
  
  create view view_score(sno,name,bittime)
  as
  select sno,sex,bittime from xs
  select* from view_score
  
  --drop view view_score
  /*  触发器的使用
  create trigger no_insert  --alter 
  on xs
  after insert
  as
  begin
  raiserror('警告：！xs表中不允许插入新记录',1,1);--打印出错误语句
  rollback transaction
  end
  insert into xs(sno,sex,class) values(2,'2',12)
  */
  alter trigger up_trigger
  on xs
  after update
  as
  begin
  select sno as 更新后的学号 from inserted
  select sno as 更新前的学号 from deleted
  end
  --update xs set sex = '2'  在update 之后触发，可以查看更新前后数据。
  
  create trigger del_trigger
  on xs
  after delete
  as
  begin
  select sno as delete_number from DELETED  --DELETED 为触发器中删除内容，可以用在update,insert 等
  end
  --delete from xs 
  --数据库触发器（可以从库下面的数据库触发器删除）
  create trigger safty
  on database
  for DROP_TABLE,alter_table
  as
  begin
  print '当前数据库禁止更改删除操作！'
  rollback transaction
  end
  --服务器级别的触发器
  create trigger safty_server
  on all server
  for drop_table,alter_table
  as
  begin
  print '当前服务器禁止更改删除操作'
  end
   --删除语句
  if exists(select 1 from sys.server_triggers where name = 'safty_server')
  drop trigger safty_server on all server;
  --创建登录触发器
  create login log_test with password='123456'
  
  create trigger connect_limit
  on all server with execute as 'sa'
  for logon
  as
  begin
  if original_login()='log_test'
  and 
  (select eventdata().value('(/event_instance/clienthost)[1]','nvarchar(15)'))
  not in('192.168.104.155')
  rollback;
  end
  --创建登录时间限制触发器
  create trigger time_limit
  on all server with execute as 'log_test'
  for logon
  as
  begin
  if original_login()='log_test' and convert(char(10),getdate(),108) between
  '8:00:00' and '16:15:00'
  rollback;
  end;
  
  insert into xs(sno,sex,class) values (1,'1',1321)
  
  --对特点信息限制更新触发器
  create trigger update_limit on xs
  for update
  as 
  if update (class) and exists(select * from inserted where sno =1)
  raiserror('学号为1的同学，班级不能修改',16,1)
  rollback transaction
  --update xs set class = 2 where sno = 1
  --触发器也可以做 级联删除，用于同步更新关联表中信息。
  
  sp_helptext update_limit  --查看触发器详情
  alter table xs disable trigger update_limit  --禁用触发器
  disable trigger update_limit on xs
  --启用触发器
  alter table xs enable trigger update_limit
  
  --索引分析与维护。
  set showplan_all on --/off --使用SHOWPLAN_ALL返回有关语句执行情况的详细信息
  
  go
  set showplan_all on 
  go
  select * from xs 
  go
  set showplan_all off
  go
  --查看磁盘信息
  go
  set statistics io on
  go
  select * from xs
  go
  set statistics io off
  go
  
  
  begin transaction insert_xs
  insert into xs(sno,sex,class) values(2,'11',5212)
  if @@ERROR = 0
  begin
  print '插入失败'
  rollback
  end
  else
  begin
  commit transaction 
  end
  
  select * from sys.credentials
  

  use testdb
  
  use master
  go
  --设置数据库为简单模式
  alter database zhihu
  set recovery simple;
  go
  select DATABASEPROPERTYEX('zhihu','recovery')--查看数据库恢复模式
  go
  alter database zhihu set recovery full; --设置数据库为完整恢复模式
  go
  
 
  declare tname_cursor cursor
  for select name from sys.tables order by name
  open tname_cursor
  declare @tname varchar(50)
  fetch next from tname_cursor into @tname
  while (@@FETCH_STATUS <>-1)
  begin
    if (@@FETCH_STATUS <>-2)
        begin
        select @tname ='['+RTRIM(@tname)+']' 
        print 'select '''+@tname+'''=count(*) from '+@tname
        exec('select '''+@tname+'''=count(*) from '+@tname)
        --select @tname = COUNT(*) from @tname
        end
  fetch next from tname_cursor into @tname
  end
  close tname_cursor
  deallocate tname_cursor
  
--练习
CREATE TABLE employees (
emp_no int NOT NULL,
birth_date date NOT NULL,
first_name varchar(14) NOT NULL,
last_name varchar(16) NOT NULL,
gender char(1) NOT NULL,
hire_date date NOT NULL,
PRIMARY KEY (emp_no));

insert into employees values(1,'1950','ll','ff','1',GETDATE()-1000)
insert into employees values(2,'1999','l','f','2',GETDATE()-500)
insert into employees values(3,'1995','l11','fff','3',GETDATE()-50)

select  @@connections as '试图登录次数'
select GETDATE() as '当前时间'

declare @xmlvalues xml
set @xmlvalues = (select title,author from guilintu_poly where id = 1 for xml auto,type)
select @xmlvalues

exec sp_helpuser
select * from DDL_DATABASE_LEVEL_EVENT where databasename = 天津大学

--判断是否具有IDENTITY,可以使用 OBJECTPROPERTY 函数确定一个表是否具有 IDENTITY（标识）列,用法:
Select OBJECTPROPERTY(OBJECT_ID('表名'),'TableHasIdentity') --有则返回1,否则返回0
--获取标识列自增量
SELECT IDENT_INCR('csv_take')
--获取最后生成的标识值
SELECT IDENT_CURRENT('csv_take')
--查看当前表的索引情况
exec sp_helpindex [表名] 

select CONVERT(varchar(20),GETDATE(),111)
select DATEPART(mm,getdate())
select DATENAME(yy,GETDATE())
select DATEADD(mm,1,GETDATE())--加一个月
select DATEDIFF(dd,'2020/01/05',GETDATE()) --从开始时间到结束时间，中间的差距
select APP_NAME()--当前执行的应用程序的名称

select column_name from information_schema.columns where TABLE_NAME = 'sci补充_guilin0420'

select HOST_NAME() --接口名称

declare @hname varchar(50)
set @hname = HOST_NAME()
print @hname

--加法运算
declare @sum int,@i int,@csum int
select @sum =0,@i = 1
while @i<= 100
begin
select @sum = @sum+@i
select @i = @i +1
end
select @csum =convert(varchar(10),@sum)
print '1+2+3...100:'+convert(varchar(10),@sum)
go
select GETDATE() as 当前时间,@@CONNECTIONS as 试图登录次数 
select MAX(id) as 最大值,STDEV(id) as 标准差 from gl_sci_0514
select STUFF('abcd',2,2,'efg') --从左边第二位，替换两位，为目标字符

create table student(age int,name varchar(10))
begin transaction
insert into student values(10,'ll')
insert into student values(100,'lll')
insert into student values(1000,'llll')
commit transaction --提交事情
rollback transaction --如果在begin transaction 之后，事物没有commit,则可以通过rollback 还原数据，如果commit则无法返回

select * from student 

alter table student add 籍贯 char(10)
--通过事物的方式，对数据进行限制，控制录入的信息
begin transaction
insert into student values(11,'aa','黑龙江')
insert into student values(11,'bb','黑龙江')
insert into student values(11,'cc','黑龙江')
--insert into student values(11,'dd','黑龙江')
declare @countnum int
select @countnum = (select COUNT(*) from student where 籍贯='黑龙江')
if @countnum >3
begin
rollback transaction
print'你的信息有误，插入不成功'
end
else
begin
commit transaction
print'您的插入成功'
end

 
遇到tempdb 空间满了，可以运行下面语句释放tempdb空间
USE [tempdb]
GO
DBCC FREESYSTEMCACHE ('ALL')
GO
DBCC SHRINKFILE (N'tempdev' , 1024)
GO