基於mysql的資料庫基本操作(含初始數據寫入sequel pro步驟)

05-01

本文為新手入門mysql的筆記，比較基礎，大神請繞道；
本文沒有按照之前的風格梳理，而是以注釋的形式寫在代碼塊里，請直接看代碼塊；
閱讀本文大約需要15分鐘，謝謝。

SQLZOO網站練習部分

一，查詢select、條件查詢（where子句）

資料庫如下：

查詢人口大於200萬的大國家：

SELECT name FROM worldWHERE population >= 200000000;

and:查詢人口大於200萬的南美洲國家：

SELECT name FROM worldWHERE population >= 200000000 and continent=South America;

or: 查詢人口大於2億或者面積大於300萬的國家

select name, population,area from worldwhere area >= 3000000 or population >= 200000000

查詢人口大於200萬的南美洲國家名稱、gdp、人均gdp：

SELECT name ,GDP,GDP/population FROM worldWHERE population >= 200000000 and continent=South America

查詢France, Germany,Italy的人口population：

select name , population from worldwhere name in (France, Germany,Italy)

查詢人口大於200萬的南美洲、亞洲國家的名稱、gdp、人均gdp：

SELECT name ,GDP,GDP/population FROM worldWHERE population >= 200000000 and continent IN (South America ,Asia)

查詢面積大於300百萬或者人口大於2.5億的國家，二者只能符合一個

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

查詢名字中含有United的國家：

select name from worldwhere name like %United%

like的用法中，

%United%的前後『不可少
%United%表示，只要名字中有United都找出來
%United表示，名字以United結尾
United％表示，名字以United開始

下圖表示了where語句中邏輯查找的語法：

Round：ROUND（f,p）將f舍入到小數點後的p位

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

round(population/1000000,2)返回的是：將population除1000000，並保留2為有效數字後的結果

注意：p可能為負值，

當p＝-1將舍入到最接近的10，

當p＝-2將舍入到最接近的100，

當p＝-3將入到最接近的1000,

如下例子：

select name ,Round(gdp/population, -3) from worldwhere gdp >= 1000000000000

上例返回的是，GDP大於1000000000000的國家的人均GDP，保留到千位的結果：

這部分我並沒有練習完，因為最近事情多，又有面試要準備的，所以就以《必知必會》這本書為主來練習，因為我覺得《必知必會》講的更透徹寫，網站練習是用來輔助的。

網站網址為：

SQLZOO?

zh.sqlzoo.net

二，《SQL必知必會》練習

《必知必會》這本書小巧精緻，很適合初學者入門用，也可帶在身上當工具書用。下面一步步展開。

下載書籍配套數據

配套數據點擊下面鏈接下載：

Sams Teach Yourself SQL in 10 Minutes - Fourth Edition?

www.forta.com

請注意下載與您使用的資料庫軟體一致的版本，我用的是mysql，下載的是一個壓縮包，解壓後是3個文件，分別如下：

將數據寫入到軟體中

我用的軟體是sequel pro，數據寫入後將是如下界面：

那麼如何寫入呢？我自己當初被這個問題搞的焦頭爛額，幾近放棄，最後解決了才恍然大悟！下面一步步展開。

前期首先應在電腦上裝好mysql，這個過程請自行百度教程，這裡不在詳述，千萬要注意的一點是：初始密碼一定要記住！然後通過電腦的命令窗登錄並修改密碼。

然後是裝編輯軟體，畢竟我們肯定不願意在系統提供的那個黑乎乎的界面上操作資料庫。我裝的是sequel pro，那麼首先應聯結資料庫，這時候就要用到資料庫密碼了，登錄進來後，新建一個資料庫，我是建的和書籍同名的資料庫，如上大圖。

有了資料庫，我們的表才有地方放，而有了表數據才能存儲的進來。所以接下來我們滑鼠點擊我們已經新建的資料庫，然後游標定位在『Query』窗口，如上圖。

接下來，我們把網站上下載好的文件解壓，進去後，看到有2個txt文件，分別為『Create』、Populate。現在重點來了：

這兩個是SQL命令，不是數據！

我們點開Create.txt，把裡面的內容全部複製，粘貼到上述大圖中的右上方空白處，然後點擊下圖中的右邊的藍色的位置：

就是讓軟體run all queries，運行所有的代碼，就會發現右邊已經新建了五個表！

神奇吧！哈哈????

接下來對Populate.txt也進行同樣的操作，這一步是把數據寫入到我們剛剛新建的5個表裡！

至此，我們完成了寫入，關鍵是要理解：

我們下載下來的只是新建和寫入數據的代碼，不能導入，只能運行後讓軟體生產表！

接下來，我么可以寫一個語句檢查下這幾個表到底有沒有數據：

select *from `Customers`

得到的結果是：

如上，寫入成功！

接下來就可以按照樹上的講解，逐個練習了，下面是我的聯繫代碼：

# not：否定跟在其後的條件，可用於剔除不要的數據,# 下例中求得是非供應商dll01生產的產品，也可用<>代替。/*select `prod_name`,`vend_id`from `Products`where not `vend_id` = dll01*/# 查詢多個關鍵字的時候，用in()更清晰，運算順序也更清晰/*select `prod_name`,`prod_price`,`vend_id`from `Products`where `vend_id` in (dll01,brs01) and `prod_price` >= 10;*/ # ()的運算優先順序比and和or都高，應養成使用()的習慣，沒有壞處。/*select `prod_name`,`prod_price`,`vend_id`from `Products`where (`vend_id` = dll01 or `vend_id` =brs01) and `prod_price` >= 10; */ # and和or聯合運用時，and的優先順序高，此時將出錯，應該用()，如上/*select `prod_name`,`prod_price`from `Products`where `vend_id`=dll01 or `vend_id`=brs01and `prod_price` >=10;*/ # in() 與or的作用一致，注意使用in必須要有()，且好處多多。/*select `prod_name`,`prod_price`,`vend_id`from `Products`where `vend_id` in( dll01 , brs01);*/ # or 檢索時只需符合一項即可/*select `prod_name`,`prod_price`,`vend_id`from `Products`where `vend_id` = dll01 or `vend_id` = brs01; */ # 並列檢索/*select `prod_id`,`prod_name`,`prod_price`,`vend_id`from `Products`where `vend_id` = dll01 and `prod_price` <= 4 ORDER BY `prod_name`*/ -- 排序# 查空值/*SELECT `cust_name` from `Customers`where `cust_email` is null; */# limit: 從第4行開始，共輸出5行/*SELECT `prod_name`,`prod_price`from `Products`limit 4,5*/# 通配符％，like的使用# xxx%：查找以xxx開頭的/*select `prod_id`, `prod_name`from `Products`where `prod_name` like fish%*/ # %xxx：查找以xxx結尾的/*select `prod_id`, `prod_name`from `Products`where `prod_name` like %toy*/# %xxx%：查找任意含xxx的行,只要有xxx，無論在什麼位置都返回該行/*select `prod_id`,`prod_name`from `Products`where `prod_name` like %bean%;*/# x%y：以x開頭，以y結尾的行/*select `prod_id`,`prod_name`from `Products`where `prod_name` like f%y*/ # 注意此時開頭和結尾是整個內容的開頭和結尾，而非其中某個詞# 許多字元後面含有空格，則不是以y結尾，就不會檢索出f開頭y結尾的結果，詳見49頁。# 通配符%有一個極好的用處，就是查郵箱的，如下/*select `cust_email`from `Customers`where `cust_email` like %@fun4all.com%*/# 通配符"_"，只匹配單個字元，對比下面兩例的區別 /*select `prod_id`,`prod_name`from `Products`where `prod_name` like _ inch teddy bear;*//*select `prod_id`,`prod_name`from `Products`where `prod_name` like __ inch teddy bear;*/# 注意通配符%的檢索結果/*select `prod_id`,`prod_name`from `Products`where `prod_name` like % inch teddy bear;*/# 注意：上述3個返回不同結果，％能匹配多個字元，_只能匹配一個字元# 1,不可過度使用通配符，能不用則不用；# 2,若用盡量放在結尾處，放在開頭處易影響搜索速度# 3,千萬注意通配符的位置！！！# 用concat拼接兩列: mysql中只能如此concat()/*select concat(`vend_name`, (,`vend_country`,))from `Vendors`order by `vend_name`;*/# 給合併形成的新列取一個名字vend_title/*select concat(`vend_name`, (,`vend_country`,))as vend_titlefrom `Vendors`order by `vend_name`;*/# 新建1列計算某一訂單的總價格/*select `order_num` ,`prod_id`,`quantity`,`item_price`, quantity*`item_price` as expanded_pricefrom `OrderItems`where `order_num`=20008;ORDER BY expanded_price;*//*-- ------------------------------------------------------# select不僅可以檢索數據，還可以測試、計算、檢驗函數,類似print# select asdf # 輸出字元asdf# select 3*2 # 計算3*2，返回6# select trim( asdf ) # 去除字元串前後的空格# select now() # 返回當下時間 # 返回當前日期、時間-- ------------------------------------------------------*//*-- ------------------------------------------------------ 函數一,文本處理函數 1,LEFT(xxx,n)，RIGHT(xxx,n):返回xxx從左右開始的第一個到第n個字元； 2,LTRIM(),RTRIM():去除左右的空格； 3,lower(),upper():將字元串全部小寫、大寫； 4,LENGTH():返回字元串的長度 5,SOUNDEX():根據發音，模糊搜索二,日期、時間處理函數 1,year,month,day可分別提取時間中的年、月、日期； 2,select now(),返回當前日期時間如下是查詢2012-01-12日的訂單select *from `Orders`where year(`order_date`) = 2012 and month(`order_date`) = 01 and day(`order_date`) =12 二,數值處理函數 1,sin():求正線值 2,cos():求餘弦值 3,TAN():求正切值 4,SQRT():求平方根 5,ABS():求絕對值 6,EXP():求指數值 7,pi():求圓周率 8，＋－*／，常規加減乘除可直接用，並用as新建列------------------------------------------------------*//*-- ------------------------------------------------------匯總數據1,AVG():返回某列的平均值，注意是「列」，列名必須給出,只用於單列返回所有價格的均價select avg(`prod_price`) as avg_pricefrom `Products`返回特定供應商的均價select avg(`prod_price`) as avg_pricefrom `Products`where `vend_id`=dll01返回特定供應商的不同商品價格的均價select avg(DISTINCT `prod_price`) as avg_pricefrom `Products`where `vend_id`=dll012,count():返回某列的行數，可以是所有列，也可是某一列所有行統計行數：此時控制nullselect COUNT(*)from `OrderItems`某一列的行樹統計：此時忽略空值nullselect count(`cust_email`) as num_custfrom `Customers`3,max(),min() 用於查找最大的數值、日期，此時會忽略空值null 用於文本數據時，max返回最後一個數據，min返回第一個4,sum():返回某列的和，會忽略空值null求某1列的和select sum(quantity)from `OrderItems`求所有花費select sum(`item_price`*`quantity`) as total_pricefrom `OrderItems`求某個訂單的所有花費select sum(`item_price`*`quantity`) as total_pricefrom `OrderItems`where `order_num`=20005一個綜合應用：select count(*) as num_items, max(`prod_price`) as price_max, min(`prod_price`) as price_min, avg(`prod_price`) as price_avg, sum(`prod_price`) as price_sumfrom `Products`-- ------------------------------------------------------*//*-- ------------------------------------------------------分組數據:將數據按照不同需要分為多個邏輯組，每個組進行聚類計算。一，GROUP BY1，求每個供應商提供了多少產品，注意與where的區別select `vend_id`,count(`prod_name`)from `Products`group by `vend_id`2，group by的嵌套，二級分組select `vend_id`, count(`prod_name`) as name_count, count(`prod_price`) as price_countfrom `Products`group by `vend_id`,`prod_price`3，having過濾已經分出來的組過濾：得出商品數量大於3的供應商select `vend_id`, count(*) as prod_numfrom `Products`group by `vend_id`having count(*) >= 34，注意where和having過濾數據的區別： 1，where用於過濾行級數據 2，having用於過濾分組出來的數據，且分組過濾只能用having 3，having支持絕大部分where語句，至少條件不同而已 4，where在分組前過濾，having在分組後過濾 5，having應結合group by語句，而where則可應用標準的行級過濾where過濾時，關鍵字可不必在select內select `prod_price`from `Products`where `vend_id`=dll01having過濾時，關鍵字必須在select列select `prod_price`,`vend_id`from `Products`having `vend_id`=dll01where和having綜合運用：select `vend_id`, count(*) as prod_numfrom `Products`where `prod_price` >=4group by `vend_id`having count(*) >= 35，order by：正確排序的唯一方法select `order_num`,count(*) as itemsfrom `OrderItems`group by `order_num`having items >= 3ORDER BY items;------------------------------------------------------*//*-- ------------------------------------------------------select子句:selectfromwheregroup byhavingorder by-- ------------------------------------------------------*/-- 聯結表/*-- ------------------------------------------------------內聯結：一次返回不同表中的數據，通過兩張表共同的鍵，分別在2張表中查不同的數據簡單寫法：where語句select `vend_name`,`prod_name`,`prod_price`from `Vendors`,`Products`where `Vendors`.`vend_id`=`Products`.`vend_id`標準寫法：INNER JOIN on寫法select `vend_name`,`prod_name`,`prod_price`from `Vendors` INNER JOIN `Products`on `Vendors`.`vend_id`=`Products`.`vend_id`聯機多張表：select `vend_name`,`prod_name`,`prod_price`,`quantity`from `Vendors`,`Products`,`OrderItems`where `Vendors`.`vend_id`=`Products`.`vend_id`and `Products`.`prod_id`=`OrderItems`.`prod_id`and `order_num`=20007select `cust_name`,`cust_contact`from `OrderItems`,`Customers`,`Orders`where `Customers`.`cust_id`=`Orders`.`cust_id`and `Orders`.`order_num`=`OrderItems`.`order_num`and `prod_id`=rgan01-- ------------------------------------------------------*//*-- ------------------------------------------------------使用表別名：給表重命名select `cust_name`,`cust_country`from `Customers` as c, `Orders` as o, `OrderItems` as oiwhere c.`cust_id`=o.`cust_id`and o.`order_num`=oi.`order_num`and `prod_id`=rgan01-- ------------------------------------------------------*//*-- ------------------------------------------------------自聯結：self-join同一張表select c1.`cust_id`,c1.`cust_name`,c1.`cust_contact`from `Customers` as c1, `Customers` as c2where c2.`cust_contact`=jim jonesand c1.`cust_name`=c2.`cust_name`也可用子查詢實現：select `cust_id`,`cust_name`,`cust_contact`from `Customers`where `cust_name`=(select `cust_name` from `Customers` where `cust_contact`=jim jones ) 自聯結速度比子查詢快很多，建議優先自聯結-- ------------------------------------------------------*//*-- ------------------------------------------------------自然聯結：naturaljoinselect C.*, O.`order_num`,O.`order_date`,OI.`prod_id`,OI.`quantity`,OI.`item_price`from `Customers` as C,`Orders` as O,`OrderItems` as OIwhere C.`cust_id`= O.`cust_id` and O.`order_num` = OI.`order_num` and oi.`prod_id` = rgan01 通配符*只對第一個表有用，即檢索出該表的所有列，其餘表中的列都是明確列出來的，沒有重複檢索。-- ------------------------------------------------------*//*-- ------------------------------------------------------外聯結：包含哪些沒有關聯的表，注意與內聯結的區別內聯結：檢索所有顧客及其訂單select `Customers`.`cust_id`,`Orders`.`order_num`from `Customers` INNER JOIN `Orders`on `Customers`.`cust_id`=`Orders`.`cust_id`外聯結：檢索所有顧客及其訂單，即使該顧客沒有訂單左外聯結：select `Customers`.`cust_id`,`Orders`.`order_num`from `Customers` left outer JOIN `Orders`on `Customers`.`cust_id`=`Orders`.`cust_id`left表示從左邊的Customers表檢索沒有包涵的，也可right檢索右邊Orders：右外聯結：select `Customers`.`cust_id`,`Orders`.`order_num`from `Customers` right outer JOIN `Orders`on `Customers`.`cust_id`=`Orders`.`cust_id`帶聚集函數的聯結：按cust_id，提取每個顧客的訂單量select `Customers`.`cust_id`, COUNT(`Orders`.`order_num`) as num_ord from `Customers` inner join `Orders`on `Customers`.`cust_id`=`Orders`.`cust_id`group by `Customers`.`cust_id`-- ------------------------------------------------------*/-- 組合查詢/*-- ------------------------------------------------------1,使用多條where語句查詢select `cust_name`,`cust_contact`,`cust_email`,`cust_state`from `Customers`where `cust_state` in (il,in,mi)or `cust_name`=fun4allorder by `cust_email`2，使用union結合多個select共同查詢select `cust_name`,`cust_contact`,`cust_email`,`cust_state`from `Customers`where `cust_state` in (il,in,mi)nionselect `cust_name`,`cust_contact`,`cust_email`,`cust_state`from `Customers`where `cust_name`=fun4all注意：1，union可以聯結個select，但之間必須有union；2，各個select必須具有相同的行，但順序可不一致；3，列數據類型必須兼容。注意：union和多個select查詢的結果略有不同，前者會去重，後者不會如果想union不去重，也可用union all代替union，如下：select `cust_name`,`cust_contact`,`cust_email`,`cust_state`from `Customers`where `cust_state` in (il,in,mi)union allselect `cust_name`,`cust_contact`,`cust_email`,`cust_state`from `Customers`where `cust_name`=fun4all-- ------------------------------------------------------*/-- 排序/*-- -------------------------------只能在最後一條的select後面order by,如下：select `cust_name`,`cust_contact`,`cust_email`,`cust_state`from `Customers`where `cust_state` in (il,in,mi)union allselect `cust_name`,`cust_contact`,`cust_email`,`cust_state`from `Customers`where `cust_name`=fun4allorder by `cust_name`,`cust_contact`-- ------------------------------------------------------*/-- 插入:insert/*-- ------------------------------------------------------1，插入完整的行insert into `Customers`(`cust_id`, `cust_name`, `cust_address`, `cust_city`, `cust_state`, `cust_zip`, `cust_country`, `cust_contact`, `cust_email`)values (1000000006, toy land, 123 any street, new york, NY, 11111, USA, null, null)2，插入行的一部分:未給出值的默認為nullinsert into `Customers`(`cust_id`, `cust_name`, `cust_address`, `cust_city`, `cust_state`, `cust_zip`, `cust_country`)values (1000000009, roy land, 123 any street, new york, NY, 11111, USA)3，插入檢索出的結果：insert select可插入多行，檢索出多少就插入多少insert into ....select....from ..insert into `Customers`(`cust_id`, `cust_contact`, `cust_email`, `cust_name`, `cust_address`, `cust_city`, `cust_state`, `cust_zip`, `cust_country`) select `cust_id`, `cust_contact`, `cust_email`, `cust_name`, `cust_address`, `cust_city`, `cust_state`, `cust_zip`, `cust_country`from CustNew詳見136頁4，從一個表複製到另一個表select into：將一個表的內容複製到一個全新的運行的表1，CustCopy已經存在：select *into CustCopyfrom `Customers`2，創建一個新表名為CustCopy，並將數據放進去CREATE TABLE CustCopy asselect * from `Customers`-- ------------------------------------------------------*/

三，小結

SQL非常的重要，我每次面試總會有SQL的題，面試官也會仔細問：
SQL的查詢非常重要，所謂「增刪查改」，一定要會各種查！其餘幾個可選擇性練習，很多公司的資料庫是不會讓新手去維護，更不要說讓我們去刪除了，萬一誤刪一張表，後果很嚴重的。
group by、where、order by、having是四個最基本的功能，一定要熟之又熟；
聯結表一定要會。這是SQL最重要的功能，我們還要會區分各種查找的區別。面試的時候肯定會有這方面的，所以這又是一個重要板塊。

以上就是我作為一個小白學習SQL的一點體會和面試反饋出來的能力需求，希望對你有幫助。其實並沒有那麼難，還算是相對簡單的，學習成本不是很高，但要是學精就不容易了，我準備再練習一下網站上的題，也歡迎大家和我交流。

以上就是本文的全部，謝謝你查看本文。

（人氣稀薄????，急需關愛????。如果您竟然看到了這裡還沒走開，請幫忙多多點贊、收藏哈，謝謝啦朋友們～～）