MySQL中CRUD操作及常用查询语法举例详解

2025-10-12 09:08 数据库作者：心酸伴随人生

Mysql-CURD

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

1. Create

语法：

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

案例：

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 单行数据 + 全列插入

inser into students values (1, 20250123, 'Jack' , NULL);

1.2 多行数据 + 指定列插入

inser into (sn , name , qq) students values (20250124, 'ali' , "123456789") , (20250125 , "alger" , "23456781");

1.3 插入否则更新

1.3.1 on duplicate key

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

语法：

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

案例：

insert into students values (1 , 20250126 , "Jack" , "345678912") on duplicate key update sn = 20250126;

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

1.3.2 replace

语法：

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

2. Retrieve

语法：

SELECT
    [DISTINCT] {* | {column [, column] ...}
    [FROM table_name]
    [WHERE ...]
    [ORDER BY column [ASC | DESC], ...]
    LIMIT ...

2.0 select 顺序

SQL SELECT 语句的典型执行顺序为：

FROM（指定数据来源表，是查询的基础）。
WHERE（筛选行，对 FROM 后的结果进行条件过滤）。
SELECT（指定要查询的列或表达式）。
ORDER BY（对结果集排序）。
LIMIT（限制结果集的行数，多用于分页等场景）。

MySQL中CRUD操作及常用查询语法举例详解

案例：

# 案例
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 * from exam_result;

2.2 指定列查询

select id , name , english from exam_result;

2.3 查询并计算临时表达式

select id , name , english + chinese + math from exam_result;

2.4 为2.3起别名

select id , name , english + chinese + math as total from exam_result;

select id , name , english + chinese + math total from exam_result;

2.5 查询结果去重

select distinct math from exam_result;

2.6 where 条件

2.6.1 运算符

比较运算符：

运算符	说明
>, >=, <, <=	大于，大于等于，小于，小于等于
=	等于，NULL 不安全，例如 NULL = NULL 的结果是 NULL
<=>	等于，NULL 安全，例如 NULL <=> NULL 的结果是 TRUE(1)
!=, <>	不等于，区分 NULL 安全与 NULL 不安全
BETWEEN a0 AND a1	范围匹配，[a0, 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)

2.6.2 英语不及格的同学及英语成绩

select name , english from exam_result where english < 60;

2.6.3 语文成绩在 [80, 90] 分的同学及语文成绩

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

2.6.4 数学成绩是 58 或者 59 分的同学及数学成绩

select name , math from exam_result where math = 58 or math = 59;
select name , math from exam_result where math in (58 , 59);

2.6.5 姓孙的同学及孙某同学

select name from exam_result where name like "孙%"; # 孙悟空、孙权
select name from exam_result where name like "孙_"; # 孙权

2.6.5 语文成绩好于英语成绩的同学

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

2.6.6 总分在200分以下的同学

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

注意：total 不可以在 where 子句中使用。

2.6.7 不是孙某同学

select name from exam_result where name not like "孙_";

2.6.8 name不是NULL的

select name from exam_result where name is not null;

2.7 order by

asc 升序（默认）。
desc 降序。

语法：

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

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

select name , math from exam_result order by math;

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

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

2.7.3 查询姓孙的同学或者姓曹的同学数学成绩，结果按数学成绩由高到低显示

select name , math from exam_result where name like "孙%" or name like "曹%" order by math desc;

2.8 limit

语法：

# 起始下标为 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 s;

2.8.1 按 id 进行分页，每页 3 条记录，分别显示第 1、2、3 页。

select id , name , math , chinese , english from exam_result order by id asc limit 0,3;
select id , name , math , chinese , english from exam_result order by id asc limit 3,3;
select id , name , math , chinese , english from exam_result order by id asc limit 6,3;

3. Update

语法：

UPDATE table_name SET column = expr [, column = expr ...]
    [WHERE ...] [ORDER BY ...] [LIMIT ...]

对查询到的结果进行列值更新。

3.1 将孙悟空同学的数学成绩变更为 80 分

update exam_result set math = 80 where name = "孙悟空";

3.2 将总成绩倒数前三的 3 位同学的数学成绩加上 30 分

update exam_result set math = math + 30 order by chinese + math + english asc limit 3;

3.3 将所有人的数学成绩更新为原来的2倍

update exam_result set math = math * 2;

注意：update通常需要判断条件，更新全表的语句慎用！

4. Delete

语法：

DELETE FROM table_name [WHERE ...] [ORDER BY ...] [LIMIT ...]

4.1 删除孙悟空

delete from exam_result where name = "编程孙悟空";

4.2 删除整表内容

delete from exam_result;

慎用！

5. 插入查询结果

语法：

INSERT INTO table_name [(column [, column ...])] SELECT ...

1️⃣ 创建一个表，结构复制 exam_result 表。

create table no_duplicate_table like exam_result;

2️⃣ 将 exam_result 查询的结果插入到 no_duplicate_table 表。

insert into no_duplicate_table select id , name , chinese , math , english from exam_result;

6. 聚合函数

函数	说明
COUNT([DISTINCT] expr)	返回查询到的数据的数量
SUM([DISTINCT] expr)	返回查询到的数据的总和，不是数字没有意义
AVG([DISTINCT] expr)	返回查询到的数据的平均值，不是数字没有意义
MAX([DISTINCT] expr)	返回查询到的数据的最大值，不是数字没有意义
MIN([DISTINCT] expr)	返回查询到的数据的最小值，不是数字没有意义

6.1 统计表中有多少行

select count(*) from exam_result;

NULL不计入结果。
select count(distinct col) from table-name 统计去重的结果。

6.2 统计数学总成绩

select sum(math) from exam_result;

6.3 统计不及格同学的数学总成绩

select sum(math) from exam_result where math < 60;

6.4 统计平均分

select avg(chinese + math + english) from exam_result;

6.5 返回英语最高分

select max(english) from exam_result;

6.6 返回数学最低分

select min(math) from exam_result;

注意：select name, min(math) from exam_result;

报错信息：只有按 name 分组后才可以这样使用，可通过 where order by limit方式查询。

7. group by子句

在 select 中使用 group by子句可以对指定列进行分组查询，通常与聚合函数一起使用。

准备工作：创建一个雇员信息表（oracle 9i经典测试表）

DROP database IF EXISTS `scott`;
CREATE database IF NOT EXISTS `scott` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

USE `scott`;

DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (
  `deptno` int(2) unsigned zerofill NOT NULL COMMENT ' 部门编号 ',
  `dname` varchar(14) DEFAULT NULL COMMENT ' 部门名称 ',
  `loc` varchar(13) DEFAULT NULL COMMENT ' 部门所在地点 ' 
);


DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (
  `empno` int(6) unsigned zerofill NOT NULL COMMENT '雇员编号',
  `ename` varchar(10) DEFAULT NULL COMMENT '雇员姓名',
  `job` varchar(9) DEFAULT NULL COMMENT '雇员职位',
  `mgr` int(4) unsigned zerofill DEFAULT NULL COMMENT '雇员领导编号',
  `hiredajavascriptte` datetime DEFAULT NULL COMMENT '雇佣时间',
  `sal` decimal(7,2) DEFAULT NULL COMMENT '工资月薪',
  `comm` decimal(7,2) DEFAULT NULL COMMENT '奖金',
  `deptno` int(2) unsigned zerofill DEFAULT NULL COMMENT '部门编号'
);


DROP TABLE IF EXISTS `salgrade`;
CREATE TABLE `salgrade` (
  `grade` int(11) DEFAULT NULL COMMENT '等级',
  `losal` int(11) DEFAULT NULL COMMENT '此等级最低工资',
  `hisal` int(11) DEFAULT NULL COMMENT '此等级最高工资'
);


insert into dept (deptno, dname, loc)
values (10, 'ACCOUNTING', 'NEW YORK');
insert into dept (deptno, dname, loc)
values (20, 'RESEARCH', 'DALLAS');
insert into dept (deptno, dname, loc)
values (30, 'SALES', 'CHICAGO');
insert into dept (deptno, dname, loc)
values (40, 'OPERATIONS', 'BOSTON');

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, null, 20);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7499, 'ALLEN', js'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, null, 20);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, null, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, null, 10);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, null, 20);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7839, 'KING', 'PRESIDENT', null, '1981-11-17', 5000, null, 10);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7844, 'TURNER', 'SALESMAN', 7698,'1981-09-08', 1500, 0, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, null, 20);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, null, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, null, 20);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, null, 10);

insert into salgrade (grade, losal, hisal) values (1, 700, 1200);
insert into salgrade (grade, losal, hisal) values (2, 1201, 1400);
insert into salgrade (grade, losal, hisal) values (3, 1401, 2000);
insert into salgrade (grade, losal, hisal) values (4, 2001, 3000);
insert into salgrade (grade, losal, hisal) values (5, 3001, 9999);

在 Oracle 9i 的经典测试表（SCOTT 用户下的 EMP 和 DEPT 表）中，deptno 字段的外键关系是逻辑上的、约定俗成的，而不是通过数据库物理外键约束（Foreign Key Constraint）强制实现的。

7.1 显示每个部门的平均工资和最高工资

select deptno , avg(sal) , max(sal) from emp group by deptno;

SELECT 子句中的字段，要么必须包含在 GROUP BY 子句中，要么必须被包含在聚合函数（如 AVG, MAX, SUM, COUNT 等）中。
group by理解为分组，也可以理解为分表。

7.2 显示每个部门不同岗位的平均工资和最低工资

select deptno , job, avg(sal) , min(sal) from emp group by deptno , job;

7.3 having

having 和 group by 配合使用，对 group by 结果进行过滤。

7.3.1 查询每个部门的平均工资，并只显示那些平均工资低于 2000 的部门

select avg(sal) from emp group by deptno having avg(sal) < 2000;

7.3 having 和 where 的区别

7.3.1 查询每个部门的平均工资（但不包括员工名 SMITH 的员工数据）。

select dejavascriptptno , avg(sal) from emp where ename != "SMITH" group by deptno;

简单的比喻：

WHERE 是原材料质检员，在加工（分组聚合）前就把烂苹果（不合格的行）扔掉。
HAVING 是成品质检员，在加工（分组聚合）完成后，检查做好的苹果罐头（分组结果），把不合格的整批罐头扔掉。

where 之后，也是一个表。where 本质是先过滤出你想要分组的表，之后再通过 group by 进行分组。

8. SQL查询中各个关键字的执行顺序

from > on > join > where > group by > with > having > select > distinct > order by > limit

总结

到此这篇关于MySQL中CRUD操作及常用查询语法举例详解的文章就介绍到这了,更多相关MySQL CRUD操作及查询语法内容请搜索编程客栈(www.devze.com)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程客栈(www.devze.com)！

继续阅读：crud语法 mysql查询语句详解 mysql的crud

目录

Mysql-CURD

1. Create

1.1 单行数据 + 全列插入

1.2 多行数据 + 指定列插入

1.3 插入否则更新

2. Retrieve

2.0 select 顺序

2.1 全列查询

2.2 指定列查询

2.3 查询并计算临时表达式

2.4 为2.3起别名

2.5 查询结果去重

2.6 where 条件

2.7 order by

2.8 limit

3. Update

3.1 将孙悟空同学的数学成绩变更为 80 分

3.2 将总成绩倒数前三的 3 位同学的数学成绩加上 30 分

3.3 将所有人的数学成绩更新为原来的2倍

4. Delete

4.1 删除孙悟空

4.2 删除整表内容

5. 插入查询结果

6. 聚合函数

6.1 统计表中有多少行

6.2 统计数学总成绩

6.3 统计不及格同学的数学总成绩

6.4 统计平均分

6.5 返回英语最高分

6.6 返回数学最低分

7. group by子句

7.1 显示每个部门的平均工资和最高工资

7.2 显示每个部门不同岗位的平均工资和最低工资

7.3 having

7.3 having 和 where 的区别

8. SQL查询中各个关键字的执行顺序

总结

更多精彩内容

精彩评论

最新数据库

MySQL从库过滤所有DELETE操作的几种方案

使用Python高效实现MySQL数据同步的几种方案

MySQL的隐式转换在连表查询时常见的异常问题及解决方案

从入门到实践详解SQL四大核心语言详解:DQL、DML、DDL、DCL

MySQL查询语句总结之单表查询和多表查询

数据库排行榜

Hadoop Key Management Server (KMS)配置及测试

spark报错ERROR ObjectStore: Version information found in metastore differs 2.1.0 from expected schema version 1.2.0. Schema verififcation is disabled hive.metastore.schema.verification so setting version.

Navicat连接Oracle数据库的详细步骤与注意事项

redis-cluster集群调优之cluster-require-full-coverage参数

解决Navicat远程连接MySQL出现 10060 unknow error的方法