【MySQL】数据库的增删查改

---

前言

MySQL 最重要的部分就是在增删查改这四个操纵上，我们在实际的开发中也会频仍运用到这些操纵，尤其是查找，这部分有很多门道在里面，必要好好明确，多敲几遍代码认识认识
（由于博主本人在写博客的时候觉得 sql 关键字大写太累眼，故后续的 sql 语句将全部使用小写，因为它不区分大小写）

---

1. 新增

插入关键词：insert

---

1.1 全插入

1. insert [into] 表名 values (值，值 ......);

复制代码

• 这里演示的是全插入，及插入的值要和表对应列的数量，数据类型以及序次相匹配
  
• into 在此可以省略，但博主并不发起
  

在插入前我们要先选定命据库，然后可以用 desc 表名 先查察表的结构

接下来我们就可以向 student 插入一条数据，并查询表里的内容

1. -- 演示
2. insert into student values (1, '张三', 19);

复制代码

  使用 select * from 表名 就可以查询表的实际内容，在下面的查询知识点中我们会介绍，这里提前使用一下
  

这样就成功插入了一条数据 ~
那如果我们在插入数据时，缺乏一些信息，比如我们知道 id 为 2 的人叫 “李四”，但是并不知道他的年事，这时候该怎么办？
而且一行一行的插入效率很低，有什么办法可以提高插入效率吗？

---

1.2 指定某些列名插入

1. insert into 表名 (列名，列名 ......) values (值，值 ......);

复制代码

• 插入时也是要和指定列的数据类型以及序次相匹配，没有指定到的列如果有默认值，那就是默认值；如果答应为 null，那就是 null；如果前面的都不满足，则会报错
  

1. -- 演示
2. insert into student (id, name) values (2, '李四');

复制代码

---

1.3 多行插入

1. insert into 表名 values (值，值 ......), (值，值 ......), ......;

复制代码

• 非常简单，在后面多加几个括号就行，其他规则和上面同等
  

1. -- 演示
2. insert into student values (3, '王五', 20), (4, '赵六', 21);

复制代码

---

1.4 边查询边插入

1. insert into 表1 select * from 表2;

复制代码

• 要求两张表的列数、类型、序次必须要相匹配
  
• 比力少用，相识即可
  

---

2. 约束

对于数据库存储的数据，我们每每会做出一些“限制”“要求”，防止错误的数据被插入到数据表中。MySQL 中定义了一些维护数据库完整性的规则，一样平常叫做“约束”，常见的约束类型如下
约束类型说明非空约束：not null指定某列不能存储 null 值唯一性约束：unique指定某列的每一行必须由唯一值默认值约束：default没有给某列赋值时就为默认值主键约束：primary key用于唯一的标识表中的每一行，相当于 not null 和 unique 的结合外键约束：foreign key用于在两个表之间建立链接，确保数据的引用完整性check约束：check用于限制某列中的值必须满足特定的条件   这些约束都是用在创建表的时候跟在某几列的后面，用来约束插入数据时的数据正当性，因此在建表前要先计划好列的约束
  

---

2.1 非空约束

创建表时，指定某列不能存储 null 值，而且修改时也不能为空

1. -- 演示
2. -- 创建一张包含 id、name 的学生表，要求 id 非空
3. create table student (
4.         id int not null,
5.     name varchar(20)
6. );

复制代码

---

2.2 唯一性约束

创建表时，指定某列的每一行必须由唯一值。在插入大概修改前会查询该列在表中的每一行，若重复就会报错

1. -- 演示
2. -- 创建一张包含 id、name 的学生表，要求 id 非空，name 非空且不能重名
3. create table student (
4.         id int not null,
5.     name varchar(20) unique not null
6. );

复制代码

---

2.3 默认值约束

在插入数据时，如果不指定该列的值，那就会主动酿成默认值

1. -- 演示
2. -- 创建一张包含 id、name、age 的学生表，要求 id 非空，name 非空且不能重名，age 默认值为 19
3. create table student (
4.         id int not null,
5.     name varchar(20) unique not null,
6.     age int default 19
7. );

复制代码

我们在使用 desc 表名 的时候，看到的 default 指的就是默认值

---

2.4 主键约束

primary key 表现主键，用于唯一的标识表中的每一行，它会主动成为非空的，并且每个值都是唯一的。例如一个学生表中，学生的学号 id 就可以设置为主键，因为一个班级里的学生学号不可能重复

1. -- 演示
2. -- 创建一张包含 id、name、age 的学生表，要求 id 为主键，name 不能重名，age 默认值为 19
3. -- 方法一：跟在某一个字段后面
4. create table student1 (
5.         id int primary key,
6.     name varchar(20) unique,
7.     age int default 19
8. );
10. -- 方法二：在最后设置主键（常用于多个主键的设置）
11. create table student2 (
12.         id int,
13.     name varchar(20) unique,
14.     age int default 19,
15.     primary key (id)
16. );

复制代码

再来使用 desc 表名 来查察表的结构，能发现跟之前的“素表”有很多差别点
特点：

• 主键是一个表的身份标识，一样平常不答应一个表中同时有多个主键。不过在特殊情况下也会有把多个列共同作为一个主键，也叫做联合主键
  
• 主键在插入大概修改的时候也会触发查询，若存在重复或为 null ，就会报错
  
• 主键是 not null 和 unique 的结合，不必要再用 not null 了
  

对于整数类型的主键，我们常搭配自增长 auto_increment 来使用。在插入数据对应字段不给值时，数据库会主动分配，使用原先的数据最大值 + 1

1. -- 演示
2. -- 创建一张包含 id、name 的学生表，要求 id 为主键搭配 auto_increment，name 不能重名
3. create table student (
4.         id int primary key auto_increment,
5.     name varchar(20) unique
6. );
8. -- 向表中插入数据，id 不指定值，name 为 '张三'
9. insert into student(name) values ('张三');
11. -- 向表中再两组插入数据，id 为 100，name 为 '李四';
12. -- 第二组 id 不指定，name 为 '王五'   
13. insert into student values (100, '李四');
14. insert into student(name) values ('王五');

复制代码

这就是自增长 auto_increment 的用法

---

2.5 外键约束

外键约束用于在两个表之间建立链接，关联其他表的主键大概唯一键，确保数据的引用完整性。此时有两张表，一张班级表，包罗班级号等信息；另一张学生表，上面也有班级号，表现该学生来自哪个班级。此时学生表上的班级号就必须要跟班级表上存在的班级号对应上，这时就必要外键来约束学生表上的班级号，制止出现错误或不存在班级号

1. ......,foreign key(本表中的列) references 主表表名(被引用的列)

复制代码

• 外键我们一样平常习惯放在末了写，可以同时设置多个外键
  
• 设置外键的表叫做“子表”，被引用的表叫做“父表”，父表子表的列都是要真是存在的，父表中的被引用的列的数据会制约子表中的数据
  

1. -- 演示
2. -- 创建一张包含班级号、班级人数的班级表，要求班级号为主键，班级人数为非空
3. create table class (
4.     class_id int primary key,
5.     class_number int not null
6. );
8. -- 再创建一张包含学号、学生姓名、学生所属班级的学生表，要求学号为主键，学生姓名非空,班级号和 class 表建立外键
9. create table student (
10.         stu_id int primary key,
11.     stu_name varchar(20) not null,
12.     class_id int,
13.     foreign key(class_id) references class(class_id)
14. );

复制代码

1. -- 演示
2. -- 再往 class 表中插入几组数据，接着往 student 表中插入数据，检验外键约束
3. insert into class values (101, 50), (102, 48), (103, 52);
4. -- 合法插入
5. insert into student values (1, '张三', 101), (2, '李四', 102);

复制代码

1. -- 演示
2. -- 非法插入，此时我们往学生表中故意插入错误的班级号
3. insert into student values (3, '王五', 201);

复制代码

因为 class 表中没有 201 号班级，所以数据库会报错，这就是外键的作用
   在设置外键后，我们就无法直接删除父表，因为此时外键还在发挥作用，所以只能先删子表，再删父表（该操纵十分危险，不推荐这样做）
  

---

2.6 check 约束

   因为 MySQL 中并不能直接支持 check 约束，所以这里仅作简单介绍
  check约束确保数据库表中某个列的值必须满足特定的条件。这个条件是一个布尔表达式，只有当表达式的效果为 true 时，插入或更新的数据才会被答应

1. -- 演示
2. -- 创建一张包含学号、学生姓名、年龄的学生表，要求学号为主键，学生年龄必须大于等于 18
3. create table student (
4.         id int primary key,
5.     name varchar(20),
6.     age int,
7.     check (age >= 18)
8. );

复制代码

• check 约束中的表达式可以是任何有效的布尔表达式，包括比力操纵符、逻辑操纵符、算术运算等
  
• check 约束有助于保证数据的正确性和同等性，但过多的使用可能会对数据库性能产生影响，因为数据库必要在每次插入或更新数据时检查这些约束
  

---

2.7 外键的逻辑删除

   场景：一家卖文具的店肆，店家手头上有两张数据表，分别是商品表（goods_id，goods_name …），订单表（order_id， …， goods_id）；订单表上有商品表上对应的商品编号，即两张表的 goods_id 建立了外键
  店家之前在买一款铅笔，过阵子他发现该笔的销售额十分低，想要下架。此时，下架这个操纵要怎么实行
  错误的想法：直接把商品表上该款铅笔的商品编号给删掉不就好了
but，这样操纵的话，之前卖出去的该款铅笔的订单要怎么处置惩罚，它可不能删除，而且两张表又使用外键通过商品编号联系到了一起，无法直接删除父表中的商品编号
正确的做法：在创建商品表之初，就再多计划一列商品状态 goods_state，上架为 1，下架则为 0，这样既简单又轻松，不必要删除任何东西
这就是所谓的逻辑删除，所以所以，在创建表之前，我们就应该把多数情况思量进去，这样可以大大减少后期改动，降低成本

---

3. 查询 - 初阶

查询关键词：select
   对于全部查询，生成的都是虚表，及暂时数据，不会影响到原始数据
  

---

3.1 全列查询

1. select * from 表名;

复制代码

• 我们在上面展示的查询操纵就是全列查询，它可以表现表里的全部行全部列
  
• 但是在数据量大的时候不发起使用，因为该操纵很吃体系资源，而且查询速率也会很慢，严肃的乃至会让整个数据库挂掉，照旧有点危险的
  

---

3.2 指定列查询

1. select 列名，列名 ...... from 表名;

复制代码

照旧上面的这个表，此时我们只想查询姓名和年事这两列

1. -- 演示
2. select name, age from student;

复制代码

---

3.3 指定表达式查询

1. select 表达式 from 表名;

复制代码

想象一个景象：我们想要知道学生是否成年，就可以在查询时让年事都减 18 岁，通过正负零来加速判定。这时候就可以使用指定表达式查询（意思就是边查边计算，一些简单的加减乘除）

1. -- 演示
2. select name, age - 18 from student;

复制代码

我们可以发现，除了李四我们在插入时没给他添加年事之外，其他学生都是已成年

---

3.4 别名查询

1. select 列名 [as] 别名 from 表名;

复制代码

在上面的指定表达式查询中，age - 18 这个表达式酿成了列名，在表中非常破坏美感。别发急，sql 也思量到了这一点，它答应我们使用as 来给列起别名（当然，as 其实也可以省略，但是本人非常不发起）

1. -- 演示
2. select name, age - 18 as new_age from student;

复制代码

你看看，是不是雅观多了~

---

3.5 去重查询

1. select distinct 列名 from 表名;

复制代码

多个行的数据如果出现雷同的值，就只会保存一份

1. -- 演示
2. -- 为了演示，我们再插入一组数据
3. insert into student values (5, '周七', 20); -- 此时周七和王五的年龄相同
4. -- 去重查询年龄
5. select distinct age from student;

复制代码

留意：在使用去重操纵时，如果 distinct 修饰多列时，则必要多列里有完全雷同的数据时才能去重

1. -- 演示
2. select distinct name, age from student;

复制代码

这里就是因为名字不一样而导致去重失败

---

3.6 排序查询

1. select 列名 from 表名 order by 列名 [asc | desc];

复制代码

• 这里 order by 后面的列名意思是以该列为准则来排序
  
• asc 为升序（从小到大），desc 为降序（从大到小）
  
• 如果列名后不指定排序规则不写，那就默认是 asc
  
• 对于中文字符，排序的大小关系取决于该数据库的配置和所使用的排序规则；我们更常使用具体数字大小来排序，而如果出现null，它参与任何运算的效果依然是 null，视为比任何值都小
  

1. -- 演示
2. select name, age from student order by age asc;

复制代码

• 留意：没有 order by 子句的查询，返回的序次都是随机未定义的，不能信托这个序次
  
• 如果排序时两个数据相称，那这两行数据的序次就是不可猜测的，如上图中的王五和周七
  

而且我们也可以对多个字段举行排序，排序的优先级根据誊写的序次

1. -- 演示
2. -- 插入一组数据，id 为 1，年龄为 20
3. insert into student values (1, '郑二', 20);
4. -- 再来对 id 和 age 一起排序（id 在前，就先对 id 排序，如果 id 相同，则再根据 age 来排序）
5. select id, name, age from student order by id, age asc;

复制代码

---

3.7 条件查询

条件查询关键字：where

1. select 列名 from 表名 where 条件;

复制代码

原理：条件查询会遍历表里的每一行记录，把每一行的数据都带入条件中。若建立，则放入效果聚集中，若不建立，就跳过，末了打印出效果聚集 —— 建立的行所组成的暂时表
   从原理中我们也可以看出，当数据量非常大时，条件查询的效率会变得很低。但是别担心，MySQL 中另有一个名叫索引的机制，有了它加速查询的效率，后面我们也会讲到索引
  where 后面的条件一样平常是由列名+运算符组成的子句（也可以是表达式大概列的别名）
在 sql 中，运算符我们之前学到的 C语言 以及 Java 类似，但有些许差别，大体可以分为两种
比力运算符
运算符说明>、<、>=、<=大于、小于、大于等于、小于等于=等于，但是 null = null 的效果为 null<=>等于，但是 null = null 的效果为 true!= 大概 <>不等于，且不能用于 null 的判定is null等于 null 就返回 trueis not null不等于 null 就返回 truein (a1、a2 …)筛选匹配，如果等于 in 里面的任一值，就返回 truebetween a1 and a2范围匹配，[a1，a2] 左闭右闭，如果在这个范围内，则返回 truelike模糊匹配，% 匹配零个或多个任意字符，_ 严格匹配一个任意字符

• 等于为 =，要跟 Java 的 == 区分开
  
• 判定是否等于 null 时，可以使用 <=>，也可以用 is null；判定不等于 null 时， 只能使用 is not null，不可以用 != 和 <=>
  
• like 是一个比力低效的操纵，使用要节制
  

---

   

• 筛选匹配：in 的使用
  

1. select 列名 from 表名 where 列名 in (a1、a2 ......);
3. -- 演示
4. -- 查询学生表中年龄为 20 和 21 的学生
5. select * from student where age in (20,21);

复制代码

---

1. -- 2. 范围匹配：between 的使用
2. select 列名 from 表名 where 列名 between a1 and a2;
4. -- 先插入三组数据：id 为 6、7、8，姓名为 '张三三'、'三张三'、''，年龄为 18，19，22
5. insert into student values (6,'张三三',18), (7,'三张三',19), (8,'',22);
7. -- 演示
8. -- 查询学生表中年龄为 18 到 20 的学生
9. select * from student where age between 18 and 20;

复制代码

---

1. -- 3. 模糊匹配：like 的使用
2. select 列名 from 表名 where 列名 like '[% | _]';
4. -- 演示
5. -- 查询学生表中姓名以'张'为开头的学生
6. select * from student where name like '张%';
8. -- 查询学生表中姓名以'三'为结尾的学生
9. select * from student where name like '_三';

复制代码

---

1. -- 演示
2. -- 查询学生表中姓名出现'张'字的学生
3. select * from student where name like '%张%';
5. -- 查询学生表中姓名为零个字符的学生且 id 为 8 的学生（and 下面会讲到）
6. select * from student where name like '%' and id = 8;

复制代码

   总结：
① 张% —— 查询以 ‘张’ 开头的
② %张 —— 查询以 ‘张’ 结尾的
③ %张% —— 查询包罗 ‘张’ 的
  

---

逻辑运算符
运算符说明and与，多个条件都为 true，就返回 trueor或，任意一个条件为 true，就返回 truenot非，条件为 true，就返回 false

• and 的优先级高于 or，但是在使用 and 和 or 的时候，用小括号来确定实行序次更加靠谱（养成一个好习惯）
  

1. -- 演示
2. -- 查询学生表中名字里有 '张' 字而且年龄小于 20 的学生
3. select * from student where name like '%张%' and age < 20;

复制代码

---

1. -- 演示
2. -- 查询学生表中年龄小于 19 或者大于等于 20 的学生
3. select * from student where age < 19 or age >= 20;

复制代码

---

3.8 分页查询

1. -- 从 0 开始，筛选 n 条结果
2. select 列名 from 表名 limit n;
4. -- 从 s 开始，筛选 n 条结果（表中起始下标为 0）
5. select 列名 from 表名 limit s, n;
7. -- 更建议使用下面这种方式，每次查询 n 条结果，每次偏移量为 s
8. select 列名 from 表名 limit n offset s;

复制代码

• 分页查询可以限制一次查询最多能查出多少条效果
  
• 使用场景：当数据量非常多的时候，使用 select * 一次性全部展示出来效率很低，而且用户查询也不方便
         此处展示的就是分页查询在实际场景中的用法
     
  
  
  

1. -- 演示
2. -- 查询年龄前三小的学生信息，要排除为 null 的情况
3. select * from student where age is not null order by age asc limit 3;
5. -- 按 id 进行分页，每一页 3 条记录，分别显示第 1、2、3 页，若 id 相同，再按照 age 排序
6. select * from student order by id, age asc limit 3 offset 0; # 从第 0 条查起
7. select * from student order by id, age asc limit 3 offset 3; # 从第 3 条查起
8. select * from student order by id, age asc limit 3 offset 6; # 从第 6 条查起

复制代码

   由上面的例子我们也可以看出，当条件、排序和分页一起查询的时候
  序次应该是：select 列名 from 表名 where 条件 order by 列名 [asc|desc] limit n offset s
  

---

4. 查询 - 进阶

   也不算什么进阶，单纯是上面的小标题太多了，分开写好看点