DQL实例

1 DQL准备工作和语法

准备工作

create database tb_mydb4;
use tb_mydb4;
#创建商品表：
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 [distinct] 
* | 列名,列名 
from 表 
where 条件

2 简单查询

mysql> desc product;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| pid         | int(11)     | NO   | PRI | NULL    |       |
| pname       | varchar(20) | YES  |     | NULL    |       |
| price       | double      | YES  |     | NULL    |       |
| category_id | varchar(32) | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+

• 查询所有的商品.

select * from product;

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

select pname,price from product;

• 查询价格,去掉重复值.

select price from product;
select distinct price from product;

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

select pname, price, (price + 10) from product;

• 别名查询.使用的关键字是as（as可以省略的）.列别名

select pname as 商品名称, price as 商品价格 from product;

• 别名查询.使用的关键字是as（as可以省略的）.表别名

select pname 商品名称, price 商品价格 from product;

3 条件查询

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

mysql> desc product;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| pid         | int(11)     | NO   | PRI | NULL    |       |
| pname       | varchar(20) | YES  |     | NULL    |       |
| price       | double      | YES  |     | NULL    |       |
| category_id | varchar(32) | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+

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

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;

• 查询商品价格在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);

• LIKe 中的 %代表匹配任意长度的任意字符; _代表匹配一个任意字符
• 查询商品名称含有’霸’字的所有商品

select * from product
where pname like '%霸%';

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

select * from product
where pname like '香%';

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

select * from product
where pname like '_想%';

• 商品没有分类id的商品

select * from product
where category_id is null;

• 查询有分类id的商品

select * from product
where category_id is not null;

4 排序查询

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

SELECT * FROM 表名 ORDER BY 排序字段 ASC|DESC;
#ASC 升序 (默认), 从小到大排序
#DESC 降序, 从大到小排序

mysql> desc product;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| pid         | int(11)     | NO   | PRI | NULL    |       |
| pname       | varchar(20) | YES  |     | NULL    |       |
| price       | double      | YES  |     | NULL    |       |
| category_id | varchar(32) | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+

• 使用价格排序(降序)

select * from product
order by price desc;

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

select * from product
order by price desc, category_id desc;

• 显示商品的价格(去重复)，并排序(降序)

select distinct price from product;

5 聚合查询

• 之前我们做的查询都是横向查询，它们都是根据条件一行一行的进行判断，而使用聚合函数查询是纵向查询，它是对一列的值进行计算，然后返回一个单一的值；另外聚合函数会忽略空值。
• 五个聚合函数：
  • count：统计指定列不为NULL的记录行数；
  • sum：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；
  • max：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；
  • min：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；
  • avg：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；
• 查询商品的总条数

select count(*) from product; # 13
select count(pid) from product; #  13
select count(category_id) from product; # 12? 13?
select pid from product;
select count(1) from product;

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

select count(1) from product
where price>200;

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

select sum(price) from product
where category_id='c001';

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

select avg(price) from product
where category_id='c002';

• 查询商品的最高价格和最底价格

select min(price) 最低价格,max(price) 最高价格 from product

6 分组查询 重点难点

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

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

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

having与where的区别:

• having是在分组操作执行后, 对分组后的数据进行过滤.
• where是在分组操作执行前, 对分组前的数据 只能使用表原始列进行条件过滤
• having后面可以使用 聚合函数
• where后面不可以使用 聚合函数。
• 当一条SQL语句中, 既有where 又有 group by \ having时, 先执行 where, 再执行 group by, 最后执行having

前提: 分组之后

select 5 --只能写 被分组的列和聚合函数

from 1

where 2	--不能使用聚合函数

group by 3

having 4	--可以使用聚合函数, 加分组条件

order by 6

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

select category_id, count(1)
from product
group by category_id;

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

select category_id, count(1)
from product
group by category_id
having count(1)>1;

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

select category_id,count(1)
from product
where price>200
group by category_id
having count(1)>1
;

7 条数限制查询-- 重点重点重点

• LIMIT是MySQL内置函数，其作用是用于限制查询结果的条数。
• 格式: select * from 表名 limit m,n
• 其中: m是指记录开始的index，从0开始，表示第一条记录，n是指从第m+1条开始，取n条。
• 例如:

 select * from tablename limit 2,4 -- 即取出第3条至第6条，4条记录    

• 应用场合:分页
• 分页查询格式:

SELECT * FROM 表名 LIMIT startRow,pageSize;

• 例如：

select * from products limit 0,5;   #第一页，每页显示5条。
select * from products limit 5,5;   #第二页，每页显示5条。
select * from products limit 10,5;  #第三页，每页显示5条。
select * from products limit startRow,5; #第curPage页，每页显示5条, startRow的值如何计算呢?

-- 后台计算出页码、页数(页大小)
-- 分页需要的相关数据结果分析如下,
-- 注意:下面是伪代码不用于执行
int curPage = 2; -- 当前页数
int pageSize = 5; -- 每页显示数量
int startRow = (curPage - 1) * pageSize; -- 当前页, 记录开始的位置(行数)计算
int totalSize = select count(*) from products; -- 记录总数量
int totalPage = Math.ceil(totalSize * 1.0 / pageSize); -- 总页数