按在地上摩擦面试官mysql之实战四

Java八股文宝典 2021-04-20

259

日常工作中，会遇到大量的sql编写，同样一个逻辑，每个人写的sql会有不同。实战篇会带领大家进行大量的sql练习，希望你自己独立思考，进行sql编写

针对actor表创建视图actor_name_view？

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 datetime NOT NULL);

针对actor表创建视图actor_name_view，只包含first_name以及last_name两列，并对这两列重新命名，first_name为first_name_v，last_name修改为last_name_v

针对actor表创建视图actor_name_view？

自己思考几分钟,在看下我的思路：

点击空白处查看答案

1. create view关键字

2. select col as col1 用法

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

针对salaries表emp_no字段创建索引idx_emp_no，查询emp_no为10005？

REATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
create index idx_emp_no on salaries(emp_no);

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

针对salaries表emp_no字段创建索引idx_emp_no，查询emp_no为10005？

自己思考几分钟,在看下我的思路：

点击空白处查看答案

1. force index 关键字

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

在last_update后面新增加一列名字为create_date

存在actor表，包含如下列信息：

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  datetime NOT NULL);

现在在last_update后面新增加一列名字为create_date, 类型为datetime, NOT NULL，默认值为'2020-10-01 00:00:00'

在last_update后面新增加一列名字为create_date

自己思考几分钟,在看下我的思路：

点击空白处查看答案

1. 添加column

2. 默认值 default

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

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

CREATE TABLE employees_test(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL);


CREATE TABLE audit(
EMP_no INT NOT NULL,
NAME TEXT NOT NULL
);

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

自己思考几分钟,在看下我的思路：

点击空白处查看答案

1. 用 CREATE TRIGGER 语句构造触发器，用 BEFORE或AFTER 来指定在执行后面的SQL语句之前或之后来触发TRIGGER

2. 触发器执行的内容写出 BEGIN与END 之间

3. 可以使用 NEW与OLD 关键字访问触发后或触发前的employees_test表单记录

create trigger audit_log after insert on employees_test for each row

begin

insert into audit values(new.id,new.name);

end

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

有一个员工表employees简况如下:

CREATE TABLE IF NOT EXISTS titles_test (
id int(11) not null primary key,
emp_no int(11) NOT NULL,
title varchar(50) NOT NULL,
from_date date NOT NULL,
to_date date DEFAULT NULL);

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

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

自己思考几分钟,在看下我的思路：

点击空白处查看答案

1. 先去重找到最小的id

2. 删除不是最小的id的重复数据

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

将所有to_date为9999-01-01的全部更新为NULL,且 from_date更新为2001-01-01

CREATE TABLE IF NOT EXISTS titles_test (
id int(11) not null primary key,
emp_no int(11) NOT NULL,
title varchar(50) NOT NULL,
from_date date NOT NULL,
to_date date DEFAULT NULL);


insert into titles_test values ('1', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('2', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('3', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('4', '10004', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('5', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('6', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('7', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01');

将所有to_date为9999-01-01的全部更新为NULL,且 from_date更新为2001-01-01

自己思考几分钟,在看下我的思路：

点击空白处查看答案

1. 送分题不讲了

update titles_test set to_date=NULL,from_date='2001-01-01' where to_date='9999-01-01'

将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005,其他数据保持不变，使用replace实现

CREATE TABLE titles_test (
   id int(11) not null primary key,
   emp_no  int(11) NOT NULL,
   title  varchar(50) NOT NULL,
   from_date  date NOT NULL,
   to_date  date DEFAULT NULL);


insert into titles_test values
('1', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('2', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('3', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('4', '10004', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('5', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('6', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('7', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01');

将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005,其他数据保持不变，使用replace实现，直接使用update会报错

将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005,其他数据保持不变，使用replace实现

自己思考几分钟,在看下我的思路：

点击空白处查看答案

1. 函数replace使用

update titles_test set emp_no = replace(emp_no,10001,10005)

where id=5;

将titles_test表名修改为titles_2017

CREATE TABLE IF NOT EXISTS titles_test (
id int(11) not null primary key,
emp_no int(11) NOT NULL,
title varchar(50) NOT NULL,
from_date date NOT NULL,
to_date date DEFAULT NULL);


insert into titles_test values ('1', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('2', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('3', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('4', '10004', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('5', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('6', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('7', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01');

将titles_test表名修改为titles_2017

自己思考几分钟,在看下我的思路：

点击空白处查看答案

1. alter + rename

ALTER TABLE titles_test RENAME AS titles_2017

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

REATE TABLE employees_test(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);


CREATE TABLE audit(
EMP_no INT NOT NULL,
create_date datetime NOT NULL
);

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

自己思考几分钟,在看下我的思路：

点击空白处查看答案

1. alter table + add foreign key + refrences

2. 经理自己不展示，需要过率以下

ALTER TABLE audit ADD FOREIGN KEY (emp_no) REFERENCES employees_test (id)

Q10

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

create table emp_bonus(
emp_no int not null,
btype smallint not null);
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL, PRIMARY KEY (`emp_no`,`from_date`));


INSERT INTO emp_bonus VALUES (10001,1);
INSERT INTO salaries VALUES(10001,85097,'2001-06-22','2002-06-22');
INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');

请你写出更新语句，将所有获取奖金的员工当前的(salaries.to_date='9999-01-01')薪水增加10%。(emp_bonus里面的emp_no都是当前获奖的所有员工)

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

自己思考几分钟,在看下我的思路：

点击空白处查看答案

1. 先选择符合条件的emp_no，在筛选to_date，最后将符合的字段进行更新。推荐使用内连接查询，速度会快一些

2. 推荐方法二，内连接速度快

方法一：update salaries s set s.salary=s.salary*1.1

where s.emp_no in(

select emp_no

from emp_bonus

) and s.to_date = '9999-01-01';

方法二：

update salaries T join emp_bonus T2 on T2.emp_no = T.emp_no

set salary = salary * 1.1

where to_date = '9999-01-01'

OS:每天坚持跟山虎做十道题，几天之后你的sql功底就会突飞猛进。

大家好，我是山虎，喜欢数学，编码，算法，股票，AI。经历过一次失败的创业。东西不要死记硬背，要做到自己真正的理解。年轻人就要折腾，年轻人就要折腾，年轻人就要折腾。原创不易，帮忙转发。

JAVA八股文

随时欢迎与我讨论各种问题

mysql 面试

文章转载自Java八股文宝典，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

按在地上摩擦面试官mysql之实战四

评论