SQL基础学习（下）

SQL基础学习（下）

7. 多表查询

当多张表存在关联关系时，根据需求的不同可能会同时需要多张表一起使用

7.1 子查询

注意：每一个查询的SQL语句其结果本身就是一张表

将某个SQL的查询结果作为另一个SQL的查询条件

# 查询员工编号为7的员工所在的部门信息
select dno,dname,loc from depart where dno=(select dno from emp where eno=7);

注意：= > <等等后面只能跟一个值（子查询的结果必须是唯一的）

# 查询性别为男的员工对应的部门信息
select dno,dname,loc from depart where dno in(select dno from emp where sex='男');

7.2 多表查询

# 查询员工编号为7的员工信息及其所在的部门信息
select depart.dno,dname,loc,eno,ename,sex,birthday,phone,emp.dno from depart,emp where eno=7 and emp.dno=depart.dno;

表别名（直接空格隔开，仅在本次查询中生效）

# 查询员工编号为7的员工信息及其所在的部门信息
select d.dno,dname,loc,eno,ename,sex,birthday,phone,e.dno from depart d,emp e where eno=7 and e.dno=d.dno;

思考：使用别名的意义

1.为了简化，在开发过程中，为了让表信息更加的清晰，通常表名会很长

2.子查询中可能存在子查询的结果中的字段和其他表的字段名一样，此时就需要区分

7.3 连接查询

a.左连接 left join

以左边的表为主，查询出左边表中的所有数据和右边表中符合条件的数据，如果右表中的数据条数少于左表则以null代替

# 查看部门信息及对应的员工信息
select d.*,e.* from depart d left join emp e on d.dno=e.dno;

b.右连接 right join

以右边表为主，查询出右边表中所有数据和左边表中符合条件的数据，如果左边表中的数据条数少于右边，则以null代替

# 查看部门信息及对应的员工信息
select d.*,e.* from emp e right join depart d on e.dno=d.dno;

c.内连接 inner join

查询出符合条件的数据，不符合条件（on后面的条件）的数据直接过滤掉

# 查看已有员工的部门及员工信息
select d.*,e.* from depart d inner join emp e on d.dno=e.dno;

d.合并查询 union/union all

将多张表中相同字段的结果在同一张表中显示出来

create table t_web(
id int primary key auto_increment,
name varchar(25) not null,
country varchar(50) not null);
insert into t_web values(1,'微博','cn'),(2,'Google','usa'),(3,'stactoverflow','ind');

 create table t_apps(
 id int primary key auto_increment,
 appname varchar(25) not null,
 country varchar(50) not null);
 insert into t_apps values(1,'QQ','cn'),(2,'wechat','cn');

 select country from t_web
 union all
 select country from t_apps;

 select country from t_web
 union
 select country from t_apps;

union和union all的区别

union all将两张表中的数据（相同字段）全部显示出来

union将两张表中的数据（相同字段）去重复后显示出来

8.常用函数

MySQL的底层使用的是SQL语言，该语言本身也类似于Java等开发语言，其中提供了相关的函数、子程序等等方式，以方便开发使用。

数据库的主要作用是用于存储数据的，而非做业务处理的，在数据库中处理业务是需要消耗性能的，因此，不建议在数据库中使用过多的函数，建议在开发语言中处理。

concat(arg0,arg1,…) 拼接

select concat('你','好','呀','!');
# 使用mybatis框架进行模糊查询时的使用方案（假设变量名为name）
select * from emp where ename like concat('%',name,'%');

now() 获取当前的系统时间

unix_timestamp() 获取当前时间的时间戳

date_format() 格式化日期

select date_format(now(),'%Y年%m月%d号 %H:%i:%s');

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-lx4K3CoN-1606905968902)(./asset/日期格式化.png)]

from_unixtime() 格式化时间戳

select from_unixtime(unix_timestamp(),'%Y年%m月%d号 %H:%i:%s');

mysql日期格式

常用函数与聚合函数的区别

聚合函数是直接操作数据的，函数是操作获取的数据的

9. 数据备份

每家公司通常都会有自己的服务器，存储公司相关的一些信息数据，在使用的过程中可能会出现需要进行数据迁移（搬家），为了防止数据丢失，可以进行数据备份

9.1 数据导出

导出数据需要退出MySQL的环境

语法：mysqldump -u root -p 数据库名 [表名] > 导出的地址

# 导出整个数据库（所有表-表结构及数据）
mysqldump -u root -p tongbaishan > e:/tongbaishan.sql
# 导出指定表
mysqldump -u root -p tongbaishan emp > e:/emp.sql
# 只导出表结构
mysqldump -u root -p -d tongbaishan > e:/tongbaishan2.sql

9.2 数据导入

在数据中运行指定的SQL文件

注意：导入数据库前提是要先选择一个数据库

source e:/tongbaishan.sql

10.视图

视图就是一张虚拟的表（和表的使用方式【增删改查】类似）

使用场景

甲方公司简称A公司，乙方公司简称B公司，A公司将项目外包给了B公司，要求B公司派遣人员到A公司的办公地点进行办公（驻场办公），A公司的工作人员都需要有门禁权限，所以A公司需要给B公司的工作人员开权限（需要B公司将对应的开发人员的信息给到A公司），同时如果开发团队人员发生了改变，B公司需要及时将改变的情况和人员信息发给A公司

上述场景中需要考虑的问题

思考：B公司如何将人员的信息给到A公司

思考：给的信息中如何对保密的信息进行屏蔽处理（B公司的人员表中有很多员工，信息中包含公司的敏感信息）

思考：如果人员信息发生了改变，如何能够更方便快捷的将变更后的信息发送给甲方

思考：B公司承接了很多公司的外包项目，B公司就需要对应多家公司，上述的几个问题都需要进行再次考虑

语法：create view 视图名 as 查询语句;

 create view v_a_emp
 as
 select ename,sex,phone from emp;

# 需求 查看部门的薪资总和
select dno,sum(salary) from emp e join salarys s on e.eno=s.eno group by dno;

# 需求 计算部门的薪资总和
create view v_sal 
as
select dno,sum(salary) total from emp e join salarys s on e.eno=s.eno group by dno;

注意点：在创建视图时，如果结果中的字段有使用函数或聚合函数，需要给一个别名作为视图中的字段名

# 尝试去修改视图中的薪资总和数据
update v_sal set total=19500 where dno=10;
#ERROR 1288 (HY000): The target table v_sal of the UPDATE is not updatable
#v_sal表中不支持update操作

当视图中的数据修改后无法推断出原表中的数据应该如何改变时，该操作就不能执行

思考：通过哪些关键词或函数等创建出来的视图中的数据是不能进行修改操作的

聚合函数、分组等等

通常情况下，B公司在给出对应的视图后，需要提供一个账号给甲方公司，该账号是可以设置权限的（通常只给查询的权限）

总结

视图提高了数据的安全性，用户只能看到提供的数据，同时可以设置权限

简化用户操作复杂度，可以将复杂的SQL操作包装成视图，方便下次使用