第3章关系数据库标准语言SQL31-33

数据库系统概论AnIntroductiontoDatabaseSystem第3章关系数据库标准语言SQL第3章关系数据库标准语言SQL3.1SQL概述3.2学生－课程数据库3.3数据定义3.4数据查询3.5数据更新3.6视图SQL语言的前身是SEQUEL语言，是1974年由Boyce和Chamberlin提出的，作为SystemR上的查询语言。后改为SQL。1986年美国国家标准局ANSI将SQL语言作为数据库语言的美国标准，即SQL－86标准1987年国际标准化组织ISO将SQL作为数据库语言的国际标准。3.1.1SQL的产生与发展3.1.2SQL的特点综合统一高度非过程化面向集合的操作方式以同一种语法提供两种使用方式语言简洁，易学易用，9个动词SQL视图V1视图V2基表B1基表B2基表B3基表B4存储文件S1存储文件S2存储文件S3存储文件S4用户外模式模式内模式3.1.3SQL的基本概念3.1.3SQL的基本概念用户用SQL语言对基本表和视图进行操作基本表本身独立存在的表，一个关系对应一个表一个（或多个）基本表对应一个存储文件一个表可以带若干索引，索引也存放在存储文件中存储文件存储文件的逻辑结构组成了关系数据库的内模式存储文件的物理结构是任意的，对用户是透明的视图从一个或几个基本表或视图导出的表是虚表，只存放视图的定义而不存放对应数据3.2学生－课程数据库学生表：Student(Sno,Sname,Ssex,Sage,Sdept)课程表：Course(Cno,Cname,Cpno,Ccredit)学生选课表：SC(Sno,Cno,Grade)学号Sno姓名Sname性别Ssex年龄Sage所在系Sdept95001李勇男20CS95002刘晨女19IS95003王敏女18MA95004张立男19ISStudent3.2学生－课程数据库Course课程号课程名先行课学分CnoCnameCpnoCcredit1数据库542数学23信息系统144操作系统635数据结构746数据处理27PASCAL语言643.2学生－课程数据库SC学号课程号成绩SnoCnoGrade9500119295001285950013889500229095002380补：SQLServer数据库的基本管理与设置2.2.1启动和关闭数据库服务（1）使用自动服务系统（2）用SQLServer服务管理器启动SQLServer（3）从Windows应用程序启动SQLServer（4）从命令行启动SQLServer补：创建数据库1．数据库的存储结构数据库的逻辑存储结构：指的是数据库是由哪些性质的信息所组成。诸如表、视图、索引、约束、存储过程等各种不同的数据库对象数据库的物理存储结构：指的是数据库文件是如何在磁盘上存储的。一个数据库至少应该包含一个数据库文件和一个事务日志文件。存储数据的文件，扩展名为“mdf”存储数据库的更新情况等日志信息，扩展名为“ldf”补：创建数据库2、创建数据库在默认情况下，只有系统管理员和数据库拥有者可以创建数据库。SQL创建数据库的方法：（1）使用向导创建数据库（2）使用企业管理器创建数据库（3）使用Transact-SQL创建数据库补：创建数据库CREATEDATABASE数据库名[ON[PRIMARY][文件格式[，…n]]][LOGON{文件格式[，…n]}]文件格式::=([NAME=逻辑文件名，]FILENAME=‘操作系统下的物理路径和文件名’[，SIZE=文件初始大小][，MAXSIZE={文件最大大小|UNLIMITED}][，FILEGROWTH=增量值][，…n]（3）使用Transact-SQL创建数据库主文件日志文件补：创建数据库例2-1用createdatabase创建一个数据库，此数据库的名字为sm，其他选项均采用默认设置。createdatabasesm在默认位置创建数据库的主数据库文件－sm_Data.mdf。和数据库的事务日志文件－sm_Log.ldf。C:\programfiles\MicrosoftSQLServer\Mssql\Data数据文件和日志文件的初始大小为1MB，增长方式－自动增长；每次增长10％，最大大小无限制。补：创建数据库例2-2createdatabaseCompanyOnprimary(name=Company_data,filename='C:\SQL\Data\Company.mdf',size=10,maxsize=unlimited,filegrowth=10%)Logon(name=Company_log,filename='C:\SQL\Data\Company.ldf',size=1,maxsize=5,filegrowth=1)数据文件逻辑名物理文件名日志文件逻辑名日志文件物理名补：修改数据库使用ALTERDATABASE语句修改数据库语法格式如下：ALTERDATABASE数据库名{ADDFILE文件格式[,…n][TO文件组]|ADDLOGFILE文件格式[,…n]|REMOVEFILE日志文件名[withdelete]|MODIFYFILE文件格式|MODIFYNAME=新数据库名|ADDFILEGROUP文件组名|REMOVEFILEGROUP文件组名|MODIFYFILEGROUP文件组名{FILEGROUP_PROPERTYNAME=新文件组名}}补：修改数据库例2-3在sm数据库中添加一个数据文件sm_data1alterdatabasesmaddfile(name='sm_data1',filename='C:\SQL\Data\sm_data1.mdf',size=4,maxsize=unlimited,filegrowth=10%)GO补：修改数据库例2-4将sm数据库中的数据文件sm_data1的最大大小改为120MBalterdatabasesmmodifyfile(name='sm_data1',maxsize=120MB)GO补：设置数据库选项数据库的选项：一个数据库的特性。用sp_dboption系统存储过程设置数据库选项。系统存储过程sp_dboption查看数据库选项的基本语法：sp_dboption[‘数据库名’][‘配置选项名’][‘设置值’]更改其配置设置的数据库名选项名称取值：Authoshrink,Dbouseonly,Readonly,Singleuse取值：Ture,false或on,off补：设置数据库选项例2-5sp_dboption'sm','readonly','true'将sm数据库设置为只读例2-6sp_dboption'sm','readonly',‘false'将sm数据库设置为可写例2-7sp_dboption'sm',‘autoshrink','true'将sm数据库设置为可自动收缩补：删除数据库删除一个数据库时也删除了该数据库的全部对象，从而将其所占的磁盘空间全部释放掉。删除数据库的基本语法：DROPDATABASE数据库名[,...n]注：（1）删除数据库后应备份master数据库。（2）无法删除系统数据库（msdb,model,master,tempdb）（3）以下情况不能删除数据库：用户使用此数据库时数据库正在被恢复时数据库正在参与复制时dropdatabaseCompany删除数据库company3.3数据定义3.3.1SQL模式的定义与删除一、定义模式一个SQL模式定义为基本表的集合。一个SQL模式由模式名和模式拥有者的用户名或帐号来确定，并且包含模式中的每一个元素（基本表、视图、索引等）的定义。创建SQL模式就是定义了一个存储空间。SQL模式的创建可用CREATESCHEMA语句定义，其基本句法如下：CREATESCHEMA〈模式名〉AUTHORIZATION〈用户名〉3.3.1SQL模式的定义与删除二、删除模式DROP语句的句法如下：DROPSCHEMA〈模式名〉［CASCADE│RESTRICT］其方式有两种：CASCADE(级联式)方式。RESTRICT(约束式)方式。3.3.2基本表的定义、删除与修改一、定义基本表二、数据类型三、模式与表四、修改基本表五、删除基本表一、定义基本表语句格式CREATETABLE表名（列名数据类型[列级完整性约束条件][，列名数据类型[列级完整性约束条件]]…[，表级完整性约束条件]）；表名：所要定义的基本表的名字列名：组成该表的各个属性（列）列级完整性约束条件：涉及相应属性列的完整性约束条件表级完整性约束条件：涉及一个或多个属性列的完整性约束条件一、定义基本表常用完整性约束主码约束：PRIMARYKEY参照完整性约束唯一性约束：UNIQUE非空值约束：NOTNULL取值约束：CHECK一、定义基本表[例1]CREATETABLEStudent(SnoCHAR(9)PRIMARYKEY，SnameCHAR(20)UNIQUE，SsexCHAR(1)，SageINT，SdeptCHAR(15))；一、定义基本表CREATETABLECourse(CnoCHAR(4)PRIMARYKEY,CnameCHAR(40),CpnoCHAR(4),CreditSMALLINT,FOREIGNKEY(Cpno)REFERENCESCourse(Cno))一、定义基本表CREATETABLESC(SnoCHAR(5),CnoCHAR(3),Gradeint,Primarykey(Sno,Cno),FOREIGNKEY(Sno)REFERENCESStudent(Sno)FOREIGNKEY(Cno)REFERENCESCourse(Cno))二、数据类型整数型bigint数据类型：大整数型，长度为8个字节，可以存储－263(－9223372036854775808)～263－1(9223372036854775807)范围内的数字。int数据类型：整数型，长度为4个字节，可存储范围是－231(－2147483648)～231－1(2147483647)。smallint数据类型：短整数型，长度为2个字节，可存储范围只有－215(－32768)～215－1(32767)。tinyint数据类型：微短整数型，长度为1个字节，只能存储0-255范围内的数字。二、数据类型小数数据类型decimal[(p[,s])]numeric[(p[,s])]精度存储字节数1-9510-19920-281329-3817Precision精度（小数点两边的总位数）scale刻度（小数点右边的位数）SQLServer分配给这两个类型数据的存储空间随精度的不同而不同二、数据类型近似数值型float[(n)]从－1.79E＋308到1.79E＋308之间的浮点数字数据，n必须为从1到53之间的值n所在范围精度存储大小1～247位数4B25～5315位数8Breal从－3.40E＋38到3.40E＋38之间的浮点数字数据，存储大小为4B字符型char[(n)]：长度为n个字节的固定长度且非Unicode的字符数据，存储大小为n个字节。n必须是一个介于1和8000之间的数值。varchar[(n)]：长度为n个字节的可变长度且非Unicode的字符数据。n必须是一个介于1和8000之间的数值。存储大小为输入数据的字节的实际长度，而不是n个字节，所输入的数据字符长度可以为零。text数据类型：当要存储的字符类型数据非常庞大，以至于8000字节完全不够用时使用。用来声明变长且非unicode的字符数据。在定义过程中，不需要指定字符的长度。最大长度为231－1(2147483647)个字符，约2G。二、数据类型Unicode字符型当列中各项所包含的Unicode字符数不同时(至多为4000)