mysql查詢語句

標籤：order by   ble   操作   int   ike   strong   class   time()   當前日期   

基本查詢

去除重複記錄 >SELECT DISTINCT vend_id FROM products;

分頁 >SELECT * FROM products LIMIT 5;

>SELECT * FROM products LIMIT 0,5;

>SELECT * FROM products LIMIT 5,5;

 

排序(降序) >SELECT * FROM products ORDER BY prod_price DESC;

排序(升序) >SELECT * FROM products ORDER BY prod_price [ASC];

多列排序 >SELECT * FROM products ORDER BY prod_price ASC,prod_name ASC;

 

過濾查詢 查詢產品價格在2到10之間的產品

>SELECT * FROM products WHERE prod_price >= 2 AND prod_price <= 10;

>SELECT * FROM products WHERE prod_price BETWEEN 2 AND 10;

查詢產品價格不等於2.5的所有產品

>SELECT * FROM products WHERE prod_price <> 2.5; >SELECT * FROM products WHERE prod_price != 2.5;

查詢沒有電子郵件資訊的客戶

>SELECT * FROM customers WHERE cust_email IS NULL;

查詢有電子郵件資訊的客戶

>SELECT * FROM customers WHERE cust_email IS NOT NULL;

 

過濾查詢

查詢由供應商1001和1003製造並且價格在10元以上的產品

>SELECT * FROM products WHERE vend_id = ‘1001‘ OR vend_id = ‘1003‘ AND prod_price > 10;

>SELECT * FROM products WHERE (vend_id = ‘1001‘ OR vend_id = ‘1003‘) AND prod_price > 10;

>SELECT * FROM products WHERE vend_id IN(‘1001‘,‘1003‘) AND prod_price > 10;

查詢不是由供應商1001和1003製造的產品

> SELECT * FROM products WHERE vend_id NOT IN(‘1001’,‘1003’) ;

模糊查詢

“_”萬用字元代表一個字元 “%”萬用字元代表0個或一個或任意多個字元 查詢產品名稱中以jet開頭的產品

> SELECT * FROM products WHERE prod_name LIKE ‘jet%‘;

查詢_ ton anvil產品

> SELECT * FROM products WHERE prod_name LIKE ‘_ ton anvil‘

? 不要過度使用LIKE萬用字元，如果其他動作符可以完成就使用其他動作符

? 萬用字元搜尋使用的時間比其他搜尋的時間長

? 如果確實需要使用萬用字元，除非絕對有必要，否則不要把萬用字元放到WHERE子句的開始處，把通配 符放到搜尋模式的開始處，搜尋起來是最慢的 

 

更多基本查詢

列的別名

> SELECT vend_id AS ‘供應商編號‘ FROM products;

算數運算

>SELECT quantity,item_price,quantity * item_price AS ‘總價‘ FROM orderitems;

 

文本處理函數

left()返回左邊指定長度的字元

> SELECT prod_name,LEFT(prod_name,2) FROM products;

right()返回右邊指定長度的字元

>SELECT prod_name,RIGHT(prod_name,5) FROM products;

length()返回字串的長度

> SELECT prod_name,LENGTH(prod_name) FROM products;

lower()將字串轉換為小寫

> SELECT prod_name,LOWER(prod_name) FROM products;

upper()將字串轉換為大寫

> SELECT prod_name,UPPER(prod_name) FROM products;

 

文本處理函數

ltrim()去掉字串左邊的空格

> SELECT prod_name,LTRIM(prod_name) FROM products;

rtrim()去掉串右邊的空格

>SELECT prod_name,RTRIM(prod_name) FROM products;

trim()去掉左右兩邊的空格

>SELECT prod_name,TRIM(prod_name) FROM products;

字串串連

>SELECT CONCAT(‘I love ‘,cust_name) AS ‘Message‘ FROM customers;

日期時間函數

函數	用途	函數	用途
curDate()	返回當前日期	curTime()	返回目前時間
now()	返回當前日期和時間	date()	返回日期時間的的日期部分
time()	返回日期時間的時間部分	day()	返回日期的天數部分
dayofweek()	返回一個日期對應星期數	hour()	返回時間的小時部分
minute()	返回時間的分鐘部分	month()	返回日期的月份部分
second()	返回時間的秒部分	year()	返回日期的年份部分
datediff()	計算兩個日期之差	addDate()	添加一個日期（天數）

 

 

 

 

 

 

 

 

 

日期和時間函數

擷取2005-9-1日的訂單

>SELECT * FROM orders WHERE order_date = ‘2005-09-01‘;

>SELECT * FROM orders WHERE DATE(order_date) = ‘2005-09-01‘;

擷取2005年9月的訂單

>SELECT * FROM orders WHERE order_date >= ‘2005-09-01‘ AND order_date <= ‘2005-09-30‘;

>SELECT * FROM orders WHERE YEAR(order_date) = ‘2005‘ AND MONTH(order_date) = ‘9‘;

彙總函式 ? min() ? max() ? count() ? sum() ? avg() 彙總函式常用於統計資料使用 彙總函式統計時忽略值為NULL的記錄

 

彙總函式

查詢商品價格最高的產品

> SELECT MAX(prod_price) FROM products;

查詢商品價格最低的產品

> SELECT MIN(prod_price) FROM products;

查詢商品價格總和

> SELECT SUM(prod_price) FROM products;

查詢商品平均價格

> SELECT AVG(prod_price) FROM products;

查詢客戶數量

>SELECT COUNT(*) FROM customers;

>SELECT COUNT(cust_email) FROM customers;

 

分組統計

擷取每個供應商提供的產品數量

> SELECT vend_id,COUNT(*) FROM products GROUP BY vend_id;

擷取提供產品數量大於2的供應商

> SELECT vend_id,COUNT(*) FROM products GROUP BY vend_id HAVING COUNT(*) > 2;

HAVING語句用於GROUP BY的過濾 WHERE用於分組前過濾

 

擷取產品提供產品數量大於等於2併產品價格大於10的供應商

> SELECT vend_id,COUNT(*) FROM products WHERE prod_price > 10 GROUP BY vend_id HAVING COUNT(*) >= 2;

 

查詢語句順序 1. SELECT 2. FROM 3. WHERE 4. GROUP BY 5. HAVING 6. ORDER BY 7. LIMIT

 

子查詢

子查詢指的是嵌套在查詢中的查詢

擷取訂購商品編號為TNT2的客戶名

1.從訂單詳情表中擷取訂單編號：

> SELECT order_num FROM orderitems WHERE prod_id = "TNT2";

2.根據訂單編號擷取下訂單的客戶ID：

> SELECT cust_id FROM orders WHERE order_num IN (‘20005‘,‘20007‘);

3.根據客戶ID擷取客戶的姓名：

> SELECT cust_name FROM customers WHERE cust_id IN (‘10001‘,‘10004‘);

 

子查詢

SELECT cust_name FROM customers WHERE cust_id IN (SELECT cust_id FROM orders WHERE order_num IN(SELECT order_num FROM orderitems WHERE prod_id = "TNT2") );

子查詢

擷取每個客戶下的訂單數量

> SELECT cust_id,cust_name, (SELECT COUNT(*) FROM orders WHERE orders.cust_id = customers.cust_id) FROM customers；

 

等值查詢 > SELECT ts.id AS ‘stuid‘,stu_name,tc.id AS ‘class_id‘,class_name FROM t_student AS ts,t_class AS tc WHERE ts.class_id = tc.id 內聯結查詢 > SELECT ts.id AS ‘stuid‘,stu_name,tc.id AS ‘class_id‘,class_name FROM t_student AS ts INNER JOIN t_class AS tc ON ts.class_id = tc.id

 

左(外)聯結查詢

> SELECT ts.id AS ‘stuid‘,stu_name,tc.id AS ‘class_id‘,class_name FROM t_student AS ts LEFT JOIN t_class AS tc ON ts.class_id = tc.id

 

右(外)聯結查詢

>SELECT ts.id AS ‘stuid‘,stu_name,tc.id AS ‘class_id‘,class_name FROM t_student AS ts RIGHT JOIN t_class AS tc ON ts.class_id = tc.id

 

組合查詢

查詢所有的使用者和公司，並在一個結果集中顯示

> SELECT id,name,createtime FROM t_user UNION SELECT id,name,createtime FROM t_company;

組合查詢

查詢所有的使用者和公司，並在一個結果集中按照建立時間(createtime)降序顯示

> select id,name,createtime from t_user union select id,name,createtime from t_company order by createtime desc;

 

組合查詢

? union必須由兩條或兩條以上的select語句組成，語句之間使用union分割

? union的每個查詢必須包含相同的列，運算式或彙總函式

? 列的資料類型必須相容：類型不必完全相同，但是必須是相互可以轉換的

? union查詢會自動去除重複的行，如果不需要此特性，可以使用union all

? 對union結果進行排序，order by語句必須在最後一條select語句之後

>SELECT vend_id FROM vendors UNION ALL SELECT vend_id FROM products;

 

資料庫引擎： ? InnoDB：可靠的交易處理引擎，不支援全文檢索搜尋 ? MyISAM：是一個效能極高的引擎，支援全文檢索搜尋，但不支援交易處理 ? MEMORY：功能等同於MyISAM引擎，但由於資料存放區在記憶體中，所以速度快 > create table xxx ( … )engine=innodb;

 

根據查詢記錄添加到表： >insert into t_tableb(val) select val from t_tablea;

 

mysql查詢語句