MYSQL听课笔记（函数自查）

数据库	查看系统中有哪些数据库	show
	选择进入数据库	use
	删除数据库	drop database
	创建数据表	create table
	查看有哪些数据表	show tables

表定义	查看表结构	desc
	删除数据表	drop table
	创建带有约束条件的表（*6）	primary key
		foreign key (字段名) references 表名（字段名）
		auto_increment
		default
		(not) null
		unique
		# 其中primary key,foreign key 和unique
		# 常见字符类型int,varchar(),float(),decimal
	修改表名	alter table X rename
	修改字段名	alter table X change Y 字段类型
	修改字段类型	alter table X modify Y 字段类型
	添加字段	alter table X add Y 字段类型
	修改字段的排列位置	alter table X modify Y 字段类型 first/after
	删除字段	alter table drop X
	插入数据	insert into 表 values
	批量导入数据(文件)	load data infile '/Users/apple/mysql-safe-dir/employee.csv' into table emp fields terminated by ',' ignore 1 lines;
	》查看安全路径	show variables like '%secure%';
	检查导入的全部数据？	select *
	检查导入数据的总行数？	select count(*)
	更新数据	update 表 set…where…
	》修改某个字段的全部数据	update 表 set…where…
	》设置数据安全权限	set sql_safe_updates=0;
	删除数据
	》一行一行	delete from 表 (where…)
	》一次清空（保留表结构）	truncate 表

	单表查询（虚拟结果集）
	》查询全部字段	select * from 表
	》查询指定字段（列）	select A,B... from 表
	》设置别名	select A as B from 表
	》查询不重复数据	select distinct A from 表
	》条件查询	where 后面：
	》》大于小于	>,<
	》》不等于	=,<>,=!
	》》介于	between...and…
	》》在取值范围内	in(a,b..)
	》空值查询	where A is (not) null
	》模糊查询
	》》字符串类型	where A like "_a%"
	》》数值类型	where A <> "a"
	》查询结果排序	order by A
	》》多字段排序	order by A(asc),B(desc)
	》限制查询结果数量	limit
	》》默认偏移&偏移常量	limit 0,5 / limit 5(默认偏移为0)

	聚合运算
	》常见5种	SUM/MIN/MAX/AVG/COUNT
	》注意非空计数（类比Excel）	count(*)=count(1); count(字段);count(distinct 字段)
	》分组运算逻辑	所有聚合函数都会忽略空值COUNT函数相当于Excel中的COUNTA（对非空单元格计数）所有聚合函数都会忽略空值通常不按照主键字段分组，无意义分组之后通常只显示分组字段和聚合字段，其他字段并没有太大意义
	》几种计数补充

	分组查询
	》分组后筛选	#重点两个区别：书写顺序和执行顺序；#where语句无法执行聚合操作
	》》筛选一：where
	》》筛选二：having	having +聚合函数

多表连接查询
》几种连接如何实现？
》》内连接	select * from t1 innner join t2 on key1=key2	当主表中关键字段的取值在附表中都存在的情况下，左连接和右连接的结果跟内连接是一样的
》》左连接	select * from t1 left join t2 on key1=key2
》》右链接	select * from t1 right join t2 on key1=key2
》》全连接去重	select * from t1 union select * from t2
》》全连接不去重	select * from t1 union all select * from t2
》》左反连接	select * from t1 left join t2 on key1=key2 where key2 is null
》》右反连接	select * from t1 right join t2 on t1.key1=t2.key2 where t1.key1 is null;
》笛卡尔乘积	select * from emp, dept	/当两张表之间没有关键字段时候，使用笛卡尔积连接/
》》消除笛卡尔乘积（加上筛选条件）	select * from emp, dept where emp.deptno=dept.deptno	#哪一种连接更加高效（笛卡尔积与内连接）呢？ -- 内连接
》自连接	select emp.ename as 员工姓名, leader.ename as 领导姓名 from emp left join emp as leader on emp.mgr=leader.empno	# 逻辑：通过别名，将一张表虚拟为多表，两张表进行匹配，类似Excel的VLOOKUP函数
》不等值连接	select * from emp left join salgrade on sal between losal and hisal;

	子查询
	》标量子查询:一行一列的某个值	select empno,ename,job,mgr from emp where mgr=(select mgr from emp where ename ='allen')
	》行子查询：一行	select empno,ename,job,deptno from emp where (deptno,job)=(select deptno,job from emp where ename='smith') and ename<>'smith';
	》列子查询	select empno,ename,sal,grade from emp left join salgrade on grade between losal and hisal where empno not in (select distinct mgr as 领导工号 from emp where mgr is not null)
	》》》in 包括，在...范围
	》》》all 全部满足，比较	select * from emp where sal > all(select sal from emp where deptno=30) and deptno<>30;
	》》》any 满足任意一个，比较	select * from emp where sal > any(select sal from emp where deptno=30) and deptno<>30;
	》from子查询（新建表+连接）
	》select子查询（新建字段）

/* 备注：
表子查询需要加上别名
子查询效率并不高，先进行子查询创建临时表，之后又撤销，进行主查询，多次创建子查询表需要多次撤销多次
*/
/* 使用场景
（1）where子句中，无法使用聚合函数作为筛选条件，必须使用子查询
（2）分组后的结果再分组，一条select语句中只能使用一次group by，这种情况下必须使用子查询，必须使用子查询
（3）笛卡尔积连接后的结果再跟另一张表join时，必须使用子查询
（4）在where,group by,having子句中使用开窗函数，必须使用子查询
*/

	常用函数
	数学函数
	》返回绝对值	ABS()
	》返回最大整数值（向上取整）	ceiling()
	》返回最小整数值（向下取整）	floor()
	》四舍五入保留d位小数	round( ,)
	字符串函数
	》文本字符拼合	concat( , ,..)
	》返回（第一次）出现位置	instr(abcdef,c)
	》返回从（左边）起的N个字符	left(abcdf,3)
	》返回从（右边）起的N个字符	right(abcdf,4)
	》返回从（指定位置）起的N个字符	mid(abcdef,3,2)
	》截取从（指定位置A）起到指定位置B的N个字符串	substring(abcdef,1,4)
	》删除两边空格	trim( 'a' )
	》选择字符串并替换	replace(abc,"a","e")

	时期时间函数
	》返回指定时间、日期/文本->日期表达式	date('20220120')
	》》返回指定日期年份	year()
	》》返回指定日期月份	month()
	》》返回指定日期日	day()
	》》返回指定时间的小时（范围在0~23）	hour()
	》返回当前时间
	》》返回当前日期值'yyyy-mm-dd'或yyyymmdd	curdate()
	》》返回当前时间值'hh:mm:ss'或hhmmss	curtime()
	》》返回当前日期和时间'yyyy-mm-dd hh:mm:ss'或yyyymmddhhmmss	now()
	》对日期进行加减运算
	》》增加	date_add(date,interval expr type)
		adddate(date,interval expr type)
	》》减少	date_sub(date,interval expr type)
		subdate(date,interval expr type)
	》计算时间差
	》》（秒，分，时，天，月，年)	timestampdiff(month,'2020-01-01'.'2022-02-14')
	》》（返回unix从"1970-01-01 00:00:00"开始的秒数	unix_timestamp('2020-01-01')
	》》以'yyyy-mm-dd hh:mm:ss'或yyyymmddhhmmss	from_unixtime(72438975889)
	》根据formate字符串对date格式化	date_format("2022-01-19 12:00:00","%Y-%m-%d")

	分组合并函数
	将group by产生的同一个分组的值连接起来，返回一个字符串	group_concate([distinct]str[order by str asc/desc][seperator])
		group_concate(字段1）

	使用案例积累1》与if函数结合使用	统计出每个课程成绩前3的学生
		Group_Concat(If(排名=1,concat(stu_name,”+”,score),null))As 第一名
	逻辑函数
	》判断第一个表达式是否为NULL，是返回第二个参数值	ifnull(comm,0)
	》判断第一个表达式是否为NULL，是返回第二个参数值	if(expr1,expr2,expr3)
	》判断expr1是否为true，是返回expr2,如果expr3为false，返回expr4的值	case when expr1 then expr2 [when expr3 then expr4…else expr]end
	备注：if和case when其实逻辑运行是一样的，只不过当逻辑判断表达式长的时候，更适合用case when

	开窗函数	对字段聚合；不能和where一起使用
	》》语法	开窗函数名([partition by order by range\|rows between ... and ...])
		第三个子句frame只对动态窗口（滑动）有效（以下标红的为动态函数）
		备注：开窗函数的字段可以是另外一个开窗函数的结果
	》函数分类（按照功能）
	》》聚合函数
	>>> sum()
	>>> avg()
	>>> max/min()
	>>> count()
	》》序号函数
	>>> row_numwber() 显示分区中不重复不间断的序号	记录用户登录时间3个函数都适用
	>>> rank() 显示分区中重复间断的序号	活动发放优惠券适合rank(并列间断)控制成本
	>>> dense_rank() 显示分区中重复不间断的序号	高考等成绩排名多用dense_rank(并列且不间断)
	》》分布函数
	>>> percent_rank()
	>>> cume_dist()
	》》前后函数	# 适用场景：计算同环比
	>>> lag() 过去/上次	lag(create_date,1) over(partition by user_no order by create_date) as last_date
	>>> lead() 未来/下次	lead(create_date,1) over(partition by user_no order by create_date) as lead_date
	》》头尾函数
	>>> first_value()	first_value(amount) over(partition by user_no order by create_date rows between unbounded preceding and unbounded following) as first_amount
	>>> last_value()	last_value(amount) over(partition by user_no order by create_date rows between unbounded preceding and unbounded following) as last_amount
	》》其他函数
	>>> nth_value()
	>>> n_tile()

备注：

开窗函数和普通聚合函数的区别：

聚合函数是将多条记录聚合为一条；而开窗函数是每条记录都会执行，有几条记录执行完还是几
条。
聚合函数也可以用于开窗函数中。