如何实现 MySQL 增删改查操作

操作数据库最主要的操作就是增删改查，也就是 CURD。

• C 是 create 新增
• U 是 update 修改
• R 是 retrieve 查询
• D 是 delete 删除

1.新增

1.1 不指定列插入

命令格式：

insert into 表名 values(列, 列, 列, 列......);
1

into 可以省略，但是不建议。

需要注意的是新增前，务必先选中一个数据库。

示例：给 student 表插入两条记录。

看到图中提示的 OK 后，说明插入成功。

use java 即为选中 java 这个数据库后开始操作。

在 SQL 中不区分单双引号，即单引号和双引号都可以表示字符串。

注意：

插入的内容要和数据表的列数和类型匹配，不然不会报错。

可以看出 student 表中有两个列，一个要是整数，一个要是字符或字符串


列数不匹配的错误信息：


类型不匹配的错误信息：

1.2 指定列插入

命令格式：

insert into 表名 (列名) values(1);
1

也可以一次性指定多个列。

命令格式：

insert into 表名 (id, name) values(1, '张三');
1

如果要指定多个列，就在表名后面的括号里使用逗号分割即可。


演示：

插入成功。

使用 insert 指定列插入后，未被指定的列则会以 默认值（NULL） 填充。

default 下就是默认值的意思。

1.3 一次性插入多行

命令格式：

insert into 表名 values (列, 列, 列...), (列, 列, 列...);
1

演示：

圈出的部分提示我的数据库有3行的数据受到了影响。

现在有一个问题：

为什么在 mysql 中一次插入一条记录，分十次插入的效率要低于一次插入十条记录呢？

1、原因1：由网络请求和响应的时间开销引起的~~

每一次插入都会有一定的时间开销，而十次插入的时间开销明显更多。


2、原因2：数据库服务器是把数据保存在硬盘上的。

这就好比，我下楼拿快递，快递到一个我下去拿一个，有几个快递就要拿几次；
然而还可以等所有的快递都到了再一起去拿，这样就一次拿完了。


3、原因3：

mysql关系型数据库，每进行一个sql操作，内部都会开启一个实务，每次开启实务也会有一定的开销。

这个以后会讲到。

2.查询

2.1 全列查询（查询表中的所有列）

命令格式：

select * from 表名;
1

示例：查询 student 表中的所有学生

可以看到 student 表的两列信息都查询到了。

2.2 指定列查询

命令格式：

select 列名 from 表名;
1

示例：查询 student 表中的 name 列。

此时显示出来的就是 name 列的全部信息。

2.3 在查寻过程中进行简单计算（列和列之间）

示例1：计算 books 表中图书减去5元后的价格。

命令格式：

select 列名, 列名-5 from 表名; 
1

减去前：

减去后：

在进行表达式查询的时候，查询结果是一个“临时表”。这个临时表并不是写入到硬盘当中的，临时表的类型也不是和原表类型完全一致（会尽可能把数据给表示进去）。


可以发现再次查询全表，价格并未发生改变。

示例2：计算 student 表中所有学生的总分

命令格式：

select 列, 列, 列 + 列 + 列 from 表名;
1

查询结果：

上述结果都是临时表。
表达式查询知识针对每一行所对应的列进行计算，无法进行行与行之间的计算。

2.4 给查询结果的列指定一个别名

命令格式：

select 列, 列, 列 + 列 + 列 total from 表名;
1

示例：计算 student 表中所有学生的总分

演示：

total 就是起的别名，直接写在 from 的前面。
但是这样直接在加 from 前面加别名容易看错，这时可以加上一个 as。

现在来说优化前面的示例：

命令格式：

select 列, 列, 列 + 列 + 列 as total from 表名;
1

演示：

得到了与之前相比相同的结果。

2.5 查询的时候针对列来去重（把重复的记录合成一个）

命令格式：

select distinct 列 from 表名;
1

示例：将 student 表 math 成绩相同的合并为一个。

演示：

可以看到相同的 math 成绩被去掉了。

distinct 也可以进行多个列的去重，只不过要求是所有的列必须是相同的。

多个列去重的情况：

命令格式：

select distinct 列, 列... from 表名;
1

示例：将 student 表 math 和 chinese 成绩都相同的合并为一个。

演示：

可以看到两门课程都已经去重完毕。

2.6 针对查询结果进行排序

这里需要用到 order by

示例：将 chinese 成绩排序升序的。

命令格式

select * from 表名 order by 列名;
1

演示：

可以看到 chinese 成绩被排序成升序的了。

列名后面不加东西就是默认升序排列。

上图即为升序。

降序的写法：

在列名后面加上一个 desc

演示：

可以看到 chinese 成绩被排序成降序的了

2.6.1 关于排序的注意事项

1、如果 SQL 中没有显示的写 order by，认为查询结果的顺序是不可预期的！！！
写代码不可以依赖这种不可预期的顺序！！！

不加 order by ，看起来好像是根据插入的顺序一次排序的。但是实际上，mysql 并没有对顺序做任何的承诺！！！

这个时候就不能依赖的个顺序！！！


2、如果要排序的列中有 NULL ，此时的NULL 就是最小值~~

示例：升序排序 student 表中 chinese 成绩。

可以看到表示最小值的 NULL 被排在了第一个。

如果要是多个纪录，排序的列值相同，此时先后顺序也是不确定的~~

3、排序也可以针对表达式或者别名来进行~~

示例：按照同学的总分进行升序排序

命令格式：

select 列名, 列名 + 列名 + 列名... as total from 表名 order by 别名; 
1

演示：

此时就是按照升序排成总分。

因为在 SQL 中NULL和任何值计算。结果都是 NULL，所以罗志祥的总分才会最低。

4、排序还可以指定多个列进行排序（更复杂的比较规则）

示例：先按照 chinese 成绩排序，再按照 math 成绩排序。

命令格式：

select * from 表名 order by 列名, 列名;
1

演示：

2.7 条件查询（按照一定条件进行筛选）

2.7.1 描述条件关系的运算符

关系运算符


<=> 是针对 NULL 特殊处理了，使用 = 来比较某个值和 NULL 之间的关系，
结果任然是 NULL，NULL 又会被当做 false。

like 作为模糊匹配，不要求值完全相等，只满足一部分相等即可。

逻辑运算符

2.7.2 基本条件查询示例

示例1：查询 chinese 成绩低于70分的同学

命令格式：

 select 列名, 列名 from 表名 where 列名 < 70;
1

演示：

NULL 和任何数据进行计算都是 NULL，NULL < 70 被当成 false。
这就是为什么罗志祥没被查到的原因。

示例2：查询 english 成绩好于 chinese 成绩的同学

命令格式：

select * from 表名 where 列名 > 列名;
1

演示：

示例3：查询总分大于200的同学

命令格式：

select 列名, 列名 + 列名 + 列名 as total from 表名 where 列名 + 列名 + 列名 > 值;
1

演示：

在这条命令中 where 后面的条件不能使用别名来表示。

示例4：查询语文和数学成绩大于70的同学

命令格式：

select 列名, 列名, 列名 from 表名 where 列名 > 值 and 列名 > 值;
1

演示：

示例5：查询数学成绩大于80或者语文成绩大于80的同学

命令格式：

 select 列名, 列名, 列名 from 表名 where 列名 > 值 or 值 > 值;
1

演示：

在条件中如果有 and 和 or 要先计算 and 后计算 or。

示例6：查询语文成绩在70~90之间的同学。

命令格式1：

select 列名, 列名 from 表名 where 列名 >= 值 and 列名 <= 值;
1

演示：

命令格式2：

select 列名, 列名 from 表名 where 列名 between 值 and 值;
1

演示：

between and 可以查询索引，效率更快。

示例7：查询数学成绩是 72 或者 75 或者 87 或者 76分的同学及数学成绩。

命令格式1：

select 列名, 列名 from 表名 where 列名 = 值 or 列名 = 值 or 列名 = 87 or 列名 = 值;
1

演示：

命令格式2：

select 列名, 列名 from 表名 where 列名 in(值, 值, 值, 值);
1

演示：

2.7.3 使用 % 和 _ 来查询任意个字符

1、在 SQL 中 % 可以用来表示任意个字符。

示例：查询名字带张的同学。

命令格式：

select * from 表名 where 列名 like '值%';
1

演示：

% 在数值的最后面，只能查询到一 ‘张’ 开头的信息，若是 ‘张’ 在末尾或是中间位置则查询不到。

可以看到小张名字也带张，但是没有查询出来。

如果要查询末尾的字符，将 % 放到开头位置即可。

演示：

如果将要查询的字符放到中间位置，只要是这个字符就会显示。

演示：

2、SQL 中 _ 表示匹配任意一个字符。

示例：在张后面匹配两个字符

命令格式：

select * from 表名 where 列名 like '值_';
1

演示：


要匹配几个字符就加几个 _ 。

下图是匹配两个字符的

下划线可以在两端位置，也可以在中间位置。

模糊查询对数据库来说，查询的开销还是比较大的。

2.7.4 NULL结果查询

示例：查询语文成绩是null的同学

查询空结果的错误命令：

select * from 表名 where 列名 = null;
1

带入的条件如果是null，比较的结果也会是null，会被当成false。

正确的命令1：

select * from 表名 where 列名 <=> null;
1

演示：

正确的命令2：

select * from 表名 where 列名 is null;
1

演示：

is null 只能用来比较一个列是否为空。
<=> 可以用来比较两个列，如果某一个行两列都是null，也能查询出来。

2.7.5 使用 limit 来限制查询结果的数量

示例：查询前三个同学的信息

命令格式：

select * from 表名 limit 值;
1

演示：

只查询到了前三个同学的成绩。

limit 后面的数字表示查询的是前n条数据。

2.7.6 搭配 0ffset 指定从第几条开始筛选（0ffset是从0开始计算的）

示例：从第一条数据开始进行筛选

命令格式：

select * from 表名 limit 值 offset 值;
1

演示：

第一条数据被筛选掉了。

3.修改

3.1 一行记录的修改

示例：将张三同学的数学成绩改为80分

命令格式：

update 表名 set 列名 = 值 where name = '张三';
1

演示：

可以看到张三的数学成绩改为了80分。

set math 表示描述了数学这一个列。
where = ‘张三’ 描述了那些行需要修改，条件如果是真就修改，是假就pass。

3.2 多行记录的修改

使用 like 进行模糊查找

示例：将所有名字带张的同学的数学成绩改为66

命令格式：

update 表名 set 列名 = 值 where 列名 like '%张%';
1

演示：

3.3 利用表达式进行修改

示例：把所有同学的语文成绩减去10分

命令格式：

update 表名 set 列名 = 列名 - 值;
1

演示：

3.4 多个列的修改

示例：将张三同学的三科成绩改为0分

命令格式：

update 表名 set 列名 = 值, 列名 = 值, 列名 = 值 where 列名 = '字符';
1

演示：

3.5 搭配子句进行修改

update 还可以搭配 order by 和 limit 等子句。

示例：给总成绩倒数第一名的同学的数学成绩设置成100分。

思路：

• 先求总分并按升序排序。
• 再设置为100分。

1、求总分的命令格式：

select 列名, 列名 + 列名 + 列名 as 别名 from 表名 order by 别名;

1
2

演示：

2、设置100分的命令格式：

update 表名 set 列名 = 值 order by 列名 + 列名 + 列名 limit 值;
1

演示：


可以看到总分倒数第一张三同志数学成绩被改为了100。

4.删除

示例：删除张三同志的考试成绩

命令格式：

delete from 表名 where 列名 = '字符';

1
2

演示：

可以发现张三被删除了。

delete 是一个永久性的删除。

后面的条件也是和update一样，可以支持where 、order by、limit。
如果没写条件，就把整个表里的记录都删了。