MySQL表的增删查改

1. CRUD

CRUD即增加（Create），查询（Retrieve），更新（Update），删除（Delete）四个单词的首字母缩写

2. 新增（Create）

2.1 单行数据 + 全列插入

语法：

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

其中，后面的值要与前面的字段一一对应

如果列和值的个数不匹配就会报错，且这条记录无法写入数据库

如果插入的值包括了这个表中的所有的列，那么可以简写成下面的形式

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

虽然不用在表名后面指定列名，在values列表中按表中定义字段的顺序设置相应的值

2.2 指定列插入

在新增wangwu这个记录时，没有去指定id这一列，id这一列就会用默认的值NULL去填充

2.3 多行插入

语法：

insert into 表名[(指定列...)] values (值...), (值...)...;

2.4 插入查询结果

语法：

insert into 目标表名 [指定列 [，指定列...] ] select ...

将旧表中查询出来的指定列插入到新表中

3. 查询（Retrieve）

3.1 全列查询

语法：

select * from 表名；

*是一个通配符，表示要查询表中的所有列

注：全列查询在生产环境中是一个非常危险的操作，因为在生产环境中，一个表中的数据量可能会很多很多，有可能达到TB级

1. 当一个查询开始的时候，磁盘需要开销，网络也需要开销
2. 如果这一条语句开始执行，那么就有可能把服务器资源吃完，其他程序或是数据库操作就要等待当前SQL语句执行完之后，才能继续执行

因此，不加任何限制的查询在生产环境中不要使用

3.2 指定列查询

首先，创建一个exam表，如下：

语法：

select 列名[, 列名]... from 表名

示例：只查询id，name，语文成绩

3.3 查询字段为表达式

1. 表达式不包含字段
   
   数值类型，效果就是让所有的列中都包含一个表达式中的值

2. 表达式中包含一个字段
   
   将所有学生的英语成绩在原来的基础上加10分

3. 表达式包含多个字段
   
   查询所有学生的总分

3.4 别名：distinct

对于上面查询总分时，字段chinese + math + english太长了，我们可以用一个别名去替代它

其中as是一个关键字，可以省略

select id, name, chinese + math + english 总分 from exam; //省略as
select id, name, chinese + math + english '总分' from exam; //可以给别名加上单引号

注意：如果别名中包含空格，就必须要用单引号包起来！

select id, name, chinese + math + english '总 分' from exam;

另外，我们的表中本来是没有总分这一列，所以通过表达式查询出来的结果集是通过一个临时表返回给我们的，执行完之后临时表就删除了。在MySQL中，所有的查询结果都会通过临时表返回给用户

3. 5 去重

去重，即去掉查询结果中完全一致的结果，只保留一条，需要用到关键字distinct
语法：

select distinct 列名[, 列名...] from 表名

示例：

1. 先只查询数学成绩
   
   可知，有两个98.0

2. 再去使用distinct关键词去重
   
   发现，这时98.0只剩下一个了，也就达成了去重的效果

3. 注意，在查询结果中，每一列都相同的才认为它们是重复数据
   查询时加上id这一列
   

4. 再去重
   
   不难发现，依旧是七条记录，这就说明了在去重是，只有查询结果中所有的列都相同才会被认定为重复记录，且去重后，重复的记录只保留一条

3.6 排序：order by

使用order by子句后，查询出来的结果会按照指定的规则排序
排序规则：

1. asc：升序
2. desc：降序
3. 默认是升序

注意：我们在查看表结果时用到的desc是describe（描述），而在排序中desc是descend（下降）

语法：

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

示例：

1. 按语文成绩从高到低排序（降序）
   
2. 对数学成绩进行升序排序
   
3. 对数学成绩进行默认排序
   
   没有写排序规则的时候，默认就是升序排序，但是强烈建议明确指定排序规则

---

• NULL的数据排序，视为比任何值都小，升序出现最上面，降序出现在最下面

先插入一条带有NULL数据的记录

1. 对英语成绩进行升序排序
   

2. 对英语成绩进行降序排序
   

---

• 使用表达式及别名排序

1. 对总分进行降序排序
   
2. 排序的时候也可以使用别名，使生成的临时表更加简洁
   
   注意，孙大圣的语文和数学是有成绩的，但是总分却是一个NULL

MySQL中NULL比较特殊

1. 不论和什么值进行运算，返回值都是NULL
2. NULL始终被判定为false

---

• 可以对多个字段进行排序，排序的优先级与书写顺序相关

order by 列名[asc | desc], 列名[asc | desc]...

先对数学降序排序，再按语文升序排序，再按英文进行升序排序

先按数学成绩进行降序排序；在数学成绩相同时，按照语文成绩升序排序；在数学和语文成绩相同时，按照英语成绩升序排序

3.7 条件查询：where

可以通过一些运算符，比如逻辑运算符和比较运算符，指定一些条件，过滤掉不符合跳进啊的记录，把符合条件的记录返回给用户

比较运算符：

运算符	说明
>,>=,<,<=	大于，大于等于，小于，小于等于
=	等于，NULL不安全，例如NULL = NULL的结果时NULL
<=>	等于，NULL安全，例如NULL<=>NULL的结果时TRUE(1)
!=, <>	不等于
between a0 and a1	范围匹配，[a0, a1]，如果a0 <= value <= a1，返回true（1）
in(option,…)	如果时option中的任意一个，返回true(1)
is NULL	是NULL
is not NULL	不是NULL
like	模糊匹配。%表示任意多个（包括0个）任意字符；_表示任意一个字符

注意：在其他编程语言中，判断相等用的是==，而在SQL语言中，用的是=，赋值也是=

逻辑运算符：

运算符	说明
and	多个条件必须都为true(1)，结果才是true(1)
or	任意一个条件为true(1)，结果为true(1)
not	条件为true(1)，结果为false(0)

使用条件查询的语法：

select * from 表名 where 列名/表达式 运算符 条件;

1. 查询英语不及格的同学及英语成绩
   
2. 查询语文成绩好于英语成绩的同学
   
3. 查询总分在200分以下的同学
   

我们再试着用别名来代替where中条件语句

发现报错了，这就说明了：在where语句中，不能使用别名，只能使用完整的表达式

究其原因，是与MySQL执行SQL语句的顺序相关

• 如果要在数据中查某些表，首先要确定表，先执行from
• 在查询过程中，要根据指定的查询条件把符合条件的数据过滤出来，这时执行的就是where子句
• 执行select后面的指定的列，这些列是需要加入到最终的结果集中
• 排序操作，根据order by子句中指定的列名和排序规则进行最后的排序

3.8 分页查询：limit

在生产环境中，用select * from 表名;直接去查看整张表，不加以限制记录条数，是不安全的

我们可以通过分页查询，来控制一次查询出来的结果集中的记录条数，这不仅可以有效地减少数据库服务器的压力，同时对用户也比较友好

语法：

//从0开始，筛选出n条记录
select 列名 from 表名 [where... ] [order by...] limit n;

其中，n表示限制查询的条数

//从s开始，筛选n条记录
select 列名 from 表名 [where...] [order by...] limit s, n;

其中，s表示从第几条记录开始，n表示读取多少条记录

//从s开始，筛选n条记录，比第二种用法更明确，建议使用
select 列名 from 表名 [where...] [order by...] limit n offset s;

如果第一页要从第一条记录开始，s取0
s = (当前页数 - 1) * 每页显示的记录数

4. 更新（Update）

语法：

update 表名 set 列名 = 新值 [, 列名 = 新值...] [ where...] [order by...] [limit ...]

• 将孙悟空同学的数学成绩变更为80分
  

• 如果这时再插入一条孙悟空的记录，再将孙悟空的数学成绩变更为80分，会怎样呢？
  
  匹配到两条记录，只修改了一条记录，因为第一条记录无需修改

• 如果这时，将孙悟空同学的数学成绩修改为90分，会怎样呢？
  
  匹配到两条记录，修改了两条记录

因此，在进行update操作的时候，如果不加以where条件限制，修改的将是整张表中的所有记录，是非常危险的操作！

• 将总成绩倒数前三的3位同学的数学成绩减30分
  
  注意：math = math - 30 不能写成 math -= 30，这种写法MySQL是不支持的

5. 删除（Delete）

语法：

delete from 表名 [where...] [order by...] [limit ...]

• 删除孙悟空同学的考试成绩

两行数据受到影响，也就是说删除了两条数据，因为通过where匹配到了两条数据行

在delete时，如果不加where条件会出现什么问题？
答：如果删除时，不加以条件限制，那么整张表的数据都会被删除掉（清空），是非常危险的操作

但在生产环境中，一般不去使用delete操作，一般在表中会加一个deleteState字段，用来表示这条记录是否被删除了，0表示正常（没有被删除），1表示已经被删除了，用update操作去更新deleteState字段，就可以实现删除功能，这条被删除的数据并没有被实质上删除掉，而是始终存在于数据库中