標籤：

SQL 常用命令及練習--之一

05-24
SQL 常用命令及練習--之一

  1. SQL是什麼

SQL是結構化查詢語言，可以訪問和處理資料庫

可以對資料庫進行查詢、取回、插入、更新、刪除、創建新表、存儲過程、視圖、設置許可權

RMDBS是關係型資料庫管理系統

表是相關的數據項的集合，它由列和行組成

2. SQL語法(入門)

mysql> use RUNOOB;//選擇資料庫Database changedmysql> set names utf8;//設置使用的字符集Query OK, 0 rows affected (0.00 sec)mysql> SELECT * FROM Websites;//讀取數據表的信息+----+--------------+---------------------------+-------+---------+| id | name | url | alexa | country |+----+--------------+---------------------------+-------+---------+| 1 | Google | https://www.google.cm/ | 1 | USA || 2 | 淘寶 | https://www.taobao.com/ | 13 | CN || 3 | 菜鳥教程 | http://www.runoob.com/ | 4689 | CN || 4 | 微博 | http://weibo.com/ | 20 | CN || 5 | Facebook | https://www.facebook.com/ | 3 | USA |+----+--------------+---------------------------+-------+---------+5 rows in set (0.01 sec)

SQL SELECT 語句

SELECT 語句用於從資料庫中選取數據。

結果被存儲在一個結果表中，稱為結果集。

SQL SELECT 語法

SELECT column_name,column_nameFROM table_name;SELECT * FROM table_name;eg.SELECT name,country FROM Websites;//選擇name、country列SELECT * FROM Websites;

SQL SELECT DISTINCT 語句

在表中，一個列可能會包含多個重複值，有時您也許希望僅僅列出不同（distinct）的值。

DISTINCT 關鍵詞用於返回唯一不同的值。

SQL SELECT DISTINCT 語法

SELECT DISTINCT column_name,column_nameFROM table_name;eg.SELECT DISTINCT country FROM Websites;//從 "Websites" 表的 "country" 列中選取唯一不同的值，也就是去掉 "country" 列重複值

SQL WHERE 子句

WHERE 子句用於提取那些滿足指定標準的記錄。

SQL WHERE 語法

SELECT column_name,column_nameFROM table_nameWHERE column_name operator value;eg.SELECT * FROM Websites WHERE country=CN;//文本欄位用引號SELECT * FROM Websites WHERE id=1;//數值欄位不用引號SELECT name, population FROM world WHERE name IN (Luxembourg, Mauritius, Samoa);SELECT name, area FROM world WHERE area BETWEEN 250000 AND 300000

WHERE 子句中的運算符

=, <>, > ,< ,>= ,<=, BETWEEN, LIKE, IN

SQL AND & OR 運算符

如果第一個條件和第二個條件都成立，則 AND 運算符顯示一條記錄。

如果第一個條件和第二個條件中只要有一個成立，則 OR 運算符顯示一條記錄。

SELECT * FROM WebsitesWHERE country=CNAND alexa > 50;//選擇CN為country的alexa大於50的列SELECT * FROM WebsitesWHERE country=USAOR country=CN;SELECT * FROM WebsitesWHERE alexa > 15AND (country=CN OR country=USA);

SQL ORDER BY 關鍵字

ORDER BY 關鍵字用於對結果集按照一個列或者多個列進行排序。

ORDER BY 關鍵字默認按照升序對記錄進行排序。如果需要按照降序對記錄進行排序，您可以使用 DESC 關鍵字。

SQL ORDER BY 語法

SELECT column_name,column_nameFROM table_nameORDER BY column_name,column_name ASC|DESC;SELECT * FROM WebsitesORDER BY alexa;//按照alexa列升序排列SELECT * FROM WebsitesORDER BY alexa DESC;//按照alexa列降序排列SELECTSE * FROM WebsitesORDER BY country,alexa;//先按照第一個column name排序，再按照第二個column name排序

SQL INSERT INTO 語句

INSERT INTO 語句用於向表中插入新記錄。

SQL INSERT INTO 語法

INSERT INTO 語句可以有兩種編寫形式。

第一種形式無需指定要插入數據的列名，只需提供被插入的值即可：

INSERT INTO table_nameVALUES (value1,value2,value3,...);INSERT INTO Websites (name, url, alexa, country)VALUES (百度,https://www.baidu.com/,4,CN);

第二種形式需要指定列名及被插入的值：

INSERT INTO table_name (column1,column2,column3,...)VALUES (value1,value2,value3,...);INSERT INTO Websites (name, url, country)VALUES (stackoverflow, http://stackoverflow.com/, IND);//alexa未指定

SQL UPDATE 語句

UPDATE 語句用於更新表中已存在的記錄。

SQL UPDATE 語法

UPDATE table_nameSET column1=value1,column2=value2,...WHERE some_column=some_value;eg.UPDATE Websites SET alexa=5000, country=USA WHERE name=菜鳥教程;

SQL DELETE 語句

DELETE 語句用於刪除表中的行。

SQL DELETE 語法

DELETE FROM table_nameWHERE some_column=some_value;eg.DELETE FROM WebsitesWHERE name=百度 AND country=CN;

3. 練習

3.1 basic

SELECT basics/zh?

sqlzoo.net

a. 這個例子顯示』France法國』的人口。字串應該在單引號中。

修改此例子,以顯示德國 Germany 的人口。

SELECT population FROM world WHERE name = Germany

b. 查詢顯示面積為 5,000,000 以上平方公里的國家,該國家的人口密度(population/area)。人口密度並不是 WORLD 表格中的欄,但我們可用公式(population/area)計算出來。

修改此例子,查詢面積為 5,000,000 以上平方公里的國家,對每個國家顯示她的名字和人均國內生產總值(gdp/population)

SELECT name, gdp/population FROM world WHERE area > 5000000

c. 檢查列表:單詞「IN」可以讓我們檢查一個項目是否在列表中。

此示例顯示了「Luxembourg 盧森堡」,「Mauritius 模里西斯」和「Samoa 薩摩亞」的國家名稱和人口。

顯示「Ireland 愛爾蘭」,「Iceland 冰島」,「Denmark 丹麥」的國家名稱和人口。

SELECT name, population FROM world WHERE name IN (Ireland, Iceland, Denmark);

d. 哪些國家是不是太小,又不是太大?

BETWEEN 允許範圍檢查 - 注意,這是包含性的。 此例子顯示面積為 250,000 及 300,000 之間的國家名稱和該國面積。

修改此例子,以顯示面積為 200,000 及 250,000 之間的國家名稱和該國面積。

SELECT name, area FROM world WHERE area BETWEEN 200000 AND 250000

e. 你可以用WHERE name LIKE B%來找出以 B 為開首的國家。

%是萬用字元,可以用代表任何字完。

SELECT name FROM world WHERE name LIKE Y%

SELECT names/zh - SQLZOO?

sqlzoo.net

f. 找出以 Y 為結尾的國家。

SELECT name FROM world WHERE name LIKE %Y

g. 「Luxembourg 盧森堡」中有一個x字母,還有一個國家的名字中有x。列出這兩個國家。

找出所有國家,其名字包括字母x。

SELECT name FROM world WHERE name LIKE %x%

h. 「Iceland 冰島」和「Switzerland 瑞士」的名字都是以」land」作結束的。還有其他嗎?

找出所有國家,其名字以 land 作結尾。

SELECT name FROM world WHERE name LIKE %land

i. 「Columbia 哥倫比亞」是以 C 作開始,ia 作結尾的。還有兩個國家相同。

找出所有國家,其名字以 C 作開始,ia 作結尾。

SELECT name FROM world WHERE name LIKE C% and name like %ia

j. 「Greece 希臘」中有雙 e 字。哪個國家有雙 o 字呢?

找出所有國家,其名字包括字母oo。

SELECT name FROM world WHERE name LIKE %oo%

l. 「Bahamas 巴哈馬」中有三個 a,還有嗎?

找出所有國家,其名字包括三個或以上的a。

m. 「India 印度」和」Angola 安哥拉」的第二個字母都是 n。

你可以用底線符_當作單一個字母的萬用字元。

SELECT name FROM world WHERE name LIKE _t%ORDER BY name

n. 「Lesotho 賴索托」和」Moldova 摩爾多瓦」都有兩個字母 o,被另外兩個字母相隔著。

找出所有國家,其名字都有兩個字母 o,被另外兩個字母相隔著。

SELECT name FROM world WHERE name LIKE %o__o%

o. 「Cuba古巴」和」Togo 多哥」都是 4 個字母。

找出所有國家,其名字都是 4 個字母的。

SELECT name FROM world WHERE name LIKE ____

3.2 country

SQLZOO:SELECT from WORLD Tutorial/zh?

sqlzoo.net

a.顯示具有至少2億人口的國家名稱。 2億是200000000，有八個零。

SELECT name FROM world WHERE population>200000000

b. 找出有至少200百萬(2億)人口的國家名稱，及人均國內生產總值。

select name, gdp/population from world where population > 200000000

c. 顯示South America南美洲大陸的國家名字和以百萬為單位人口數。 將人口population 除以一百萬(1000000)得可得到以百萬為單位人口數。

select name, population/1000000 from world where continent=South America

d. 顯示法國，德國，義大利(France, Germany, Italy)的國家名稱和人口。

select name, population from world where name in (France, Germany, Italy)

e. 顯示包含單詞「United」為名稱的國家。

select name from world where name like %United%

f. 成為大國的兩種方式：如果它有3百萬平方公里以上的面積，或擁有250百萬(2.5億)以上人口。

展示大國的名稱，人口和面積。

select name, population, area from world where area > 3000000 or population > 250000000

g. 美國、印度和中國(USA, India, China)是人口又大，同時面積又大的國家。排除這些國家。

顯示以人口或面積為大國的國家，但不能同時兩者。顯示國家名稱，人口和面積。

select name,population,area from world where (area > 3000000 and population < 250000000) or (area < 3000000 and population > 250000000)

h. 除以為1000000（6個零）是以百萬計。除以1000000000（9個零）是以十億計。使用 ROUND 函數來顯示的數值到小數點後兩位。

對於南美顯示以百萬計人口，以十億計2位小數GDP。

select name, ROUND(population/1000000,2), ROUND(gdp/1000000000,2) from worldwhere continent=South America

i. 顯示國家有至少一個萬億元國內生產總值（萬億，也就是12個零）的人均國內生產總值。四捨五入這個值到最接近1000。

顯示萬億元國家的人均國內生產總值，四捨五入到最近的$ 1000

select name, ROUND(gdp/population,-3) from world where gdp > 1000000000000

j. The CASE statement shown is used to substitute North America for Caribbean in the third column.

Show the name - but substitute Australasia for Oceania - for countries beginning with N.

???

k. Show the name and the continent - but substituteEurasiafor Europe and Asia; substituteAmerica- for each country inNorth AmericaorSouth AmericaorCaribbean. Show countries beginning with A or B

3.3 nobel prize

SELECT from Nobel Tutorial?

sqlzoo.net

nobel(yr, subject, winner)

a. 顯示誰贏得了1962年文學獎(Literature)。

SELECT winner FROM nobel WHERE yr = 1962 AND subject = Literature

b. 顯示「愛因斯坦」(Albert Einstein) 的獲獎年份和獎項。

select yr, subject from nobel where winner=Albert Einstein

c. 顯示2000年及以後的和平獎(『Peace』)得獎者。

select winner from nobel where yr>=2000 and subject=Peace

d. 顯示1980年至1989年(包含首尾)的文學獎(Literature)獲獎者所有細節（年，主題，獲獎者）。

select * from nobel where (yr>=1980 and yr<=1989) and subject=Literature

e. 顯示總統獲勝者的所有細節：

  • 西奧多?羅斯福 Theodore Roosevelt
  • 伍德羅?威爾遜 Woodrow Wilson
  • 吉米?卡特 Jimmy Carter

SELECT * FROM nobel WHERE winner IN (Theodore Roosevelt, Woodrow Wilson, Jimmy Carter)

f. 顯示名字為John 的得獎者。 (注意:外國人名字(First name)在前，姓氏(Last name)在後)

select winner from nobel where winner like John%

g. 顯示1980年物理學(physics)獲獎者，及1984年化學獎(chemistry)獲得者。

select * from nobel where (yr=1980 and subject=physics) or (yr=1984 and subject=chemistry)

h. 查看1980年獲獎者，但不包括化學獎(Chemistry)和醫學獎(Medicine)。

select * from nobel where yr=1980 and subject not in (Chemistry,Medicine)

i.顯示早期的醫學獎(Medicine)得獎者（1910之前，不包括1910），及近年文學獎(Literature)得獎者（2004年以後，包括2004年）。

select * from nobel where (subject=Medicine and yr < 1910) or (subject=Literature and yr>=2004)

j. Find all details of the prize won by PETER GRüNBERG

select * from nobel where winner=PETER GRüNBERG

k. 查找尤金?奧尼爾EUGENE ONEILL得獎的所有細節 Find all details of the prize won by EUGENE ONEILL

select * from nobel where winner=EUGENE ONEILL

l. 騎士列隊 Knights in order

列出爵士的獲獎者、年份、獎頁(爵士的名字以Sir開始)。先顯示最新獲獎者，然後同年再按名稱順序排列。

select winner, yr, subject from nobel where winner like Sir% order by yr desc, winner

m. The expression subject IN (Chemistry,Physics)can be used as a value - it will be 0 or 1.

Show the 1984 winners and subject ordered by subject and winner name; but list Chemistry and Physics last.

???

3.4

SELECT within SELECT Tutorial/zh?

sqlzoo.net

a. 列出每個國家的名字name，當中人口population是高於俄羅斯Russia的人口。

SELECT name FROM world WHERE population > (SELECT population FROM world WHERE name=Russia)

b. 列出歐州每國家的人均GDP，當中人均GDP要高於英國United Kingdom的數值。

select gdp/population from world where continent=Europe and gdp/population > (select gdp/population from world where name=United Kingdom)

c. 在阿根廷Argentina及 澳大利亞Australia所在的洲份中，列出當中的國家名字name及洲分continent。按國字名字順序排序

select name,continent from world where continent in (select continent from world where name in (Argentina,Australia)) order by name

d. 哪一個國家的人口比加拿大Canada的多，但比波蘭Poland的少?列出國家名字name和人口population 。

select name, population from world where population > (select population from world where name=Canada) and population < (select population from world where name=Poland)

e. Germany德國（人口8000萬），在Europe歐洲國家的人口最多。Austria奧地利（人口850萬）擁有德國總人口的11％。

顯示歐洲的國家名稱name和每個國家的人口population。以德國的人口的百分比作人口顯示。

select name, round(population/(select population from world where name=Germany),2) from world where continent=Europe

f. 哪些國家的GDP比Europe歐洲的全部國家都要高呢? [只需列出name。] (有些國家的記錄中，GDP是NULL，沒有填入資料的。)

select name from world where gdp > all(select gdp from world where continent=Europe and gdp>0)

g. 在每一個州中找出最大面積的國家，列出洲份continent, 國家名字name及面積area。 (有些國家的記錄中，AREA是NULL，沒有填入資料的。)

SELECT continent, name, area FROM world x WHERE area >= ALL (SELECT area FROM world y WHERE y.continent=x.continent AND area>0)

推薦閱讀：

※MYSQL的用戶變數和系統變數
※aws aurora論文觀後
※線上的資料庫發現索引不合理怎麼處理？
※MySQL訓練——JOIN@sqlzoo.net
※8.11 優化器鎖操作

TAG:MySQL | 資料庫 |