文章目录
牛客网用的SQLite,这里的答案按照Mysql写的,有的会标注
12题
获取所有部门中当前员工薪水最高的相关信息,给出dept_no, emp_no以及其对应的salary,按部门排序
select r.dept_no,r.emp_no,max(r.salary)
from (select d.dept_no,d.emp_no,s.salary from dept_emp d
inner join salaries s on d.emp_no = s.emp_no
where d.to_date = '9999-01-01'
and s.to_date = '9999-01-01')
as r
group by r.dept_no
order by r.dept_no
利用了GROUP BY 默认取非聚合数据的第一条记录,所以先排好序,拿到的emp_no第一条信息,也是与最大salary匹配的
18题
查找当前薪水(to_date=‘9999-01-01’)排名第二多的员工编号emp_no、薪水salary、last_name以及first_name,不准使用order by
第一种
先选出最高薪水,再剔除这一薪水的范围内求最高薪水
select e.emp_no,max(s.salary),e.last_name,e.first_name
from employees e inner join salaries s
on e.emp_no = s.emp_no
where s.to_date = '9999-01-01'
and s.salary NOT IN (select max(salary) from salaries where to_date = '9999-01-01');
第二种
类似第一种
select e.emp_no,s.salary,e.last_name,e.first_name
from employees e inner join salaries s
on e.emp_no = s.emp_no
where s.to_date = '9999-01-01'
and s.salary = (select max(salary) from salaries where salary <
(select max(salary) from salaries where to_date = '9999-01-01')
and to_date = '9999-01-01');
第三种
用自连接,薪水第二高说明上面只有一个薪水,但可能有多人有相同薪水,故用DISTINCT
去重
左表为s1,右表为s2,s1 有 100 100 98 96 四种薪水,则自连接之后
s1.98 < s2.100,此时s1.98关联s2.100,
对应s1.salary < s2.salary
条件下HAVING COUNT(DISTINCT s2.salary) = 1
98只小于100这一个数字,所以98成立
96上面有98、100两个数字,不符合条件
select e.emp_no,s.salary,e.last_name,e.first_name
from employees e inner join salaries s
on e.emp_no = s.emp_no
where s.to_date = '9999-01-01'
and s.salary = (select s1.salary from salaries s1
inner join salaries s2
on s1.salary < s2.salary
where s1.to_date = '9999-01-01' and s2.to_date = '9999-01-01'
group by s1.emp_no
having count(distinct s2.salary) = 1);
19题
查找所有员工的last_name和first_name以及对应的dept_name,也包括暂时没有分配部门的员工
SOLUTION
连用两次left join
(牛客网上只能用 INNER JOIN``LEFT JOIN
)
select e.last_name,e.first_name,dp.dept_name
from employees e
left join
dept_emp de on e.emp_no = de.emp_no
left join
departments dp on dp.dept_no = de.dept_no;
21题
查找所有员工自入职以来的薪水涨幅情况,给出员工编号emp_no以及其对应的薪水涨幅growth,并按照growth进行升序
SOLUTION
连用两次inner join
select e.emp_no,(b.salary - c.salary) as growth from employees e
inner join
salaries b on e.emp_no = b.emp_no and b.to_date = '9999-01-01'
inner join
salaries c on e.emp_no = c.emp_no and e.hire_date = c.from_date
order by growth;
26题
汇总各个部门当前员工的title类型的分配数目,结果给出部门编号dept_no、dept_name、其当前员工所有的title以及该类型title对应的数目count
注意:group by 后面的顺序不能变,先按部门再按头衔
select dp.dept_no,dp.dept_name,t.title,count(t.title) count
from departments dp,dept_emp de,titles t
where
dp.dept_no = de.dept_no
and de.emp_no = t.emp_no
and de.to_date = '9999-01-01'
and t.to_date = '9999-01-01'
group by de.dept_no,t.title
32题(拼接)
将employees表的所有员工的last_name和first_name拼接起来作为Name,中间以一个空格区分
//SQLite
select last_name || " " || first_name as Name from employees
//MySQL
//第一种
select concat(last_name," ",first_name) as Name from employees
//第二种
select concat(concat(last_name," "),first_name) as Name from employees
35题(INSERT IGNORE INTO)
对于表actor批量插入如下数据,如果数据已经存在,请忽略,不使用replace操作
SELECT IGNORE INTO actor (actor_id,first_name,last_name,last_update)
VALUES(3,'ED','CHASE','2006-02-15 12:34:33')
38题(视图)
针对actor表创建视图actor_name_view,只包含first_name以及last_name两列,并对这两列重新命名,first_name为first_name_v,last_name修改为last_name_v:
SQLite
//第一种
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
39题(强制索引)
针对salaries表emp_no字段创建索引idx_emp_no,查询emp_no为10005, 使用强制索引。
//SQLite
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
40题(加列)
存在actor表,包含如下列信息:
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')));
现在在last_update后面新增加一列名字为create_date, 类型为datetime, NOT NULL,默认值为’0000-00-00 00:00:00’
ALTER TABLE actor ADD COLUMN create_date datetime NOT NULL
DEFAULT '0000-00-00 00:00:00'
COLUMN
可省略
41题(触发器)
CRE构造一个触发器audit_log,在向employees_test表中插入一条数据的时候,触发插入相关的数据到audit中。
//SQLite
CREATE TRIGGER audit_log AFTER INERT ON employees_test
BEGIN
INSERT INTO audit VALUES(NEW.ID,NEW.NAME);
END
//Mysql
CREATE TRIGGER audit_log AFTER INERT
ON employees_test
INSERT INTO audit VALUES(NEW.ID,NEW.NAME);
注:如果有多个执行语句,则命令行有变化
例:定义一个触发器,一旦有满足条件的删除操作,就会执行BEGIN和END中的语句
我们可以使用DELIMITERE
指定自定义的定界符。
DELIMITER new_delemiter
new_delemiter
可以设为1个或多个长度的符号,默认的是分号(;),我们可以把它修改为其他符号,如$
;DELIMITER $(注意:我们修改了定界符使用完了,记得修改回来DELIMITER ;
)
参考代码:
44题(Replace)
将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005,其他数据保持不变,使用replace实现。
replace(字符串,“需要替换的子串”,“用于替换子串的字符串”)
SQLite
//第一种
REPLACE INTO titles_test VALUES(5,10005,'1986-06-26', '9999-01-01');
//第二种
UPDATE titles_test SET emp_no = REPLACE(emp_no,10001,10005) WHERE
id = 5;
45题(外键)
在audit表上创建外键约束,其emp_no对应employees_test表的主键id
//Mysql
ALTER TABLE audit ADD PRIMARY KEY (EMP_no) REFERENCES employees_test(ID)
47题(INTERSECT)
存在如下的视图:
create view emp_v as select * from employees where emp_no >10005;
如何获取emp_v和employees有相同的数据?
select * from emp_v intersect select * from employees
49 题
针对库中的所有表生成select count(*)对应的SQL语句
//sqlite
select "select count(*) from " || name || ";" as cnts
from sqlite_master where type = 'table';
//mysql
SELECT concat("select count(*) from ",new.table_name,";") AS cnts
FROM (SELECT table_name FROM information_schema.tables
WHERE table_schema = 'sqlite_master') AS new;
查询看看库里有多少个表,表名等
select * from INFORMATION_SCHEMA.TABLES
information_schema这张数据表保存了MySQL服务器所有数据库的信息。如数据库名,数据库的表,表栏的数据类型与访问权限等。
51题(substr)
获取Employees中的first_name,查询按照first_name最后两个字母,按照升序进行排列
select first_name from employees order by substr(first_name,-2)
本题考查 substr(X,Y,Z)
或 substr(X,Y)
函数的使用。其中X
是要截取的字符串。Y
是字符串的起始位置(注意第一个字符的位置为1,而不为0),取值范围是±(1~length(X)),当Y等于length(X)时,则截取最后一个字符;当Y等于负整数-n时,则从倒数第n个字符处截取。
Z是要截取字符串的长度,取值范围是正整数,若Z省略,则从Y处一直截取到字符串末尾;若Z大于剩下的字符串长度,也是截取到字符串末尾为止。
53题(group_concat)
按照dept_no进行汇总,属于同一个部门的emp_no按照逗号进行连接,结果给出dept_no以及连接出的结果employees
//sqlite
select dept_no,group_concat(emp_no) as employees
from dept_emp group by dept_no;
//mysql
select dept_no,group_concat(emp_no separator ',') as employees
from dept_emp group by dept_no;
group_concat(X,Y)
,其中X是要连接的字段,Y是连接时用的符号,可省略,默认为逗号。此函数必须与 GROUP BY 配合使用。此题以 dept_no 作为分组,将每个分组中不同的emp_no用逗号连接起来(即可省略Y)
57题(EXISTS/NOT EXISTS)
MySQL EXISTS 和 NOT EXISTS 子查询语法如下:
SELECT … FROM table WHERE EXISTS (subquery)
SELECT … FROM table WHERE NOT EXISTS (subquery)
使用含有关键字exists查找未分配具体部门的员工的所有信息
select * from employees where not exists
(select emp_no from dept_emp where employees.emp_no = dept_emp.emp_no)
59题(CASE)
给出emp_no、first_name、last_name、奖金类型btype、对应的当前薪水情况salary以及奖金金额bonus。 bonus类型btype为1其奖金为薪水salary的10%,btype为2其奖金为薪水的20%,其他类型均为薪水的30%。 当前薪水表示to_date=‘9999-01-01’
输出格式:
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 INNER JOIN emp_bonus AS b ON e.emp_no = b.emp_no
INNER JOIN salaries AS s ON e.emp_no = s.emp_no AND s.to_date = '9999-01-01'
61题
对于employees表中,给出奇数行的first_name
本题描述有误,先排序,再取奇数行
select e1.first_name from employees e1 where
(select count(*) from employees e2 where e1.first_name >= e2.first_name)&1=1
奇数的确定条件,可以% 2 = 1
,也可以& 1 = 1