～ DQL 操作表记录 – 查

数据查询语言（DQL），用于查询数据库中的数据。

SELECT <字段名列表>
FROM <表名或视图>
[ WHERE 查询条件 ]
[ GROUP BY <分组的字段名> ]
[ ORDER BY <排序的列名>[ ASC或DESC ] ]
[ LIMIT [位置偏移量,] 行数 ]
 
说明：
1. 字段名列表为要查询的字段名，各字段间使用逗号分隔。若查询表中所有字段，则使用 * 表示。
2. FROM 后的表名为要查询的数据的来源，可单个或多个。
3. [可选]
   3-1. WHERE子句为可选项，指定查询的条件；
   3-2. GROUP BY子句表名查询出来的数据按指定字段进行分组；
   3-3. ORDER BY子句指定按什么顺序显示查询出来的数据，什么字段的升序（ASC）或降序（DESC）；
   3-4. LIMIT子句(可用于分页查询，常与ORDER BY配合使用)
        SELECT语句查询出来的数据有可能是全部数据，实际开发中可能只需要指定行数的记录，就需要使用LIMIT语句。
        其中，位置偏移量是指从结果集第几条数据开始显示(第一条记录的位置偏移量为0，第二条为1...)，此参数可选，省略时从第一条显示；
        行数，指的是显示记录的条数。
 
如，SELECT `studentNo`,`studentName`,`phone`,`address`,`bornDate`
   FROM `student`
   WHERE `gradeId`=1
   ORDER BY studentNo
   LIMIT 4;

准备工作（数据表 _ 插入数据）

#创建商品表：
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,'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);

简单查询（显示所有记录）

// 查询表中的所有数据（所有商品）
   SELECT * FROM product;
 
# 查询商品名和商品价格
  SELECT pname,price FROM product;
# 查询商品的价格
  SELECT price FROM product;
# 去重 distinct
  SELECT DISTINCT price FROM product;
 
 
# 了解 - 查询结果是表达式（运算查询）：将所有商品的价格+10元进行显示
  SELECT price+10 FROM product;
# 了解 - 起别名
  SELECT price+10 AS 打折价格 FROM product;
  SELECT price+10 打折价格 FROM product;

条件查询

# 条件查询
 
#查询商品名称为“花花公子”的商品所有信息：
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;
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;
SELECT * FROM product WHERE price BETWEEN 200 AND 1000;
 
#查询商品价格是200或800的所有商品
SELECT * FROM product WHERE price = 200 OR price = 800;
SELECT * FROM product WHERE price IN (200,800);
 
#查询含有'霸'字的所有商品
SELECT * FROM product WHERE pname LIKE '%霸%';
 
#查询以'香'开头的所有商品
SELECT * FROM product WHERE pname LIKE '香%';
 
#查询第二个字为'想'的所有商品
SELECT * FROM product WHERE pname LIKE '_想%';
 
#查询没有分类的商品
SELECT * FROM product WHERE category_id IS NULL;
 
#查询有分类的商品
SELECT * FROM product WHERE category_id IS NOT NULL;
 
# 查询所有价格大于2000的电脑商品(catetory_id是c001)
# 或者价格大于1000的服装商品(catetory_id是c002)
SELECT * FROM product WHERE (price>2000 AND category_id='c001')
			OR (price>1000 AND category_id='c002');
 
# select * from product where (price>1000 and category_id='c002';
 
# 查询所有价格大于2000的电脑商品(catetory_id是c001)
# 或者价格大于1000的服装商品(catetory_id是c002)
SELECT * FROM product WHERE price>2000 AND category_id IN('c001','c002');

SQL 语句查询

[1] 排序查询：通过 order by 语句，对查询出的结果进行排序，放置在 select 语句的最后。

SELECT * FROM 表名 ORDER BY 排序字段ASC|DESC;
 
# ASC 升序（从小打到，默认） ； DESC 降序（从大到小）

# 查询所有记录的name和price,结构按照价格从大到小进行排序
SELECT pname,price FROM product ORDER BY price DESC;
 
#1.查询所有商品信息,使用价格排序(降序)
SELECT * FROM product ORDER BY price DESC;
 
#2.查询所有商品信息,在价格排序(降序)的基础上，以分类排序(降序)
#先将查询结果 在价格上进行降序,如果价格相同再以分类进行降序,如果价格不相同,不在对分类进行排序
SELECT * FROM product ORDER BY price DESC,category_id DESC;
 
#3.显示商品的价格(去重复)，并排序(降序)
SELECT DISTINCT price FROM product ORDER BY price DESC;

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

聚合函数用来对已有数据进行汇总，以下为 5 个常用的聚合（统计）函数：

• count：统计指定列不为 NULL 的记录行数；
• sum：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为 0；
• max：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；
• min：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；
• avg：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为 0；

/*
聚合查询:以前的查询都是横向记录查询，而聚合查询是纵向个数查询
		聚合查询的特点:查询到的结果是：单一值
		
	聚合函数:
		count:求记录数的聚合函数,count函数会自动忽略空值
		
	    以下四个,通常用于数值的计算
		max:求最大值
		min:求最小值
		avg:求平均值
		sum:求和
*/
 
#查询统计所有商品的个数
SELECT COUNT(*) FROM product;
 
#查询统计一共有多少个分类
SELECT COUNT(category_id) FROM product;
 
#查询所有商品价格的最大值
SELECT MAX(price) FROM product;
 
#查询所有商品价格的最小值
SELECT MIN(price) FROM product;
 
#查询所有商品价格的最平均值
SELECT AVG(price) FROM product;
 
#查询所有商品价格的总值
SELECT SUM(price) FROM product;
 
#查询所有商品价格的最大值,最小值,平均值,总和
SELECT MAX(price) AS 最大值,MIN(price) 最小值,AVG(price) 平均值,SUM(price) 总和 FROM product;

[3] 分组查询：使用 group by 字句对查询信息进行分组。

分组查询:把查询数据分为几个组。关键字: group by 字段
 
	基本格式：
	SELECT 字段1,字段2… FROM 表名 GROUP BY分组字段 HAVING 分组条件;
	# 分组操作中的having子语句，是用于在分组后对数据进行过滤的，作用类似于where条件。
		
	先分组,再查询,具体查询到的结果数,取决于能分多少组
	如果分组后 还有需要条件判断 那么请用having关键字
		
		where和having的区别:
		
		    where 写在 基本查询后面的，是在分组前对数据进行过滤
		    having 写在 分组查询后面的，是在分组后对数据进行过滤
		
		    where后面是不能写 count sum等聚合（统计）函数
		    having后面可以写count和sum等聚合函数过滤数据

# 查询所有以category_id分组后的价格的总和
# 具体有多少个总和 取决于可以分多少组
SELECT SUM(price) FROM product GROUP BY category_id;
 
#0 统计所有商品的个数
SELECT COUNT(*) FROM product;
 
#1 统计各个分类商品的个数
SELECT COUNT(*) FROM product GROUP BY category_id;
 
 
#2 统计各个分类商品的个数,且只显示个数大于1的信息
SELECT category_id, COUNT(*) 个数  FROM product  GROUP BY category_id  HAVING 个数 > 1;

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

/*
分页查询：只查询记录中的一部分。关键字: limit 数值1(下标,从0开始),数值2(需要查出来记录数)
		
	SELECT 字段1，字段2... FROM 表名 LIMIT M,N
		M: 整数，表示从第几条索引开始，计算方式 （当前页-1）*每页显示条数
		N: 整数，表示查询多少条数据
*/
SELECT * FROM product LIMIT 0,5;
 
SELECT * FROM product LIMIT 5,5;
 
SELECT * FROM product LIMIT 10,5;
 
# 查询的公式: 假设每一页我要查询 n 条 
 
# 第1页 limit (1-1)*n,n
# 第2页 limit (2-1)n,n
# 第100页 limit (100-1)*n,n
# 第m页 limit (m-1)*n,n

查询表中的数据记录

# 基本语法

select <字段列表>
from <表名或视图>
[where 查询条件]
[group by <分组的字段>]
[order by <排序的字段> [asc|desc]]                 // asc 升序；desc 降序
[limit [从结果集第几条开始显示,]显示记录的条数];

[ 准备工作 ]               => 商品表（字段_数据记录）

# 创建商品表

create table product(
	pid int not null PRIMARY KEY,
	pname VARCHAR(20) not null,
	price DOUBLE,
	category_id VARCHAR(32)
)comment='商品表';

# 插入商品数据

INSERT into product
VALUES
	( 1, '联想', 5000,'c001' ),
	( 2, '海尔', 3000,'c001' ),
	( 3, '雷神', 5000,'c001' ),
	( 4, 'JACK JONES', 800,'c002' ),
	( 5, '真维斯', 200, 'c002' ),
	( 6, '花花公子', 440,'c002' ),
	( 7, '劲霸', 2000, 'c002'),
	( 8, '香奈儿', 800, 'c003' ),
	( 9, '相宜本草', 200, 'c003'),
	( 10, '面霸', 5, 'c003' ),
	( 11, '好想你枣', 56, 'c004' ),
	( 12, '香飘飘', 1, 'c005' ),
	( 13, '果酒', 1, null );

• 简单查询

# 查询表中所有数据：select * from product;

# 查询商品名称、商品价格：select pname,price from product;

# 查询商品价格，且去重：select distinct price from product;    // distinct，去重

// 以下了解即可

# 查询结果是表达式（运算查询）：select distinct price+10 from product;  // 所有价格 +10

# 起别名：select distinct price+10 as 打折价格 from product;

• 条件查询

# 查询商品名称为花花公子的商品的所有信息；
-- 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;
-- select * from product where not(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 price<=1000;

# 查询商品价格是200或800的商品的所有信息
-- select * from product where price=200 or price=800;
-- select * from product where price in(200,800);

# 查询商品名称含有“霸”字的商品的所有信息
-- select * from product where pname like '%霸%';

# 查询商品名称以“香”开头的商品的所有信息
-- select * from product where pname like '香%';

# 查询商品名称的第二个字是“想”的商品的所有信息
-- select * from product where pname like '_想%';

# 查询没有分类的商品的所有信息
-- select * from product where category_id is null;

# 查询有分类的商品的所有信息
-- select * from product where category_id is not null;

# 查询价格大于2000的电脑商品（category_id是c001）或者价格大于1000的服装商品（category_id是c002）
-- select * from product where (price>2000 and category_id='c001') or (price>1000 and category_id='c002');

• 排序查询

# 查询所有商品信息，并按倒序排列    asc：正序，从小到大   desc：倒序，从大到小
-- select * from product order by price desc;

# 查询所有商品信息，按价格进行到序 — 如果价格相同，再以分类进行降序；如果价格不同，则不再进行分类
-- select * from product order by price desc,category_id desc;

# 查询商品价格，降序，去重
-- select distinct price from product order by price desc;

• 聚合查询：又称纵向或统计查询，是对查询后的结果的列进行计算，然后返回一个单一值；另，聚合函数会忽略空值。

聚合（统计）函数，主要用来对已有数据进行汇总，以下是 5 个常用函数：

1）count：统计指定列不为 null 的记录的行数；    // 行数

2）sum：计算指定列的数值的和。如果该列数据类型不是数值类型，则结果为0。    // 求和

3）max/min：计算指定列的最大值/最小值。如果该列是字符串类型，那么按字符串排序运算。    // 求最值

4）avg：计算指定列的平均值。如果该列数据类型不是数值类型，则结果为0。    // 求平均值

# 查询所有商品的数量
-- select count(*) from product;  // 记录总数

# 查询总共有多少个分类
-- select count(distinct category_id) from product;

# 查询所有商品的价格的最大值、最小值、平均值、总和
-- select max(price) from product;    # 最大值
-- select min(price) from product;    # 最小值
-- select avg(price) from product;  # 平均值
-- select sum(price) as 总和 from product;  # 总和，别名

• 分组查询

# 基本语法
select ... from 表名 group by 分组字段 having 分组条件;

说明：
1）先分组，再查询 — 具体查询到的结果数，取决于分了多少组
2）分组后，如果需要条件判断，使用having    // having不同于where，其后面可以有聚合函数

# 查询不同的种类各有多少个商品
-- select count(*) from product group by category_id;

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

• 分页查询

[ 查询公式 ] 假设每一页要查询 n 条数据，则

第一页：limit (1-1)n,n
第二页：limit (2-1)n,n
第 m 页：limit (m-1)n,n

# 基本语法
select * from 表名 limit M,N;

# 代码示例：共有13条数据，每页显示5条。

# select * from product limit 0,5;    // 第一页显示1-5条
# select * from product limit 5,5;    // 第二页显示6-10条
select * from product limit 10,5;     // 第三页显示11-13条