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

第三章关系数据库标准语言SQLStructuredQuerylanguage3.1SQL概述3.1.1SQL的发展1974年提出，在SYSTEMR上实现1986年10月，ANSI定为关系数据库语言的美国标准，并公布了标准SQL92年通过的修改标准SQL---921999年---SQL99标准2003年---SQL2003标准SQL发展SQL-86：第一个SQL标准，由美国国家标准局(AmericanNationalStandardInstitute，简称ANSI)公布，1987年国际标准化组织(InternationalOrganizationforStandardization，简称ISO)通过。该标准也称为SQL-1SQL-92：在1992年，由ISO和ANSI对SQL-86进行重新修订，该标准也称为SQL-2SQL-99：在1999年，该版本在SQL-2的基础上，扩展了诸多功能，包括递归、触发、面向对象技术等。该标准也称为SQL-3SQL-2003：该标准也称SQL-4，于2003年发布3.1.2SQL的特点1、高度非过程化2、功能完备且一体化3、统一的语法结构4、语言简洁，易学易用功能动词数据库查询SELECT数据定义CREATE，DROPALTER数据操纵INSERT，UPDATE，DELETE数据控制GRANT，REVOKE3.1.3SQL体系结构SQL视图1视图2基本表1存储文件1基本表2基本表3基本表4存储文件2外模式模式内模式用户基本表（BaseTable）。简称基表，是数据库中实际存在的关系。视图是从一个或几个基表导出的表，它本身不实际存储在数据库中，只存放对视图的定义信息（没有对应的数据）。因此，视图是一个虚表或虚关系，而基表是一种实关系存储文件。每个基表对应一个存储文件，一个基表还可以带一个或几个索引，存储文件和索引一起构成了关系数据库的内模式。学生-课程数据库学生学号姓名性别出生年份籍贯学院090101王英女1989河北计算机090102王小梅女1990江苏信电090103张磊男1990江苏计算机090104孙鹏男1991河南计算机090105李晓斌男1991河南信电090106钱序男1989湖北外文课程课程号课程名学时开课学期课程性质180101数据结构722必修180102操作系统484必修180103数据库原理564必修学习学号课程号成绩090101180101700901011801028509010218010280090103180103783.2SQL的定义功能3.2.1基本表的定义1、表结构的定义1)定长和变长字符串CHAR（n)VARCHAR(n)2)定长和变长位串BIT（n）BITVARING（n）3)整型数INTERGERSMALLINT(16位字长）4)定点数DECIMAL(i,j)4)浮点数FLOATDOUBLEPRECISION5)日期型DATE6)时间型TIME基本表的定义语句格式:CREATETABLE表名（列名1类型１[列级完整性约束条件][,列名2类型2[列级完整性约束条件]]…[,表级完整性约束条件]);列级完整性约束：1）“NOTNULL”的属性的值不允许为空值2）“UNIQUE”表示该属性上的值不得重复；CREATETABLE学生(学号CHAR(8)NOTNULLUNIQUE,姓名CHAR(8),性别CHAR(2),出生年份DATETIME,籍贯CHAR(8),学院CHAR(15));2、主关键字的定义1）在列出关系模式的属性时,在属性及其类型后加上保留字PRIMARYKEY，2）在列出关系模式的所有属性后，再附加一个声明：PRIMARYKEY（属性1[，属性2，…]）说明：如果关键字由多个属性构成，则必须使用第二种方法。CREATETABLE学生(学号CHAR(8)PRIMARYKEY,姓名CHAR(8),性别CHAR(2),出生年份DATETIME,籍贯CHAR(8),学院CHAR(15));CREATETABLE学生(学号CHAR(8),姓名CHAR(8),性别CHAR(2),出生年份DATETIME,籍贯CHAR(8),学院CHAR(15),PRIMAEYKEY(学号));CREATETABLE课程(课程号CHAR(8),课程名CHAR(15),学时SMALLINT，开课学期DATETIME,课程性质CHAR(15),先修课程号CHAR(8),PRIMAEYKEY(课程号));CREATETABLE学习(学号CHAR(8),课程号CHAR(8),成绩SMALLINT,PRIMAEYKEY(学号,课程号));3、外部关键字的定义1）如果外部关键字只有一个属性，可以在它的属性名和类型后面直接用“REFERENCES”说明它参照了某个表的某些属性，其格式为：REFERENCES表名（属性）2）在CREATETABLE语句的属性列表后面增加一个或几个外部关键字说明，其格式为：FOREIGNKEY（属性）REFERENCES表名（属性）CREATETABLE学习(学号CHAR(8),课程号CHAR(8),成绩SMALLINT,PRIMAEYKEY(学号,课程号),FOREIGNKEY(学号)REFERENCES学生(学号),FOREIGNKEY(课程号)REFERENCES课程(课程号));3.2.2基本表的修改和删除ALTERTABLE表名[ADD新列名数据类型[完整性约束]]|[DROP完整性约束名COLUMN列名|[ALTERCOLUMN列名类型]；例：将课程表中“课程名”的长度改为20。ALTERTABLE课程ALTERCOLUMN课程名CHAR(20)例：在表学生中增加属性“入学时间”，类型为日期型。ALTERTABLE学生ADDS_entrancedatetime删除表的操作语句格式为：DROPTABLE表名例：增加课程名称必须取唯一值的约束条件ALTERTABLECourseADDUNIQUE(Cname)索引的定义索引是加快数据检索的一种工具–一张表可以建立多个索引，可从不同的角度加快查询速度；–如果索引建立得较多，会给数据维护带来较大的系统开销。索引是由搜索码值，指针的记录构成–索引逻辑上按照搜索码值进行排序，但不改变表中记录的物理顺序；–索引和基本表分别存储。如在班级表中按所属学院建立的索引InstituteIdx，它与Class表之间的关系可以用图来表示：索引的类型有聚簇、非聚簇、唯一这几种，非聚簇索引就是普通索引，一张表可以建立多个普通索引。每张表仅能建立一个聚簇索引–聚簇索引是按搜索码值的某种顺序(升序/降序)来重新组织表记录，即索引的顺序就是表记录存放的顺序；1、建立索引CREATE[UNIQUE][CLUSTERED|NONCLUSTERED]INDEX索引名ON表名(列名[次序][,列名[次序]…])中：UNIQUE：表示建立唯一索引；CLUSTERED|NONCLUSTERED：表示建立聚簇或非聚簇索引，默认为非聚簇索引；indexName：索引的名称，索引是数据库中的对象，因此在一个数据库中必须唯一；tableName(columnName1[ASC|DESC],columnName2[ASC|DESC],…)：指出为哪个表的哪些属性建立索引[ASC|DESC]为按升序还是降序建立索引，默认为升序；3.2.3索引的建立和删除例：CREATEINDEXidx_snameON学生(姓名);CREATEUNIQUEINDEXidx_sno_cnoON学习(学号asc,课程号desc);2、删除索引DROPINDEX索引名例：DROPINDEXidx_snameonStudent3.2.3索引的建立和删除一个表如果建有大量索引会影响INSERT、UPDATE和DELETE语句的性能；对小型表进行索引可能不会产生优化效果。下面的情况不建立索引：•从来不或者很少在查询中引用的列。•只有两个或者若干个值的列，例如性别（男或女）。•记录数目很少的表。基本语句格式：SELECT[ALL|DISTINCT]属性列表FROM基本表（或视图）[WHERE条件表达式][GROUPBY列名][HAVING条件表达式][ORDERBY列名[ASC|DESC]];3.3数据查询SQL基本结构包括3个子句：–SELECT子句对应投影运算，指定查询结果中所需要的属性或表达式–FROM子句对应笛卡尔积，给出查询所涉及的表，表可以是基本表、视图或查询表–WHERE子句对应选择运算，指定查询结果元组所需要满足的选择条件SELECT和FROM是必须的，其他是可选的单表查询：涉及一个表的查询。(1)选择表中的若干列（投影）(2)选择表中的若干元组（选择）(3)对查询分组(4)使用集函数(5)对查询结果排序3.3.1单表查询【例】在学生表中找出全体学生的所有信息。SELECT*FROM学生【例】在学生表中找出全体学生的姓名和籍贯。SELECT姓名,籍贯FROM学生【例】在学生表中找出全体学生的姓名和年龄。SELECT姓名,2010-出生年份FROM学生【例】给计算字段定义别名。SELECT姓名,2011-出生年份as年龄FROM学生查询条件谓词比较=,,,=,=,!=,,!,!,确定范围BETWEENAND，NOTBETWEENAND确定集合IN，NOTIN字符匹配LIKE，NOTLIKE空值ISNULL，ISNOTNULL多重条件AND，OR【例】查找有不及格门次的学生学号。SELECTDISTINCT学号FROM学习WHERE成绩60【例】查询所有年龄在20岁以下的学生姓名及年龄。SELECT姓名,2010-出生年份as年龄FROM学生WHERE2010-出生年份20【例】查询年龄在18-22岁（包括18岁和22岁）之间的学生的姓名、年龄和学院。SELECT姓名,2010-出生年份,学院FROM学生WHERE2010-出生年份BETWEEN18AND22;【例】在表学生中找出计算机学院或机电学院的学生信息。SELECT姓名,出生年份,学院FROM学生WHERE学院IN(‘计算机’,‘机电’)SELECT姓名,出生年份,学院FROM学生WHERE学院=‘计算机‘OR学院='机电';【例】查找DB_Design课程的课程号和课程名SELECT课程号,课程名FROM课程WHERE课程名LIKE‘DB\_Design’ESCAPE’\’【例】查询所有姓王的学生的所有情况。SELECT*FROM学生WHERE姓名like‘王*';%(*)代表：任意长度的字符串_(?)代表:任意单个字符【例】查询以“DB_”开头，倒数第三个字符为i的课程的详细信息SELECT*FROM课程WHERE课程名like‘DB\_%i__’escape‘\’;【例】查找没有成绩的学生学号及相应课程号SELECT学号,课程号FROM学习WHERE成绩ISNULL【例】查询各门课程的选课人数。SELECT课程号,COUNT(学号)as选课人数FROM学习GROUPBY课程号对查询结果分组【例】查询选修了2门以上课程的学生学号。SELECT学号FROM学习GROUPBY学号HAVINGCOUNT(课程号)1【例】查询1号课程的平均成绩。SELECTAVG(成绩)FROM学习WHERE课程号='1'聚簇函数：COUNTSUMAVGMAXMIN使用集函数【例】查询学生总人数。SELECTCOUNT(*)as总人数FROM学生【例】查询选修了2号课程的学生学号和成绩，查询结果按成绩从大到小排列。SELECT学号,成绩FROM学习WHERE课程号='2'ORDERBY成绩DESC对查询结果排序【例】查询学号在1-4之间至少选修了两门课程的学生学号及其选修的课程数,并对课程数降序排