6.12作业

1.select dept.dept_name,avg(emp.incoming)as'平均收入' from dept inner join emp on dept.dept1=emp.dept2 group by dept.dept1;#内连接select dept.dept_name,avg(emp.incoming)as'平均收入' from dept left join emp on dept.dept1=emp.dept2 group by dept.dept1;#左连接
select dept.dept_name,avg(emp.incoming)as'平均收入' from dept right join emp on dept.dept1=emp.dept2 group by dept.dept1;#右连接
select dept.dept_name,avg(emp.incoming)as'平均收入' from dept,emp where dept.dept1=emp.dept2 group by dept.dept1;#基本连接
select t.dept_name,avg(t.incoming)as'平均收入' from (select*from dept,emp where dept.dept1=emp.dept2)t group by t.dept1;#临时表

2.select sum(t.incoming) from (select*from dept,emp where dept.dept1=emp.dept2)t where t.dept_name='财务'; #临时表
select sum(incoming) from dept,emp where dept.dept1=emp.dept2 and dept_name='财务';#基本连接
select sum(incoming) from emp where dept2 in (select dept1 from dept where dept_name='财务');#嵌套

3.select emp.name,emp.sid from dept inner join emp on dept1=dept2 where dept_name='IT技术';

4.select name from emp where dept2 in (select dept1 from dept where dept_name='财务') and incoming>2000;

5.select worktime_start from dept join emp on dept1=dept2 where incoming=(select min(emp.incoming) from dept inner join emp where dept.dept1=emp.dept2 and dept.dept_name='销售')and dept_name='销售';

6.select name,sid,dept_name from dept,emp where dept1=dept2 and age<(select avg(age) from emp);

7.select dn from (select d.dept_name dn,sum(e.incoming) sum from dept d join emp e on d.dept1=e.dept2 group by dept_name)g where sum >9000;

8.select emp.name from dept join emp on dept.dept1=emp.dept2 where dept.dept_name='财务' and emp.incoming<3800;

9.（1）select name from dept,emp where dept1=dept2 and  incoming=(select min(incoming) from dept join emp on dept1=dept2 where dept_name='财务') and dept_name ='财务';
（2）select name from emp where incoming in (select min(incoming) from (select*from emp where dept2 in (select dept1 from dept where dept_name = '财务'))t)and dept2 in (select dept1 from dept where dept_name = '财务');  
10.select name from dept,emp where dept1=dept2 and age in (select max(age) from emp where dept2 in (select dept1 from dept where dept_name='销售')) and dept_name='销售';


11.select name,dept_name from dept,emp where dept1=dept2 and incoming in (select min(incoming) from emp);

12.select incoming,dept_name from dept,emp where dept1=dept2 and name='李四';

13.select dept1,dept_name from dept,emp where dept1=dept2 and incoming <4000 ;

14.select name,dept_name,incoming from dept join (select*from emp join (select dept2 number,max(incoming) max from emp group by dept2)t where dept2=number and incoming=max group by incoming desc)g where dept1=dept2;

15.select name,sid,incoming from dept,emp where dept1=dept2 and dept_name='财务' order by incoming desc limit 0,2;

16.select sid,name from dept,emp where dept1=dept2 and dept_name='财务' and incoming <(select avg(incoming) from emp where dept2 in (select dept1 from dept where dept_name='财务'));

17.select dept_name from dept join (select dept2,count(sid) count from emp group by dept2)t where dept1=dept2 and count>1;

18.select age,dept2 from emp where incoming between 3001 and 7500;

19.select dept_name from dept,emp where dept1=dept2 and worktime_start between '19700101' and '19791231';

20.select dept_name from dept,emp where dept1=dept2 and name = '张三';

21.select name,dept_name from dept join (select * from emp join (select dept2 dn,max(age) max from emp group by dept2)t on emp.dept2=dn where age=max)g on dept.dept1=g.dn

22.select sum,dept_name from dept join (select dept2,sum(incoming) sum from emp group by dept2)t on dept.dept1=t.dept2;
23.select sid,dept_name from dept,emp where dept1=dept2 and incoming>7000;

24.select dept_name from dept left join emp on dept1=dept2 where name is null;

25.select*from emp order by dept2 desc,worktime_start asc;

26.select name,sid from dept,emp where dept1=dept2 and dept_name ='财务' and incoming in (select max(incoming) from emp where dept2 in (select dept1 from dept where dept_name='财务'));

27.select name,dept_name from dept,emp where dept1=dept2 and age in (select max(age) maxage from (select*from dept,emp where dept1=dept2 and incoming between 7500 and 8500)t) and incoming between 7500 and 8500;









mysql数据库的权限管理：
在linux系统通过命令:
mysqladmin -uroot password  '123456'


mysql的权限管理涉及到 mysql库，里面包含的表用来定义mysql工具的一些配置、权限


需要修改或增加权限都要进入mysql库：
use mysql；
查看当前mysql工具一些用户情况：
select host,user,password from user;

给mysql增加一个新的用户：
insert into user(host,user,password) values('localhost','dcs30',password('123456'));

每一次执行权限操作之后，需要进行 刷新权限
flush privileges;

赋予 select,insert,delete,update 在所有库里所有表 给到'dcs30'@'localhost'  识别密码 123456
grant select,insert,delete,update on *.* to 'dcs30'@'localhost' identified by '123456';                         ==================================*.*指所有库所有表

每一次执行权限操作之后，需要进行 刷新权限
flush privileges;

查看指定用户的数据库权限：
show grants for 'dcs30'@'localhost';

简化的操作：
use mysql；  ===进入mysql库

grant all privileges on *.* to 'root'@'%' identified by '123456';   ===赋予所有的权限跟到一个具有远程操作权限的root用户  ========================== %指远程权限  

flush privileges;  ===刷新权限


其他的操作：
update user set password=password('12345')  where user='dcs30' and host='localhost'; ===》修改密码

revoke all on *.* from 'dcs30'@'localhost'; ===》取消相关用户的权限操作

delete from user where user='dcs30'; ==>可以直接在user表进行用户的删除，注意不要乱删root用户



使用Navicat连接数据库
Navicat是一个数据库连接工具，一个直接连接服务器里面的数据库管理工具的工具，图像化界面

3306,是MySQL的默认端口号，一般是使用3306~3309

使用Navicat连接数据库需要注意服务器的mysql服务有没有启动，以及防火墙有没有关闭


以下是在Navicat里面的查询写的笔记：
#运行方式有两种：
#1，直接点击运行
#2，选择sql语句，右击‘运行已选择的’

# “#”代表注释，被注释的内容不会被程序识别和执行
# 使用#号可以做单行注释
-- 单行注释也可以使用快捷键 ctrl+/  在所在行的行首增加--
-- 多行注释可以使用/* .....*/
/*
这是多行注释的内容
这是多行注释的内容
这是多行注释的内容
*/
--  只写一条sql语句可以不加分号，但是两条或以上就要加分号‘;’
--  这是一个英文的分号';'   这是一个中文的分号‘；’

-- 使用快捷键 ctrl+/进行注释的行，也可以使用快捷键 ctrl+/ 进行取消注释

/*
在Navicat中新建的查询时临时的，可以保存，是保存在Navicat这个工具里，并不是保存在MySQL
我们在Navicat中的查询写好的SQL语句记得进行保存，下次在进入Navicat时，就可以直接重复使用
*/






----------多表查询-笔记--------

select *from aa;
select *from cc;


1,内连接 inner join  对两张表当中有字段的值是相同的，就可以通过内连接进行连接
select * from aa inner join cc on aa.id=cc.s_id;
select * from aa join cc on aa.id=cc.s_id;   #这是一个内连接的简写

2，左连接  left join  以左边的表为主，显示完整的左边表，右边表只会展示匹配上的数据
select * from aa left join cc on aa.id=cc.s_id

3，右连接  right join 以右边的表为主，显示完整的右边表，左边表只会展示匹配上的数据
select * from aa right join cc on aa.id=cc.s_id


左连接和右连接的区别：
1，左连接展示完整的左边表信息，右连接展示完整的右边表信息
2，左连接的查询查询速度比右连接的查询速度要快

4，基本连接  对于多张表当中字段的值相同就可以通过基本连接的方法进行连接（结果类似于内连接，主要区别在于语法上）
select * from aa,cc,ff where aa.id=cc.s_id and ff.fid=aa.id

create table ff(fid int(10),class int(10));
insert into ff values(1001,2130);
select *from ff;


5，硬连接（机械拼接，做上下拼接的） union 主要是对字段相同的表进行拼接，要求拼接的表字段要一致,拼接出来的结果表字段名以第一个表为主
select * from aa union select sid,name from grade union select * from cc;


多表查询的一些常用方法：
上述的内连接，左连接，右连接，基本连接是常见的一些方法
还有临时表方法、嵌套方法
临时表方法
select *from aa inner join cc on aa.id=cc.s_id  ===这是一张临时表t
求张三的成绩：
select t.score from (select *from aa inner join cc on aa.id=cc.s_id) as t where t.name = 'zhangsan';

求张三的成绩：
嵌套方法： 把求出的结果的sql，给到下一个表的条件去使用
嵌套方法  ‘=’方法：
1，先求通过aa表求出zhangsan对应的id（先通过单表的方法求出一个结果）
select id from aa where name='zhangsan'   ===得出结果 1001
2，把前面步骤求出的结果作为下一个表的条件使用
select score from cc where s_id = (select id from aa where name='zhangsan')

嵌套方法  ‘in’方法：
1，先求通过aa表求出zhangsan对应的id（先通过单表的方法求出一个结果）
select id from aa where name='zhangsan'   ===得出结果 1001
2，把前面步骤求出的结果作为下一个表的条件使用
select score from cc where s_id in (select id from aa where name='zhangsan')

嵌套方法 的 ‘=’方法和‘in’方法的区别：
‘=’方法只能对一个值使用，'in'方法可以对一个值或多个值生效，所以直接用in方法即可


求出谁没有参加考试？
条件：没有参加考试（score为空）  结果：谁--名字name

方法一 基本连接+嵌套：
select name from aa,cc where aa.id=cc.s_id  ==求出参加考试的人
select name from aa where name not in (select name from aa,cc where aa.id=cc.s_id) ==求出不包括参加考试的人

方法二 左连接：
select name from aa left join cc on aa.id=cc.s_id where cc.score is null

方法三 右连接：
select name from cc right join aa on cc.s_id=aa.id where cc.score is null

方法四 临时表：
select *from aa left join cc on aa.id=cc.s_id  ===>临时表t
select name from (select *from aa left join cc on aa.id=cc.s_id) t where t.score is null