MySQL基本查询案例练习
目录
一.案例1
需求
解决代码
二.案例2
需求
解决代码
一.案例1
创建一个学生表，插入以下数据
insert into student values(1,'张明','男',89,78,90),
(2,'李静','男',77,73,60),
(3,'王五','女',45,90,90),
(4,'李毅','女',66,78,80),
(5,'李彩','男',69,88,98),
(6,'张宝','男',79,78,88),
(7,'刘芳','女',89,90,91),
(7,'刘芳','女',89,90,91);
需求
-- 查询表中所有学生的信息。
-- 查询表中所有学生的姓名和对应的英语成绩。
-- 过滤表中重复数据。
-- 统计每个学生的总分。
-- 在所有学生总分数上加10分特长分。
-- 使用别名表示学生分数。
-- 查询英语成绩大于等于90分的同学
-- 查询总分大于250分的所有同学-- 在所有学生总分数上加10分特长分。
-- 查询英语分数在80—90之间的同学。
-- 查询英语分数不在80-90之间的同学。-- 查询数学分数为89,90,91的同学。
-- 查询数学分数不为89,90,91的同学。-- 查询所有姓李的学生英语成绩。
-- 查询数学分80并且语文分80的同学。
-- 查询英语80或者总分200的同学
-- 对数学成绩降序排序后输出。
-- 对总分排序后输出，然后再按从高到低的顺序输出
-- 对姓李的学生总分成绩排序输出-- 查询男生和女生分别有多少人，并将人数降序排序输出
-- 查询男生和女生分别有多少人，并将人数降序排序输出 ,查询出人数大于3的性别人数信息
解决代码
use world;create table student(id int,name varchar(20),gender varchar(20),chinese int,english int,math int
);insert into student values(1,'张明','男',89,78,90),(2,'李静','男',77,73,60),(3,'王五','女',45,90,90),(4,'李毅','女',66,78,80),(5,'李彩','男',69,88,98),(6,'张宝','男',79,78,88),(7,'刘芳','女',89,90,91),(7,'刘芳','女',89,90,91);-- 查询表中所有学生的信息。
select * from student ;
-- 查询表中所有学生的姓名和对应的英语成绩。
select name,english from student;
-- 过滤表中重复数据。
select distinct * from student ;
-- 统计每个学生的总分。
select name,(chinese+english+math) as total_score from student;
-- 在所有学生总分数上加10分特长分。
select name,(chinese+english+math)+10 as new_total_score from student;
-- 使用别名表示学生分数。
select name,chinese '语文',english '英语',math '数学' from student;
-- 查询英语成绩大于等于90分的同学
select * from student where english >=90;
-- 查询总分大于250分的所有同学
select * ,(chinese+english+math) as total_score from student where(chinese+english+math) >250;-- 在所有学生总分数上加10分特长分。
select * ,(chinese+english+math)+10 as total_score from student;-- 查询英语分数在80—90之间的同学。select * from student where english between 80 and 90 ;select * from student where english >=80 and english <=90;-- 查询英语分数不在80-90之间的同学。select * from student where english not between 80 and 90 ;select * from student where not english between 80 and 90 ;select * from student where not (english between 80 and 90) ;select * from student where english <80 or english >90;-- 查询数学分数为89,90,91的同学。
select * from student where math in (89,90,91);
select * from student where math=89 or math =90 or math =91;-- 查询数学分数不为89,90,91的同学。
select * from student where math not in (89,90,91);
select * from student where math!=89 && math !=90 && math !=91;-- 查询所有姓李的学生英语成绩。
select name,english from student where name like '李%';
-- 查询数学分80并且语文分80的同学。
select * from student where math =80 and chinese =80;
-- 查询英语80或者总分200的同学
select * from student where english =80 and (chinese+english+math)=200;
-- 对数学成绩降序排序后输出。
select * from student order by math desc ;
-- 对总分排序后输出，然后再按从高到低的顺序输出
select *,(chinese+english+math) as total_score from student order by (chinese+english+math) desc ;
-- 对姓李的学生总分成绩排序输出
select *,(chinese+english+math) as li_total_score from student where name like '李%' order by (chinese+english+math) desc;-- 查询男生和女生分别有多少人，并将人数降序排序输出
select gender,count(*) as gender_count from student group by gender order by gender_count;-- 查询男生和女生分别有多少人，并将人数降序排序输出 ,查询出人数大于3的性别人数信息
select gender,count(*) as gender_count from student group by gender having gender_count>3 order by gender_count;
二.案例2
新建一个员工表，插入以下数据
insert into emp values (7369,'SMITH','CLERK',7902, 19801214,800,null,20),
(7499,'ALLEN','SALESMAN',7902, 19811216,1000,300,30),
(7521,'WARD','SAKLESMAN',7902, 19820217,1200,500,30),
(7566,'JONES','MANAGER',7902, 19870411,850,null,20),
(7580,'MARTIN','SALESMAN',7902, 19901010,1100,1400,30),
(7634,'BLAKE','MANAGER',7902, 19831214,1800,null,30),
(7655,'CLARK','MANAGER',7902, 19850215,2800,null,10),
(7789,'KING','PRESIDENT',7902, 19870422,3800,null,20),
(7799,'TURNER','SALESMAN',7902, 19900703,4800,0,10),
(7892,'ADAMS','CLERK',7902, 19771122,4400,null,30),
(7902,'JAMES','CLERK',7902, 19940210,1200,null,20),
(7923,'FORD','MILLER',7902, 19870911,800,null,30),
(7369,'MILLER','CLERK',7902, 19801217,600,null,20),
(7369,'SCOTT','ANALYST',7902, 19801217,800,null,10);
需求
1.按员工编号升序排列不在10号部门工作的员工
2.查询姓名第二个字母不是A且薪水大于1000的员工信息，按年薪降序排列
年薪=月薪*12+奖金
ifnull（comm，0）如果comm的值是null，则当做0，不为null则还是原来的值
3.求每个部门的平均薪水
4.求各个部门的最高薪水
5.求每个部门每个岗位的最高薪水
6.求平均薪水大于2000的部门编号
7.将部门平均薪水大于1500的部门编号列出来，按部门平均薪水降序排列
8.选择公司中有奖金的员工姓名，工资
9.查询员工最高工资和最低工资的差距
解决代码
create table emp(empno int, -- 员工编号ename varchar(20),-- 员工姓名job varchar(20),-- 工作名字 mgr int, -- 上级领导编号hiredate date, -- 入职日期sal int, -- 薪资comm int, -- 奖金deptno int -- 部门编号
);insert into emp values (7369,'SMITH','CLERK',7902, 19801214,800,null,20),(7499,'ALLEN','SALESMAN',7902, 19811216,1000,300,30),(7521,'WARD','SAKLESMAN',7902, 19820217,1200,500,30),(7566,'JONES','MANAGER',7902, 19870411,850,null,20),(7580,'MARTIN','SALESMAN',7902, 19901010,1100,1400,30),(7634,'BLAKE','MANAGER',7902, 19831214,1800,null,30),(7655,'CLARK','MANAGER',7902, 19850215,2800,null,10),(7789,'KING','PRESIDENT',7902, 19870422,3800,null,20),(7799,'TURNER','SALESMAN',7902, 19900703,4800,0,10),(7892,'ADAMS','CLERK',7902, 19771122,4400,null,30),(7902,'JAMES','CLERK',7902, 19940210,1200,null,20),(7923,'FORD','MILLER',7902, 19870911,800,null,30),(7369,'MILLER','CLERK',7902, 19801217,600,null,20),(7369,'SCOTT','ANALYST',7902, 19801217,800,null,10);-- 1.按员工编号升序排列不在10号部门工作的员工
select * from emp where deptno !=10 order by empno asc ; -- 2.查询姓名第二个字母不是A且薪水大于1000的员工信息，按年薪降序排列-- 年薪=月薪*12+奖金-- ifnull（comm，0）如果comm的值是null，则当做0，不为null则还是原来的值select * from emp where not( ename like 'A%') and sal >1000 order by (sal*12+ifnull(comm,0));
-- 3.求每个部门的平均薪水
select deptno,avg(sal) from emp group by deptno ;
select deptno,avg(sal) from emp group by deptno order by avg(sal) desc; -- 4.求各个部门的最高薪水
select deptno ,max(sal) from emp group by deptno ;-- 5.求每个部门每个岗位的最高薪水
select deptno,job,max(sal) from emp group by deptno,job order by deptno ;-- 6.求平均薪水大于2000的部门编号
select deptno,avg(sal) from emp group by deptno having avg(sal)>2000 ;
-- 7.将部门平均薪水大于1500的部门编号列出来，按部门平均薪水降序排列
select deptno ,avg(sal) from emp group by deptno having avg(sal)>1500 order by avg(sal) desc ;
-- 8.选择公司中有奖金的员工姓名，工资
select * from emp where comm is not null and comm !=0;
-- 9.查询员工最高工资和最低工资的差距
select max(sal)-min(sal) '工资差' from emp 