MySQL DQL 数据查询 - 精简版

-- 创建数据库并使用
create database test1;
use test1;

-- 创建产品表
CREATE TABLE tb_product (
    pid         INT PRIMARY KEY auto_increment comment '产品id编号',
    pname       VARCHAR(20) comment '产品名称',
    price       int comment '产品价格',
    category_id VARCHAR(32) comment '产品所属分类编号'
);

-- 插入测试数据
INSERT INTO tb_product VALUES (1,'联想',5000,'c001');
INSERT INTO tb_product VALUES (2,'海尔',3000,'c001');
INSERT INTO tb_product VALUES (3,'雷神',5000,'c001');
INSERT INTO tb_product VALUES (4,'杰克琼斯',800,'c002');
INSERT INTO tb_product VALUES (5,'真维斯',200,'c002');
INSERT INTO tb_product VALUES (6,'花花公子',440,'c002');
INSERT INTO tb_product VALUES (7,'劲霸',2000,'c002');
INSERT INTO tb_product VALUES (8,'香奈儿',800,'c003');
INSERT INTO tb_product VALUES (9,'相宜本草',200,'c003');
INSERT INTO tb_product VALUES (10,'面霸',10,'c003');
INSERT INTO tb_product VALUES (11,'好想你枣',5,'c004');
INSERT INTO tb_product VALUES (12,'香飘飘奶茶',10,'c005');
INSERT INTO tb_product VALUES (13,'海澜之家',200,'c002');
INSERT INTO tb_product VALUES (14,'男人之家',200,'c003');
INSERT INTO tb_product VALUES (15,'男人之家',200);

-- 查询所有商品
select * from tb_product;

-- 查询指定字段
select pname,price from tb_product;

-- 起别名（as可以省略）
select pname as `商品名称`,price as `价格` from tb_product;

-- 运算查询：价格+10
select pname,price+10 as price from tb_product;

-- 去重查询（distinct）
select distinct category_id from tb_product;

-- 比较查询
select * from tb_product where pname='花花公子';
select * from tb_product where price=800;
select * from tb_product where price!=800;
select * from tb_product where price>60;

-- 范围查询（between...and）
select * from tb_product where price between 200 and 1000;

-- 集合查询（in）
select * from tb_product where price in (200,800);

-- 模糊查询（like）
select * from tb_product where pname like '香%';      -- 以'香'开头
select * from tb_product where pname like '_想%';     -- 第二个字为'想'

-- 空值查询
select * from tb_product where category_id is null;
select * from tb_product where category_id is not null;

-- 准备数据
create table tb_score(
    name varchar(32),
    math int
);
insert into tb_score values ('张三', 90), ('小明', 80), ('李四', 85), ('李辉', null);

-- count(*)：统计所有行（包含null）
select count(*) as total from tb_score;      -- 结果：4

-- count(字段)：统计非null行
select count(math) as total from tb_score;   -- 结果：3

-- max/min/avg/sum
select max(math) as total from tb_score;     -- 结果：90
select min(math) as total from tb_score;     -- 结果：80
select avg(math) as total from tb_score;     -- 结果：85.0000
select sum(math) as total from tb_score;     -- 结果：255

SELECT 分组字段, 聚合函数(统计字段)
FROM 表名
[WHERE 筛选条件]
GROUP BY 分组字段
[HAVING 分组后条件];

-- 准备数据
create table students(
    id int auto_increment,
    name varchar(20),
    age tinyint unsigned,
    gender enum('male', 'female'),
    score tinyint,
    primary key(id)
);
insert into students values (null, 'Tom', 23, 'male', 97);
insert into students values (null, 'Jack', 24, 'male', 88);
insert into students values (null, 'Rose', 26, 'male', 99);
insert into students values (null, 'Eric', 27, 'male', 59);
insert into students values (null, 'Jennifer', 22, 'female', 76);

-- 按性别分组，统计每组人数
select gender, count(*) from students group by gender;

-- 按性别分组，计算平均年龄
select gender, round(avg(age),0) from students group by gender;

-- group_concat：拼接分组后的字符串
select gender, count(*), group_concat(name) from students group by gender;

-- 分组后过滤：只显示人数大于2的性别
select gender, count(*) as num from students group by gender having num > 2;

-- 统计各分类商品个数，只显示个数大于1的
select category_id, count(*) as num from tb_product group by category_id having num > 1;

-- 组合使用：先where过滤，再group by分组，最后having过滤
select category_id, count(*) as num from tb_product where price > 3000 group by category_id having num > 1;

-- 价格降序
select * from tb_product order by price desc;

-- 价格升序
select * from tb_product order by price asc;

-- 先过滤再排序
select * from tb_product where price > 500 order by price desc;

-- 取前3条
select * from tb_product order by pid asc limit 3;

-- 每页3条，取第2页
select * from tb_product order by pid asc limit 3,3;

执行顺序：FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT