[MySQL#6] 表的CRUD (1) | Create | Retrieve(查)

1. 插入

1.1 单行数据 - 全列插入 + 指定列插入

1.2 多行数据 - 全列插入 + 指定列插入

1.3 更新

1.4 替换

2. 查找

2.1 select 列

2.2 where 条件

具体案例

2.3 结果排序

总结关键字执行顺序

2.4 筛选分页结果

CRUD : Create(创建)，Retrieve(读取)，Update(更新)，Delete（删除）

1. 插入

语法：

INSERT [INTO] table_name
    [(column [, column] ...)] #列字段
    VALUES (value_list) [, (value_list)] ... #列字段的内容
    
value_list: value, [, value] ...

案例：

-- 创建一张学生表
CREATE TABLE students (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    sn INT NOT NULL UNIQUE COMMENT '学号',
    name VARCHAR(20) NOT NULL,
    qq VARCHAR(20)
);

1.1 单行数据 - 全列插入 + 指定列插入

指定列插入列字段和列字段的内容一定要一一匹配

insert into student (sn, name, qq) values (123, '张飞', '12345');

全列插入全列插入有两种方式，一个是省略 values 左侧字段名，一个是都指定

insert into students values (10, 124, '关羽', '13245');
insert into students (id, sn, name, qq) values (14, 125, '刘备', '14525');

可以省略 into

insert students (sn, name, qq) values (126, '诸葛亮', '12525');

1.2 多行数据 - 全列插入 + 指定列插入

指定列多行插入

insert students (sn, name, qq) values (127, '曹操', '15256'), (128, '许攸', '23445');

全列多行插入

insert students values (20, 129, '孙权', '12256'), (21, 130, '吕布', '33445');

1.3 更新

由于主键或者唯一键对应的值已经存在而导致插入失败。

但我就是想让它先确认是不是在数据库中存在，不存在就插入，存在不要拦我然后执行后面的修改语句。

选择性的进行同步更新操作语法：

INSERT ... ON DUPLICATE KEY UPDATE
    column = value [, column = value] ...

如果不存在就插入，存在发生主键或者唯一键冲突不要报错，接着执行后面的修改语句。

insert into students values (14, 111, '周瑜', '56321') on duplicate key update sn=111, name='周瑜', qq=56321;

on duplicate key update

注意更新的值不能和其他的主键和唯一键冲突，否则不能更新。

0 row affected: 表中有冲突数据，但冲突数据的值和 update 的值相等
1 row affected: 表中没有冲突数据，数据被插入
2 row affected: 表中有冲突数据，并且数据已经被更新

通过 MySQL 函数获取受到影响的数据行数

select row_count();

1.4 替换

主键或者唯一键没有冲突，则直接插入
如果冲突，则删除后再插入。

REPLACE INTO students (sn, name, qq) values (131, '孙悟空', '98752');

1 row affected: 表中没有冲突数据，数据被插入
2 row affected: 表中有冲突数据，删除后重新插入

这里从 id 就可以看到是删除后插入的，因为 id 是自增的，刚才是 22，现在是 23 了。

2. 查找

语法：

SELECT
    [DISTINCT] {* | {column [, column] ...}
    [FROM table_name] # 从那个表筛选
    [WHERE ...] # 筛选条件
    [ORDER BY column [ASC | DESC], ...] # 对筛选结果排序
    LIMIT ... # 限定筛选出来的条数

distinct：对内容进行去重
*：全列查询
column，column…：指定列查询

案例：

-- 创建表结构
CREATE TABLE exam_result (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL COMMENT '同学姓名',
    chinese float DEFAULT 0.0 COMMENT '语文成绩',
    math float DEFAULT 0.0 COMMENT '数学成绩',
    english float DEFAULT 0.0 COMMENT '英语成绩'
);

-- 插入测试数据
INSERT INTO exam_result (name, chinese, math, english) VALUES
    ('唐三藏', 67, 98, 56),
    ('孙悟空', 87, 78, 77),
    ('猪悟能', 88, 98, 90),
    ('曹孟德', 82, 84, 67),
    ('刘玄德', 55, 85, 45),
    ('孙权', 70, 73, 78),
    ('宋公明', 75, 65, 30);

2.1 select 列

全列查询通常情况下不建议使用 * 进行全列查询查询的列越多，意味着需要传输的数据量越大；可能会影响到索引的使用。（索引后面再说）

select * from exam_result;

指定列查询指定列的顺序不需要按定义表的顺序来

select id, name, chinese from exam_result;

查询字段为表达式select 非常特殊，后面可以跟 select 自带的子句，筛选条件等，也可以跟表达式
为查询结果指定别名语法：

SELECT column [AS] alias_name [...] FROM table_name;

可带 as，可不带

结果去重

select distinct math from exam_result;

测试：

2.2 where 条件

刚刚是对表的整体信息做筛选，但是实际在做查询的时候一定有筛选条件。
按条件筛选影响的是未来显示出来信息的条目数或者说是行数，以前是按列位单位把全部行都拿出来了。
如果一列想拿那些行由where条件来决定。

where是筛选子句，后面可以跟特定的比较运算符来决策我们应该如何进行筛选，

where就有点像C/C++里面的if语句，根据后面条件进行判断。

比较运算符

运算符	说明
>, >=, <, <=	大于，大于等于，小于，小于等于
=	等于，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 个）任意字符；_ 表示任意一个字符

= 时两侧进行null值比较，是不能参与运算的
如果想判断null是否相等使用 <=>
不过一般也不这样去判断，一般更喜欢用 IS NULL 去判断一个值是否是null

逻辑运算符

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

具体案例

1. 英语不及格的同学及英语成绩 ( < 60 )

select name, english from exam_result where english < 60;

2. 语文成绩在 [80, 90] 分的同学及语文成绩使用 AND 进行条件连接

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

使用 BETWEEN … AND … 条件

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

3. 数学成绩是 58 或者 59 或者 98 或者 99 分的同学及数学成绩使用 OR 进行条件连接，满足任意一个就为真

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

使用 IN 条件，满足 () 里任意一个就为真

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

姓孙的同学及孙某同学有时候匹配并不给具体值的更细节的字段含义，可能就只给一个模糊搜索的关键字。就如上面。反正条件不给全就给一个模糊的条件。
我们就可以用 LIKE 模糊匹配% 匹配任意多个（包括 0 个）任意字符注意 MySQL 可以用 ‘ ’ 或者 “ ” 表示字符串

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

_ 匹配严格的一个任意字符

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

4. 语文成绩好于英语成绩的同学

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

5. 总分在 200 分以下的同学

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

可以看到where后面也有跟表达式，曾经不是说过可以对列进行重命名吗，那where后面还用写这么一大堆吗？

我们发现它报错了，未知列total，我们不是做过重命名吗。这个total不是已经有了吗，怎么这里报不知道total呢？

这里我们就需要讨论一些东西了
❓：from这个表，where这个条件判断和select指明要显示的列，整个sql语句的执行顺序是什么？

很简单，我们一定是先执行from，在执行where，然后在执行select。
筛选后再执行，从1中带着2去3中筛选
原因：只把小于操作 2 的相加后再打印，更节省空间
所以不可以在 where 中使用重命名

6. 语文成绩 > 80 并且不姓孙的同学

AND 与 NOT 的使用

select name, chinese from exam_result where chinese > 80 and name not like '孙%';

7. 孙某同学，否则要求总成绩 > 200 并且语文成绩 < 数学成绩并且英语成绩 > 80

要么就是孙某同学，要么就得满足后面的一堆要求，总共就两个条件，在mysql如果条件很多可以用（）把这个条件括起来，表示一个单元

select name, chinese, math, english, chinese + math + english total 
from exam_result 
where name like '孙_' or (chinese + math + english > 200 and chinese < math and english > 80);

NULL 的查询

select name from test where name is null;
select name from test where name is not null;

null 和 ’ ’ 不是一个东西

2.3 结果排序

语法：

SELECT ... FROM table_name [WHERE ...]
    ORDER BY column [ASC|DESC], [...];

ASC 为升序（从小到大）(ascending)
DESC 为降序（从大到小）(descding)
默认为 ASC

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

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

select name, math from exam_result order by math asc;

NULL 视为比任何值都小，升序出现在最上面

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

select name, math, english, chinese from exam_result order by math desc, english, chinese;

每个同学的各门成绩都要查，首先按照数学降序排序，如果数学成绩相同就按英语升序排序，如果数学和英语成绩都相同就按语文升序排序。

查询同学及总分，由高到低

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

❓ 为什么在order by这里可以使用别名进行排序，而where后面没有办法使用别名？

所以能不能用别名，完全是取决于当前sql中子句的执行顺序！

你要对表结构的数据进行排序，一定是得先有数据！
有人可能说不是表结构不就天然有数据吗我直接把表结构数据全排完，然后在选行不行。
但你会愿意这样浪费时间处理数据排序吗？
因为你没有筛选，在排序的时候一定有大批数据其实是不需要排序的，而对这些数据排序本身就是浪费空间和时间，mysql没有这么笨！
所以会先筛选 select 再排序 order by

总结关键字执行顺序

from > on> join > where > group by > with > having > select（含重命名） > distinct > order by > limit

2.4 筛选分页结果

什么是分页呢？

如果一个表中数据量太大，这个时候如果全列查询就有一大堆，这样不便于查看分析
有时候我们不想一次显示这么多，因此我们就可以对结果进行 LIMIT 分页。

limit 本身没有筛选功能，只是按照它后面跟的数字把要显示的结果按照起始位置和步长给我们显示多条记录。

语法：

-- 起始下标为 0

-- 从 s 开始，筛选 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT s, n

-- 从 0 开始，筛选 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n;

-- 从 s 开始，筛选 n 条结果，比第二种用法更明确，建议使用
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n OFFSET s;

建议： 对未知表进行查询时，最好加一条 LIMIT 1，避免因为表中数据过大，查询全表数据导致数据库卡死。

默认从 0 下标开始，筛选多条记录。
limit 分页读取的执行顺序，是在最后的
也可以指定下标开始筛选后面跟的是步长。从指定位置开始，连续读取多条记录。

select * from exam_result limit 4 offset 3;

LIMIT 后面跟的是筛选几行，OFFSET 后面跟的是从那行开始。

limit 可以进行分页。就比如数据多就可以这样进行分页读