2021-05-23牛客sql（21-35）

21.获取员工其当前的薪水比其manager当前薪水还高的相关信息，
第一列给出员工的emp_no，
第二列给出其manager的manager_no，
第三列给出该员工当前的薪水emp_salary,
第四列给该员工对应的manager当前的薪水manager_salary

考察：根据题意两表联结查询

select emp_no, manager_no, emp_salary, manager_salary
from
(SELECT de.dept_no, de.emp_no, s.salary AS emp_salary
FROM dept_emp AS de, salaries AS s
WHERE de.emp_no=s.emp_no
AND de.to_date='9999-01-01'
AND s.to_date='9999-01-01') AS a,
(SELECT dm.dept_no, dm.emp_no AS manager_no, s.salary AS manager_salary
FROM dept_manager AS dm, salaries AS s
WHERE dm.emp_no=s.emp_no
AND dm.to_date='9999-01-01'
AND s.to_date='9999-01-01') AS b
WHERE a.dept_no=b.dept_no
AND a.emp_salary>b.manager_salary;

22.汇总各个部门当前员工的title类型的分配数目，即结果给出部门编号dept_no、dept_name、其部门下所有的员工的title以及该类型title对应的数目count，结果按照dept_no升序排序
考察：多表联结与不同值的分类汇总
在group by 的时候，为啥除了title外又加了 demp_no的筛选条件呢

SELECT de.dept_no,d.dept_name,t.title,COUNT(*) AS count
  FROM departments d
  LEFT JOIN dept_emp de
  ON d.dept_no = de.dept_no
  INNER JOIN titles t
  ON de.emp_no = t.emp_no
  GROUP BY de.dept_no,t.title
  ORDER BY de.dept_no ASC;

23 查找描述信息(film.description)中包含robot的电影对应的分类名称(category.name)以及电影数目(count(film.film_id))，而且还需要该分类包含电影总数量(count(film_category.category_id))>=5部
考察：group by 与having的用法

SELECT c.name AS 分类名称category.name,
COUNT(f.film_id) AS 电影数目count(film.film_id) 
FROM film f,category c,
film_category fc 
WHERE f.film_id = fc.film_id 
AND c.category_id = fc.category_id 
AND f.description LIKE '%robot%' 
AND c.category_id IN 
(SELECT  category_id 
FROM film_category 
GROUP BY category_id 
HAVING COUNT(film_id) >= 5)

24.使用join查询方式找出没有分类的电影id以及名称
考察：交集中非空部分的选取

SELECT f.film_id, f.title
FROM film AS f
LEFT JOIN film_category AS fc
ON f.film_id=fc.film_id
WHERE category_id IS NULL;

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

select title,description
from film f
where f.film_id in (select fc.film_id
                    from category c join film_category fc 
                    on c.category_id=fc.category_id
                    where name='Action')；

26.将employees表的所有员工的last_name和first_name拼接起来作为Name，中间以一个空格区分
考察：表的拼接

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

27. 创建新表
考察：表的创建（PS.表名不要加引号）

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 date not null
);

28.对于表actor批量插入如下数据(不能有2条insert语句哦!)
考察：批量插入数据

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

29.对于表actor插入如下数据,如果数据已经存在，请忽略(不支持使用replace操作)
考察:批量插入数据

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

补充：
mysql中常用的三种插入数据的语句:
insert into表示插入数据，数据库会检查主键，如果出现重复会报错；
replace into表示插入替换数据，需求表中有PrimaryKey，
或者unique索引，如果数据库已经存在数据，则用新数据替换，如果没有数据效果则和insert into一样；
insert ignore表示，如果中已经存在相同的记录，则忽略当前新数据；

30. 创建一个actor_name表，并且将actor表中的所有first_name以及last_name导入该表.
考察：创建数据表

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;

补充：mysql创建数据表的三种办法
1.常规创建
create table if not exists 目标表
2.复制表格
create 目标表 like 来源表
3.将table1的部分拿来创建table2
create table if not exists actor_name
(first_name varchar(45) not null,
last_name varchar(45) not null)
select first_name,last_name
from actor

31. 对first_name创建唯一索引uniq_idx_firstname，对last_name创建普通索引idx_lastname
简单提一下关于MySQL中给字段创建索引的四种方式：

CREATE INDEX idx_lastname ON actor(last_name);
CREATE UNIQUE INDEX uniq_idx_firstname ON actor(first_name);

添加主键
ALTER TABLE tbl_name ADD PRIMARY KEY (col_list);
// 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。

添加唯一索引
ALTER TABLE tbl_name ADD UNIQUE index_name (col_list);
// 这条语句创建索引的值必须是唯一的。

添加普通索引
ALTER TABLE tbl_name ADD INDEX index_name (col_list);
// 添加普通索引，索引值可出现多次。

添加全文索引
ALTER TABLE tbl_name ADD FULLTEXT index_name (col_list);
// 该语句指定了索引为 FULLTEXT ，用于全文索引。

PS: 附赠删除索引的语法：
DROP INDEX index_name ON tbl_name;
// 或者
ALTER TABLE tbl_name DROP INDEX index_name；
ALTER TABLE tbl_name DROP PRIMARY KEY;

32. 针对actor表创建视图actor_name_view，只包含first_name以及last_name两列，并对这两列重新命名，first_name为first_name_v，last_name修改为last_name_v：
考察：建视图，create view 视图名 as select xxx from xx

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

33.针对salaries表emp_no字段创建索引idx_emp_no，查询emp_no为10005, 使用强制索引
考察：强制索引
补充：强制索引FORCE INDEX() 强制索引使用方法：force index(字段名)

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

34.在last_update后面新增加一列名字为create_date, 类型为datetime, NOT NULL，默认值为’2020-10-01 00:00:00’
考察：添加新列

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

35.构造一个触发器audit_log，在向employees_test表中插入一条数据的时候，触发插入相关的数据到audit中
考察：触发器

create trigger audit_log
after insert on employees_test
for each row
begin
insert into audit values(new.id,new.name);
end