SQL(基于SQL SEVER )知识积累

日常使用SQL处理数据的随笔记录

1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。    

2.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如:    
select id from t where num is null    
可以在num上设置默认值0,确保表中num列没有null值,然后这样查询:    
select id from t where num=0    

3.应尽量避免在 where 子句中使用!=或<>操作符,否则将引擎放弃使用索引而进行全表扫描。  

‘’与null ,is not null 是不为空值
 <> '' 是不等于 字符串空
'' 表示空字符串
null表示什么都没有,什么都不是
判断'' 用 ='' 或 <>'' , 而null 用is null 或is not null,字段中如果出现null 会对查询语句造成影响有可能导致索引失效,所以字段中尽量不要出现null ,用'' 或其他字符代替 ,纠正一下其他人的观点:null不代表空字符串 哦, 代表表示什么都没有,什么都不是,可以出现在任意类型字段里面(int,decimal,varchar 等等都可以是null),''只能在varchar,char类型里面出现 

4.应尽量避免在 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    

5.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    

6.下面的查询也将导致全表扫描:    
select id from t where name like '%abc%'    

7.应尽量避免在 where 子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描。如:    
select id from t where num/2=100    
改为:
select id from t where num=100*2    

 8.应尽量避免在where子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描。如:    
select id from t where substring(name,1,3)='abc'--name以abc开头的id   
改为:
select id from t where name like 'abc%'    

9.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算,否则系统将可能无法正确使用索引。    

10.在使用索引字段作为条件时,如果该索引是复合索引,那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引,    
否则该索引将不会被使用,并且应尽可能的让字段顺序与索引顺序相一致。    

11.不要写一些没有意义的查询,如需要生成一个空表结构:    
select col1,col2 into #t from t where 1=0    
这类代码不会返回任何结果集,但是会消耗系统资源的,应改成这样:    
create table #T

12.很多时候用 exists 代替 in 是一个好的选择:    
select num from a where num in(select num from b)    
用下面的语句替换:    
select num from a where exists(select 1 from b where num=a.num)    
EXISTS(包括 NOT EXISTS )子句的返回值是一个BOOL值。 EXISTS内部有一个子查询语句(SELECT ... FROM...), 我将其称为EXIST的内查询语句。其内查询语句返回一个结果集。 EXISTS子句根据其内查询语句的结果集空或者非空,返回一个布尔值。

一种通俗的可以理解为:将外查询表的每一行,代入内查询作为检验,如果内查询返回的结果取非空值,则EXISTS子句返回TRUE,这一行行可作为外查询的结果行,否则不能作为结果。

分析器会先看语句的第一个词,当它发现第一个词是SELECT关键字的时候,它会跳到FROM关键字,然后通过FROM关键字找到表名并把表装入内存。接着是找WHERE关键字,如果找不到则返回到SELECT找字段解析,如果找到WHERE,则分析其中的条件,完成后再回到SELECT分析字段。最后形成一张我们要的虚表。
WHERE关键字后面的是条件表达式。条件表达式计算完成后,会有一个返回值,即非0或0,非0即为真(true),0即为假(false)。同理WHERE后面的条件也有一个返回值,真或假,来确定接下来执不执行SELECT。
分析器先找到关键字SELECT,然后跳到FROM关键字将STUDENT表导入内存,并通过指针找到第一条记录,接着找到WHERE关键字计算它的条件表达式,如果为真那么把这条记录装到一个虚表当中,指针再指向下一条记录。如果为假那么指针直接指向下一条记录,而不进行其它操作。一直检索完整个表,并把检索出来的虚拟表返回给用户。EXISTS是条件表达式的一部分,它也有一个返回值(true或false)。

在插入记录前,需要检查这条记录是否已经存在,只有当记录不存在时才执行插入操作,可以通过使用 EXISTS 条件句防止插入重复记录。
INSERT INTO TableIn (ANAME,ASEX) 
SELECT top 1 '张三', '男' FROM TableIn
WHERE not exists (select * from TableIn where TableIn.AID = 7)

EXISTS与IN的使用效率的问题,通常情况下采用exists要比in效率高,因为IN不走索引,但要看实际情况具体使用:
IN适合于外表大而内表小的情况;EXISTS适合于外表小而内表大的情况。并且两者查询结果不一定一致,in只能查询到内表中符合条件,再符合外表的数据,exist能查询到符合外表,再符合内表的数据

exists是用来判断是否存在的,当exists(查询)中的查询存在结果时则返回真,否则返回假。not exists则相反。
exists做为where 条件时,是先对where 前的主查询询进行查询,然后用主查询的结果一个一个的代入exists的查询进行判断,如果为真则输出当前这一条主查询的结果,否则不输出。
in则是先对子查询进行查询,然后把子查询所得的数据全部映射到内存中hash表中,最后进行主查询,对主查询查询出来的结果和子查询的结果进行比较,一条一条的进行筛选。
exist和in都是一条一条的进行筛选,不同的是,in是先将范围查询出来,即先进行子查询,而exist则是查一条,再放到子查询中比较查询,即后执行子查询。
in和exists
in 是把外表和内表作hash 连接,而exists是对外表作loop循环,每次loop循环再对内表进行查询。一直以来认为exists比in效率高的说法是不准确的。

 

select * from ODS_Student as a where AcademicTermNo = '20162' and  not exists (select 1 from ODS_Student as b
where AcademicTermNo = '20163' and a.StudentNo= b.StudentNo
)

select * from ODS_Student where AcademicTermNo = '20162' and StudentNo not in (select StudentNO from ODS_Student
where AcademicTermNo = '20163'
)

 

select * from ODS_Course as a,#Temp_有课教师 as b where a.GH=b.GH and a.Year=b.Year Order By a.Year,a.GH


select * from ODS_Course as b where exists (select 1 from #Temp_有课教师 as a where a.GH=b.GH and a.Year=b.Year) Order By b.Year,b.GH

 

 

13.并不是所有索引对查询都有效,SQL是根据表中数据来进行查询优化的,当索引列有大量数据重复时,SQL查询可能不会去利用索引,    
如一表中有字段sex,male、female几乎各一半,那么即使在sex上建了索引也对查询效率起不了作用。    

14.索引并不是越多越好,索引固然可以提高相应的 select 的效率,但同时也降低了 insert 及 update 的效率,    
因为 insert 或 update 时有可能会重建索引,所以怎样建索引需要慎重考虑,视具体情况而定。    
一个表的索引数最好不要超过6个,若太多则应考虑一些不常使用到的列上建的索引是否有必·要。    

    
15.尽量使用数字型字段,若只含数值信息的字段尽量不要设计为字符型,这会降低查询和连接的性能,并会增加存储开销。    
这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符,而对于数字型而言只需要比较一次就够了。    
    
16.尽可能的使用 varchar 代替 char ,因为首先变长字段存储空间小,可以节省存储空间,    
其次对于查询来说,在一个相对较小的字段内搜索效率显然要高些。    
    
17.任何地方都不要使用 select * from t ,用具体的字段列表代替“*”,不要返回用不到的任何字段。    
    
18.避免频繁创建和删除临时表,以减少系统表资源的消耗。

19.临时表并不是不可使用,适当地使用它们可以使某些例程更有效,例如,当需要重复引用大型表或常用表中的某个数据集时。但是,对于一次性事件,最好使用导出表。    
    
20.在新建临时表时,如果一次性插入数据量很大,那么可以使用 select into 代替 create table,避免造成大量 log ,    
以提高速度;如果数据量不大,为了缓和系统表的资源,应先create table,然后insert。

21.如果使用到了临时表,在存储过程的最后务必将所有的临时表显式删除,先 truncate table ,然后 drop table ,这样可以避免系统表的较长时间锁定。    
    
22.尽量避免使用游标,因为游标的效率较差,如果游标操作的数据超过1万行,那么就应该考虑改写。    
    
23.使用基于游标的方法或临时表方法之前,应先寻找基于集的解决方案来解决问题,基于集的方法通常更有效。

24.与临时表一样,游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法,尤其是在必须引用几个表才能获得所需的数据时。
在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时间允许,基于游标的方法和基于集的方法都可以尝试一下,看哪一种方法的效果更好。

25.尽量避免大事务操作,提高系统并发能力。

26.尽量避免向客户端返回大数据量,若数据量过大,应该考虑相应需求是否合理。

27.数据查询的顺序

先 from(表)where(按条件取出数据)group by(再对取出的数据进行分组)having(分组之后再过滤得到最新数据集)select(按照设置列从数据集里面取出数据)order by(对取出的数据进行排序)
所以执行顺序:from---where--group by---having---select---order by


28、(行列转换) 

use demo
  select  xueyuan,count(xuehao)as '总人数',
sum(case when xingbie='女' then 1 else 0 end) 女生人数,
sum(case when xingbie='男' then 1 else 0 end) 男生人数
from dbo.[BenKXSBD] GROUP BY xueyuan 
应该用sum

pivot是sql server 2005 提供的运算符,所以只要数据库在05版本以上的都可以使用。主要用于行和列的转换。

 pivot纵表转横表

select
    t2.姓名,
    t2.数学,
    t2.物理,
    t2.语文
from Scores as t1
pivot (sum(分数) for 课程 in(数学,语文,物理)) as t2

pivot将原来表中 课程字段中的 数据行 数学,语文,物理 转换为列,并用sum取对应列的值。

unpivot 横表转纵表

select
     *
 from
 scores2
 unpivot (分数 for 课程 in (语文,数学,物理)) as t3

unpivot 将 语文,数学,物理 列转为行,分数为新的一列存放对应的值。


29、
为什么是三个单引号?( ' 转义)
select '''+@schoolID+''' as SchoolID,count(*)as payCount,CONVERT(varchar(10),ReceiveTime,23) as payDate from '+@dbname+'[dbo].[tPaidin]
where BatchID=(select SetValue from  '+@dbname+'[dbo].[sConfig]
where code=''ConfirmBatch'')
group by CONVERT(varchar(10),ReceiveTime,23)

30、null表示空,用is null判断,''表示空字符串,用=判断

31、partition by 对数据分组排序
       row_number() over(排序依据) as ** 依据排序依据给数据编号
       select * from (select pro_id,img,create_time, ROW_NUMBER() over(partition by pro_id order by  create_time) as row_sort  from product_imgs )  as t where t.row_sort=1
       

32、分组中也可以加入筛选条件WHERE,不过这里一定要注意的是,执行顺序为:WHERE过滤→分组→聚合函数。牢记!
聚合函数在WHERE之后执行,所以这里在WHERE判断条件里加入聚合函数是做不到的。用having就可以完成
如果有分组的话,查询的列 必须要在分组中

where子句的作用是在对查询结果进行分组前,将不符合where条件的行去掉,即在分组之前过滤数据,条件中不能包含聚合函数,使用where条件显示特定的行。
having子句的作用是筛选满足条件的组,即在分组之后过滤数据,条件中经常包含聚合函数,使用having条件显示特定的组,也可以使用多个分组标准进行分组。

33、ctrl + k + c : 注释多行
        ctrl + k + u : 取消多行注释。当然,也可以注释一行!

34、自动排序加序号 
  Row_Number() over(order by **) 加序号递加
  Rank 是连续排序,但是是跳跃排序,例如有两个第一名,后面跟着的就是第三名
  dense_rank 是连续排序,有两个第二名时仍然跟着第三名

35、将两个或更多查询的结果集组合为单个结果集,该结果集包含联合查询中的所有查询的全部行。UNION的结果集列名与UNION运算符中第一个Select语句的结果集的列名相同。另一个Select语句的结果集列名将被忽略。其中两种不同的用法是UNION和UNION ALL,区别在于UNION从结果集中删除重复的行。如果使用UNION ALL 将包含所有行并且将不删除重复的行。

UNION和UNION ALL的区别:union 检查重复union all 不做检查比如 select 'a' union select 'a' 输出就是一行 a比如 select 'a' union all select 'a' 输出就是两行 a

36、nullif(a,b)如果a和b相同,则返回null值

37、SQL语句优化
 查看sql语句,尽量简化代码,有个清晰的结构。由于业务复杂,于是sql语句难免复杂,但是从代码易读性上考虑,可以优化代码结构。比如我们可以用cte。
 查看sqlserver执行计划,图十分复杂,看的人眼花缭乱。看这图,得从右边向左边看,慢慢分析,看主要性能耗费在哪个环节上。
 查看I/O统计

38、根据指定的值来排序
order by 
case `state`
when 2 then 1// 当值为2,排在第一个    
when 1 then 2// 当值为1,排在第二个
when 3 then 3// 当值为3,排在第三个    
end asc// 按上面顺序,正序排列(也可为desc)

39、EXEC命令的两种用法,一种是执行一个存储过程,另一种是执行一个动态的批处理

40、char是定长的,char(8),你输入的字符小于8时,它会再后面补空值。当你输入的字符大于指定的数时,它会截取超出的字符
varchar(n) 长度为 n 个字节的可变长度且非 Unicode 的字符数据
nvarchar(n) 
包含 n 个字符的可变长度 Unicode 字符数据
text存储可变长度的非Unicode数据,最大长度为2^31-1(2,147,483,647)个字符
nchar、nvarchar、ntext。这三种从名字上看比前面三种多了个“N”。它表示存储的是Unicode数据类型的字符
一般来说,如果含有中文字符,用nchar/nvarchar,如果纯英文和数字,用char/varchar

41、group by 按照后面的字段的先后顺序进行排序

42、如 :给emp表增加字段为id
alter table ODS_Voucher add  ID UniqueIdentifier default newid()
修改某一列内容用update

alter用法

在表的第一列添加一个字段

语法结构:alter table 表名 add 字段名 数据类型 first;

案例:在数据表tb_department1第一列添加一个int(11)类型字段telephone,使用的SQL语句如下:

命令语句:alter table tb_department1 add telephone int first;

 在表的指定列之后添加一个字段

语法结构:alter table 表名 add 字段名 数据类型 after 已存在字段名;

案例:在数据表tb_department1中name列后添加一个int型字段column1,指定部门的名称唯一,使用的SQL语句如下:

命令语句:alter table tb_department1 add column1 int after name;

 
43、select 查询默认不会去掉重复行,除非指定distinct
        union 合并操作会去掉重复行,不想去重复行,用union all

44、用连接查询代替多嵌套子查询可以提高查询效率

45、Join (连接) 

across join(交叉连接)

select * from a,b 一次查询两张表的数据,行数是a,b表的乘积,列数是a,b 表的和,这种多表查询又称笛卡尔查询,使用笛卡尔查询时要非常小心,由于结果集是目标表的行数乘积,对两个各自有100行记录的表进行笛卡尔查询将返回1万条记录,对两个各自有1万行记录的表进行笛卡尔查询将返回1亿条记录。

内连接(inner join) ,外连接:没有匹配数据的另外一张表返回空的数据

46、sunstring截取字符串,substring( expression, start, length)
参数:expression 字符串、二进制字符串、文本、图像、列或包含列的表达式start 整数或可以隐式转换为 int 的表达式,指定子字符串的开始位置length 整数或可以隐式转换为 int 的表达式,指定子字符串的长度

47、增加字段

ALTER TABLE tableName ADD  columnName VARCHAR(20) DEFAULT NULL --增加一个字段,默认为空

ALTER TABLE tableName ADD  columnName VARCHAR(20) NOT NULL --增加一个字段,默认不能为空

修改字段类型

ALTER TABLE tableName ALTER COLUMN columnName VARCHAR(10) --修改一个字段的类型

修改主键

Declare @Pk varChar(100); Select @Pk=Name from sysobjects where Parent_Obj=OBJECT_ID('tableName') and xtype='PK'; if @Pk is not null exec('Alter table tableName Drop '+ @Pk)

48 、删除指定条件行的数据

 delete from [SHBudget].[dbo].[Courses] where StudentNO like 'PT%'

删除表中数据 Drop Delete Truncate

执行速度,一般来说: drop> truncate > delete。

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

drop语句将删除表的结构被依赖的约束(constrain),触发器(trigger)索引(index);依赖于该表的存储过程/函数将被保留,但其状态会变为:invalid。

delete (删除表中的数据):delete 语句用于删除表中的行。delete语句执行删除的过程是每次从表中删除一行,并且同时将该行的删除操作作为事务记录在日志中保存,以便进行进行回滚操作。

truncate table 删除表中的所有行,但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值,请改用delete。

49、Bulk Insert 百万级数据导入(暂时没用到,用EXCEL直接导入数据会很快)

50、在本地建一个数据库,让其他人连接上本地数据库(打开本地防火墙入站规则1433端口,然后打开SQL Sever 配置管理器,启动实例SQLSever网络配置协议的TCP/IP协议)

51、 update [dbo].[Courses]  set StudentType =  (case when  exists
                                                                 (select StuNo from [SHBudget].[dbo].[Student] where StuNO=[Courses].StudentNO  and StudentType='本科生') then 1
                                                   when  exists
                                                                 (select StuNo from [SHBudget].[dbo].[Student] where StuNO=[Courses].StudentNO  and StudentType='硕士研究生') then 2
                                                   when  exists
                                                                 (select StuNo from [SHBudget].[dbo].[Student] where StuNO=[Courses].StudentNO  and StudentType='博士研究生') then 3 end)

使用update一定要注意有没有where条件,不然更新可能会导致其他不能满足条件的数据被覆盖

52、聚集索引(clustered):数据行的物理顺序与列值(一般是主键的那一列)的逻辑顺序相同,一个表中只能拥有一个聚集索引。

非聚集索引:该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同,一个表中可以拥有多个非聚集索引。

假如索引是聚集索引,那么B树索引的叶子节点保存的是数据页中的实际数据。假如索引是非聚集索引,那么B树叶子节点保存的是指向数据页的指针。

https://www.cnblogs.com/xwdreamer/archive/2012/07/06/2579504.html

https://blog.sqlauthority.com/2009/08/24/sql-server-index-seek-vs-index-scan-diffefence-and-usage-a-simple-note/

53、索引视图实际上是一种将一组唯一值“物化”为群集索引形式的视图(白话是,给视图中的唯一值列加聚集索引,然后数据会存储在硬盘中),提高查询速度。

54、SQL Sever中的架构,通俗点说就是一个组,里面可以存放表、视图、存储过程等
主要是用于权限控制安全控制的,不同架构拥有的权限不同

 

55、复制表数据到已经存在的表

INSERT INTO targetTableName SELECT COLUMNS FROM sourceTableName;

复制表数据到新建的表

SELECT COLUMNS INTO newTableName FROM sourceTableName where whereExpression ;

 

56、char、varchar、nchar、nvarchar的区别

对于程序中的string型字段,SQLServer中有char、varchar、nchar、nvarchar四种类型来对应(暂时不考虑text和ntext),开建立数据库中,对这四种类型往往比较模糊,这里做一下对比。

定长或变长

所谓定长就是长度固定的,当输入的数据长度没有达到指定的长度时将自动以英文空格在其后面填充,使长度达到相应的长度;有var前缀的,表示是实际存储空间是变长的,比如varchar,nvarchar变长字符数据则不会以空格填充,比较例外的是,text存储的也是可变长。

Unicode或非Unicode

数据库中,英文字符只需要一个字节存储就足够了,但汉字和其他众多非英文字符,则需要两个字节存储。如果英文与汉字同时存在,由于占用空间数不同,容易造成混乱,导致读取出来的字符串是乱码。Unicode字符集就是为了解决字符集这种不兼容的问题而产生的,它所有的字符都用两个字节表示,即英文字符也是用两个字节表示。而前缀n就表示Unicode字符,比如nchar,nvarchar,这两种类型使用了Unicode字符集。

基于以上两点来看看字段容量

char,varchar 最多8000个英文,4000个汉字
nchar,nvarchar 可存储4000个字符,无论英文还是汉字

使用(个人偏好)

如果数据量非常大,又能100%确定长度且保存只是ansi字符,那么char
能确定长度又不一定是ansi字符或者,那么用nchar;
对于超大数据,如文章内容,使用nText

其他的通用nvarchar

char、varchar、nchar、nvarchar特点比较

CHAR

CHAR存储定长数据很方便,CHAR字段上的索引效率级高,比如定义char(10),那么不论你存储的数据是否达到了10个字节,都要占去10个字节的空间。

VARCHAR

存储变长数据,但存储效率没有CHAR高,如果一个字段可能的值是不固定长度的,我们只知道它不可能超过10个字符,把它定义为 VARCHAR(10)是最合算的。VARCHAR类型的实际长度是它的值的实际长度+1。为什么"+1"呢?这一个字节用于保存实际使用了多大的长度。
从空间上考虑,用varchar合适;从效率上考虑,用char合适,关键是根据实际情况找到权衡点。

TEXT

text存储可变长度的非Unicode数据,最大长度为2^31-1(2,147,483,647)个字符。

NCHAR、NVARCHAR、NTEXT

这三种从名字上看比前面三种多了个"N"。和char、varchar比较起来,nchar、nvarchar最多存储4000个字符,不论是英文还是汉字;而char、varchar最多能存储8000个英文,4000个汉字。可以看出使用nchar、nvarchar数据类型时不用担心输入的字符是英文还是汉字,较为方便,但在存储英文时数量上有些损失。
所以一般来说,如果含有中文字符,用nchar/nvarchar,如果纯英文和数字,用char/varchar。

57、数据库中码 候选码 主码的区别

主码    候选码   码

ps:元组理解为一张表的某一行,属性理解为一张表的某一列,属性名就是列的名字(字段)。

1(码):码是可以确定一个元组的所有信息的属性名或属性名组。

例如在 { a, b, c, d } 中,

假设知道 a 的值就能确定  a, b, c, d  的值,

假设知道 c, d 的值就可以确定  a, b, c, d  的值,

那么 { a } 就是码,{ c, d } 就是码。

并且 { a, b }, { a, c }, { a, b, c }, { a, b, c, d } 等也都是码,因为它们也可以确定一个元组的所有值,即使很多余。

2(候选码):候选码的真子集中不存在码,候选码可以有多个。

就上面的例子而言,{ a } 是候选码,{ c, d } 是候选码,因为它们的真子集中不存在码。

而诸如 { a, b } 并不是候选码,因为它的真子集中含有 { a }, 且 { a } 是码。

3(主码):主码就是主键的意思,主码是任意一个候选码。

还是上面的例子,主码是候选码 { a }, { c, d } 中的其中一个。

既可以是 { a }, 也可以是 { c, d }。 

58、执行计划,可以用来查看sql执行的过程,进行优化分析

执行计划结果出来了,那我们要怎么看呢?一般我们只需关注以下几个参数。

  1. 哪一步开销较高。开销较高的地方说明这个地方查询比较耗时。
  2. 哪些步骤所影响的数据行较多。这个可以通过连接线条的粗细来判断。
  3. 每一步做了些什么事情。

  要了解Sql Server的五种查询方式之前,我们要弄明白两个概念,Sql Server中的两种索引,聚集索引和非聚集索引。【聚集索引】直接决定了记录的存放位置,或者说,根据聚集索引可以直接获取到该记录,一般我们表的主键都是用聚集索引。【非聚集索引】则保存了二个信息,1.相应索引字段的值。2.记录对应聚集索引的位置(如果表没有聚集索引则保存记录指针)。因此,如果能通过聚集索引来查找记录,则速度是最快的。

  下面是Sql Server查询数据的五种方式,这对我们理解执行计划非常重要。五种方式如下。

  1. 【Table Scan】:遍历整个表来查找匹配的数据行,速度最慢。
  2. 【Index Scan】:依据索引先从表中过滤出一部分记录,然后再查找所有匹配的数据行。查询速度比Table Scan稍快。
  3. 【Index Seek】:依据索引,定位记录的存放位置,然后再取得记录,因此,其查询速度比前面两种都快。
  4. 【Clustered Index Scan】:按聚集索引(一般是主键)遍历整个表,因为它的记录就是按聚集索引来顺序存放的。注意它与Table Scan的区别,其实它们都是进行全表扫描,只不过Table Scan是不带索引的扫描,而Clustered Index Scan是按聚集索引扫描的。
  5. 【Clustered Index Seek】:聚集索引获取记录,它是直接拿到那条记录,而没有进行全表扫描,因此它的查询速度是最快的。

  当我们查看执行计划结果的时候,如果看到【Table Scan】,说明这个表没有建立任何索引,包括聚集索引。但往往看到更多的是【Clustered Index Scan】,表示该查询还是扫描了速个表,只不过是按聚集索引,实际效果还是和【Table Scan】没什么区别,因此,这时候我们可能要考虑建立'组合字段索引'。

能过执行计划的结果图我们只能看出哪一块比较耗时,但是看不到Sql Server具体是怎么执行的。为了看得更明白,我们可以通过一条sql命令来查看,如下代码。

set statistics profile on

59、触发器 https://www.cnblogs.com/chengliping/p/4082930.html

在SQL Server里面也就是对某一个表的一定的操作,触发某种条件,从而执行的一段程序。触发器是一个特殊的存储过程,它不能被显式地调用﹐而是在往表中插入记录﹑更新记录或者删除记录时被自动地激活。所以触发器可以用来实现对表实施复杂的完整性约束。

  SQL Server为每个触发器都创建了两个专用表:Inserted表和Deleted表。Deleted和Inserted分别代表出发事件的表“旧的一条记录”和“新的一条记录”。这两个表由系统来维护﹐它们存在于内存中而不是在数据库中。这两个表的结构总是与被该触发器作用的表的结构相同。触发器执行 完成后﹐与该触发器相关的这两个表也被删除。  

Instead of 、 After、for触发器

   Instead of触发器用于替代引起触发器执行的T-SQL语句。除表之外﹐Instead of 触发器也可以用于视图﹐用来扩展视图可以支持的更新操作。        

  After触发器在一个Insert,Update或Deleted语句之后执行﹐进行约束检查等动作都在After触发器被激活之前发生。After触发器只能用于表。一个表或视图的每一个修改动作(insert,update和delete)都可以有一个instead of 触发器﹐一个表的每个修改动作都可以有多个After触发器。

  for触发器与after相似。for与Insert,Update或Deleted语句同时执行。

60、临时表和临时表变量的区别 https://www.cnblogs.com/qixuejia/p/3860410.html?utm_source=tuicool

跳转

61、Delete Drop 和Truncate的区别

delete删除数据,会进行事务的提交,可以回滚,不释放数据空间

Truncate会删除数据,不会进行事务的提交,不可以回滚,释放数据空间

Drop会删除数据和表结构,不会进行事务的提交,不可以回滚,释放表空间

62、https://www.cnblogs.com/JohnXIe/archive/2008/07/24/1250534.html  临时表 全局表 表变量 

 

63、数据库实例相当于程序,数据库相当于二进制文件,需要程序对二进制文件进行增删改查操作

 

64、以一张表的数据更新另外一张表里面的数据

(1)多表关联更新数据,主表有对应的数据才会更新,否则不更新,这种方法比较保险

update CourseHandle  set CourseHandle.SubjectCode = Temp.SubjectCode from CourseHandle,Temp where
CourseHandle.Stu_DepartCode=Temp.Stu_DepartCode

(2)在关联表中找和主表指定字段相同的数据,如果没有对应的数据,则返回空值,这样主数据有可能丢失,慎用

update CourseHandle  set SubjectCode  = (select SubjectCode from Temp where CourseHandle.Stu_DepartCode=Temp.Stu_DepartCode)
 

 

65、将Excel中的数据导入到数据库表A后,某一列被自动定义成了Float数据类型,然而,我们的正式库中的表B的相对应的列是Varchar类型的,所以,就出现了表B中所对应的列数据变成了科学计数法的形式。

解决方案:数值型类型有精确数值和非精确数值

原文地址:http://www.cnblogs.com/studyzy/archive/2009/08/27/1555356.html

存储数据的sql类型

精确型:Numeric、Decimal

精确数值数据类型是那些可以指定精度值和小数位数值的数据类型,而近似数值数据类型是以预定义方式存储的。只有精确数值数据能确保会在算术运算后精确到指定的最小有效位

近似型:Float、Double、Real

 

 

66、以A表为条件删除B表内容,若是多字段条件匹配,使用exists

  Delete from Temp_Student  where StateID like '[234]%'   and not exists (select 1 from ODS_Course as c where 
  Temp_Student.StuNO=c.StuNo and Temp_Student.Year = c.Year )

67、日志文件的清除

USE [SHBudget]
ALTER DATABASE [SHBudget]
SET RECOVERY SIMPLE WITH NO_WAIT
ALTER DATABASE [SHBudget]
SET RECOVERY SIMPLE
GO
USE [SHBudget]
DECLARE @logname VARCHAR(150)
SELECT @logname = NAME
FROM sys.database_files
WHERE NAME LIKE'%log'
DBCC SHRINKFILE(@logname, 11, TRUNCATEONLY)
GO
USE [SHBudget]
ALTER DATABASE [SHBudget]
SET RECOVERY FULL WITH NO_WAIT
ALTER DATABASE [SHBudget]
SET RECOVERY FULL
GO

68、更改mdf与ldf的存储位置

换Data存储位置:https://www.cnblogs.com/bravenight/p/4497249.html

69、sql给字符串按指定长度补0

select right('00000000'+cast(convert(int,[/BIC/ZVDSK1])as varchar),8) from [SHBudget].[dbo].[主数据$]

 

70、数据的差集;利用sql server取两个数据集的交、差、补集

SQLServer中通过intersect,union,except和三个关键字对应交、并、差三种集合运算。

71、添加约束

添加主键约束(给建好的表添加联合主键约束)

Alter table [ODS_TempStaff] alter column Year int not null 
Alter table [ODS_TempStaff] alter column GH varchar(20) not null 
--增加主键
Alter table [ODS_TempStaff] add constraint pk_Y_G primary key( Year, GH)
 72、不要用中文做匹配,要用唯一标识的中文

73、最好不要存null值,当作为参数来进行查询的时候,null值当作什么都不传,会有匹配null值的数据得不到匹配

74、对数据进行计数或者求和操作的时候,注意逻辑,有没有重复的数据,需要distinct出子表

75、字符串处理

字符串匹配四种:%   [ ]   [^ ]   _  

charindex()函数:

CHARINDEX ( expressionToFind , expressionToSearch [ , start_location ] )

expressionToFind :目标字符串,就是想要找到的字符串,最大长度为8000 。

expressionToSearch :用于被查找的字符串。

start_location:开始查找的位置,为空时默认从第一位开始查找。

select id, substring(str,charindex(',',str)+1,len(str)-charindex(',',str)) from test;

patindex() 函数:

返回pattern字符串在表达式expression里第一次出现的位置,起始值从1开始算

reverse(substring(reverse(accountCode),patindex('%[1-9]%',reverse(accountCode)),len(accountCode))) as accountCode,accountName :去除字符串右边的0

replace()函数:替换指定的字符串

stuff()函数:删除指定长度的字符,并在指定的起点处插入另一组字符。

parsename()函数:解析以'.'分隔开的字符串,一般解析IP地址

76、删除以指定字符开头的表

先将要删除的表格查询出来,再将拼接好的删除语句粘贴到可视化编辑窗口,执行语句

77、GUID和UUID的区别

UUID全称:Universally Unique Identifier,即通用唯一识别码。是一个由4个连字号(-)将32个字节长的字符串分隔后生成的字符串,总共36个字节长。比如:550e8400-e29b-41d4-a716-446655440000

UUID是让分布式系统中的所有元素都能有唯一的辨识信息,而不要要通过中央控制端来做辨识信息的指定。如此一来,每个人都可以创建不与其他人冲突的UUID。在这样的情况下,就不需考虑数据库创建时的名称重复问题。目前最广泛应用的UUID,是微软公司的全局唯一标识符(GUID),而其他重要的应用,则有Linux ext2/ext3文件系统、LULS加密分区、GNOME、KDE、Mac OS X等等。

GUID(Globally Unique Identifier):是微软对UUID这个标准的实现。UUID是由开放软件基金会(OSF)定义的。UUID还有其它各种实现,不止GUID一种。比如我们这里在Java中用到的。

在已经建立的表中,新增GUID字段:insert into 表名 (字段名) values (newid())

78、内容匹配
SELECT *
  FROM [SHBudget].[dbo].[DWD_Voucher]  where DepartCode like '[a-zA-Z]%'  /*查询以字母开头的数据*/

79、时间戳 TimeStap的理解:数据库中自动生成的唯一二进制数字,与时间和日期无关的, 通常用作给表行加版本戳的机制。存储大小为 8个字节。

每个数据库都有一个计数器,当对数据库中包含 timestamp 列的表执行插入或更新操作时,该计数器值就会增加。该计数器是数据库时间戳。这可以跟踪数据库内的相对时间,而不是时钟相关联的实际时间。一个表只能有一个 timestamp 列。每次修改或插入包含 timestamp 列的行 时,就会在 timestamp 列中插入增量数据库时间戳值。这一属性使 timestamp 列不适合作为键使用,尤其是不能作为主键使用。对行的任 何更新都会更改 timestamp 值,从而更改键值。如果该列属于主键,那么旧的键值将无效,进而引用该旧值的外键也将不再有效。如果该表在动态游标 中引用,则所有更新均会更改游标中行的位置。如果该列属于索引键,则对数据行的所有更新还将导致索引更新,时间戳是不可以转换为时间的,只能标注该行修改了,定义也不应选择为时间类型,并且时间戳应尽量由数据库维护。

使用某一行中的 timestamp 列可以很容易地确定该行中的任何值自上次读取以后是否发生了更改。如果对行进行了更改,就会更新该时间戳值。如果没有对行进行更改,则该时间戳值将与以前读取该行时的时间戳值一致。若要返回数据库的当前时间戳值,请查询具体的数据库定义。

已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页