[牛客数据库SQL实战] 31~40题及个人解答

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

-- 执行计划 EXPLAIN
EXPLAIN SELECT * FROM employees;

运行时间：19ms

占用内存：3424k

• EXPLAIN 介绍
  EXPLAIN是一个执行SQL语句的模拟优化器，可以通过EXPALIN来查看增删查改操作的执行计划，即MySQL是如何处理sql语句，分析查询语句或者表结构的性能。
• 作用:
  通过查看EXPALIN结果，可以知道以下信息
  1、表的读取顺序
  2、数据读取操作的操作类型
  3、哪些索引可以使用
  4、哪些索引被实际使用
  5、表之间的引用
  6、每张表有多少行被优化器查询

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

-- 这里是使用SQLite语法来审核的。。。
SELECT last_name || ' ' || first_name AS Name FROM employees;

运行时间：29ms

占用内存：3308k

• MySQL环境下拼接字符

-- 取巧 直接手动加空格作为分隔符 
SELECT CONCAT(last_name, ' ' ,first_name) AS Name 
FROM employees;
-- 使用CONCAT_WS()函数  
SELECT CONCAT_WS(' ', last_name, first_name) AS Name
FROM employees;

• MySQL拼接字符串函数用法

• CONCAT(str1,str2,…)
  返回结果为连接参数产生的字符串。如有任何一个参数为NULL，则返回值为NULL。
• CONCAT_WS(separator,str1,str2,...)
  CONCAT()的特殊形式。第一个参数是其它参数的分隔符。分隔符的位置放在要连接的两个字符串之间。
  分隔符可以是一个字符串，也可以是其它参数。

mysql多个字段拼接
concat、concat_ws、group_concat函数用法

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

列表	类型	是否为NULL	含义
actor_id	smallint(5)	not null	主键id
first_name	varchar(45)	not null	名字
last_name	varchar(45)	not null	姓氏
last_update	timestamp	not null	最后更新时间，默认是系统的当前时间

-- 建表 字段名+类别
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'))
)

运行时间：16ms

占用内存：3320k

• 注意：SQLite 3.7.9 不支持大写的类型声明。。。VARCHAR、SMALLINT、TIMESTAMP是不支持的

-- sakila示例数据库已有actor表
CREATE TABLE myactor (
	actor_id SMALLINT(5) NOT NULL,
	first_name VARCHAR(45) NOT NULL,
	last_name VARCHAR(45) NOT NULL,
	-- 注意不能使用CURRENT_TIMESTAMP()
	last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	PRIMARY KEY(actor_id)	
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

• MySQL 获取当前时间函数：

current_timestamp() localtime() localtimestamp() sysdate()

• MySQL 获取当前日期：

curdate() = current_date()

34. 对于表actor批量插入如下数据

actor_id	first_name	last_name	last_update
1	PENELOPE	GUINESS	2006-02-15 12:34:33
2	NICK	WAHLBERG	2006-02-15 12:34:33

-- 使用insert into语法
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');

运行时间：17ms

占用内存：3320k

-- 使用union select形式
INSERT INTO actor
SELECT 1,'PENELOPE', 'GUINESS', '2006-02-15 12:34:33'
UNION SELECT 2,'NICK', 'WAHLBERG', '2006-02-15 12:34:33';

运行时间：22ms

占用内存：3292k

• 注意：此时查看表内数据会发现last_update字段数据和插入数据不同 刚好相差14小时

• 插入数据
  1|PENELOPE |GUINESS |2006-02-15 12:34:33|
  2|NICK |WAHLBERG |2006-02-15 12:34:33|
• 读取数据
  1|PENELOPE |GUINESS |2006-02-16 02:34:33|
  2|NICK |WAHLBERG |2006-02-16 02:34:33|

• 以上错误是在DBeaver环境下。。。MySQL命令行显示正常

DBeaver 客户端中时间显示问题解决:https://www.cnblogs.com/peng18/p/9260690.html

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

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

-- 要达到表内没有该数据就插入，有就忽略的效果 不使用replace
-- 此为SQLite环境下
INSERT OR IGNORE INTO actor
VALUES(3,'ED', 'CHASE', '2006-02-15 12:34:33');

运行时间：21ms

占用内存：3440k

• MySQL环境下 myactor为个人建表

-- 表内没有该数据就插入，有就忽略的效果 不使用replace
INSERT IGNORE INTO myactor
VALUES(3,'ED', 'CHASE', '2006-02-15 12:34:33');

-- 使用replace函数
REPLACE INTO myactor(actor_id, first_name, last_name, last_update)
VALUES(3,'ED', 'CHASE', '2006-02-15 12:34:33');

[MySQL]MySQL数据库中插入操作时先判断数据是否存在，不存在则插入数据，存在则更新数据

36. 创建一个actor_name表，将actor表中的所有first_name以及last_name导入改表。

列表	类型	是否为NULL	含义
first_name	varchar(45)	not null	名字
last_name	varchar(45)	not null	姓氏

-- 从一个表的基础上创建另一个表
CREATE TABLE actor_name AS SELECT first_name, last_name FROM myactor;

运行时间：21ms

占用内存：3288k

• 注意：如此建立的表格只含有基本的数据类型，而不包含其他表结构，比如：主键、索引等结构。

MySQL官方文档：CREATE TABLE ... SELECT

37. 针对actor表，对first_name创建唯一索引uniq_idx_firstname，对last_name创建普通索引idx_lastname

-- 对first_name创建唯一索引uniq_idx_firstnam
CREATE UNIQUE INDEX uniq_idx_firstname ON actor(first_name);
-- 对last_name创建普通索引idx_lastname
CREATE INDEX idx_lastname ON actor(last_name);

运行时间：16ms

占用内存：3424k

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

-- 创建视图view actor_name_view
CREATE VIEW actor_name_view(first_name_v, last_name_v) 
	AS SELECT first_name, last_name FROM actor;

运行时间：25ms

占用内存：3424k

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

-- SQLite中,使用 INDEXED BY 语句进行强制索引查询
SELECT * FROM salaries INDEXED BY idx_emp_no WHERE emp_no=10005;

运行时间：17ms

占用内存：3428k

-- MySQL中，使用 FORCE INDEX 函数进行强制索引查询
EXPLAIN SELECT * FROM salaries FORCE INDEX(idx_emp_no) WHERE emp_no=10005;
SELECT * FROM salaries WHERE emp_no=10005; -- 4ms (+5ms)
SELECT * FROM salaries FORCE INDEX(idx_emp_no) WHERE emp_no=10005; -- 2ms (+1ms)

MySQL force Index 强制索引概述
Mysql中的force index和ignore index

40. 针对actor表，在last_update后面新增加一列名字为create_date, 类型为datetime, NOT NULL，默认值为'0000 00:00:00'

-- 修改表结构
ALTER TABLE actor 
    ADD create_date datetime DEFAULT '0000-00-00 00:00:00' NOT NULL;

运行时间：26ms

占用内存：3432k

• 两个注意点
  1. 题目给的默认值是有问题的 不是'0000 00:00:00' 而是'0000-00-00 00:00:00'
  2. SQLite3.7.9 不支持大写数据类型。。。

• 报错：Invalid default value for 'create_date'
  如果你在MySQL实际环境下运行该代码，将会报以上错误
  这是因为sql_model有NO_ZERO_DATE的选项，在该模式下'0000-00-00 00:00:00'是个无效值

mysql> SHOW VARIABLES LIKE 'sql_mode';
| sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |

可以将其换成其他日期， 比如'1970-01-01 00:00:01' UTC 到 '2038-01-19 03:14:07' UTC，即可正常运行

Invalid default value for 'create_date' timestamp field

完整的个人练习代码

我的练习SQL代码已经上传至Github：https://github.com/slowbirdoflsh/newcode-sql-practice
仅供参考~~~