mysql decs倒叙查询_MySQL表单查询综合练习

班级：网络1302 学号：2013045421 姓名：缪晨杰

CREATE DATABASE stu;

USE stu;

-- 学生表

CREATE TABLE STUDENT(

sno VARCHAR(3) NOT NULL,

sname VARCHAR(4) NOT NULL,

ssex VARCHAR(2) NOT NULL,

SBIRTHDAY DATETIME,

CLASS VARCHAR(5));

INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (108 ,'曾华' ,'男' ,'1977-09-01','95033');

INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (105 ,'匡明' ,'男' ,'1975-10-02','95031');

INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (107 ,'王丽' ,'女' ,'1976-01-23','95033');

INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (101 ,'李军' ,'男' ,'1976-02-20','95033');

INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (109 ,'王芳' ,'女' ,'1975-02-10','95031');

INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (103 ,'陆君' ,'男' ,'1974-06-03','95031');

-- 课程表

CREATE TABLE COURSE(

CNO VARCHAR(5) NOT NULL,

CNAME VARCHAR(10) NOT NULL,

TNO VARCHAR(10) NOT NULL);

INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('3-105' ,'计算机导论',825); INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('3-245' ,'操作系统' ,804); INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('6-166' ,'数据电路' ,856); INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('9-888' ,'高等数学' ,100);

-- 成绩表

CREATE TABLE SCORE (

SNO VARCHAR(3) NOT NULL,

CNO VARCHAR(5) NOT NULL,

DEGREE DEC(10, 1) NOT NULL);

INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (103,'3-245',86);

INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (105,'3-245',75);