目录

      本文主旨:条件列上的索引对数据库delete操作的影响。

sqlServer_基础概念

1、索引的工作原理

我给大家推荐一个别人的总结。

  • 1.架构
    • 1.1.创建架构并在架构中创建表
    • 1.2.删除架构
    • 1.3.修改表的架构
  • 2.视图
    • 2.1.新建视图
    • 2.2.使用视图修改数据
    • 2.3.删除视图
  • 3.索引
    • 3.1.聚集索引
    • 3.2.非聚集索引
    • 3.3.创建索引
    • 3.4.修改索引
    • 3.5.查看索引
    • 3.6.查看索引碎片
    • 3.7.查看统计信息

     
事由:今天在博客园北京俱乐部MSN群中和网友讨论了关于索引对delete的影响问题,事后感觉非常汗颜,因为我的随口导致错误连篇。大致话题是这样的,并非原话:

SQL server的管理工具

2、索引的设计原则

对于一张表来说,索引的有无和建立什么样的索引,要取决与where字句和Join表达式中。

一般来说建立索引的原则包括以下内容:

主键列:系统一般会自动建立聚集索引。

非主键列:有大量重复值并且经常进行条件查询、排序、分组的列,或者经常频繁访问的列,考虑建立聚集索引。

如果在一个经常做插入操作的表中建立索引,应使用fillfactor(填充因子)来减少页分裂,同时提高并发度降低死锁的发生。如果在表为只读表,填充因子可设为100。

另外我们在选择索引键的时候,尽量采用小数据类型(最好是整数)的列作为索引键,这样每个索引页能尽可能多的容纳索引键和指针,用整数的好处是因为整数的访问速度最快。

1.架构

架构是一种独立于用户的逻辑分组,组中可以存储表,视图,存储过程等。假如表1在架构1中,表2在架构2中,用架构1的用户名登录时表2不可见。且未添加该架构的数据库不能被该架构的用户访问。

      [讨论:] delete course where classID=500001
classID上没有创建任何索引,为了提高删除效率,如果在classID上创建一个非聚集索引会不会提高删除的效率呢?  

SQL server联机丛书

开始菜单à Microsoft SQL Server 2008 à 文档和教程 à SQL Server联机丛书

SQL Server 配置管理器

用于启动和管理SQL server数据库的服务端,以及其他相关功能。

当我们启动SQL Server 配置管理器之后,可以在左侧目录中看到“SQL Server服务”,在“SQL Server服务”里,我们就可以对SQL Server的服务端,也就是核心数据引擎进行管理。

其中“SQL Server (MSSQLSERVER)” 和 “SQL Server (SQLEXPRESS)”就是代表我们所安装的具体的服务端,前者是正式版,后者是体验版。

打开Server配置管理器的另一种方法:

“我的电脑”à右键菜单à管理à”服务和应用”àSQL Server配置管理器

3、使用索引的注意事项

动作描述 使用聚集索引 使用非聚集索引
 外键列
 主键列
 列经常被分组排序(order by)
 返回某范围内的数据(BETWEEN、>、>=、< 和 <=)  
 小数目的不同值  
 大数目的不同值  
 频繁更新的列  
 频繁修改索引列  
 一个或极少不同值    

1.1.创建架构并在架构中创建表

执行如下语句

CREATE LOGIN hy WITH PASSWORD = '123456'
GO
--新建登录名
CREATE DATABASE schematest
GO
--新建数据库
USE schematest
GO
CREATE USER u_for_test FOR LOGIN hy
GO
CREATE SCHEMA dbo_Schema
go
--在schematest数据库下添加dbo_Schema
CREATE TABLE T1(id INT,NAME VARCHAR(20))
go

CREATE TABLE dbo_Schema.T2(Nid int,DD datetime)
go

GRANT SELECT ON SCHEMA :: dbo_Schema TO u_for_test;
--给u_for_test赋予SELECT权限
--重新使用hy登录即可。

用hy登录,打开未添加dbo_Schema架构的数据库,出现如下提示
图片 1
打开schematest数据库,展开表,dbo_Schema下的T2表可见,非dbo_Schema架构下的T1表不可见。
图片 2

      我当时的观点:不能。

SQL server profiler

当我们的数据服务端出现问题和故障的时候,它可以给我们提供实时的跟踪工具,和性能监控的作用。

4、索引的分类

按存储结构区分

“聚集索引(又称聚类索引,簇集索引)”,“分聚集索引(非聚类索引,非簇集索引)”

聚集索引

每个表只能有一个聚集索引,默认情况下主键默认就是聚集索引。聚集索引确定表中数据的物理顺序。就好比字典中按拼音查找一样。

定义聚集索引时使用的列越少越好。

聚集索引不适用于:频繁更改的列(这将导致整行移动(因为 SQL Server
必须按物理顺序保留行中的数据值。因为在大数据量事务处理系统中数据是易失的)

聚集索引的约束唯一性并不是指字段也要是唯的。

创建聚集索引语法:create  CLUSTERED  Index  索引名称 on
表名(需要创建索引列)

非聚集索引

一个表如果没有聚集索引时,理论上可以建立249个非聚集索引。每个非聚集索引提供访问数据的不同排序顺序。

如果创建索引时不加索引关键字,默认创建的就是非聚集索引。

数据存储在一个地方,索引存储在另一个地方,索引带有指针指向数据的存储位置。就好比字典中按偏旁查找一样。

创建非聚集索引语法:create NONCLUSTERED index 索引名 on
表名(创建索引列)

按数据唯一性区分:

“唯一索引”,“非唯一索引”

唯一键索引

建立唯一键约束(默认非聚集索引,实际上唯一键约束是用唯一索引来约束的)

创建唯一键约束,同时创建同名的唯一非聚集索引, 同时创建同名统计信息;
唯一键约束靠唯一索引来约束。

唯一键约束的索引不能像正常的索引使用太多的索引参数,因为唯一键约束与其索引同在。而单独创建的唯一索引可以设置更多的参数。

创建唯一键约束语法:  alter TABLE 表名 add  constraint   索引名称
unique(需要创建的列)(删除唯一键索引的语句跟删除主键聚集索引一样)

唯一索引

唯一索索引跟唯一键约束的作用是一样的,都是来检测数据的唯一性。

不管是建立唯一索引还是唯一约束,被创建的列都不允许有重复数据,重复的NULL值也不可以。

唯一索引创建语法:CREATE unique index ix_RowID  on
TABLE(RowID)删除语句:drop index 索引名

唯一键索引与唯一索引对比

功能一样,唯一键索引比唯一索引多验证 unique key。

唯一键索引没有唯一索引灵活。

按键列个数区分:

“单列索引”,“多列索引”。

千万数据量时。多列索引会比多个单列索引速度快很多。

索引视图

索引视图是具体化的视图,它的结果集是经过计算的,并且存储在数据库中。

索引视图更适合在OLAP(读取较多,更新较少)的数据库中使用,不适合在OLTP(记录即时的增、删、改、查)的数据库中使用

一个标准视图转换为一个索引视图必须遵守以下规则:  

1.视图必须使用With Schemabinding选项来创建。如果创建视图时没有with Schemabinding,试图创建视图时就会报错,因为该视图未绑定到架构。注意:
schemabinding建立索引的时候必须先创建唯一聚集索引。  

2.在这个视图中不能使用其他视图、导出表、行集函数或自查询,也就是说只能使用表。

3.视图只能链接同一个数据库中的表并且链接表时只能使用INNER JOIN。
INNER JOIN前后不能使同一个表,不能使用LEFT(RIGHT) JOIN 或者 LEFT (RIGHT) OUTER JOIN

4.视图不能包含UNION子句、TOP子句、ORDER BY子句、Having子句、Rollup子句、Cube子句、compute子句、Compute By子句或Distinct关键字。

5.视图不允许使用某些集合函数,如:Count(*)可以使用count_big(*)代替、avg()、max()、min()、stdev()、stdevp()、var()或varp()等。

6.视图不能使用Select * 这样的语句,也就是说视图的所有字段都必须显示指定。

7.视图不能包含Text、ntext、image类型的列。

8.如果视图包含一个Group By子句,那么他必须在Select列中包含count_big(*)。

位图索引(慎用

位图索引适用于低基数的列,比如说“性别”列,数据仓库中的维表的主键,等等。理论上来说,他们都适合应用位图索引。但是这并不是使用位图索引唯一的条件。滥用位图索引会导致严重的错误,而且这些错误往往是很隐蔽的,不易被发现的错误。

位图索引的原理:

采用位图索引,一个键指向多行,有时候是数以百计甚至更多。如果更新了一个位图索引键,那么这个键指向的数以百计的记录会与你实际更新的那一行一同被锁定。

1.2.删除架构

删除架构前必须删除或者移动该架构的所有对象,不然删除操作将会失败。如执行下列语句

DROP SCHEMA dbo_Schema
GO

结果如图所示
图片 3
此时要将T2表删除或者移动到其他架构才能成功删除dbo_Schema

     
我当时的理由:数据库在执行删除时,如果在classID上创建了非聚集索引,首先按这个非聚集索引查找数据,找到索引行后,根据索引行后面带的聚集索引地址最后找到真正的物理数据行,并且执行删除,这个过程看起来没有作用,只能创建聚集索引来提高删除效率,因为如果classID是聚集索引,那么直接聚集索引删除,此时的效率最高。

SQL Server Management Studio

它就是SQL server的图形化的管理界面,也就是客户端。

5、索引的查找

数据库中有一个名为sysindexes的系统表,专门管理索引。查看一张表的索引属性,可以在查询分析器中使用以下命令:select
* from sysindexes where
id=object_id(‘A);而要查看表的索引所占空间的大小,可以使用系统存储过程命令:sp_spaceused
A,其中参数A为被索引的表名。

1.3.修改表的架构

如图所示,右键表名——设计——右侧属性栏中修改表的架构
图片 4
如图所示,当把T2表所引用的架构修改为dbo后,可继续删除架构dbo_Schema操作。就能成功删除dbo.Schema
图片 5

     
下班后对这个话题再次想了下,觉的自己的观点都自相矛盾,既然知道删除时,会在条件列上试图应用已经存在的索引,那么为什么创建非聚集索引会无效呢?如果表的数据相当大,classID上如果没有任何索引,查找数据时就要执行表扫描,而表扫描的速度是相当慢的,为此为了证明下这个问题,我特意做了一个示意性的实验。

启动Management Studio

在登陆界面输入相关的信息:

服务器类型:数据库引擎

服务器名称:我们可以输入IP地址,
计算机名称。如果是访问本机的SQL server服务并且没有改变默认端口号的话,只需要输入一个点
” . ”,它就代表本机的SQL Server正式版的服务端。(体验版是.\SQLEXPRESS)

身份验证:SQL Server身份验证

用户名:sa

密码:sa

6、索引语法

 [ UNIQUE ]唯一索引    [CLUSTERED]聚集索引  
[NONCLUSTERED ] 非聚集索引

CREATE [UNIQUE] [CLUSTERED| NONCLUSTERED ]

INDEX index_name ON { table | view } ( column [ ASC | DESC ] [ ,…n
] )

[with[PAD_INDEX][[,]FILLFACTOR=fillfactor]

[[,]IGNORE_DUP_KEY] //用于控制当往包含于一个唯一聚集索引中的列中插入重复数据时SQL
Server所作的反应。

[[,]DROP_EXISTING] //用于指定应删除并重新创建已命名的先前存在的聚集索引或者非聚集索引。

[[,]STATISTICS_NORECOMPUTE]  //用于指定过期的索引统计不会自动重新计算。

[[,]SORT_IN_TEMPDB] //用于指定创建索引时的中间排序结果将存储在
tempdb 数据库中。

]

[ ON filegroup ] //用于指定存放索引的文件组。

 

CREATE INDEX命令创建索引各参数说明如下:

UNIQUE:用于指定为表或视图创建唯一索引,即不允许存在索引值相同的两行。

CLUSTERED:用于指定创建的索引为聚集索引。

NONCLUSTERED:用于指定创建的索引为非聚集索引。

index_name:用于指定所创建的索引的名称。

table:用于指定创建索引的表的名称。

view:用于指定创建索引的视图的名称。

ASC|DESC:用于指定具体某个索引列的升序或降序排序方向。

Column:用于指定被索引的列。

PAD_INDEX:用于指定索引中间级中每个页(节点)上保持开放的空间。

FILLFACTOR =
fillfactor
:用于指定在创建索引时,每个索引页的数据占索引页大小的百分比,fillfactor的值为1到100。

2.视图

视图是数据库中原始数据的一种变换,是查看表数据的一种方式,视图是一种逻辑对象,是虚拟的表,是一串SELECT语句,并不是真实的表。

       创建两个表course
和course2,创建语句如下,它们唯一的区别就在于索引,course表中classID上创建了非聚集索引,而course2上没有创建任何索引

当SQL Server身份验证无法登陆时

1、 用windows身份验证(也就是用本机管理员来登陆,不需要输入用户名密码的)。

2、 展开左侧目录中的 SQL
Server à 安全性 à 登陆名 à 双击sa à 打开sa 用户的属性窗口。

3、 修改密码

4、 取消”强制实施密码策略”

5、 在“状态”选项卡中,对“是否允许连接到数据引擎”和“登陆”分别选择“授予”和“启动”。

6、 点击确定关闭sa 用户的属性窗口

7、 右键点击服务器根节点,选择属性打开“服务器属性”弹窗。

8、 选择“安全性”选项卡,设置“服务器身份验证”为“SQL Server和Windows身份验证”。

9、 确定并关闭“服务器属性”弹窗,然后在SQL server配置管理器中重启数据服务端,再用sql server 帐户密码来登陆即可。

7、示例

–表bigdata创建一个名为idx_mobiel的非聚集索引,索引字段为mobiel

create index idx_mobiel

on bigdata(mobiel)

–表bigdata创建一个名为idx_id的唯一聚集索引,索引字段为id

–要求成批插入数据时忽略重复值,不重新计算统计信息,填充因子为40

create unique clustered index idx_id

on bigdata(id)

with pad_index,

fillfactor=40,

ignore_dup_key,

statistics_norecompute

2.1.新建视图

示例1:利用student表和class_student表的数据新建视图class_01,记录01班学生详细信息
Student表的数据如图所示
图片 6
Class_student表的数据如图所示
图片 7
执行下列语句新建视图class_01

CREATE VIEW class_01
AS
SELECT class_student.stu_no,class_id,stu_name,stu_sex,stu_age,stu_addr,stu_native_place,stu_birthday,stu_enter_score,stu_phone,stu_father_name,stu_mather_name
FROM class_student INNER JOIN student
ON class_student.stu_no=student.stu_no
WHERE class_id='01'

视图class_01的数据如图所示
图片 8

注:视图只是一个SELECT语句,数据根据基表的数据改变而自动改变。

CREATE TABLE [dbo].[course](
 [ID] [int] IDENTITY(1,1) NOT NULL,
 [sCourseName] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,
 [classID] [int] NULL,
 CONSTRAINT [PK_CKH] PRIMARY KEY CLUSTERED 
(
 [ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
–创建索引
create index IX_classID
on course(classID)

SQL Management Studio的界面操作

左侧目录中,我们可以创建数据库数据表。

左上角的新建查询按钮,可以打开一个输入与执行SQL语句的窗口。在此窗口中我们可以通过按F5键或点击“执行”来运行SQL语句。 在输入多条SQL语句的情况下,可以选中需要执行的代码,然后按F5来只执行被选中的部分。

8、清理索引

先分析表的索引:当你发现,扫描密度行,最佳计数和实际计数的比例已经严重失调。逻辑扫描碎片占了非常大的百分比,每页平均可用字节数非常大时,就说明你的索引需要重新整理一下了。

当索引碎片太多的时候,就会很严重地影响到查询的速度。检查索引碎片 DBCC
SHOWCONTIG(表)

这时候我们可以采取两种方法来解决:

一种时整理索引碎片(DBCC INDEXDEFRAG),另一种是重建索引(DBCC
DBREINDEX)

DBCC INDEXDEFRAG
只有在该命令正在运行时才可用。而且可以在不丢失已完成工作的情况下中断该操作。这种方法的缺点是在重新组织数据方面没有聚集索引的除去/重新创建操作有效。

重新创建聚集索引将对数据进行重新组织,其结果是使数据页填满。填满程度可以使用
FILLFACTOR
选项进行配置。这种方法的缺点是索引在除去/重新创建周期内为脱机状态,并且操作属原子级。如果中断索引创建,则不会重新创建该索引。

也就是说,要想获得好的效果,还是得用重建索引。

DBCC DBREINDEX(表,索引名,填充因子)

第一个参数,可以是表名,也可以是表ID。

第二个参数,如果是”,表示影响该表的所有索引。

第三个参数,填充因子,即索引页的数据填充程度。如果是100(这里是%),表示每一个索引页都全部填满,此时select效率最高,但以后要插入索引时,就得移动后面的所有页,效率很低。如果是0,表示使用先前的填充因子值。

DBCC DBREINDEX(A,”,100)

2.2.使用视图修改数据

示例2:有course表数据,基于course表新建视图coursetest,列名为course_id,course_name,credits。
Course表数据如图所示
图片 9
执行下列语句新建coursetest视图

CREATE VIEW coursetest
AS
SELECT course.course_id,course_name,credits FROM course

Coursetest视图数据如图所示
图片 10
在coursetest视图中插入一行course_id为“0013”的数据

INSERT INTO coursetest(course_id,course_name,credits)
VALUES('0013','嵌入式系统开发','5')

Course表数据如图所示
图片 11
这行数据也被插入到course表中,在基于单张表的视图中可以通过增删改视图数据来更新基表数据,对基于多张表的视图不可更新。

CREATE TABLE [dbo].[course2](
 [ID] [int] IDENTITY(1,1) NOT NULL,
 [sCourseName] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,
 [classID] [int] NULL,
 CONSTRAINT [PK_CKH2] PRIMARY KEY CLUSTERED 
(
 [ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

sqlcmd命令行管理工具

通过纯指令的方式来管理SQL
server数据库服务端。

开始菜单à 运行à输入cmd打开命令行窗口à输入sqlcmd /? 查看sqlcmd命令的帮助信息。

在该命令行下我们可以通过sql语句来操作数据库。

比如:

sqlcmd

use test

select * from student

go

最后,必须输入go才会开始执行SQL语句。exit退出sqlcmd命令行状态。

9、管理索引

select * from sysindexes where id=object_id(‘A’)// 查看A表的索引属性

exec sp_spaceused A //查看表的索引所占空间的大小A为被索引的表名

 

–查看索引定义

Exec sp_helpindex A  

–将索引名由’id’ 改为’idx’

Exec sp_rename A.id’,’idx’ 

–删除A表中的idx索引

drop index A.idx

–检查A表中索引id的碎片信息

dbcc showcontig(A,id)

–整理test数据库中A表的索引id上的碎片

dbcc indexdefrag(Test,A,id)

–更新A表中的全部索引的统计信息

update statistics A

 

2.3.删除视图

DROP VIEW coursetest

      实验过程:

修改数据表结构

很多时候我们需要修改数据表字段结构,比如添加字段、修改字段类型和字段名,但是SQL
server默认情况下会阻止我们对数据表结构的修改。所以我们需要更改SQL Server的设置参数。

工具菜单à 选项à 打开”选项”弹窗中的”Designers”选项卡à取消”阻止保存要求重新创建表的更改”前面的选中状态。

3.索引

       
第一步:分别给两个表插入相当的数据1000行,然后删除第500条记录。

T-SQL基本语法

3.1.聚集索引

聚集索引数据按照索引的顺序排序,查询速度比非聚集索引快。当插入数据时,按索引顺序对数据重新排序。打个比方,新华字典中按拼音查字就是聚集索引,找到了矮字就能按顺序查下去找到爱字。一个表只能有1个聚集索引
如果一个表在创建主键时没有聚集索引也没指定唯一非聚集索引,会对PRIMARY
KEY字段自动创建聚集索引

delete course
where classID=500
delete course2
where classID=500

select语句

语法:

SELECT 字段列表 FROM 表名

3.2.非聚集索引

非聚集索引不按照索引顺序排序,制定了表中数据的逻辑顺序,采用指针指向数据页的形式。一个表可以拥有多个非聚集索引。打个比方,新华字典中按笔画查字就是非聚集索引,笔画索引顺序和字的顺序不一致,依靠指针来指向数据页。

       执行计划图如下:我们可以看到在执行删除时,数据库分为三部分:

where子句

where运算符

=,>,<,>=,<=,<>,!=,!>,!<

<>表示不等于,!>不大于。

AND 、OR、NOT

 

BETWEEN

select * from student 

where age BETWEEN 13 AND 19

 

查询指定的数据值是否在第一个值和第二个值的范围内。

LIKE

select * from student 

where name LIKE ‘%小%’

 

 

模糊查询,可以使用通配符,

%用来表示任意个任意字符,

_ 下划线用来表示一个字符。

 

select * from student 

where name LIKE ‘_白’

 

 

 

IN

是指从一个集合中去逐一匹配,只要数据值在集合中能找到相同的项,where条件就成立了。

 

select * from student 

where name IN (‘小张’,’小黑’,’小平’,’小李’)

 

———————————-

select * from student 

where name IN (select name from student where age <20)

 

 

 

 

 

EXISTS

用来判断一个子查询是否有结果,当子查询返回了至少一个结果时,where条件成立。

 

select * from student 

where exists(select * from student where age =99)

 

 

 

3.3.创建索引

示例3:设置IndexDemo1表的id字段为PRIMARY
KEY,看系统是否自动为该字段创建了聚集索引。执行下列语句

CREATE DATABASE IndexDemo
USE IndexDemo
CREATE TABLE IndexDemo1(
id INT NOT NULL,
A CHAR(10),
B VARCHAR(10),
CONSTRAINT PK_id PRIMARY KEY(id)
)

结果如图所示
图片 12
聚集索引以PRIMARY KEY的键名为索引名。
执行下列语句删除PRIMARY KEY

ALTER TABLE IndexDemo1
DROP CONSTRAINT PK_id

聚集索引PK_id也同时被删除了。
示例4:在示例3的IndexDemo1表中,插入几行数据,添加聚集索引,观察数据顺序,添加非聚集索引,观察数据顺序
IndexDemo1的数据如图所示(未添加索引)
图片 13
执行下列语句,为id列添加聚集索引

CREATE CLUSTERED INDEX clustered_index ON IndexDemo1(id)

添加聚集索引clustered_index后IndexDemo1表的数据如图所示
图片 14
可以发现,表中数据按照id列从小到大进行排序。
此时在表中插入一条数据

INSERT INTO IndexDemo1(id,A,B)VALUES('7','g','f')

表中数据排序如图所示
图片 15
执行下列代码删除聚集索引clustered_index并对id列创建非聚集索引nonclustered_index

DROP INDEX IndexDemo1.clustered_index
GO--删除聚集索引clustered_index
CREATE NONCLUSTERED INDEX nonclustered_index ON IndexDemo1(id)
GO--创建非聚集索引nonclustered_index

表中的数据如图所示
图片 16
此时添加一条记录

INSERT INTO IndexDemo1(id,A,B)VALUES('8','g','f')

表中的数据如图所示
图片 17
在未创建聚集索引,创建了非聚集索引的表中新插入的数据是添加在末行的。

         1:查找到要删除的数据行;

group by子句

将指定字段中的相同的值进行分组。值相同的只显示一行。

示例1:

SELECT age,COUNT(name) from student group by age

示例2:

在sql server 中所显示的字段列表中,不能使用group by后面没有出现过的字段名,除非使用聚合函数。

SELECT age,address,COUNT(name) from student group by age,address

3.4.修改索引

当数据更改时,有必要重新生成索引,重新组织索引或者禁止索引。

  • 重新生成索引表示删除索引,并且重新创建索引。这样可以根据指定的填充度压缩页来删除碎片,回收磁盘空间,重新排序索引。
  • 重新组织索引对索引碎片的整理程度低于重新生成索引。
  • 禁止索引表示禁止用户访问索引。

示例5:对IndexDemo1表中的id列重新生成索引,重新组织索引和禁止索引。
执行下列语句

ALTER INDEX nonclustered_index ON IndexDemo1 REBUILD
--重新生成索引
ALTER INDEX nonclustered_index ON IndexDemo1 REORGANIZE
--重新组织索引
ALTER INDEX nonclustered_index ON IndexDemo1 DISABLE
--禁用索引

注:禁用索引后重新启用索引,只需重新生成索引就可以了。

         2:包含一个top操作。

order by子句

比如倒序排序

SELECT * from studentorder by id DESC

3.5.查看索引

可以利用目录视图和系统函数查看索引。这样的函数有很多,不一一列举了。
图片 18

         3:执行聚集索引删除。

top子句

3.6.查看索引碎片

右键索引名,在属性——碎片中查看碎片
图片 19

图片 20

Having子句

用来给分组设置条件

示例:

SELECT age,name from student group by age,name having name = '小李'

3.7.查看统计信息

在表下的统计信息中,右键点击要查看统计信息的索引名,点击详细信息
图片 21

         
区别一:由于course表的classID上创建了索引,所以查找时按PK_classID来查找,course2表的classID由于没有任何的索引,为了查找到要删除的数据行,就只能按聚集索引查找,此时实际上是全表扫描。

DISTINCT子句

清除并返回结果中重复的值。

SELECT DISTINCT age from student

         
区别二:系统开销不同,让人意外的是,结果表明好像白天的观点是正确的,创建了索引的coure表在开销上比没有创建索引的course2还大一点。

insert into插入数据

         
分析区别二的原因:
我们来看下聚集索引删除的具体内容,下面是在条件列classID上创建了非聚集索引的表course表在发生删除时的执行计划图,它在删除后需要维护索引PK_classID,占用部分的系统开销。而没有创建索引的表course2由于没有索引维护的额外开销,所以反而占优势。

一次插入一行数据

insert into student (name,age,sex,address,phone) values('小宝',13,1,'城革大本营',12345678)

 

一次插入多行数据

insert into student (name,age,sex,address,phone) values
('大宝',28,1,'城革大本营',12345678),
('小宝',13,1,'城革大本营',12345678),
('老宝',82,1,'城革大本营',12345678);

图片 22     

省略字段名按表的字段顺序来插入数据

insert into student values('小白楼',60,1,'沙坪坝',12345678)

注意:这种方式必须按照表的字段顺序(除了主键ID)来排列语句中的字段值,并且所有字段都必须填写值

     
第二步:分别给两个表插入相当的数据10000行,然后删除第5000条记录。

聚合函数

AVG() 求平均值

SUM() 求合

MIN()/MAX() 求最大最小值

COUNT() 统计行数

         区别同第一步。难道我的观点真的正确?

UPDATE语句

update dbo.student set name='小白龙' where id = 14

     
第三步:分别给两个表插入相当的数据100000行,然后删除第50000条记录。执行计划图如下:

DELETE语句

delete dbo.student where id=14

         区别一:同前两步的区别一。

练习

创建一张学生数据表,包含字段id、name、age、sex、address、phone、classNum

1、 一次性插入5条学生数据,并且不写字段名。

2、 用select语句查询ID为2到ID为4之间的记录,(用BETWEEN关键字)。

3、 查询出所有姓王的同学(用LIKE模糊查询)。

4、 查询出班上年龄为(16、17、23、24)的同学

5、 统计各班分别有多少名学生

6、 分别统计男生与女生的年龄总合。

7、 找到年龄最大的女生。

8、 修改id为3的学生姓名为”李小虫”

9、 删除id为3的学生。

连接查询

同时查询多张数据表并将这些数据表以一定的逻辑关系进行连接,让它们显示的结果类似于一张数据表。

与连接有关的关键字:

INNER JOIN 、OUTER JOIN ( LEFT和RIGHT)、FULL JOIN、CROSS JOIN

 

        
区别二:系统开销不同,此时会发现创建了索引的course表在开销上占5%,而没有创建索引的course2表占了95%,这可是10倍的区别啊。

内部连接

它根据一个或几个相同的字段将记录匹配在一起,将这两张表中的数据一起查询出来。

内部连接的特点是,只显示有关联的数据,但是没有关系的数据是不会被显示出来的。

图片 23    

语法:

SELECT <字段列表> FROM <第一张表> <连接类型> <第二张表> <ON 连接条件>

     
第四步:分别给两个表插入相当的数据1000000行,然后删除第500000条记录。

二表连接,示例:

select * from student INNER JOIN class ON student.cid = class.id

            区别同第三步。

多表连接,示例:

select student.name,classInfo.className,teacher.name from student
INNER JOIN 
classInfo  ON student.cid = classInfo.cid
INNER JOIN 
teacher ON classInfo.teacher= teacher.tid

      总结:当删除语句的条件列没有创建索引时分两种情况:

多表连接的使用别名,省略as

select s.name,c.className,t.name from student as s
INNER JOIN 
classInfo as c  ON s.cid = c.cid
INNER JOIN 
teacher as t ON c.teacher= t.tid

我们可以通过as关键字来给数据表定义一个别名,而且通过这个别名调用表中的字段。

注意:只要定义了别名,就必须使用别名,原表的名字就不能再用了。

而且as关键字是可以省略的:

select s.name,c.className,t.name from student  s
INNER JOIN classInfo  c  ON s.cid = c.cid
INNER JOIN teacher  t ON c.teacher= t.tid

补充:内部连接的INNER
JOIN可以简化为JOIN ,效果是一样的。

           
第一:数据量较小,我测试时在10000以下,此时两者的差别不大,反而会因为创建了索引而引起磁盘开销。开销差距不大是因为数据量小时,即使全表扫描速度也不慢,此时索引的优势并不明显。

外部连接

内部连接有一定的排他性,第二张表是对第一张表的补充,如果第一张表不需要第二张表中的某些数据,那么第二张表中不被需要的数据就不会被显示出来。

           
第二:数据量较大,我测试时在100000以上,此时两者的差别较大。条件列创建了索引的表明显效率高。

语法:

SELECT <字段列表> FROM <左表><LEFT | RIGHT > [OUTER] JOIN <右表> ON <连接条件>

如果使用LEFT就是显示左表中的所有数据,如果使用Right就是显示右表中的所有数据

           
第三:归根结底,系统的主要开销还是在删除的第一步,查找数据行上。能更快查找到删除行的方案效率最高。

示例:

select *from student as s RIGHT JOIN Class Info as c  ON s.cid = c.cid

多部外部连接示例:

select * from student  s RIGHT JOIN classInfo  c  ON s.cid = c.cid
LEFT JOINteacher t ON c.teacher=t.tid

完全连接

完全连接( FULL JOIN 或 FULL OUTER JOIN )

用于显示所连接的所有表的所有数据,即使这条数据没有任何关联关系。

select *from student  s FULL JOIN classInfo  c  ON s.cid = c.cid

练习:

1、 先重做上课时讲的例子。

2a、
假设现在制作一个超市购物系统,产品信息表(product)(id、name、price)、用户表(customer)(id、name)、购物清单表(saleList)(id、产品编号pid、用户编号cid)

2b、 用一条select语句查询某个用户的购清单上的所有产品。

2c、 用一条select语句查询得到某个用户的购清单上的所有产品的总价。

 

3a、假设现在制作一个电影院的数据查询系统,坐位表(site)(id、row、col)、客户表(customer)(id、name、phoneNum)、电影票(ticket)(id、cid、sid、mid)、电影表(movie)(id、name、mtime)

3b、查询某一场电影的所有坐位上的客户的信息。

3c、查询某一场电影的所有坐位上的客户的信息,并且显示空坐位。

(如何判断一个字段的值为NULL值:

select * from movie where name is null)
select s.id,c.name from dbo.ticket t
join dbo.customer c on t.cid=c.id
join dbo.movie m on t.mid=m.id
right join dbo.site s on t.sid = s.id
where m.id=1
union
select id,'无座' as name  from site where id not in
(select site.id from ticket
join dbo.customer on ticket.cid=customer.id
join dbo.movie on ticket.mid=movie.id
right join dbo.site on ticket.sid = site.id
where movie.id=1)

3d、查询某一个客户看过的所有电影的名称。

子查询

它是指一个select查询语句,并不是直接从数据表中来得到数据,而是从另外一个查询语句的结果集中来进行查询。

示例:

select s.name,s.age,s.sex from (

select * from student where sex = 0

) as s

where age >20

其中,在from关键字的后面,并不是数据表而是select语句。

交叉连接

交叉连接在本质上,也可以看做是一种内连接。只显示有一关联的数据。

示例

–内连接写法

select * from classInfo

inner join teacher

on classInfo.teacher=teacher.tid

 

–交叉连接写法

select * from classInfo,teacher

where classInfo.teacher=teacher.tid

 

 

两者的结果是一样的

 

联合UNION

使用两个或两个以上查询合并后只返回一个结果集

比如:

得到班上年龄大于20和所有男生的合集

select * from student where age>20

union

select * from student where sex = 1

 

 

前提每条select语句返回的字段列表的个数和顺序必须是一致的。

 

联合后返回重复的数据

union联合后的结果自动去除掉多个select结果中的重复数据,如果需要重复显示这些重复数据,我们可以使用union all关键字:

select * from student where age>20

union all

select * from student where sex = 1

 

创建与修改数据库、表

 

SQL Server中的对象名

多数情况下我们使用的是数据表或数据库的简写形式,实际上SQL
server中的数据表有4层命名约定。

[数据服务器名.[数据库名.[模式名.]]] 对象名

.test.dbo.student

数据库服务器名:默认是指当前已登陆的这个数据服务器。

数据库名:默认是指在客户端左上角的下拉列表中已选择的数据库名,或用use
指令指定数据库。

use test select * from student where sex = 1

模式名

SQL server对象可以拥有两种模式名。

第一种模式:该对象拥有的权限的用户。

第二种模式:默认dbo,允许多个登陆用户共享的一种访问模式。

模式所代表的就是访问权限,通常我们使用默认的dbo模式。

CREATE语句

它用来创建数据库对象

语法:

CREATE <对象类型> <对象名称>

CREATE DATABASE news

CREATE TABLE newContext( id int )

CREATE DATABASE创建数据库

新创建的数据库,除了创建者、系统管理员、数据库所有者以外,其他人都无法访问。

CREATE DATABASE 的完整语法

CREATE DATABASE
[
[ON | PRIMARY ]
(
[NAME = ‘实例名’ ,]
[FILENAME = ‘文件名’ ,]
[SIZE = 文件大小 ,]
[MAXSIZE = 文件最大容量]
)
]
[
[ON | PRIMARY ]
(
[NAME = ‘实例名’ ,]
[FILENAME = ‘文件名’ ,]
[SIZE = 文件大小 ,]
[MAXSIZE = 文件最大容量]
)
]
 [COLLATE <核对名称>]
[FOR ATTACH [ WITH <server broker> ||FROM ATTACH_REBUILD_LOG ||WITH DB_CHAINING ON|OFF|TRUSTWORTHY ON|OFF  ]]
[AS SNAPSHOT OF<源数据库名>]
ON

 

用在两个地方:一是定义数据库文件的位置。二是定义数据日志库文件的位置。

PRIMARY 关键字用于指定多个数据库文件中的主文件。

NAME 指定文件的实例名称。也就是在数据库的逻辑名(非物理文件名)

FILENAME 就是指数据文件的物理位置和文件名,mdf(数据库)  ldf(日志文件)

SIZE 数据库大小,可以在数字后面用KB或GB表示数据库的大小。

MAXSIZE 最大小容量。

 

COLLATE

用于处理排序和字母大小写等问题

 

FOR ATTACH

将已存在的一些数据库文件附加到当前服务器上。当前,这个文件必须是数据库的一部分。

 

WITH DB_CHAINING

跨越数据库所有权

 

TRUSTWORTHY

为sql server数据库文件添加安全层

创建数据库示例:

CREATE DATABASE TESE22BB
ON
(
NAME =TEST22BB,
FILENAME = 'e:\test22bb.mdf',
SIZE =30MB,
MAXSIZE = 50MB
)
LOG ON
(
NAME = 'TEST22BBLOG',
FILENAME='e:\test22bb.ldf',
SIZE = 10MB,
MAXSIZE = 20MB
)
GO

 

用这种方式,我们可以在指定的硬盘或U盘路径之下创建数据库。

 

注意:如果需要对数据库文件进行复制、剪切或删除操作。

 

查看数据库信息

EXEC sp_helpdb ‘test’

以类似查询语句的结果集的方式返回数据库的大小、拥有者、创建日期、文件路径等信息。

 

CREATE TABLE创建数据表

CREATE TABLE 数据表名

创建表之前确定是否已经选择当前数据库

 

完整语法

CREATE TABLE [数据库.[数据库所有者]] 数据表名
(
<字段名><字段的数据类型>
[DEFAULT <默认值表达式>]
|
[IDENTITY [seed,increment][NOT FOR REPLICATION] ]
[ROWGUIDCOL]
[COLLATE<COLLATION NAME>]
[PRIMARY KEY]
[NULL | NOT NULL]
[<column constraint 字段约束>]
|
[table_constraint 表约束]
|
[字段名 as 计算列表达式]
)
[ON (<文件组>)|DEFAULT]
[TEXTIMAGE_ON(<文件组>)|DEFAULT]

 

DEFAULT 默认值

指该字段在没有输入值的情况下默认使用的值。

IDENTITY标识、自增量

默认情况下,每条记录自动增加1

NOT FOR REPLICATION

就是指对这个表进行复制的时候,ID主键的值是重新排列,还是延用之前的ID

ROWGUIDCOL

是指将一个表中的数据复制到另一个表中时,如果产生ID重复情况下,应用如何处理。

COLLATE

用于处理排序和字母大小写等问题。

PRIMARY KEY

设置该字段为主键

NULL/NOT NULL

是否允许为空

字段约束

对字段中输入的数据进行规则的限制。

计算列

可以创建一个本身没有任何数据的列,这个列的值由其他列来动态的生成。

比如:

PCount AS price*num

这里我们就定义了一个计算列,总价=单价*数量

 

注意:

1、不能计算主键、外键、唯一键

2、只能引用当前数据表中的字段

 

表约束

对插入表的数据进行限制

ON

如果数据库由多个部分组成,我们可以指定数据表存储在哪个部分。

TEXTIMAGE_ON

与ON的作用类似,但是它只有在表中有Text或Image类型的字段时才有效。

创建数据表的示例:

use testStudent2;

CREATE TABLE student(

sid int IDENTITY PRIMARY KEY NOT NULL,

sName nvarchar(50) NOT NULL,

sAge int,

sSex bit  DEFAULT 0 NOT NULL,

sYW float DEFAULT 0 NOT NULL,

sSX float DEFAULT 0 NOT NULL,

sCount AS sYW+sSX

)

 

练习:

创建一个产品销售表,字段如下:pid、pname(产品名称)、pPrice(产品价格)、pNum(产品销售数量)、pCount(产品销售总价= pPrice* pNum),用CREATE语句创建这个数据表。

 

ALTER修改语句

ALTER <数据对象类型><数据对象名称>

ALTER DATABASE 修改数据库

修改数据库名

ALTER DATABASE test MODIFY NAME = test22

将数据库test改名为test22

修改数据库大小

ALTER DATABASE test MODIFY FILE (SIZE = 500MB)

注意:不能变小,只能增大它的容量。

ALTER TABLE 修改数据表

最常见的操作就是修改数据表名和表中的字段。

 

添加字段

ALTER TABLE dbo.student

ADD --这个关键字代表添加

phoneNum char(20) DEFAULT '00000000',

sAddress nvarchar(100) ,

createTime DateTime DEFAULT GETDATE()

--GETDATE()代表获取系统当前时间

修改字段名

EXEC sp_rename ‘表名.原字段名’ , ’新字段名’ , ’COLUMN’

示例:

EXEC sp_rename 'student.createTime','regTime','COLUMN'

修改字段类型

ALTER TABLE 表名 ALTER COLUMN 字段名 类型

示例:

ALTER TABLE dbo.student

ALTER COLUMN sAge nvarchar(30)

删除字段

ALTER TABLE 表名 DROP COLUMN 字段名

示例:

ALTER TABLE dbo.student

DROP COLUMN sAddress

字段的值会被一起删除

修改表名

EXEC sp_rename ‘原表名’,’新表名’

示例:

EXEC sp_rename 'student','studentInfo'

DROP语句

删除数据库对象,比如:删除数据表、视图、存储过程、触发器

语法:

DROP <数据对象> <数据对象名>

DROP语句可以同时删除多张数据表

DROP TABLE 表1,表2,….

示例:

drop table table1,table2,table3

DROP删除数据库

DROP DATABASE 数据库名

练习:

用户CREATE 语句创建一个电影院相关的数据库,其中包含数据表(site)(id、row int、col int)、客户表(customer)(id int,name
nvarchar(50)、phoneNum char(20))、电影表(movie)(id int 、name nvarchar(50)、mtime dateTime)

 

其中,用户电话的默认值是12345678

电影的默认时间是当前系统时间

每个表的id都必须是自增的主键

修改site数据表名为userSite

修改customer中的字段phoneNum的类型为char(50)

 

数据库相关的内容

系统数据库

Author

发表评论

电子邮件地址不会被公开。 必填项已用*标注