临时表、视图与系统函数_Lab2，实验内容：1、针对指
实验二 临时表、视图与系统函数
实验目的：
理解CTE与视图的知识，掌握临时表、CTE与视图的创建与使用方法，能够根据需要创建CTE、视图，掌握视图应用技术，熟悉常用系统函数的应用方法。
实验内容：
1、 针对指定的表进行全文检索配置，利用全文检索检索记录。
2、 创建视图。
3、 练习常用系统函数使用方法
实验过程及要求：
1、基于派生表或者CTE命令，完成以下操作，并把相应的命令写在空白处。
（1）写出查询学生基本信息的语句，包括学号、姓名、专业班级、本人平均分（score1）、本班平均分（score1）、本人完成学分总计。
（2）查询周四第5节有课的同学的名单，显示学号、姓名、专业班级、课程名。
2、写出以下视图的创建命令，并把命令写在空白处。
（1）查看课程表的视图，显示专业班级、课程名、教室、上课老师姓名
（2）创建学生视图，通过该视图只能更新数据学院的学生信息
（3）查看学生基本信息的视图，包括学号、姓名、性别、年龄、本人电话、班级名称、班导姓名、班导电话
4、 自主练习常用系统函数，理解函数的功能与含义。写出以下命令与结果
（1） 显示系统时间2年3个月后的日期与当年第几周，输出格式为：2023-09-21 38
（2）输出一个基于你的MySQL的UUID
（3）输出你的姓名全拼使用password、rsa、aes加密后的密码。格式如下：
zhangsan
password('zhangsan')=
rsa('zhangsan')=
aes('zhangsan')=
具体实验过程：
1. 基于派生表或者CTE命令，完成以下操作，并把相应的命令写在空白处。
（1）查询学生基本信息的语句，包括学号、姓名、专业班级、本人平均分（score1）、本班平均分（score1）、本人完成学分总计。
-- 创建一个CTE
WITH student_info AS (
SELECT ustudent.Sid, ustudent.Sname, ugrade.gname
AS
class, usc.score1, AVG(usc.score1) OVER (PARTITION BY ugrade.gid)
AS
class_avg, SUM(ucourse.credit) OVER (PARTITION BY ustudent.Sid)
AS
total_credits
FROM ustudent
JOIN ugrade ON ustudent.gid = ugrade.gid
JOIN usc ON ustudent.Sid = usc.sid
JOIN ucourse ON usc.cid = ucourse.cid
)
-- 从CTE表中获取信息
SELECT Sid, Sname, class, score1, class_avg, total_credits
FROM student_info;
理解CTE与视图的知识，掌握临时表、CTE与视图的创建与使用方法，能够根据需要创建CTE、视图，掌握视图应用技术，熟悉常用系统函数的应用方法。
实验内容：
1、 针对指定的表进行全文检索配置，利用全文检索检索记录。
2、 创建视图。
3、 练习常用系统函数使用方法
实验过程及要求：
1、基于派生表或者CTE命令，完成以下操作，并把相应的命令写在空白处。
（1）写出查询学生基本信息的语句，包括学号、姓名、专业班级、本人平均分（score1）、本班平均分（score1）、本人完成学分总计。
（2）查询周四第5节有课的同学的名单，显示学号、姓名、专业班级、课程名。
2、写出以下视图的创建命令，并把命令写在空白处。
（1）查看课程表的视图，显示专业班级、课程名、教室、上课老师姓名
（2）创建学生视图，通过该视图只能更新数据学院的学生信息
（3）查看学生基本信息的视图，包括学号、姓名、性别、年龄、本人电话、班级名称、班导姓名、班导电话
4、 自主练习常用系统函数，理解函数的功能与含义。写出以下命令与结果
（1） 显示系统时间2年3个月后的日期与当年第几周，输出格式为：2023-09-21 38
（2）输出一个基于你的MySQL的UUID
（3）输出你的姓名全拼使用password、rsa、aes加密后的密码。格式如下：
zhangsan
password('zhangsan')=
rsa('zhangsan')=
aes('zhangsan')=
具体实验过程：
1. 基于派生表或者CTE命令，完成以下操作，并把相应的命令写在空白处。
（1）查询学生基本信息的语句，包括学号、姓名、专业班级、本人平均分（score1）、本班平均分（score1）、本人完成学分总计。
-- 创建一个CTE
WITH student_info AS (
SELECT ustudent.Sid, ustudent.Sname, ugrade.gname
AS
class, usc.score1, AVG(usc.score1) OVER (PARTITION BY ugrade.gid)
AS
class_avg, SUM(ucourse.credit) OVER (PARTITION BY ustudent.Sid)
AS
total_credits
FROM ustudent
JOIN ugrade ON ustudent.gid = ugrade.gid
JOIN usc ON ustudent.Sid = usc.sid
JOIN ucourse ON usc.cid = ucourse.cid
)
-- 从CTE表中获取信息
SELECT Sid, Sname, class, score1, class_avg, total_credits
FROM student_info;
1、 针对指定的表进行全文检索配置，利用全文检索检索记录。
2、 创建视图。
3、 练习常用系统函数使用方法
1、基于派生表或者CTE命令，完成以下操作，并把相应的命令写在空白处。
（1）写出查询学生基本信息的语句，包括学号、姓名、专业班级、本人平均分（score1）、本班平均分（score1）、本人完成学分总计。
（2）查询周四第5节有课的同学的名单，显示学号、姓名、专业班级、课程名。
2、写出以下视图的创建命令，并把命令写在空白处。
（1）查看课程表的视图，显示专业班级、课程名、教室、上课老师姓名
（2）创建学生视图，通过该视图只能更新数据学院的学生信息
（3）查看学生基本信息的视图，包括学号、姓名、性别、年龄、本人电话、班级名称、班导姓名、班导电话
4、 自主练习常用系统函数，理解函数的功能与含义。写出以下命令与结果
（1） 显示系统时间2年3个月后的日期与当年第几周，输出格式为：2023-09-21 38
（2）输出一个基于你的MySQL的UUID
（3）输出你的姓名全拼使用password、rsa、aes加密后的密码。格式如下：
zhangsan
password('zhangsan')=
rsa('zhangsan')=
aes('zhangsan')=
具体实验过程：
1. 基于派生表或者CTE命令，完成以下操作，并把相应的命令写在空白处。
（1）查询学生基本信息的语句，包括学号、姓名、专业班级、本人平均分（score1）、本班平均分（score1）、本人完成学分总计。
-- 创建一个CTE
WITH student_info AS (
SELECT ustudent.Sid, ustudent.Sname, ugrade.gname
AS
class, usc.score1, AVG(usc.score1) OVER (PARTITION BY ugrade.gid)
AS
class_avg, SUM(ucourse.credit) OVER (PARTITION BY ustudent.Sid)
AS
total_credits
FROM ustudent
JOIN ugrade ON ustudent.gid = ugrade.gid
JOIN usc ON ustudent.Sid = usc.sid
JOIN ucourse ON usc.cid = ucourse.cid
)
-- 从CTE表中获取信息
SELECT Sid, Sname, class, score1, class_avg, total_credits
FROM student_info;
-- 创建一个CTE
WITH student_info AS (
SELECT ustudent.Sid, ustudent.Sname, ugrade.gname
AS
class, usc.score1, AVG(usc.score1) OVER (PARTITION BY ugrade.gid)
AS
class_avg, SUM(ucourse.credit) OVER (PARTITION BY ustudent.Sid)
AS
total_credits
FROM ustudent
JOIN ugrade ON ustudent.gid = ugrade.gid
JOIN usc ON ustudent.Sid = usc.sid
JOIN ucourse ON usc.cid = ucourse.cid
)
-- 从CTE表中获取信息
SELECT Sid, Sname, class, score1, class_avg, total_credits
FROM student_info;
AVG(usc.score1) OVER (PARTITION BY ugrade.gid) 为窗口函数的用法 partition by 是根据指定的分组
（2）查询周四第5节有课的同学的名单，显示学号、姓名、专业班级、课程名。
WITH thursday_schedule AS
(
SELECT ustudent.Sid, ustudent.Sname, ugrade.gname AS class, ucourse.Cname
FROM ujobtable
JOIN ustudent ON ujobtable.gid = ustudent.gid
JOIN ugrade ON ustudent.gid = ugrade.gid
JOIN ucourse ON ujobtable.cid = ucourse.Cid
WHERE ujobtable.week = 4 AND ujobtable.timeseg like '%5%'
)
SELECT Sid, Sname, class, Cname
FROM thursday_schedule;
2. 写出以下视图的创建命令，并把命令写在空白处。
（1）查看课程表的视图，显示专业班级、课程名、教室、上课老师姓名。
CREATE VIEW course_schedule_view AS
SELECT ugrade.gname AS class, ucourse.Cname, ujobtable.room, uteacher.tname AS teacher_name
FROM ujobtable
JOIN ugrade ON ujobtable.gid = ugrade.gid
JOIN ucourse ON ujobtable.cid = ucourse.Cid
JOIN uteacher ON ujobtable.tid = uteacher.tid;
（2）创建学生视图，通过该视图只能更新数据学院的学生信息。
CREATE VIEW student_update_view AS
SELECT *
FROM ustudent
WHERE gid IN (SELECT gid FROM ugrade WHERE did = 'CS');
（3）查看学生基本信息的视图，包括学号、姓名、性别、年龄、本人电话、班级名称、班导姓名、班导电话。
CREATE VIEW student_info_view AS
SELECT ustudent.Sid, ustudent.Sname, ustudent.Ssexy AS gender,
YEAR(CURDATE()) - YEAR(ustudent.Sbdate) AS age,
ustudent.stele AS personal_phone,ugrade.gname AS class,
homework.uteacher.tname AS class_teacher, homework.uteacher.tele AS class_teacher_phone
FROM ustudent
JOIN ugrade ON ustudent.gid = ugrade.gid
JOIN ujobtable ON ustudent.gid = ujobtable.gid
JOIN homework.uteacher ON ujobtable.tid = homework.uteacher.tid;
4. 自主练习常用系统函数，理解函数的功能与含义。写出以下命令与结果
（1）显示系统时间2年3个月后的日期与当年第几周，输出格式为：2023-09-21 38
select DATE_ADD(DATE_SUB(curdate(), INTERVAL 2 YEAR ),INTERVAL 3 MONTH)
, WEEK(CURDATE()) AS CurrentWeek;
（2）输出一个基于你的MySQL的UUID
SELECT UUID() AS generated_uuid;
（3）输出你的姓名全拼使用password、rsa、aes加密后的密码。
格式如下：
zhangsanpassword('zhangsan')= PASSWORD('zhangsan')
rsa('zhangsan')= RSA_ENCRYPT('zhangsan', 'encryption_key')
aes('zhangsan')= AES_ENCRYPT('zhangsan', 'encryption_key')