BUAA-SQL-数据库作业2（lr班）

SQL

字数统计: 8.4k阅读时长: 32 min

 2024/07/05 

第二次数据库作业

一、安装数据库

1.在计算机上安装一个DBMS软件

1.1 基本环境

环境：windows11 + mysql-8.0.28-winx64 命令行工具
下载链接：https://downloads.mysql.com/archives/community/
mysql-8.0.28-winx64目录下创建配置文件my.ini。

[mysqld]

# port
port=3306

# set basedir to your installation path
basedir=D:\\SOFTWARE\\mysql\\mysql-8.0.28-winx64

# set datadir to the location of your data directory
datadir=D:\\SOFTWARE\\mysql\\mysql-8.0.28-winx64\\data

# 以下设置utf8字符集
[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqld]
character-set-server=utf8

# 以下设置允许加载本地文件数据至数据库
[mysqld]
local_infile = 1

1.2 初始化

ctrl+X 启动 powershell(管理员模式)，进入bin目录
```
cd D:\SOFTWARE\mysql\mysql-8.0.28-winx64\bin
```
初始化，创建data文件夹
```
.\mysqld.exe --initialize-insecure
```
制作服务，此处命名为mysql157
```
.\mysqld.exe --install mysql157
```

1.3 每次启动数据库服务

ctrl+X 启动 powershell(管理员模式)，进入bin目录

cd D:\SOFTWARE\mysql\mysql-8.0.28-winx64\bin

启动服务

net start mysql157

注：

如果此处出现无服务报错，任务管理器中打开【详细信息】，把’mysqld.exe’进程关闭。

关闭sql的时候一定记得net stop mysql57!!!不能直接关闭powershell

连接：此处也可以不加--local-infile，需要向数据库导入本地文件数据时需要添加--local-infile参数。
初始化时，先设置密码再进行连接：
1. D:\SOFTWARE\mysql\mysql-8.0.28-winx64\bin添加到path环境变量。重启后生效。初始化的时候暂时先不重启。
  - 如果环境变量配置成功，可以直接 mysqld xxx命令。否则直接在bin目录下.\mysqld.exe xxx命令。mysql同理。
2. .\mysqld.exe --skip-grant-tables，用于稍后跳过密码输入
3. .\mysql.exe -h 127.0.0.1 -P 3306 -u root -p --local-infile输入密码直接回车跳过即可
4. set password = '123456'; 设置密码
5. 检查连接成功：show databases; 需要分号
6. 改字符集为utf8：set names utf8;

连接指令：

mysql -h 127.0.0.1 -P 3306 -u root -p --local-infile

1.4 每次需要手动关闭数据库服务，否则可能出现端口占用问题

连接后，会进入>mysql，使用exit;停止
net stop mysql57终止服务
关闭powershell即可
注：如果忘记net stop mysql57，可能start的时候会出现无服务报错，需要任务管理器中把’mysqld.exe’进程关闭。

2.默认数据库用途

mysql> show databases;

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

mysql 数据库

存储了 MySQL 服务器正常运行所需的各种系统信息，包含了关于数据库对象元数据的数据字典表和系统表等。从 MySQL 8.0 开始，mysql 系统表和数据字典表使用 InnoDB 存储引擎，存储在 MySQL 数据目录下的 mysql.ibd 表空间文件中。
information_schema 信息数据库
INFORMATION_SCHEMA 数据库提供了访问数据库元数据的各种视图，包括数据库、表、字段类型以及访问权限等。这些信息有时候也被称为数据字典（data dictionary ）或者系统目录（system catalog），主要来源就是 mysql 系统数据库中的数据字典表。INFORMATION_SCHEMA 中的表实际上都是只读的视图，只能执行查询操作，不能执行 DML 语句。
performance_schema 性能数据
performance_schema 性能数据库为 MySQL 服务器的运行时状态提供了一个底层的监控功能。
MySQL 默认启动了性能数据库，也可以在启动服务时通过参数 performance_schema 指定是否启用。
sys 数据库
MySQL 5.7.7 引入了 sys 数据库，其中包含了一系列的视图，可以方便 DBA 和开发人员解释 performance_schema 性能数据库中收集的数据。sys 数据库中的对象主要用于性能调优和诊断，包括：
- 视图，将性能数据库中的数据进行汇总，以更加容易理解的形式进行展示。
- 存储过程，配置性能数据库以及生成诊断报告等操作。
- 存储函数，用于查询性能数据库的配置和格式化服务。

3.字符集和排序规则

查看字符集

mysql> show variables like 'character%';

字符集的设置可以在MySQL实例、数据库、表、列四个级别。未显示设置时会继承字符集和排序规则。
查看当前MySQL支持的字符集的方式有两种，一种是通过查看information_schema.character_set系统表，一种是通过命令show character set查看
每个指定的字符集都会有一个或多个支持的排序规则，可以通过两种方式查看，一种是查看information_schema.collations表，另一种是通过show collation命令查看
每个字符集可以对应多个排序规则，但每个排序规则只能对应一个字符集
排序规则的命令通常是以对应的字符集的名字为开头，并以自己的特定属性结尾，比如排序规则utf8_general_ci和latin1_swedish_ci就分别是对应utf8和latin1字符集的排序规则。mysql8默认情况下的字符集是utf8mb4

部分字符集为gbk，会出现中文属性乱码等问题，需要设置成utf8。修改my.ini配置文件重启并set names utf8;即可
mysql：>=5.5.49的版本，字符集是utf-8下，char(10)和varchar(10)存储的汉字和英文的数量都是10个

4.从其他同学的电脑上访问刚刚安装的数据库

如果my.ini配置文件中有bind-address 127.0.0.1，注释掉此行，允许数据库被非本地ip访问
进入mysql数据库

use mysql

授权来自任何ip可以以用户名root登录数据库

update user set host='%' where user='root';
flush privileges;

（mysql8.0对于设置数据库允许远程操作的命令有更新）先创建用户，此处建议使用自己数据库的密码
```
create user root@'%' identified by '123456';
flush privileges;
```
如果遇到以下报错：应该是之前创建，执行删除的时候没有删除干净。此时重新进行删除

ERROR 1396 (HY000): Operation CREATE USER failed for ‘root‘@‘%’

如果报错执行以下命令，再尝试创建用户。

drop user root@'%';
flush privileges;

成功创建用户后，给用户授权

grant all privileges on *.* to 'root'@'%';
flush privileges;

测试远程访问：

由于之前启动服务均使用的本地ip：mysql -h 127.0.0.1 -P 3306 -u root -p

所以测试远程访问时，查到自己电脑的ip，我的为10.24.167.173。使用mysql -h 127.0.0.1 -P 3306 -u root -p即可成功使用预设密码访问。

注：此处使用自己电脑测试，但是使用非本地ip，即可达到测试效果。如果使用别人电脑，需要保证两个电脑处在相同局域网下，保证ip可以互相ping通。

测试结果如下：

二、在school数据库中完成以下练习

创建数据库
```
mysql> create database school;
```
进入数据库：
```
mysql> use school;
```

2.1 创建上述表，并定义相应的完整性约束。

2.1.1 一些针对可能出现的数据库版本标准不同的补充说明：(在mysql8.0.28下)

mysql>=5.5.49的版本，字符集是utf-8下，char(10)和varchar(10)存储的汉字和英文的数量都是10个
mysql8.0，默认属性的设置可以不使用枚举，使用check约束：check ((性别 = '男') or (性别 = '女'))

2.1.2 建表：

由于存在外码依赖，建表顺序为：系表->课程表->学生表->选课表。

系表（系号，系名，系主任），其中系号是主码，系名不能有重复的。

create table 系表
(系号 int not null,
系名 varchar(10) unique,
系主任 varchar(3),
primary key(系号)
);

课程表（课程号，课程名，先修课，学分），课程号是主码，课程名必须唯一，学分必须大于0小于5。

create table 课程表
(课程号 char(3) not null,
课程名 varchar(8) unique,
先修课 char(3),
学分 int,
primary key(课程号),
check (学分 > 0 and 学分 < 5)
);

对于课程表建表的说明：

check (学分 > 0 and 学分 < 5)使用check约束时可以不命名，数据库会自动为check约束命名。但是后续修改约束时不方便。详见题目2.7的说明。
mysql8.0也可以建表时为约束命名，使用constraint关键字，例如constraint 学分约束 check (学分 > 0 and 学分 < 5)，学分约束为对此check约束的命名。
为避免中文属性乱码等问题，字符集设置成utf8。已在修改my.ini配置文件修改。

学生表（学号，姓名，性别，年龄，入学年份，籍贯，系号，班长学号），学号是主码，系号和班长学号是外部码，手机号码必须唯一，学生的年龄不得小于10岁和大于50岁，性别必须是’男’或者’女’。

create table 学生表
(学号 varchar(4) not null,
姓名 varchar(3),
性别 char(1),
年龄 int,
入学年份 varchar(4),
籍贯 char(2),
系号 int,
班长学号 varchar(4),
手机号码 char(11),
primary key(学号),
foreign key (系号) references 系表(系号),
unique(手机号码),
check (年龄 >=10 and 年龄 <=50),
check ((性别 = '男') or (性别 = '女'))
);

对于学生表建表的说明：

学号和入学年份设置varchar因为后续题目需要插入其他长度的数据。也可以此处设置为var定长，后续需要使用变长时再使用alter table 学生表 modify column 学号 varchar(4);语句，修改类型为varchar。
由于后续导入数据时，发现对于**”学生表”和“系表”中，对 ‘系号’ 的前导零处理不一致。如果把‘系号’**设置为varchar类型，无法把’6’和’06’识别为同系，导致导入数据时，大量数据会违背系号的外码约束foreign key (系号) references 系表(系号)而不被导入。所以此处系号选择int类型。
unique约束 unique(手机号码) 需要数据表中存在手机号码属性，故加入属性手机号码 char(11)。
此处建表时，舍去设置**’班长学号’**的外码约束foreign key (班长学号)references 学生表(学号)：
- 此处为导入数据时，发现如果建表时，预先设置此**’班长学号’**外码约束，会出现对按行导入数据时，数据的先后顺序要求。（如果学生A的班长B在学生A的数据之后出现，则在导入学生A时，其违背了外码约束（班长B的数据还尚未被导入，未在学生表的主码中出现），故数据A不会被导入，发生错误。
- 原因是**’班长学号’和其主码‘学号’都在“学生表”**数据表中。
- 解决方案是，对于主码外码在一个表里的情况，先建表，并导入数据，导入数据之后再使用alter table 学生表 add constraint 学生班长外码约束 foreign key (班长学号) references 学生表(学号);添加外码约束。
mysql8.0，默认属性的设置可以不使用枚举，使用check约束：check ((性别 = '男') or (性别 = '女'))

选课表（学号，课程号，成绩），（学号，课程号）是主码，学号和课程号是外部码，成绩不能小于0分和大于100分

create table 选课表
(学号 varchar(6) not null,
课程号 char(3) not null,
成绩 numeric(4,1),
primary key (学号,课程号),
foreign key (学号) references 学生表(学号),
foreign key (课程号) references 课程表(课程号),
check (成绩 <= 100 and 成绩 >= 0)
);

numeric(4,1)是3位整数,精确到一位小数,最多4位,四舍五入

2.2 导入数据与对错误数据的分析

2.2.1 一种导入数据的方法

把excel的多个sheet分别另存为.csv格式。（此时.csv内字符串的前导0均不显示，如果使用txt打开可正常显示前导0，说明没有问题。否则可能是在csv内对源数据进行了改动，需要把源数据重新复制进.csv）
把.csv的第一行属性名删除。（或者稍后的sql导入语句添加ignore 1 lines，忽略第一行属性名的导入）
把.csv使用txt打开，把其ANSI格式另存为utf8格式。（注：每次对.csv使用excel视图修改后，均需要转换为utf8格式）。
保证启动数据库时使用--local-infile参数：mysql -h 127.0.0.1 -P 3306 -u root -p --local-infile。

否则导入本地数据可能出现报错：

ERROR 3948 (42000): Loading local data is disabled; this must be enabled on both the client and server sides

导入命令：

load data local infile 'C:\\Users\\Musel\\Desktop\\xueshengbiao.csv' 
into table 学生表 
fields terminated by ',' ;

# 如果没有在.csv内删除第一行属性名，需要加入 ignore 1 lines
# 此处可以不加行分隔符'\n'的命令，也可以正常识别换行并导入

2.2.2 对错误数据的分析

第一次使用上述命令导入学生表等数据后，发现skipped了大量数据，没有合法数据被加进学生表。选课表也存在一定问题。

使用show warnings;查看warnings提示。

发现的错误数据的类型如下：

导入数据时，发现对于**”学生表”和“系表”中，对 ‘系号’ 的前导零处理不一致。如果把‘系号’**设置为varchar类型，无法把’6’和’06’识别为同系，导致导入数据时，大量数据会违背系号的外码约束foreign key (系号) references 系表(系号)而不被导入。所以此处系号选择int类型。
导入数据时，发现如果建表时，预先设置此**’班长学号’**外码约束，会出现对按行导入数据时，数据的先后顺序要求。（如果学生A的班长B在学生A的数据之后出现，则在导入学生A时，其违背了外码约束（班长B的数据还尚未被导入，未在学生表的主码中出现），故数据A不会被导入，发生错误。
- 原因是**’班长学号’和其主码‘学号’都在“学生表”**数据表中。
- 解决方案是，对于主码外码在一个表里的情况，先建表，并导入数据，导入数据之后再使用alter table 学生表 add constraint 学生班长外码约束 foreign key (班长学号) references 学生表(学号);添加外码约束。
修改上面两个问题后，可以成功导入所有学生表数据。
- 如果使用alter table 学生表 add constraint 学生班长外码约束 foreign key (班长学号) references 学生表(学号);添加外码约束时，发现报错：
  
  ERROR: Cannot add or update a child row: a foreign key constraint fails…
- 原因：使用select * from 学生表，发现**’班长学号’**列，存在空字符串””。空字符串和NULL并不是相等的。外码允许NULL，但是主码中没有””空字符串的学号。
  
  使用update 学生表 set 班长学号=NULL where 学号='0202';命令把0202，0301，0402三个空字符串都改成NULL值，就可以使用alter table 学生表 add constraint 学生班长外码约束 foreign key (班长学号) references 学生表(学号);添加外码约束了。
对于选课表，有四条数据主键重复，只导入了各自重复主键的第一条数据。数据库跳过重复主键，未报错。
有两条数据的学号外码在学生表中不存在，数据库跳过这两条数据，未报错。

2.3 在学生表中插入学号为26，姓名为’李四’，性别为’女’，年龄为20，入学年份为2008，籍贯为’广东’，手机号码为10010001000，班长学号为10的一条记录

没有学号10的班长，所以添加：insert into 学生表(学号) values('10');
如果char类型不是变长，alter table 学生表 modify column 学号 varchar(4);
如果没有手机号列，添加：alter table 学生表 add 手机号码 char(11);

insert into 学生表 (学号,姓名,性别,年龄,入学年份,籍贯,班长学号,手机号码) 
values('26','李四','女',20,'2008','广东','10','10010001000');

2.4 删除上述记录

delete from 学生表 where 学号='26';

2.5 将学生表中的姓名字段的长度改为6个汉字

alter table 学生表 modify column 姓名 varchar(6);

2.6 为学生表增加一个字段电子邮件，20个字符

alter table 学生表 add 电子邮件 varchar(20);

2.7 对课程表的学分字段上的完整性约束进行修改，使其在0到6之间取值

# 先删除旧约束。再添加新约束
# 由于初始化建表时没有对check约束显式命名，mysql默认为其命名
# 所以需要先获取约束的默认名字
insert into 课程表 values('c09','xx','c01',9);
> ERROR 3819 (HY000): Check constraint '课程表_chk_1' is violated.

# 删除约束
alter table 课程表 drop constraint 课程表_chk_1;
 
# 添加新约束
alter table 课程表 add constraint 课程学分check check(学分>0 and 学分<6);

2.8 为学生表在学号列上创建cluster索引

mysql默认为主键建立聚簇索引，使用show index查看

show index from 学生表;

或者创建：

create index cluster on 学生表(学号);

2.9 创建一个视图，计算每门课的最高分

create view 课程最高分 as 
select 课程号,max(成绩) as 最高分 from 选课表
group by 课程号;

2.10 查找每个学生的学号、姓名、总成绩和平均分

select 学生表.学号,姓名,sum(成绩) as 总成绩,avg(成绩) as 平均分
from 学生表,选课表
where 学生表.学号=选课表.学号
group by 学生表.学号;

2.11 将6系所有学生的年龄，改为7系学生的平均年龄

没有7系学生? null了

# 错误写法：
update 学生表 set 年龄 = (select avg(年龄) from 学生表 where 系号=7 ) where 系号=6;
> "You can't specify target table '学生表' for update in FROM clause"

# 如果修改数据，需要为查询结果建立子表
update 学生表 
set 年龄 = (select tmp.平均年龄 
    from(
        select avg(年龄) as 平均年龄 from 学生表 where 系号 = 7 
    )tmp ) 
where 系号=6;

2.12 将’曹洪’同学操作系统课程的成绩改为62分

update 选课表 
set 成绩=62
where (学号=(select 学号 from 学生表 where 姓名='曹洪') 
and 课程号=(select 课程号 from 课程表 where 课程名='操作系统'));

2.13 查找所有学生的姓名、入学年份和籍贯

select 姓名,入学年份,籍贯 from 学生表;

2.14 列出籍贯为’山东’的同学的所有属性

select * from 学生表 where 籍贯='山东';

2.15 查找年龄最小的学生的学号和姓名

select 学号,姓名 from 学生表 
where 年龄 = (select min(年龄) from 学生表);

2.16 查找选修了’数据库’的学生的学号

select 学号 from 选课表 
where 课程号 in (select 课程号 from 课程表 where 课程名='数据库');

因为课程名可能不唯一，所以可能select到多个同名课程号，应该用in

2.17 查找选修了’编译技术’的女学生的学号和姓名

select 学号,姓名 from 学生表 
where 性别 = '女' 
and 学号 in 
  (select 学号 from 选课表 
  where 课程号 in 
      (select 课程号 from 课程表 where 课程名='编译技术'));

2.18 查找’典韦’同学的班长所选修的课程的课程号

select 课程号 from 选课表
where 学号 in (select 班长学号 from 学生表 where 姓名 = '典韦') ;

2.19 查找名字中倒数第二字为’侯’的学生的学号、姓名和所在系的系名

select 学号,姓名,系名 from 学生表,系表
where 姓名 like '%侯_'
and 系表.系号 = 学生表.系号;

# 或者正则：
select 学号,姓名,系名 from 学生表,系表
where 姓名 REGEXP '侯[:alpha:]{1}$'
and 系表.系号 = 学生表.系号;

2.20 查找名字以P打头，倒数第三字为L的课程的名字

select 课程名 from 课程表
where 课程名 REGEXP 'P[:alpha:]*L[:alpha:]{2}';

2.21 查找’甘宁’同学所有选修课程的总分

select sum(成绩) from 选课表
where 学号 in (select 学号 from 学生表 where 姓名='甘宁');

2.22 查找既选修了’数据库’，也选修了’操作系统’的同学

select * from 学生表
where 学号 in ( select 学号 from 选课表
    where 课程号 in (select 课程号 from 课程表 where 课程名='操作系统')
and 学号 in (select 学号 from 选课表 
    where 课程号 in (select 课程号 from 课程表 where 课程名='数据库')));

2.23 查找没有选修’数据库’课程的学生的学号和姓名

select 学号,姓名 from 学生表
where not exists
    (select * from 选课表 
        where 学号 = 学生表.学号 
        and 课程号 = (select 课程号 from 课程表 where 课程名='数据库'));

2.24 查找’数据库’课程及格了，但’编译技术’没有及格的学生的学号和姓名

select 学号,姓名 from 学生表
where 学号 in (select 学号 from 选课表
    where 课程号 in (select 课程号 from 课程表 where 课程名='数据库')
    and 成绩>=60
    and 学号 in (select 学号 from 选课表 
        where 课程号 in (select 课程号 from 课程表 where 课程名='编译技术') 
        and 成绩<60
    )
);

2.25 查找数据库成绩低于数据库课平均成绩的同学的学号和姓名

select 学号,姓名 from 学生表
where 学号 in (select 学号 from 选课表
    where 课程号 in (select 课程号 from 课程表 where 课程名='数据库')
    and 成绩 < (select avg(成绩) from 选课表
        where 课程号 in (select 课程号 from 课程表 where 课程名='数据库'))
);

2.26 查找与’貂蝉’同学选修课程完全相同的学生的学号和姓名（不能多选也不能少选）

# ==
Select 学号,姓名 From 学生表
    Where Not Exists 
    # 这个select语句的返回值：对于每个貂蝉选的课程，如果返回值为真，把这个课程作为select返回的集合里的课程
        (Select * from 
            (select * from 课程表 where 课程号 in 
                (select 课程号 from 选课表 where 学号=
                    (select 学号 from 学生表 where 姓名='貂蝉'))) 
            as tmp
            # 对于（学号，课程号）的元组，选课表里有不存在的值。
            # 等价于：
            # 以下语句的返回值：是否找到【（这个学生）没选的（貂蝉课表里有的课）】这样的元组
            Where Not Exists
                (Select * from 选课表
                    Where 学号=学生表.学号 
                    And 课程号=tmp.课程号)
            )
    and Not Exists 
    # 这个select语句的返回值：对于每个貂蝉 没 选的课程，如果返回值为真，把这个课程作为select返回的集合里的课程
        (Select * from 
            (select * from 课程表 where not exists
                (select 课程号 from 选课表 where (课程号 = 课程表.课程号)
                    and 学号= (select 学号 from 学生表 where 姓名='貂蝉')))
            as tmp2
            # 对于（学号，课程号）的元组，选课表里有 存在 的值。
            # 等价于：
            # 以下语句的返回值：是否找到【（这个学生）选的（貂蝉课表里 没有 的课）】这样的元组
            Where Exists
                (Select * from 选课表
                    Where 学号=学生表.学号 
                    And 课程号=tmp2.课程号)
            );

2.27 查找不仅选修了’貂蝉’同学选修的课程，而且还选修了其他课程的同学

# >=
Select 学号,姓名 From 学生表
    Where Not Exists 
    # 这个select语句的返回值：对于每个貂蝉选的课程，如果返回值为真，把这个课程作为select返回的集合里的课程
        (Select * from 
            (select * from 课程表 where 课程号 in 
                (select 课程号 from 选课表 where 学号=
                    (select 学号 from 学生表 where 姓名='貂蝉'))) 
            as tmp
            # 对于（学号，课程号）的元组，选课表里有不存在的值。
            # 等价于：
            # 以下语句的返回值：是否找到【（这个学生）没选的（貂蝉课表里有的课）】这样的元组
            Where Not Exists
                (Select * from 选课表
                    Where 学号=学生表.学号 
                    And 课程号=tmp.课程号)
            )
    and Exists 
    # 这个select语句的返回值：对于每个貂蝉 没 选的课程，如果返回值为真，把这个课程作为select返回的集合里的课程
        (Select * from 
            (select * from 课程表 where not exists
                (select 课程号 from 选课表 where (课程号 = 课程表.课程号)
                    and 学号= (select 学号 from 学生表 where 姓名='貂蝉')))
              as tmp2
            # 对于（学号，课程号）的元组，选课表里有 存在 的值。
            # 等价于：
            # 以下语句的返回值：是否找到【（这个学生）选的（貂蝉课表里 没有 的课）】这样的元组
            Where Exists
                (Select * from 选课表
                    Where 学号=学生表.学号 
                    And 课程号=tmp2.课程号)
            );

2.28 查找’高等数学’平均成绩最高的系的系名

没有高等数学，只有数学？
```sql
方法一：
先建立(平均分，系号)视图
create view 系平均分视图 as
select avg(成绩) as 平均分,系号 from 选课表,学生表
where 课程号 in (select 课程号 from 课程表 where 课程名 = ‘数学’)
and 选课表.学号=学生表.学号
group by 系号

再从视图上找最高分对应的系

select 系名 from 系表
where 系号 =
(select 系号 from 系平均分视图
where 平均分 = (select max(平均分) from 系平均分视图);

方法二：写在一条语句，不过方法一的视图需要作为子表tmp，并且创建两次

select 系名 from 系表
where 系号=
(select 系号 from
# 第一次创建子表tmp(平均分，系号)
(select avg(成绩) as 平均分,系号 from 选课表,学生表
where 课程号 in (select 课程号 from 课程表 where 课程名 = ‘数学’)
and 选课表.学号=学生表.学号
group by 系号) as tmp

where 平均分 = 
    (select max(平均分) from 
        # 第二次创建子表tmp(平均分，系号)
        (select avg(成绩) as 平均分,系号 from 选课表,学生表 
        where 课程号 in (select 课程号 from 课程表 where 课程名 = '数学') 
        and 选课表.学号=学生表.学号
        group by 系号) as tmp)
);


##### 2.29 查找至少有一个籍贯为'四川'同学所选修课程的课程名
- 没有四川人?
```sql
select distinct 课程名 from 课程表,选课表
where 学号 in
(select 学号 from 学生表 where 籍贯 = '四川')
and 课程表.课程号 = 选课表.课程号;

# 或者
select distinct 课程名 from 课程表,选课表
where exists
(select * from 学生表 where 籍贯 = '安徽' and 学生表.学号 = 选课表.学号)
and 课程表.课程号 = 选课表.课程号;

# 一开始写的exists语句忘了写两个表格的学号约束，错误地返回了所有数据
# select distinct 课程名 from 课程表,选课表
# where exists
# (select * from 学生表 where 籍贯 = '安徽')
# and 课程表.课程号 = 选课表.课程号;

2.30 查询选修了’数据库’课程的学生的学号和获得的学分

select 学号,学分 from 选课表,课程表
where 选课表.课程号 = (select 课程号 from 课程表 where 课程名 = '数据库')
and 选课表.课程号=课程表.课程号;
# 记得写表名  选课表.课程号
# 记得写连接约束

三、回答问题

3.1 SQL语言的特点

综合统一：集数据定义，数据操纵，数据查询和数据控制于一体。单一的结构——关系，带来了数据操作符的统一。
高度非过程化：用户只需提出“做什么”，无须告诉“怎么做”，不必了解存取路径。
面向集合的操作方式：操作的对象和操作的结果均为集合。
以同一种语法结构提供两种使用方式：SQL既是自含式语言，又是嵌入式语言
语言简捷，易学易用：类似于英语自然语言

3.2 完整性约束

NOT NULL：非空约束：字段的值不能为空
UNIQUE：唯一约束：指所有记录中字段的值不能重复出现
PRIMARY KEY：主码约束，唯一约束 + 非空约束
FOREIGN KEY：外码约束：用于限制两个表的关系，保证从表该字段的值必须来自于主表相关联的字段的值
CHECK：mysql8.0新特性，约束表中的某个字段或者一些字段必须满足某个条件。

3.3 自然连接和等值连接有什么差别

等值连接是WHERE子句中用来连接两个表的连接条件的连接符是=。连接条件的各连接字段类型必须可比。
自然连接是一种特殊的等值连接，它要求两个关系中进行比较的分量必须是相同的属性组，并且在结果中把重复的属性列去掉。

https://developer.aliyun.com/article/879135

3.4 子查询分为哪几种？它们之间有什么区别

不相关子查询
不相关子查询的执行顺序是由内向外，即每个子查询在上一级查询处理前求解，子查询的结果集合作为其外部查询的检索条件的条件值。
相关子查询
相关子查询的查询条件引用了其外部查询的某个属性值，从而其执行依赖于其外部查询。
区别：子查询的查询条件是否依赖父查询。

3.5 索引有什么作用和缺点

作用：加快查询速度，提供多种存取路径。

缺点：占用一定存储空间。基本表更新时，索引要进行维护。

3.6 基本表和视图有什么区别？视图有什么优点？什么样的视图是可以更新的？

视图是从一个表或者几个基本表（视图）中导出的表。与基本表不同，是一个虚表。数据库只存放视图的定义，不存放视图对应的数据。基本表的数据发生变化，视图查询出的数据随之改变。
视图的优点：
- 简化用户的操作
- 以多种角度看待同一数据
- 对重构数据库提供了一定程度的逻辑独立性
- 对机密数据提供安全保护
行列子集视图可以更新。行列子视图：从单个基本表导出的视图，并且只是去掉了基本表的某些行和某些列，但保留了主码。

3.7 请针对第三章SQL语言讲义中的除法例子，给出其他两种除法的实现方法

第三章讲义的除法例子：查询选修了全部课程的学生姓名

关系代数运算：$\prod _{Sno,Cno}(SC)÷\prod _{Cno}(COURSE)\bowtie\prod _{Sno,SName}(STUDENT)$

方法一：双not exists法

Select Sname From STUDENT
    Where Not Exists 
          (Select * from COURSE
              Where Not Exists
                  (Select * from SC
                     Where Sno=STUDENT.Sno 
                          And Cno=COURSE.Cno));

方法二和三的注释中，对于定义中的设关系R（X，Y）与关系S（Z），其中Y和Z具有相同的属性数，且对应属性出自相同域，

称域X为被除表的独有属性

域Y为被除表的共有属性

域Z为除表的共有属性

方法二：Count(*)法

count(*)做除法的条件：被除表的共有属性与除表的共有属性，应当是外部码关系，或者至少二者可以满足构成外部码的约束。

也就是对于定义设关系R（X，Y）与关系S（Z），其中Y和Z具有相同的属性数，且对应属性出自相同域，要保证$Y\subseteq Z$

Select Sname From STUDENT
where Sno in (
    Select Sno from SC group by Sno
    having count(Sno) = ( select count(*) from COURSE )
);

方法三：定义法

定义：设关系R（X，Y）与关系S（Z），其中Y和Z具有相同的属性数，且对应属性出自相同域。关系R除以关系S所得的商关系是关系R在属性X上投影的一个子集，该子集和 S（Z）的笛卡儿积必须包含在R（X，Y）中。
记为：$R÷S={t;|;t \in {x}(R) ;;\wedge;;s \in S ;;\wedge;; <t,s> \in R }$

域X和域Z 做广义笛卡尔积，如果新乘出来得到的新元组值在原来的关系集合R里，这个值保留进除法得到的结果里。

# 与学生表做连接，得到姓名
Select Sname From STUDENT
where Sno in (

# select出 被除表中 做除法剩下的 独有属性
select Sno From SC
where Sno not in(

    # select出 被除表中 应该被除掉的 独有属性
    select Sno from

    # 【被除表的独有属性】投影 与 【除表的共有属性】投影 做广义笛卡尔积
    # 得到子表 b1
    (select Sno from SC as a1
    cross join
    select Cno from COURSE as a2) as b1

    # 对于每个广义笛卡尔积表的独有属性和共有属性，检查被除表中是否存在这个元组（由做笛卡尔积的过程知，独有属性从被除表得到，所以一定存在。检查共有属性即可），返回【共有属性不在被除表里存在】的元组的独有属性
    where b1.Cno not in (select Cno from SC where SC.Sno = b1.Sno)
));

Previous Post

Adobe Pr 2020 经验记录帖

CATALOG

1. 第二次数据库作业
1. 1.1. 一、安装数据库
2. 1.2. 二、在school数据库中完成以下练习

2. 方法一：

3. 先建立(平均分，系号)视图

4. 再从视图上找最高分对应的系

5. 方法二：写在一条语句，不过方法一的视图需要作为子表tmp，并且创建两次

5.0.0.0.1. 2.30 查询选修了’数据库’课程的学生的学号和获得的学分

5.1. 三、回答问题

5.1.0.0.1. 3.1 SQL语言的特点
5.1.0.0.2. 3.2 完整性约束
5.1.0.0.3. 3.3 自然连接和等值连接有什么差别
5.1.0.0.4. 3.4 子查询分为哪几种？它们之间有什么区别
5.1.0.0.5. 3.5 索引有什么作用和缺点
5.1.0.0.6. 3.6 基本表和视图有什么区别？视图有什么优点？什么样的视图是可以更新的？
5.1.0.0.7. 3.7 请针对第三章SQL语言讲义中的除法例子，给出其他两种除法的实现方法