CRUD : Create ; Retrieve ; Update ; Delete
以一张学生表为例
//创建表
create table students(
id int unsigned primary key auto_increment,
sn int not null unique comment '学号',
name varchar(20) not null, qq varchar(20)
);
MariaDB [test1]> desc students;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| sn | int(11) | NO | UNI | NULL | |
| name | varchar(20) | NO | | NULL | |
| qq | varchar(20) | YES | | NULL | |
+-------+------------------+------+-----+---------+----------------+
单行数据 + 全列插入
//插入两条数据,value_list数量必须和定义表的列的数量和顺序一致
MariaDB [test1]> insert into students values(50,500,'小明',NULL);
Query OK, 1 row affected (0.00 sec)
MariaDB [test1]> insert into students values(51,501,'小张','123456');
Query OK, 1 row affected (0.00 sec)
MariaDB [test1]> select * from students;
+----+-----+--------+--------+
| id | sn | name | qq |
+----+-----+--------+--------+
| 50 | 500 | 小明 | NULL |
| 51 | 501 | 小张 | 123456 |
+----+-----+--------+--------+
2 rows in set (0.00 sec)
多行数据 + 指定列插入
//插入两条记录，value_list 数量必须和指定列数量及顺序一致
MariaDB [test1]> insert into students (id, sn, name) values
-> (52, 600,'小杨'),
-> (53, 601,'小孙');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [test1]> select * from students;
+----+-----+--------+--------+
| id | sn | name | qq |
+----+-----+--------+--------+
| 50 | 500 | 小明 | NULL |
| 51 | 501 | 小张 | 123456 |
| 52 | 600 | 小杨 | NULL |
| 53 | 601 | 小孙 | NULL |
+----+-----+--------+--------+
4 rows in set (0.00 sec)
插入是否更新
//由于主键或者唯一键对应的值已经存在导致插入失败
//主键冲突
MariaDB [test1]> insert into students (id, sn, name) values (50,502,'小明明');
ERROR 1062 (23000): Duplicate entry '50' for key 'PRIMARY'
//唯一键冲突
MariaDB [test1]> insert into students (sn, name) values (501,'小张张');
ERROR 1062 (23000): Duplicate entry '501' for key 'sn'
//可以选择性的进行同步更新操作
INSERT ... ON DUPLICATE KEY UPDATE
column = value [, column = value] ...
MariaDB [test1]> insert into students (id, sn, name) values (50,502,'小小明')
on duplicate key update sn=502, name='小小明';
Query OK, 2 rows affected (0.00 sec)
//0 row affected: 表中有冲突数据，但冲突数据的值和 update 的值相等
//1 row affected: 表中没有冲突数据，数据被插入
//2 row affected: 表中有冲突数据，并且数据已经被更新
MariaDB [test1]> select row_count();
+-------------+
| row_count() |
+-------------+
| 2 |
+-------------+
1 row in set (0.00 sec)
MariaDB [test1]> select *from students;
+----+-----+-----------+--------+
| id | sn | name | qq |
+----+-----+-----------+--------+
| 50 | 502 | 小小明 | NULL |
| 51 | 501 | 小张 | 123456 |
| 52 | 600 | 小杨 | NULL |
| 53 | 601 | 小孙 | NULL |
+----+-----+-----------+--------+
4 rows in set (0.00 sec)
替换
主键 或者 唯一键 没有冲突，则直接插入；
主键 或者 唯一键 如果冲突，则删除后再插入
MariaDB [test1]> replace into students(sn, name) values(601,'小小孙');
Query OK, 2 rows affected (0.03 sec)
//1 row affected: 表中没有冲突数据，数据被插入
//2 row affected: 表中有冲突数据，删除后重新插入
MariaDB [test1]> select *from students;
+----+-----+-----------+--------+
| id | sn | name | qq |
+----+-----+-----------+--------+
| 50 | 502 | 小小明 | NULL |
| 51 | 501 | 小张 | 123456 |
| 52 | 600 | 小杨 | NULL |
| 55 | 601 | 小小孙 | NULL |
+----+-----+-----------+--------+
4 rows in set (0.00 sec)
Retrieve(检索)
语法:
SELECT
[DISTINCT] {* | {column [, column] ...}
[FROM table_name]
[WHERE ...]
[ORDER BY column [ASC | DESC], ...]
LIMIT ...
//创建表结构
CREATE TABLE exam_result (id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) NOT NULL COMMENT '同学姓名',
yuwen float DEFAULT 0.0 COMMENT '语文成绩',
shuxue float DEFAULT 0.0 COMMENT '数学成绩',
yingyu float DEFAULT 0.0 COMMENT '英语成绩'
);
//指定列查询
select id,name,yingyu from exam_result;
+----+--------+--------+
| id | name | yingyu |
+----+--------+--------+
| 1 | 小红 | 68 |
| 2 | 小绿 | 60 |
| 3 | 小黑 | 72 |
| 4 | 小黄 | 74 |
| 5 | 小橙 | 73 |
| 6 | 小青 | 75 |
| 7 | 小蓝 | 70 |
| 8 | 小白 | 82 |
+----+--------+--------+
8 rows in set (0.01 sec)
//全列查询
MariaDB [test1]> select * from exam_result;
+----+--------+-------+--------+--------+
| id | name | yuwen | shuxue | yingyu |
+----+--------+-------+--------+--------+
| 1 | 小红 | 66 | 67 | 68 |
| 2 | 小绿 | 68 | 69 | 60 |
| 3 | 小黑 | 70 | 71 | 72 |
| 4 | 小黄 | 72 | 73 | 74 |
| 5 | 小橙 | 75 | 74 | 73 |
| 6 | 小青 | 78 | 79 | 75 |
| 7 | 小蓝 | 76 | 77 | 70 |
| 8 | 小白 | 80 | 81 | 82 |
+----+--------+-------+--------+--------+
8 rows in set (0.00 sec)
表达式包含一个字段
MariaDB [test1]> select id, name, yuwen + 10 from exam_result;
+----+--------+------------+
| id | name | yuwen + 10 |
+----+--------+------------+
| 1 | 小红 | 76 |
| 2 | 小绿 | 78 |
| 3 | 小黑 | 80 |
| 4 | 小黄 | 82 |
| 5 | 小橙 | 85 |
| 6 | 小青 | 88 |
| 7 | 小蓝 | 86 |
| 8 | 小白 | 90 |
+----+--------+------------+
8 rows in set (0.00 sec)
//表达式包含多个字段
MariaDB [test1]> select id, name, yuwen + yingyu + shuxue from exam_result;
+----+--------+-------------------------+
| id | name | yuwen + yingyu + shuxue |
+----+--------+-------------------------+
| 1 | 小红 | 201 |
| 2 | 小绿 | 197 |
| 3 | 小黑 | 213 |
| 4 | 小黄 | 219 |
| 5 | 小橙 | 222 |
| 6 | 小青 | 232 |
| 7 | 小蓝 | 223 |
| 8 | 小白 | 243 |
+----+--------+-------------------------+
8 rows in set (0.00 sec)
为查询结果指定别名
语法 : SELECT column [AS] alias_name [...] FROM table_name;
MariaDB [test1]> select id, name, yuwen + yingyu + shuxue 总分 from exam_result;
+----+--------+--------+
| id | name | 总分 |
+----+--------+--------+
| 1 | 小红 | 201 |
| 2 | 小绿 | 197 |
| 3 | 小黑 | 213 |
| 4 | 小黄 | 219 |
| 5 | 小橙 | 222 |
| 6 | 小青 | 232 |
| 7 | 小蓝 | 223 |
| 8 | 小白 | 243 |
+----+--------+--------+
//去重
SELECT DISTINCT shuxue FROM exam_result;where条件
比较运算符:
运算符 | 说明 |
> , >= , < , <= | 正常运算符 |
= | 等于,NULL不安全, NULL = NULL的结果是NULL |
<=> | 等于,NULL安全,NULL<=>NULL的结果是TRUE(1) |
!=, <> | 不等于 |
BETWEEN A0 AND A1 | 范围匹配[a0 , a1] 如果a0 <= value <=a1 |
IN(option,…) | 如果是option中的任意一个,返回true(1) |
IS NULL | 是NULL |
IS NOT NULL | 不是空 |
LIKE | 模糊匹配,% 表示任意多个(包括 0 个) 任意字符; _表示任意一个字符 |
逻辑运算符
运算符 | 说明 |
AND | 多个条件必须都为TRUE(1) , 结果才是TRUE(1) |
OR | 任意一个条件为TRUE(1) |
NOT | 条件为TRUE(1), 结果为FALSE(0) |
MariaDB [test1]> select name ,yingyu from exam_result where yingyu < 70;
+--------+--------+
| name | yingyu |
+--------+--------+
| 小红 | 68 |
| 小绿 | 60 |
+--------+--------+
2 rows in set (0.00 sec)
MariaDB [test1]> select name ,yingyu from exam_result where yingyu >= 70 and yingyu <= 80;
+--------+--------+
| name | yingyu |
+--------+--------+
| 小黑 | 72 |
| 小黄 | 74 |
| 小橙 | 73 |
| 小青 | 75 |
| 小蓝 | 70 |
+--------+--------+
5 rows in set (0.00 sec
MariaDB [test1]> select name ,yingyu from exam_result where yingyu between 70 and 80;
+--------+--------+
| name | yingyu |
+--------+--------+
| 小黑 | 72 |
| 小黄 | 74 |
| 小橙 | 73 |
| 小青 | 75 |
| 小蓝 | 70 |
+--------+--------+
5 rows in set (0.00 sec)
MariaDB [test1]> select name ,shuxue from exam_result where shuxue = 71 or shuxue = 73 or shuxue = 79;
+--------+--------+
| name | shuxue |
+--------+--------+
| 小黑 | 71 |
| 小黄 | 73 |
| 小青 | 79 |
+--------+--------+
3 rows in set (0.00 sec)
MariaDB [test1]> select name ,shuxue from exam_result where shuxue in(71,73,79);
+--------+--------+
| name | shuxue |
+--------+--------+
| 小黑 | 71 |
| 小黄 | 73 |
| 小青 | 79 |
+--------+--------+
3 rows in set (0.00 sec)
//_ 匹配严格的一个任意字符
MariaDB [test1]> select name from exam_result where name like '小__';
+-----------+
| name |
+-----------+
| 小小红 |
+-----------+
1 row in set (0.00 sec)
//匹配任意多个字符 "%" .
MariaDB [test1]> select name from exam_result where name like '小%';
+-----------+
| name |
+-----------+
| 小红 |
| 小绿 |
| 小黑 |
| 小黄 |
| 小橙 |
| 小青 |
| 小蓝 |
| 小白 |
| 小小红 |
+-----------+
9 rows in set (0.00 sec)
MariaDB [test1]> select name,yuwen,shuxue from exam_result where yuwen > shuxue ;
+--------+-------+--------+
| name | yuwen | shuxue |
+--------+-------+--------+
| 小橙 | 75 | 74 |
+--------+-------+--------+
1 row in set (0.01 sec)
MariaDB [test1]> select name,yuwen+shuxue+yingyu 总分 from exam_result where yuwen +shuxue+ yingyu <220;
+--------+--------+
| name | 总分 |
+--------+--------+
| 小红 | 201 |
| 小绿 | 197 |
| 小黑 | 213 |
| 小黄 | 219 |
+--------+--------+
4 rows in set (0.00 sec)NULL的查询
MariaDB [test1]> select NULL=NULL,NULL =1,NULL=0;
+-----------+---------+--------+
| NULL=NULL | NULL =1 | NULL=0 |
+-----------+---------+--------+
| NULL | NULL | NULL |
+-----------+---------+--------+
1 row in set (0.00 sec)
MariaDB [test1]> select NULL<=>NULL,NULL<=>1,NULL<=>0;
+-------------+----------+----------+
| NULL<=>NULL | NULL<=>1 | NULL<=>0 |
+-------------+----------+----------+
| 1 | 0 | 0 |
+-------------+----------+----------+
1 row in set (0.00 sec)结果排序
语法:
ASC 为升序（从小到大）
DESC 为降序（从大到小）
默认为 ASC
SELECT ... FROM table_name [WHERE ...]
ORDER BY column [ASC|DESC], [...];
注意：没有 ORDER BY 子句的查询，返回的顺序是未定义的，永远不要依赖这个顺序NULL 视为比任何值都小，升序出现在最上面
NULL 视为比任何值都小，降序出现在最下面
多字段排序，排序优先级随书写顺序
MariaDB [test1]> select name ,shuxue,yuwen,yingyu from exam_result order by shuxue asc ,yingyu asc,yuwen asc;
+-----------+--------+-------+--------+
| name | shuxue | yuwen | yingyu |
+-----------+--------+-------+--------+
| 小红 | 67 | 66 | 68 |
| 小绿 | 69 | 68 | 60 |
| 小黑 | 71 | 70 | 72 |
| 小黄 | 73 | 72 | 74 |
| 小橙 | 74 | 75 | 73 |
| 小蓝 | 77 | 76 | 70 |
| 小青 | 79 | 78 | 75 |
| 小小红 | 80 | 70 | 90 |
| 小白 | 81 | 80 | 82 |
+-----------+--------+-------+--------+
9 rows in set (0.00 sec)
总分降序排序
MariaDB [test1]> select name ,shuxue+yuwen+yingyu 总分 from exam_result order by 总分 desc;
+-----------+--------+
| name | 总分 |
+-----------+--------+
| 小白 | 243 |
| 小小红 | 240 |
| 小青 | 232 |
| 小蓝 | 223 |
| 小橙 | 222 |
| 小黄 | 219 |
| 小黑 | 213 |
| 小红 | 201 |
| 小绿 | 197 |
+-----------+--------+
9 rows in set (0.00 sec)筛选分页结果
语法:
-- 起始下标为 0
-- 从 0 开始，筛选 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n;
-- 从 s 开始，筛选 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT s, n;
-- 从 s 开始，筛选 n 条结果，比第二种用法更明确，建议使用
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n OFFSET n;
建议：对未知表进行查询时，最好加一条 LIMIT 1，避免因为表中数据过大，查询全表数据导致数据库卡死
按 id 进行分页，每页 3 条记录，分别显示 第 1、2、3 页
MariaDB [test1]> select id,name,shuxue,yingyu ,yuwen from exam_result order by id limit 5 offset 0;
+----+--------+--------+--------+-------+
| id | name | shuxue | yingyu | yuwen |
+----+--------+--------+--------+-------+
| 1 | 小红 | 67 | 68 | 66 |
| 2 | 小绿 | 69 | 60 | 68 |
| 3 | 小黑 | 71 | 72 | 70 |
| 4 | 小黄 | 73 | 74 | 72 |
| 5 | 小橙 | 74 | 73 | 75 |
+----+--------+--------+--------+-------+
5 rows in set (0.00 sec)
MariaDB [test1]> select id,name,shuxue,yingyu ,yuwen from exam_result order by id limit 5 offset 5;
+----+-----------+--------+--------+-------+
| id | name | shuxue | yingyu | yuwen |
+----+-----------+--------+--------+-------+
| 6 | 小青 | 79 | 75 | 78 |
| 7 | 小蓝 | 77 | 70 | 76 |
| 8 | 小白 | 81 | 82 | 80 |
| 9 | 小小红 | 80 | 90 | 70 |
+----+-----------+--------+--------+-------+
4 rows in set (0.00 sec)updata数据更新
UPDATE table_name SET column = expr [, column = expr ...]
[WHERE ...] [ORDER BY ...] [LIMIT ...]MariaDB [test1]> select name,yuwen + shuxue + yingyu 总分 from exam_result order by 总分 limit 3;
+--------+--------+
| name | 总分 |
+--------+--------+
| 小绿 | 197 |
| 小红 | 201 |
| 小黑 | 213 |
+--------+--------+
3 rows in set (0.00 sec)
MariaDB [test1]> update exam_result set shuxue = shuxue +30 order by yuwen + shuxue + yingyu limit 3
;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
更新全表
MariaDB [test1]> select * from exca_result;
ERROR 1146 (42S02): Table 'test1.exca_result' doesn't exist
MariaDB [test1]> select * from exam_result;
+----+-----------+-------+--------+--------+
| id | name | yuwen | shuxue | yingyu |
+----+-----------+-------+--------+--------+
| 1 | 小红 | 66 | 97 | 68 |
| 2 | 小绿 | 68 | 99 | 60 |
| 3 | 小黑 | 70 | 101 | 72 |
| 4 | 小黄 | 72 | 73 | 74 |
| 5 | 小橙 | 75 | 74 | 73 |
| 6 | 小青 | 78 | 79 | 75 |
| 7 | 小蓝 | 76 | 77 | 70 |
| 8 | 小白 | 80 | 81 | 82 |
| 9 | 小小红 | 70 | 80 | 90 |
+----+-----------+-------+--------+--------+
9 rows in set (0.00 sec)
MariaDB [test1]> update exam_result set yuwen = yuwen + 30;
Query OK, 9 rows affected (0.00 sec)
Rows matched: 9 Changed: 9 Warnings: 0
MariaDB [test1]> select * from exam_result;
+----+-----------+-------+--------+--------+
| id | name | yuwen | shuxue | yingyu |
+----+-----------+-------+--------+--------+
| 1 | 小红 | 96 | 97 | 68 |
| 2 | 小绿 | 98 | 99 | 60 |
| 3 | 小黑 | 100 | 101 | 72 |
| 4 | 小黄 | 102 | 73 | 74 |
| 5 | 小橙 | 105 | 74 | 73 |
| 6 | 小青 | 108 | 79 | 75 |
| 7 | 小蓝 | 106 | 77 | 70 |
| 8 | 小白 | 110 | 81 | 82 |
| 9 | 小小红 | 100 | 80 | 90 |
+----+-----------+-------+--------+--------+
9 rows in set (0.00 sec)delete
语法:
DELETE FROM table_name [WHERE ...] [ORDER BY ...] [LIMIT ...]MariaDB [test1]> select * from exam_result where name = '小红';
+----+--------+-------+--------+--------+
| id | name | yuwen | shuxue | yingyu |
+----+--------+-------+--------+--------+
| 1 | 小红 | 96 | 97 | 68 |
+----+--------+-------+--------+--------+
1 row in set (0.00 sec)
MariaDB [test1]> delete from exam_result where name = '小红';
Query OK, 1 row affected (0.00 sec)
MariaDB [test1]> select * from exam_result where name = '小红';
Empty set (0.00 sec)
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
