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
总结
当一个表记录着某一属性的变动,可以使用自身联结的方式找到每一段的变动(前提是该表有两个属性记录着某行
=======================================================================================
有一个薪水表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);
================================================================================
题目已经先执行了如下语句:
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,只包含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对应的记录。
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 //注意要给子查询表加别名
)
==========================================================================
将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);
================================================================================
查找字符串 10,A,B 中逗号,出现的次数cnt
让原本字符串的长度减去去掉逗号后字符串的长度即可
length(str) //获取原字符串长度
replace(str,“,”,“”) //使用replace去掉字符串里面的逗号
解题SQL
select (length(“10,A,B”) - length(replace(“10,A,B”,“,”,“”))) as cnt;
===================================================================================
获取Employees中的first_name,查询按照first_name最后两个字母,按照升序进行排列
使用right和left来指定order by的比较方法
//指定只比较后面右边两个字符,也就是最后面2个
select first_name from employees order by right(first_name,2);
====================================================================
按照dept_no进行汇总,属于同一个部门的emp_no按照逗号进行连接,结果给出dept_no以及连接出的结果employees
使用group_concat来进行组的拼接,group_concat默认的就是使用逗号来连接
select group_concat(emp_no)
from dept_no
group by emp_no
===========================================================================
使用含有关键字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,其中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’;
拓展
下面拓展一些分区专用的函数
-
rank() :并列排名,继续累加
-
dense_rank:并列排名,不继续累加
-
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’;
拓展
下面拓展一些分区专用的函数
-
rank() :并列排名,继续累加
-
dense_rank:并列排名,不继续累加
-
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大面试专题就全都有的。以上提及的这些全部的面试+学习的各种笔记资料,我这差不多来回搞了三个多月,收集整理真的很不容易,其中还有很多自己的一些知识总结。正是因为很麻烦,所以对以上这些学习复习资料感兴趣,