mysql 练习
USE gg;
/*
CREATE TABLE STUDENT
(SNO VARCHAR(3) NOT NULL,
SNAME VARCHAR(4) NOT NULL,
SSEX VARCHAR(2) NOT NULL,
SBIRTHDAY DATETIME,
CLASS VARCHAR(5)) COLLATE=’utf8_bin’ ENGINE=InnoDB;
CREATE TABLE COURSE
(CNO VARCHAR(5) NOT NULL,
CNAME VARCHAR(10) NOT NULL,
TNO VARCHAR(10) NOT NULL) COLLATE=’utf8_bin’ ENGINE=InnoDB;
CREATE TABLE SCORE
(SNO VARCHAR(3) NOT NULL,
CNO VARCHAR(5) NOT NULL,
DEGREE NUMERIC(10, 1) NOT NULL) COLLATE=’utf8_bin’ ENGINE=InnoDB;
CREATE TABLE TEACHER
(TNO VARCHAR(3) NOT NULL,
TNAME VARCHAR(4) NOT NULL, TSEX VARCHAR(2) NOT NULL,
TBIRTHDAY DATETIME NOT NULL, PROF VARCHAR(6),
DEPART VARCHAR(10) NOT NULL) COLLATE=’utf8_bin’ ENGINE=InnoDB;
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);
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);
INSERT INTO SCORE(SNO,CNO,DEGREE) VALUES (‘103′,’3_245’,86);
INSERT INTO SCORE(SNO,CNO,DEGREE) VALUES (‘105′,’3_245’,75);
INSERT INTO SCORE(SNO,CNO,DEGREE) VALUES (‘109′,’3_245’,68);
INSERT INTO SCORE(SNO,CNO,DEGREE) VALUES (‘103′,’3_105’,92);
INSERT INTO SCORE(SNO,CNO,DEGREE) VALUES (‘105′,’3_105’,88);
INSERT INTO SCORE(SNO,CNO,DEGREE) VALUES (‘109′,’3_105’,76);
INSERT INTO SCORE(SNO,CNO,DEGREE) VALUES (‘101′,’3_105’,64);
INSERT INTO SCORE(SNO,CNO,DEGREE) VALUES (‘107′,’3_105’,91);
INSERT INTO SCORE(SNO,CNO,DEGREE) VALUES (‘108′,’3_105’,78);
INSERT INTO SCORE(SNO,CNO,DEGREE) VALUES (‘101′,’6_166’,85);
INSERT INTO SCORE(SNO,CNO,DEGREE) VALUES (‘107′,’6_106’,79);
INSERT INTO SCORE(SNO,CNO,DEGREE) VALUES (‘108′,’6_166’,81);
INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (‘804′,’李诚’,’男’,’1958-12-02′,’副教授’,’计算机系’);
INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (‘856′,’张旭’,’男’,’1969-03-12′,’讲师’,’电子工程系’);
INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (‘825′,’王萍’,’女’,’1972-05-05′,’助教’,’计算机系’);
INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (‘831′,’刘冰’,’女’,’1977-08-14′,’助教’,’电子工程系’);
*/
/*
— 查询Student表中的所有记录的Sname、Ssex和Class列。
SELECT st.Sname,st.Ssex,st.Class
FROM STUDENT st;
— 查询教师所有的单位即不重复的Depart列。
SELECT DISTINCT tt.DEPART
FROM TEACHER tt ;
— 查询Student表的所有记录。
SELECT st.*
FROM STUDENT st;
— 查询Score表中成绩在60到80之间的所有记录。
SELECT sc.*
FROM SCORE sc
WHERE sc.DEGREE >= 60 AND sc.DEGREE <=80 ORDER BY sc.SNO desc;
— 查询Score表中成绩为85，86或88的记录。
SELECT sc.*
FROM SCORE sc
WHERE sc.DEGREE in (85,86,88) ORDER BY sc.SNO desc;
— 查询Student表中“95031”班或性别为“女”的同学记录。
SELECT st.*
FROM STUDENT st
WHERE st.CLASS = ‘95031’ OR st.SSEX =’女’
ORDER BY st.SNO;
— 以Class降序查询Student表的所有记录。
SELECT st.*
FROM STUDENT st
ORDER BY st.CLASS DESC
— 以Cno升序、Degree降序查询Score表的所有记录。
SELECT sc.*
FROM SCORE sc
ORDER BY sc.CNO ,sc.DEGREE DESC;
— 查询“95031”班的学生人数。
SELECT COUNT(*) <
