牛客网SQL实战二刷 | Day7

「题外话」

第18篇原创博文后，今天收获了我的第7位粉丝✌️，7是我喜欢的数字，刚好今天也是系列的Day7，继续加油加油鸭?。

转入正题⬇️

「牛客网SQL实战二刷」是个系列学习笔记博文，每天解析6道SQL题目～ 今天是第37- 42题。

每篇笔记的格式大致为，三大板块：

• 大纲
• 题目（题目描述、思路、代码、相关参考资料/答疑）
• 回顾

「往期回顾」❤️
《牛客网SQL实战二刷 | 完整解析 – 目录索引》

一、大纲

题目	知识点
37	创建唯一索引「CREATE UNIQUE INDEX」，创建普通索引「CREATE INDEX」
38	创建视图「CREATE VIEW」
39	使用强制索引「INDEXED BY」
40	新增列「ALTER … ADD COLUMN」
41	构建触发器「CREATE TRIGGER」
42	删除记录「DELETE FROM」

二、题目

37. 对first_name创建唯一索引uniq_idx_firstname

• 题目描述

针对如下表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')))

对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)

注意： 两行代码间需以 分号 分隔，否则无法通过。

?SQL CREATE INDEX 语法

CREATE INDEX index_name ON table_name (column_name)

注释："column_name" 规定需要索引的列

?SQL CREATE UNIQUE INDEX 语法

CREATE UNIQUE INDEX index_name ON table_name (column_name)

• 参考资料
  《SQL CREATE INDEX 语句》http://www.w3school.com.cn/sql/sql_create_index.asp

38. 针对actor表创建视图actor_name_view

• 题目描述

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

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')))

• 代码

CREATE VIEW actor_name_view AS
SELECT first_name AS first_name_v, last_name AS last_name_v
FROM actor

?SQL CREATE VIEW 语法

CREATE VIEW "VIEW_NAME" AS "SQL 语句";

• 参考资料
  《SQL Create View》https://www.1keydata.com/cn/sql/sql-create-view.php

39. 针对上面的salaries表emp_no字段创建索引idx_emp_no

• 题目描述

针对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 INDEXED BY idx_emp_no WHERE emp_no = '10005'

?SQL 强制索引

• SQLite中，使用 INDEXED BY 语句进行强制索引查询，
  SELECT * FROM salaries INDEXED BY idx_emp_no WHERE emp_no = 10005
• MySQL中，使用 FORCE INDEX 语句进行强制索引查询，
  SELECT * FROM salaries FORCE INDEX idx_emp_no WHERE emp_no = 10005

作者：wasrehpic
来源：https://www.nowcoder.com/questionTerminal/f9fa9dc1a1fc4130b08e26c22c7a1e5f

• 参考资料

1. 《SQLite Indexed By》https://www.runoob.com/sqlite/sqlite-indexed-by.html
2. 《MYSQL中常用的强制性操作（例如强制索引）》https://www.jb51.net/article/49807.htm

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

• 题目描述

存在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’

• 代码

ALTER TABLE actor ADD COLUMN create_date datetime NOT NULL DEFAULT '0000-00-00 00:00:00'

注意：题目提示的日期格式是’0000 00:00:00’，但是应该补充完整为‘0000-00-00 00:00:00’。

?SQLite Alter 命令

ALTER TABLE database_name.table_name ADD COLUMN column_def...;

• 参考资料
  《SQLite Alter 命令》https://www.runoob.com/sqlite/sqlite-alter-command.html

41. 构造一个触发器audit_log

• 题目描述

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

?SQLite 触发器（Trigger）语法

SQLite 触发器（Trigger）是数据库的回调函数，它会在指定的数据库事件发生时自动执行/调用。

CREATE  TRIGGER trigger_name [BEFORE|AFTER] event_name 
ON table_name
BEGIN
 -- 触发器逻辑....
END;

• 构造触发器时注意以下几点：
  1、用 CREATE TRIGGER 语句构造触发器，用 BEFORE或AFTER 来指定在执行后面的SQL语句之前或之后来触发TRIGGER
  2、触发器执行的内容写出 BEGIN与END 之间
  3、可以使用 NEW与OLD 关键字访问触发后或触发前的employees_test表单记录

作者：wasrehpic
来源：https://www.nowcoder.com/questionTerminal/7e920bb2e1e74c4e83750f5c16033e2e

• 代码

CREATE TRIGGER audit_log AFTER INSERT ON employees_test
BEGIN
    INSERT INTO audit(EMP_no, NAME) VALUES (NEW.ID, NEW.NAME);
END

注意：在BEGIN 和END之间的触发的语句，结束需分号分隔。

• 参考资料
  《SQLite 触发器（Trigger）》https://www.runoob.com/sqlite/sqlite-trigger.html

42. 删除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');

• 代码

DELETE FROM titles_test WHERE id NOT IN
(SELECT MIN(id) FROM titles_test GROUP BY emp_no)

注意：题目指的是，按照emp_no分组后，只保留每组内最小的id。

?DELETE 语法

DELETE FROM 表名称 WHERE 列名称 = 值

• 参考资料
  《DELETE 语句》http://www.w3school.com.cn/sql/sql_delete.asp

三、回顾

知识点	题目
创建索引	37，39
创建视图	38
新增列	40
构建触发器	41
删除记录	42