简单MySQL语句练习(一)

扈立岩

2021.03.30

112人浏览

利用基础MySQL语句,做的24条简单练习

# 1.查询数据总条数 

SELECT * FROM hly0329.employee2;  


#2.年龄排序

select * from employee2 order by AGE;   


#3.年龄排序 倒叙

select * from employee2 order by AGE desc;   


# 4.子查询 查最大年龄的人的人名

select NAME from employee2 where AGE = (select max(age) from employee2 NAME); 


# 5.按照年龄分组 显示年龄和分组人数

select AGE, count(ID) from employee2 group by AGE;

   

#6.查杨小小的所学课程 显示课程和人名

select COURSE , NAME from employee2 where NAME = '杨小小';


#7.年龄最小的人的课程和名字

select COURSE, NAME from employee2 where AGE = (select min(AGE)from employee2);



#8.年龄最大的人的课程和名字

select COURSE, NAME from employee2 where AGE = (select max(AGE)from employee2);


#9.全查询

select * from employee2;


#10.依据ID查询一个人的所有信息

select * from employee2 where id = 15;


#11.插入 张三,男,22,Java 这条数据

INSERT INTO employee2 (`NAME`,`SEX`,`AGE`,`COURSE`) VALUES ('张三','男','22','java');


#12.查出李月的学号

select ID from employee2 where NAME = '李月';



#13.把李月的课程改为Java 年龄改为21岁

update employee2 set COURSE = 'java' where id = 2;



#14.删除学号为2的学生

delete from employee2 where id = 2;


#15.删除18岁以下的学生

set sql_safe_updates = 0;

delete from employee2 where AGE < 18;


#16.查学号最大的同学的名字

select max(ID) from employee2;



#17.查web班学生年龄的最大的学生所有信息

select * from employee2 where COURSE ='web前端' and AGE =(select max(AGE) from employee2);


#18.按照学号排序 从大到小

select * from employee order by salary;



#19.查20岁以上的人的所有信息

select * from employee2 where age >= 20;



#20.20以上的学生的人数

select count(ID) from employee2 where age >= 20;


#21.Java班20岁以下的学生人数

select count(age) from employee2 where COURSE = 'java' and age < 20;


#22.Java班的所有女生的信息

select * from employee2 where COURSE = 'java' and sex = '女';


#23.Java班所有女生的人数 以及所有男生的人数

select count(id) from employee2 where COURSE = 'java' order by sex;


#24.所有学生性别改成女

update employee2 set SEX = '女';


老师评分: 4.8分

老师评语: KEEP!

扈立岩    247 天津市西青区 设计师杨冰是女孩 1999.11.28
京ICP备17020986号-1
筑坐极2021版