Mysql进阶

多表查询

        什么是多表查询？ 

举个例子：SELECT * FROM students, classes;

        这行语句就把student表和classes表连接起来一起查询。

        一次查询两个表的数据，查询的结果也是一个二维表，它是students表和classes表的“乘积”，即students表的每一行与classes表的每一行都两两拼在一起返回。结果集的列数是students表和classes表的列数之和，行数是students表和classes表的行数之积。

        这种多表查询又称笛卡尔查询，使用笛卡尔查询时要非常小心，由于结果集是目标表的行数乘积，对两个各自有100行记录的表进行笛卡尔查询将返回1万条记录，对两个各自有1万行记录的表进行笛卡尔查询将返回1亿条记录。

        

        很明显我们大多数情况用不到一次返回半个小目标条记录，那该怎么进行过滤呢？

        使用where关键字。

        SELECT
            s.id sid,
            s.name,
        FROM students s, classes c
        WHERE s.gender = 'M' AND c.id = 1;

        这里的where关键字规定了只返回性别为男，班级id为1的记录，这样返回就会快很多。

        

      而多表查询中又分有好几个知识点：

1. 内连接
2. 外连接
3. 子查询（标量、列、行、表）

下面一个个地进行讲解

内连接

        前面我们的例子中使用的就是内连接，只不过是内连接当中的隐式连接。  显式连接和隐式连接都是一样的作用，大家看自己的习惯进行使用。

        下面要拓展的一点是起别名，有的字段叫category，太长了怎么办？给他起一个别名，在第一次声明他时，在后面起上别名即可。

        

A B的实现其实功能上是一样的。

外连接

        外连接分为左外连接和由外连接。

        左外连接：返回左表的全部内容，包括与右表有关联的部分。

        右外连接：返回右表的全部内容，包括与左表有关联的部分。

        大家可能会问，这个外连接有什么作用呢？

        有这么一个情况，左表有的某一个字段，右表没有对应的字段与其产生联系。而我们又被要求操作数据库返回左表的全部内容以及部分与右表相关的内容。这时候就能用到外连接了。

        其实左右外连接中，如果互换了一下表的位置，并该为另一个外连接，其结果都一样。两个表中，无非是谁在左，谁在右的区别。如果有一个人只会用左外连接，那也可以说他学会了外连接。

子查询

        子查询就是一个查询里面包含了另一个查询。形成了一个嵌套，所以我们叫他做嵌套查询，也叫子查询。

        子查询有很多类型。

1. 标量子查询 
2. 列子查询
3. 行子查询
4. 表子查询

        标量子查询，即子查询只返回一个值，可能是某一个人的年龄，性别等，但只有一个值。

        列子查询和行子查询， 就是返回一行或者一列的值。

        表子查询，返回多行多列。

举个例子。

        别如我要查询在“”JoneyB“”进公司之后才进来的员工的信息。

        那首先就是要查询到JoneyB的入职时间

        select entrydate from emp where name = 'JoneyB'

        大家会发现在宇宙大爆发前就已经进了。他返回一个 datetime的值给我们 比如叫d。

        然后 查询entrydate大于这个值的员工信息。

        select * from emp where entrydate > d;

        最后 我们d是怎么得到的？ 是通过查询语句，那就把d复制到最后的这个查询的位置上

         select * from emp where entrydate >(select entrydate from emp where name = 'JoneyB')

        这个形式就是标量子查询了。因为他只返回一个值，就是datetime类型的值。

那列和行其实也差不多，无非是select 后面跟的东西是什么。

但实际操作时候可能会有点疑问。下面进行解答。

        当我们返回数据的时候，有时候会在同一条语句下返回两个数据，那该怎么接收？

        比如select entrydate,name from emp where xxx

        这一行sql语句中，我们返回了entrydate和name，那进行子查询的时候，该怎么接收呢？

        使用()聚合接收。

        select * from emp where (entrydate,name) = (select entrydate,name from emp where xxx)

表子查询：

        当我需要查询 入职员工日期为 “2023-01-01”之后的员工信息和部门名称，该怎么写呢？

        首先我们会用到emp表和dept表。

        先对入职员工日期为 “2023-01-01”之后的员工信息进行查询

        select * from emp where entrydate > '2023-01-01'

        然后拿到他们的部门名称和员工信息

        select e.*,d.name from dept d ,(select * from emp where entrydate > '2023-01-01') e where e.dept_id = d.id 

到这里，基本操作就讲解完了。

事务

        我说的是基本操作。 事务是高级thing。

        全程应该叫事务控制，是用来做统一操作的。 即一套流程，要么全部成功执行，要么全部失败。

        什么时候用到呢？  有这么一个情况：我们要执行两个指令，一个是删除student的信息，一个是删除student的表格。 如果前面一个指令没有问题，而后面一个指令我写错字了，没用执行完毕。那显而易见就会拉下一个指令。 如果我们一套指令有很多的操作，里面有的执行完了，有的没有执行完，那找问题的时候就会爽上天。

        事务就是不让我们爽的东西。下面说一下事务控制的操作。

其实十分简单。

        如果需要用到事务，只需要在前面加上begin，最后加上commit，如果中间有操作执行失败，那就会全部失败，如果全部成功了，那就会提交。 那么提交意味着什么？ 就是图上这个commit，提交意味着操作的数据真正地在数据库中完成了，去到数据库中能找到刚刚的数据变化。

事务有四大特性

四大特性

        内容就这么多，要是再多的字来形容也算是废话了。只要理解了就好了。

索引

        当数据库中的数据非常多时，我们简单地查询一个数据都会变得非常慢。我手里没这么多数据，所以就只是听别人讲是这样的。我发誓，我看到别人这么操作的时候，确实也花了很多时间，十几秒钟才找了一个数值出来，这很明显是要被炒鱿鱼的。

        那怎么进行优化呢？答案就是我的ctrl1写的 索引。

        索引就是建立一个数据建构，让查询的时候及时数据库非常庞大，也能快速查询得到对应的数据。

        有多快呢？ 原来十几秒才能查到的，用了索引几毫秒就查到了。

        索引只需要在创建表格的时候设置一下就能使用了，当然也可以数据给完进去才设置。

        设置的代码也就一行，如果只设置一个字段的话。这里就不贴代码出来了。索引是只需要设置一次，后面的查询都会跟着这个索引来寻找对应的数据。

索引的结构        

        默认是使用B+Tree结构组织的索引。

        先不说什么是B+tree结构，先说我们熟悉的，为什么不用二叉搜索树呢？

        在二叉搜索树当中，左子树当中的所有数据都是小于根节点的，而右子数当中的所有数据都是大于根节点的，而且根结点的左右两个子树又都是一个二叉搜索数，而如果是一个二叉搜索数，又很容易出现一个问题，那就是偏向一边的情况。如果我们在保存数据的时候，是根据数据的从大到小或者是从小到大的顺序来保存的，此时就会将二叉搜索数绘化成一个单向链表，此时它的搜索性能就会大打折扣。

        其实就是说，如果有时候存的数据比较特殊，使用二叉搜索树的效率就会低得离谱。

        那我们想一下。这真正的问题是什么呢？ 其实就是在数据量大的时候，层数太多，一层层走下去会很慢。二叉搜索树只有两个脚，不是走这边就是走那边，给数据的选择太少了，所以数据量大的时候就会长得很深。

        

        说回我们的主角。

        B+Tree的中文名叫多路平衡搜索树，他是一棵搜索树的同时，路子还多。

        前面我们说二叉搜索树的缺点就是路子不够野，太有局限性。现在的多路就能解决这个问题。  

        图中我们只看到了三路，其实不止是三路的，真正的实现其实有n路。如果我们看到真正的多路平衡树，我们就会爱上它，因为它又矮又胖，我们的数据查找不用经过太多层就能最终找到。

        

        在最下面的我们称之为叶子节点，在B+tree中，只有叶子节点才存放数据，而叶子节点头上的非叶子节点，只存一个指引，让其一层一层地找到最终的数据，

        

        它的叶子节点元素是按照元素的从小到大的顺序来排序的。像60 20 6 18 29 34 38 45是不是都是按照从小到大的顺序排序的？而且在叶子节点形成了一个双向链表，我们由上一个元素可以找到下一个元素，比如下一个元素也可以找到上一个元素。这是b+数索引结构它的一个特点。

        查找过程

        比如现在我要来查找一个数据，这个数据是53，我要来看一下具体是怎么查找的。首先它会从根节点开始对比，在根节点当中进行对比的时候，它内部会采用二分查找的方式来进行操作，直接去定位53在哪个区间范围之内，最终定位到在38和67之间，是大于等于38，并且小于67的，所以他会走p2指针， p2指针指向的是下一个磁盘块，指向的是磁盘块。

        所以接下来他会再去查找磁盘块当中的数据，然后再来对比53在哪个区间范围之内，是介于47~55之间的。紧接着他会继续走磁盘罐当中的p2指针，而 p2指针又指向的是下面磁盘块，所以继续到下面这个磁盘块当中再来查找，最终找到磁盘块当中的53，并获取到对应的data。

        

        最终生成的B+搜索树，会占用比较大的空间，也会增加对数据的更改所需时间。但优点是搜索会特别的快。

        大家心里也或多或少明白了，这其实就是用空间来置换时间的一个数据结构。空间问题其实大家不用担心，在企业中的硬盘大的很，而且我们对数据的操作中，80%是查询操作，如果对数据的更改需要的时间稍微增加了，我们也是能接受的。