牛客SQL39---42

目录

 

SQL39  针对上面的salaries表emp_no字段创建索引idx_emp_no，查询emp_no为10005

题目描述

答案

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

题目描述

答案

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

题目描述

答案

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

题目描述

答案：

---

SQL39  针对上面的salaries表emp_no字段创建索引idx_emp_no，查询emp_no为10005

题目描述

针对salaries表emp_no字段创建索引idx_emp_no，查询emp_no为10005, 使用强制索引。
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`));
create index idx_emp_no on salaries(emp_no);

答案

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

MYSQL中强制索引查询使用：FORCE INDEX(indexname);

SQLite中强制索引查询使用：INDEXED BY indexname;

force index要紧跟from后面，写在where语句之前，否则会报错

 

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

题目描述

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

1 2 3 4 5

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'

答案

Alter table actor
add column create_date 
datetime NOT NULL Default'2020-10-01 00:00:00' 

 

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

题目描述

构造一个触发器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
);

答案

create trigger audit_log 
After Insert on employees_test
for each row
begin
    insert into audit values(new.id,new.name);
end

 

在MySQL中，创建触发器语法如下：
CREATE TRIGGER trigger_name
trigger_time trigger_event ON tbl_name
FOR EACH ROW
trigger_stmt
其中：

• trigger_name：标识触发器名称，用户自行指定；
• trigger_time：标识触发时机，取值为 BEFORE 或 AFTER；
• trigger_event：标识触发事件，取值为 INSERT、UPDATE 或 DELETE；
• tbl_name：标识建立触发器的表名，即在哪张表上建立触发器；
• trigger_stmt：触发器程序体，可以是一句SQL语句，或者用 BEGIN 和 END 包含的多条语句，每条语句结束要分号结尾。

【NEW 与 OLD 详解】
MySQL 中定义了 NEW 和 OLD，用来表示
触发器的所在表中，触发了触发器的那一行数据。
具体地：

1. 在 INSERT 型触发器中，NEW 用来表示将要（BEFORE）或已经（AFTER）插入的新数据；
2. 在 UPDATE 型触发器中，OLD 用来表示将要或已经被修改的原数据，NEW 用来表示将要或已经修改为的新数据；
3. 在 DELETE 型触发器中，OLD 用来表示将要或已经被删除的原数据；
   使用方法： NEW.columnName （columnName 为相应数据表某一列名）

 

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

题目描述

删除emp_no重复的记录，只保留最小的id对应的记录。
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表为

id	emp_no	title	from_date	to_date
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

答案：

Delete from titles_test
where id not in
(
select * From
(select Min(id)
from titles_test
group by emp_no) as a
)

mysql不允许在子查询的同时删除原表数据，而sqllite可以这样做 

首先按照编号分组，将相同编号的分为一组，

在每个相同编号的分组中取出最小的，即去除重复值，保留最小值。

在一层查找，删除不在这个最小值范围内的，那保留下来的，就是单一emp_no,且id最小的