MySQL
王**
MySQL
项目描述
关于MySQL基本语法的一些练习
上传时间
2021.03.30
浏览人数
963人
王**
河北省衡水市阜城县
Hot:15829
# 1.查询学生选课表中的全部数据
select * from student_info;
# 2.查询计算机系学生的姓名、年龄
select name,age from student_info where faculty = '计算机';
# 3.查询成绩在70﹏80分之间的学生的学号、课程号和成绩
select id,classnum,grade from student_info where grade between 70 and 80;
# 4.查询计算机系年龄在18﹏20之间且性别为“男”的学生的姓名和年龄
select name,age from student_info where sex = '男' and age between 18 and 20 and faculty = '计算机';
# 5.查询课程号为“c01”的课程的最高分数
select max(grade) from student_info where classnum = 1;
# 6.查询计算机系学生的最大年龄和最小年龄
select max(age),min(age) from student_info where faculty = '计算机';
# 7.统计每个系的学生人数
select faculty,count(id) from student_info group by faculty;
# 8.统计每门课程的选课人数和考试最高分
select class,count(id),max(grade) from student_info group by class;
# 9.将计算机系的成绩按降序显示结果
select * from student_info where faculty = '计算机' order by grade desc;
# 10.查询选修了课程“c02”的学生的姓名和所在系
select name,faculty from student_info where classnum = 2;
# 11.查询成绩在80分以上的学生的姓名、课程号和成绩，并按成绩的降序排列结果
select name,classnum,grade from student_info where grade > 80 order by grade desc;
# 12.删除成绩小于50分的选课记录
delete from student_info where grade < 50;
set sql_safe_updates = 0;
# 13.将所有选修了课程“C01”的学生的成绩加10分
update student_info set grade = grade + 10 where classnum = 1;
# 14.将计算机系所有选修了课程“计算机文化基础”课程的学生的成绩加10分
update student_info set grade = grade + 10 where class = '计算机文化基础' and faculty = '计算机';
select name,grade from student_info where class = '计算机文化基础' and faculty = '计算机';
# 15.查询数据总条数
select count(id) from students;
# 16.年龄排序
select * from students order by age;
# 17.年龄排序 倒序
select * from students order by age desc;
# 18.子查询 查最大年龄的人的人名
select name from students where age = (select max(age) from students);
# 19.按照年龄分组 显示年龄和分组人数
select age, count(id) from students group by age;
# 20.查江雪芳的所学课程 显示课程和人名
select course, name from students where name = '江雪芳' ;
# 21.年龄最小的人的课程和名字
select name, course from students where age = (select min(age) from students);
# 22.年龄最大的人的课程和名字
select name, course from students where age = (select max(age) from students);
# 23.全查询
select * from students;
#24. 依据ID查询一个人的所有信息
select * from students where id = 22;
# 25.插入 张三，男，22，Java 这条数据
insert into students (name,sex,age,course) value ('张三','男',22,'Java');
# 26.查出李月的学号
select id from students where name = '李月';
# 27.把李月的课程改为Java 年龄改为21岁
update students set course = 'JAVA', age = 21 where name = '李月';
set sql_safe_updates = 0;
# 28.删除学号为2的学生
delete from students where id = 2;
# 29.删除18岁以下的学生
delete from students where age <18;
# 30.查学号最大的同学的名字
select name from students where age = (select max(id) from students);
# 31.查web班学生年龄的最大的学生所有信息
select * from students where course = 'WEB班' and age = (select max(age) from students where course = 'WEB班');
# 32.按照学号排序 从大到小
select * from students order by id desc;
# 33.查20岁以上的人的所有信息
select * from students where age >= 20;
# 34.20以上的学生的人数
select count(age) from students where age >= 20;
# 35.Java班20岁以下的学生人数
select count(age) from students where course = 'JAVA' and age < 20;
# 36.Java班的所有女生的信息
select * from students where course = 'JAVA' and sex = '男';
# 37.Java班所有女生的人数 以及所有男生的人数
select sex,count(id) from students where course = 'JAVA' group by sex;
# 38.所有学生性别改成女
update students set sex = '女'
王**
