SQL语言-数据定义

5.1SQL语言概述SQL，结构化查询语言StructuredQueryLanguage是一种介于关系代数与关系演算之间的语言已成为关系数据库的标准语言支持关系系统Oracle、Informix、Sybase、MSSQL、INGRES、DB2、VF、FoxPro、Access等SQL86由ANSI（美国国家标准局）于1986年10月公布1987年6月国际标准化组织OSI把它采纳为国际标准SQL89ISO于1989年4月公布，增强了完整性特征SQL92ISO于1992年公布，也称为SQL2SQL99意义：自SQL成为国际标准语言以后，各个数据库厂家纷纷推出各自支持的SQL软件或与SQL的接口软件。大多数数据库均用SQL作为共同的数据存取语言和标准接口，使不同数据库系统之间的互操作有了共同的基础。这个意义十分重大。因此，有人把确立SQL为关系数据库语言标准及其后的发展称为一场革命。SQL成为国际标准，对数据库以外的领域也产生了很大影响，有不少软件产品将SQL语言的数据查询功能与图形功能、软件工程工具、软件开发工具、人工智能程序结合起来。SQL已成为关系数据库领域中一个主流语言。意义：大多数数据库开发商支持SQL/99,但并不是百分之百符合这个标准。目前市场上存在一些不同风格的SQL,因为每一个RDBMS开发商都试图扩展这个标准，以增强产品的功能，提高产品的吸引力,因此各个DBMS产品在实现标准SQL语言时也各有差别，读者在使用某个DBMS产品时，应仔细阅读系统提供的有关手册。本书中我们将遵循SQL/99标准向大家介绍语句。但为了增强大家的认识，我们会通过SQLServer数据库阐述部分特性，与标准SQL/99会有一些偏离。1、数据定义DDL语言（数据定义语言），主要定义数据库的逻辑结构，包括定义基本表、视图和索引；2、数据操纵DML语言（数据操纵语言），包括数据查询和更新（插入、删除和修改）两大类操作；3、数据控制DCL语言（数据控制语言），包括对基本表和视图的授权，完整性规则的描述和事务控制；4、嵌入式SQL规定了SQL语句在宿主语言的程序中使用规则。1.SQL语言的组成：1.综合统一查询、操纵、定义、控制操作功能一体化语言风格统一，数据操作符统一性2.高度非过程化用户只需提出“做什么”，而不必指明“怎么做”隐蔽数据存取路径3.面向集合的操作方式操作的对象和结果都是元组的集合（关系）2.SQL语言的特点：4.以同一种语法结构提供两种使用方式交互式语言，一般DBMS都提供联机交互工具，用户可直接键入SQL命令对数据库进行操作，由DBMS来进行解释，针对所有DB用户。嵌入式语言，将SQL语句嵌入到高级语言（宿主语言），使应用程序充分利用SQL访问数据库的能力，针对应用程序员。两种类型的语法结构基本一致5.语言简洁，易学易用类似于英语的自然语言操作谓词少（核心功能只用9个动词）SQL功能动词数据查询数据定义数据操纵数据控制SELECTCREATE，DROP，ALTERINSERT，UPDATE，DELETEGRANT，REVOKE3.SQL对关系数据库模式的支持基本表B1视图V1基本表B3基本表B4存储文件S1存储文件S2基本表B2视图V2SQL外层/外模式概念层/概念模式内层/存储模式基本表（basetable)：数据库中实际独立存在的表（关系）存储在实际的文件中存储文件存储基本表的数据视图（view)是一个虚拟表从基本表或其他视图中导出的表没有实际的存储位置数据库中只存放视图的定义而不存放视图对应的数据，数据仍然存放在导出视图的基本表中3.2数据定义数据定义功能包括定义基本表、视图和索引操作对象操作方式创建删除修改表CREATTABLEDROPTABLEALTERTABLE视图CREATVIEWDROPVIEW索引CREATINDEXDROPINDEX准备工作：数据类型数据类型用于给特定的列提供数据规则，不同的DBMS支持的数据类型不同，有细微的差别。SQL提供的主要类型：数值型、字符型、位串型、日期型T-SQL数据类型T-SQL中支持的、常用的数据类型字符型日期时间类型数值类型二进制型位型文本型图像型时间戳型非unicod字符型固定长度或可变长度的字符数据类型允许定义的长度是1-8000有两种固定长度的char(10)类型可变长度的varchar()类型输入字符型数据时必须用单引号将数据括起来，例：’hello’unicod字符型使用UNICODEUCS-2字符集定义值在1到4,000之间（含）。存储大小为两倍n字节。有两种固定长度的nchar()类型可变长度的nvarchar()类型输入字符型数据时格式，例：N’hello’日期时间类型datetime和smalldatetime是用来存储日期和时间数据比较内容DatetimeSmalldatetime最小值Jan1,1753Jan1,1900最大值Dec31,9999Jun6,2079占用存储空间8byte4byte精度3.33毫秒1分钟整数类型整数类型的比较比较内容IntSmallintTinyint最小值-231-2150最大值231-1215-1255占用存储空间4byte2byte1byte浮点数类型比较内容FloatReal最小值-1.79E+308-3.40E+38最大值1.79E+3083.40E+38占用存储空间8Byte4Byte精度最多15位最多7位精确数类型精确数类型有两种形式DecimalNumeric与浮点类型数据的区别可以自定义精度的位数例num_col(5,2)货币型货币类型的比较比较内容MoneySmallmoney最小值-922,337,203,685,477.5808-214,748.3648最大值922,337,203,685,477.5807214,748.3647占用存储空间8Byte4Byte精度小数点后4位小数点后4位位数据类型位数据类型bit是一种逻辑数据类型只有1和0两种数值一般常用作true/false使用定义bit列时，不允许为NULL，也不能建立索引多个bit列可占用一个字节文本型和图像型文本类型text图像类型image每行可以存储2GB的二进制大型对象时间戳型时间戳型表示Timestamp由系统自动赋值的一个计数器数据记录了数据行的操作顺序时间戳列的数据保持惟一，它可以惟一标识表中的列时间戳列的值实际来自于事务日志语句格式：CREATETABLE表名(列名数据类型[列级完整性约束条件][,列名数据类型[列级完整性约束条件]…][,表级完整性约束条件）；1、定义基本表CREATETABLE列和表完整性约束条件约束类型说明Notnull防止NULL值进入该列unqiue防止重复值进入该列Default默认约束，将该列常用的值定义为缺省值，减少数据输入Check检查约束，通过约束条件表达式设置列值应满足的条件Primarykey要求进入该列的所有值是唯一的，且不为NULLForeignkey表间约束列约束或表约束，分别取决于否应用于一个或多个列例1创建无约束的表结构建立学生表student，由学号、姓名、性别、年龄、系别5个属性组成。CREATETABLEstudent(Snochar(8),Snamechar(20),Ssexchar(1)Sageint,Sdeptchar(20))创建有约束的表结构Createtablestudent(snochar(8)primarykey,snamechar(8)notnullunique,ssexchar(2)default'男'check(ssex='男'orssex='女'),sagetinyintcheck(sagebetween15and50),sdeptchar(20))T-SQL中具体创建约束时，分为命名约束与自命名约束，自命名约束不需要constraint关键字，直接在字段定义后创建，命名约束需要constraint关键字，对约束命名，位置在字段定义结束后。创建有约束的表结构createtablesc(snochar(8),cnochar(2),gradetinyint,constraintpk_scprimarykey(sno,cno),constraintfk_stuidforeignkey(sno)referencesstudent(sno),constraintfk_courseforeignkey(cno)referencescourse(cno),constraintck_gradecheck(grade=0andgrade=100))注意：教材是标准SQL99语法，和具体的DBMS的语法有区别语句格式ALTERTABLE表名[ADD新列名数据类型[完整性约束]][ADDconstraint[完整性约束]][DROPconstraint完整性约束][DROPCOLUMN列名]--T-SQL[ALTERcolumn列名数据类型]--T-SQL修改内容增加新列和新的完整性约束条件（ADD子句）删除指定的完整性约束条件（DROP子句）修改原有的列定义（ALTER子句）2、修改基本表ALTERTABLE例2向Student表中增加“入学时间”列，其数据类型为日期型。ALTERTABLEStudentADDScomeDATEtime;例3将Student表中年龄的数据类型改为半字长整数。ALTERTABLEStudentALTERSageSMALLINT;例4删除sc表中成绩取值限制的约束ALTERTABLEStudentDROPconstraintck_grade;语句格式DROPTABLE表名例5删除Student表DROPTABLEStudent说明：删除基本表定义时，表中的数据、在此表上建立的索引将同时被删除；此表上的视图不被删除，但已无法使用。3、删除基本表DROPTABLE4、索引表的索引就是表中数据的目录索引基于表中的列创建，可以基于不同的列创建多个索引在进行数据查询时，如果不使用索引，就需要将数据文件分块，逐个读到内存中进行查找比较操作。如果使用索引，可先将索引文件读入内存，根据索引项找到元组的地址，然后再根据地址将元组数据读入内存，并且由于索引文件中只含有索引项和元组地址，文件很小，而且索引项经过排序，索引可以很快的读入内存并找到相应元组地址，极大地提高查询的速度。使用索引可保证数据的唯一性。在索引的定义中包括了数据唯一性的内容。使用索引可加快连接速度索引的分类聚簇/聚集索引索引树的叶级页包含实际的数据；表的物理顺序与记录的索引顺序相同。非常类似于目录表，目录表的顺序与实际页码顺序。一个表只能有一个聚簇索引。非聚簇/非聚集索引记录的物理顺序与逻辑顺序没有必然关系，一个表也可以同时有多个非聚簇索引，类似于一本书同时有主题索引和作者索引。Sqlserver在创建主键是会根据主键自动创建聚簇索引，表中数据的物理顺序要重新排列，和聚簇索引的顺序保持一致。SQLServer在某个列上建立unique约束时，会根据该列自动创建唯一索引。语句格式CREATE[UNIQUE][CLUSTER]INDEX索引名ON表名(列名[次序][,列名[次序]]…);UNIQUE表示每一个索引值只对应唯一的数据记录CLUSTER聚簇索引：索引项的顺序与表中记录的物理顺序相一致相关说明索引可以提高数据检索速度索引可以保证行的唯一性。索引会增加系统的开销（空间、速度）索引由系统自动使用和维护建立索引CREATEINDEX例6为学生-课程数据库中Student，Course，SC三个表建立索引。Student表按姓