SELECT檢索表數據,必須至少給出兩條信息--------想選擇什麼,以及從什麼地方選擇。
SELECT prod_name FROM products;
上述語句利用SELECT語句從products表中檢索一個名為prod_name的列
SELECT prod_id,prod_name,prod_price FROM products;
SELECT * FROM products;
SELECT vend_id FROM products;
select返回了14行,但是表中只有4個供應商,因為products表中列出了14個產品,如何檢索有不同值的列表
SELECT DISTINCT vend_id FROM products;
SELECT prod_name FROM products LIMIT 5;
SELECT prod_name FROM products LIMIT 5 , 5;
從第五行開始輸出5行
第一個數位開始位置,第二個數為檢索的行數
SELECT prod_name FROM products LIMIT 4 OFFSET 3;
從第三行開始,輸出四行
使用完全限定輸出表名
SELECT products.prod_name FROM products;
SELECT prod_name FROM products ORDER BY prod_name;
mysql對prod_name列以字母順序排序
SELECT prod_id,prod_price,prod_name FROM products ORDER BY prod_price , prod_name;
首先按照價格然後按照名稱排序 作用,當價格相同的時候,會再按照名稱來排序。
如果prod_price列中的數據所有值都是唯一的,則不會按照prod_name來排序
ORDER BY默認是按照升序的方式排列,如果進行降序排列,就必須標出 DESC;
SELECT prod_id , prod_price ,prod_name FROM products ORDER BY prod_price DESC;
SELECT prod_id , prod_price ,prod_name FROM products ORDER BY prod_price DESC, prod_name;
先按照價格從高到底排序,價格相同的再按照名稱升序排列。
與DESC相反的是ASC,ASC沒有多大作用,因為默認就是ASC升序排列。
SELECT prod_price FROM products ORDER BY prod_price DESC LIMIT 2;
找出兩個最貴的物品的值
SELECT prod_name ,prod_price FROM products WHERE prod_price = 2.50;
查詢價格等於2.50的商品名字和價格
操作符 說明 = 等於 <> 不等於 != 不等於 < 小於 > 大於 <= 小於等於 >= 大於等於 BETWEEN 在制定的兩個值之間SELECT prod_name ,prod_price FROM products WHERE prod_name = 'fuses';
SELECT prod_name,prod_price FROM products WHERE prod_price <10;
SELECT vend_id , prod_name FROM products WHERE vend_id <> 1003;
查詢供應商不是1003制造的所有產品
SELECT prod_name , prod_price FROM products WHERE prod_price BETWEEN 5 AND 10;
查詢價格在5和之間的商品名稱和價格
SELECT prod_id , prod_price ,prod_name FROM products WHERE vend_id =1003 AND prod_price <=10;
SELECT prod_id , prod_price ,prod_name FROM products WHERE vend_id =1002 OR vend_id = 1003;
SELECT prod_name , prod_price FROM products WHERE vend_id = 1002 OR vend_id = 1003 AND prod_price >=10;
從上面的結果來看,有兩個值的結果小於10,顯然返回的結果未按照預期的進行過濾,為什麼會這樣呢,原因是在於計算的次序,SQL在處理OR操作符前,優先處理AND操作符,當SQL看到上述where子句時,它理解為由供應商1003制造的任何價格都為10美元以上,包含10美元,或者由供應商1002提供的產品,不管價格。換句話說,由於AND的計算次序中優先級更高,操作符被錯誤的組合了。正確的操作方法:
SELECT prod_name , prod_price FROM products WHERE (vend_id = 1002 OR vend_id = 1003) AND prod_price >=10;
IN操作符來制定條件范圍,范圍中的每個條件都可以進行匹配。
SELECT prod_name , prod_price FROM products WHERE vend_id IN (1002,1003) ORDER BY prod_name;
IN和OR的用法有點類似,上面也可以這樣寫:
SELECT prod_name , prod_price FROM products WHERE vend_id=1002 OR vend_id=1003 ORDER BY prod_name;
where子句中的not操作符的用法只有一個,那就是否定後它之後所有的任何條件
SELECT prod_name , prod_price FROM products WHERE vend_id NOT IN (1002,1003) ORDER BY prod_name;
上面用到的所有查詢命令
SELECT prod_name FROM products; SELECT prod_id,prod_name,prod_price FROM products; SELECT * FROM products; SELECT vend_id FROM products; SELECT DISTINCT vend_id FROM products; SELECT prod_name FROM products LIMIT 5; SELECT prod_name FROM products LIMIT 5 , 5; SELECT prod_name FROM products LIMIT 1 , 1; SELECT prod_name FROM products LIMIT 4 OFFSET 3; SELECT products.prod_name FROM products; SELECT prod_name FROM products ORDER BY prod_name; SELECT prod_id,prod_price,prod_name FROM products ORDER BY prod_price , prod_name; SELECT prod_id , prod_price ,prod_name FROM products ORDER BY prod_price DESC; SELECT prod_id , prod_price ,prod_name FROM products ORDER BY prod_price DESC, prod_name; SELECT prod_price FROM products ORDER BY prod_price DESC LIMIT 2; SELECT prod_name ,prod_price FROM products WHERE prod_price = 2.50; SELECT prod_name ,prod_price FROM products WHERE prod_name = 'fuses'; SELECT prod_name,prod_price FROM products WHERE prod_price <10; SELECT vend_id , prod_name FROM products WHERE vend_id <> 1003; SELECT prod_name , prod_price FROM products WHERE prod_price BETWEEN 5 AND 10; SELECT prod_id , prod_price ,prod_name FROM products WHERE vend_id =1003 AND prod_price <=10; SELECT prod_id , prod_price ,prod_name FROM products WHERE vend_id =1002 OR vend_id = 1003; SELECT prod_name , prod_price FROM products WHERE vend_id = 1002 OR vend_id = 1003 AND prod_price >=10; SELECT prod_name , prod_price FROM products WHERE (vend_id = 1002 OR vend_id = 1003) AND prod_price >=10; SELECT prod_name , prod_price FROM products WHERE vend_id IN (1002,1003) ORDER BY prod_name; SELECT prod_name , prod_price FROM products WHERE vend_id=1002 OR vend_id=1003 ORDER BY prod_name; SELECT prod_name , prod_price FROM products WHERE vend_id NOT IN (1002,1003) ORDER BY prod_name;