一、高级查询
数据库主要是用于对数据进行存储，更新、删除、查询操作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;
扫一扫
8314
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
