MYSQL·其四·SELECT,_select * from table-优快云博客

本文链接：https://blog.youkuaiyun.com/HandsomeChow/article/details/90747979

本文介绍了MYSQL中的SELECT选择语句，包括查看和修改表格、WHERE条件约束、选择特定列、使用DISTINCT去除重复值以及排序操作。还提到了DATE日期计算的相关知识点，如日期比较和排序，并指出MYSQL对字符串的大小写不敏感。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

对照着官网展示的结果，发现我上一篇自己打的Txt文件跟官网不同……所以结果不同，算了，不是很重要。往下看吧。

4.1 SELECT选择语句

SELECT语句用于从表格中获取信息，一般格式为：

SELECT what_to_select        --字段名。*符号表示全部字段
FROM which_table             --表名
WHERE conditions_to_satisfy; --选择条件，可省略

以下是一些具体示范。

查看整个表格。

SELECT * FROM 表格名;

mysql> SELECT * FROM pet;    --从表格pet中选择全部列的信息，即查看整个表格
+----------+--------+---------+------+------------+------------+
| name     | owner  | species | sex  | birth      | death      |
+----------+--------+---------+------+------------+------------+
| Fluffy   | Harold | cat     | f    | 1933-02-04 | 0000-00-00 |
| Claws    | Gwen   | dog     | m    | 1994-03-17 | 0000-00-00 |
| Buffy    | Harold | dog     | f    | 1989-05-13 | 0000-00-00 |
| Fang     | Benny  | dog     | m    | 1990-08-27 | 0000-00-00 |
| Bowser   | Diane  | dog     | m    | 1979-08-31 | 1995-07-29 |
| Chirpy   | Gwen   | bird    | NULL | 1997-12-09 | 0000-00-00 |
| Whistler | Gwen   | snake   | m    | 1996-04-29 | NULL       |
| Fluffy   | Harold | cat     | f    | 1933-02-04 | NULL       |
| Claws    | Gwen   | dog     | m    | 1994-03-17 | NULL       |
| Buffy    | Harold | dog     | f    | 1989-05-13 | NULL       |
| Fang     | Benny  | dog     | m    | 1990-08-27 | NULL       |
| Bowser   | Diane  | dog     | m    | 1979-08-31 | 1995-07-29 |
| Chirpy   | Gwen   | bird    | NULL | 1997-12-09 | NULL       |
| Whistler | Gwen   | snake   | m    | 1996-04-29 | NULL       |
| Puffball | Diane  | hamster | f    | 1999-03-30 | NULL       |
+----------+--------+---------+------+------------+------------+
15 rows in set (0.35 sec)

修改表格。两种方法：

1、删除整个表格，修改txt内容，再全部导入。显然，对于小改动，开销太大。

2、使用update修改部分数据。

DELETE FROM pet;
LOAD DATA LOCAL INFILE 'pet.txt' INTO TABLE pet;

UPDATE pet SET birth = '1989-08-31' WHERE name = 'Bowser';

4.2WHERE 通过条件约束，从表中选择特定的行

梳理一下脉络。

表格包括行和列两个维度。

SELECT * FROM table;    --选择了所有的行和列，也就是整个表格。

进一步地，我不是对整个表格感兴趣，只希望选择出符合某些条件的行，那么使用WHERE来筛选。

在WHERE中，可以使用=, >=, <=, !=, AND, OR, ()等运算符进行条件匹配、组合。

以及注意到，这条命令“SELECT * FROM pet WHERE birth >= '1998-1-1';”，虽然从文本上看，日期是字符串，但背后其实是DATE类型，有相应的函数处理，比如这里，能够使用>=进行比较。

SELECT * FROM table;    --选择所有的行和列

SELECT * FROM table WHERE condition;    --选择符合条件的行

--选择“name 字段是Bowser”的行，双引号中的内容就是条件
mysql> SELECT * FROM pet WHERE name = 'Bowser';
+--------+-------+---------+------+------------+------------+
| name   | owner | species | sex  | birth      | death      |
+--------+-------+---------+------+------------+------------+
| Bowser | Diane | dog     | m    | 1989-08-31 | 1995-07-29 |
| Bowser | Diane | dog     | m    | 1989-08-31 | 1995-07-29 |
+--------+-------+---------+------+------------+------------+
2 rows in set (0.00 sec)

-->=
mysql> SELECT * FROM pet WHERE birth >= '1998-1-1';
+----------+-------+---------+------+------------+-------+
| name     | owner | species | sex  | birth      | death |
+----------+-------+---------+------+------------+-------+
| Puffball | Diane | hamster | f    | 1999-03-30 | NULL  |
+----------+-------+---------+------+------------+-------+
1 row in set (0.00 sec)

--AND
mysql> SELECT * FROM pet WHERE species = 'dog' AND sex = 'f';
+-------+--------+---------+------+------------+------------+
| name  | owner  | species | sex  | birth      | death      |
+-------+--------+---------+------+------------+------------+
| Buffy | Harold | dog     | f    | 1989-05-13 | 0000-00-00 |
| Buffy | Harold | dog     | f    | 1989-05-13 | NULL       |
+-------+--------+---------+------+------------+------------+
2 rows in set (0.00 sec)

--OR
mysql> SELECT * FROM pet WHERE species = 'snake' OR species = 'bird';
+----------+-------+---------+------+------------+------------+
| name     | owner | species | sex  | birth      | death      |
+----------+-------+---------+------+------------+------------+
| Chirpy   | Gwen  | bird    | NULL | 1997-12-09 | 0000-00-00 |
| Whistler | Gwen  | snake   | m    | 1996-04-29 | NULL       |
| Chirpy   | Gwen  | bird    | NULL | 1997-12-09 | NULL       |
| Whistler | Gwen  | snake   | m    | 1996-04-29 | NULL       |
+----------+-------+---------+------+------------+------------+
4 rows in set (0.00 sec)

--()
mysql> SELECT * FROM pet WHERE(species = 'cat' AND sex = 'm')OR(species = 'dog' AND sex = 'f');
+-------+--------+---------+------+------------+------------+
| name  | owner  | species | sex  | birth      | death      |
+-------+--------+---------+------+------------+------------+
| Buffy | Harold | dog     | f    | 1989-05-13 | 0000-00-00 |
| Buffy | Harold | dog     | f    | 1989-05-13 | NULL       |
+-------+--------+---------+------+------------+------------+
2 rows in set (0.00 sec)

注意，MYSQL对string类型的大小写不敏感，也就是bowser和BOwser诸如此类，对它来说是没有差别的。

4.3 通过字段名，选择特定的列

在通过条件对行进行筛选的基础上，我们也不一定想看到表格的所有属性，比如我们只关心name，birth两个字段/属性，进而产生了选择特定的列的操作需求。

DISTINCT。有一些属性的值重复出现，使用DISTINCT使其只输出一次。

SELECT * FROM table;    --选择所有的行和列

SELECT * FROM table WHERE condition;    --选择符合条件的行

SELECT name1, name2, name3 FROM table WHERE condition;    --选择特定的列/属性

mysql> SELECT name, birth FROM pet;
+----------+------------+
| name     | birth      |
+----------+------------+
| Fluffy   | 1933-02-04 |
| Claws    | 1994-03-17 |
| Buffy    | 1989-05-13 |
| Fang     | 1990-08-27 |
| Bowser   | 1989-08-31 |
| Chirpy   | 1997-12-09 |
| Whistler | 1996-04-29 |
| Fluffy   | 1933-02-04 |
| Claws    | 1994-03-17 |
| Buffy    | 1989-05-13 |
| Fang     | 1990-08-27 |
| Bowser   | 1989-08-31 |
| Chirpy   | 1997-12-09 |
| Whistler | 1996-04-29 |
| Puffball | 1999-03-30 |
+----------+------------+
15 rows in set (0.00 sec)

mysql> SELECT owner FROM pet;
+--------+
| owner  |
+--------+
| Harold |
| Gwen   |
| Harold |
| Benny  |
| Diane  |
| Gwen   |
| Gwen   |
| Harold |
| Gwen   |
| Harold |
| Benny  |
| Diane  |
| Gwen   |
| Gwen   |
| Diane  |
+--------+
15 rows in set (0.00 sec)

mysql> SELECT DISTINCT owner FROM pet;
+--------+
| owner  |
+--------+
| Harold |
| Gwen   |
| Benny  |
| Diane  |
+--------+
4 rows in set (0.01 sec)

mysql> SELECT name, species, birth FROM pet
    -> WHERE species = 'dog' OR species = 'cat';
+--------+---------+------------+
| name   | species | birth      |
+--------+---------+------------+
| Fluffy | cat     | 1933-02-04 |
| Claws  | dog     | 1994-03-17 |
| Buffy  | dog     | 1989-05-13 |
| Fang   | dog     | 1990-08-27 |
| Bowser | dog     | 1989-08-31 |
| Fluffy | cat     | 1933-02-04 |
| Claws  | dog     | 1994-03-17 |
| Buffy  | dog     | 1989-05-13 |
| Fang   | dog     | 1990-08-27 |
| Bowser | dog     | 1989-08-31 |
+--------+---------+------------+
10 rows in set (0.00 sec)

4.4 排序

一般默认的排序顺序是升序ASC，降序是DESC。

在前面的示例中，您可能已经注意到结果行的显示顺序并不特殊。当行以某种有意义的方式排序时，通常更容易检查查询输出。要对结果排序，请使用ORDER BY子句。

也就是，为了便于查看，在选择出感兴趣的列和行后，对其进行排序，使其呈现方式也符合我们的要求。

进一步地，可以使用多个列排序，也可以按不同方向对不同的列进行排序。

SELECT * FROM table;    --选择所有的行和列

SELECT * FROM table WHERE condition;    --选择符合条件的行

SELECT name1, name2, name3 FROM table WHERE condition;    --选择特定的列/属性

SELECT name FROM table WHERE condition ORDER BY name;   
--根据选择的字段的值进行排序，默认升序ASC ascending
SELECT name FROM table WHERE condition ORDER BY name DESC;    --降序

SELECT name FROM table WHERE condition ORDER BY BINARY name;    --强制对string大小写敏感

注意，在字符类型列上，与所有其他比较操作一样，排序通常以不区分大小写的方式执行。这意味着对于除大小写之外相同的列，顺序是未定义的。可以使用类似于:ORDER BY BINARY col_name的二进制命令，强制对列进行区分大小写的排序。

--升序ASC
mysql> SELECT name, birth FROM pet ORDER BY birth;
+----------+------------+
| name     | birth      |
+----------+------------+
| Fluffy   | 1933-02-04 |
| Fluffy   | 1933-02-04 |
| Buffy    | 1989-05-13 |
| Buffy    | 1989-05-13 |
| Bowser   | 1989-08-31 |
| Bowser   | 1989-08-31 |
| Fang     | 1990-08-27 |
| Fang     | 1990-08-27 |
| Claws    | 1994-03-17 |
| Claws    | 1994-03-17 |
| Whistler | 1996-04-29 |
| Whistler | 1996-04-29 |
| Chirpy   | 1997-12-09 |
| Chirpy   | 1997-12-09 |
| Puffball | 1999-03-30 |
+----------+------------+
15 rows in set (0.00 sec)

--降序DESC
mysql> SELECT name, birth FROM pet ORDER BY birth DESC;
+----------+------------+
| name     | birth      |
+----------+------------+
| Puffball | 1999-03-30 |
| Chirpy   | 1997-12-09 |
| Chirpy   | 1997-12-09 |
| Whistler | 1996-04-29 |
| Whistler | 1996-04-29 |
| Claws    | 1994-03-17 |
| Claws    | 1994-03-17 |
| Fang     | 1990-08-27 |
| Fang     | 1990-08-27 |
| Bowser   | 1989-08-31 |
| Bowser   | 1989-08-31 |
| Buffy    | 1989-05-13 |
| Buffy    | 1989-05-13 |
| Fluffy   | 1933-02-04 |
| Fluffy   | 1933-02-04 |
+----------+------------+
15 rows in set (0.00 sec)

--多个列.列的先后也决定了排序的先后。先满足species的排序，在此基础上，满足birth的排序
--species默认升序，birth指定降序
mysql> SELECT name,species,birth FROM pet ORDER BY species, birth DESC;
+----------+---------+------------+
| name     | species | birth      |
+----------+---------+------------+
| Chirpy   | bird    | 1997-12-09 |
| Chirpy   | bird    | 1997-12-09 |
| Fluffy   | cat     | 1933-02-04 |
| Fluffy   | cat     | 1933-02-04 |
| Claws    | dog     | 1994-03-17 |
| Claws    | dog     | 1994-03-17 |
| Fang     | dog     | 1990-08-27 |
| Fang     | dog     | 1990-08-27 |
| Bowser   | dog     | 1989-08-31 |
| Bowser   | dog     | 1989-08-31 |
| Buffy    | dog     | 1989-05-13 |
| Buffy    | dog     | 1989-05-13 |
| Puffball | hamster | 1999-03-30 |
| Whistler | snake   | 1996-04-29 |
| Whistler | snake   | 1996-04-29 |
+----------+---------+------------+
15 rows in set (0.00 sec)

4.5 DATE日期计算

总结一下以下会出现的用法。

SELECT name AS alias FROM table;    --在输出时name显示为alias别名
/*
其中，name不单可以是表格的属性字段名，也可以是函数，比如CURDATE()，也就是根据公式生成而不是现成的。
*/

mysql> SELECT name, birth, CURDATE(),TIMESTAMPDIFF(YEAR, birth,CURDATE())AS age FROM pet;
/*
请使用timestampdiff（）函数。它的参数是您希望结果表达的单位，以及两个日期，用于计算差异。


CURDATE()返回当前日期。
YEAR()返回年份。
MONTH()返回月份。
DAYOFMONTH()返回日期。
TIMESTAMPDIFF()计算日期差异。

*/

MONTH(birth) = MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH))
--DATE_ADD()日期加法MONTH(birth) = MOD(MONTH(CURDATE()),12)+1
--取余
SELECT '2018-10-31' + INTERVAL 1 DAY;
--INTERVAL

mysql> SELECT name, birth, CURDATE(),TIMESTAMPDIFF(YEAR, birth,CURDATE())AS age FROM pet;
/*
请使用timestampdiff（）函数。它的参数是您希望结果表达的单位，以及两个日期，用于计算差异。
*/
+----------+------------+------------+------+
| name     | birth      | CURDATE()  | age  |
+----------+------------+------------+------+
| Fluffy   | 1933-02-04 | 2019-06-03 |   86 |
| Claws    | 1994-03-17 | 2019-06-03 |   25 |
| Buffy    | 1989-05-13 | 2019-06-03 |   30 |
| Fang     | 1990-08-27 | 2019-06-03 |   28 |
| Bowser   | 1989-08-31 | 2019-06-03 |   29 |
| Chirpy   | 1997-12-09 | 2019-06-03 |   21 |
| Whistler | 1996-04-29 | 2019-06-03 |   23 |
| Fluffy   | 1933-02-04 | 2019-06-03 |   86 |
| Claws    | 1994-03-17 | 2019-06-03 |   25 |
| Buffy    | 1989-05-13 | 2019-06-03 |   30 |
| Fang     | 1990-08-27 | 2019-06-03 |   28 |
| Bowser   | 1989-08-31 | 2019-06-03 |   29 |
| Chirpy   | 1997-12-09 | 2019-06-03 |   21 |
| Whistler | 1996-04-29 | 2019-06-03 |   23 |
| Puffball | 1999-03-30 | 2019-06-03 |   20 |
+----------+------------+------------+------+
15 rows in set (0.01 sec)

--添加ORDER BY
mysql> SELECT name, birth, CURDATE(),TIMESTAMPDIFF(YEAR, birth,CURDATE())AS age FROM pet ORDER BY name;
+----------+------------+------------+------+
| name     | birth      | CURDATE()  | age  |
+----------+------------+------------+------+
| Bowser   | 1989-08-31 | 2019-06-03 |   29 |
| Bowser   | 1989-08-31 | 2019-06-03 |   29 |
| Buffy    | 1989-05-13 | 2019-06-03 |   30 |
| Buffy    | 1989-05-13 | 2019-06-03 |   30 |
| Chirpy   | 1997-12-09 | 2019-06-03 |   21 |
| Chirpy   | 1997-12-09 | 2019-06-03 |   21 |
| Claws    | 1994-03-17 | 2019-06-03 |   25 |
| Claws    | 1994-03-17 | 2019-06-03 |   25 |
| Fang     | 1990-08-27 | 2019-06-03 |   28 |
| Fang     | 1990-08-27 | 2019-06-03 |   28 |
| Fluffy   | 1933-02-04 | 2019-06-03 |   86 |
| Fluffy   | 1933-02-04 | 2019-06-03 |   86 |
| Puffball | 1999-03-30 | 2019-06-03 |   20 |
| Whistler | 1996-04-29 | 2019-06-03 |   23 |
| Whistler | 1996-04-29 | 2019-06-03 |   23 |
+----------+------------+------------+------+
15 rows in set (0.00 sec)

--添加ORDER BY
mysql> SELECT name, birth, CURDATE(),TIMESTAMPDIFF(YEAR, birth,CURDATE())AS age FROM pet ORDER BY age;
+----------+------------+------------+------+
| name     | birth      | CURDATE()  | age  |
+----------+------------+------------+------+
| Puffball | 1999-03-30 | 2019-06-03 |   20 |
| Chirpy   | 1997-12-09 | 2019-06-03 |   21 |
| Chirpy   | 1997-12-09 | 2019-06-03 |   21 |
| Whistler | 1996-04-29 | 2019-06-03 |   23 |
| Whistler | 1996-04-29 | 2019-06-03 |   23 |
| Claws    | 1994-03-17 | 2019-06-03 |   25 |
| Claws    | 1994-03-17 | 2019-06-03 |   25 |
| Fang     | 1990-08-27 | 2019-06-03 |   28 |
| Fang     | 1990-08-27 | 2019-06-03 |   28 |
| Bowser   | 1989-08-31 | 2019-06-03 |   29 |
| Bowser   | 1989-08-31 | 2019-06-03 |   29 |
| Buffy    | 1989-05-13 | 2019-06-03 |   30 |
| Buffy    | 1989-05-13 | 2019-06-03 |   30 |
| Fluffy   | 1933-02-04 | 2019-06-03 |   86 |
| Fluffy   | 1933-02-04 | 2019-06-03 |   86 |
+----------+------------+------------+------+
15 rows in set (0.00 sec)


mysql> SELECT name, birth, MONTH(birth) FROM pet;
+----------+------------+--------------+
| name     | birth      | MONTH(birth) |
+----------+------------+--------------+
| Fluffy   | 1933-02-04 |            2 |
| Claws    | 1994-03-17 |            3 |
| Buffy    | 1989-05-13 |            5 |
| Fang     | 1990-08-27 |            8 |
| Bowser   | 1989-08-31 |            8 |
| Chirpy   | 1997-12-09 |           12 |
| Whistler | 1996-04-29 |            4 |
| Fluffy   | 1933-02-04 |            2 |
| Claws    | 1994-03-17 |            3 |
| Buffy    | 1989-05-13 |            5 |
| Fang     | 1990-08-27 |            8 |
| Bowser   | 1989-08-31 |            8 |
| Chirpy   | 1997-12-09 |           12 |
| Whistler | 1996-04-29 |            4 |
| Puffball | 1999-03-30 |            3 |
+----------+------------+--------------+
15 rows in set (0.00 sec)

mysql> SELECT name, birth FROM pet WHERE MONTH(birth) = 5;
+-------+------------+
| name  | birth      |
+-------+------------+
| Buffy | 1989-05-13 |
| Buffy | 1989-05-13 |
+-------+------------+
2 rows in set (0.00 sec)

mysql> SELECT name, birth FROM pet
    -> WHERE MONTH(birth) = MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH));
Empty set (0.01 sec)

mysql> SELECT name, birth FROM pet
    -> WHERE MONTH(birth) = MOD(MONTH(CURDATE()),12)+1;
Empty set (0.35 sec)

mysql> SELECT '2018-10-31' + INTERVAL 1 DAY;
+-------------------------------+
| '2018-10-31' + INTERVAL 1 DAY |
+-------------------------------+
| 2018-11-01                    |
+-------------------------------+
1 row in set (0.00 sec)

mysql> SELECT '2018-10-32' + INTERVAL 1 DAY;
+-------------------------------+
| '2018-10-32' + INTERVAL 1 DAY |
+-------------------------------+
| NULL                          |
+-------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1292 | Incorrect datetime value: '2018-10-32' |
+---------+------+----------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT 1 IS NULL, 1 IS NOT NULL;
+-----------+---------------+
| 1 IS NULL | 1 IS NOT NULL |
+-----------+---------------+
|         0 |             1 |
+-----------+---------------+
1 row in set (0.00 sec)