oracle 牛客网,牛客网数据库SQL编程笔记

第一题：查找最晚入职员工的所有信息

select * from employees

where hire_date = (select max(hire_date) from employees);

解题思路：利用子查询，找出入职员工里最晚的入职时间，在查询入职最晚的员工的信息。

其实这道题第一时间想到使用 order by 进行排序查找第一条，但这种方法仅限于当最晚入职数据只有一条时才正确，不能保证有多条同一最晚时间时的准确性。

select * from employees

order by hire_date desc

limit 1;

第二题：查找入职员工时间排名倒数第三的员工所有信息

select * from employees

where hire_date = (

select distinct hire_date

from employees

order by hire_date desc

limit 2,1);

解题思路：利用子查询方法，关键点是要用distinct去重，去重后会按入职日期进行排序分组，多个相同入职日期会分为一组，在此基础上再查找员工信息。

在系统里可能用例中没有相同日期入职的员工，所以下面的方法也可以通过，但是这样并不严谨。

select *

from employees

where emp_no in (

select emp_no from employees order by hire_date desc limit 2,1)；

第三题：查找各个部门当前(to_date='9999-01-01')领导当前薪水详情以及其对应部门编号dept_no

select s.*,d.dept_no

from salaries as s,dept_manager as d

where s.to_date='9999-01-01'

and d.to_date='9999-01-01'

and s.emp_no = d.emp_no;

解题思路：将两张表通过emp_no进行连接，并限定查找条件为是当前时间(to_date='9999-01-01')的情况。

第四题：查找所有已经分配部门的员工的last_name和first_name

select employees.last_name,employees.first_name,dept_emp.dept_no

from dept_emp left join employees

on dept_emp.emp_no = employees.emp_no;

解题思路：查找所有已经分配部门的员工,则将dept_emp作为主表，使用左联结，也可使用自然连接，但是要明确dept_emp.dept_no不能为空。

select employees.last_name,employees.first_name,dept_emp.dept_no

from dept_emp inner join employees

on dept_emp.emp_no = employees.emp_no

where dept_emp.dept_no is not null；

第五题：查找所有员工的last_name和first_name以及对应部门编号dept_no，也包括展示没有分配具体部门的员工。

select employees.last_name,employees.first_name,dept_emp.dept_no

from employees left join dept_emp

on dept_emp.emp_no = employees.emp_no;

解题思路：employees作为主表，使用左联结即可。

第六题：查找所有员工入职时候的薪水情况，给出emp_no以及salary， 并按照emp_no进行逆序

select salaries.emp_no,salaries.salary

from employees,salaries

where employees.emp_no=salaries.emp_no

and employees.hire_date=salaries.from_date

order by salaries.emp_no DESC

解题思路：使用内连接或者左连接，条件是salaries表的from_date 和 employees表的hire_date 的值要相等，因此有限制条件employees.hire_date=salaries.from_date，两个date都是刚入职的时间。

第七题：查找薪水涨幅超过15次的员工号emp_no以及其对应的涨幅次数t

select emp_no,count(*) as t

from salaries

group by emp_no having count(emp_no)>15;

解题思路：在系统中，该方法输入可以通过，但并不严谨，个人认为涨幅可包括涨薪和降薪两种，主要问题是刚入职薪水不应该算做涨幅薪水，所以count(emp_no)>16才应该正确。

看到一解法思路：count(*) 为计算全部数据的行数地意思，比较关键的一个点就是联结条件a.to_date = b.from_date，这个条件限定了两个工资之比必须是相邻的，如果没有这个条件，那同一个emp_no下的任意两个salary都可以做对比，可以把这个条件去掉，对比两个查询结果，就明白了。

select a.emp_no,count(*) t

from salaries a inner join salaries b on a.emp_no=b.emp_no and a.to_date = b.from_date

where a.salary < b.salary

group by a.emp_no

having t > 15;

第八题：找出所有员工当前(to_date='9999-01-01')具体的薪水salary情况，对于相同的薪水只显示一次,并按照逆序显示

select distinct salary

from salaries

where to_date='9999-01-01'

order by salary desc;

解题思路：对于相同的薪水只显示一次，使用distinct去重的方法，但是数据较多时使用distinct效率相比group by要慢。

select salary

from salaries

where to_date='9999-01-01'

group by salary

order by salary desc;

第九题：获取所有部门当前manager的当前薪水情况，给出dept_no, emp_no以及salary，当前表示to_date='9999-01-01'

select dept_manager.dept_no,dept_manager.emp_no,salaries.salary

from dept_manager inner join salaries

on dept_manager.emp_no = salaries.emp_no

where salaries.to_date='9999-01-01'

and dept_manager.to_date='9999-01-01';

解题思路：关键点是dept_manager表的to_date和salaries表的to_date都要等于'9999-01-01'，s.to_date = '9999-01-01'限定当前时间当前工资，d.to_date = '9999-01-01'则限定在职经理，有两个“当前”。

第十题：获取所有非manager的员工emp_no

select employees.emp_no

from employees left join dept_manager

on employees.emp_no=dept_manager.emp_no

where dept_manager.dept_no is null；

解题思路：employees作为主表，使用左联结，限定条件为d.dept_no为空，选出在employees但不在dept_manager中的emp_no记录。

第十一题：获取所有员工当前的manager，如果当前的manager是自己的话结果不显示，当前表示to_date='9999-01-01'。结果第一列给出当前员工的emp_no,第二列给出其manager对应的manager_no。

select dept_emp.emp_no,dept_manager.emp_no as manager_no

from dept_emp left join dept_manager

on dept_emp.dept_no = dept_manager.dept_no

where dept_emp.to_date='9999-01-01'

and dept_manager.to_date='9999-01-01'

and dept_emp.emp_no<>dept_manager.emp_no

解题思路：由于每个部门都有manager，每个员工都有对应的部门，所以用部门号dept_no进行左连接，根据题目要求，如果当前的manager是自己的话结果不显示，所以限定条件dept_emp.emp_no与dept_manager.emp_no不相等，且题目要求当前，所以限定员工时间和经理任职时间都是to_date='9999-01-01'。

第十二题：获取所有部门中当前员工薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary

select dept_emp.dept_no,salaries.emp_no,max(salaries.salary) AS salary

from salaries left join dept_emp

on salaries.emp_no = dept_emp.emp_no

where dept_emp.to_date = '9999-01-01' AND salaries.to_date = '9999-01-01'

group by dept_emp.dept_no

解题思路：

①用group by dept_emp.dept_no将每个部门分为一组，用MAX()函数选取每组中工资最高者。

②为获得当前薪水，工资表和部门表的to_date都是'9999-01-01'

第十三题：从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t

select title,count(title)as t

from titles

group by title

having count(title)>=2

解题思路：首先使用group by对tittle进行分组，然后用having计算个数大于等于2的分组即可。

第十四题：从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t。注意对于重复的emp_no进行忽略

select title,count(distinct emp_no)as t

from titles

group by title

having count(title)>=2

解题思路：同上题，主要是使用distinct对emp_no 去重。

第十五题：查找employees表所有emp_no为奇数，且last_name不为Mary的员工信息，并按照hire_date逆序排列

select *

from employees

where last_name !='Mary' and emp_no%2=1

order by hire_date DESC

解题思路：last_name不为Mary，使用 last_name !='Mary' 表示，emp_no为奇数，可使用emp_no取余为1，即emp_no%2=1表示，最后进行排序。

第十六题：统计出当前各个title类型对应的员工当前(to_date='9999-01-01')薪水对应的平均工资。结果给出title以及平均工资avg。

select title,avg(salary) as avg

from titles inner join salaries

on titles.emp_no = salaries.emp_no

where titles.to_date='9999-01-01' and salaries.to_date='9999-01-01'

group by titles.title

解题思路：使用group by对title进行分组，然后限定条件to_date='9999-01-01'，使用平均函数求出平均工资。

第十七题：获取当前(to_date='9999-01-01')薪水第二多的员工的emp_no以及其对应的薪水salary

select emp_no,salary

from salaries

where to_date='9999-01-01'

and salary =(select distinct salary

from salaries

where to_date='9999-01-01'

order by salary desc limit 1,1)

解题思路：首先要找出薪水第二多的员工，通过将薪水从高到底排序，剔除重复的薪水，找出排名第二的薪水额度，在查找该员工的信息。

第十八题：查找当前薪水(to_date='9999-01-01')排名第二多的员工编号emp_no、薪水

salary、last_name以及first_name，不准使用order by

select employees.emp_no,max(salary) as salary,last_name,first_name

from employees inner join salaries

on employees.emp_no = salaries.emp_no

where salaries.to_date='9999-01-01'

and salary not in (select max(salary)

from salaries

where to_date='9999-01-01')

解题思路：将最大的薪水剔除，再求最大的薪水，就是薪水第二多。

第十九题：查找所有员工的last_name和first_name以及对应的dept_name，也包括暂时没有分配部门的员工

select last_name,first_name,dept_name

from (employees left join dept_emp on employees.emp_no=dept_emp.emp_no)

left join departments on dept_emp.dept_no=departments.dept_no

解题思路：解题思路：本题关键在于理清三表之间关联关系。需要使用两次左联结。红色部分可以省略。

①题目要求包括暂时没有分配部门的员工，所以将employees作为主表，使用左联结将其和dept_emp表联结，联结条件为员工编号emp_no，得出所有员工所在部门，并将结果命名为a。

②将查询结果a与departments左联结，联结条件为部门编号dept_no，求出部门名字dept_name。

第二十题：查找员工编号emp_no为10001其自入职以来的薪水salary涨幅值growth

select max(salary)-min(salary) as growth

from salaries

where emp_no='10001'

select(

(select salary from salaries where emp_no=10001 order by to_date desc limit 1)-

(select salary from salaries where emp_no=10001 order by to_date asc limit 1))

as growth

解题思路：对“入职以来的薪水salary涨幅值growth”有两种理解，第一种是员工入职以来工资最大值减去工资最小值，参考方法一。第二种理解是最新一次工资减去入职时的工资，即为涨幅值growth，参考方法二，方法二更严谨。

第二十一题：查找所有员工自入职以来的薪水涨幅情况，给出员工编号emp_no以及其对应的薪水涨幅growth，并按照growth进行升序

select a.emp_no,(b.salary-a.salary) as growth

from

(

select salaries.emp_no,salaries.salary

from employees,salaries

where employees.emp_no=salaries.emp_no

and employees.hire_date=salaries.from_date)

as a,

(

select emp_no,salary

from salaries

where to_date ='9999-01-01') as b

where a.emp_no=b.emp_no

order by growth

解题思路：1.首先将两表通过emp_no进行连接，筛选雇佣时间与薪资时间相等的条件，查找出员工入职时候的薪资

2.通过salaries表查找出当前薪资筛选条件to_date ='9999-01-01'

3.将查找出的a表和b表通过emp_no进行连接，最后对growth进行排序

第二十二题：统计各个部门的工资记录数，给出部门编码dept_no、部门名称dept_name以及次数sum

select dept_emp.dept_no,departments.dept_name,count(salary) as sum

from (dept_emp left join salaries

on dept_emp.emp_no=salaries.emp_no)

left join departments

on dept_emp.dept_no=departments.dept_no

group by dept_emp.dept_no

解题思路：两次连接左连接和内连接都可以，先将dept_emp表和 salaries表进行连接，再与departments进行连接，由于是查找各个部门的工资记录数，所以要记得进行group by分组

第二十三题：对所有员工的当前(to_date='9999-01-01')薪水按照salary进行按照1-N的排名，相同salary并列且按照emp_no升序排列

select emp_no,salary,dense_rank() over (order by salary desc) as rank

from salary

where to_date='9999-01-01'

order by salary desc,emp_no asc

解题思路：使用窗口函数DENSE_RANK() OVER()函数即可将实现薪水按照salary进行按照1-N的排名，相同salary并列。

窗口函数： OVER ([PARITITION BY ] ORDER BY ) [PARITITION BY ]可以省略。

①能够作为窗口函数的聚合函数(sum,avg,count,max,min)

②专用窗口函数(RANK,DENSE_RANK,ROW_NUMBER)

RANK() OVER (ORDER BY )：计算排序，如果存在相同位次的记录，则会跳过之后的位次。如1,1,1,4

DENSE_RANK () OVER (ORDER BY )：计算排序，即使存在相同位次的记录，也不会跳过之后的位次。如1,1,1,2

ROW_NUMBER () OVER (ORDER BY )：计算排序，赋予唯一的连续位次。如：1,2,3,4

select s1.emp_no,s1.salary,count(DISTINCT s2.salary) as rank

from salaries s1,salaries s2

where s1.to_date='9999-01-01'

and s2.to_date='9999-01-01'

and s1.salary<=s2.salary

group by s1.emp_no

order by s1.salary DESC,s1.emp_no ASC

1、从两张相同的salaries表(分别为s1与s2)进行对比分析，先将两表限定条件设为to_date = '9999-01-01'，挑选出当前所有员工的薪水情况。

2、本题的精髓在于 s1.salary <= s2.salary，意思是在输出s1.salary的情况下，有多少个s2.salary大于等于s1.salary，比如当s1.salary=94409时，有3个s2.salary(分别为94692,94409,94409)大于等于它，但由于94409重复，利用COUNT(DISTINCT s2.salary)去重可得工资为94409的rank等于2，其余排名依次类推。

3、由于使用了count函数，所以需要GROUP BY s1.emp_no，否则输出的记录只有一条(可能是第一条或者最后一条，根据不同的数据库而定)

4、最后先以 s1.salary 逆序排列，再以 s1.emp_no 顺序排列输出结果

第二十四题：获取所有非manager员工当前的薪水情况，给出dept_no、emp_no以及salary，当前表示to_date='9999-01-01'

select dept_emp.dept_no,employees.emp_no,salaries.salary

from employees inner join salaries

on employees.emp_no=salaries.emp_no

inner join dept_emp

on employees.emp_no=dept_emp.emp_no

where salaries.to_date='9999-01-01'

and employees.emp_no not in (select emp_no

from dept_manager

where to_date='9999-01-01')

解题思路：

1.将employees表和salaries表进行连接，查找出员工当前薪资

2.再与dept_manager表连接，查找出员工所在部门

3.通过employees表和dept_manager表查找不属于manager员工的员工

4.通过to_date='9999-01-01'对员工和当前领导进行限定

第二十五题：获取员工其当前的薪水比其manager当前薪水还高的相关信息，当前表示to_date='9999-01-01',

结果第一列给出员工的emp_no，第二列给出其manager的manager_no，第三列给出该员工当前的薪水emp_salary,第四列给该员工对应的manager当前的薪水manager_salary

select a.emp_no,b.emp_no as manager_no,a.salary as emp_salary,b.salary as manager_salary

from (

select dept_emp.emp_no,salaries.salary,dept_emp.dept_no

from dept_emp inner join salaries

on dept_emp.emp_no=salaries.emp_no

where dept_emp.to_date='9999-01-01'

and salaries.to_date='9999-01-01') as a,

(

select dept_manager.emp_no,salaries.salary,dept_manager.dept_no

from dept_manager inner join salaries

on dept_manager.emp_no=salaries.emp_no

where dept_manager.to_date='9999-01-01'

and salaries.to_date='9999-01-01') as b

where a.dept_no=b.dept_no and a.salary > b.salary

解题思路：1.连接dept_emp和salaries表，创建部门员工工资表a，

2.连接dept_manager和salaries表，创建部门领导工资表b

3.将两个子查询表a和表b进行连接，找出在同一个部门的manager和员工，限定条件为员工工资大于manager工资。

第二十六题：汇总各个部门当前员工的title类型的分配数目，结果给出部门编号dept_no、dept_name、其当前员工所有的title以及该类型title对应的数目count

select departments.dept_no,departments.dept_name,title,count(title) as count

from departments,dept_emp,titles

where departments.dept_no=dept_emp.dept_no

and dept_emp.emp_no=titles.emp_no

and dept_emp.to_date='9999-01-01'

and titles.to_date='9999-01-01'

group by departments.dept_no,title

解题思路：重点理解各个部门下各个title类型的汇总。

①先以员工编号为联结条件联结titles表和dept_emp表，得到员工部门编号和员工对应的title类型，再以部门编号为联结条件联结departments表，得到部门名称，最后记得限定titles表和dept_emp表的时间为当前to_date ='9999-01-01'。

②汇总的是各个部门下各个title类型，所以要先将部门编号进行一次汇总，再将tltle类型进行汇总，聚合函数count的参数为title。

第二十七题：给出每个员工每年薪水涨幅超过5000的员工编号emp_no、薪水变更开始日期from_date以及薪水涨幅值salary_growth，并按照salary_growth逆序排列。提示：在sqlite中获取datetime时间对应的年份函数为strftime('%Y', to_date)

select s1.emp_no,s1.from_date,(s1.salary-s2.salary) as salary_growth

from salaries s1 inner join salaries s2

on s1.emp_no=s2.emp_no

where salary_growth>5000

and (strftime('%Y',s1.to_date)-strftime('%Y',s2.to_date)=1

or strftime('%Y',s1.from_date)-strftime('%Y',s2.from_date)=1)

order by salary_growth DESC

解题思路：

本题的难点在于如何理解 每年薪水涨幅，以及复用salaries表求出每年薪水涨幅超过5000的员工，具体思路如下：

1、假设s1是涨薪水前的表，s2是涨薪水后的表，因为每个员工涨薪水的时间不全固定，有可能一年涨两次，有可能两年涨一次，所以每年薪水的涨幅，应该理解为两条薪水记录的from_date相同或to_date相同。

/** 如果只限定to_date相同，则将第三条原始测试数据的52668改成62668时，就会少一条【62668-48584=14084】的记录

INSERT INTO salaries VALUES(10008,46671,'1998-03-11','1999-03-11');

INSERT INTO salaries VALUES(10008,48584,'1999-03-11','2000-03-10');

INSERT INTO salaries VALUES(10008, 62668 ,'2000-03-10','2000-07-31'); **/

2、找到s1与s2符合要求的记录后，用s2的薪水减去s1的薪水，用salary_growth表示，加上限定条件 s1.emp_no = s2.emp_no AND salary_growth > 5000，即同一员工每年涨幅超过5000的记录

3、最后依次输出emp_no、from_date、salary_growth，并以salary_growth逆序排列

第二十八题：查找描述信息中包括robot的电影对应的分类名称以及电影数目，而且还需要该分类对应电影数量>=5部

第二十九题：使用join查询方式找出没有分类的电影id以及名称

第三十题：使用子查询的方式找出属于Action分类的所有电影对应的title,description

第三十一题：获取select * from employees对应的执行计划

explain select * from employees

解题思路：explain模拟优化器执行SQL语句，在5.6以及以后的版本中，除过select，其他比如insert，update和delete均可以使用explain查看执行计划，从而知道mysql是如何处理sql语句，分析查询语句或者表结构的性能瓶颈。

作用:https://blog.csdn.net/UFO___/article/details/80951869

第三十二题：将employees表的所有员工的last_name和first_name拼接起来作为Name，中间以一个空格区分

select last_name||' '||first_name as Name

from employees;

select CONCAT(last_name,' ',first_name) as Name

from employees;

解题思路:不同数据库连接字符串的方法不完全相同，MySQL、SQL Server、Oracle等数据库支持CONCAT方法，而本题在网站所用的SQLite数据库只支持用连接符号"||"来连接字符串

第三十三题：创建一个actor表，包含如下列信息

image.png

create table actor(

actor_id smallint(5) not null,

first_name varchar(45) not null,

last_name varchar(45) not null,

last_update timestamp not null default (datetime('now','localtime')),

primary key(actor_id));

解题思路：最关键的两点是创建主键和默认系统当前时间的设置。

第三十四题：对于表actor批量插入如下数据

CREATE TABLE IF NOT EXISTS actor (

actor_id smallint(5) NOT NULL PRIMARY KEY,

first_name varchar(45) NOT NULL,

last_name varchar(45) NOT NULL,

last_update timestamp NOT NULL DEFAULT (datetime('now','localtime')))

image.png

insert into actor (actor_id,first_name,last_name,last_update)

values(1,'PENELOPE','GUINESS','2006-02-15 12:34:33'),

(2,'NICK','WAHLBERG','2006-02-15 12:34:33');

解题思路：插入数据语法：insert into (,....) values (,....);

第三十五题：对于表actor批量插入如下数据,如果数据已经存在，请忽略，不使用replace操作

CREATE TABLE IF NOT EXISTS actor (

actor_id smallint(5) NOT NULL PRIMARY KEY,

first_name varchar(45) NOT NULL,

last_name varchar(45) NOT NULL,

last_update timestamp NOT NULL DEFAULT (datetime('now','localtime')))

image.png

insert or ignore into actor (actor_id,first_name,last_name,last_update)

values(3,'ED','CHASE','2006-02-15 12:34:33')

解题思路:针对牛客网sqlite3环境，需要上方写法，insert into:插入数据,如果主键重复，则报错 ，insert repalce:插入替换数据,如果存在主键或unique数据则替换数据，insert ignore:如果存在数据,则忽略

insert ignore into actor (actor_id,first_name,last_name,last_update)

values(3,'ED','CHASE','2006-02-15 12:34:33')

解题思路：针对mysql数据库，如果数据存在则忽略，在insert into之间加个ignore就可以

第三十六题：对于如下表actor，其对应的数据为:

image.png

创建一个actor_name表，将actor表中的所有first_name以及last_name导入改表。 actor_name表结构如下

image.png

create table actor_name as

select first_name,last_name

from actor

解题思路：不同数据库语法不同，在Mysql中，可以加as，也可以不加as,但是针对牛客网sqlite3环境，需要加as 才能通过。

第三十七题：针对如下表actor结构创建索引：

CREATE TABLE IF NOT EXISTS actor (

actor_id smallint(5) NOT NULL PRIMARY KEY,

first_name varchar(45) NOT NULL,

last_name varchar(45) NOT NULL,

last_update timestamp NOT NULL DEFAULT (datetime('now','localtime')))

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

create unique index uniq_idx_firstname on actor(first_name);

create index idx_lastname on actor(last_name);

解题思路：创建唯一索引：CREATE UNIQUE INDEX ON ();

创建一个简单的索引：CREATE INDEX ON ();

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

CREATE TABLE IF NOT EXISTS actor (

actor_id smallint(5) NOT NULL PRIMARY KEY,

first_name varchar(45) NOT NULL,

last_name varchar(45) NOT NULL,

last_update timestamp NOT NULL DEFAULT (datetime('now','localtime')))

create view actor_name_view as

select first_name as first_name_v,last_name as last_name_v

from actor

解题思路一： CREATE VIEW ... AS ... 的 AS 是创建视图语法中的一部分，而后面的两个 AS 只是为字段创建别名

CREATE VIEW actor_name_view (fist_name_v, last_name_v) AS

SELECT first_name, last_name FROM actor

解题思路二：创建视图语句：CREATE VIEW (,…)AS；

第三十九题：针对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

indexed by idx_emp_no

where emp_no=10005

解题思路：针对牛客网sqlite3则需使用 INDEXED BY 语句进行强制索引查询

select *

from salaries

force index idx_emp_no

where emp_no=10005

解题思路：针对mysql数据库则强制索引：FORCE INDEX();

SELECT * FROM FORCE INDEX ()

第四十题：存在actor表，包含如下列信息：

CREATE TABLE IF NOT EXISTS actor (

actor_id smallint(5) NOT NULL PRIMARY KEY,

first_name varchar(45) NOT NULL,

last_name varchar(45) NOT NULL,

last_update time stamp NOT NULL DEFAULT (datetime('now','localtime')));

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

alter table actor

add column create_date datetime NOT NULL default '0000-00-00 00:00:00'

解题思路：添加列是使用变更表定义的ALTER TABLE语句，ALTER TABLE ADD COLUMN ，其中COLUMN可省略。

第四十一题：构造一个触发器audit_log，在向employees_test表中插入一条数据的时候，触发插入相关的数据到audit表中

CREATE TRIGGER audit_log AFTER INSERT ON employees_test

BEGIN

INSERT INTO audit VALUES(NEW.ID,NEW.NAME);

END;

解题思路：

CREATE TRIGGER

ON FOR EACH ROW ;

1.创建触发器使用语句：CREATE TRIGGER trigname;

2.指定触发器触发的时机：可以选 BEFORE或者AFTER

3.触发器触发事件：[INSERT/UPDATE/ADD] ON tablename

4.触发器触发事件后需要执行的语句,写在BEGIN和END之间；

5.触发器中可以通过NEW获得触发事件之后2对应的tablename的相关列的值，OLD获得触发事件之前的2对应的tablename的相关列的值

audit表里只有emp_no和name两列，所以只能插入id和name这两列，我们可以使用 NEW与OLD 关键字访问触发后(或触发前)的employees_test表单记录。

第四十二题：删除emp_no重复的记录，只保留最小的id对应的记录

delete from titles_test

where id not in (

select min(id)

from titles_test

group by emp_no);

解题思路：先用group by和 min() 选出每个 emp_no 分组中最小的 id，然后用 delete from ... where ... not in ... 删除 “非每个分组最小id对应的所有记录”。

第四十三题：将所有to_date为9999-01-01的全部更新为NULL,且 from_date更新为2001-01-01。

update titles_test

set to_date = null, from_date='2001-01-01'

where to_date='9999-01-01'

解题思路：

①使用update语句更新，多个更新之间用逗号隔开，而不能使用and连接。

②这里两个更新分别考察了简单update语句和搜索型update语句。

③一个比较容易出错的地方是某列更新为null时不能使用 is null的方法，正确的方法是：update set = null where 。

第四十四题：将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005,其他数据保持不变，使用replace实现

replace into titles_test values (5, 10005, 'Senior Engineer', '1986-06-26', '9999-01-01')

解题思路一：全字段更新替换，replace into 跟 insert (update同理)功能类似，不同点在于：replace into 首先尝试插入数据到表中， 1. 如果发现表中已经有此行数据(根据主键或者唯一索引判断)则先删除此行数据，然后插入新的数据。 否则，直接插入新数据。要注意的是：插入数据的表必须有主键或者是唯一索引！否则的话，replace into 会直接插入数据，这将导致表中出现重复的数据。

MySQL replace into 有三种形式：

replace () values(...)

replace () select ...

replace set =value, ...

前两种形式用的多些。其中 “into” 关键字可以省略，不过最好加上 “into”，这样意思更加直观。

update titles_test

set emp_no = replace(emp_no,10001,10005)

where id = 5

解题思路二：

运用REPLACE(X,Y,Z)函数。其中X是要处理的字符串，Y是X中将要被替换的字符串，Z是用来替换Y的字符串，最终返回替换后的字符串。以下语句用 UPDATE和REPLACE 配合完成，用REPLACE函数替换后的新值复制给 id=5 的 emp_no。REPLACE的参数为整型时也可通过，但是这个语句在Mysql中不能实现。

第四十五题：将titles_test表名修改为titles_2017

rename table titles_test to titles_2017;

alter table titles_test rename to titles_2017;

解题思路：变更表名：rename table to ;两个方法在mysql控制台中都可以成功，而在牛客中只有第二个可以。

第四十六题：在audit表上创建外键约束，其emp_no对应employees_test表的主键id

drop table audit;

create table audit(

EMP_no INT NOT NULL,

create_date datetime NOT NULL,

foreign key(EMP_no) references employees_test(ID));

解题思路:先删除表，然后再建立该表，在表中直接进行外键约束；

alter table audit add foreign key(emp_no) references employees_test(ID);

解题思路mysql：创建外键约束语法：alter table add foreign key(子表的外键字段) references (父表的主键名称);

第四十七题：存在如下的视图：create view emp_v as select * from employees where emp_no >10005;

如何获取emp_v和employees有相同的数据，输出格式

image.png

select * from emp_v

select ev.* from employees as em,emp_v as ev

where em.emp_no=ev.emp_no

select * from employees

intersect

select * from emp_v

解题思路：

方法一：emp_v的全部记录均由 employees 导出，因此可以投机取巧，直接输出 emp_v 所有记录；

方法二：用 WHERE 选取二者 emp_no 相等的记录；

方法三：用 INTERSECT 关键字求 employees 和 emp_v 的交集

第四十八题：将所有获取奖金的员工当前的薪水增加10%。

update salaries set salary=salary*1.1

where emp_no in (

select emp_no from emp_bonus

) and to_date='9999-01-01'

解题思路：使用update更新表工资，限定条件是获得奖金的员工的当前薪水。

第四十九题：针对库中的所有表生成select count(*)对应的SQL语句

image.png

SELECT "select count(*) from " || name || ";" AS cnts

FROM sqlite_master WHERE type = 'table'

解题思路：SQLite数据库中一个特殊的名叫 SQLITE_MASTER 上执行一个SELECT查询以获得所有表的索引。每一个 SQLite 数据库都有一个叫 SQLITE_MASTER 的表， 它定义数据库的模式。对于表来说，type 字段永远是 ‘table’，name 字段永远是表的名字。

mysql答案参考：

在Mysql中，针对库中的所有表生成select count()对应的SQL语句：

select concat("select count() from ",table_name,";") as cnts

from information_schema.tables where table_schema='niuke'; (niuke是我做牛客sql题专门建立的一个数据库)

在Mysql中，针对所有数据库中的所有表生成select count()对应的SQL语句：

select concat("select count() from ",table_name,";") as cnts

from (select table_name from information_schema.tables) as new;

MySQL中获得所有表的名称：

select table name

from information schema.tables where table schema='mydata';

MySQL语句中，‘mydata’是当前数据库的名称的意思

information_schema.tables表示从表名中选择，information_schema.xxx中xxx可选的还有很多字段，如information_schema.columns表示从所有表的所有字段中选择。

MySQL字符串的连接使用concat函数，多个字符串连接中间用逗号隔开。另外，结果中from和表名之间是有一个空格的，所以在字符串"select count(*) from "的from后要加一个空格。

第五十题：将employees表中的所有员工的last_name和first_name通过(')连接起来。

SELECT last_name || "'" || first_name FROM employees

解题思路：SQLite数据库中，只支持用连接符号"||"来连接字符串，不支持用函数连接

select concat(last_name,"'",first_name) as name

from employees

解题思路：MySQL中可用函数进行字符串连接。

第五十一题：查找字符串'10,A,B' 中逗号','出现的次数cnt。

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

解题思路：使用length函数算出字符串长度，在使用replace函数将“，”替换为空，计算长度

第五十二题：获取Employees中的first_name，查询按照first_name最后两个字母，按照升序进行排列

select first_name

from employees

order by (substr(first_name,length(first_name)-1))

解题思路：在牛客系统中，需使用函数substr(X,Y,Z) 或 substr(X,Y) ，其中X是要截取的字符串，Y是字符串的起始位置(注意第一个字符的位置为1，而不为0)，取值范围是±(1~length(X))，当Y等于length(X)时，则截取最后一个字符；当Y等于负整数-n时，则从倒数第n个字符处截取。Z是要截取字符串的长度，取值范围是正整数，若Z省略，则从Y处一直截取到字符串末尾；若Z大于剩下的字符串长度，也是截取到字符串末尾为止。

select first_name

from employees

order by substring(first_name, (length(first_name)-1), 2) asc;

解题思路：针对mysql数据库使用字符串截取函数substring(对象字符串,截取的起始位置,截取的字符数)，截取出最后两位字符，然后使用order by …asc进行升序排序。

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

select dept_no,group_concat(emp_no,',') as employees

from dept_emp

group by dept_no

解题思路：使用聚合函数group_concat(X,Y)，其中X是要连接的字段，Y是连接时用的符号，默认为逗号，可省略，此函数必须与 GROUP BY 配合使用，先对dept_no 进行分组，在使用group_concat函数进行聚合。

select dept_no,group_concat(emp_no separator ',') as employees

from dept_emp

group by dept_no;

解题思路：

①连接同一列字段：group_concat( [distinct] [order by 排序字段 asc/desc ] [separator '分隔符'] ) 。分隔符可以选择省略，省略时默认为逗号，这里还是写出来了。另外还有一点需要注意，group_concat函数中的各个参数之间用空格隔开，不能用逗号隔开，不然会出错。

②按照dept_no进行汇总，所以要对dept_no进行分组

第五十四题：查找排除当前最大、最小salary之后的员工的平均工资avg_salary。

select avg(salary) as avg_salary

from salaries

where salary not in (

select min(salary) from salaries where to_date='9999-01-01')

and salary not in (

select max(salary) from salaries where to_date='9999-01-01')

and to_date='9999-01-01'

解题思路：查找出当前工资最大和最小值后，求取当前工资的平均值。

第五十五题：分页查询employees表，每5行一页，返回第2页的数据

select * from employees limit 5 offset 5;

解题思路：利用 LIMIT 和 OFFSET 关键字。LIMIT 后的数字代表返回几条记录，OFFSET 后的数字代表从第几条记录开始返回(第一条记录序号为0)，也可理解为跳过多少条记录后开始返回。

select * from employees limit 5,5;

解题思路：只利用 LIMIT 关键字。注意：在 LIMIT X,Y 中，Y代表返回几条记录，X代表从第几条记录开始返回(第一条记录序号为0)。

第五十六题：获取所有员工的emp_no、部门编号dept_no以及对应的bonus类型btype和received ，没有分配具体的员工不显示

select dept_emp.emp_no,dept_no,btype,recevied

from dept_emp left join emp_bonus

on dept_emp.emp_no=emp_bonus.emp_no

解题思路：由于dept_emp表中都是已分配部门的员工，因此只用 dept_emp表与emp_bonus表左连接即可解决。

第五十七题：使用含有关键字exists查找未分配具体部门的员工的所有信息

select employees.*

from employees

where not exists(

select emp_no

from dept_emp

where dept_emp.emp_no=employees.emp_no)

解题思路：谓词exists的作用是“判断是否存在满足某些条件的记录”，如果存在这样满足条件的记录，返回真，不存在，返回假。因此首先找出已经分配部门的员工，再加上not进行否定即可。

第五十八题：存在如下的视图：

create view emp_v as select * from employees where emp_no >10005;

获取employees中的行数据，且这些行也存在于emp_v中。注意不能使用intersect关键字。

select * from emp_v

解题思路：由于emp_v的全部记录均由 employees 导出，可直接输出 emp_v 所有记录

select e.* from employees e, emp_v ev where e.emp_no = ev.emp_no;

解题思路：用 WHERE 选取二者 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'。

select emp_bonus.emp_no,first_name,last_name,btype,salary,(salary*btype*0.1) as bonus

from emp_bonus left join employees

on emp_bonus.emp_no=employees.emp_no

left join salaries

on emp_bonus.emp_no=salaries.emp_no

where to_date='9999-01-01'

select eb.emp_no,e.first_name,e.last_name,eb.btype,s.salary,

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

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

else s.salary * 0.3 end) as bonus

from salaries s,emp_bonus eb,employees e

where eb.emp_no = s.emp_no

and eb.emp_no = e.emp_no

and s.to_date = '9999-01-01';

解题思路：①使用case when表达式对奖金进行行列转换，这里使用的是case when then …的搜索case表达式，最后要记得使用end结束case，并将结果重命名为bonus。

②使用员工编号为限定条件将三表联结查询，还有限定时间为当前。

第六十题：按照salary的累计和running_total，其中running_total为前两个员工的salary累计和，其他以此类推。

select emp_no,salary,

sum(salary) over(order by emp_no) as running_total

from salaries

where to_date= '9999-01-01';

解题思路：

①本题关键在于把sum聚合函数作为窗口函数使用，所有聚合函数都能用做窗口函数，其语法和专用窗口函数完全相同。

sum() over() as 别名；

②running_total列是逐个员工的工资的累计和，每一行的工资都是前面所有行的工资总计。

③限定时间为当前'9999-01-01'才能符合输出格式。

不使用窗口函数的方法：

select s1.emp_no, s1.salary,

(select sum(s2.salary)

from salaries s2

where s2.emp_no <= s1.emp_no

and s2.to_date = '9999-01-01') as running_total

from salaries s1

where s1.to_date = '9999-01-01'

order by s1.emp_no;

解题思路：利用复表查询，以及running_total等于逐个员工的工资的累计和，即找出在表2中小于等于表1员工编号的所有员工工资进行求和，最后记得限定时间为当前，且要按照emp_no升序排序。

第六十一题：对于employees表中，给出奇数行的first_name。

select a.first_name

from (select emp_no, first_name, row_number() over(order by first_name) as row_num

from employees) a

where row_num % 2 = 1

order by emp_no;

解题思路：①窗口函数row_number的作用是赋予唯一的连续位次。巧用窗口函数row_number对数据进行行排序，对first_name进行排序，将得到的位次命名为row_num。②用求余函数找出奇数行。

select e1.first_name from

(select e2.first_name,

(select count(*) from employees as e3

where e3.first_name <= e2.first_name)

as rowid from employees as e2) as e1

where e1.rowid % 2 = 1

解题思路：

1、本题用到了三层 select 查询，为了便于理解，采用缩进方式分层，且最外层对应e1，最内层对应e3；

2、在e3层中，采用 count() 函数对 e2.first_name 进行排名标号，即在给定 e2.first_name的情况下，不大于 e2.first_name 的 e3.first_name 的个数有多少，该个数刚好与 e2.first_name 的排名标号匹配，且将该值命名为 rowid；**

/注意：排名标号后并未排序，即[Bob, Carter, Amy]的排名是[2,3,1]，选取奇数排名后输出[Carter, Amy]，所以可见参考答案中的first_name并未按字母大小排序/**

3、在e1层中，直接在限定条件 e1.rowid % 2 = 1 下，代表奇数行的 rowid，选取对应的 e1.first_name；

4、e2层则相当于连接e1层(选取表示层)与e3层(标号层)的桥梁。