数据库之子查询，用户管理

回顾

多表查询

　　当我们的一条记录分散不同的表中时，就需要进行多表查询

　　例如　　一对一　　一对多　　多对多

 

　　1.笛卡尔积查询，意思是将两个表中的所有数据全部关联在一起

　　　　例如：a表，有两条，b表，有三条，一共6条

　　　　　　　缺点：会产生大量的错误数据，需要用添加来过滤

　　　　select *from 表1,表2,... where 过滤条件

　　

　　连接查询

内连接查询 inner join
    select *from 表1 join 表2 on 关系过滤条件
    两边的数据必须完全匹配成功才显示
    select *from emp join dept on dept.id = emp.dept_id;

外连接查询  不常用（不应该出现这种没有正确关联的数据）
左外连接    left join
    左边表的数据无论是否匹配成功，都要全部显示
    select * from emp left join dept on dept.id = emp.dept_id;
右外连接    right join
    右边表的数据无论是否匹配成功，都要全部显示
    select *from emp right join dept on dept.id = emp.dept_id;

on 专门用来筛选出正确的匹配关系，只能与join一起使用
但是在join中可以把on 换成where
反过来，在普通查询中不可以使用on
通常在连接查询join中推荐使用on

连接查询解决问题的思路
1.先联合查询 select *from emp join dept
2.on来筛选正确关系 on dept.id = emp.dept_id
3.where 来进行额外的条件过滤，where dept.id = 1
示例:select *from emp join dept on dept.id = emp.dept_id where dept.id = 1

多对多关系的案例:
     Trish老师教过哪些人?
示例:
select tea.name,stu.name from tea join tsr join stu 
on tea.id = tsr.t_id and stu.id = tsr.s_id
where tea.name = "Trish";

 

子查询

　　子查询指的是当一个查询语句被作为另一个查询语句的条件时，该查询语句就称之为子查询（内层查询）

　　可以将一个大的问题拆分成几个小问题，然后一步一步来查询

 

　　需求：财务部有哪些人

连接查询
select *from emp join dept
on emp.dept_id = dept.id
where dept.name = "财务";

 

　　子查询语法: 将子查询（内层查询）用括号包裹即可

　　子查询的实现思路　

1.通过部门名称拿到部门的id
　　select id from dept where name = "财务";

2.通过部门id去员工那查询对应的员工
　　select *from emp where dept_id = (select id from dept where name = "财务");

 

 

　　查询平均年龄大于25的部门名称　

1.先查询出一堆 平均年龄大于25的部门id
2.再通过id，查询部门的名称
　　　　select name from dept where id in
　　　  (select dept_id from emp group by dept_id having avg(age) > 25);

 

 

　　查询平均年奶大于25的部门名称

　　使用连接查询完成　

1.先连接在一起
2.on 筛选正确匹配关系
3.where 条件
　　　　
select dept.name from emp join dept
on emp.dept_id = dept.id
group by dept.name
having avg(age) > 25;

 

 

　　查询每个部门工资最高的员工信息　

1.查询出每个部门的最高工资是多少
　　select dept_id,max(salary),dept.name from emp group by dept_id;

2.拿着最高工资查询员工信息
　　select *from emp join
　　(select dept_id,max(salary) as maxs from emp group by dept_id) as t1
　　on emp.dept_id = t1.dept_id
　　where emp.salary = t1.maxs;

 
查询语句无论多长，其实都是一步一步做出来的，可以先写一段测试一下有无问题，没有再接着写
　　select t1.day_id,success,fail from
　　(select day_id,count(*) as success from test group by day_id,result having result = "success") as t1
　　join
　　(select day_id,count(*) as fail from test group by day_id,result having result = "fail") as t2
　　on t1.day_id = t2.day_id

 

 

用户管理

　　mysql用户指的是客户端连接服务器时使用的账户

　　在一些公司中，很多项目的数据，可能会放在同一个服务器，

　　那就必须要为每一个用户明确其所拥有的权限

　　通常到公司之后，都会给你一个账号名称和密码，并且为你制定一个你可以访问那些数据库或表

 

　　对用户账户增删改查，以及权限的增删改查

　　

　　mysql与权限相关的表：

　　　　user columns_priv　　tables_priv db

　　　　select *from user \G;　　# 当字段太多，一行显示不了可以\G

 

　　create user 用户名@主机名称　　identified by "123";

　　该语句只是单纯创建一个用户，后续还要分配权限，稍微麻烦

 

　　推荐使用grant语句可以在创建账户的同时分配权限

　　grant all on *.* to 用户名@主机名 identified by "密码";

　　grant all on *.* to jack@localhost identified by "123";

　　授予所有库的所有表的所有权限给jack这个账户，密码为123，只允许在127.0.0.1上登录

　　主机地址可以是%，意味着这个用户可以在任何主机上登录服务器，需要加上双引号

　　这个用户不能给其他用户授权，默认只有root可以为其他账户授权

 

　　grant all on day45.* to virgil@localhost identified by "123"; # day45的所有表

　　grant all on day45.dept to Trish@localhost identified by "123"; # day45的dept表

 

　　mysql的权限可以精确到列（某个字段的某种权限）

　　但是不能精确到某一行（需要使用视图）

 

　　可以将权限授予其他账户

　　grant all on day45.dept to Nero@localhost identified by "123" with grant option;

 

　　收回权限

　　REVOKE all privileges [column] on db.table from user@"host";

　　REVOKE all privileges on day45.dept from Nero@"localhost";

 

　　删除用户

　　drop user username@host;

 

　　刷新权限

　　flush privileges

 

　　正常开发时，我们的数据库服务器也会运行在云服务器上，经常需要从本地远程操作数据库

　　但是默认情况是root 只能在服务器本机上登录

　　所以我们可以用grant语句来授权所有主机

　　grant all on *.* to root@192.168.14.10 identified by "123321" with grant option;