牛客网SQL实战～持续更新

一、命令汇总

except集合差，union集合并，intersect集合交

inner join返回的是两个表完全匹配的信息，left join返回左表全部信息和右表存在的信息，right join返回右表全部信息和左表存在的信息，full join是left join和right join的并集

二、题目练习

1、查找最晚入职员工的所有信息

正确做法

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

一个不完整的做法：如果最晚入职的存在几个日期一致的情况，就会出错，此处可以加一个distinct来改进，看第2题

select * from employees order by hire_date desc limit 1;

2、 查找入职员工时间排名倒数第三的员工所有信息

select * from employees where hire_date=(select distinct hire_date from employees order by hire_date desc limit 2,1);

3、查找当前薪水详情以及部门编号dept_no

select s.*,d.dept_no from salaries s,dept_manager d where 
s.to_date='9999-01-01' and d.to_date='9999-01-01'
and s.emp_no=d.emp_no;

4、查找所有已经分配部门的员工的last_name和first_name

select e.last_name,e.first_name,d.dept_no from dept_emp d,employees e where
e.emp_no=d.emp_no;

5、查找所有员工的last_name和first_name以及对应部门编号dept_no

关于left_join的使用：left_join关键字会从左表中返回所有的行，如果右表没有匹配的行，可以输出空

select e.last_name,e.first_name,d.dept_no from employees e
left join dept_emp d
on e.emp_no=d.emp_no;

6、查找所有员工入职时候的薪水情况

select e.emp_no,s.salary from employees e,salaries s where e.emp_no=s.emp_no 
and e.hire_date=s.from_date
order by e.emp_no desc;

7、查找薪水涨幅超过15次的员工号emp_no以及对用的涨幅次数t：

有一个问题，万一薪水不是一直涨呢？

select emp_no,count(emp_no) as t from salaries
group by emp_no
having t>15;

8、找出所有员工当前薪水salary情况

select salary from salaries
where to_date='9999-01-01'
group by salary
order by salary desc;

9、获取所有部门当前manager的薪水情况，给出dept_no,emp_no以及salary，当前表示to_date='999-01-01'

select d.dept_no,d.emp_no,s.salary from dept_manager d,salaries s
where d.emp_no=s.emp_no
and d.to_date=s.to_date
and d.to_date='9999-01-01';

10、获取所有非manager的员工emp_no

#1、left join 方法
select e.emp_no from employees e
left join dept_manager d
on e.emp_no=d.emp_no
where d.emp_no is null;

#2、NOT IN 方法
select emp_no from employees
where emp_no not in (select emp_no from dept_manager);

#3、集合求差方法,except集合差，union集合并，intersect集合交
select emp_no from employees
EXCEPT
select emp_no from dept_manager;

11、获取所有员工当前的manager

select e.emp_no,d.emp_no as manager_no from dept_emp e,dept_manager d
where e.dept_no=d.dept_no
and d.to_date='9999-01-01'
except
select e.emp_no,d.emp_no as manager_no from dept_emp e,dept_manager d
where e.dept_no=d.dept_no
and e.emp_no=d.emp_no;

12、获取所有部门当中当前员工薪水最高的相关信息

select d.dept_no,d.emp_no,max(s.salary) as salary
from dept_emp d,salaries s
where d.to_date='9999-01-01'
and s.to_date='9999-01-01'
and d.emp_no=s.emp_no
group by d.dept_no;

13、从titles表获取按照title进行分组

select title,count(title) as t
from titles
group by title
having t>1;

14、从titles表获取按照title进行分组，注意对于重复的emp_no进行忽略

select title,count(distinct emp_no) t
from titles
group by title
having t>1;

15、查找employees表

select * from employees
where emp_no%2=1
and last_name!='Mary'
order by hire_date desc;

16、统计出当前各个title类型对应的员工当前薪水对应的平均工资，结果给出title以及平均工资avg

select t.title,avg(s.salary) as avg
from salaries s,titles t
where t.emp_no=s.emp_no
and s.to_date='9999-01-01'
and t.to_date='9999-01-01'
group by t.title;

使用inner join 

select t.title,avg(s.salary)
from salaries s
inner join 
titles t
on
t.emp_no=s.emp_no
and s.to_date='9999-01-01'
and t.to_date='9999-01-01'
group by t.title;

17、获取当前薪水第二多的员工的emp_no以及其对用的薪水salary

select emp_no,salary from salaries
where salary=(select distinct salary from salaries order by salary desc limit 1,1)
and to_date='9999-01-01';

18、获取当前薪水第二多的员工的emp_no以及其对应的薪水salary，不准使用order by

select e.emp_no,max(s.salary),e.last_name,e.first_name
from employees e
inner join salaries s
on e.emp_no=s.emp_no
where to_date='9999-01-01'
and salary not in (select max(salary) from salaries where to_date='9999-01-01');

19、查找所有员工的last_name和first_name以及对应的dept_name

select e.last_name,e.first_name,t.dept_name
from employees e
left join dept_emp d
on e.emp_no=d.emp_no
left join departments t
on t.dept_no=d.dept_no;

20、查找员工编号emp_no为1001其自入职以来的薪水salary涨幅值growth

select (max(salary)-min(salary)) as growth
from salaries
where emp_no='10001';

 21、查找所有员工自入职以来的薪水涨幅情况

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

22、统计各个部门对应员工涨幅的次数总和

select d.dept_no,de.dept_name,count(s.salary) as sum
from salaries s
inner join dept_emp d
on s.emp_no=d.emp_no
inner join departments de
on d.dept_no=de.dept_no
group by d.dept_no;

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

select s1.emp_no,s1.salary,count(distinct s2.salary) rank
from salaries s1,salaries s2
where s1.salary<=s2.salary and s1.to_date='9999-01-01' and s2.to_date='9999-01-01'
group by s1.emp_no order by rank;

24、获取所有非manager员工当前的薪水情况?

select de.dept_no,e.emp_no,s.salary
from employees e
inner join salaries s
on e.emp_no=s.emp_no
and s.to_date='9999-01-01'
inner join dept_emp de
on e.emp_no=de.emp_no
and de.to_date='9999-01-01'
where de.emp_no not in(select emp_no from dept_manager);

25、获取员工其当前的薪水比其manager当前薪水还高的相关信息?

select s1.emp_no as emp_no,s2.emp_no as manager_no,s1.salary as emp_salary,s2.salary as manager_salary
from
(select s.emp_no,de.dept_no,s.salary from salaries s inner join
dept_emp de on de.emp_no=s.emp_no and s.to_date='9999-01-01') as s1,
(select s.emp_no,dm.dept_no,s.salary from salaries s inner join
dept_manager dm on dm.emp_no=s.emp_no and s.to_date='9999-01-01') as s2
where s1.dept_no=s2.dept_no
and s1.salary>s2.salary;

26、汇总各个部门当前员工的title类型的分配数目?

select de.dept_no,dm.dept_name,t.title,count(t.emp_no) as count
from titles t
inner join dept_emp de
on de.emp_no=t.emp_no
and de.to_date='9999-01-01'
and t.to_date='9999-01-01'
inner join departments dm
on de.dept_no=dm.dept_no
group by de.dept_no,t.title;

27、给出每个员工每年薪水涨幅超过5000的员工编号emp_no?

select s2.emp_no,s2.from_date,(s2.salary-s1.salary) as salary_growth
from salaries s1,salaries s2
where s1.emp_no=s2.emp_no
and s2.salary-s1.salary>5000
and strftime('%Y',s2.to_date)-strftime('%Y',s1.to_date)=1
order by salary_growth desc;

28、查找描述信息中包括robot的电影对应的分类名称以及电影数目，而且还需要该分类对应电影数?

select c.name,count(fcc.film_id) as num
from film_category fc
inner join category c
on c.category_id=fc.category_id
inner join (select * from film f where f.description like '%robot%') as f
on f.film_id=fc.film_id
inner join (select *,count(fc.film_id) as num from film_category fc group by category_id having num>=5) as fcc
on fcc.category_id=fc.category_id;

29、使用join查询方式找出没有分类的电影id以及名称

select f.film_id,f.title
from film f
inner join film ff
on f.film_id=ff.film_id
and ff.film_id not in (select film_id from film_category);

30、使用子查询的方式找出属于Action分类的所有电影对应的title，descripthon?

select title,description
from film
where film_id in (select film_id from film_category 
where category_id =(select category_id from category where name='Action'));

31、获取select * from employees对应的执行计划

explain select * from employees;

32、将employees表的所有员工的last_name,first_name拼接起来作为Name?

mysql中支持使用concat或者A+B的模式，但是SQLite只能用||

select last_name||' '||first_name as Name
from employees;

33、创建一个actor表?

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

34、批量插入数据

insert into actor
values
(1,'PENELOPE','GUINESS','2006-02-15 12:34:33'),
(2,'NICK','WAHLBERG','2006-02-15 12:34:33');

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

在 SQLite 中，用 INSERT OR IGNORE 来插入记录，或忽略插入与表内UNIQUE字段都相同的记录

1	INSERT OR IGNORE INTO actor VALUES (3, 'ED', 'CHASE', '2006-02-15 12:34:33')

用 INSERT OR REPLACE 来插入记录，或更新替代与表内UNIQUE字段都相同的记录

1	INSERT OR REPLACE INTO actor VALUES (3, 'ED', 'CHASE', '2006-02-15 12:34:33')

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

36、创建一个actor_name表

create table actor_name
as select first_name,last_name from actor;

create table actor_name(
first_name varchar(45) not null,
last_name varchar(45) not null);
insert into actor_name select first_name,last_name from actor;

37、对first_name创建唯一索引uniq_idx_firstname?

create unique index uniq_idx_firstname on actor(first_name);
create index idx_lastname on actor(last_name);

38、针对actor表创建视图actor_name_view?

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

39、针对上面的salaries表emp_np字段创建索引idx_emp_no?

select * from salaries indexed by idx_emp_no where emp_no='10005';

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

使用alter add可以往数据库里面加一列

alter table actor add column create_date 
datetime not null default '0000-00-00 00:00:00';

41、构造一个触发器audit_log?

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

2.指定触发器触发的事件在执行某操作之前还是之后，使用语句：BEFORE/AFTER [INSERT/UPDATE/ADD] ON tablename

3.触发器触发的事件写在BEGIN和END之间；

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

注意分号

create trigger audit_log after insert on employees_test
begin
    insert into audit values(NEW.ID,NEW.NAME);
end;

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

注意，如果使用not in，那么后面的范围必须带有括号

delete from titles_test
where id not in
(select min(id) from titles_test group by title);

43、将所有to_date为9999-01-01的全部更新为NULL

update titles_test set to_date=NULL,from_date='2001-01-01' where to_date='9999-01-01';

44、将id=5以及emp_no=1001的行数据替换成id=5以及emp_no=10005

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

45、将titlle_test表改名为title_2017

alter table titles_test rename to titles_2017;