牛客网sql实战（三）

28.查找描述信息中包括robot的电影对应的分类名称以及电影数目，而且还需要该分类对应电影数量>=5部
不是很明白？
根据题意，需要有四张表

• 题目所给的三张表：film,category,film_category
• 另外需要一张每个分类对应的电影数量>=5的电影分类表

select c.name,count(*) from 
(select category_id from film_category
group by category_id having count(category_id)>=5) as a,
film as b,category as c,film_category as d
where b.film_id=d.film_id
and c.category_id=d.category_id
and a.category_id=d.category_id
and b.description like '%robot%';

29.使用join查询方式找出没有分类的电影id以及名称
本题不需要用到category表，将film表左连接film_category,再筛选出category_id为null的记录，即为没有分类的电影

select f.film_id,f.title from film as f left join film_category as fc
on f.film_id=fc.film_id where fc.category_id is null

30.使用子查询的方式找出属于Action分类的所有电影对应的title,description

• 第一步：找到所有属于action分类的电影id
• 第二步：根据得到的电影id得到对应的电影信息

-- 根据得到的电影id得到对应的电影信息
select title,description from film where film_id in(
-- 找到所有属于action分类的电影id
select fc.film_id from film_category as fc inner join category c on fc.category_id=c.category_id
where c.name='Action')

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

explain select * from employees;

32.将employees表的所有员工的last_name和first_name拼接起来作为Name，中间以一个空格区分
sqllite连接字符串的写法：

select last_name||" "||first_name as name  from employees

mysql下可以用concat:

select concat(last_name,' ',first_name) as name from employees;

33.创建一个actor表，包含如下列信息
注意：DEFAULT (datetime('now','localtime')) 设置默认值为系统的当前时间

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.对于表actor批量插入如下数据

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

35.对于表actor批量插入如下数据,如果数据已经存在，请忽略，不使用replace操作
sqllite3的做法：

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

mysql的做法：

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

36.创建一个actor_name表，将actor表中的所有first_name以及last_name导入改表

create table actor_name as select first_name,last_name from actor;

37.针对如下表actor结构创建索引：
对first_name创建唯一索引uniq_idx_firstname，对last_name创建普通索引idx_lastname

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 unique index uniq_idx_firstname on actor(first_name);
create index idx_lastname on actor(last_name);

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

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

方法二：

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

知识点补充：

什么是视图？
视图是虚拟的表，与包含数据的表不一样，视图只包含使用时动态检索数据的查询
视图仅仅是用来查看存储在别处的数据的一种设施，本身不包含数据，因此返回的数据是从其他表中检索出来的，在添加或更改这些表中的数据时，视图将返回改变过的数据
视图的使用
create view 用于 创建视图
show create view viewname 查看创建视图的语句
drop view viewname删除视图
更新视图：方式1：先drop再create;方式2：create or replace view

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

sqllite的解法：

SELECT * FROM salaries INDEXED BY idx_emp_no WHERE emp_no = 10005

mysql的解法：

SELECT * FROM salaries FORCE INDEX (idx_emp_no) WHERE emp_no = 10005

存在actor表，包含如下列信息：
现在在last_update后面新增加一列名字为create_date, 类型为datetime, NOT NULL，默认值为'0000-00-00 00:00:00'

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

解法：

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

注意：修改表中的记录使用update，更改表中的结构使用alter
40.构造一个触发器audit_log，在向employees_test表中插入一条数据的时候，触发插入相关的数据到audit中

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

知识点补充：
什么是触发器？

在某个表发生更改时自动处理，是mysql响应以下任意语句而自动执行的一条mysql语句

• delete
• insert
• update

创建触发器的注意事项：

• 唯一的触发器名
• 触发器关联的表
• 触发器应该响应的活动（delete、insert或update）
• 触发器何时执行
  41.删除emp_no重复的记录，只保留最小的id对应的记录

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

42.将所有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'

43.将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005,其他数据保持不变，使用replace实现。

update titles_test set emp_no=replace(emp_no,10001,10005) where id=5;

44.将titles_test表名修改为titles_2017

-- rename table titles_test to titles_2017;
ALTER TABLE titles_test RENAME TO titles_2017

45.在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));

46.存在如下的视图：
create view emp_v as select * from employees where emp_no >10005;
如何获取emp_v和employees有相同的数据

select b.* from emp_v as a,employees as b where a.emp_no=b.emp_no

注意：需要在前加上表的显示，不然同时得到两张表的重复记录*
47.将所有获取奖金的员工当前的薪水增加10%。

update salaries set salary=salary*1.1 where emp_no in (
    select a.emp_no from emp_bonus as a inner join salaries as b on 
a.emp_no=b.emp_no and b.to_date='9999-01-01')

48.针对库中的所有表生成select count()对应的SQL语句*
sqlite写法：

select "select count(*) from "||name||";" as cnts
    from sqlite_master 
    where type='table';

mysql写法：

select concat("select count(*) from ",table_name,";") as cnts
from (select table_name from information_schema.columns) as new

49.将employees表中的所有员工的last_name和first_name通过(')连接起来
sqlite写法：

select last_name||"'"||first_name from employees;

mysql写法：

select concat(last_name,"'",first_name) from employees;

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

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

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

• substr语法：参数一：目标串；参数二：+表示从左往右数，-表示从右往左数；参数三：截取的长度

select first_name from employees order by substr(first_name,-2,2)

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

select dept_no,group_concat(emp_no,',') from dept_emp
group by dept_no

53.查找排除当前最大、最小salary之后的员工的平均工资avg_salary

select avg(salary) as avg_salary from salaries where to_date='9999-01-01'
and salary<>(select min(salary) from salaries where to_date='9999-01-01')
and salary<>(select max(salary) from salaries where to_date='9999-01-01');

54.分页查询employees表，每5行一页，返回第2页的数据

select * from employees limit 5,5

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

• 先得到分配了部门的员工信息与部门信息的表，使用内连接
• 在将上表与bonus表进行左连接，没有bonus信息的地方不显示

select a.emp_no,b.dept_no,c.btype,c.recevied 
from employees as a 
inner join dept_emp as b on a.emp_no=b.emp_no
left join emp_bonus as c on a.emp_no=c.emp_no;

56.使用含有关键字exists查找未分配具体部门的员工的所有信息

select a.* from employees as a where not exists 
(select emp_no from dept_emp where emp_no=a.emp_no)

57.给出emp_no、first_name、last_name、奖金类型btype、对应的当前薪水情况salary以及奖金金额bonus。 bonus类型btype为1其奖金为薪水salary的10%，btype为2其奖金为薪水的20%，其他类型均为薪水的30%。

case语句的使用：

case (条件变量)
when (条件值一) then (内容）
when (条件值二) then (内容）
、、、
else (内容) end

select a.emp_no,a.first_name,a.last_name,b.btype,c.salary,
(case b.btype
 when 1 then c.salary*0.1
 when 2 then c.salary*0.2
 else c.salary*0.3 end
)as bonus
from employees as a 
inner join emp_bonus as b 
on a.emp_no=b.emp_no
inner join salaries as c on a.emp_no=c.emp_no
where c.to_date='9999-01-01'

58.按照salary的累计和running_total，其中running_total为前两个员工的salary累计和

select a.emp_no,a.salary,
(select sum(b.salary) from salaries as b 
 where b.emp_no<=a.emp_no and b.to_date='9999-01-01') 
as running_total 
from salaries as a 
where a.to_date='9999-01-01' order by a.emp_no; 

59.对于employees表中，给出奇数行的first_name

• 按照first_name排序，确定行号？如何确定？生成一个新表，其中小于当前first_name的记录个数即为按first_name排序后的行号
• 利用where语句筛选出行号为奇数的记录

select e1.first_name from 
    (select e2.first_name,(
            select count(*) from employees as e3 where e3.first_name<=e2.first_name
        )as row_id from employees as e2
    ) as e1
where e1.row_id&1=1