mysql 练习题笔记@

 

 

USE school_2;

-- 1. 学生信息表
CREATE TABLE student (
  SNO varchar(20) NOT NULL COMMENT '学号（主键）',
  SNAME varchar(20) DEFAULT NULL COMMENT '学生姓名',
  AGE int DEFAULT NULL COMMENT '学生年龄',
  SEX char(2) DEFAULT NULL COMMENT '学生性别',
  PRIMARY KEY (SNO)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='学生信息表：存储所有学生基本信息';

-- 2. 课程信息表
CREATE TABLE course (
  CNO varchar(20) NOT NULL COMMENT '课程号（主键）',
  CNAME varchar(20) DEFAULT NULL COMMENT '课程名称',
  TEACHER varchar(20) DEFAULT NULL COMMENT '任课教师姓名',
  PRIMARY KEY (CNO)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='课程信息表：存储所有开设课程信息';

-- 3. 成绩表（最后创建！）
CREATE TABLE sc (
  SNO varchar(20) NOT NULL COMMENT '学号',
  CNO varchar(20) NOT NULL COMMENT '课程号',
  SCORE int NOT NULL COMMENT '考试成绩',
  PRIMARY KEY (SNO,CNO),
  KEY CNO (CNO),
  CONSTRAINT sc_ibfk_1 FOREIGN KEY (SNO) REFERENCES student (SNO),
  CONSTRAINT sc_ibfk_2 FOREIGN KEY (CNO) REFERENCES course (CNO)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='成绩表：记录学生选课及成绩信息';



-- USE school_2;
-- 
-- -- 先删成绩表（外键依赖表）
-- DROP TABLE IF EXISTS sc;
-- 
-- -- 再删课程表
-- DROP TABLE IF EXISTS course;
-- 
-- -- 最后删学生表
-- DROP TABLE IF EXISTS student;

 

 

insert into student (sno, sname, age, sex) values ('1', '李强', 23, '男');
insert into student (sno, sname, age, sex) values ('2', '刘丽', 22, '女');
insert into student (sno, sname, age, sex) values ('5', '李友', 22, '男');
insert into student (sno, sname, age, sex) values ('6', '胡振瑜', 26, '男');

insert into course values('k1','c语言','王华');
insert into course(cno,cname,teacher) values('k5','数据库原理','程军');
insert into course values('k8','编译原理','程军');

insert into sc values("1","k1",83);
insert into sc values("2","k1",85);
insert into sc values("5","k1",92);
insert into sc values("2","k5",90);
insert into sc values("5","k5",84);
insert into sc values("5","k8",80);

 

####################

1查询“程军”老师所教授的所有课程；
select * from course where teacher = "程军";

2查询“李强”同学所有课程的成绩;
select sc.score from sc,student where student.sname = "李强" and sc.sno=student.sno;

3查询课程名为“c语言”的平均成绩；
select avg(score) from sc,course where course.cname="c语言" and sc.cno = course.cno;

4查询选修了所有课程的同学信息。
select * from student
    where not exists
    (
    select * from course
    where not exists
    (
    select * from sc
    where course.cno=sc.cno and student.sno=sc.sno
    )
);

5检索王老师所授课程的课程号和课程名。
select cname,cno from course where teacher like "王%%";

6检索年龄大于23岁的男学生的学号和姓名。
select sno,sname from student where age>23;

7检索至少选修王老师所授课程中一门课程的女学生姓名。
    distinct 唯一的意思
select student.sname from student
    where student.sex="女" and sno in
    (
    select distinct(sno) from course,sc where teacher like '王%%'  and course.cno=sc.cno
);

8检索李同学不学的课程的课程号。
 反向思路 先查出他学了的课程 再用所有课程编号减去 他学了的
select course.cno from course
where course.cno not in
    (
    select sc.cno from student,sc 
    where sname like "李%" and student.sno=sc.sno
);

9检索至少选修两门课程的学生学号。
    只能以sno编组   注：如果以cno编组意思就是课程代码，选修>=2的数量
select sno from sc group by sno having count(*)>=2;

10检索全部学生都选修的课程的课程号与课程名。
select cno,cname from course
    where cno in
    (
    select cno from sc 
    group by cno having count(*)=(select count(*) from student)
);