一、命令汇总
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 REPLACE 来插入记录,或更新替代与表内UNIQUE字段都相同的记录
1 |
|
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;