牛客网sql题库（31-60题）—— 个人答案与过程解析

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

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

解析，两种字符串连接方式：mysql CONCAT(str1,str2,…) 和 CONCAT_WS(separator,str1,str2,…)

33 建表

create table actor(
    actor_id smallint(5) not null ,
    first_name varchar(45) not null comment '姓名',
    last_name varchar(45) not null comment '姓氏',
    last_update date not null comment '日期',
    primary key(actor_id)
)engine=innodb,default charset=utf8;

34 批量插入数据

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

35 插入数据时忽略已存在的数据

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

36 根据旧表创建新表

create table actor_name as select first_name,last_name from actor;

37 创建索引

对first_name创建唯一索引uniq_idx_firstname，对last_name创建普通索引idx_lastname

alter table actor add unique uniq_idx_firstname(first_name);
alter table actor add index idx_lastname(last_name);

解析：
索引分类：

1. 普通索引
2. 唯一索引
   - 主键索引：primary key
   - 唯一索引：unique
   主键索引只针对主键这一列，有且仅有一列，列值不为空且不重复。唯一索引unique可以有多个列，列值可以为空但是不能重复

创建索引的方式：

1. 在创建表的时候创建索引 create table 表名 ( id int , index(id) )
2. 创建表之后创建索引 create index 索引名 on 表名(字段名) 或 alter table 表名 add index 索引名(字段名)

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;

39 使用强制索引查询mysql

select * from salaries
force index (idx_emp_no)
where emp_no=10005;

40 新增一列

现在在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';

这里跳过第41题，题目为触发器

42 删除表的部分记录

delete from titles_test
where id not in (select * from (select min(id) from titles_test group by emp_no) as s);

解析：mysql不允许对同一张表，同时进行查询和删除操作，所以这里需要将表命名为另一张表，作为一张中间表进行查询，然后对原表titles_test进行删除

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

update titles_test set to_date=null ,from_date='2001-01-01';

44 将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005,其他数据保持不变

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

45 将titles_test表名修改为titles_2017

alter table titles_test rename to titles_2017

创建外键约束

在audit表上创建外键约束，其emp_no对应employees_test表的主键id

alter table audit
add constraint foreign key (emp_no)
references employees_test(id);

关于外键约束可以看下面这两篇博客
MySQL之外键约束
MySQL数据库——外键约束
MySQL外键约束（FOREIGN KEY）用来在两个表的数据之间建立链接，它可以是一列或者多列。一个表可以有一个或多个外键。外键对应的是参照完整性，一个表的外键可以为空值，若不为空值，则每一个外键的值必须等于另一个表中主键的某个值。外键的主要作用是保持数据的一致性、完整性

外键是表的一个字段，不是本表的主键，但对应另一个表的主键。定义外键后，不允许删除另一个表中具有关联关系的行。

比如主表student，字段为 学生id，学生姓名，学生性别，主键为学生id
从表course，字段为科目号，学生sid，科目成绩，主键为科目号，外键为学生sid，这里的外键指向主表student的主键学生id

指定外键的方式，在创建表的时候指定

create table course(
    cid smallint(5) primary key,
    sid varchar(45) not null comment '学生sid',
    score varchar(45) not null comment '科目成绩',
    foreign key(sid) references student(id)
)engine=innodb,default charset=utf8;

这样就给course创建外键约束，让sid变成外键，指向student表的主键——学生id

除此之外，可以像一开始的题目答案那样，alter添加外键约束

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

update salaries as s inner join emp_bonus as e on s.emp_no = e.emp_no
set salary = salary*1.1
where to_date='9999-01-01'

50 将employees表中的所有员工的last_name和first_name通过(’)连接起来

select concat(RTrim(last_name),"'", LTrim(first_name)) from employees

这里通过RTrim和LTrim分别去除右边，左边的空格

51 查找字符串中逗号出现的次数

select id,(length(string)-length(replace(string,',',''))) as cnt
from strings;

将逗号替换为空字符串（不是空格），然后用替换前后的字符串长度相减即为逗号出现次数

52 按照first_name最后两个字母升序进行输出

select first_name
from employees
order by right(first_name,2)

解析：right函数可以返回从最右边开始指定长度的字符串，left函数是返回从最左边开始的指定长度字符串

53 按照dept_no进行汇总，属于同一个部门的emp_no按照逗号进行连接

select dept_no,group_concat(emp_no) as employees
from dept_emp
group by dept_no

https://blog.csdn.net/harry5508/article/details/102481017
group_concat（）函数可以将group by产生的同一个分组中的值连接起来，返回一个字符串结果。

语法：group_concat( [ distinct ] 要连接的字段 [ order by 排序字段 asc/desc ] [ separator ‘分隔符’ ] )
通过使用distinct可以排除重复值
可以使用order by子句对要连接的值先排序后连接，如果不指定默认为升序asc
separator是一个字符串值，如果不指定则默认为逗号

注意：当数据太大，group_concat超出了默认值1024，超过就会截断，group_concat查询出来的数据就会不全

54 平均工资

select (sum(salary)-max(salary)-min(salary)) / (count(1)-2) as avg_salary
from salaries
where to_date='9999-01-01'

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

select *
from employees
limit 5,5

limit x , y ：跳过 x 条数据，读取 y 条数据

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

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

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

select e1.emp_no,
		e1.first_name,
		e1.last_name,
		e2.btype,s1.salary,
		case
			when e2.btype=1 then s1.salary*0.1
			when e2.btype=2 then s1.salary*0.2
			else s1.salary*0.3
		end as bonus
from employees as e1,emp_bonus as e2,salaries as s1
where e1.emp_no=e2.emp_no and e1.emp_no=s1.emp_no and s1.to_date='9999-01-01';

60 统计salary的累计和running_total

select emp_no,
        salary,
        sum(salary) over (order by emp_no) as running_total
from salaries    
where to_date='9999-01-01';

开窗函数可以不使用partition by，只使用order by，默认按照全局排序