【MySql】牛客SQL刷题（下）

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

拼接：concat函数，"||"拼接字符串

-- 方法一 
select concat(concat(last_name," "),first_name) as name from employees;
-- 方法二
select concat(last_name," ",first_name) as name from employees;
-- 方法三："||"拼接字符串
select last_name||" "||first_name as name from employees;

创建一个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)
);

对于表actor批量插入数据

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

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

IGNORE：存在，自动忽略
```
INSERT IGNORE INTO actor values(3,'ED','CHASE','2006-02-15 12:34:33');
```

创建一个actor_name表，从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;

表actor结构创建索引

对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);
```

针对actor表创建视图actor_name_view，并给字段起别名；

-- 方法一
create view actor_name_view (first_name_v,last_name_v) as
select first_name ,last_name from actor;
-- 方法二
CREATE VIEW actor_name_view AS
SELECT first_name AS fist_name_v, last_name AS last_name_v
FROM actor

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

-- 创建索引
create index idx_emp_no on salaries(emp_no);
-- 强制使用索引 indexed
select * from salaries
indexed by idx_emp_no
where emp_no = '10005'

添加列：last_update后面新增加一列名字为create_date, 类型为datetime, NOT NULL，默认值为'0000 00:00:00'
```
ALTER table actor add create_date datetime not null default '0000-00-00 00:00:00';
```

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

create trigger audit_log after insert on employees_test
-- 触发内容在begin，end间，并且内容必须分号结尾
begin
-- new：拿到上个表中的id，name对应值（此值是employees更新之后的值）
-- old：拿到更新之前的值
     insert into audit values(new.id,new.name);
end;

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

(一个表中，多个emp_no重复，删除，并保留其中一个最小的)

思路：通过emp_no分组，只要不是min id，就删除；
```
delete
from titles_test
where id not in(
	select min(id) from titles_test group by emp_no
);
```
更新日期：

将所有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_data = '9999-01-01';
```
将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005,其他数据保持不变，使用replace实现。

replace：
- 全字段替换
- replace函数：replace(x,y,z)
```
replace into titles_test 
values(5, 10005, 'Senior Engineer', '1986-06-26', '9999-01-01');
```

将titles_test表名修改为titles_2017

alter table titles_test rename to titles_2017；

在audit表上创建外键约束，其emp_no对应employees_test表的主键id。
```
alter table audit
add foreign key(emp_no) references employees_test(id)
```

通过某字段（emp_no）获取两张表的相同数据

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

将所有获取奖金的员工当前的薪水增加10%;

两个表：奖金表，员工薪水表

查询奖金表中的emp_no
```
update salaries
set salary = salary * 1.1
where emp_no in (select emp_no from emp_bonus);
```
将employees表中的所有员工的last_name和first_name通过(')连接起来
```
select last_name||"'"||first_name as name from employees;
```
查找字符串'10,A,B' 中逗号','出现的次数cnt

没有直接统计字符串数量的方法；

通过构造两个字符串，相减，求出差值；
```
select length('10,A,B') -length(replace('10,A,B',",","")) as cnt;
```
获取Employees中的first_name，查询按照first_name最后两个字母，按照升序进行排列

substr(string,start,length)

length可以省略，表示截取到最后；
```
select first_name 
from employees
order by substr(first_name,length(first_name)-1)
```
按照dept_no进行汇总，属于同一个部门的emp_no按照逗号进行连接，结果给出dept_no以及连接出的结果employees

group_concat聚合函数：group_concat(X,Y)

x：药连接的字段

y：连接用的符号（默认逗号，可以省略）
```
select dept_no,group_concat(emp_no) AS employees
from dept_emp
group by dept_no
order by dept_no;
```

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

排除某些条件：not in（<>）

select avg(salary) as avg_salary
from salaries
where salary not in (select max(salary) from salaries)
and salary not in (select min(salary) from salaries);

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

5行一页，第二页：6~10

分页：limit(5,5)—从第六条数据开始，显示5条
```
select *
from employees
limit 5,5
```
获取所有员工的emp_no、部门编号dept_no以及对应的bonus类型btype和received ，没有分配具体的员工不显示

left join：以左表为主，先满足左表数据
```
select m.emp_no,m.dept_no,e.btype,e.recevied  
from dept_emp as m
left join emp_bonus as e
on m.emp_no = e.emp_no
```

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

-- 不用exists
select * from employees
where emp_no not in (select emp_no from dept_emp);
-- 用exists
SELECT * FROM employees as e WHERE NOT EXISTS 
(SELECT emp_no FROM dept_emp WHERE emp_no = e.emp_no)

获取有奖金的员工相关信息。

bonus类型btype为1其奖金为薪水salary的10%，btype为2其奖金为薪水的20%，其他类型均为薪水的30%

通过btype，算出bonus，需要用到case

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

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

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

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

注意这里是奇数行，不是emp_no是奇数，emp_no可能是从偶数开始；

所以：首先需要拿到表记录总数count(*)

(select count(*) from employees as e2
WHERE e1.first_name <= e2.first_name
)

这条语句返回的是：当前的记录时第几条记录；
```
select first_name
from employees as e1
where (select count(*) from employees as e2
      WHERE e1.first_name <= e2.first_name
      ) % 2 = 1;
```