MySQL Day03（DQL-简单查询、运算符、排序、聚合、分组、分页）

一、DQL操作

1、认识DQL 

数据查询操作，根据需求对数据进行筛选已确定数据以什么样的格式显示。

 语法格式

select 
   [all|distinct]
   <列1> [别名],
   <列2> [别名],
from <表名和视图名> [别名],<表名和视图名> [别名] ...
[where<条件表达式>]
[group by <列名>
[having <条件表达式>]]
[order by <列名> [asc|descc]]
[limit <数字或者列表>];


select *|列名 from 表 where 条件；

示例：

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


-- 查询商品名和商品价格
select pname,price from  product;

-- 别名查询，使用的关键字是as(as 可以省略的)
-- 表别名
select * from product as p;
-- 列别名
select pname as pn from product;
-- 去掉重复值
select distinct price from product;
-- 查询结果是表达式（运算查询），将所有商品的价格+10缘进行显示
select pname,price+10 as '新价格' from product;

2、运算符

数据库中的表结构确立后，表中的数据代表的意义就已经确定。通过MySQL运算符进行运算，就可以获取到表结构以外的另一种数据。

1）算术运算符

 示例：

-- 将每件商品的价格加10
select pname,price + 10 as new_price from product;

-- 将所有商品的价格上调10%
select pname,price * 1.1 as new_price from product;

 运行结果

 

 

2）比较运算符

 示例：

-- 查询商品名称为“海尔洗衣机”的商品所有信息
select * from product where pname = '海尔洗衣机';

 

-- 查询介个为800的商品
select * from product where price = 800;

-- 查询价格不足800的所有商品
select * from product where price < 800;

 

-- 查询山坡价格大于60元的所有商品信息
select * from product where price > 60;

-- 查询商品价格在200-1000之间所有商品
select * from product where price between 200 and 1000;

select * from product where price >=200 and <=1000;

select * from product where price >=200 && price <= 1000;

 

-- 查询价格在800或200的商品
select * from product where price in (200,800);
select * from product where price =200 or price = 800;
select * from product where price =200 || price =800;

%用来匹配任意字符，相当于空着的，x裤x,(裤在名字中间出现）

                                                           x裤，（裤子在最后）

                                                          裤x,(裤子在前面出现）

 

 

 

 '_蔻%'是指第二个字为蔻，且后面还有其他文字

-- 查询category_id为null的商品
select * from product where pname like '_蔻%';   -- 下划线匹配单个字符

 

 

-- 查询category_id 为null的商品
select * from product where category_id is null;

 

 

-- 查询category_id不为null分类的商品
select * from product where category_id is not null;

-- 使用least求最小值
select least（10,5,50);

 如果求最小值时，有值为null，则不会进行比较，结果直接为null。

-- 使用greatest求最大值
select greatest(10,5,50) as '最大值';

 

 3）逻辑运算符

与运算

-- 与运算
select 3 & 5;
0011
0101
------
0001

 

或运算

-- 或运算
select 3 | 5;

0011
0101
------
0110

 4）位运算符

是在二进制数上进行计算的运算符。位运算会先将操作数变成二进制数，进行位运算。然后再将计算结果从二进制数变回十进制数。

 位异或（相同为0，不同为1）

-- 位异或
select 3^5;
0011
0101
------
0110

 位右移

-- 位右移
select 3>>1;

0011
00011
-------->向右移动一位
0001

位左移

select 3<<1;

 0011
00110
<------
 0110

 

位取反

select ~3；

位数在4个一下弄
0011
取反----
1100

 

 3、排序查询（order by）

需要对读取的数据进行排序，使用order by子句即可进行排序，再返回搜索结果

语法

select 
  字段名1，字段名2，...
from 表名
order by 字段民1 [asc|desc],字段名2[asc|desc]......

特点

asc默认升序，desc代表降序，默认升序

order by 用于子句中可以支持单个字段，多个字段，标傲世，函数，别名

order by子句，放在查询语句的最后面。limit子句除外

示例：

-- 使用价格排序（降序）
select * from product order by price desc;

 

--在价格排序（降序）的基础上，已分类排序（降序）
select * from product order by price,category_id desc;

 

-- 显示商品的价格（去重复），并排序（降序）
select distinct price from product order by price desc;

4、聚合查询

纵向查询，是对一列的值进行计算，然后返回一个单一的值； 会忽略空值

 1）count（）

-- 查询商品的总条数
select count(pid) from product;
select count(*) from product;

>>18

>>18 

 2)sum()

-- 查询分类为’c001‘的所有商品价格的总和
select sum(price) from product where category_id = 'c001';

3)max（）、min（）

-- 查询商品的最大价格
select max(price) from product;
-- 查询商品的最小价格
select min(price) from product;

-- 也可以写成
select max(price),min(price) from product;

4)avg()

-- 查询分类为‘c002’所有商品的平均价格。
select avg(price) from product where category_id = 'c002';

5)NULL值的处理

 示例：

-- 测试对NULL值的处理
create table test_null(
  c1 varchar(20),
	c2 int

);

-- 插入数据
insert into test_null values('aaa',3);
insert into test_null values('bbb',3);
insert into test_null values('ccc',null);
insert into test_null values('ddd',6);

-- 测试
select count(*), count(1),count(c2) from test_null;

 

 

select sum(c2),max(c2),min(c2),avg(c2) from test_null;

 

 5、分组查询

1）group by

分组查询是指使用group by 子句对查询信息进行分组

格式：

select 字段1，字段2。。。 from 表名 group by 分组字段 having 分组条件

如果要进行分组的话，则使用select子句之后，只能出现分组的字段和统计函数，其他的字段不能出现 

 操作：

-- 统计各个分类商品的个数
select category_id,count(*) from product group by category_id;

 

 流程：from product----group by------count（pid）--------select --------c00x----数量

2）分组查询-having

分组之后的条件筛选-having

   分组之后对统计结果进行筛选的话必须使用having，不能使用where

   where子句用来筛选from子句中指定的操作所产生的行

   group by 字句用来分组where子句的输出

   having子句用来从分组的结果中筛选行

格式

select 字段1，字段2... from 表名 group by 分组字段 having 分组条件;

操作

-- 统计各个分类商品的个数，且只显示个数大于4的信息
select category_id,count(*) from product group by category_id having count(*) >1;

流程： from product----group by------count（pid）--------select --------c00x----数量---having

3）分页查询-limit

用于数据量巨大，显示屏长度有限，对数据需要采取分页显示。（从0开始）

格式

-- 方法1 显示前n条
select 字段1，字段2... from 表名 limit n;

-- 方式2 分页显示
select 字段1，字段2... from 表名 limit m，n;
m:整数，表示从第几条索引开始，计算方式（当前页-1）*每页显示条数
n：整数，表示查询多少条数据

操作

-- 查询product 表的前5条记录
select * from product limit 5;

-- 从第4条开始显示，显示5条
select * from product limit 3，5;

 

 对于分页的理解：

 4）insert into select语句

将一张表的数据导入到另一张表中，可以使用insert into select 语句(表2必须存在）

格式

insert into table2() select value1,value2,。。。。from table1;
或者：
insert into table 2 select * from table1;

示例1：

-- insert into table select 。。。
create table product2(
  pname varchar(20),
	price double
);

insert into product2(pname,price) select pname,price from product;
select * from product2;

 示例2：

create table product3(
  category_id varchar(20),
	product_count int
);

insert into product3 select category_id,count(*) from product group by category_id;
select * from product3;