SQL 面试总结

1. SQL的运行顺序

from --> where -- > group by -- > having -- > select -- > order by -- > limit

2. SQL常用知识

• select

筛选的字段名称

• from

后面跟数据库名称

• where

条件筛选

• distinct - 去重

selectdistinct name, age
selectcount(distinct name)

• order by - 排序

orderby age desc# 年龄倒序orderby age asc# 年龄升序

• limit

offset  跳过多少页
limit   限制多少条数据
用于SQL查询最后使用
select name fromtableorderby age asclimit1

• group by - 分组（去重）

groupby 用于数据分组展示
select name fromtablegroupby name

• having

一般和 groupby 连用
groupbyhaving
用于 数据 筛选
groupby name  having name ='zs'

• 常见函数

求和函数
    sum() 

平均值函数 
    avg()
中位数函数 
    median()
标准差函数 
    stddev()
求最大值函数 
    max()
求最小值函数 
    min()
计数函数 
    count()
类型转换函数 
    cast(字段名 AS 格式类型 )
日期格式转换函数
    date_format(date,'%Y %m %d %H:%i %s')
返回统计的年份
    selectYEAR('2020-10-10')fromtable  
返回统计的月份
    MONTH()  
返回统计的天
    DAY()  
返回小时值
    HOUR()  
返回分钟值
    MINUTE() 
返回秒数 
    SECOND()
全年第几周   
    WEEK()  
取年-月-日函数
    DATE()
    
计算时间差函数，interval代表指定的单位，常用可选：
    timestampdiff(interval,start_time,end_time)YEAR 年数
    MONTH 月数
    DAY 天数（返回秒数差除以3600*24的整数部分）
    HOUR 小时（返回秒数差除以3600的整数部分）
    MINUTE 分钟（返回秒数差除以60的整数部分）
    SECOND 秒
    
两个日期相减，返回天数，大的值在前
    datediff(end_time,start_time)

两个日期相减，返回 time 差值（时分秒格式），大的值在前   
    timediff(end_time,start_time)

百分比格式表示
    CONCAT(字段名,'%')   
替换函数          
    replace(字符串，原字符，新字符) 

字符串的截取函数
    substring(字符串，起始位置，截取字符数）
    
四舍五入函数
 Round()--将数值四舍五入为指定数值，用法：Round(数值，返回的小数位)

• 窗口函数

row_number()# 1 2 3 4select 
    s_id,avg(score)as'平均成绩',
    row_number()over(orderbyavg(score)DESC)as'排名'from 
    sc

rank()# 1 2 2 4select*,
    rank()over(PARTITIONby c_id orderby score desc)as'排名'from 
    sc 
    
dense_rank()# 1 2 2 3

Lag() 求当前行的前 N 行  #用来计算 与前一天相比 新增的销售额select(nowTime -(lag(nowTime,1)over(PARTITIONby c_id orderby score desc)))

Lead()  求当前行之后第 N 行对应的字段的值
select*,Lead(score,3)over(PARTITIONby c_id orderby score desc)as'排名'from sc

• 表连接 - 内连接 左连接 右连接 外连接

left
right
inner
outer

• 子查询

select 
    emp_no 
from 
    employees  where emp_no notin(select emp_no from dept_manager)

• like

1.%(任意字符) 的用法 查询名字以 网结尾的 
%占位符表示0个或多个字符，可放在查询条件的任意位置

select 
    name 
fromtablewhere 
    name like'%网'2._的用法 查询四个字符，并且是以 abc结尾
_占位符表示一个字符，可放在查询条件的任意位置用法和%类似

select 
    str
fromtablewhere 
    name like'_abc'3.[] 指定范围 ([a-f]) 或集合 ([abcdef]) 中的任何单个字符
like’[CK]ars[eo]n’ 将搜索下列字符串：Carsen、Karsen、Carson 和 Karson（如 Carson）。
like’[M-Z]inger’ 将搜索以字符串 inger 结尾、以从 M 到 Z 的任何单个字母开头的所有名称（如 Ringer）。

4.[^] 不属于指定范围 ([a-f]) 或集合 ([abcdef]) 的任何单个字符
like’M[^c]%’ 将搜索以字母 M 开头，并且第二个字母不是 c 的所有名称（如MacFeather）。

5.* 它同于DOS命令中的通配符，代表多个字符
c*c代表cc,cBc,cbc,cabdfec等多个字符。

6.？同于DOS命令中的？通配符，代表单个字符
b?b代表brb,bFb等

• between and

SELECT*FROM 
    table_name
WHERE 
    date_column BETWEEN'1998-01-01'AND'2020-01-01';

*not - in

select 
    emp_no 
from 
    employees  where emp_no notin(select emp_no from dept_manager)

• or

select age fromtablewhere age >10or age <5

• 逻辑判断

selectif(score>60,'及格','不及格')as 标准 fromtable

• 聚合函数 sum max min count avg stdev median

sum() 求和
max() 最大值
min() 最小值
count() 计数
avg() 平均值
median() 中位数
stdev() 标准差

• case when

select 
    name,casewhen home ='beijing'then'shoudu'casewhen home ='beijing'then'shoudu'endas shoudu_yes 
fromtable

• 分组查询严格模式

SQL的分组查询可以通过使用GROUPBY子句来进行。
在MySQL中，默认情况下，如果SELECT语句包含了非聚合列（没有被聚合函数处理）
而不是所有的列都在GROUPBY子句中指定，则会引发错误。这种模式称为"严格模式"。

• 临时表

-- student 学生表-- s_course 选课关系表-- course 课程表
查询同时选修了 Java基础和高等数学这两门课程，并且Java基础成绩高于高等数学的学生信息
--临时表with tl as(select 
        s.sno, 
        s.sex, 
        s.sname, 
        sc.mark
    from 
        student s, 
        s course sc, 
        course c 
    where 
        s.sno = sc.sno 
        and sc.cno = c.cno
        and c.cname ='java基础'),
t2 as(select 
        s.sno, s.sname, sc.mark 
    from 
        student s, 
        s course sc, 
        course c 
    where 
        s.sno = sc.sno
        and sc.cno = c.cnoand c.cname ='高等数学')select 
    t1.sno, 
    t1.sname, 
    t1.sex
from 
    t1, t2
where t1.sno = t2.sno
    and t1.mark > t2 .mark;

• 自连接

select
    b.*from
    age_table as a,
    age_table as b
where 
    a.age =12and b.name ='zs'-------------------------------------------select 
    a.ename,(select ename from emp as b where b.empno = a.mgr )as manager 
from 
    emp as a

• 空值影响处理

SELECT*FROM table_name WHERE column_name ISNULL;SELECT*FROM table_name WHERE column_name ISNOTNULL;

• exists

exists 子查询
基本语法
whereexists(查询语句)// 根据查询的结果进行判断，如果结果存在就返回 1 ，否则就返回 0 --- 求出，有学生所在的班级select*from 
    my_class as c 
    whereexists(select stu_id from my_student as s where s.class_id = c.class_id)

• 文本 | 时间函数 文本函数
• substring()函数 字符串的截取：substring(字符串，起始位置，截取字符数）
• concat()字符串拼接 语法：concat(字符串1,字符串2,…)
• replace() 替换函数 语法：replace(字符串，原字符，新字符)

时间函数

3. SQL牛客面试题

1.SQL195 查找最晚入职员工的所有信息

找出最晚入职，就是找出最近的入职员工

order by hire_date desc

字段倒序

limit 1

select*from 
    employees orderby hire_date desclimit1select*from 
    employees where hire_date =(selectmax( hire_date )from employees )

1. SQL196 查找入职员工时间排名倒数第三的员工所有信息

使用了 子表查询
窗口函数 dense_rank() over(order by hire_date desc) # 1 2 2 3

select 
    emp_no, birth_date, 
    first_name, 
    last_name,gender, 
    hire_date 
from(select*, dense_rank()over(orderby  hire_date desc)as n  from employees where n =3)

1. SQL197 查找当前薪水详情以及部门编号dept_no

联表查询内连接
排序

select 
    s.emp_no emp_no, 
    s.salary salary , 
    s.from_date from_date , 
    s.to_date to_date, 
    d.dept_no dept_no  
from salaries s 
    join dept_manager d on s.emp_no = d.emp_no 
    orderby s.emp_no asc;

1. SQL198 查找所有已经分配部门的员工的last_name和first_name以及dept_no

表查询的左连接

select 
    e.last_name  last_name , 
    e.first_name  first_name , 
    d.dept_no dept_no 
from 
    dept_emp d leftjoin employees e on d.emp_no  = e.emp_no 

1. SQL199 查找所有员工的last_name和first_name以及对应部门编号dept_no

左表查询

select
    e.last_name last_name,
    e.first_name first_name,
    d.dept_no dept_no
from
   employees e 
   leftjoin dept_emp d on d.emp_no = e.emp_no

1. SQL201 查找薪水记录超过15条的员工号emp_no以及其对应的记录次数t

group by 字段 having 字段
count(字段)

select 
    emp_no ,count(emp_no) t 
from 
    salaries  groupby emp_no havingcount(emp_no)>15;

1. SQL202 找出所有员工当前薪水salary情况

distinct salary 去重
order by salary desc 排序

selectdistinct salary 
from 
    salaries orderby salary desc

1. SQL204 获取所有非manager的员工emp_no

not in()
子查询 select emp_no from dept_manager

select 
    emp_no 
from 
    employees  where emp_no notin(select emp_no from dept_manager)

1. SQL205 获取所有员工当前的manager

内连接查询

select 
    d.emp_no emp_no, 
    de.emp_no manager  
from 
    dept_emp d innerjoin  dept_manager de on d.dept_no = de.dept_no 
where 
    d.emp_no != de.emp_no

1. SQL206 获取每个部门中当前员工薪水最高的相关信息

子表查询
内连接
窗口函数 rank( ) # 1 2 2 4
partition by 分组
order by sa.salary desc 排序

select
    a.dept_no dept_no,
    a.emp_no emp_no,
    a.salary maxSalary
from(select
            de.dept_no,
            de.emp_no,
            sa.salary,
            rank()over(partitionby
                    de.dept_no
                orderby
                    sa.salary desc) rank_n
        from
            dept_emp de
            join salaries sa on de.emp_no = sa.emp_no
    ) a
where
    a.rank_n =1

1. 查找employees表emp_no与last_name的员工信息

where
and
order by

select  
    emp_no,    
    birth_date,    
    first_name,    
    last_name,    
    gender,    
    hire_date 
from 
    employees  
where 
    emp_no %2!=0and last_name !='Mary'orderby hire_date desc

1. SQL210 统计出当前各个title类型对应的员工当前薪水对应的平均工资

avg() 函数
内连接
分组
排序

select  
    t.title ,avg(s.salary) average 
from  
    titles t join salaries s on t.emp_no  = s.emp_no 
    groupby t.title  orderby average asc

1. xxxxx

4. 网上收集面试题

1. having和where的区别

having是在分组后对数据进行过滤

where是在分组前对数据进行过滤

having后面可以使用聚合函数

where后面不可以使用聚合

HAVING SUM(population)>1000000

1. SQL语句中的执行顺序是怎样的

form -> where -> group by -> having -> select -> order by -> limit

1. SQL中如何实现去重操作

第一种

distinct
select distinct name,id form table
select count(distinct name) form table

第二种

group by
select 重复的字段名 from 表名 group by 重复的字段名

1. SQL中 count(*), count(字段), count(distinct 字段)的区别是什么

count(*)表示的是直接查询符合条件的数据库表的行数
COUNT(字段)表示的是查询符合条件的列的值不为NULL的行数
count(distinct 字段) 表示的是查询去重之后符合条件的列的值不为NULL的行数

1. SQL如何求’yyyy-MM-dd’ 的日期差

select datediff(day, ‘2008-12-29’, ‘2018-12-30’) as date

select datediff(day,convert(date,‘20220824’,112),convert(date,‘20230824’,112)) from your_table;

1. SQL中 if、case when的区别

case when

select name, case when home = ‘beijing’ then ‘shoudu’ end as shoudu_yes from table
表达式 | 多条件判断 | 适合复杂逻辑

if

SELECT id, name, IF(score >= 60, ‘及格’, ‘不及格’) AS result FROM students;
单条件判断 | 函数形式 | 简单逻辑

1. SQL中什么是笛卡尔积，笛卡尔积一般出现在什么情况下

笛卡尔积
是两张表的行数的乘积
一般出现在联表查询的时候
内连接可以解决这个问题

1. SQL中除了rank以外还有哪些窗口函数

窗口函数
除了rank，sql还提供了许多其他窗口函数，用于在指定的范围内进行计算、排序和分析。以下是一些常见的窗口函数。

①dense_mark：分配连续的、不重复的排名给结果集中的行，相同的值会获得相同的排名，而且不会跳过排名。

②row_number：为结果集中的每一行分配唯一的、连续的整数排名，不考虑相同值的行。

③ntile(n)：将结果集分成n个大小相等的部分，并为每个部分的行分配一个整数值表示部分号（1到n）。

④lead(column , offset)：返回当前行之后的某一行中的列值。

⑤lag(column,offset)：返回当前行之前的某一行中的列值。

⑥first_value：获取分组集合中第一行的某个列值。

⑦last_value：获取分组集合中最后一行的某个列值。

⑧sum/avg/min/max(column) over(partition by … order by …)：计算指定窗口中某列的总和/平均值/最小/最大值。

1. 简述一下max()聚合函数和窗口函数max(A)over(partiton by B)的区别

①max聚合函数：用于在查询中对某个列的值进行聚合计算，得出整个结果集的最大值。
②max窗口函数：在查询结果集的某个窗口（或分区）上进行计算的函数。窗口函数可以同时获得每个分区的最大值。

1. sql如何将类型为float的字段保留两位小数

mysql 使用format函数

select format(float_column,2) as formatted_float from your_table;

sql server

select round(float_column,2) as formatted_float from your_table;

1. sql中，left join , right join , inner join 有什么差别

①inner join返回两个表中的匹配行，如果某行一个表中找不到匹配的行，那么这个行不会在结果中显示。

②left join返回左表中的所有行以及右表中与坐标匹配的行。如果在右表中找不到匹配的行，那么对应的右表列将会显示为NULL。

③right join返回右表的所有行，以及左表中与右表匹配的行。如果在左表中找不到匹配的行，那么对应的左表列将会显示为NULL。

join操作的效率通常比单纯使用子查询的效率要高，但应尽量避免多重嵌套join或join多张表，以免影响查询性能。

1. sql中如何将int类型的字段转换为string类型

mysql

select cast(int_column as char) as string_column from your_table;

1. 窗口函数和where的执行顺序孰先孰后

在sql中，where子句通常会在窗口函数之前执行。

首先，数据库会根据where子句的条件对表进行筛选，过滤出符合条件的行。然后，在已经筛选出的结果集上，窗口函数开始计算。
这个执行顺序确保了窗口函数在筛选和过滤数据后进行计算，以及在计算窗口函数不会考虑不满足where子句条件的行。

1. sql中，如何求字段整体的标准差和均值

均值使用聚合函数avg计算：

select avg(column) as mean from your_table;

标准差用聚合函数stdev计算：

select stdev(column) as std from your_table;

1. SQL中如何将’yyyy-MM-dd’的日期格式转换为’yyyyMMdd’形式

select convert(varchar(8),date_column,112) from your_table;

select cast(date_column as char(8)) from your_table;