一、高级查询
数据库主要是用于对数据进行存储,更新、删除、查询操作CURD。在这几种操作中,最重要的就是查询。
select 【all | distinct】 字段列表| * |字段名 【as】 别名 from 数据源 【as】 表别名 【where子句】【group by子句】
【order by子句】【having 子句】【limit 子句】
五子句:【where子句】【group by子句】【order by子句】【having 子句】【limit 子句】
五子句可以随意组合使用,但是组合中的顺序必须按语法的顺序书写。
前期数据
DROP TABLE IF EXISTS goods;
CREATE TABLE goods (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
brand CHAR(16),
goodsName CHAR(16),
channel CHAR(8),
price DECIMAL(10,2),
inventory SMALLINT UNSIGNED
)CHARSET utf8;
INSERT INTO goods VALUES (DEFAULT ,'huawei','mate','3G',2300,30);
INSERT INTO goods VALUES (DEFAULT ,'apple','6plus','4G',4500,43);
INSERT INTO goods VALUES (DEFAULT ,'apple','6splus','4G',5100,20);
INSERT INTO goods VALUES (DEFAULT ,'samsung','galaxy6','4G',3800,28);
INSERT INTO goods VALUES (DEFAULT ,'huawei',NULL ,'3G',3200,15);
INSERT INTO goods VALUES (DEFAULT ,'apple','5c','3G',1800,38);
INSERT INTO goods VALUES (DEFAULT ,'huawei','mate8','4G',3500,51);
INSERT INTO goods VALUES (DEFAULT ,'huawei','p8Max','4G',2700,27);
INSERT INTO goods VALUES (DEFAULT ,'apple','5s','3G',2300,38);
INSERT INTO goods VALUES (DEFAULT ,'Samsung','galaxy5','3G',3400,33);
1、select选项
【all | distinct】
说明:
all (默认)在显示结果中包含重复的数据
idstinct 在显示结果中去掉重复的数据。
此处的重复是在显示结果中的整行来对比
-- 显示有多少种品牌
SELECT DISTINCT brand FROM goods;
2、字段别名
只体现在结果显示,并不修改结构。目的为了更直观的显示数据
-- 别名
SELECT brand AS 品牌,goodsName AS 品名,price AS 价格 FROM goods;
3、数据源
所谓的数据源就是数据的来源。
数据源可以分为单表数据源,多表数据源,子查询数据源。
单表数据源: 数据的来源只来源于一个表
多表数据源: 数据的来源于多个表
子查询数据源: from后数据的来源是另一个select语句
前置表:
DROP TABLE IF EXISTS stu;
CREATE TABLE stu (
s_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
s_name CHAR(10) NOT NULL ,
s_sex CHAR(2) DEFAULT '男',
s_age TINYINT UNSIGNED DEFAULT 18,
c_id TINYINT UNSIGNED
);
INSERT INTO stu (s_id, s_name, s_sex, s_age, c_id) VALUES (DEFAULT ,'xiaoming','男',18,1);
INSERT INTO stu (s_id, s_name, s_sex, s_age, c_id) VALUES (DEFAULT ,'xiaohong','女',17,2);
INSERT INTO stu (s_id, s_name, s_sex, s_age, c_id) VALUES (DEFAULT ,'xiaogang','男',19,3);
INSERT INTO stu (s_id, s_name, s_sex, s_age, c_id) VALUES (DEFAULT ,'xiaohei','男',18,2);
INSERT INTO stu (s_id, s_name, s_sex, s_age, c_id) VALUES (DEFAULT ,'xiaoze','男',18,3);
DROP TABLE IF EXISTS class;
CREATE TABLE class(
c_id TINYINT UNSIGNED UNIQUE AUTO_INCREMENT,
c_name CHAR(8),
c_room CHAR(10)
)CHARSET utf8;
INSERT INTO class (c_id, c_name, c_room) VALUES (DEFAULT ,'php','401');
INSERT INTO class (c_id, c_name, c_room) VALUES (DEFAULT ,'java','402');
INSERT INTO class (c_id, c_name, c_room) VALUES (DEFAULT ,'c#','403');
①、单表数据源
-- 单表数据源
SELECT * FROM stu;
SELECT * FROM class;
②、多表数据源
语法:
select * from 表A,表B,...
说明:
多表数据源产生的结果是一个笛卡儿积。
-- 多表查询
SELECT * FROM stu,class;
③、子查询数据源
在子查询后必须要给一个别名
-- luci为别名
SELECT * FROM (SELECT * FROM stu) luci;
4、表别名
在一条select语句中引用一个表时,也可以为这个表起一个表别名,主要应用在连接查询中。
-- 表别名
SELECT * FROM stu AS s;
-- 使用表别民引用字段
SELECT s.s_name,s.s_sex FROM stu AS s;
5、where子句
①、使用
where子句,根据表达式在将数据从硬盘中读取到内存时进行第一次筛选。
SELECT * FROM stu WHERE c_id = '2';
②、原理
6、group by子句
语法:
group by 字段1,字段2,...
①、作用
group by 是对where子句得到的结果进行分组统计
②、原理
SELECT any_value(id),brand,any_value(goodsName),any_value(channel),any_value(price),any_value(inventory) FROM goods GROUP BY brand;
提示:
如果省略group by 表示,相当于所有的记录为一个大组,但是要把唯一组所有的记录都返回。
③、统计函数
group by重要是在统计上,MySQL对分组的数据提供了很多的统计函数
count(字段名 | *)
对group by 得到的每一组,求每一组的记录数。
count(字段名)
按指定的字段进行统计,如果某一条记录的被统计的字段是NULL值,这条记录并不会参与统计
-- 需求:统计每一品牌的记录数
select brand,count(brand) FROM goods GROUP BY brand;
select brand,count(goodsName) FROM goods GROUP BY brand;
count(*)
统计记录数,并不受NULL值的影响
-- 需求:统计每一品牌的记录数
select brand,count(*) FROM goods GROUP BY brand;
max(字段名)
统计每一组中指定的字段的最大值。
-- 需求:统计每一品牌的价格最大值
SELECT brand,max(price) FROM goods GROUP BY brand;
min(字段名)
统计每一组中指定的字段的最小值。
-- 需求:统计每一品牌的价格最小值
SELECT brand,min(price) FROM goods GROUP BY brand;
avg(字段名)
统计每一组中指定的字段的平均值。
-- 需求:统计每一品牌的价格平均值
SELECT brand,avg(price) FROM goods GROUP BY brand;
sum(字段名)
统计每一组中指定的字段的总值。
-- 需求:统计每一品牌的库存总数量
SELECT brand,sum(inventory) FROM goods GROUP BY brand;
④、回溯统计
语法:
with rollup;
多字段进行分组,统计函数默认是应用在最小组上的,如果相对包含最小组(最小组之上)的大组进行同样的统计,那么with rollup就可以实现这个需求
-- 需求:分别统计每种品牌3G和4G手机的库存总量
SELECT any_value(brand) AS brand,any_value(channel) AS channel,sum(inventory) AS sum FROM goods GROUP BY channel,brand with ROLLUP;
7、having子句
作用:
where 是在将硬盘中的数据读取到内存是进行第一次筛选
group by是针对where子句匹配到的记录进行分组统计
having是针对group by分组统计得到的结果再进行第二次筛选
-- 需求:查找每一种品牌中,库存数量大于100的品牌
SELECT brand,sum(inventory) FROM goods GROUP BY brand HAVING sum(inventory)>100;
扩展:
统计函数,是不能出现在where子句中
having可以替代where子句,但是强烈不建议这么用(个人理解:where是在数据读取到内存之前进行筛选,不符合条件的数据并不进入到内存中,而having省略了group by相当于每个数据为一个分组,即先把所有数据读取到内存中再对每个分组进行数据筛选)
8、order by子句
语法:
order by 字段1【asc | desc】,字段2【asc | desc】,...
说明:
order by 对where子句、group by子句、having子句得到的结果进行一个显示顺序上的控制
asc 缺省值,升序
desc 降序
-- 需求:查找所有的商品按价格进行升序排序
SELECT * FROM goods ORDER BY price ASC;
-- 需求:查找所有的商品按价格进行降序排序
SELECT * FROM goods ORDER BY price DESC;
多字段排序:
-- 需求:先对品牌进行升序排序,如果品牌相同再对价格进行降序排序
SELECT * FROM goods ORDER BY brand ASC ,price DESC;
扩展:
-- 需求:输出每个品牌中最贵的手机信息
SELECT *,max(price) FROM (SELECT * FROM goods ORDER BY price DESC) lucy GROUP BY brand;
9、limit子句
语法:
limit 【offset,】rows
说明:
limit在select中是对where子句、group by子句、having子句、order by子句得到的结果进行一个显示行数的控制。
-- 需求:显示前五行
SELECT * FROM goods LIMIT 5;
-- 需求:显示价格较低的前五款手机
SELECT * FROM goods ORDER BY price ASC LIMIT 5;
offset 偏移量,如果省略表示为0
rows 显示的函数
运用实例:
数据分页:
分页相关的因素:
每一页显示的记录数: rowsPetPage 人为设定
当前的页码数: curPage 用户所要浏览的页码
假设:rowsPetPage = 3 表示每一页显示3条记录
curPage = 3 表示当前用户要浏览第三页的信息
-- 需求:数据分页SELECT * FROM goods LIMIT rowsPetPage*(curPage-1),rowsPetPage
-- 用预定义SQL语句 SELECT * FROM goods LIMIT ?,?;
-- 第一页
SELECT * FROM goods LIMIT 0,3;
-- 第二页
SELECT * FROM goods LIMIT 3,3;