数据库习题大集合

习题数据资料

  1 #创建表及插入记录
  2 CREATE TABLE class (
  3   cid int(11) NOT NULL AUTO_INCREMENT,
  4   caption varchar(32) NOT NULL,
  5   PRIMARY KEY (cid)
  6 ) ENGINE=InnoDB CHARSET=utf8;
  7 
  8 INSERT INTO class VALUES
  9 (1, '三年二班'), 
 10 (2, '三年三班'), 
 11 (3, '一年二班'), 
 12 (4, '二年九班');
 13 
 14 CREATE TABLE course(
 15   cid int(11) NOT NULL AUTO_INCREMENT,
 16   cname varchar(32) NOT NULL,
 17   teacher_id int(11) NOT NULL,
 18   PRIMARY KEY (cid),
 19   KEY fk_course_teacher (teacher_id),
 20   CONSTRAINT fk_course_teacher FOREIGN KEY (teacher_id) REFERENCES teacher (tid)
 21 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 22 
 23 INSERT INTO course VALUES
 24 (1, '生物', 1), 
 25 (2, '物理', 2), 
 26 (3, '体育', 3), 
 27 (4, '美术', 2);
 28 
 29 CREATE TABLE score (
 30   sid int(11) NOT NULL AUTO_INCREMENT,
 31   student_id int(11) NOT NULL,
 32   course_id int(11) NOT NULL,
 33   num int(11) NOT NULL,
 34   PRIMARY KEY (sid),
 35   KEY fk_score_student (student_id),
 36   KEY fk_score_course (course_id),
 37   CONSTRAINT fk_score_course FOREIGN KEY (course_id) REFERENCES course (cid),
 38   CONSTRAINT fk_score_student FOREIGN KEY (student_id) REFERENCES student(sid)
 39 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 40 
 41 INSERT INTO score VALUES
 42 (1, 1, 1, 10),
 43 (2, 1, 2, 9),
 44 (5, 1, 4, 66),
 45 (6, 2, 1, 8),
 46 (8, 2, 3, 68),
 47 (9, 2, 4, 99),
 48 (10, 3, 1, 77),
 49 (11, 3, 2, 66),
 50 (12, 3, 3, 87),
 51 (13, 3, 4, 99),
 52 (14, 4, 1, 79),
 53 (15, 4, 2, 11),
 54 (16, 4, 3, 67),
 55 (17, 4, 4, 100),
 56 (18, 5, 1, 79),
 57 (19, 5, 2, 11),
 58 (20, 5, 3, 67),
 59 (21, 5, 4, 100),
 60 (22, 6, 1, 9),
 61 (23, 6, 2, 100),
 62 (24, 6, 3, 67),
 63 (25, 6, 4, 100),
 64 (26, 7, 1, 9),
 65 (27, 7, 2, 100),
 66 (28, 7, 3, 67),
 67 (29, 7, 4, 88),
 68 (30, 8, 1, 9),
 69 (31, 8, 2, 100),
 70 (32, 8, 3, 67),
 71 (33, 8, 4, 88),
 72 (34, 9, 1, 91),
 73 (35, 9, 2, 88),
 74 (36, 9, 3, 67),
 75 (37, 9, 4, 22),
 76 (38, 10, 1, 90),
 77 (39, 10, 2, 77),
 78 (40, 10, 3, 43),
 79 (41, 10, 4, 87),
 80 (42, 11, 1, 90),
 81 (43, 11, 2, 77),
 82 (44, 11, 3, 43),
 83 (45, 11, 4, 87),
 84 (46, 12, 1, 90),
 85 (47, 12, 2, 77),
 86 (48, 12, 3, 43),
 87 (49, 12, 4, 87),
 88 (52, 13, 3, 87);
 89 
 90 
 91 CREATE TABLE student(
 92   sid int(11) NOT NULL AUTO_INCREMENT,
 93   gender char(1) NOT NULL,
 94   class_id int(11) NOT NULL,
 95   sname varchar(32) NOT NULL,
 96   PRIMARY KEY (sid),
 97   KEY fk_class (class_id),
 98   CONSTRAINT fk_class FOREIGN KEY (class_id) REFERENCES class (cid)
 99 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
100 
101 INSERT INTO student VALUES
102 (1, '男', 1, '理解'), 
103 (2, '女', 1, '钢蛋'), 
104 (3, '男', 1, '张三'), 
105 (4, '男', 1, '张一'), 
106 (5, '女', 1, '张二'), 
107 (6, '男', 1, '张四'), 
108 (7, '女', 2, '铁锤'), 
109 (8, '男', 2, '李三'), 
110 (9, '男', 2, '李一'), 
111 (10, '女', 2, '李二'), 
112 (11, '男', 2, '李四'), 
113 (12, '女', 3, '如花'), 
114 (13, '男', 3, '刘三'), 
115 (14, '男', 3, '刘一'), 
116 (15, '女', 3, '刘二'), 
117 (16, '男', 3, '刘四');
118 
119 CREATE TABLE teacher(
120   tid int(11) NOT NULL AUTO_INCREMENT,
121   tname varchar(32) NOT NULL,
122   PRIMARY KEY (tid)
123 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
124 
125 INSERT INTO teacher VALUES
126 (1, '张磊老师'), 
127 (2, '李平老师'), 
128 (3, '刘海燕老师'), 
129 (4, '朱云海老师'), 
130 (5, '李杰老师');

习题资料

  1、查询所有的课程的名称以及对应的任课老师姓名  

　　注意
　　　　1.该题的主语,是课程;而非老师. 
　　　　2.表的字段名字再查询时要对应好,否则报错

select course.cname,teacher.tname from teacher inner join course on teacher_id=teacher.tid;

2、查询学生表中男女生各有多少人

     注意
        1.此处的count(4) 里的数字可以是任何数,主要是为了语法的完整.结果是各组人数
        2.分组后,select 后面只能进行与分组相关的查询, 包括使用聚合函数

select gender,count(4) from student group by gender;

3、查询物理成绩等于100的学生的姓名

　　注意
    　　course inner join score on 条件一定要给外键关联的(on后面的条件意味着,两个表如何结合);否则表结果,会形成笛卡儿积的效果(一张数据重复且数据混淆的表),这不是我们想要的.

select sname from student where sid in (select student_id from course inner join score on course_id= course.cid where course.cname="物理" and score.num=100);

4、查询平均成绩大于八十分的同学的姓名和平均成绩

    注意
       一定注意空格,avg(num) as 之间的空格一定不要省;否则会报错
select sname,avg_num from student inner join (select avg(num) as avg_num,student_id from score group by student_id having avg(num)>80)as t1 on student.sid=student_id;

5、查询所有学生的学号，姓名，选课数，总成绩

    注意
    没有选课的学生也是要算在里面的. sum()  总和;count() 计算个数
    on 后面的条件,字表中一定要给出,否则,报错
select sid,sname,count_course,sum_score from student left join (select 
student_id,count(course_id) as count_course,sum(num) as sum_score from score group by student_id) as t1 on t1.student_id=student.sid;

6、 查询姓李老师的个数

    注意
        正则使用的语法要注意, 如果要匹配的是绝对值,就用字段名=值;否则(%,_),字段名和值是分开的,中间有个like;其他的(^,$...)也是要跟值分开,不过中间以regexp分割.

select count(tname) from teacher where tname like "李%";

7、 查询没有报李平老师课的学生姓名

 

转载于:https://www.cnblogs.com/lgw1171435560/p/10295971.html