MySQL（2）：表的增删查改

最新推荐文章于 2024-06-20 17:27:13 发布

一杆梅子酒丶

最新推荐文章于 2024-06-20 17:27:13 发布

阅读量274

点赞数

分类专栏： MySQL数据库

本文链接：https://blog.csdn.net/weixin_43690795/article/details/109385892

版权

MySQL数据库专栏收录该内容

4 篇文章 0 订阅

订阅专栏

文章目录

增加（insert）
- - 全列插入
  - 指定列插入
查询（select）
修改数据（update）
删除数据（delete）

增加（insert）

全列插入

语法

insert into [表名] values(参数1, 参数2, 参数3 ......);

示例

insert into student values(3, "孙悟空", 22, "1998-2-28 15:08:11", 98, 96.5, 92.03);

MariaDB [study]> select * from student;
+------+-----------+------+---------------------+---------+-------+---------+
| id   | name      | age  | birth               | chinese | math  | english |
+------+-----------+------+---------------------+---------+-------+---------+
|    3 | 孙悟空    |   22 | 1998-02-28 15:08:11 |   98.00 | 96.50 |   92.03 |
+------+-----------+------+---------------------+---------+-------+---------+
1 row in set (0.00 sec)

指定列插入

语法

insert into [表名](列1, 列2, 列3 ......) values(参数1, 参数2, 参数3 ......);

示例

insert into student(id, name, age, chinese, math, english) values(1, "猪八戒", 20, 60, 62.5, 58);

MariaDB [study]> select * from student;
+------+-----------+------+---------------------+---------+-------+---------+
| id   | name      | age  | birth               | chinese | math  | english |
+------+-----------+------+---------------------+---------+-------+---------+
|    3 | 孙悟空    |   22 | 1998-02-28 15:08:11 |   98.00 | 96.50 |   92.03 |
|    1 | 猪八戒    |   20 | NULL                |   60.00 | 62.50 |   58.00 |
+------+-----------+------+---------------------+---------+-------+---------+
2 rows in set (0.00 sec)

查询（select）

全列查询

语法

select * from [表名];

示例

select * from student;

+------+-----------+------+---------------------+---------+-------+---------+
| id   | name      | age  | birth               | chinese | math  | english |
+------+-----------+------+---------------------+---------+-------+---------+
|    3 | 孙悟空    |   22 | 1998-02-28 15:08:11 |   98.00 | 96.50 |   92.03 |
|    1 | 猪八戒    |   20 | NULL                |   60.00 | 62.50 |   58.00 |
|    1 | 唐僧      |   16 | 2000-10-01 07:21:03 |   80.06 | 82.50 |   86.00 |
|    5 | 沙悟净    |   21 | 1999-05-08 21:10:18 |   72.00 | 75.08 |   70.03 |
|    6 | 白龙马    |   19 | 2000-03-26 14:10:58 |   70.00 | 76.08 |   71.04 |
+------+-----------+------+---------------------+---------+-------+---------+
5 rows in set (0.00 sec)

指定列查询

语法

select 列1, 列2, 列3 ...... from [表名];

示例

select id, name, age from student;

+------+-----------+------+
| id   | name      | age  |
+------+-----------+------+
|    3 | 孙悟空    |   22 |
|    1 | 猪八戒    |   20 |
|    1 | 唐僧      |   16 |
|    5 | 沙悟净    |   21 |
|    6 | 白龙马    |   19 |
+------+-----------+------+
5 rows in set (0.00 sec)

排列查询

语法

select * from [表名] order by [依据的列] [asc/desc];

示例
默认升序排列（asc）

select * from student order by id;

+------+-----------+------+---------------------+---------+-------+---------+
| id   | name      | age  | birth               | chinese | math  | english |
+------+-----------+------+---------------------+---------+-------+---------+
|    1 | 猪八戒    |   20 | NULL                |   60.00 | 62.50 |   58.00 |
|    1 | 唐僧      |   16 | 2000-10-01 07:21:03 |   80.06 | 82.50 |   86.00 |
|    3 | 孙悟空    |   22 | 1998-02-28 15:08:11 |   98.00 | 96.50 |   92.03 |
|    5 | 沙悟净    |   21 | 1999-05-08 21:10:18 |   72.00 | 75.08 |   70.03 |
|    6 | 白龙马    |   19 | 2000-03-26 14:10:58 |   70.00 | 76.08 |   71.04 |
+------+-----------+------+---------------------+---------+-------+---------+
5 rows in set (0.00 sec)

降序排列（desc）

select * from student order by id desc;

+------+-----------+------+---------------------+---------+-------+---------+
| id   | name      | age  | birth               | chinese | math  | english |
+------+-----------+------+---------------------+---------+-------+---------+
|    6 | 白龙马    |   19 | 2000-03-26 14:10:58 |   70.00 | 76.08 |   71.04 |
|    5 | 沙悟净    |   21 | 1999-05-08 21:10:18 |   72.00 | 75.08 |   70.03 |
|    3 | 孙悟空    |   22 | 1998-02-28 15:08:11 |   98.00 | 96.50 |   92.03 |
|    1 | 猪八戒    |   20 | NULL                |   60.00 | 62.50 |   58.00 |
|    1 | 唐僧      |   16 | 2000-10-01 07:21:03 |   80.06 | 82.50 |   86.00 |
+------+-----------+------+---------------------+---------+-------+---------+
5 rows in set (0.00 sec)

分页查询

语法
在所有数据中查询前n条

select * from [表名] limit n;

在所有数据中从第s条开始查询前n条

select * from [表名] limit n offset s;

示例

select * from student limit 3;

+------+-----------+------+---------------------+---------+-------+---------+
| id   | name      | age  | birth               | chinese | math  | english |
+------+-----------+------+---------------------+---------+-------+---------+
|    3 | 孙悟空    |   22 | 1998-02-28 15:08:11 |   98.00 | 96.50 |   92.03 |
|    1 | 猪八戒    |   20 | NULL                |   60.00 | 62.50 |   58.00 |
|    1 | 唐僧      |   16 | 2000-10-01 07:21:03 |   80.06 | 82.50 |   86.00 |
+------+-----------+------+---------------------+---------+-------+---------+
3 rows in set (0.00 sec)

select * from student limit 2 offset 2;

+------+-----------+------+---------------------+---------+-------+---------+
| id   | name      | age  | birth               | chinese | math  | english |
+------+-----------+------+---------------------+---------+-------+---------+
|    1 | 唐僧      |   16 | 2000-10-01 07:21:03 |   80.06 | 82.50 |   86.00 |
|    5 | 沙悟净    |   21 | 1999-05-08 21:10:18 |   72.00 | 75.08 |   70.03 |
+------+-----------+------+---------------------+---------+-------+---------+
2 rows in set (0.00 sec)

多字段的表达式查询

语法

select 列1 + 列2 ...... from [表名];

示例

select id, name, chinese + math from student;

+------+-----------+----------------+
| id   | name      | chinese + math |
+------+-----------+----------------+
|    3 | 孙悟空    |         194.50 |
|    1 | 猪八戒    |         122.50 |
|    1 | 唐僧      |         162.56 |
|    5 | 沙悟净    |         147.08 |
|    6 | 白龙马    |         146.08 |
+------+-----------+----------------+
5 rows in set (0.00 sec)

字段的别名查询

语法

select 列1 + 列2 as 别名 from [表名];

示例

select id, name, chinese + math + english as score from student;

+------+-----------+--------+
| id   | name      | score  |
+------+-----------+--------+
|    3 | 孙悟空    | 286.53 |
|    1 | 猪八戒    | 180.50 |
|    1 | 唐僧      | 248.56 |
|    5 | 沙悟净    | 217.11 |
|    6 | 白龙马    | 217.12 |
+------+-----------+--------+
5 rows in set (0.00 sec)

数据的去重查询

语法
根据某一字段，进行数据去重后，再查询

select distinct 列 ...... from [表名];

示例

select distinct id from student;

+------+
| id   |
+------+
|    3 |
|    1 |
|    5 |
|    6 |
+------+
4 rows in set (0.00 sec)

条件查询

比较运算符

操作符	描述
=, <=>	检测两个值是否相等
!=, <>	检测两个值是否不相等
>	检测左边的值是否大于右边的值
<	检测左边的值是否小于右边的值
>=	检测左边的值是否大于等于右边的值
<=	检测左边的值是否小于等于右边的值
like	模糊匹配
in	判断查询的数据是否符合给与的多个选项数据之一
between…and…	判断查询范围是否介于两个值之间

逻辑运算符

操作符	描述
and	逻辑与，两个条件都满足返回true
or	逻辑或，两个条件任意满足一个返回true
not	逻辑非，若不满足条件返回true

语法

select * from [表名] where [条件];

示例
=的使用

select * from student where name = "孙悟空";

+------+-----------+------+---------------------+---------+-------+---------+
| id   | name      | age  | birth               | chinese | math  | english |
+------+-----------+------+---------------------+---------+-------+---------+
|    3 | 孙悟空    |   22 | 1998-02-28 15:08:11 |   98.00 | 96.50 |   92.03 |
+------+-----------+------+---------------------+---------+-------+---------+
1 row in set (0.00 sec)

in的使用

select * from student where name in ("唐僧", "沙悟净");

+------+-----------+------+---------------------+---------+-------+---------+
| id   | name      | age  | birth               | chinese | math  | english |
+------+-----------+------+---------------------+---------+-------+---------+
|    1 | 唐僧      |   16 | 2000-10-01 07:21:03 |   80.06 | 82.50 |   86.00 |
|    5 | 沙悟净    |   21 | 1999-05-08 21:10:18 |   72.00 | 75.08 |   70.03 |
+------+-----------+------+---------------------+---------+-------+---------+
2 rows in set (0.00 sec)

between…and…的使用

select * from student where chinese between 80 and 100;

+------+-----------+------+---------------------+---------+-------+---------+
| id   | name      | age  | birth               | chinese | math  | english |
+------+-----------+------+---------------------+---------+-------+---------+
|    3 | 孙悟空    |   22 | 1998-02-28 15:08:11 |   98.00 | 96.50 |   92.03 |
|    1 | 唐僧      |   16 | 2000-10-01 07:21:03 |   80.06 | 82.50 |   86.00 |
+------+-----------+------+---------------------+---------+-------+---------+
2 rows in set (0.00 sec)

like的使用

select * from student where name like "白%" or math > 80;

+------+-----------+------+---------------------+---------+-------+---------+
| id   | name      | age  | birth               | chinese | math  | english |
+------+-----------+------+---------------------+---------+-------+---------+
|    3 | 孙悟空    |   22 | 1998-02-28 15:08:11 |   98.00 | 96.50 |   92.03 |
|    1 | 唐僧      |   16 | 2000-10-01 07:21:03 |   80.06 | 82.50 |   86.00 |
|    6 | 白龙马    |   19 | 2000-03-26 14:10:58 |   70.00 | 76.08 |   71.04 |
+------+-----------+------+---------------------+---------+-------+---------+
3 rows in set (0.00 sec)

修改数据（update）

注意不能把状态条件忘记，否则修改是针对整张表的
语法

update [表名] set 列1 = val1 ...... where [条件];

示例

update student set birth = "2004-10-1 07:21:03" where name = "唐僧";

+------+--------+------+---------------------+---------+-------+---------+
| id   | name   | age  | birth               | chinese | math  | english |
+------+--------+------+---------------------+---------+-------+---------+
|    1 | 唐僧   |   16 | 2004-10-01 07:21:03 |   80.06 | 82.50 |   86.00 |
+------+--------+------+---------------------+---------+-------+---------+
1 row in set (0.00 sec)

删除数据（delete）

注意不能把状态条件忘记，否则删除是针对整张表的
语法

delete from [表名] where [条件];

示例

delete from student where name = "白龙马";

+------+-----------+------+---------------------+---------+-------+---------+
| id   | name      | age  | birth               | chinese | math  | english |
+------+-----------+------+---------------------+---------+-------+---------+
|    3 | 孙悟空    |   22 | 1998-02-28 15:08:11 |   98.00 | 96.50 |   92.03 |
|    1 | 猪八戒    |   20 | NULL                |   60.00 | 62.50 |   58.00 |
|    1 | 唐僧      |   16 | 2004-10-01 07:21:03 |   80.06 | 82.50 |   86.00 |
|    5 | 沙悟净    |   21 | 1999-05-08 21:10:18 |   72.00 | 75.08 |   70.03 |
+------+-----------+------+---------------------+---------+-------+---------+
4 rows in set (0.00 sec)