Mysql 基本語句 + 進階查詢

標籤：roo   文本編輯   應該   資料庫操作   優點   說明   萬用字元   電腦   大於   

MySQL執行SQL指令檔的命令：

從cmd進入mysql命令列模式：

mysql> -uroot –prootpassword –Ddatabasename

如果是我本地的資料庫，就相應修改為：

Mysql> -uroot –p111111 –Dmy_database < xxx.sql

執行SQL指令檔：

mysql> source E:\program files\sql\xxxxxx.sql.

如所示:

2. MySQL基礎知識：2.1 串連MySQL資料庫

主機名稱（電腦名稱）——如果串連到本地MySQL伺服器，為localhost;

連接埠——預設連接埠為3306.

2.2 資料庫操作

假設資料庫的名稱為：myDatabase;

（1）選擇資料庫：

輸入：USE myDatabase;

輸出：Database changed

（2）顯示所有的資料庫：

輸入：SHOW DATABASES;

（3）顯示該資料庫下的所有表：

輸入：SHOW TABLES;

（4）顯示某一個表內的所有列，假設表名為customers:

輸入: SHOW COLUMNS FROM customers; 或者是 DESCRIBE customers;

2.3 檢索資料（SELECT）

輸入：SELECT product_name FROM products;

含義：從products表中檢索出一個名為product_name的列。

注意：檢索出的資料沒有排序，返回的資料可能是資料被添加到表中的順序。

輸入：SELECT product_id, product_name, product_price FROM products;

輸入：SELECT * FROM products;

輸入：SELECT DISTINCT vend_id FROM products;

輸入：SELECT product_name FROM products LIMIT 5; //少於5行.

輸入：SELECT product_name FROM products LIMIT 5,5; //檢索出第6行後的5行.

輸入：SELECT product.product_name FROM products; //使用完整表名

輸入：SELECT products.product_name FROM myDatabase.products; //完全限定資料庫名和表名

2.4 排序檢索資料（ORDER BY）

輸入：SELECT product_name FROM products ORDER BY product_name; //按一列排序.

輸入：SELECT product_id, product_price, product_name FROM products ORDER BY product_price, product_name; //按多個列排序.

指定排序方向(如果不指定排序方向，預設是升序ASC)：

輸入：SELECT product_id,product_price,product_name FROM products ORDER BY product_price DESC/ASC; //DESC是降序，ASD是升序.

輸入：SELECT product_id,product_price,product_name FROM products ORDER BY product_price DESC, product_name; //先按照product_price降序排，再對結果按name排序.

輸入：SELECT product_price FROM products ORDER BY product_price DESC LIMIT 1;

注意：關鍵字順序SELECT->FROM>ORDER BY>LIMIT,如果次序不對，結果會報錯。

2.5 過濾資料（WHERE）

過濾條件：=,<>,!=,<,<=,>,>=,BETWEEN

操作符	=	<>	!=	<	<=	>	>=	BETWEEN
說明	等於	不等於	不等於	小於	小於等於	大於	大於等於	之間

 

輸入：SELECT product_name,product_price FROM products WHERE product_price=2.50;

輸入：SELECT product_name,product_price FROM products WHERE product_price BETWEEN 5 AND 10;

輸入：SELECT product_name FROM products WHERE product_price IS NULL; // 空值檢查.

2.6 資料過濾（組合WHERE子句，AND/OR/IN/NOT）

輸入：SELECT product_id,product_price,product_name FROM products WHERE vend_id = 1003 AND product_price <=10;

輸入：SELECT product_name,product_price FROM products WHERE vend_id=1002 OR vend_id=1003;

輸入：SELECT product_name,product_price FROM products WHERE (vend_id=1002 OR vend_id=1003) AND product_price >=10;

注意：任何時候使用具有AND和OR操作符的WHERE子句，都應該使用圓括弧明確地分組操作符，而不要去過分依賴預設計算次序。

輸入：SELECT product_name,product_price FROM products WHERE vend_id IN (1002,1003) ORDER BY product_name;

輸入：SELECT product_name,product_price FROM products WHERE vend_id=1002 OR vend_id=1003 ORDER BY product_name;

注意：在where子句中應用IN和OR的功能是相同的，但是IN操作符的優點如下：

（1） 使用長的合法選項清單時，IN操作符的文法更清楚且更直觀；

（2） 計算的次序更容易管理；

（3） IN操作符一般比OR操作符清單執行更快；

（4） IN的最大優點是可以包含其他SELECT語句，使得能夠更動態地建立WHERE子句。

輸入：SELECT product_name,product_price FROM products WHERE vend_id NOT IN (1002,1003) ORDER BY product_name;

2.7 用萬用字元進行過濾（LIKE—%）

萬用字元（wildcard）：用來匹配值的一部分的特殊字元。

LIKE指示MySQL後跟的搜尋模式利用萬用字元匹配，而不是直接相等匹配進行比較。

%：表示任何字元出現任意次數。

1）找到所有以詞jet開頭的產品：

輸入：SELECT product_id,product_name FROM products WHERE product_name LIKE ‘jet%’;

注意：萬用字元可以在搜尋模式中任意位置使用，並且可以使用多個萬用字元。

輸入：SELECT product_id,product_name FROM products WHERE product_name LIKE ‘%anvil%’;

_：用途與%一樣，但底線只匹配單個字元而不是多個字元。

注意：萬用字元搜尋的處理一般要比之前討論的其他搜尋花費時間長。使用萬用字元的技巧：

（1） 不要過度使用萬用字元，即如果其他動作符能達到相同目的，應該使用其他動作符；

（2） 確實需要使用萬用字元時，除非絕對有必要，否則不要把它們用在搜尋模式的開始處。把萬用字元置於搜尋模式的開始處，搜尋起來是最慢的；

（3） 仔細注意萬用字元的位置。如果放錯地方，可能不會返回想要的資料。

2.8 用Regex進行搜尋

Regex：用來匹配文本中的特殊的串（字元集合）。所有種類的程式設計語言、文字編輯器、作業系統都支援Regex。

注意：MySQL僅支援多數Regex實現的一個很小的子集。

 

2.8.1基底字元匹配

輸入：SELECT product_name FROM products WHERE product_name REGEXP ‘1000’ ORDER BY product_name;

含義：它告訴MySQL,REGEXP後所跟的東西作為Regex（與文字本文1000匹配的一個Regex）處理。

注意：LIKE與REGEXP的區別：

（1） LIKE匹配整個列。如果被匹配的文本在列值中出現，LIKE將不會找到它，相應的行也不會被返回（除非使用萬用字元）；

（2） REGEXP在列值內進行匹配，如果被匹配的文本在列值中出現，REGEXP將會找到它，相應的行也會被返回。

 

2.8.2 進行OR匹配

輸入：SELECT prod_name FROM products WHERE prod_name REGEXP ‘1000|2000’ ORDER BY prod_name;

輸入：SELECT prod_name FROM products WHERE prod_name REGEXP ‘[123] Ton’ ORDER BY prod_name;

含義：[123]定義一組字元，意思是匹配1或2或3，等同於1|2|3。

2.9建立計算欄位

需求：由於儲存在表中的資料不是應用程式所直接需要的。因此需要在檢索的過程中同時進行計算。

2.9.1 拼接欄位( concatenate)

輸入：SELECT Concat( vend_name, ‘(’, vend_country, ‘)’) FROM vendors ORDER BY vend_name;

含義：將會形成一個新的拼接後的列名，為Concat(vend_name, ‘(’, vend_country, ‘)’)。

2.9.2 使用別名(alias)

輸入：SELECT Concat(RTrim(vend_name), ‘(’, vend_country, ‘)’) AS vend_title FROM vendors ORDER BY vend_name;

含義：表示拼接後，並建立一個新的計算欄位名vend_title用來儲存計算結果。

2.9.3 執行算術計算

輸入：SELECT prod_id, quantity, item_price, quantity*item_price AS expanded_price FROM orderitems WHERE order_num = 2000;

 

2.10使用資料處理函數

SQL支援以下類型的函數：

（1） 處理字串函數；

（2） 在數值資料上進行算術操作；

（3） 處理日期和時間值並從這些值中提取特定成分（例如，返回兩個日期之差，檢查日期有效性等）。

2.10.1 文本處理函數

輸入：SELECT vend_name, Upper(vend_name) AS vend_name_upcase FROM vendors ORDER BY vend_name;

文本函數	Left/Right()	Length()	Locate()	Lower/Upper	LTrim/RTrim	Substring()
說明	返回串左/右的字元	串長度	串的子串	小寫/大寫	去掉左/右邊的空格	返回字元的字元

 

 

2.10.2 日期和時間處理函數

在資料庫中，日期和時間均採用相應的資料類型和特殊的格式儲存，以便能快速和有效地排序或過濾，並且節省實體儲存體空間。

常用日期和時間的處理函數表

AddDate/AddTime()	增加一個日期/時間.
CurDate/CurTime()	返回當前日期/時間.
Date()	返回日期時間的日期部分.
DateDiff()	計算兩個日期之差.
Date_Add()	高度靈活的日期運算函數.
Date_Sub()	從日期減去指定的時間間隔.
Date_Format()	返回一個格式化的日期或時間串.
Year/Month/Day()	返回一個日期的年/月/天數部分.
DayOfWeek()	對於一個日期，返回對應的星期幾.
Now()	返回當前日期和時間.
Hour/Minute/Second	返回一個時間的小時/分鐘/秒.

 

在MySQL中，無論是插入或更新表值，還是用WHERE子句進行過濾，日期必須為格式yyyy-mm-dd。

輸入：SELECT cust_id, order_num FROM orders WHERE order_date = ‘2005-09-01’；

注意：假如資料庫的order_date欄位值儲存的時間除了日期還有時間外，則上述的檢索會失敗。因此，更保險的檢索語句為：

輸入：SELECT cust_id, order_num FROM orders WHERE Date(order_date) = ‘2005-09-01’;

輸入: SELECT cust_id, order_num FROM orders WHERE Date(order_date) BETWEEN ‘2005-09-01’ AND ‘2005-09-30’;

輸入：SELECT cust_id, order_num FROM orders WHERE Year(order_date) = 2005 AND Month(order_date) = 9;

 

2.10.3 數值處理函數

數值函數	Abs()	Cos()	Exp ()	Pi()	Rand()	Sqrt()
說明	絕對值	餘弦	指數	圓周率	隨機數	平方根

Mysql 基本語句 + 進階查詢