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

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:

7️⃣：条件判断函数：

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、