mysql中DQL操作

查询语句语法结构

select [字段名],[函数(字段名)]
From 数据表名
[连接]
[where子句条件判断]
[group by分组]
[having 二次筛选]
[order by 字段名]
[limit x,x]
[offset x];

简单查询

--1查询所有字段所有数据
    select * from 数据表名;
    select * from sale; -- * 代表所以字段
-- 2 查询部分字段数据
		-- select 字段名称,字段名称2,字段名称3 from 数据表名 
		-- 查看销售表中的订单编号，产品编号，数量
       select order_id,product,number from sale；
		
			-- 列题1：想要看评分和播放量的情况
			SELECT `播放量`,`评分`from `爱奇艺视频数据`;
			
			-- 列题2：想要看电视剧的演员与电视剧的类型
			SELECT `演员`,`类型` from `爱奇艺视频数据`;

sql语句中进行简单算术 + - * / %，where

         --  1 ：+ - * / %
		-- SELECT 字段名称，字段名称2，字段名称2*0.8 from 数据表名
		-- 电商活动，所有商品8折优化，计算所有商品的单价
        SELECT order_id,product,number,number*0.8 from sale;
			-- 查看电视剧的好评率
			SELECT * ,`好评数`/`评分人数` 好评率 from `爱奇艺视频数据` ;-- 好评率是取的别名
			添加条件筛选数据
		-- 对数据按照条件进行查询需要使用where子句添加逻辑条件判断
        SELECT *from 数据表名 where 字段名 = 4;
		-- 2 ：简单逻辑判断
			-- > >= < <= = !=
			-- 查看4号销售员卖出去的所有订单
            SELECT *from sale where salesman = 4;
			
				-- 想要看评分和播放量的情况,并且没完结的不看
				SELECT `评分`,`播放量`,已完结  from `爱奇艺视频数据` WHERE `状态` = '已完结';
				-- 想要看电视剧的演员与电视剧的类型,并且出过两季以上
				SELECT `演员`,`类型`,`总集数` from `爱奇艺视频数据` WHERE `总集数`>=2;

区分大小写查询

-- mysql默认不区分大小写，需要在条件字段前添加binary关键字支持区分大小写
select * from sale where order_id="YD19004"；
select * from sale where binary order_id="YD19004"；-- binary区分大小写，

多条件

and 和

			-- 1.多条件and--多个条件必须同时满足
			-- 查询4号销售员卖的所有的XXXXX商品的订单
       	   SELECT * from sale where salesman = 4 and product = 8605001;
				-- 2.想要看评分和播放量的情况,并且没完结的不看、播放量大于100000000
				SELECT `评分`,`播放量` from `爱奇艺视频数据` WHERE `状态` = '已完结'and `播放量` >100000000;

or 或者

            --  1. 多条件or--多个条件只需要满足其中一个
			 SELECT * from 数据表 WHERE 字段名 条件 or 条件;
			-- 查询4号或者9号销售员买出去的所有订单
            SELECT * from sale where salesman = 4 or 9;-- or
				-- 想要看评分和播放量的情况,只看08年或12年上映的
				select `评分`,`播放量`,`上映时间` FROM `爱奇艺视频数据` where `上映时间`=2008 or `上映时间`=2012;

like模糊匹配

-- 使用like进行模糊查询，模糊的对字段进行匹配，%标识任意个任意字符
			-- 判断开头：前面不加%
			-- 判断结尾：后面不加%
			-- 查询所有的zh开头的订单
      -- like 一定搭配通配符使用
      --  匹配一个字符的通配符是_   like "%于正_"  输出结果 于正化/于正思	
				-- 想要看评分和播放量的情况,只看喜欢的演员演的
				SELECT * from `爱奇艺视频数据` where 演员 like "%胡歌%";
				
				-- 想要看语言\地区\简介,只看悬疑剧且超清的
				SELECT `语言`,`地区`,`简介`,`类型` FROM `爱奇艺视频数据` WHERE `类型` LIKE "%悬疑%" and 类型 like "%超清%";
				SELECT `语言`,`地区`,`简介`,`类型` FROM `爱奇艺视频数据` WHERE `类型` LIKE "%悬疑%/%超清%";
				
				-- 想要看评分\播放量\总集数,并且只看喜欢的导演于正和其他导演的电视剧
        SELECT 评分,播放量,总集数,导演 from `爱奇艺视频数据` where 导演 like "%于正/%" -- 输出于正/xxx/xx

NULL判断

			-- is null 判断为空的数据
			-- is not null判断不为空的数据
			-- 查询所有单价为空的产品
         SELECT * from product where PRICE is not;
			
				-- 查询出所有演员为空的电视剧
				SELECT * from `爱奇艺视频数据` where 演员 is null;
				
				-- 查询所有演员不为空的电视剧
				SELECT * from `爱奇艺视频数据` where 演员 is not null;

between与not

-- 介于与不介与使用between与not
			
			SELECT * from 数据表名 where 字段名 not between 条件 and 条件;-- 不介与
			SELECT * from 数据表名 where 字段名 between 条件 and 条件;-- 介于
			
			-- 查询1-5号销售员卖出去的所有订单
      SELECT * from sale where salesman  not between 1 and 5;
			
      SELECT * from sale where salesman  between 1 and 5;    

in()与not in(

       -- 查询所有的组长卖出去的订单
        SELECT * from sale where salesman in(1,6,13);
   	    SELECT * from sale where salesman not in(1,6,13)
		
				-- 查询出所有的国产剧\美剧\韩剧
				SELECT * from 爱奇艺视频数据 where 类型 in(国产剧,美剧,韩剧);
				
				-- 查询出所有不包括05年\08年\12年的电视剧
				SELECT * from 爱奇艺视频数据 where 上映时间 in(05,08,12);
				

起别名

 -- SELECT 原字段名 新字段名 from 数据表名；
	        -- 将销售员的每个字段都改成中文
			SELECT 好评数/评分人数 好评率 from `爱奇艺视频数据`;-- 第一种方法
			SELECT 好评数/评分人数 as 好评率 from `爱奇艺视频数据`;-- 第二种方法
			

函数

单行函数

数值函数

        select abs(-45) 取绝对值, -- 取绝对值      输出：45
			   ceil(9.001)  向上取整,-- 向上取整   输出：10
			   floor(8.9999) 向下取整,-- 向下取整  输出：8
			   round(9.45678) 四舍五入 -- 四舍五入 输出：9
		 from dual -- dual 虚拟表

字符串函数

-- 常用的有小写,大写,计算长度、截取部分字符串、字符串拼接
    select
		lower("ABCD") 大写转小写, -- 大写转小写 输出:abcd
        upper("abcd") 小写转大写, -- 小写转大写 输出：ABCD
		length("中国") 计算字符串长度,-- 计算字符串长度 输出：6 (一个中文是3个字符)
		-- left()   right()
		substr("四川成都",3,2)截取部分字符,-- 3，第几位 2，截取几位，输出：成都
		concat("中国","成都") 字符串拼接 -- 字符串拼接 输出：中国成都

日期与时间函数

        -- curdate()年月日
		-- curtime()时分秒	
		-- now()年月日时分秒 当前语句执行的时间
		-- sysdate()年月日时分秒 当前函数执行的时间
		-- sleep(3)表示暂停,括号内写数值,单位为秒,上例意思就是查询的时候暂停3秒
		-- 虽然可以表示年月日时分秒,但是插入数据的时候,还是要参照表的结构
		-- date_format(date, "%Y-%m-%d")
		-- date_add(date, interval expr type) 原时间加一个日期
		-- date_sub(date, interval expr type) 原时间减一个日期
		-- datediff(date1, date2) 两日期之间相隔天数
        select curdate(),curdate(),now(),sysdate() 
		 SELECT NOW(),SYSDATE(),SLEEP(3),NOW(),SYSDATE() -- sleep(3) 代表运行暂停3秒
		 select date_format(date,"%Y年%m月%D月") from sale; -- 大写%D 输出是英文th，-- d小写是中文 月
		 -- date_format()取出来的数据是字符串
		 select month(date) from sale;
		
		 -- month year 1 day 取出来的数据是数字 
		 select date,date_add(date,interval 5 day)from sale;-- intervsal 5 day代表添加天数
		 select date,date_add(date,interval 2 year)from sale;-- interval 2 year 代表添加多少年
		 
		 select date,
		 date_add(date,interval 10 day),-- 日期加运算
		 date_sub(date,interval 10 year)-- 日期减运算
		 from sale;
		 
		 select datediff("2022-10-31","2021-10-30") -- （相差天数）第一个日期减第二个日期 输出：366
        select abs(datediff("2020-10-31","2021-10-30")) 

			-- 商品毛利率毛利额/销售额四舍五入取整,并且算出毛利率(百分比)
			select concat(round(毛利额/销售额*100, 2),"%")from `商品_毛利率`
		

多行函数/聚合函数

-- 查看销售记录表中number字段的最大值，最小值，求和，平均值与计数
select max(number) 最大值,
       min(number) 最小值,
	   sum(number) 求和,
	   avg(number) 平均值,
	   count(number) 计数 -- 计算所有非空值的数量
 from sale;

SELECT count(`数据获取日期`),
      count(`演员`),
			count(*),-- 如果所有字段都为空，则不计算
			count(1) -- 只要有一条记录，那么对1计数一次
 from `爱奇艺视频数据` 

条件判断函数

if判断

-- 销量数据表，number大于200为达到满减，其他为小额订单
      select *,if(number>200,"达到满减","小额订单")from sale;

case—end判断

	-- 1，根据电视剧评分，将电视剧分为封神、值得推荐、下饭视频
     SELECT 整理后剧名,评分,
		 case
		 when 评分>=9 then "封神"
		 when 评分>=7 then "值得推荐"
		 else "下饭视频"
		 end type-- type新建字段起的别名
		 from `爱奇艺视频数据` 

--2，销售分组表，销售三组发奖金，销售一组扣绩效
     select *,
		 case team -- team字段
		 when "销售三组" then "发奖金"
		  when "销售一组" then "扣绩效"
			else team -- 等于他本身的值team
			end type -- type给字段起的别名
			from salesman

窗口函数排名

    -- rank函数：如果有并列名次的行，会占用下一名次的位置,输出结果：1，1，1，4。
	-- dense_rank函数：如果有并列名次的行，不占用下一名次的位置。输出结果是：1，1，1，2
	-- row_number函数：不考虑并列名次的情况。输出结果是：1，2，3，4
	-- <窗口函数> over (partition by <用于分组的列名> order by <用于排序的列名>
	-- 对成绩表按科目进行成绩排名
        select *,
				rank() over(
				partition by cid -- 用于分组的列
				ORDER BY score desc -- 用于排序的列名
				) 排名--  代表取的字段名称
				from sc 
				
				 select *,
				dense_rank() over(
				partition by cid 
				ORDER BY score desc 
				) 排名2 --  代表取的字段名称
				from sc 
				
				 select *,
				row_number() over(
				partition by cid 
				ORDER BY score desc 
				) 排名3 --  代表取的字段名称
				from sc 
				
				-- 查看成绩表的累积和
              select *,
			  sum(score) over(
				partition by cid -- 分组依据
				ORDER BY score desc 
				) 求和
				from sc 

去重

-- 对查询出来的数据进行去重
	-- distinct
	-- 查看sale表中所有的销售员有哪些
  SELECT distinct salesman from sale;
	SELECT count(distinct product) from sale; 

分组group by

-- group by
	-- 按条件进行对数据进行分组，可以完成数据的统计
	-- 1 单个字段进行分组
		-- 统计sale表中每一个销售员卖出去多好订单
		 SELECT salesman,COUNT(*) 订单量
		from sale
		GROUP BY salesman  -- 各个分组里的计算
		
      SELECT salesman,COUNT(DISTINCT order_id)订单量
		from sale
		GROUP BY salesman

	-- 2 多个联合分组
		-- 统计sale表中每一个销售员每种产品卖了多少个
        select salesman,product,sum(number) 数量
		from sale
		GROUP BY salesman,product

-- 爱奇艺数据统计每一年多少部悬疑剧上映
		select 上映时间, count(1) 数量
		from `爱奇艺视频数据`
		where `类型` like "%悬疑%"
		group by 上映时间
-- 餐饮数据统计每种菜有多少加餐厅,平均人均消费多少元
		select 类别, count(1) 餐厅数量, avg(`人均消费`) 平均人均消费
		from `上海餐饮数据`
		group by 类别

二次筛选having

-- having
	-- 对where子句查询出来的结果进行二次过滤筛选
	-- 统计所有销售员中卖出去的产品数量超过1000的员工有哪些
    SELECT cid,avg(score)平均分
    from sc
    group by cid
    having 平均分>70;
	
		-- 爱奇艺数据统计每一年有多少部电视剧上映,并且上映电视剧超过1000部的年份有哪些
		select 上映年份 -- , count(1) num
		from `爱奇艺视频数据`
		group by 上映年份
		having count(1)>1000

排序order by

	-- asc 升序  desc 降序
	-- 统计每个销售员卖出去的所有产品的数量，并进行降序排序
     SELECT salesman,product,number
	 from sale 
	 order by number DESC
	
		-- 爱奇艺数据查看喜欢的演员演的电视剧,按评分进行降序排序
		SELECT *,
		 rank() over(
		 partition by `剧名`
		 order by 评分 DESC
		 )排名
	    from `爱奇艺视频数据` -- 排名
 

分页limit

    -- 查询第N页的数据limit公式为(n-1)*count
	-- 统计每个销售员卖出去的所有产品的数量，并降序排序，取前3名销售员
select salesman,sum(number)snum
from sale
GROUP BY salesman
ORDER BY snum desc
limit 0,3;-- 第一个参数0代表跳过前几个数，第二个参数3,代表取几个数为一页
-- 取第n页的内容：（n-1）*count,count

		-- 爱奇艺数据查看喜欢的演员播放量前三的电视剧有哪些
		select `整理后剧名`,`演员`,sum(`好评数`)
		from `爱奇艺视频数据`
		where 演员 like '%杨幂%'
		group by `整理后剧名`,`演员`
		order by 好评数 desc
		limit 3,2 

跳过N条数据 offset

-- offset
	-- 查询指定的第几条数据的时候可以使用limit配合offset来使用
	-- limit y offset x 分句表示: 跳过 x 条数据，读取 y 条数据
	-- 统计每个销售员卖出去的所有产品的数量，并降序排序，取前3至6名销售员
select salesman,sum(number)snum
from sale
GROUP BY salesman
ORDER BY snum desc
limit 3
offset 9;-- 跳过9，取3为一页

多表连接查询

内连接 using子句\on子句

-- 1 using子句
			-- 需要指定关联字段名，且字段名必须相同。
			/*
			select column1,column2,column3
			from table1
			 join table2 
			using (column);
			*/
			-- 查询product表中每个产品对应的品牌名称
            SELECT *
			from product
			join brand
			using(brand_id);-- 链接二个表，合并表
			
			-- 查询00072-口品牌的所有产品有哪些
			SELECT *
			from product
			join brand
			using (brand_id)
			WHERE brand_id=00072;
-- 1 on子句
			-- 需要指定关联字段名，但是字段名可以不相同。
			/*
			select t1.col1,t1.col2,t2.col1
			from table1 t1
		    join table2 t2 
			on (t1.col = t2.col);
			*/
		/*	select *
        from  数据表1  取的别名s
        join  数据表2  取的别名ss
        on  别名. 数据表1字段= 别名.数据表2字段*/ -- 链接二个表
			
			-- 查询sale表中每张订单的销售员姓名
         select *
		 from sale s-- s取的别名
		 join salesman ss
		 on s.salesman = ss.id
		
			-- 统计二组每个销售员卖出的订单数量总合
			 SELECT salesman,sum(number) snum
			from sale s
			join salesman ss
		    on s.salesman = ss.id
			WHERE team = "销售二组"
			GROUP BY salesman
			
			-- 统计每个月订单销售产品数超过100的订单数量总和
			SELECT month(date)mon,sum(price*number)销售额
			from sale s
			join product p
			on s.product = p.product_id
			GROUP BY mon

外连接

                union -- union的作用就是取并集,并去重
				union all -- union all的作用就是取并集但是不去重
		-- 1 左外连接
			/*
			select t1.col1,t1.col2,t2.col1
			from table1 t1
			left [outer] join table2 t2 
			on (t1.col = t2.col);
			*/
			
			-- 查询每一个品牌旗下的每一种商品,需要保留所有的品牌信息
     SELECT * from brand b left join product p
	 on b.brand_id = p.brand_id
	 union all
  
	SELECT * from brand b right join product p
	 on b.brand_id = p.brand_id
		-- 2.右外连接
			/*
			select t1.col1,t1.col2,t2.col1
			from table1 t1
			right [outer] join table2 t2 
			on (t1.col = t2.col);
			*/
		
	-- 查询每一个品牌旗下的每一种商品,需要保留所有的品牌信息
			select *
			from product p 
			right join brand b
			on p.brand_id = b.brand_id
			where p.product_name is null

		-- 3 全外连接
			/*
			select t1.col1,t1.col2,t2.col1
			from table1 t1
			full outer join table2 t2 
			on (t1.col = t2.col);
				MySQL不支持全外连接，可以使用左右外连接取并集的方式实现同样的效果
				
			*/

自然连接natural join

-- 查询出来的结果是所有能匹配上的数据。取交集。
	-- 自动根据相同字段名，将所有能匹配的数据进行连接。
	-- 如果没有指定需要查询的字段，会遍历所有表中的所有字段，执行效率低。
	/*
	select column1,column2,column3
	from table1
	natural join table2;
	*/
	-- 查询product表中每个产品对应的品牌名称
    select *
	from product
	natural join brand;

交叉连接cross ioin

	/*
	select column1,column2,column3
	from table1
	cross join table2;
	*/
	-- 查询product和brand表能匹配到的所有可能
    select * 
	from sc
	CROSS join df -- 第一种方法
	
	 select * 
	from sc,df -- 第二种方法

自连接

	-- 自己关联自己，需要一张表中的一个字段与另一个字段有关联。
	/*
	select t1.col1,t1.col2,t2.col1
	from table1 t1
	join table1 t2 
	on (t1.col = t2.col);
	*/
	-- 查询每个组员的组长的姓名
    select *
	from salesman s1
	join salesman s2
	on s1.mgr = s2.id

多表连接join on

	-- 三张及以上表格连接查询的时候，其实可以当做前两张表查询的结果再与第三张表进行连接查询。
	/*
	select t1.col,t1.col,t2.col,t3.col
	from table1 87865068
	join table2
	on (t1.col = t2.col)
	join table3
	on (t1.col = t3.col);
	*/
	-- 查询每个销售员的姓名与销售金额总和
    -- （拆分） salesman表里的 姓名
	-- 销售金额 单价*数量
	-- 单价 来自product表
	-- 数量 来自sale表
SELECT ss.name, sum(price*number) 金额
from sale s
join salesman ss
on s.salesman = ss.id
join product p
on s.product = p.product_id
GROUP BY ss.name

-- 查询销售订单中数量大于1000的订单编号、销售员名称、销售日期、数量、金额
select order_id,name,date,number,price*number amount
from sale s
join salesman ss
on s.salesman = ss.id
join product p
on s.product = p.product_id
where number > 1000

-- 查询每个产品对应的品牌名称与销售数量总和及销售金额总和
SELECT s.product,b.brand_name,sum(number) s_num,sum(number*price) amount
FROM sale s
join product p
on s.product=P.product_id
join brand b
on s.brand = b.brand_id
GROUP BY s.product

子查询

-- 一个查询语句嵌套在另一个SQL语句内部的查询,
    -- 不相关子查询 子查询可以单独运行 
		-- 单行子查询 子查询的结果只有一个值
		 -- 查询sale表中所有订单数量超过平均数的订单的数量
         select count(distinct order_id)
		 from sale
		 where number > (SELECT avg(number) from sale )

		-- 多行子查询 子查询的结果是一个列表
		-- 查询sale表中各个组长卖出去的订单的数量
        select salesman,count(distinct order_id) 订单数量
		from sale 
		where salesman in(SELECT id from salesman where mgr=0)
		GROUP BY salesman

		-- 子查询的结果作为一个新的表,子查询的结果必须起别名
		-- 找出销售数量前三名的员工的姓名
        -- 拆分:找出销售数量前三名的员工的姓名
		 -- 先算出每一个销售员销量的数量
		  -- 找前三名
     SELECT *
	   from (
	       SELECT *,
		   DENSE_RANK() over(
		   order by s_num desc
			 ) ranking
		   from(
			     SELECT salesman,sum(number) s_num
				 from sale
				 GROUP BY salesman)t1)t2
	 where ranking <=3

相关子查询

   -- 相关子查询
	-- 子查询不能独立运行
	-- sc表中,每一科超过平均分的学生
  SELECT * from sc where  cid =1 and score > (SELECT avg(score) FROM sc WHERE cid = 1) 
	UNION
	SELECT * from  sc where   cid =2 and score > (SELECT avg(score) FROM sc WHERE cid = 2) 
		UNION
	SELECT * from  sc where  cid =3 and score > (SELECT avg(score) FROM sc WHERE cid = 3);

SELECT * FROM sc s where score >(select avg(score) from sc where s.cid = cid)

-- 查询订单数量大于平均值的订单信息
SELECT *
from sale
where number >(SELECT avg(number) from sale)

通用表达式 with as

	-- 通用表表达式（CTE）是一个在语句级别定义的临时结果集。
	-- 作用范围是当前语句,CTE可以理解成一个可以复用的子查询
	-- 当然跟子查询还是有点区别的，CTE可以引用其他CTE，但子查询不能引用其他子查询
	-- with 通标表达式名 as (查询语句)
	-- 定义多个通用表达式的时候,前一个通用表达式与后一个通用表达式之间用逗号隔开
	-- with 只需要写一次

with cte1 as(  
     SELECT salesman,sum(number) s_num from sale GROUP BY salesman),
		 cte2 as(
		 select *,
		 DENSE_RANK() OVER(
		 order by s_num desc) ranking
		 from cte1)
     SELECT *from cte2 where ranking<=3;