【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;