程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> 《MySQL 必知必會》讀書總結,《mysql必知必會》

《MySQL 必知必會》讀書總結,《mysql必知必會》

編輯:MySQL綜合教程

《MySQL 必知必會》讀書總結,《mysql必知必會》


這是 《MySQL 必知必會》 的讀書總結。也是自己整理的常用操作的參考手冊。

   

使用 MySQL

  • 連接到 MySQL

shell>mysql -u root -p

Enter password:******

  • 顯示數據庫

mysql>SHOW DATABASES;

  • 選擇數據庫

mysql>USE mytest;

  • 顯示數據庫中的表

mysql>SHOW TABLES;

  • 顯示表列

mysql>SHOW COLUMNS FROM tmall_user;

mysql>DESCRIBE tmall_user;

第二種方法與第一種方法效果相同

  • SHOW 的其他用法

mysql>SHOW STATUS;

顯示服務器的狀態信息

mysql>SHOW CREATE DATABASE mytest;

顯示創建數據庫的語句

mysql>SHOW CREATE TABLE tmall_user;

顯示創建表的語句

mysql>SHOW GRANTS;

顯示授權用戶的安全權限

mysql>SHOW ERRORS;

顯示服務器錯誤

mysql>SHOW WARNINGS;

顯示服務器警告信息

   

基本查詢(SELECT 語句)

  • 查詢某列

mysql>SELECT username FROM tmall_user;

  • 查詢多列

mysql>SELECT username, password, time FROM tmall_user;

  • 查詢所有列

mysql>SELECT * FROM tmall_user;

  • 查詢不同行

mysql>SELECT DISTINCT password FROM tmall_user;

注:DISTINCT 關鍵字應用於所有列。

  • 限制結果

mysql>SELECT username FROM tmall_user LIMIT 5

返回5行

mysql>SELECT username FROM tmall_user LIMIT 5,5

返回從第5行開始的5行

注:查詢結果的第一行為行0,因此,LIMIT 1,1 將檢索出第二行

新語法:LIMIT 4 OFFSET 3 從行3開始取4行,就像 LIMIT 3,4 一樣

  • 使用完全限定的列名、表名

mysql>SELECT tmall_user.username FROM mytest.tmall_user;

   

排序數據(ORDER BY 子句)

  • 排列數據

mysql>SELECT username FROM tmall_user

->ORDER BY username;

對 username 結構列以字母順序排列數據

  • 按多個列排序

mysql>SELECT prod_id, prod_price, prod_name FROM products

->ORDER BY prod_peice, prod_name;

查詢三個列,先按照價格排序,然後再按名稱排序

  • 指定排序方向

mysql>SELECT prod_id, prod_price, prod_name FROM products

->ORDER BY prod_price DESC

默認的排序是升序排序,DESC 關鍵字指定降序排序(價格由高到低)

  • 多個列排序並指定方向

mysql>SELECT prod_id, prod_price, prod_name FROM products

->ORDER BY prod_price DESC, prod_name;

以降序排序產品(價格從高到低),然後再對產品名排序。

注:DESC 關鍵字之應用到直接位於其前面的列名,如果想在多個列上進行降序排序,必須對每個列指定 DESC 關鍵字。

  • 使用 ORDER BY 和 LIMIT 的組合,找出一個列中最高或最低的值

mysql>SELECT prod_price FROM products

->ORDER BY prod_price DESC

->LIMIT 1;

查詢價格最貴的那個商品

   

查詢條件(WHERE 子句)

  • 查詢滿足條件的行

mysql>SELECT prod_name, prod_price FROM products

->WHERE prod_price = 2.50

返回 prod_price 值為 2.50 的行

  • WHERE 子句操作符

=

等於

<>

不等於

!=

不等於

<

小於

<=

小於等於

>

大於

>=

大於等於

BETWEEN

在指定的兩個值之間

  • 一些操作符的例子

mysql>SELECT prod_name, prod_price FROM products

->WHERE prod_price < 10;

列出價格小於 10 的所有產品

mysql>SELECT vend_id, prod_name FROM products

->WHERE vend_id <> 1003;

列出不是由供應商1003制造的所有產品

mysql>SELECT prod_name, prod_price FROM products

->WHERE prod_price BETWEEN 5 AND 10

查詢價格在 5 和 10 之間的所有產品

mysql>SELECT prod_name FROM products

->WHERE prod_price IS NULL;

查詢價格列值為 NULL 的行。(空值=值為NULL)

  • 組合 WHERE 子句(邏輯操作符)

AND

OR

  • IN 操作符

mysql>SELECT prod_name FROM products

->WHERE vend_id IN (1002, 1003)

->ORDER BY prod_name;

查詢供應商1002和1003制造的所有產品

IN WHERE 子句中用來指定要匹配值得清單的關鍵字,功能與 OR 相當

  • NOT 操作符

mysql>SELECT prod_name FROM products

->WHERE vend_id NOT (1002, 1003)

->ORDER BY prod_name;

查詢除1002和1003之外的所用供應商制造的產品

NOT WHERE 子句中用來否定後跟條件的關鍵字,MySQL中的 NOT 支持使用 對 IN、BETWEEN 和 EXISTS 子句取反

  • LIKE 操作符(通配符過濾)

%

任何字符出現任意次數,不能匹配 NULL

_

任何一個字符

mysql>SELECT prod_name FROM products

->WHERE prod_name LIKE 's%e';

查詢以s開頭以e結尾的產品

   

   

函數

  • 文本處理函數

Left()

返回串左邊的字符

Length()

返回串的長度

Locate()

找出串的一個子串

Ltrim()

去掉串左邊的空格

Right()

返回串右邊的字符

Rtrim()

去掉串右邊的空格

Soundex()

返回串SOUNDEX值(根據發音比較而不是字母比較)

SubString()

返回子串的字符

Upper()

轉換為大寫

mysql>SELECT name FROM tmall_user

->WHERE SOUNDEX(name) = SOUNDEX('雷君')

返回:雷軍

  • 時間和日期處理函數

AddDate()

  

AddTime()

  

CurDate()

返回當前日期

CurTime()

返回當前時間

Date()

返回日期時間的日期部分

DateDiff()

計算兩個日期之差

Date_Add()

高度靈活的日期運算函數

Date_Format

返回一個格式化的日期或時間串

Day()

返回日期的天數部分

DayOfWeek()

返回日期對應的星期數

Hour()

返回時間的小時部分

Minute()

返回時間的分鐘部分

Month()

返回日期的月份部分

Now()

返回當前日期和時間

Second()

返回時間的秒數部分

Time()

返回日期時間的時間部分

Year()

返回日期的年份部分

mysql>SELECT username FROM tmall_user

->WHERE DATE(time) = '2016-09-21';

使用日期比較過濾查詢結果

mysql>SELECT count(id) FROM tmall_user

->WHERE DATE(time) BETWEEN '2016-09-01' AND '2016-09-30';

查詢2016年9月注冊的用戶數量

mysql>SELECT count(id) FROM tmall_user

->WHERE Year(time) = 2016 AND Month(time) = 9;

效果與上例相同

  • 數值處理函數

Abs()

  

Cos()

  

Mod()

  

Pi()

  

Rand()

  

Sin()

  

Sqrt()

  

Tan()

  

   

匯總數據(聚集函數)

  • 聚集函數

AVG()

返回某列的平均值

COUNT()

返回某列的行數

MAX()

返回某列的最大值

MIN()

返回某列的最小值

SUM()

返回某列值之和

  • AVG() 函數

mysql>SELECT AVG(prod_price) FROM products;

返回所有products表中產品的平均價格

mysql>SELECT AVG(prod_price) FROM products

->WHERE vend_id = 1003;

返回特定供應商所提供產品的平均價格

AVG() 函數只應用於單個列,忽略列值為NULL的行

  • COUNT() 函數

mysql>SELECT COUNT(*) FROM customers;

對所有行進行計數,包括列值為NULL的行

mysql>SELECT COUNT(name) FROM tmall_user;

只對有昵稱的行進行計數,忽略列值為NULL的行

  • MAX()函數和MIN()函數

mysql>SELECT MAX(prod_price) FROM products;

返回最貴的物品的價格

MAX()函數一般用於找出最大的數值或日期值,但也可以返回任意列中的最大值。在用於文本數據時,按照相應順序排序,返回最後一行。

MAX()函數忽略列值為NULL的行

MIN()函數與MAX()函數功能相反。

  • SUM()函數

mysql>SELECT SUM(quantity) FROM orderitems

->WHERE order_num = 20005;

查詢所訂購物品的總數(所有quantity之和)

mysql>SELECT SUM(item_price*quantity) FROM orderitems

->WHERE order_num = 20005;

查詢所訂購物品的總金額。

  • 聚集不同值(DISTINCT)

mysql>SELECT AVG(DISTINCT prod_price) FROM products

->WHERE vend_id = 1003;

返回特定供應商的產品的平均價格,平均值只考慮各個不同的價格。

  • 組合聚集函數

mysql>SELECT COUNT(*), MIN(prod_price), MAX(prod_price),

->AVG(prod_price) FROM products;

單條SELECT語句執行了4個聚集計算,返回4個值

   

   

   

分組數據(GROUP BY 子句和 HAVING 子句)

  • 創建分組

mysql>SELECT vend_id, COUNT(*) FROM products

->GROUP BY vend_id;

按vend_id排序並分組數據,COUNT()會對每個 vend_id 分組計算 num_prods 一次而不是對整個表計算。

  • 過濾分組

HAVING 非常類似於 WHERE。HAVING 支持所有 WHERE 操作符。他們之間的差別是WHERE 過濾行,而HAVING 過濾分組。

另一種理解:WHERE 在數據分組前進行過濾,HAVING 在數據分組後進行過濾。

mysql>SELECT cust_id, COUNT(*) FROM orders

->GROUP BY cust_id

->HAVING COUNT(*) >= 2;

過濾兩個以上的訂單的分組

mysql>SELECT vend_id, COUNT(*) FROM products

->WHERE prod_price >=10

->GROUP BY vend_id

->HAVING COUNT(*) >= 2;

列出具有兩個以上,價格為10以上的產品的供應商

mysql>SELECT order_num, SUM(quantity*item_price) AS ordertotal

->FROM orderitems

->ORDER BY order_num

->HANVING SUM(quantity*item_price) >= 50

->ORDER BY ordertoal;

GROUP BY 子句按照訂單號分組數據,以便SUM(*)返回總計訂單價格。HAVING 子句過濾數據,使得返回總計訂單價格>=50的訂單。最後用ORDER BY排序輸出。

  • SELECT 子句順序

SELECT

要返回的列或表達式

FROM

要查詢的表

WHERE

行過濾

GROUP BY

分組

HAVING

分組過濾

ORDER BY

輸出排序順序

LIMIT

行數

   

子查詢

  • 查詢訂購物品TNT2的所有客戶

mysql>SELECT cust_name, cust_contact FROM customers

->WHERE cust_id IN (SELECT cust_id FROM orders

-> WHERE order_num IN(SELECT order_num

-> FROM orderitems

-> WHERE prod_id = 'TNT2'));

雖然子查詢一般與IN操作符結合使用,但也可以用於測試等於=、不等於<>。

  • 作為計算字段使用子查詢

mysql>SELECT cust_name, cust_state, (SELECT COUNT(*)FROM orders

-> WHERE orders.cust_id = customers.cust_id) AS orders

->FROM customers

->ORDER BY cust_name;

查詢customers表中每個客戶的訂單總數。訂單與相應客戶ID存儲在orders表中

   

聯結表

  • 創建聯結

mysql>SELECT vend_name, prod_name, prod_price

->FROM vendors, products

->WHERE vendors.vend_id = products.vend_id

->ORDER BY vend_name, prod_name;

這兩個表用WHERE子句聯結,需要完全限定列名。

由沒有聯結條件的表關系返回的結果為笛卡爾積,檢索出的行數=表1的行數×表2的行數。應該保證所有聯結都有WHERE子句。

  • 內部聯結(等值聯結)

mysql>SELECT vend_name, prod_name, prod_price

->FROM vendors INNER JOIN products

->ON vendors.vend_id = products.vend_id;

兩個表之間的關系以 INNER JOIN 指定,ON 的條件與上例 WHERE 條件相同

  • 聯結多個表(內部聯結)

mysql>SELECT prod_name, vend_name, prod_price, quantity

->FROM orderitems, products, vendors

->WHERE products.vend_id = vendors.vend_id

-> AND orderitems.prod_id = products.prod_id

-> AND order_num = 20005;

mysql>SELECT cust_name, cust_contact

->FROM customers, orders, orderitems

->WHERE customers.cust_id = orders.cust_id

-> AND orderitems.order_num = orders.order_num

-> AND prod_id = 'TNT2';

使用聯結查詢訂購物品TNT2的所有客戶

  • 使用表別名

mysql>SELECT cust_name, cust_contact

->FROM customers AS c, orders AS o, orderitems AS oi

->WHERE c.cust_id = o.cust_id

-> AND oi.order_num = o.order_num

-> AND prod_id = 'TNT2';

  • 自聯結

mysql>SELECT prod_id, prod_name FROM products

->WHERE vend_id = (SELECT vend_id FROM products

-> WHERE prod_id = 'DTNTR');

mysql>SELECT p1.prod_id, p1.prod_name

->FROM products AS p1, products AS p2

->WHERE p1.vend_id = p2.vend_id AND p2.prod_id = 'DTNTR'

選擇某物品的供應商所生產的其他物品,第一種方法使用子查詢,第二種方法使用自聯結,推薦用自聯結而不用子查詢。

使用表別名的主要原因是能在單條SELECT語句中不止一次引用相同的表

  • 自然聯結

自然聯結排除多次出現,使每個列只返回一次。這一般是通過對第一個表使用SELECT *,對所有其他的表使用明確的子集來完成的。

mysql>SELECT c*, o.order_num, o.order_date, oi.prod_id, oi_quantity,

->oi.item_price

->FROM customers AS c, orders AS o, orderitems AS oi

->WHERE c.cust_id = o.cust_id

->AND oi.order_num = o.order_num

->AND prod_id = 'FB';

  • 外部聯結

mysql>SELECT customers.cust_id, orders.order_num

->FROM customers INNER LEFT OUTER JOIN orders

->ON customers.cust_id = orders.cust_id;

查詢所有客戶及其訂單,包括沒有訂單的客戶,

LEFT 指從左邊的表選擇所有行

RIGHT 指從右邊的表選擇所有行

  • 帶聚集函數的聯結

mysql>SELECT customers.cust_name, 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;

檢索所有客戶及每個客戶所下的訂單數

mysql>SELECT customers.cust_name, customers.cust_id,

-> COUNT(orders.order_num) AS num_ord

->FROM customers LEFT OUTER JOIN orders

-> ON customers.cust_id = orders.cust_id

->GROUP BY customers.cust_id;

檢索所有客戶及每個客戶所下的訂單數(包括沒有下訂單的客戶)

  • 使用聯結的要點
  • 保證正確的聯結條件
  • 應該總是提供聯結條件,否則會得出笛卡爾積
  • 如果一個聯結中包含多個表,甚至每個聯結采用不同的聯結類型,應當先分別測試每個聯結,這將使故障排除更為簡單。

   

組合查詢(UNION 操作符)

   

全文本搜索

   

插入數據

   

更新和刪除數據

  • 更新數據(UPDATE 語句)

不要省略WHERE子句,再使用UPDATE時一定要注意細心。因為稍不注意,就會更新表中所有行。

UPDATE customers SET cust_email = '[email protected]'

WHERE cust_id = 10005;

更新多個列

UPDATE customers SET cust_email = 'The Fudds', cust_email = '[email protected]'

WHERE cust_id = 10005;

  

   

   

規范

  • 對 SQL 關鍵字使用大寫,而對所有列和表名使用小寫,這樣做使代碼更易於閱讀和調試。
  • 任何時候使用具有 AND 和 OR 操作符的 WHERE 子句,都應該使用圓括號明確地分組操作符。
  • 不要過度使用通配符,通配符通常消耗更多的時間。
  • 使用聚集函數來匯總數據,比你在自己的軟件中計算要快得多,這些函數是高效設計的。
  • 用逐漸子查詢來建立查詢

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved