04第四章关系数据库语言SQLppt课件

1、第四章 关系数据库语言SQL第四章 关系数据库语言SQL4.1SQL简介简介4.2查询语句查询语句4.3数据更新数据更新4.4数据定义数据定义小结小结4.1 SQL简介SQL是结构化查询语言是结构化查询语言Structured Query Language的缩写，的缩写， 是一种面向关系数据库的国是一种面向关系数据库的国际标准语言，在当前数据库领域中应用最为广泛和际标准语言，在当前数据库领域中应用最为广泛和成功，美国国家标准局成功，美国国家标准局ANSI在在1986年将年将SQL作为作为关系数据库系统的标准语言，后被国际标准化组织关系数据库系统的标准语言，后被国际标准化组织ISO采纳为国际标准

2、。现在很多大型数据库都采纳为国际标准。现在很多大型数据库都实现了实现了SQL语言。语言。4.1.1 SQL语言的发展SQL的发展历程主要有以下几个阶段：的发展历程主要有以下几个阶段：（11974年由年由Boyce和和Chamberlin提出，当时称为提出，当时称为SEQUELStructured English Query Language）。）。（219751979年年IBM公司对公司对SEQUEL进行了修改，并由进行了修改，并由San Jose Research Laboratory研制了著名的关系数据库研制了著名的关系数据库管理系统原型管理系统原型System R，实现了这种语言。，实现

3、了这种语言。（31981年年IBM推出了商用关系数据库推出了商用关系数据库SQL/DS，并将其改名，并将其改名为为SQL，由于它功能丰富，语言简洁，因此倍受用户及计算，由于它功能丰富，语言简洁，因此倍受用户及计算机工业界欢迎，被众多计算机公司和软件公司所采用。机工业界欢迎，被众多计算机公司和软件公司所采用。（4目前目前SQL语言不仅用于小型数据库如语言不仅用于小型数据库如Foxpro、Access，而且更广泛用于各种大型数据库，如而且更广泛用于各种大型数据库，如Sysbase、SQL Server、Oracle、Informix、DB2、Ingres等。等。SQL在发展中经历了一系列标准化过程

4、：在发展中经历了一系列标准化过程：（11982年美国国家标准局年美国国家标准局American National Standard Institute，简称，简称ANSI开开始制订始制订SQL标准。标准。（21986年年10月月ANSI的数据库委员会的数据库委员会X3H2批批准了准了SQL作为关系数据库语言的美国标准；同年作为关系数据库语言的美国标准；同年ANSI公布公布SQL语言的第一个标准语言的第一个标准SQL-86，并于次，并于次年 由 国 际 标 准 化 组 织 年 由 国 际 标 准 化 组 织 I n t e r n a t i o n a l Standardization Or

5、ganization，简称，简称ISO通过通过SQL-86标准。标准。（31989年年ISO对对SQL-86进行补充，推出进行补充，推出SQL-89标准，也称标准，也称SQL1。（41992年年ISO又推出又推出SQL-92标准，亦称标准，亦称SQL2。（52019年起年起ANSI陆续公布增加了面向对象功陆续公布增加了面向对象功能的新标准能的新标准SQL-99亦称亦称SQL3的的12个标准文个标准文本。本。4.1.2 SQL语言的特点（1非过程化：非过程化：SQL语言是非过程化语言，在语言是非过程化语言，在SQL语言中，只要求用户提语言中，只要求用户提出出“做什么做什么”，而无需指出，而无需指

6、出“怎样做怎样做”。SQL语句操作的过程由系统自语句操作的过程由系统自动完成。动完成。（2一体化：一体化：SQL可以操作于不同层次模式，集数据定义语言可以操作于不同层次模式，集数据定义语言DDL）、）、数据操纵语言数据操纵语言DML）、数据控制语言）、数据控制语言DCL为一体。用为一体。用SQL语言可语言可实现实现DB生命周期的全部活动，其中包括建立数据库、建立用户账号、生命周期的全部活动，其中包括建立数据库、建立用户账号、定义关系模式、查询及数据维护、数据库安全控制等。定义关系模式、查询及数据维护、数据库安全控制等。（3两种使用方式，统一的语法结构：两种使用方式，统一的语法结构：两种使用方式

7、两种使用方式:自含式语言与嵌入式语言自含式语言与嵌入式语言自含式方式就是联机交互使用方式自含式方式就是联机交互使用方式;嵌入式方式是指嵌入式方式是指SQL语句嵌入某种高级程序设计语言的程序中，以实现数语句嵌入某种高级程序设计语言的程序中，以实现数据库操作。据库操作。尽管这两种使用方式不同，但尽管这两种使用方式不同，但SQL语言的语法结构基本是一致的。语言的语法结构基本是一致的。4.1.3 SQL语言的书写准则 遵从某种准则可以提高语句的可读性，通常遵从某种准则可以提高语句的可读性，通常遵循的准则主要有：遵循的准则主要有：SQL语句对大小写不敏感，但关键字常用大写语句对大小写不敏感，但关键字常用

8、大写SQL语句可写在一行上，但为便于理解，增强语句可写在一行上，但为便于理解，增强条理性，常习惯于每个字句占用一行条理性，常习惯于每个字句占用一行关键字不能在行与行之间分开，很少采用缩写关键字不能在行与行之间分开，很少采用缩写形式形式SQL中的数据项需同时列出时，分隔符用中的数据项需同时列出时，分隔符用“,”；字符或字符串常量的定界符用单引号；字符或字符串常量的定界符用单引号“ ”表示表示4.2 查询语句4.2.1 基本查询基本查询4.2.2 使用列表达式使用列表达式4.2.3 WHERE从句的进一步使用从句的进一步使用4.2.4 数据汇总数据汇总4.2.5 连接查询连接查询4.2.6 嵌套查

9、询嵌套查询4.2.7 联合查询联合查询4.2.8 使用系统内置函数的查询使用系统内置函数的查询 SQL的核心是数据查询。的核心是数据查询。对于数据库的查询操作是通过对于数据库的查询操作是通过 SELECT 查询命查询命令实现的，它的基本形式由令实现的，它的基本形式由 SELECTFROMWHERE 查询块组成，多个查询块可以嵌套执行。查询块组成，多个查询块可以嵌套执行。总述SQL查询语句的完整语法描述如下： SELECT ALL|DISTINCT ， FROM ， WHERE GROUP BY HAVING ORDER BY ASC|DESC ； 指定要显示的属性列指定要显示的属性列指定查询对

10、象指定查询对象指定查询条件指定查询条件查询结果按指查询结果按指定列的值分组定列的值分组筛选出只有满足指筛选出只有满足指定条件的组定条件的组对查询结果表按指列值对查询结果表按指列值的升序或降序排序的升序或降序排序 4.2.1 基本查询最基本的查询就是从指定的表中找出符最基本的查询就是从指定的表中找出符合条件的记录。这样的查询由合条件的记录。这样的查询由 SELECT和和 FROM 短语构成无条件查询，或由短语构成无条件查询，或由 SELECT、FROM 和和 WHERE 或其他短或其他短语构成条件查询。语构成条件查询。 （1简单的无条件查询简单的无条件查询设某个数据库中有表设某个数据库中有表st

11、u_info，请把该表中的所，请把该表中的所有记录的所有属性查询出来有记录的所有属性查询出来: SELECT * / *表示所有字段，是通表示所有字段，是通配符配符 FROM stu_info/ 选定数据库中的表选定数据库中的表 （2 2简单的条件查询简单的条件查询请查找请查找stu_infostu_info表中所有的女生的信息。表中所有的女生的信息。SELECT * FROM stu_info WHERE xbm=女女 /设定条件设定条件 在在SELECT子句中可以决定哪些列出现在结果关子句中可以决定哪些列出现在结果关系中，这相当于关系代数中的投影运算。系中，这相当于关系代数中的投影运算。

12、具体办法：在具体办法：在SELECT子句之后不是写子句之后不是写“*”，而是根据需要列出在而是根据需要列出在FROM子句中声明的诸关系的属子句中声明的诸关系的属性。例：性。例：SELECT xh,xm /选定字段选定字段FROM stu_info /选定数据库中的表选定数据库中的表WHERE xbm=女女 /设定条件设定条件 （3 3查询语句中的投影操作查询语句中的投影操作使用使用 SQL 的的SELECT 可以将查询结果排序，排可以将查询结果排序，排序的短语是序的短语是 ORDER BY，具体格式如下：，具体格式如下： SELECT 列名列名1，列名，列名2， FROM 表名表名 WHERE

13、 条件表达式条件表达式 ORDER BY 列名列名1 ASC | DESC ，列名，列名2ASC | DESC按升序按升序ASC或降序或降序DESC排序，允许按一列排序，允许按一列或多列排序。或多列排序。（4 4设定排序条件设定排序条件下面是使查询结果排序的例子下面是使查询结果排序的例子:SELECT * /“*”表示所有字段表示所有字段 FROM stu_info /选定数据库中的表选定数据库中的表 WHERE xbm=女女 /设定条件设定条件 ORDER BY xh /设定排序字段设定排序字段 在查询过程中经常会出现一些重复记录，例如在查询过程中经常会出现一些重复记录，例如“SELECT

14、bh FROM stu_info”。其查询结果将会。其查询结果将会有多个重复的班级名，显然这种结果不能令人满意。有多个重复的班级名，显然这种结果不能令人满意。因此需要去掉重复值，此时需要指定因此需要去掉重复值，此时需要指定 DISTINCT 短语：短语： SELECT DISTINCT bh FROM stu_info 这里短语这里短语DISTINCT 的作用是去掉查询结果中的重复的作用是去掉查询结果中的重复值。执行结果中班级名都不重复。值。执行结果中班级名都不重复。 （5 5限定重复记录限定重复记录4.2.2 使用列表达式在在 SQL SQL中，指定列的方法我们学习了以下两种：中，指定列的方

15、法我们学习了以下两种： （1 1指定列指定列 （2 2使用通配符使用通配符* * 第第(3)(3)种是：可用列表达式，来获取经过计算的种是：可用列表达式，来获取经过计算的查询结果。查询结果。列表达式不仅可以是算术表达式，还可以是字符列表达式不仅可以是算术表达式，还可以是字符串常量、函数等。串常量、函数等。(6) (6) 计算列值计算列值SELECT 学号,xh,年龄, YEAR(GETDATE( )-YEAR(CSRQ),bhFROM stu_info(7) (7) 修改查询结果的列标题修改查询结果的列标题(1) SELECT YEAR(GETDATE( )-YEAR(CSRQ) AS 年龄,

16、 nl FROM stu_info(2) SELECT 学号=xh, nl FROM stu_info(3) SELECT xh 学号, nl FROM stu_info查询工作的关键就在通过分析怎样把实际问查询工作的关键就在通过分析怎样把实际问题中的自然语言描述转化为题中的自然语言描述转化为1 1从哪个表中查询。即：从哪个表中查询。即：fromfrom子句子句2 2要查询哪些列。要查询哪些列。 即：即：selectselect子句子句3) 3) 要查询的条件。要查询的条件。 即：即：wherewhere子句子句完成查询工作的三个关键步骤完成查询工作的三个关键步骤其中第其中第3 3步最复杂步最

17、复杂4.1.3 WHERE从句的进一步使用常用的查询条件常用的查询条件查询条件查询条件谓词谓词比较比较= = = != ! !20 WHERE nl20（9 9确定范围确定范围between andbetween and和和 not between and not between and如如: :查询考试成绩在查询考试成绩在60-7060-70之间的学号、课程号、考试之间的学号、课程号、考试成绩：成绩：SELECT xh, kch, kscjSELECT xh, kch, kscjFROM xkFROM xkWHERE kscj BETWEEN 60 AND 70WHERE kscj BETW

18、EEN 60 AND 70（10 10确定集合确定集合 IN IN 和和 NOT IN NOT IN例：查询复材例：查询复材0108 0108 、复材、复材01090109班的学生班的学生的信息的信息SELECT xh,xm,xbm,bhSELECT xh,xm,xbm,bhFROM stu_infoFROM stu_infoWHERE bh IN( WHERE bh IN( 复材复材0108, 0108, 复材复材0109)0109)（11 11字符匹配字符匹配NOT LIKE 和和 LIKE 与与 % 和和 _ 搭配。搭配。%代表任意长度的字符串代表任意长度的字符串.如如 a%b: acb

19、, addgb 等都满足匹配等都满足匹配_(下横线下横线)代表任意单个字符。代表任意单个字符。例：查询班级名中第例：查询班级名中第1个字为个字为“计计字的学生信息：字的学生信息： SELECT * FROM stu_info WHERE bh like 计计%例：查询名字中第例：查询名字中第2个字为个字为“宁宁字的学生信息：字的学生信息： SELECT * FROM stu_info WHERE xm like _宁宁%例：查询例：查询DB_Design课程的课程号和学分。课程的课程号和学分。 SELECT ckh,kcxf FROM GCourse WHERE km LIKE DB_Desi

20、gn ESCAPE 例：查询以例：查询以DB_开头，且倒数第开头，且倒数第3个字符为个字符为 i的课程的详细情的课程的详细情况。况。 SELECT * FROM GCourse WHERE km LIKE DB_%i_ _ ESCAPE 当用户要查询的字符串本身就含有当用户要查询的字符串本身就含有 % % 或或 _ _ 时，要使用时，要使用 ESCAPE ESCAPE 换码字符换码字符 短语对通配符进行转义。短语对通配符进行转义。(12) (12) 涉及空值的查询涉及空值的查询SELECT * FROM stu_info WHERE bh IS NULL(13) (13) 多重条件查询多重条件

21、查询 用逻辑运算符用逻辑运算符andand和和oror连接，连接，andand的优先级的优先级高于高于oror。如：查询应什么班，姓刘的学生。如：查询应什么班，姓刘的学生。SELECT SELECT * * FROM stu_info FROM stu_info WHERE bh like WHERE bh like 应应% and % and 姓名姓名 like like 刘刘%4.2.4 数据汇总（14 14聚合函数聚合函数count (*)统计元组（记录）个数统计元组（记录）个数count(distinct | all) 统计一列中值的个数统计一列中值的个数sum(distinct |

22、all)求一列值的总和求一列值的总和（数值型）数值型）avg(distinct | all)求一列值的平均数求一列值的平均数max(distinct | all)求一列值中的最大值求一列值中的最大值min(distinct | all)求一列值中的最小值求一列值中的最小值聚合函数一览表聚合函数一览表例：查询学生总数例：查询学生总数SELECT count(*) FROM stu_info例：已知数据库表例：已知数据库表XK中高等数学课程的代码为中高等数学课程的代码为090101，查询学生选修高等数学的平均成绩：，查询学生选修高等数学的平均成绩：SELECT avg(KSCJ) AS 高等数学高

23、等数学 FROM XK WHERE KCH=090101（15 15GROUP BYGROUP BY子句子句 Group by 子句将查询结果表按某一列或多列值分组，值相子句将查询结果表按某一列或多列值分组，值相等的分为一组。等的分为一组。如：在表如：在表xk中查询各课程编号及相应的选课人数中查询各课程编号及相应的选课人数注：注：GROUP BYGROUP BY子句中不能使用子句中不能使用聚合函数，并且必须在聚合函数，并且必须在GROUP GROUP BYBY子句中列出子句中列出SELECTSELECT列表中所列表中所有的非聚合项。有的非聚合项。例：在表例：在表stu_info中查询每个专业的

24、男生、女生人数中查询每个专业的男生、女生人数在在group by子句后面加子句后面加WITH ROLLUP或或WITH CUBE如果还要显示每个专如果还要显示每个专业的总人数及全体学业的总人数及全体学生总人数，如何实现？生总人数，如何实现？ SELECT kch,count(xh) FROM xk GROUP BY kch SELECT zyh, xbm, count(*) as 人数人数 FROM stu_info GROUP BY zyh, xbm例：查询选修了例：查询选修了3 3门以上课程的学生及选课数：门以上课程的学生及选课数：SELECT XH, COUNT(SELECT XH, C

25、OUNT(* *) )FROM XKFROM XKGROUP BY XHGROUP BY XHHAVING (COUNT(HAVING (COUNT(* *) 3) 3) HAVING HAVING短语指定分组或聚合的条件。只有满足条件的分组短语指定分组或聚合的条件。只有满足条件的分组才被选出来，才被选出来，HAVINGHAVING一般与一般与GROUP BYGROUP BY子句一起使用。子句一起使用。（16 16HAVING HAVING 短语短语SELECT XH, COUNT(*)FROM XKWHERE (XH LIKE 2019%)GROUP BY XHHAVING (COUNT(*

26、) 3)例：查询学号前四位是例：查询学号前四位是20192019的学生的学号及选课数：的学生的学号及选课数：having having 短语指定选择组的条件，只有满足短语指定选择组的条件，只有满足条件的组才被选出来。一般和条件的组才被选出来。一般和group bygroup by子子句一起使用句一起使用wherewhere子句从基本表中选择满足条件的元组，子句从基本表中选择满足条件的元组，而不是指定满足条件的分组，这是二者的而不是指定满足条件的分组，这是二者的根本区别。根本区别。若一个查询同时涉及两个以上的表，称为连接若一个查询同时涉及两个以上的表，称为连接查询。查询。连接查询是关系数据库中最

27、主要的查询，按连连接查询是关系数据库中最主要的查询，按连接类型可分为：内连接、外连接和交叉连接。其中接类型可分为：内连接、外连接和交叉连接。其中内连接又分：等值连接、非等值连接和自然连接；内连接又分：等值连接、非等值连接和自然连接；外连接又分：左外连接、右外连接和全外连接等。外连接又分：左外连接、右外连接和全外连接等。4.2.5 连 接 查 询 连接查询中用来连接两个关系的条件称为连接查询中用来连接两个关系的条件称为连接条件或连接谓词，格式：连接条件或连接谓词，格式： 表名表名1.1.列名列名 比较运算符比较运算符 表名表名2.2.列名列名其中比较运算符主要有：其中比较运算符主要有：= =、

28、、 =、=、!=!=当连接运算符为当连接运算符为= =时，叫等值连接。时，叫等值连接。使用其它运算符称为非等值连接。使用其它运算符称为非等值连接。连接谓词中的列名称为连接字段。连接谓词中的列名称为连接字段。 例：查询每个学生及其选课情况例：查询每个学生及其选课情况SELECT *FROM stu_info,xkWHERE stu_info.xh=xk.xh例：查询应化例：查询应化041041班每个学生及其选修课程的情况班每个学生及其选修课程的情况SELECT stu_info.xh, stu_info.xm, stu_info.bh, stu_info.xbm, xk.kch, xk.kscj

29、FROM stu_info,xkWHERE stu_info.xh = xk.xh and stu_info.bh=应化应化041 /复合条件连接复合条件连接多表查询：涉及两个表以上的查询多表查询：涉及两个表以上的查询SELECT xm FROM stu_info, xk, gcourseWHERE gcourse.km=数据库原理数据库原理 and gcourse.kch=xk.kch and xk.xh=stu_info.xh为了简化，表名可以取为了简化，表名可以取别名，且别名只在本次别名，且别名只在本次查询有效查询有效增加表别名后的结果：增加表别名后的结果：SELECT xm FROM

30、stu_info a, xk b, gcourse cWHERE c.km=数据库原理数据库原理 and c.kch=b.kch and b.xh=a.xh例如：查询选修了有机化学这门课程的学生的姓名例如：查询选修了有机化学这门课程的学生的姓名下面讨论另一类基于多个关系的查询，这类查下面讨论另一类基于多个关系的查询，这类查询所要求的结果出自一个关系，但相关的条件却涉询所要求的结果出自一个关系，但相关的条件却涉及多个关系。在前面的例子中，及多个关系。在前面的例子中，WHERE 之后是一之后是一个相对独立的条件，这个条件或者为真、或者为假。个相对独立的条件，这个条件或者为真、或者为假。但是，有时需

31、要用另外的方式来表达检索要求。比但是，有时需要用另外的方式来表达检索要求。比如，当检索关系如，当检索关系X中的记录时，它的条件依赖于相关中的记录时，它的条件依赖于相关的关系的关系Y中的记录属性值，这时使用中的记录属性值，这时使用SQL的嵌套查的嵌套查询功能将非常方便。询功能将非常方便。 4.2.6 嵌 套 查 询 在在SQLSQL语言中，一个语言中，一个select-from-whereselect-from-where语句称为一个语句称为一个查询块，将一个查询块嵌套在另一个查询块的查询块，将一个查询块嵌套在另一个查询块的wherewhere子句或子句或havinghaving短语的条件中的查

32、询称为嵌套查询。短语的条件中的查询称为嵌套查询。等价形式：等价形式：SELECT xm FROM stu_info,xk WHERE stu_info.xh=xk.xh and kch=05SELECT xmFROM stu_info例：在例：在stu_infostu_info表中查询选修了表中查询选修了0505号课程的学生姓名号课程的学生姓名WHERE xh IN ( SELECT xh FROM xk WHERE kch =05 )例：查询与刘玉涛在同一例：查询与刘玉涛在同一个班学习的学生的学号、个班学习的学生的学号、姓名、班级。姓名、班级。SELECT xh, xm, bhFROM st

33、u_infoWHERE bh IN(17)(17)带有带有in in谓词的子查询谓词的子查询(18)(18)带有比较运算符的子查询带有比较运算符的子查询例：查询与学号是例：查询与学号是2019900120199001的的学生在同一个班学习的学生，学生在同一个班学习的学生，按学号排序。按学号排序。SELECT xh, xm, bhFROM stu_infoWHERE bh ORDER BY xh ( SELECT bh FROM stu_info WHERE xm =刘玉涛刘玉涛 ) = ( SELECT bh FROM stu_info WHERE xh =20199001 )(19)(19)

34、带有带有anyany或或allall谓词的子查询谓词的子查询比较子查询通常用到操作符比较子查询通常用到操作符ALL、ANY。其通用格式为：其通用格式为： ALL|ANY例：查询其他班级中比应化例：查询其他班级中比应化041班某个学生年龄小班某个学生年龄小的那些学生的姓名和年龄。的那些学生的姓名和年龄。SELECT xm,nl FROM stu_info WHERE bh应化应化041 and nl any( select nl from stu_info where bh=应化应化041)(20)(20)带有带有existsexists谓词的子查询谓词的子查询EXISTS是测试子查询是否有数据

35、行返回，有则返回是测试子查询是否有数据行返回，有则返回TRUE，否则返回否则返回FALSE。例：查询选修了高等数学例：查询选修了高等数学(090101)的学生姓名、性别和班级。的学生姓名、性别和班级。SELECT xm,xbm,bh FROM stu_infoWHERE exists ( select * from xk where xh=stu_info.xh and kch=090101)例：查询没有选修高等数学例：查询没有选修高等数学(090101)的学生姓名、性别和班级。的学生姓名、性别和班级。SELECT xm,xbm,bh FROM stu_infoWHERE not exists

36、(select * from xk where xh=stu_info.xh and kch=090101)4.2.7 结合(集合)查询 select语句的查询结果是元组的集合，所以多个语句的查询结果是元组的集合，所以多个select语句的结果可进行集合操作。语句的结果可进行集合操作。集合操作主要包括：集合操作主要包括：并操作并操作UNION、交操作、交操作INTERSECT、差操作、差操作MINUS标准标准SQL只提供只提供UNION，其语法格式如下：，其语法格式如下： SELECT_1 UNION ALL SELECT_2 如果要将查询的结果保存在当前数据库新建的表如果要将查询的结果保存在

37、当前数据库新建的表NEWSTU中，可以使用中，可以使用INTO子句：子句：SELECT * INTO NEWSTU FROM aUNION ALLSELECT * FROM b例：查询编号为例：查询编号为01的学院的学生及所有学院的本科生的学院的学生及所有学院的本科生SELECT * FROM stu_info WHERE xsh=01 UNIONSELECT * FROM stu_info WHERE pyccm=本科本科 4.2.8 使用系统内置函数的查询(21) (21) 数学函数数学函数 ABS( ABS(数字型表达式数字型表达式) )、RAND(RAND(整型表达式整型表达式) )(

38、22) (22) 字符串处理函数字符串处理函数 ASCII( ASCII(字符型字符型) )：返回表达式最左端字符的：返回表达式最左端字符的ASCIIASCII值值 CHAR(0-255 CHAR(0-255之间的整数之间的整数) )：将：将ASCIIASCII转换为字符转换为字符 LEFT( LEFT(字符型，整型字符型，整型) )：返回从字符串左边开始指定个：返回从字符串左边开始指定个数的字符数的字符 LTRIM( LTRIM(字符型字符型) )：删除字符串最左边的空格，并返回：删除字符串最左边的空格，并返回字符串字符串 REPLACE( REPLACE(字符型字符型1 1，字符型，字符型

39、2 2，字符型，字符型3)3)：用：用3 3替换替换1 1中中包含的包含的2 2，并返回替换后的表达式，并返回替换后的表达式 SUBSTRING(express,start,length) SUBSTRING(express,start,length)：返回：返回expressexpress中指定的部中指定的部分数据分数据 STR(float_exp ,length ,decimal) STR(float_exp ,length ,decimal)：将数字数据转换为字：将数字数据转换为字符数据符数据(23) (23) 系统函数系统函数getdate( ):getdate( ):返回当前的系统日

40、期和时间返回当前的系统日期和时间year( )year( )、month( )month( )、day( ):day( ):分别返回指定日期的年、月、分别返回指定日期的年、月、日日 cast(cast(表达式表达式 AS AS 数据类型数据类型) )：将表达式的值转换为数据：将表达式的值转换为数据类型参数所指定的类型。类型参数所指定的类型。casecase函数：一种是简单的函数：一种是简单的CASECASE函数，另一种是搜索型函数，另一种是搜索型的的CASECASE函数。函数。简单简单CASECASE语法格式：语法格式：CASE CASE 输入表达式输入表达式 WHEN WHEN 比较表达式比

41、较表达式 THEN THEN 结果表达式结果表达式 ELSE ELSE 表达式表达式ENDEND搜索搜索CASECASE语法格式：语法格式：CASECASE WHEN WHEN 条件条件1 THEN 1 THEN 表达式表达式1 1 WHEN WHEN 条件条件2 THEN 2 THEN 表达式表达式2 2 ELSE ELSE 表达式表达式ENDEND例：在例：在STU_INFOSTU_INFO中查询中查询0303学院学生的学号、姓名、性别，并将性别分别转换成学院学生的学号、姓名、性别，并将性别分别转换成“男男生生”、“女生女生”。SELECT XH,XM,XBM,XBM=SELECT XH,

42、XM,XBM,XBM= CASE CASE 男男 WHEN XBM THEN WHEN XBM THEN 男生男生 ELSE ELSE 女生女生 ENDENDFROM STU_INFOFROM STU_INFOWHERE XSH=03WHERE XSH=03例：查询成绩表例：查询成绩表CSCS中选修了中选修了“数据库技术及应用数据库技术及应用课程的学生的学号、姓名、课程的学生的学号、姓名、5 5分制成分制成绩绩SELECT SELECT 学号学号, ,姓名姓名, ,5 5分制成绩分制成绩= = CASECASE WHEN WHEN 成果成果60 THEN =60 AND=60 AND成绩成绩7

43、0 THEN =70 AND=70 AND成绩成绩80 THEN =80 AND=80 AND成绩成绩90 THEN =90 THEN =90 THEN 优秀优秀 ENDENDFROM CS FROM CS 4.3 数据更新4.3.1 插入数据插入数据4.3.2 修改数据修改数据4.3.3 删除数据删除数据4.2.1 插入数据n语句格式语句格式nINSERTnINTO (， )nVALUES ( ， )n功能功能 将新元组插入指定表中。将新元组插入指定表中。(1) (1) 插入单个元组插入单个元组例：例：INSERT INTO stu_info (xh,xm,xbm) VALUES (2019

44、090209, 王东方王东方, 男男)如果基本表只有上述三个属性，则该句等价于：如果基本表只有上述三个属性，则该句等价于： INSERT INTO stu_info VALUES (2019090209, 王东方王东方, 男男)(2) (2) 插入子查询结果插入子查询结果例：在例：在STU_INFO表中查询出应化表中查询出应化041班的学生情况并存放在班的学生情况并存放在YINGHUA表中。表中。 INSERT INTO yinghua SELECT xh,xm,xbm,csrq FROM stu_info WHERE bh=应化应化041 n语句格式语句格式n INSERT n INTO (

45、 ， )n 子查询子查询n功能功能: 将子查询结果插入指定表中，用以插入批量记录将子查询结果插入指定表中，用以插入批量记录4.3.2 修改数据 语句格式语句格式UPDATE SET =，=,WHERE 功能功能修改指定表中满足修改指定表中满足WHERE子句条件的元组子句条件的元组(3) (3) 修改一个元组的值修改一个元组的值 UPDATE stu_info UPDATE stu_info SET bh= SET bh=材材0169 0169 WHERE xh=2019090209 WHERE xh=2019090209注意：对批量数据的修改，注意：对批量数据的修改，一定把将要修改的记录先查一

46、定把将要修改的记录先查询出来，确认无误后再修改。询出来，确认无误后再修改。(4) (4) 修改多个元组的值修改多个元组的值 UPDATE gcourse UPDATE gcourse SET kcxf=2 SET kcxf=2 (5) (5) 带子查询的修改语句带子查询的修改语句 将选修了高数的学生的成绩加将选修了高数的学生的成绩加1010分分 UPDATE xk UPDATE xk SET kscj=kscj+10 SET kscj=kscj+10 WHERE kch=(select kch from gcourse where WHERE kch=(select kch from gcou

47、rse where km=km=高等数学高等数学)4.3.3 删除数据语法格式语法格式 DELETE FROM WHERE 功能功能删除指定表中满足删除指定表中满足WHERE子句条件的元组子句条件的元组WHERE子句子句指定要删除的元组指定要删除的元组缺省表示要删除表中的所有元组缺省表示要删除表中的所有元组(6) (6) 删除一个元组的值删除一个元组的值 DELETE FROM stu_info DELETE FROM stu_info WHERE xh=2019050010 WHERE xh=2019050010(7) (7) 删除多个元组的值删除多个元组的值 DELETE FROM stu

48、_info DELETE FROM stu_info WHERE bh= WHERE bh=应应02030203 删除表中所有数据删除表中所有数据 DELETE FROM stu_info DELETE FROM stu_info (8) 带子查询的删除语句带子查询的删除语句 将信息学院学生的成绩全部删除将信息学院学生的成绩全部删除DELETE FROM xk WHERE 12= ( SELECT xsh FROM stu_info WHERE stu_info.xh=xk.xh )注意：在日常工作中，操作删除语句，一定要先查询要删除的注意：在日常工作中，操作删除语句，一定要先查询要删除的数据

49、，确认无误后再删除。数据，确认无误后再删除。SELECT * FROM xk WHERE 12= ( select xsh from stu_info where stu_info.xh=xk.xh )4.4 数据定义4.4.1 定义基本表定义基本表4.4.2 修改基本表修改基本表4.4.3 删除基本表删除基本表4.4.4 视图视图4.4.5 索引索引4.4 数据定义 SQL的数据定义语句的数据定义语句操作对象操作对象操作方式操作方式创建创建删除删除修改修改表表视图视图索引索引4.4.1 定义基本表语句格式：语句格式： CREATE TABLE ( ， ， ) ：所要定义的基本表的名字：所要定

50、义的基本表的名字 ：组成该表的各个属性列）：组成该表的各个属性列） ：涉及相应属性列的完整：涉及相应属性列的完整性约束条件性约束条件 ：涉及一个或多个属性列：涉及一个或多个属性列的完整性约束条件的完整性约束条件 例：建立一个学生表例：建立一个学生表Student，它由学号，它由学号Sno、姓名、姓名Sname、性别、性别Ssex、年龄年龄Sage、所在系、所在系Sdept五个属性组成。其中学号不能为空，值是唯一的，五个属性组成。其中学号不能为空，值是唯一的，并且姓名取值也唯一。并且姓名取值也唯一。 CREATE TABLE Student ( Sno VARCHAR(5) NOT NULL U

51、NIQUE， Sname VARCHAR(20) UNIQUE， Ssex VARCHAR(1) ， Sage INT， Sdept VARCHAR(15)SnoSnameSsex SageSdept字符型字符型长度为长度为5不能为不能为空值取值唯一空值取值唯一字符型字符型长度为长度为20字符型字符型长度为长度为1整数整数字符型字符型长度为长度为15常用完整性约束常用完整性约束主码约束：主码约束： PRIMARY KEY唯一性约束：唯一性约束：UNIQUE非空值约束：非空值约束：NOT NULL默认值约束：默认值约束：DEFAULT检查约束：检查约束：CHECK参照完整性约束：参照完整性约束：

52、FOREIGN KEYREFERENCE例：建立一个例：建立一个“学生选课学生选课表表SC，它由学号，它由学号Sno、课程号、课程号Cno，修课成绩，修课成绩Grade组成，其中组成，其中(Sno, Cno)为主码。为主码。CREATE TABLE SC( Sno VARCHAR(5) , Cno VARCHAR(3) , Grade int, Primary key (Sno, Cno) )例：创建表例：创建表n_jobsCREATE TABLE n_jobs( job_id smallint IDENTITY(1,1) PRIMARY KEY CLUSTERED, job_desc var

53、char(50) NOT NULL DEFAULT (新部门新部门), min_lvl tinyint NOT NULL CHECK (min_lvl = 12), max_lvl tinyint NOT NULL CHECK (max_lvl = 250)例：使用表达式例：使用表达式(最低分最低分+最高分最高分)/2)生成中等分计算列。生成中等分计算列。CREATE TABLE 得分得分 ( 最低分最低分 int, 最高分最高分 int, 中等分中等分 AS (最低分最低分+最高分最高分)/2)4.4.2 修改基本表语法格式：语法格式：ALTER TABLE ALTER COLUMN ADD

54、 完整性约束完整性约束 DROP ：要修改的基本表：要修改的基本表ALTER COLUMN 子句：要更改的列，要更改子句：要更改的列，要更改的列不能是数据类型为的列不能是数据类型为text,image,ntext或或timestamp等列等列ADD子句：增加新列和新的完整性约束条件子句：增加新列和新的完整性约束条件DROP子句：删除指定的完整性约束条件或列子句：删除指定的完整性约束条件或列名名1、添加列、添加列例：向学生表中添加入学时间列，其数据类型为日例：向学生表中添加入学时间列，其数据类型为日期型期型 ALTER TABLE stu_info ADD rxsj DATETIME可能破坏可能

55、破坏已有数据已有数据2、修改列、修改列例：将教师表的例：将教师表的xb列改为列改为smallint数据类型数据类型 ALTER TABLE gtech ALTER COLUMN xb smallint3、删除列或约束、删除列或约束例：将数据库表例：将数据库表gtech中的中的xb列删除列删除 ALTER TABLE gtech DROP COLUMN xb例：删除学生姓名必须取唯一值的约束例：删除学生姓名必须取唯一值的约束 ALTER TABLE stu_info DROP UNIQUE(xm)4.4.3 删除基本表语法格式：语法格式： DROP TABLE 基本表定义一旦删除，表中的数据、表

56、上的索引基本表定义一旦删除，表中的数据、表上的索引和视图都将自动被删除和视图都将自动被删除删除基本表时，系统会从数据字典中删去有关该删除基本表时，系统会从数据字典中删去有关该基本表及其索引的描述。基本表及其索引的描述。 例：删除例：删除EDU_D库中的表库中的表stu_info DROP TABLEstu_info4.4.4 视图视图是关系数据库系统提供给用户以多种角度观察数据库中视图是关系数据库系统提供给用户以多种角度观察数据库中数据的重要机制。数据的重要机制。视图是从一个或多个基本表或视图导出的表，是个虚表。视图是从一个或多个基本表或视图导出的表，是个虚表。数据库只存放视图的定义，不存放视

57、图对应的数据，这些数数据库只存放视图的定义，不存放视图对应的数据，这些数据仍存放在原来的基本表中。所以，视图的数据跟随基本表据仍存放在原来的基本表中。所以，视图的数据跟随基本表的数据而变化。的数据而变化。视图一旦被定义，就可以被查询、删除，修改及再定义一个视图一旦被定义，就可以被查询、删除，修改及再定义一个新的视图等。新的视图等。视图包含一系列带有名称的列和行数据。这些数据来自定义视图包含一系列带有名称的列和行数据。这些数据来自定义视图的查询所引用的基表中，并且在引用视图时动态生成。视图的查询所引用的基表中，并且在引用视图时动态生成。1、定义视图、定义视图(1) 用企业管理器建立视图用企业管理

58、器建立视图 请同学们上机自己学习建立视图的方法。请同学们上机自己学习建立视图的方法。(2) 用用SQL语句建立视图语句建立视图 CREATE VIEW 视图名视图名 AS WITH CHECK OPTION其中其中WITH CHECK OPTION表示对视图进行表示对视图进行UPDATE、INSERT和和DELETE操作时要保证更新、操作时要保证更新、插入或删除的行满足视图定义中的条件表达式插入或删除的行满足视图定义中的条件表达式A、选择列定义视图、选择列定义视图 可定义一个视图，该视图由表的部分列组成。下可定义一个视图，该视图由表的部分列组成。下例中，视图例中，视图MyView1包含了包含了

59、employees表中的三个列。表中的三个列。 CREATE VIEW MyView1 AS SELECT lname, fname FROM employee定义了视图后，可以和表一样，使用定义了视图后，可以和表一样，使用SELECT语句访语句访问它。问它。例如：例如： SELECT * FROM MyView1B、基于列的表达式定义视图、基于列的表达式定义视图 在定义视图时，除了使用基础表的列外，还可使在定义视图时，除了使用基础表的列外，还可使用基本表的列的表达式，生成自己的列。用基本表的列的表达式，生成自己的列。 下例中，视图下例中，视图MyView2的第四列即由表达式计算的第四列即由表

60、达式计算而来。而来。 CREATE VIEW MyView2(xh, xm, xbm, nl) AS SELECT xh，xm，xbm，year(getdate()-year(csrq) FROM stu_infoC、选择行定义视图、选择行定义视图 可定义一个视图，该视图由表的部分行组成。可定义一个视图，该视图由表的部分行组成。下例中，视图下例中，视图MyView3只包含只包含employee表中姓名包表中姓名包含含sh的行。的行。 CREATE VIEW MyView3 AS SELECT * FROM employee WHERE fname LIKE %sh%D、选择行和列、选择行和列