第4章 关系数据库标准语言SQL6

HuanghuaiUniversityInstituteofInformationEngineering数据库系统概论AnIntroductiontoDatabaseSystems黄淮学院信息工程学院张银玲第4章关系数据库标准语言SQL基本要求：了解并熟练掌握关系数据库标准语言SQL，包括数据定义、数据操纵及数据控制。重点：使用SQL语言完成对数据库的查询、插入、删除、更新操作难点：用SQL语言正确的完成各类简单、连接及复合查询4.1SQL概述4.2SQL数据定义4.3SQL数据查询4.4SQL数据更新4.5安全性控制4.6嵌入式SQL概述4.7视图第4章关系数据库标准语言SQL概述SQL（StructuredQueryLanguage）是1974年由Boyde和Chamberlin提出的1974年IBM的SystemR1979年Oracle1982年IBM的DB21984年Sybase1986年10月ANSI公布SQL-86标准1989年ISO公布SQL-89标准（120页）1992年公布SQL-92标准（622页）1999年公布SQL-99标准（1700页）2003年公布SQL-2003标准（3600页）SQL语言的特点综合统一集DDL、DML、DCL为一体实体和联系都是关系，因此每种操作只需一种操作符高度非过程化面向集合的操作方式以同一种语法结构提供两种使用方式（交互式和嵌入式）语言简捷，易学易用数据定义CREATE、DROP、ALTER数据查询SELECT数据更新INSERT、UPDATE、DELETE数据控制GRANT、REVOKESQL语言的特点支持三级模式结构视图外模式基本表（的集合）模式存储文件和索引内模式SQL数据库体系结构用户2用户3用户4视图V1视图V2基本表B1基本表B2基本表B3基本表B4存储文件S1存储文件S2存储文件S3存储文件S4用户1SQL用户视图基本表存储文件4.1SQL概述4.2SQL数据定义4.3SQL数据查询4.4SQL数据更新4.5安全性控制4.6嵌入式SQL概述4.7视图第4章关系数据库标准语言SQLSQLServer数据库文件逻辑存储结构和物理存储结构一、数据库的逻辑存储结构指的是数据库是由哪些性质的信息所组成，SQLServer的数据库不仅仅只是数据的存储，所有与数据处理操作相关的信息都存储在数据库中。二、数据库的物理存储结构则是讨论数据库文件是如何在磁盘上存储的，数据库在磁盘上是以文件为单位存储的，由数据库文件和事务日志文件组成，一个数据库至少应该包含一个数据库文件和一个事务日志文件。数据库文件数据库文件是存放数据库数据和数据库对象的文件一个数据库可以有一个或多个数据库文件，当有多个数据库文件时，有一个文件被定义为主数据库文件，扩展名为mdf它用来存储数据库的启动信息和部分或全部数据，一个数据库只能有一个主数据库文件其它数据库文件被称为次数据库文件，扩展名为ndf用来存储主文件没存储的其它数据采用多个数据库文件来存储数据的优点体现在(1)数据库文件可以不断扩充而不受操作系统文件大小的限制(2)可以将数据库文件存储在不同的硬盘中这样可以同时对几个硬盘做数据存取提高了数据处理的效率对于服务器型的计算机尤为有用日志文件是用来记录数据库更新情况的文件扩展名为ldf，例如使用INSERTUPDATEDELETE等对数据库进行更改的操作，都会记录在此文件中，而如SELECT等对数据库内容不会有影响的操作则不会记录在案。一个数据库可以有一个或多个事务日志文件,SQLServer有个特点它在执行数据更改时会设置一个开始点和一个结束点如果尚未到达结束点就因某种原因使操作中断则在SQLServer重新启动时会自动恢复已修改的数据使其返回未被修改的状态由此可见当数据库破坏时可以用事务日志恢复数据库内容日志文件SQLServer数据库文件组为了便于分配和管理，SQLServer允许将多个文件归纳为同一组，并赋予此组一个名称，这就是文件组。文件组是将多个数据库文件集合起来形成的一个整体，文件组分为主文件组、用户自定义文件组和默认文件组。一个文件只能存在于一个文件组中，一个文件组也只能被一个数据库使用。主文件组中包含了所有的系统表，当建立数据库时主文件组包括主数据库文件和未指定组的其它文件。在创建数据库对象时，如果没有指定将其放在哪一个文件组中，就会将它放在默认文件组中，如果没有指定默认文件组则主文件组为默认文件组说明：日志文件不属于任何文件组。使用Transact-SQL语言创建数据库，语法如下：CREATEDATABASEdatabase_name[ON[PRIMARY]([NAME=logical_file_name，]FILENAME=‘os_file_name’[，SIZE=size][，MAXSIZE={max_size|UNLIMITED}][，FILEGROWTH=growth_increment])[，…n]filegroupspec::=FILEGROUPfilegroup_namefilespec[，…n]Logon([NAME=logical_file_name，]FILENAME=‘os_file_name’[，SIZE=size][，MAXSIZE={max_size|UNLIMITED}][，FILEGROWTH=growth_increment])database_name：数据库的名称，最长为128个字符。PRIMARY：该选项是一个关键字，指定主文件组中的文件。LOGON：指明事务日志文件的明确定义。NAME：指定数据库的逻辑名称，这是在SQLServer系统中使用的名称，是数据库在SQLServer中的标识符。各参数说明如下：FILENAME：指定数据库所在文件的操作系统文件名称和路径，该操作系统文件名和NAME的逻辑名称一一对应。SIZE：指定数据库的初始容量大小。MAXSIZE：指定操作系统文件可以增长到的最大尺寸。FILEGROWTH：指定文件每次增加容量的大小，当指定数据为0时，表示文件不增长。例:创建了一个Company数据库创建了一个Company数据库，该数据库的主数据文件逻辑名称为Company_data，物理文件名为Company.mdf，初始大小为10MB，最大尺寸为无限大，增长速度为10%；数据库的日志文件逻辑名称为Company_log，物理文件名为Company.ldf，初始大小为1MB，最大尺寸为5MB，增长速度为1MB。createdatabasecompanyonprimary(name=company_data,filename='d:\sql\data\company.mdf',size=10,maxsize=unlimited,filegrowth=10%)logon(name=company_log,filename='d:\sql\data\company.ldf',size=1,maxsize=5,filegrowth=1)例：创建一个包含自定义文件组的数据库。该数据库名称为company，有1个10MB和2个20MB的数据文件和2个10MB的事务日志文件。数据文件逻辑名称为company1、company2和company3，物理文件名为company1.mdf、company2.ndf和company3.ndf。主文件是company1，由primary指定，次数据文件company2和company3由自定义文件组mygroup1指定。三个数据文件的最大尺寸分别为300MB、200MB和100MB，增长速度分别为20%、15%和3MB。事务日志文件的逻辑名为company1和company2，物理文件名为company1.ldf和company2.ldf，最大尺寸均为50MB，文件增长速度为5MB。具体SQL代码如下：createdatabasecompanyonprimary(name=company1_data,filename='f:\sql\data\company1.mdf',size=10,maxsize=300,filegrowth=20%),filegroupmygroup1(name=company2_data,filename='f:\sql\data\company2.ndf',自定义文件组注意此处有逗号size=20,maxsize=200,filegrowth=10%),(name=company3_data,filename='f:\sql\data\company3.ndf',size=20,maxsize=100,filegrowth=3)logon(name=company1_log,filename='f:\sql\data\company1.ldf',size=10,maxsize=50,filegrowth=5),(name=company2_log,filename='f:\sql\data\company2.ldf',size=10,maxsize=50,filegrowth=5)SQL的数据定义定义和修改基本表（定义模式中的关系）：CREATETABLEDROPTABLEALTERTABLE定义视图（定义外模式）：CREATEVIEWDROPVIEW定义索引（定义内模式）：CREATEINDEXDROPINDEX基本表的定义基本格式CREATETABLE表名（列名数据类型[列级完整性约束条件][，列名数据类型[列级完整性约束条件]]…[，表级完整性约束条件]）；表名：所要定义的基本表的名字列名：组成该表的各个属性（列）列级完整性约束条件：涉及相应属性列的完整性约束条件表级完整性约束条件：涉及一个或多个属性列的完整性约束条件[例1]建立一个“学生”表Student，它由学号Sno、姓名Sname、性别Sex默认值为男、年龄Birth、所在系Dept五个属性组成。其中学号不能为空，值是唯一的，并且姓名取值也唯一。CREATETABLEStudent(SnoCHAR(5)NOTNULLUNIQUE，SnameCHAR(20)UNIQUE，SexCHAR(2)DEFAULT(‘男’)，BirthINT，DeptCHAR(15))；基本表的定义基本表的定义说明：SQL支持空值的概念。允许空值的列未输入数据时系统自动置为空值。SQL支持的数据类型随系统不同而有所差异整数bigint:从-263(-9223372036854775808)到263-1(9223372036854775807)的整型数据（所有数字）。int:从-231(-2,147,483,648)到231-1(2,147,483,647)的整型数据（所有数字）。smallint:从-215(-32,768)到215-1(32,767)的整数数据。tinyint:从0到255的整数数据。bit、decimal和numericbit:1或0的整数数据。decimal:从-1038+1到1038–1的固定精度和小数位的数字数据。numeric:功能上等同于decimal。money和smallmoneymoney:货币数据值介于-263(-922,337,203,685,477.5808)与263-1(+922,337,203,685,477.5807)之间，精确到货币单位的千分之十。MSSQLServer支持数据类型smallmoney:货币数据值介于-214,748.3648与+214,748.3647之间，精确到货币单位的千分之十。近似数字float:从-1.79E+308到1.79E+308的浮点精度数字。real:从-3.40E+38到3.40E+38的浮点精度数字。时间日