上一节内容学习了关于数据表的基本操作,也就是针对单表的增删改查以及创建和删除,而在实际开发中,往往是多表联合操作,尤其是插入和查询用的最多,而这两步都要经过一个“筛选”的过程,这个过程要根据具体业务逻辑,综合不同的表,查询后决定是否满足插入或其他条件。
本节内容涉及的广泛一些,我们需要创建多个表,进行复杂一点的操作,数据库管理工具这里使用的是 Navicat 12
,还有很多类似的软件,比如:sqlyog
、SQL-Front
等等。
官网:https://www.navicat.com.cn/download/navicat-premium
解决 Navicat12 链接MySQL的错误:2059 - authentication plugin 'caching_sha2_password'
解决方案:https://jingyan.baidu.com/article/0aa22375e7966ac8cc0d64b3.html
这里特别注意最后一句命令:
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';
BY后面的字符串就是你的 mysql 密码,如果你没有正确输入,那么测试链接数据库的时候会提示这个错误:1045 Access denied for user 'root'@'localhost' (using password: YES)
其实就相当于修改了 root 账户的密码。
字段分别是:部门编号、部门名称、部门所在地址位置。
字段分别是:员工编号、员工名称、职位、上级、入职日期、薪资、补贴、所属部门编号、所使用的语言。
字段分别是:等级、最低工资、最高工资。
sql
文件在公众号 推荐学java
回复navicat
获取。与之对应,还有多行处理函数,前者是说有一条输入,对应一条输出结果;后者则是多行输入,对应一条输出结果,比如:求和函数sum()
。
+
.
// 将列 LANGUAGE 首字母转大写 select concat(upper(substr(language,1,1)),substr(language,2,length(language)-1)) as '语言' from emp;ifnull(值1, 值2)
:空处理函数,可以将 null 转换成一个具体值。 如果值1为null,那么会取值2参与运算。
select (sal + IFNULL(comm,0)) *12 as '年薪' from emp;format(列名,'格式')
:数字格式化,例如显式千分位:
select e.ename,format(e.sal,'$999,999') as sal from emp e;上一节内容涉及的都是但张表操作和基本的sql关键词的使用,下面的内容涉及多张表之间的查询操作,以及相关SQL专业知识。
当多张表关联查询时,如果不加任何约束条件,那么查询结果是多张表记录的乘机。
需求:
查询每个员工所在的部门名称,要求显示员工姓名、部门名称。
分析:
员工表中只有部门编号,部门名称是在部门表中,所以就需要两张表关联查询。
SQL语句:
select e.ename, d.dname from emp e join dept d on e.deptno = d.deptno; // on 后的条件是等值的,叫做等值连接
需求:
找出每个员工的薪资等级,要求显示员工姓名、薪资、薪资等级。
分析:
薪资等级是一张单独的表,员工姓名和薪资在员工表中,同样需要两张表联合查询。
SQL语句:
// on 后的条件不是等值的,叫做非等值连接
select e.ename, e.sal, s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;
需求:
找出每个员工的领导的姓名,显示员工姓名、领导姓名。
分析:
员工表中有员工姓名和员工的上级领导编号,领导同样也是员工,也存在员工表中,所以需要将一张表(员工表)看做两张表来查询,故称作这种情况为自连接查询。
SQL语句:
select e.ename as '员工姓名',em.ename as '领导姓名' from emp e join emp em on e.mgr = em.empno;
需求:
找出每个员工所在的部门,显示员工姓名、部门名称。
分析:
员工在员工表中,存在部门编号,部门名称是在部门表中。
SQL语句:
select e.ename, d.dname from emp e right join dept d on e.deptno = d.deptno;
带有
right
的是右外连接,又称右链接,此时查询会以right
右边的表作为主表来查询,任何一个右链接查询都可以写出对应的左连接查询,反之亦然。
对应左连接查询SQL:
select e.ename, d.dname from dept d left join emp e on e.deptno = d.deptno;
总结:
语法格式:
select a.xxx,b.xxx,c.xxx from a表 a join b表 b on a和b的链接条件 join c表 c on a和c的链接条件
案例1:三张表关联查询
找出每个员工的部门名称以及工资等级,显示员工姓名、薪资、部门名称、薪资等级。
SQL语句:
select e.ename,e.sal,d.dname,s.grade from emp e join dept d on e.deptno = d.deptno join salgrade s on e.sal between s.losal and s.hisal;
案例2: 三张表链接+左连接查询
找出每个员工的部门名称以及工资等级,显示员工姓名、薪资、领导名、部门名称、薪资等级。
SQL语句:
select e.ename, em.ename as '上级领导', e.sal, d.dname, s.grade from emp e join dept d on e.deptno = d.deptno join salgrade s on e.sal between s.losal and hisal left join emp em on e.mgr = em.empno;
select语句中嵌套select语句,那么被嵌套的select语句称为子查询。
select
...(select)
from
...(select)
where
...(select)
案例:
找出比最低工资(员工的工资,不是工资登记表中的最低工资)高的员工姓名和工资。
SQL语句:
select e.ename,e.sal from emp e where e.sal > (select min(sal) from emp);
from子句中的子查询结果可以当做一张临时表来使用,因为from后面本身就是表名
案例:
找出每个工作岗位的平均薪资的工资等级。
SQL语句:
select s.job,s.avg,sal.grade from (select job, avg(sal) avg from emp group by job) s join salgrade sal on s.avg between sal.losal and sal.hisal ;
案例:
找出每个员工的部门名称,显示员工姓名、部门名称。
SQL语句:
select e.ename, (select d.dname from dept d where d.deptno = e.deptno) d from emp e;
将查询结果集进行合并,比如:两条select语句的结果拼接在一起。
案例:
将
job
为四绝之一
和Java全栈工程师
的员工查出来,显示员工名、职位名。
SQL语法:
select e.ename, e.job from emp e where e.job = '四绝之一'
union
select e.ename,e.job from emp e where e.job ='Java全栈工程师';
就这个需求而言,SQL查询可以用之前学的 or
或者 in(值1,值2)
都可以实现结果,但在多表链接查询时 union
效率更高。
union注意事项:
将查询结果集的一部分取出来,通常多用于分页查询中。
用法示例:
-- 将所有职位查询来降序排列,只显示前5条
select * from emp order by job desc limit 5;
-- 将查询结果 从第四条开始,向后取5条数据出来
select e.ename, e.sal from emp e order by sal desc limit 3,5;
limit注意事项:
limit
在 order by
之后执行。通用分页公式:
limit (pageNo-1)*pageSize, pageSize;
这个名词是MySQL特有的,Oracle中也有类似技术,但不叫这个名字。存储引擎就是一张表存储/组织数据的方式。
InnoDB
utf8
show engines \G
这道理,查询相关高级知识已经完结了,下面内容是偏理论性的,但也很重要,属于高级操作,请结合知识导图查看。
一个事务就是一个完整的业务逻辑。
只有增、删、改才会有事务一说。
这和存储引擎有关系,MySQL使用 InnoDB
,而 InnoDB
对没次操作的DML语句都会有日志记录文件,事务的执行结果要么多条语句全部失败,要么全部成功,无论是什么结果都会清空日志文件,所以事务支持回滚。
select @@tx_isolation
索引是在数据库表的字段上添加的,是为了提高查询效率提供的一种机制。一个字段可以添加一个索引,当然也可以多个字段联合起来添加索引。
unique
约束的话,也会自动添加索引tablespace
中,是一种二叉树(B-Tree)结构。where
后面,一条件的形式存在,也就是经常被扫描语法:
create index 索引名 on 表名(字段名);
语法:
drop index 索引名 on 表名;
语法:
explain sql查询语句;
说明:
通过查看结果中的 type
和 rows
值来做判断,前者如果是 ref
则说明是通过索引来扫描的。
%
开头了or
的时候,要能使用索引,那么要求 or
两边的字段都要有索引,如果只要一边有索引,那么索引会失效where
当中,索引列参加了运算,索引失效where
当中,索引列使用了函数,索引失效不同角度看待同一份数据。
只有
DQL
语句才能以view
的形式创建。
create view 视图名称 as select语句;
drop view 视图名称;
我们可以面向视图对象进行增删改查,这将会导致原表数据被操作。方便、简化开发、利于维护
要求所有表都必须有主键,每一个字段都是原子性不可再分。
在第一范式的基础上,要求每个非主键字段完全依赖主键,不要产生部分依赖。
在第二范式的基础上,要求所有非主键字段完全依赖主键,不要产生传递依赖。
关于多表的操作,最常用的就是查询,且是最重要的内容,本章节内容会影响到我们后面的进阶内容,需要加强练习。这里建议大家在学习的过程中写 SQL
语句在相应的工具中进行,本文开始介绍了工具
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。