創(chuàng)建數(shù)據(jù)庫(kù) exec
CREATE DATABASE exec;
創(chuàng)建學(xué)生表
CREATE TABLE student(
sno VARCHAR(3),
sname VARCHAR(10) NOT NULL,
ssex VARCHAR(3),
sbrithday DATETIME,
class VARCHAR(5) NOT NULL,
PRIMARY KEY(sno))
ENGINE=INNODB DEFAULT CHARSET=utf8;
創(chuàng)建課程表
CREATE TABLE course(
cno VARCHAR(10),
cname VARCHAR(30) NOT NULL,
tno VARCHAR(10) NOT NULL,
PRIMARY KEY(cno))
ENGINE=INNODB DEFAULT CHARSET=utf8;
創(chuàng)建成績(jī)表
CREATE TABLE grade(
sno VARCHAR(3),
cno VARCHAR(5),
degree TINYINT NOT NULL,
PRIMARY KEY(sno, cno))
ENGINE=INNODB DEFAULT CHARSET=utf8;
創(chuàng)建教師表
CREATE TABLE teacher(
tno VARCHAR(5),
tname VARCHAR(20) NOT NULL,
tsex VARCHAR(2),
tbrithday DATETIME,
prof VARCHAR(20) NOT NULL,
depart VARCHAR(30) NOT NULL,
PRIMARY KEY(tno))
ENGINE=INNODB DEFAULT CHARSET=utf8;
創(chuàng)建等級(jí)表
CREATE TABLE rank(
down TINYINT NOT NULL,
up TINYINT NOT NULL,
rank VARCHAR(2),
PRIMARY KEY(rank))
ENGINE=INNODB DEFAULT CHARSET=utf8;
插入學(xué)生信息
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);
插入成績(jī)信息
INSERT INTO GRADE(SNO,CNO,DEGREE)VALUES ('103','3-245',86);
INSERT INTO GRADE(SNO,CNO,DEGREE)VALUES (105,'3-245',75);
INSERT INTO GRADE(SNO,CNO,DEGREE)VALUES (109,'3-245',68);
INSERT INTO GRADE(SNO,CNO,DEGREE)VALUES (103,'3-105',92);
INSERT INTO GRADE(SNO,CNO,DEGREE)VALUES (105,'3-105',88);
INSERT INTO GRADE(SNO,CNO,DEGREE)VALUES (109,'3-105',76);
INSERT INTO GRADE(SNO,CNO,DEGREE)VALUES (101,'3-105',64);
INSERT INTO GRADE(SNO,CNO,DEGREE)VALUES (107,'3-105',91);
INSERT INTO GRADE(SNO,CNO,DEGREE)VALUES (108,'3-105',78);
INSERT INTO GRADE(SNO,CNO,DEGREE)VALUES (101,'6-166',85);
INSERT INTO GRADE(SNO,CNO,DEGREE)VALUES (107,'6-106',79);
INSERT INTO GRADE(SNO,CNO,DEGREE)VALUES (108,'6-166',81);
插入等級(jí)信息
INSERT INTO rank(down,up,rank) VALUES(90,100,'A');
INSERT INTO rank(down,up,rank) VALUES(80,89,'B');
INSERT INTO rank(down,up,rank) VALUES(70,79,'C');
INSERT INTO rank(down,up,rank) VALUES(60,69,'D');
INSERT INTO rank(down,up,rank) VALUES(0,59,'E');
插入課程信息
INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('3-105' ,'計(jì)算機(jī)導(dǎo)論', '825');
INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('3-245' ,'操作系統(tǒng)' , '804');
INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('6-166' ,'數(shù)據(jù)電路' , '856');
INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('9-888' ,'高等數(shù)學(xué)' , '100');
1、 查詢Student表中的所有記錄的Sname、Ssex和Class列。
SELECT sname, ssex, class FROM student;
2、 查詢教師所有的單位即不重復(fù)的Depart列。
SELECT DISTINCT depart FROM teacher;
3、 查詢Student表的所有記錄。
SELECT * FROM student;
4、 查詢Grade表中成績(jī)?cè)?0到80之間的所有記錄。
SELECT * FROM grade WHERE degree BETWEEN 60 AND 80;
5、 查詢Grade表中成績(jī)?yōu)?5，86或88的記錄。
SELECT * FROM grade WHERE degree IN (85, 86, 88);
6、 查詢Student表中“95031”班或性別為“女”的同學(xué)記錄。
SELECT * FROM student WHERE class='95031' AND ssex='女';
7、 以Class降序查詢Student表的所有記錄。
SELECT * FROM student ORDER BY class DESC;
8、 以Cno升序、Degree降序查詢Grade表的所有記錄。
SELECT * FROM grade ORDER BY cno, degree DESC;
9、 查詢“95031”班的學(xué)生人數(shù)。
SELECT COUNT(class) FROM student WHERE class='95031';
10、查詢Grade表中的最高分的學(xué)生學(xué)號(hào)和課程號(hào)。
SELECT sno, cno FROM grade ORDER BY degree LIMIT 1;
11、查詢‘3-105’號(hào)課程的平均分。
SELECT AVG(degree) FROM grade WHERE cno='3-105';
12、查詢Grade表中至少有5名學(xué)生選修的并以3開頭的課程的平均分?jǐn)?shù)。
SELECT AVG(degree), COUNT(*) AS c FROM grade WHERE cno LIKE '3%' GROUP BY cno HAVING c>=5;
13、查詢最低分大于70，最高分小于90的Sno列。
SELECT sno FROM grade GROUP BY sno HAVING MIN(degree)>70 AND MAX(degree)<90;
14、查詢所有學(xué)生的Sname、Cno和Degree列。
SELECT student.sname, grade.cno, grade.degree FROM student JOIN grade ON student.sno=grade.sno;
15、查詢所有學(xué)生的Sno、Cname和Degree列。
SELECT student.sno,course.cname,grade.degree FROM student JOIN grade ON student.sno=grade.sno JOIN course ON grade.cno=course.cno;
16、查詢所有學(xué)生的Sname、Cname和Degree列。
SELECT student.sname, course.cname, grade.degree FROM student JOIN grade ON student.sno=grade.sno JOIN course ON grade.cno=course.cno;
17、查詢“95033”班所選課程的平均分。
SELECT AVG(degree) FROM grade JOIN student ON grade.sno=student.sno WHERE class='95033';
18、查詢選修課成績(jī)?yōu)锳等的學(xué)生信息
SELECT * FROM student JOIN grade ON student.sno=grade.sno GROUP BY degree HAVING MIN(degree)>=90 AND MAX(degree)<=100;
19、在grade表中查詢選修“3-105”課程的成績(jī)高于選修“3-105”并且學(xué)號(hào)為“109”的所有同學(xué)的記錄。
SELECT * FROM student JOIN grade ON student.sno=grade.sno WHERE grade.degree>(SELECT degree FROM grade WHERE grade.sno='109' AND grade.cno='3-105') AND grade.cno='3-105';
