mysql基础--单表查询_单表查询代码-优快云博客

本文链接：https://blog.youkuaiyun.com/IT_1120/article/details/143593083

单表查询操作

1.准备数据

CREATE DATABASE IF NOT EXISTS db2 CHARSET=utf8;
USE db2;
# 建测试表
CREATE TABLE products
(
    id          INT PRIMARY KEY AUTO_INCREMENT, -- 商品ID
    name        VARCHAR(24)    NOT NULL,        -- 商品名称
    price       DECIMAL(10, 2) NOT NULL,        -- 商品价格
    score       DECIMAL(5, 2),                  -- 商品评分，可以为空
    is_self     VARCHAR(8),                     -- 是否自营
    category_id INT                             -- 商品类别ID
);

CREATE TABLE category
(
    id   INT PRIMARY KEY AUTO_INCREMENT, -- 商品类别ID
    name VARCHAR(24) NOT NULL            -- 类别名称
);

# 添加测试数据
INSERT INTO category
VALUES (1, '手机'),
       (2, '电脑'),
       (3, '美妆'),
       (4, '家居');

INSERT INTO products
VALUES (1, '华为Mate50', 5499.00, 9.70, '自营', 1),
       (2, '荣耀80', 2399.00, 9.50, '自营', 1),
       (3, '荣耀80', 2199.00, 9.30, '非自营', 1),
       (4, '红米note 11', 999.00, 9.00, '非自营', 1),
       (5, '联想小新14', 4199.00, 9.20, '自营', 2),
       (6, '惠普战66', 4499.90, 9.30, '自营', 2),
       (7, '苹果Air13', 6198.00, 9.10, '非自营', 2),
       (8, '华为MateBook14', 5599.00, 9.30, '非自营', 2),
       (9, '兰蔻小黑瓶', 1100.00, 9.60, '自营', 3),
       (10, '雅诗兰黛粉底液', 920.00, 9.40, '自营', 3),
       (11, '阿玛尼红管405', 350.00, NULL, '非自营', 3),
       (12, '迪奥996', 330.00, 9.70, '非自营', 3);

2.基础查询

知识点:

基础查询关键字: select:查什么    from: 从哪儿查

基础查询格式:  select [distinct] 字段名 | * from 表名;
			 []:可以省略
			  |: 或者
			  *: 对应表的所有字段名
	          distinct: 去除重复内容
	          as  : 可以给表或者字段起别名

示例:

-- 需求1: 查看所有商品
select * from products;
select
    id,
    name,
    price,
    score,
    is_self,
    category_id
from products;
-- 需求2: 查看所有商品的名称和价格
select name,price from products;
-- 需求3: 查看所有商品的名称和价格,要求给字段名起别名展示
select name as 姓名,price as 价格 from products;
select name  姓名,price  价格 from products;
-- 需求4: 查看所有商品的名称和价格,要求给表名起别名并使用
select products.name,products.price from products;
-- 如果给表起了表名,必须用别名调用字段
select p.name,p.price from products as p;
select name,price from products as p;
-- 需求5: 查看所有的分类编号,要求去重展示
select DISTINCT category_id from products;

3.条件查询

知识点:

条件查询关键字:  where 

条件查询基础格式: select 字段名 from 表名 where 条件;

		      比较运算符: >  <  >=  <=  !=  <>
		      
		      逻辑运算符: and  or  not
		      
		      范围 查询: 连续范围:between x and y       非连续范围: in(x,y)
		      
		      模糊 查询: 关键字:like   %:0个或者多个字符   _:一个字符
		      
		      非空 判断: 为空: is null    不为空:is not null

比较查询

# 1.比较运算符: >  <  >=  <=  !=  <>

-- 需求1: 查询所有'自营'的商品
select * from products where is_self = '自营';
-- 需求2: 查询评分在'9.50'(不含)以上的商品
select * from products where score > 9.50;
-- 需求3: 查询评分在'9.50'(含)以上的商品
select * from products where score >= 9.50;
-- 需求4: 查询价格在999(不含)以下的商品
select * from products where price < 999;
-- 需求5: 查询价格在999(含)以下的商品
select * from products where price <= 999;
-- 需求6: 查询评分不等于9.30的商品
select * from products where score != 9.3;
select * from products where score <> 9.3;

逻辑查询

-- and: 并且  or:或者  not:取反
-- 需求1: 查询自营商品中所有价格大于2000的商品信息
select * from products where is_self = '自营' and price > 2000;
-- 需求2: 查询商品评分在9.0(含)-9.5(含)之间的商品信息
select * from products where score >= 9 and score <= 9.5;
-- 需求3: 查询商品价格在1000(含)到3000(含)之间的商品信息
select * from products where price >= 1000 and price <= 3000;
-- 需求4: 查询价格是999或者2199或者2399的商品
select * from products where price = 999 or price = 2199 or price = 2399;
-- 需求5: 查询商品是'华为Mate50'或者'荣耀80'的商品
select * from products where name = '华为Mate50' or name = '荣耀80';
-- 需求6: 查询商品不是自营的商品
select * from products where not is_self = '自营';
select * from products where  is_self = '非自营';
-- 需求7: 查询商品不在1000到3000之间的商品
select * from products where not (price >= 1000 and price <= 3000);
select * from products where  price < 1000 or price > 3000;

范围查询

-- 需求1: 查询商品价格在1000(含)到3000(含)之间的商品信息
select * from products where  price BETWEEN 1000 and 3000;
-- 注意: 以下语法是错误的
select * from products where 1000 <= price <= 3000;
-- 需求2: 查询商品不在1000到3000之间的商品
select * from products where  price not BETWEEN 1000 and 3000;
-- 需求3: 查询价格是999或者2199或者2399的商品
select * from products where price in(999,2199,2399);
-- 需求4: 查询商品是'华为Mate50'或者'荣耀80'的商品
select * from products where name in('华为Mate50','荣耀80');

模糊查询

-- 关键字: like   符号 %:任意多个字符  _:任意1个字符
-- 需求1: 查询商品名称以'华'开头的商品信息
select * from products where name like '华%';
-- 需求2: 查询商品名称以'华'开头并且8个字符的商品信息
select * from products where name like '华_______';
-- 需求3: 查询商品名称以'66'结尾商品信息
select * from products where name like '%66';
-- 需求4: 查询商品名称中包含'兰'字的商品信息
select * from products where name like '%兰%';
-- 需求5: 查询商品名称中第3个字是'兰'字的商品信息
select * from products where name like '__兰%';

非空判断

/*
null在sql中代表空的,没有任何意义的意思
如果数据中有空字符串'',字符串'null',一定要注意,他们和sql中的null不是一回事!!!
*/
-- 需求1:查询未评分的商品信息
select * from products where score is null;
-- 注意: 以下方式是错误的
select * from products where score = null;
select * from products where score = '';
select * from products where score = 'null';
select * from products where score is 'null';

-- 为了方便演示null和'','null'的区别,可以插入部分测试数据
insert into products(name,price) values('拯救者Y9000','9999');
insert into products(name,price) values('null','99');
insert into products(name,price) values('',0);

-- 需求2:查询商品名称是'null'的商品信息
select * from products where name = 'null';
-- 需求3:查询商品名称是''的商品信息
select * from products where name = '';

4.排序查询

知识点:

排序查询关键字: order by

排序查询基础格式: select 字段名 from 表名 order by 排序字段名 asc|desc;
			asc : 升序(默认)
			desc: 降序
			
排序查询进阶格式: select 字段名 from 表名 order by 排序字段1名 asc|desc , 排序字段2名 asc|desc;
			注意: 如果order by后跟多个排序字段,先按照前面的字段排序,如果有相同值的情况再按照后面的排序规则排序

示例:

-- 示例1：查询所有商品，并按照评分从高到低进行排序
SELECT * FROM products ORDER BY score DESC;

-- 示例2：查询所有商品，先按照评分从高到低进行排序，评分相同的再按照价格从低到高排序
SELECT * FROM products ORDER BY score DESC, price;

5.聚合函数

知识点:

聚合函数: 又叫统计函数,也叫分组函数

常用聚合函数:  sum()  count()  avg()   max()  min()

聚合查询基础格式: select 聚合函数(字段名) from 表名;    注意: 此处没有分组默认整个表就是一个大的分组

注意: 聚合函数(字段名)会自动忽略null值,以后统计个数一般用count(*)统计因为它不会忽略null值

示例:

# 注意: 别名不建议用中文,以下仅仅为了演示
-- 示例1：统计当前商品一共有多少件
SELECT count(id) FROM products;
SELECT count(*) FROM products;

-- 示例2：对商品评分列进行计数、求最大、求最小、求和、求平均
SELECT
    COUNT(score) AS cnt,
    MAX(score) AS max_score,
    MIN(score) AS min_score,
    SUM(score) AS total_score,
    AVG(score) AS avg_score
FROM products;


-- 示例3：统计所有非自营商品评分的平均值
SELECT
    is_self,
    AVG(score)
FROM
    products
WHERE
    is_self = '非自营';

6.分组查询

知识点:

分组查询关键字: group by

分组查询基础格式: select 分组字段名,聚合函数(字段名) from 表名 group by 分组字段名;

注意: select后的字段名要么在group by后面出现过,要么写到聚合函数中,否则报错...sql_mode=only_full_group_by

分组查询进阶格式: select 分组字段名,聚合函数(字段名) from 表名 [where 非聚合条件] group by 分组字段名 [having 聚合条件];

where和having的区别? 
    书写顺序: where在group by 前,having在group by后
    执行顺序: where在group by 前,having在group by后
    分组函数: where后不能跟聚合条件,只能跟非聚合条件,having后可以使用聚合条件,也可以使用非聚合条件(不建议)
    应用场景: 建议大多数过滤数据都采用where,只有当遇到聚合条件的时候再使用having
    使用别名: where后不能使用别名,having后可以使用别名

示例:

-- 示例1：统计每个分类的商品数量
SELECT
    category_id,
    -- ② 再聚合：对每一组的id进行count计数
    COUNT(id) AS cnt
FROM products
-- ① 先分组：按照category_id进行分组
GROUP BY category_id;



-- 示例2：统计每个分类中自营和非自营商品的数量

SELECT
    category_id,
    is_self,
    COUNT(id) AS cnt
FROM products
GROUP BY category_id, is_self;



-- 示例3：统计每个分类商品的平均价格，并筛选出平均价格低于1000的分类

SELECT category_id,
       -- 再聚合
       AVG(price)
FROM products
-- 先分组
GROUP BY category_id
-- 对分组聚合的结果进行筛选
HAVING AVG(price) < 1000;

-- 示例4：统计自营商品中，每个分类的商品的平均价格，并筛选出平均价格高于2000的分类
SELECT category_id,
       AVG(price) AS avg_price
FROM products
WHERE is_self = '自营' -- where在分组之前对数据进行过滤
GROUP BY category_id
HAVING AVG(price) > 2000; -- having在分组聚合之后对数据进行过滤

SELECT category_id,
       AVG(price) AS avg_price
FROM products
WHERE is_self = '自营'
GROUP BY category_id
HAVING avg_price > 2000; -- MySQL在HAVING中可以使用聚合函数结果的别名！

7.limit查询

知识点:

分页查询关键字: limit

分页查询基础格式: select 字段名 from 表名 limit x,y;
			x: 起始索引,默认从0开始     x = (页数-1)*y
			y: 本次查询的条数
		
注意: limit能完成topN需求,但是不能考虑到并列情况,此问题可以使用后期学习的开窗函数解决

示例:

- 示例1：获取所有商品中，价格最高的商品信息
SELECT
    *
FROM products
ORDER BY price DESC
LIMIT 1; -- LIMIT 0, 1;


-- 示例2：将商品数据按照价格从低到高排序，然后获取第2页内容（每页3条）
SELECT
    *
FROM products
ORDER BY price
LIMIT 3, 3;

-- 示例3：当分页展示的数据不存在时，不报错，只不过查询不到任何数据
SELECT * FROM products LIMIT 20, 10;

8.SQL顺序

书写顺序: SELECT -> DISTINCT -> 聚合函数 -> FROM -> WHERE -> GROUP BY -> HAVING -> ORDER BY -> LIMIT
执行顺序: FROM -> WHERE -> GROUP BY -> 聚合函数 -> HAVING -> SELECT -> DISTINCT -> ORDER BY -> LIMIT