MySQL-习题
2018-05-08 10:48 yelena 阅读(216) 评论(0) 编辑 收藏 举报/*查询每门课的平均成绩*/ SELECT cno,AVG(degree) FROM score GROUP BY cno; /*查询Score表中至少有5名学生选修的并以3开头的课程的平均分数*/ SELECT AVG(degree) FROM score WHERE cno=( SELECT cno FROM score GROUP BY cno HAVING COUNT(*)>5 AND cno LIKE '3%' ) /*查询分数大于70,小于90的Sno列*/ SELECT sno FROM score WHERE degree>70 AND degree<90; /*查询所有学生的Sname、Cno和Degree列*/ SELECT sname,cno,degree FROM score JOIN student ON score.`sno`=student.sno; /*查询所有学生的Sno、Cname和Degree列*/ SELECT student.sno,cname,degree FROM score JOIN student ON score.`sno`=student.`sno` JOIN course ON score.`cno`=course.`cno` /*查询所有学生的Sname、Cname和Degree列*/ SELECT sname,cname,degree FROM score JOIN student ON score.`sno`=student.`sno` JOIN course ON score.`cno`=course.`cno` /*查询“95033”班学生的平均分*/ SELECT AVG(degree) FROM score JOIN student ON score.`sno`=student.`sno` GROUP BY class HAVING class='95033' /*查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录*/ SELECT * FROM score JOIN student ON score.`sno`=student.`sno` WHERE degree>(SELECT degree FROM score WHERE sno='109' AND cno='3-105') AND cno='3-105'; /*查询score中选学多门课程的同学中分数为非最高分成绩的记录*/ SELECT * FROM score GROUP BY sno HAVING degree NOT IN ( SELECT MAX(degree) FROM score GROUP BY cno ) /*查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列*/ SELECT sno,sname,sbirthday FROM student WHERE sbirthday LIKE '1977%'; /*查询“张旭“教师任课的学生成绩(姓名)*/ SELECT sno,degree FROM score WHERE cno=( SELECT cno FROM course WHERE tno=( SELECT tno FROM teacher WHERE teacher.`tname`='张旭' ) ) /*查询考计算机导论的学生成绩*/ SELECT sno,degree FROM score JOIN course ON score.`cno`=course.`cno` WHERE cname='计算机导论' /*查询李诚老师教的课程名称*/ SELECT cname FROM course WHERE tno=( SELECT tno FROM teacher WHERE tname='李诚' ) /*教高等数学的老师是哪个系的*/ SELECT depart FROM teacher WHERE tno=( SELECT tno FROM course WHERE cname='高等数学' ) /*查询选修某课程的同学人数多于5人的教师姓名*/ SELECT teacher.`tname` FROM course JOIN teacher ON course.`tno`=teacher.`tno` WHERE cno = ( SELECT course.cno FROM score JOIN student ON score.`sno`=student.`sno` JOIN course ON score.`cno`=course.`cno` GROUP BY course.`cno` HAVING COUNT(*)>5 ) /*查询95033班和95031班全体学生的记录*/ SELECT * FROM score JOIN student ON score.`sno`=student.`sno` JOIN course ON score.`cno`=course.`cno` /*查询存在有85分以上成绩的课程Cno*/ SELECT course.cno FROM score JOIN course ON score.`cno`=course.`cno` WHERE degree>85 /*查询出“计算机系“教师所教课程的成绩表*/ SELECT degree FROM score WHERE cno IN( SELECT cno FROM course JOIN teacher ON course.`tno`=teacher.`tno` WHERE depart='计算机系' ) /*查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学 的Cno、Sno和Degree,并按Degree从高到低次序排序*/ SELECT * FROM score WHERE cno='3-105' AND degree>( SELECT MAX(degree) FROM score WHERE cno='3-245' ) ORDER BY degree DESC /*查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学 的Cno、Sno和Degree*/ SELECT * FROM score WHERE cno='3-105' AND degree>( SELECT MAX(degree) FROM score WHERE cno='3-245' ) /*查询所有教师和同学的name、sex和birthday*/ SELECT * FROM student JOIN score ON student.`sno`=score.`sno` JOIN course ON score.`cno`=course.`cno` JOIN teacher ON course.`tno`=teacher.`tno` SELECT * FROM student /*查询所有“女”教师和“女”同学的name、sex和birthday*/ SELECT * FROM score JOIN student ON score.`sno`=student.`sno` JOIN course ON score.`cno`=course.`cno` JOIN teacher ON course.`tno`=teacher.`tno` WHERE (ssex='女'AND tsex<>'男') OR (tsex='女' AND ssex<>'男') /*查询成绩比该课程平均成绩低的同学的成绩表*/ SELECT * FROM score WHERE (cno='3-105'AND degree<(SELECT AVG(degree) FROM score GROUP BY cno HAVING cno='3-105')) OR(cno='3-245'AND degree<(SELECT AVG(degree) FROM score GROUP BY cno HAVING cno='3-245')) OR(cno='6-166'AND degree<(SELECT AVG(degree) FROM score GROUP BY cno HAVING cno='6-166')) /*查询所有任课教师的Tname和Depart*/ SELECT tname,depart FROM course JOIN teacher ON course.`tno`=teacher.`tno` /*查询所有未讲课的教师的Tname和Depart*/ SELECT tname,depart FROM teacher WHERE teacher.`tno` NOT IN ( SELECT course.`tno` FROM course JOIN teacher ON course.`tno`=teacher.`tno` ) /*查询至少有2名男生的班号*/ SELECT class FROM student GROUP BY class HAVING COUNT(*)>=2 /*查询Student表中不姓“王”的同学记录*/ SELECT * FROM student WHERE sname NOT IN ( SELECT sname FROM student WHERE sname LIKE '王%' ) /*查询Student表中每个学生的姓名和年龄*/ SELECT sname,YEAR(NOW())-YEAR(sbirthday) AS age FROM student /*查询Student表中最大和最小的Sbirthday日期值*/ SELECT MAX(sbirthday),MIN(sbirthday) FROM student /*以班号和年龄从大到小的顺序查询Student表中的全部记录*/ SELECT class,YEAR(NOW())-YEAR(sbirthday) AS age FROM student ORDER BY class ASC,age ASC /*查询“男”教师及其所上的课程*/ SELECT tname,cname FROM course JOIN teacher ON course.`tno`=teacher.`tno` WHERE teacher.`tsex`='男' /*查询最高分同学的Sno、Cno和Degree列*/ SELECT student.sno,cno,degree FROM score JOIN student ON score.`sno`=student.`sno` WHERE degree=( SELECT MAX(degree) FROM score ) /*查询和“李军”同性别的所有同学的Sname*/ SELECT sname FROM student WHERE ssex=( SELECT ssex FROM student WHERE sname='李军' ) /*查询和“李军”同性别并同班的同学Sname*/ SELECT sname FROM student WHERE ssex=(SELECT ssex FROM student WHERE sname='李军') AND class=(SELECT class FROM student WHERE sname='李军') /*查询所有选修“计算机导论”课程的“男”同学的成绩表*/ SELECT * FROM score JOIN student ON score.`sno`=student.`sno` JOIN course ON score.`cno`=course.`cno`
CREATE TABLE product_type( protype_id INT PRIMARY KEY, protype_name VARCHAR(20) ) DESC product_type; CREATE TABLE product( pro_id INT PRIMARY KEY, pro_name VARCHAR(50), protype_id INT, price INT, pinpai VARCHAR(5), chandi VARCHAR(5), CONSTRAINT product_product_type_fk FOREIGN KEY(protype_id) REFERENCES product_type(protype_id) ) DESC product; INSERT INTO product_type VALUES(1,'家用电器'); INSERT INTO product_type VALUES(2,'手机数码'); INSERT INTO product_type VALUES(3,'电脑办公'); INSERT INTO product_type VALUES(4,'图书影像'); INSERT INTO product_type VALUES(5,'家居家具'); INSERT INTO product_type VALUES(6,'服装配饰'); INSERT INTO product_type VALUES(7,'个护化妆'); INSERT INTO product_type VALUES(8,'运动户外'); INSERT INTO product_type VALUES(9,'汽车用品'); INSERT INTO product_type VALUES(10,'食品酒水'); INSERT INTO product_type VALUES(11,'营养保健'); SELECT * FROM product_type; INSERT INTO product VALUES(1,'康佳(KONKA)42英寸全高清液晶电视',1,1999,'康佳','深圳'); SELECT * FROM product; INSERT INTO product VALUES(2,'索尼(SONY)4G手机(黑色)',2,3288,'索尼','深圳'); INSERT INTO product VALUES(3,'海信(Hisense)55英寸智能电视',1,4199,'海信','青岛'); INSERT INTO product VALUES(4,'联想(Lenovo)14.0英寸笔记本电脑',3,5499,'联想','北京'); INSERT INTO product VALUES(5,'索尼(SONY)13.3英寸触控超极本',3,11499,'索尼','天津'); INSERT INTO product VALUES(11,'索尼(SONY)60英寸全高清液晶电视',1,6999,'索尼','天津'); INSERT INTO product VALUES(12,'联想(Lenovo)14.0英寸笔记本电脑',3,2999,'联想','北京'); INSERT INTO product VALUES(13,'联想 双卡双待3G手机',2,988,'联想','北京'); INSERT INTO product VALUES(15,'惠普(HP)黑白激光打印机',3,1169,'惠普','天津'); /*查询价格在1000-5000之间品牌为联想的商品名称、商品价格、产品类型*/ SELECT pro_name,price,protype_name FROM product JOIN product_type ON product.`protype_id`=product_type.`protype_id` WHERE (price BETWEEN 1000 AND 5000) AND (pro_name LIKE '%联想%'); /*查询ID为5的商品的产品类型相同的所有品牌的品牌、产地、此品牌的商品数量*/ SELECT * FROM product JOIN product_type ON product.`protype_id`=product_type.`protype_id` WHERE protype_name=(SELECT protype_name FROM product JOIN product_type ON product.`protype_id`=product_type.`protype_id` WHERE pro_id=5) /*删除产品类型表中ID大于7的记录*/ DELETE FROM product_type WHERE protype_id>7; /*修改'家居家具'为'家具用品'*/ UPDATE product_type SET protype_name='家具用品' WHERE protype_id=5; /*查询‘家用电器’下所有商品的品牌和价格*/ SELECT pinpai,price FROM product JOIN product_type ON product.`protype_id`=product_type.`protype_id` WHERE protype_name='家用电器';
CREATE TABLE student( sno VARCHAR(20) PRIMARY KEY, sname VARCHAR(20) NOT NULL, ssex VARCHAR(20) NOT NULL, sbirthday DATETIME, class VARCHAR(20) ) DESC student; CREATE TABLE teacher( tno VARCHAR(20) PRIMARY KEY, tname VARCHAR(20) NOT NULL, tsex VARCHAR(20) NOT NULL, tbirthday DATETIME, prof VARCHAR(20), depart VARCHAR(20) NOT NULL ) DESC teacher; CREATE TABLE course( cno VARCHAR(20) PRIMARY KEY, cname VARCHAR(20) NOT NULL, tno VARCHAR(20) NOT NULL, CONSTRAINT course_teacher_fk FOREIGN KEY(tno) REFERENCES teacher(tno) ) DESC course; CREATE TABLE score( sno VARCHAR(20) NOT NULL, cno VARCHAR(20) NOT NULL, degree DECIMAL(4,1), CONSTRAINT score_student_fk FOREIGN KEY(sno) REFERENCES student(sno), CONSTRAINT score_course_fk FOREIGN KEY(cno) REFERENCES course(cno) ) DESC score; INSERT INTO student VALUES('108','曾华','男',19770901,'95033'); SELECT * FROM student; INSERT INTO student VALUES('105','匡明','男',19751002,'95031'); INSERT INTO student VALUES('107','王丽','女',19760123,'95033'); INSERT INTO student VALUES('101','李军','男',19760220,'95033'); INSERT INTO student VALUES('109','王芳','女',19750210,'95031'); INSERT INTO student VALUES('103','陆君','男',19740603,'95031'); INSERT INTO teacher VALUES('804','李诚','男',19581202,'副教授','计算机系'); SELECT * FROM teacher; INSERT INTO teacher VALUES('856','张旭','男',19690312,'讲师','电子工程系'); INSERT INTO teacher VALUES('825','王萍','女',19720505,'助教','计算机系'); INSERT INTO teacher VALUES('831','刘冰','女',19770814,'助教','电子工程系'); INSERT INTO course VALUES('3-105','计算机导论','825'); SELECT * FROM course; INSERT INTO course VALUES('3-245','操作系统','804'); INSERT INTO course VALUES('6-166','数字电路','856'); INSERT INTO course VALUES('9-888','高等数学','831'); INSERT INTO score VALUES('103','3-245',86); SELECT * FROM score; INSERT INTO score VALUES('105','3-245',75); INSERT INTO score VALUES('109','3-245',68); INSERT INTO score VALUES('103','3-105',92); INSERT INTO score VALUES('105','3-105',88); INSERT INTO score VALUES('109','3-105',76); INSERT INTO score VALUES('101','3-105',64); INSERT INTO score VALUES('107','3-105',91); INSERT INTO score VALUES('108','3-105',78); INSERT INTO score VALUES('101','6-166',85); INSERT INTO score VALUES('107','6-166',79); INSERT INTO score VALUES('108','6-166',81); TRUNCATE TABLE score; /*查询Student表中的所有记录的Sname、Ssex和Class列*/ SELECT sname,ssex,class FROM student; /*查询教师所有的单位即不重复的Depart列*/ SELECT DISTINCT depart FROM teacher; /*查询Student表的所有记录*/ SELECT * FROM student; /*查询Score表中成绩在60到80之间的所有记录*/ SELECT * FROM score WHERE degree BETWEEN 60 AND 80; /*查询Score表中成绩为85,86或88的记录*/ SELECT * FROM score WHERE degree=85 OR degree=86 OR degree=88; /*查询Student表中“95031”班或性别为“女”的同学记录*/ SELECT * FROM student WHERE class='95031' OR ssex='女'; /*以Class降序查询Student表的所有记录*/ SELECT * FROM student ORDER BY class DESC; /*以Cno升序、Degree降序查询Score表的所有记录*/ SELECT * FROM score ORDER BY cno ASC,degree DESC; /*查询“95031”班的学生人数*/ SELECT class,COUNT(class) FROM student GROUP BY class; /*查询Score表中的最高分的学生学号和课程号*/ SELECT student.sno,cno FROM score JOIN student ON score.`sno`=student.`sno` WHERE degree=( SELECT MAX(degree) FROM score JOIN student )