数据分析/运营——常用MySQL操作、函数

---

前言

本文主要介绍MySQL常见的一些操作和函数。具体包括增删改查和数学函数、字符函数以及日期函数。

一、查找操作

1、select 的用法
功能：打印
语法：
select 列名称 
from 表名称 

2、distinct的用法
功能：去重
语法：
select distinct 列名称
from 表名称 

3、limit的用法
功能：提取前几项
语法：
select 列名 
from 表名称 
limit n,m  或者 limit n
limit n,m表示提取从第n行开始，往后提取m列  （注：下标从0开始）
limit n 表示提取前 n列  

4、where的用法
功能：条件查询
语法：
select 列名
from 表名称 
where 查询条件 

5、exists的用法
功能：判断子查询是否会至少返回一行数据
select 列名
from  表名称 
where exists (子查询)

6、order by 的用法
功能：排序
语法：
select 列名称 
from 表名称
order by 用于排序的列1 desc/asc,  用于排序的列2   desc/asc

7、like的用法
功能：模糊匹配
语法：
select 列1，列2....
from 表名称
where 列N like  <匹配模式>

LIKE 运算符 描述
WHERE 地址 LIKE 'a%' 查找以“a”开头的任何值
WHERE 地址 LIKE '%a' 查找以“a”结尾的任何值
WHERE 地址 LIKE '%or%' 在任何位置查找任何具有“or”的值
WHERE 地址 LIKE '_r%' 在第二个位置查找任何具有“r”的值
WHERE 地址 LIKE 'a_%_%' 查找以“a”开头且长度至少为 3 个字符的值
WHERE 地址 LIKE 'a%o' 找到以"a"开头，以"o"结尾的值

8、in的用法
功能：WHERE 子句中指定多个值。
语法：
select 列1，列2，列3,....
from 表名称
where in(值1，值2，值3....)

9、between的作用
功能：BETWEEN 操作符用于选取介于两个值之间的数据范围内的值。
语法：
select 列1
from 表名称 
where 列名
between 值1 and 值2 
（包含左右端点）

10、join的用法
功能：连接表
语法：
# 内连，取交集
select t1.列， t2.列
from  表1  as t1
join 表2  as t2
on t1.连接列 = t2.连接列

# 左连，保留左表全部信息
select t1.列， t2.列
from  表1  as t1
left  join 表2  as t2
on t1.连接列 = t2.连接列

# 右连，保留右表全部信息 
select t1.列， t2.列
from  表1  as t1
right join 表2  as t2
on t1.连接列 = t2.连接列

# 全连，取并集
select t1.列， t2.列
from  表1  as t1
full join 表2  as t2
on t1.连接列 = t2.连接列

11、union的用法
功能：拼接两个表（与join不同，join是左右连，union是上下连，所以需要具有相同的列数）
语法：
select 列1，列2，列3,..., from 表名称1
union
select 列1，列2，列3,... from 表名称2

注释：默认情况下，UNION 运算符选择一个不同的值。如果允许重复值，请使用
UNION ALL。
即union 和union all的区别是：union不包含重复值，union all 可以包含重复值

tips：如果想要拼接两个列名不用的表，可以使用as别名

select  列1 as 新列名
from 表名称1
union
select  列2  as 新列名
from 表名称2

12、is null的用法
功能：判断是否为空
语法：
select 列名
from 表名称
where 列名 is null
或者  列名 is not null

二、插入操作

1、create的用法
功能：创建数据库/数据表
语法：
create database 数据库名称；
create table  数据表名称 (列名 列类型)； 

2、insert into的用法
功能：插入行数据
语法：
insert into 表名称（列1， 列2， 列3，...）
values(值1， 值2， 值3，...)
如果需要为所有列添加值，则不需要在SQL查询中指定列名称
insert into 表名称 
values(值1， 值2， 值3，...，值n)

3、alter table的用法
功能：在已有的表中添加、修改或删除列（针对列）

添加列
alter table 表名称1 
add 新列名 datatype；

删除列
alter table 表名称1
drop column 列名称 

修改列类型
alter table 表名称1
alter column 列名称 datatype

添加not null 约束语法 
alter table 表名称
alter column 列名称 datatype not null

三、修改操作

1、update作用
功能：更新数据
语法：
update 表名称 
set  列1=值1， 列2=值2，.....
where 限定条件
WHERE 子句指定哪些记录需要更新。如果省略 WHERE 子句，所有记录都
将更新！

四、删除操作

1、delete的用法
功能：删除行数据
语法：
delete from  表名称 
where 删除条件 
WHERE 子句指定需要删除哪些记录。如果省略了 WHERE 子句，表中所有记录
都将被删除！

五、常用数学函数

max()：最大值
min()：最小值
sum()：求和
count()：计数
abs()：绝对值
avg()：平均值
ceil()：向上取整
floor()：向下取整
pow(x,y)：x的y次方
rand()：返回0-1随机数
round()：四舍五入
truncate()：截断

六、常用日期函数

1、curdate的用法
功能：返回当前日期
语法：select curdate()
案例：select curdate()  --  '2020-06-20'

2、adddate的用法
功能：返回起始日期加上n天后的日期
语法：select adddate(date, n)
案例：select adddate('2020-06-20', 2)  --  '2020-06-22'

3、curtime的用法
功能：返回当前时间
语法：select curtime()
案例：select curtime()  --  '13:36:35'

4、addtime的用法
功能：返回时间t加上n秒的时间 
语法：select addtime(t, n)
案例：select addtime('13:36:35', 10)  --  '13:36:45'

5、datediff的用法
功能：返回两个日期的间距
语法：datediff(enddate, startdate)
案例：select datediff( '2020-06-22',  '2020-06-20')  --  2

6、date_format的用法
功能：按表达式的要求显示日期
语法：select date_format(date, format)
案例：select date_format('2020/06~22', '%Y-%m-%d')   --   '2020-06-22'

7、year的用法
功能：返回年份
语法：select year(date);
案例：select year('2020-06-22')  --  2020

8、month的用法
功能：返回月份
语法：select month(date)
案例：select month('2020-06-22')  -- 6

9、day的用法
功能：返回日
语法：select day(date)
案例：select year('2020-06-22')  --  22

10、dayname的用法
功能：返回日期d是星期几
语法：select  dayname(d)
案例：select dayname('2020-06-22')    --   'Monday'

11、dayofmonth的用法
功能：返回日期d是本月的第几天
语法：select dayofmonth(d)
案例：select dayofmonth('2020-06-22')    --   22

12、dayofweek的用法
功能：返回日期d是周几
语法：select dayofweek(d)
案例：select dayofweek('2020-06-22')    --  2

13、quarter的用法
功能：返回日期d是第几季度
语法：select quarter(d)
案例：select quarter('2020-06-22')    --  2

14、str_to_date的用法
功能：把字符串转为日期
语法：str_to_date(sting, formate_mask)
案例：select str_to_date('2020-06-20', '%Y-%m-%d')    --   2020-06-20

七、常用字符串处理函数

1、length的用法
功能：返回字符串的长度 
语法：select  length(str)
案例：select length('abc')  --  3

2、left的用法
功能：返回字符串的前n个字符
语法：
select left(str, n)
如select  left('abcdefg', 2)    --  ab

3、right的用法
功能：返回字符串的后n个字符
语法：
select right(str, n)
如select right('abcdefg', 2)    --    fg

4、concat的用法
功能：拼接多个字符串
语法：select concat(s1,s2,....,sn)
案例：select concat('abc', '+', 'edf')    --   'abc+edf'

5、locate的用法
功能：从字符串s中获取s1的位置
语法：
select locate(s1, s)
select locate('b', 'abc') -- 2

6、lower的用法
功能：将字符串s的所有字母变成小写字母
语法：
select lower(s)
select lower('ABC') -- 'abc'

7、upper的用法
功能：将字符串s的所有字母变成大写字母
语法：
select upper(s)
select upper('abc') -- 'ABC'

8、ltrim的用法
功能：去掉字符串s左边的字符 
语法：select ltrim(s)
案例：select ltrim('   aaa')    --    'aaa'

9、rtrim的用法
功能：去掉字符串s右边的字符 
语法：select rtrim(s)
案例：select rtrim('aaa   ')    --   'aaa'

10、lpad的用法
功能：在s1的左边填充s2,使字符串长度达到len
语法：select lpad(s1, len, s2)
案例：select lpad('123', 6, '*')    --   '***123'

11、rpad的用法
功能：在s1的右边填充s2,使字符串长度达到len
语法：select rpad(s1, len, s2)
案例：select lpad('123', 6, '*')    --   '123***'

12、reverse的用法
功能：反转字符串s
语法：select reverse(s)
案例：select reverse('abc')    --    'cba'

13、substr的用法
功能：从字符串 s 的 start 位置截取长度为 length 的子字符串
语法：select substr(s, start, length)
案例：select substr('1234567', 2, 2)    --    '23'

14、replace的用法
功能：将字符串 s2 替代字符串 s 中的字符串 s1
语法：replace（s, s1, s2）
案例：select replace('abcd', 'a', 'A')    --    'Abcd'

八、聚合函数（GROUP BY）

1、GROUP BY 的用法
功能：先讲数据按照特定的字段进行分组，然后再对分组后的数据进行聚合（汇总）计算。——  类似EXCEL中的透视表功能
语法：SELECT `分组字段` --- 可以多个，类比于EXCEL透视表中的行标签
		    ,聚合函数(`剩余字段`) --- count、max、min、avg，类比于EXCEL透视表中的【值】
	 FROM table GROUP BY `分组字段`  --- 可以多个，但必须和SELECT 后面的分组字段一致
案例：求每个班级的人数和最高分  
SELECT   class
		,COUNT(DISTINCT student_id) AS `班级人数`
		,MAX(score) AS `最高分`
FROM 	table 
GROUP BY class

2、Having的用法
功能：讲聚合计算后的结果进行筛选
		HAVING：先聚合，再计算，最后对计算的【结果】筛选。区别于
		WHERE：先筛选，再计算。
语法：SELECT col1, COUNT(*) 
	  FROM table GROUP BY col1
	  HAVING COUNT(*) > 1
案例：求班级的人数超过100人的所有班级的人数和最高分
SELECT   class
		,COUNT(DISTINCT student_id) AS `班级人数`
		,MAX(score) AS `最高分`
FROM 	table 
GROUP BY class
HAVING COUNT(DISTINCT student_id)  > 100

九、窗口函数

更加详细的用法可以参考我的另一篇博客：
https://blog.youkuaiyun.com/qq_43019258/article/details/106772401

1、rank的用法
功能：先分组再排名，得到的排名：可重复、不连续
语法：
select *,
rank() over(partition by <参与分组的列> order by <参与排列的列> [asc|desc]) as 别名

2、dense_Rank的用法
功能：先分组再排名，得到的排名：可重复、连续
语法：
select *,
dense_rank() over(partition by <参与分组的列> order by <参与排列的列> [asc|desc]) as 别名
from 表名称

3、row_number的用法
功能：先分组再排名，得到的排名：不重复、连续
语法：
select *,
row_number() over(partition by <参与分组的列> order by <参与排列的列> [asc|desc]) as 别名
from 表名称

4、sum的用法
功能：先分组，再累计求和
select *,
sum(<用于求和的列>) over(partition by <参与分组的列> order by <参与排列的列> [asc|desc]) as 别名
from 表名称

5、avg的用法
功能：先分组，再累计求平均
select *,
avg(<用于求平均的列>) over(partition by <参与分组的列> order by <参与排列的列> [asc|desc]) as 别名
from 表名称

6、count的用法
功能：先分组，再累计计数
select *,
count(<用于计数的列>) over(partition by <参与分组的列> order by <参与排列的列> [asc|desc]) as 别名
from 表名称


7、min的用法
功能：先分组，再累计求最小值
select *,
min(<用于求最小值的列>) over(partition by <参与分组的列> order by <参与排列的列> [asc|desc]) as 别名
from 表名称


6、max的用法
功能：先分组，再累计求最大值
select *,
max(<用于求最大值的列>) over(partition by <参与分组的列> order by <参与排列的列> [asc|desc]) as 别名
from 表名称