2.4 DQL语句
2.4.1 准备工作
创建商品表:
案例演示:
#商品表
CREATE TABLE product (
pid INT PRIMARY KEY AUTO_INCREMENT, # 自增加 AUTO_INCREMENT
pname VARCHAR(20),#商品名称
price DOUBLE, #商品价格
pdate DATE, # 日期
cid int #分类ID
);
#目录表
create table category(
id INT PRIMARY KEY ,
cname varchar(100)
);
INSERT INTO product VALUES(NULL,'泰国大榴莲', 98, NULL, 1);
INSERT INTO product VALUES(NULL,'泰国大枣', 38, NULL, 1);
INSERT INTO product VALUES(NULL,'新疆切糕', 68, NULL, 2);
INSERT INTO product VALUES(NULL,'十三香', 10, NULL, 2);
INSERT INTO product VALUES(NULL,'泰国大枣', 20, NULL, 2);
insert into product values(null,'泰国大枣',98,null,20); #没有对应
insert into product values(null,'iPhone手机',800,null,30);#没有对应
INSERT INTO category VALUES(1,'国外食品');
INSERT INTO category VALUES(2,'国内食品');
INSERT INTO category VALUES(3,'国内服装'); #没有对应
2.4.2 完整DQL语法顺序
SELECT DISTINCT
< select_list >
FROM
< left_table > < join_type >
JOIN < right_table > ON < join_condition >
WHERE
< where_condition >
GROUP BY
< group_by_list >
HAVING
< having_condition >
ORDER BY
< order_by_condition >
LIMIT < limit_number >
2.4.3 简单查询
SQL语法关键字:
SELECT
FROM
案例:
1)查询所有的商品.
select * from product;
2)查询商品名和商品价格.
select pname,price from product;
3)别名查询,使用的as关键字,as可以省略的.
表别名:
select * from product as p;
列别名:
select pname as pn from product;
4)去掉重复值.
select distinct price from product;
5)查询结果是表达式(运算查询):将所有商品的价格+10元进行显示.
select pname,price+10 from product;
2.4.4 条件查询
SQL语法关键字:
WHERE
案例:
1)查询商品名称为十三香的商品所有信息:
select * from product where pname = '十三香';
2)查询商品价格>60元的所有的商品信息:
select * from product where price > 60;
where后的条件写法:
> ,<,=,>=,<=,<>
like 使用占位符_和%:_代表一个字符 %代表任意个字符.
select * from product where pname like '%新%';
in在某个范围中获得值(exists).
select * from product where pid in (2,5,8);
2.4.5 排序
SQL语法关键字:
ORDER BY
ASC(升序) DESC(降序)
案例:
1)查询所有的商品,按价格进行排序.(asc-升序,desc-降序)
select * from product order by price;
2)查询名称有新的商品的信息并且按价格降序排序
select * from product where pname like '%新%' order by price desc;
2.4.6 聚合函数(组函数)
特点:只对单列进行操作
常用的聚合函数:
sum():求某一列的和
avg():求某一列的平均值
max():求某一列的最大值
min():求某一列的最小值
count():求某一列的元素个数
案例:
1)获得所有商品的价格的总和:
select sum(price) from product;
2)获得所有商品的平均价格:
select avg(price) from product;
3)获得所有商品的个数:
select count(*) from product;
2.4.7 分组
SQL语法关键字:
GROUP BY
HAVING
案例:
1)根据cno字段分组,分组后统计商品的个数.
select cid,count(*) from product group by cid;
2)根据cno分组,分组统计每组商品的平均价格,并且平均价格> 60;
select cid,avg(price) from product group by cid having avg(price)>60;
注意事项:
group by子句中的列,不一定要出现在select语句中
聚合函数只能出现select语句中或者having语句中,一定不能出现在where语句中。
2.4.8 分页查询
关键字:
lIMIT [offset,] rows
lIMIT 关键字不是SQL92 标准提出的关键字,它是MySQL 独有的语法。
通过limit 关键字, MySQL 实现了物理分页。
分页分为逻辑分页和物理分页:
逻辑分页:将数据库中的数据查询到内存之后再进行分页。
物理分页:通过LIMIT关键字,直接在数据库中进行分页,最终返回的数据,只是分页后的数据。
格式:
SELECT * FROM table LIMIT [offset,] rows
offset :偏移量
rows :每页多少行记录。
案例:
分页查询商品表,每页3条记录,查第一页
SELECT * FROM product LIMIT 0,3
2.4.9 子查询
定义:
子查询允许把一个查询嵌套在另一个查询当中。
子查询,又叫内部查询,相对于内部查询,包含内部查询的就称为外部查询。
子查询可以包含普通select,可以包括的任何子句,比如:distinct、 group by、order by、limit、join和union等;
但是对应的外部查询必须是以下语句之一:select、insert、update、delete。
位置:
select中、from 后、where 中.
group by 和order by 中无实用意义。
举例:
查询“化妆品”分类下的商品信息
SELECT * FROM (SELECT p.* FROM product p, category c where p.cid=c.id and c.cname=’化妆品’);
2.4.10 其他查询语句
union 集合的并集(不包含重复记录)
unionall 集合的并集(包含重复记录)