[牛客数据库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
仅供参考~~~
