Java数据库 1.4 SQL 语句 DQL

准备工作

#创建商品表：
pid int
pname varchar(20)
price double
category_id varchar(32)
插入一条数据:
pid=1,pname='联想',price=5000,category_id='c001'
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;

执行结果:

#别名查询.使用的关键字是as（as可以省略的）.表别名:
select * from product as p;

执行结果:

#别名查询.使用的关键字是as（as可以省略的）.列别名:
select pname as pn from product;

#去掉重复值.
select distinct price from product;

#查询结果是表达式（运算查询）：将所有商品的价格+10元进行显示.
select pname,price+10 from product;

条件查询

运算符	< <= = != <>	大于, 小于, 等于, 不等于
比较运算符	BETWEEN…AND…	显示在某一区间的值 (含头含尾)
	IN (set)	显示在 in 列表中的值, 例: in(100,200)
	LIKE’张 pattern	模糊下旬, 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
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

排序查询

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

格式:

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

练习

查询 id 为 1 3 5 7 的商品, 并按价格降序排列.

#使用价格排序(降序)
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 sum(price) as 'totalprice' 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 后面不可以使用聚合函数

练习

/*
学生成绩表scores:
sid: 学生成绩的id
score: 成绩
sname: 课程
*/
CREATE TABLE scores(
 sid INT PRIMARY KEY,
 score INT,
 sname VARCHAR(200)
);
INSERT INTO scores(sid,score,sname) VALUES(1,90,'语文');
INSERT INTO scores(sid,score,sname) VALUES(2,70,'数学');
INSERT INTO scores(sid,score,sname) VALUES(3,90,'外语');
INSERT INTO scores(sid,score,sname) VALUES(4,50,'语文');
INSERT INTO scores(sid,score,sname) VALUES(5,90,'数学');
INSERT INTO scores(sid,score,sname) VALUES(6,80,'外语');
INSERT INTO scores(sid,score,sname) VALUES(7,100,'语文');
INSERT INTO scores(sid,score,sname) VALUES(8,20,'数学');
INSERT INTO scores(sid,score,sname) VALUES(9,100,'外语');
INSERT INTO scores(sid,score,sname) VALUES(10,75,'编程');

统计各个科目的成绩书:

SELECT sname, COUNT(*) FROM scores GROUP BY sname;

执行结果:

统计各个科目的个数, 且只显示个数大于 1 的信息:

SELECT sname, COUNT(*) FROM scores GROUP BY sname HAVING COUNT(*) > 1;

执行结果:

计算每个科目的平均得分:

SELECT sname, AVG(score) FROM scores GROUP BY sname;

执行结果:

计算每个科目的平均得分, 且只显示平均分低于 80 的信息:

SELECT sname, AVG(score) FROM scores GROUP BY sname HAVING AVG(score) < 80;

执行结果: