0基础学习python+大数据开发——MYSQL-day02

一、DQL数据查询语言

1、 SQL查询的5个子句

select * from 数据表 [where条件子句] [group by分组子句] [having条件子句] [order by排序子句] [limit限制查询子句];

特别注意，五子句如果有多个子句同时出现在一条SQL查询中的话，必须严格按照以上顺

序，否则会出现报错

 2、准备数据集

        在正式学习之前先进行一些准备工作，让大家包括我自己更加直观的学习

-- 创建一个shopping数据库
create database if not exists shopping default charset=utf8;


-- 选择shopping数据库
use shopping;


--在shopping数据库中创建一个product表
create table product(
    id int auto_increment primary key,
    name varchar(20),
    price decimal(11,2),
    score float(5,1),
    is_self enum('官方自营','专卖店'),
    category_id int
) default charset=utf8;

        插入数据

insert into product values(null,'小米15',4999.00,9.9,'官方自营',1001);
insert into product values(null,'小米15',4799.00,9.7,'专卖店',1001);
insert into product values(null,'华为mate80',5999.00,9.8,'官方自营',1001);
insert into product values(null,'华为mate80',5699.00,9.4,'专卖店',1001);
insert into product values(null,'联想小新',5999.00,9.9,'官方自营',1002);
insert into product values(null,'联想小新',5699.00,9.4,'专卖店',1002);
insert into product values(null,'玩家国度',11099.00,9.9,'官方自营',1002);
insert into product values(null,'玩家国度',10999.00,9.7,'专卖店',1002);
insert into product values(null,'欧莱雅面膜',499.00,9.9,'官方自营',1003);
insert into product values(null,'欧莱雅面膜',429.00,9.8,'专卖店',1003);
insert into product values(null,'兰蔻小黑瓶',999.00,9.9,'官方自营',1003);
insert into product values(null,'兰蔻小黑瓶',929.00,9.7,'专卖店',1003);

3、select查询

#根据某些条件从某个表中查询指定字段内容
格式：select *|列名,列名,... from 表 where 条件

先做了解

4、简单查询

1、查询所选表中的所有数据，*代表所有

         示例：查询所有的商品。根据准备的数据集进行操作

select * from product;

2、查询所选表中指定列（字段）的数据

        语法：select 列1,列2 from 表名;

         查询商品名和商品价格

select name,price from product;

 3、查询所选表中的结果带有表达式（运算查询）

         将所有商品的价格+100元进行显示

select id,price+100 from product;

4、查询所选表起别名（后期多表查询时使用较多）

        语法：select 列1，列2 from 表名 as 别名；

select * from product as p;
-- 实际应用中as可以省略不写
select* from product p;

select g.id,g.name from product p;

5、字段别名(替换所选列的字段名字）

        语法：select 字段 as 别名 from 表名；

-- select id as g_id,name as g_name from product;

select id as `编号`, name as `商品名称` from product;

注意：像中文这样的特殊字符与关键字一样需要使用反撇号``括起来

5、where条件查询--子句

作用：筛选出数据表中满足条件的数据

        基本语法

select * from 表名 where 条件;

5.1比较查询

作用：用来对两个数据进行比较，比较的结果是成立（True）或不成立（False）。

        比较成立的显示出来，反之

 比较运算符：=(等于)、>(大于)、<(小于）、>=(大于等于)、<=(小于等于)、!=(不等于)、<>(不等于)

        示例：

#查询商品价格大于3000的商品所有信息：
select * from product where price > 3000;

#查询商品名称为“小米15”的商品所有信息：
select * from product where name ='小米15';

5.2逻辑运算符

作用：连接多个条件，表示多个条件之间的与、或、非关系。

逻辑运算符：

and：并且，两边都成立才成立

or：或者，两边由任何一边成立即成立

not：非，取反(成立表不成立，不成立变成立)

        示例：

#查询商品价格大于等于3000并且小于6000的商品所有信息：
select * from product where price >=3000 and price < 6000;

#查询商品分类编号为1001或者商品价格小于500的商品所有信息：
select * from product where category_id = 1001 or price < 500;

#查询商品店铺为非官方自营的商品所有信息：
select * from product where not(is_self = '官方自营');

5.3like模糊查询操作

作用：对数据模糊查询，查询含有某些内容的数据。

        语法：select * from 表名 where 字段 like '匹配内容'；

模糊查询运算符:

%（百分号）：任意多个任意字符，没有字符和数量限制

_（下划线）任意一个字符没有字符但有数量限制，只能为1

        示例：

#查询所有商品中，商品名以‘小米’开头的商品所有信息
select * from product where name like '小米%';

#查询所有商品中，商品名以‘面膜’结尾的商品所有信息
select * from product where name like '%面膜';

#查询所有商品中，商品名中带有‘小’的商品所有信息
select * from product where name like '%小%';

#查询所有商品中，商品名为4个字符的商品所有信息
select * from product where name like '____';

5.4范围查询

作用：查询指定范围内的数据。

范围查询运算符

between ... and ... 表示在一个连续的范围内查询
in 表示在一个非连续的范围内查询

        示例：

#查询评分在9.4 - 9.7之间的商品信息
select * from product where score between 9.4 and 9.7;
-- 等价于
-- select * from product where score >= 9.4 and score <=9.7;

#查询评分在9.4 和 9.7的商品信息
select * from product where score in(9.4,9.7);
-- 等价于
-- select * from product where score = 9.4 or score = 9.7;

重点：因为计算机底层是二进制，==>会将9.4和9.7先转化为二进制，底层进行二进制与二进制的比较，所以在使用between and 和 in时，尽量不要判断小数。

注意：有的版本可以比较，有的不行

5.5空值与非空值查询

作用：筛选出值为NULL的数据。

空值与非空值查询运算符：

is null：值是null，则为true

is not null：值不是null，则为true

注意事项：空值的判断一定不能用=或!=

        示例：

#查询score字段为null的商品信息
select * from product where score is null;

#查询score字段不为null的商品信息
select * from product where score is not null;

6、聚合函数（扩展）

定义：聚合函数又叫组函数、统计函数，用来对表中的指定列数据进行统计计算。

常用的聚合函数：

count():求指定列的总记录数；

max()：求指定列的最大值；

min()：求指定列的最小值；

sum()：求指定列的和；

avg()：求指定列的平均值

语法：select 聚合函数(字段)...from 表名;

注意：

聚合函数的计算会忽略 NULL 值。

统计查询结果的行数也可以使用count(*)。

        示例：

-- 聚合函数的使用
#返回 product商品表中总记录数，==>count()建议统计主键列
select count(id) as cnt from product;
select count(*) as cnt from product;

#返回评分中的最大值
select max(score) as max_value from product;

#返回评分中的最小值
select min(score) as min_value from product;

#返回购买福所有商品的平均价格
select avg(price) as avg_price from product;

#返回购买福所有商品的总价格
select sum(price) as total_price from product;

7、group by分组查询--子句

GROUP BY分组聚合分为两步：先分组，再聚合。

先分组：把表数据按照指定列的值进行划分，值相同的数据划分到同一个组；

再聚合：分别针对每一组数据使用聚合函数进行统计。

语法：select 分组字段...，count()...from 表名 group by 分组字段...；

        示例：

-- group by 分组字段 ==> 经常和聚合函数结合一起使用
#1、分组怎么使用=>分组操作还有去重功能
select category_id from product group by category_id;

#2、分组 + 聚合，
# 求每个分类的商品数量
select category_id,count(*) as cnt from product group by category_id;

#3、多字段分组 + 聚合，
# 求每个分类中官方自营和专卖店商品的数量
#需求：官方自营和1001组合，专卖店和1001组合，...官方自营和1003组合，专卖店和1003组合。
select is_self, category_id, count(*) as cnt from product group by is_self, category_id;

注意：分组之后，SELECT后面查询的字段，必须满足下列2种情况之一：

情况1：字段是分组字段【即在GROUP BY之后出现】

情况2：字段被聚合函数统计【聚合函数(字段)】

8、having过滤--子句

作用：针对group by分组结果进一步筛选，虽然和where类似，都是进行数据筛选，但是having发生分组聚合之后；where发生在分组聚合之前。

having和where的区别：

having是对分组聚合之后的结果进行过滤，where是对分组前的数据进行过滤。

优先级：where > group by > 聚合函数 > having

having后面可以使用聚合函数(统计函数)， where后面不可以使用聚合函数。

          语法：

select
    分组字段...,
    聚合函数(字段)...
from 表名
group by 分组字段1, 分组字段2...
having 条件表达式；

having功能上与where类似，都可以进行数据的过滤，简单语句的情况下，having子句可以替代where子句

        示例：

#1、having功能上与where类似，都可以进行数据的过滤，简单语句的情况下，having子句可以替代where子句
select * from product where price > 6000;
#等价于
select * from product having price > 6000;

当语法中有group by子句时，两者的区别就很大

#2、当语法中有group by子句时，两者的区别就很大
-- 统计每个分类商品的平均价格，并筛选出价格低于1000的分类
select category_id, avg(price)  from product group by category_id having avg(price) < 1000;
#等价于
select category_id, avg(price) as avg_price from product group by category_id having avg_price < 1000;


#统计官方自营商品中，每个分类商品的平均价格，并筛选出平均价格高于3000的分类
select category_id, avg(price) from product where is_self = '官方自营' group by category_id having avg(price) >3000;
#等价于
select category_id, avg(price) as avg_price from product where is_self = '官方自营' group by category_id having avg_price >3000;

9、order by排序查询--子句

asc表示升序排列、desc表示降序排列，默认为升序且升序时asc可以省略。

可以进行多列，多列排序时，前列值相同的数据，则按照后列值排序。

        语法

select    
    *
from 表名
order by 列1 [asc|desc], 列2 [asc|desc],...;

        示例：

#1、按照商品评分对数据从大到小排序
select * from product order by score desc;

#2、按照商品价格由低到高排序
select * from product order by price;
#等价于
select * from product order by price asc;

#3、多字段排序，先按照第一个排序，能比较出大小，不进行后续排列了；如果前面字段值相同，则继续按照第二个字段进行排序
#需求：按照商品评分对数据从大到小排序，且按照商品价格由低到高排序
select * from product order by score desc, price asc;

10distinct去重查询操作

作用：对查询结果中重复的行进行去重

注意：distinct是针对查询结果中的整行内容进行去重，不是单个字段。

当多字段组合时，相当于是一个整体，要同时重复，才能被去重。

语法

select
    distinct 字段1, 字段2,...
from 表;

示例：

#1、查询所有商品名称，实现去重
select distinct name from product;

#2、查询所有商品信息（商品+价格）进行去重操作
select distinct name, price from product;

11、limit限制查询（分页查询）--子句

作用：获取查询结果中指定范围内的行

select     
    字段列表
from 表名
#从第m+1行开始，往后获取n行内容
limit m, n;

M：表示开始行索引，默认是0，代表从第M+1行开始

N：表示查询条数，即提取多少条数据

示例：

#1、获取价格最高的商品（假定最高的只有一个没有重复的情况）
select * from product order by price desc limit 0, 1;

案例：

#需求：将商品数据按照价格从敌法哦高排序，然后获取第二页的内容（每页3条）
#思路：1排序：按price从高到低排序
#2、limit m,n： 根据页码和每页条数，计算m和n的值
select * from product order by price limit 3,3;
#limit分页查询公式 => select * from 数据表 limit (当前页页码 - 1) * 每页数量, 每页数量

#例子：每页显示5条记录，一共10页，问第5页，sql语句怎么写
select * from product order by price limit 20,5;

limit分页查询公式 => select * from 数据表 limit (当前页页码 - 1) * 每页数量, 每页数量

12、SQL语句执行顺序

注意：SQL语句的执行顺序往往和我们写sql的顺序是不一样的！！！

1) FROM

2) WHERE

3) GROUP BY

4) 聚合函数

5) HAVING

6) SELECT

7) DISTINCT【去重】

8) ORDER BY

9) LIMIT

SQL查询时，后面的步骤都是基于前一步的结果继续操作的！！！

结语：这是我自己当前学习的总结，希望对大家有用