MySQL进阶增删改查[图文超详细讲解]

• 一.创建数据库约束
• • 1.1约束类型
  • 1.2not null约束
  • 1.3unique :唯一约束(约束列)
  • 1.4default :默认值约束
  • 1.5primary key : 主键约束(常用)
  • 1.6foreign key : 外键约束(两表进行关联)
• 二.表的顺序
• • 2.1 一对一
  • 2.2 一对多
  • 2.3 多对多
  • 2.4语句示例
• 三.新增
• • 3.1新增进阶操作
• 四.查询
• • 4.1聚合查询(单表查询)
  • • 4.11聚合函数
    • 4.12group by 子句查询
    • 4.1.3having
  • 4.2联合查询(多表查询)
  • • 4.2.1内链接
    • 4.2.2外连接
    • 4.2.3自连接（了解）
    • 4.2.4合并查询
• 五.内容总结

一.创建数据库约束

1.1约束类型

not null - 指示某列不能存储 null值。
unique - 保证某列的每行必须有唯一的值。
default - 规定没有给列赋值时的默认值。
primary key - 是 (not null 和 unique )的结合确保某列（或两个列多个列的结合）有唯一标识有助于更容易更快速地找到表中的一个特定 的记录。
foreign key - 保证一个表中的数据匹配另一个表中的值的参照完整性。
check - 保证列中的值符合指定的条件。对于MySQL数据库，对CHECK子句进行分析，但是忽略CHECK子句。

1.2not null约束

创建表时，可以指定某列不为空：
如果插入空值就会报错
可以多设置not null来限制多列

create table student (
id int not null,
sn int ,
name varchar(20)
);

1.3unique :唯一约束(约束列)

**指定sn列为唯一的、不重复的：

create table student (
id int not null,
sn int unique,
name varchar(20)
);

重复输入带有 unique列约束列的值会插入失败

1.4default :默认值约束

指定插入数据时，name列为空，默认值可以自己设置例如"匿名"：

create table student (
id int not null,
sn int unique,
name varchar(20) default 'unkown'
);

**

1.5primary key : 主键约束(常用)

指定id列为主键:(有primary key 约束条件存在就可以省去null约束和唯一约束)
但是主键只能对一个列进行约束

create table student (
id int  primary key,
sn int unique,
name varchar(20) 
);

对于整数类型的主键，常配搭自增长auto_increment来使用,插入数据对应字段不给值时，使用最大
值+1。

create table student (
id int  primary key auto_increment,
sn int unique,
name varchar(20) default 'unkown'
);

我们可以在插入时输入id可是不输入,再插入后数据库的表会自己生成自增主键约束那个列来进行自增长+1

如果我们在插入中没有进行默认自增长插入,当我们想插入的某一值后,再进行默认自增主键插入后,会在我们刚插入的数据后进行自增长

1.6foreign key : 外键约束(两表进行关联)

创建学生表student，一个学生对应一个班级，一个班级对应多个学生
我们可以创建两个表,班级表来当做"父表" 学生表来当成"子表"

classes_id为外键，关联班级表id
create table student (
id int  primary key auto_increment,
name varchar(20),
classId int,
foreign key (classesId) references class(classId)
);

这里我们就用classId来进行班级和学生进行了关联

这里我们在student子表进行插入班级Id为100时,当创建父表没有这个classId列中没有100就会报错,所以student子表插入失败

当我们进行将原classId进行修改后100可以发现,会发生修改错误,修改的classId在附表class中存在的classId中存在,我峨嵋你就会插入成功

当我们想要删除父表class中的classId时我们会发现,我们并不能删除或者修改,也就是当class 与 student中由classId这已关联就不可以在父表class进行删除或者修改有关联的classId了,同理我们可以删除class 与 student中由classId这无关联的数据

二.表的顺序

2.1 一对一

一对一就是输入一个信息就可以找到这个人:如这身份证就是这个人的独特唯一身份信息

2.2 一对多

就是一条信息对应一个群体,例如:一个班级了有很多学生,我们可以知道这个班级里学生的学号,如上述外键约束同理

2.3 多对多

代表不同的对象相互之间都是由关系,但不是相互的唯一的信息,如: 大学中高数这一门课程,这节课可以由很多班级或者院校的同学去上课,学生也可以选择去上一些大学物理,大学英语这样课.

2.4语句示例

– 创建课程表:

create table course (
id int primary key auto_increment,
name varchar(20)
);
create table student (
id int primary key auto_increment,
name varchar(20)
);

创建学生-课程的中间表:

create table score (
student_id int,
course_id int,
foreign key (student_id) references student(id),
foreign key (course_id) references course(id)
);

三.新增

3.1新增进阶操作

创建两张表,当我们发现这两张表 列个数相同,数据类型相同,我们就可以执行 insert into B select * from A;
这样我们就可以完成插入,成功将A表的数据插入B表中

我们删除B表重现创建与A表列相同数据类型想反的表B;输入insert into B select name,id from A;
注解:当我们进行两个表插入时,我们只需要列表个数相同,并且列表数据类型相等就可以进行插入,但是这样操作大多数没有意义,新增语句操作后是存储到硬盘中,所以我们要注意此操作,防止弄巧成拙.

四.查询

4.1聚合查询(单表查询)

4.11聚合函数

函数 ------------------------ 说明
COUNT(expr) 返回查询到的数据的 数量
SUM(expr) 返回查询到的数据的 总和，不是数字没有意义
AVG(expr) 返回查询到的数据的 平均值，不是数字没有意义
MAX(expr) 返回查询到的数据的 最大值，不是数字没有意义
MIN(expr) 返回查询到的数据的 最小值，不是数字没有意义

select count(列名) from 表名;

count 是我们在查询中查询输入列名的总行数

我们发现chinese这一列也是8行,但是在我们查询中只会显示出7行,这就代表带有null的行数是不会计入count查询结果中

当我们进行有null的行和无null行的两个列进行count查询会发现只会查询没有null行的数据所以id有10行但是count只查询9行

sum操作:根据这(一列或者多列)进行整体相加(只能针对数字进行运算,不能针对字符串进行运算)
我们也可以用where来进行条件操作



avg(平均数)
max(最大数)
min(最小数)
这些查询语句都和上述类似就简单略过了.

4.12group by 子句查询

group by(列名) 根据行的值来进行分组,将行相同值分为一组
我们先执行group by(列名)后在执行前面的操作

我们可以执行where语句来进行条件查询

4.1.3having

having查询语句时时在group by 分组后进行条件查询
我们也可以用where 和 having来进行搭配条件查询

4.2联合查询(多表查询)

实际开发中往往数据来自不同的表，所以需要多表联合查询。多表查询是对多张表的数据取笛卡尔积：
也就是将两个表每一行和另一表的每一行进行联合查询

如下

在查询中我们会发现有我们不需要的数据，这样我们就可以来进行where来筛选查询

4.2.1内链接

我们先创建一些表

我们在多表查询的会发现有很多数据时多余的

例如我们可来查询许仙的成绩

利用select 列名,列名from(表名)join(表名) on 条件
join on 和where 应用类似（join on了解即可，一般我们使用两个表查询情况下更多会使用where）

我们来进行多表查询

rom 表名 join 表名 on 条件 join 表名 on 条件

4.2.2外连接

我们发现join on 这个条件很麻烦，容易出错
其实我们可以用join on来构造其他链接
select 列名,列名from(表名) inner join(表名) on 条件
select 列名,列名from(表名) left join(表名) on 条件
select 列名,列名from(表名) right join(表名) on 条件
我们来创建两个列与列没有相关联的表

当我们进行联合查询

当我们进行where语句查询后会发现我们有一些需要的数据会丢失

select 列名,列名from(表名) left join(表名) on 条件
我们进行做查询之后jiukey 找到我们需要的数据了

select 列名,列名from(表名) right join(表名) on 条件**
与左链接同理

我们发现内连接和外连接是有表与表的结构不同
如图1.内2.左外3.右外

4.2.3自连接（了解）

自连接是将本身一张表进行行和行转化成列联合查询

同理可以根据where语句进行查询来减少多余数据得到我们想要得到想要的数据

4.2.4合并查询

我们将两个select查询用 union 来进行合并查询
union all是不去重的，我们了解就可以了

当我们在一张表进行查询可以用or

五.内容总结

约束类型

 not null - 指示某列不能存储 null值。
 unique - 保证某列的每行必须有唯一的值。
 default - 规定没有给列赋值时的默认值。
 primary key - 是 (not null  和 unique )的结合确保某列（或两个列多个列的结合）有唯一标识有助于更容易更快速地找到表中的一个特定          的记录。
 foreign key - 保证一个表中的数据匹配另一个表中的值的参照完整性。
 check - 保证列中的值符合指定的条件。对于MySQL数据库，对CHECK子句进行分析，但是忽略CHECK子句。

聚合查询

*函数                                          说明
COUNT(expr)                            返回查询到的数据的 数量
SUM(expr)                       返回查询到的数据的 总和，不是数字没有意义
AVG(expr)                      返回查询到的数据的 平均值，不是数字没有意义
MAX(expr)                      返回查询到的数据的 最大值，不是数字没有意义
MIN(expr)                      返回查询到的数据的 最小值，不是数字没有意义