SQL实践学习(一)——单表查询

SQL实践练习(一)

打算新开一个SQL系列，分享一些自己在学习SQL时觉得有用的东西，会尽量贴合现实业务，用生活中的例子举例，实践出真知，这是这个系列的第一篇，只会涉及单独一个表的SQL操作——单表查询。这个系列会循序渐进，由浅到深，保证大家能够听懂。参考教程：MySQL教程 (yiibai.com)

SQL语法要点

• SQL语句要以分号【;】结尾
• SQL不区分关键字的大小写，但是插入到表中的数据是区分大小写的
• Windows系统默认不区分表名及字段名的大小写

题目背景

现在你是一个供应商的老板，你现在手里有一张记录仓库里产品数据的表product，你需要对数据进行观察

-- 创建数据库shop
CREATE DATABASE IF NOT EXISTS `shop` DEFAULT CHARACTER SET utf8 ;

表名和字段名的规范写法是加上``（键盘Tab上方 Esc下方），但是不加上也没事

-- 创建表product
CREATE TABLE `product`(
	`product_id` CHAR(4) NOT NULL, -- 商品 ID
	`product_name` VARCHAR(100) NOT NULL, -- 商品名
	`product_type` VARCHAR(32) NOT NULL, -- 商品类型
	`sale_price` INTEGER, -- 销售价格
	`purchase_price` INTEGER, -- 进货价格
	`regist_date` DATE, -- 产品注册日期
	PRIMARY KEY(product_id)-- 主键是 product_id
);

• INSERT INTO

-- 往商品表中插入8条数据
INSERT INTO product VALUES('0001', 'T 恤衫', '衣服', 1000, 500, '2009-09-20');
INSERT INTO product VALUES('0002', '打孔器', '办公用品', 500, 320, '2009-09-11');
INSERT INTO product VALUES('0003', '运动 T 恤', '衣服', 4000, 2800, NULL);
INSERT INTO product VALUES('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20');
INSERT INTO product VALUES('0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15');
INSERT INTO product VALUES('0006', '叉子', '厨房用具', 500, NULL, '2009-09-20');
INSERT INTO product VALUES('0007', '擦菜板', '厨房用具', 880, 790, '2008-04-28');
INSERT INTO product VALUES('0008', '圆珠笔', '办公用品', 100, NULL, '2009-11-11');

• WHERE 过滤（注意SQL语言里的【等于】不是==而是=，【不等于】不是 != 而是<>）

-- 选取【商品类型】为“衣服” 的元组/记录，最终结果只展示商品名字和商品类型
SELECT product_name, product_type -- 展示所有列用 SELECT *
FROM product
WHERE product_type = '衣服';

• AS 改别名

-- 改名，中文需要双引号
SELECT product_id     AS id,
       product_name   AS name,
       purchase_price AS "进货单价"
FROM product;

• DISTINCT 去重

-- 使用 DISTINCT 删除 product_type 列中重复的数据，展示出来的是去重后的数据
SELECT DISTINCT product_type
FROM product;

• IS NULL / IS NOT NULL

-- 找出 进货价格缺失（值为NULL）的记录
SELECT *
FROM product
WHERE purchase_price IS NULL;

• WHERE + 表达式

-- 找出盈利超过500元的商品（展示商品名字，售价和进价）
SELECT product_name, sale_price, purchase_price
FROM product
WHERE sale_price - purchase_price >= 500;

• OR AND

-- 找出【商品类型】为办公用品，且【注册日期】在9月11或者9月20的记录
SELECT product_name, product_type, regist_date
FROM product
WHERE product_type = '办公用品'                -- 括号是必要的
	AND (regist_date = '2009-09-11' OR regist_date = '2009-09-20');

• 聚合函数

-- ----------------聚合函数----------------------
-- COUNT(*)计算表中的行数（包含 NULL），结果就是8
SELECT COUNT(*)
FROM product;

-- COUNT(字段)计算 除NULL 以外数据的行数，结果就是6
SELECT COUNT(purchase_price)
FROM product;

-- 计算NULL值行数，也可以用COUNT(*)-COUNT(字段)
SELECT COUNT(*)
FROM product
WHERE purchase_price IS NULL

-- 计算销售单价和进货单价的合计值
SELECT SUM(sale_price), SUM(purchase_price) 
FROM product;

-- 计算销售单价和进货单价的平均值
SELECT AVG(sale_price), AVG(purchase_price)
FROM product;
-- 注意NULL值没有参与SUM和AVG运算，比如purchase_price列有两个缺失值，求平均时分母是6不是8

-- 找出最晚的注册日期和最早的注册日期（MAX 和 MIN 也可用于非数值型数据）
SELECT MAX(regist_date), MIN(regist_date)
FROM product;

-- 计算去除重复数据后的数据行数，即计算这一列有几种值
SELECT COUNT(DISTINCT product_type)
FROM product;

-- 是否使用 DISTINCT 时的销售价格总和
SELECT SUM(sale_price), SUM(DISTINCT sale_price)
FROM product;
-- ----------------------聚合函数-------------------

• 分组

-- 按照商品种类统计各类商品的数量 衣服 2 办公用品 2 厨房用具 4
SELECT product_type, COUNT(*) AS type_sum
FROM product
GROUP BY product_type -- 按照商品种类分组
ORDER BY type_sum; -- 升序排序

• 分组后过滤

-- 按照商品种类统计各类商品的数量，只需要数量为2的商品类型（衣服 办公用品）
SELECT product_type, COUNT(*)
FROM product
GROUP BY product_type -- 分组
HAVING COUNT(*) = 2; -- 过滤掉组内行数非2的组 

补充：一个SQL语句的执行顺序是

【$FROM\to WHERE\to GROUPBY\to HAVING\to SELECT\to ORDERBY$】

其中WHERE是分组前的过滤，HAVING是分组后的过滤

• LIKE模糊查询

-- 模糊查询，在product表里体现不出LIKE的作用，我们自己想象一个记录全班学生信息的表student
-- 找出班上所有姓王的学生
SELECT *
FROM student
WHERE name LIKE '王%'; -- %是通配符，代表所有任意字符

-- 找出班上名字为三个字，且第二个字是'小'的学生
SELECT *
FROM student
WHERE name LIKE '_小_'; -- _是占位符，代表任意一个字符

（注：如果要匹配的字符中恰好有%和_，使用换码字符\将通配符或者占位符转义为普通字符）

---

⭐这一篇先介绍最简单的单表查询，在这个系列的下一篇我们会再创建一张表shop_product，继续介绍子查询和多表查询，敬请期待！⭐