sqlserver 第三章结构化查询语言SQL新.docx
- 文档编号:23762482
- 上传时间:2023-05-20
- 格式:DOCX
- 页数:33
- 大小:34.67KB
sqlserver 第三章结构化查询语言SQL新.docx
《sqlserver 第三章结构化查询语言SQL新.docx》由会员分享,可在线阅读,更多相关《sqlserver 第三章结构化查询语言SQL新.docx(33页珍藏版)》请在冰豆网上搜索。
sqlserver第三章结构化查询语言SQL新
第一部分系统数据库功能简介
一、Master数据库:
是SQLServer系统最重要的数据库,数据库记录了一个SQLServer
系统的所有系统信息,这些信息主要有:
A、所有的登录信息
B、系统设置信息
C、SQLServer初始化信息
D、系统中其它系统数据库和用户数据库的相关信息,包括其主文件的存放位置等;
二、Model数据库:
是所有用户数据库和Tempdb数据库的创建模板,当创建数据库时系统会将Model数据库中的内容复制到新建的数据库中去。
A、数据库的最小容量
B、数据库选项设置
C、经常使用的数据库对象,如用户自定义的数据类型函数规则缺省值等;
三、Msdb数据库:
存储计划信息与备份和恢复相关的信息。
代理服务数据库,为其警报、任务调度和记录操作员的操作提供存储空间。
四、Tempdb数据库:
临时的表和存贮过程,在此数据库上数据操作比别的数据库要快
一个临时数据库,它为所有的临时表、临时存储过程及其它临时操作提供存储空间。
五、两例示例数据库pubs和northwind为学习工具,帮助的代码都可运行。
六、几个最重要的系统表:
1.Sysobjects表
SQLServer的主系统表sysobjects出现在每个数据库中,它对每个数据库对象含有一行记录。
2.Syscolumns表
系统表syscolumns出现在master数据库和每个用户自定义的数据库中,它对基表或者视图的每个列和存储过程中的每个参数含有一行记录。
3.Sysindexes表
系统表sysindexes出现在master数据库和每个用户自定义的数据库中,它对每个索引和没有聚簇索引的每个表含有一行记录,它还对包括文本/图像数据的每个表含有一行记录。
4.Sysusers表
系统表sysusers出现在master数据库和每个用户自定义的数据库中,它对整个数据库中的每个WindowsNT用户、WindowsNT用户组、SQLServer用户或者SQLServer角色含有一行记录。
5.Sysdatabases表
系统表sysdatabases对SQLServer系统上的每个系统数据库和用户自定义的数据库含有一行记录,它只出现在master数据库中。
6.Sysdepends表
系统表Sysdepends对表、视图和存储过程之间的每个依赖关系含有一行记录,它出现在master数据库和每个用户自定义的数据库中。
7.Sysconstraints表
系统表sysconstraints对使用CREATETABLE或者ALTERTABLE语句为数据库对象定义的每个完整性约束含有一行记录,它出现在master数据库和每个用户自定义的数据库中。
第二部分创建和管理数据库
一、创建数据库:
1、格式:
Createdatabase数据库名
On[Primary]
(Name=逻辑文件名,
Filename=物理文件名,
Size=文件起始大小,
Maxsize=文件最大容量,
Filegrowth=文件增量),
……
Filegroup文件组名
(Name=逻辑文件名,
Filename=物理文件名,
Size=文件起始大小,
Maxsize=文件最大容量,
Filegrowth=文件增长容量)
……
Logon
(Name=逻辑文件名,
Filename=物理文件名,
Size=文件起始大小,
Maxsize=文件最大容量,
Filegrowth=文件增长容量)
……
说明:
(1)Primary:
指定下面文件为主文件组的文件。
可省略。
(2)Filename:
指定文件的实际存储位置。
(3)Size:
指定文件的起始大小(512K/1M)。
(4)Maxsize:
指定文件可达到的最大容量。
(5)Filegrowth:
定义的文件的增量。
文件的增量设置不能超过Maxsize设置。
可以指定一个确切的增长数值,也可以指定增长的百分比(起始值的百分比),默认为10%。
(10%/1M)
(6)Logon:
指定下面为日志文件。
(7)主要数据文件扩展名*.Mdf,次要数据文件扩展名*.ndf,日志文件扩展名*.ldf
2、实例:
创建带有多个文件组的数据库
Createdatabasebook
OnPrimary
(Name=book_mdf,
Filename='d:
\books\book_mdf.mdf',
Size=3,
Maxsize=30,
Filegrowth=3),
Filegroupgroup1
(Name=book_ndf1,
Filename='d:
\books\book_ndf1.ndf',
Size=2MB,
Maxsize=20MB,
Filegrowth=25%),
Filegroupgroup2
(Name=book_ndf2,
Filename='d:
\books\book_ndf2.ndf',
Size=4,
Maxsize=30,
Filegrowth=4)
Logon
(Name=book_ldf1,
Filename='d:
\books\book_ldf1.ldf',
Size=5,
Maxsize=40,
Filegrowth=5),
(Name=book_ldf2,
Filename='d:
\books\book_ldf2.ldf',
Size=6,
Maxsize=60,
Filegrowth=30%)
二、修改和删除数据库:
1、修改数据库:
(1)添加数据文件:
a)格式:
Alterdatabase数据库名AddFile
(Name=逻辑文件名,
Filename=物理文件名,
Size=文件起始大小,
Maxsize=文件最大容量,
Filegrowth=文件增量)
[ToFileGroup文件组名]
b)说明:
ToFileGroup:
指定添加的数据文件到哪个文件组中,该文件组必须存在,默认为主文件组。
c)实例:
Alterdatabasestudent
addfile
(Name=student_ndf,
Filename='d:
\sql\student_ndf.ndf',
Size=1,
Maxsize=5,
Filegrowth=1)
(2)添加日志文件:
a)格式:
Alterdatabase数据库名AddLogFile
(Name=逻辑文件名,
Filename=物理文件名,
Size=文件起始大小,
Maxsize=文件最大容量,
Filegrowth=文件增量)
b)实例:
Alterdatabasestudent
AddLogFile
(Name=student_ldf1,
Filename='d:
\sql\student_ldf1.ldf',
Size=4,
Maxsize=40,
Filegrowth=15%)
(3)添加文件组:
a)格式:
Alterdatabase数据库名AddFilegroup文件组名。
b)实例:
AlterdatabasestudentAddFilegroupgroup1
向该文件组中添加文件
AlterdatabasestudentAddFile
(Name=student_ndf2,
Filename='d:
\sql\student_ndf2.ndf',
Size=3,
Maxsize=30,
Filegrowth=3)
ToFilegroupgroup1
(4)修改文件(数据文件和日志文件):
a)格式:
Alterdatabase数据库名ModifyFile
(Name=逻辑文件名,
[Size=新的文件大小,]
[Maxsize=将要达到的容量,]
[Filegrowth=修改后的增量])
b)说明:
修改数据文件和日志文件的格式是相同的,但逻辑文件名和物理文件名不能修改。
在修改文件时,必须指定文件的逻辑名,用来标识将要修改的文件。
而不必指定文件的物理名,否则将出现错误。
如果指定修改文件的Size,则新的文件大小必须比当前文件大小要大。
而修改文件的Maxsize和Filegrowth时,数值可以增大,也可以和原来的相同,也可以减小。
c)实例:
AlterdatabasestudentModifyFile
(Name=student_ldf1,
Filegrowth=20%)
(5)删除文件:
a)格式:
Alterdatabase数据库名RemoveFile文件名
b)说明:
不能删除主要数据文件和主要日志文件。
c)实例:
AlterdatabasestudentRemoveFilestudent_ldf1
(6)删除文件组:
a)格式:
Alterdatabase数据库名RemoveFilegroup文件组名
b)说明:
要删除的文件组中不能包含数据文件。
c)实例:
AlterdatabasestudentRemoveFilestudent_ndf2
AlterdatabasestudentRemoveFilegroupgroup1
2、删除数据库:
(1)格式:
Dropdatabase数据库名
(2)实例:
Dropdatabasestudent
三、设置数据库选项:
1、格式:
SP_dboption数据库名[,‘选项名’[,ture|false]]
2、说明:
SP_dboption:
系统的存储过程(systemprocedure),可以通过它设置数据库选项,它支持对所有
可用的数据库选项进行设置。
a)如果只设置{SP_dboption数据库名},那么将显示在本数据库中设置为True的选项。
b)如果设置{SP_dboption数据库名,‘选项名’},那么将显示指定的选项当前处于什么状态(ON或者OFF)
3、实例:
将student数据库的readonly选项设置为True,即打开此选项。
常用三项(readonly|singleuser|dbouseonly)
SP_dboptionstudent,'readonly','true'
第三部分存储数据
一、数据类型:
指定列、数据存储参数和局部变量的数据特性。
数据按照数据类型存储在列中。
数据类型可以分为两大类:
1、系统数据类型:
是SQLSERVER支持的内置数据类型。
1)字符型:
char、nchar、varchar、nvarchar、text、ntext。
a)char:
固定长度的非Unicode字符数据,最大的长度为8000字符。
b)nchar:
固定长度的Unicode数据,最大的长度为4000字符。
c)varchar:
可变长度的非Unicode数据,最大的长度为8000字符。
d)nvarchar:
可变长度的Unicode数据,最大的长度为4000字符。
e)text:
可变长度的非Unicode数据,最大的长度为2^31-1个字符。
f)ntext:
可变长度的Unicode数据,最大的长度为2^30-1个字符
对于定义为char或nchar的列,SQLSERVER将用字符串来填满指定的字节数。
定义为varchar或nvarchar的列只存储输入的实际长度,可能舍去尾部空间。
SQLSERVER处理尾部空间,取决于SETANSI_PADDING,以及该列是固定长度还是可变长度。
根据SETANSI_PADDING值处理尾部空间
ANSI_PADDING
char和nchar
Varchar和nvarchar
ON
串被空格填充到列的长度
串未被空格填充到列的长度,尾部空间被保留
OFF
串被空格填充到列的长度
串未被空格填充到列的长度,尾部空间被截掉
2)日期和时间型:
smalldatetime、datetime。
a)smalldatetime:
从1900年1月1日到2079年6月6日,精确到1分钟。
b)datetime:
从1753年1月1日到9999年12月31日,精确到三百分之
一秒,即3.33毫秒。
3)数值型数据类型:
A、整型:
smallint、int、tinyint。
a)smallint:
从-2^15到2^15-1。
b)int:
从-2^31到2^31-1。
c)tinyint:
从0到255。
B、近似数字数据类型:
float和real。
a)float:
浮点精度数字数据,从-1.79E+308到1.79E+308。
b)real:
:
浮点精度数字数据,从-3.40E+308到3.40E+308。
C、精确数字数据类型:
decimal和numeric。
a)decimal:
不带符号的整数,按10进位。
b)numeric:
decimal(十近制)的同义词,可带小数(numeric(3,2)—共有三位数,其中有两位是小数,e.g.1.23;有最大值问题0-9正确,10报错)
D、货币数据类型:
money和smallmoney。
a)money:
从-2^63到2^63-1,精确到每个货币单位的万分之一。
b)smallmoney:
从-214,748.3648到+214,748.3647,精确到每个货币单位的万分之一。
4)逻辑数据类型:
bit。
Bit:
整形数据,值为1或0。
2、用户自定义类型:
用户可以通过两个系统存储过程创建和删除用户定义类型。
(它们不是真正的新数据类型,而像是一种复合型数据类型或结构。
)Sp_addtype过程创建用户定义的数据类型,Sp_droptype过程删除定义的数据类型。
创建自定义的数据类型:
(1)、格式:
Sp_addtype自定义数据类型名,系统的数据类型,’[null|notnull]’
(2)、说明:
A、用户自定义的数据类型是基于系统的数据类型创建的。
B、[null|notnull]:
指定该列是否为空。
默认为null。
C、如果系统数据类型包括圆括弧,必须用引号把它括起来。
(3)、实例:
Sp_addtypebirthday,datetime,’notnull’
Createtablestu1(sidint,sbirthdaybirthday)
删除自定义的数据类型:
(1)、格式:
[Exec]sp_droptype自定义的数据类型。
(2)、实例:
[Exec]sp_droptypebirthday。
注意:
我们还可以通过企业管理器创建自定义的数据类型。
首先,选中一个数据库,点击右键,选中“新建”,从弹开的菜单中选中“用户定义的数据类型”,在打开的窗体中填充相应的选项即可。
二、表操作:
1、创建表:
(1)、格式:
Createtable表名(字段名1数据类型,
………
字段名n数据类型)
(2)、说明:
表名的定名原则要遵循标识符的定名原则。
字段和字段之间用逗号隔开。
(3)、实例:
Usebook
go
Createtablebook(bookidint,booknamevarchar(10))
2、修改表:
(1)、增加字段:
a)、格式:
Altertable表名Add字段名字段类型
b)、实例:
Altertablebookaddprovideridvarchar(10),addressvarchar(20)
(2)、修改字段:
a)格式:
Altertable表名Altercolumn字段名字段类型
b)实例:
AltertablebookAltercolumnaddressvarchar(10)
(3)、删除字段:
a)格式:
Altertable表名Dropcolumn字段名
b)实例:
Altertablebookdropcolumnaddress
3、删除表:
(1)、格式:
Droptable表名
(2)、说明:
把不再起作用的表从数据库中删除。
(3)、实例:
Droptablebook
三、数据完整性:
是通过实现过程数据完整性和声明数据完整性来强制执行的。
1.1、Identity:
identity属性可以生成唯一标识表中每一行的连续值。
1)格式:
Identity(初始值,增量)
2)实例:
Createtabletab1(idintidentity(1,1),namevarchar(10))
altertabletab2addgradeintidentity(1,1)
3)说明:
一个表中只能有一个identity标识的列,字段必须是整型。
不能更新定义有identity属性的列。
不能向定义有identity属性的列赋null值或附加默认约束。
2.2、Uniqueidentifier:
使用Uniqueidentifer和Newid函数也可以生成列的唯一值,与
Identity属性类似。
如果创建的列是uniqueidentifer数据类型,则必须使用newid()函数为它生成新值。
实例:
Createtabletab2(iduniqueidentifier,namevarchar(10))
插入值:
insertintotab2values(newid(),'mary')
createtables(stuidintidentity(2,2),ageuniqueidentifier)
insertintotab2values('mary',newid())
3.3、使用约束:
(1)主键(primarykey):
唯一标识每一行。
1)说明:
Ø一个表中只可以定义一个主键。
Ø不能在主键列中输入null值。
Ø最多可定义16列作为主键。
Ø定义之后,则不能禁用primarykey约束.
2)实例:
A、创建单一主键:
Createtabletab3(idintprimarykey,bidint)
Altertabletab3addconstraintpk_idprimarykey(id)
B、创建复合主键:
Createtabletab4(idint,bidint,constraintpk_id_bidprimarykey(id,bid))
Altertabletab4addconstraintpk_id_bidprimarykey(id,bid)
(2)外键(foreignkey):
Foreignkey定义列值与另一个表的Primarykey相匹配的列。
1)说明:
ØForeignkey约束必须引用另一个表的Primarykey或Unique约束。
Ø相关表中定义为主键的所有列必须作为Foreignkey所包含在当前表中。
ØOnupdatecascade|Ondeletecascade级联更新和级联删除
Ø如果使用Withnocheck选项,将不会验证表中的现有数据。
2)实例:
Createtabletab5(cidintprimarykeynotnull,idint,
foreignkey(id)referencestab3(id))
altertabletab4addconstraintfk_idforeignkey(id)
referencestab5(id)
级联更新和级联删除
altertablea2addconstraintfk_did
foreignkey(did)referencesa1(id)onupdatecascade
altertablea2addconstraintfk_did
foreignkey(did)referencesa1(id)ondeletecascade
createtablea2(didint,dnamevarchar(20),
foreignkey(did)referencesa1(id)onupdatecascade)
createtablea2(didint,dnamevarchar(20),
foreignkey(did)referencesa1(id)ondeletecascade)
(3)CHECK约束:
根据指定值测试列中的输入值。
每次再列中插入或更新数据时均要进行这一测试。
1)说明:
Ø可以为Check约束定义Where字句中的类似条件,但它们不能包含子查询。
ØCheck约束条件可以引用同一个表中的列。
ØCheck约束条件必须对布尔表达式求值。
Ø可以绑定有规则的列定义Check约束
2)实例:
createtabletab6(idint,sexchar
(2)constraintchk_tab6_sex
check(sexin('m','w')))
altertabletab6addconstraintck_idcheck(sexin(‘m’,’w’))
(4)默认约束(default):
此约束用于在用户未提供列值的情况下,提供一个自动添加的列值。
1)说明:
Ø一个表中只能有一列定义有Default约束。
Ø不能在数据类型为Timestamp的列或具有Identity属性的列中定义Default约束。
2)实例:
Createtabletab7(idint,addressvarchar(20)constraintDef_tab7_adddefault'吉林省长春市')
或者Altertabletab7addconstraintdef_tab7_iddefault100forid
(5)唯一性约束(Unique):
在列中应用unique约束以确保列中不输入重复值。
列中所有行的值均不相同。
1)说明:
Ø可以向表中的多列应用unique约束。
Ø向现有表应用unique约束时,一直会验证现有数据。
Ø可以向notnull列应用unique约束,但仅有其中一行能包含null值(候选建)。
2)实例:
Createtabletab8(idintunique)
或者
Createtabletab9(idint)
Altertabletab9addconstrain
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- sqlserver 第三章结构化查询语言SQL新 第三 结构 查询 语言 SQL