数据库查询和操作

我不是女神ヾ 2023-06-25 06:28 12阅读 0赞

**数据库查询和操作**

**3.2****　单表查询**

1.  **3.2.1  ****投影运算**
2.  **3.2.2  ****选择运算** 
3.  **3.2.3  ****排序运算**
4.  **3.2.4  ****查询表**
5.  **3.2.5  ****聚合查询**

**SQL****基本结构包括****3****个子句：**

*  **SELECT****子句**
    
    1.  **对应投影运算，指定查询结果中所需要的属性或表达式**
 *  **FROM****子句**** **
    
    1.  **对应笛卡尔积，给出查询所涉及的表，表可以是基本表、视图或查询表**
 *  **WHERE****子句** 
    
    1.  **对应选择运算（包括连接运算所转化的选择运算），指定查询结果元组所需要满足的选择条件**

**3.2.1  ****投影运算**

**SELECT****和****FROM****是必须的，其他是可选的**

*  **基本语法为：**

**  SELECT** ***A*****1****,** ***A*****2****, ...,** ***A******n***

**  FROM** ***R*****1****,** ***R*****2****, ...,** ***R******m***

**  WHERE** ***P***

*   *   *  ***A*****1****,** ***A*****2****, ...,** ***A******n*****代表需要查找的属性或表达式**
         *  ***R*****1****,** ***R*****2****, ...,** ***R******m*****代表查询所涉及的表**
         *  ***P*****代表谓词****(****即选择条件****)****，如果省略****WHERE****子句，表示*****P*****为真**
         *  **SQL****的查询结果中允许包含重复元组**
 *  **SQL****执行过程（逻辑上的理解）：**
    
    1.  **首先对*****R*****1****,** ***R*****2****, ...,** ***R******m*****执行笛卡尔积**
    2.  **然后在笛卡尔积中选择使得谓词*****P*****为真的记录**
    3.  **再在*****A*****1****,** ***A*****2****, ...,** ***A******n*****属性列中进行投影运算，不消除重复元组**
        
         *  **如需消除重复元组，必须使用关键字****DISTINCT**

**刚才描述的****SQL****查询执行过程只是逻辑上的，在具体执行时会进行优化处理，查询优化的内容详见第****7****章。**

**SQL****执行过程（逻辑上的理解）：**

*  **首先对*****R*****1****,** ***R*****2****, ...,** ***R******m*****执行笛卡尔积**
 *  **然后在笛卡尔积中选择使得谓词*****P*****为真的记录**
 *  **再在****A1, A2, ..., An****属性列中进行投影运算，不消除重复元组**
 *  **如需消除重复元组，必须使用关键字****DISTINCT**

（一）查询指定列
    
    查询所有班级的班级编号、班级名称和所属学院
    
    SELECT classNo,className,institute
    
    FROM Class查询所有所属学院（上述查询不消除重复元组）
    
    SELECT institute
    
    FROM Class
    
    （二）消除重复元组
    
    消除重复元组，使用DINSTINCT关键字（distinct）
    
    SELECT DISTINCT institute
    
    FROM Class
    
    可使用两种方法
    
    将所有的列再SELECT子句中列出（可以改变列的显示顺序）：
    
    使用*符号，*表示所有属性，按照表定义时的顺序显示所有属性
    
    例：查询所有班级的全部信息
    
    select classNo,className,classNum,grade,institude
    
    FROM Class
    
    或者
    
    SELECT * FROM Class
    
    （三）给属性列取别名
    
    可以为属性取一个便于理解的列名，如果用中文来显示列名，可以为属性列取别，特别适合经过计算的列
    
    查询所有班级的所属学院，班级编号和班级名称，要求用中文显示列名
    
    select institute 所属学院,classNo 班级编号,className班级名称
    
    这种查询可以使用AS关键字取别名：
    
    select institute AS 所属学院,classNo AS 班级编号,className AS 班级名称
    
    from class
    
    （四）查询经过计算的列
    
    可使用属性，常数，函数和表达式
    
    查询每门课程的课程号，课程名以及周课时（周课时为课时数除以16），并将课程名中大写字母改为小写字母输出。
    
    select courseNo 课程号,lower(courseName)课程名,courseHour/16 AS 周课时
    
    FROM Course
    
    函数lower()将大写字母改为小写字母
    
    （五）选择运算
    
    WHERE子句可实现关系代数中的选择运算
    
    WHERE常用的查询条件有：
    
    比较运算：>,>=,<,<=,=,<>（或!=）
    
    范围查询：[NOT]BETWEEN<值1>AND<值2>
    
    集合查询：[NOT]IN<集合>
    
    空值查询：IS[NOT]null
    
    字符匹配查询:[NOT]LIKE<匹配字符串>
    
    逻辑查询:AND,OR,NOT
    
    使用比较运算符>,>=,<,<=,=,<>（或者!=）
    
    例:查询2015级的班级编号，班级名称和所属学院
    
    Select classNo,className,institute
    
    From class
    
    Where grade=2007
    
    查询每门课程的课程号，课程名以及周课时（周课时为课时数除以16），并且查询课程号的条件是001或者004,然后将课程名中大写字母改为小写字母输出。
    
    select courseNo 课程号,lower(courseName)课程名,courseHour/16 AS 周课时 FROM Course where courseNo=001 OR courseNo=004
    
    
    select * from course
    
    where courseNo=001 or courseNo=004 or courseNo=006
    
    ②
    
    SELECT classNo, className, institute
    
    FROM Class
    
    WHERE grade=2007
    
    在学生Student表中查询年龄大于或等于19岁的同学学号、姓名和出生日期。
    
    select studentNo,studentName,birthday
    
    from student
    
    where year(getdate())-year(birthday)>=19
    
    函数getdate()获取当前系统的日期
    
    函数year()提取日期中的年份
    
    （六）范围查询
    
    BETWEEN...AND用于查询属性值在某一个范围内的元组
    
    NOT BETWEEN...AND用于查询属性值不在某一个范围内的元组
    
    BETWEEN后是属性的下限值，AND后是属性的上限值
    
    [例3.8] 在选课Score表中查询成绩在80～90分之间的同学学号、课程号和相应成绩
    
    select studentNo,courseNo,score
    
    from score
    
    where score between 80 and 90
    
    呵呵哒自己写的多条件查找
    
    select studentNo,courseNo,score
    
    from score
    
    where score between 80 and 90 and courseNo=001 or courseNo=002 and studentNo=1500001
    
    在选课Score表中查询成绩不在80～90分之间的同学学号、课程号和相应成绩。
    
    SELECT studentNo, courseNo, score
    
    FROM Score
    
    WHERE score NOT BETWEEN 80 AND 90
    
    呵呵哒自己的改良版的查询
    
    SELECT studentNo, courseNo, score
    
    FROM Score
    
    WHERE score NOT BETWEEN 80 AND 90 AND courseNo=001 or courseNo=002 or courseNo=004
    
    集合查询
    
    IN用于查询属性值在某个集合内的元组
    
    NOT IN用于查询属性值不在某个集合内的元组
    
    IN后面是集合，可以是具体的集合，也可以是查询出来的元组集合
    
    在选课Score表中查询选修了“001”、“005”或“003”课程的同学学号、课程号和相应成绩。
    
    SELECT studentNo, courseNo, score
    
    FROM Score
    
    WHERE courseNo IN ('001', '005', '003')
    
    在选课Score表中查询选修不是“001”、“005”或“003”课程的同学学号、课程号和相应成绩。
    
    SELECT StudentNo, count(*) 门数, avg(score) 平均分,
    
                     max(score) 最高分
    
    FROM Score
    
    GROUP BY StudentNo
    
    HAVING studentNo in('1500001','1500004')
    
    
    
    select studentNo,courseNo,score
    
    from score
    
    where courseno not in('001','005','003')
    
    在学生 Student表中查询籍贯不是“南昌”或“上海”的同学姓名、籍贯和所属班级编号。
    
    select studentName,native,classNo
    
    from student
    
    where native not in("南昌","上海")
    
    （七）空值查询
    
    空值表示未知或不确定的值，空值表示为null
    IS null用于查询属性值为空值
    IS NOT null用于查询属性值不为空值
    IS不能用“=”替代
    [例3.12] 在课程Course表中查询先修课程为空值的课程信息.
    
    select *
    
    from course
    
    where priorCourse is null
    
    [例3.13] 在课程Course表中查询有先修课程的课程信息。
    
    select *
    
    from course
    
    where priorCourse is not null
    
    字符匹配查询
    
    LIKE用于字符匹配查询，语法格式为：
    
    [NOT]LIKE<匹配字符串>[ESCAPE]<换码字符>
    
    如果在LIKE前没有NOT，则查询指定的属性列值与<匹配字符串>相匹配的元组；
    
    如果在LIKE前有NOT，则查询指定的属性列值不与<匹配字符串>相匹配的元组。
    
    <匹配字符串>可以是一个具体的字符串，也可以包括通配符%和_
    
    %表示任意长度的字符串
    
    ab%，表示所有以ab开头的任意长度的字符串；
    
    zhang%ab，表示以zhang开头，以ab结束，中间可以是任意个字符的字符串。
    
    符号_(下划线)表示任意一个字符
    
    ab_，表示所有以 ab开头的3个字符的字符串，其中第3个字符为任意字符；
    
    a__b表示所有以a开头，以b 结束的4个字符的字符串，且第2、3个字符为任意字符。
    
    在学生Student表中查询所有姓王且全名为3个汉字的同学学号和姓名
    
    select studentNo,studentName from student where studentname like '李__'
    
    在班级Class表中查询班级名称中含有会计或者管理的班级信息
    
    select * from class
    
    where classname like '%会计%' or classname like '%管理%'
    
    在学生Student表中查询名字中不含有“福”的同学学号和姓名。
    
    select studentNo,studentName
    
    from student
    
    where studentname not like '%福%'
    
    在学生Student表中查询蒙古族的同学学号和姓名
    
    select studentNo,studentName
    
    from student
    
    where nation like '蒙古族'
    
    注意：如果匹配字符串中不含有%和_，则LIKE与比较运算符“=”的查询结果一样
    
    select studentNo,studentName
    
    from student
    
    where nation='蒙古族'该查询等价于下面的查询：
    
    在班级Class表中查询班级名称中含有“16_”符号的班级名称
    
    SELECT className
    
    FROM Class
    
    WHERE className LIKE '%16\_%' ESCAPE '\'
    
    “ESCAPE ‘\’”表示\为换码字符
    
    紧跟在\符号后的_不是通配符，而是普通的用户要查询的符号
    
    如果将#字符作为换码字符，则该查询可改写为：
    
    SELECT className
    
    FROM Class
    
    WHERE className LIKE '%16#_%' ESCAPE '#'
    
    逻辑查询
    
    SQL提供AND、OR和NOT逻辑运算符分别实现逻辑与、逻辑或和逻辑非运算
    
    [例3.19] 在选课Score表中查询选修了“001”、“005”或“003”课程的同学学号、课程号和相应成绩
    
    select studentNo,courseNo,score
    
    from score
    
    where courseNo='001'or courseNo='005' or courseNo='003'
    
    在例3.10中使用的是集合运算，本例中采用逻辑“或”运算
    
    [例3.20] 在Student表中查询1998年出生且民族为“汉族”的同学学号、姓名、出生日期。
    
    select studentNo,studentName,birthday
    
    from student
    
    where year(birthday)=1998 and nation='汉族'
    
    扩展
    
    select studentNo,studentName,birthday
    
    from student
    
    where year(birthday)=1999 and nation='汉族' or year(birthday)=1998 and nation='汉族'
    
    select studentNo,studentName,birthday
    
    from student
    
    where year(birthday) in ('1998','1999')
    
    [例3.20] 在Student表中查询1998年出生且民族为“汉族”的同学学号、姓名、出生日期。
    
    select studentNo,studentName,birthday
    
    from student
    
    where year(birthday)=1998 and nation='汉族'
    
    [例3.21] 在Student表中查询籍贯既不是“南昌”也不是“上海”的同学姓名、籍贯和所属班级编号。
    
    select studentName,native,classNo
    
    from student
    
    where native!='南昌' and native!='上海'
    
    [例3.22] 在选课Score表中查询成绩在80～90分之间的同学学号、课程号和相应成绩。
    
    select studentNo,courseNo,score
    
    FROM score
    
    where score>=80 and score<=90
    
    [例3.23] 在选课Score表中查询成绩不在80～90分之间的同学学号、课程号和相应成绩。
    
    select studentNo,courseNo,score
    
    from score
    
    where score<90 or score>90
    
    3.2.3  排序运算
    
    使用ORDER BY 子句实现排序运算，其语法为：
    
    ORDER BY <表达式1> [ASC | DESC] [, <表达式2> [ASC | DESC] ... ]
    
    其中：
    
    <表达式1>, <表达式2>,  ... 可以是属性、函数或表达式
    缺省按升序(ASC)排序
    按降序排序，必须指明DESC选项
    该运算含义是：
    
    在查询结果中首先按<表达式1>的值进行排序
    在<表达式1>值相等的情况下再按<表达式2>值排序
    依此类推
    在学生 Student表中查询籍贯既不是“南昌”也不是“上海”的同学姓名、籍贯和所属班级编号，并按籍贯的降序排序输出。
    
    ①SELECT studentName, native, classNo
    
       FROM Student
    
       WHERE native!='南昌'
    
             AND native!='上海'
    
       ORDER BY native DESC
    
    
    select studentName,native,classNo
    
    from student
    
    where native!='北京' and native!="上海"
    
    order by native ASC
    
    
    select studentNo,studentName,classNo,birthday
    
    from student
    
    where sex='女'
    
    order by classNo,month(birthday) ASC
    
    其中：month()函数表示提取日期表达式的月份
    
    3.2.4  查询表
    
    FROM子句后面可以是基本关系、视图，还可以是查询表
    
    [例3.26] 查询1999年出生的“女”同学基本信息。
    
    分析：可以先将学生表中的女生记录查询出来，然后再对查询表进行选择、投影操作。
    
    ①select studentNo,studentName,birthday
    
    from(select * from student where sex='女') as a where year(birthday)=1999
    
    ②select studentNo,studentName,birthday
    
    from(select * from student where sex='男') as a where year(birthday)=1998
    
    select studentNo,studentName,birthday
    
    ③该查询等价于下面的查询：
    
    from student
    
    where year(birthday)=1999 and sex='女'
    
    ④select studentNo,studentName,birthday
    
    from student
    
    where year(birthday)=1999 and sex='男'
    
    3.2.5 聚合查询
    
    SQL查询提供了丰富的数据分类、统计和计算的功能
    
    统计功能通过聚合函数来实现
    分类功能通过分组子句来实现
    统计和分组结合在一起实现丰富的查询功能
    SQL提供的聚合函数(aggregate function)包括：
    count( [DISTINCT | ALL] {* | <列名>} )：统计关系的元组个数或一列中值的个数；
    sum( [DISTINCT | ALL] <列名> )：统计一列中值的总和(此列必须为数值型)；
    avg( [DISTINCT | ALL] <列名> )：统计一列中值的平均值(此列必须为数值型)；
    max( [DISTINCT | ALL] <列名> )：统计一列中值的最大值；
    min( [DISTINCT | ALL] <列名> )：统计一列中值的最小值。
    指定DISTINCT谓词，表示在计算时首先消除<列名>取重复值的元组，然后再进行统计
    指定ALL谓词或没有DISTINCT谓词，表示不消除<列名>取重复值的元组
    [例3.27] 查询学生总人数。
    在SQL查询中，往往需要对数据进行分类运算（即分组运算）
    分组运算的目的是为了细化聚合函数的作用对象
    如不对查询结果分组，则聚合函数作用于整个查询结果
    如对查询结果进行分组，则聚合函数分别作用于每个组，查询结果按组聚合输出
    SQL通过GROUP BY和HAVING子句实现分组运算
    GROUP BY对查询结果按某一列或某几列进行分组，值相等的分为一组；
    HAVING对分组的结果进行选择，仅输出满足条件的组。    该子句必须与GROUP BY子句配合使用
    select count(distinct studentNo) from Score
    
    [例3.30] 查询每个同学的选课门数、平均分和最高分。
    
    ①
    
    SELECT StudentNo, count(*) 门数, avg(score) 平均分,
    
                     max(score) 最高分
    
    FROM Score
    
    GROUP BY StudentNo
    
    HAVING studentNo in('1500001','1500004')
    
    ②
    
    select studentNo,score
    
    from score
    
    group by studentNo
    
    having studentNo in('1500001','1500004') and score between 90 and 100
    
    ③
    
    select studentNo,count(*)门数,avg(score)平均分,max(score)平均分
    
    from score
    
    group by studentNo
    
    having avg(score)<=80
    
    按学号StudentNo分组，将StudentNo值相同的元组作为一组
    
    然后对每组进行计数、求平均值和求最大值
    
    并判断平均值是否大于等于80，如果是则输出该组，否则丢弃该组，不作为输出结果
    
    注意：例3.30和例3.31中是将重修的课程作为不同的课程来处理
    
    3.3　连接查询
    
    在实际应用中，往往会涉及到多个关系的查询，需用到连接运算或子查询
    ② 连接运算是关系数据库中使用最广泛的一种运算，包括等值连接、自然连接、非等值连接、自表连接和外连接等
    
    3.3.1  等值与非等值连接
    
    3.3.2  自表连接
    
    3.3.3  外连接
    
    3.3.1  等值与非等值连接
    
    该运算在WHERE子句中加入连接多个关系的连接条件
    格式为：
    WHERE [<表1>.]<属性名1> <比较运算符> [<表2>.]<属性名2>
    
                    [ <逻辑运算符>
    
                      [<表3>.]<属性名3> <比较运算符> [<表4>.]<属性名4> ... ]
    
    比较运算符包括：
    >、>=、<、<=、=、<>(或!=)
    当比较运算符为=时，表示等值连接
    其他运算为非等值连接
    WHERE子句的连接谓词中的属性称为连接属性
    连接属性之间必须具有可比性
    等值连接
    
    查找会计学院全体同学的学号、姓名、籍贯、班级编号和所在班级名称。
    
    该查询的结果为学号、姓名、籍贯、班级编号和班级名称，在SELECT子句中必须包含这些属性
    
    由于班级名称和所属学院在班级表Class中，学号、姓名、籍贯、班级编号在学生表Student中，FROM子句必须包含Class表和Student表
    
    由于班级编号classNo既是班级表的主码，也是学生表的外码，这2个表的连接条件是claaaNo相等，在WHERE子句中必须包含连接条件Student.classNo=Class.classNo
    
    本查询要查询出会计学院的学生记录，在WHERE子句中还必须包括选择条件institute='会计学院'
    
    select studentNo,studentName,native,student.classNo,className
    
    from student,class
    
    where student.classNo=class.classNo and institute='会计学院'
    
    在连接操作中，如果涉及到多个表的相同属性名，必须在相同的属性名前加上表名加以区分
    
    如Student.classNo、Class.classNo
    
    WHERE子句中
    
    Student.classNo=Class.classNo为连接条件
    
    institute='会计学院' 为选择条件
    
    [例3.33] 查找选修了课程名称为“计算机原理”的同学学号、姓名。
    
    查询结果为学号、姓名，在SELECT子句中必须包含这些属性
    
    学号和姓名在学生表中，课程名称在课程表中，FROM子句必须包含学生表Student、课程表Course
    
    学生表与课程表之间是多对多联系，需通过成绩表转换为两个多对一的联系，FROM子句必须包含成绩表Score
    
    课程号既是课程表的主码，也是成绩表的外码，这2个表的连接条件是课程号相等；学号既是学生表的主码，也是成绩表的外码，这2个表的连接条件是学号相等。在WHERE子句中涉及三个关系的连接，其连接条件为：
    
        Course.courseNo=Score.courseNo AND Score.studentNo=Student.studentNo
    
    查找选修“计算机原理”课程的同学，在WHERE子句中必须包括选择条件courseName='计算机原理'
    
    
    
    select a.studentNo,studentName
    
    from student a,course b,score c
    
    where b.courseNo=c.courseNo and c.courseNo=a.studentNo and b.courseName='计算机原理'
    
    
    
    呵呵哒自己写的一条查询语句
    
    将两个表连接起来
    select a.studentno,a.studentname,b.courseno,b.score
    
    from student a,score b
    
    where a.studentno=b.studentno
    
    为表示同时选修“001”和“002”课程的选择条件
    其次，基于成绩表Score构造一个查询表c，查找出选修了编号为“002”课程的所有同学
    
     （1）(SELECT * FROM Score WHERE courseNo='002') c
    
    select a.studentno,studentname,b.courseno,b.score,c.courseno,c.score
    
    from student a,score b,(select * from score where courseno='002')c
    
    where b.courseno='001' and a.studentno=b.studentno and a.studentno=c.studentno
    
    （2）select a.studentno,a.studentname,b.courseno,b.score,c.courseno,c.score
    
    from student a,score b,
    
    (select * from score where courseno='002')c
    
    where b.courseno='001' and a.studentno=b.studentno and a.studentno=c.studentno
    
    order by a.studentno
    
    （3）select a.studentno as 学号,studentname as 姓名,sum(credithour) as 总学分
    
    from student a,course b,score c
    
    where a.studentno=c.studentno and c.courseno=b.courseno and score>=60
    
    group by a.studentno,studentName
    
    having sum(credithour)>=28
    
    order by a.studentno
    
    （4）
    
    select a.studentno,studentname,sum(credithour)
    
    from student a,course b,score c
    
    where a.studentno=c.studentno and c.courseno=b.courseno and score>=60
    
    group by a.studentno,studentName
    
    having sum(credithour)>=28 and studentname="李小勇"
    
    order by a.studentno
    
    本查询既使用了WHERE子句，也使用了HAVING子句，都是选择满足条件的元组，但其选择的范围是不一样：
    
    (1) WHERE子句：作用于整个查询对象，对元组进行过滤。
    
    (2) HAVING子句：仅作用于分组，对分组进行过滤。
    
    本例的查询过程是：
    
      首先在Score 表中选择课程成绩大于等于60分的元组(只有60分及以上  才能获得学分)，将这些元组与Student和Score 表进行连接，形成一个新 关系；
      在新关系中按学号进行分组，统计每组的总学分；
      将总学分大于等于28的组选择出来形成一个结果关系；
     将结果关系输出。
    再根据查询条件在连接的两张表查询出对应的查询表出来
    select a.studentNo,studentName,b.courseNo,b.score
    
    From Student a,score b,(select * from score where courseno in('002','003','004'))c
    
    where b.courseNo='001' and a.studentno=b.studentno and a.studentno=c.studentno
    
    ⑤[例3.38] 在学生表Student中查找与“李宏冰”同学在同一个班的同学姓名、班级编号和出生日期。
    
    select a.studentname,a.classno,a.birthday
    
    from student a,student b
    
    where b.studentname='李宏冰' and a.classno=b.classNo
    
    或者
    
    select a.studentname,a.classno,a.birthday
    
    from student a,(select *from student where studentname='李宏冰')b
    
    where a.classno=b.classno