建站
咨詢
售后
建站咨詢
新聞中心
這里有您想知道的互聯(lián)網(wǎng)營銷解決方案
詳解Mariadb單表查詢
MariaDB Server 是最流行的開源關(guān)系型數(shù)據(jù)庫之一。它由 MySQL 的原始開發(fā)者制作，并保證保持開源。它是大多數(shù)云產(chǎn)品的一部分，也是大多數(shù)Linux發(fā)行版的默認(rèn)配置。MariaDB 被設(shè)計為 MySQL 的直接替代產(chǎn)品，具有更多功能，新的存儲引擎，更少的錯誤和更好的性能。
目前成都創(chuàng)新互聯(lián)已為上1000+的企業(yè)提供了網(wǎng)站建設(shè)、域名、網(wǎng)頁空間、網(wǎng)站改版維護(hù)、企業(yè)網(wǎng)站設(shè)計、網(wǎng)站維護(hù)等服務(wù)，公司將堅持客戶導(dǎo)向、應(yīng)用為本的策略，正道將秉承"和諧、參與、激情"的文化，與客戶和合作伙伴齊心協(xié)力一起成長，共同發(fā)展。我們擁有完善的網(wǎng)絡(luò)基礎(chǔ)設(shè)施服務(wù)，能夠為企業(yè)或個人提供空間域名、網(wǎng)頁空間、企業(yè)郵局、網(wǎng)站加速、數(shù)據(jù)庫、云主機(jī)等網(wǎng)絡(luò)基礎(chǔ)服務(wù)。
單表簡單查詢
前幾天記了下創(chuàng)建、刪除、修改數(shù)據(jù)庫，表啊之類的學(xué)習(xí)筆記，今天終于要開始查詢了，查詢數(shù)據(jù)嘛~在我心里反正挺難的，畢竟SQL不好寫，腦袋笨啊。
首先呢，Mysql官方提供了一個數(shù)據(jù)庫實例給我們用，那~就是大名鼎鼎的world.sql.
進(jìn)入數(shù)據(jù)庫后，輸入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; //切換數(shù)據(jù)庫
Database changed
MariaDB [world]> SHOW TABLES; //有三張表供我們使用
+-----------------+
| Tables_in_world |
+-----------------+
| city |
| country |
| countrylanguage |
+-----------------+
3 rows in set (0.00 sec)
MariaDB [world]> DESC city; //city表的結(jié)構(gòu)
+-------------+----------+------+-----+---------+----------------+
| 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函數(shù)和各種操作符
-
tbl_name.* 引用其他表的列
1.查詢所有列
這個就沒什么說的了，非常非常簡單~
MariaDB [world]> SELECT * FROM city;
2.查詢指定列并將其列顯示為別名
別名是個很有用的功能呢~特別是多表查詢的時候
MariaDB [world]> SELECT ID as 'iid' FROM city LIMIT 2;
+-----+
| iid |
+-----+
| 129 |
| 1 |
+-----+
3.限定顯示行數(shù)-LIMIT
之前也有用到LIMIT，LIMIT 關(guān)鍵字接收兩個參數(shù)，第一個參數(shù)是偏移位置，第二個參數(shù)是顯示行數(shù)
MariaDB [world]> SELECT * FROM city LIMIT 2,2; //這里默認(rèn)是按主鍵排序的，所以2表示顯示第3行數(shù)據(jù)，一共顯示兩行
+----+----------------+-------------+----------+------------+
| 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.按照指定列排序排序數(shù)據(jù)后顯示
ODER BY之前也有用到，根據(jù)指定列排序嘛~ ASC是順序顯示（默認(rèn)，從小到大），DESC是倒序顯示，當(dāng)指定了多個列時，先按前面的列排序（分了一組），然后再在組內(nèi)按后面的列排序，依次類推。以下就顯示了人口最多的兩個國家：
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.去除重復(fù)的的數(shù)據(jù)
SELECT DISTINCT 列名 FROM 表名;
SELECT DISTINCT * FROM city; //當(dāng)然，這個city表中是沒有重復(fù)數(shù)據(jù)的
那我們創(chuàng)建一個表測試一下吧：
MariaDB [world]> CREATE TABLE test(name VARCHAR(50),pass VARCHAR(50));
MariaDB [world]> INSERT INTO test VALUES //插入了4條測試數(shù)據(jù)
-> ('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 |
+------+-------+
所以，完全相同的行才會被當(dāng)作重復(fù)數(shù)據(jù)排除掉。
6.使用常量、表達(dá)式、函數(shù)進(jìn)行排序
MariaDB [world]> SELECT
'The City Info:', //顯示一列常量，當(dāng)然也可是變量
Name, //普通的一列
CountryCode AS Country, //使用別名
Population*100 AS Population , //對人口放大100倍
MD5(ID) AS ID //使用MD5函數(shù)生成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子句過濾結(jié)果集
上面的內(nèi)容，我們只是控制了顯示哪些列，而并沒有控制顯示哪些行，當(dāng)然，LIMIT可能算是控制了顯示哪些行；但經(jīng)常我們會有更復(fù)雜的要求，比如查找所有人口大于1,000,000的城市，這時候就可以使用WHERE子句來控制顯示哪些行了。
當(dāng)然，這些過濾動作可以放在業(yè)務(wù)層來做，可是，我們費了很大的力氣傳輸了數(shù)萬條數(shù)據(jù)卻只用到其中的一條，有必要嘛？而且，誰能保證業(yè)務(wù)層的過濾算法能比數(shù)據(jù)庫管理系統(tǒng)中的更高效呢？所以~一般很少有人在業(yè)務(wù)層進(jìn)行數(shù)據(jù)過濾。
[WHERE where_condition]
使用WHERE子句僅需后面跟上where_condition即可，where_condition包括操作符、函數(shù)等，只要這行數(shù)據(jù)使這個表達(dá)式為TRUE則顯示此行：
常用操作符：
-
大于: >;
-
小于:
-
等于: =;
-
大于等于: >=;
-
小于等于:
-
不等于: !=;
1.查詢?nèi)丝诔^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
當(dāng)給定列的值為NULL時返回TRUE，否則返回FALSE
MariaDB [world]> SELECT * FROM city WHERE CountryCode IS NULL;
Empty set (0.00 sec)
MariaDB [world]> SELECT NULL IS NULL; //僅當(dāng)給定值為NULL時，返回TRUE
+--------------+
| NULL IS NULL |
+--------------+
| 1 |
+--------------+
1 row in set (0.01 sec)
3.邏輯與AND操作符
表達(dá)式（測試條件）1 AND 表達(dá)式（測試條件）2 ： 當(dāng)表達(dá)式1和表達(dá)式2都為TRUE時，整個表達(dá)式才成立
查詢中國所有人口大于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) //當(dāng)一條數(shù)據(jù)同時滿足這兩個條件才會被顯示出來
4.邏輯或操作符OR
表達(dá)式1 OR 表達(dá)式2： 任一表達(dá)式為TRUE時，整個表達(dá)式都為TRUE ，當(dāng)表達(dá)式1已經(jīng)為TRUE時不再判斷表達(dá)式2
列出所有北京和上海的城市：
一個城市所屬的地區(qū)，不可能又屬于北京又屬于上海，所以是上海和北京城市的并集：
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.邏輯與或連用
當(dāng)條件過多時，可以結(jié)合的使用與、或操作，比如：
查詢北京或浙江人口大于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'這個條件，當(dāng)這個條件判斷完后得到的TRUE或FALSE，再用這個布爾值與 AND Population > 1000000 進(jìn)行與操作。
當(dāng)聯(lián)合使用AND和OR時還是比較推薦使用（）的，這樣不容易亂。
查詢浙江人口小于100000且大于10000的城市：
MariaDB [world]> SELECT * FROM city WHERE District = 'Zhejiang' AND ( Population > 10000 AND Population in set (0.00 sec) //雖然這個例子看起來沒必要使用（）吧....干笑....
今天突然翻文檔發(fā)現(xiàn)一個東西，AND的優(yōu)先級要比OR優(yōu)先級高，所以，看如下例子：
查詢浙江或北京人口大于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進(jìn)行運算，而這條數(shù)據(jù)正好地區(qū)是北京。
//所以想要的正確篩選數(shù)據(jù)，需要加一個括號
6.范圍檢測BETWEEN AND
一個值滿足一段連續(xù)的范圍時為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 ‘a(chǎn)bc’ AND ‘efg’ 作為條件篩選了一下，竟然能篩選出900多行數(shù)據(jù)，不知道什么原理。
好像只匹配了第一個字符的ASCII值，這個東西實在想不明白有什么場景會把字符用上….
BETWEEN 1 AND 10 相當(dāng)于 >=1 AND
7.離散范圍檢測IN
當(dāng)一個值屬于一段離散數(shù)據(jù)之中時為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
將其原本的布爾值進(jìn)行邏輯非操作后再判斷
比如：查詢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; //與以上結(jié)果相同，函數(shù)在下面的文章總結(jié)。
分享標(biāo)題：詳解Mariadb單表查詢
本文路徑：http://fisionsoft.com.cn/article/djsdejs.html
