【MySQL】表的增删查改

1. Create

首先创建一张表

创建表完毕后查看表结构，可以看到表结构如下：

1.1 表的插入操作

一般在进行插入操作时，我们有多种插入方法：

指定列插入

全列插入

多行插入

1.2 插入否则更新操作

向表中插入记录时，如果待插入记录中的主键或唯一键已经存在，那么就会因为主键冲突或唯一键冲突导致插入失败。

这时可以选择性地进行同步更新操作：

• 如果表中没有冲突数据，则直接插入数据。
• 如果表中有冲突数据，则将表中的数据进行更新。

比如向学生表中插入记录时，如果没有出现主键冲突则直接插入记录，如果出现了主键冲突，则将表中冲突记录的学号和姓名进行更新。

执行替换数据的SQL后，也可以通过受影响的数据行数判断本次数据的插入情况。

• 1 row affected：表中没有冲突数据，数据直接被插入。
• 2 row affected：表中有冲突数据，冲突数据被删除后重新插入。

2. Retrieve

为了进行演示，下面创建一个成绩表，表当中包含自增长的主键id、姓名以及该同学的语文成绩、数学成绩和英语成绩。

下面我们插入一些数据，便于演示后续的查询操作。

2.1 select查询

全列查询

在查询数据时直接用 * 代替column列表，表示进行全列查询，这时将会显示被筛选出来的记录的所有列信息。

说明一下： 通常情况下不建议使用 * 进行全列查询，因为被查询到的数据需要通过网络从MySQL服务器传输到本主机，查询的列越多也就意味着需要传输的数据量越大，此外，进行全列查询还可能会影响到索引的使用。

指定列查询

在查询数据时也可以只对指定的列进行查询，这时将需要查询的列在column列表列出即可。

查询字段为表达式

column列表中的表达式中也可以包含多个表中已有的字段，这时我们就可以通过表达式计算出更多有意义的数据。

为查询结果指定别名

比如查询成绩表中的数据时，将每条记录中的三科成绩相加，然后将计算结果对应的列指定别名为“总分”。

结果的去重

查询成绩表时指定查询数学成绩对应的列，可以看到数学成绩中有重复的分数。

如果想要对查询结果进行去重操作，可以在SQL中的select后面带上distinct。

2.2 where条件

• 如果在查询数据是没有指定where子句，那么会直接将表中所有的记录作为数据源来一次执行select语句。
• 如果在查询数据时指定了where子句，那么在查询数据时会先根据where子句筛选出符合条件的记录，然后将符合条件的记录作为数据源来一次执行select子句。

where子句中可以指明一个或多个筛选操作，各个筛选条件直接用逻辑运算符AND或OR进行关联，下面给出了where子句中常用的比较运算符和逻辑运算符。

查询英语不及格的同学及其英语成绩

查询语文成绩在80到90分的同学及其语文成绩

此外，这里也可以使用BETWEEN a0 AND a1来指明语文成绩的的所在区间。

查询数学成绩是58或59或98或99分的同学及其数学成绩

在where子句中指明筛选条件为数学成绩等于58或59或98或99，在select的column列表中指明要查询的列为姓名和数学成绩。

此外，这里也可以通过==IN(58, 59, 98, 99)==的方式来判断数学成绩是否符合筛选要求。

查询姓张的同学

在where子句中通过模糊匹配来判断当前同学是否姓孙 （需要用到%来匹配多个字符） ，在select的column列表中指明要查询的列为姓名。

查询李某同学

在where子句中通过模糊匹配来判断当前同学是否为孙某 （需要用到_来严格匹配单个字符） ，在select的column列表中指明要查询的列为姓名。

查询语文成绩好于英语成绩的同学

查询语文成绩大于80分并且不姓王的同学

在where子句中指明筛选条件为语文成绩大于80，并且通过模糊匹配和not来保证该同学不姓王，在select的column列表中指明要查询的列为姓名和语文成绩。

2.3 null的查询

我们使用这样一张表进行演示。

查询qq号已知的同学

在where子句中指明筛选条件为QQ号不为NULL，在select的column列表中指明要查询的列为姓名和QQ号。

查询QQ号未知的同学

需要注意的是，在与NULL值作比较的时候应该使用 <=> 运算符，使用 = 运算符无法得到正确的查询结果。

因为=运算符是NULL不安全的，使用=运算符将任何值与NULL作比较，得到的结果都是NULL。

但是<=>运算符是NULL安全的，使用<=>运算符将NULL和NULL作比较得到的结果为TRUE(1)，将非NULL值与NULL作比较得到的结果为FALSE(0)。

2.4 order by排序

对于没有 ORDER BY 子句的查询，返回的顺序是未定义的，永远不要依赖这个顺序

下面，通过一些案例来理解order by排序：

注：SQL语句中的ASC和DESC分别代表的是排升序和排降序，默认为ASC。

查询同学及其数学成绩，按数学成绩升序显示

在select的column列表中指明要查询的列为姓名和数学成绩，在order by子句中指明按照数学成绩进行升序排序。

查询同学及其QQ号，按QQ号升序显示

说明一下： NULL值视为比任何值都小，因此排升序时出现在最上面。

查询同学的各门成绩，依次按数学降序、英语升序、语文升序显示

在select的column列表中指明要查询的列为姓名、数学成绩、英语成绩和语文成绩，在order by子句中指明依次按照数学成绩排降序、英语成绩排升序和语文成绩排升序。

可以看到显示结果是按照数学成绩进行降序排序的，而相同的数学成绩之间则是按照英语成绩进行升序排序的。

查询同学及其总分，按总分降序显示

在select的column列表中指明要查询的列为姓名和总分（表达式查询），在order by子句中指明按照总分进行降序排序。

order by子句的执行是在select语句之后的，所以在order by子句中可以使用别名。

2.5 limit筛选分页结果

建议： 对未知表进行查询时最好在查询SQL后加上limit 1，避免在查询全表数据时因为表中数据过大而导致数据库卡死。

查询第1页记录时在查询全表数据的SQL后，加上limit子句指明从第0条记录开始，向后筛选出3条记录。

查询第2页记录时在查询全表数据的SQL后，加上limit子句指明从第3条记录开始，向后筛选出3条记录。（由于我的这个表中只有五条记录，所以最终只筛选出了两条）

3. Update

将张三的数学成绩修改为80分

将总成绩倒数前三的3位同学的数学成绩加上30分

在修改数据之前，先查看总成绩倒数前三的3位同学的数学成绩。

在update语句中指明要将筛选出来的记录的数学成绩加上30分，并在修改后再次查看数据确保数据成功被修改。

4. Delete

4.1 删除数据

删除张三同学的考试成绩

删除整张表数据

创建一张测试表，表中包含一个自增长的主键id和姓名。

向表中插入一些测试数据用于删除。

在delete语句中只指明要删除数据的表名，而不通过where、order by和limit指明筛选条件，这时将会删除整张表的数据。

再向表中插入一些数据，在插入数据时不指明自增长字段的值，这时会发现插入数据对应的自增长id值是在之前的基础上继续增长的。
查看创建表时的相关信息时可以看到，有一个AUTO_INCREMENT=n的字段，该字段表示下一次插入数据时自增长字段的值应该为n。

当通过delete语句删除整表数据时，不会重置AUTO_INCREMENT=n字段，因此删除整表数据后插入数据对应的自增长id值会在原来的基础上继续增长。

4.2 截断表

• 只能对整表操作，不能像DELETE一样针对部分数据操作。
• 实际上MySQL不对数据操作，所以比DELETE更快，但是TRUNCATE在删除数据时，并不经过真正的事物，所以无法回滚。
• 会重置AUTO_INCREMENT项

创建一张测试表，表中包含一个自增长的主键id和姓名。

向表中插入一些测试数据用于删除

在truncate语句中只指明要删除数据的表名，这时便会删除整张表的数据，但由于truncate实际不对数据操作，因此执行truncate语句后看到影响行数为0。

再向表中插入一些数据，在插入数据时不指明自增长字段的值，这时会发现插入数据对应的自增长id值是重新从1开始增长的。

当通过truncate语句删除整表数据时，会重置AUTO_INCREMENT字段，因此截断表后插入数据对应的自增长id值会重新从1开始增长。