mysql常用語句練習-基於ecshop2.7.3資料庫(1)

ghostwu發表於2018-03-10
MySql資料庫

SELECT * FROM ecs_goods WHERE goods_id = 1;
SELECT goods_id, goods_name FROM ecs_goods WHERE goods_id = 1;
SELECT goods_id,cat_id,goods_name FROM ecs_goods WHERE cat_id != 3;
SELECT goods_id,cat_id,goods_name FROM ecs_goods WHERE cat_id <> 3;
SELECT goods_id,cat_id,goods_name,shop_price FROM ecs_goods WHERE shop_price > 3000;
SELECT goods_id,cat_id,goods_name,shop_price FROM ecs_goods WHERE shop_price <= 100;

SELECT goods_id,cat_id,goods_name,shop_price FROM ecs_goods WHERE cat_id = 4 OR cat_id = 3;
SELECT goods_id,cat_id,goods_name,shop_price FROM ecs_goods WHERE cat_id IN( 3, 4 );

SELECT goods_id,cat_id,goods_name,shop_price FROM ecs_goods WHERE shop_price BETWEEN 100 AND 858;

SELECT goods_id,cat_id,goods_name,shop_price FROM ecs_goods WHERE cat_id != 3 AND cat_id != 4
SELECT goods_id,cat_id,goods_name,shop_price FROM ecs_goods WHERE cat_id NOT IN( 3, 4 );
SELECT goods_id,cat_id,goods_name,shop_price FROM ecs_goods WHERE cat_id NOT BETWEEN 3 AND 4

SELECT goods_id,cat_id,goods_name,shop_price FROM ecs_goods WHERE ( shop_price > 100 AND shop_price < 300 )
OR ( shop_price > 1000 AND shop_price < 3000 );

SELECT goods_id,cat_id,goods_name,shop_price,click_count FROM ecs_goods WHERE (cat_id = 3 ) AND ( shop_price >= 1000 AND shop_price <= 3000 )
AND ( click_count > 5 )

 

SELECT * FROM ecs_goods WHERE cat_id IN ( 2, 3, 4, 5 )

SELECT goods_id,cat_id,goods_name FROM ecs_goods WHERE goods_name LIKE `諾基亞%`

 

 #把表中欄位num取值範圍為20~29之間的值變成20【注：ecshop2.7.3沒有這個ecs_mian1這個表，可以自己建立一個，他就一個欄位num, int型別，然後填充一些測試資料】

UPDATE ecs_mian1 SET num = ( FLOOR( num / 10 ) * 10 ) WHERE num >= 20 AND num <= 29

 #把表中欄位num取值範圍為30~39之間的值變成30

UPDATE ecs_mian1 SET num = ( FLOOR( num / 10 ) * 10 ) WHERE num BETWEEN 30 AND 39

 

#以”諾基亞”開頭的商品

SELECT goods_id, goods_name FROM ecs_goods WHERE goods_name LIKE `諾基亞%`;

#擷取”諾基亞”後面的商品名稱，並用”小米”開始 連線起來
SELECT goods_id, CONCAT( `小米`, SUBSTRING( goods_name, 4 ) ) FROM ecs_goods WHERE goods_name LIKE `諾基亞%`;

#擷取”諾基亞”後面的商品名稱，並用”小米”開始 連線起來後更新
UPDATE ecs_goods SET goods_name = CONCAT( `小米`, SUBSTRING( goods_name, 4 ) ) WHERE goods_name LIKE `諾基亞%`

#以上面的語句相反
UPDATE ecs_goods SET goods_name = CONCAT( `諾基亞`, SUBSTRING( goods_name, 4 ) ) WHERE goods_name LIKE `小米%`

 

關於NULL:

SELECT NULL = NULL
SELECT NULL != NULL

#建表
CREATE TABLE ghost_user_info(
id INT( 3 ),
user_name VARCHAR( 20 )
)CHARSET utf8 ENGINE MYISAM;

#插入測試資料
INSERT INTO ghost_user_info VALUES( 1, `張三` );
INSERT INTO ghost_user_info VALUES( 2, NULL );

#user_name不等於NULL的記錄，不能這樣寫
SELECT * FROM ghost_user_info WHERE user_name != NULL

#user_name不等於NULL的記錄，應該這樣寫
SELECT * FROM ghost_user_info WHERE user_name IS NOT NULL

#同樣的，等於NULL的記錄應該是 IS NULL而不是 = NULL
SELECT * FROM ghost_user_info WHERE user_name = NULL
SELECT * FROM ghost_user_info WHERE user_name IS NULL

 #統計函式

SELECT AVG( shop_price ) FROM ecs_goods;
SELECT MAX( shop_price ) FROM ecs_goods;
SELECT MIN( shop_price ) FROM ecs_goods;
SELECT COUNT(*) FROM ecs_goods;
SELECT SUM( shop_price ) / COUNT(*) FROM ecs_goods;

#積壓貨款
SELECT SUM( shop_price * goods_number ) FROM ecs_goods;

 #統計每個分類的商品平均價格, group比較耗費資源（先按cat_id排序，再統計）

SELECT cat_id,AVG( shop_price ) FROM ecs_goods GROUP BY cat_id;

#查詢每個分類下，商品的數量
SELECT cat_id, COUNT(*) FROM ecs_goods GROUP BY cat_id;

#每個分類下最貴的產品
SELECT cat_id, MAX(shop_price) FROM ecs_goods GROUP BY cat_id;

 

#本店價格比市場價格小200以上的商品
SELECT goods_id, goods_name, market_price – shop_price FROM ecs_goods WHERE market_price – shop_price > 200

#下面的語句，報錯（Unknown column `discount_price` in `where clause` ）,discount_price這個別名是結果集中(存在記憶體中）的，where後面的欄位需要是磁碟表中的欄位

SELECT goods_id, goods_name, ( market_price – shop_price ) AS discount_price FROM ecs_goods WHERE discount_price > 200

#可以用having關鍵字過濾查詢出來的結果集
SELECT goods_id, goods_name, ( market_price – shop_price ) AS discount_price FROM ecs_goods WHERE 1 HAVING discount_price > 200

#多列排序

SELECT goods_id, cat_id, goods_name, shop_price FROM ecs_goods ORDER BY cat_id ASC, shop_price DESC;

 

#查詢最新的商品1
SELECT goods_id, goods_name, cat_id FROM ecs_goods ORDER BY goods_id DESC LIMIT 0,1
#下面兩句組合，等於後面的where子查詢
SELECT goods_Id, goods_name,cat_id FROM ecs_goods WHERE goods_id = 32
SELECT MAX( goods_id ) FROM ecs_goods;
#(where子查詢)查詢最新的商品2
SELECT goods_Id, goods_name,cat_id FROM ecs_goods WHERE goods_id = ( SELECT MAX( goods_id ) FROM ecs_goods );

 

#from子查詢：每一個分類下，最大的產品id( 最新的產品 )
SELECT goods_id, goods_name, cat_id FROM ecs_goods ORDER BY cat_id ASC, goods_id DESC
#把上面的語句 當前一個臨時的表，放在from後面 組成一個from子查詢
SELECT goods_id, goods_name, cat_id FROM ( SELECT goods_id, goods_name, cat_id FROM ecs_goods ORDER BY cat_id ASC, goods_id DESC ) AS tmp GROUP BY cat_id

 

#查詢有商品的分類

SELECT * FROM ecs_category c WHERE EXISTS( SELECT * FROM ecs_goods g WHERE g.cat_id = c.cat_id );

 #查詢沒有商品的分類

SELECT * FROM ecs_category c WHERE not EXISTS( SELECT * FROM ecs_goods g WHERE g.cat_id = c.cat_id );

 

參考：

MySQL EXISTS 和 NOT EXISTS 子查詢語法如下：

  • SELECT … FROM table WHERE EXISTS (subquery)

該語法可以理解為：將主查詢的資料，放到子查詢中做條件驗證，根據驗證結果（TRUE 或 FALSE）來決定主查詢的資料結果是否得以保留。

 

 

#查出主隊與客隊在2016-06-01~2016-07-01的名稱以及比賽結果

create table m(
     mid int,
     hid int,
     gid int,
     mres varchar(10),
     matime date
)engine myisam charset utf8;

create table t (
     tid int,
     tname varchar(20)
)engine myisam charset utf8;
 
 
insert into m
     values
     (1,1,2,`2:0`,`2006-05-21`),
     (2,2,3,`1:2`,`2006-06-21`),
     (3,3,1,`2:5`,`2006-06-25`),
     (4,2,1,`3:2`,`2006-07-21`);

 
insert into t
     values
     (1,`國安`),
     (2,`申花`),
     (3,`布林聯隊`);

SELECT m.*, t1.`tname`, t2.`tname` FROM m INNER JOIN t AS t1
ON m.`hid` = t1.`tid` INNER JOIN t AS t2 ON m.`gid` = t2.`tid`
WHERE m.`matime` BETWEEN `2006-06-01` AND `2006-07-01`

 

相關文章