31 获取select * from employees对应的执行计划

31.1 题目描述

获取select * from employees对应的执行计划

31.2 题解 explain

explain
select 
    *
from 
    employees

• explain模拟优化器执行SQL语句，在5.6以及以后的版本中，除过select，其他比如insert，update和delete均可以使用explain查看执行计划，从而知道mysql是如何处理sql语句，分析查询语句或者表结构的性能瓶颈。
  作用

1、表的读取顺序
2、数据读取操作的操作类型
3、哪些索引可以使用
4、哪些索引被实际使用
5、表之间的引用
6、每张表有多少行被优化器查询

32 将employees表的所有员工的last_name和first_name拼接起来作为Name

32.1 题目描述

将employees表的所有员工的last_name和first_name拼接起来作为Name，中间以一个空格区分

CREATE TABLE `employees` ( `emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));

32.2 题解

32.2.1 SQLLite字段拼接 ||

select
    last_name || ' ' || first_name
from 
    employees

32.2.2 MySQL字段拼接 concat/concat_ws

select 
    CONCAT_WS(space(1),last_name,first_name) as Name 
from 
    employees; 

• 参考文章：MySQL中的字段拼接 concat() concat_ws() group_concat()函数

32 创建一个actor表，包含如下列信息

32.1 题目描述

创建一个actor表，包含如下列信息

32.2 题解

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 timestamp not null default (datetime('now','localtime')) 
)

33 批量插入数据

33.1 题目描述

对于表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')))

33.2 题解

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

34 批量插入数据，不使用replace操作

34.1 题目描述

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

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

34.2 题解

34.2.1 SQLLite(insert or ignore into)

insert or ignore into
    actor
values('3','ED','CHASE','2006-02-15 12:34:33')

34.2.2 MySQL(insert ignore into)

insert or ignore into
    actor
values('3','ED','CHASE','2006-02-15 12:34:33')

-- 如果不存在则插入，如果存在则忽略
INSERT OR IGNORE INTO tablename VALUES(...);
 
-- 如果不存在则插入，如果存在则替换
INSERT OR REPLACE INTO tablename VALUES(...);
 
-- 这里指的存在表示的是unique属性的列值存在的情况下，unique表示键值唯一

35 创建一个actor_name表

35.1 题目描述

对于如下表actor，其对应的数据为:

创建一个actor_name表，将actor表中的所有first_name以及last_name导入改表。 actor_name表结构如下：

35.2 题解

35.2.1 我的题解 分2步

create table actor_name(
    first_name varchar(45) not null
    ,last_name varchar(45) not null
);

insert into actor_name
    select 
        first_name
        ,last_name
    from 
        actor;

35.2.2 参考题解

• sqllite

create table actor_name as
select 
    first_name
    ,last_name 
from actor;

• mysql

create table actor_name
select 
    first_name
    ,last_name 
from actor;

36* 对first_name创建唯一索引uniq_idx_firstname

36.1 题目描述

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

36.2 题解

• 创建索引的语句CREATE [UNIQUE/...] INDEX indexName ON tableName(colName);

create unique index uniq_idx_firstname on actor(first_name);
create index idx_lastname on actor(last_name);

37 针对actor表创建视图actor_name_view

37.1 题目描述

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

37.2 题解

• 和35 创建表相同思路，其实视图也可以看作是一张表（临时表）

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

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

38.1 题目描述

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

38.2 题解

38.2.1 SQLlite(indexed by [idx_name])

select
    * 
from 
    salaries
indexed by
    idx_emp_no 
where 
    emp_no = 10005

38.2.2 mysql(force index [idx_name])

select
    * 
from 
    salaries
force index
    idx_emp_no 
where 
    emp_no = 10005

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

39.1 题目描述

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

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

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'

39.2 题解(alter table ... add...)

alter table actor add
    create_date datetime not null default '0000-00-00 00:00:00'

40* 构造一个触发器audit_log

40.1 题目描述

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

40.2 题解

create trigger audit_log after insert on employees_test
begin
    insert into audit values(NEW.id,NEW.name); -- 这里的id和name是触发后employees_test的id、name
end;

1. 创建触发器使用语句：CREATE TRIGGER trigname;
2. 指定触发器触发的事件在执行某操作之前还是之后，使用语句：BEFORE/AFTER [INSERT/UPDATE/ADD] ON tablename
3. 触发器触发的事件写在BEGIN和END之间；
4. 触发器中可以通过NEW获得触发事件之后步骤2对应的tablename的相关列的值，OLD获得触发事件之前的步骤2对应的tablename的相关列的值