《2.3关系数据库的数据定义与操纵.ppt》由会员分享，可在线阅读，更多相关《2.3关系数据库的数据定义与操纵.ppt（44页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、2.3 2.3 关系数据库的数据定义与操纵关系数据库的数据定义与操纵关系数据库的数据定义与操纵关系数据库的数据定义与操纵1本本 节节 要要 点点uSQL语言简介语言简介u数据定义数据定义u数据查询数据查询uSQL视图视图2SQL语言的发展语言的发展19741981today1974年年 由由CHAMBERLIN和和BOYEE提出，当时称为提出，当时称为 SEQUEL(STUCTURED ENGLISH QUERY LANGUAGE)1970s,IBM公司对其公司对其进行了修改，并用于进行了修改，并用于其其SYSTEM R关系数关系数据库系统中据库系统中1981年年 IBM推出其商用关系推出其商
2、用关系关系数据库关系数据库SQL/DS，并将其，并将其名字改为名字改为SQL，由于，由于SQL语语言功能强大，简洁易用，因此言功能强大，简洁易用，因此得到了广泛的使用得到了广泛的使用今天广泛应用于各种大型今天广泛应用于各种大型数据库，如数据库，如SYBASE、INFORMIX、INGRES ORACLE、DB2等，也用等，也用于各种小型数据库，如于各种小型数据库，如FOXPRO、ACCESSSEQUEL3SQL是结构化查询语言（是结构化查询语言（Structured Query Language）的缩写，其功能包括的缩写，其功能包括SQL语言的特点语言的特点数据数据查询查询数据数据操纵操纵数据
3、数据定义定义数据数据控制控制SQL语言风格统一，可以独立完成数据库的全部操语言风格统一，可以独立完成数据库的全部操作。并且语言简洁、方便实用、功能齐全，已成为作。并且语言简洁、方便实用、功能齐全，已成为目前应用最广的关系数据库语言。目前应用最广的关系数据库语言。4SQL语言的特点语言的特点5SQL语语言言支支持持关关系系型型数数据据库库的的三三级级模模式式结结构构。其其中中，外外模模式式对对应应于于视视图图（View）和和部部分分基基本本表表（Base Table），模模式式对对应于基本表，内模式对应于存储文件。应于基本表，内模式对应于存储文件。2.3.1 SQL语言的数据定义语言的数据定义7
4、数数据据定定义义语语言言DDL用用于于执执行行数数据据定定义义的的操操作作，如如创创建建或删除表、索引和视图之类的对象或删除表、索引和视图之类的对象。1 定义（创建）表定义（创建）表【格式格式】CREATE TABLE （字段级完整性约束条件字段级完整性约束条件 字字段段级级完完整整性性约约束束条条件件 ）；）；【功能功能】定义（也称创建）一个表。定义（也称创建）一个表。数据定义数据定义8SQL语句格式的约定符号语句格式的约定符号语句格式中语句格式中,中的内容是必须的，是用户自定义中的内容是必须的，是用户自定义语义语义;为任选项为任选项 或或分隔符分隔符|表示必选项表示必选项,即必选其中之一项
5、即必选其中之一项,N表示前面得项可以重复多次表示前面得项可以重复多次是合法标识符，最多可有是合法标识符，最多可有128个字符，如个字符，如S,SC,C，不允许重名。，不允许重名。列名列名(字母开头，可含字母、数字、字母开头，可含字母、数字、$、_=128_=128字符字符)。同一表中不许有重名列。同一表中不许有重名列。91BYTES0至至255TINYINT2BYTES-215至至215-1SMALLINT4BYTES-231到到231-1INT0,1,NULLBIT占用的字节占用的字节数据内容与范围数据内容与范围数据类型数据类型各种数据类型的有关各种数据类型的有关规定规定如下表：如下表：实际
6、使用实际使用1BIT，但会，但会占用占用1BYTE，若一个，若一个数据中有数个数据中有数个BIT字字段，可共占段，可共占1个个BYTE10 数据类型数据类型数据内容与范围数据内容与范围占用的字节占用的字节DATETIME1753/1/1至至9999/12/318BYTESSMALLDATETIME1900/1/1至至2079/6/64BYTESCHAR1-8000个字符个字符1个个字字符符占占1B，尾尾端端空白字符保留空白字符保留VARCHAR1-8000个字符个字符1个个字字符符占占1B，尾尾端端空白字符删除。空白字符删除。TEXT231-1个字符个字符1个个字字符符占占2B，最最大大可存储
7、可存储2GB11 数据类型数据类型数据内容与范围数据内容与范围占用的字节占用的字节NUMERIC-1038-1至至1038-11-9位数使用位数使用5BYTES10-19位数使用位数使用9BYTES20-28位数使用位数使用13BYTES29-38位数使用位数使用17BYTESDECIMAL-1038-1至至1038-15-17BYTES因因长长度度而而异异，与与NUMERIC相同相同FLOAT-1.79E+306至至1.79E+308,最最多多可可表表示示53位数位数8BYTESREAL-3.40E+38到到3.40E+38，最最多多可可表表示示24位数位数4BYTES12【例例1】创创建建
8、一一个个表表STUD（学学生生信信息息表表），它它由由以以下下字字段段组组成成：学学号号（C，10）；姓姓名名（C，8）；性性别别（C，2）；班班级级名名（C，10）；系系别别代代号号（C，2）；地地址址（C，50）；出出生生日日期期（D）；是否团员是否团员（L）；）；备注备注（M）。）。CREATE TABLE STUD（学学号号C（10），姓姓名名 C（10），性性别别 C（2），班班级级名名 C（10）,系系别别代代号号 C（2）,地地址址 C（50）,出出生日期生日期 D,是否团员是否团员 L,备注备注 M,照片照片 G）;【例例2】创创建建一一个个表表SC（课课程程成成绩绩表表），它
9、它由由以以下下字字段段组组成成：学学号（号（C，10）；）；课程号（课程号（C，2）。）。CREATE TABLE SC（学号学号 C（10），），课程号课程号 C（2）;132 修改表修改表【格式格式】ALTER TABLE ADD （，）完完 整整 性性 约约 束束 NULL|NOT NULLDROP 完完整整性性约约束束MODIFY ；【功能功能】修改表结构。修改表结构。【说说明明】ADD子子句句用用于于增增加加指指定定表表的的字字段段变变量量名名、数数据据类类型型、宽宽度度和和完完整整性性约约束束条条件件；DROP子子句句用用于于删删除除指指定定的的的的完完整整性性约束条件；约束条件；
10、MODIFY子句用于修改原有的字段变量的值。子句用于修改原有的字段变量的值。【例例3】在课程成绩表在课程成绩表SC中，增加一个成绩字段变量（中，增加一个成绩字段变量（N，3）。）。ALTER TABLE SC ADD 成绩成绩 N(3);143 创建索引创建索引在日常生活中我在日常生活中我们们会会经经常遇到索引，例如常遇到索引，例如图书图书目目录录、词词典索引典索引等。等。借助索引，人借助索引，人们们会很快地找到需要的会很快地找到需要的东东西。西。索引是索引是数据数据库库随机随机检检索索的常用手段，它的常用手段，它实际实际上就上就是是记录记录的关的关键键字与其相字与其相应应地址的地址的对应对应
11、表。表。此外，在此外，在SQL SERVERSQL SERVER中，中，行的唯一性行的唯一性也是通也是通过过建建立唯一索引来立唯一索引来维护维护的。的。索引的索引的作用作用可归纳为：可归纳为：1.加快查询速度；加快查询速度；2.保证行的唯一性。保证行的唯一性。15建立索引的语句是建立索引的语句是CREATE INDEX其语法格式为：其语法格式为：CREATE UNIQUE CLUSTER INDEX ON (次序次序,次序次序)UNIQUE表明建立唯一索引。表明建立唯一索引。CLUSTER表示建立聚集索引。表示建立聚集索引。次序用来指定索引值的排列顺序，可为次序用来指定索引值的排列顺序，可为A
12、SC（升序）升序）或或DESC（降序），缺省值为降序），缺省值为ASC。3 创建索引创建索引【例例4】为表为表SC在在SNO和和CNO上建立唯一索引。上建立唯一索引。USE STUDENT CREATE UNIQUE INDEX SCI ON SC(SNO,CNO)1641 删除数据库删除数据库【格式格式】DROP DATABASE ;【功能功能】删除指定数据库的结构和数据。删除指定数据库的结构和数据。【说明说明】谨慎使用。谨慎使用。42 删除表删除表【格式格式】DROP TABLE ;【功能功能】删除指定表的结构和内容（包括在此表上建立的索引）。删除指定表的结构和内容（包括在此表上建立的索引
13、）。【说说明明】如如果果只只是是想想删删除除一一个个表表中中的的所所有有记记录录，则则应应使使用用DELETE语句。语句。17SQL语语言言使使用用数数据据定定义义语语言言（DATA DEFINITION LANGUAGE，简称简称DDL）实现其数据定义功能。实现其数据定义功能。操作操作对象对象操作对象操作对象创建创建删除删除修改修改表表Create tableDrop tableAlter table视图视图 Create view Drop view索引索引Create indexDrop index数据数据库库Create databaseDrop databaseAlter datab
14、ase182.3.2 SQL数据查询数据查询是数据库中最常见的操作。数据查询是数据库中最常见的操作。SQL语语言言提提供供SELECT语语句句，通通过过查查询询操操作作可可得得到到所需的信息。所需的信息。SELECT语句的一般格式为：语句的一般格式为：SELECT列名列名，列名列名FROM表名或视图名表名或视图名，表名或视图名表名或视图名WHERE检索条件检索条件GROUP BY HAVING ORDER BY ASC|DESC;19SELECT语句的一般格式：语句的一般格式：SELECT ALL|DISTINCT.，FROM！，INNER|LEFT|RIGHT|FULL JOIN！ON WH
15、ERE AND GROUP BY HAVING UNION ORDER BY ASC|DESC ;注意：注意：“”内的内容是内的内容是任选任选的，的，“”内的内容是内的内容是必选必选的；的；语句中若出现多个语句中若出现多个“|”分隔的子句，表示可以选择其中任一子句；分隔的子句，表示可以选择其中任一子句；各个子句之间最少需用一个空格隔开；各个子句之间最少需用一个空格隔开；一个一个SQL语句可以占用多行，但最后一行需用语句可以占用多行，但最后一行需用“；”结束（半角字符）结束（半角字符）20查询的查询的结果结果是仍是一个是仍是一个表表。SELECT语句的执行过程是语句的执行过程是:根根据据WHER
16、E子子句句的的检检索索条条件件，从从FROM子子句句指指定定的的基基本本表表或或视视图图中中选选取取满满足足条条件件的的元元组组，再再按按照照SELECT子句中指定的列，投影得到结果表。子句中指定的列，投影得到结果表。如如果果有有GROUP子子句句，则则将将查查询询结结果果按按照照相同的值进行分组。相同的值进行分组。如如果果GROUP子子句句后后有有HAVING短短语语，则则只只输输出出满满足足HAVING条件的元组。条件的元组。如如果果有有ORDER子子句句，查查询询结结果果还还要要按按照照的值进行排序。的值进行排序。21Student表表查询结果查询结果22例例1 查询全体学生的学号、姓名
17、和年龄。查询全体学生的学号、姓名和年龄。SELECT SNO,SN,AGE FROM S例例2 查询学生的全部信息。查询学生的全部信息。SELECT*FROM S用用*表示表示S表的全部列名，而不必逐一列出。表的全部列名，而不必逐一列出。例例3 查询选修了课程的学生号。查询选修了课程的学生号。SELECT DISTINCT SNO FROM SC查询结果中的重复行被去掉查询结果中的重复行被去掉上述查询均为不使用上述查询均为不使用WHERE子句的无条件查询，也称作子句的无条件查询，也称作投影投影查询查询。23另另外外，利利用用投投影影查查询询可可控控制制列列名名的的顺顺序序，并并可可通通过过指指
18、定定别名别名改变查询结果的列标题的名字。改变查询结果的列标题的名字。例例4 查询全体学生的姓名、学号和年龄。查询全体学生的姓名、学号和年龄。SELECT SNAME NAME,SNO,AGE FROM S其中，其中，NAME为为SNAME的别名的别名 24条件查询条件查询当当要要在在表表中中找找出出满满足足某某些些条条件件的的行行时时，则则需需使使用用WHERE子句子句指定查询条件。指定查询条件。WHERE子句中，条件通常通过三部分来描述：子句中，条件通常通过三部分来描述：1 列名；列名；2 比较运算符；比较运算符；3 列名、常数。列名、常数。运算符运算符含义含义=,=,85262-2 多重条
19、件查询多重条件查询当当WHERE子子句句需需要要指指定定一一个个以以上上的的查查询询条条件件时时，则则需需要要使使用用逻逻辑辑运运算算符符AND、OR和和NOT将将其其连连结结成成复复合合的的逻逻辑表达式。辑表达式。其其优优先先级级由由高高到到低低为为：NOT、AND、OR，用用户户可可以以使使用括号改变优先级。用括号改变优先级。例例7 查查询询选选修修C1或或C2且且分分数数大大于于等等于于85分分学学生生的的学学号号、课课程号和成绩。程号和成绩。SELECT SNO，CNO，SCOREFROM SCWHERE（CNO=C1 OR CNO=C2）AND SCORE=85 272-3 确定范围
20、确定范围例例8 查查询询工工资资在在1000至至1500之之间间的的教教师师的的教教师师号号、姓姓名名及职称。及职称。SELECT TNO,TN,PROFFROM TWHERE SAL BETWEEN 1000 AND 1500等价于等价于SELECT TNO,TN,PROFFROM TWHERE SAL=1000 AND SAL=150028例例9 查查询询工工资资不不在在1000至至1500之之间间的的教教师师的的教教师师号号、姓名及职称。姓名及职称。SELECT TNO,TN,PROFFROM TWHERE SAL NOT BETWEEN 1000 AND 1500292-4 确定集合确
21、定集合利用利用“IN”操作可以查询属性值属于指定集合的元组。操作可以查询属性值属于指定集合的元组。例例10 查询选修查询选修C1或或C2的学生的学号、课程号和成绩。的学生的学号、课程号和成绩。SELECT SNO,CNO,SCORE FROM SC WHERE CNO IN(C1,C2)此语句也可以使用逻辑运算符此语句也可以使用逻辑运算符“OR”实现。实现。SELECT SNO,CNO,SCORE FROM SC WHERE CNO=C1 OR CNO=C2利用利用“NOT IN”可以查询指定集合外的元组。可以查询指定集合外的元组。30例例11 查查询询没没有有选选修修C1，也也没没有有选选修
22、修C2的的学学生生的的学学号号、课课程号和成绩。程号和成绩。SELECT SNO,CNO,SCORE FROM SC WHERE CNO NOT IN(C1,C2)等价于：等价于：SELECT SNO,CNO,SCORE FROM SC WHERE CNO!=C1 AND CNO!=C2312-5 部分匹配查询部分匹配查询上上例例均均属属于于完完全全匹匹配配查查询询，当当不不知知道道完完全全精精确确的的値値时时，用用户户还还可可以以使使用用LIKE或或NOT LIKE进进行行部部分分匹匹配查询（也称模糊查询）。配查询（也称模糊查询）。LIKE定义的一般格式为：定义的一般格式为：LIKE 属属性
23、性名名必必须须为为字字符符型型，字字符符串串常常量量的的字字符符可可以以包包含含如下两个特殊符号：如下两个特殊符号：%：表示任意知长度的字符串；：表示任意知长度的字符串；_：表示任意单个字符。：表示任意单个字符。例例12 查询所有姓张的教师的教师号和姓名。查询所有姓张的教师的教师号和姓名。SELECT TNO,TN FROM TWHERE TN LIKE 张张%322-5 部分匹配查询部分匹配查询LIKE定义的一般格式为：定义的一般格式为：LIKE 属属性性名名必必须须为为字字符符型型，字字符符串串常常量量的的字字符符可可以以包包含含如下两个特殊符号：如下两个特殊符号：%：表示任意知长度的字符
24、串；：表示任意知长度的字符串；_：表示任意单个字符。：表示任意单个字符。例例13 查查询询姓姓名名中中第第二二个个汉汉字字是是“力力”的的教教师师号号和姓名。和姓名。SELECT TNO,TN FROM TWHERE TN LIKE _ _力力%注：一个汉字占两个字符。注：一个汉字占两个字符。332-6空值查询空值查询某个字段没有值称之为具有空值（某个字段没有值称之为具有空值（NULL）。）。通常没有为一个列输入值时，该列的值就是空值。通常没有为一个列输入值时，该列的值就是空值。空值不同于零和空格，它不占任何存储空间。空值不同于零和空格，它不占任何存储空间。例如，某些学生选课后没有参加考试，有
25、选课记录，例如，某些学生选课后没有参加考试，有选课记录，但没有考试成绩，考试成绩为空值，这与参加考试，但没有考试成绩，考试成绩为空值，这与参加考试，成绩为零分的不同。成绩为零分的不同。例例14 查询没有考试成绩的学生的学号和相应的课程号。查询没有考试成绩的学生的学号和相应的课程号。SELECT SNO,CNOFROM SCWHERE SCORE IS NULL注注 意意：这这 里里 的的 空空 值值 条条 件件 为为 IS NULL，不不 能能 写写 成成SCORE=NULL。342-7 常用库函数及统计汇总查询常用库函数及统计汇总查询SQL提供了许多提供了许多库函数库函数，增强了基本检索能力
26、。，增强了基本检索能力。常用的库函数，如表所示常用的库函数，如表所示函数名称函数名称功能功能AVG按列计算平均值按列计算平均值SUM按列计算值的总和按列计算值的总和MAX求一列中的最大值求一列中的最大值MIN求一列中的最小值求一列中的最小值COUNT按列值计个数按列值计个数35例例15 求学号为求学号为S1学生的总分和平均分。学生的总分和平均分。SELECT SUM(SCORE)AS TotalScore,AVG(SCORE)AS AveScoreFROM SCWHERE(SNO=S1)注意：函数注意：函数SUM和和AVG只能对只能对数值型数值型字段进行计算。字段进行计算。36例例16 求求选
27、选修修C1号号课课程程的的最最高高分分、最最低低分分及及之之间间相相差差的分数的分数SELECT MAX(SCORE)AS MaxScore,MIN(SCORE)AS MinScore,MAX(SCORE)-MIN(SCORE)AS DiffFROM SCWHERE(CNO=C1)例例17 求计算机系学生的总数求计算机系学生的总数SELECT COUNT(SNO)FROM SWHERE DEPT=计算机计算机37例例18 求学校中共有多少个系求学校中共有多少个系SELECT COUNT(DISTINCT DEPT)AS DeptNum FROM S注意：注意：加加入入关关键键字字DISTINC
28、T后后表表示示消消去去重重复复行行，可可计计算算字字段段“DEPT“不同值的数目。不同值的数目。COUNT函数对函数对空值空值不计算，但对不计算，但对零零进行计算。进行计算。例例19 统计有成绩同学的人数统计有成绩同学的人数SELECT COUNT(SCORE)FROM SC上例中成绩为零的同学计算在内，没有成绩（即为空值）的不上例中成绩为零的同学计算在内，没有成绩（即为空值）的不计算。计算。38例例20 利用特殊函数利用特殊函数COUNT(*)求计算机系学生的总数求计算机系学生的总数SELECT COUNT(*)FROM SWHERE DEPT=计算机计算机COUNT（*）用来统计元组的个数
29、用来统计元组的个数不消除重复行，不允许使用不消除重复行，不允许使用DISTINCT关键字。关键字。392-8 分组查询分组查询GROUP BY子子句句可可以以将将查查询询结结果果按按属属性性列列或或属属性性列列组组合合在在行行的的方方向向上上进进行行分分组组，每每组组在在属属性性列列或或属属性性列组合列组合上具有相同的值。上具有相同的值。例例21 查询各位教师的教师号及其任课的门数。查询各位教师的教师号及其任课的门数。SELECT TNO,COUNT(*)AS C_NUMFROM TCGROUP BY TNO GROUP BY子子句句按按TNO的的值值分分组组，所所有有具具有有相相同同TNO的
30、的元元组组为为一一组组，对对每每一一组组使使用用函函数数COUNT进进行计算，统计出各位教师任课的门数。行计算，统计出各位教师任课的门数。40若在分组后还要按照一定的条件进行筛选，则需使用若在分组后还要按照一定的条件进行筛选，则需使用HAVING子句子句。例例22 查询选修两门以上课程的学生学号和选课门数查询选修两门以上课程的学生学号和选课门数SELECT SNO,COUNT(*)AS SC_NUM FROM SCGROUP BY SNO HAVING COUNT(*)=2 GROUP BY子子句句按按SNO的的值值分分组组，所所有有具具有有相相同同SNO的的元元组组为为一一组组，对对每每一一
31、组组使使用用函函数数COUNT进进行计算，统计出每位学生选课的门数。行计算，统计出每位学生选课的门数。HAVING子句去掉不满足子句去掉不满足COUNT（*）=2的组。的组。41当当在在一一个个SQL查查询询中中同同时时使使用用WHERE子子句句，GROUP BY 子子句句和和HAVING子子句句时时，其其顺顺序序是是WHEREGROUP BY HAVING。WHERE与与HAVING子子句句的的根根本本区区别别在在于于作作用用对对象象不同。不同。WHERE子子句句作作用用于于基基本本表表或或视视图图，从从中中选选择择满足条件的元组；满足条件的元组；HAVING子子句句作作用用于于组组，选选择
32、择满满足足条条件件的的组组，必必须须用用于于GROUP BY子子句句之之后后，但但GROUP BY子句可没有子句可没有HAVING子句。子句。422-9 查询的排序查询的排序当需要对查询结果排序时，应该使用当需要对查询结果排序时，应该使用ORDER BY子句子句ORDER BY子句必须出现在其他子句之后子句必须出现在其他子句之后排排序序方方式式可可以以指指定定，DESC为为降降序序，ASC为为升升序序，缺缺省省时为升序时为升序例例23 查查询询选选修修C1 的的学学生生学学号号和和成成绩绩，并并按按成成绩绩降降序序排排列。列。SELECT SNO,SCOREFROM SCWHERE CNO=C
33、1ORDER BY SCORE DESC 43例例24 查查询询选选修修C2、C3、C4或或C5课课程程的的学学号号、课课程程号号和和成成绩绩，查查询询结结果果按按学学号号升升序序排排列列，学学号号相相同同再再按按成成绩绩降降序序排列。排列。SELECT SNO,CNO,SCORE FROM SCWHERE CNO IN(C2,C3,C4,C5)ORDER BY SNO,SCORE DESC 例例25 求求选选课课在在三三门门以以上上且且各各门门课课程程均均及及格格的的学学生生的的学学号号及及其总成绩，查询结果按总成绩降序列出。其总成绩，查询结果按总成绩降序列出。SELECT SNO,SUM(SCORE)AS TotalScore FROM SCWHERE SCORE=60GROUP BY SNOHAVING COUNT(*)=3ORDER BY SUM(SCORE)DESC44