sql资料库

1、distinct(关键词distinct用于返回唯一不同的值):查询结果中去除重复行的关键字

select distinct(university) from user_profile 
select distinct university from user_profile 

distinct是紧跟在select后面的,不能在其他位置,不然就会报错,

当distinct去重多个字段,是去掉多行一摸一样的数据,保留一行数据的:

SELECT distinct name, continent FROM world

 多个列去重的时候,不能用distinct(name,continent),不能加括号,

2、limit:限制返回的数据的数量

SELECT column1, column2, ...
FROM table_name
LIMIT [offset,] row_count;

offset,row_count都是可选的;offset是从第几行开始;row_count是显示多少行;

显示前两行数据: 

select device_id from user_profile limit 2

从第一行开始显示2行数据: 

select device_id from user_profile limit 0,2

limit也可以结合offset使用,limit限制显示的条数,offset指定从第几行开始显示

select device_id from user_profile limit 2 offset 1

含义:输出2行数据,从第二行开始输出

3、as:为列指定别名,可以省略

给返回值device_id这一列重新命名为:user_infos_example

select device_id as user_infos_example from user_profile limit 2 

省略写法:

select device_id user_infos_example from user_profile limit 2 

 4、age不为空

where age is not null或

where age!=" "

5、and的优先级高于or

select device_id,gender,age,university,gpa 
from user_profile
where gpa>3.5 and university="山东大学"or 
gpa>3.8 and university="复旦大学"

 6、聚合函数:对一组值执行计算并返回单一的值

常见的5个聚合函数:sum()、avg() 、max() 、min() 、count()

聚合函数不能作为where的条件,不能用where筛选可以用having

select university,
avg(question_cnt) as avg_question_cnt ,
avg(answer_cnt) as avg_answer_cnt
from user_profile
group by university
having avg_question_cnt<5 or avg_answer_cnt<20

聚合函数忽略空值

7、SQL语句执行顺序

sql的语法顺序:

select、from、join、where、group by、having、order by、limit

SELECT 
    COUNT(*) 
FROM 
    employees 
JOIN 
    departments ON employees.department_id = departments.id 
WHERE 
    departments.name = 'Sales' 
GROUP BY 
    employees.name 
HAVING 
    COUNT(*) > 2 
ORDER BY 
    COUNT(*) DESC 
LIMIT 5;

sql的执行顺序:

from、where、group by、having、select、order by、limit  

8、select后面的要查询的结果,可以用函数,可以进行计算,

就是select 字段名,计算字段,函数 。。。

select name, gdp,  population, gdp/population 人均gdp from world

看上面的算“人均gdp”的列,就直接用“gdp/population”了 

 

9、like是模糊查询,后面跟通配符,"_" ,"%"两种

"_"是占位符,"%"是通配符,

例如:查询name中第二个字母是“t“的字段

select name
from world
where name like '_t%'

10、order by 字段名 asc/desc,字段名 asc/desc (默认升序排序,asc升序排序可以省略)

11、count(*)计算总行数

12、group by和select的相互牵制

 因为select是最后执行的,group by先分组,然后去重

如果不使用group by时,使用聚合函数,那么select后面不能有字段名,只能用聚合函数或者聚合函数参与的运算:

select name, count(*) from students

像上面的代码会报错,不能聚合函数和非聚合函数一起查询

只有使用了group by,select后面才能跟字段名,而且是 group by后面出现的字段名,不是后面出现的也不能用

下面这个可以,因为sex是group by后面出现过的字段

select sex, count(*) from students
group by sex

这个不可以,因为name不是 group by后面出现过的字段

select name, count(*) from students
group by sex

13、 group by的原理

先把数据分区:

然后把分区的这列数据单独拿出来分组去重:

 然后聚合计算就是按照分组后的这些数据进行计算

14、where和having的区别

where是在group by之前对原表格的数据进行筛选,而having是在group by之后对group by分组的数据进行筛选,

having只能用聚合函数和group by作为分组依据的字段

where不能使用聚合函数

15、sql执行原理:

16、函数

1️⃣:round(x,y):四舍五入函数

对x值进行四舍五入,精确到小数点后y位

y为负值时,保留小数点左边相应的位数为0,不进行四舍五入

例如:round(3.15,1)返回3.2  round(14.15,-1)返回10

2️⃣:concat(s1,s2...):连接字符串函数

当任意参数是null时,结果返回null

例如:concat('my', ,'sql')返回值为my sql(中间还有一个空格不要忽略)

concat('my',null,'sql')返回null

3️⃣:replace(s,s1,s2)替换函数

使用字符串s2替换s中的所有s1

例如:replace('MySQLMySQL‘,'SQL','sql')返回结果是:MysqlMysql

4️⃣:截取字符串里的一部分函数

left(s,n)函数:从左往右,截取字符串s中前n位,例如:left(“abcdefg”,3)结果返回:abc

right(s,n)函数:从右往左,截取字符串s中n位,例如:right(“abcdefg”,3)结果返回:efg

substring(s,n,len)函数:从n开始截取s中长度位len的几位(n可以为负数,但截取顺序都是从左往右)

例如:substring(“abcdefg”,-2,3)结果返回:fg

substring(“abcdefg”,2,3)结果返回:bcd

5️⃣:时间日期函数

year(date):获取日期中的年份

month(date):获取日期中的月份

day(date):获取日期中的日

date_add(date,interval expr type):对指定起始时间进行加操作

date_sub(date,interval expr type):对指定起始时间进行减操作

参数说明:date是起始时间

expr是从起始时间中加或者减的时间间隔

type是指定时间间隔的类型,也就是指定expr的类型,类型有:day、week、month、year

例如:date_add('2021-08-03 23:29:29',interval 1 day) 返回2021-08-04 23:29:29

也就是在起始时间上增加一天

datediff(date1,date2):计算两个日期之间间隔的天数,只有日期部分参与计算,时间不参与

date_format(date,format):将日期和时间输出为format格式

SELECT DATE_FORMAT(NOW(), '%Y-%m-%d');

常见的格式化选项有:

%Y 年份,四位数
%y 年份,两位数
%m 月份,两位数
%d 日期,两位数
%H 小时,24小时制,两位数
%h 小时,12小时制,两位数
%i 分钟,两位数
%s 秒,两位数
%p AM/PM

6️⃣:窗口函数

注意:窗口函数计算的是分组窗口内值的累积,是累积值,不是单个值,

写法:函数() over(子句):over()指定函数执行的数据范围

函数() over(partition by 字段名 order by 字段名 asc/desc rows between 范围 and 范围)

子句有三个:partition by 要分的组,分组、order by 要排序的列 asc/desc,排序、窗口(rows)字句

窗口子句(rows):

窗口字句的描述:

(1)起始行:N preceding/unbounded preceding

(2)当前行:current row

(3)终止行:N following/unbounded following

 举例子:

rows between unbounded preceding and current row 从之前所有的行到当前行

rows between N preceding and current row 从前面两行到当前行

rows between current row and unbounded preceding 从当前行到之后所有的行

rows between current row and 1 preceding 从当前行到最后一行

注意:排序字句后面缺少窗口子句,窗口默认是:rows between unbounded preceding and current row 从之前所有的行到当前行

排序子句和窗口子句都缺失,窗口默认是:rows between unbounded preceding and unbounded following 全部的数据

所以要注意:即使说每一行的数据都不一样,也要进行排序,不能省略排序数据,这样才会计算从之前行到当前行的数据,不排序就会造成计算全部数据,看下面的例子:

执行流程:

(1)通过partition by和order by 子句确定大窗口(定义出上界unbounded preceding和下界unbounded  following)

(2)通过row子句针对每一行数据确定小窗口

(3)对每行的小窗口内的数据执行函数并生成新的列

partition by和group by的区别:

前者只分组,不去重;后者分组还去重

前者分组后的数据可以显示非聚合列,但后者只能显示聚合列

 ​​​​​​​partition by: 

group by:

窗口函数常用的函数:排序函数、聚合函数、取值窗口函数

(1)排序函数:row_number/rank/dense_rank,这些函数是没有参数的

 三个函数的区别:

案例:

案例一:对每个部门的员工依据工资从高到低进行排序

select 

d.dept_name,
a.emp_name,
a.salary,

row_number() over(partition by d.dept_name order by a.salary desc) as 'row_number',
rank() over(partition by d.dept_name order by a.salary desc) as 'rank',
dense_rank() over(partition by d.dept_name order by a.salary desc) as 'dense_rank'

from dm.employee a
join dm.department d on a.dept_id=d.dept_id

结果:

 案例二:求每个员工占全部员工月薪综合的比例

select 
 emp_name,
 salary,
 sum(salary) over() as sum_salary,
 salaty/sum(salary) over() as ratio

from dm.employee
order by ratio desc;

结果:​​​​​​​

OVER():这时候,是一个空子句,此时的效果和没有使用OVER()函数是一样的,作用的是这个表所有数据构成的窗口

(2)聚合函数:sum(参数)、avg(参数)、max(参数)、min(参数)、count(参数)

函数里面要有参数,

聚合函数和窗口聚合函数的区别:

聚合函数是对一组值进行计算并返回单一值的函数,通常用于整个数据集或某个数据分组。

窗口聚合函数是一种特殊的聚合函数,它可以根据指定的窗口(window)来计算每一行数据的聚合结果,而不是简单地针对整个数据集或分组进行计算。

注意:窗口函数结合集合函数使用的时候,只会分组后求聚合函数的值然后显示在每一行,并不会进行筛选,下面拿max()聚合函数来举例子:

原始两个表:

业务需求:

我的代码:(不正确的,但让你看清楚max的用法)

select d.dept_no,d.emp_no,max(salary) over(partition by d.dept_no) maxSalary 
from dept_emp d join salaries s on d.emp_no=s.emp_no

我这里用的是窗口函数,窗口聚合函数是max,我以为max会筛选出来分区后工资最高的显示,但是不是这样的,而是筛选出每个部门工资最高的工资,在这个区的每一行显示,看下面的结果(我代码的结果):

 所以我这样的思路是不能解决上面的问题的,正确的思路是:

按照部门分区后,用排序函数dense_rank,有重复的都显示出来,然后该表作为子查询的表,外面嵌套的表的查询条件就是,排序的序号=1就好了,代码如下:

select t.dept_no,t.emp_no,t.salary as maxSalary

from(
    select d.dept_no,d.emp_no,s.salary,dense_rank() over(partition by d.dept_no order by s.salary desc) as s_rank
from dept_emp d join salaries s on d.emp_no=s.emp_no
    ) t

where t.s_rank=1
order by t.dept_no

 结果:

(3)取值窗口函数

lag():返回窗口内当前行之前的第n行数据

lead():返回窗口内当前行之后的第n行数据

first_value:返回窗口内第一行数据

last_value:返回窗口内最后一行数据

nth_value:返回窗口内第n行数据

7️⃣:条件判断函数:if/case when

(1)if(表达式,v1,v2)

如果表达式返回true,就返回v1,否则返回v2

(2)case when

注意:一定要加end

8️⃣:mod取余数函数

用法:mod(被除数,除数):被除数/除数的余数

17、表连接

内连接(inner join/join)、左连接(left join)、右连接(right join)

写法:

select 表名
from 表1 join 表2 on 表1.字段名=表2.字段名

完全连接图示:

内连接图示:会把null值去除,就是不取一边有一边没有的值,取两边都有的值进行相✖️

这样理解:先完全连接,然后内连接就是去除有null值的行,只保留全部有值的行 

左连接图示:保留左边表的所有行来匹配右边的表,如果右边表有的字段左边表没有那就不管,就是以左边表为主,

这样理解:先完全连接,然后左连接就是去除左边为null的值的行,让左边是都有数据的

 右连接图示:以右表为主,保留右边表的所有行,左边表去适应右边的表,

这样理解:先完全连接,然后右连接就是去除右边为null的值的行,让右边都是有数据的

 

18、子查询

如果子查询语句在from紧跟在from后面,必须要有别名

select * from (select 字段名 from 表名)as s

19、sql语句的运行顺序

20、sql中百分比的乘法计算,要换算成小数的形式,sql中“%”是不能参与运算的,

 sql百分比运算的正确写法:

select value*0.8 from table_name

错误写法:

select value*80% from table_name

21、去除空格函数:

rtrim:去除字符串右边的空格

ltrim:去除字符串左边的空格

trim:去除两端的

字符串连接函数:concat()

22、函数可以用在order by中

例如:

代码:

select first_name
from employees
order by right(first_name,2)

23、group_concat(x,y)函数

作用:将group by分组后的数据连接起来,参数x是要连接的字符串,y是用什么连接,默认是逗号,省略就是逗号,可以省略

例如:

代码:

select dept_no,group_concat(emp_no) as employees
from dept_emp
group by dept_no

24、exists关键字

作用:用于判断语句是否有返回数据,如有至少一行数据就是true,没有数据就是false

例子:

 也可以用:

where not EXISTS(子查询)

25、 mysql中的row_number()函数一定要结合窗口函数使用,不能单独使用

 26、mysql中where后面不能使用聚合函数

where count(name)>3

注意:👆上面是错误的写法❌

所以如果需要使用聚合函数当条件判断,可以用having:

group by 。。。
having count(name)>3

但是用having,必须要结合group by使用,因为having是对group by分组后的结果进行筛选的

 

 

 

 

  • 20
    点赞
  • 28
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

陌一一

你的鼓励是我最大的动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值