MySQL-增删改查操作（1）-CSDN博客

本文链接：https://blog.csdn.net/2301_80418043/article/details/142601598

1.Create新增

1.1语法

INSERT [INTO] table_name
 [(column [, column] ...)]
VALUES
 (value_list) [, (value_list)] ...
value_list: value, [, value] ...

1.2示例

#创建一个用于演示的表
create table users(
    id bigint,
    name varchar(20) comment '用户名'
);

1.2.1单行数据全列插入

value_list中值的数量必须和定义表的列的数量及顺序一致

# 插⼊第⼀条记录
mysql> insert into users values (1, '张三');
Query OK, 1 row affected (0.02 sec)
# 插⼊第⼆条记录
mysql> insert into users values (2, '李四');
Query OK, 1 row affected (0.01 sec)
# 查询结果
mysql> select * from users;
+------+------+
| id | name |
+------+------+
| 1 | 张三 |
| 2 | 李四 |
+------+------+
2 rows in set (0.00 sec)

1.2.2单行数据指定列插入

value_list 中值的数量必须和指定列数量及顺序⼀致

# 指定了具体要插⼊的列
mysql> insert into users(id, name) values (3, '王五');
Query OK, 1 row affected (0.01 sec)
mysql> select * from users;
+------+------+
| id | name |
+------+------+
| 1 | 张三 |
| 2 | 李四 |
| 3 | 王五 |
+------+------+
3 rows in set (0.00 sec)

1.2.3多行数据指定列插入

在⼀条INSERT语句中也可以指定多个value_list，实现⼀次插⼊多⾏数据

# 每个value_list表⽰⼀⾏数据
mysql> insert into users(id, name) values (4, '赵六'), (5, '钱七');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from users;
+------+------+
| id | name |
+------+------+
| 1 | 张三 |
| 2 | 李四 |
| 3 | 王五 |
| 4 | 赵六 |
| 5 | 钱七 |
+------+------+
5 rows in set (0.00 sec)

2.Retrieve检索

2.1语法

SELECT
 [DISTINCT]
 select_expr [, select_expr] ...
 [FROM table_references]
 [WHERE where_condition]
 [GROUP BY {col_name | expr}, ...]
 [HAVING where_condition]
 [ORDER BY {col_name | expr } [ASC | DESC], ... ]
 [LIMIT {[offset,] row_count | row_count OFFSET offset}]

2.2示例

2.2.1构造数据

--创建表结构
CREATE TABLE exam (
 id BIGINT,
 name VARCHAR(20) COMMENT '同学姓名',
 chinese float COMMENT '语⽂成绩',
 math float COMMENT '数学成绩',
 english float COMMENT '英语成绩'
);
-- 插⼊测试数据
INSERT INTO exam (name, chinese, math, english) VALUES
(1, '唐三藏', 67, 98, 56),
(2, '孙悟空', 87, 78, 77),
(3, '猪悟能', 88, 98, 90),
(4, '曹孟德', 82, 84, 67),
(5, '刘⽞德', 55, 85, 45),
(6, '孙权', 70, 73, 78),
(7, '宋公明', 75, 65, 30);
Query OK, 7 rows affected (0.00 sec)
Records: 7 Duplicates: 0 Warnings: 0

2.3 Select

2.3.1全列查询

查询所有记录

# 使⽤ * 可以查询表中所有列的值
mysql> select * from exam;
+----+--------+---------+------+---------+
| id | name | chinese | math | english |
+----+--------+---------+------+---------+
| 1 | 唐三藏 |      67 | 98   | 56      |
| 2 | 孙悟空 |      87 | 78   | 77      |
| 3 | 猪悟能 |      88 | 98   | 90      |
| 4 | 曹孟德 |      82 | 84   | 67      |
| 5 | 刘⽞德 |      55 | 85   | 45      |
| 6 | 孙权   |      70 | 73   | 78      |
| 7 | 宋公明 | 75      | 65   | 30      |
+----+--------+---------+------+---------+
7 rows in set (0.00 sec)

2.3.2指定列查询

查询所有人的编号，姓名和语文成绩

mysql> select id, name, chinese from exam;
+----+--------+---------+
| id | name | chinese |
+----+--------+---------+
| 1 | 唐三藏 |      67 |
| 2 | 孙悟空 |      87 |
| 3 | 猪悟能 |      88 |
| 4 | 曹孟德 |      82 |
| 5 | 刘⽞德 |      55 |
| 6 | 孙权   |      70 |
| 7 | 宋公明 |      75 |
+----+--------+---------+
7 rows in set (0.00 sec)

在select后⾯的查询列表中指定希望查询的列，可以是⼀个也可以是多个，中间⽤逗号隔开

指定列的顺序与表结构中的列的顺序⽆关

2.3.3查询字段为表达式

常量表达式

# 表达式本⾝就是⼀个常
mysql> select id, name, 10 from exam;
+----+--------+----+
| id | name | 10 |
+----+--------+----+
| 1 | 唐三藏 | 10 |
| 2 | 孙悟空 | 10 |
| 3 | 猪悟能 | 10 |
| 4 | 曹孟德 | 10 |
| 5 | 刘⽞德 | 10 |
| 6 | 孙权 | 10 |
| 7 | 宋公明 | 10 |
+----+--------+----+
7 rows in set (0.00 sec)
# 也可以是常量的运算
mysql> select id, name, 10 + 1 from exam;
+----+--------+--------+
| id | name | 10 + 1 |
+----+--------+--------+
| 1 | 唐三藏 | 11     |
| 2 | 孙悟空 | 11     |
| 3 | 猪悟能 | 11     |
| 4 | 曹孟德 | 11     |
| 5 | 刘⽞德 | 11     |
| 6 | 孙权   | 11     |
| 7 | 宋公明 | 11     |
+----+--------+--------+
7 rows in set (0.01 sec)

把所有学生的语文成绩加10分

# 表达式中包含⼀个字段
mysql> select id, name, chinese + 10 from exam;
+----+--------+--------------+
| id | name | chinese + 10 |
+----+--------+--------------+
| 1 | 唐三藏 |           77 |
| 2 | 孙悟空 |           97 |
| 3 | 猪悟能 |           98 |
| 4 | 曹孟德 |           92 |
| 5 | 刘⽞德 |           65 |
| 6 | 孙权   |           80 |
| 7 | 宋公明 |           85 |
+----+--------+--------------+
7 rows in set (0.00 sec)

计算所有学生语文，数学和英语成绩的总分

# 表达式包含多个字段
mysql> select id, name, chinese + math + english from exam;
+----+--------+--------------------------+
| id | name | chinese + math + english |
+----+--------+--------------------------+
| 1 | 唐三藏 |                      221 |
| 2 | 孙悟空 |                      242 |
| 3 | 猪悟能 |                      276 |
| 4 | 曹孟德 |                      233 |
| 5 | 刘⽞德 |                      185 |
| 6 | 孙权   |                      221 |
| 7 | 宋公明 |                      170 |
+----+--------+--------------------------+
7 rows in set (0.00 sec)

2.3.4为查询结果指定别名

2.3.4.1语法

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

AS可以省略，别名如果包含空格必须⽤单引号包裹

2.3.4.2示例

为总分这一列指定别名

mysql> select id, name, chinese + math + english as 总分 from exam;
+----+--------+------+
| id | name | 总分   | # 表头以别名显⽰
+----+--------+------+
| 1 | 唐三藏 | 221   |
| 2 | 孙悟空 | 242   |
| 3 | 猪悟能 | 276   |
| 4 | 曹孟德 | 233   |
| 5 | 刘⽞德 | 185   |
| 6 | 孙权   | 221   |
| 7 | 宋公明 | 170   |
+----+--------+------+
7 rows in set (0.00 sec)

2.3.5结果去重查询

2.3.5.1查询当前所有的数学成绩

# 通过观察有两条98的记录
mysql> select math from exam;
+------+
| math |
+------+
| 98   | # 第⼀条
| 78   |
| 98   | # 第⼆条
| 84   |
| 85   |
| 73   |
| 65   |
+------+
7 rows in set (0.00 sec)

2.3.5.2在结果集中去除重复记录，可以使用DISTINCT

# 去重查询
mysql> select distinct math from exam;
+------+
| math |
+------+
| 98   |
| 78   |
| 84   |
| 85   |
| 73   |
| 65   |
+------+
6 rows in set (0.00 sec)

使用DISCTINCT去重时，只有查询列表中所有列的值都相同才会判定为重复

注意：

• 查询时不加限制条件会返回表中所有结果，如果表中的数据量过⼤，会把服务器的资源消耗殆尽

• 在⽣产环境不要使不加限制条件的查询

2.4Where条件查询

2.4.1语法

SELECT
 select_expr [, select_expr] ... [FROM table_references]
 WHERE where_condition

2.4.2比较运算符

运算符	说明
>,>=,<,<=	大于，大于等于，小于，小于等于
=	等于，对于NULL的比较不安全，比如NULL=NULL结果还是NULL
<=>	等于，对于NULL的比较是安全的，比如NULL<=>NULL结果是TRUE（1）
!=,<>	不等于
value BETWEEN a0 AND a1	范围匹配，[a0,a1]，如果a0<=value<=a1,返回TRUE或1，NOT BETWEEN则取反
value IN （option，...)	如果value在option列表中，则返回TRUE（1），NOT IN则取反
IS NULL	是NULL
IS NOT NULL	不是NULL
LIKE	模糊匹配，%表示任意多个（包括0个）字符；_表示任意一个字符，NOT LIKE则取反

2.4.3逻辑运算符

运算符	说明
AND	多个条件必须都为TRUE（1），结果才是TRUE（1）
OR	任意一个条件为TRUE（１），结果为TRUE（１）
NOT	条件为TRUE（１），结果为FALSE（０）

2.4.4示例

2.4.4.1基本查询

查询英语不及格的同学及英语成绩（＜６０）

mysql> select name, english from exam where english < 60;
+--------+---------+
| name | english |
+--------+---------+
| 唐三藏 | 56 　　|
| 刘⽞德 | 45　　 |
| 宋公明 | 30 　　|
+--------+---------+
3 rows in set (0.00 sec)

查询语文成绩高于英语成绩的同学

mysql> select name, chinese, english from exam where chinese > english;
+--------+---------+---------+
| name | chinese | english |
+--------+---------+---------+
| 唐三藏 | 　　67 | 　　　56 |
| 孙悟空 |　　 87 | 　　　77 |
| 曹孟德 |　　 82 | 　　　67 |
| 刘⽞德 | 　　55 | 　　　45 |
| 宋公明 | 　　75 |　　　 30 |
+--------+---------+---------+
5 rows in set (0.00 sec)

总分在200分以下的同学

mysql> select name, chinese + math + english as 总分 from exam where chinese + 
math + english < 200;
+--------+------+
| name | 总分 |
+--------+------+
| 刘⽞德 | 185 |
| 宋公明 | 170 |
+--------+------+
2 rows in set (0.00 sec)

2.4.4.2 AND和OR

查询语文成绩大于80分且英语成绩大于80分的同学

mysql> select * from exam where chinese > 80 and english > 80;
+----+--------+---------+------+---------+
| id | name | chinese | math | english |
+----+--------+---------+------+---------+
| 3 | 猪悟能 |　　　 88 | 　98 | 　　　90 |
+----+--------+---------+------+---------+
1 row in set (0.00 sec)

查询语文成绩大于80分或英语成绩大于80分的同学

mysql> select * from exam where chinese > 80 OR english > 80;
+----+--------+---------+------+---------+
| id | name | chinese | math | english |
+----+--------+---------+------+---------+
| 2 | 孙悟空 |      87 |   78 |      77 |
| 3 | 猪悟能 |      88 |   98 |      90 |
| 4 | 曹孟德 |      82 |   84 |      67 |
+----+--------+---------+------+---------+
3 rows in set (0.00 sec)

2.4.4.3范围查询

语文成绩在［８０，９０］分的同学及语文成绩

# 使⽤BETWEEN AND 实现
mysql> select name, chinese from exam where chinese between 80 and 90;
# 使⽤ AND 实现
mysql> select name, chinese from exam where chinese >= 80 and chinese <= 90;
+--------+---------+
| name | chinese |
+--------+---------+
| 孙悟空 | 87 |
| 猪悟能 | 88 |
| 曹孟德 | 82 |
+--------+---------+
3 rows in set(0.00 SEC)

数学成绩是 78 或者 79 或者 98 或者 99 分的同学及数学成绩

# 使⽤IN实现
mysql> select name, math from exam where math in (78, 79, 98, 99);
# 使⽤OR实现
mysql> select name, math from exam where math = 78 or math = 79 or math = 98 or
 math = 99;
+--------+------+
| name | math |
+--------+------+
| 唐三藏 | 98 |
| 孙悟空 | 78 |
| 猪悟能 | 98 |
+--------+------+
 3 rows in set (0.00 sec)

２.4.4.4模糊查询

查询所有姓孙的同学

mysql> select * from exam where name like '孙%';
+----+--------+---------+------+---------+
| id | name | chinese | math | english |
+----+--------+---------+------+---------+
| 2 | 孙悟空 |      87 |   78 |      77 |
| 6 | 孙权 |        70 |   73 |      78 |
+----+--------+---------+------+---------+
2 rows in set (0.00 sec)

查询姓孙且姓名共有两个字同学

mysql> select * from exam where name like '孙_';
+----+------+---------+------+---------+
| id | name | chinese | math | english |
+----+------+---------+------+---------+
| 6 | 孙权 |        70 |   73 |     78 |
+----+------+---------+------+---------+
1 row in set (0.00 sec)

2.4.4.5 NULL的查询

查询英语成绩为NULL的记录

mysql> select * from exam where english is null;
+----+------+---------+------+---------+
| id | name | chinese | math | english |
+----+------+---------+------+---------+
| 8  |  张⻜ |      27 |    0 |   NULL |
+----+------+---------+------+---------+
1 row in set (0.00 sec)

查询英语成绩不为NULL的记录

# 使⽤is not null
mysql> select * from exam where english is not null;
+----+--------+---------+------+---------+
| id | name | chinese | math | english |
+----+--------+---------+------+---------+
| 1 | 唐三藏 | 67 | 98 | 56 |
| 2 | 孙悟空 | 87 | 78 | 77 |
| 3 | 猪悟能 | 88 | 98 | 90 |
| 4 | 曹孟德 | 82 | 84 | 67 |
| 5 | 刘⽞德 | 55 | 85 | 45 |
| 6 | 孙权 | 70 | 73 | 78 |
| 7 | 宋公明 | 75 | 65 | 30 |
+----+--------+---------+------+---------+
7 rows in set (0.00 sec)

NULL与其他值进行运算结果为NULL

# 观察结果中的总分
mysql> select name, chinese + math + english as 总分 from exam;
+--------+------+
| name | 总分 |
+--------+------+
| 唐三藏 | 221 |
| 孙悟空 | 242 |
| 猪悟能 | 276 |
| 曹孟德 | 233 |
| 刘⽞德 | 185 |
| 孙权 | 221 |
| 宋公明 | 170 |
| 张⻜ | NULL |
+--------+------+
8 rows in set (0.00 sec)

注意

• WHERE条件中可以使⽤表达式，但不能使⽤别名

• AND的优先级⾼于OR，在同时使⽤时，建议使⽤⼩括号()包裹优先执⾏的部分

• 过滤NULL时不要使⽤等于号(=)与不等于号(!= , <>)

• NULL与任何值运算结果都为NULL

２.５Order by 排序

2.5.1语法

-- ASC 为升序（从⼩到⼤）
-- DESC 为降序（从⼤到⼩）
-- 默认为 ASC
SELECT ... FROM table_name [WHERE ...] ORDER BY {col_name | expr } [ASC | 
DESC], ... ;

2.5.2示例

按数学成绩从低到高排序（升序）

mysql> select name, math from exam order by math asc;
+--------+------+
| name | math |
+--------+------+
| 张⻜ | 0 |
| 宋公明 | 65 |
| 孙权 | 73 |
| 孙悟空 | 78 |
| 曹孟德 | 84 |
| 刘⽞德 | 85 |
| 唐三藏 | 98 |
| 猪悟能 | 98 |
+--------+------+
8 rows in set (0.00 sec)

按语文成绩从高到低排序（降序）

mysql> select name,chinese from exam order by chinese desc;
+--------+---------+
| name | chinese |
+--------+---------+
| 猪悟能 | 88 |
| 孙悟空 | 87 |
| 曹孟德 | 82 |
| 宋公明 | 75 |
| 孙权 | 70 |
| 唐三藏 | 67 |
| 刘⽞德 | 55 |
| 张⻜ | 27 |
+--------+---------+
8 rows in set (0.00 sec)

按英语成绩从高到低排序

mysql> select name, english from exam order by english desc;
+--------+---------+
| name | english |
+--------+---------+
| 猪悟能 | 90 |
| 孙权 | 78 |
| 孙悟空 | 77 |
| 曹孟德 | 67 |
| 唐三藏 | 56 |
| 刘⽞德 | 45 |
| 宋公明 | 30 |
| 张⻜ | NULL | # NULL被看做⽐任何值都⼩
+--------+---------+
8 rows in set (0.00 sec)

查询同学各⻔成绩，依次按数学降序，英语升序，语⽂升序的⽅式显⽰

mysql> select name, math, english, chinese from exam order by math desc, 
english asc, chinese asc;
+--------+------+---------+---------+
| name | math | english | chinese |
+--------+------+---------+---------+
| 唐三藏 | 98 | 56 | 67 |
| 猪悟能 | 98 | 90 | 88 |
| 刘⽞德 | 85 | 45 | 55 |
| 曹孟德 | 84 | 67 | 82 |
| 孙悟空 | 78 | 77 | 87 |
| 孙权 | 73 | 78 | 70 |
| 宋公明 | 65 | 30 | 75 |
| 张⻜ | 0 | NULL | 27 |
+--------+------+---------+---------+
8 rows in set (0.00 sec)

可以使⽤列的别名进⾏排序

mysql> select name, chinese + math + english as 总分 from exam order by 总分
desc;
+--------+------+
| name | 总分 |
+--------+------+
| 猪悟能 | 276 |
| 孙悟空 | 242 |
| 曹孟德 | 233 |
| 唐三藏 | 221 |
| 孙权 | 221 |
| 刘⽞德 | 185 |
| 宋公明 | 170 |
| 张⻜ | NULL |
+--------+------+
8 rows in set (0.00 sec)

注意

• 查询中没有ORDER BY ⼦句，返回的顺序是未定义的，永远不要依赖这个顺序

• ORDER BY ⼦句中可以使⽤列的别名进⾏排序

• NULL 进⾏排序时，视为⽐任何值都⼩，升序出现在最上⾯，降序出现在最下⾯

2.6分页查询

2.6.1语法

-- 起始下标为 0
-- 从 0 开始，筛选 num 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT num;
-- 从 start 开始，筛选 num 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT start, num;
-- 从 start 开始，筛选 num 条结果，⽐第⼆种⽤法更明确，建议使⽤
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT num OFFSET start;

2.6.2示例

# 查询第⼀⻚数据
mysql> select * from exam order by id asc limit 0, 3;
+----+--------+---------+------+---------+
| id | name | chinese | math | english |
+----+--------+---------+------+---------+
| 1 | 唐三藏 |      67 |   98 |      56 |
| 2 | 孙悟空 |      87 |   78 |      77 |
| 3 | 猪悟能 |      88 |   98 |      90 |
+----+--------+---------+------+---------+
3 rows in set (0.00 sec)
# 查询第⼆⻚数据
mysql> select * from exam order by id asc limit 3, 3;
+----+--------+---------+------+---------+
| id | name | chinese | math | english |
+----+--------+---------+------+---------+
| 4 | 曹孟德 |      82 |   84 |      67 |
| 5 | 刘⽞德 |      55 |   85 |      45 |
| 6 | 孙权 |        70 |   73 |      78 |
+----+--------+---------+------+---------+
3 rows in set (0.00 sec)
# 查询第三⻚数据，没有达到limit的条数限制，也不会有任何影响，有多少条就显⽰多少条
mysql> select * from exam order by id asc limit 6, 3;
+----+--------+---------+------+---------+
| id | name | chinese | math | english |
+----+--------+---------+------+---------+
| 7 | 宋公明 |     75 |    65 |      30 |
| 8 | 张⻜ |       27 |     0 |    NULL |
+----+--------+---------+------+---------+
2 rows in set (0.00 sec)