- CREATE DATABASE 50q;
- USE 50q;
- CREATE TABLE STUDENT
- (
- SNO VARCHAR(3) NOT NULL,
- SNAME VARCHAR(4) NOT NULL,
- SSEX VARCHAR(2) NOT NULL,
- SBIRTHDAY DATETIME,
- CLASS VARCHAR(5)
- );
- -- VARCHAR(M) 每个值占用的字节长度=该值字节数+1,M只是其最大值--
- -- MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format--
- CREATE TABLE COURSE
- (
- CNO VARCHAR(5) NOT NULL,
- CNAME VARCHAR(10) NOT NULL,
- TNO VARCHAR(3) NOT NULL
- );
- CREATE TABLE SCORE
- (
- SNO VARCHAR(3) NOT NULL,
- CNO VARCHAR(5) NOT NULL,
- DEGREE NUMERIC(10, 1) NOT NULL
- ) ;
- -- NUMERIC(5,2)的赋值范围是[-999.99,999.99]--
- -- NUMERIC(M)等价于NUMERIC(M,0), NUMERIC等价于NUMERIC(10)--
- 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
- );
- ALTER TABLE STUDENT ADD PRIMARY KEY (SNO);
- ALTER TABLE SCORE ADD PRIMARY KEY (SNO,CNO);
- ALTER TABLE COURSE ADD PRIMARY KEY (CNO);
- ALTER TABLE TEACHER ADD PRIMARY KEY (TNO);
- -- 先添加外键,再添加值--
- -- 外键在两张table中的数据类型须一致--
- ALTER TABLE SCORE ADD CONSTRAINT FK_SCORE_STUDENT FOREIGN KEY (SNO) REFERENCES STUDENT(SNO);
- ALTER TABLE SCORE ADD CONSTRAINT FK_SCORE_COURSE FOREIGN KEY (CNO) REFERENCES COURSE(CNO);
- ALTER TABLE COURSE ADD CONSTRAINT FK_COURSE_TEACHER FOREIGN KEY (TNO) REFERENCES TEACHER(TNO);
- -- 先对REFERENCES中所指的表添加值--
- 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 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','助教','电子工程系');
- 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' ,'高等数学' ,831);
- 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-166',79);
- INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (108,'6-166',81);
- -- 1、 查询Student表中的所有记录的Sname、Ssex和Class列。--
- SELECT SNAME,SSEX,CLASS FROM STUDENT;
- -- 2、 查询教师所有的单位即不重复的Depart列。--
- SELECT DISTINCT DEPART FROM TEACHER;
- -- 3、 查询Student表的所有记录。--
- SELECT * FROM STUDENT;
- -- 4、 查询Score表中成绩在60到80之间的所有记录。--
- SELECT * FROM SCORE
- WHERE DEGREE>=60 AND DEGREE<=80;
- SELECT * FROM SCORE
- WHERE DEGREE BETWEEN 60 AND 80; /*BETWEEN AND: 取值包括两边界*/
- -- 5、 查询Score表中成绩为85,86或88的记录。--
- SELECT * FROM SCORE
- WHERE DEGREE=85 OR DEGREE=86 OR DEGREE=88;
- SELECT * FROM SCORE
- WHERE DEGREE IN (85,86,88);
- -- 6、 查询Student表中“95031”班或性别为“女”的同学记录。--
- SELECT * FROM STUDENT
- WHERE CLASS=95031 OR SSEX='女';
- -- 7、 以Class降序查询Student表的所有记录。--
- SELECT * FROM STUDENT
- ORDER BY CLASS DESC;
- -- 8、 以Cno升序、Degree降序查询Score表的所有记录。--
- SELECT * FROM SCORE
- ORDER BY CNO, DEGREE DESC;
- -- 9、 查询“95031”班的学生人数。--
- SELECT COUNT(*) FROM STUDENT
- WHERE CLASS=95031;
- -- 10、查询Score表中的最高分的学生学号和课程号。--
- SELECT SNO,CNO FROM SCORE
- WHERE DEGREE=(SELECT MAX(DEGREE) FROM SCORE);
- SELECT SNO,CNO FROM SCORE
- ORDER BY DEGREE DESC
- LIMIT 1;
- -- 11、查询‘3-105’号课程的平均分。--
- SELECT AVG(DEGREE) FROM SCORE
- WHERE CNO='3-105';
- -- 12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。--
- SELECT AVG(DEGREE) FROM SCORE
- WHERE CNO LIKE'3%'
- GROUP BY CNO
- HAVING COUNT(*)>=5;
- -- 13、查询最低分大于70,最高分小于90的Sno列。--
- SELECT SNO FROM SCORE
- GROUP BY SNO
- HAVING MAX(DEGREE)<90 AND MIN(DEGREE)>70;
- -- 14、查询所有学生的Sname、Cno和Degree列。--
- SELECT SNAME,CNO,DEGREE
- FROM STUDENT,SCORE
- WHERE STUDENT.SNO=SCORE.SNO;
- SELECT SNAME,CNO,DEGREE
- FROM STUDENT INNER JOIN SCORE
- ON STUDENT.SNO=SCORE.SNO;
- -- 15、查询所有学生的Sno、Cname和Degree列。--
- SELECT SNO,CNAME,DEGREE
- FROM SCORE,COURSE
- WHERE SCORE.CNO=COURSE.CNO;
- SELECT SNO,CNAME,DEGREE
- FROM SCORE INNER JOIN COURSE
- ON SCORE.CNO=COURSE.CNO;
- -- 16、查询所有学生的Sname、Cname和Degree列。--
- SELECT SNAME,CNAME,DEGREE
- FROM STUDENT,COURSE,SCORE
- WHERE STUDENT.SNO=SCORE.SNO
- AND COURSE.CNO=SCORE.CNO;
- -- 17、查询“95033”班所选课程的平均分。--
- SELECT AVG(DEGREE)
- FROM SCORE,STUDENT
- WHERE SCORE.SNO=STUDENT.SNO
- AND CLASS=95033;
- SELECT AVG(DEGREE)
- FROM SCORE INNER JOIN STUDENT
- ON SCORE.SNO=STUDENT.SNO
- WHERE CLASS=95033;
- -- 18、假设使用如下命令建立了一个grade表:
- -- CREATE TABLE GRADE
- -- (
- -- LOW NUMERIC(3,0),
- -- UPP NUMERIC(3),
- -- RANK CHAR(1)
- -- );
- -- INSERT INTO GRADE
- -- VALUES(90,100,'A');
- -- INSERT INTO GRADE
- -- VALUES(80,89,'B');
- -- INSERT INTO GRADE
- -- VALUES(70,79,'C');
- -- INSERT INTO GRADE
- -- VALUES(60,69,'D');
- -- INSERT INTO GRADE
- -- VALUES(0,59,'E');
- -- COMMIT;
- -- 现查询所有同学的Sno、Cno和rank列。--
- SELECT SNO,CNO,DEGREE
- FROM SCORE INNER JOIN GRADE
- WHERE DEGREE BETWEEN LOW AND UPP;
- -- 19、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。--
- SELECT A.*
- FROM SCORE AS A JOIN SCORE AS B
- WHERE A.CNO='3-105'
- AND A.DEGREE>B.DEGREE
- AND B.SNO='109'
- AND B.CNO='3-105';
- SELECT * FROM SCORE
- WHERE CNO='3-105' AND DEGREE>(SELECT DEGREE FROM SCORE
- WHERE CNO='3-105' AND SNO=109);
- -- 20、查询score中选学一门以上课程的同学中分数为非最高分成绩的记录。--
- SELECT * FROM SCORE
- WHERE DEGREE<(SELECT MAX(DEGREE) FROM SCORE)
- AND SNO IN(SELECT SNO FROM SCORE
- GROUP BY SNO
- HAVING COUNT(*)>1);
- -- 21、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。--
- SELECT A.* FROM SCORE AS A INNER JOIN SCORE AS B
- WHERE B.SNO=109 AND B.CNO='3-105' AND A.CNO='3-105'AND A.DEGREE>B.DEGREE;
- -- 22、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。--
- SELECT SNO,SNAME,SBIRTHDAY
- FROM STUDENT
- WHERE YEAR(SBIRTHDAY)=(SELECT YEAR(SBIRTHDAY) FROM STUDENT
- WHERE SNO=108);
- -- 23、查询“张旭“教师任课的学生成绩。--
- SELECT DEGREE
- FROM SCORE,TEACHER,COURSE
- WHERE SCORE.CNO=COURSE.CNO
- AND COURSE.TNO=TEACHER.TNO
- AND TEACHER.TNAME='张旭';
- -- 24、查询选修某课程的同学人数多于5人的教师姓名。
- SELECT TNAME
- FROM TEACHER,COURSE,SCORE
- WHERE TEACHER.TNO=COURSE.TNO
- AND COURSE.CNO=SCORE.CNO
- GROUP BY SCORE.CNO
- HAVING COUNT(*)>5;
- -- 25、查询95033班和95031班全体学生的记录。--
- SELECT * FROM STUDENT
- WHERE CLASS IN (95033,95031);
- -- 26、查询存在有85分以上成绩的课程Cno.--
- SELECT DISTINCT CNO FROM SCORE
- WHERE DEGREE>85;
- -- 27、查询出“计算机系“教师所教课程的成绩表。--
- SELECT DEGREE FROM SCORE,TEACHER,COURSE
- WHERE SCORE.CNO=COURSE.CNO
- AND COURSE.TNO=TEACHER.TNO
- AND TEACHER.DEPART='计算机系';
- -- 28、查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。--
- SELECT TNAME,PROF FROM TEACHER
- WHERE DEPART='计算机系'
- AND PROF NOT IN (SELECT PROF FROM TEACHER
- WHERE DEPART='电子工程系');
- -- 29、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。--
- SELECT * FROM SCORE
- WHERE DEGREE>ANY(SELECT DEGREE FROM SCORE WHERE CNO='3-245')
- AND CNO='3-105'
- ORDER BY DEGREE DESC;
- -- 30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree.--
- SELECT * FROM SCORE
- WHERE DEGREE>ALL(SELECT DEGREE FROM SCORE WHERE CNO='3-245')
- AND CNO='3-105';
- -- 31、查询所有教师和同学的name、sex和birthday.--
- SELECT SNAME AS NAME,SSEX AS SEX,SBIRTHDAY AS BIRTHDAY
- FROM STUDENT
- UNION
- SELECT TNAME AS NAME,TSEX AS SEX,TBIRTHDAY AS BIRTHDAY
- FROM TEACHER;
- -- 32、查询所有“女”教师和“女”同学的name、sex和birthday.--
- SELECT SNAME AS NAME,SSEX AS SEX,SBIRTHDAY AS BIRTHDAY
- FROM STUDENT
- WHERE SSEX='女'
- UNION
- SELECT TNAME AS NAME,TSEX AS SEX,TBIRTHDAY AS BIRTHDAY
- FROM TEACHER
- WHERE TSEX='女';
- -- 33、查询成绩比该课程平均成绩低的同学的成绩表。--
- SELECT A.*
- FROM SCORE AS A LEFT OUTER JOIN SCORE AS B
- ON A.CNO=B.CNO
- GROUP BY A.SNO,B.CNO
- HAVING A.DEGREE<AVG(B.DEGREE);
- SELECT A.* FROM SCORE AS A
- WHERE DEGREE<(SELECT AVG(DEGREE) FROM SCORE AS B
- WHERE A.CNO=B.CNO);
- -- 34、查询所有任课教师的Tname和Depart.--
- SELECT TNAME,DEPART
- FROM TEACHER,COURSE
- WHERE TEACHER.TNO=COURSE.TNO;
- SELECT A.TNAME,A.DEPART
- FROM TEACHER A INNER JOIN COURSE B
- ON A.TNO=B.TNO;
- SELECT TNAME,DEPART FROM TEACHER AS A
- WHERE EXISTS(SELECT * FROM COURSE AS B
- WHERE A.TNO=B.TNO);
- SELECT TNAME,DEPART FROM TEACHER
- WHERE TNO IN (SELECT TNO FROM COURSE);
- -- 35 查询所有未讲课的教师的Tname和Depart.--
- SELECT TNAME,DEPART FROM TEACHER
- WHERE TNO NOT IN (SELECT TNO FROM COURSE); /*NOT IN效率最差*/
- SELECT TNAME,DEPART
- FROM TEACHER AS A LEFT JOIN COURSE AS B
- USING (TNO)
- WHERE B.TNO IS NULL;
- SELECT TNAME,DEPART FROM TEACHER AS A
- WHERE NOT EXISTS (SELECT * FROM COURSE AS B
- WHERE A.TNO=B.TNO);
- -- 36、查询至少有2名男生的班号。--
- SELECT CLASS FROM STUDENT
- GROUP BY CLASS,SSEX
- HAVING COUNT(*)>=2;
- SELECT CLASS FROM STUDENT
- WHERE SSEX='男'
- GROUP BY CLASS
- HAVING COUNT(SSEX)>1;
- -- 37、查询Student表中不姓“王”的同学记录。--
- SELECT * FROM STUDENT
- WHERE SNAME NOT LIKE '王%';
- -- 38、查询Student表中每个学生的姓名和年龄。--
- SELECT SNAME,YEAR(NOW())-YEAR(SBIRTHDAY) AS AGE
- FROM STUDENT;
- -- 39、查询Student表中最大和最小的Sbirthday日期值。--
- SELECT MAX(SBIRTHDAY),MIN(SBIRTHDAY)
- FROM STUDENT;
- -- 40、以班号和年龄从大到小的顺序查询Student表中的全部记录。--
- SELECT * FROM STUDENT
- ORDER BY CLASS DESC,SBIRTHDAY;
- -- 41、查询“男”教师及其所上的课程。--
- SELECT TEACHER.TNO,TNAME,CNO
- FROM TEACHER INNER JOIN COURSE
- ON TEACHER.TNO=COURSE.TNO
- WHERE TSEX='男';
- -- 42、查询最高分同学的Sno、Cno和Degree列。--
- SELECT SNO,CNO,DEGREE
- FROM SCORE
- WHERE DEGREE=(SELECT MAX(DEGREE) FROM SCORE);
- -- 43、查询和“李军”同性别的所有同学的Sname.--
- SELECT SNAME FROM STUDENT
- WHERE SSEX=(SELECT SSEX FROM STUDENT
- WHERE SNAME='李军');
- SELECT A.SNAME
- FROM STUDENT AS A, STUDENT AS B
- WHERE A.SSEX=B.SSEX
- AND B.SNAME='李军';
- -- 44、查询和“李军”同性别并同班的同学Sname.--
- SELECT A.SNAME
- FROM STUDENT AS A, STUDENT AS B
- WHERE A.SSEX=B.SSEX
- AND A.CLASS=B.CLASS
- AND B.SNAME='李军';
- -- 45、查询所有选修“计算机导论”课程的“男”同学的成绩表--
- SELECT STUDENT.SNO,DEGREE
- FROM SCORE,STUDENT,COURSE
- WHERE SCORE.CNO=COURSE.CNO
- AND STUDENT.SNO=SCORE.SNO
- AND COURSE.CNAME='计算机导论'
- AND STUDENT.SSEX='男';