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(*) <