【SQL每日一练】day16：索引、视图、触发器、表操作练习

题目一：批量插入数据，不使用replace操作

题目要求：

题目已经先执行了如下语句:

drop table if exists 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);
insert into actor values ('3', 'WD', 'GUINESS', '2006-02-15 12:34:33');

对于表actor插入如下数据,如果数据已经存在，请忽略(不支持使用replace操作)

actor_id	first_name	last_name	last_update
'3'	'ED'	'CHASE'	'2006-02-15 12:34:33'

思路：

        为了插入数据时避免重复 actor_id 的数据覆盖，可以使用 MySQL 提供的 INSERT IGNORE 或 INSERT ... ON DUPLICATE KEY UPDATE。但是，由于题目要求“忽略已经存在的数据”，我们可以使用 INSERT IGNORE，它会在遇到重复键时忽略插入操作。

运行代码示例：

INSERT IGNORE INTO actor (actor_id, first_name, last_name, last_update)
VALUES ('3', 'ED', 'CHASE', '2006-02-15 12:34:33');

题目二：对first_name创建唯一索引uniq_idx_firstname

题目要求：

针对如下表actor结构创建索引：

(注:在 SQLite 中,除了重命名表和在已有的表中添加列,ALTER TABLE 命令不支持其他操作，

mysql支持ALTER TABLE创建索引)

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

对first_name创建唯一索引uniq_idx_firstname，对last_name创建普通索引idx_lastname

思路：

        创建唯一索引：ALTER TABLE 表名 ADD UNIQUE INDEX 索引名 (列名); 

        创建索引：ALTER TABLE 表名 ADD INDEX 索引名 (列名);

运行代码示例：

-- 为 first_name 列创建唯一索引
ALTER TABLE actor
ADD UNIQUE INDEX uniq_idx_firstname (first_name);

-- 为 last_name 列创建普通索引
ALTER TABLE actor
ADD INDEX idx_lastname (last_name);

题目三：针对actor表创建视图actor_name_view

题目要求：

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

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

 

后台会插入2条数据:

insert into actor values ('1', 'PENELOPE', 'GUINESS', '2006-02-15 12:34:33'), ('2', 'NICK', 'WAHLBERG', '2006-02-15 12:34:33');

然后打印视图名字和插入的数据

思路：

        创建视图：CREATE VIEW视图名 AS 视图内容

运行代码示例：

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

题目要求：

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

后台会检查是否使用强制索引

思路：

        强制索引FORCE INDEX。FORCE INDEX强制查询优化器使用指定的命名索引。查询优化器是MySQL数据库服务器中的一个组件，它为SQL语句提供最佳的执行计划。查询优化器使用可用的统计信息来提出所有候选计划中成本最低的计划。
书写顺序：

SELECT……
FROM ……
FORCE INDEX(index_name)
WHERE……

运行代码示例：

SELECT * 
FROM salaries 
FORCE INDEX (idx_emp_no)  -- 强制使用索引 idx_emp_no
WHERE emp_no = 10005;

题目五：在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，默认值为'2020-10-01 00:00:00'

思路：

        新增字段：ALTER TABLE <表名> ADD COLUMN <新字段名> <数据类型> [约束条件] [FIRST|AFTER 已存在的字段名];

运行代码示例：

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

题目六：构造一个触发器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

);

后台会往employees_test插入一条数据:

INSERT INTO employees_test (ID,NAME,AGE,ADDRESS,SALARY)VALUES (1, 'Paul', 32, 'California', 20000.00 );

然后从audit里面使用查询语句:

select * from audit;

思路：

        触发器的创建：

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 包含的多条语句，每条语句结束要分号结尾。

运行代码示例：

-- 创建触发器 audit_log
CREATE TRIGGER audit_log
AFTER INSERT ON employees_test
FOR EACH ROW
BEGIN
    -- 将新插入的数据插入到 audit 表中
    INSERT INTO audit (EMP_no, NAME)
    VALUES (NEW.ID, NEW.NAME);
END;

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

题目要求：

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

(以下2个表已经创建了)

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

后台会判断是否创建外键约束，创建输出1，没创建输出0

思路：

        创建外键：

ALTER TABLE table_name
constraint 约束名 foreign key(外键列)
references 主键表(主键列)

运行代码示例：

ALTER TABLE audit
ADD CONSTRAINT fk_emp_no
FOREIGN KEY (EMP_no) REFERENCES employees_test(ID);