1.基本語法

select [all|distinct]  字段1 [別名1],字段2 [別名2]...
from 表名 
where 條件 
group by 分組字段                                   
having 分組之后的條件 [asc|desc]
order by 排序
limit 數(shù)字或列表

2.數(shù)據(jù)準(zhǔn)備

CREATE TABLE product
(
    pid         INT PRIMARY KEY,
    pname       VARCHAR(20),
    price       DOUBLE,
    category_id VARCHAR(32)
);
# 插入數(shù)據(jù)
INSERT INTO product(pid,pname,price,category_id) VALUES(1,'聯(lián)想',5000,'c001');
INSERT INTO product(pid,pname,price,category_id) VALUES(2,'海爾',3000,'c001');
INSERT INTO product(pid,pname,price,category_id) VALUES(3,'雷神',5000,'c001');
INSERT INTO product(pid,pname,price,category_id) VALUES(4,'杰克瓊斯',800,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(5,'真維斯',200,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(6,'花花公子',440,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(7,'勁霸',2000,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(8,'香奈兒',800,'c003');
INSERT INTO product(pid,pname,price,category_id) VALUES(9,'相宜本草',200,'c003');
INSERT INTO product(pid,pname,price,category_id) VALUES(10,'面霸',5,'c003');
INSERT INTO product(pid,pname,price,category_id) VALUES(11,'好想你棗',56,'c004');
INSERT INTO product(pid,pname,price,category_id) VALUES(12,'香飄飄奶茶',1,'c005');
INSERT INTO product(pid,pname,price,category_id) VALUES(13,'海瀾之家',1,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(14,'小米',1999,'');
INSERT INTO product(pid,pname,price,category_id) VALUES(15,'華為',6999,'null');
INSERT INTO product(pid,pname,price,category_id) VALUES(16,'蜜雪冰城',1,null);

3.簡單查詢

# 1.查詢所有的商品.  
select *  from product;
# 2.查詢商品名和商品價(jià)格. 
select pname,price from product;
# 3.查詢結(jié)果是表達(dá)式（運(yùn)算查詢）：將所有商品的價(jià)格+10元進(jìn)行顯示.
select pname,price+10 from product;

4.條件查詢

(1)比較查詢

# 查詢商品名稱為“花花公子”的商品所有信息：
SELECT * FROM product WHERE pname = '花花公子';
# 查詢價(jià)格為800商品
SELECT * FROM product WHERE price = 800;
# 查詢價(jià)格不是800的所有商品
SELECT * FROM product WHERE price != 800;
SELECT * FROM product WHERE price <> 800;
SELECT * FROM product WHERE not (price = 800);
# 查詢商品價(jià)格大于60元的所有商品信息
SELECT * FROM product WHERE price > 60;
# 查詢商品價(jià)格小于等于800元的所有商品信息
SELECT * FROM product WHERE price <= 800;

(2).范圍查詢

# 查詢商品價(jià)格在200到1000之間所有商品
SELECT * FROM product WHERE price BETWEEN 200 AND 1000;
# 查詢商品價(jià)格是200或800的所有商品
SELECT * FROM product WHERE price IN (200,800);
# 查詢商品價(jià)格不是200或800的所有商品
SELECT * FROM product WHERE price NOT IN (200,800);

(3).邏輯查詢

# 查詢商品價(jià)格在200到1000之間所有商品
SELECT * FROM product WHERE price >= 200 AND price <=1000;
# 查詢商品價(jià)格是200或800的所有商品
SELECT * FROM product WHERE price = 200 OR price = 800;
# 查詢價(jià)格不是800的所有商品
SELECT * FROM product WHERE NOT(price = 800);

(4).模糊查詢

# 模糊 查詢: 關(guān)鍵字:like   %:0個(gè)或者多個(gè)字符   _:一個(gè)字符
# 需求1: 查詢商品名稱以'香'開頭的所有商品信息
select * from product where pname like '香%';
# 需求2: 查詢商品名稱包含'想'字的所有商品信息
select * from product where pname like '%想%';
# 需求3: 查詢商品名稱以'斯'字結(jié)尾的所有商品信息
select * from product where pname like '%斯';
# 需求4: 查詢商品名稱第三個(gè)字是'斯'的商品信息
select * from product where pname like '__斯%';
# 需求5: 查詢商品名稱以'香'開頭并且是三個(gè)字的所有商品信息
select * from product where pname like '香__';

(5).非空查詢

# 查詢沒有分類的商品
SELECT * FROM product WHERE category_id IS NULL;
# 查詢有分類的商品
SELECT * FROM product WHERE category_id IS NOT NULL;

5.排序查詢

• ASC 升序 (默認(rèn))
• DESC 降序

# 1.使用價(jià)格排序(降序)
SELECT * FROM product ORDER BY price DESC;
# 2.在價(jià)格排序(降序)的基礎(chǔ)上，以分類排序(降序)
SELECT * FROM product ORDER BY price DESC,category_id DESC;

6.聚合查詢

# 1、查詢商品的總條數(shù)
SELECT COUNT(*) FROM product;
# 2、查詢價(jià)格大于200商品的總條數(shù)
SELECT COUNT(*) FROM product WHERE price > 200;
# 3、查詢分類為'c001'的所有商品的總和
SELECT SUM(price) FROM product WHERE category_id = 'c001‘;
# 4、查詢分類為'c002'所有商品的平均價(jià)格
SELECT AVG(price) FROM product WHERE categ ory_id = 'c002‘;
# 5、查詢商品的最大價(jià)格和最小價(jià)格
SELECT MAX(price),MIN(price) FROM product;

7.分組查詢

#1 統(tǒng)計(jì)各個(gè)分類商品的個(gè)數(shù)
SELECT category_id ,COUNT(*) FROM product GROUP BY category_id ;
#2 統(tǒng)計(jì)各個(gè)分類商品的個(gè)數(shù),且只顯示個(gè)數(shù)大于1的信息
SELECT category_id ,COUNT(*) FROM product GROUP BY category_id HAVING COUNT(*) > 1;

having與where的區(qū)別:
1).having是在分組后對數(shù)據(jù)進(jìn)行過濾,where是在分組前對數(shù)據(jù)進(jìn)行過濾
2).having后面可以使用分組函數(shù)(統(tǒng)計(jì)函數(shù)),where后面不可以使用分組函數(shù)。文章來源地址http://www.zghlxwxcb.cn/news/detail-786103.html

8.分頁查詢

# 1、查詢商品的第1-5條
SELECT * FROM product limit 0,5

到了這里，關(guān)于學(xué)習(xí)筆記-數(shù)據(jù)查詢語言DQL的文章就介紹完了。如果您還想了解更多內(nèi)容，請?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！