【简单易懂】MySQL增删改查(进阶)

目录

1.数据库约束

1.1 约束类型

1.2 NULL约束

1.3 UNIQE:唯一约束

1.4 DEFAULT:默认值约束

1.5 PRIMARY KEY:主键约束

 1.6 FOREIGN KEY:外键约束

2. 表的设计

3. 新增

4. 查询

4.1 聚合查询

4.1.2 GROUP BY子句

4.1.3 HAVING

4.2 联合查询

4.2.1 内连接

4.2.2 外连接

4.2.3 自连接

4.2.4 子查询


1.数据库约束

1.1 约束类型

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

1.2 NULL约束

创建表时,可以指定某列不为空:
create table student (
    id int not null,
    name varchar(30)
);

 如果往 id 列插入空值,就直接报错

【简单易懂】MySQL增删改查(进阶)_第1张图片

1.3 UNIQE:唯一约束

uniqe就是指定列的每一个数值为唯一值,不能有重复。如果往指定的列中插入重复值也会报错。

create table student (
    id int unique,
    name varchar(30)
);
    

 如下列表信息中已经表明

【简单易懂】MySQL增删改查(进阶)_第2张图片

1.4 DEFAULT:默认值约束

default 就是给插入空数据时指定默认值,也可以手动默认

create table student (
   id int,
   name varchar(30) default,
   age int
);

【简单易懂】MySQL增删改查(进阶)_第3张图片

 往表中增加信息

【简单易懂】MySQL增删改查(进阶)_第4张图片

 将name 默认为 ‘guangming’

create table student (
   id int,
   name varchar(20) default 'guangming'
);

 【简单易懂】MySQL增删改查(进阶)_第5张图片

1.5 PRIMARY KEY:主键约束

对于一个表来说,只能有一个列被指定为主键,并且 primary key 是 not null 和 unique 的结合,确保某列有唯一标识,有助于更容易更快速的找到表中的一个特定的记录。

确认了主键,该列既不能为空又不能为重复值。

如下插入数据,将 id 列为空,最终显示结果为错误

【简单易懂】MySQL增删改查(进阶)_第6张图片

 插入重复值,为错误

 对于主键的关键用法就是使用在数字自增中,auto_ncrement 是主要句子

创建表

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

 插入数据

insert into into student (id, name) values
(null, 'lisi'),
(null, 'zahngsan'),
(null,'wnagwu');

 在插入数据中,并没有插入 id ,但是表中可以自动生成

【简单易懂】MySQL增删改查(进阶)_第7张图片

 自增主键也可以手动指定 id, 但是后序的自增是从指定的 id 开始的

【简单易懂】MySQL增删改查(进阶)_第8张图片

 1.6 FOREIGN KEY:外键约束

外键用于关联其他表的主键或唯一建

 foreign key (字段名) references 主表 (列)

 例如创建一张学生表和班级表,学生表就依赖于班级表,学生表就为‘字表’,班级表就为‘父表’

【简单易懂】MySQL增删改查(进阶)_第9张图片

 先创建一个班级表

 【简单易懂】MySQL增删改查(进阶)_第10张图片

 classId 既是 class 表的主键,又是  student 表的外键

【简单易懂】MySQL增删改查(进阶)_第11张图片

 然后构造一个学生表,来指定一下 外键约束

【简单易懂】MySQL增删改查(进阶)_第12张图片

 往表中插入数据,其中 classId 为1,在class 表中存在,所以插入成功

 如果我们将classId 为10,在class 表中不存在,所以插入不成功

insert into student values (null, 'wangwu', 10);

 【简单易懂】MySQL增删改查(进阶)_第13张图片

 外键约束,同样也约束着父表,当父表中的某个记录被子表中依赖着的时候,进行修改或者删除都会失败

delete from class where class where classId = 1;

 【简单易懂】MySQL增删改查(进阶)_第14张图片

 外键约束工作原理

在子表中插入新的记录的时候,就会根据对应的值,在父表中先查询,查询到之后,才能够执行后序的插入。

查询操作就是根据父表中被依赖的这一列,必须要有索引,即得到 primary key 或者unique 

2. 表的设计

2.1 一对一

就是唯一性,例如每个人只有一个身份证号

2.2 一对多

以班级为例,一个班级可以有多个学生。

2.3 多对多

以学生和课程为例,一个学生可以选多们课,一门课可以被多个学生选

3. 新增

 将查询到的结果插入新的表中,但是需要保证查询到的结果与插入到新的表中类型匹配,列的顺序可以不一样。

创建 A 表  、 B表

create table A (id int ,name varchar(20), age int);
create table B (id int ,name varchar(20), age int);

 往表A中插入数据

insert into A values(1, 'lisi', 13), (2, 'wangwu', 15);

【简单易懂】MySQL增删改查(进阶)_第15张图片

 将查询A的结果插入表B中

insert into B select * from A

【简单易懂】MySQL增删改查(进阶)_第16张图片

4. 查询

4.1 聚合查询

4.1.1 聚合函数

常见的统计总数,计算平均值等,可以使用聚合函数来实现,常见的聚合函数有:

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

count 函数 的用法

查询  exam 表的人数, count(*)

 select * from count(*) from exam;

【简单易懂】MySQL增删改查(进阶)_第17张图片

 在 count 时,也可以指定某个列,如,指定math 列,count(math)

select count(math) from exam;

 【简单易懂】MySQL增删改查(进阶)_第18张图片

 注意,如果表中存在 null ,则是不会被记录在当中的

 sum 求和函数

将列的若干个数值相加

例如,求 math 列的总成绩, sum(math)

select sum(math) from exam;

 【简单易懂】MySQL增删改查(进阶)_第19张图片

 sum 函数只能针对数字进行运算,不能针对字符串来进行

 例如对name进行计算,sum(name)

select sum(name) from exam;

【简单易懂】MySQL增删改查(进阶)_第20张图片

 一个聚合函数里面的参数,也可以通过表达式的方式进行运算

select sum(chinese) + sum(math) from exam;
select sum(chinese + math + english) from exam;

【简单易懂】MySQL增删改查(进阶)_第21张图片

 还可以用 as 起别名来显示;

select sum(chinese + math + english) as total from exam;

【简单易懂】MySQL增删改查(进阶)_第22张图片

 聚合函数还可以搭配 where 字句来使用,可以基于条件进行筛选,把筛选结果再进行聚合

 例如,先筛选出 english 大于 85 的,然后将大于 85 的求和

select sum(english) from eaxm where english > 85;

【简单易懂】MySQL增删改查(进阶)_第23张图片

4.1.2 GROUP BY子句

SELECT 中使用 GROUP BY 子句可以对指定列进行分组查询。需要满足:使用 GROUP BY 进行分组查询时,SELECT 指定的字段必须是 分组依据字段 ,其他字段若想出现在 SELECT 中则必须包含在聚合函数中

 例如对 emp 表进行分组在聚合函数查询

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

【简单易懂】MySQL增删改查(进阶)_第24张图片

 group by 是可以使用 where, 只不过是在分组之前执行

 如下,先去掉 name 是 liliu 的,在进行对 role 分组

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

 【简单易懂】MySQL增删改查(进阶)_第25张图片

4.1.3 HAVING

GROUP BY 子句进行分组以后,需要对分组结果再进行条件过滤时,不能使用 WHERE 语句,而需要用HAVING

 如下,先对role 进行分组,求avg, 然后执行筛选条件 having, 最终将筛选之后的显示出来。

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

【简单易懂】MySQL增删改查(进阶)_第26张图片

4.2 联合查询

联合查询就是把多个表的记录往一起合并,一起进行查询,称为多表查询,使用的方法是“笛卡尔积”

运算过程: 先拿第A表的第一行记录与第B表的每行记录分别组合,得到一行新的记录,

再继续拿A表的记录和B的记录重复执行上述的操作。

得到的列数就是A 的列数 + B的列数

【简单易懂】MySQL增删改查(进阶)_第27张图片

 例如先单独查询 表 a 和表 student ,

select * from a;
select * from student;

【简单易懂】MySQL增删改查(进阶)_第28张图片

 进行联合查询

语句就是直接 select * from 表名,表名;

表名与表名之间是有逗号隔开

select * from a, student;

【简单易懂】MySQL增删改查(进阶)_第29张图片

 加上条件,可以单独列出一行的信息,

select * from a, student where a.id = student.studentId;

【简单易懂】MySQL增删改查(进阶)_第30张图片

 只保留名字和成绩,student.name = exam.name 是两个表中名字一样的

select student. name, exam.math from student,exam where
student.name = exam.name and student.classId = 1;

 【简单易懂】MySQL增删改查(进阶)_第31张图片

4.2.1 内连接

语法

select 字段 from 1 别名 1 [inner] join 2 别名 2 on 连接条件 and 其他条件 ;
select 字段 from 1 别名 1, 2 别名 2 where 连接条件 and 其他条件 ;
select student. name, exam.math from student join exam on
student.name = exam.name and student.classId = 1;

 【简单易懂】MySQL增删改查(进阶)_第32张图片

4.2.2 外连接

外连接分为左外连接和右外连接。如果联合查询,左侧的表完全显示我们就说是左外连接;右侧的表完全显示我们就说是右外连接。

语法

-- 左外连接,表 1 完全显示
select 字段名   from 表名 1 left join 表名 2 on 连接条件 ;
-- 右外连接,表 2 完全显示
select 字段 from 表名 1 right join 表名 2 on连接条件

4.2.3 自连接

自连接是指在同一张表连接自身进行查询。

4.2.4 子查询

子查询是指嵌入在其他 sql 语句中的 select 语句,也叫嵌套查询
单行子查询:返回一行记录的子查询
查询与 不想毕业 同学的同班同学:
select * from student where classes_id=(select classes_id from student where name='不想毕业');
多行子查询:返回多行记录的子查询
案例:查询 语文 英文 课程的成绩信息
-- 使用IN
select * from score where course_id in (select id from course where name='语文'or name='英文');

4.2.5 合并查询

在实际应用中,为了合并多个 select 的执行结果,可以使用集合操作符 union union all 。使用 UNION 和 UNION ALL 时,前后查询的结果集中,字段需要一致。
union
该操作符用于取得两个结果集的并集。当使用该操作符时,会自动去掉结果集中的重复行。
案例:查询 id 小于 3 ,或者名字为 英文 的课程:
select * from course where id<3
union
select * from course where name='英文';
-- 或者使用or来实现
select * from course where id<3or name='英文';
union all
该操作符用于取得两个结果集的并集。当使用该操作符时,不会去掉结果集中的重复行。
案例:查询 id 小于 3 ,或者名字为 “Java” 的课程
-- 可以看到结果集中出现重复数据
select * from course where id<3
union all
select * from course where name='英文';

你可能感兴趣的