数据库概论-chap01定义功能

第一章SQL定义号称学过数据库不会SQL也枉然

秦失其鹿，世人竞逐，一人得之，其余皆止天上布满了无数的星辰，每一个星辰都是一个火球，都有它各自的光辉，可是在众星之中，只有一个星卓立不动，它的不可动摇的性质，在天宇中是无与伦比的。在人世间也是这样；无数的人生活在这世间，他们都是有血肉有知觉的，可是我知道只有一个人能够确保他的不可侵犯的地位，任何力量都不能使他动摇，我就是他。Mr.SQL如是说SQL概述SQL数据定义功能SQL数据查询功能SQL数据修改功能SQL数据控制功能可编程SQL3提纲SQL概述：历史SQL:StruceuredQueryLanguang1974年，由Boyce和Chamber提出1975-1979年，在SystemR上实现，由IBM的SanJose研究室研制，称为SequelSQL概述：标准化SQL-86“数据库语言SQL”SQL-89“具有完整性增强的数据库语言SQL”，增加了对完整性约束的支持SQL-92“数据库语言SQL”，是SQL-89的超集，增加了许多新特性，如新的数据类型，更丰富的数据操作，更强的完整性、安全性支持等。Official与defactoSQL概述：标准化SQL-99：增加对面向对象模型的支持SQL-2003：XML，MergeSQL-2006SQL-2008SQL-2011：分析函数对自己，学而不厌；对人家，诲人不倦SQL概述：特点一体化集DDL，DML，DCL于一体单一的结构----关系，带来了数据操作符的统一面向集合的操作方式一次一集合高度非过程化用户只需提出“做什么”，无须告诉“怎么做”，不必了解存取路径两种使用方式，统一的语法结构SQL既是自含式(用户使用)的，又是嵌入式的(程序员使用)语言简洁，易学易用SQL概述：操作符SQL功能操作符数据查询SELECT数据定义CREATE，ALTER，DROP数据操纵INSERT，UPDATE，DELETE数据控制GRANT，REVOKE示例关系DEPT(D#,DNAME,DEAN)S(S#,SNAME,SEX,AGE,D#)C(C#,CN,PC#,CREDIT)SC(S#,C#,GRADE)PROF(P#,PNAME,AGE,D#,SAL)PC(P#,C#)SQL数据定义功能数据库定义模式定义表定义域定义视图定义索引定义簇(cluster)目录(catalog)模式(schema)对象(object)列(column)域(domain)用户定义类型规则(rule)断言(assertion)标准SQL文件组数据库模式表图书馆阅览室书架列约束视图索引对象命名：<数据库>.<模式>.<表>University.Library.Book数据库定义创建数据库createdatabase<数据库名> [on[primary]<文件描述><文件组>…] [logon<文件描述><文件组>…]最简单的创建数据库的命令如下：createdatabaseUniversity使用use命令，指定当前要使用的数据库use UniversitycreatedatabasedemoDB1onprimary( name=demo_dat1, filename='D:\SQL_Practice\demodata1.mdf', size=10, maxsize=50, filegrowth=5)logon( name=demo_log1, filename='D:\SQL_Practice\demodata1.ldf', size=5, maxsize=25, filegrowth=5)模式定义模式把对象和用户分离开来创建模式createschema<模式名>createschemaUniversity.Library查看模式select*fromsys.schemasdbo11guest22INFORMATION_SCHEMA33sys44基本表的定义基本表的定义（CREATE）格式

createtable

表名（列名数据类型[default

缺省值][notnull][unique][，列名数据类型[default

缺省值][notnull]]……[，primarykey（列名[，列名]…）][，foreignkey

（列名[，列名]…）

references

表名（列名[，列名]…）][，check（条件）]）基本表的定义

createtableS (S# char（8）, SNAME char（8）notnull

default‘Unknown’, AGE tinyint, SEX char(1), primarykey(S#), check(SEX=‘M’

orSEX=‘F’) )基本表的定义createtableC (C# char（4）primarykey, CNAME char（8）notnullunique, PC# char（4）

foreignkey

referencesC(C#))基本表的定义createtableSC (S# char（8）, C# char（4）, GRADE tinyint, auGRADE SQRT(GRADE)*10persisted primarykey(S#,C#), foreignkey(S#)referencesS(S#), foreignkey(C#)referencesC(C#), check((GRADEisnull)

orGRADEbetween0and100)))基本表的定义sysobjects列名数据类型描述namesysname对象名。Idint对象标识号。xtypechar(2)对象类型。uidsmallint所有者对象的用户ID。crdatedatetime对象的创建日期。schema_verint版本号，该版本号在每次表的架构更改时都增加。基本表的定义syscolumns列名数据类型描述namesysname列名或过程参数的名称。idint该列所属的表对象ID。xtypetinyintsystypes

中的物理存储类型。xusertypesmallint扩展的用户定义数据类型ID。lengthsmallintsystypes中的最大物理存储长度。offsetsmallint该列所在行的偏移量；如果为负，表示可变长度行。typetinyintsystypes

中的物理存储类型。usertypesmallintsystypes

中的用户定义数据类型ID。isnullableint表示该列是否允许空值：基本表的定义INFORMATION_SCHEMA.SCHEMATAINFORMATION_SCHEMA.TABLESINFORMATION_SCHEMA.COLUMNSINFORMATION_SCHEMA.CHECK_CONSTRAINTSINFORMATION_SCHEMA.VIEWSINFORMATION_SCHEMA.DOMAINS……基本表的定义//创建对象的前缀useS_C_SifEXISTS (select name from sysobjects where ID=OBJECT_ID('S')droptableScreatetableS(…)//察看属性列信息selectname,xtype,xusertype,length,offset,type,usertypefromsyscolumnswhereID=OBJECT_ID('S')基本表的定义修改基本表定义（ALTER）更改、添加、除去列和约束格式：

altertable

表名

[add

子句] 增加新列和约束

[drop子句] 删除列和约束

[modify

子句] 修改列定义 示例

altertableSaddLOCATIONchar[30]基本表的定义altertableSaddresumechar[100]notnullaltertableSaltercolumnresumechar[80]如何定义两个相互参照的表？E#ENAMED#D#DNAMEMGRcreatetableemp(E#primarykey…)createtabledept(D#primarykey,MGRforeignkey…)altertableempaddconstraintD#foreignkey…基本表的定义撤消基本表定义（drop）格式

droptable

表名DANGER

删除表定义及该表的所有数据、索引、触发器、约束和权限规范

任何引用已除去表的视图或存储过程必须通过dropview或dropprocedure语句显式除去droptable不能用于除去由foreignkey约束引用的表。必须先除去引用的foreignkey约束或引用的表临时表的定义临时表草稿簿，试验中间的数据处理只记录回滚信息，不记录重做信息临时表上的数据更新比其他表快4倍私有临时表createtable

#my_table全局临时表createtable

##my_tabletempdbcreatetablemy_table域定义域类型（SQL-92）char（n）：固定长度的字符串varchar（n）：可变长字符串int：整数smallint：小整数类型numeric（p，d）：定点数，小数点左边p位，右边q位real：浮点数doubleprecision：双精度浮点数date：日期（年、月、日）time：时间（小时、分、秒）interval：两个date或time类型数据之间的差数据类型范围存储字节数精确数字bit取值为1或0表中的bit列合并为一个字节存储tinyint1字节smallint2字节int4字节bigint8字节decimal(p,s)numeric(p,s)固定精度和小数位数的数值，p是总位数，s是小数位数。数据范围

随精度而变，5~171~9位数,5字节29~38位数,17字节money-922,337,203,685,477.5808~922,337,203,685,477.58078字节smallmoney-214,748.3648~214,748.36474字节近似数字float(n)-1.79E+308~1.79E+308n位于1~24,4字节n位于25~53,8字节realfloat(24)的同义词4字节日期和时间date0001-01-01~9999-12-313字节time00:00:00.0000000~23:59:59.99999995字节smalldatetime1900-01-01~2079-06-064字节datetime日期：1753-01-01~9999-12-31时间：00:00:00~23:59:59.9978字节datetime2(精度)日期：0001-01-01~9999-12-31时间：00:00:00到23:59:59.9999999精度小于3，6字节精度为3和4，7字节其他精度，8字节datetimeoffset日期：0001-01-01~9999-12-31时间：00:00:00到23:59:59.999999910字节字符char(n)1~8000固定n字节varchar(n)1~8000实际字符串长度varchar(max)实际字符串长度text实际字符串长度nchar,nvarchar,ntextUnicode字符串字符数的两倍二进制binary(n)1~8000固定n字节varbinary(n)1~8000实际数据长度varbinary(max)实际数据长度image实际数据长度标识符timestamp,rowversion表示行更新的唯一序号8字节uniqueidentifier全局唯一标识符16字节杂项cursor声明cursor变量以保持对游标的引用table声明table变量以保持对结果集的引用sql_variant声明存储混合数据类型的列层次结构xml声明存储xml数据的列hierarchyId表示结点在树层次结构中的位置空间数据geography表示圆形地球坐标系中的数据geometry表示欧几里得（平面）坐标系中的数据数据类型的使用说明日期类型date：0001-1-1~9999-12-31smalldatetime：1900-1-1~2079-6-6datetime：1753-1-1~9999-12-31datetime2：0001-1-1~9999-12-31弃置不用的数据类型对于text,ntext,image,应尽可能避免在应用中使用它们，因为后续的SQLServer版本可能会放弃对它们的支持，取而代之的是用varchar(max),nvarchar(max),或者varbinary(max)来加以声明数据类型的使用说明decimal数据类型decimal(p,s)是精确数据类型，精度p指的是总位数，s指的是小数点右边的位数。p和s决定了数字的范围，12.3456是符合decimal(6,4)定义的，而123.4567则会发生溢出错误。declare@num1decimal(4,2), @num2decimal(4,2)set @num1=12.344set @num2=12.345select @num1,@num2输出结果为：@num1=12.34,@num2=12.35数据类型的使用说明money数据类型money使用4位小数存储数据，所以很容易发生小数的舍入错误。declare @Amoney=1.0,@Bmoney=160.0,@Cmoney,@Ddecimal(19,4),@Edecimal(19,9)set @C=@A/@Bset @D=1.0/160.0set @E=1.0/160.0select @C,@D,@E@C=0.0062,@D=0.0063,@E=0.006250000。数据类型的使用说明rowversion数据类型如果表中有一列被声明为rowversion，那么只要一行被修改，该行的rowversion列就会发生改变。它是跨表唯一的，也即任何表的修改都会使该值递增createtableverTb(valchar(10),verrowversion)

insertintoverTb(val)values(‘zero’)

updateverTbsetval='one'select*fromverTB

updateverTbsetval='two'select*fromverTB数据类型的使用说明uniqueidentifier数据类型uniqueidentifier可以产生跨数据库和服务器的全局唯一标识符(GUID)使用newid()函数，可以直接产生uniqueidentifier类型的值。另一个函数newsequentialid()，它产生的GUID总是大于先前通过该函数生成的GUID执行selectnewid()，它的输出结果如下： DD64B592-D-32E9FCB540FA数据类型的使用说明创建table数据类型createtypemyTbastable( col1int, col2int)declare@tmpmyTbinsertinto@tmpvalues(1,2),(2,5),(3,7),(4,8);select*from@tmp数据类型的使用说明显式数据类型转换通过使用命令cast或者convert，可以将一种数据类型转换为另一种。它们的命令格式分别如下：cast(表达式as

数据类型[(数据长度)])convert(数据类型[(数据长度)],表达式[,输出样式])selectcast(123.45asdecimal(10,4)) 输出结果：123.4500selectcast(123.45asdecimal(10,1)) 输出结果：123.5selectcast(12.34567asmoney) 输出结果：12.3457select‘Myageis:’+cast(28aschar(4)) 输出结果：Myageis:28selectcast(cast(123.45asint)aschar(10)) 输出结果：123selectconvert(varchar(30),getdate(),106) 输出结果：17082012selectconvert(varchar(30),getdate(),110) 输出结果：08-17-2012selectcast('SQL'asbinary(3)) 输出结果：0x53514C数据类型的使用说明隐式数据类型转换如果不指定显式的类型转换，那么在对两个不同数据类型的列值进行运算时，系统会进行隐式的类型转换，也即先将它们转换为同一个数据类型，然后再进行运算执行select1+'1'，其结果是2执行select1+‘a’，会显示类型转换错误select iif(1=1,‘A’,88.8)selectcasewhen1=1thennullelsenullend用户定义数据类型UDDT：User-defineddatatype保证数据一致性格式

createdomain

域名数据类型示例createdomain

person-namechar（20）

类似C语言中：

typedef

ADDRESS_LIST{ charname[10]; chartelephone[20]; charlocation[20]};

ADDRESS_LISTtom;用户定义数据类型:SQLServer EXECsp_addtypephone_number,’varchar(20)’,’notnull’

createtablecustomer (cust_id smallint notnull, cust_phone phone_number, cust_fax varchar(20) notnull, cust_eamil varchar(20) notnull)

select colid,name,xtype,length,xusertype,offset from syscolumns where id=object_id(‘customer’)

createtypephone_numberfromvarchar(20)notnull定义者的选择变长之利 减少存储开销＋元组数/页高变长之弊 查询计算偏移＋更新挪移数据变长之用 长短显著不一＋很少发生变化深刻的理解影响＋邃远的预见未来charorvarchar定义者的选择主码保证其惟一性必须进行字符匹配一个表的主码经常是另外一个表的外码，而外码是对主码的复制，如果主码太长的话，加上外码则会占据很大的表空间表之间的连接一般是基于主外码的，为加快查询会在主外码上建立索引，太长的主码会使得一个页面里容纳很少的索引项，从而增加查找数据时所需要的磁盘I/O数。真主的选择：让阿卜杜拉.赛义德.侯赛因.穆罕默德...做主码IDENTITY:SQLServerIDENTITY[(seed,increment)]为一些没有有效主码的表提供计数器有一个起始数（种子），增量值（步长）IDENTITY属性不能为空，也不能带有default示例createtablecustomer1 (cust_id smallint IDENTITYnotnull，

cust_name varchar(50) notnull)createtablecustomer2 (cust_id smallint IDENTITY(100,20)notnull cust_name varchar(50) notnull)IDENTITYIDENTITY有关函数IDENT_SEED(表名)

返回种子值IDENT_INCR

(表名)

返回增量值IDENT_CURRENT(表名)

返回指定的表最后生成的标识值SCOPE_IDENTITY()

返回同一作用域最后生成的标识值@@IDENTITY

返回当前会话中任何作用域内最后生成的标识值IDENTITYDBCCCHECKIDENT检查指定表的当前标识值，必要时对其进行更正DBCCCHECKIDENT(表名,NORESEED)

不重置当前标识值。DBCCCHECKIDENT返回一个报表，它指明当前标识值和应有的标识值DBCCCHECKIDENT(表名,RESEED)

如果表的当前标识值小于列中存储的最大标识值，则使用标识列中的最大值对其进行重置DBCCCHECKIDENT(表名,RESEED,new_reseed_value)

当前值设置为new_reseed_value索引的定义表AB53716224B上的索引指针索引码RID21RID32RID13RID44whereB=3RowID：（文件号，页面号，页内偏移号）索引的定义格式create[unique][cluster]index

索引名on

表名(列名[asc/desc][,列名asc/desc]]…)

unique：唯一性索引，不允许表中不同的行在索引列上取相同值。若已有相同值存在，则系统给出相关信息，不建此索引。系统拒绝违背唯一性的插入、更新

cluster：聚簇索引，表中元组按索引项的值排序并物理地聚簇在一起。一个基本表上只能建一个聚簇索引

asc/desc：索引表中索引值的排序次序，缺省为asc为什么需要降序索引？临江之麋表AB53716224表AB71625324聚簇索引p1p2p1p2➝p1➝p2➝p1➝p2➝p1➝p2聚簇索引的优点假定内存只能容纳一个页面，按照B值顺序访问数据罴说索引的作用查找元组表连接排序分组保证唯一性索引对约束的支持通过创建唯一性索引来支持主码约束和唯一性约束索引的定义:SQLServer创建索引选项WITHFILLFACTOR=

fillfactor

指定在创建索引的过程中，各索引页的填满程度。如果某个索引页填满，系统就必须花时间拆分该索引页，以便为新行腾出空间，这需要很大的开销

createindexSNAME_indonS(SNAME) withfillfactor=100 fillfactor为100将完全填满每一页，只有确定表中的索引值永远不会更改时，该选项才有用索引码1256索引码123索引码56插入码3索引的定义IGNORE_DUP_KEY

在创建唯一聚簇索引时，如果为索引指定了IGNORE_DUP_KEY，并且执行了创建重复码的INSERT语句，系统将发出警告消息并忽略重复的行。如果没有为索引指定IGNORE_DUP_KEY，系统会发出一条警告消息，并回滚整个INSERT语句DBCCDBREINDEX重建指定数据库中表的一个或多个索引DBCCDBREINDEX(S,S_ID_IND,70)DBCCDBREINDEX(S,70)索引的定义组合索引建立在多个属性列上的索引如果组合索引建立在A,B,C上,那它只对检索A,A+B,A+B+C列的查询起作用ABC336136335253123ABC123136253335336永某氏之鼠组合索引的用场orderbyA,B,CselectBfromRwhereA>10select*fromRwhereA>10and B=20SQLServer的其他索引覆盖索引：它是把select中的列也包含进索引中，这样就可以完全基于索引回答查询，避免对表的访问。定义覆盖索引的关键字是includecreateindexmy_idxonR(A)includeBB值不出现在索引的中间结点上与组合索引的差别？黔驴技穷过滤索引：在索引的定义中可以加入where语句，索引中只包括那些满足过滤条件的列值如果一个列上有很多null，可以用whereisnotnull使得索引中不包括null对于一个列A，在[1，20]上值很多，建索引的话，由于其选择度很低，实际效果并不好，但在[20，100]上值很少，对这区间建立索引效果就会很好。可以在创建A上的索引时，用where指定索引范围如果1000个学生中，有10个女生，那么在对性别建索引时，可以指定性别='女‘createindexmy_idxonS(SEX)whereSEX=‘F’Oracle的函数索引createindexidx1onS(name)selectfromSwhereUPPER(name)=‘TOM’createindexidx2onS(UPPER(name))索引的定义索引的删除dropindex

索引名dropindex语句不适用于通过定义primarykey或unique约束创建的索引，它们必须通过除去约束来撤销索引的定义索引的有关说明可以动态地定义索引，即可以随时建立和删除索引不允许用户在数据操作中引用索引。索引如何使用完全由系统决定，这支持了数据的物理独立性应该在使用频率高的、经常用于