目录
实验软件:MYSQL Serve 8.0
实验目的:对基于课程被选中的次数做排名
实现方法:基于派生表的查询,窗口函数
知识目录:
1.查询语句的执行顺序:FROM → ON →JOIN → WHERE → GROUP BY → HAVING → SELECT→ ORDER BY → LIMIT
2.基于派生表的查询:当子查询出现在FROM子句中时,子查询生成的临时派生表则成为主查询的查询对象。
- 子查询:嵌套进查询语句2的查询语句1,查询语句1为子查询,查询语句2为主查询
- 区别于嵌套查询(将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询):基于派生表的查询的查询语句不一定要是查询块只要有SELECT-FROM就行。(查询块:SELECT-FROM-WHERE)
- 子句:括号内的语句
- 派生表的生命周期仅在其主查询内有效,不能够被越级查询
- 通过FROM子句生成派生表时,AS关键字可以省略,但必须为派生表指定一个别名(别名不可以是关键字,包含形如!的特殊字符)
3.外连接:table1 LEFT OUTTER JOIN table2 ON (table1.column=table2.column)
-
外连接:把悬浮元组也保留在结果中,而在其他属性(column)上填空值的连接
-
如果只保留左边关系中的悬浮元组则为左连接(left outer join或left join),只保留右边关系中的悬浮元组则为右外连接(right outer join或right join)
-
悬浮元组:在连接过程中因为不符合连接条件而被舍弃的元组
-
因为MYSQL不支持全外连接,故外连接必须指明是左外连接还是右外连接
4.窗口函数:RANK 并列名次相同,名次不连续
DENSE_RANK 并列名次相同,名次连续
ROW_NUMBER 不论并列与否,名次连续
5.变量:@变量名 := (变量值),可以赋具体值可以赋空值
赋值符号":=“,判断符号:”=";
6.判断语句
-
IF语句:IF(判断条件,true,failure);
-
CASE 语句:
CASE
WHEN (判断条件) THEN true ELSE failure
END
如果没有ELSE则会返回空值, CASE和END之前可以有多个WHEN……THEN……ELSE语句
创建实验数据库School
create database if not exists School;
create table if not exists Student(
Sno char(9) primary key,
Sname char(20) unique,
Ssex enum('男','女') default '男' comment'取值范围只能是男或女',
Smajor char(10), #专业
Sage smallint constraint check (sage>=16),
Sclass smallint);
create table if not exists Course(
Cno char(4) primary key,
Cname char(40) not null,
Cpno char(4), #先修课
Ccredit smallint); #学分
create table if not exists SC(
Sno char(9),
Cno char(4),
Grade smallint, #成绩
primary key(Sno,Cno),
foreign key(Sno) references Student(Sno),
foreign key(Cno) references Course(Cno));
create unique index Stusno on Student(Sno);
create unique index Coucno on Course(Cno)