牛客MySQL：错题，Java程序员

group by s1.emp_no

having max(s2.to_date) = ‘9999-01-01’;

最后再进行一个排序

select s1.emp_no,sum(s2.salary - s1.salary) as growth from salaries s1,salaries s2

where s1.emp_no = s2.emp_no

and s1.to_date = s2.from_date

group by s1.emp_no

having max(s2.to_date) = ‘9999-01-01’

order by growth

总结

当一个表记录着某一属性的变动，可以使用自身联结的方式找到每一段的变动（前提是该表有两个属性记录着某行

对所有员工的薪水按照salary进行按照1-N的排名

=======================================================================================

题目描述

---

有一个薪水表salaries简况如下:

对所有员工的薪水按照salary进行按照1-N的排名，相同salary并列且按照emp_no升序排列：

题解思路

---

排序使用一个order by salary即可，但问题出在要如何统计t_rank。

select s.emp_no,s.salary from salaries s order by s.salary desc;

我想到的是使用关联子查询，统计salaries表里面有多少个salary是大于当前salary的，然后进行加1即可，当然还要进行相同salary去重，这里也可以改成大于等于，那样就不需要进行加1

select s.emp_no,s.salary,

(select count(distinct s2.salary) from salaries s2 where s2.salary >= s.salary)

from salaries s order by s.salary desc;

查找描述信息中包含robot的电影对应的分类名称以及电影数目，而且还需要该分类包含电影总数量>=5部

===============================================================================================================

题目描述

---

film表

| 字段 | 说明 |

| — | — |

| film_id | 电影id |

| title | 电影名称 |

| description | 电影描述信息 |

​ CREATE TABLE IF NOT EXISTS film (

​ film_id smallint(5) NOT NULL DEFAULT ‘0’,

​ title varchar(255) NOT NULL,

​ description text,

​ PRIMARY KEY (film_id));

category表

| 字段 | 说明 |

| — | — |

| category_id | 电影分类id |

| name | 电影分类名称 |

| last_update | 电影分类最后更新时间 |

​ CREATE TABLE category (

​ category_id tinyint(3) NOT NULL ,

​ name varchar(25) NOT NULL, last_update timestamp,

​ PRIMARY KEY ( category_id ));

film_category表

| 字段 | 说明 |

| — | — |

| film_id | 电影id |

| category_id | 电影分类id |

| last_update | 电影id和分类id对应关系的最后更新时间 |

​ CREATE TABLE film_category (

​ film_id smallint(5) NOT NULL,

​ category_id tinyint(3) NOT NULL, last_update timestamp);

查找描述信息(film.description)中包含robot的电影对应的分类名称(category.name)以及电影数目(count(film.film_id))，而且还需要该分类包含电影总数量(count(film_category.category_id))>=5部 查找描述信息中包含robot的电影对应的分类名称以及电影数目，而且还需要该分类包含电影总数量>=5部

解题思路

---

除了最后的判断该分类包含电影总数量>=5部，前面的都是很简单

使用内联接将三张表都联结起来，然后使用where来对robot进行模糊查询，再对电影名字进行group by即可

写出的SQL如下

select c.name,count(f.film_id)

from film f inner join film_category fc on f.film_id = fc.film_id

inner join category c on fc.category_id = c.category_id

where f.description like ‘%robot%’

group by c.name;

那么要怎么判断该分类的电影数目要大于5呢

我这里的解决办法就是使用子查询，在having后对当前组的category_id进行子查询来查该分类有多少条电影数目

由于要在having使用关联子查询，所以group by 必须要拥有该字段才可以关联起来，需要加上c.category_id

最后的SQL修改成下面这样

select c.name,count(f.film_id)

from film f inner join film_category fc on f.film_id = fc.film_id

inner join category c on fc.category_id = c.category_id

where f.description like ‘%robot%’

group by c.name,c.category_id

having 5 <= (select count(fc2.film_id) from film_category fc2

where fc2.category_id = c.category_id);

批量插入数据，不使用replace操作

================================================================================

题目描述

---

题目已经先执行了如下语句:

drop table if exists actor; CREATE TABLE actor ( actor_id smallint(5) NOT NULL PRIMARY KEY, first_name varchar(45) NOT NULL, last_name varchar(45) NOT NULL, last_update DATETIME NOT NULL); insert into actor values ('3', 'WD', 'GUINESS', '2006-02-15 12:34:33');

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

| actor_id | first_name | last_name | last_update |

| — | — | — | — |

| ‘3’ | ‘ED’ | ‘CHASE’ | ‘2006-02-15 12:34:33’ |

解题思路

---

第一次知道MySQL有ignore的写法，忽略相同的数据

insert ignore into actor values(‘3’, ‘WD’, ‘GUINESS’, ‘2006-02-15 12:34:33’);

对first_name创建唯一索引uniq_idx_firstname，对last_name创建普通索引idx_lastname

==================================================================================================================================

题目描述

---

针对如下表actor结构创建索引：

(注:在 SQLite 中,除了重命名表和在已有的表中添加列,ALTER TABLE 命令不支持其他操作，

mysql支持ALTER TABLE创建索引)

CREATE TABLE actor (

actor_id smallint(5) NOT NULL PRIMARY KEY,

first_name varchar(45) NOT NULL,

last_name varchar(45) NOT NULL,

last_update datetime NOT NULL);

解题思路

---

MySQL添加4种索引的方式

//添加主键，不能为null且必须唯一

alter table tbl_name add primary key(索引列);

//添加一个唯一索引

alter table tbl_name add unique 索引名字(索引列);

//添加一个普通索引

alter table tbl_name add index 索引名字(索引列);

//添加一个全文索引

alter table tbl_name add fulltext 索引名字(索引列);

配上删除索引的方式

//删除非聚簇索引

drop index 索引名字 on tbl_name;

alter table tbl_name drop index 索引名字;

//删除主键索引

alter table tbl_name drop primary key;

针对actor表创建视图actor_name_view

==========================================================================================

题目描述

---

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

CREATE TABLE actor (

actor_id smallint(5) NOT NULL PRIMARY KEY,

first_name varchar(45) NOT NULL,

last_name varchar(45) NOT NULL,

last_update datetime NOT NULL);

解题思路

---

创建视图的语句为

create view view_name as SQL;

解题SQL

create view actor_name_view

as

select first_name as first_name_V,last_name as last_name_v from actor;

在last_update后面新增加一列名字为create_date

================================================================================================

题目描述

---

存在actor表，包含如下列信息：

CREATE TABLE actor (

actor_id smallint(5) NOT NULL PRIMARY KEY,

first_name varchar(45) NOT NULL,

last_name varchar(45) NOT NULL,

last_update datetime NOT NULL);

现在在last_update后面新增加一列名字为create_date, 类型为datetime, NOT NULL，默认值为’2020-10-01 00:00:00’

解题思路

---

增加列的SQL为

alter table tbl_name add (列信息);

解题SQL

alter table tbl_name add(create_date datetime not null default ‘2020-10-01’);

针对salaries表emp_no字段创建索引idx_emp_no，查询emp_no为10005, 使用强制索引

=========================================================================================================================

题目描述

---

针对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 tbl_name force index(索引名字) where …

解题SQL

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

删除emp_no重复的记录，只保留最小的id对应的记录。

==========================================================================================

题目描述

---

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

解题思路

---

首先第一步，我们要找到哪些要保留的id，注意到这里的id是作为primary key，所以一定不可能出现重复的，也就是不可能出现有emp_no对应的最小id出现在其他emp_no里面

下面SQL就可以获取到需要保留的最小id

select min(t.id)

from titles_test t

group by t.emp_no;

接下来，就可以进行删除

delete from titles_test

where id not in(

select min(t.id)

from titles_test t

group by t.emp_no;

)

然后就会发现报错

提示不可以边读边删（这是因为update操作会拿排他锁，与共享锁发生冲突！）

既然不可以对一张表进行边读边删，那么可以让读的表是一张临时表

delete from titles_test

where id not in(

select * from (

select min(t.id)

from titles_test t

group by t.emp_no

)as temp //注意要给子查询表加别名

)

使用replace进行删除

==========================================================================

题目描述

---

将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005,其他数据保持不变，使用replace实现，直接使用update会报错

解题思路

---

第一次知道replace。。。

使用replace可以减少一次where判断

语法为：

replace(字段名,被替代值,替代值)

比如上面的需求使用update的实现为

update t set emp_no = 10001 where emp_no = 10005 and id = 5;

如果改成replace就可以省略掉emp_no = 10005，只要将id=5的行数据的10005都变成10001即可

update t set emp_no = relace(emp_no,10005,10001) where emp_no = 5;

修改表的名字

===================================================================

题目描述

---

将titles_test表名修改为titles_2017

解题思路

---

修改表的名字仍然使用alter table ，然后使用rename to来修改名字

alter table tbl_name rename to new_name;

解题SQL

alter table tbl_name rename to titles_2017;

增加外键

=================================================================

题目描述

---

在audit表上创建外键约束，其emp_no对应employees_test表的主键id。

解题思路

---

增加表外键的SQL

alter table tbl_name add constraint foreign key(column1)

references tbl_name_two(column2)

解题SQL

alter table audit add constraint foreign key(emp_no)

references employeess_test(id);

查找指定字符串中逗号,出现的次数cnt

================================================================================

题目描述

---

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

解题思路

---

让原本字符串的长度减去去掉逗号后字符串的长度即可

length(str) //获取原字符串长度

replace(str,“,”,“”) //使用replace去掉字符串里面的逗号

解题SQL

select (length(“10,A,B”) - length(replace(“10,A,B”,“,”,“”))) as cnt;

只根据字段的左右两边指定字符order by

===================================================================================

题目描述

---

获取Employees中的first_name，查询按照first_name最后两个字母，按照升序进行排列

解题思路

---

使用right和left来指定order by的比较方法

//指定只比较后面右边两个字符，也就是最后面2个

select first_name from employees order by right(first_name,2);

组concat

====================================================================

题目描述

---

按照dept_no进行汇总，属于同一个部门的emp_no按照逗号进行连接，结果给出dept_no以及连接出的结果employees

解题思路

---

使用group_concat来进行组的拼接，group_concat默认的就是使用逗号来连接

select group_concat(emp_no)

from dept_no

group by emp_no

使用EXISTS判断是否存在

===========================================================================

题目描述

---

使用含有关键字exists查找未分配具体部门的员工的所有信息。共有两张表，一张employees，另一张为dept_emp，使用emp_no关联在了一起

解题思路

---

这道题其实使用一个外联接就可以解决，但只允许使用exists

使用exists一般都是伴随着子查询去做的

解题SQL

select * from employees es

where not exists(select *

from dept_emp de

es.emp_no = de.emp_no);

获取有奖金的员工相关信息。

==========================================================================

题目描述

---

获取有奖金的员工相关信息。

共有4张表，一张员工表employees，一张部门表dept_emp，另一张奖金表emp_bonus还有一张为薪资表salaries（四张表通过emp_no关联起来）

给出emp_no、first_name、last_name、奖金类型btype、对应的当前薪水情况salary以及奖金金额bonus。 bonus类型btype为1其奖金为薪水salary的10%，btype为2其奖金为薪水的20%，其他类型均为薪水的30%。 当前薪水表示to_date=‘9999-01-01’

解题思路

---

首先将这4张表内联起来，内联薪资表时，记得还要匹配当前薪水的(to_date = ‘9999-01-01’)

重点在于如何对奖金进行判断（bonus类型btype为1其奖金为薪水salary的10%，btype为2其奖金为薪水的20%，其他类型均为薪水的30%）

使用case when then end语句来判断

case

when …

then …

end

具体形式为

(case when emp_bonus.btype = 1 then salaries * 0.1

when emp_bonus.btype = 2 then salaries * 0.2

else salaries * 0.3) as bonus

加上之前的内联结，解题SQL为

select eb.emp_no,en.first_name,en.last_name,eb.btype,sa.salary,

(case when eb.btype = 1 then sa.salary * 0.1

when eb.btype = 2 then sa.salary * 0.2

else sa.salary * 0.3 end) as bonus

from emp_bonus eb

inner join employees en on en.emp_no = eb.emp_no

inner join salaries sa on sa.emp_no = en.emp_no and sa.to_date = ‘9999-01-01’;

统计salary的累计和running_total

=======================================================================================

题目描述

---

按照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使用子查询，去叠加上面的数据

select s.emp_no,s.salary,

(select sum(s2.salary) from salaries s2

where s2.emp_no <= s.emp_no and s2.to_date = “9999-01-01”)

as running_total

from salaries s

where to_date = “9999-01-01”;

第二种，使用滑动窗口函数

首先我们来认识一下窗口函数

窗口函数是group by的改进，数据进行了group by之后，会变成一块块，返回的数据相当于就只有一行了，而窗口函数不会合并成一行，会每行都返回出来。

语法如下

<窗口函数> over (partition by <用于分组的列名>

order by <用于排序的列名>)

举个栗子

我这里有一张成绩表

现在对class进行聚合查每个班级的总score

SELECT id,SUM(score) FROM t13 GROUP BY class;

可以看到，这里都变成了一条行数据，不是id列没有使用聚合函数进行处理返回了每一块的第一行

现在我们使滑动窗口函数进行聚合

SELECT id,SUM(score) over (PARTITION BY class) FROM t13;

并没有像group by一样杂糅成一行

如果我们不进行分区会怎么样？

SELECT id,SUM(score) over () FROM t13;

可以看到，如果不进行分区，就默认整个是一个分区

假如我们进行排序而不进行分区又会怎样

SELECT id,SUM(score) over (ORDER BY id) FROM t13;

可以看到，分区的规则是前N个（N代表第几行，比如第一行，那么对应的分区就是前一行，第二行时，对应的分区就是前两行，。。。）

那么我们使用窗口函数的这个性质，就可以得出前N个总和了

题解SQL

SELECT emp_no, salary,

SUM(salary) OVER(ORDER BY emp_no) AS running_total

FROM salaries

WHERE to_date = ‘9999-01-01’;

拓展

下面拓展一些分区专用的函数

1. rank() ：并列排名，继续累加

2. dense_rank：并列排名，不继续累加

3. row_number：不并列排名

举个栗子

我们往原来的表加几条数据，现在表变成了这样

然后我们看看这几个函数的区别

SELECT rank() over (ORDER BY score DESC),

小编13年上海交大毕业，曾经在小公司待过，也去过华为、OPPO等大厂，18年进入阿里一直到现在。

深知大多数初中级Java工程师，想要提升技能，往往是自己摸索成长，但自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！

因此收集整理了一份《2024年最新Java开发全套学习资料》送给大家，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友，同时减轻大家的负担。

由于文件比较大，这里只是将部分目录截图出来，每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频

如果你觉得这些内容对你有帮助，可以添加下面V无偿领取！（备注Java）

最后

整理的这些资料希望对Java开发的朋友们有所参考以及少走弯路，本文的重点是你有没有收获与成长，其余的都不重要，希望读者们能谨记这一点。

其实面试这一块早在第一个说的25大面试专题就全都有的。以上提及的这些全部的面试+学习的各种笔记资料，我这差不多来回搞了三个多月，收集整理真的很不容易，其中还有很多自己的一些知识总结。正是因为很麻烦，所以对以上这些学习复习资料感兴趣，

png#pic_center)

可以看到，分区的规则是前N个（N代表第几行，比如第一行，那么对应的分区就是前一行，第二行时，对应的分区就是前两行，。。。）

那么我们使用窗口函数的这个性质，就可以得出前N个总和了

题解SQL

SELECT emp_no, salary,

SUM(salary) OVER(ORDER BY emp_no) AS running_total

FROM salaries

WHERE to_date = ‘9999-01-01’;

拓展

下面拓展一些分区专用的函数

1. rank() ：并列排名，继续累加

2. dense_rank：并列排名，不继续累加

3. row_number：不并列排名

举个栗子

我们往原来的表加几条数据，现在表变成了这样

然后我们看看这几个函数的区别

SELECT rank() over (ORDER BY score DESC),

小编13年上海交大毕业，曾经在小公司待过，也去过华为、OPPO等大厂，18年进入阿里一直到现在。

深知大多数初中级Java工程师，想要提升技能，往往是自己摸索成长，但自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！

因此收集整理了一份《2024年最新Java开发全套学习资料》送给大家，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友，同时减轻大家的负担。
[外链图片转存中…(img-vYU2i4aw-1710427871221)]
[外链图片转存中…(img-K3yc6ETu-1710427871222)]
[外链图片转存中…(img-5i3QX5l1-1710427871223)]

由于文件比较大，这里只是将部分目录截图出来，每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频

如果你觉得这些内容对你有帮助，可以添加下面V无偿领取！（备注Java）
[外链图片转存中…(img-Per8EQ2k-1710427871223)]

最后

整理的这些资料希望对Java开发的朋友们有所参考以及少走弯路，本文的重点是你有没有收获与成长，其余的都不重要，希望读者们能谨记这一点。

[外链图片转存中…(img-K28GSrd3-1710427871223)]

[外链图片转存中…(img-OMf7KH6N-1710427871224)]

其实面试这一块早在第一个说的25大面试专题就全都有的。以上提及的这些全部的面试+学习的各种笔记资料，我这差不多来回搞了三个多月，收集整理真的很不容易，其中还有很多自己的一些知识总结。正是因为很麻烦，所以对以上这些学习复习资料感兴趣，

本文已被CODING开源项目：【一线大厂Java面试题解析+核心总结学习笔记+最新讲解视频+实战项目源码】收录