Linux下MySQL常用操作網路匯總總結

一查詢數值型資料:
 SELECT * FROM tb_name WHERE sum > 100;
 查詢謂詞:>,=,<,<>,!=,!>,!<,=>,=<
 
二查詢字串
 SELECT * FROM tb_stu  WHERE sname  =  '小劉'
 SELECT * FROM tb_stu  WHERE sname like '劉%'
 SELECT * FROM tb_stu  WHERE sname like '%程式員'
 SELECT * FROM tb_stu  WHERE sname like '%PHP%'
 
三查詢日期型資料
 SELECT * FROM tb_stu WHERE date = '2011-04-08'
 注:不同資料庫對日期型資料存在差異: ：
 (1)MySQL:SELECT * from tb_name WHERE birthday = '2011-04-08'
 (2)SQL Server:SELECT * from tb_name WHERE birthday = '2011-04-08'
 (3)Access:SELECT * from tb_name WHERE birthday = #2011-04-08#
 
四查詢邏輯型資料
 SELECT * FROM tb_name WHERE type = 'T'
 SELECT * FROM tb_name WHERE type = 'F'
 邏輯運算子:and or not
 
五查詢非空資料
 SELECT * FROM tb_name WHERE address <>'' order by addtime desc
 注:<>相當於PHP中的!=
 
六利用變數查詢數值型資料
 SELECT * FROM tb_name WHERE id = '$_POST[text]' 
注:利用變數查詢資料時，傳入SQL的變數不必用引號括起來，因為PHP中的字串與數值型資料進行串連時，程式會自動將數值型資料轉變成字串，然後與要串連的字串進行串連
 
七利用變數查詢字串資料 
SELECT * FROM tb_name WHERE name LIKE '%$_POST[name]%' 
完全符合的方法"%%"表示可以出現在任何位置
 
八查詢前n條記錄
 SELECT * FROM tb_name LIMIT 0,$N;
 limit語句與其他語句，如order by等語句聯合使用，會使用SQL語句千變萬化，使程式非常靈活
 
九查詢後n條記錄
 SELECT * FROM tb_stu ORDER BY id ASC LIMIT $n
 
十查詢從指定位置開始的n條記錄
 SELECT * FROM tb_stu ORDER BY id ASC LIMIT $_POST[begin],$n
 注意:資料的id是從0開始的
 
十一查詢統計結果中的前n條記錄
 SELECT * ,(yw+sx+wy) AS total FROM tb_score ORDER BY (yw+sx+wy) DESC LIMIT 0,$num
 
十二查詢指定時間段的資料
 SELECT  要尋找的欄位 FROM 表名 WHERE 欄位名 BETWEEN 初始值 AND 終止值
 SELECT * FROM tb_stu WHERE age BETWEEN 0 AND 18
 
十三按月查詢統計資料
 SELECT * FROM tb_stu WHERE month(date) = '$_POST[date]' ORDER BY date ;
 註：SQL語言中提供了如下函數，利用這些函數可以很方便地實現按年、月、日進行查詢
 year(data):返回data運算式中的公元年分所對應的數值
 month(data):返回data運算式中的月分所對應的數值
 day(data):返回data運算式中的日期所對應的數值
 
十四查詢大於指定條件的記錄
 SELECT * FROM tb_stu WHERE age>$_POST[age] ORDER BY age;
 
十五查詢結果不顯示重複記錄
 SELECT DISTINCT 欄位名 FROM 表名 WHERE 查詢條件 
注:SQL語句中的DISTINCT必須與WHERE子句聯合使用，否則輸出的資訊不會有變化 ,且欄位不能用*代替
 
十六NOT與謂詞進行組合條件的查詢
 (1)NOT BERWEEN … AND … 對介於起始值和終止值間的資料時行查詢 可改成 <起始值 AND >終止值
 (2)IS NOT NULL 對非空值進行查詢 
 (3)IS NULL 對空值進行查詢
 (4)NOT IN 該式根據使用的關鍵字是包含在列表內還是排除在列表外，指定運算式的搜尋，搜尋運算式可以是常量或列名，而列名可以是一組常量，但更多情況下是子查詢
 
十七顯示資料表中重複的記錄和記錄條數
 SELECT  name,age,count(*) ,age FROM tb_stu WHERE age = '19' group by date
 
十八對資料進行降序/升序查詢
 SELECT 欄位名 FROM tb_stu WHERE 條件 ORDER BY 欄位 DESC 降序
 SELECT 欄位名 FROM tb_stu WHERE 條件 ORDER BY 欄位 ASC  升序
 注:對欄位進行排序時若不指定排序方式，則預設為ASC升序
 
十九對資料進行多條件查詢
 SELECT 欄位名 FROM tb_stu WHERE 條件 ORDER BY 欄位1 ASC 欄位2 DESC  …
 注意:對查詢資訊進行多條件排序是為了共同限制記錄的輸出，一般情況下，由於不是單一條件限制，所以在輸出效果上有一些差別。
 
二十對統計結果進行排序
 函數SUM([ALL]欄位名) 或 SUM([DISTINCT]欄位名),可實現對欄位的求和，函數中為ALL時為所有該欄位所有記錄求和,若為DISTINCT則為該欄位所有不重複記錄的欄位求和
 如：SELECT name,SUM(price) AS sumprice  FROM tb_price GROUP BY name
 
SELECT * FROM tb_name ORDER BY mount DESC,price ASC
 
二十一單列資料分組統計
 SELECT id,name,SUM(price) AS title,date FROM tb_price GROUP BY pid ORDER BY title DESC
 注:當分組語句group by排序語句order by同時出現在SQL語句中時，要將分組語句書寫在排序語句的前面，否則會出現錯誤
 
二十二多列資料分組統計
 多列資料分組統計與單列資料分組統計類似 
SELECT *，SUM(欄位1*欄位2) AS (新欄位1) FROM 表名 GROUP BY 欄位 ORDER BY 新欄位1 DESC
 SELECT id,name,SUM(price*num) AS sumprice  FROM tb_price GROUP BY pid ORDER BY sumprice DESC
 註：group by語句後面一般為不是彙總函式的數列，即不是要分組的列
 
二十三多表分組統計

 SELECT a.name,AVG(a.price),b.name,AVG(b.price) FROM tb_demo058 AS a,tb_demo058_1 AS b WHERE a.id=b.id GROUP BY b.type;

索引的操作

如何建立索引 ·索引通常被用於提高WHERE條件的資料行匹配或者執行連接操作時匹配其它表的資料行的搜尋速度。所以我們選擇索引的列也通常選擇出現在WHERE子句、join子句、ORDERBY或GROUP BY子句中的列。·選擇索引時，需要考慮資料列的基數。所謂基數，是指資料列所包含的不同的資料的個數。如果基數相對於資料表行數較高的話，索引的效果比較好。換句話說，就是指資料列的不同的值越多，索引效果越好。如果一個資料列只包含：0或者1兩個值的話，索引的用處就不大。如果值出現的幾率幾乎相等，那麼無論搜尋哪個值都可能得到一半的資料行。在這些情況下，最好根本不要使用索引。·索引值較短的值，所選擇的資料類型也儘可能的要小一些。譬如：如果TEXT能滿足需求的話，我們就不需要用MEDIUTEXT。·如果建立聯合索引的話，譬如對：t1,t2,t3建立聯合索引的話，同時也是對t1和t1,t2建立了索引。但是如果單獨指定t2、t3、t1t3、t2t3的值的話，都會用不到索引。
  sql語句中如何避免沒有使用索引的情況 首先先瞭解一下mysql最佳化器的工作原理：MySQL查詢最佳化工具最主要的目標是儘可能地使用索引，並且使用最嚴格的索引來消除儘可能多的資料行。所以當我們提交查詢語句時，如果最佳化器排除不合格資料的速度越快，那麼查詢的結果也會越快。·盡量比較資料類型相同的資料列。例如，INT與BIGINT是不同的。CHAR(10)被認為是CHAR(10)或VARCHAR(10)，但是與CHAR(12)或VARCHAR(12)不同。·盡量不要在where子句中對索引列使用運算式或者函數。如果你在索引列使用函數調用或者更複雜的算術運算式，MySQL就不會使用索引，因為它必須計算出每個資料行的運算式值。   WHERE mycol < 4 / 2使用索引
   WHERE mycol * 2 < 4沒有使用索引· 在使用LIKE時，盡量不要在開頭使用萬用字元。    WHEREcol_name LIKE ’%string%’    WHEREcol_name LIKE ’abc%’·不要使用類型轉換。如果某個索引列是int型，而在查詢時，賦值為字元型，將使用不了索引。    SELECT* FROM mytbl WHERE num_col = 1;使用索引
    SELECT *FROM mytbl WHERE num_col = ‘1’;沒有使用索引·在SELECT語句中可以使用STRAIGHT_JOIN關鍵字來重載最佳化器的選擇。    SELECTSTRAIGHT_JOIN ... FROM t1, t2, t3 ... ;
    SELECT ...FROM t1 STRAIGHT_JOIN t2 STRAIGHT_JOIN t3 ...

視圖

視圖是從一個或幾個基本表（或視圖）匯出的表。它與基本表不同，是一個虛表。資料庫只存放視圖的定義，而不存放視圖對應的資料，這些資料仍存放在原來的基本表中。所以基本表中的資料發生變化，從視圖中查詢出的資料也就隨之改變了。從這個意義上講，視圖就像一個視窗，透過它可以看到資料庫中自己感興趣的資料及其變化。
既然視圖的定義是基於基本表的，哪為什麼還要定義視圖呢？這是因為合理地使用視圖能夠帶來許多好處：
1、 視圖能簡化使用者操作
    視圖機制使使用者可以將注意力集中在所關心地資料上。如果這些資料不是直接來自基本表，則可以通過定義視圖，使資料庫看起來結構簡單、清晰，並且可以簡化使用者的的資料查詢操作。例如，那些定義了若干張表串連的視圖，就將表與表之間的串連操作對使用者隱藏起來了。換句話說，使用者所作的只是對一個虛表的簡單查詢，而這個虛表是怎樣得來的，使用者無需瞭解。
2、 視圖使使用者能以多種角度看待同一資料
    視圖機制能使不同的使用者以不同的方式看待同一資料，當許多不同種類的使用者共用同一個資料庫時，這種靈活性是非常必要的。
3、 視圖對重構資料庫提供了一定程度的邏輯獨立性
    資料的物理獨立性是指使用者的應用程式不依賴於資料庫的物理結構。資料的邏輯獨立性是指當資料庫重構造時，如增加新的關係或對原有的關係增加新的欄位，使用者的應用程式不會受影響。層次資料庫和網狀資料庫一般能較好地支援資料的物理獨立性，而對於邏輯獨立性則不能完全的支援。
在關許資料庫中，資料庫的重構造往往是不可避免的。重構資料庫最常見的是將一個基本表“垂直”地分成多個基本表。例如：將學生關係Student（Sno，Sname，Ssex，Sage，Sdept），
分為SX（Sno，Sname，Sage）和SY（Sno，Ssex，Sdept）兩個關係。這時原表Student為SX表和SY表自然串連的結果。如果建立一個視圖Student：
CREATE VIEW Student（Sno，Sname，Ssex，Sage，Sdept）
AS
SELECT SX.Sno，SX.Sname，SY.Ssex，SX.Sage，SY.Sdept
FROM SX，SY
WHERE SX.Sno=SY.Sno；
    這樣儘管資料庫的邏輯結構改變了（變為SX和SY兩個表了），但應用程式不必修改，因為建立立的視圖定義為使用者原來的關係，使使用者的外模式保持不變，使用者的應用程式通過視圖仍然能夠尋找資料。
當然，視圖只能在一定程度上提供資料的邏輯獨立，比如由於視圖的更新是有條件的，因此應用程式中修改資料的語句可能仍會因為基本表構造的改變而改變。
4、視圖能夠對機密資料提供安全保護
    有了視圖機制，就可以在設計資料庫應用系統時，對不同的使用者定義不同的視圖，使機密資料不出現在不應該看到這些資料的使用者視圖上。這樣視圖機制就自動提供了對機密資料的安全保護功能。例如，Student表涉及全校15個院系學生資料，可以在其上定義15個視圖，每個視圖只包含一個院系的學生資料，並只允許每個院系的主任查詢和修改本原系學生視圖。
5、適當的利用視圖可以更清晰地表達查詢
    例如經常需要執行這樣的查詢“對每個學生找出他獲得最高成績的課程號”。可以先定義一個視圖，求出每個同學獲得的最高成績：
CREATE VIEW VMGRADE
AS
SELECT Sno，MAX(Grade) Mgrade
FROM SC
GROUP BY Sno；
然後用如下的查詢陳述式完成查詢：
SELECT SC.Sno，Cno FROM SC，VMGRADE WHERE SC.Sno = VMGRADE.Sno AND SC.Grade = VMGRADE.Mgrade；

預存程序

是一組為了完成特定功能的SQL語句集，是利用SQL Server所提供的Transact-SQL語言所編寫的程式。經編譯後儲存在資料庫中,是在MySQL伺服器中儲存和執行的，可以減少用戶端和伺服器端的資料轉送。預存程序是資料庫中的一個重要對象，使用者通過指定預存程序的名字並給出參數（如果該預存程序帶有參數）來執行它。預存程序是由流量控制和SQL語句書寫的過程，這個過程經編譯和最佳化後儲存在資料庫伺服器中，預存程序可由應用程式通過一個調用來執行，而且允許使用者聲明變數。同時，預存程序可以接收和輸出參數、返回執行預存程序的狀態值，也可以嵌套調用。

為什麼要使用預存程序：

ü  預存程序是已經被認證的技術！

ü  預存程序會使系統運行更快！

ü  預存程序是可複用的組件！它是資料庫邏輯而不是應用程式。

ü  預存程序將被儲存！

預存程序的優點：

Ø  預存程序只在創造時進行編譯，以後每次執行預存程序都不需再重新編譯，而一般SQL語句每執行一次就編譯一次,所以使用預存程序可提高資料庫執行速度。

Ø  當對資料庫進行複雜操作時（如對多個表進行Update、Insert、Query、Delete時），可將此複雜操作用預存程序封裝起來與資料庫提供的交易處理結合一起使用。

Ø  預存程序可以重複使用，可減少資料庫開發人員的工作量。

Ø  安全性高，可設定只有某此使用者才具有對指定預存程序的使用權。

 

 

1、建立預存程序

其中，IN表示輸入參數；OUT表示輸出參數； INOUT表示既可以是輸入，也可以是輸出； param_name參數是預存程序的參數名稱；type參數指定預存程序的參數類型，該類型可以是MySQL資料庫的任意資料類型。

characteristic參數有多個取值。其取值說明如下：

LANGUAGE SQL：說明routine_body部分是由SQL語言的語句組成，這也是資料庫系統預設的語言。

[NOT] DETERMINISTIC：指明預存程序的執行結果是否是確定的。DETERMINISTIC表示結果是確定的。每次執行預存程序時，相同的輸入會得到相同的輸出。NOT DETERMINISTIC表示結果是非確定的，相同的輸入可能得到不同的輸出。預設情況下，結果是非確定的。

{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }：指明子程式使用SQL語句的限制。CONTAINS SQL表示子程式包含SQL語句，但不包含讀或寫資料的語句；NO SQL表示子程式中不包含SQL語句；READS SQL DATA表示子程式中包含讀資料的語句；MODIFIES SQL DATA表示子程式中包含寫資料的語句。預設情況下，系統會指定為CONTAINS SQL。

SQL SECURITY { DEFINER | INVOKER }：指明誰有許可權來執行。DEFINER表示只有定義者自己才能夠執行；INVOKER表示調用者可以執行。預設情況下，系統指定的許可權是DEFINER。

COMMENT 'string'：注釋資訊。

技巧：建立預存程序時，系統預設指定CONTAINS SQL，表示預存程序中使用了SQL語句。但是，如果預存程序中沒有使用SQL語句，最好設定為NO SQL。而且，預存程序中最好在COMMENT部分對預存程序進行簡單的注釋，以便以後在閱讀預存程序的代碼時更加方便。

2、調用預存程序

MySQL中使用CALL語句來調用預存程序。調用預存程序後，資料庫系統將執行預存程序中的語句。

然後，將結果返回給輸出值。CALL語句的基本文法形式如下：
CALL  sp_name([parameter[,…]]) ;

3、查看預存程序的定義

   MySQL中可以通過SHOW CREATE語句查看預存程序和函數的狀態。其基本文法形式如下：
SHOW CREATE { PROCEDURE |FUNCTION} sp_name ;
其中，“PROCEDURE”參數表示查詢預存程序；“FUNCTION”參數表示查詢儲存函數；“sp_name”參數表示預存程序或函數的名稱。

4、修改預存程序

修改預存程序和函數是指修改已經定義好的預存程序和函數。MySQL中通過ALTER PROCEDURE語句來修改預存程序。通過ALTER FUNCTION語句來修改儲存函數。本小節將詳細講解修改預存程序和函數的方法。
MySQL中修改預存程序和函數的語句的文法形式如下：
ALTER {PROCEDURE | FUNCTION} sp_name [characteristic ...]
characteristic:
{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'

5、刪除預存程序
刪除預存程序和函數指刪除資料庫中已經存在的預存程序和函數。MySQL中使用DROP PROCEDURE語句來刪除預存程序。通過DROP FUNCTION語句來刪除儲存函數。其基本形式如下：
DROP { PROCEDURE| FUNCTION } sp_name;

Linux 關於MySQL操作，From：http://blog.chinaunix.net/uid-26602509-id-3139913.html