SQL语句(DQL)

DQL准备工作和语法

准备工作

#创建商品表：
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 条件

简单查询

-- 查询所有的商品.
SELECT * from product;
-- 查询商品名和商品价格.
select pname,price from product;
-- 查询价格,去掉重复值.
select DISTINCT price from product;
-- 查询结果是表达式（运算查询）：将所有商品的价格+10元进⾏显示.
select pname,price+10 from product;
-- 别名查询.使⽤的关键字是as（as可以省略的）.列别名
select pname,price+10 as '价格' from product;
select pname,price+10 '价格' from product;
-- 别名查询.使⽤的关键字是as（as可以省略的）.表别名
select * from product as p;
select * from product p;

条件查询

比较运算符	< <= > >= =<>	⼤于、⼩于、⼤于(⼩于)等于、不等于
	BETWEEN…AND…	显示在in列表中的值，例：in(100,200)
	LIKE ‘字符’	模糊查询，like语句中，% 代表零个或多个任意字符，_ 代表⼀个字符， 例如： first_name like ‘_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 price!=800; -- mysql特有的符号
#查询商品价格⼤于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;

排序查询

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

• 格式:

SELECT * FROM 表名 ORDER BY 排序字段 ASC|DESC;
#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;

聚合查询

之前我们做的查询都是横向查询，它们都是根据条件⼀⾏⼀⾏的进⾏判断，⽽使⽤聚合函数查询是纵向查询，它是对⼀列的值进⾏计算，然后返回⼀个单⼀的值；另外聚合函数会忽略空值。
今天我们学习如下五个聚合函数：

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

练习：

#查询商品的总条数
select count(*) from product; -- 不推荐
select count(pid) from product;
select count(category_id) 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 category_id='c002';
#查询商品的最⼤价格和最⼩价格
select max(price),min(price) from product;

分组查询

分组查询是指使⽤group by字句对查询信息进⾏分组。
格式：SELECT 字段1,字段2… FROM 表名 GROUP BY分组字段 HAVING 分组条件;
分组操作中的having⼦语句，是⽤于在分组后对数据进⾏过滤的，作⽤类似于where条件。

having与where的区别:

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

练习

#统计各个分类商品的个数
select category_id,count(*) from product group by category_id;
#统计各个分类商品的个数,且只显示个数⼤于1的信息
SELECT category_id,count(*) from product GROUP BY category_id HAVING
count(*)>1;
#统计价格>200元的 各个分类商品的个数,且只显示个数⼤于1的信息
-- select * from product where price>200 group by category_id;
-- select category_id,count(*) from product where price>200 group by
category_id ;
select category_id,count(*) from product where price>200 group by
category_id HAVING count(*)>1;

条数限制查询

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); -- 总⻚数