[大数据]《MySQL》增删查改(进阶)

《MySQL》增删查改(进阶)

前言:

本章虽然是SQL进阶,但更多是对于查询方面的进阶学习.同时加入相关表的设计方面的知识

1. 数据库约束

1.1 约束的概念与作用

在没有约束的情况下,数据库对输入的数据限制十分的小,所以引入了约束的概念,使得数据库可以让程序员定义对输入数据的约束条件,数据库在已经被约束的情况下会校验数据,如果校验不通过则直接报错,使得保证数据的安全性.

约束的本质其实就是使得数据报错及时,更好地保证数据的安全性.

1.2 约束的类型

约束的类型共有6种:

1. not null : 指定某列不能存储null值

2. unique : 指定某列不能有重复的值,确保数据的唯一性

3. default : 规定没有给列赋值的数据一个默认值, 数据库中默认的默认值为null.

4. primary key : 主键,为not null与unique的结合,确保某列(或多个列的集合)有唯一标识,使得更容易快速地找到这个表中的一个特定的记录

5. foreign key : 外键,保证一个表中的数据与另一个表的数据能实现匹配.

6. check: 保证数据按照指定的条件进行筛选,但是check 语句在MySQL5系列并不支持.

1.3 not null 约束

一般对于没有进行null的表,我们可以查到:

-- 创建一个普通学生表:
create table exam_result1 (
 id int ,
 name varchar(20),
 chinese decimal (3 , 1),
 math decimal (3 , 1),
 english decimal (3 , 1)
);
-- 查看表结构:
desc exam_result1;

初始情况下,一个表是允许数据为null的,我们可以插入值为null的数据.

但是在数据已经被not null 约束后,则不可以插入值为null的数据.

-- 创建一个普通学生表:
create table exam_result1 (
 id int not null,
 name varchar(20),
 chinese decimal (3 , 1),
 math decimal (3 , 1),
 english decimal (3 , 1)
);
-- 查看表结构:
desc exam_result1;

插入为null 值,则会报错:

1.4 unique 约束

在没有unique约束的表中,我们可以插入许多相同的数据.

-- 创建一个成绩结果表
create table result (
 id int,
 name varchar(20),
 chinese decimal (3 , 1),
 math decimal (3 , 1),
 english decimal (3 , 1)
);
-- 插入3条数据相同的数据
insert into result values(1,"张三",99,99,99),(1,"张三",99,99,99),(1,"张三",99,99,99);
-- 查询数据:
select * from result;
-- 查看表结构:
desc result;

-- 创建一个id被unique约束的成绩结果表
create table result (
 id int unique,
 name varchar(20),
 chinese decimal (3 , 1),
 math decimal (3 , 1),
 english decimal (3 , 1)
);
-- 插入3条数据相同的数据
insert into result values(1,"张三",99,99,99),(1,"张三",99,99,99),(1,"张三",99,99,99);
-- 插入3条id不同的数据
insert into result values(1,"张三",99,99,99),(2,"张三",99,99,99),(3,"张三",99,99,99)
-- 查询数据:
select * from result;
-- 查看表结构:
desc result;

当插入id相同的数据时,会报错:

插入id不同,但是其他数据相同时.插入成功:

表结构:

结论: 被unique约束的列,必须保持数据的相异性,插入相同数据则会报错.

1.5 default 约束

default 比较简单,作用在于为没有赋值的列给予一个自定义的默认值.

对于一个普通表而言,数据默认的默认值为null

-- 创建一个修改default值的表
create table result (
 id int unique,
 name varchar(20) default "未命名",
 chinese decimal (3 , 1),
 math decimal (3 , 1),
 english decimal (3 , 1)
);

表结构:

1.6 primary key 约束

primary key约束后 ,这个列则作为主键:

• 不能为空,相当于 not null

• 不能重复,相当于 unique

• 一个表只能有一个主键

-- 创建一个有主键的表
create table result (
 id int primary key,
 name varchar(20),
 chinese decimal (3 , 1),
 math decimal (3 , 1),
 english decimal (3 , 1)
);

查看表结构:

自增主键:在主键后面修饰 : auto_increment,功能如下

• 如果写指定内容,这是手动指定,如果写入null,则会按mysql的自增规则自动生成

• 自增规则为,保存目前所有数据的最大值,下一个自增则为最大值+1.,目的也是为了保证数据不出现重复性的问题.

当然自增主键的缺点也是较为明显的:当出现海量数据时,则会出现自增主键数字十分庞大的情况.

关于自增主键在数据库分布式部署的问题及解决方法

如果数据库是分布式部署的话:

则会出现问题: 数据库必须多个节点的数据结合在一起才是一个完整的数据库,mysql在设置自增主键时,只能确保自己的节点的数字是唯一的,而不能确保多个节点的数字都是唯一的.这就会导致数据库出现重复性数据.

解决方案一(代价较大)

在生成数据时,使多个节点直接通过网络相互沟通,在了解彼此情况之下,再来生成唯一id.

但这样的代价就十分明显了,浪费了大量的时间在于沟通,使得效率十分低下.

解决方案二

通过时间撮形成随机数,在拼接上主机数,再在同一时间撮情况下,拼接上一个随机值

即

唯一id = 时间撮(ms) + 主机号码 + 随机数 (不是相加,而是字符串拼接)

• 保证了不同时间进入数据库的唯一id不同

• 保证了同一时间进入数据库但存入不同主机的唯一id不同

• 保证了同一个时间进入数据库并存入相同主机的唯一id不同(随机值的作用就体现在这里)

当然,如果同一个时间进入数据库并存入相同主机的随机值还相同的话,确实理论上会产生相同的id.但是概率极低,工程一般通过设置十分巨大的随机数,已达到几乎不可能出现这种情况.

1.6 foreign key (外键)

在商品表与仓库表之间

先写完所有的列,在进行外键约束语句.

create table warehouse(
  wareid int unique;
  name varchar(20);  
);
create table product(
  id int primary key auto_increment,
  name varchar(20),
  wareid int; 
  foreign key (wareid) references warehouse(wareid);
);

形成外键关联后, 仓库表则称为父表,商品表则称为子表

• 如果父表为空,则直接往子表插入信息则会报错.

• 在外键的约束下,子表插入数据时,都会触发父表的查询,父表中出现才会查询成功

• 父表对子表产生的约束为:不能随意插入与修改.(需要根据父表内容)

• 子表对父表产生的约束为:不能随意修改与删除.(避免影响子表数据)

如何解决电商表与订单表的下架问题

在商品表为父表,订单表为子表

商品表(goodsid,name,price)

订单表(orderid,name,goodsid)

如何下架商品表中goodsid数据为100的商品,但是不影响订单表的数据呢?

解决方法:在商品表设置一个是否上架的判断.

即:

商品表(goodsid,name,price,ok)

ok作为判断是否上架的依据

订单表(orderid,name,goodsid)

1.7 check 约束

直接约束:

-- 判断学生表中的性别内容只能为男或者女
check (sex = "男" or sex = "女");

2 表的设计

2.1 一对一

其实采用的方式就比较简单,直接用一个字段将两个表之间联系起来即可

例如在 在日常网站中,一个用户对应一个账号,一个账号对应一个用户,就是属于一对一的关系.

一般都是直接利用一个额外的id来关联:

create table user(
  id int,
  name varchar(20),
);
create table account(
    acc int,
    password int,
    userid  int
);

2.2 一对多

当一个表与另一个表的多行数据有关时,也是采用字段的方式关联起来

例如,学生只有一个班级,而一个班级有多个学生,这就属于一对多的关系.

create table student(
  id int,
  name varchar(20), 
  classid int

);
create table class(
    classid int,
    name varchar(20) 
);

2.3 多对多

当A表与B表是一对多的关系,并且B表对A表也是一对多的关系时,就是一对多的关系

例如: 学生与课程,一个学生可以选择多个课程,一个课程可以被多个学生选中,我们一般采用一个中间表直接产生关联.

create table student(
  id int,
  name varchar(20)
);
create table course(
    courseid int,
    name varchar(20) 
);
create table student_course(
    studentid int,  
    courseid int
);

将查到的数据直接插入表

insert into 表名 select 列名 from 表名;

先执行查询操作,查询到的结果,再插入到另一个表里.

需要保证查询结果的临时表与插入表的列的类型是一一对应的.

3. 聚合查询

聚合查询是针对行进行计算并查询的.

3.1 聚合函数

• count() 返回查询到的数据的数量

  当一个表中有null值时.会出现以下情况

  • 对于全列查询,如果这一行的数据全为空或者某一行为空,也会计算进去这一行.

  • 对于对某一列进行查询,如果这一行的这列数据为null,则不会计算进去这一行.

    

    注意count() 要紧挨着哦,不然会语法错误.

• sum() 返回查询到的数据的总和

  • 如果某行中需要计算的列有null,对于null 不会影响计算结果

• avg() 返回查询到的数据的平均值

  • avg不会计算进null的值,也只除以非null 的行数.确保平均值的正确

• max() 返回查询到的数据的最大值

• min() 返回查询到的数据的最小值

3.2 分组查询

使用group by 子句实现.

将表中的若干行,分为多组,通过某一列作为分组依据,分组依据这一列相同的值,则归为一组.

例如:学生成绩表.查询男女各自的最大值,最小值,平均值

select gender,max(score),min(score),avg(score) from studnet group by gender;

分组查询的条件筛选

• 在聚合查询之前进行筛选,针对筛选后的结果再进行聚合.使用where 子句

  • 去除了"李四"同学后的学生成绩表.查询男女各自的最大值,最小值,平均值

    select gender,max(score),min(score),avg(score) from studnet where name!="李四"group by gender;
    

• 在聚合之后,进行筛选,使用having 子句

  • 查询男女之间平均分大于80性别

    select gender,max(score),min(score),avg(score) from studnet group by gender having avg(score) > 80;
    

• 同时使用

  • 查询去除了"李四"同学后的学生成绩表,查询男女各自的最大值,最小值,平均值且男女之间平均分大于80性别.

    select gender,max(score),min(score),avg(score) from studnet where name!= "李四"group by gender having avg(score) > 80;
    

4. 联合查询

4.1 笛卡尔积的概念

对多份数据进行全排列

例如:

• 笛卡尔积的列数 = 两个表的列数之和

• 笛卡尔积的行数 = 两个表的行数之积

因此在实际开发中,由于笛卡尔积导致的表数据会巨大所以实际开发中,多表查询操作一定要慎重.

4.2 多表查询

select * from 表1,表2,表3.......;
select * from 表1 join 表2 join 表3 ....on.....

创建多个表:

create table classes(
    id int,
    name varchar(20),
    descrime varchar(20)
);
create table student (
   id int primary key auto_increment,
   sn int unique,
   name varchar(20) default 'unkown',
   qq_mail varchar(20),
   classes_id int
);
create table course(
    id int,
    name varchar(20)
);
create table score (
   id int primary key auto_increment,
   score decimal(3, 1),
   student_id int,
   course_id int
);
insert into classes(name, descrime) values 
('计算机系2021级1班', '学习了计算机原理,c语言'),
('中文系2021级1班','学习了中国传统文学'),
('自动化2021级1班','学习了机械自动化');

insert into student(sn, name, qq_mail, classes_id) values

('09982','李一','xuanfeng@qq.com',1),
('00835','李二',null,1),
('00391','李三',null,1),
('00031','李四','xuxian@qq.com',1),
('00054','李五',null,1),
('51234','李六','say@qq.com',2),
('83223','李七',null,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),
(80, 7, 2),(92, 7, 6);

4.2.1 多表查询实例

查询"李一"同学的成绩:

1. 先查询学生表与成绩表的笛卡尔积

   select * from student,score;
   

   

• 十分巨大

2. 再过滤掉无用重复数据

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

   

3. 再查找许仙同学的成绩

   select * from student,score where student.id = score.student_id and name = "李一";
   

   图片4.18

4.2.2 链接方式:

1. 内链接:

   只提取两表中互相对应的数据进行多表查询

   

   select 字段 from 表1 别名1  join 表2 别名2 on 连接条件 and 其他条件;
   
   select 字段 from 表1 别名1,表2 别名2 where 连接条件 and 其他条件;
   

2. 左外链接:

联合查询，左侧的表完全显示我们就说是左外连接

![在这里插入图片描述](https://img-blog.csdnimg.cn/be9e325eac9146cdbb9ee4738b21a579.png#pic_center)


```sql
select 字段名  from 表名1 left join 表名2 on 连接条件;

3. 右外链接:

   联合查询，右侧的表完全显示我们就说是右外连接

   

   select 字段 from 表名1 right join 表名2 on 连接条件;
   

4. 自链接:

   自己与自己链接,一般用于行比较

4.2.3 子查询:

子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询

• 查询李四的同班同学: (单列查询)

  select * from student where classes_id=(select classes_id from student where 
  name='李四');
  

• 查询语文和英语的课程成绩信息: (多列查询)

  select * from score where course_id in (select id from course where 
  name='语文' or name='英文');
  

4.2.4 合并查询:

为了合并多个select的执行结果，可以使用集合操作符 union，union all。使用UNION

和UNION ALL时，前后查询的结果集中，字段需要一致

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

-- 两者相同


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

使用union会去重,union all不会去重

总结:

内容相对复杂,需要重点关注数据库约束和聚合与联合查询.

对于子查询与合并查询在实际开发中较少使用,但仍然要会使用.

表的设计一般较少使用,在学校课程中会涉及ER图的使用,有兴趣可以了解一下ER图.