SQL秋招刷题

1、查看最近日期或者查找倒数第几的日期，对日期字段使用order by排序，并使用limit限制。limit 0,1

2、表的连接where条件在Join on 后面，on本身是对字段限制条件，条件可以用and连接

3、left join返回左表的所有记录以及右表和左表连接字段相同的记录

right join返回右表的所有记录以及左表和右表连接字段相同的记录

inner join返回左表和右表连接字段相同的记录

join默认是内连接inner join

4、对group by后的字段限定条件使用having关键字

5、distinct关键字限定某一字段求不重复结果

6、表的连接后对空值字段限定条件，使用where 字段 is null

7、不等于!=或者<>。a%b返回a/b的余数。avg(字段)求平均，一般和分组使用。

8、 GROUP BY语句用来与聚合函数(aggregate functions such as COUNT, SUM, AVG, MIN, or MAX.)联合使用来得到一个或多个列的结果集。 **因为聚合函数通过作用于一组数据而只返回一个单个值，因此，在SELECT语句中出现的元素要么为一个聚合函数的输入值，要么为GROUP BY语句的参数，否则会出错。**这是因为其他参数一组可能含有多个数据，造成混乱。

实例

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

思路：使用join连接两张包含员工信息和薪资的表，并使用窗口函数添加一列根据部门分组对工资排名的列，选择聚合表的相关字段并使用where条件限制排名=1

#根据部门dept_no分组，对每组根据salary排名
select t.dept_no,t.emp_no,t.salary as maxSalary
from(
select t1.dept_no,t1.emp_no,t2.salary,row_number() over(partition by t1.dept_no order by t2.salary desc) as rk 
    from (select * from dept_emp where to_date='9999-01-01')t1
    inner join (select * from salaries where to_date='9999-01-01')t2
    on t1.emp_no=t2.emp_no
)t
where t.rk=1
order by t.dept_no;

9、不使用order by取出薪水第二高的员工信息

思路：使用max函数取出薪水的最大值，再用<取出不含最大值的子表，在子表中再取最大值。

select t1.emp_no,t2.salary,t1.last_name,t1.first_name
from employees t1
inner join salaries t2
on t1.emp_no=t2.emp_no
where t2.salary=
(select max(salary) from salaries where salary<(select max(salary) from salaries));

10、可以使用join进行多个表的连接

11、 查找在职员工自入职以来的薪水涨幅情况

思路：构造两张表，一个为入职员工薪资表，一个为当前薪资表，当前员工薪资减入职时员工薪资为涨幅

select t2.emp_no,(t2.salary-t1.salary) as growth
from(
select s.emp_no,s.salary from salaries s 
    inner join employees e on s.emp_no=e.emp_no and s.from_date=e.hire_date
)t1
inner join(
select s.emp_no,s.salary from salaries s where to_date='9999-01-01'
)t2
on t1.emp_no=t2.emp_no
order by growth;

12、排序的窗口函数区别：

RANK() 在计算排序时，若存在相同位次，会跳过之后的位次。

例如，有3条排在第1位时，排序为：1，1，1，4······

DENSE_RANK() 在计算排序时，若存在相同位次，不会跳过之后的位次。

例如，有3条排在第1位时，排序为：1，1，1，2······

ROW_NUMBER() 这个函数赋予唯一的连续位次。

例如，有3条排在第1位时，排序为：1，2，3，4······

13、注意子查询的逻辑，每构造一张虚拟表使用select从表中取数，字表放在()里，给字表一个别称

14、字符串连接方法

concat(字段1,‘分隔符’,字段2)

concat_ws(‘分隔符’,字段1，字段2，…) 适用于同一分隔符连接多个字段

字符串截取：substring(字符串，位置，子串长度)

select substring(time,1,10) as dt from ***

15、创建新表

create table actor(
actor_id smallint(5) not null primary key auto increment,
first_name varchar(45) not null,
last_name varchar(45) not null,
last_update date not null DEFAULT (datetime('now','localtime'))
#添加外键约束
foreign key(actor_id) references tb_Users(ID));

#已经建好表的情况添加约束
#添加外键约束
alter table audit add constraint foreign key(emp_no)
references employees_test(id);

#创建新表的三种方法
#1、常规创建
create table if not exists 目标表
#2、复制表格
create 目标表 like 来源表
#3、将table1的部分拿来创建table2
create table if not exists actor_name
(
first_name varchar(45) not null,
last_name varchar(45) not null
)
select first_name,last_name
from actor

16、插入内容

insert into actor values(1,'PENELOPE','GUINESS','2006-02-15 12:34:33'),
                         (2,'NICK','WAHLBERG','2006-02-15 12:34:33');
                         
# mysql中常用的三种插入数据的语句:
# insert into表示插入数据，数据库会检查主键，如果出现重复会报错；
# replace into表示插入替换数据，需求表中有PrimaryKey，
#             或者unique索引，如果数据库已经存在数据，则用新数据替换，如果没有数据效果则和insert into一样；
# insert ignore表示，如果中已经存在相同的记录，则忽略当前新数据；
insert ignore into actor values("3","ED","CHASE","2006-02-15 12:34:33");

17、文件导入：

方法一：直接导入source

use your_database;
SOURCE your_disk:/yourpath/table.sql

方法二：load data infile

load data infile "/data/mysql/e.sql" into table e fields terminated by ',';

load data infile 'F:/MySqlData/test1.csv' --CSV文件存放路径
into table student--要将数据导入的表名
fields terminated by ',' optionally enclosed by '"' escaped by '"'
lines terminated by '\r\n';

#导入数据中包含中文字体
load data infile 'F:/MySqlData/test1.csv' --CSV文件存放路径
into table student character set gb2312 --要将数据导入的表名
fields terminated by ',' optionally enclosed by '"' escaped by '"'
lines terminated by '\r\n';

17、文件导出

select * from e into outfile "/data/mysql/e.sql" fields terminated by ',';

17、创建索引

1. 添加主键

2. 添加唯一索引

3. 添加普通索引

4. 添加全文索引

PS: 附赠删除索引的语法：

`DROP INDEX index_name ON tbl_name;``// 或者``ALTER TABLE tbl_name DROP INDEX index_name；``ALTER TABLE tbl_name DROP PRIMARY KEY;`

18、创建视图

create view actor_name_view 
as(
select first_name as first_name_v,
last_name as last_name_v
from actor);

19、为原表添加一列，时间类型

alter table actor add column 
create_date datetime not null default'2020-10-01 00:00:00';

20、构造触发器（向一张表执行某项操作，另一张表会进行某种更改）

CREATE TRIGGER trigger_name
trigger_time trigger_event ON tbl_name
FOR EACH ROW
trigger_stmt
其中：

• trigger_name：标识触发器名称，用户自行指定；

• trigger_time：标识触发时机，取值为 BEFORE 或 AFTER；

• trigger_event：标识触发事件，取值为 INSERT、UPDATE 或 DELETE；

• tbl_name：标识建立触发器的表名，即在哪张表上建立触发器；

• trigger_stmt：触发器程序体，可以是一句SQL语句，或者用 BEGIN 和 END 包含的多条语句，每条语句结束要分号结尾。 NEW 用来表示将要或已经插入的新数据； OLD 用来表示将要或已经被修改的原数据 。

• #在插入数据之后触发向新表中插入新的数据
  create trigger audit_log 
  after insert on employees_test
  for each row
  begin 
      insert into audit values(new.id,new.name);
  end;
  

21、删除数据

#mysql不支持在子查询的同时将表中数据删除（外面再套一层select
delete from titles_test where id not in(
select * from (
    select min(id) from titles_test group by emp_no)a);

22、更新数据（行记录）

#set后多个字段使用逗号（，）分隔
update titles_test
set to_date=null,from_date='2001-01-01'
where to_date='9999-01-01';

#员工涨薪10%
UPDATE salaries SET salary=1.1*salary WHERE to_date='9999-01-01' and emp_no in (
select emp_no from emp_bonus);

#replace函数将数据进行替换
update titles_test set emp_no=replace(emp_no,10001,10005) where id=5;

23、更改表名

alter table old_name rename as new_name;

24、 查找字符串’10,A,B’ 中逗号’,'出现的次数cnt。

select (length("10,A,B")-length(replace("10,A,B",",","")))

25、字符串函数：length(),left(),right()

26、 聚合函数group_concat（X，Y），其中X是要连接的字段，Y是连接时用的符号，可省略，默认为逗号。
此函数必须与GROUP BY配合使用。

语法：group_concat( [distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator ‘分隔符’] )

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

27、 查找排除在职(to_date = ‘9999-01-01’ )员工的最大、最小salary之后，其他的在职员工的平均工资avg_salary。

select (sum(salary)-max(salary)-min(salary))/(count(salary)-2) as avg_salary
from salaries where to_date="9999-01-01";

28、LIMIT 语句结构： LIMIT X,Y

• Y ：返回几条记录
• X：从第几条记录开始返回（第一条记录序号为0，默认为0）

29、EXISTS作用；判断是否存在某种条件的记录，如果存在这种记录就返回TRUE,否则返回FALSE.相当于IN。NOT EXISTS 相当于NOT IN

写法：通常在where子句中，右侧书写一个参数，通常为子查询，左侧无参数

`select *``from t1``where exists (select * from t2 where t1.column = t2.column)`

30、查找员工奖金信息（ bonus类型btype为1其奖金为薪水salary的10%，btype为2其奖金为薪水的20%，其他类型均为薪水的30%。 当前薪水表示to_date=‘9999-01-01’ ）

select e.emp_no,e.first_name,e.last_name,b.btype,s.salary,
case b.btype
when 1 then s.salary*0.1
when 2 then s.salary*0.2
else s.salary*0.3
end as bonus
from employees e
inner join emp_bonus b on e.emp_no=b.emp_no
inner join salaries s on e.emp_no=s.emp_no
where s.to_date="9999-01-01";

31、窗口函数也称OLAP函数（online analytical processing),意思对数据库进行实时分析。将某个查询作为字表进行分组排序查询。
语法：

`<窗口函数> OVER ([PARTITION BY <列清单>]``      ``ORDER BY <排序用列清单>)`
窗口函数分为两类：1）聚合函数；2）专用函数

聚合函数：sum,min,max,avg,count

专用窗口函数：rank,dense_rank,row_number

32、round(字段,n)保留n位小数

求某一部分所占的比例，用round()函数,case关键字，sum()函数等结合完成

统计正常用户发送给正常用户邮件失败的概率:， 结果保留到小数点后面3位(3位之后的四舍五入)，并且按照日期升序排序

select e.date,
round(sum(case e.type when "completed" then 0 else 1 end)/count(e.type),3) as p
from email e
inner join user u1
on e.send_id=u1.id and u1.is_blacklist=0
inner join user u2
on e.receive_id=u2.id and u2.is_blacklist=0
group by e.date
order by e.date;

33、 统计一下牛客每个用户最近登录是哪一天。 select选的字段是分组字段和聚合字段，不会有错误。

select user_id,max(date) as d
from login
group by user_id
order by user_id;

43、计算新登录用户次日留存率。

次日留存率=第一天登录并且第二天也登录的用户数/总用户数

SELECT
ROUND(COUNT(DISTINCT user_id)*1.0 / (SELECT COUNT(DISTINCT user_id) FROM login), 3)
FROM login
WHERE (user_id, date)
IN (SELECT user_id, DATE_ADD(MIN(date),INTERVAL 1 DAY) FROM login GROUP BY user_id);

44、 统计一下牛客每个日期登录新用户个数，

#方法一：使用窗口函数对每个用户登录日期进行排序
select a.date,
sum(case when a.t_rank=1 then 1 else 0 end) new
from 
(select date, row_number() over(partition by user_id order by date) t_rank
from login) a
group by a.date
order by a.date;

#方法二：连接两张表，一张为表中所有日期，一张为每个用户第一次登录日期
select t1.date,count(t2.user_id) as new
from (select distinct date from login) t1
left join (select user_id,min(date) as date from login group by user_id)t2
on t1.date=t2.date
group by t1.date
order by t1.date;

45、 统计一下牛客每个日期新用户的次日留存率。

select date
        ,ifnull(round((sum(case when (user_id,date)in
            (select user_id,date_add(date,interval -1 day) 
             from login)  and (user_id,date)in (select user_id,min(date)from login group by user_id)
            then 1 else 0 end))/
        (sum(case when (user_id,date)in
            (select user_id,min(date)from login group by user_id)
            then 1 else 0 end)),3),0)as p
from login
group by date
order by date;

46、统计每个日期累计求和

select u.name as u_n,l.date,t.ps_num
from login l
inner join (
select user_id,date,sum(number) over(partition by user_id order by date) as ps_num from passing_number)t
on l.user_id=t.user_id and l.date=t.date
inner join user u
on l.user_id=u.id
order by l.date,u.name;

47、求考试分数大于小组平均分数的记录。以成绩表构造一个分组平均成绩表进行连接

select g.* from grade g
inner join(
select job,avg(score) as avg from grade group by job
)t
on g.job=t.job and g.score>t.avg
order by id;

48、返回每组积分前两名记录。将积分表使用窗口函数添加字段排名，限定排名小于等于2

select t.id,l.name,t.score
from(
select *,dense_rank() over(partition by language_id order by score desc) as rk from grade
)t
inner join 
language l
on t.language_id=l.id
where t.rk<=2
order by l.name,t.score desc,t.id;

49、求每组中位数的位置

FLOOR(x) 返回小于或等于 x 的最大整数.

无论是原序列是奇数个还是偶数个，开始位置都是n+1地板除2，结束位置都是n+2地板除2

select job,
floor((count(score)+1)/2) as start,
floor((count(score)+2)/2) as end
from grade
group by job
order by job;

#求中位数
#中位数无论正序还是倒序都处于同一位置
with t_rank as
(
    select *,
       count(score) over(partition by job) as total,
       row_number() over(partition by job order by score) as a, #升序序号
       row_number() over(partition by job order by score desc) as b #逆序序号
    from grade
)

select id,job,score,b
from t_rank
where a>=total/2 and b>=total/2
order by id

50、MySQL日期可以比较date>"9999-01-01"

51、改变日期格式：
date_format(date,"%y-%m-%d")

str_to_date(date,"%Y-%m_%d") date1

52、表的自关联

select * from table_test t1,table_test t2 where t2.pid=t1.id; #找到表每个id对应的父id（pid）