MySQL表的增删改查（基础）

一、CRUD

• 注释：在SQL中可以使用“–空格+描述”来表示注释说明。
• CRUD 即增加(Create)、查询(Retrieve)、更新(Update)、删除(Delete)四个单词的首字母缩写。

二、新增（Create）

1.插入完整一行数据：

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

注意：

1. 进行增删查改的时候，务必要先选中数据库。
2. 每次新增，都是直接新增一行（一条记录）。
3. 个数和类型要和表的结构匹配，否则报错。

2.指定列插入：

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

这样插入的话，未被指定的列，则是以默认值来进行填充的：

注意区分 NULL 和 ''

3.还可以一次指定插入多行：
即后面的括号有多组：

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

知识点：
在MySQL中，一次插入一条记录，分10次插入的效率要低于一次把10个记录一起插入！！！
原因：

1. MySQL是一个“客户端服务器”结构的程序：
   
   既然是通过网络访问，那么发起网络请求和返回网络响应，每一次都是有一定的时间开销的！
2. “数据库服务器”结构是把数据保存在硬盘上的，而硬盘的访问速度较慢。在进行IO操作的时候，虽然数据量会对效率有影响，但是影响一般不会很大，而更大的则是IO操作的次数！
3. MySQL是关系型数据库，每次进行一个sql操作，内部都会开启一个事务：每次开启事务也有一定的开销。

三、查询（Retrieve）

查询是sql中最复杂的操作~

3.1 基本查询

1.全列查询
即查询表里的所有列。

select * from 表名;

此处的*叫作通配符，代表了所有的列。
无论你这里的表是啥样的，里面有几列，都是啥名字？没关系，都可以使用*来代表所有。

初学阶段常用，但是！！！
千万不敢随便在公司的生产环境上进行select * 非常危险！！！
因为：

服务器的硬件资源是有限的，包括不限于CPU、内存、硬盘、网络带宽…
如果在一些场景中，把某个资源给吃光了，就会很容易导致程序出现严重问题！！！
那么能否通过充值的方式来获得更高配置的服务器，解决上述问题呢？可行的，又不完全可行。因为当前硬件的发展，已经赶不上需求的发展，毕竟当前一些头部的互联网公司，动不动就是上亿的用户。
正是因为上述矛盾的存在，才有了分布式！本质上就是增加更多的机器，提供更多的硬件资源。

2.指定列查询

select 列名 from 表名;

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

相比于全列查询精简不少，但是仍要谨慎使用。

3.查询列为“表达式”，在查询过程中，进行一个简单计算 (列和列之间)

例如查询 英语成绩+10：

select name,english+10 from exam_result;

例如查询 三门成绩相加：

select name,english+chinese+math from exam_result;

注意：

1. 进行表达式查询的时候，查询结果是一个“临时表”。
   这个临时表，并不是写入到硬盘中的，临时表的类型也不是和原始表完全一致（会尽可能把数据给表示进去）。
   
2. 表达式查询只是针对每一行的对应列进行计算，而无法进行“行和行”之间的运算。
3. 这里加字符串的话，会尝试把字符串隐式转换成数字。如果没法顺利转成则会警告（和Java是不一样的）。
4. 表达式可以加括号()。
5. 当查询列数据相加时，临时表的新列名就是两个列名相 ‘+’，并不直观。所以我们有下面一个查询方式：

4.给查询结果的列指定别名

total 就是新名字：

select name,english+chinese+math total from exam_result;

select name,english+chinese+math as total from exam_result;

尽量把as加上，不然容易混淆~

5.查询时，针对列去重 (把重复记录合并成一个)

select distinct 列名 from 表名;

有重复的列数据会合并成一个。

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

要求选中的所有列数据全都相同，这时候才进行合并。

6.针对查询结果进行排序

select * from 表名 order by 列名;

根据某一列的数据来进行排序（默认为升序排序）。
降序：

select * from 表名 order by 列名 desc;

后加desc即按降序排序（descend的缩写）；默认或后加asc为升序排序（ascend的缩写）。

也可以根据临时表的数据排序：

select name,english+chinese+math as total from exam_result order by total;

关于排序注意：

1. MySQL上的数据量可能是非常大的（内存放不下），所以更好的选择是归并排序。
   但是也不一定真是归并，因为很多成熟的软件里面使用的一些具体策略会有针对性的进行优化。
2. 如果SQL中没有显式地写order by，则认为查询结果的顺序是不可预期的！写代码不能依赖默认的顺序！且如果多个记录排序的列值相同，此时先后顺序也是不确定的！
3. 如果需要排序的列中有NULL，则NULL视为“最小值”，比0和负数还小。
4. SQL中，NULL和任何值进行运算，结果还是NULL。
5. 排序还可以指定多个列来进行排序，因为生活中/开发中排序往往不是简单的“升序和降序”，还会有一些更复杂的规则，综合考量多方面因素来完成整体的排序（更复杂的比较规则了），如下：

7.更复杂的比较规则

select * from exam_result order by chinese,math;

以上代码是先排chinese，相同时再排math。（默认依然是升序）
每个列的后面都可以加上asc或desc：

select * from exam_result order by chinese desc,math asc;

3.2 条件查询

条件查询思想是：根据查询结果，按行进行筛选。where+子句。
关键词：where

通过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 个）任意字符；_ 表示任意一个字符

注意：

1. 在where字句中，‘=’ 又变为了比较相等。
2. <=>是针对NULL特殊处理了，使用=来比较某个值和NULL的相等关系，结果仍然是NULL，而NULL又会被当成false。

逻辑运算符：

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

select name,english from exam_result where english<60;

select * from exam_result where chinese>english;

select name,english from exam_result where english<60 or english is null;

select name,english from exam_result where english<60 and english<chinese order by english;

过程： 对数据库中保存的数据进行查询，查的时候，每得到一行，就先代入到条件中判断。
因此， where条件可以使用表达式，但不能使用别名，因为得到一行就先进行where判断，此时并不知道别名：

select name,chinese+english+math as total from exam_result where chinese+english+math > 200;

注意：

1. where条件可以使用表达式，但不能使用别名。
2. and的优先级高于or，在同时使用时，需要使用小括号()包裹优先执行的部分。
3. 若where chinese = null会报错：
   
   set 通常有两个含义：1.设置 ；2.集合。这里显然意思是“空的集合”，因为使用=来比较某个值和null的相等关系，结果仍然是null，而null又会被当成false。应该使用<=>或is null。但是这种情况：select * from exam_result where chinese <=> math;使用<=>的话若两者都为null也会返回true；而如果使用is null就会比较麻烦！

---

范围匹配查询：

select name,chinese from exam_result where chinese >= 80 and chinese <= 90;

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

因为最左前缀匹配原则（方便索引），所以我们一般使用 between-and。

select name,math from exam_result where math=58 or math=59 or math=98 or math=99;

select name,math from exam_result where math in (58,59,98,99);

---

模糊匹配查询：
通配符%： 表示 任意个 字符

例如查询姓“孙”同学的成绩：

select * from exam_result where name like '孙%';

此处的%是通配符，可以表示任意个字符。（只要以“孙”开头的都被查询出）
以“孙”结尾：

select * from exam_result where name like '%孙';

包含“孙”：

select * from exam_result where name like '%孙%';

通配符_： 表示 任意一个 字符

select * from exam_result where name like '孙_';

此时只能匹配到两个字的以“孙”开头的字符串。想要三个字：（以此类推）

select * from exam_result where name like '孙__';

同%，_也可以任意位置搭配。

把“孙”换成任意字符串，结果都是同理。

注意：模糊查询，对于数据库来说，查询开销是比较大的！

MySQL支持的模糊匹配功能是非常有限的，在实际开发中可能会遇到一些更复杂的模糊匹配。
可能会描述一些更复杂的规则，如：某某字符出现在什么位置范围，重复出现的次数范围，包含一些特殊符号之类的…于是发明了正则表达式来描述这种字符串的规则~
在Java的学习中，String里有的方法，比如replace就支持正则表达式。
正则表达式就是使用一些特殊的规则，来描述一个字符串长啥样，在查询/进行其他操作的时候，按照这套规则进行匹配！使用一些特殊符号来表示上述规则，这种特殊的字符串就是正则表达式。

---

limit限制数量
上面我们讲到过，随便在公司的生产环境上进行select *是非常危险的，因为全部查询的话数据量一般非常大，就容易把硬盘IO或者网络带宽吃满！而limit可以很好限制查询结果的数量。

select * from exam_result limit 5;

这样只查询了前五条记录。
而且分页查询也是通过limit实现的：

3.3 分页查询

搭配 offset (偏移量) 就可以指定从第几条开始进行筛选了（offset的值从0开始计算）：

select * from exam_result limit 5 offset 0;
select * from exam_result limit 5 offset 5;
select * from exam_result limit 5 offset 10;
......

或者：（前是offset）

select * from exam_result limit 0,5;
select * from exam_result limit 5,5;
select * from exam_result limit 10,5;

四、修改（Update）

update 表名 set 列名 = 值,列名 = 值...;

update 表名 set 列名 = 值,列名 = 值... where 条件;

where+子句：描述了哪些行需要修改。仍然是要把每一行都带入条件，条件为真就是要修改的，为假就过。
这个修改操作就是切实地在改服务器的硬盘数据了，这样的修改完成之后就会“持久生效”。且一般是多行修改。

例子：

update exam_result set chinese = chinese-5;

（不要写-=，sql不支持）
结果：

如果没写where子句，就是匹配所有行。但是有的行语文成绩是null，空值没法进行算术运算，所以只改变了8行。

update exam_result set math = 70 where name like '孙%';

---

update 还可以同时修改多个列，多个列之间用逗号分隔开：

update exam_result set chinese=0,math=0,english=0 where name='孙';

---

update 还可以搭配 order by / limit 等子句来进行使用。
例如给总成绩倒数四名的同学，数学成绩设置成10分：先加和得到总分，按照总分升序排序，取结果的前四个设置：

update exam_result set math=10 order by chinese+english+math limit 4;

其实就是精准定位三个信息：表、行、列。务必要保证条件正确！

五、删除（Delete）

delete from 表名 where 条件;

也是在修改数据库服务器的硬盘，也是持久化删除（删了就没了！）
后面的条件也是和update一样，可以支持where，order by，limit …
如果没写条件，呢就是把整个表里所有的记录都删了。所以也是危险操作~

这种删除，只是删了表里的数据，表依然存在（空表）；而 drop table 是表连同里面的数据都一锅端了！