MySQL数据库进阶操作（超详细大总结）

目录

        前言：

        数据库约束

        NULL约束

        unique约束

        primary key主键约束

         default约束

        外键约束

        表的设计

        基本思路

        一对一关系

        一对多关系

        多对多关系

        新增

        聚合查询

        count函数

        sum函数

        avg函数

        max，min函数

        分组查询

        分组之前，指定条件

        分组之后，指定条件 

        分组前后指定条件

        联合查询

        内连接

        示例：

        外连接

        示例：

        自连接

        示例：

        子查询

        示例：

        合并查询

        示例：

        小结：

---

前言：

  数据库的基础操作需要我们去理解，和思考。对于比较复杂的查询，要理解其语句的执行顺序，这样会有助于我们对于表的一些操作。

数据库约束

NULL约束

  语法：create table 表名(列 not null, 列...);

create table person(id int not, name varchar(20));

  注意：可以清楚看见不能够在id这一列插入null。如果我们不指定，它默认是可以为null的。如果指定列插入，不指定被not null约束这一列时，就会报错。因为默认会给这列添加null。

unique约束

  语法：create table 表名(列 unique,列...);

create table person(id int unique, name varchar(20));
insert into person values(1,'aaa');
insert into person values(1,'bbb');

   注意：当用unique约束之后，这列的数据只能存在一份，不能重复。如果插入重复的数据则会报错。

primary key主键约束

  语法：create table 表名(列 primary key,列...);

create table person(id int primary key, name varchar(20));
insert into person values(null,'aaa');
insert into person values(1,null);
insert into person values(1,null);
desc person;

注意：

  当我在id这列插入null时报错了，插入重复数据时也报错了。primary key是not null和unique的结合体。

  主键的存在就是为了区分每一列的。如果数据量特别大，我们自己去添加主键时就会特别麻烦。mysql为我们提供了自增主键：auto_increment。在插入数据时只需要给主键一个null，它就会自动帮我们找到独一无二的主键。添加主键的规则是找到前面主键的最大值，然后逐渐递增。

  如果数据量特别大，已经超过主键的最大值，这时候就需要我们分库分表。如果一个服务器不够存储数据，那么就需要多个服务器来存储。这里涉及到“分布式系统中唯一id生成算法”。实现公式：时间戳 + 主机编号 + 随机因子。结合这三部分就可以生成一个全局唯一的id。

create table person(id int primary key auto_increment, name varchar(20));
insert into person values(null, 'aaa');
insert into person values(null, 'aaa');
insert into person values(null, 'aaa');
insert into person values(100, 'aaa');
insert into person values(null, 'aaa');
select * from person;

 default约束

  语法：create table 表名(列，列 defaule 值...);

create table person(id int, name varchar(20) default '无名氏');
insert into person(id) values(1);
select * from person;

  注意：当给指定列添加默认约束之后，如果我们不主动的添加数据，这列的数据默认就是我们指定的值。

外键约束

  语法：foreign key (列 ) references 表名(列));

create table class(id int primary key, name varchar(20));
create table student(id int, name varchar(20), class_id int, foreign key (class_id) references class(id));

注意：

  当设置外键约束之后，student为子表，class为父表。student表里class_id和class表里的id建立连接。class_id必须在class表里的id里存在，不然就会报错。学生表里的数据要依赖于班级表中的数据，班级表中的数据要对学生表中数据产生约束力。

  由于在子表里添加数据需要遍历父表中的列。为了保证其效率，父表相应列需要有索引。设置主键之后会自动添加索引，所以被关联的父表列必须有索引。unique约束也会自动添加索引。

  当父表约束子表后，其实子表也对父表产生约束。如果父表和子表里的数据已经产生了引用关系，那么就不能删除父表中的数据，不然子表中的数据存在就不合理了。删除表时也只能先删除子表然后删除父表。 

表的设计

  表的设计需要一定的经验，这里先简单介绍下。

基本思路

  1）先明确实体。

  2）再明确实体之间的关系。

          一对一

          一对多

          多对多

          没关系

  3）结合上述内容，确定表结构。

一对一关系

   一个学生只能有一个账户，一个账户只能被一个学生拥有。

  1）学生和账户在同一个表里。这里没有写全，可以表达明确意思。

stuent_account(id, name, username, password);

  2）学生和账户不在同一个表里，建立关联关系。

student(student_id, name);
account(username, password, student_id);

student(id, name, account_id);
account(account_id, username, password);

一对多关系

  一个学生只能存在一个班级中，一个班级可有多个学生。

student(id, name, class_id);
class(class_id, name);

多对多关系

  一个学生可选修多门课程，一门课程可被多名学生选修。

student(student_id, name);
class(class_id, name);
student_class(student_id, class_id);

新增

  将查询结果插入到另一个表中。

  语法：insert into 表1 select * from 表2；

insert into student2 select * from student;

   注意：没有values。原始数据表需和目的地表列相匹配。

聚合查询

  使用聚合函数进行查询，针对于行和行之间。这里演示的几个聚合函数查询。

count函数

select count(*) from student;

   注意：会将查询的临时表带入count函数求得值。

  注意：针对列计数时，会对null特殊处理。不会将null算进去。

sum函数

   注意：针对score列进行求和。也会对null特殊处理。

avg函数

   注意：针对score列求平均值。对null特殊处理。也可以使用表达式进行求平均值。

max，min函数

  注意：求最大最小值，也会对null特殊处理。不会认为null为最小值。 

分组查询

  语法：group by 列；

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

   注意：对于role相同的值分完组之后，针对每个组求平均值。

分组之前，指定条件

  语法：where 条件 group by 列；

  求平均值不计算dd老师。

 select role,avg(salary) from emp where name != 'dd' group by role;

  注意：可以清楚看见老师的平均值为6000。

分组之后，指定条件 

  语法：group by 列 having 条件；

  平均值在10000块以下的岗位

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

  注意：已经不包含老板的平均值了。

分组前后指定条件

  语法：where 条件 group by 列 having 条件；

  不包含dd老师，平均值在10000块以下的岗位。

select role,avg(salary) from emp where name != 'dd' group by role having avg(salary) < 10000;

   注意：这里在分组前筛选了dd老师，分组求完平均值之后筛选出10000块以下的岗位。

联合查询

  联合查询是针对于表和表之间的查询。要查找的数据在不同的表里，就需要联合查询。它会将两张表的列拼在一起，组成一张大表（笛卡尔积）。分别取出第一张表中的每一条记录，和第二张表里的每一条记录进行全排列，组合成新的记录。那么最终大表就有两张表记录个数相乘条记录，两张表列数相加条列。然后再根据需求选择最终的数据。

内连接

  语法：select 列 from 表名，表名...;

示例：

  对于以下两张表查找学生的班级

  注意：笛卡尔积之后的结果，由于是全排列，会有很多错误的数据。然后筛选需要查找的数据。

select * from student,class;

注意：

  筛选之后的结果，寻找两张表之间的连接条件。

  当两张表的列名相同时，需要指定表名去确定列名。可用as为列起别名。

select student.name, class.name from student,class where student.classId = class.classId;

  注意：使用join on进行内连接。也可再join前加inner体现出内连接。

  语法：join连接两张表之间的笛卡尔积，on后加条件。

select student.name as 学生姓名,class.name as 班级 from student inner join class on student.classId = class.classId;

  注意：对于两张表之间的查询需要一个连接条件。那么三张表之间的笛卡尔积就需要两个连接条件。第一种直接用逗号连接三张表即可，join on的写法是在两张表笛卡尔积的结果上继续join on笛卡尔积。

外连接

  如果两张表之间的数据都是有对应关系的，那么内连接和外连接的结果都是一样的。相反如果有一些记录没有对应关系，那么内连接最多可查询的记录只能是两张表交集的记录条数，而外连接可以指定要完全显示哪张表的记录。外连接只能使用join on来完成。

示例：

  针对以下两张表进行操作。两张表有记录没有对应关系。

 内连接

select * from student,score where student.id = score.student_id;

  注意：最多可显示两张表交集的记录条数。

外连接

  语法：可在join前加left或者right来指定完全显示哪张表的所有列。

左外连接

 select * from student left join score on student.id = score.student_id;

  注意：会完全显示join左边表的所有列。如果右表没有与之相对应的列会用null填充。

右外连接

select * from student right join score on student.id = score.student_id;

  注意：会完全显示join右边表的所有列。如果左表没有与之相对应的列会用null填充。

自连接

  语法：select 列... from 表 as 别名1，表 as 别名2 where 条件；

  针对一张表内行和行之间的比较。我们可将行转为列进行比较。即自己和自己进行笛卡尔积。

示例：

  查找哪两个同学的成绩相差20分（还是上面那么张score表）

select * from score as s1,score as s2 where s1.student_id != s2.student_id and s1.score - s2.score = 20;

  注意：自己和自己笛卡尔积，需要指定别名，不然名字一样无法识别。接着就横根据需求指定条件即可。

子查询

  语法：一个SQL语句的查询结果作为另一个SQL语句的条件。

示例：

  查询哪位同学的成绩为70分。（上面的student和score表）

select * from student where id = (select student_id from score where score = 70);

  注意：这样的写法可读性差，在使用过程中需注意。

合并查询

  语法：SQL语句 union/union all SQL语句；

  将查询的结果合并为一张表，两张表的列需相互匹配。使用union 或者union all关键字。union会将查询的结果去重，union all不会去重。这样使用可针对不同的表进行。

示例：

  查询成绩为70或者90的同学。（上面的score表）

select * from score where score = 70 union select * from score where score = 90;

  注意：已经成功的显示在了一张表中。 

小结：

  在学习过程中，要多去思考。理解其中的原理，会对我们的学习有很大帮助。