SqlServer数据库所有知识总汇

--if exists(select * from sys.databases where name='test')
-- drop database test
--go
--
--create database test
--on primary
--(
--name='test_dat',
--filename='D:\DB\test_dat.mdf',
--size=5MB,
--maxsize=20MB,
--filegrowth=1MB
--),
--(
--name='test_dat2',
--filename='D:\DB\test_dat2.ndf',
--size=5MB,
--maxsize=20MB,
--filegrowth=1MB
--)
--log on
--(
--name='test_log',
--filename='D:\DB\test_log.ldf',
--size=5MB,
--maxsize=20MB,
--filegrowth=1MB
--)
--go
--use test
--go
--
--create table school
--(
--id int primary key identity(1,1),
--sname varchar(20) not null
--)
--go
--insert into school values('农安十中')
--insert into school values('试验中学')
--go

select * from school
--create table classes 
--( 
--id int identity(1,1), 
--tname varchar(20) not null,
--sid int 
--) 
--create table users
--(
--id int primary key identity(1,1),
--uname varchar(20),
--upass varchar(20)
--)
select * from school
select * from classes
select * from users
--常见系统存储过程
exec sp_databases---显示服务器上所有的数据库
exec sp_helpdb zf----显示某一个或者所有的数据库的信息
exec sp_renamedb 'zf', 'zf2' ---修改数据库的名称
exec sp_tables ---显示所有的表
exec sp_columns users ---显示某一个表的所有列的信息
exec sp_help users ----显示某个表的所有信息
exec sp_helpconstraint users   ----查看某一个表的约束
exec sp_helpindex users ---查看某一个表的索引
exec sp_stored_procedures users  ---查看某一个表的存储过程
exec sp_helptext my_view_users ---查看未加密的存储过程，触发器或者试图的实际文本
exec sp_rename 'users.uname','username' ----给表或者表的字段重命名:sp_rename '表名[.字段名]','newname'
---doc存储过程
exec xp_cmdshell 'mkdir D:\aa',no_output
exec xp_cmdshell 'rd D:\aa' 
EXEC xp_cmdshell 'dir *.exe'
---启用xp_cmdshell
-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO



---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--主键？？
--1保证你每行数据都是唯一值的那个列，这个列不能为空，也不能重复出现相同的值，你可以理解成人的身份证号码，网卡的地址号码，以及公司的税务号码，他们都是唯一而且必须有的。
--2主键的值不可重复，也不可为空（NULL）。
--3主键可以由一个字段，也可以由多个字段组成，分别成为单字段主键或多字段主键。
--4作用：加快数据库的操作速度
--5一个表里可以设两个(或者多个)主键 但是SQL只允许一个主键
--外键？
--1外键表示了两个关系之间的联系。以另一个关系的外键作主关键字的表被称为主表，具有此外键的表被称为主表的从表。
--   例如：班级表(Class as B) [cid ,cname] 学生表(Student as A) [sid ,name ,cid]  那么就是班级是主表，学生是子表。
--2外建也是一种约束的
--3 外键必须是另一个表的主键

--试图
--1视图是从一个特定的角度来查看数据库中的数据，
--2一个视图是由SELECT查询语句组成的虚拟表。视图包含一系列的列和行。但是，视图不存储真实数据。 只是在引用时动态生成
--3视图是由一张或多张表中的数据组成的
--4单个表的试图的可以执行操作而且直接影响到对应的表，多个表的试图不能进行操作，只可以查看
--5视图是存储在数据库中的查询的SQL 语句，属于定制数据的机制，它主要出于两种原因：安全原因， 视图可以隐藏一些数据，将重要的数据列过滤掉；另一原因是可使复杂的查询易于理解和使用。
--优点：
--1视图集中即是使用户只关心它感兴趣的某些特定数据和他们所负责的特定任务
--2视图本身就是一个复杂查询的结果集，这样在每一次执行相同的查询时，不必重新写这些复杂的查询语句,只要一条简单的查询视图语句即可
--3视图能够实现让不同的用户以不同的方式看到不同或相同的数据集。因此，当有许多不同水平的用户共用同一数据库时，这显得极为重要。
--4视图可以作为一种安全机制。通过视图用户只能查看和修改他们所能看到的数据
--left join :左连接，返回左表中所有的记录以及右表中连接字段相等的记录。 
--right join :右连接，返回右表中所有的记录以及左表中连接字段相等的记录。 
--inner join: 内连接，又叫等值连接，只返回两个表中连接字段相等的行。 
--full join:外连接，返回两个表中的行：left join + right join 
--cross join:结果是笛卡尔积，就是第一个表的行数乘以第二个表的行数。 
create view my_view_users 
as 
select * from users

--存储过程
--是SQL语句和控制流语句的预编译集合。
--存储过程已经经过编译，只需执行它就可以。
--用户通过名字指定存储过程并给出参数
--存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。
--存储过程是由流控制和SQL语句书写的过程，这个过程经编译和优化后存储在数据库服务器中，应用程序使用时只要调用即可。
--优点：
--通过存储过程可以使相关的动作在一起发生，从而可以维护数据库的完整性。
--存储过程可以简化运行提高效率
--存储过程因为SQL语句已经预编绎过了，因此运行的速度比较快。 
--存储过程可以接受参数、输出参数、返回单个或多个结果集以及返回值。可以向程序返回错误原因。 
--示例：
--存储过程实现登录
--create proc user_login
[email=--@uname]--@uname[/email] varchar(20),
[email=--@upass]--@upass[/email] varchar(20),
[email=--@flag]--@flag[/email] varchar(20) output
--as
--begin
--if exists(select * from users where [email=uname=@uname]uname=@uname[/email])
--begin
-- if exists(select * from users where [email=uname=@uname]uname=@uname[/email] and [email=upass=@upass]upass=@upass[/email])
--  set @flag='登录成功'
-- else
-- set @flag='密码错误'
--end
--else 
-- set @flag ='不存在该用户'
--end
--
--drop proc user_login
--
--
--declare @flag varchar(20)
--exec user_login 'admin','123',@flag output
--select @flag as 登录结果

--事务
--事务是一种机制.是一个操作序列,它包含一组数据库操作命令,并且所有的命令作为一个整体一起向系统提交或撤消,即这一组数据库命令要么都执行,要么都不执行. 
--事务运行的三种模式: 
--A:自动提交事务 
--每条单独的语句都是一个事务。每个语句后都隐含一个COMMIT。 
--B:显式事务 
--以BEGIN TRANSACTION显式开始，以COMMIT或ROLLBACK显式结束。 
--C:隐性事务 
--在前一个事务完成时，新事务隐式启动，但每个事务仍以COMMIT或ROLLBACK显式结束。
--第二点:你要了解事务的4个属性,即ACID 
--原子性:事务是一个完整的操作 要么全做要么全不做
--一致性:当事务完成时,数据是必须处于一致状态 
--隔离性:对数据进修改的所有并发事务是彼此隔离的. 
--持久性:事务完成后,它对于系统的影响是永久的. 
-- 
----事务的try-catch
--begin tran 
--
--begin try 
-- insert into classes(tname,sid) values('dd',546)--sid约束小于100,引发错误
-- commit tran 
--end try 
--
--begin catch 
-- print 'error'
-- rollback tran
--end catch 

--函数 function??
--函数是由一个或多个 Transact-SQL 语句组成的子程序，可用于封装代码以便重新使用。
--可使用 CREATE FUNCTION 语句创建、使用 ALTER FUNCTION 语句修改、以及使用 DROP FUNCTION 语句除去用户定义函数
--共七类： 
--1.数学函数：abs()、sign()、pi()、power()等。 
--2.统计函数：avg()、sum()、max()、min()等。 
--3.字符串函数：char()、space()、nchar()、len()等。 
--4.日期函数：getdate()、day()、month()、year()等。 
--5.系统函数：db_id()、file_id()、user_id()、user()等。 
--6.其他函数：textptr()、textvalid()、patindex()、cursor_status()等。 
--7.用户自定义函数： 
----一个自定义function示例
--create function split(@str varchar(500),@pos int) 
--returns varchar(500) 
--as 
--begin 
--if @str='' or @pos<1 or @pos>500 
--return '' 
--declare @i int 
--declare @j int 
--declare @s int 
--declare @ret varchar(500) 
--set @s=1 
--set @j=0 
--set @ret='' 
--while @s<=len(@str) 
-- begin 
-- set @j=@j+1 
-- set @i=charindex('|',@str,@s) 
-- if @i<1 
-- begin 
-- set @ret=substring(@str,@s,len(@str)-@s+1) 
-- break 
-- end 
-- else 
-- begin 
-- set @ret=substring(@str,@s,@i-@s) 
-- end 
-- if @j=@pos break 
-- set @s=@i+1 
-- end 
-- if @j<@pos 
-- return '' 
-- return @ret 
--end 
--
--select dbo.split('AAA|BBBB|CCCC' ,1) 

--触发器（trigger）
--是在对表进行插入，更新或者删除时自动执行的存储过程。
--是个特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由个事件来触发
--触发器经常用于加强数据的完整性约束和业务规则等。
--触发器可通过数据库中的相关表实现级联更改
--触发器可以强制比用 CHECK 约束定义的约束更为复杂的约束
----触发器和inserted数据集
CREATE TRIGGER TR_USERS_UPDATE ON dbo.students
FOR UPDATE 
AS 
declare @USER_IF int
select @USER_IF=sage from inserted --* 
if @user_if>1 
insert into students values('更新',@USER_IF) 
go 
drop trigger TR_USERS_UPDATE
---测试
update students set sage=340 where id =1--更新同时添加一条新记录
select * from students
--范式(Normal Formate)
--第一范式：每列都是不可再分的最小数据单元，要求不能再分
--第二范式：每列都和主键相关，即除了主键以外的其他列，都依赖于该主键，要求一个表只描述一件事
--第三范式：数据表中的每列都和主键直接相关，而不是间接关联，要求不是传递依赖关系。


---------------------基本用法
--默认约束
alter table users
add constraint DF_uname default('dajian') for uname
--唯一约束
alter table users
add constraint UQ_uname unique(uname)
--check约束
alter table users
add constraint CK_upass check(upass != 'admin')
----创建windows登录用户
use master
exec sp_grantlogin '域名/帐户名'
----创建SQL登录用户
exec sp_addlogin 'admin','131258'
exec sp_addlogin 'admin2','131258'
--创建数据库用户
--1进入到要分配权限的数据库
--2为当前数据库添加数据库用户
use test
go
exec sp_grantdbaccess 'admin','admin2'
go
---给数据库用户分配权限(CRUD)
use test
go
--为用户分配school表的删除以外的权限
grant select ,insert ,update on school to admin2
--分配创建表的权限
grant create table to admin2
--声明局部变量 赋值后用于查询
declare @name varchar(20)
set @name ='dajian'
select * from users where [email=uname=@name]uname=@name[/email]
---声明全局变量(@@ 表示)
系统常用全局变量：
@@error 最后一个T-SQL的错误号
delete from notables ---notalbes表示不存在的数据表
select @@error ---208
print '当前错误号为：'+convert(varchar(5),@@error)----当前错误号为：208
一般用来累计错误，与事务并用如：
begin tran
...
update bank set balance =balance-1000
set @errorCount = @errorCount [email=+@@error]+@@error[/email]
if(@errorCount>0)
rollback tran
else
commit tran

@@identity 最后一个插入的标识列
insert into users values('ada','afad')
select @@identity---6
@@language 当前使用的语言
select @@language --简体中文
@@servername 本地服务器的名称
select @@servername  --WWW-90DB9457179
@@servicename  本地SQL服务的名称
select @@servicename----MSSQLSERVER
@@rowcount 受上一个SQL语句影响的行数
select * from users
select @@rowcount---4
@@max_connections 同时创建最大连接数目
select @@max_connections-----32767
@@trancount 当前打开事务的总数
begin tran
select @@trancount---1
rollback tran
select @@trancount---0
@@version 当前SQL Server的版本信息
select @@version  ---Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)   Oct 14 2005 00:33:37   Copyright (c) 1988-2005 Microsoft Corporation  Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3) 
set 和select赋值的区别
1 select可以多个同时赋值，set必须一个一个赋值
例如：
select @a=1,@b=2,@c=3
set @a=1
set @b=2
set @c=3
2 当查询结果不为一时，只能用select赋值，且去最后一条记录
declare @name varchar(20)
set @name = (select uname from users where id =2) --有且只能有一条记录
select @name = (select uname from users where id !=2) --多条记录时，取最后一条记录
--
--create table students
--(
--id int primary key identity(1,1),
--sname varchar(20),
--sage int 
--)
--insert into students values('111',3)
--insert into students values('222',23)
--insert into students values('333',33)
--insert into students values('444',43)
--insert into students values('555',55)
--insert into students values('6666',63)

--while循环
declare @age int
set  nocount on
print '加分前的年龄是：'
select * from students
while(1=1)
begin
select @age=min(sage) from students 
if(@age<40)
  begin
   update students set sage=sage+2
  end
else
  begin
   break
  end
end
print '加分后的年龄是：'
select * from students
--case用法
select sname as 学生姓名,年龄段=case
when sage <50 then '少年'
when sage >50 and sage<80 then '中年'
else '老年'
end
from students
update students set sage =sage-2 
--索引
--索引提供指针指向表中的指定列，然后根据指定的排序顺序排列这些指针。
--数据库找到指定的索引，然后跟随指针到达包含值的行
--索引是表中列的逻辑指针清单。
--一个表只允许由一个聚集索引，可以有多个非聚集索引；
--创建users表的uname的索引，用于查询
if exists(select name from sys.indexes where name='myindex_users')
drop index users.myindex_users
go
create nonclustered index myindex_users
on users(uname)
with fillfactor= 30
go
---按照自定义的索引查询表
select * from users with (index = myindex_users) 
----但是一般不用人工指定索引，SQL会根据所创建的索引，自动优化查询
----索引可以加快查询速度，但索引自身也需要维护，并占用一定的资源，所以不需要为每一个列创建索引，
----为某一个列创建索引的标准：1频繁搜索2用于排序(没必要为小表创建索引)
--索引其实可以理解成一本字典的目录，通过目录我们很快的能找到我们要找的那一页，如果没有目录，我们就要一篇一篇的翻，很明显，使用索引的表要比没有使用索引的标的查询速度快几十倍
--什么是索引?SQL Server 2000里有什么类型的索引? 
--索引用来快速访问数据库表格或者视图里的数据。在SQL Server里，它们有两种形式:聚集索引和非聚集索引。
--1聚集索引在索引的叶级保存数据。这意味着不论聚集索引里有表格的哪个(或哪些)字段，这些字段都会按顺序被保存在表格。由于存在这种排序，所以每个表格只会有一个聚集索引。
--2非聚集索引在索引的叶级有一个行标识符。这个行标识符是一个指向磁盘上数据的指针。它允许每个表格有多个非聚集索引。