MySQL基础之查询数据
一、DQL操作表中记录环境搭建
细节:查询只是查看数据,不会修改表中数据
创建商品表(goods2)包含:
商品名称(name),
商品价格(price),
商品销量(sales_volume),
商品生产日期(produced_date)
CREATE TABLE goods2 (
NAME VARCHAR(20),
price DOUBLE,
sales_valume INT,
produced_date DATE
);
INSERT INTO goods2 VALUES
('华为P40',5999,1000,'2020-08-20','手机'),
('小米11',4999,5000,'2020-12-28','手机'),
('红米K30',2999,22000,'2020-03-11','手机'),
('糯米',8.99,200,'2016-06-08','食物'),
('米糊',7.99,30,'2013-11-22','食物'),
('iPhone 12',6799,12000,'2020-10-28','手机'),
('DELL 7590',8799,300,'2019-06-18','电脑'),
('立白洗衣粉',12.9,39000,'2018-02-13','日用品'),
('立白洗衣粉',12.9,39000,'2018-02-13','日用品'),
(NULL,88,666,NULL,NULL),
('联想电脑',8799,700,'2017-03-13','电脑'),
('惠普电脑',8799,50,'2008-12-13','电脑');
二、简单查询
1、查询goods2表中的 name 和 price 列
SELECT NAME, price FROM goods2;
2、查询goods2表中所有字段
SELECT NAME, price, sales_volume, produced_date, category FROM goods2;
3、查询所有字段, 使用*代表所有列, 列就是字段
SELECT * FROM goods2;
4、去除重复查询: DISTINCT
SELECT DISTINCT NAME FROM goods2;
5、查询goods2表中的 name 和 price 列（取别名）
name列的别名为 商品名称，price列的别名为 价格
SELECT NAME AS 姓名, price AS 价格 FROM goods2;
5.1 取别名时AS关键字可以省略
SELECT NAME 姓名2, price 价格2 FROM goods2;
三、条件查询
6、查询price大于1000的商品
SELECT * FROM goods2 WHERE price>1000;
7、查询sales_volume小于5000的商品
SELECT * FROM goods2 WHERE sales_volume<5000;
8、查询price不等于6799的商品
SELECT * FROM goods2 WHERE price!=6799;
SELECT * FROM goods2 WHERE price<>6799;
SELECT * FROM goods2 WHERE price=6799;
四、逻辑运算符
9、查询price大于1000且sales_volume小于500的商品(两个条件同时满足)
SELECT * FROM goods2 WHERE price>1000 AND sales_volume<500;
10、查询price大于8000 或 sales_volume小于100的商品(两个条件其中一个满足)
SELECT * FROM goods2 WHERE price>8000 OR sales_volume<100;
11、查询name是华为P40和小米11和米糊的商品
SELECT * FROM goods2 WHERE NAME='华为P40' OR NAME='小米11' OR NAME='米糊';
12、in: 在…里面,只要是满足()里面的数据都可以
查询name是 华为P40 和 小米11 和 米糊 的商品
SELECT * FROM goods2 WHERE NAME IN ('华为P40', '小米11', '米糊');
13、扩展:查询name不是华为P40和小米11和米糊的商品
SELECT * FROM goods2 WHERE NAME NOT IN ('华为P40', '小米11', '米糊');
14、范围: BETWEEN 值1 AND 值2 – 表示从值1到值2范围，包头又包尾
查询price大于等于1000，且小于等于5000的商品
SELECT * FROM goods2 WHERE price>=1000 AND price<=5000;
SELECT * FROM goods2 WHERE price BETWEEN 1000 AND 5000;
细节: between 值1 and 值2, 小的写前,面大的写后面
15、扩展:查询商品名称是null的商品
SELECT * FROM goods2 WHERE NAME IS NULL;
16、扩展:查询商品名称不是null的商品
SELECT * FROM goods2 WHERE NAME IS NOT NULL;
五、模糊查询like
17、查询米开头的商品
SELECT * FROM goods2 WHERE NAME LIKE '米%';
18、查询商品名称中包含’米’字的商品
SELECT * FROM goods2 WHERE NAME LIKE '%米%';
19、扩展:查询名称第二个字为米的商品
SELECT * FROM goods2 WHERE NAME LIKE '_米%';
20、扩展:查询名称最后字为米的商品
SELECT * FROM goods2 WHERE NAME LIKE '%米';
六、查询排序
21、单列排序
查询所有数据,使用price升序排序
order by 默认是升序
SELECT * FROM goods2 ORDER BY price;
22、组合排序
查询所有数据,在price降序排序的基础上，如果price相同再以sales_volume降序排序
SELECT * FROM goods2 ORDER BY price DESC, sales_volume DESC;
七、聚合函数
23、聚合函数
SELECT 聚合函数(字段) FROM 表名;
查询商品个数, COUNT统计时会忽略NULL值
SELECT COUNT(NAME) FROM goods2;
SELECT COUNT(price) FROM goods2;
24、COUNT最好的处理方式, *表示所有列理解为统计行数,最准确
SELECT COUNT(*) FROM goods2;
25、扩展用法：统计price大于1000的总个数
SELECT COUNT(*) FROM goods2 WHERE price>1000;
26、查询所有商品总销量
总销量是把所有商品的销量加起来
SELECT SUM(sales_volume) FROM goods2;
27、查询销量最低的商品
SELECT MIN(sales_volume) FROM goods2;
28、查询销量最高的商品
SELECT MAX(sales_volume) FROM goods2;
29、查询商品平均价格
SELECT AVG(price) FROM goods2;
30、扩展:让小数显示指定的位数(2位)
ROUND(数据, 小数位数)
SELECT ROUND(AVG(price), 2) FROM goods2;
31、细节:如果不是数值类型(整数/小数)，那么聚合函数计算结果为0
SELECT AVG(NAME) FROM goods2;
八、分组查询
32、按商品类型分组
SELECT * FROM goods2 GROUP BY category;
33、分组后会返回每组的第一条数据
通常我们只获取分组字段
SELECT category FROM goods2 GROUP BY category;
34、查询每种类型的商品数量
SELECT category, COUNT(*) FROM goods2 GROUP BY category;
35、查询销量大于100的商品,按商品类型分组,统计每组的数量
SELECT category, COUNT(*) FROM goods2 WHERE sales_volume>100 GROUP BY category;
36、查询销量大于100的商品,按商品类型分组,统计每组的数量,并只显商品类型数量大于2的数据
SELECT category, COUNT(*) FROM goods2 WHERE sales_volume>100 GROUP BY category HAVING COUNT(*)>2;
九、分页查询
37、查询商品表中数据，跳过前面2条，显示3条
SELECT * FROM goods2 LIMIT 2, 3;
38、假设我们一每页显示3条记录的方式来分页，SQL语句如下：
第一页: 跳过0条, 获取3条
SELECT * FROM goods2 LIMIT 0, 3;
39、如果跳过的条数是0,可以省略
SELECT * FROM goods2 LIMIT 3;
40、第二页: 跳过3条, 获取3条
SELECT * FROM goods2 LIMIT 3, 3;
41、第三页: 跳过6条, 获取3条
SELECT * FROM goods2 LIMIT 6, 3;
42、第四页: 跳过9条, 获取3条
SELECT * FROM goods2 LIMIT 6, 3;
43、第四页: 跳过9条, 获取3条
SELECT * FROM goods2 LIMIT 9, 3;
十、小结
扩展查询的七个关键字顺序
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
LIMIT
-- 先查询表里面所有的数据并进行过滤。（此时用where关键字过滤的是表里面的数据，把name为null的给过滤掉了）
-- 然后进行分组，并统计每一组有多少条数据。
-- 利用HAVING关键字对查询的结果再次过滤 把个数大于等于2的展示出来。
-- 对having过滤之后的结果按照个数进行排序
-- 最后再跳过第一个，展示两条数据
SELECT category, COUNT(*) AS 个数 FROM goods2 WHERE NAME IS NOT NULL GROUP BY category HAVING 个数 >= 2 ORDER BY 个数 ASC LIMIT 1,2;