武汉3班DAY13和DAY14Mysql 命令补充以及单表和多表练习题

在sql里创建一个用户给与远程链接’%‘的权限 grant all privileges on *.*to'goutuizi'@'%' identified by '123456';
把远程链接权限修改为本地登录权限localhost   update user set host='localhost' where user='goutuizi';
查看当前用户的权限show  grants  for  'ergouzi'@'%';  
取消所有权限revoke all on *.* from 'ergouzi'@'localhost';
删除用户delete from user where user='ergouzi' and host='localhost';
修改root用户的密码update user set password=password('123456') where user='root';
修改完刷新权限再登录新用户

1、查询1833班的成绩信息
select*from grants where class=1833;

2，查询1835班，语文成绩大于80小于90的成绩信息
select *from grants where class=1835 and chinses between 50 and 70;
select * from grants where class=1835 and chinses>50 and chinses<70;

3，查询学生表中3到6行的数据
select * from grants;
select *from grants limit 2,4;
4，显示1833班英语成绩为59，数学成绩为61的姓名与学号,
select name,id from grants where class=1833 and english=98 and math=61;

select name,id from grants where class = 1833 and english = 98 and math =61;



5，查询出1833班成绩并且按语文成绩排序（降序）
select *from grants where class=1833 order by chinses desc;

6，查询1833班与1835班，语文成绩与数学成绩都小于80的姓名。
select name from grants where class in (1833,1835) and chinses<80 and math<80;
select * from grants where class =1833 AND class=1835 and chinses<80 OR math<80;
SELECT * FROM grants WHERE (CLASS = 1833 OR CLASS=1835) and (chinses<80 AND math<80);

7，查询出没有参加语文考试的学生姓名和班级名称。
select name,class from grants where chinses is null;

8，求出班上语文成绩不及格的学生姓名
select name from grants where chinses<60;
9，求出每个班的数学平均成绩
select class ,avg(math) from grants group by class ;


10、求出每个班级语文成绩总分 --涉及到每个的时候都需要分组
select class,sum(chinses) from grants group by class;

11、将语文成绩不及格的学生成绩改为60分
update grants set chinses =60 where chinses<60;

12、三科分数都大于70分的人名和年纪
select name,age from grants where chinses>70 and math>70 and english>70;
13、求出英语分数高于70且其它任何一科目大于60分的人和班级
select class,name from grants where english>70 and (chinses>60 or math>60);
select * from grants
14、统计每个班的人数
select class,count(*)from grants group by class;

15、求每个班数学成绩大于80的人数
select class,count(*) from grants where math>80 group by class;


16、求出每个班英语成绩最高的那个人的姓名和班级名称 --每个班英语成绩 最高
1.select name,class from(select * from grants order by english desc)as a group by class;
2.select max(english),class from grants group by class;
select * from grants;
select max(english) from grants GROUP BY class;
select * from grants where english in (select max(english) from grants GROUP BY class);

#公司场景当中就会把，一些数据存放在多个表中，通过某些字段进行行关联查询
create table aa(id int(20),name varchar(20));
create table cc(s_id int(20),score int(4));
#方法一：基本连接：（只有相同字段的值才可以进行连接显示）
select * from aa;
select * from cc;
select * from aa,cc where aa.id =cc.s_id
#方法二：内连接：（只有相同字段的值才可以进行连接显示，没有相同
#字段的值，字段不会显示）
select * from aa inner join cc  on aa.id = cc.s_id

# 方法三 ：左连接 left join
#（他是以左表为主表，右表没有相同字段的值，那么自动用null填充）
select * from aa left join cc on aa.id = cc.s_id
#方法四：右连接 right join
#（他是以右表为主表，左表没有相同字段的值，那么自动用null填充）
select * from cc right join aa on aa.id = cc.s_id
#面试问题： 左连接与右连接的区别

#方法五： 硬链接 union 进行连接
select * from aa UNION select * from cc;
#(硬链接的区别;那个表在前面就以那个表的字段为准，后面表的数据
#直接追加在前面表字段当中)

#方法六：临时表 #用零时表查询二狗子的成绩
select * from aa,cc where aa.id=cc.s_id  #把这两张表进行拼接，
#当作一个临时表用来查询 ，临时表不加别名会报错，用as可以不用也可以
select * from (select * from aa,cc where aa.id=cc.s_id)as t where name = 'ergouzi';

#方法七：=方法嵌套
select * from aa;
select * from cc; #(= 表示数据相等，in表示在这个集合当中)
select id from aa where name='ergouzi'

select * from cc where s_id =(select id from aa where name='ergouzi')

#嵌套in方法
select * from cc where s_id in(select id from aa where name='ergouzi')

1.列出每个部门的平均收入及部门名称;
select *from dept,emp where dept.deptl=emp.dept2;
select dept_name,avg(incoming)from (select *from dept,emp where dept.deptl=emp.dept2)as a1 group by dept_name;
2.财务部门的收入总和；
select dept_name,sum(incoming)from (select *from dept,emp where dept.deptl=emp.dept2)as a1 where dept_name='财务' group by dept_name;
3.It技术部入职员工的员工号
select dept_name,sid from (select *from dept,emp where dept.deptl=emp.dept2)as a1 where dept_name='It技术' group by dept_name;
4.财务部门收入超过2000元的员工姓名
select name from (select *from dept,emp where dept.deptl=emp.dept2)as a1 where dept_name='财务'and incoming>2000;
5.找出销售部收入最低的员工的入职时间;
1.select worktime_start,name from emp where incoming in (select min(incoming) from emp where dept2=102);
2.select dept_name,worktime_start from (select * from emp,dept where emp.dept2=dept.deptl)a where dept_name='销售' order by incoming asc limit 0,1;
3.select worktime_start,min(incoming) from (select * from emp,dept where emp.dept2=dept.deptl)a where dept_name='销售';
7.列出每个部门收入总和高于9000的部门名称
select dept_name from (select * from emp,dept where emp.dept2=dept.deptl)a group by dept_name having sum(incoming)>9000;
8.查出财务部门工资少于3800元的员工姓名
select name from (select *from dept,emp where dept.deptl=emp.dept2)a where dept_name='财务'and incoming<3800;
9.求财务部门最低工资的员工姓名；
select name from  (select * from emp,dept where emp.dept2=dept.deptl)a where dept_name='财务' order by incoming asc limit 0,1;
select name from emp where incoming in(select min(incoming) from emp where dept2=101);
10.找出销售部门中年纪最大的员工的姓名
select name from (select * from emp,dept where emp.dept2=dept.deptl)a where dept_name='销售' order by age desc limit 0,1;
11.求收入最低的员工姓名及所属部门名称
select name,dept_name from (select * from emp,dept where emp.dept2=dept.deptl)a where incoming order by incoming asc limit 0,1;
13.求员工收入小于4000元的员工部门编号及其部门名称
select deptl,dept_name from (select * from emp,dept where emp.dept2=dept.deptl)a where incoming<4000;

14.列出每个部门中收入最高的员工姓名，部门名称，收入，并按照收入降序；
select *from emp,dept where emp.dept2=dept.deptl order by incoming desc ;
select name,dept_name,incoming from (select *from emp,dept where emp.dept2=dept.deptl order by incoming desc)a group by dept_name;
select *from(select name,dept_name,incoming from (select *from emp,dept where emp.dept2=dept.deptl order by incoming desc)a group by dept_name)b order by incoming desc;
15.求出财务部门收益最高的俩位员工的姓名，工号，收益
select name,dept_name,incoming from(select *from emp,dept where emp.dept2=dept.deptl)t where dept_name='财务'order by incoming desc limit 0,2;
16.查询财务部低于平均收入的员工号与员工姓名：
select avg(incoming) from (select *from emp,dept where emp.dept2=dept.deptl)t where dept_name='财务';
select sid,name from emp where incoming<(select avg(incoming) from (select *from emp,dept where emp.dept2=dept.deptl)t where dept_name='财务');
17.列出部门员工数大于1个的部门名称；
select dept_name,count(name) from (select * from emp,dept where emp.dept2=dept.deptl)t group by dept_name having count(name)>1;
18.列出部门员工收入不超过7500，且大于3000的员工年纪及部门编号；
select age,deptl from (select * from emp,dept where emp.dept2=dept.deptl)t where incoming<=7500 and incoming>3000;
19.求入职于20世纪70年代的员工所属部门名称；
select dept_name from (select * from emp,dept where emp.dept2=dept.deptl)t where worktime_start like '197%';
20.查找张三所在的部门名称；
select dept_name from (select * from emp,dept where emp.dept2=dept.deptl)t where name='张三';
21.列出每一个部门中年纪最大的员工姓名，部门名称；
select *from emp,dept where emp.dept2=dept.deptl order by age desc;
select name,dept_name from(select *from emp,dept where emp.dept2=dept.deptl order by age desc)t group by dept_name;
22.列出每一个部门的员工总收入及部门名称；
select dept_name,sum(incoming) from emp,dept where emp.dept2=dept.deptl group by dept_name;
24.找出哪个部门还没有员工入职；
select dept_name from dept left join emp on dept.deptl=emp.dept2 where name is null;
25.先按部门号大小排序，再依据入职时间由早到晚排序员工信息表 ；
select *from dept left join emp on dept.deptl=emp.dept2 order by deptl asc,worktime_start asc;
26.求出财务部门工资最高员工的姓名和员工号
select * from dept left join emp on dept.deptl=emp.dept2 order by incoming desc ;
select name,sid from(select * from dept left join emp on dept.deptl=emp.dept2 order by incoming desc )a where dept_name='财务'limit 0,1;
27.求出工资在7500到8500之间，年龄最大的员工的姓名和部门名称
select * from dept left join emp on dept.deptl=emp.dept2 order by age desc;
select name,dept_name from (select * from dept left join emp on dept.deptl=emp.dept2 order by age desc)a where incoming between 7500 and 8500;