SQL

|
create table 学院 (
    学院代码 varchar(10),
    学院名称 varchar(50),
    办公楼 varchar(30),
    预算 numeric(12,2),
    primary key (学院代码)
);

|
create table 学生 (
    学号 varchar(10),
    姓名 varchar(20) not null,
    学院代码 varchar(10),
    入学年份 int,
    primary key (学号),
    foreign key (学院代码) references 学院(学院代码)
);

|
create table 课程 (
    课程代码 varchar(8),
    课程名称 varchar(100),
    学院代码 varchar(10),
    学分 numeric(2,0),
    primary key (课程代码),
    foreign key (学院代码) references 学院(学院代码)
);

|
alter table 学生 add 手机号码 varchar(15);

|
alter table 学生 drop 手机号码;

|
drop table 课程;

|
delete from 学生;

|
-- 插入学院信息
insert into 学院 values 
('CS', '计算机学院', '信息大楼', 2500000.00),
('MATH', '数学学院', '理学大楼', 1800000.00),
('PHYS', '物理学院', '理学大楼', 2200000.00);
 
-- 插入学生信息
insert into 学生 values

|
select 姓名 from 学生;

|
+--------+
| 姓名   |
+--------+
| 张三   |
| 李四   |
| 王五   |
| 赵六   |
+--------+

|
select 学号, 姓名 from 学生;

|
+---------+--------+
| 学号    | 姓名   |
+---------+--------+
| 2021001 | 张三   |
| 2021002 | 李四   |
| 2021003 | 王五   |
| 2022001 | 赵六   |
+---------+--------+

|
select * from 学生;

|
+---------+--------+--------------+--------------+
| 学号    | 姓名   | 学院代码     | 入学年份     |
+---------+--------+--------------+--------------+
| 2021001 | 张三   | CS           |         2021 |
| 2021002 | 李四   | MATH         |         2021 |
| 2021003 | 王五   | CS           |         2021 |
| 2022001

|
select 姓名, 学号 from 学生 
where 学院代码 = 'CS';

|
+--------+---------+
| 姓名   | 学号    |
+--------+---------+
| 张三   | 2021001 |
| 王五   | 2021003 |
+--------+---------+

|
select 姓名, 学号 from 学生 
where 学院代码 = 'CS' and 入学年份 = 2021;

|
select 学院代码 from 学生;

|
+--------------+
| 学院代码     |
+--------------+
| CS           |
| CS           |
| MATH         |
| PHYS         |
+--------------+

|
select distinct 学院代码 from 学生;

|
+--------------+
| 学院代码     |
+--------------+
| CS           |
| MATH         |
| PHYS         |
+--------------+

|
select 学生.姓名, 学生.学院代码, 学院.学院名称
from 学生, 学院
where 学生.学院代码 = 学院.学院代码;

|
select 姓名, 学院代码, 学院名称
from 学生 natural join 学院;

|
+--------+--------------+-----------------+
| 姓名   | 学院代码     | 学院名称        |
+--------+--------------+-----------------+
| 张三   | CS           | 计算机学院      |
| 王五   | CS           | 计算机学院      |
| 李四   | MATH         | 数学学院        |
| 赵六   | PHYS         | 物理学院        |
+--------+--------------+-----------------+

|
select 课程名称, 学院名称
from 课程 natural join 学院;

|
+--------------------+-----------------+
| 课程名称           | 学院名称        |
+--------------------+-----------------+
| 程序设计基础       | 计算机学院      |
| 高等数学           | 数学学院        |
| 大学物理           | 物理学院        |
+--------------------+-----------------+

|
select 学生.姓名, 学生.学号, 学院.学院名称, 学院.办公楼
from 学生, 学院
where 学生.学院代码 = 学院.学院代码 
  and 学院.学院代码 = 'CS';

|
select 姓名, 学号, 学院名称, 办公楼
from 学生 natural join 学院
where 学院代码 = 'CS';

|
select 姓名, 2024 - 入学年份 as 在校年数
from 学生;

|
+--------+--------------+
| 姓名   | 在校年数     |
+--------+--------------+
| 张三   |            3 |
| 李四   |            3 |
| 王五   |            3 |
| 赵六   |            2 |
+--------+--------------+

|
select s.姓名, c.学院名称
from 学生 as s, 学院 as c
where s.学院代码 = c.学院代码;

|
select distinct s2.姓名
from 学生 as s1, 学生 as s2
where s1.学院代码 = s2.学院代码 
  and s1.姓名 = '张三' 
  and s2.姓名 != '张三';

|
select 姓名, 学号 from 学生
where 姓名 like '王%';

|
+--------+---------+
| 姓名   | 学号    |
+--------+---------+
| 王五   | 2021003 |
+--------+---------+

|
select 姓名, 学号 from 学生
where 姓名 like '%明%';

|
select 姓名 from 学生
where 姓名 like '___';

|
select 课程名称 from 课程
where 课程名称 like '%\%%' escape '\';

|
select 姓名 from 学生
where 姓名 not like '李%';

|
select 姓名, 入学年份 from 学生
order by 姓名;

|
+--------+--------------+
| 姓名   | 入学年份     |
+--------+--------------+
| 张三   |         2021 |
| 李四   |         2021 |
| 王五   |         2021 |
| 赵六   |         2022 |
+--------+--------------+

|
select 姓名, 入学年份 from 学生
order by 入学年份 desc;

|
select 姓名, 学院代码, 入学年份 from 学生
order by 学院代码, 入学年份 desc;

|
+--------+--------------+--------------+
| 姓名   | 学院代码     | 入学年份     |
+--------+--------------+--------------+
| 张三   | CS           |         2021 |
| 王五   | CS           |         2021 |
| 李四   | MATH         |         2021 |
| 赵六   | PHYS         |         2022 |
+--------+--------------+--------------+

|
select 姓名, 入学年份 from 学生
where 入学年份 between 2021 and 2022;

|
select 姓名, 入学年份 from 学生
where 入学年份 >= 2021 and 入学年份 <= 2022;

|
select 姓名, 入学年份 from 学生
where 入学年份 not between 2020 and 2021;

|
select 姓名, 学号 from 学生
where (学院代码, 入学年份) = ('CS', 2021);

|
+--------+---------+
| 姓名   | 学号    |
+--------+---------+
| 张三   | 2021001 |
| 王五   | 2021003 |
+--------+---------+

|
select 姓名, 学号 from 学生
where 学院代码 = 'CS' and 入学年份 = 2021;

|
select * from 学生
where 学院代码 = 'CS';

|
select 学生.*, 学院.学院名称
from 学生, 学院
where 学生.学院代码 = 学院.学院代码;

|
select s.学号 as 学生学号, 
       s.姓名 as 学生姓名,
       c.学院名称 as 所属学院,
       s.入学年份,
       2024 - s.入学年份 as 在校年数
from 学生 as s, 学院 as c
where s.学院代码 = c.学院代码
  and

|
+--------------+--------------+-----------------+--------------+--------------+
| 学生学号     | 学生姓名     | 所属学院        | 入学年份     | 在校年数     |
+--------------+--------------+-----------------+--------------+--------------+
| 2021002      | 李四         | 数学学院        |         2021 |            3 |
| 2022001      | 赵六         | 物理学院        |         2022 |            2 |
| 2021001      | 张三

|
create table 开课信息 (
    课程代码 varchar(8),
    学期 varchar(10),
    年份 int,
    primary key (课程代码, 学期, 年份),
    foreign key (课程代码) references 课程(课程代码)
);
 
-- 插入一些开课数据
insert into 开课信息 values 
('CS101', '春季', 2024),
('CS101', 

|
select 课程代码 from 开课信息 where 学期 = '春季' and 年份 = 2024
union
select 课程代码 from 开课信息 where 学期 = '秋季' and 年份 = 2024;

|
+--------------+
| 课程代码     |
+--------------+
| CS101        |
| MATH201      |
| PHYS101      |
+--------------+

|
select 课程代码 from 开课信息 where 学期 = '春季' and 年份 = 2024
union all
select 课程代码 from 开课信息 where 学期 = '秋季' and 年份 = 2024;

|
+--------------+
| 课程代码     |
+--------------+
| CS101        |
| MATH201      |
| PHYS101      |
| CS101        |
| PHYS101      |
+--------------+

|
select 课程代码 from 开课信息 where 学期 = '春季' and 年份 = 2024
intersect
select 课程代码 from 开课信息 where 学期 = '秋季' and 年份 = 2024;

|
select 课程代码 from 开课信息 where 学期 = '春季' and 年份 = 2024
except
select 课程代码 from 开课信息 where 学期 = '秋季' and 年份 = 2024;

|
select 课程代码 from 开课信息 where 学期 = '秋季' and 年份 = 2024
except
select 课程代码 from 开课信息 where 学期 = '春季' and 年份 = 2024;

|
select 课程代码 from 开课信息 where 学期 = '春季'
union
select 课程代码 from 开课信息 where 学期 = '秋季'
order by 课程代码;

|
(select 课程代码 from 开课信息 where 学期 = '春季'
 intersect
 select 课程代码 from 开课信息 where 学期 = '秋季')
union
select 课程代码 from 课程 where 学分 = 4;

|
-- 添加一些包含null值的学生记录
insert into 学生 values 
('2023001', '陈七', null, 2023),  -- 还未确定学院
('2023002', '李八', 'CS', null); -- 入学年份未登记
 
-- 更新某个学院的预算为null（可能正在重新评估）
update 学院 set 预算 = null where 学院代码 = 'PHYS';

|
select 姓名, 
       2024 - 入学年份 as 在校年数,
       预算 * 1.1 as 调整后预算
from 学生, 学院
where 学生.学院代码 = 学院.学院代码;

|
+--------+--------------+-----------------+
| 姓名   | 在校年数     | 调整后预算      |
+--------+--------------+-----------------+
| 张三   |            3 |     2750000.000 |
| 王五   |            3 |     2750000.000 |
| 李八   |         NULL |     2750000.000 |
| 李四   |            3 |     1980000.000 |
| 赵六   |

|
select 姓名, 入学年份
from 学生
where 入学年份 > 2021;

|
-- 查找入学年份未知的学生
select 姓名, 学号
from 学生
where 入学年份 is null;

|
+--------+---------+
| 姓名   | 学号    |
+--------+---------+
| 李八   | 2023002 |
+--------+---------+

|
-- 查找入学年份已知的学生
select 姓名, 学号, 入学年份
from 学生
where 入学年份 is not null;

|
select distinct 学院代码
from 学生;

|
select 姓名, 入学年份
from 学生
order by 入学年份;

|
+--------+--------------+
| 姓名   | 入学年份     |
+--------+--------------+
| 李八   |         NULL |
| 张三   |         2021 |
| 李四   |         2021 |
| 王五   |         2021 |
| 赵六   |         2022 |
| 陈七   |         2023 |
+--------+--------------+

|
select s.姓名, s.学院代码, c.学院名称
from 学生 s, 学院 c
where s.学院代码 = c.学院代码;

|
+--------+--------------+-----------------+
| 姓名   | 学院代码     | 学院名称        |
+--------+--------------+-----------------+
| 张三   | CS           | 计算机学院      |
| 王五   | CS           | 计算机学院      |
| 李八   | CS           | 计算机学院      |
| 李四   | MATH         | 数学学院        |
| 赵六   |

|
-- 创建课程成绩表
create table 课程成绩 (
    学号 varchar(10),
    课程代码 varchar(8),
    成绩 numeric(5,2),  -- 成绩精确到小数点后两位
    primary key (学号, 课程代码),  -- 复合主键，确保每位学生每门课只有一条成绩记录
    foreign key (学号) references 学生(学号),
    foreign key (课程代码) references 课程(课程代码)
);

|
-- 插入一些课程成绩数据
insert into 课程成绩 values
('2021001', 'CS101', 85.5),
('2021001', 'MATH201', 92.0),
('2021002', 'MATH201', 78.5),
('2021003', 'CS101', 88.0),
('2021003'

|
select avg(成绩) as 平均成绩
from 课程成绩;

|
+--------------+
| 平均成绩     |
+--------------+
|    87.083333 |
+--------------+

|
select sum(预算) as 总预算
from 学院
where 预算 is not null;

|
+------------+
| 总预算     |
+------------+
| 4300000.00 |
+------------+

|
-- 统计总记录数（包括null值）
select count(*) as 总选课记录数
from 课程成绩;
 
-- 统计非null值的数量
select count(成绩) as 已评分记录数
from 课程成绩;

|
+--------------------+
| 总选课记录数       |
+--------------------+
|                  7 |
+--------------------+
 
+--------------------+
| 已评分记录数       |
+--------------------+
|                  6 |
+--------------------+

|
select 
    min(成绩) as 最低分,
    max(成绩) as 最高分
from 课程成绩
where 成绩 is not null;

|
+-----------+-----------+
| 最低分    | 最高分    |
+-----------+-----------+
|     78.50 |     92.00 |
+-----------+-----------+

|
select 学院代码, count(*) as 学生数量
from 学生
where 学院代码 is not null
group by 学院代码;

|
+--------------+--------------+
| 学院代码     | 学生数量     |
+--------------+--------------+
| CS           |            3 |
| MATH         |            1 |
| PHYS         |            1 |
+--------------+--------------+

|
select 
    课程代码,
    count(成绩) as 参与评分人数,
    avg(成绩) as 平均成绩,
    min(成绩) as 最低分,
    max(成绩) as 最高分
from 课程成绩
group by 课程代码;

|
+--------------+--------------------+--------------+-----------+-----------+
| 课程代码     | 参与评分人数       | 平均成绩     | 最低分    | 最高分    |
+--------------+--------------------+--------------+-----------+-----------+
| CS101        |                  2 |    86.750000 |     85.50 |     88.00 |
| MATH201      |                  2 |    85.250000 |     78.50 |     92.00 |
| PHYS101      |                  2

|
select 
    学院代码,
    入学年份,
    count(*) as 学生数量
from 学生
where 学院代码 is not null and 入学年份 is not null
group by 学院代码, 入学年份
order by 学院代码, 入学年份;

|
+--------------+--------------+--------------+
| 学院代码     | 入学年份     | 学生数量     |
+--------------+--------------+--------------+
| CS           |         2021 |            2 |
| MATH         |         2021 |            1 |
| PHYS         |         2022 |            1 |
+--------------+--------------+--------------+

|
select 
    课程代码,
    count(成绩) as 参与评分人数,
    avg(成绩) as 平均成绩
from 课程成绩
group by 课程代码
having avg(成绩) > 85;

|
+--------------+--------------------+--------------+
| 课程代码     | 参与评分人数       | 平均成绩     |
+--------------+--------------------+--------------+
| CS101        |                  2 |    86.750000 |
| MATH201      |                  2 |    85.250000 |
| PHYS101      |                  2 |    89.250000 |
+--------------+--------------------+--------------+

|
select 
    学院代码,
    count(*) as 学生数量
from 学生
where 学院代码 is not null
group by 学院代码
having count(*) > 1;

|
+--------------+--------------+
| 学院代码     | 学生数量     |
+--------------+--------------+
| CS           |            3 |
+--------------+--------------+

|
select 
    c.学院名称,
    count(distinct s.学号) as 学生总数,
    count(cj.成绩) as 已评分科目数,
    avg(cj.成绩) as 平均成绩
from 学院 c
natural join 学生 s
left join 课程成绩 cj on s.学号 =

|
+-----------------+--------------+--------------------+--------------+
| 学院名称        | 学生总数     | 已评分科目数       | 平均成绩     |
+-----------------+--------------+--------------------+--------------+
| 计算机学院      |            3 |                  4 |    89.250000 |
| 物理学院        |            1 |                  1 |    87.000000 |
| 数学学院        |            1 |                  1 |    78.500000 |

|
select 学号, 课程代码, 成绩
from 课程成绩
where 成绩 > (select avg(成绩) from 课程成绩 where 成绩 is not null);

|
+---------+--------------+--------+
| 学号    | 课程代码     | 成绩   |
+---------+--------------+--------+
| 2021001 | MATH201      |  92.00 |
| 2021003 | CS101        |  88.00 |
| 2021003 | PHYS101      |  91.50 |
+---------+--------------+--------+

|
select distinct 学号, 姓名
from 学生 
where 学号 in (
    select distinct cj.学号
    from 课程成绩 cj, 课程 k
    where cj.课程代码 = k.课程代码 
      and k.学院代码 = 'CS'
);

|
+---------+--------+
| 学号    | 姓名   |
+---------+--------+
| 2021001 | 张三   |
| 2021003 | 王五   |
| 2022001 | 赵六   |
+---------+--------+

|
select 学号, 姓名
from 学生 
where 学号 not in (
    select distinct cj.学号
    from 课程成绩 cj, 课程 k
    where cj.课程代码 = k.课程代码 
      and k.学院代码 = 'CS'
      and cj.学号 is not null
);

|
+---------+--------+
| 学号    | 姓名   |
+---------+--------+
| 2021002 | 李四   |
| 2023001 | 陈七   |
| 2023002 | 李八   |
+---------+--------+

|
select 学号, 课程代码, 成绩
from 课程成绩
where 成绩 > some (
    select avg(cj.成绩)
    from 课程成绩 cj, 课程 k
    where cj.课程代码 = k.课程代码 
      and k.学院代码 = 'CS'
      and cj.成绩 is not null
    group by cj.课程代码

|
+---------+--------------+--------+
| 学号    | 课程代码     | 成绩   |
+---------+--------------+--------+
| 2021001 | MATH201      |  92.00 |
| 2021003 | CS101        |  88.00 |
| 2021003 | PHYS101      |  91.50 |
| 2022001 | PHYS101      |  87.00 |
+---------+--------------+--------+

|
select 学号, 课程代码, 成绩
from 课程成绩
where 成绩 > all (
    select avg(cj.成绩)
    from 课程成绩 cj, 课程 k
    where cj.课程代码 = k.课程代码 
      and k.学院代码 = 'CS'
      and cj.成绩 is not null
    group by cj.课程代码

|
+---------+--------------+--------+
| 学号    | 课程代码     | 成绩   |
+---------+--------------+--------+
| 2021001 | MATH201      |  92.00 |
| 2021003 | CS101        |  88.00 |
| 2021003 | PHYS101      |  91.50 |
| 2022001 | PHYS101      |  87.00 |
+---------+--------------+--------+

|
select 学号, 姓名
from 学生 s
where exists (
    select 1
    from 课程成绩 cj
    where cj.学号 = s.学号
);

|
+---------+--------+
| 学号    | 姓名   |
+---------+--------+
| 2021001 | 张三   |
| 2021002 | 李四   |
| 2021003 | 王五   |
| 2022001 | 赵六   |
+---------+--------+

|
select 学号, 姓名
from 学生 s
where not exists (
    select 1
    from 课程成绩 cj
    where cj.学号 = s.学号
);

|
+---------+--------+
| 学号    | 姓名   |
+---------+--------+
| 2023001 | 陈七   |
| 2023002 | 李八   |
+---------+--------+

|
select 学院名称, 平均成绩
from (
    select 
        c.学院名称,
        avg(cj.成绩) as 平均成绩
    from 学院 c
    natural join 课程 k
    join 课程成绩 cj on k.课程代码 = cj.课程代码
    where cj.成绩 is not null
    group by c.

|
+--------------+--------------+
| 学院名称     | 平均成绩     |
+--------------+--------------+
| 物理学院     |    89.250000 |
+--------------+--------------+

|
select 
    s.学号,
    s.姓名,
    (select count(*) 
     from 课程成绩 cj 
     where cj.学号 = s.学号) as 选课数量
from 学生 s;

|
+---------+--------+--------------+
| 学号    | 姓名   | 选课数量     |
+---------+--------+--------------+
| 2021001 | 张三   |            2 |
| 2021002 | 李四   |            1 |
| 2021003 | 王五   |            2 |
| 2022001 | 赵六   |            2 |
| 2023001 |

|
select 
    学号,
    课程代码,
    成绩,
    成绩 - (select avg(成绩) from 课程成绩 where 成绩 is not null) as 与平均分差值
from 课程成绩
where 成绩 is not null;

|
+---------+--------------+--------+--------------------+
| 学号    | 课程代码     | 成绩   | 与平均分差值       |
+---------+--------------+--------+--------------------+
| 2021001 | CS101        |  85.50 |          -1.583333 |
| 2021001 | MATH201      |  92.00 |           4.916667 |
| 2021002 | MATH201      |  78.50 |          -8.583333 |
| 2021003

|
with 学院平均成绩 as (
    select 
        k.学院代码,
        avg(cj.成绩) as 平均成绩
    from 课程 k
    join 课程成绩 cj on k.课程代码 = cj.课程代码
    where cj.成绩 is not null
    group by k.学院代码
),
全校平均成绩 

|
+-----------------+--------------+-----------------+-----------------+
| 学院名称        | 平均成绩     | 总平均成绩      | 与全校差值      |
+-----------------+--------------+-----------------+-----------------+
| 物理学院        |    89.250000 |       87.083333 |        2.166667 |
| 计算机学院      |    86.750000 |       87.083333 |       -0.333333 |
| 数学学院        |    85.250000 |       87.083333 |       -1.833333 |

|
insert into 学生 
values ('2024001', '王小华', 'CS', 2024);

|
insert into 学生 (学号, 姓名, 学院代码, 入学年份)
values ('2024002', '李小明', 'MATH', 2024);

|
insert into 学生 (学号, 姓名, 入学年份)
values ('2024003', '张三丰', 2024);

|
insert into 课程成绩 (学号, 课程代码, 成绩) values
('2024001', 'CS101', 88.5),
('2024001', 'MATH201', 92.0),
('2024002', 'MATH201', 85.5),
('2024002', 'PHYS101', 90.0);

|
-- 首先创建荣誉名单表
create table 荣誉名单 (
    学号 varchar(10),
    姓名 varchar(20),
    学院名称 varchar(50),
    平均成绩 numeric(4,1),
    primary key (学号)
);
 
-- 基于查询结果插入数据
insert into 荣誉名单 (学号, 姓名, 学院名称, 平均成绩)
select 
    s.学号,

|
update 学生 
set 学院代码 = 'PHYS'
where 学号 = '2024001';

|
update 学生 
set 学院代码 = 'PHYS', 入学年份 = 2023
where 学号 = '2024001';

|
update 课程成绩 
set 成绩 = 成绩 + 5
where 课程代码 = 'CS101' 
  and 学号 in (
      select 学号 
      from 学生 
      where 学院代码 = 'CS' and 入学年份 = 2021
  )
  and 成绩 is not null;

|
update 学院 
set 预算 = 预算 * 1.05  -- 增加5%
where 预算 is not null;

|
update 课程成绩 
set 成绩 = case 
    when 成绩 >= 90 then 成绩 + 2  -- 优秀学生额外加2分
    when 成绩 >= 80 then 成绩 + 1  -- 良好学生额外加1分
    else 成绩  -- 其他学生成绩不变
end
where 成绩 is not null;

|
delete from 学生 
where 学号 = '2024003';

|
delete from 课程成绩 
where 成绩 is null;

|
delete from 学生 
where 学号 not in (
    select distinct 学号 
    from 课程成绩 
    where 学号 is not null
);

|
-- 1. 为缺席考试的学生记录0分
update 课程成绩 
set 成绩 = 0 
where 成绩 is null;
 
-- 2. 插入补考机会记录
insert into 课程成绩 (学号, 课程代码, 成绩)
select 学号, 课程代码, null
from 课程成绩 
where 成绩 < 60
  and 学号 not in (
      select 学号 
      from 课程成绩 as 内层
      where 内层

|
-- 创建历史数据归档表
create table 历史成绩 as 
select * from 课程成绩 where 1=0;  -- 复制结构但不复制数据
 
-- 将2021年以前的成绩数据迁移到历史表
insert into 历史成绩 
select cj.*
from 课程成绩 cj
join 学生 s on cj.学号 = s.学号
where s.入学年份 < 2021

|
update 课程成绩 
set 成绩 = 成绩 * 1.1 
where 成绩 < (select avg(成绩) from 课程成绩);

|
-- 错误示例：可能导致无限循环或意外结果
update 学生 
set 学号 = 学号 + 1000
where 入学年份 = 2024;
 
-- 正确做法：使用更明确的标识符
update 学生 
set 学号 = concat('NEW', 学号)
where 入学年份 = 2024 and 学号 not like 'NEW%';

|
-- 开始事务
begin transaction;
 
-- 转账操作示例：从学院A的预算转移到学院B
update 学院 set 预算 = 预算 - 100000 where 学院代码 = 'CS';
update 学院 set 预算 = 预算 + 100000 where 学院代码 = 'MATH';
 
-- 检查操作结果，如果正确则提交
commit;
 
-- 如果出现问题则回滚
-- rollback;