MySQL：单表查询

Nooryes_

已于 2023-12-30 17:41:42 修改

阅读量876

点赞数 22

文章标签： mysql 数据库

于 2023-12-06 08:51:01 首次发布

本文链接：https://blog.csdn.net/Bob516516/article/details/134802178

版权

本文详细介绍了MySQL中的单表查询操作，包括`SELECT`、`FROM`、`WHERE`、`IN`、`BETWEEN`、`IS NULL`等条件筛选，以及聚合函数如`SUM`、`AVG`、`MAX`、`MIN`和`COUNT`。还讨论了`LIKE`模糊查询、`ORDER BY`排序、`GROUP BY`分组、`HAVING`筛选、`LIMIT`限制返回记录数和`DISTINCT`去重等功能。示例丰富，适用于初学者和进阶者提升MySQL查询技能。

摘要由CSDN通过智能技术生成

select

select 2*7 as res; 【as res: 把res当作字段(别名) as: 用作;别名】


mysql> select 'qnmd'
    -> ;
+------+
| qnmd |
+------+
| qnmd |
+------+
1 row in set (0.00 sec)

mysql> select 2*7;
+-----+
| 2*7 |
+-----+
|  14 |
+-----+
1 row in set (0.01 sec)

mysql> select 'go fuck' as qnmd;
+---------+
| qnmd    |
+---------+
| go fuck |
+---------+
1 row in set (0.00 sec)

mysql> select 2*7 as res;
+-----+
| res |
+-----+
|  14 |
+-----+
1 row in set (0.00 sec)

from

· select * from /表名/;

· select * from /表名/,/表名/...;多个表一起查，会返回笛卡尔积

mysql> select * from t1;
+------+----------+
| id   | name     |
+------+----------+
|    1 | noooryes |
|    2 | Tom      |
+------+----------+
2 rows in set (0.00 sec)

mysql> select * from t2;
+--------+--------+
| score1 | score2 |
+--------+--------+
|     99 |     11 |
|     99 |     61 |
+--------+--------+
2 rows in set (0.00 sec)

mysql> select * from t1, t2;
+------+----------+--------+--------+
| id   | name     | score1 | score2 |
+------+----------+--------+--------+
|    1 | noooryes |     99 |     11 |
|    2 | Tom      |     99 |     11 |
|    1 | noooryes |     99 |     61 |
|    2 | Tom      |     99 |     61 |
+------+----------+--------+--------+
4 rows in set (0.00 sec)

dual

eg：select 2*7 as res from dual;【from dual可以不写】

dual是一个虚拟表

mysql> select 2*7 as res from dual;
+-----+
| res |
+-----+
|  14 |
+-----+
1 row in set (0.00 sec)

mysql> select 2*7 as res;
+-----+
| res |
+-----+
|  14 |
+-----+
1 row in set (0.00 sec)

where

作用是条件筛选,一条一条地查

mysql> select * from t1;
+------+----------+
| id   | name     |
+------+----------+
|    1 | noooryes |
|    2 | Tom      |
|    3 | Tom      |
|    4 | Tom      |
+------+----------+
4 rows in set (0.01 sec)

mysql> select * from t1 where name='Tom' and id=3;
+------+------+
| id   | name |
+------+------+
|    3 | Tom  |
+------+------+
1 row in set (0.01 sec)

in(替代=)，not in

in: 限定查找范围

mysql> select * from t1 where name in ('Tom');
+------+------+
| id   | name |
+------+------+
|    2 | Tom  |
|    3 | Tom  |
|    4 | Tom  |
+------+------+
3 rows in set (0.01 sec)