表的增删改查, 英文简称CRUD
Create: 增, Retrieve: 查, Update: 改, Delete: 删
下面一一介绍, 新建一张学生表为例进行操作
MariaDB [prictice]> create table student(
-> id int unsigned primary key auto_increment,
-> stu_id int not null unique,
-> name char(6) not null,
-> chinese float(3, 1) not null comment '语文成绩',
-> math float(3, 1) not null comment '数学成绩',
-> english float(3, 1) not null comment '英语成绩'
-> );
①Create(增)
单行全列插入
MariaDB [prictice]> insert into student values
-> (1, 10001, '小明', 69.5, 73.5, 59.0);
单行指定列插入
MariaDB [prictice]> insert into student(stu_id, name, chinese, math, english) values
-> (10002, '小红', 99.5, 93.0, 95.5);
多行全列插入
MariaDB [prictice]> insert into student values
-> (3, 10003, '小华', 72.5, 73.0, 69.0),
-> (4, 10004, '小芳', 92.0, 93.5, 92.5);
多行指定列插入
MariaDB [prictice]> insert into student(stu_id, name, chinese, math, english) values
-> (10005, '小白', 71.0, 73.0, 77.5),
-> (10006, '小黑', 67.5, 66.5, 61.0);
插入否则更新
插入数据如果和主键或唯一键冲突, 则进行更新, 否则直接插入
MariaDB [prictice]> insert into student values
-> (1, 10001, '大明', 71.0, 83.5, 86.0)
-> on duplicate key update name = '大明', chinese = 71.0, math = 83.5, english = 86.0;
-- 0 row affected: 表中有冲突数据，但冲突数据的值和 update 的值相等
-- 1 row affected: 表中没有冲突数据，数据被插入
-- 2 row affected: 表中有冲突数据，并且数据已经被更新
可以通过函数row_count()来获取受到影响的行数
select row_count();
替换
插入数据和主键或唯一键冲突, 删除后插入, 否则直接插入
MariaDB [prictice]> replace into student values
-> (6, 10006, '小黄', 77.0, 81.5, 99.5);
-- 1 row affected: 表中没有冲突数据，数据被插入
-- 2 row affected: 表中有冲突数据，删除后重新插入
②Retrieve(查)
全列查询
MariaDB [prictice]> select * from student;
+----+--------+--------+---------+------+---------+
| id | stu_id | name | chinese | math | english |
+----+--------+--------+---------+------+---------+
| 1 | 10001 | 大明 | 71.0 | 83.5 | 86.0 |
| 2 | 10002 | 小红 | 99.5 | 93.0 | 95.5 |
| 3 | 10003 | 小华 | 72.5 | 73.0 | 69.0 |
| 4 | 10004 | 小芳 | 92.0 | 93.5 | 92.5 |
| 5 | 10005 | 小白 | 71.0 | 73.0 | 77.5 |
| 6 | 10006 | 小黄 | 77.0 | 81.5 | 99.5 |
+----+--------+--------+---------+------+---------+
指定列查询
MariaDB [prictice]> select stu_id, name, chinese from student;
+--------+--------+---------+
| stu_id | name | chinese |
+--------+--------+---------+
| 10001 | 大明 | 71.0 |
| 10002 | 小红 | 99.5 |
| 10003 | 小华 | 72.5 |
| 10004 | 小芳 | 92.0 |
| 10005 | 小白 | 71.0 |
| 10006 | 小黄 | 77.0 |
+--------+--------+---------+
查询字段为表达式
MariaDB [prictice]> select stu_id, name, 1, chinese + 1, chinese + math from student;
+--------+--------+---+-------------+----------------+
| stu_id | name | 1 | chinese + 1 | chinese + math |
+--------+--------+---+-------------+----------------+
| 10001 | 大明 | 1 | 72.0 | 154.5 |
| 10002 | 小红 | 1 | 100.5 | 192.5 |
| 10003 | 小华 | 1 | 73.5 | 145.5 |
| 10004 | 小芳 | 1 | 93.0 | 185.5 |
| 10005 | 小白 | 1 | 72.0 | 144.0 |
| 10006 | 小黄 | 1 | 78.0 | 158.5 |
+--------+--------+---+-------------+----------------+
为查询结果指定别名(as可省)
MariaDB [prictice]> select stu_id, name, chinese + math + english as 总分 from student;
+--------+--------+--------+
| stu_id | name | 总分 |
+--------+--------+--------+
| 10001 | 大明 | 240.5 |
| 10002 | 小红 | 288.0 |
| 10003 | 小华 | 214.5 |
| 10004 | 小芳 | 278.0 |
| 10005 | 小白 | 221.5 |
| 10006 | 小黄 | 258.0 |
+--------+--------+--------+
结果去重
MariaDB [prictice]> select distinct chinese from student;
+---------+
| chinese |
+---------+
| 71.0 |
| 99.5 |
| 72.5 |
| 92.0 |
| 77.0 |
+---------+
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 个）任意字符；_ 表示任意一个字符
逻辑运算符
AND
多个条件必须都为 TRUE(1)，结果才是 TRUE(1)
OR
任意一个条件为 TRUE(1), 结果为 TRUE(1)
NOT
条件为 TRUE(1)，结果为 FALSE(0)
简单示例
MariaDB [prictice]> select stu_id, name, chinese from student where chinese > 80;
+--------+--------+---------+
| stu_id | name | chinese |
+--------+--------+---------+
| 10002 | 小红 | 99.5 |
| 10004 | 小芳 | 92.0 |
+--------+--------+---------+
MariaDB [prictice]> select stu_id, name, chinese from student where chinese = 71;
+--------+--------+---------+
| stu_id | name | chinese |
+--------+--------+---------+
| 10001 | 大明 | 71.0 |
| 10005 | 小白 | 71.0 |
+--------+--------+---------+
MariaDB [prictice]> select stu_id, name, chinese from student where chinese != 71;
+--------+--------+---------+
| stu_id | name | chinese |
+--------+--------+---------+
| 10002 | 小红 | 99.5 |
| 10003 | 小华 | 72.5 |
| 10004 | 小芳 | 92.0 |
| 10006 | 小黄 | 77.0 |
+--------+--------+---------+
MariaDB [prictice]> select stu_id, name, chinese from student where chinese between 90 and 100;
+--------+--------+---------+
| stu_id | name | chinese |
+--------+--------+---------+
| 10002 | 小红 | 99.5 |
| 10004 | 小芳 | 92.0 |
+--------+--------+---------+
MariaDB [prictice]> select stu_id, name, chinese from student where chinese in(71, 72, 73);
+--------+--------+---------+
| stu_id | name | chinese |
+--------+--------+---------+
| 10001 | 大明 | 71.0 |
| 10005 | 小白 | 71.0 |
+--------+--------+---------+
MariaDB [prictice]> select stu_id, name, chinese from student where name like '小%';
+--------+--------+---------+
| stu_id | name | chinese |
+--------+--------+---------+
| 10002 | 小红 | 99.5 |
| 10003 | 小华 | 72.5 |
| 10004 | 小芳 | 92.0 |
| 10005 | 小白 | 71.0 |
| 10006 | 小黄 | 77.0 |
+--------+--------+---------+
MariaDB [prictice]> select * from student where name like '小%' and chinese > 90;
+----+--------+--------+---------+------+---------+
| id | stu_id | name | chinese | math | english |
+----+--------+--------+---------+------+---------+
| 2 | 10002 | 小红 | 99.5 | 93.0 | 95.5 |
| 4 | 10004 | 小芳 | 92.0 | 93.5 | 92.5 |
+----+--------+--------+---------+------+---------+
MariaDB [prictice]> select * from student where name like '大%' or chinese > 90;
+----+--------+--------+---------+------+---------+
| id | stu_id | name | chinese | math | english |
+----+--------+--------+---------+------+---------+
| 1 | 10001 | 大明 | 71.0 | 83.5 | 86.0 |
| 2 | 10002 | 小红 | 99.5 | 93.0 | 95.5 |
| 4 | 10004 | 小芳 | 92.0 | 93.5 | 92.5 |
+----+--------+--------+---------+------+---------+
MariaDB [prictice]> select * from student where name not like '小%';
+----+--------+--------+---------+------+---------+
| id | stu_id | name | chinese | math | english |
+----+--------+--------+---------+------+---------+
| 1 | 10001 | 大明 | 71.0 | 83.5 | 86.0 |
+----+--------+--------+---------+------+---------+
结果排序
MariaDB [prictice]> select * from student order by chinese;
+----+--------+--------+---------+------+---------+
| id | stu_id | name | chinese | math | english |
+----+--------+--------+---------+------+---------+
| 1 | 10001 | 大明 | 71.0 | 83.5 | 86.0 |
| 5 | 10005 | 小白 | 71.0 | 73.0 | 77.5 |
| 3 | 10003 | 小华 | 72.5 | 73.0 | 69.0 |
| 6 | 10006 | 小黄 | 77.0 | 81.5 | 99.5 |
| 4 | 10004 | 小芳 | 92.0 | 93.5 | 92.5 |
| 2 | 10002 | 小红 | 99.5 | 93.0 | 95.5 |
+----+--------+--------+---------+------+---------+
MariaDB [prictice]> select * from student order by chinese desc;
+----+--------+--------+---------+------+---------+
| id | stu_id | name | chinese | math | english |
+----+--------+--------+---------+------+---------+
| 2 | 10002 | 小红 | 99.5 | 93.0 | 95.5 |
| 4 | 10004 | 小芳 | 92.0 | 93.5 | 92.5 |
| 6 | 10006 | 小黄 | 77.0 | 81.5 | 99.5 |
| 3 | 10003 | 小华 | 72.5 | 73.0 | 69.0 |
| 1 | 10001 | 大明 | 71.0 | 83.5 | 86.0 |
| 5 | 10005 | 小白 | 71.0 | 73.0 | 77.5 |
+----+--------+--------+---------+------+---------+
筛选分页结果
从0开始筛选n = 3条结果
MariaDB [prictice]> select * from student order by chinese limit 3;
+----+--------+--------+---------+------+---------+
| id | stu_id | name | chinese | math | english |
+----+--------+--------+---------+------+---------+
| 1 | 10001 | 大明 | 71.0 | 83.5 | 86.0 |
| 5 | 10005 | 小白 | 71.0 | 73.0 | 77.5 |
| 3 | 10003 | 小华 | 72.5 | 73.0 | 69.0 |
+----+--------+--------+---------+------+---------+
从s = 0开始筛选n = 3条结果
MariaDB [prictice]> select * from student order by chinese limit 0, 3;
+----+--------+--------+---------+------+---------+
| id | stu_id | name | chinese | math | english |
+----+--------+--------+---------+------+---------+
| 1 | 10001 | 大明 | 71.0 | 83.5 | 86.0 |
| 5 | 10005 | 小白 | 71.0 | 73.0 | 77.5 |
| 3 | 10003 | 小华 | 72.5 | 73.0 | 69.0 |
+----+--------+--------+---------+------+---------+
从s = 0开始, 筛选n = 3条结果
MariaDB [prictice]> select * from student order by chinese limit 3 offset 0;
+----+--------+--------+---------+------+---------+
| id | stu_id | name | chinese | math | english |
+----+--------+--------+---------+------+---------+
| 1 | 10001 | 大明 | 71.0 | 83.5 | 86.0 |
| 5 | 10005 | 小白 | 71.0 | 73.0 | 77.5 |
| 3 | 10003 | 小华 | 72.5 | 73.0 | 69.0 |
+----+--------+--------+---------+------+---------+
③Update(改)
MariaDB [prictice]> select * from student where name = '大明';
+----+--------+--------+---------+------+---------+
| id | stu_id | name | chinese | math | english |
+----+--------+--------+---------+------+---------+
| 1 | 10001 | 大明 | 71.0 | 83.5 | 86.0 |
+----+--------+--------+---------+------+---------+
MariaDB [prictice]> update student set chinese = 100.0 where name = '大明';
MariaDB [prictice]> select * from student where name = '大明';
+----+--------+--------+---------+------+---------+
| id | stu_id | name | chinese | math | english |
+----+--------+--------+---------+------+---------+
| 1 | 10001 | 大明 | 100.0 | 83.5 | 86.0 |
+----+--------+--------+---------+------+---------+
④Delete(删)
MariaDB [prictice]> select * from student;
+----+--------+--------+---------+------+---------+
| id | stu_id | name | chinese | math | english |
+----+--------+--------+---------+------+---------+
| 1 | 10001 | 大明 | 100.0 | 83.5 | 86.0 |
| 2 | 10002 | 小红 | 99.5 | 93.0 | 95.5 |
| 3 | 10003 | 小华 | 72.5 | 73.0 | 69.0 |
| 4 | 10004 | 小芳 | 92.0 | 93.5 | 92.5 |
| 5 | 10005 | 小白 | 71.0 | 73.0 | 77.5 |
| 6 | 10006 | 小黄 | 77.0 | 81.5 | 99.5 |
+----+--------+--------+---------+------+---------+
MariaDB [prictice]> delete from student where name = '小黄';
MariaDB [prictice]> select * from student;
+----+--------+--------+---------+------+---------+
| id | stu_id | name | chinese | math | english |
+----+--------+--------+---------+------+---------+
| 1 | 10001 | 大明 | 100.0 | 83.5 | 86.0 |
| 2 | 10002 | 小红 | 99.5 | 93.0 | 95.5 |
| 3 | 10003 | 小华 | 72.5 | 73.0 | 69.0 |
| 4 | 10004 | 小芳 | 92.0 | 93.5 | 92.5 |
| 5 | 10005 | 小白 | 71.0 | 73.0 | 77.5 |
+----+--------+--------+---------+------+---------+