MySql实例

1. 需求：

设计数据库表存储：（用户考试信息）        用户信息、考试时间、考试科目与考试成绩，及所属年级！

2. 测试数据：

    U001, 张三，1985-09-09, 广州天河,  java,80,基础班,  考试时间为2014-01-01  jsp,90,就业班,      考试时间为2014-03-01，mysql,90, 就业班, 考试时间为2014-04-04     U002 ,李四，1995-09-09, 广州越秀, java,67,基础班,  考试时间为2014-01-01，mysql,90, 就业班,     考试时间为2014-04-04     ……….(录入其他记录)

3. 查询需求：

       1. 查询学号是U001的学生参加2014-01-01 “java”课程考试的成绩，要求输出学生姓名和成绩       2. 查询出通过考试（高于60分）的学员所在的姓名、、所属学学习阶段、考试科目名称、学员的成绩。       利用子查询语句，筛选出生日期比“李四”大的学生       查询“java”课程考试成绩为60-80分的学生名单       查询参加最近一次“mysql”考试成绩最高分和最低分       查询出基础班考试的平均成绩；       需求（存储过程）       统计并显示2014-04-04的mysql考试平均分       如果平均分在70以上，显示“考试成绩优秀”       如果在70以下，显示“考试成绩较差”

4.实现

1.数据库设计

    学生表    学号 姓名 出生日期 地址 课程号 CREATE TABLE student(    sid VARCHAR(4) PRIMARY KEY,    sname VARCHAR(20),    sbday VARCHAR(20),    saddress VARCHAR(20),    scouId INT,    CONSTRAINT student_course_fk FOREIGN KEY(scouId) REFERENCES course(couId) ON UPDATE CASCADE ON DELETE CASCADE    );课程表    课程号 课程名 考试时间 班级号CREATE TABLE course(    couId INT PRIMARY KEY AUTO_INCREMENT,    couName VARCHAR(20),    couTime VARCHAR(20),    couCid INT,    CONSTRAINT course_class_fk FOREIGN KEY(couCid) REFERENCES class(cid)    ) 班级表    班级号 班级名CREATE TABLE class(    cid INT PRIMARY KEY AUTO_INCREMENT,    cname VARCHAR(20))成绩表    学号 课程号 成绩CREATE TABLE score(    sStuId VARCHAR(4),    sCouId INT,    sgrade INT,    CONSTRAINT score_student_fk FOREIGN KEY(sStuId) REFERENCES student(sid),    CONSTRAINT score_course_fk FOREIGN KEY(sCouId) REFERENCES course(couId)    );

2.源码

CREATE DATABASE homework;USE homework;-- 班别表CREATE TABLE class(    cid INT PRIMARY KEY AUTO_INCREMENT,    cname VARCHAR(20))INSERT INTO class(cname) VALUES('基础班');INSERT INTO class(cname) VALUES('就业班');-- 课程表CREATE TABLE course(    couId INT PRIMARY KEY AUTO_INCREMENT,    couName VARCHAR(20),    couTime VARCHAR(20),    couCid INT,    CONSTRAINT course_class_fk FOREIGN KEY(couCid) REFERENCES class(cid)    ) INSERT INTO course(couName,couTime,couCid) VALUES('java','2014-01-01',1);INSERT INTO course(couName,couTime,couCid) VALUES('jsp','2014-03-01',2);INSERT INTO course(couName,couTime,couCid) VALUES('mysql','2014-04-04',2);DROP TABLE student;-- 学生表CREATE TABLE student(    sid VARCHAR(4) PRIMARY KEY,    sname VARCHAR(20),    sbday VARCHAR(20),    saddress VARCHAR(20),    scouId INT,    CONSTRAINT student_course_fk FOREIGN KEY(scouId) REFERENCES course(couId) ON UPDATE CASCADE ON DELETE CASCADE    );DROP TABLE score;-- 成绩表CREATE TABLE score(    sStuId VARCHAR(4),    sCouId INT,    sgrade INT,    CONSTRAINT score_student_fk FOREIGN KEY(sStuId) REFERENCES student(sid),    CONSTRAINT score_course_fk FOREIGN KEY(sCouId) REFERENCES course(couId)    );  INSERT INTO student VALUES('U001','狗蛋','1985-09-17','火星',1 );INSERT INTO student VALUES('U002','美美','1993-07-16','火星', 2);INSERT INTO student VALUES('U003','狗剩','1988-10-17','火星',1);INSERT INTO student VALUES('U004','铁蛋','1989-06-09','火星',1 );DELETE FROM score;DELETE FROM student;SELECT * FROM student;INSERT INTO score VALUES('U001',1,80);INSERT INTO score VALUES('U001',2,90);INSERT INTO score VALUES('U002',3,90);INSERT INTO score VALUES('U003',1,80);INSERT INTO score VALUES('U004',1,80);INSERT INTO score VALUES('U004',2,55);INSERT INTO score VALUES('U003',2,85);INSERT INTO score VALUES('U003',3,96);SELECT * FROM student;SELECT * FROM score;SELECT * FROM class;SELECT * FROM course;SELECT cou.*,cla.*    FROM course cou,class cla        WHERE cou.couCid = cla.cid;DELETE FROM student;--     2. 查询出通过考试（高于60分）的学员所在的姓名、、所属学学习阶段、考试科目名称、学员的成绩。SELECT s.sname,cla.cname,sg.sgrade,cou.couName    FROM student s,score sg,course cou,class cla        WHERE s.sid=sg.sStuId AND sg.sCouId=cou.couId AND sg.sgrade>60 ;--     查询“java”课程考试成绩为60-80分的学生名单SELECT s.sname,cla.cname,sg.sgrade,cou.couName    FROM student s,score sg,course cou,class cla        WHERE s.sid=sg.sStuId AND sg.sCouId=cou.couId AND sg.sgrade>60 AND sg.sgrade<=80 AND cou.couCid = 1 ;--     查询参加最近一次“mysql”考试成绩最低分SELECT MIN(sg.sgrade) AS mysql    FROM score sg,course cou        WHERE  sg.sCouId=cou.couId AND cou.couId = 3;-- 查询参加最近一次“mysql”考试成绩最高分SELECT MAX(sg.sgrade) AS mysql    FROM score sg,course cou        WHERE  sg.sCouId=cou.couId AND cou.couId = 3;-- 查询出基础班考试的平均成绩SELECT AVG(sg.sgrade) AS avg_jichuban    FROM score sg,course cou,class cla        WHERE  sg.sCouId=cou.couId AND cla.cid = cou.couCid AND cla.cid=2;TRUNCATE TABLE score;-- 查询所有信息SELECT s.*,sg.*,cou.*,cla.*    FROM student s,score sg,course cou,class cla        WHERE s.sid=sg.sStuId AND sg.sCouId=cou.couId AND cou.couCid = cla.cid ORDER BY s.sid;-- 存储过程--     统计并显示2014-04-04的mysql考试平均分DELIMITER $CREATE PROCEDURE pro_avg_mysql(OUT grade INT)BEGIN    SELECT AVG(sg.sgrade) AS mysql INTO grade        FROM score sg,course cou            WHERE  sg.sCouId=cou.couId AND cou.couId = 3; END $CALL pro_avg_mysql(@grade);SELECT @grade;--     如果平均分在70以上，显示“考试成绩优秀”--     如果在70以下，显示“考试成绩较差”DROP PROCEDURE pro_avg_score;DELIMITER $CREATE PROCEDURE pro_avg_score(OUT gradeAll VARCHAR(20))BEGIN    DECLARE temp INT;    SELECT AVG(sg.sgrade) AS grade INTO temp        FROM score sg,course cou            WHERE  sg.sCouId=cou.couId;     IF temp>70 THEN        SET gradeAll = '考试成绩优秀';    ELSEIF temp<=70 THEN        SET gradeAll = '考试成绩较差';    END IF;END $CALL pro_avg_score(@gradeAll);SELECT @gradeAll;