数据库练习1
韩佳*
MySQL, 数据库,
项目描述
利用MySQL基础语句完成相关练习.
上传时间
2021.03.30
浏览人数
982人
韩佳*
天津市南开区
Hot:20091
# 1.查询数据总条数
select count(id) from students;
# 2.年龄排序
select * from students order by age;
# 3.年龄排序 倒序
select * from students order by age desc;
# 4.子查询 查最大年龄的人的人名
select name from students where age = (select max(age) from students);
# 5.按照年龄分组 显示年龄和分组人数
select age, count(id) from students group by age;
# 6. 查杨小小的所学课程 显示课程和人名
select course, name from students where name = '江雪芳' ;
# 7.年龄最小的人的课程和名字
select name, course from students where age = (select min(age) from students);
# 8.年龄最大的人的课程和名字
select name, course from students where age = (select max(age) from students);
# 9.全查询
select * from students;
# 10.依据ID查询一个人的所有信息
select * from students where id = 22;
# 11.插入 张三，男，22，Java 这条数据
insert into students (name,sex,age,course) value ('张三','男',22,'Java');
# 12.查出李月的学号
select id from students where name = '李月';
# 13.把李月的课程改为Java 年龄改为21岁
update students set course = 'JAVA', age = 21 where name = '李月';
set sql_safe_updates = 0;
# 14.删除学号为2的学生
delete from students where id = 2;
# 15.删除18岁以下的学生
delete from students where age <18;
# 16.查学号最大的同学的名字
select name from students where age = (select max(id) from students);
# 17.查web班学生年龄的最大的学生所有信息
select * from students where course = 'WEB班' and age = (select max(age) from students);
# 18.按照学号排序 从大到小
select * from students order by id desc;
# 19.查20岁以上的人的所有信息
select * from students where age >= 20;
# 20.20以上的学生的人数
select count(age) from students where age >= 20;
# 21.Java班20岁以下的学生人数
select count(age) from students where course = 'JAVA' and age < 20;
# 22.Java班的所有女生的信息
select * from students where course = 'JAVA' and sex = '女';
# 23.Java班所有女生的人数 以及所有男生的人数
select count(id) from students where course = 'JAVA' order by sex;
# 24.所有学生性别改成女
update students set sex = '女'
韩佳*
