MySQL-数据库查询进阶

一.数据库的约束

        数据库自动的对数据的合法性进行校验检查的一些列机制，目的是为了保护数据库中的数据，避免插入/修改一些非法数据。

        1.约束类型（数据库在引入约束后，执行效率就会受到影响，可能会降低很多）

                NOT NULL 指示某列不能存储NULL值。              

                UNIQUE 表中的某一列中的值是唯一的，不可重复的。（不仅仅会限制插入还会限制修修改，会让后续插入数据/修改数据的时候，都先执行一次查询操作，通过这个查询操作的结果来确定当前这个记录是否存在）

                DEFAULT 默认值（默认值的默认值是NULL） 

                PRIMARY KEY 主键 

                        在一张表中只能有一个主键，但是主键并不是只能是表中的一个列，可以是多个列形成的主键。

                         对于带有主键的表来说，每次插入/修改数据的时候，也会先执行一次查询操作。

                         mysql会自动将带有unique和primary key的列自动生成索引，从而加快查询速度。

                        主键的唯一性：

                                mysql提供了一种“自增主键”的机制，主键通常是int/bigint的类型，在插入数据的时候，不必手动指定主键的值，而是由数据库服务器自动从1开始依次递增的分配主键的值（相当于使用了一个变量，来保存当前表的id的最大值，后续分配自增主键都是根据这个最大值来分配的，如果手动指定id，也会更新这个最大值）。

                        自增主键也是有一定的局限性的，如果是单个mysql服务器没有问题，但是在分布式系统中，又多个mysql服务器构成的集群，这个时候依靠自增主键就不行了。

                        分布式系统中生成唯一id的算法：

                                时间戳（如果在插入数据慢的情况下用时间戳就可以了） + 主机/机房编号（同一时间添加多个商品时，此时多个商品的数据是落到不同机器上的，所以此时要保证唯一性就可以使用时间戳 + 主机/机房编号） + 随机因子（就算时间戳 + 主机/机房编号在在同一时间插入多个商品的数据时还是有可能有相同的，所以在引入随机因子就可以了）（这里+号是指字符串拼接）

                FOREIGN KEY外键（描述了两个表之间的关联关系）

                        在指定外键的时候需要注意的一点是，父表中需要被关联的列需要是主键或者unique。                

                        class表中数据约束了，student表中的数据，把class表称为“父表” 约束别人的表，把student表称为“子表”  被别人约束的表。

                        如果在子表中已经引用了某个值，那么在父表中就不可以删除这个被引用的值了，也不可以将整个父表删除，因为将整个表删除的前提是将这个表中的数据先清空。

                        如果在网上商城中，一个商品已经被用户购买，但是商家此时又想下架这个商品，但是不可以将这个商品从商品表中删除，所以只能将其隐藏。

二.表的设计

        1.一对一

        2.一对多

        3.多对一

三.查询进阶

        1.将查询的结果作为插入的值

                条件：查询出来的结果集合要和目标插入表中的列数和类型匹配

                insert into student2 select * from student；

        2.聚合查询             

                count：如果查询是列则返回的是该列中有多少数据，如果是整张表则返回的是这个表中有多少行。select count(distinct math) from exam_result;返回math列去重后的数据量。

                sum：只能针对数字类型使用（会自动排除NULL），mysql在执行聚合函数前会将数据先转换为double如果可以转换完成则执行聚合函数，如果没有转换完成则返回0。

        3.分组查询

                使用group by进行分组（针对指定的列进行分组，将这一列中的相同值分为一组，得到若干组），针对每一个分组，再分别进行查询。

                select role,avg(salary) from emp group by role;

                如果分组但不聚合的情况下，是从每组中找一个代表的数据，来表示每组的数据，所以group by一定要配合聚合查询来使用，否则得到的结果是没有意义的。

                分组查询中也可以带有条件

                        在刨除掉员工为张三的前提下，计算公司中不同员工种类的平均工资。

                        select role,avg(salary) from work where name != '张三' group by role; 

                having：

                        查询每个岗位的平均薪资，但排除平均薪资超过两万的结果,同时刨除掉张三的薪资。

                        select role,avg(salary) from work  where name != '张三' group by role having avg(salary) < 20000;

        4.联合查询（多表查询）

                内连接：
                        笛卡儿积（简单无脑的对需要进行笛卡儿积的表进行排列组合，将所有可能的情况都穷举一遍，所以得到的结果就包含一些合法数据和非法无意义的数据）。                      

                        通过笛卡儿积得到的结果需要通过连接条件进行筛选，将无意义的数据筛选出去。

                        select * from 表1, 表2;(将表1和表2进行笛卡儿积组合)

                        select * from 表1, 表2 where 连接条件;（进行条件筛选）

                        在进行笛卡儿积的时候，一旦所操作的表的数据量过大，或者表的数目太多，此时笛卡尔积的结果就会非常的庞大，此时就有可能使mysql服务器挂掉。

                外连接：

                        如果两个表中的数据都是对应关系的，此时对这两个表进行内连接和外连接的结果是相同的，但是如果不对应的话，结果就会有出入。

                        左外连接：以左侧的表为基准，将左侧表中的每一个数据都会出现在最终的结果中，如果是不对应的列，那么用null来填充。

                                内连接结果：

                                左外连接结果：

                                右外连接结果：

                                在数据库中其实也是存在全连接的，但是mysql中并不支持，在oracle中是支持的。 

        5.子查询

                将多个简单的sql语句拼接成一个复杂的sql语句然后去执行多个步骤。（可读性很差，在实际开发中并不建议使用）        

        6.合并查询

                将多个sql语句的查询结果集合，合并到一起。

                select * from course where id < 3 or name = '英文';

                select * from course where id < 3 union select * from course where name = '英文';

                or只能操作一个表，但是union可以针对不同的表，但是这两个表列的类型和数量要匹配，查询结果的列的名字同第一个表的列名相同。使用union后结果是自动去重的，可以使用union all来阻止这种结果的发生。