• 设为首页
  • 收藏本站
  • 积分充值
  • VIP赞助
  • 手机版
  • 微博
  • 微信
    微信公众号 添加方式：
    1:搜索微信号（888888）
    2:扫描左侧二维码
  • 登录 立即注册
    • 切换风格
    快捷导航
    福建二哥 › 门户 › 查看主题

    MySQL数据库表内容的增删查改操作实例详解

    发布者: 土豆服务器 | 发布时间: 2025-6-14 14:28| 查看数: 161| 评论数: 0|帖子模式

    一、增加表数据
    1. 语法：INSERT [INTO] table_name [column , column...] VALUES (value_list) [ ,(value_list)];
    复制代码
    1. mysql> create table students (
    2.     -> id int unsigned primary key auto_increment,
    3.     -> number int not null unique comment '学号',
    4.     -> name varchar(20) not null,
    5.     -> telephone char(11)
    6.     -> );
    7. Query OK, 0 rows affected (0.05 sec)
    复制代码
    1.全列插入与指定列插入

    value_list的数量必须和表中定义的字段数量一样的时候，才属于全列插入，才可以省略指定插入元素的部分。因为我们设置了自增属性，所以可以不用给id设定值，但是这样的话，就不属于全列插入了就必须指定插入的字段是哪些了。
    1. //全列插入
    2. insert into students values(1, 202501, '张三', '15812345678');
    3. //指定列插入
    4. insert into students (number, name, telephone) values(202503, '王五', '17712345678');
    复制代码
    2.多行数据插入

    在插入数据的时候也可以同时插入多条数据，对于多行数据的插入也满足全列插入与指定列插入的规则。
    1. //全列多行插入
    2. insert into students values(4, 202504, '赵六', '12312345678'), (5, 202505, '田七', '12345656789');
    3. //指定列多行插入
    4. insert into students (number, name, telephone) values(202506, '你好', '12312345678'), (202507, '哈哈', '12345656789');
    复制代码
    3.更新与替换插入
    1. 更新语法：ON DUPLICATE KEY UPDATE
    2. 替换语法：REPLACE
    复制代码
    因为一般表中都会有主键和唯一键的约束，那么我们在插入的时候如果出现唯一键和主键冲突的情况就会插入失败，那么如果我们就想插入呢，那么可以使用更新或者替换语句，将数据更新成我们新插入的，或者整个替换一下。
    1. mysql> insert into students (id, number, name) values(1, 202501, '北顾') on duplicate key update number=202510, name = '北顾';
    2. Query OK, 2 rows affected (0.00 sec)
    3. mysql> select * from students;
    4. +----+--------+--------+-------------+
    5. | id | number | name   | telephone   |
    6. +----+--------+--------+-------------+
    7. |  1 | 202510 | 北顾   | 15812345678 |
    8. |  2 | 202502 | 李四   | 17712345678 |
    9. |  3 | 202503 | 王五   | 17712345678 |
    10. |  4 | 202504 | 赵六   | 12312345678 |
    11. |  5 | 202505 | 田七   | 12345656789 |
    12. |  6 | 202506 | 你好   | 12312345678 |
    13. |  7 | 202507 | 哈哈   | 12345656789 |
    14. +----+--------+--------+-------------+
    15. 7 rows in set (0.00 sec)
    复制代码
    上面的代码为插入否则更新的操作，先使用insert插入数据，如果说遇到主键或者唯一键的冲突而导致的插入失败的时候，可以执行duplicate key update进行数据的更新操作。
    如图可以看到返回值发生了变化，如果说插入的数据有冲突的但是冲突的数据和原数据是一样的那么就相当于不做任何操作，返回0；如果说没有数据冲突的话，直接插入返回1；如果有数据冲突，并执行了数据更新操作的话，就返回2。
    1. mysql> replace into students (number, name) value(202510, 'hello');
    2. Query OK, 2 rows affected (0.01 sec)
    3. mysql> select * from students;
    4. +----+---------+--------+-------------+
    5. | id | number  | name   | telephone   |
    6. +----+---------+--------+-------------+
    7. |  2 |  202502 | 李四   | 17712345678 |
    8. |  3 |  202503 | 王五   | 17712345678 |
    9. |  4 |  202504 | 赵六   | 12312345678 |
    10. |  5 |  202505 | 田七   | 12345656789 |
    11. |  6 |  202506 | 你好   | 12312345678 |
    12. |  7 |  202507 | 哈哈   | 12345656789 |
    13. |  8 |  202510 | hello  | NULL        |
    14. +----+---------+--------+-------------+
    15. 9 rows in set (0.00 sec)
    复制代码
    上面的代码则是替换代码，他的操作是如果没有冲突那么就直接插入并返回1，如果有冲突大的话，他会先删除冲突数据，然后再重新插入并返回2。

    二、查看表数据


    1.全列查询与指定列查询
    1. SELECT [column， column] FROM table_name;
    复制代码
    当column不指定且设置为*的时候，就是全列查询了，但是一般不建议使用全列查询，因为查询的列越多，意味着需要传输的数据量就越大，会影响效率。对于指定列查询输入的字段名称不需要和定义的时候顺序一样，select关键字的作用相当于是打印，而我们定义打印什么他就会显示什么，定义什么顺序显示，他就会按什么顺序显示。

    2.查询表达式字段

    上述也说了select是一个起到一个打印显示的作用，而column from table_name，才是指定打印的内容，那么打印的内容可不可以不是表中的呢？或者打印的字段可以不可以是表达式呢？打印的字段可不可以用表中的字段作为参数的表达式呢？
    1. mysql> create table exam (
    2.     -> id int unsigned primary key auto_increment,
    3.     -> name varchar(20) not null,
    4.     -> chinese float default 0.0,
    5.     -> math float default 0.0,
    6.     -> english float default 0.0
    7.     -> );
    8. Query OK, 0 rows affected (0.05 sec)
    9. mysql> insert into exam (name, chinese, math, english) values
    10.     -> ('张三', 67, 65, 86),
    11.     -> ('李四', 98, 56, 84),
    12.     -> ('王五', 76, 45, 97),
    13.     -> ('赵六', 99, 43, 91);
    14. Query OK, 4 rows affected (0.00 sec)
    15. Records: 4  Duplicates: 0  Warnings: 0
    16. //显示非表中的数据
    17. mysql> select 10;
    18. +----+
    19. | 10 |
    20. +----+
    21. | 10 |
    22. +----+
    23. 1 row in set (0.00 sec)
    24. //显示表达式数据
    25. mysql> select 10 + 20;
    26. +---------+
    27. | 10 + 20 |
    28. +---------+
    29. |      30 |
    30. +---------+
    31. 1 row in set (0.00 sec)
    32. //显示表中数据为参数的表达式数据
    33. mysql> select id + 10 from exam;
    34. +---------+
    35. | id + 10 |
    36. +---------+
    37. |      11 |
    38. |      12 |
    39. |      13 |
    40. |      14 |
    41. +---------+
    42. 4 rows in set (0.00 sec)
    复制代码
    3.为查询结果起别名
    1. 语法：SELECT column [AS] alias_name [...] FROM table_name;
    复制代码
    1. mysql> select 10 + 20 as '总数';
    2. +--------+
    3. | 总数   |
    4. +--------+
    5. |     30 |
    6. +--------+
    7. 1 row in set (0.00 sec)
    8. mysql> select id, name, chinese + math + english as '总分' from exam;
    9. +----+--------+--------+
    10. | id | name   | 总分   |
    11. +----+--------+--------+
    12. |  1 | 张三   |    218 |
    13. |  2 | 李四   |    238 |
    14. |  3 | 王五   |    218 |
    15. |  4 | 赵六   |    233 |
    16. +----+--------+--------+
    17. 4 rows in set (0.00 sec)
    复制代码
    4.结果去重
    1. 语法: SELECT DISTINCT column FROM table_name;
    复制代码
    5.WHERE条件

    比较运算符运算符说明>, >=, <, <=没有什么特殊含义，就是单纯的比较=等于，<=>等于!=, <>不等于BETWEEN x1 AND x2
    进行范围匹配，如果一个数值再[x1, x2]之间，那么就返回trueIN (option, ...)如果是option中的一个，那么就返回tureIS NULL是NULLIS NOT NULL不是NULLLIKE模糊匹配。 %表示任意多个任意字符；_表示任意一个字符逻辑运算符运算符说明AND多个条件都必须满足才返回trueOR任意满足一个条件返回trueNOT满足条件的时候，返回false，相当于匹配不是该条件的内容BETWEEN x1 AND x2
    下面是操作符的一些使用案例：
    基本比较的使用
    1. mysql> select * from exam;
    2. +----+--------+---------+------+---------+
    3. | id | name   | chinese | math | english |
    4. +----+--------+---------+------+---------+
    5. |  1 | 张三   |      67 |   65 |      86 |
    6. |  2 | 李四   |      98 |   56 |      84 |
    7. |  3 | 王五   |      76 |   45 |      97 |
    8. |  4 | 赵六   |      99 |   43 |      91 |
    9. +----+--------+---------+------+---------+
    10. 4 rows in set (0.00 sec)
    11. mysql> select id, name, math from exam where math < 60;
    12. +----+--------+------+
    13. | id | name   | math |
    14. +----+--------+------+
    15. |  2 | 李四   |   56 |
    16. |  3 | 王五   |   45 |
    17. |  4 | 赵六   |   43 |
    18. +----+--------+------+
    19. 3 rows in set (0.00 sec)
    复制代码
    AND与BETWENN AND的使用
    1. //查询语文分数再80到100之间的同学
    2. mysql> select id, name, chinese from exam where chinese >= 80 and chinese <= 100;
    3. +----+--------+---------+
    4. | id | name   | chinese |
    5. +----+--------+---------+
    6. |  2 | 李四   |      98 |
    7. |  4 | 赵六   |      99 |
    8. +----+--------+---------+
    9. 2 rows in set (0.00 sec)
    10. mysql> select id, name, chinese from exam where chinese between 80 and 100;
    11. +----+--------+---------+
    12. | id | name   | chinese |
    13. +----+--------+---------+
    14. |  2 | 李四   |      98 |
    15. |  4 | 赵六   |      99 |
    16. +----+--------+---------+
    17. 2 rows in set (0.00 sec)
    复制代码
    OR与IN的使用
    1. //查询英语分数为86或97的同学
    2. mysql> select id, name, english from exam where english=86 or english=97;
    3. +----+--------+---------+
    4. | id | name   | english |
    5. +----+--------+---------+
    6. |  1 | 张三   |      86 |
    7. |  3 | 王五   |      97 |
    8. +----+--------+---------+
    9. 2 rows in set (0.00 sec)
    10. mysql> select id, name, english from exam where english in(86, 97);
    11. +----+--------+---------+
    12. | id | name   | english |
    13. +----+--------+---------+
    14. |  1 | 张三   |      86 |
    15. |  3 | 王五   |      97 |
    16. +----+--------+---------+
    17. 2 rows in set (0.00 sec)
    复制代码
    LIKE的使用
    1. //查询姓张和姓王的同学
    2. mysql> select id, name from exam where name like '张%' or name like '王%';
    3. +----+--------+
    4. | id | name   |
    5. +----+--------+
    6. |  1 | 张三   |
    7. |  3 | 王五   |
    8. +----+--------+
    9. 2 rows in set (0.00 sec)
    10. mysql> insert into exam values(5, '张文强', 98, 90, 79);
    11. Query OK, 1 row affected (0.01 sec)
    12. //查询名字为2个字，还性张的同学
    13. mysql> select id, name from exam where name like '张_';
    14. +----+--------+
    15. | id | name   |
    16. +----+--------+
    17. |  1 | 张三   |
    18. +----+--------+
    19. 1 row in set (0.00 sec)
    复制代码
    where与表达式混合使用
    1. //总分大于230的同学
    2. mysql> select id, name, chinese + math + english as '总分' from exam where chinese + math + english > 230;
    3. +----+-----------+--------+
    4. | id | name      | 总分   |
    5. +----+-----------+--------+
    6. |  2 | 李四      |    238 |
    7. |  4 | 赵六      |    233 |
    8. |  5 | 田七      |    231 |
    9. |  6 | 张文强    |    267 |
    10. +----+-----------+--------+
    11. 4 rows in set (0.00 sec)
    复制代码
    AND与NOT混合使用
    1. //语文分数大于90，不是不姓李的同学
    2. mysql> select name, chinese from exam where chinese > 90 and name not like '李_';
    3. +-----------+---------+
    4. | name      | chinese |
    5. +-----------+---------+
    6. | 赵六      |      99 |
    7. | 田七      |      92 |
    8. | 张文强    |      98 |
    9. +-----------+---------+
    10. 3 rows in set (0.00 sec)
    复制代码
    =和<=>的区别
    两者都是判断两个值是否相等的，但是第一个属于非安全的，如果用NULL去和任意值就行比较的话，都会返回NULL，因为MySQL中对于NULL代表的是未知的值，所以说比较的时候结果也是未知的。而<=>的话能处理NULL值的比较，会把NULL当作一个值来看待，如果都是NULL就返回1，不是返回0。

    6.结果排序
    1. 语法：SELECT ... FROM table_name ... ORDER BY column [ASC|DESC], [...];
    2. 使用案例：select * from tset_table order by xxxx;
    复制代码
    操作是将select选出的显示数据，按照column数据的值进行升序或者降序排列显示，ASC是默认值，表示升序，DESC表示降序。对于NULL的话，看作比任何数据都小的一个值。还可以进行多段排序规则的定义，如果两个数值相等，那么第一个排序规则就无法排序，就会继续按照第二个排序规则继续排序了。
    1. mysql> insert into exam values(7, '哈哈', 98, 77, 79);
    2. Query OK, 1 row affected (0.01 sec)
    3. mysql> insert into exam values(8, '王强', NULL, 79, 59);
    4. Query OK, 1 row affected (0.01 sec)
    5. //语文按照降序，数学按照升序排列
    6. mysql> select * from exam order by chinese desc, math;
    7. +----+-----------+---------+------+---------+
    8. | id | name      | chinese | math | english |
    9. +----+-----------+---------+------+---------+
    10. |  4 | 赵六      |      99 |   43 |      91 |
    11. |  2 | 李四      |      98 |   56 |      84 |
    12. |  7 | 哈哈      |      98 |   77 |      79 |
    13. |  6 | 张文强    |      98 |   90 |      79 |
    14. |  5 | 田七      |      92 |   60 |      79 |
    15. |  3 | 王五      |      76 |   45 |      97 |
    16. |  1 | 张三      |      67 |   65 |      86 |
    17. |  8 | 王强      |    NULL |   79 |      59 |
    18. +----+-----------+---------+------+---------+
    19. 8 rows in set (0.00 sec)
    20. //排序总分, order by中可以使用列名称的别名
    21. mysql> select id, name, chinese + math + english as 总分 from exam order by 总分 desc;
    22. +----+-----------+--------+
    23. | id | name      | 总分   |
    24. +----+-----------+--------+
    25. |  6 | 张文强    |    267 |
    26. |  7 | 哈哈      |    254 |
    27. |  2 | 李四      |    238 |
    28. |  4 | 赵六      |    233 |
    29. |  5 | 田七      |    231 |
    30. |  1 | 张三      |    218 |
    31. |  3 | 王五      |    218 |
    32. |  8 | 王强      |   NULL |
    33. +----+-----------+--------+
    34. 8 rows in set (0.00 sec)
    复制代码
    7.筛选分页结果
    1. //从0开始筛选n条结果
    2. SELECT ... FROM table_name [...] LIMIT n;
    3. //从s开始筛选n条结果
    4. SELECT ... FROM table_name [...] LIMIT s, n;
    5. SELECT ... FROM table_name [...] LIMIT n OFFSET S;
    复制代码
    在对未知的表就行查询显示的时候，最好加上LIMIT，避免表中大的数据量过大，查询全表导致数据库卡顿。如果查询的数据不够n个的话不会有任何的影响。
    1. mysql> select * from exam limit 3;
    2. +----+--------+---------+------+---------+
    3. | id | name   | chinese | math | english |
    4. +----+--------+---------+------+---------+
    5. |  1 | 张三   |      67 |   65 |      86 |
    6. |  2 | 李四   |      98 |   56 |      84 |
    7. |  3 | 王五   |      76 |   45 |      97 |
    8. +----+--------+---------+------+---------+
    9. 3 rows in set (0.00 sec)
    10. mysql> select * from exam limit 3, 3;
    11. +----+-----------+---------+------+---------+
    12. | id | name      | chinese | math | english |
    13. +----+-----------+---------+------+---------+
    14. |  4 | 赵六      |      99 |   43 |      91 |
    15. |  5 | 田七      |      92 |   60 |      79 |
    16. |  6 | 张文强    |      98 |   90 |      79 |
    17. +----+-----------+---------+------+---------+
    18. 3 rows in set (0.00 sec)
    19. mysql> select * from exam limit 6, 3;
    20. +----+--------+---------+------+---------+
    21. | id | name   | chinese | math | english |
    22. +----+--------+---------+------+---------+
    23. |  7 | 哈哈   |      98 |   77 |      79 |
    24. |  8 | 王强   |    NULL |   79 |      59 |
    25. +----+--------+---------+------+---------+
    26. 2 rows in set (0.00 sec)
    27. mysql>
    复制代码
    8.插入查询的结果
    1. mysql> create table exam_zhang (
    2.     -> id int unsigned primary key auto_increment,
    3.     -> name varchar(20) not null,
    4.     -> chinese float default 0.0,
    5.     -> math float default 0.0,
    6.     -> english float default 0.0
    7.     -> );
    8. Query OK, 0 rows affected (0.05 sec)
    9. mysql> insert into exam_zhang select distinct * from exam where name like '张%';
    10. Query OK, 2 rows affected (0.00 sec)
    11. Records: 2  Duplicates: 0  Warnings: 0
    12. mysql> select * from exam_zhang;
    13. +----+-----------+---------+------+---------+
    14. | id | name      | chinese | math | english |
    15. +----+-----------+---------+------+---------+
    16. |  1 | 张三      |     100 |  130 |     116 |
    17. |  6 | 张文强    |      98 |  120 |     109 |
    18. +----+-----------+---------+------+---------+
    19. 2 rows in set (0.01 sec)
    复制代码
    9.group by子句

    使用该子句可以将指定的列进行分组查询，对于使用分组查询的时候，select显示的列必须是出现在group by子句中，或者说是聚合函数才可以。
    1. mysql> create table student (
    2.     -> class_id int not null,
    3.     -> name varchar(10) not null,
    4.     -> score float default 0.0
    5.     -> );
    6. Query OK, 0 rows affected (0.03 sec)
    7. mysql> insert into student values  
    8.     -> (1, '张三', 98.7),
    9.     -> (1, '李四', 97.2),
    10.     -> (1, '王五', 88.6),
    11.     -> (2, '赵六', 79.4),
    12.     -> (2, '田七', 99.9),
    13.     -> (2, '王强', 50.4);
    14. Query OK, 6 rows affected (0.01 sec)
    15. Records: 6  Duplicates: 0  Warnings: 0
    16. //查看每个班级的最高分
    17. mysql> select class_id, max(score) from student group by class_id;
    18. +----------+------------+
    19. | class_id | max(score) |
    20. +----------+------------+
    21. |        1 |       98.7 |
    22. |        2 |       99.9 |
    23. +----------+------------+
    24. 2 rows in set (0.00 sec)
    25. //必须是聚合函数，或者是group by里出现的列
    26. mysql> select class_id, name, max(score) from student group by class_id;
    27. ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.student.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
    复制代码
    三、修改表数据
    1. 语法：UPDATE table_name SET column=xx [, column=xx, ...] [WHERE ...];
    复制代码
    1. //将总分最高的前三名英语成绩提供30分
    2. mysql> select name, english, math+chinese+english as 总分 from exam order by 总分 desc limit 3;
    3. +-----------+---------+--------+
    4. | name      | english | 总分   |
    5. +-----------+---------+--------+
    6. | 张三      |      86 |    286 |
    7. | 张文强    |      79 |    267 |
    8. | 哈哈      |      79 |    254 |
    9. +-----------+---------+--------+
    10. 3 rows in set (0.00 sec)
    11. mysql> update exam set english = english + 30 order by math+english+chinese desc limit 3;
    12. Query OK, 3 rows affected (0.01 sec)
    13. Rows matched: 3  Changed: 3  Warnings: 0
    14. mysql> select name, english, math+chinese+english as 总分 from exam order by 总分 desc limit 3;
    15. +-----------+---------+--------+
    16. | name      | english | 总分   |
    17. +-----------+---------+--------+
    18. | 张三      |     116 |    316 |
    19. | 张文强    |     109 |    297 |
    20. | 哈哈      |     109 |    284 |
    21. +-----------+---------+-------
    22. //全列更改--非常不建议这样做
    23. mysql> update exam set math = math + 30;
    24. Query OK, 8 rows affected (0.00 sec)
    25. Rows matched: 8  Changed: 8  Warnings: 0
    复制代码
    四、删除与截断表数据
    1. 删除语法：DELETE FROM table_name [....];
    复制代码
    如果说不加任何范围选择条件的话，那么就相当于是删除整个表数据的操作了。
    1. 截断语法：TRUNCATE [TABLE] table_name;
    复制代码
    对于删除操作来说，是将表单个或者多个数据进行删除，而截断则是对整个表进行操作，会将整个表数据都清除。这样的话可以通过释放表的存储空间来实现清空表的操作，而DELETE语句需要逐行删除记录，并且会记录每一行的删除操作到日志中。所以会比DELETE快很多。
    如果表中有自增列，操作会将自增列的值重置为初始值（通常是 1）。而DELETE语句不会重置自增列的值。还有不可滚回和不处罚触发器的特点，以后在讲述。
    到此这篇关于MySQL数据库表内容的增删查改操作大全的文章就介绍到这了,更多相关mysql数据库表增删查改内容请搜索脚本之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持脚本之家！

    来源：https://www.jb51.net/database/339217k53.htm
    免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！

    最新评论

    新人须知

    常见问题

    维权申诉

    关注微博

    扫描微信

    关于合作

    通讯联系

    QQ Archiver 手机版 小黑屋 福建二哥 ( 闽ICP备2022004717号|闽公网安备35052402000345号 )

    Powered by Discuz! X3.5 © 2001-2023

    快速回复 返回顶部 返回列表