本篇博客介绍表的增删改查操作。
表的增删改查,英文简称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 |
+----+--------+--------+---------+------+---------+
删除整表数据
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 | 小白 | 100.0 | 73.0 | 77.5 |
+----+--------+--------+---------+------+---------+
MariaDB [prictice]> delete from student;
MariaDB [prictice]> select * from student;
Empty set (0.00 sec)
截断表
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 | 小白 | 100.0 | 73.0 | 77.5 |
+----+--------+--------+---------+------+---------+
MariaDB [prictice]> truncate student;
MariaDB [prictice]> select * from student;
Empty set (0.00 sec)
注意:该操作慎用。
- 只能对整表操作,不能像delete一样针对部分数据操作。
- 实际上MySQL不对数据操作,所以比delete快。
- 会重置auto_increment项。
二者区别
删除整表数据不会重置自增主键。
截断表会重置自增主键。
聚合函数
函数 | 说明 |
COUNT([DISTINCT] expr) | 返回查询到的数据的数量 |
SUM([DISTINCT] expr) | 返回查询到的数据的总和,不是数字没有意义 |
AVG([DISTINCT] expr) | 返回查询到的数据的平均值,不是数字没有意义 |
MAX([DISTINCT] expr) | 返回查询到的数据的最大值,不是数字没有意义 |
MIN([DISTINCT] expr) | 返回查询到的数据的最小值,不是数字没有意义 |
示例:
创建一张学生表。
MariaDB [lab330]> create table student(
-> id int unsigned primary key auto_increment comment '序号',
-> name varchar(10) not null comment '姓名',
-> chinese float(3, 1) not null comment '语文成绩',
-> math float(3, 1) not null comment '数学成绩',
-> english float(3, 1) not null comment '英语成绩'
-> );
Query OK, 0 rows affected (0.00 sec)
插入数据。
MariaDB [lab330]> insert into student values
-> (1, '范庄元', 93.5, 90.0, 79.5),
-> (2, '林英新', 89.5, 88.0, 92.5),
-> (3, '牛孝通', 83.5, 79.0, 98.5),
-> (4, '胡南', 93.0, 92.0, 99.5),
-> (5, '邢玉莹', 83.5, 88.0, 90.5),
-> (6, '郭梦杰', 91.5, 79.5, 89.0);
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0
聚合函数count的使用。
-- 统计学生的数量
MariaDB [lab330]> select count(*) from student;
+----------+
| count(*) |
+----------+
| 6 |
+----------+
1 row in set (0.00 sec)
--统计语文成绩的数量
MariaDB [lab330]> select count(chinese) from student;
+----------------+
| count(chinese) |
+----------------+
| 6 |
+----------------+
1 row in set (0.00 sec)
--统计去重之后语文成绩的数量
MariaDB [lab330]> select count(distinct chinese) from student;
+-------------------------+
| count(distinct chinese) |
+-------------------------+
| 5 |
+-------------------------+
1 row in set (0.00 sec)
聚合函数sum的使用。
--统计英语总分
MariaDB [lab330]> select sum(english) from student;
+--------------+
| sum(english) |
+--------------+
| 549.5 |
+--------------+
1 row in set (0.00 sec)
聚合函数avg的使用。
--统计数学平均分
MariaDB [lab330]> select avg(math) from student;
+-----------+
| avg(math) |
+-----------+
| 86.08333 |
+-----------+
1 row in set (0.00 sec)
聚合函数max的使用。
--查询语文最高分
MariaDB [lab330]> select max(chinese) from student;
+--------------+
| max(chinese) |
+--------------+
| 93.5 |
+--------------+
1 row in set (0.00 sec)
聚合函数min的使用。
---查询英语最低分
MariaDB [lab330]> select min(english) from student;
+--------------+
| min(english) |
+--------------+
| 79.5 |
+--------------+
1 row in set (0.00 sec)