数据库系统概论——综合实验

 

 

#查询平均成绩大于等于90的学号、课程成绩；
SELECT 学号,课程成绩
FROM `学生`
WHERE 课程成绩>='90';

#查询选修了课程名为“信息系统”的学生的学号姓名；
SELECT 学号,姓名
FROM `学生`,`课程`
WHERE `学生`.`课程号`=`课程`.`课程号` AND `课程`.`课程名`='信息系统';

#(2)嵌套：
SELECT 学号,姓名
FROM `学生`
WHERE `课程号` IN
(SELECT `课程号`
FROM `课程`
WHERE 课程名='信息系统');

#创建视图 IG_view
CREATE VIEW IG_view (学号,姓名,课程号,课程成绩)
AS
SELECT 学号,姓名,课程号,课程成绩
FROM 学生,学校
WHERE `学生`.`系编号`=`学校`.`系编号` AND `系名`='计算机系'; 

#统计每门课程选修人数，输出课程号和人数，结果按人数降序
SELECT 课程号,COUNT(学号)
FROM `学生`
GROUP BY 课程号
# HAVING COUNT(学号)>='3'
ORDER BY COUNT(学号) DESC,`课程号`  ;

#授权用户u1对学生表（姓名）、课程表，有查询、修改权限，并给其他用户授权（密码：123）
CREATE USER u1 @'host' IDENTIFIED BY '123';

# SHOW GRANTS FOR 'u1'@'HOST';

GRANT SELECT,UPDATE
ON TABLE 课程
TO u1@'HOST';

GRANT SELECT(姓名),UPDATE(姓名)
ON TABLE 学生
TO u1@'HOST';


/*
Show triggers;
#查看某个触发器的内容：
SHOW CREATE TRIGGER update_cno;
drop trigger update_cno;

*/
#  设置触发器
#  1.修改课程中课程号时，学生表中可以自动修改

CREATE TRIGGER update_cno
after update on 课程
for each row
begin

UPDATE 学生 SET 课程号=new.`课程号` WHERE 课程号=old.课程号 ;

end;

#创建触发器，在插入或修改学生信息时，如果年龄低于18岁，自动修改为18岁

CREATE TRIGGER update_age
BEFORE UPDATE on 学生
for each row
begin

IF(new.年龄<'18')
 THEN  SET new.`年龄`='18';
END IF;
 
end;

CREATE TRIGGER insert_age
BEFORE INSERT on 学生
for each row
begin

IF(new.年龄<'18')
 THEN  SET new.`年龄`='18';
END IF;
 
end;