45
Mariadb学习总结（七）：单表查询
source link: https://www.linuxprobe.com/singal-table-select.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
单表简单查询
前几天记了下创建、删除、修改数据库，表啊之类的学习笔记，今天终于要开始查询了，查询数据嘛~在我心里反正挺难的，毕竟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; //与以上结果相同，函数在下面的文章总结。
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK