MySQL管理之SQL語句例項

MySQL管理之SQL語句例項

首先，我們來匯入world庫，這個world庫中的表是mysql ocp考試專用表，在網上有下

mysql> source/root/world_innodb.sql

表結構如下：本文來自http://yijiu.blog.51cto.com 轉載請經博主允許 ,盜帖可恥！

CREATE TABLE `Country` (
  `Code` char(3) NOT NULL DEFAULT '',
  `Name` char(52) NOT NULL DEFAULT '',
  `Continent` enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL DEFAULT 'Asia',
  `Region` char(26) NOT NULL DEFAULT '',
  `SurfaceArea` float(10,2) NOT NULL DEFAULT '0.00',
  `IndepYear` smallint(6) DEFAULT NULL,
  `Population` int(11) NOT NULL DEFAULT '0',
  `LifeExpectancy` float(3,1) DEFAULT NULL,
  `GNP` float(10,2) DEFAULT NULL,
  `GNPOld` float(10,2) DEFAULT NULL,
  `LocalName` char(45) NOT NULL DEFAULT '',
  `GovernmentForm` char(45) NOT NULL DEFAULT '',
  `HeadOfState` char(60) DEFAULT NULL,
  `Capital` int(11) DEFAULT NULL,
  `Code2` char(2) NOT NULL DEFAULT '',
  PRIMARY KEY (`Code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

使用show table status檢視錶狀態如下所示：

mysql> show tablestatus;

+-----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+

+-----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+

| City | InnoDB | 10 | Compact | 4321 | 94 | 409600 | 0 | 131072 | 0 | 4080 | 2014-10-02 15:35:18 |NULL | NULL | latin1_swedish_ci | NULL | | |

| Country | InnoDB | 10 | Compact | 241 | 407 | 98304 | 0 | 0 | 0 | NULL | 2014-10-02 15:35:18 |NULL | NULL | latin1_swedish_ci | NULL | | |

| CountryLanguage |InnoDB | 10 | Compact | 856 | 114 | 98304 | 0 | 65536 | 0 | NULL | 2014-10-02 15:35:18 |NULL | NULL | latin1_swedish_ci | NULL | | |

+-----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+

3 rows in set (0.00sec)

mysql> select *from Country where Name="China"\G

***************************1. row ***************************

Code: CHN

Name: China

Continent: Asia

Region: Eastern Asia

SurfaceArea: 9572900.00

IndepYear: -1523

Population: 1277558000

LifeExpectancy: 71.4

GNP: 982268.00

GNPOld: 917719.00

LocalName: Zhongquo

GovernmentForm:People'sRepublic

HeadOfState: Jiang Zemin

Capital: 1891

Code2: CN

1 row in set (0.00sec)

本文來自http://yijiu.blog.51cto.com 轉載請經博主允許 ,盜帖可恥！

由此可以看到此表與City表關聯，如下所示

因為city表存在城市ID編號，也就是剛才與Capital= 1891 相關的資訊

mysql> select *from City where id = 1891;

+------+--------+-------------+----------+------------+

+------+--------+-------------+----------+------------+

| 1891 | Peking | CHN | Peking | 7472000 |

+------+--------+-------------+----------+------------+

1 row in set (0.00sec)

而CountryLanguage表裡是將CountryCode做關聯

看到編號為CHN，那查一下關於CHN相關的資訊

可看到CountryLanguage用到以CHN作為關聯可以查到相關城市

mysql> select *from CountryLanguage where CountryCode = 'CHN';

+-------------+-----------+------------+------------+

+-------------+-----------+------------+------------+

| CHN | Chinese | T | 92.0 |

| CHN | Dong | F | 0.2 |

| CHN | Hui | F | 0.8 |

| CHN | Mantu | F | 0.9 |

| CHN | Miao |F | 0.7 |

| CHN | Mongolian | F | 0.4 |

| CHN | Puyi | F | 0.2 |

| CHN | Tibetan | F | 0.4 |

| CHN | Tujia | F | 0.5 |

| CHN | Uighur | F | 0.6 |

| CHN | Yi | F | 0.6 |

| CHN | Zhuang | F | 1.4 |

+-------------+-----------+------------+------------+

12 rows in set (0.00sec)

接下來就進入主題

查詢語句初識本文來自http://yijiu.blog.51cto.com 轉載請經博主允許 ,盜帖可恥！

來個例子先，查詢City表中的前10行

mysql> select Id,Name, Population From City limit 10;

+----+----------------+------------+

| Id | Name | Population |

+----+----------------+------------+

| 1 | Kabul | 1780000 |

| 2 | Qandahar | 237500 |

| 3 | Herat | 186800 |

| 4 | Mazar-e-Sharif | 127800 |

| 5 | Amsterdam | 731200 |

| 6 | Rotterdam | 593321 |

| 7 | Haag | 440900 |

| 8 | Utrecht | 234323 |

| 9 | Eindhoven | 201843 |

| 10 | Tilburg | 193238 |

+----+----------------+------------+

10 rows in set (0.00sec)

查詢從第10行到20行,中間相差10行

limit 10,10;意思為從第幾行開始並從這行開始向下顯示多少行

mysql> select Id,Name, Population From City limit 10,10;

+----+-------------------+------------+

| Id | Name | Population |

+----+-------------------+------------+

| 11 | Groningen | 172701 |

| 12 | Breda | 160398 |

| 13 | Apeldoorn | 153491 |

| 14 | Nijmegen | 152463 |

| 15 | Enschede | 149544 |

| 16 | Haarlem | 148772 |

| 17 | Almere | 142465 |

| 18 | Arnhem | 138020 |

| 19 | Zaanstad | 135621 |

| 20 |s-Hertogenbosch | 129170 |

+----+-------------------+------------+

10 rows in set (0.00sec)

錯誤的sql：如下所示

select * from tb where xxxx limit 537793977, 20;

如果出現這樣的sql，意味著先要掃描表裡面的537793977行後再取20行返回，這樣成本就會很高

LIMIT的一個原則：

在生產環境中使用LIMIT後只跟一個數，而且最好不大於500，如果是連續的，包括上面的sql，利用上面的SQL得到一個ID的最大值，那麼這時候我們就會用到份頁

如下所示：

優化前：

mysql> select Id,Name, Population From City limit 10;

+----+----------------+------------+

| Id | Name | Population |

+----+----------------+------------+

| 1 | Kabul | 1780000 |

| 2 | Qandahar | 237500 |

| 3 | Herat | 186800 |

| 4 | Mazar-e-Sharif | 127800 |

| 5 | Amsterdam | 731200 |

| 6 | Rotterdam | 593321 |

| 7 | Haag | 440900 |

| 8 | Utrecht | 234323 |

| 9 | Eindhoven | 201843 |

| 10 | Tilburg | 193238 |

+----+----------------+------------+

10 rows in set (0.00sec)

優化後如下：

使用last_max_id通過程式進行計算得到的

語法：

mysql>select Id, Name, Population From City where id ><last_max_id> limit 10;

mysql> select Id,Name, Population From City where id >10 limit 10;

+----+-------------------+------------+

| Id | Name | Population |

+----+-------------------+------------+

| 11 | Groningen | 172701 |

| 12 | Breda | 160398 |

| 13 | Apeldoorn | 153491 |

| 14 | Nijmegen | 152463 |

| 15 | Enschede | 149544 |

| 16 | Haarlem | 148772 |

| 17 | Almere | 142465 |

| 18 | Arnhem | 138020 |

| 19 | Zaanstad | 135621 |

| 20 |s-Hertogenbosch | 129170 |

+----+-------------------+------------+

10 rows in set (0.00sec)

本文來自http://yijiu.blog.51cto.com 轉載請經博主允許 ,盜帖可恥！

引數解釋：

where id> 10 limit 10 就是取前10行到第20行

如果取前10的話，將資料放進去，得到的就是last_max_id然後傳到下一頁，進行對比

這就是所謂的分頁

#這並不是完全在sql裡實現的，如果想特別精準的分頁，這種訪問有可能有問題的。

#跳頁的話有種方法：一般粗略的估計一下即可，不會讓其非常精準的顯示出來，只求速度足夠快

比如跳頁，如果對於整個系統進行搜尋的話，非查不可的情況，我們可以使用專屬的搜尋系統進行檢視，網際網路領域中能不進行寫則不寫，以提高速度

count, max(), min()使用

count

count(*)在早版本會走主鍵的，最新版本會走普通索引

mysql> selectcount(*), count(id) from City;

+----------+-----------+

| count(*) | count(id)|

+----------+-----------+

| 4079 | 4079 |

+----------+-----------+

1 row in set (0.00sec)

那麼問題來了：主建為何沒有第二索引快？

因為Innodb主建就表本身裡的資料，如果count主建，需要將整個表掃描一遍，這樣建帶資料讀的塊更大

走主建相當於把整個表都要讀（全表掃描）本文來自http://yijiu.blog.51cto.com 轉載請經博主允許 ,盜帖可恥！

而在Innodb裡是索引列+主建值儲存的結構體系，這麼做的話會更快

如果直接使用Secondary index的話會快一點

如今mysql對count(*)做了優化，預設會走Secondary index，所以在以後計算總數的時候，不要總計算列數，直接寫count(*)就可以了

列出人數最多的城市

我們現在有需求，我們知道City表中有城市的總人數，我們現在想統計一下最多人數的城市

那麼我們先來看一下表結構本文來自http://yijiu.blog.51cto.com 轉載請經博主允許 ,盜帖可恥！

mysql> desc City ;

+-------------+----------+------+-----+---------+----------------+

+-------------+----------+------+-----+---------+----------------+

| ID | int(11) | NO | PRI | NULL | auto_increment |

| CountryCode |char(3) | NO | MUL | | |

| Population | int(11) | NO | | 0 | |

+-------------+----------+------+-----+---------+----------------+

5 rows in set (0.00sec)

由此我們可知條件，Population為每個城市的總人數，我們只要篩出Population最大的值即可，如下所示：

mysql> select * from City where Population = (selectmax(Population) from City);

+------+-----------------+-------------+-------------+------------+

+------+-----------------+-------------+-------------+------------+

| 1024 | Mumbai(Bombay) | IND | Maharashtra| 10500000 |

+------+-----------------+-------------+-------------+------------+

1 row in set (0.00sec)

括號中內的內容為子句查詢；

而select max(Population) from City 表示查詢這個表中人數最多的行

先來看一個例子，執行上面的sql子句：

mysql> selectmax(Population) from City;

+-----------------+

| max(Population) |

+-----------------+

| 10500000 |

+-----------------+

1 row in set (0.00sec)

max是內建函式，表示將取最大數值的行

那麼將其封裝到子句裡面，再進行對比，即 Population= 最大的欄位

找到城市人數最少的城市

mysql> select *from City where Population = (select min(Population) from City);

+------+-----------+-------------+----------+------------+

+------+-----------+-------------+----------+------------+

| 2912 | Adamstown |PCN | – | 42 |

+------+-----------+-------------+----------+------------+

1 row in set (0.01sec)

使用oder by進行排序

mysql> select *from City order by Population desc limit1;

+------+-----------------+-------------+-------------+------------+

+------+-----------------+-------------+-------------+------------+

| 1024 | Mumbai(Bombay) | IND | Maharashtra| 10500000 |

+------+-----------------+-------------+-------------+------------+

1 row in set (0.00sec)

本文來自http://yijiu.blog.51cto.com 轉載請經博主允許 ,盜帖可恥！

查詢大於人員數大於100W的城市有有哪些

使用count(*) 統計出總數

mysql> selectcount(*) from City where Population > 1000000;

+----------+

| count(*) |

+----------+

| 237 |

+----------+

1 row in set (0.00sec)

可看到一共有237個城市

那麼再來查詢人口大於100W的城市是否一共有237個

mysql> select *from City where Population >1000000;

#拉到最後可看到如下的數值

+------+--------------------------+-------------+----------------------+------------+

237 rows in set (0.00sec)

我們還可以使用函式，如果什麼引數都沒有加的情況下就使用以下函式，會得到上一個sql的執行所得到的行數

mysql> selectfound_rows();

+--------------+

| found_rows() |

+--------------+

| 237 |

+--------------+

1 row in set (0.00sec)

本文來自http://yijiu.blog.51cto.com 轉載請經博主允許 ,盜帖可恥！

那麼使用count(*)並執行函式來檢視效果又會是什麼樣

mysql> selectcount(*) from City where Population > 1000000;

+----------+

| count(*) |

+----------+

| 237 |

+----------+

1 row in set (0.00sec)

mysql> selectfound_rows();

+--------------+

| found_rows() |

+--------------+

| 1 |

+--------------+

1 row in set (0.00sec)

FOUND_ROWS函式

比如取前10行，但表中總共有多少行是未知的，這裡在比其他資料中多了一個函式:

SQL_CALC_FOUND_ROWS

先來看一下yw表有多少行

mysql> selectcount(*) from yw;

+----------+

| count(*) |

+----------+

| 6000000 |

+----------+

1 row in set (15.64sec)

再執行列印前10行內容

mysql> select SQL_CALC_FOUND_ROWS * fromCity limit 10;

ERROR 1146 (42S02):Table 'test1.City' doesn't exist

mysql> select SQL_CALC_FOUND_ROWS* from yw limit 10;

+----+---------+---------+---------+---------+---------------------+------------------------------------------------------+

| id | c1 | c2 | c3 | c4 | c5 | c6 |

+----+---------+---------+---------+---------+---------------------+------------------------------------------------------+

| 1 | 463681 | 1098981 | 1817518 | 2222359 | 2014-09-24 15:38:29 |wubxwubxwubxwubxwubxwubxwubxwubxwubxwubxwubxwubx |

| 2 | 2333997 | 269341 | 2459005 | 915557 |2014-09-24 15:38:29 | wubxwubxwubx |

| 3 | 2971523 | 1226698 | 842469 | 414525 | 2014-09-24 15:38:29 | wubxwubxwubxwubxwubxwubxwubxwubxwubx |

| 4 | 2835700 | 930937 | 2835332 | 1945110 | 2014-09-24 15:38:29 | wubx |

| 5 | 1578655 | 1044887 | 2649255 | 2307696 |2014-09-24 15:38:29 | wubxwubxwubxwubxwubxwubxwubx |

| 6 | 1442242 | 992011 | 1740281 | 190626 |2014-09-24 15:38:29 | wubxwubxwubxwubxwubxwubxwubxwubxwubxwubxwubxwubxwubx |

| 7 | 693798 | 309586 | 753637 | 2403923 | 2014-09-24 15:38:29 |wubxwubxwubxwubxwubxwubxwubxwubxwubxwubx |

| 8 | 888272 | 2581335 | 1547343 | 1465295 | 2014-09-24 15:38:29 |wubxwubxwubxwubx |

| 9 | 1608599 | 240304 | 2475805 | 2157717 | 2014-09-24 15:38:29 | wubxwubxwubxwubx |

| 10 | 2833881 | 185188 | 1736996 | 565924 | 2014-09-24 15:38:29 |wubxwubxwubxwubxwubxwubxwubxwubxwubxwubx |

+----+---------+---------+---------+---------+---------------------+------------------------------------------------------+

10 rows in set (17.88sec)

mysql> selectfound_rows();

+--------------+

| found_rows() |

+--------------+

| 6000000 |

+--------------+

1 row in set (0.00sec)

6000000就是表的行數

如果併發比較大的情況下，這個函式用的還是比較多的，但是一般不會直接一個表就直接使用found_rows,一般都是跟where條件，跟上條件並計算此型別的條目數，一般此類場景中比較常見

主要就是用SQL_CALC_FOUND_ROWS來減少操作為主要目的

模糊匹配

使用like

語法：like "Wu%" #不能以百分號開頭，如果使用%開頭，則不會用到索引

查詢以wu開頭的城市

mysql> select *from City where Name like 'Wu%';

+------+-----------+-------------+---------------------+------------+

+------+-----------+-------------+---------------------+------------+

| 1894 | Wuhan | CHN | Hubei | 4344600 |

| 1929 | Wuxi | CHN | Jiangsu | 830000 |

| 1964 | Wuhu | CHN | Anhui | 425740 |

| 2022 | Wuhai | CHN | Inner Mongolia | 264081 |

| 2058 | Wuzhou | CHN | Guangxi | 210452 |

| 2163 | Wuwei | CHN | Gansu | 133101 |

| 3084 | Wuppertal |DEU | Nordrhein-Westfalen | 368993 |

+------+-----------+-------------+---------------------+------------+

7 rows in set (0.01sec)

不用過多做解釋了

使用sum統計其列總數

sum也是mysql內建函式，用法都是一樣的，如下所示：

我們來統計中國幾個重要城市的人口總數

mysql> selectsum(Population) from City;

+-----------------+

| sum(Population) |

+-----------------+

| 1429559884 |

+-----------------+

1 row in set (0.00sec)

mysql> selectsum(Population) from City where CountryCode='CHN';

+-----------------+

| sum(Population) |

+-----------------+

| 175953614 |

+-----------------+

1 row in set (0.00sec)

或只統計上海和天津的總人數

mysql> selectsum(Population) from City where CountryCode='CHN' and Name='TianJin' orName='ShangHai';

+-----------------+

| sum(Population) |

+-----------------+

| 14983100 |

+-----------------+

1 row in set (0.00sec)

使用表關聯，檢視出每個國家的首都

剛才我們直接使用了sum查到了首都的詳細資訊，如果使用表關聯使用這兩個表來查我們首都詳細資訊的話，該如何去查

因為我們知道Code:CHN，那麼我們使用Capital欄位與city表的id進行關聯：

先來檢視錶內容：

mysql> select * from Country limit 1;

+------+-------+---------------+-----------+-------------+-----------+------------+----------------+--------+--------+-----------+----------------------------------------------+-------------+---------+-------+

+------+-------+---------------+-----------+-------------+-----------+------------+----------------+--------+--------+-----------+----------------------------------------------+-------------+---------+-------+

| ABW | Aruba | North America | Caribbean | 193.00 | NULL | 103000 | 78.4 | 828.00 |793.00 | Aruba | NonmetropolitanTerritory of The Netherlands | Beatrix | 129 |AW |

+------+-------+---------------+-----------+-------------+-----------+------------+----------------+--------+--------+-----------+----------------------------------------------+-------------+---------+-------+

1 row in set (0.00 sec)

如上可以看到，Country表的Capital是指向國家首都城市的id，所以利用關聯可以得到

可以使用Country表的Capital和City表id關聯，並加上限制Country.Code='CHN';

mysql> selecta.name,b.name from Country a , City b where a.Code="CHN" and a.Capital=b.id;

+-------+--------+

| name | name |

+-------+--------+

| China | Peking |

+-------+--------+

1 row in set (0.00sec)

語法解釋：

語法可分為兩截：

select a.name,b.namefrom Country a , City b ; 這是一段

where a.Code="CHN" and a.Capital=b.id; 這又是一段

先來執行第一段

mysql> selecta.name,b.name from Country a , City b limit 1;

+-------+-------+

| name | name |

+-------+-------+

| Aruba | Kabul |

+-------+-------+

1 row in set (0.00sec)

可以看到，每個國家的名稱以及國家的首都都呈現出來

那麼我們想針對某個國家檢視其首都的話肯定需要加where條件進行匹配了

where的同時，首先需要統計國家的程式碼號，中國無非是CHN，但是隻是篩選出了CHN的城市，並沒有曬出其首都，如下所示

mysql> selecta.name,b.name from Country a ,City b where a.Code='CHN' limit 10;

+-------+----------------+

| name | name |

+-------+----------------+

| China | Kabul |

| China |Qandahar |

| China | Herat |

| China |Mazar-e-Sharif |

| China |Amsterdam |

| China |Rotterdam |

| China | Haag |

| China |Utrecht |

| China | Eindhoven |

| China |Tilburg |

+-------+----------------+

10 rows in set (0.00sec)

我們還需要對其做條件匹配，我們看到，Country表中有Capital的欄位，沒錯這是國家首都編號，而City中也存在城市的ID，那麼這兩個欄位是相等的，所以我們只要將Counry表中的Capital和City表中的城市ID匹配上即可，如下所示：

mysql> selecta.name,b.name from Country a , City b where a.Code='CHN' and a.Capital=b.id;

+-------+--------+

| name | name |

+-------+--------+

| China | Peking |

+-------+--------+

1 row in set (0.00sec)

country為為別名a

city的別名為b

並使用where進行別名a

也就是country表中的code進行匹配

如果某行等於CHN，並且與別名b表中，也就是city表中的id匹配，則列印

如果想看每個國家的首都在哪裡，則如下：

mysql> selecta.name,b.name from Country a , City b where a.Capital=b.id limit 10;

+----------------------+------------------+

| name | name |

+----------------------+------------------+

| Aruba | Oranjestad |

| Afghanistan | Kabul |

| Angola | Luanda |

| Anguilla | The Valley |

| Albania | Tirana |

| Andorra | Andorra la Vella |

| NetherlandsAntilles | Willemstad |

| United ArabEmirates | Abu Dhabi |

| Argentina | Buenos Aires |

| Armenia | Yerevan |

+----------------------+------------------+

10 rows in set (0.00sec)

將其他表中的資料匯入到當前表

建立一個測試表及相關操作

mysql> createtable test5 like City;

Query OK, 0 rowsaffected (0.02 sec)

從其他表往新表中匯入記錄

mysql> insert intotest5 select * from City limit 100;

Query OK, 100 rowsaffected (0.00 sec)

Records: 100 Duplicates: 0 Warnings: 0

當線上上環境，想調整索引，但是原表確大，不容易操作時，可以上面的方法建一個測試表，測試完後再刪掉就可以了

使用update更新表資料

使用update將北京改名

mysql> updatetest5 set Name='Peking', District='Peking' where id=1;

Query OK, 1 rowaffected (0.02 sec)

Rows matched: 1 Changed: 1 Warnings: 0

如果需要更新多個列的話，必須以逗號間隔開來

UPDATE重大標識：

如果update沒有加條件等於全表更新,所以執行update的時候一定要加where條件

或者把事務開起來，或是把自動提交關了

begin;
update test5 set Name='Peking', District='aaa' where id=1;
select * from test5 where id=1; #確認一下
rollback; or commit; #沒有問題再提交操作

delete 操作：
刪除新增的第一條記錄，也要考慮事務操作：

begin;
delete from wubx_1 where id=1;
select * from wubx_1 where id=1;
rollback |commit

以上，為MySQL的日常sql例項，感謝各位看官

MySQL管理之SQL語句例項

相關文章