MySQL表的增删改查

---

前言

注释：在SQL中可以使用“--空格+描述”来表示注释说明.
CRUD 即增加(Create)、查询(Retrieve)、更新(Update)、删除(Delete)四个单词的首字母缩写.
再进行本节之前,需要先创建表,并向表里面写入数据.

一、新增 insert

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

注意:

1. 要求values后面()中的字段的个数和表头约定的列数以及每个列的类型要匹配.
2. 在SQL中表示字符串,可以使用'也可以使用”.Java中要求只能是”表示字符串.'表示的是字符,但是在SQL里面,没有单独的“字符类型".
3. 如果某一列是datetime，此时如何进行插入呢?

1)通过指定格式的字符串来插入一个指定时间.

insert into values (1,2022-03-07);

2)通过now()函数来插入一个当前系统时间.

insert into values (1,now());

4. insert进行插入的时候,可以只插入其中的某一列或者某几个列的.此时其他的列将采用默认值.

insert into student(id,name) values(10,'xiaohua');

5. 查看当前数据库的编码格式:

show variables like 'character%';

1.1单行数据 + 全列插入

insert into student values(1,'zhangsna',19,90,89);

1.2 多行数据 + 指定列插入

insert也可以一次插入多行,values 后面的每个()都对应到一行,可以一次性带多个(),多个()之间用逗号来分割.

二、查找语句

2.1 全列查找

直接把一个表的所有列和所有行都查询出来.
查找的结果是一个"临时表",之前说的数据库的表,都是在硬盘上的,而此处的查询结果临时表,不是在硬盘上,而是在内存上,随着进行了输出之后,数据也就被释放了.
select操作不会影响到服务器这边硬盘上保存的数据.

select * from 表名;

*叫做通配符,表示一个表的所有列.

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

1. 查询的列越多，意味着需要传输的数据量越大；
2. 可能会影响到索引的使用。

2.2 指定列查询

只查询自己关注的列.

select 列名 from 表名;

这里在查询的时候,会显示的告诉数据库要查询的是哪些列,数据库就会针对性的返回数据.相对于全列查询,这种指定列查询更高效,指定列查询比全列查询使用的频率要高一些.
这种查询结果,只是一个"临时表",随着打印的进行,内存就释放了.临时表的结果对于数据库服务器的原始数据没有任何影响.

2.3 指定查询字段为表达式

对应english那一列的成绩都加上10.

-- 表达式包含一个字段
select name,math+ 10 from exam_result;

第三列为三科成绩的总和.

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

在查询的时候,同时进行一些运算操作.
select操作的结果是临时表.原来数据库服务器上的数据不变,这里的查询结果变了,只是说数据库服务器针对查询的结果进行了加工,把加工后的数据作为临时表显示出来.

2.4 查询字段指定别名(as)

相当于给查询结果的临时表,指定新的列名.
通过指定别名的方式,来避免得到的临时表名字比较乱.

将三科成绩的总和其名为total:

-- total 就是指定的别名 ,这里的as是可以省略的
select name,chinese+math+english as total from exam_result;

2.5 针对查询结果去重(distinct)

针对查询结果把重复的记录去掉.

select distinct english from exam_result;

如果是针对多个列来进行去重,就得多个列的值都相同的时候才视为重复.

2.6 排序

针对查询结果(临时表)进行排序,不会影响到数据库服务器上的原始数据.

-- asc 表示升序排序, desc表示降序排序
-- 如果后面什么都不写,默认是升序排序的
select 列名 from 表名 order by 列名 asc/desc;

降序排序:

升序排序:

如果数据库中带有NULL值,它会被认为是最小值(升序排就在最前面,降序排就在最后面).

排序的时候可以通过order by 来指定多个列进行排序,先根据第一个列进行排序,如果第一列结果相同,则按照第二列的大小进行排序.

2.7 select 中的条件查询:where

select 列名 from 表名 where 条件;

查询结果就是把满足条件的结果保留,把不满足条件的结果就过滤掉了.

比较运算符:

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

逻辑运算符:

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

条件查询: where
筛选出英语不及格的成绩:

selsct name,english from exam_result where english < 60;

这个条件就会针对查询结果进行筛选,服务器就会遍历表中的每条记录,如果记录符合条件,就返回给客户端,如果不符合条件,就跳过.

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

针对同一行的语文/英语之间进行比较.

where 子句中不能使用别名.

查询总成绩小于200分的:

select name, chinese + english + math from exam_result where chinese + english + math <200;

查询语文大于80并且英语大于80的.

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

查询语文大于80或者英语大于80的.

select name,chinese,english from exam_result where chinese > 80 or english > 80;

and 的优先级高于or,如果同时有and和or,则先计算and,后计算or.如果要打破优先级,需要加().

范围查找:
between..and 查询的是闭区间.

查找出语文成绩在89~90之间的:

-- [80,90]
select * from exam_result where chinese between 89 and 90;

下面的语句和上面的没有本质区别

select * from exam_result where chinese >= 89 and chinese <= 90;

in:
只要数学成绩是括号里面的任意值,都可以被查询出来.

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

这里也可以通过or实现同样的效果.

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

模糊查询:like搭配通配符来使用,只要对方的字符串符合此处描述的形式就可以.
%代表任意个任意字符(也包含0个字符).
_代表任意一个字符.

上图中%sh就可以匹配以sh开头的任意字符.
如果前后都加%sh%,说明在sh前后都可以有任意字符.

_只能匹配到一个任意字符.

上图表示未匹配到.

NULL 的查询：
为空:is null

查询数学成绩为空的列:

select name, math from exam_result where math is null;

select * from exam_result where chinese <=> null;

不为空:is not null;

查询语句中,和null进行比较

2.8 分页查询

可以使用limit来实现分页查询.

假设一页就显示3条记录:只取出结果中的前三条.

select * from exam_result limit 3;
-- 或者写成
select * from exam_result limit 3 offset 0;

从下标3开始,再往后找三条:即第2页

 select * from exam_result limit 3 offset 3;

查询第3页:

 select * from exam_result limit 3 offset 6;

limit同样可以搭配条件,以及order by 等操作来组合使用.

按总成绩进行降序查询,只显示前三列:

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

三、修改

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

如果where省略了,就是修改所有记录.
另外,除了where之外,像order by 和limit也是可以使用的.

update会修改数据库服务器上面的原始数据.

update exam_result set id = 5 where name = 'shitou';

update exam_result set math = 67,chinese = 80 where name = 'shisan';

将表中的所有语文成绩缩小2倍.

update exam_result set chinese = chinese / 2;

可以查看警告的详细信息:

show warnings;

四、删除

delete from 表名 where 条件;

从表中删除id = 8的那一行.

delete from exam_result where id = 8;

一旦条件写错了,影响范围会很大.
如果不写条件,就会把整个表的数据给删除掉.
如果是drop table;表也就会给删没了,数据也就没有了.

---

以上.