MySQL-DQL之数据表操作

数据查询语言：简称DQL(Data Query Language)

零. 准备工作

创建数据库

create database test charset = utf8;
use test;

创建商品表:

CREATE TABLE product
(pid         INT PRIMARY KEY,pname       VARCHAR(20),price       DOUBLE,category_id VARCHAR(32)
);

插入数据:

INSERT INTO product(pid,pname,price,category_id) VALUES(1,'联想',5000,'c001');
INSERT INTO product(pid,pname,price,category_id) VALUES(2,'海尔',3000,'c001');
INSERT INTO product(pid,pname,price,category_id) VALUES(3,'雷神',5000,'c001');
INSERT INTO product(pid,pname,price,category_id) VALUES(4,'杰克琼斯',800,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(5,'真维斯',200,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(6,'花花公子',440,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(7,'劲霸',2000,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(8,'香奈儿',800,'c003');
INSERT INTO product(pid,pname,price,category_id) VALUES(9,'相宜本草',200,'c003');
INSERT INTO product(pid,pname,price,category_id) VALUES(10,'面霸',5,'c003');
INSERT INTO product(pid,pname,price,category_id) VALUES(11,'好想你枣',56,'c004');
INSERT INTO product(pid,pname,price,category_id) VALUES(12,'香飘飘奶茶',1,'c005');
INSERT INTO product(pid,pname,price,category_id) VALUES(13,'海澜之家',1,'c002');

一. 简单查询

根据某些条件从某个表中查询指定字段的内容

格式：select [distinct]*| 列名,列名 from 表 where 条件

1.查询所有的商品.

select *  from product;

2.查询商品名和商品价格.

select pname,price from product;

3.查询结果是表达式（运算查询）：将所有商品的价格+10元进行显示.

select pname,price+10 from product;

二. 条件查询

1. 比较查询

查询商品名称为“花花公子”的商品所有信息：

SELECT * FROM product WHERE pname = '花花公子';

查询价格为800商品

SELECT * FROM product WHERE price = 800;

查询价格不是800的所有商品

SELECT * FROM product WHERE price != 800;
SELECT * FROM product WHERE price <> 800;

查询商品价格大于60元的所有商品信息

SELECT * FROM product WHERE price > 60;

查询商品价格小于等于800元的所有商品信息

SELECT * FROM product WHERE price <= 800;

2. 范围查询

查询商品价格在200到1000之间所有商品

SELECT * FROM product WHERE price BETWEEN 200 AND 1000;

查询商品价格是200或800的所有商品

SELECT * FROM product WHERE price IN (200,800);

3. 逻辑查询

查询商品价格在200到1000之间所有商品

SELECT * FROM product WHERE price >= 200 AND price <=1000;

查询商品价格是200或800的所有商品

SELECT * FROM product WHERE price = 200 OR price = 800;

查询价格不是800的所有商品

SELECT * FROM product WHERE NOT(price = 800);

4. 模糊查询

查询以’香’开头的所有商品

SELECT * FROM product WHERE pname LIKE '香%’;

查询第二个字为’想’的所有商品

SELECT * FROM product WHERE pname LIKE '_想%’;

5. 非空查询

查询没有分类的商品

SELECT * FROM product WHERE category_id IS NULL;

查询有分类的商品

SELECT * FROM product WHERE category_id IS NOT NULL;

三. 排序查询

通过order by语句，可以将查询出的结果进行排序。暂时放置在select语句的最后。

格式：SELECT * FROM 表名 ORDER BY 排序字段 ASC|DESC;

ASC 升序 (默认)
DESC 降序

1.使用价格排序(降序)

SELECT * FROM product ORDER BY price DESC;

2.在价格排序(降序)的基础上，以分类排序(降序)

SELECT * FROM product ORDER BY price DESC,category_id DESC;

四. 聚合查询

之前我们做的查询都是横向查询，它们都是根据条件一行一行的进行判断，而使用聚合函数查询是纵向查询，它是对一列的值进行计算，然后返回一个单一的值；另外聚合函数会忽略空值。

如下五个聚合函数：

查询商品的总条数

SELECT COUNT(*) FROM product;

查询价格大于200商品的总条数

SELECT COUNT(*) FROM product WHERE price > 200;

查询分类为’c001’的所有商品的总和

SELECT SUM(price) FROM product WHERE category_id = 'c001‘;

查询分类为’c002’所有商品的平均价格

SELECT AVG(price) FROM product WHERE categ ory_id = 'c002‘;

查询商品的最大价格和最小价格

SELECT MAX(price),MIN(price) FROM product;

五. 分组查询

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

格式：SELECT 字段1,字段2… FROM 表名 GROUP BY 分组字段 HAVING 分组条件;

分组操作中的having子语句，是用于在分组后对数据进行过滤的，作用类似于where条件。

having与where的区别:
1).having是在分组后对数据进行过滤,where是在分组前对数据进行过滤
2).having后面可以使用分组函数(统计函数),where后面不可以使用分组函数。

• 统计各个分类商品的个数

  SELECT category_id ,COUNT(*) FROM product GROUP BY category_id ;
  

• 统计各个分类商品的个数,且只显示个数大于1的信息

  SELECT category_id ,COUNT(*) FROM product GROUP BY category_id HAVING COUNT(*) > 1;
  

• 请结合下表业务说明，按照（交易日期）分组与按照（交易日期+用户ID）分组，在数据计算上的区别（请用文字说明区别）

  CREATE TABLE IF NOT EXISTS trade_history
  (BASE_DATE    DATE NOT NULL comment '交易日期',SEQUENCE     INT  NOT NULL comment '交易序列号',USER_ID      INT  NOT NULL comment '用户ID',TRADE_AMOUNT INT  NOT NULL comment '交易金额',PRIMARY KEY (BASE_DATE, SEQUENCE, USER_ID)
  );insert into trade_history
  values ('2020-06-05', 1, 1, 100),('2020-06-07', 1, 1, 200),('2020-06-07', 2, 1, 150),('2020-06-25', 1, 2, 100),('2020-06-27', 1, 2, 200),('2020-06-27', 2, 2, 250),('2020-06-27', 3, 2, 120);select * from trade_history;
  

  按照（交易日期）分组，计算的是某一日的交易额情况，而按照（交易日期+用户ID）分组，可以计算的是某一日某一人的交易额情况，简单来说就是多增加了一个考虑维度。

MySQL5.7之后,在分组查询的时候,select中的字段,如果没有出现在聚合函数中,就必须出现在groupby后面,否则,就会报错

需求中有每一个/每一种/每一 XXXX

六. 分页查询

分页查询在项目开发中常见，由于数据量很大，显示屏长度有限，因此对数据需要采取分页显示方式。例如数据共有30条，每页显示5条，第一页显示1-5条，第二页显示6-10条。

格式：

SELECT 字段1，字段2... FROM 表名 LIMIT M,N

M: 整数，表示从第几条索引开始，计算方式 （当前页-1）*每页显示条数
N: 整数，表示查询多少条数据

SELECT 字段1，字段2... FROM 表名 LIMIT 0,5
SELECT 字段1，字段2... FROM 表名 LIMIT 5,5

select pid,pname,price from product limit 0,5;
select pid,pname,price from product limit 5,5;