SELECT product_name, product_type
FROM product
WHERE product_type = '衣服';
法则:
SELECT *FROM <表名>; *是全部列
SELECT product_id AS id,
product_name AS name,
purchase_price AS "进货单价"
FROM product; as取别名,中文用引号
SELECT DISTINCT product_type
FROM product; DISTINCT删除列中重复的数据
SELECT product_name, product_type, sale_price
FROM product
WHERE NOT sale_price >= 1000;
And 真真真其余假;or有真则真;not转换
Select product_name,regist_date
From product
Where regist>’2009-4-28’; 日期通常应该使用引号, 并且应该使用日期格式
SELECT COUNT(*) FROM product; 全部数据的行数(包含NULL)
SELECT COUNT(purchase_price) FROM product; (列名)null之外的数据行数
SELECT COUNT(DISTINCT product_type) FROM product; 去除重复数据后的数据行数
SELECT SUM(sale_price), AVG(purchase_price), MAX(regist_date), MIN(regist_date)
FROM product;
SELECT product_type, COUNT(*)
FROM product
GROUP BY product_type; -- 按照商品种类统计数据行数
不能使用别名;where在group前
SELECT product_type, COUNT(*)
FROM product
GROUP BY product_type
HAVING COUNT(*) = 2;
-- 错误形式(因为product_name不包含在GROUP BY聚合键中)
SELECT product_type, COUNT(*)
FROM product
GROUP BY product_type
HAVING product_name = '圆珠笔'; HAVING的用法类似WHERE
SELECT product_id, product_name, sale_price, purchase_price
FROM product
ORDER BY sale_price DESC; 降序(默认升序)