mysql 练习题笔记

 

 

创建如下表：

学生student：sno：学号，sname：姓名，age：年龄 sex：性别

create database school;

use school;

格式：
create table if not exists 表名(列名 数据类型);

create table if not exists student(
    sno char(20), 
    sname char(20) character set gbk, 
    age int, sex char(2) character set gbk check(sex in('男','女')), 
    primary key(sno)
);

插入数据
格式：
insert into 表名 (列名1,列名2,列名3) values ("1",2,5)
或者    insert into 表名 ("1",2,5)

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, '男');

查询数据表
select * from student;


* 注意 如果cmd查询出来是 乱码？
set character_set_client =gb2312;
set character_set_connection =gb2312;
set character_set_results =gb2312;
或者 set names gbk
https://blog.csdn.net/qq_44084157/article/details/91527148

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

课程course：cno：课程代码，cname：课程名称，teacher：教师

create table if not exists course(
    cno char(20),
    cname char(20) character set gbk,
    teacher char(20) character set gbk,
    primary key(cno)
);

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

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

学生成绩sc：sno：学号，cno：课程代码，score：成绩

create table if not exists sc(
    sno char(20) not null,
    cno char(20) not null,
    score int null,
    primary key(sno,cno),
    foreign key(sno) references student(sno),
    foreign key(cno) references course(cno)    
);

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)
);