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分组后的结果进行筛选的