数据库应用课程学生表、课程表、选课表mysql语句实战练习

-- 创建学生表
create table  student(
sno char(7) PRIMARY key COMMENT '学号',
sname varchar(10) not null COMMENT "姓名",
ssex char(2) COMMENT '性别',
sage int  COMMENT '年龄',
dept varchar(20) COMMENT '所在系',
check( ssex in ('男','女'))
);

显示学生表的类型

desc student;

1.2 创建课程表

-- 创建课程表
create table course(
cno varchar(10) primary key COMMENT '课程号',
cname varchar(20) not null COMMENT '课程名',
credit int  COMMENT '学分',
semester int null COMMENT '开课学期',
check(credit>0)
);

显示课程表的类型

describe course;

1.3创建选课表

-- 创建选课表
create table sc(
sno char(7)  COMMENT '学号',
cno char(10)  COMMENT '课程号',
grade int comment '成绩',
PRIMARY key (sno,cno),
check(grade BETWEEN 0 and 100),
CONSTRAINT fk_sno FOREIGN key(sno) REFERENCES student(sno)
on UPDATE CASCADE on DELETE CASCADE,
CONSTRAINT fk_cno FOREIGN key(cno) REFERENCES course(cno)
on UPDATE CASCADE on DELETE CASCADE
);

显示选课表的类型

desc sc;

1.4 插入学生表

insert into student(sno,sname,ssex,sage,dept)
VALUES
('0811101','李勇','男',21 ,'计算机系'),
('0811102','刘晨','男',20 ,'计算机系'),
('0811103','王敏','女',20 ,'计算机系'),
('0811104','张晓红','女',19 ,'计算机系'),
('0821101','张立','男',20 ,'信息管理系'),
('0821102','吴宾','女',19 ,'信息管理系'),
('0821103','张海','男',20 ,'信息管理系'),
('0831101','钱小平','女',21 ,'通信工程系'),
('0831102','王大力','男',20 ,'通信工程系'),
('0831103','张珊珊','女',19 ,'通信工程系');

查询插入学生表的情况

select * from student;

1.5 插入课程表

-- 插入课程表
insert  course
VALUES
('C001','高等数学',4,1),
('C002','大学英语',3,1),
('C003','大学英语',3,2),
('C004','计算机文化学',2,2),
('C005','Java',2,3),
('C006','数据库基础',4,5),
('C007','数据结构',4,4),
('C008','计算机网络',4,4);

查询插入课程表的情况

select * from course;

1.6 插入选课表

-- 插入选课表
insert into sc
VALUES
('0811101','C001',96),
('0811101','C002',80),
('0811101','C003',84),
('0811101','C005',62),
('0811102','C001',92),
('0811102','C002',90),
('0811102','C004',84),
('0811102','C006',76),
('0811102','C003',85),
('0811102','C005',73),
('0811102','C007',null),
('0811103','C001',50),
('0811103','C004',80),
('0831101','C001',50),
('0831101','C004',80),
('0831102','C007',null),
('0831103','C004',78),
('0831103','C005',65),
('0831103','C007',null);

查询选课表情况

select * from sc;

2.以下布置操作是根据以上建立的三张表格基础上进行mysql语句的练习

2.1 查询学生信息并添加入新表

2.1.1 查询全体学生的学号与姓名，查询结果存入新表s1。

-- 查询全体学生的学号与姓名，查询结果存入新表s1。
create table s1 select sno,sname from student;

2.1.2查询全体学生的姓名、学号和所在系，查询结果存入新表s2。

-- 查询全体学生的姓名、学号和所在系，查询结果存入新表s2。
select sname,sno,dept from student;

查询全体学生的详细记录

-- 查询全体学生的详细记录
select * from student;

2.2 查询全体学生的姓名及其出生年份

-- 查询全体学生的姓名及其出生年份
select sname,year(now())-sage as 出生年份 from student;

查询计算机系全体学生

-- 查询计算机系全体学生
select * from student where dept='计算机系';

查询所有年龄20岁以下的学生的姓名及年龄，

-- 查询所有年龄20岁以下的学生的姓名及年龄，
select sname,sage from student where sage<20;

2.3 根据信息条件（分数范围）检索满足条件的信息

查询成绩不及格学生的学号

-- 查询成绩不及格学生的学号
select sno from sc where grade<60;

查询考试成绩在80～90之间的学生学号、课程号和成绩

-- 查询考试成绩在80～90之间的学生学号、课程号和成绩
select sno,cno,grade from sc where grade BETWEEN 80 and  90;

等价

select sno,cno,grade from sc where grade >= 80 and  grade<=90;

查询考试成绩不在80～90之间的学生学号、课程号和成绩

-- 查询考试成绩不在80～90之间的学生学号、课程号和成绩

select sno,cno,grade from sc where grade not BETWEEN 80 and  90;

等价

select sno,cno,grade from sc where grade>=0 and grade < 80 or  grade<=100 and grade>90;

2.4 根据所属（系别）的范围检索满足条件的信息

查询信息管理系、通信工程系和计算机系学生的姓名和性别

-- 查询信息管理系、通信工程系和计算机系学生的姓名和性别
select sname,ssex from student where dept in ('信息管理系','通信工程系','计算机系');

等价

select sname,ssex from student where dept='信息管理系' or dept='通信工程系' or dept='计算机系';

我们再插入几个同学的数据

insert into student(sno,sname,ssex,sage,dept)
VALUES
('0811111','李佳均','男',99 ,'美术系'),
('0811122','张某龙','男',20 ,'建工系'),
('0811133','王假杰','男',20 ,'机械系'),
('0811144','杨某男','男',20 ,'机械系');

查询信息管理系、通信工程系和计算机系三个系之外的其他系学生的姓名和性别

-- 查询信息管理系、通信工程系和计算机系三个系之外的其他系学生的姓名和性别

select sname,ssex from student where dept not in 
(select dept from student where dept in ('信息管理系','通信工程系','计算机系'));
--下面等价

select sname,ssex from student where dept not in ('信息管理系','通信工程系','计算机系');

2.5 根据部分关键字匹配检索信息

查询姓“张”的学生详细信息

-- 查询姓“张”的学生详细信息
SELECT * from student where sname LIKE '张%';

查询姓“张”、姓“李”和姓“刘”的学生的详细信息

-- 查询姓“张”、姓“李”和姓“刘”的学生的详细信息
SELECT * from student where sname LIKE '张%' or sname LIKE '李%' or sname LIKE '刘%';

查询名字的第2个字为“小”或“大”的学生的姓名和学号

-- 查询名字的第2个字为“小”或“大”的学生的姓名和学号
SELECT sname,sno from student where sname LIKE '_小%' or sname LIKE '_大%';

查询所有不姓“刘”的学生姓名

-- 查询所有不姓“刘”的学生姓名
select sname from student where sname not like '刘%';

在Student表中查询学号的最后一位不是2、3、5的学生信息

-- 在Student表中查询学号的最后一位不是2、3、5的学生信息
select * from student where sno not like '%2' or sno not like '%3' or sno not like '%5';

查询还没有考试的学生的学号和相应的课程号

-- 查询还没有考试的学生的学号和相应的课程号
SELECT sno,cno from sc where grade is null;

2.6 根据关键字的具体信息

查询计算机系男生的姓名

-- 查询计算机系男生的姓名
select sname from student where dept='计算机系' and ssex='男';

查询C002和C003课程中考试成绩在80～90的学生的学号、课程号和成绩

-- 查询C002和C003课程中考试成绩在80～90的学生的学号、课程号和成绩
select sno,cno,grade from sc where cno in('C002','C003') and  grade BETWEEN 80 and 90;

2.7 根据降序、升序、平均值、最高、最低分

查询修了“C002”课程的学生的学号及成绩，查询结果按成绩降序排列

-- 查询修了“C002”课程的学生的学号及成绩，查询结果按成绩降序排列，

select sno ,grade from sc where cno ='C002' order by grade desc;

查询全体学生详细信息，结果按系名升序排列，同一个系的学生按出生日期降序排列

-- 查询全体学生详细信息，结果按系名升序排列，同一个系的学生按出生日期降序排列
select * from student order by dept,year(now())-sage  desc;

统计学生总人数

-- 统计学生总人数
select count(*) from student;

统计选修了课程的学生人数

-- 统计选修了课程的学生人数
select count(DISTINCT sno) as count from sc ;

计算学号为“0811101”的学生的考试总成绩

--  计算学号为“0811101”的学生的考试总成绩
select sum(grade) from sc where sno='0811101';

计算“0831103”学生的平均成绩

-- 计算“0831103”学生的平均成绩
select round(avg(grade),1) as avg from sc where sno like '0831103';

查询“C001”课程考试成绩的最高分和最低分

-- 查询“C001”课程考试成绩的最高分和最低分
select cno,max(grade),min(grade) from sc where cno='C001';

2.8 根据同一类别（group by）统计属于这个类别的数量

统计每门课程的选课人数，列出课程号和选课人数

-- 统计每门课程的选课人数，列出课程号和选课人数

select cno,count(sno)as count from sc GROUP BY cno;

统计每个学生的选课门数和平均成绩

-- 统计每个学生的选课门数和平均成绩
select sno,count(cno),avg(grade) from sc group by sno;

统计每个系的女生人数

-- 统计每个系的女生人数
select dept,count(sno) from student where ssex='女' group by dept;

3.多个的条件共同筛选下的查询（进阶）

统计每个系的男生人数和女生人数以及男生的最大年龄和女生的最大年龄

-- 统计每个系的男生人数和女生人数以及男生的最大年龄和女生的最大年龄
select dept, ssex, count(sno),max(sage) from student group by dept,ssex order by CONVERT(dept using gbk) desc;

查询选课门数超过3门的学生的学号和选课门数

-- 查询选课门数超过3门的学生的学号和选课门数

select sno,count(cno) from sc group by sno HAVING count(cno)>3;

查询选课门数大于等于4门的学生的平均成绩和选课门数

-- 查询选课门数大于等于4门的学生的平均成绩和选课门数

select sno,AVG(GRADE),count(cno) from sc group by sno HAVING count(cno)>=4;

查询计算机系和信息管理系每个系的学生人数

-- 查询计算机系和信息管理系每个系的学生人数

select dept,count(sno) from student where dept in('计算机系','信息管理系') group by dept;

给不同分数段的同学评价

-- 查询“C001”课程的考试情况，列出学号和成绩，对成绩进行如下处理：
-- 如果成绩大于等于90，则在查询结果中显示“优”；
-- 如果成绩在80到89分之间，则在查询结果中显示“良”；
-- 如果成绩在70到79分之间，则在查询结果中显示“中”；
-- 如果成绩在60到69分之间，则在查询结果中显示“及格”；
-- 如果成绩小于60分，则在查询结果中显示“不及格”。
-- CASE WHEN THEN
select sno,
CASE  
when grade>=90 then '优' 
when grade >=80 and grade<89 then '良'
when grade >=70 and grade<79 then '中'
when grade >=60 and grade<69 then '及格'
else '不及格'
end as '等级'
 from sc where cno='C001';

统计计算机系每个学生的选课门数，包括没有选课的学生。
列出学号、选课门数和选课情况，其中对选课情况的处理为：
如果选课门数超过4，则选课情况为“多”；
如果选课门数在2~4，则选课情况为“一般”；
如果选课门数少于2，则选课情况为“少”；
如果学生没有选课，则选课情况为“未选”。
并将查询结果按选课门数降序排序