笔记|MySQL的增删查改【进阶】 mysql|数据库|java|MYSQL数据库

文章目录

- 数据库的约束
- - 约束的类型
  - NOT NULL
  - UNIQUE
  - DEFAULT
  - PRIMARY KEY
  - FOREIGN KEY
  - CHECK
- 表的设计
- - 实体之间的关系
  - - 一对一
    - 一对多
    - 多对多
- 新增
- 查询
- - 聚合查询
  - - 聚合函数
    - - count( )
      - sum( )
      - avg( )
      - max( ),min( )
    - 分组查询
    - - having子句和where子句
  - 联合查询
  - - 内连接和外连接
    - 自连接
    - 子查询
    - 合并查询

数据库的约束

所谓约束就是数据库允许程序员定义一些对数据的限制规则，数据库会在对数据进行操作时按照这些规则对数据进行校验；
约束的本质就是让我们及时发现数据中的错误，更好地保证数据的正确性；

约束的类型
数据库中的约束类型主要有下面几种：

文章图片

NOT NULL

初始情况下，一个表是可以为空的；

文章图片

但当添加了NOT NULL约束以后，被添加了约束的列就不允许为空了：

文章图片

UNIQUE

默认情况下，表中的数据是可以重复的；

文章图片

添加了UNIQUE约束后，被指定了唯一值的某列的每行就不可以重复了；

文章图片

DEFAULT

默认的默认值为NULL；

文章图片

通过default约束可以修改默认值；

文章图片

PRIMARY KEY

主键不可以为空；
主键不可以重复；
一个表中不能有多条主键；

文章图片

主键作用重要，同时唯一且不可重复，为了方便用户的使用，MySQL提供了“自增主键”；

文章图片

对于自增主键，如果用户对自增主键所在的列指定了具体的数值，则认为是手动指定；如果用户指定为NULL，则MySQL按照自增规则自动生成；

文章图片

MySQL中的自增主键，需要记录当前自增的数值，同时保证再次自增之后，主键不可以重复，因此MySQL直接记录当前自增主键的最大值，再进行下一次的自增；

文章图片

FOREIGN KEY

FOREIGN KEY，外键，描述了两张表之间的关联关系；
两张表之间，负责约束的一方，称为父表；被约束的一方，称为子表；

创建2张表：

文章图片

父表为空时，子表不可以插入数据；

文章图片

在外键约束下，每次操作数据时，都会首先触发在父表中的查询，父表中存在才能插入/修改成功；

文章图片

子表和父表之间是相互限制的；
父表对子表的限制是不可以随意插入和修改数据；
子表对父表的限制是不可以随意删除修改数据；

文章图片

相比于一次查询，这种首先查询父表的操作可能会拖慢执行效率，但如果查询操作能够触发索引，却也可以提高执行效率；
因此在建立外键约束的时候，MySQL要求，引用的父表的列，必须是主键或者是受UNIQUE约束，因为它们自带索引，查询速度也就会快一些；

CHECK

直接对表中的值做出限制；

表的设计

表的设计是取决于需求场景的，因此在设计表之前我们首先要明确需求场景；
然后提取出需求中的实体，为每个实体分配一张表，同时需要理清实体与实体之间的关系；

实体之间的关系
一对一

对于实体A而言，只有唯一的实体B与之对应；对实体B而言，只有唯一的实体A与之对应；
例如，一个人只能有一个身份证号，一个身份证号也只对应一个人；

一对多

对于实体A而言，可以有多个实体B与之对应；
例如，一个班级可以有多名学生；

多对多

实体A有多个实体B与之对应，实体B也有多个实体A与之对应；

新增

把查询结果作为新增的数据；
insert into 表名1 select 列名 from 表名2；
从表名2中查询出来的结果的列数和类型必须与表名1匹配，列名不做要求；

//创建一个学生表 create table student( -> id int, -> name varchar(20)); //为学生表插入数据 insert into student values -> (1,'海绵宝宝'), -> (2,'派大星'), -> (3,'蟹老板'); //创建一个学生表2，不在表2中插入数据 create table student2( -> id int, -> name varchar(20)); //将从学生表中查询到的数据插入到学生表2中 insert into student2 select * from student;

文章图片

查询聚合查询
聚合函数 MySQL中常用的聚合函数主要有：

文章图片

count( )

文章图片

count(*)可以查询表中所有数据的数量（包括null值）；
count(列名)查询时不包含null值的情况；

文章图片

MySQL中不正确的空格数量可能会引起错误，要特别注意；

文章图片

sum( )

文章图片

null与数字运算的结果仍为null，所以这里null不参与运算；

文章图片

sum( )参与查询的数据必须为数字，否则就会产生警告;

avg( )

文章图片

max( ),min( )

文章图片

分组查询

把表中的若干行，分成几组；
指定某一列作为分组的依据，分组依据的列值相同，则分为一组；
被划分之后的每个组都可以使用聚合函数；
group by 列；

文章图片

having子句和where子句

进行聚合查询时，可以指定条件进行筛选；
在聚合之前进行筛选使用where子句；
在聚合之后进行筛选使用having子句；

文章图片

聚合之前和聚合之后都进行筛选：
【笔记|MySQL的增删查改【进阶】】

文章图片

聚合前后都进行筛选的情况，其实是首先进行where子句的筛选，然后进行分组查询，最后再进行having子句的筛选；

联合查询

又称多表查询，意思是把多个表的记录合并到一起，综合进行查询；

笛卡尔积：将两张表中的所有记录进行排列组合，穷举出所有可能情况；
针对多个表j进行联合查询，本质上就是先对多个表进行笛卡尔运算；
笛卡尔积的列数，就是原来两张表的列数之和；笛卡尔积的行数，就是原来两张表的行数之积；

单纯进行笛卡尔运算得到的结果中包含了大量的无效数据，为了最终得到一个有用的数据表，我们指定合理的过滤条件（连接条件），挑出有效数据，这个过程就是联合查询的过程；

SQL中计算笛卡尔积：
select * from 表1，表2；
select 列名 from 表1 join 表2；

举个栗子：（查询海绵宝宝的成绩）

create table classes (id int primary key auto_increment, name varchar(20), `desc` varchar(100)); create table student (id int primary key auto_increment, sn varchar(20),name varchar(20), qq_mail varchar(20) , classes_id int); create table course(id int primary key auto_increment, name varchar(20)); create table score(score decimal(3, 1), student_id int, course_id int); insert into classes(name, `desc`) values ('信息工程学院2020级1班', '学习了计算机相关知识'), ('生物农业科技学院2019级3班','学习了生物农业相关知识'), ('数学学院2021级3班','学习了数学'); insert into student(sn, name, qq_mail, classes_id) values ('01','海绵宝宝','xuanfeng@qq.com',1), ('02','派大星',null,1), ('05','蟹老板',null,1), ('06','痞老板','say@qq.com',2), ('07','章鱼哥',null,2), ('09','嘿','foreigner@qq.com',2); insert into course(name) values ('Java'),('中国传统文化'),('计算机原理'),('语文'),('高阶数学'),('英文'); insert into score(score, student_id, course_id) values -- 海绵宝宝 (70.5, 1, 1),(98.5, 1, 3),(33, 1, 5),(98, 1, 6), -- 派大星 (60, 2, 1),(59.5, 2, 5), -- 蟹老板 (33, 3, 1),(68, 3, 3),(99, 3, 5), -- 痞老板 (67, 4, 1),(23, 4, 3),(56, 4, 5),(72, 4, 6), -- 章鱼哥 (81, 5, 1),(37, 5, 5), -- 嘿 (56, 6, 2),(43, 6, 4),(79, 6, 6);