一、MySQL数据库（单表）（2）

1 SQL语句（DQL）

数据准备：

/*
   产品表,商品表 product
   pid 主键 
   pname 商品名字
   price 价格
   category_id 分类
   c001 家电
   c002 服装
   c003 化妆品
   c004 食品
   c005 饮料
*/
CREATE TABLE product(
    pid INT PRIMARY KEY AUTO_INCREMENT,
    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,'JACK JONES',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,'果9',1,NULL);

1.1 简单查询

  数据的基本查询
  拼接语句  
  关键字 
    select  选择,查询  distinct 去重   as 重命名
    from    来自哪里,哪个表
    where   条件

	注意：
		distinct去重一般就select 后面跟一列，否则很可能逻辑上是有问题的
		as别名，可以写出来，也可以省略as关键字，直接跟别名
    
    基本语法:
      select 列名,列名 from 表名 查询指定的列
      select * from 表名 查询所有列 

# 查询数据表,显示商品名和价格
SELECT pname,price FROM product;

# 查询全部的商品数据
SELECT * FROM product;


# 查询数据表,查询价格,去掉重复的
SELECT DISTINCT(price) FROM product;

# 查询数据表,所有的商品价格上调100元

SELECT pname,price+100 FROM product;

# 查询数据表,所有的商品价格上调100元 对查询的结果集进行列的重命名
SELECT pname,price+100 AS'price' FROM product;

# 查询数据表,所有的商品价格上调100元 对查询的结果集进行列的重命名,简化书写
# 去掉as,直接空格,写上新列名
SELECT pname,price+100 price FROM product;

---

1.2 条件查询

比较运算符	< <= > >= = <>	大于、小于、大于(小于)等于、不等于
	BETWEEN …AND…	显示在某一区间的值(含头含尾)
	IN(set)	显示在in列表中的值，例：in(100,200)
	LIKE ‘字符’	模糊查询，like语句中，% 代表零个或多个任意字符，_ 代表一个字符， 例如：first_name like '_a%';（表示a前面有一位字符，a后面可以有或没有）
	IS NULL	判断是否为空
逻辑运行符	and	多个条件同时成立
	or	多个条件任一成立
	not	不成立，例：where not(salary>100);

实例：

#查询商品名称为“花花公子”的商品所有信息：
SELECT * FROM product WHERE pname = '花花公子';


#查询价格为800商品
SELECT * FROM product WHERE price = 800;


#查询价格不是800的所有商品
SELECT * FROM product WHERE price <> 800;
SELECT * FROM product WHERE NOT(price = 800);


#查询商品价格大于60元的所有商品信息
SELECT * FROM product WHERE price > 60;

#查询商品价格在200到1000之间所有商品
SELECT * FROM product WHERE price >= 200 AND price <= 1000;
# BETWEEN 值 and 值 区间范围查询 ,前面的值必须小于后面的
# BETWEEN 值 and 值 值是日期,前面的日期小, 2019-5-5   and 2019-10-6
SELECT * FROM product WHERE price BETWEEN 200 AND 1000;

#查询商品价格是200或800的所有商品
SELECT * FROM product WHERE price =200 OR price = 800;
# 推荐使用in查询,包含即可  in(数据无顺序关系)
SELECT * FROM product WHERE price IN(200,800,1);



#查询商品名称含有'霸'字的所有商品
# 使用通配符 % 零个或者多个
SELECT * FROM product WHERE pname LIKE '%霸%';


#查询商品名称以'香'开头的所有商品
SELECT * FROM product WHERE pname LIKE '香%';

#查询商品名称第二个字为'想'的所有商品
SELECT * FROM product WHERE pname LIKE '_想%'


#商品没有分类id的商品
#查询列 category_id 是空值
SELECT * FROM product WHERE category_id IS NULL;

#查询有分类id的商品
#查询列 category_id 不是空值
SELECT * FROM product WHERE category_id IS NOT NULL;

---

1.3 排序查询

 排序查询:
    对查询的结果集排序
    关键字 order by
    升序: asc  默认的,可以不写
    降序: desc
    排序写在SQL语句的最后

SELECT * FROM product ORDER BY price DESC;

#在价格排序(降序)的基础上，以分类排序(降序)
SELECT * FROM product ORDER BY category_id DESC;

#显示商品的价格(去重复)，并排序(降序)
SELECT DISTINCT (price) FROM product ORDER BY price DESC;

---

1.4 聚合查询

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

• count：统计指定列不为NULL的记录行数；

• sum：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；

• max：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；

• min：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；

• avg：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；

#查询商品的总条数
SELECT COUNT(*) FROM product;
SELECT COUNT(pid) FROM product;

#查询价格大于200商品的总条数
SELECT COUNT(pid)  FROM product WHERE price > 200;

#查询所有商品的价格总和
SELECT SUM(price) FROM product;

#查询分类为'c001'的所有商品的价格总和
SELECT SUM(price) FROM product WHERE category_id = 'c001';

#查询分类为'c002'所有商品的平均价格
SELECT AVG(price) FROM product WHERE category_id = 'c002';

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

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

---

1.5 分组查询

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

• 格式：

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

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

举个例子：

 分组查询
 举例:
   80人,分成10个组,每个组8人
   统计数据,每个小组统计
   1组: 成绩100
   2组: 成绩99
   
 一个列的相同值,进行分组
 关键字 group by 列名

having与where的区别:

1. having是在分组操作执行后, 对分组后的数据进行过滤.
   where是在分组操作执行前, 对分组前的数据 只能使用表原始列进行条件过滤（where是真实原始的数据表过滤，having是临时数据表过滤出的）

2. having后面可以使用 聚合函数
   where后面不可以使用 聚合函数。

3. 当一条SQL语句中, 既有where 又有 group by \ having时, 先执行 where, 再执行 group by, 最后执行having

查询执行的优先级如下：
select 6 （最后展示结果列）

from 1 （先找到表）

where 2（紧接着过滤）

group by 3（where过滤完后的表，临时表进行分组）

having 4 （然后临时表的过滤条件）

order by 5 （再排序）

---

案例介绍：

# 按照商品分类,进行价格求和
SELECT SUM(price),category_id FROM product GROUP BY category_id;

# 需求追加, 不想看到求和后价格低于1000的
# 在分组求和后的数据基础之上,再次条件过滤
# where 条件过滤在表中过滤(真实的数据表)
# 查询后的结果,过滤条件,使用关键字 having

SELECT SUM(price) price,category_id FROM product GROUP BY category_id
HAVING price > 1000;


# 统计各个分类商品的个数
SELECT COUNT(pid),category_id FROM product GROUP BY category_id;

# 统计各个分类商品的个数,且只显示个数大于1的信息
SELECT COUNT(pid) pid,category_id FROM product GROUP BY category_id HAVING pid>1;

# 统计价格>200元的 各个分类商品的个数,且只显示个数大于1的信息
SELECT COUNT(pid) pid,category_id FROM product WHERE price > 200 GROUP BY category_id HAVING pid>1;

---

1.6 条数限制查询

限制条数查询
关键字 limit (SQL语句的最末尾)
语法: limit m,n
m: 数据表的开始索引0开始
n: 限制显示多少条

SELECT  * FROM product LIMIT 1,10;
SELECT * FROM product LIMIT 0, 1000;

应用场合