MySQL蓝桥实验平台挑战二,「MySQL 基础课程 」 挑战参考答案

部分同学在完成「MySQL 基础课程」挑战的过程中，常会遇到一些相似的问题。我们将 3 个挑战题目的参考答案整理出来，供大家参考。各题目的解法不唯一，你也可以将已经通过的不同解题方法分享出来。

挑战实验一 搭建一个简易的成绩管理系统的数据库

$ sudo service mysql start

$ mysql -u root

mysql> CREATE DATABASE gradesystem;

mysql> use gradesystem

mysql> CREATE TABLE student(

-> sid int NOT NULL AUTO_INCREMENT,

-> sname varchar(20) NOT NULL,

-> gender varchar(10) NOT NULL,

-> PRIMARY KEY(sid)

-> );

mysql> CREATE TABLE course(

-> cid int NOT NULL AUTO_INCREMENT,

-> cname varchar(20) NOT NULL,

-> PRIMARY KEY(cid)

-> );

mysql> CREATE TABLE mark(

-> mid int NOT NULL AUTO_INCREMENT,

-> sid int NOT NULL,

-> cid int NOT NULL,

-> score int NOT NULL,

-> PRIMARY KEY(mid),

-> FOREIGN KEY(sid) REFERENCES student(sid),

-> FOREIGN KEY(cid) REFERENCES course(cid)

-> );

mysql> INSERT INTO student VALUES(1,'Tom','male'),(2,'Jack','male'),(3,'Rose','female');

mysql> INSERT INTO course VALUES(1,'math'),(2,'physics'),(3,'chemistry');

mysql> INSERT INTO mark VALUES(1,1,1,80),(2,2,1,85),(3,3,1,90),(4,1,2,60),(5,2,2,90),(6,3,2,75),(7,1,3,95),(8,2,3,75),(9,3,3,85);

挑战实验二 成绩管理系统的数据操作

$ sudo service mysql start

$ mysql -u root

mysql> source /home/shiyanlou/createdb.sql

mysql> SELECT s.sid,s.sname,s.gender FROM student s,( SELECT m.sid,m.cid,m.score FROM mark m,course c WHERE m.cid = c.cid AND c.cname='physics' ORDER BY m.score DESC LIMIT 1) t WHERE s.sid = t.sid INTO OUTFILE '/tmp/physics.txt';

mysql> UPDATE mark m,student s,course c SET m.score = m.score+3 WHERE m.sid = s.sid AND s.sname = 'Tom' AND m.cid = c.cid AND c.cname = 'chemistry';

挑战实验三 简化数据操作提升性能

$ sudo service mysql start

$ mysql -u root

mysql> source /home/shiyanlou/createdb2.sql

mysql> CREATE TRIGGER trigger_modify

-> AFTER UPDATE ON mark

-> FOR EACH ROW

-> INSERT INTO modifymark(m_sid,m_cid,m_score,m_time) VALUES(old.sid,old.cid,new.score,current_timestamp());

mysql> UPDATE mark m,student s,course c SET m.score = m.score+3 WHERE m.sid = s.sid AND s.sname = 'Tom' AND m.cid = c.cid AND c.cname = 'chemistry';

mysql> delimiter //

mysql> CREATE PROCEDURE math_proc()

-> BEGIN

-> SELECT c.cname,s.sname,m.score FROM mark m,course c,student s WHERE m.cid = c.cid AND c.cname = 'math' AND m.sid = s.sid ORDER BY m.score;

-> END

-> //

mysql> delimiter ;

mysql> GRANT select ON *.* TO 'testuser'@'localhost' IDENTIFIED BY '123456' WITH GRANT OPTION;

注意：本帖仅供分享优质答案(代码精简规范，测试通过)供其他学员参考，请勿在本帖下方提问，提问请到课程问答区域提问。