Mariadb学习总结（七）：单表查询

共计 13370 个字符，预计需要花费 34 分钟才能阅读完成。

单表简单查询

前几天记了下创建、删除、修改数据库，表啊之类的学习笔记，今天终于要开始查询了，查询数据嘛~ 在我心里反正挺难的，毕竟 SQL 不好写，脑袋笨啊。

首先呢，Mysql 官方提供了一个数据库实例给我们用，那~ 就是大名鼎鼎的 world.sql.

进入数据库后，输入 SOURCE /PATH/world.sql
例如：

MariaDB [world]> SOURCE /root/world.sql
MariaDB [world]> SHOW DATABASES;  // 可以看到我们的 world 库了
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb               |
| mysql              |
| performance_schema |
| world              |
+--------------------+
MariaDB [world]> USE world;  // 切换数据库
Database changed
MariaDB [world]> SHOW TABLES;  // 有三张表供我们使用
+-----------------+
| Tables_in_world |
+-----------------+
| city            |
| country         |
| countrylanguage |
+-----------------+
3 rows in set (0.00 sec)
MariaDB [world]> DESC city;  //city 表的结构
+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| ID          | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name        | char(35) | NO   |     |         |                |
| CountryCode | char(3)  | NO   | MUL |         |                |
| District    | char(20) | NO   |     |         |                |
| Population  | int(11)  | NO   |     | 0       |                |
+-------------+----------+------+-----+---------+----------------+

非常简单的单表查询

先看下 SELECT 语句的语法吧：

SELECT
    [ALL | DISTINCT | DISTINCTROW]
    [HIGH_PRIORITY]
    [STRAIGHT_JOIN]
    [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
    [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr ...]
    [ FROM table_references
      [WHERE where_condition]
      [GROUP BY {col_name | expr | position} [ASC | DESC], ... [WITH ROLLUP]]
      [HAVING where_condition]
      [ORDER BY {col_name | expr | position} [ASC | DESC], ...]
      [LIMIT {[offset,] row_count | row_count OFFSET offset}]
      [PROCEDURE procedure_name(argument_list)]
      [INTO OUTFILE 'file_name' [CHARACTER SET charset_name] [export_options]

其中 select_expr 可以为其下值：

列的名称
*-> 所有列
Mysql 函数和各种操作符
tbl_name.* 引用其他表的列

1. 查询所有列

这个就没什么说的了，非常非常简单~

MariaDB [world]> SELECT * FROM city;

2. 查询指定列并将其列显示为别名

别名是个很有用的功能呢~ 特别是多表查询的时候

MariaDB [world]> SELECT ID as 'iid' FROM city LIMIT 2;
+-----+
| iid |
+-----+
| 129 |
|   1 |
+-----+

3. 限定显示行数 -LIMIT

之前也有用到 LIMIT，LIMIT 关键字接收两个参数，第一个参数是偏移位置，第二个参数是显示行数

MariaDB [world]> SELECT * FROM city LIMIT 2,2;  // 这里默认是按主键排序的，所以 2 表示显示第 3 行数据，一共显示两行
+----+----------------+-------------+----------+------------+
| ID | Name           | CountryCode | District | Population |
+----+----------------+-------------+----------+------------+
|  3 | Herat          | AFG         | Herat    |     186800 |
|  4 | Mazar-e-Sharif | AFG         | Balkh    |     127800 |
+----+----------------+-------------+----------+------------+
2 rows in set (0.01 sec)

4. 按照指定列排序排序数据后显示

ODER BY 之前也有用到，根据指定列排序嘛~ ASC 是顺序显示（默认，从小到大），DESC 是倒序显示，当指定了多个列时，先按前面的列排序（分了一组），然后再在组内按后面的列排序，依次类推。以下就显示了人口最多的两个国家：

MariaDB [world]> SELECT * FROM city ORDER BY Population DESC LIMIT 2;
+------+-----------------+-------------+-------------+------------+
| ID   | Name            | CountryCode | District    | Population |
+------+-----------------+-------------+-------------+------------+
| 1024 | Mumbai (Bombay) | IND         | Maharashtra |   10500000 |
| 2331 | Seoul           | KOR         | Seoul       |    9981619 |
+------+-----------------+-------------+-------------+------------+
2 rows in set (0.01 sec)

5. 去除重复的的数据

SELECT DISTINCT 列名 FROM 表名;
 SELECT DISTINCT * FROM  city; // 当然，这个 city 表中是没有重复数据的

那我们创建一个表测试一下吧：

MariaDB [world]> CREATE TABLE test(name VARCHAR(50),pass VARCHAR(50));

MariaDB [world]> INSERT INTO test VALUES  // 插入了 4 条测试数据
    -> ('test','123'),
    -> ('test','321'),
    -> ('test','123'),
    -> ('test1','123');
MariaDB [world]> SELECT DISTINCT * FROM test;
+-------+------+
| name  | pass |
+-------+------+
| test  | 123  |
| test  | 321  |
| test1 | 123  |
+-------+------+

MariaDB [world]> SELECT DISTINCT name,pass FROM test;                                    
+-------+------+
| name  | pass |
+-------+------+
| test  | 123  |
| test  | 321  |
| test1 | 123  |

MariaDB [world]> SELECT DISTINCT pass,name FROM test;
+------+-------+
| pass | name  |
+------+-------+
| 123  | test  |
| 321  | test  |
| 123  | test1 |
+------+-------+

所以，完全相同的行才会被当作重复数据排除掉。

6. 使用常量、表达式、函数进行排序

MariaDB [world]> SELECT 
'The City Info:',        // 显示一列常量，当然也可是变量
Name,                    // 普通的一列
CountryCode AS Country,  // 使用别名
Population*100 AS Population ,   // 对人口放大 100 倍
MD5(ID) AS ID            // 使用 MD5 函数生成 ID 这一列的 MD5 值
FROM city 
ORDER BY Population DESC 
LIMIT 2;
+----------------+-----------------+---------+------------+----------------------------------+
| The City Info: | Name            | Country | Population | ID                               |
+----------------+-----------------+---------+------------+----------------------------------+
| The City Info: | Mumbai (Bombay) | IND     | 1050000000 | 021bbc7ee20b71134d53e20206bd6feb |
| The City Info: | Seoul           | KOR     |  998161900 | 273448411df1962cba1db6c05b3213c9 |
+----------------+-----------------+---------+------------+----------------------------------+
2 rows in set (0.01 sec)

使用 WHERE 子句过滤结果集

上面的内容，我们只是控制了显示哪些列，而并没有控制显示哪些行，当然，LIMIT 可能算是控制了显示哪些行；但经常我们会有更复杂的要求，比如查找所有人口大于 1,000,000 的城市，这时候就可以使用 WHERE 子句来控制显示哪些行了。

当然，这些过滤动作可以放在业务层来做，可是，我们费了很大的力气传输了数万条数据却只用到其中的一条，有必要嘛？而且，谁能保证业务层的过滤算法能比数据库管理系统中的更高效呢？所以~ 一般很少有人在业务层进行数据过滤。

[WHERE where_condition]

使用 WHERE 子句仅需后面跟上 where_condition 即可，where_condition 包括操作符、函数等，只要这行数据使这个表达式为 TRUE 则显示此行：

常用操作符：

大于: >;
小于:
等于: =;
大于等于: >=;
小于等于:
不等于: !=;

1. 查询人口超过 1000000 的城市

MariaDB [world]> SELECT * FROM city WHERE Population >= 1000000;
+------+--------------------------+-------------+----------------------+------------+
| ID   | Name                     | CountryCode | District             | Population |
+------+--------------------------+-------------+----------------------+------------+
|    1 | Kabul                    | AFG         | Kabol                |    1780000 |
|   35 | Alger                    | DZA         | Alger                |    2168000 |
|   56 | Luanda                   | AGO         | Luanda               |    2022000 |
|   69 | Buenos Aires             | ARG         | Distrito Federal     |    2982146 |
................................ 人工省略.............................................
| 3798 | Phoenix                  | USA         | Arizona              |    1321045 |
| 3799 | San Diego                | USA         | California           |    1223400 |
| 3800 | Dallas                   | USA         | Texas                |    1188580 |
| 3801 | San Antonio              | USA         | Texas                |    1144646 |
| 4068 | Harare                   | ZWE         | Harare               |    1410000 |
+------+--------------------------+-------------+----------------------+------------+
238 rows in set (0.09 sec)
// 可以看到，有 238 个城市的人口大于 1000000

2. 判空操作 ->IS NULL

当给定列的值为 NULL 时返回 TRUE，否则返回 FALSE

MariaDB [world]> SELECT * FROM city WHERE CountryCode IS NULL;
Empty set (0.00 sec)

MariaDB [world]> SELECT NULL IS NULL;  // 仅当给定值为 NULL 时，返回 TRUE
+--------------+
| NULL IS NULL |
+--------------+
|            1 |
+--------------+
1 row in set (0.01 sec)

3. 逻辑与 AND 操作符

表达式（测试条件）1 AND 表达式（测试条件）2：当表达式 1 和表达式 2 都为 TRUE 时，整个表达式才成立

查询中国所有人口大于 100000 的城市：

MariaDB [world]> SELECT * FROM city WHERE CountryCode = 'CHN' AND Population > 100000;
+------+---------------------+-------------+----------------+------------+
| ID   | Name                | CountryCode | District       | Population |
+------+---------------------+-------------+----------------+------------+
| 1890 | Shanghai            | CHN         | Shanghai       |    9696300 |
| 1891 | Peking              | CHN         | Peking         |    7472000 |
............................. 手工省略.....................................
| 2228 | Zhucheng            | CHN         | Shandong       |     102134 |
| 2229 | Kunshan             | CHN         | Jiangsu        |     102052 |
| 2230 | Haining             | CHN         | Zhejiang       |     100478 |
+------+---------------------+-------------+----------------+------------+
341 rows in set (0.00 sec)  // 当一条数据同时满足这两个条件才会被显示出来

4. 逻辑或操作符 OR

表达式 1 OR 表达式 2：任一表达式为 TRUE 时，整个表达式都为 TRUE，当表达式 1 已经为 TRUE 时不再判断表达式 2

列出所有北京和上海的城市：

一个城市所属的地区，不可能又属于北京又属于上海，所以是上海和北京城市的并集：

MariaDB [world]> SELECT * FROM city WHERE District = ‘Peking’ OR District = ‘Shanghai’;

+------+-----------+-------------+----------+------------+
| ID   | Name      | CountryCode | District | Population |
+------+-----------+-------------+----------+------------+
| 1890 | Shanghai  | CHN         | Shanghai |    9696300 |
| 1891 | Peking    | CHN         | Peking   |    7472000 |
| 2236 | Tong Xian | CHN         | Peking   |      97168 |
+------+-----------+-------------+----------+------------+
3 rows in set (0.00 sec)

5. 逻辑与或连用

当条件过多时，可以结合的使用与、或操作，比如：

查询北京或浙江人口大于 1000000 的城市：

MariaDB [world]> SELECT * FROM city WHERE District = 'Peking' OR District = 'Zhejiang' AND Population > 1000000;
+------+-----------+-------------+----------+------------+
| ID   | Name      | CountryCode | District | Population |
+------+-----------+-------------+----------+------------+
| 1891 | Peking    | CHN         | Peking   |    7472000 |
| 1905 | Hangzhou  | CHN         | Zhejiang |    2190500 |
| 1915 | Ningbo    | CHN         | Zhejiang |    1371200 |
| 2236 | Tong Xian | CHN         | Peking   |      97168 |
+------+-----------+-------------+----------+------------+
4 rows in set (0.00 sec)  
// 逻辑操作是从左向右的二目操作符，所以首先判断是 District = 'Peking' OR District = 'Zhejiang' 这个条件，当这个条件判断完后得到的 TRUE 或 FALSE，再用这个布尔值与 AND Population > 1000000 进行与操作。

当联合使用 AND 和 OR 时还是比较推荐使用（）的，这样不容易乱。

查询浙江人口小于 100000 且大于 10000 的城市：

MariaDB [world]> SELECT * FROM city WHERE District = 'Zhejiang' AND ( Population > 10000 AND Population 
今天突然翻文档发现一个东西，AND 的优先级要比 OR 优先级高，所以，看如下例子：
查询浙江或北京人口大于 100000 的城市：
MariaDB [world]> SELECT * FROM city WHERE District = 'Peking' OR District = 'Zhejiang' AND Population > 100000;
+------+-----------+-------------+----------+------------+
| ID   | Name      | CountryCode | District | Population |
+------+-----------+-------------+----------+------------+
| 1891 | Peking    | CHN         | Peking   |    7472000 |
| 1905 | Hangzhou  | CHN         | Zhejiang |    2190500 |
| 1915 | Ningbo    | CHN         | Zhejiang |    1371200 |
................... 手工省略...............................
| 2199 | Yuyao     | CHN         | Zhejiang |     114065 |
| 2230 | Haining   | CHN         | Zhejiang |     100478 |
| 2236 | Tong Xian | CHN         | Peking   |      97168 |   // 看这一行，为什么人口 97168 被筛选出来了呢？+------+-----------+-------------+----------+------------+
16 rows in set (0.00 sec)

// 原因是 District = 'Zhejiang' AND Population > 100000 为 FALSE，然后再与其前的 OR 进行运算，而这条数据正好地区是北京。// 所以想要的正确筛选数据，需要加一个括号
6. 范围检测 BETWEEN AND
一个值满足一段  连续的  范围时为 TRUE 否则为 FALSE
查询 ID 范围在 1 -10 的城市：
MariaDB [world]> SELECT * FROM city WHERE ID BETWEEN 1 AND 10;
+----+----------------+-------------+---------------+------------+
| ID | Name           | CountryCode | District      | Population |
+----+----------------+-------------+---------------+------------+
|  1 | Kabul          | AFG         | Kabol         |    1780000 |
|  2 | Qandahar       | AFG         | Qandahar      |     237500 |
|  3 | Herat          | AFG         | Herat         |     186800 |
|  4 | Mazar-e-Sharif | AFG         | Balkh         |     127800 |
|  5 | Amsterdam      | NLD         | Noord-Holland |     731200 |
|  6 | Rotterdam      | NLD         | Zuid-Holland  |     593321 |
|  7 | Haag           | NLD         | Zuid-Holland  |     440900 |
|  8 | Utrecht        | NLD         | Utrecht       |     234323 |
|  9 | Eindhoven      | NLD         | Noord-Brabant |     201843 |
| 10 | Tilburg        | NLD         | Noord-Brabant |     193238 |
+----+----------------+-------------+---------------+------------+
10 rows in set (0.00 sec)
刚用 Name BETWEEN 'abc' AND 'efg' 作为条件筛选了一下，竟然能筛选出 900 多行数据，不知道什么原理。
好像只匹配了第一个字符的 ASCII 值，这个东西实在想不明白有什么场景会把字符用上....
BETWEEN 1 AND 10 相当于  >=1 AND

7. 离散范围检测 IN
当一个值属于一段  离散数据  之中时为 TRUE，例如 1 IN (2,3) 明显 1 不等于 2 不等于 3，所以为 FALSE
查询 ID 属于 1，3，5，7，9 的城市：
MariaDB [world]> SELECT * FROM city WHERE ID IN (1,3,5,7,9);
+----+-----------+-------------+---------------+------------+
| ID | Name      | CountryCode | District      | Population |
+----+-----------+-------------+---------------+------------+
|  1 | Kabul     | AFG         | Kabol         |    1780000 |
|  3 | Herat     | AFG         | Herat         |     186800 |
|  5 | Amsterdam | NLD         | Noord-Holland |     731200 |
|  7 | Haag      | NLD         | Zuid-Holland  |     440900 |
|  9 | Eindhoven | NLD         | Noord-Brabant |     201843 |
+----+-----------+-------------+---------------+------------+
5 rows in set (0.01 sec)
查询北京、浙江、河南的所有城市：
MariaDB [world]> SELECT * FROM city WHERE District IN ('Peking','Zhejiang','Henan');
+------+--------------+-------------+----------+------------+
| ID   | Name         | CountryCode | District | Population |
+------+--------------+-------------+----------+------------+
| 1891 | Peking       | CHN         | Peking   |    7472000 |
| 1905 | Hangzhou     | CHN         | Zhejiang |    2190500 |
| 1906 | Zhengzhou    | CHN         | Henan    |    2107200 |
| 1915 | Ningbo       | CHN         | Zhejiang |    1371200 |
| 1934 | Luoyang      | CHN         | Henan    |     760000 |
| 1951 | Kaifeng      | CHN         | Henan    |     510000 |
...................... 手工省略...............................
| 2214 | Cixi         | CHN         | Zhejiang |     107329 |
| 2230 | Haining      | CHN         | Zhejiang |     100478 |
| 2236 | Tong Xian    | CHN         | Peking   |      97168 |
| 2242 | Yuzhou       | CHN         | Henan    |      92889 |
| 2246 | Linhai       | CHN         | Zhejiang |      90870 |
| 2252 | Huangyan     | CHN         | Zhejiang |      89288 |
+------+--------------+-------------+----------+------------+
36 rows in set (0.01 sec)
所以，IN 操作符跟 OR 的功能很类似，比如 District IN ('Peking','Zhejiang','Henan')等于 District = 'Peking' OR District = 'Zhejiang' OR District = 'Henan'
8. 逻辑非 NOT 
将其原本的布尔值进行逻辑非操作后再判断
比如：查询 ID<10 的城市
ID<10 可以写成 ID 不大于等于 =  也就是 !ID >=10
MariaDB [world]> SELECT * FROM city WHERE NOT ID >=10;
+----+----------------+-------------+---------------+------------+
| ID | Name           | CountryCode | District      | Population |
+----+----------------+-------------+---------------+------------+
|  1 | Kabul          | AFG         | Kabol         |    1780000 |
|  2 | Qandahar       | AFG         | Qandahar      |     237500 |
|  3 | Herat          | AFG         | Herat         |     186800 |
|  4 | Mazar-e-Sharif | AFG         | Balkh         |     127800 |
|  5 | Amsterdam      | NLD         | Noord-Holland |     731200 |
|  6 | Rotterdam      | NLD         | Zuid-Holland  |     593321 |
|  7 | Haag           | NLD         | Zuid-Holland  |     440900 |
|  8 | Utrecht        | NLD         | Utrecht       |     234323 |
|  9 | Eindhoven      | NLD         | Noord-Brabant |     201843 |
+----+----------------+-------------+---------------+------------+
9 rows in set (0.00 sec)
所以，NOT 可以用作以上任何的操作，比如，ID 范围不在 10-4000 的城市：
MariaDB [world]> SELECT * FROM city WHERE ID NOT BETWEEN 10 AND 4000;
+------+----------------------+-------------+----------------+------------+
| ID   | Name                 | CountryCode | District       | Population |
+------+----------------------+-------------+----------------+------------+
|    1 | Kabul                | AFG         | Kabol          |    1780000 |
|    2 | Qandahar             | AFG         | Qandahar       |     237500 |
|    3 | Herat                | AFG         | Herat          |     186800 |
............................. 手工省略.......................................
| 4077 | Jabaliya             | PSE         | North Gaza     |     113901 |
| 4078 | Nablus               | PSE         | Nablus         |     100231 |
| 4079 | Rafah                | PSE         | Rafah          |      92020 |
+------+----------------------+-------------+----------------+------------+
88 rows in set (0.00 sec)
9. 字符串搜索 LIKE
使用 LIKE 可以检索符合通配符的字符串，有如下两个字符串：

%：任意个任意字符
_：单个任意字符

搜索名称以 T 开头的城市：
MariaDB [world]> SELECT * FROM city WHERE Name LIKE 'Y%';
+------+--------------------+-------------+----------------+------------+
| ID   | Name               | CountryCode | District       | Population |
+------+--------------------+-------------+----------------+------------+
|  126 | Yerevan            | ARM         | Yerevan        |    1248700 |
|  516 | York               | GBR         | England        |     104425 |
|  955 | Yogyakarta         | IDN         | Yogyakarta     |     418944 |
| 1220 | Yamuna Nagar       | IND         | Haryana        |     144346 |
| 1300 | Yeotmal (Yavatmal) | IND         | Maharashtra    |     108578 |
| 1396 | Yazd               | IRN         | Yazd           |     326776 |
........................... 手工省略......................................
| 3888 | Yonkers            | USA         | New York       |     196086 |
+------+--------------------+-------------+----------------+------------+
63 rows in set (0.00 sec)
搜索名称为三个字母的城市：
MariaDB [world]> SELECT * FROM city WHERE Name LIKE '___';
+------+------+-------------+---------------------+------------+
| ID   | Name | CountryCode | District            | Population |
+------+------+-------------+---------------------+------------+
|   29 | Ede  | NLD         | Gelderland          |     101574 |
|  362 | Itu  | BRA         | São Paulo           |     132736 |
|  396 | Jaú  | BRA         | São Paulo           |     109965 |
|  454 | Poá  | BRA         | São Paulo           |      89236 |
| 1387 | Qom  | IRN         | Qom                 |     777677 |
................................................................
| 2902 | Ica  | PER         | Ica                 |     194820 |
| 3134 | Ulm  | DEU         | Baden-Württemberg   |     116103 |
| 3379 | Van  | TUR         | Van                 |     219319 |
| 3588 | Ufa  | RUS         | Baškortostan        |    1091200 |
| 3775 | Hue  | VNM         | Thua Thien-Hue      |     219149 |
+------+------+-------------+---------------------+------------+
31 rows in set (0.00 sec)

MariaDB [world]> SELECT * FROM city WHERE CHAR_LENGTH(Name)=3;  // 与以上结果相同，函数在下面的文章总结。

阿里云 2 核 2G 服务器 3M 带宽 61 元 1 年，有高配
腾讯云新客低至 82 元 / 年，老客户 99 元 / 年
代金券：在阿里云专用满减优惠券