SELECT * FROM laowang;
#结果如下：
+----+----------+---------------+------+---------------------+
| id | username | email | age | created_at |
+----+----------+---------------+------+---------------------+
| 1 | wang | wang@163.com | 26 | 2025-05-12 10:31:50 |
| 2 | zhang | zhang@163.com | 28 | 2025-05-12 10:35:25 |
| 3 | li | li@163.com | 36 | 2025-05-12 10:35:25 |
+----+----------+---------------+------+---------------------+
3 rows in set (0.00 sec)
3.2查询指定字段
SELECT username, email FROM laowang;
#输出如下：
+----------+---------------+
| username | email |
+----------+---------------+
| wang | wang@163.com |
| zhang | zhang@163.com |
| li | li@163.com |
+----------+---------------+
3 rows in set (0.00 sec)
3.3根据条件查询
#查看laowang表中，年龄大于26，且名字l开头的字段
SELECT * FROM laowang WHERE age > 26 AND username LIKE 'l%';
#输出如下：
+----+----------+------------+------+---------------------+
| id | username | email | age | created_at |
+----+----------+------------+------+---------------------+
| 3 | li | li@163.com | 36 | 2025-05-12 10:35:25 |
+----+----------+------------+------+---------------------+
1 row in set (0.00 sec)
3.4排序与分页
#查询laowang表中按age降序排列后的第2-3条记录SELECT * FROM laowang ORDER BY age DESC LIMIT 2 OFFSET 1;
#输出如下：
+----+----------+---------------+------+---------------------+
| id | username | email | age | created_at |
+----+----------+---------------+------+---------------------+
| 2 | zhang | zhang@163.com | 28 | 2025-05-12 10:35:25 |
| 1 | wang | wang@163.com | 26 | 2025-05-12 10:31:50 |
+----+----------+---------------+------+---------------------+
2 rows in set (0.00 sec)
3.5聚合函数
#计算laowang表中，年龄的平均大小
SELECT COUNT(*) AS users, AVG(age) AS age FROM laowang;
#输出如下：
+-------+---------+
| users | age |
+-------+---------+
| 3 | 30.0000 |
+-------+---------+
1 row in set (0.00 sec)
3.6查询数据分析
字段
功能
WHERE
过滤条件
AND
连接符
LIKE ‘l%’
索引匹配前缀’l’
ORDER BY
排序
DESC
降序
LIMIT 2
返回2条记录
OFFSET 1
跳过前面1条记录，从第2条记录开始
COUNT(*)
统计laowang表的总行数
AVG(age)
计算所有用户的平均年龄
AS
别名
四、修改数据(UPDATE)
4.1修改单条数据
#修改laowang表中，id=1记录的email和age字段
UPDATE laowang-> SET email = 'laowang@163.com', age =28-> WHERE id = 1;
#输出如下：
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
4.2修改多条数据
UPDATE laowang-> SET age = age + 1-> WHERE age <30;
#输出如下：
Query OK, 3 rows affected (0.00 sec)
4.3查询表变化
SELECT * FROM laowang;
#输出如下：
+----+----------+-----------------+------+---------------------+
| id | username | email | age | created_at |
+----+----------+-----------------+------+---------------------+
| 1 | wang | laowang@163.com | 30 | 2025-05-12 10:31:50 |
| 2 | zhang | zhang@163.com | 30 | 2025-05-12 10:35:25 |
| 3 | li | li@163.com | 37 | 2025-05-12 10:35:25 |
+----+----------+-----------------+------+---------------------+
3 rows in set (0.00 sec)
4.4修改数据分析
字段
功能
UPDATE
修改表
SET email
修改email字段
WHERE id = 1
匹配id为1
SET age = age + 1
age字段自增1
WHERE age <30
匹配age小于30
五、删除数据(DELETE)
5.1删除单条记录
DELETE FROM laowang WHERE id = 2;
#输出如下：
Query OK, 1 row affected (0.00 sec)#结果如下：
SELECT * FROM laowang;
+----+----------+-----------------+------+---------------------+
| id | username | email | age | created_at |
+----+----------+-----------------+------+---------------------+
| 1 | wang | laowang@163.com | 30 | 2025-05-12 10:31:50 |
| 3 | li | li@163.com | 37 | 2025-05-12 10:35:25 |
+----+----------+-----------------+------+---------------------+
2 rows in set (0.00 sec)
5.2条件删除
#删除年龄大于30的条件
DELETE FROM laowang WHERE age > 30;
#输出如下：
Query OK, 1 row affected (0.00 sec)#结果如下：
SELECT * FROM laowang;
+----+----------+-----------------+------+---------------------+
| id | username | email | age | created_at |
+----+----------+-----------------+------+---------------------+
| 1 | wang | laowang@163.com | 30 | 2025-05-12 10:31:50 |
+----+----------+-----------------+------+---------------------+
1 row in set (0.00 sec)
5.3清空表(保留表结构)
DELETE FROM laowang;
#输出如下：
Query OK, 1 row affected (0.00 sec)#结果如下：
SELECT * FROM laowang;
Empty set (0.00 sec)
