MySQL基础(三) 学习笔记

一、高级查询

             数据库主要是用于对数据进行存储,更新、删除、查询操作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;

             

评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值