mysql结合业务进行学习总结

mysql学习总结1：基础

• DDL（Data Definition Language）常用（容易忘记）记录

• 修改表字段类型
• 增加表字段
• 删除表字段
• 字段改名
• 更改表名

• DML（Data Manipulation Language）

• 同时更新多表数据
• 同时删除多表数据
• having的用法
• union 和 union all的区别

• mysql常用函数

• 字符串函数
• 数值函数
• 日期和时间函数
• 流程函数
• 窗口函数
• 其他常用函数

许多常用的相关的内容这里就不多写了，大家看着也烦。

DDL（Data Definition Language）常用（容易忘记）记录

修改表字段类型

ALTER TABLE tablename MODIFY [COLUMN] column_definiton [FIRST | AFTER col_name];

例如，修改表emp的ename字段定义，将 varchar(10)改为 varchar(20):

alter table emp modify ename varchar(20);

通过此语句可以修改字段排列顺序。

增加表字段

ALTER TABLE tablename ADD [COLUMN] column_definition [FIRST | AFTER col_name];

例如，在表emp中字段ename后面新增加字段age，类型为int(3)，并且备注为“年龄”:

alter table emp add column age int(3) default null COMMENT '年龄'  after ename;

删除表字段

ALTER TABLE tablename DROP [COLUMN] col_name;

例如，将字段 age 删除掉:

alter table emp drop column age;

字段改名

ALTER TABLE tablename CHANGE [COLUMN] old_col_name column_definition [FIRST | AFTER col_name];

例如，将 age 改名为 age1，同时修改字段类型为 int(4):

aIter table emp change age age1 int(4);

注意：change可以修改列名称，modify 不能。

更改表名

ALTER TABLE tablename RENAME [TO] new_tablename

例如，将表 emp 改名为empl

alter table emp rename empl;

DML（Data Manipulation Language）

同时更新多表数据

UPDATE t1,t2,.,tn set t1.field1=expr1,tn.fieldn=exprn [WHERE CONDITION];

例如：同时更新表emp中的字段sal和表dept 中的字段deptname。

update emp a,dept b set a.sal=b.deptno,b.deptname=a.ename where a.deptno=b.deptno;

注意:多表更新的语法更多地用于根据一个表的字段来动态地更新另外一个表的字段。

同时删除多表数据

DELETE t1,t2,.,tn FROM tl,t2,.,tn [WHERE CONDITION];

如果 from 后面的表名用别名，则 delete 后面也要用相应的别名，否则会提示语法错误。
例如：同时删除表emp和dept中deptno为3的记录。

delete a,b from emp a,dept b where a.deptno=b.deptno and a.deptno=3;

注意：无论多表删除还是单表删除请注意where条件。建议还是逐个表的做删除。

having的用法

HAVING和WHERE 的区别在于，HAVING是对聚合后的结果进行条件的过滤，而 WHERE是在聚合前就对记录进行过滤。如果逻辑允许，我们尽可能用 WHERE 先过滤记录，因为这样结果集减小，聚合的效率将大大提高，最后再根据逻辑看是否用 HAVING 进行再过滤。

例如：查询用户表中重复的account的数据。

select account, count(1) as a from user group by account having a > 1;

union 和 union all的区别

UNION 和 UNION ALL 的主要区别是 UNION ALL 是把结果集直接合并在一起，而UNION 是将UNION ALL 后的结果进行一次 DISTINCT，去除重复记录后的结果。

mysql常用函数

字符串函数

函数	功能
CONCAT(sl,s2,…,sn)	连接sl,s2,…,sn 为一个字符串
INSERT(str,x,y,instr)	将字符串 str 从第x位置开始，y个字符长的子串替换为字符 instr
LOWER(str)	将字符串 str 中所有字符变为小写
UPPER(str)	将字符串 str 中所有字符变为大写
LEFT(str,x)	返回字符串 str 最左边的x个字符
RIGHT(str,x)	返回字符串 str 最右边的x个字符
LPAD(str,n,pad)	用字符串pad 对str 最左边进行填充，直到长度为n个字符长度
RPAD(str,n,pad)	用字符串 pad 对 str 最右边进行填充，直到长度为n个字符长度
LTRIM(str)	去掉字符串str左侧的空格
RTRIM(str)	去掉字符串 str 行尾的空格
REPEAT(str,x)	返回str 重复x次的结果
REPLACE(str,a,b)	用字符串b替换字符串str 中所有出现的字符串a
STRCMP(s1.s2)	比较字符串 s1和s2
TRIM(str)	去掉字符串行尾和行头的空格
SUBSTRING(str,x,y)	返回从字符串 str x 位置起y个字符长度的字串

数值函数

函数	功能
ABS(x)	返回 x 的绝对值
CEIL(x)	返回大于x 的最小整数值
FLOOR(x)	返回小于 x的最大整数值
MOD(x,y)	返回x/y 的模
RAND()	返回 0~1内的随机值；产生0~100的随机数则为100*RAND()
ROUND(x,y)	返回参数X的四舍五入的有y 位小数的值
TRUNCATE(x,y)	返回数字x截断为y位小数的结果

日期和时间函数

函数	功能
CURDATE()	返回当前日期，2023-01-09
CURTIME()	返回当前时间，12:12:12
NOW()	返回当前的日期和时间，2023-01-09 12:12:12
UNIX_TIMESTAMP(date)	返回日期date的UNIX 时间截
FROM_UNIXTIME	返回UNIX时间戳的日期值
WEEK(date)	返回日期date 为一年中的第几周
YEAR(date)	返回日期 date 的年份
HOUR(time)	返回 time 的小时值
MINUTE(time)	返回time的分钟值
MONTHNAME(date)	返回date 的月份名
DATE_FORMAT(date,fmt)	返回按字符串fmt格式化日期date值
DATE_ADD(date,INTERVAL expr type)	返回一个日期或时间值加上一个时间间隔的时间值
DATEDIFF(expr,expr2)	返回起始时间expr和结束时间expr2 之间的天数

DATE_FORMAT(date,fmt) 函数示例：
1、返回24小时制

select date_format(now(), '%Y-%m-%d %T')
结果如下：
2023-01-09 19:03:57

2、返回12小时制

select date_format(now(), '%Y-%m-%d %r')
结果如下：
2023-01-09 07:05:39 PM

当然该函数中的fmt参数还有许多其他写法，具体可以参考mysql官方文档。

DATE_ADD(date,INTERVAL expr type)函数示例：
1、第一列返回了当前日期时间，第二列返回距离当前日期31 天后的日期时间，第三列返回距离当前日期一年两个月后的日期时间。

select now() current,date_add(now(),INTERVAL 31 day) after31days,date_add(now(),INTERVAL '1_2' year_month)after_oneyear_twomonth;
结果如下：
2023-01-09 19:10:09	    2023-02-09 19:10:09	    2024-03-09 19:10:09

2、同样也可以用负数让它返回之前的某个日期时间，如下面这个例子第一列返回了当前日期时间，第二列返回距离当前日期 31 天前的日期时间，第三列返回距离当前日期一年两个月前的日期时间。

select now() current,date_add(now(),INTERVAL -31 day) after31days,date_add(now(),INTERVAL '-1_-2' year_month)after_oneyear_twomonth;
结果如下：
2023-01-09 19:12:09	    2022-12-09 19:12:09	    2021-11-09 19:12:09

DATEDIFF(date1，date2)函数示例：
下面看看离2023年过年还有几天（2023年1月21号是大年三十）：

select DATEDIFF('2023-01-21', now());
结果如下：
12
还有12天就过年了，我们还在外打工/(ㄒoㄒ)/~~

流程函数

函数	功能
IF(value,t f)	如果 value 是真，返回t;否则返回 f
IFNULL(value1,value2)	如果 value1不为空，返回 value1，否则返回 value2
CASE WHEN [value1] THEN [result1]…ELSE [default] END	如果 value1 是真，返回 result1，否则返回 default
CASE[expr] WHEN [value1] THEN [result1]… ELSE [default] END	如果 expr 等于 value1，返回 result1，否则返回 default

窗口函数

注意，注意，注意：窗口函数为mysql8及以上版本支持的功能。使用前先查看数据库版本。必须记得查看开发、测试、预生产及生产库的所有版本（经常遇到各环境数据库版本不一致的坑）。查询数据库版本号：select VERSION();，随便哪个库直接执行这个语句即可。

函数	功能
ROW_NUMBER()	分区中的当前行号
RANK()	当前行在分区中的排名，含序号间隙
DENSE_RANK()	当前行在分区的排名，没有序号间隙
PERCENT_RANK()	百分比等级值
CUME_DIST()	累计分配值
FIRST_VALUE()	窗口中第一行的参数值
LAST_VALUE()	窗口中最后一行的参数值
LAG()	分区中指定行落后于当前行的参数值
LEAD()	分区中领先当前行的参数值
NTH_VALUE()	从第N行窗口框架的参数值
NTILE(N)	分区中当前行的桶号

问题：通过group by分组后查询最大时间所在的那条数据

咱们先看看如下表中的数据：

select * from order_tab;

需求描述：我们要查询出每一个user_no最新时间的一条数据。我们期望的数据是id为4和8的数据。

这个问题会经常遇到，通常我们的写法如下（这个是错误写法，查询结果错误）：

select user_no,max(create_time) as create_time,ot.* from order_tab ot group by user_no;

查询结果如下：

会发现查询出了id为1和5的数据，明显不对。

解决方法：

1、mysql所有版本的解决方法：

通过子查询+内连接进行处理

select ot.* from order_tab ot inner join (
select user_no,max(create_time) as create_time from order_tab ot group by user_no) a 
on ot.user_no = a.user_no and ot.create_time = a.create_time;

查询结果如下图：

2、通过窗口函数ROW_NUMBER()函数处理

select * from
	(select
		row_number() over(partition by ot.user_no order by create_time desc) as rowNum,
		ot.id,
		ot.user_no,
		ot.amount,
		ot.create_time
	from order_tab ot ) a
where a.rowNum = 1

查询结果如下：

我们会发现多了一行rowNum。

再看看另一种写法：

select * from
	(select
		row_number() over w as rowNum,
		ot.id,
		ot.user_no,
		ot.amount,
		ot.create_time
	from order_tab ot 
	window w as (partition by ot.user_no order by create_time desc)
	) a
where a.rowNum = 1

解释一下窗口函数ROW_NUMBER()的用法吧。
其中，row_number()后面的 over 是关键字，用来指定函数执行的窗口范围，如果后面的括号中什么都不写，则意味着窗口包含所有行，窗口函数在所有行上进行计算;如果不为空则支持以下4种语法。

1. window_name: 给窗口指定一个别名，如果 SOL 中涉及的窗口较多，采用别名则更清晰易读。上面的例子中如果指定一个别名 w。
2. partition 子句:窗口按照哪些字段进行分组，窗口函数在不同的分组上分别执行。上面的例子就按照user_no 进行分组。在每个user_no上，分别执行从1开始的顺序编号。
3. order by 子句: 按照哪些字段进行排序,窗口函数将按照排序后的记录顺序进行编号，既可以和 partition 子句配合使用，也可以单独使用。上例中二者同时使用。
4. frame 子句: frame 是当前分区的一个子集，子句用来定义子集的规则，通常用来作为滑动窗口使用。【此示例中没有演示】

其他常用函数

函数	功能
DATABASE()	返回当前数据库名，select DATABASE()
VERSION()	返回当前数据库版本，select VERSION()
USER()	返回当前登录用户名，select USER()
INET_ATON(IP)	返回 IP 地址的数字表示
INET_NTOA(num)	返回数字代表的 IP 地址
PASSWORD(str)	返回字符串 str 的加密版本，一个41位长的字符串
MD5()	返回字符串 str 的 MD5值

以上内容如有错误，欢迎大家评论指正。后面工作中如果发现需要记录的内容会不定期更新此文档。