数据库在测量中的应用第三讲结构化查询语言课件

1、第六章 SQL语言8/22/20221 结构化查询语言(SQL,Structure Query Language)是关系型数据库的主流语言，它是一种非过程化的程序语言，也就是说，只需要写出做什么就可以了。8/22/202226.1 SQL概述 SQL语言的主要目的是为了提供访问数据库的标准方法，而不管数据库应用系统的其余部分用什么语言编写的，它既是为数据库的交互式查询而设计的（动态SQL），也可在过程化语言编写的数据库应用程序中使用（嵌入式SQL）。8/22/20223 SQL是集DDL(Data Description Language,数据定义语言)、DML（Data Manipulati

2、on Language,数据操作语言）、数据控制功能于一体的数据库语言。 SQL是介于关系代数和关系演算之间的一种结构化查询语言，由于它使用方便、功能丰富、语言简洁易学，因此极易推广和应用。8/22/20224 类似于英语自然语言，简单易学。 是一种非过程语言。 是一种面向集合的语言。 既可以独立使用，又可以嵌入到宿主语言中使用。宿主语言 具有查询、操作、定义和控制一体化功能。SQL语言的特点8/22/202256.2 SQL数据定义功能SQL格式：CREATE TABLE 表名(字段名1 数据类型1 NOT NULL, 字段名2 数据类型2 NOT NULL,)IN 数据库名说明： 主键：P

3、RIMARY KEY， Decimal (12,4) default 0，如果某字段作为表的关键字，应该定义该字段为非空（NOT NULL）。6.2.1 建立数据表8/22/20226例1：用SQL语言建立一个名称为student的数据表，其结构为：学号(整型)、姓名(文本，8字节)、性别(文本，2字节)、出生日期(日期型)和班级(文本，5字节)。8/22/202278/22/202288/22/202298/22/2022108/22/2022118/22/2022128/22/202213SQL格式：ALTER TABLE 表名 ADD 字段名 数据类型运行该语句后，数据表将增加一字段；A

4、LTET TABLE 表名 DROP 字段名运行该语句后，数据表将删除指定的字段。6.2.2 修改数据表8/22/2022148/22/2022158/22/202216SQL格式：DROP 表名6.2.3 删除数据表8/22/2022171. 输出某字段记录SQL格式：SELECT 字段名FROM 表名其中字段名可以是多项，当为多字段时，用“,”分隔；如果是从多个表中提取数据，各个表名都要写出，表名之间用“,”分开。6.3 SQL数据查询功能6.3.1 投影查询8/22/202218例2：SELECT name,sex,classFROM student选择name、sex、class字段从

5、student表中8/22/2022198/22/2022202. 输出某字段不重复的记录SQL格式：SELECT DISTINCT 字段名FROM 表名8/22/202221例3：SELECT DISTINCT departFROM teacher选择depart字段的不重复记录从teacher表中8/22/2022228/22/202223SQL格式：SELECT 字段名FROM 表名WHERE 查询条件其中WHERE 查询条件是一个逻辑表达式它由多个关系表达式通过逻辑运算符（AND、OR、NOT）连接而成。6.3.2 选择查询8/22/202224例4：输出表score中成绩为60 80

6、分之间的记录。SELECT *FROM scoreWHERE degree BETWEEN 60 and 808/22/2022258/22/202226例5：输出表score中成绩为85、86或88分的记录。SELECT *FROM scoreWHERE degree IN (85,86,88)8/22/2022278/22/202228例6：输出表student中95031班或性别为“女”的记录。SELECT *FROM studentWHERE class= 95031 or sex=女8/22/2022298/22/202230例7：输出表student中“王”姓的记录。SELECT

7、student.no, name,sexFROM studentWHERE name Like 王*8/22/2022318/22/202232SQL格式：SELECT *FROM 表名ORDER BY 字段名 ASC（升序） DESC（降序）6.3.3 排序查询8/22/202233例8：以“班号”字段降序方式输出表student中的记录。SELECT *FROM studentORDER BY class DESC8/22/2022348/22/202235例9：以“课程编号”字段升序方式、以“分数”字段降序方式输出表score中的记录。SELECT *FROM scoreORDER BY

8、 cno,degree DESC8/22/2022366.3.4 使用合计函数函数名 功能AVG 计算一个数值型字段的平均值COUNT 计算指定字段中选择的项数，COUNT(*) 统计查询输出的行数MIN 计算指定字段中的最小值MAX 计算指定字段中的最大值SUM 计算指定字段中的数值总和8/22/202237SQL格式：SELECT 函数名 ASFROM 表名WHERE 查询条件8/22/202238例10：计算95031班的学生人数。SELECT COUNT(*) AS 95031班人数FROM studentWHERE class=950318/22/2022398/22/202240例

9、11：输出score表中的最高分。SELECT MAX(degree) AS 最高分FROM score8/22/2022418/22/202242例12：输出课程编号为3-105的平均分。SELECT AVG(degree) AS 平均分FROM scoreWHERE cno=3-1058/22/2022438/22/202244例13：输出最低分大于等于70，且最高分小于等于90的学生编号。SELECT score.noFROM scoreGROUP BY score.noHAVING MIN(score.degree)=70 And MAX(score.degree)y.degree a

10、nd y.no=109and o=3-1058/22/2022588/22/202259例20：输出成绩高于109号学生3-105课程的成绩的所有记录。SELECT o, x.no, x.degreeFROM score x, score yWHERE x.degreey.degree and y.no=109and o=3-1058/22/2022608/22/202261子查询：当一个查询是另一个查询的条件时 称之。6.3.6 子查询8/22/202262例21：输出与105号学生同年出生的所有学生的no、name、birthday字段。SELECT student.no, student

11、.name, student.birthdayFROM studentWHERE year(student.birthday)= (SELECT year(student.birthday) FROM student WHERE student.no=105)；8/22/2022638/22/202264SELECT student.no, , student.birthdayFROM studentWHERE year(student.birthday)= (SELECT year(student.birthday) FROM student WHERE studen

12、t.no=105)；输出105号的出生年8/22/2022658/22/202266例22：输出教师“张旭”任课的学生成绩。SELECT o,score.no,score.degreeFROM scoreWHERE o= (SELECT o FROM course,teacher WHERE course.tno=teacher.no and =张旭)；8/22/2022678/22/202268例22：输出教师“张旭”任课的学生成绩。SELECT o,score.no,score.degreeFROM scoreWHERE o= (SELECT o FROM cour

13、se,teacher WHERE course.tno=teacher.no and =张旭)；教师“张旭”任课的课程编号8/22/2022698/22/202270例23：输出选修人数超过4人的任课教师名单。SETECT FROM teacherWHERE teacher.no IN (SELECT course.tno FROM course,score WHERE o=o GROUP BY course.tno HAVING COUNT (course.tno)=4)；8/22/2022718/22/202272例24：输出成绩高于85分的

14、课程编号。SELECT DISTINCT oFROM scoreWHERE degree IN (SELECT score.degree FROM score WHERE score.degree85)；8/22/2022738/22/202274例25：输出计算机系中与电子工程系职称不同教师名单和职称。SELECT , fFROM teacherWHERE depart= 计算机系 and prof NOT IN (SELECT f FROM teacher WHERE teacher.depart=电子工程系);8/22/

15、2022758/22/202276例26：输出选修3-105课程，且成绩至少比选修3-245课程的其中一个学生成绩高的学生课程编号、学号和成绩字段，并按成绩从高到低次序排列。8/22/202277SELECT o, score.no, score.degreeFROM scoreWHERE o=3-105 and score.degreeANY (SELECT score.degree FROM score WHERE o=3-245) ORDER BY score.degree DESC;8/22/2022788/22/202279SELECT o, score.no, score.degr

16、eeFROM scoreWHERE o=3-105 and score.degreeANY (SELECT score.degree FROM score WHERE o=3-245) ORDER BY score.degree DESC;选修3-245课程成绩8/22/2022808/22/202281例27：输出所有教师和学生的name、sex、birthday字段。SELECT As 姓名,teacher.sex As 性别,teacher.birthday As 出生日期FROM teacherUNIONSELECT As 姓名,st

17、udent.sex As 性别,student.birthday As 出生日期FROM student可连接多个SELECT8/22/2022828/22/202283例28：输出所有女教师和女学生姓名、性别和出生日期字段。8/22/202284SELECT name As 姓名,sex As 性别,birthday As 出生日期FROM teacherWHERE sex=女UNION SELECT name As 姓名,sex As 性别,birthday As 出生日期FROM studentWHERE sex=女8/22/2022858/22/202286 前面的子查询，每个子查询仅执行一次，而有的子查询不止执行一次，这就需要相关子查询。6.3.7 相关子查询8/22/202287例29：输出成绩低于该课程平均成绩的学生成绩表。SELECT a.no, o, a.degreeFROM score AS aWHERE degree(SELECT AVG(degree)FROM score bWHERE o=o);8/22/2022888/22/202289 用于添加新记录，并可给新记录赋值。格式：INSERT INTO 表名(字段名1,字段名2,)VALUSE (赋值1,赋值2)6.4 SQL数据操纵功能6.4.1 INSERT指令8/