[MySQL] 表的增删查改(CURD)

淡淡的烟草味﹌ 2024-04-01 16:33 16阅读 0赞

![87f55a1efef6427f8cbc5ee078ae4b65.gif][]

> ![c4304daa3a1846adadc000eea43efbf4.png][]专栏简介 :MySql数据库从入门到进阶.
> 
> ![1254c5b119a44cf0ae350489fb7736ad.png][]题目来源:leetcode,牛客,剑指offer.
> 
> ![4366a93aaa3142b5a0f16c7a2200339b.png][]创作目标:记录学习MySql学习历程
> 
> ![52a3437a932f4621b25533c330f7c6cb.png][]希望在提升自己的同时,帮助他人,,与大家一起共同进步,互相成长.
> 
> ![14aa2f0648ae44a299b8f9832af57d2c.png][]学历代表过去,能力代表现在,学习能力代表未来!

--------------------

#### 文章目录 ####

1.CURD

2.新增(create)

2.1 单行数据+全列插入

2.2 多行数据+指定列插入

3.查询(Retrieve)

3.1 全列查询

3.2 指定列查询

3.3 查询字段为表达式

3.4 别名

3.5 去重 distint

3.6 排序 order by

3.7 条件查询 where

3.8 分页查询 limit

4.修改(Updata)

5.删除(Delete)

总结

--------------------

## 1.CURD ##

*  注释: SQL中使用 **-- + 描述** 来表示注释.
 *  CURD: 即 C 新增(Creae), U 修改(Updata), R 查询(Retrieve), D 删除(Delete).
 *  操作表时, 增删改操作相对较为单一, 查询操作相对复杂.

--------------------

## 2.新增(create) ##

### 2.1 单行数据+全列插入 ###

成员数量必须和定义表的列数以及顺序一致.

**语法:**

insert into 表名 values(成员1,成员2....);

![a4617f419b7c434faedb5af30ac8d114.png][]

--------------------

### 2.2 多行数据+指定列插入 ###

**语法:**

insert into 表名(指定列1,指定列2...) values(成员1,成员2...),(成员1,成员2..)..;

![6adfeecc3e9049b381274087d6a88cd3.png][]

Tips: 使用datatime插入日期时,日期的格式为 yyyy-mm-dd hh:mm:ss ,另外 now()可以获取当前时间.

![ea7656c47afa4448b7ce022a4f34f9ea.png][]

--------------------

## 3.查询(Retrieve) ##

### 3.1 全列查询 ###

通常情况下不建议使用\* 进行全列查询

>  *  查询的列越多导出查询的数据量越大,可能使硬盘和带宽爆满.
>  *  可能影响到索引的使用

**语法:**

select * from 表名;

**示例:**

![69c2ef3fd8dd479ab58e5af43387d9cc.png][]

--------------------

### 3.2 指定列查询 ###

**语法:**

select 指定列1,指定列2.. from 表名;

**示例:**

![d85a79532de74d9f92eca1afb895bff4.png][]

--------------------

### 3.3 查询字段为表达式 ###

*  Tips:字段为表中定义的列名

**1.表达式不包含字段**

**语法:**

select name,age,10 from Test;

**示例:**

![fa13e37bd50240bb8ca64624ad8b25ae.png][]

--------------------

**2.表达式包含一个字段**

**语法:**

select name,score+10 from Test;

**示例:**

![8fe251a07fc94bd8b331f1b024c85381.png][]

--------------------

**3.表达式包含多个字段**

**语法:**

select name,score+age+10 from Test;

**示例:**

![0aa5312cac144f6d938c36043374dafd.png][]

--------------------

### 3.4 别名 ###

**若查询结果过长, 可给查询结果起一个别名更加直观.**

**语法:**

select 成员1+成员2+.. (as) 别名 from 表名;

**示例:**

![b23bc5933a774dda91928344cd2de370.png][]

--------------------

### 3.5 去重 distint ###

**语法:**

select distinct 列名 from Test;

去重前:

![a3942aa9f5db4ec690e8976c5b55777d.png][]

去重后:

![279072c6609c41079e7f933c07d19f73.png][]

--------------------

### 3.6 排序 order by ###

>  *  asc 升序 (默认)
>  *  desc 降序

**语法:**

select * from 表 (where) order by 列名 (asc/desc);

1.没有order by 字句的查询, 返回的顺序是未定义的, 不要依赖该顺序.

**2.null 数据排序视为比任何数据都小, 升序位于最上面, 降序位于最下面.**

![6605b4c4b3d145639be51f56c755e1dd.png][]

![ad8522d28fb94045ad9e1a5b2156f5a2.png][]

--------------------

**3.使用表达式以及别名排序.**

**语法:**

select 表达式 (as) 别名 from Test order by 别名 desc;

**将学生总成绩按从高到低排序.**

![0de4469fb2b942ed80ad38c6e7275ada.png][]

--------------------

**4.对多个字段进行排序, 排序优先级随书写顺序.**

**语法:**

select 字段1,字段2.. from 表名 order by 字段1 asc/desc ,字段2 asc.desc...

查询同学个门成绩按 语文降序,数学升序,英语降序的顺序来显示.

![ac339e738ebc46a9827554d08478ee36.png][]

--------------------

### 3.7 条件查询 where ###

**比较运算符:**

<table style="width:500px;"> 
 <tbody> 
  <tr> 
   <td>运算符</td> 
   <td>说明</td> 
  </tr> 
  <tr> 
   <td>&gt;,&gt;=,&lt;,&lt;=</td> 
   <td>大于,大于等于,小于,小于等于</td> 
  </tr> 
  <tr> 
   <td>=</td> 
   <td>等于, null 不安全, eg: null==null结果是 null</td> 
  </tr> 
  <tr> 
   <td>&lt;=&gt;</td> 
   <td>等于, null 安全, eg: null==null结果是true(1)</td> 
  </tr> 
  <tr> 
   <td>!=,&lt;&gt;</td> 
   <td>不等于</td> 
  </tr> 
  <tr> 
   <td>between A and B</td> 
   <td> <p>范围匹配, [A,B],</p> <p>如果&nbsp; A&lt;=value&lt;=B .返回true(1)</p> </td> 
  </tr> 
  <tr> 
   <td>in(option,...)</td> 
   <td>如果是 option 中的任意一个,返回true(1)</td> 
  </tr> 
  <tr> 
   <td>is null</td> 
   <td>是null</td> 
  </tr> 
  <tr> 
   <td>is not null</td> 
   <td>不是null</td> 
  </tr> 
  <tr> 
   <td>like</td> 
   <td> <p>模糊匹配,%表示任意多个(包含0个)任意字符</p> <p>_表示任意一个字符.</p> </td> 
  </tr> 
 </tbody> 
</table>

**逻辑运算符:**

<table style="width:500px;"> 
 <tbody> 
  <tr> 
   <td>运算符</td> 
   <td>说明</td> 
  </tr> 
  <tr> 
   <td>and</td> 
   <td>相当于&amp;,多个条件必须都为true, 结果才为true</td> 
  </tr> 
  <tr> 
   <td>or</td> 
   <td>相当于|,任意一个条件为true,结果为true</td> 
  </tr> 
  <tr> 
   <td>not</td> 
   <td>条件为true, 结果为false</td> 
  </tr> 
 </tbody> 
</table>

Tips: 别名只是客户端访问服务器的一个临时表的列名,原表中并不存在别名

*  where 条件可以使用表达式, 但不能使用别名.
 *  and 优先级高于 or, 同时使用时, 需用小括号\{\}包裹优先执行的部分.

--------------------

**1.基本查询**

**语法:**

select 列名1,列名2..from 表名 where 限制条件;

**查询英语成绩低于80分的同学**

select name,english from Test where english<80;

![3004635b6f81442492e33952dde1c019.png][]

**查询语文成绩好于英语的学生**

select name,chinese,english from Test where chinese>english;

![f367017194604bb5a3ff57e2f7b135d9.png][]

**查询总分在200以上的学生**

select name,chinese+english+math from Test where chinese+english+math>200;

![71b32b37fc7d41cabd5532f52da0e5ce.png][]

--------------------

**2.and与or**

**查询语文成绩大于80, 且英语成绩大于80的同学**

select name,chinese,english from Test where chinese>80 and english>80;

![f0445c64c112427897cb83921bea1a49.png][]

**观察 and 和 or 的优先级**

select * from Test where chinese>80 or math>70 and english>70; 
    select * from Test where (chinese>80 or math>70) and english>70;

![0b6f5f6094034bccaa6f75e44bda1a03.png][]

--------------------

**3.范围查询**

**查询语文成绩在\[80-90\]的同学.**

select name,chinese from Test where chinese between 80 and 90;

![90c32732339c4dc69d1755abc6b6537f.png][]

--------------------

**4.in**

**查询语文成绩是78或者89的同学.**

select name,chinese from Test where chinese in(78,89);

![8455cc274f564ce18fdab80dcfffc5ad.png][]

--------------------

**5.模糊查询**

**% 匹配多个字符**

select name from Test where name like "张%";

**\_ 匹配任意一个字符**

select name from Test where name like "张_";

![870488cd183d459b9abcb602f9c1b0f9.png][]

--------------------

**6.null的查询**

**查询数学成绩已知的同学的姓名**

select name,math from Test where math is not null;

![21eeb6b120af4d58968a2b093178e300.png][]

**查询数学成绩未知的同学的姓名**

select name,math from Test where math is null;

![d72ea57e39b94de7b4ebdf254439e531.png][]

--------------------

### 3.8 分页查询 limit ###

该操作日常生活中十分常见,例如:

![807a3eebcd2741e1b79e5d6e4014104a.png][]

**语法:**

--从0开始筛选n个结果
    select...from 表名 (where) (order by) limit n;
    --从s开始筛选n个结果
    select...from 表名 (where) (order by) limit s,n;
    --从s开始筛选n个结果 建议使用该方法
    select...from 表名 (where) (order by) limit n offset s;

**按id分页, 每页三条记录, 分别显示第 1,2,3页.**

select * from Test (where) order by id limit 3 offset 0;
    select * from Test (where) order by id limit 3 offset 3;
    select * from Test (where) order by id limit 3 offset 6;

![2cd935a1bc074a3d9a8d0a4da4c3b177.png][]

--------------------

## 4.修改(Updata) ##

**语法:**

update 表名 set 列名1=值,列名2=值..where 限制条件;

>  *  Tips: 若不加限制条件会修改所在列的所有内容.
>  *  可搭配where,order by,limit 使用

**将张三的语文成绩修改为80**

update Test set math=80 where name="张三";

![b74d75d462ae468fbb5e535bb1680067.png][]

**将李四的数学成绩变为70, 语文成绩变为90.**

update Test set math=70,chinese=90 where name="李四";

![f5d4a946df764a959cc8836e39be47ad.png][]

**为总成绩倒数前三的同学数学成绩加上30分.(升序排列,倒数前三在最前面)**

update Test set math=math+30  order by math+chinese+english limit 3 offset 0;

![dfdd22d62e404f73ac7dc70373d0351f.png][]

--------------------

## 5.删除(Delete) ##

**语法:**

delete from 表名 where 限制条件;

>  *  Tips: 若不加限制条件会修改所在列的所有内容.
>  *  可搭配where,order by,limit 使用

**删除张三的信息**

delete from Test where name="张三";

![0707b47ab62b4548a6b096a3c9775ea1.png][]

**删除整张表**

delete from 表名;

![e632d15f7c1c4f80a7639ee37b05c7ac.png][]

**drop 与 delete 的区别:**

*  drop注重删除结构,delete注重删除数据.

由于字段也属于结构的一部分, 所以可以搭配alter方法来删除.例如:删除students表中的chinese字段.

alter table students drop chinese;

若要删除students表中的所有名叫"张三"的人, 应该使用delete.

[87f55a1efef6427f8cbc5ee078ae4b65.gif]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/92e693b5b7994057b8f9476269f17519.gif
[c4304daa3a1846adadc000eea43efbf4.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/78ca1917d8f242728e972ae344080ff3.png
[1254c5b119a44cf0ae350489fb7736ad.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/62f6c5ca0b6845b8a017ae32ef283198.png
[4366a93aaa3142b5a0f16c7a2200339b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/4e7f3a5679164a179aa1bc1ea6e386d3.png
[52a3437a932f4621b25533c330f7c6cb.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/4935245be8dd4aacb9f72552c412b971.png
[14aa2f0648ae44a299b8f9832af57d2c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/053bd16040f749e0889442b487883510.png
[a4617f419b7c434faedb5af30ac8d114.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/3dbf800acf4642ccbc670f0c989f4723.png
[6adfeecc3e9049b381274087d6a88cd3.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/81d46a2d27a64ad8bd7bdb50cd0dfbb8.png
[ea7656c47afa4448b7ce022a4f34f9ea.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/de162bc19597496382b8043b53b57a75.png
[69c2ef3fd8dd479ab58e5af43387d9cc.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/ab37c7c583ff437fbc01908d6a7eb533.png
[d85a79532de74d9f92eca1afb895bff4.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/5493a4e3192641baa17025181c183a8e.png
[fa13e37bd50240bb8ca64624ad8b25ae.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/da5521a4cc8d4ca7965b0d8007c3230a.png
[8fe251a07fc94bd8b331f1b024c85381.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/bf652b2f678648249af95013b560478c.png
[0aa5312cac144f6d938c36043374dafd.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/207aef98f90742e6aae249d05ee2fa43.png
[b23bc5933a774dda91928344cd2de370.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/49e8185a507b44848f8a37c63111e650.png
[a3942aa9f5db4ec690e8976c5b55777d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/b92345bf5b7b4961a6c1998bf1dc58a7.png
[279072c6609c41079e7f933c07d19f73.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/c029e95f955e4a22b89b9cfbef2f1605.png
[6605b4c4b3d145639be51f56c755e1dd.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/5aa3b72810e1454cb5e00c72ee9e0fa2.png
[ad8522d28fb94045ad9e1a5b2156f5a2.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/c832036a266a45688e6ba1ad009607ec.png
[0de4469fb2b942ed80ad38c6e7275ada.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/fb9dbe1cda2649e19b5a9fae84844df0.png
[ac339e738ebc46a9827554d08478ee36.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/5b4c04e29b3f43f98bb5b94bf76421c5.png
[3004635b6f81442492e33952dde1c019.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/a28ed673e24c4d659fa58d13669a49db.png
[f367017194604bb5a3ff57e2f7b135d9.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/b1a6fb0184024774b6ab22142d59b937.png
[71b32b37fc7d41cabd5532f52da0e5ce.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/9b582aca22c94434956f0a234d22e323.png
[f0445c64c112427897cb83921bea1a49.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/0b1596995ce64e958a9c175e912c6b71.png
[0b6f5f6094034bccaa6f75e44bda1a03.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/5095047e636541f69d65989aa5c1299d.png
[90c32732339c4dc69d1755abc6b6537f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/b95d8412f1524447bb812b0642f2e638.png
[8455cc274f564ce18fdab80dcfffc5ad.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/934fd01b01454af195f602e743b25203.png
[870488cd183d459b9abcb602f9c1b0f9.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/19a5cf55b0364d8abfec02116474e834.png
[21eeb6b120af4d58968a2b093178e300.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/a301de6134e0484584e795f8e168d825.png
[d72ea57e39b94de7b4ebdf254439e531.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/0b788f25c635410bad99399ca53e582d.png
[807a3eebcd2741e1b79e5d6e4014104a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/3bfdf84ec208423f980c32e13a8c235d.png
[2cd935a1bc074a3d9a8d0a4da4c3b177.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/41e85ba2e731451b967475e7931e755a.png
[b74d75d462ae468fbb5e535bb1680067.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/044a7279916c446fa1a978b698cd9140.png
[f5d4a946df764a959cc8836e39be47ad.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/c7614e359ef04fd8b995db40377fd122.png
[dfdd22d62e404f73ac7dc70373d0351f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/6c433314d0034adfb483e7f744b821b9.png
[0707b47ab62b4548a6b096a3c9775ea1.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/bac6b60d88de4992a6824b574d251246.png
[e632d15f7c1c4f80a7639ee37b05c7ac.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/172806f034f041cbbfe0070cf232ee75.png