【MySQL】表的增删查改

最新推荐文章于 2024-07-08 11:40:23 发布
最新推荐文章于 2024-07-08 11:40:23 发布
阅读量1k 收藏 30
点赞数 15
分类专栏： MYSQL 文章标签： mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/C0631xjn_/article/details/137692727
版权

文章目录

1. 插入数据insert

CRUD中的”C“，Create，即向表中插入新的数据，常用sql命令：insert

insert可以选定列插入，并x`支持同时插入多行数据（中括号内为可选项）

insert [into] 表名 [(column1, column2, column3...)] values (value1), (value2), (value3)...

value必须与指定的列匹配，不指定列，默认就是全列插入。

mysql> insert into students (sn, name, qq) value (103, '子乔', 451592222), (104, '小贤', 451593333), (105, '一菲', 123456789);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from students;
+----+-----+--------+-----------+
| id | sn  | name   | qq        |
+----+-----+--------+-----------+
|  1 | 101 | 张伟   | 451591111 |
|  2 | 102 | 张伟   | 451591111 |
|  3 | 103 | 子乔   | 451592222 |
|  4 | 104 | 小贤   | 451593333 |
|  5 | 105 | 一菲   | 123456789 |
+----+-----+--------+-----------+

insert插入数据时，若发生主键或唯一键冲突，一般情况下MySQL会报错 。在insert语句后接上on duplicate key update col1=val1, col2=val2...，当发生主键或者唯一键冲突时，冲突元组列col1, col2…的值修改为用户指定的值val1, val2…。

mysql> select * from students;
+----+-----+--------+-----------+
| id | sn  | name   | qq        |
+----+-----+--------+-----------+
|  1 | 101 | 张伟   | 451591111 |
|  2 | 106 | 关谷   | 451594567 |
|  4 | 104 | 小贤   | 451593333 |
|  5 | 105 | 一菲   | 123456789 |
|  9 | 107 | 婉瑜   | 123123123 |
+----+-----+--------+-----------+
5 rows in set (0.00 sec)

mysql> insert into students value (2, 103, '子乔', 451522222);
ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY' #这里发生了主键冲突，无法插入，报错

mysql> insert into students value (2, 103, '子乔', 451522222) on duplicate key update sn='103', name='子乔', qq='451522222';#这次虽然依然主键冲突，但是修改了冲突那一行的一些值
Query OK, 2 rows affected (0.00 sec)

mysql> select * from students;
+----+-----+--------+-----------+
| id | sn  | name   | qq        |
+----+-----+--------+-----------+
|  1 | 101 | 张伟   | 451591111 |
|  2 | 103 | 子乔   | 451522222 |
|  4 | 104 | 小贤   | 451593333 |
|  5 | 105 | 一菲   | 123456789 |
|  9 | 107 | 婉瑜   | 123123123 |
+----+-----+--------+-----------+
5 rows in set (0.00 sec)

replace，语法和insert基本一样

replace [into] 表名 [(column, column, column...)] values (value_list), (value_list), (value_list)...

  • 主键或唯一键冲突，删除后插入

  • 主键或唯一键不冲突，直接插入

2. 查询数据select

CRUD中的”R“，Retrive，即向从表中查询数据，常用sql命令：select，select搭配一些限制命令使用，如：distinct, where, order, limit等

distinct

结果去重，对于重复的数据只显示一次。

SELECT DISTINCT column1, column2, column3 FROM your_table;

注意：distinct保证的是紧随其后所有列组合在一起的唯一性

mysql> select * from exam;
+----+--------+---------+------+---------+
| id | name   | chinese | math | english |
+----+--------+---------+------+---------+
|  1 | 张三   |      19 |   36 |      24 |
|  2 | 李四   |      11 |   83 |      83 |
|  3 | 王五   |      67 |   83 |      14 |
|  4 | 赵六   |      22 |   68 |      74 |
|  5 | 小明   |      64 |   99 |      74 |
|  6 | 张飞   |      23 |   43 |      48 |
|  7 | 刘备   |      28 |   97 |       9 |
|  8 | 刘备   |      55 |   97 |      71 |
+----+--------+---------+------+---------+
8 rows in set (0.00 sec)

mysql> select distinct math from exam; #保证数学成绩math的唯一性
+------+
| math |
+------+
|   36 |
|   83 |
|   68 |
|   99 |
|   43 |
|   97 |
+------+
6 rows in set (0.00 sec)

mysql> select distinct name,math from exam; #保证姓名+数学成绩的唯一性
+--------+------+
| name   | math |
+--------+------+
| 张三   |   36 |
| 李四   |   83 |
| 王五   |   83 |
| 赵六   |   68 |
| 小明   |   99 |
| 张飞   |   43 |
| 刘备   |   97 |
+--------+------+
7 rows in set (0.00 sec)
# 34行和35行，姓名不同，数学成绩相同，整体不同，可以共存
# "刘备 97"存在两行，因此被去重了

where

比较运算符

运算符说明
>大于
>=大于等于
<小于
<=小于等于
=等于，NULL 不安全，例如 NULL = NULL 的结果是 NULL
<=>等于，NULL 安全，例如 NULL <=> NULL 的结果是 TRUE(1)
!=, <>不等于
BETWEEN a AND b范围匹配，全闭区间[a, b]，如果 a <= value <= ab，返回 TRUE(1)
IN如果是 option 中的任意一个，返回 TRUE(1)
IS NULL是 NULL
IS NOT NULL不是 NULL
LIKE模糊匹配。% 表示任意多个（包括 0 个）任意字符；_ 表示一个字符

一般情况下，NULL不安全，不参与比较，因此只要运算符左右任一侧带有NULL，返回结果为NULL。特殊情况是<=>

逻辑运算符

  • AND，与
  • OR，或
  • NOT，非

order

默认是升序

asc: 升序

desc: 降序

limit

对显式行数的限制

  • limit n: 从开始连续显示n行
  • limit s,n: 从第s行开始，连续显示n行（起始行下标为0）
  • limit n offset s：同上

使用limit对表进行分页显示

mysql> select * from exam order by id limit 3 offset 0;
+----+--------+---------+------+---------+
| id | name   | chinese | math | english |
+----+--------+---------+------+---------+
|  1 | 张三   |      35 |   36 |      24 |
|  2 | 李四   |      27 |   83 |      83 |
|  3 | 王五   |      83 |   83 |      14 |
+----+--------+---------+------+---------+
3 rows in set (0.00 sec)

mysql> select * from exam order by id limit 3 offset 3;
+----+--------+---------+------+---------+
| id | name   | chinese | math | english |
+----+--------+---------+------+---------+
|  4 | 赵六   |      38 |   68 |      74 |
|  5 | 小明   |      80 |   99 |      82 |
|  6 | 张飞   |      39 |   43 |      48 |
+----+--------+---------+------+---------+
3 rows in set (0.00 sec)

mysql> select * from exam order by id limit 3 offset 6;
+----+--------+---------+------+---------+
| id | name   | chinese | math | english |
+----+--------+---------+------+---------+
|  7 | 刘备   |      44 |   97 |       9 |
|  8 | 刘备   |      71 |   97 |      71 |
| 10 | 悟空   |      98 | NULL |    NULL |
+----+--------+---------+------+---------+
3 rows in set (0.00 sec)

mysql> select * from exam order by id limit 3 offset 9;
+----+-----------+---------+------+---------+
| id | name      | chinese | math | english |
+----+-----------+---------+------+---------+
| 11 | 张三丰    |      76 |   60 |      60 |
+----+-----------+---------+------+---------+
1 row in set (0.00 sec)

3. 更新数据update

CRUD中的”U“，Update，即更新表中的某些数据，常用sql命令：update

更新筛选出来的数据记录，不加筛选字段默认更新整表数据，要小心谨慎！

update 表名 set col1=val, col2=val... /*筛选字段:*/ [where...][order by...][limit...]

Demo：将总成绩倒数前三的同学的英语成绩都加上10分

mysql> select name,english,chinese+math+english as total from exam order by total limit 3;
+--------+---------+-------+
| name   | english | total |
+--------+---------+-------+
| 张三   |      24 |   125 |
| 刘备   |       9 |   150 |
| 张飞   |      48 |   160 |
+--------+---------+-------+
3 rows in set (0.00 sec)

mysql> update exam set english=english+10 order by chinese+math+english asc limit 3;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select name,english,chinese+math+english as total from exam order by total limit 3;
+--------+---------+-------+
| name   | english | total |
+--------+---------+-------+
| 张三   |      34 |   135 |
| 刘备   |      19 |   160 |
| 张飞   |      58 |   170 |
+--------+---------+-------+

4. 删除数据delete

CRUD中的”D“，Delete，即删除表中的某些数据，常用sql命令：delete

删除筛选出来的数据记录，不加筛选字段默认删除整表数据，要小心谨慎！

delete from 表名 /*筛选字段:*/ [where...][order by...][limit...]

清空表中所有内容：

delete from 表名
truncate [table] 表名 #截断表

区别：

  1. delete可以选择对表的部分或全部作删除，而truncate只能对整表操作
  2. truncate实际上MySQL不对数据操作，所以比delete更快，但是TRUNCATE在删除数据的时候，并不经过真正的事务，所以无法回滚。
  3. truncate会重置auto_increment项，delete不会

5. 聚合函数

将同一列中的值，作某些具体操作，统计得到一个结果，称为“聚合”。如，学生成绩表中，求出数学成绩的总和，就是对math列的全部值累加，得到最终结果。

函数说明
count(* | expr)返回查询到的数据的数量
sum(expr)返回查询到的数据的总和
avg(expr)返回查询到的数据的平均值
max(expr)返回查询到的数据的最大值
min(expr)返回查询到的数据的最小值

  1. 聚合函数可以加上distinct语句，即去重后再统计，如count(distinct [col1],[col2], ...)

    mysql> select ename, deptno from emp;
+--------+--------+
| ename  | deptno |
+--------+--------+
| SMITH  |     20 |
| ALLEN  |     30 |
| WARD   |     30 |
| JONES  |     20 |
| MARTIN |     30 |
| BLAKE  |     30 |
| CLARK  |     10 |
| SCOTT  |     20 |
| KING   |     10 |
| TURNER |     30 |
| ADAMS  |     20 |
| JAMES  |     30 |
| FORD   |     20 |
| MILLER |     10 |
+--------+--------+
14 rows in set (0.00 sec)

mysql> select count(deptno) from emp;
+---------------+
| count(deptno) |
+---------------+
|            14 |
+---------------+
1 row in set (0.00 sec)

mysql> select count(distinct deptno) from emp; #一共有多少个不同的部门
+------------------------+
| count(distinct deptno) |
+------------------------+
|                      3 |
+------------------------+
1 row in set (0.00 sec)

  2. 对于数学运算的聚合函数，无法计算的返回0（例如：字符串类型可以求最大值最小值，但是无法求总和或平均值）

    mysql> select deptno, max(ename) from emp group by deptno;
+--------+------------+
| deptno | max(ename) |
+--------+------------+
|     10 | MILLER     |
|     20 | SMITH      |
|     30 | WARD       |
+--------+------------+
3 rows in set (0.00 sec)

mysql> select deptno, sum(ename) from emp group by deptno;
+--------+------------+
| deptno | sum(ename) |
+--------+------------+
|     10 |          0 |
|     20 |          0 |
|     30 |          0 |
+--------+------------+
3 rows in set, 14 warnings (0.00 sec)

mysql> select deptno, avg(ename) from emp group by deptno;
+--------+------------+
| deptno | avg(ename) |
+--------+------------+
|     10 |          0 |
|     20 |          0 |
|     30 |          0 |
+--------+------------+
3 rows in set, 14 warnings (0.00 sec)

mysql> select deptno, min(ename) from emp group by deptno;
+--------+------------+
| deptno | min(ename) |
+--------+------------+
|     10 | CLARK      |
|     20 | ADAMS      |
|     30 | ALLEN      |
+--------+------------+
3 rows in set (0.00 sec)

  3. 默认情况下，聚合函数忽略列值为NULL的行，不参与计算。ifnull函数，用于想要使列值为NULL的行也参与聚合统计，如：计算一组学生的最低数学成绩，若数学成绩math为NULL，则视为零分参与计算，select min(ifnull(math,0)) from students。

    mysql> select * from students;
+----+-----+--------+-----------+------+
| id | sn  | name   | qq        | math |
+----+-----+--------+-----------+------+
|  1 | 101 | 张伟   | 451591111 |   50 |
|  2 | 103 | 子乔   | 451522222 |   50 |
|  4 | 104 | 小贤   | 451593333 |   50 |
|  5 | 105 | 一菲   | 123456789 |   50 |
|  9 | 107 | 婉瑜   | 123123123 | NULL |
+----+-----+--------+-----------+------+
5 rows in set (0.00 sec)

mysql> select min(math) from students;
+-----------+
| min(math) |
+-----------+
|        50 |
+-----------+
1 row in set (0.00 sec)

mysql> select min(ifnull(math,0)) from students;
+---------------------+
| min(ifnull(math,0)) |
+---------------------+
|                   0 |
+---------------------+
1 row in set (0.00 sec)

6. 分组聚合查询group by

将一个“大表”拆分成多个“小表”，这就是分组。对于每一个“小表”，都有自己的聚合结果，这就是分组聚合。

聚合函数又称组函数，实际上就是对一个组中的某些数据作聚合操作，得出一个具体结果。上面介绍是一种特殊情况，即将整个表看作一个组，下面是将表分为多个组，对每个组进行聚合。

group by col：根据col列进行分组。

  • 显示每个部门的最低工资和平均工资（按deptno分组，group by deptno，deptno相同的成一组）

    mysql> select deptno, min(sal), avg(sal) from emp group by deptno;
+--------+----------+-------------+
| deptno | min(sal) | avg(sal)    |
+--------+----------+-------------+
|     10 |  1300.00 | 2916.666667 |
|     20 |   800.00 | 2175.000000 |
|     30 |   950.00 | 1566.666667 |
+--------+----------+-------------+
3 rows in set (0.00 sec)

用于分组的列dept可以不显示，但是为了便于观察不同分组的聚合情况，最好还是加上。

分组聚合后的表，其中的一行，是某个组（“小表”）聚合组内多个数据并且经过某种运算得出的结果，每一组只有用于分组（可理解为标识该组唯一性）的列（即group by用的的列）是一定相同的，可以作为一个独立的值在一行显示出来，而其它列不一定相同，无法在同一行显示出来。

  • 显示每个部门的每种岗位的平均工资和最高工资（分组再分组，每一组的标识是部门+岗位）

    mysql> select deptno, job, avg(sal) as 平均工资, max(sal) as 最高工资 from emp group by deptno, job;
+--------+-----------+--------------+--------------+
| deptno | job       | 平均工资      | 最高工资       |
+--------+-----------+--------------+--------------+
|     10 | CLERK     |  1300.000000 |      1300.00 |
|     10 | MANAGER   |  2450.000000 |      2450.00 |
|     10 | PRESIDENT |  5000.000000 |      5000.00 |
|     20 | ANALYST   |  3000.000000 |      3000.00 |
|     20 | CLERK     |   950.000000 |      1100.00 | 
|     20 | MANAGER   |  2975.000000 |      2975.00 |
|     30 | CLERK     |   950.000000 |       950.00 |
|     30 | MANAGER   |  2850.000000 |      2850.00 |
|     30 | SALESMAN  |  1400.000000 |      1600.00 |
+--------+-----------+--------------+--------------+
9 rows in set (0.00 sec)

having

having的作用是分组条件过滤。having和group by配合使用，对分组聚合的结果进行条件过滤

  • 显示平均工资低于2000的部门和它的平均工资
mysql> select deptno, avg(sal) as 平均工资 from emp group by deptno having 平均工资 < 2000;
+--------+--------------+
| deptno | 平均工资      |
+--------+--------------+
|     30 |  1566.666667 |
+--------+--------------+
1 row in set (0.00 sec)

having和where的作用类似，但有些区别：

  1. where 在数据被分组前进行筛选，作用于行级别的数据。
  2. having 在数据被分组后进行筛选，作用于分组级别的数据。
超人不会飞）
关注
  • 15
    点赞
  • 踩
  • 30
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
Python操作mysql数据库实现增删查改功能的方法
09-09
本篇文章将详细讲解如何使用Python实现对MySQL数据库的增删查改（CRUD）操作。 首先，我们需要导入Python中的MySQL数据库连接库`MySQLdb`。`MySQLdb`是Python的一个MySQL接口，它允许我们通过Python代码连接到MySQL...
mysql之增删查改
04-24
在本文中，我们将深入探讨“MySQL之增删查改”这一主题，这是任何数据库管理员或开发者必须掌握的基础操作。 首先，我们来看“增”（Insert）操作。在MySQL中，增加数据到表中通常通过INSERT语句实现。例如，假设有...
labview对MySQL数据库的增删查改
09-29
通过labview对MySQL数据库进行基本操作，使用时需改成自己的数据源和数据表
php+mysql实现增删查改
05-02
bootstrap实现后端页面，php+mysql实现新闻增删查改的后端开发bootstrap实现后端页面，php+mysql实现新闻增删查改的后端开发bootstrap实现后端页面，php+mysql实现新闻增删查改的后端开发
.net连接MySql增删查改
01-31
在.NET开发中，连接MySQL数据库是一项基础且重要的任务。本文将深入探讨如何使用C#语言在.NET环境中实现对MySQL数据库的连接，以及进行增、删、查、改...希望这些信息能帮助你理解和实践".NET连接MySql增删查改"。
MySQL 结构的优化方案
boluomi的博客
07-05 793
主要是指三方面，即，这些结构如果不合理，在某些场景下也会影响数据库的性能，因此优化时也可以从结构层面出发。一般在项目的库表设计之初就要考虑，当性能瓶颈出现时再调整结构，就为时过晚。
零基础学习MySQL---库的相关操作
顾得泉的博客
07-04 1203
本文主要对于MySQL库的相关操作进行了详解。
MySQL之备份与恢复(十)
Cover_sky的博客
07-07 1031
有各种各样的好的和不是那么好的备份工具。我们喜欢对LVM使用mylvmbackup做快照备份，使用Percona Xtrabackup(开源)或MySQL Enterprise Backup(收费)做InnoDB热备份。不建议对大数据量使用mysqldump,因为它对服务器有影响，并且漫长的还原时间不可预知。有一些备份工具已经出现多年了，不幸的是有些已经过时。最明显的例子是Maatkkit的mk-parallel-dump。它从没有正确运行，甚至被重新设计过好几次还是不行。
Mysql——子查询
weixin_59369438的博客
07-05 392
子查询Where子查询 单行单列 多行单列Form子查询 多行多列查询比SMITH工资高的人select * from emp where sal > (select sal from emp where ename = 'SMITH');查询比平均工资高的人select * from emp where sal > (select avg(sal) from emp);查询和经理工资相同的员工信息select * from emp where sal in (select sal from emp
mysql 中 单独获取已知日期的年月日其中之一
m0_74969835的博客
07-02 269
1.然后可以用 substring（date,起点下标，截取个数）也可以获取 其中部分数据。2.然后匹配月份可以(比如想要八月份的内容的数据) ：date like '2021-08%'
MySQL 函数简介
zhanchulan的博客
07-04 484
四舍五入到指定的小数位数。：返回一个随机浮点数。以 10 为底的对数。
Docker容器 为MySQL创建新用户和授权
Hellc007的博客
07-04 479
当您需要为 MySQL 数据库创建一个新用户并配置其访问权限时，可以按照以下步骤操作。我将创建一个名为newuser的新用户，并为其授予在任何主机上访问所有数据库的权限。
MySQL之备份与恢复(五)
Cover_sky的博客
07-05 662
可以使用SQL命令SELECT INTO OUTFILE以符号分隔文件格式创建数据的逻辑备份。(可以用mysqldump的 --tab选项导出到符号分隔文件中)。符号分隔文件包含以ASCII展示的原始数据，没有SQL、注释和列名。下面是一个导出为逗号分隔值(CVS)格式的例子。对于表个形式的数据来说这是一个很好的通用格式。导出时你可能会遇到报错，按照图中所示导入到mysql认为安全的目录下就行。比起SQL导出文件，符号分隔文件要更紧凑且更易于用命令行工具操作，这种方法最大的优点时备份和还原速度更快。
MySQL 主从复制中 MHA 工具的研究与实践
m0_63144319的博客
07-05 964
MHA 是一套用于管理 MySQL 主从复制的开源工具，主要用于在主服务器故障时，自动进行故障转移和主从切换，以确保数据库服务的持续可用。
MySQL篇-SQL优化实战
试着奔跑的菜鸟的博客
07-03 379
常见的SQL优化措施有哪些？拿到一段SQL，怎么着手优化呢？
MySQL——statement对象详解
chenyang_88的博客
07-05 288
JDBC中的statement对象用于向数据库发送SQL语句，想完成对数据库的增删改查，只需要通过这个对象向数据库发送增删改查语句即可。Statement对象的executeUpdate方法，用于向数据库发送增、删、改的sql语句，executeUpdate执行完成后，将会返回一个整数（即增删改语句导致了数据库几行数据发生了变化）。Statement.executeQuery方法用于向数据库发送查询语句，executeQuery方法返回代表查询结果的ResultSet对象。CRUD操作-create。
mysql 表优化
最新发布
马春林的专栏
07-08 113
mysql 表优化
mysql逗号分割字符串“1,2,3”实现in查询
weixin_42324471的博客
07-03 285
数据示例。
mysql 命令 —— 查看表信息（show table status）
longool的博客
07-02 454
这些列提供了有关表的各种信息，包括表的结构、存储引擎、行数、数据大小、索引大小等。可以根据这些信息来评估表的磁盘使用情况和性能。如：Data_length 显示表中数据内容大小，单位为字节。如下将返回指定表的表名、数据大小、索引大小和总大小。
mysql进行增删查改
07-22
JSP（Java Server Pages）是一种基于Java语言的Web开发技术，可以用来生成动态的Web页面。Servlet是一种Java程序，可以在Web服务器上运行，用于处理客户端请求和响应。MySQL是一种流行的关系型数据库管理系统，可以用于存储和管理数据。 增删查改是数据库操作中的四种基本操作，分别是插入数据、删除数据、查询数据和更新数据。在JSP和Servlet中，可以通过使用Java JDBC API来连接和操作MySQL数据库，实现增删查改功能。具体实现方式可以参考相关的教程和文档。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值