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

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/slowbirdoflsh/p/11219787.html