MySQL1

 多表查询

函数

-- 多表查询

-- 子查询关键字-ANY SOME
-- 任意一个

-- 查询年龄大于‘1003’部门任意一个员工年龄的员工信息
select * from emp3 where age > any(select age from emp3 where dept_id ='1003') and dept_id !='1003';

-- 子查询关键字 IN
-- 里面所有,指定集合

-- 查询研发部和销售部的员工信息，包括员工号，员工姓名
select eid,ename from emp3 where in(slect deptno from dept3 where name='研发部' or name='销售部');

-- 子查询关键字 EXISTS
-- ture(EXISTS有至少一行输出则返回true) 外部执行，前面输出

select * from emp3 where exists(select 1);
select * from emp3 where exists( select * from emp3);-- (全表输出）

-- 查询公司是否大于60岁的员工有输出
select * from emp3 where exists (select * from emp3 where age>60);-- 全表输出
select * from emp3 a where exists(select * from emp3 where a.age >60 );

-- 查询有所属部门的员工信息
select * from emp3 a where exists(select * from dept3 b where a.dept3_id=b.deptno);
select * from emp3 a where dept_id in(select dept_id from dept3 b where a.dept3_id=b.deptno);-- 用in来替换




-- 子关联查询

-- 聚合函数group_concat
-- 某列合并一行
select group_concat(emp_name) from emp;
-- 分隔符
select group_concat(emp_name separator ';') from emp;
-- 指定排序方式和分隔符
select department,group_concat(emp_name separator ';') from emp group by department;
select department,group_concat(emp_name order by salary desc separator ';') from emp group by department;


-- 数学函数
select abs(-10);

select ceil(1.1);-- 向上取整
select floor(1.9);-- 向下取整

-- 去列表最大值
select greatest(1,2,3);
-- 去列表最小值
select least(1,2,3);

-- 取模（余）
select mod(5,2);

-- 次方
select power(2,3);-- 8

-- 取随机数
select rand();
select floor(rand()*100);-- 0-100

-- 取小数的四舍五入
select round(3.5415);-- 3
select round(3.5415,3);  -- 3.542

-- 将小数直接截取到指定位数
select truncate(3.1415,3);  -- 3.141



-- 字符串函数

-- 1.获取字符串的个数
select char_length('hello');-- 5
select char_length('你好吗');-- 3

-- 字节
select length('hello');-- 5
select length('你好吗');-- 9一个汉字占一个字节

-- 2.字符串合并
select concat('hello','world');
-- 有分隔符
select concat_ws('_','hello','world');

-- 3.返回字符串在列表中第一次的位置
select field('aaa','aaa','bbb','ccc');-- 1
select field('bbb','aaa','bbb','ccc');-- 2

-- 4.去除字符串左边空格
select ltrim('    aaa');
select rtrim('          aaa   ');-- 右
select trim('    aaa      ');-- 左右

-- 5.字符串截取
select mid("helloworld",2,3);-- 从第二个字符开始截取，截取长度为3

-- 6.获取字符串A在字符串中出现的位置第一次
select position('abc' in 'habcelloabcworld');-- 2

-- 7.字符串替换
select replace('aaahelloaaaworld','aaa','bbb');-- bbbhellobbbworld

-- 8.字符串的反转
select reverse('hello');

-- 9.返回字符串后n个字符
select right('hello',3);

-- 10.字符串比较
select strcmp('hello','world');-- s1>s2 1;s1<s2 -1;

-- 11.字符串截取
select substr('hello',2,3);-- 从第二个字符开始截取，截取长度为3
select substring('hello',2,3);-- 从第二个字符开始截取，截取长度为3

-- 12.将小写转大写
select ucase("helloworld");
select upper("helloworld");

-- 14.将大写转小写
select lcase("HELLOWORLD");
select lower("HELLOWORLD");


-- 日期函数
-- 1.获取时间戳（毫秒值）；
select unix_timestamp();

-- 2.将一个日期字符串转换为毫秒值
select unix_timestamp('2021-12-21 08:08:08');

-- 3.将时间戳毫秒值转为指定格式的日期
select from_unixtime(1640045288,'%Y-%m-%d %H:%i:%s');

-- 4.获取当前的年月日
select curdate();
select current_date();

-- 5.获取当前的时分秒
select current_time();
select curtime();

-- 6.获取当前的年月日时分秒
select current_timestamp();

-- 7.从日期字符串中获取年月日
select date('2024-01-27 18:03:56');

-- 8.获取日期之间的差值
select datediff('2021-12-23','2008-08-08');
select datediff(current_date,'2008-08-08');

-- 9.获取时间的差值（秒级）
select timediff('12:12:34','10:18:56');

-- 10.日期格式化
select date_format('2021-1-1 1:1:1','%Y-%m-%d %H:%i:%s');

-- 11.将字符串转为日期
select str_to_date("August 10 2017","%M%d%Y");

-- 12.将日期进行减法-- 日期向前跳转
select date_sub('2021-10-01',interval 2 day);
select date_sub('2021-10-01',interval 2 month);

-- 13.将日期进行加法-- 日期向后跳转
select date_add('2021-10-01',interval 2 day);
select date_add('2021-10-01',interval 2 month);

-- 14.从日期中获取小时
select extract(hour from '2021-12-13 11:11:11');
select extract(year from '2021-12-13 11:11:11');
select extract(month from '2021-12-13 11:11:11');

-- 15.获取给定日期的最后一天
select last_day('2021-08-13');

-- 16.获取指定年份和天数的日期
select makedate('2021',53);

-- 17.根据日期获取年月日，时分秒
select year('2021-12-13 11:12:13');
select month('2021-12-13 11:12:13');
select minute('2021-12-13 11:12:13');
select quarter('2021-12-13 11:12:13');-- 季度

-- 18.根据日期获取信息
select monthname('2021-12-13 11:11:11');-- December获取月份的英文
select dayname('2021-12-13 11:11:11');-- Monday获取周几
select dayofweek('2021-12-13 11:11:11');-- 今天是星期几，日1，一2...
select DAYOFMONTH('2021-12-13 11:11:11');-- 本月第几天
select dayofyear('2021-12-13 11:11:11');-- 该年第几天
select yearweek('2021-12-13');--返回年份及第几周（0-53）

select NOW();-- 获取当前日期




-- 控制流函数

-- IF(expr1,expr2,expr3)
select if(5>3,'大于','小于');

-- IFNULL(expr1,expr2)
select ifnull(5,0);
select ifnull(null,0);

-- ISNULL(expr)
select isnull(5);-- 0
select isnull(null);-- 1

-- NULLIF(expr1,expr2)
select nullif(12,12);-- 一样返回null
select nullif(12,13);-- 不一样返回第一个值


-- case when 语句

select 
  case 5
		when 1 then '你好'
		when 2 then 'hello'
		when 5 then '正确'
		else 
			'其他'
		END as info;


select 
  case 
		when 2>1 then '你好'
		when 2<1 then 'hello'
		when 3>1 then '正确'
		else 
			'其他'
		END as info;     --when只执行一次--你好



use mydb4;
-- 创建订单表
create table orders(
	oid int primary key,-- 订单id
	price double,-- 订单价格
	payType INT-- 支付类型（1，微信支付 2.支付宝支付 3.银行卡支付 4.其他
	);
 insert into orders values(1,1200,1);
 insert into orders VALUES(2,1000,2);
 insert into orders VALUES(3,200,3);
 insert into orders VALUES(4,3000,1);
 insert into orders VALUES(5,1500,2);


-- 方式一
select 
		*,
  case payType
		when 1 then '微信支付'
		when 2 then '支付宝支付'
		when 5 then '银行卡支付'
		else 
			'其他支付方式'
		END as payTypeStr
	from orders;


-- 方式二
select 
		*,
  case 
		when payType=1 then '微信支付'
		when payType=2 then '支付宝支付'
		when payType=3 then '银行卡支付'
		else 
			'其他支付方式'
		END as payTypeStr
	from orders;





-- ------窗口函数----


-- 序号函数
	-- row_number() over(partition by分组  order by什么排序 desc) as 123123
	-- 	RANK() over (partition by department order by salary desc) as 113123
	-- 	dense_rank() over(partition by department order by salary desc) as 1123123




use mydb4;

-- 对每个部门的员工按照薪资进行排序，并给出排名
select 
	department,
	emp_name,
	salary,
	row_number() over(partition by department order by salary desc) as rn1,
	rank() over(partition by department order by salary desc) as rn2,
		dense_rank() over(partition by department order by salary desc) as rn3
	from emp;
	
-- !!!不能直接加where来提取前三名  因为执行from再执行where此时还没有rn1排名
-- 可以下面
select 
* 
from
(
select 
	department,
	emp_name,
	salary,
		dense_rank() over(partition by department order by salary desc) as rn 
		from emp 
	)t
where t.rn<=3;


-- 对所有员工进行全局排序 去掉partition by
-- 不加partition by表示全局排序 
select 
	department,
	emp_name,
	salary,
		dense_rank() over (order by salary desc) as rn3
	from emp;


-- 开窗聚合函数-sum avg max min

-- 加到当前行，累加
select 
	department,
	emp_name,
	salary,
		sum(salary) over (partition by department order by salary desc) as rn3
	from emp; 

-- 如果没有加order by 排序语句，默认把分组内的所有数据进行sum操作
select 
	department,
	emp_name,
	salary,
		sum(salary) over (partition by department ) as rn3
	from emp; 


-- 默认加到当前行
select 
	department,
	emp_name,
	salary,
		sum(salary) over (partition by department order by salary rows between unbounded preceding and current row ) as rn3
	from emp; 
	
	
-- 从我向上三行加到当前行
select 
	department,
	emp_name,
	salary,
		sum(salary) over (partition by department  order by salary rows between 3 preceding and current row) as rn3
	from emp; 

-- 向上三行到向后一行
select 
	department,
	emp_name,
	salary,
		sum(salary) over (partition by department  order by salary rows between 3 preceding and 1 following) as rn3
	from emp; 
	
-- 从当前行加到最后一行
select 
	department,
	emp_name,
	salary,
		sum(salary) over (partition by department  order by salary rows between current row and unbounded following ) as rn3
	from emp; 





-- 分布函数cume_dist
-- 用途：分组内小于，等于当前rank之的行数/分组内总行数

use mydb4;

select 
	department,
	emp_name,
	salary,
	cume_dist() over(order by salary) as rn1,
	cume_dist() over(partition by department order by salary) as rn2
from emp;



-- 分布函数percent rank
-- 每行按照公式 rank-1(rank()得到的排名-1）/rows-1(分组内的总行数-1)

select 
	department,
	emp_name,
	salary,
	rank() over(partition by department order by salary desc) as rn,
	percent_rank() over(partition by department order by salary desc) as rn2
from emp;




-- 前后函数lag LEAD(expr[,N[,default]])
-- 返回位于当前行的前n行lag或者后n行lead

-- 1.LAG(expr[,N[,default]])
-- (某行，n，默认值) 分组的话每组为单位到下一组第一个还用默认值
-- 没有默认值的话就是null

select 
	department,
	emp_name,
	salary,
	hiredate,-- 日期
		lag(hiredate,1,'2000-01-01') over(partition by department order by hiredate) as time1,
		lag(hiredate,2) over(partition by department order by hiredate) as time2,
from emp;



-- 头尾函数first_value LAST_VALUE(expr)
-- 返回第一个和最后一个的值

select 
	department,
	emp_name,
	salary,
	hiredate,-- 日期
	FIRST_VALUE(salary) over(partition by department order by hiredate) as first,## 这个部门按入职日期排序，得到第一个入职的员工的薪资
	LAST_VALUE(salary) over(partition by department order by hiredate) as last,
from emp;



-- 其他函数nth_value     NTILE(N)

-- 1.NTH_VALUE(expr,N)
-- 返回窗口的第n个exper的值，exper可以是表达式也可以是列名
-- 截至当前薪资，显示每个员工的薪资中排名第2或者第3的薪资

select 
	department,
	emp_name,
	salary,
	hiredate,-- 日期
	NTH_VALUE(salary,2) over(partition by department order by hiredate) as first_salary,
	NTH_VALUE(salary,3) over(partition by department order by hiredate) as second_salary,
from emp;


-- 2.NTILE(N)
-- 将分区中的有序数据分为n个等级
-- 将每个部门员工按照入职日期分成3组

select 
	department,
	emp_name,
	salary,
	hiredate,-- 日期
	ntile(3) over(partition by department order by hiredate) as  nt
from emp;

/* 组6个112233 组5个11223
*/

-- 取出每一个部门的第一组员工
select
*
FROM(
select 
	department,
	emp_name,
	salary,
	hiredate,-- 日期
	ntile(3) over(partition by department order by hiredate) as  nt
from emp
)t 
where t.nt=1;