牛客网刷题

知识点 ：改表名 ALTER TABLE

ALTER TABLE table_name RENAME TO new_table_name

ALTER  TABLE titles_test RENAME TO titles_2017

知识点: length() replace()

length(arg) 返回的是所求的字符串的长度。
replace(string,Y,Z)

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

select length('10,A,B')-length(replace('10,A,B',',','')) as cnt

知识点 group_concat()

group_concat()：

1、功能：

将group by产生的同一个分组中的值连接起来，返回一个字符串结果。

2、语法：

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

题目
按照dept_no进行汇总，属于同一个部门的emp_no按照逗号进行连接，结果给出dept_no以及连接出的结果employees
CREATE TABLE dept_emp (
emp_no int(11) NOT NULL,
dept_no char(4) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,dept_no));
输出格式:
dept_no employees
d001 10001,10002
d002 10006
d003 10005
d004 10003,10004
d005 10007,10008,10010
d006 10009,10010

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

知识点：插入新数据

mysql中常用的三种插入数据的语句:

• insert into表示插入数据，数据库会检查主键，如果出现重复会报错；

• replace into表示插入替换数据，需求表中有PrimaryKey，或者unique索引，如果数据库已经存在数据，则用新数据替换，如果没有数据效果则和insert into一样；

• insert ignore表示，如果中已经存在相同的记录，则忽略当前新数据；

题目
对于表actor插入如下数据,如果数据已经存在，请忽略(不支持使用replace操作)

insert ignore into actor values("3","ED","CHASE","2006-02-15 12:34:33");

知识点：创建视图 CREATE VIEW …AS…

针对actor表创建视图actor_name_view，只包含first_name以及last_name两列，并对这两列重新命名，first_name为first_name_v，last_name修改为last_name_v：

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

知识点：强制索引

https://www.begtut.com/mysql/mysql-force-index.html
查询优化器使用可用的统计信息来提出所有候选计划中成本最低的计划。

查询可能会请求价格在10到80之间的产品。如果统计数据显示80％的产品具有这些价​​格范围，那么它可能会认为全表扫描效率最高。但是，如果统计数据显示很少有产品具有这些价​​格范围，那么读取索引后跟表访问可能比全表扫描更快，更有效。

如果查询优化器忽略索引，您可以使用FORCE INDEX提示来指示它使用索引。

题目：
针对salaries表emp_no字段创建索引idx_emp_no，查询emp_no为10005, 使用强制索引。
CREATE TABLE salaries (
emp_no int(11) NOT NULL,
salary int(11) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,from_date));
create index idx_emp_no on salaries(emp_no);

select * from salaries force index(idx_emp_no) where  emp_no = '10005'

知识点 ：删除重复数据

mysql不允许delete from where 语句一边查询，一边删除。
删除重复项中的where子句使用了要删除的表，就要使用别名

题目

删除emp_no重复的记录，只保留最小的id对应的记录。
CREATE TABLE IF NOT EXISTS titles_test (
id int(11) not null primary key,
emp_no int(11) NOT NULL,
title varchar(50) NOT NULL,
from_date date NOT NULL,
to_date date DEFAULT NULL);

insert into titles_test values (‘1’, ‘10001’, ‘Senior Engineer’, ‘1986-06-26’, ‘9999-01-01’),
(‘2’, ‘10002’, ‘Staff’, ‘1996-08-03’, ‘9999-01-01’),
(‘3’, ‘10003’, ‘Senior Engineer’, ‘1995-12-03’, ‘9999-01-01’),
(‘4’, ‘10004’, ‘Senior Engineer’, ‘1995-12-03’, ‘9999-01-01’),
(‘5’, ‘10001’, ‘Senior Engineer’, ‘1986-06-26’, ‘9999-01-01’),
(‘6’, ‘10002’, ‘Staff’, ‘1996-08-03’, ‘9999-01-01’),
(‘7’, ‘10003’, ‘Senior Engineer’, ‘1995-12-03’, ‘9999-01-01’);

delete from titles_test
where id not in
                (select * from(select min(id)
                               from titles_test
                               group by emp_no)a
                 )

思路：“删除重复项，取其最小id”,找出最小id集合保留，反转获得非最小id组合将其删除。

知识点：触发器

CREATE TRIGGER <触发器名>
< BEFORE | AFTER > <INSERT | UPDATE | DELETE > ON <表名>
FOR EACH Row
<触发器主体>

【实例 2】创建一个名为 double_salary 的触发器，触发的条件是向数据表 tb_emp6 中插入数据之后，再向数据表 tb_emp7 中插入相同的数据，并且 salary 为 tb_emp6 中新插入的 salary 字段值的 2 倍。输入的 SQL 语句和执行过程如下所示。
mysql> CREATE TRIGGER double_salary
-> AFTER INSERT ON tb_emp6
-> FOR EACH ROW
-> INSERT INTO tb_emp7
-> VALUES (NEW.id,NEW.name,deptId,2*NEW.salary);

插入新的列 NEW.COCLUMN

题目
构造一个触发器audit_log，在向employees_test表中插入一条数据的时候，触发插入相关的数据到audit中。
CREATE TABLE employees_test(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);
CREATE TABLE audit(
EMP_no INT NOT NULL,
NAME TEXT NOT NULL
);

CREATE TRIGGER audit_log   
AFTER INSERT ON employees_test 
FOR EACH Row  
insert into audit
VALUES (NEW.id,NEW.name);

知识点： 窗口函数sum() over(partition by order by) 条件求和

按照salary的累计和running_total，其中running_total为前N个当前( to_date = ‘9999-01-01’)员工的salary累计和，其他以此类推。 具体结果如下Demo展示。。
CREATE TABLE salaries ( emp_no int(11) NOT NULL,
salary int(11) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,from_date));
输出格式:
emp_no salary running_total
10001 88958 88958
10002 72527 161485
10003 43311 204796
10004 74057 278853
10005 94692 373545
10006 43311 416856
10007 88070 504926
10009 95409 600335
10010 94409 694744
10011 25828 720572

running_total算的是累计总用人成本，salary的合计
连表

select a.emp_no,a.salary,SUM(b.salary)as running_total
from salaries a 
left join salaries b on a.emp_no >= b.emp_no and b.to_date = '9999-01-01'
where a.to_date = '9999-01-01'
group by a.emp_no

窗口函数 更简洁

SELECT emp_no, salary, SUM(salary) OVER(ORDER BY emp_no) AS running_total FROM salaries WHERE to_date = '9999-01-01'

知识点 创建外键约束

alter table 表名
add constraint foreign key表名（外键）
references 表名（主键）

题目：

在audit表上创建外键约束，其emp_no对应employees_test表的主键id。
(以下2个表已经创建了)

CREATE TABLE employees_test(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);

CREATE TABLE audit(
EMP_no INT NOT NULL,
create_date datetime NOT NULL
);

alter table audit
add constraint  foreign key  (EMP_no)
references  employees_test(id)

第一次登陆 min(date) 计数神器——sum+case

牛客每个人最近的登录日期(四)
题目描述
牛客每天有很多人登录，请你统计一下牛客每个日期登录新用户个数，
有一个登录(login)记录表，简况如下:

第1行表示user_id为2的用户在2020-10-12使用了客户端id为1的设备登录了牛客网，因为是第1次登录，所以是新用户
。。。
第4行表示user_id为2的用户在2020-10-13使用了客户端id为2的设备登录了牛客网，因为是第2次登录，所以是老用户
。。
最后1行表示user_id为4的用户在2020-10-15使用了客户端id为1的设备登录了牛客网，因为是第2次登录，所以是老用户

请你写出一个sql语句查询每个日期登录新用户个数，并且查询结果按照日期升序排序，上面的例子查询结果如下:

查询结果表明:
2020-10-12，有3个新用户(user_id为2，3，1)登录
2020-10-13，没有新用户登录
2020-10-14，有1个新用户(user_id为4)登录
2020-10-15，没有新用户登录

#明确问题：登录的当前日期=该用户所有登录日期的最小值
#方法一：计数神器——sum+case方法，不容易出错。

select distinct date
        ,sum(case when (user_id,date) in 
    (select user_id,min(date)from login group by user_id)
    then 1 else 0 end)
from login
group by date
order by date;

牛客每个人最近的登录日期(三)
牛客每天有很多人登录，请你统计一下牛客新登录用户的次日成功的留存率，
有一个登录(login)记录表，简况如下:

第1行表示user_id为2的用户在2020-10-12使用了客户端id为1的设备第一次新登录了牛客网
。。。
第4行表示user_id为3的用户在2020-10-12使用了客户端id为2的设备登录了牛客网
。。。
最后1行表示user_id为1的用户在2020-10-14使用了客户端id为2的设备登录了牛客网

请你写出一个sql语句查询新登录用户次日成功的留存率，即第1天登陆之后，第2天再次登陆的概率,保存小数点后面3位(3位之后的四舍五入)，上面的例子查询结果如下:

查询结果表明:
user_id为1的用户在2020-10-12第一次新登录了，在2020-10-13又登录了，算是成功的留存
user_id为2的用户在2020-10-12第一次新登录了，在2020-10-13又登录了，算是成功的留存
user_id为3的用户在2020-10-12第一次新登录了，在2020-10-13没登录了，算是失败的留存
user_id为4的用户在2020-10-13第一次新登录了，在2020-10-14没登录了，算是失败的留存
固次日成功的留存率为 2/4=0.5
(sqlite里查找某一天的后一天的用法是:date(yyyy-mm-dd, ‘+1 day’)，四舍五入的函数为round，sqlite 1/2得到的不是0.5，得到的是0，只有1*1.0/2才会得到0.5
mysql里查找某一天的后一天的用法是:DATE_ADD(yyyy-mm-dd,INTERVAL 1 DAY)，四舍五入的函数为round)

题解
留存率，公式列出来:
（第一天登录的新用户并且第二天也登录的用户）/（总用户）即为新登录用户的次日成功的留存率

select count(distinct user_id) from login
找到每个用户第一天登陆的日子，其实挺好找，和前面找最近登录的日子差不多，一个是max，一个是min:

select user_id,min(date) from login group by user_id
比如上面查找语句是1，2020-10-12；那么如果找到一个结果为1，2020-10-13的那么是不是就符合结果了，于是可以如下写:

select user_id,DATE_ADD(min(date),INTERVAL 1 DAY)from login group by user_id
这样就可以找到所有的在第一天登录的新用户并且第二天也登录的用户，以及第二天的日期。
所以从这个里面找到所有的count(distinct user_id)除以总用户就可以得到结果了，于是整个sql语句如下:

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);