SQLserver常见知识点

Sqlserver Drop、Delete、Truncate

老大------drop

出没场合:drop table tb --tb表示数据表的名字,下同

绝招:删除内容和定义，释放空间。简单来说就是把整个表去掉.以后要新增数据是不可能的,除非新增一个表,

   例如:一个班就是一个表,学生就是表中的数据,学生的职务就是定义

   drop table class,就是把整个班移除.学生和职务都消失

老二-----truncate

出没场合:truncate table tb

绝招:删除内容、释放空间但不删除定义。与drop不同的是,他只是清空表数据而已,他比较温柔.

同样也是一个班,他只去除所有的学生.班还在,职务还在,如果有新增的学生可以进去,也可以分配上职务

删除内容很容易理解,不删除定义也很容易理解,就是保留表的数据结构

注意:truncate 不能删除行数据,要删就要把表清空

老三-----delete

出没场合:delete table tb --虽然也是删除整个表的数据,但是过程是痛苦的(系统一行一行地删,效率较truncate低)

        或

        delete table tb where 条件

绝招:删除内容不删除定义，不释放空间。三兄弟之中最容易欺负的一个

然后关于delete的就不详细说明了,大家都懂的

关于truncate的小小总结:
truncate table 在功能上与不带 WHERE 子句的 delete语句相同：二者均删除表中的全部行。
但 truncate 比 delete速度快，且使用的系统和事务日志资源少。
delete 语句每次删除一行，并在事务日志中为所删除的每行记录一项。所以可以对delete操作进行roll back

1、truncate 在各种表上无论是大的还是小的都非常快。如果有ROLLBACK命令Delete将被撤销，而 truncate 则不会被撤销。
2、truncate 是一个DDL语言，向其他所有的DDL语言一样，他将被隐式提交，不能对 truncate 使用ROLLBACK命令。
3、truncate 将重新设置高水平线和所有的索引。在对整个表和索引进行完全浏览时，经过 truncate 操作后的表比Delete操作后的表要快得多。
4、truncate 不能触发任何Delete触发器。
5、当表被清空后表和表的索引讲重新设置成初始大小，而delete则不能。
6、不能清空父表

Sqlserver 视图

视图定义：视图是从一个或者几个基本表(或视图)导出的表。它与基本表不同，是一个虚表。数据库中只存放视图的定义，
而不存在视图对应的数据，这些数据仍然存放在原来的基本表中。所以一旦基本表中的数据发生变化，从视图
中查询的数据也就随之改变了。从这个意义上讲，视图就像一个窗口，透过它可以看到数据库中自己感兴趣的
数据及其变化。

视图是从一个或者多个表导出的，它的行为与表非常相似，但视图是一个虚拟表，在视图中可以使用SELECT语句查
询数据，以及使用insert、update和delete语句修改记录，对于视图的操作最终转化为对基本数据表的操作。视图不
仅可以方便操作，而且可以保障数据库系统的安全性。
视图一经定义便存储在数据库中，与其相对应的数据并没有像表数据那样在数据库中在存储一份，通过视图看到的数
据只是存放在基本表中的数据。可以对其进行增删该查，通过视图对数据修改，基本表数据也对应变化，反之亦然。

视图的应用：
　　　　单表视图的update、delete、insert，会改变基本表的数据。（可以执行Insert操作，但是要保证视图中没有
展示的列有默认值或可空，所以不建议使用视图来进行Insert操作）多表视图一般用于查询，可以进行update操作。
sqlserver中不能进行删除操作。

创建视图：
create view stu_view
as
select name,age from stuTable where age>20; //这一行任意一条select 的SQL语句都可以

修改视图：
alter view v_users //修改视图只需要把上面那个select修改为alter就行
as
select username, roleid from UserInfo where roleid>1

删除视图：
drop view （视图名）

创建多表视图：
1、将两个表多表合并

create view together2
as
select uid, rid, UserName, RoleId from UserInfo, roleinfo where uID=rID
这种情况下会把两个表各自的列都展示出来

2、将两个表列合并
create view together
as
select UserName as 用户名 from UserInfo
union all
select rolename as 角色名 from roleinfo
这种情况下union all意思是 将UserInfo 表中的username和roleinfo表中的rolename合并为一列
注意：使用了union all 关键字的话，两个表查询的属性列一定要一样数量，不能一个表一个属性另一个表2个属性之类。

.视图与表的区别：
1.视图是已经编译好的SQL语句，是基于SQL语句的结果集的可视化表，而表不是；
2.视图（除过索引视图）没有实际的物理记录，而基本表有；
3.表示内容，视图是窗口；
4.表占物理空间，而视图不占物理空间，视图只是逻辑概念的存在；
5.视图是查看数据表的一种方法，可以查询数据表中某些字段构成的数据，只是一些SQL语句的集合。从安全角度说，视图可以防止用户接触数据表，从而不知表结构；
6.表属于全局模式的表，是实表；视图数据局部模式的表，是虚表；
7.视图的建立和删除只影响视图本身，不影响对应的基本表。

视图不能包含 ORDER BY 子句，除非SELECT语句的选择列表中还有一个 TOP 子句。
视图不能使用 INTO 关键字。视图不能包含 OPTION 子句。
视图不能有对临时表或表变量的引用。视图最多可以有 1024 列。

Sqlserver查询语句的优化方式

1、应尽量避免在where 子句中使用or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，

如：
select id from t where num=10 or num=20
可以这样查询：select id from t where num=10 union all select id from t where num=20

2、like本身效率就比较低，应该尽量避免查询条件使用like；对于like ‘%…%’（全模糊）这样的条件，
是无法使用索引的，全表扫描自然效率很低；另外，由于匹配算法的关系，模糊查询的字段长度越大，模
糊查询效率越低。
解决办法：首先尽量避免模糊查询，如果因为业务需要一定要使用模糊查询，则至少保证不要使用全模糊
查询，对于右模糊查询，即like ‘…%’，是会使用索引的；左模糊like，‘%…’无法直接使用索引，但
可以利用reverse + function index 的形式，变化成 like ‘…%’；全模糊是无法优化的，一定要的话考
虑用搜索引擎。出于降低数据库服务器的负载考虑，尽可能地减少数据库模糊查询。

3、in和 not in 也要慎用，否则会导致全表扫描，如：
select id from t where num in(1,2,3)
对于连续的数值，能用 between 就不要用 in 了：
select id from t where num between 1 and 3

4、如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in。

Sqlserver存储过程

简而言之，存储过程就是SQL Server为了实现特定任务，而将一些需要多次调用的固定操作语句编写成程序段，
这些程序段存储在服务器上，有数据库服务器通过程序来调用。

存储过程的优点:
存储过程加快系统运行速度，存储过程只在创建时编译，以后每次执行时不需要重新编译。
存储过程可以封装复杂的数据库操作，简化操作流程，例如对多个表的更新，删除等。
可实现模块化的程序设计，存储过程可以多次调用，提供统一的数据库访问接口，改进应用程序的可维护性。
存储过程可以增加代码的安全性，对于用户不能直接操作存储过程中引用的对象，SQL Server可以设定用户对指定存储过程的执行权限。
存储过程可以降低网络流量，存储过程代码直接存储于数据库中，在客户端与服务器的通信过程中，不会产生大量的T_SQL代码流量。

存储过程的缺点：
数据库移植不方便，存储过程依赖与数据库管理系统， SQL Server 存储过程中封装的操作代码不能直接移植到其他的数据库管理系统中。
不支持面向对象的设计，无法采用面向对象的方式将逻辑业务进行封装，甚至形成通用的可支持服务的业务逻辑框架.
代码可读性差，不易维护。不支持集群。

1、创建无参存储过程：
create procedure （getAllBooks 存储过程名）
as
select * from books;

–调用,执行存储过程
exec getAllBooks;

修改储存过程：
alter procedure getAllBooks
as
select book_auth from books;

删除存储过程：
drop procedure getAllBooks;

重命名存储过程：
sp_rename getAllBooks , proc_get_allBooks;

---

2、带参数的存储过程
存储过程的参数分为两种：输入参数和输出参数

输入参数：用于向存储过程传入值，类似java语言或则c中的值传递。
输出参数：用于调用存储过程后，参会结果，类似java语言的按引用传递。

带一个参数存储过程：
create proc searchBooks(@bookID int)
as
select * from books where book_id=@bookID; --要求book_id列与输入参数相等
exec searchBooks 1; --执行searchBooks

带2个参数存储过程：
create proc searchBooks1(
@bookID int,
@bookAuth varchar(20)
)
as
select * from books where book_id=@bookID and book_auth=@bookAuth; --要求book_id和book_Auth列与输入参数相等

exec searchBooks1 1,‘金庸’; --执行serarchBooks1

3、带output参数的存储过程：
//创建带output参数的存储过程
create procedure getuserinfo(
@username varchar(50),
@role_id int output
)
as
select @role_id=RoleId from UserInfo where UserName=@username

//执行改存储过程，注意要3行代码一起执行
declare @id int
exec getuserinfo ‘user3’, @id output
select @id as ID名

Sqlserver基本语法

建表：
create table Department(
–部门编号,primary key 主键， identifty(1,1)自动增长，初始值为1，增步步长为1
Department_Id int primary key identity(1,1),

--部门名称
Department_Name nvarchar(50) not null,

--部门描述
Department_remark text

)

–char: 定长，char(10),无论存储数据是否真的到了10个字节，都要占用10个字节。
–varchar(100)：变长，varchar(100)，最多占用100个字节。存储100个字母或者50个汉字。
–text:长文本
–nvarchar(100)：存储100个字母或100个汉字。

添加主键约束：
alter table 表名 add constraint 约束名 primary key (主键)

删除主键约束：
alter table 表名 drop constraint 约束名

修改表字段：
添加字段
alter table 表名 add 列名 字段类型

// 如果字段不为空，需要先添加一个默认值
alter table 表名 add 列名 字段类型 not null default 0

Sqlserver索引

SQL Server中访问数据的方式：
从广义上讲，SQL Server检索所需数据的方法只有两种：
　　1、使用全表扫描：表扫描是相当直观。当执行表扫描时，SQL Server从表的物理起点处开始，浏览表中的每一行。
当发现和查询条件匹配的行时，就在结果集中包含它们。关于表扫描很多说法都是效率低，但是如果表数据减少的情况
下，实际上使用表扫描却是最快的。
　　2、使用索引：在查询优化过程中，优化器查看所有可用的索引结构并且选择最好的一个(这主要基于在连接和WHERE
子句中所指定的信息，以及SQL Server在索引结构中保存的统计信息)。一旦选择了索引，SQL Server将在树结构中导航
至与条件匹配的数据位置，并且只提取它所需的记录。与表扫描的区别在于，因为数据是排序的，所以查询引擎知道它
何时到达正在查找的当前范围的下界。然后它可以结束查询，或者根据需要移至下一数据范围。EXISTS的工作方式是查
到匹配的记录SQL Server就立即停止。使用索引所获得的性能与使用EXISTS类似甚至更好，因为查找数据的过程的工作
方式是类似的；也就是说，服务器可能使用某种索引知道何时没有相关内容，并且立即停止。此外，可以对数据执行非
常快速的查找(称为SEEK)，而不是在整个表中查找。

微软的SQLserver提供了两种索引：聚集索引（也称聚类索引，簇集索引）和非聚集索引（也称非聚类索引，非簇集索引）

SQL server默认是在主键上建立聚集索引的。

聚集索引：就好比一本英语字典，里面以a开头的单词全部在一块区间，以z开头的单词全在一块区间，所以查询时候会
直接翻到那个大概区域。
使用聚集索引的最大好处:就是能够根据查询要求，迅速缩小查询范围，避免全表扫描。

非聚集索引：就好比英语字典中的目录，我们查询一个以a字开头的单词时候，先去目录中查询相关页码，然后再去查询
页码对应的单词。

eg:。下面我们就来看一下在1000万条数据量的情况下各种查询的速度表现（3个月内的数据为25万条）：

（1） 仅在主键上建立聚集索引，并且不划分时间段：
Select gid,fariqi,neibuyonghu,title from tgongwen 用时：128470毫秒（即：128秒）

（2）在主键上建立聚集索引，在fariq上建立非聚集索引：
select gid,fariqi,neibuyonghu,title from Tgongwen where fariqi> dateadd(day,-90,getdate()) 用时：53763毫秒（54秒）

（3）将聚合索引建立在日期列（fariqi）上：
select gid,fariqi,neibuyonghu,title from Tgongwen where fariqi> dateadd(day,-90,getdate()) 用时：2423毫秒（2秒）

总结：所以在碰到数据量比较大的时候，尽量将聚集索引建立在相关条件限制语句上，比如上面第一个案例，聚集索引
建立在微软默认的序列ID上，但是这个ID又与实际限制条件并无太大关联，所以还是等同于全表扫描。但是案例３根据
条件需要，因为条件需求是３个月以内的数据，所以根据日期创建聚集索引，将原本混乱日期数据按照时间顺序相当于
模块化，这样就相当于把最近３个月内的数据基本上放到一个模块中，这样查询起来就很快了，不需要全表扫描，只需要
扫描最后一个模块就行。

---

过多或不当索引坏处：索引有助于提高检索性能，但过多或不当的索引也会导致系统低效。因为用户在表中每加进一个索引，数据库就要做
更多的工作。过多的索引甚至会导致索引碎片。

改善SQL语句

很多人不知道SQL语句在SQL SERVER中是如何执行的，他们担心自己所写的SQL语句会被SQL SERVER误解。
比如：
select * from table1 where name=‘zhangsan’ and tID > 10000
和执行:
select * from table1 where tID > 10000 and name=‘zhangsan’

一些人不知道以上两条语句的执行效率是否一样，因为如果简单的从语句先后上看，这两个语句的确是不一样，
如果tID是一个聚合索引，那么后一句仅仅从表的10000条以后的记录中查找就行了；而前一句则要先从全表中查
找看有几个name='zhangsan’的，而后再根据限制条件条件tID>10000来提出查询结果。
事实上，这样的担心是不必要的。SQL SERVER中有一个“查询分析优化器”，它可以计算出where子句中的搜索
条件并确定哪个索引能缩小表扫描的搜索空间，也就是说，它能实现自动优化。
虽然查询优化器可以根据where子句自动的进行查询优化，但大家仍然有必要了解一下“查询优化器”的工作原理，
如非这样，有时查询优化器就会不按照您的本意进行快速查询。

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

删除索引：
drop index index_name on table_name

---

非聚集索引：

Sqlserver锁与事务

事务具有原子性，一致性，隔离性，持久性。

原子性：事务必须是一个自动工作的单元，要么全部执行，要么全部不执行。
一致性：事务结束的时候，所有的内部数据都是正确的。
隔离性：并发多个事务时，各个事务不干涉内部数据，处理的都是另外一个事务处理之前或之后的数据。
持久性：事务提交之后，数据是永久性的，不可再回滚。

在多用户都用事务同时访问同一个数据资源的情况下，就会造成以下几种数据错误。
更新丢失：多个用户同时对一个数据资源进行更新，必定会产生被覆盖的数据，造成数据读写异常。
不可重复读：如果一个用户在一个事务中多次读取一条数据，而另外一个用户则同时更新啦这条数据，
造成第一个用户多次读取数据不一致。
脏读：第一个事务读取第二个事务正在更新的数据表，如果第二个事务还没有更新完成，那么第一个事务
读取的数据将是一半为更新过的，一半还没更新过的数据，这样的数据毫无意义。
幻读：第一个事务读取一个结果集后，第二个事务，对这个结果集经行增删操作，然而第一个事务中再次
对这个结果集进行查询时，数据发现丢失或新增。

共享锁（S）：还可以叫他读锁。可以并发读取数据，但不能修改数据。也就是说当数据资源上存在共享锁的时候，
所有的事务都不能对这个资源进行修改，直到数据读取完成，共享锁释放。

排它锁（X）：还可以叫他独占锁、写锁。就是如果你对数据资源进行增删改操作时，不允许其它任何事务操作这
块资源，直到排它锁被释放，防止同时对同一资源进行多重操作。