MySQL基础-单表查询
语法
select
[distinct] 列名1，列名2 as 别名...
from
数据表名
where
组前筛选
group by
分组字段
having
组后筛选
order by
排序的列 [asc | desc]
limit
起始索引，数据条数测试数据
# 建测试表
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);简单查询
-- ---------------------- 案例1: 简单查询 ----------------------
-- 1. 查看表中所有的数据.
select id, name, price, score, is_self, category_id from products;
select * from products; # 效果同上
-- 2. 查看指定列, 例如: 商品名, 价格
select name, price from products;
-- 3. 给列, 表起别名.
select name as 商品名, price as 商品单价, is_self from products as p;
select name as 商品名, price 商品单价, is_self from products p; # 细节1: as可以省略不写
select name as 商品名, price 商品单价, is_self from products p; # 细节2: 别名和关键字重名要加反引号``
select name as `desc`, price 商品单价, is_self from products p; # 细节2: 别名和关键字重名要加反引号``
-- 4. 去重查询. distinct, 查看所有商品的类别.
select distinct category_id from products;
select distinct category_id, is_self from products; # 细节: distinct后边有多列, 则是把多列作为1个整体来去重的.条件查询
条件运算符：
-
比较运算符：=、>、=、
select * from products where price > 4199; -- 查询价格大于4199的手机
select * from products where price != 4199; -- 查询价格不等于4199的手机信息
select * from products where price <> 4199; -- 查询价格不等于4199的手机信息
-
逻辑运算符：AND(并且)、OR(或者)、NOT(非、取反)
select * from products where price > 2000 and price
select * from products where price =4000; -- 查询价格在2000之下的手机信息和4000之上的手机信息
select * from products where not (price =4000); -- 查询价格在2000到4000的手机信息
not的意思是给条件取反
-
LIKE模糊查询
select * from products where name like '荣耀%' ; -- 查询名称以荣耀开头的手机信息
select * from products where name like '%mate%'; -- 查询名称包含mate的手机信息
select * from products where name like '%1_'; -- 查询名称倒数第二位是1的手机信息
-
范围查询
select * from products where price between 2000 and 4000;-- 查询价格在2000到4000的手机信息
select * from products where price in (2199, 2399); -- 查询价格2199, 2399的手机信息
-
空值判断：IS NULL 和 IS NOT NULL
注意：空值的判断一定不能使用 = 或 !=
select * from products where score is null; -- 查询score为null的手机信息
select * from products where score is not null; -- 查询score不为null的手机信息
常用的聚合函数
注意：聚合函数的计算会忽略NULL值
COUNT(col)：求指定列的总记录数
count：计数;
select count(*) from products; -- 查询总共有多少行
面试题：count(*)，count(1)，count(列) 区别
却别1：count(列)不会统计null值，count(*),count(1) 会统计null值
却别2：效率问题，count(主键列)> count(1)>count(*)>count(列)
MAX(col)：求指定列的最大值
maximum：最大值;
注意：如果统计的是字符串，则返回字符串长度最大的列
MIN(col)：求指定列的最小值
minimum：最小值;
SUM(col)：求指定列的和
sum：总和;
AVG(col)：求指定列的平均值
average：平均数;
注意：平均数小数比较多，需要保留特定位数，可以使用round（1234.12,2）
select round(avg(price),2) as round2_avg_price from table_name;
select max(price), min(price),round(avg(price),2),sum(price) from products; -- 依次查询最高价格，最低价格，价格平均值，价格总量
排序
- asc 升序排序
- desc 降序排序
select * from products order by score desc , price asc ; # 按score 降序，price 升序
select * from products order by price asc; --按价格升序排列
select * from products order by price desc; --按价格降序排列
select * from products order by category_id asc, score desc; --先按category_id升序排列，如果category_id相同则安score降序排列
分组查询
注意：分组查询的查询列，只能出现：分组字段，聚合函数
select
分组字段1，分组字段2, ... ... ,
聚合函数1，聚合函数2, ... ...
from table_name
group by 分组字段1，分组字段2, ... ...
select category_id,count(*) as '每组个数' from products group by category_id ; -- 分组查询，综管有三组，并显示出每组的个数
再强调下，select 后面跟的列只能是后面group by 用的列，与聚合函数。
select
count(*) as '每组个数',
round(avg(price),2) as '每组价格平均数',
max(price) as '每组最高价格',
min(price) as '每组最低价格'
from products group by category_id ;
-- 这个查询是不是就有意义了。
having 和 where 有什么区别
having 是对分组聚合之后的结果进行过滤，where是在对分组前的数据进行过滤
where -> group by ->聚合 -> habing
having 后面可以使用聚合函数（统计函数），where后面不可以使用聚合函数
select
count(*) as '每组个数',
round(avg(price),2) as '每组价格平均数',
max(price) as '每组最高价格',
min(price) as '每组最低价格'
from products group by category_id having max(price) < 3000; --这里的max(price) 可以写成 【每组最高价格】不能写成 【'每组最高价格'】
-- 查询出每组最高价格小于3000 的统计信息
limit
1、起始索引默认是从0开始，如果你写的代码起始索引为0，则可以不写
select * from products limit 2; -- 查询前两行数据
select * from products limit 1,1; -- 查询跳过第一行后的一个数据
这个分页自己学习试试，有什么不明白的可以留言
查询某也数据：limit （页数-1）*页条数，页条数
求总页数：
方法一：(总条数+页条数-1) / 页条数
方法二：
总行数%页条数>0 总行数/条数+1
总行数%页条数=0 总行数/条数
方法三：ceil (总条数/总行数)
常用函数
round（1234.1234,12，2） #四舍五入，保留2位小数
ceil（123.111） #向下取整，舍去小数