SQL详解
———————————基本概念————————————————————
数据库分为两类:实例数据库和系统数据库,我们只能创建实例数据库,
系统数据库有4个:
master(记录SQL Server系统的所有系统信息),
Model数据库(创建所有用户数据库和tempdb数据库的模板文件),
msdb数据库(存储计划信息以及与备份、还原相关的信息),
tempdb数据库(作为临时存储空间来使用的)。
SQL Server采用"提前写"方式的事务,即对数据库中的数据进行修改时,
先写入事务日志,再更新数据。
———————————数据库的操作——————————————————
1.如何创建数据库
create database mydatabase1
on(
name=mydatabase_data,
filename='e:\mydata\mydatabase_data.mdf',
size=4,
maxsize=10,
filegrowth=10%
)
log on
(
name=mydatabase_log,
filename='e:\mydata\mydatabase_log.ldf',
size=1,
maxsize=6,
filegrowth=1
)
on()是数据文件的描述 log on()事务日志文件的描述
name 逻辑文件名
filename 数据库文件要保持的路径及文件名
size 初始数据库文件的大小
maxsize 数据库文件的最大值
filegrowth 自动增长率 可以是百分比 也可以是具体值
2.如何打开数据库
use T_Employee
3.如何修改数据库的名称 不区分大小写的
范:
EXEC SP_RENAMEDB 'oldname' , 'newname'
例:
exec sp_renamedb 'T_Employee','employee'
exec sp_renamedb 'employee','T_Employee'
alter database T_Employee modify name="employee"
4.如何删除数据库
use master
drop database employee --删除数据库
————————————表的操作———————————————————
5.如何创建一个表
create table Studen(
id int primary key,
stuname nvarchar(20) not null,
gender nvarchar(10) not null default '男',
age int not null check(age>20)
)
6.如何修改表的名称
范:
exec sp_rename 'old_table_name','new_table_name'
例:exec sp_rename 'Student','s1'
7.如何清空表里的内容
范:
truncate table tablename
例:truncate table Student
8.如何删除一个表
如何删除一张表
drop table Studen
———————————列的操作————————————————————
9.如何在表中添加字段
范:
alter table [table_name] add [new_column] varchar(33)
例:
alter table Student add nation nvarchar(20)
10.如何修改表中字段的名称
范:
exec sp_rename 'table_name.old_name' , 'new_name' , 'column'
例:exec sp_rename 'Student.nation' , 'Nation' , 'column'
11.如何删除表中的字段
范:
alter table [table_name] drop column [column_name]
例:
alter table Student drop column nation
—————————————数据库的备份与还原—————————————
12.如何进行数据库的备份操作
use employee --打开数据库
exec sp_addumpdevice 'disk','testdevice2','d:\123\employee.bak' --(创建逻辑磁盘)
backup database employee to testdevice2
13.数据库的还原操作
use master
restore database employee from disk='D:\123\employee.bak' with replace --数据库的还原
14.如何对数据库进行优化
(1)Sql的注入攻击
(2)采用参数化Sql代替字符串的拼接
(3)调优的基本原理 "二八原理"即 20%的代码资源消耗占用80%的总资源消耗,把精力放在20%最消耗系统资源的Sql语句中,不要想把所有的都进行调优
(4)索引是一切优化手段的"内功",所有的优化手法都是索引衍化出来的招式而已。
一个表只能有一个聚集索引,表中的物理顺序只能有一个。非聚集索引可以创建多个。
★★★一张表中经常检索的字段创建索引。索引采用的是B树结构存储的,过一段时间的增删该操作后,会出现“存储碎片”的问题,所以要重建索引。
(5)系统访问数据库中的数据,一般为全表扫描和索引查找。
(6)优化手法:
1)在经常要进行检索的字段上创建必要的索引
2)尽量使用参数化Sql,这样不仅可以避免注入攻击,还可以执行预编译
3)调整where子句中的连接顺序,表连接最好写在其他条件之前。
4)避免使用*,因为在解析的过程中,会把*转成所有的列名,这意味着将耗费更多的时间。
5)尽量写成一条Sql语句,不要分开来写。
6)尽量避免使用Having子句,能用where尽量用where
7)尽量使用表的别名,在多个表中
8)用Existe替代In在子查询中。
9)尽量使用表连接而不是Existe
10)避免在索引列上进行计算
11)避免进行隐式转换,否则的话会进行全表扫描
12)防止检索范围过宽,如果检索范围过宽,放弃索引,会进行全表扫描
①使用 IS NOT NULL 或者不等于判断,会造成优化器假设匹配的记录太多
②使用like运算符的时候, "a%"将会使用索引,而"a%c","%c"则会使用全表扫描。
15.如何对数据库进行定时备份
manger studion连接到你的实例, 打开"对象资源管理器", 没有看到的话, 按F8
展开实例--管理--维护计划
右键"维护计划"--新建维护计划--输入维护计划名称--这样会出现创建维护计划的窗口然后, 在左边的工具箱中(没有的话, 按Ctrl+Alt+X), 将"备份数据库任务"拖到中间的黄色区域
双击拖出来的这个任务, 会出现设计属性的窗口
在数据库中, 选择你要备份的数据库,然后设置备份到那里, 并且设置备份的文件名
设计好后, 单击"确定", 你也可以单击"T-SQL", 看看sql大致会用什么样的语句来处理你的需求.
单击"计划"后面的"..."按钮, 设置执行的时间计划.
最后保存就可以了.
把sql agent服务设置为自动启动
16.建立独立的数据库登录用户
(1)对sa用户进行启用和密码设置 ★★★★★强烈建议不这样进行使用
①在总的节点下——右键属性——安全性——服务器身份验证选中SQL server和Windows身份验证模式——修改完成后重新启动下。
②总的安全性——登录名——sa——右键属性——状态——登录启用
③常规——SQL身份验证——密码——下面两个强制都勾选。
(2)开启独立的身份进行数据的登录
①在总的节点下——右键属性——安全性——服务器身份验证选中SQL server和Windows身份验证模式——修改完成后重新启动下
②在总的安全性下建立一个登录名,和密码
③在进行设置的数据库下建立一个 在安全性那建立的登录名和密码
———————————————存储过程与事物与触发器—————————
17.存储过程(Stored Procedure)是一组为了完成特定功能的T-SQL语句集合,经编译后存储在服务器端数据库中,利用存储过程可以急速SQL语句的执行。
存储过程分为系统存储过程和自定义存储过程,优点具体如下:
1.提高应用程序的通用性和可移植性(可以被应用程序多次进行使用,进行修改时只要修改存储过程而不需要修改应用程序源代码)
2.可以更有效的管理用户操作数据库的权限(避免非授权用户对数据库的访问,保证数据的安全)
3.可以提高T-SQL的速度(存储过程是被编译过的,比单条SQL语句执行的速度要快的多)
4.减轻服务器的负担,为了减轻网络上大量的sql语句。
如何创建存储过程
(1)学生查询表的存储过程
create proc 学生_proc as select * from 学生
exec 学生_proc
(2).分开执行下面两段代码,一起执行的话出错
create proc sex_proc
@sex nvarchar(4)
as
select * from 学生
where 性别=@sex
exec sex_proc
@sex='男'
(3).修改的存储过程
Alter proc 更新高考分数_proc
@高考分数 int ,
@性别 nvarchar(2)
as
update 学生 set 高考分数=高考分数+@高考分数
where 性别=@性别
exec 更新高考分数_proc
@高考分数=1000,
@性别='女'
select * from 学生 where 性别='女'
(4).扩展存储过程
exec master..xp_cmdshell 'dir D:\'
exec master..xp_enumgroups
exec master..xp_getnetname
18.如何理解事务
将SQL语句组成一个事务,其目的是保证这一组SQL语句能给得到可靠的执行,保证语句要么全部成功运行,要么都不运行。
事务的4个属性:
(1)原子性:要么全部运行,否则的话回到初始状态
(2)一致性:如果事务执行成功,所有数据都处于一个新的状态;如果失败,所有数据仍然处于事务开始前的状态。
(3)隔离性:事务所做的修改必须与其他同时并发的事务所做的修改隔离。
(4)持久性:事务提交以后,对数据库做的修改就永远的保持下来了,即使发生系统故障也不例外。
事务中锁的应用
在SQL Server中,锁是防止事务访问指定资源,实现并发控制的主要手段,
锁可以防止脏读,不可重复读和幻像读。
锁定资源的方式共有两种:一种是读操作要求的共享锁,另一种是写操作要
求的排它锁,两种锁不能共存。
共享锁:允许并发事务读取同一种资源,事务不能修改访问的资源数据,一
般使用Select语句访问资源,系统自动对所访问的资源使用共享锁。
排它锁:修改数据的事务,例如使用insert、delete、update语句,系统会
自动在所修改的事务上放置排它锁。
begin transaction
select * from 仓库 with(holdlock)
waitfor delay '00:00:08'
commit transaction
begin transaction
update 仓库 set 面积=855 where 仓库号='wh2'
select * from 仓库
rollback transaction
19.如何创建触发器
一、创建一个简单的触发器
触发器是一种特殊的存储过程,类似于事件函数,SQL Server? 允许为 INSERT、UPDATE、DELETE 创建触发器,即当在表中插入、更新、删除记录时,触发一个或一系列 T-SQL语句。
触发器可以在查询分析器里创建,也可以在表名上点右键->“所有任务”->“管理触发器”来创建,不过都是要写 T-SQL 语句的,只是在查询分析器里要先确定当前操作的数据库。
创建触发器用 CREATE TRIGGER
CREATE TRIGGER 触发器名称
ON 表名
FOR INSERT、UPDATE 或 DELETE
AS
T-SQL 语句
注意:触发器名称是不加引号的。
如下是联机丛书上的一个示例,当在 titles 表上更改记录时,发送邮件通知 MaryM。
CREATE TRIGGER reminder
ON titles
FOR INSERT, UPDATE, DELETE
AS
EXEC master..xp_sendmail 'MaryM',
'Don''t forget to print a report for the distributors.'
二、删除触发器
用查询分析器删除
在查询分析器中使用 drop trigger 触发器名称 来删除触发器。
也可以同时删除多个触发器:drop trigger 触发器名称,触发器名称...
注意:触发器名称是不加引号的。在删除触发器之前可以先看一下触发器是否存在:
if Exists(select name from sysobjects where name=触发器名称 and xtype='TR')
用企业管理器删除
在企业管理器中,在表上点右键->“所有任务”->“管理触发器”,选中所要删除的触发器,然后点击“删除”。
三、重命名触发器
用查询分析器重命名
exec sp_rename 原名称, 新名称
sp_rename 是 SQL Server? 自带的一个存储过程,用于更改当前数据库中用户创建的对象的名称,如表名、列表、索引名等。
用企业管理器重命名
在表上点右键->“所有任务”->“管理触发器”,选中所要重命名的触发器,修改触发器语句中的触发器名称,点击“确定”。
四、more....
INSTEAD OF
执行触发器语句,但不执行触发触发器的 SQL 语句,比如试图删除一条记录时,将执行触发器指定的语句,此时不再执行 delete 语句。例:
create trigger f
on tbl
instead of delete
as
insert into Logs...
IF UPDATE(列名)
检查是否更新了某一列,用于 insert 或 update,不能用于 delete。例:
create trigger f
on tbl
for update
as
if update(status) or update(title)
sql_statement --更新了 status 或 title 列
inserted、deleted
这是两个虚拟表,inserted 保存的是 insert 或 update 之后所影响的记录形成的表,deleted 保存的是 delete 或 update 之前所影响的记录形成的表。例:
create trigger tbl_delete
on tbl
for delete
as
declare @title varchar(200)
select @title=title from deleted
insert into Logs(logContent) values('删除了 title 为:' + title + '的记录')
说明:如果向 inserted 或 deleted 虚拟表中取字段类型为 text、image 的字段值时,所取得的值将会是 null。
五、查看数据库中所有的触发器
在查询分析器中运行:
use 数据库名
go
select * from sysobjects where xtype='TR'
sysobjects 保存着数据库的对象,其中 xtype 为 TR 的记录即为触发器对象。在 name 一列,我们可以看到触发器名称。
六、sp_helptext 查看触发器内容
用查询分析器查看
use 数据库名
go
exec sp_helptext '触发器名称'
将会以表的样式显示触发器内容。
除了触发器外,sp_helptext 还可以显示 规则、默认值、未加密的存储过程、用户定义函数、视图的文本
用企业管理器查看
在表上点右键->“所有任务”->“管理触发器”,选择所要查看的触发器存储过程
七、sp_helptrigger 用于查看触发器的属性
sp_helptrigger 有两个参数:第一个参数为表名;第二个为触发器类型,为 char(6) 类型,可以是 INSERT、UPDATE、DELETE,如果省略则显示指定表中所有类型触发器的属性。
例:
use 数据库名
go
exec sp_helptrigger tbl
八、递归、嵌套触发器
递归分两种,间接递归和直接递归。我们举例解释如下,假如有表1、表2名称分别为 T1、T2,在 T1、T2 上分别有触发器 G1、G2。
间接递归:对 T1 操作从而触发 G1,G1 对 T2 操作从而触发 G2,G2 对 T1 操作从而再次触发 G1...
直接递归:对 T1 操作从而触发 G1,G1 对 T1 操作从而再次触发 G1...
嵌套触发器
类似于间接递归,间接递归必然要形成一个环,而嵌套触发器不一定要形成一个环,它可以 T1->T2->T3...这样一直触发下去,最多允许嵌套 32 层。
设置直接递归
默认情况下是禁止直接递归的,要设置为允许有两种方法:
T-SQL:exec sp_dboption 'dbName', 'recursive triggers', true
EM:数据库上点右键->属性->选项。
设置间接递归、嵌套
默认情况下是允许间接递归、嵌套的,要设置为禁止有两种方法:
T-SQL:exec sp_configure 'nested triggers', 0 --第二个参数为 1 则为允许
EM:注册上点右键->属性->服务器设置。
九、触发器回滚
我们看到许多注册系统在注册后都不能更改用户名,但这多半是由应用程序决定的, 如果直接打开数据库表进行更改,同样可以更改其用户名,在触发器中利用回滚就可以巧妙地实现无法更改用户名。
use 数据库名
go
create trigger tr
on 表名
for update
as
if update(userName)
rollback tran
关键在最后两句,其解释为:如果更新了 userName 列,就回滚事务。
十、禁用、启用触发器
禁用:alter table 表名 disable trigger 触发器名称
启用:alter table 表名 enable trigger 触发器名称
如果有多个触发器,则各个触发器名称之间用英文逗号隔开。
如果把“触发器名称”换成“ALL”,则表示禁用或启用该表的全部触发器。
例:
create trigger 触发器名 on 表名 for INSERT[,UPDATE,DELETE]
as
begin
命令行或程序块
end
当仓库表执行更新操作时,触发器被执行
create trigger db_trigger1 on 仓库 for update
as
begin
create table trigger(
userid int identity(1,1) primary key,
username varchar(50),
userpwd varchar(50)
)
insert into trigger(username,userpwd) values('name','111')
end
删除触发器
drop trigger db_trigger1
————————————————系统视图的操做————————————
20.如何查找出指定数据库中的所有表
select * from INFORMATION_SCHEMA.TABLES
21.如何查找指定表中的所有列
select * from INFORMATION_SCHEMA.COLUMNS where table_name='s1'
22.检索出所有的表
select Name from sysobjects
————————————————相关函数的操作————————————
23.如何对日期函数进行操作
(1)当前的日期时间 2008-01-12 01:13:19
NOW()函数用于取得当前的日期时间
CURRENT_TIMESTAMP GETDATE()
(2)不包括时间部分的当前日期 2008-01-12 (Sql2005没有此函数)
可以使用 CURDATE()函数,CURDATE()函数还有 CURRENT_DATE 等别名
(3)不包括日期部分的当前时间 01:17:09 (Sql2005没有此函数)
可以使用 CURTIME()函数,CURTIME ()函数还有 CURRENT_TIME 等别名
(4)DATEADD (datepart , number, date )
其中参数date为待计算的日期;参数date制定了用于与 datepart 相加的值,如果指定了 非整数值,则将舍弃该值的小数部分;参数datepart指定要返回新值的日期的组成部分
(5)DATEDIFF ( datepart , startdate , enddate )
其中参数datepart为计算差额时使用的单位
(6)DATENAME()函数,这个函数可以返回一个日期的特定部分,并且尽量用名称来表述这个特定部分
DATENAME(datepart,date)其中参数date为待计算日期,date 参数也可以是日期格式的字符串;参数datepart指定要返回的日期部分的参数
(7)Microsoft SQL Server 2005 可识别的日期部分及其缩写
year yy,yyyy 年份
quarter qq,q 季度
month mm,m 月份
dayofyear dy,y 当年度的第几天
day dd,d 日
week wk,ww 当年度的第几周
weekday dw,w 星期几
hour hh 小时
minute mi,n 分
second ss,s 秒
millisecond ms 毫秒
24.用户定义数据类型
(1)如何进行添加
use database_name
1)EXEC sp_addtype 'kechenghao','varchar(6)','not null'
2)create default nation as '汉族'
用户定义数据类型——新建用户定义数据类型
3)★★默认值 可以输入 default 进行insert…
(2)删除用户定义数据类型
use database_name
EXEC sp_droptype 'kechenghao'
25.通配字符的使用
select * from T_Employee where FName like '_%o%_'
--匹配a-d中的某一个字符
select * from T_Employee where FName like '[a-d]'
--非O开头的包含O的字符串
select * from T_Employee where FName like '[^o]%o%'
--非O开头和结尾,但中间包含O的字符串
select * from T_Employee where FName like '[^o]%o%[^o]'
select * from T_Employee where FName is not null
26.Select 的执行步骤
(1)
《SQL Server 2005技术内幕 T-SQL查询》中内容的执行数序:
(8)SELECT (9) DISTINCT (11)<TOP_specification> <select_list>
(1)FROM <left_table>
(3) <join_type> JOIN <right_table>
(2) ON <join_condition>
(4)WHERE <where_condition>
(5)GROUP BY <group_by_list>
(6)WITH {CUBE|ROLLUP}
(7)HAVING <having_condition>
(10)ORDER BY <order_by_list>
(2)sql语句的执行步骤:
1)语法分析,分析语句的语法是否符合规范,衡量语句中各表达式的意义。
2) 语义分析,检查语句中涉及的所有数据库对象是否存在,且用户有相应的权限。
3)视图转换,将涉及视图的查询语句转换为相应的对基表查询语句。
4)表达式转换, 将复杂的 SQL 表达式转换为较简单的等效连接表达式。
5)选择优化器,不同的优化器一般产生不同的“执行计划”
6)选择连接方式,有三种连接方式,对多表连接可选择适当的连接方式。
7)选择连接顺序, 对多表连接选择哪一对表先连接,选择这两表中哪个表做为源数据表。
8)选择数据的搜索路径,根据以上条件选择合适的数据搜索路径,如是选用全表搜索还是利用索引或是其他的方式。
9)运行"执行计划”
27.数字函数的使用
(1)ABS()函数用来返回一个数值的绝对值
(2)select Rand()随机数
select datepart(second,getdate()) 因为里面的秒数不一样,所以也不一样
SELECT RAND(9527) 生成一个固定的函数
(3)舍入到最大整数 CEILING()函数
3.33将被舍入为4、2.89将被舍入为3、-3.61将被舍入为-3。 Ceiling→天花板
(4)舍入到最小整数 FLOOR()
3.33将被舍入为3、2.89将被舍入为2、-3.61将被舍入为-4。 Floor→地板
(5)四舍五入 ROUND()函数将数值向最近的数值舍入
Round(3.1425,2) 数值 保留位数
(6)SIGN()函数用来返回一个数值的符号,如果数值大于 0 则返回 1,如果数值等于 0 则返回 0,如果数值小于 0 则返回-1
(7)求余求整 / %
(8)POWER(X,Y)函数用来计算 X 的 Y 次幂
select POWER(2,2)
28.字符串函数的使用
(1)计算字符串长度
LENGTH()函数用来计算一个字符串的长度。该函数接受一个参数,此参数为待计算的 字符串表达式,在 MYSQLServer 中这个函数名称为 LEN()
(2)字符串转换为小写
LOWER()函数用来将一个字符串转换为小写。该函数接受一个参数,此参数为待转换的字符串表达式
(3)字符串转换为大写
UPPER ()函数用来将一个字符串转换为大写。该函数接受一个参数,此参数为待转换的字符串表达式
(4)截去字符串左侧空格
LTRIM()函数用来将一个字符串左侧的空格去掉。该函数接受一个参数,此参数为待处理的字符串表达式
(5)截去字符串右侧空格
RTRIM ()函数用来将一个字符串左侧的空格去掉。该函数接受一个参数,此参数为待处理的字符串表达式
(6)截去字符串两侧的空格
TRIM ()函数用来将一个字符串两侧的空格去掉。该函数接受一个参数,此参数为待处理的字符串表达式
(7)计算子字符串在主字符串中位置的函数
计算子字符串在主字符串中位置的函数,这个函数可以检测制定的子字符串是否存在于主字符串中,如果存在则还可以返回所在的位置
INSTR(string,substring)
其中参数 string 为主字符串,参数 substring 为待查询的子字符串。如果 string 中存在 substring 子字符串,则返回子字符串第一个字符在主字符串中出现的位置;如果 string 中不 存在 substring 子字符串,则返回 0。
(8)从左侧开始取子字符串
LEFT (string,length)其中参数 string 为主字符串,length 为子字符串的最大长度。
(9)从右侧开始取子字符串
RIGHT (string,length)其中参数 string 为主字符串,length 为子字符串的最大长度。
(10)字符串替换
REPLACE()函数可以用来将字符串的指定的子字符串替换为其它的字符串
REPLACE(string,string_tobe_replace,string_to_replace)
其中参数 string 为要进行替换操作的主字符串,参数 string_tobe_replace 为要被替换的 字符串,而 string_to_replace 将替换 string_tobe_replace 中所有出现的地方。
(11)得到字符的 ASCII 码
ASCII()函数用来得到一个字符的 ASCII 码,它有且只有一个参数,这个参数为待求 ASCII 码的字符,如果参数为一个字符串则函数返回第一个字符的 ASCII 码
SELECT ASCII('a') , ASCII('abc') 97 97
(12)得到一个 ASCII 码数字对应的字符
SELECT CHAR(56) , CHAR(90) ,'a', CHAR( ASCII('a') )
29.一些常用的全局变量
(1)查询版本
select @@version
(2).查询语言
select @@language
(3).查询GUID的值
select newid()
★★int自增字段的优点:占用空间小,无需开发人员干预,易读
缺点:效率低,数据导入导出非常麻烦
★★Guid 效率高,数据导入导出简单
缺点:占用空间大,不易读
默认值可以设置为:newid()
(4)查看硬盘分区
EXEC master..XP_fixeddrives
(5)添加 SQL登录帐户
EXEC sp_addlogin 'zhangsan', '1234'
(6)当前时间
select GETDATE() --当前时间
——————————————————SQL语句的一些操作—————————
30.如何获得插入时自增字段的值
(1)在插入的时候用 output inserted.stuid
(2)在插入语句的后面带上 select @@identity
列句:
create database T_student
use T_student
create table Student
(
stuid int primary key,
stuname nvarchar(20),
stugender nvarchar(20)
)
insert into T_Student(stuname,stugender) output inserted.stuid
values('张三','女')
insert into T_Student(stuname,stugender) values('张三','女');select @@identity;
31.表连接与子查询
(1)限制结果集行数
SELECT top 3 * FROM T_Employee WHERE FNumber NOT IN
(SELECT TOP 5 FNumber FROM T_Employee ORDER BY FSalary DESC)
ORDER BY FSalary DESC
(2)去掉数据重复
DISTINCT是对整个结果集进行数据重复处理的,而不是针对每一个列
(3)聚合函数
MAX(最大值)、MIN(最小值)、AVG (平均值)、SUM (和)、COUNT(数量)
32.主键/外键对数据插入/更新的影响
(1)主键在插入时的影响
主键是在同一张表中必须是唯一的,如果在进行数据插入的时候指定的主键与表中已有 的数据重复的话则会导致违反主键约束的异常。T_Debt 表中 FNumber 字段是主键,如果我 们执行下面 SQL:
INSERT INTO T_Debt (FNumber,FAmount, FPerson) VALUES ('1',300, 'Jim')
由于在上一节中我们已经向表中插入了一条 FNumber 字段为 1 的记录,所以运行这句
SQL 的时候会报出类似如下的错误信息:
不能在对象 'dbo.T_Debt' 中插入重复键。
而如果我们为 FNumber 设置一个不重复值的话,则会插入成功,执行下面的 SQL:
INSERT INTO T_Debt (FNumber,FAmount, FPerson) VALUES ('2',300, 'Jim')
(2)外键在插入时的影响
外键是指向另一个表中已有数据的约束,因此外键值必须是在目标表中存在的。如果插 入的数据在目标表中不存在的话则会导致违反外键约束异常。
INSERT INTO T_Debt (FNumber,FAmount, FPerson) VALUES ('3',100, 'Jerry')
由于在 T_Person 表中不存在 FName 字段等于“Jerry”的数据行,所以会数据库系统会
报出类似如下的错误信息:
INSERT 语句与 FOREIGN KEY 约束"FK T_Debt FPerson 1A14E395"冲突。该冲突发生于数据库"demo",表"dbo.T_Person", column 'FName'。
而如果我们为 FPerson 字段设置已经在 T_Person 表中存在的 FName 字段值的话则会插入成功,执行下面的 SQL:
INSERT INTO T_Debt (FNumber,FAmount, FPerson) VALUES ('3',100, 'Tom')
(3)主键对数据更新的影响
主键是在同一张表中必须是唯一的,如果在进行数据更新的时候指定的主键与表中已有 的数据重复的话则会导致违反主键约束的异常。T_Debt 表中 FNumber 字段是主键,如果我 们执行下面 SQL:
UPDATE T_Debt set FNumber = '2' WHERE FPerson='Tom'
由于表中已经存在一条 FNumber 字段为 2 的记录,所以运行这句 SQL 的时候会报出类似
如下的错误信息:
违反了 PRIMARY KEY 约束 'PK T_Debt 1920BF5C'。不能在对象 'dbo.T_Debt' 中插入重复键。
而如果我们为 FNumber 设置一个不重复值的话,则会插入成功,执行下面的 SQL:
UPDATE T_Debt set FNumber = '8' WHERE FPerson='Tom'
(4)外键对数据更新的影响
。T_Debt 表中 FPerson 字段 是指向表 T_Person 的 FName 字段的外键,如果我们执行下面 SQL:
UPDATE T_Debt set FPerson = 'Merry' WHERE FNumber='1'
由于在 T_Person 表中不存在 FName 字段等于“Merry”的数据行,所以会数据库系统会
报出类似如下的错误信息:
UPDATE 语句与 FOREIGN KEY 约束"FK T_Debt FPerson 1A14E395"冲突。该冲突发生于数据库"demo",
表"dbo.T_Person", column 'FName'。
而如果我们为 FPerson 字段设置已经在 T_Person 表中存在的 FName 字段值的话则会插入成功,执行下面的 SQL:
UPDATE T_Debt set FPerson = 'Lili' WHERE FNumber='1'
———————————————————索引的操作———————————
33.索引的意义
索引想像成汉语字典的的按笔画查找的目录。汉语字典中的汉字是按拼音的顺序 排列在书页中的,如果要查询笔画数为 18 的汉字的话就必须挨个查找每个汉字来比较每个 汉字的笔画数,这种速度是让人无法忍受的。而如果我们创建一个按笔画查找的目录:将笔 画为 5 的汉字列出来,将笔画为 6 的汉字列出来……,这样当我们要查询笔画数为 18 的汉 字的话只要来查找这个目录就可以非常快速的查找到需要的数据了。
34.索引的缺陷
索引减慢了数据插入和删除的速度。因为每次插入和删除的时候都需要更新索引,一个
表拥有的索引越多,则写操作的平均性能下降就越大
35.索引的建立
在表指定的列中单击右键进行添加一个新的索引,一般应为非聚集的。
36.索引的一些Sql命令
(1).加快数据的查询,创建唯一索引,保证字段的数据不会重复
create unique index name_index on products(productname)
(2).禁用索引
alter index product_name on products disable
(3).重新生成索引(如果有一样的字段值,则不允许执行下面语句)
alter index product_name on products rebuild
(4).删除索引
drop index products.name_index
——————————————————视图的操作————————————
37. 视图的使用
(1).创建视图
create view view_班级 as select * from 班级
(2).对视图进行加密,不允许修改视图(加密视图是不可逆的步骤,任何用户均不能查看视图的定义,修改视图)
create view view_班级 with encryption as select * from 班级
(3).查看视图的定义即如何创建视图的
sp_helptext view_班级
——————————————————约束的使用————————————
38.约束的使用
一:约束 constraint name 定义约束的名称
约束就是一种强制性的规定,在SQL SERVER 2005中提供的约整是通过定义字段的取值规则来维护数据完整性的。在SQL SERVER 中支持6类约束:NOT NULL(非空)约束、CHECK(检查约束)、UNIQUE(唯一束)、PRIMARY KEY (主键 约束)、FOREIGN KEY (外键约束) 和DEFAULT (默认约束)
create table products
(
productName nvarchar(20) not null ,
productid int not null,
contry nvarchar(20),
constraint product_name unique(productName),
constraint product_id primary key(productid),
)
(1).NOT NULL(非空)约束
create table 系部
(
系部代码 char(6) not null,
系部名称 varchar(30) not null ,
系主任 char(8)
)
(2).CHECK(检查约束)
create table products
(
productName nvarchar(20) not null unique nonclustered ,
productid int primary key clustered identity(100,2),
contry nvarchar(20) default '安徽',
amount int not null check(amount>20)
)
(3).UNIQUE(唯一束) 强调非主键列指定的字段的值是唯一的 unique nonclustered
create table products
(
productName nvarchar(20) not null unique nonclustered ,
productid int primary key clustered
)
(4).PRIMARY KEY (主键 约束) identity(100,2) 100指的是起始位置为100 2指的是递增量
create table products
(
productName nvarchar(20) not null unique nonclustered ,
productid int primary key clustered identity(100,2)
)
(5).FOREIGN KEY (外键约束)
(6).DEFAULT (默认约束) default '安徽'
create table products
(
productName nvarchar(20) not null unique nonclustered ,
productid int primary key clustered identity(100,2),
contry nvarchar(20) default '安徽'
)
(7).检查约束的文本
EXEC sp_helptext DF__products__contry__2B3F6F97
(8).通过查询分析器来查看约束的名称、创建者、类型和创建时间
EXEC sp_help DF__products__contry__2B3F6F97
(9).删除约束
alter table products drop constraint DF__products__contry__3A81B327
———————————————临时表的使用——————————————
39.临时表的使用
(1).局部临时表(当前会话中可用,不能分区,#)
create table #js
(
ygbh int not null primary key identity(2,1),
ygname nvarchar(20) not null unique nonclustered
)
select * from #js
(2).全局临时表(所有会话中都可用,##)
create table ##js
(
ygbh int not null primary key identity(2,1),
ygname nvarchar(20) not null unique nonclustered
)
select * from #js
支持除FOREIGN KEY约束以外的其他所有约束