第3章关系数据库标准语言SQL

问题：1、统计各门课的平均分、最高分、最低分，列出课程号、平均分、最高分、最低分。2、查询选修人数不足5人的课程，列出课程号、人数3、查询所有学生的选课情况，列出学号、姓名、课程号、课程名、成绩、学分。4、查询选修2号课程且成绩在90分以上的所有学生的学号、姓名、成绩。5、查询同时选修了课程号为“1”和“2”的学生学号。12第三章关系数据库标准语言SQL本章主要内容：1SQL概述2SQL的数据定义功能3SQL的数据查询功能4SQL的数据操纵功能5视图3SQL是什么？SQL是一个通用的、功能极强的关系数据库的标准语言。SQL语言是介乎于关系代数和元组演算之间的一种结构化查询语言。发展历史1972年，IBM公司开始研制实验型关系数据库管理系统SYSTEMR，配制的查询语言称为SQUARE(SpecifyingQueriesAsRelationalExpression)语言，在语言中使用了较多的数学符号。在1974年提出，Boyce和Chamberlin把SQUARE修改为SEQUEL(StructuredEnglishQUEryLanguage)语言。后来SEQUEL简称为SQL(StructuredQueryLanguage)，即“结构式查询语言”，SQL的发音仍为“sequel”。现在SQL已经成为一个标准。SQL标准的进展过程参见P76表3.1。没有一个数据库系统能够支持SQL标准的所有概念和特性，在使用具体系统时要查阅各产品的用户手册。3.1SQL概述41.综合统一集数据定义语言（DDL），数据操纵语言（DML），数据控制语言（DCL）和数据查询功能于一体，能独立完成数据库的全部操纵。定义关系模式，插入数据，建立数据库；对数据库中的数据进行查询和更新；数据库重构和维护数据库安全性、完整性控制等非关系模型的数据语言:数据定义、数据操纵分开的模式数据定义语言（模式DDL）；外模式数据定义语言（外模式DDL或子模式DDL）；与数据存储有关的描述语言（DSDL）数据操纵语言（DML）SQL语言的特点52.高度非过程化是一种第四代语言（4GL），用户只需提出“做什么”即只要提出操作要求，而不必指明“怎么做”即不需指出操作的过程和存取路径。存取路径的选择以及SQL语句的操作过程由系统（DBMS）自动完成。大大减轻了用户负担，而且有利于提高数据独立性。3.面向集合的操作方式SQL语言采用集合操作方式操作对象、查找结果可以是元组的集合一次插入、删除、更新操作的对象可以是元组的集合非关系数据模型采用的是面向记录的操作方式，操作对象是一条记录。64.以同一种语法结构提供两种使用方式自含式语言交互式：即用户直接在终端键盘上键入SQL命令向DBMS提出对数据库的操作请求，DBMS接受用户的操作请求，并在执行完后立即把结果显示给用户。嵌入式语言将SQL语句嵌入在由其他语言（例如C、Java等）编写的程序中，使SQL语句随程序的执行而执行。这样，用户就不需要在每次使用数据库时都去写SQL语句了，而只要利用应用程序提供的功能进行相应的操作就可以了。两种不同使用方式下，SQL语言的语法结构基本一致。7表3.1SQL语言的动词SQL功能动词数据定义CREATE，DROP，ALTER数据查询SELECT数据操纵INSERT，UPDATEDELETE数据控制GRANT，REVOKE5.语言简洁，易学易用——SQL的核心动词(9个)8数据定义功能可以定义基本表、视图和索引的创建和撤销数据查询功能所谓数据查询就是从数据库所保存的众多数据中挑选出符合某种条件的一部分数据，或者将这些数据挑出来之后再进行适当的运算然后再得到某种汇总信息。数据查询的对象可以是基本表，也可以是视图。查询功能是由SELECT语句来完成的。数据更新功能是指对数据库中的对象（基本表和视图）进行插入、删除和修改等操作。对应语句Insert、Delete、Update数据控制功能主要包括对基本表和视图的存取权限控制、对基本表的完整性规则的描述和事务控制等功能。涉及的语句有GRANT，REVOKE、Commit和Rollback等SQL的功能可分为四部分：9SQL视图1视图2基本表1基本表2基本表3基本表4存储文件1存储文件2外模式模式内模式SQL语言支持关系数据库的三级模式结构103.2学生课程数据库本章以学生课程数据库来讲解SQL的数据定义、数据查询和数据操纵语句。11学生课程数据库的ERD设计课程PK课程号课程名称先行课学分学生PK学号姓名性别年龄所属系选修课程PK,FK1学号PK,FK2课程号12学号Sno姓名Sname性别Ssex年龄Sage所在系Sdept200215121李勇男20CS200215122刘晨女19IS200215123王敏女18MA200215125张立男19IS(a)Student13(b)Course课程号课程名先行课学分CnoCnameCpnoCcredit1数据库542数学23信息系统144操作系统635数据结构746数据处理27PASCAL语言6414(c)SC学号课程号成绩SnoCnoGrade200215121192200215121285200215121388200215122290200215122380153.3数据定义SQL的数据定义功能，包含：数据库定义定义表（模式）创建表、删除表、修改表定义定义视图（外模式）创建视图、删除视图、间接修改视图定义：删除+创建定义索引（内模式）创建索引、删除索引、修改索引定义注：SQL标准不提供修改模式定义和修改视图定义的操作。参见教材P80表3.316补充：创建、删除数据库创建数据库CREATEDATABASE数据库名删除数据库DROPDATABASE数据库名173.3.1基本表的定义、删除与修改表结构的设计-确定表的各个属性-确定每个属性域（取值范围）定义表的属性时需要指明其数据类型及长度。一个属性选用哪种数据类型要根据实际情况来决定，比如某个列既可能包含字母也可能包含数值，那么就应该定为字符数据类型。一般要从两个方面来考虑：取值范围要做哪些运算18数据类型数据类型是数据的一种属性，表示数据所表示信息的类型。任何一种计算机语言都定义了自己的数据类型。当然，不同的程序语言都具有不同的特点，所定义的数据类型的各类和名称都或多或少有些不同。SQL标准支持多种数据类型，有数字、字符、日期和时间、二进制以及其他数据类型。参见教材P83表3.41920(1)字符数据类型：用于存储固定长度或可变长度的字符数据。字符数据是由任何字母、符号和数字任意组合而成的数据，使用单引号引起来。字符数据的类型包括Char、Varchar、nVarchar、nchar、Text和nText等。Char(n)是用于存储固定长度的字符数据，长度是n个字节，其长度最多为8000个字节。Varchar(n)是存储变长字符数据，其长度不超过8000。超过8KB的ASCII数据可以使用Text数据类型存储。例如，因为Html文档全部都是ASCII字符，并且在一般情况下长度超过8KB，所以这些文档可以Text数据类型存储在SQLServer中。21一般地，在选择使用Char(n)或Varchar(n)数据类型时，可以按照下面的原则来进行判断：（1）如果该列存储的数据的长度都相同，这时应该使用Char(n)。如果该列中存储的数据的长度相差比较大，则应该考虑使用Varchar(n)。（2）如果存储的数据的长度虽然不是完全相同，但是长度差别不大，且希望提高查询的执行效率，可以考虑使用Char(n)；如果希望降低数据存储的成本，则可以考虑使用Varchar(n)。22（2）数字数据类型：整数存储的数据类型是bit（开关标记，0、1）、Bigint、Int、Smallint和Tinyint。Int（最常用的）存储每一个值要求4个字节存储空间，使用Smallint存储每一个值要求2个字节存储空间，使用Tinyint（超短整型）存储每一个值要求1个字节存储空间，Bigint长度是8字节。精确小数数据（带固定精度和位数）对应的数据类型是Decimal和Numeric。这种数据所占的存储空间根据该数据的位数后的位数来确定。近似小数数据（对数据精度要求并不严格）的数据类型是Float和Real，用于表示指定数的大致数据值的数据类型。例如，三分之一这个分数记作.3333333，当使用近似数据类型时能准确表示。23（3）日期和时间数据类型日期和时间数据类型包括Datetime和Smalldatetime两种类型时。差别在于其表示的日期和时间范围不同。Datetime所存储每一个值要求8个存储字节，表示的范围是1753年1月1日至9999年12月31日，使用Smalldatetime数据类型时，所存储每一个值要求4个存储字节，表示的范围是1900年1月1日至2079年12月31日。日期的格式可以设定。24（4）货币数据货币数据的数据类型是Money和SmallmoneyMoney数据类型要求8个存储字节，Smallmoney数据类型要求4个存储字节。与其他数据类型不同的地方：第一，表示货币值，可以在数字前面加上$作为货币符号；第二，小数位数最多是4。25CREATETABLE表名（列名数据类型[列级完整性约束][，列名数据类型[列级完整性约束]]…[，表级完整性约束]））;说明：表名：所要定义的基本表的名字列名：组成该表的各个属性（列）列级完整性约束条件：涉及相应属性列的完整性约束条件（涉及一个列的约束）表级完整性约束条件：涉及一个或多个属性列的完整性约束条件，若涉及多个属性列必须定义在表级上。通过约束保证数据完整性，即数据的一致性和准确性。一、定义基本表：就是创建表的结构26常用完整性约束主码约束：PRIMARYKEY。一个表中最多只能有一个主键，且主键列不允许空值。默认的主键约束是唯一性的聚集索引。唯一性约束：UNIQUE，唯一性约束一个字段的值不允许出现重复check检测：目的是限制字段的取值范围。当执行INSERT或者UPDATE语句时，该约束验证相应的数据是否满足CHECK约束的条件。default默认：目的是将一个字段最有可能出现的值设置为default约束。只能应用于INSERT语句。非空值约束：NOTNULLforeignkey外键（参照完整性）:目的是构建表间关系。不同属性列之间对应关系，可以来自同一个表内或表之间。27[例5]建立“学生”表Student，学号是主码，姓名取值唯一。CREATETABLEStudent(SnoCHAR(9)PRIMARYKEY，SnameCHAR(20)UNIQUE，SsexCHAR(2)，SageSMALLINT，SdeptCHAR(20))；系统执行上面的语句后，就在数据库中建立一个新的空的“学生”表Student，并将有关“学生”表的定义及有关约束条件存放在数据字典中。当用户操作表中数据时由RDBMS自动检查该操作是否违背这些完整性约束条件。数据字典：是关系数据库管理系统内部的一组系统表，它记录了数据库中所有的定义信息，包括关系模式定义、视图定义、索引定义、完整性约束定义、各类用户对数据库的操作权限、统计信息等。关系数据库管理系统在执行SQL的数据定义语句时，实际上就是在更新数据字典表中的相应信息。在进行查询优化和查询处理时，数据字典中的信息是其重要依据。282930练习：在学生数据库中建立一个基本信息表，所包含的字段及类型、宽度如下表，其中性别字段的取值只能“男”或“女”，且默认值为“男”。（问：再加个出生日期的约束？）31字段名类型宽度学号字符型13姓名字符型10性别字符型2出生日期日期型班级字符型12联系方式字符型20useddcreatetable基本信息(学号cha