mysql数据表字段繁体转简体_大数据分析工程师入门4SQL进阶

上篇《大数据分析工程师入门3--SQL基础》以近1万字的篇幅给大家将SQL的基础内容系统性的讲解了一遍。

本文将在其基础之上稍作补充，主要讲讲数据分析工作中可能会用到的SQL进阶知识点。

主要讲解思路如下：

第1部分：DDL。主要讲解MySQL中针对库、表、表字段的创建、修改和删除等相关操作。

第2部分：索引。内容包括为何要建立索引，如何建立不同的索引，索引的查看和删除，以及索引的注意事项和建立原则。

第3部分：explain。内容包括explain的作用，和对其所返回信息的讲解。

第4部分：主从同步。主要讲一下主从同步的意义，让大家知道有这么个概念。

第5部分：show processlist。主要讲解show processlist的作用，以及跟大家分享一下数据分析工作中的常用操作命令和技巧。

第6部分：wait_timeout。问题定位与解决。

第7部分：NULL值判断。在MySQL中除了true、false，还有一种情况就是NULL，本章节会首先对这种情况进行讲解，再强调一下实际工作中需要注意的相关问题点。

话不多说，让我们直接开始吧！

DDL(Data Definition Language)

数据分析工作中，免不了与SQL数据库打交道，尤其是对库表的使用，所以如何对库表进行创建、修改和删除，是一项基础技能。

DDL(DataDefinition Language的简写形式)是SQL语言集中负责数据结构定义与数据库对象定义的语言，由CREATE、ALTER与DROP三个语法所组成。

接下来分为库、表两部分给出示例代码供读者学习了解。

• 库相关操作

# 创建数据库CREATE DATABASE testdatabase;# 选择数据库USE testdatabase;# 删除数据库DROP DATABASE testdatabase;# 列出已有数据库SHOW DATABASES;

以上操作语句如果是在命令行中执行，需注意要以分号结束。

CREATE DATABASE关键字后面的testdatabase就是新创建的库名，库名需要是唯一的，也就意味着不能和已经存在的库重名。USE testdatabase命令的作用就是切换到testdatabase库下进行后续操作。SHOW DATABASES会列出所有当前用户能访问到的数据库库名。

• 表相关操作

# 创建表CREATE TABLE test1 (    id INT unsigned NOT NULL AUTO_INCREMENT,    name VARCHAR(225),    price DECIMAL(10,2),    PRIMARY KEY (id)) ENGINE=InnoDB DEFAULT CHARSET=utf8;# 删除表DROP TABLE test1;# 修改表字段类型ALTER TABLE test1 MODIFY name VARCHAR(100);# 添加表字段ALTER TABLE test1 ADD COLUMN age INT(3) FIRST;# 删除表字段ALTER TABLE test1 DROP age;# 修改表字段名称ALTER TABLE test1 CHANGE age age2 INT(20);# 表重命名ALTER TABLE test1 RENAME test2;# 查看表结构DESC test2;

(可左右滑动查看)

同样，以上操作语句如果是在命令行中执行，需注意要以分号结束。

CREATE TABLE 的时候，要求新指定的表名必须不存在，否则会出错，这主要是为了防止意外覆盖已有的表。

ALTER TABLE后面给出的要更改信息的表名必须存在，否则将报错。使用ALTERTABLE要极为小心，应该在进行改动前做完整的备份(表结构和数据的备份)，增加列会对数据存储造成影响，因此要尽量避免此类操作。

类似地，如果删除了不应该删除的列，可能会丢失该列中的所有数据。删除表操作同样无法撤销，所以执行该操作之前需十分谨慎小心。

另外和大家分享下，工作中常用的建表小技巧：

1. 创建表时，尽量使用一个自增的整型字段做主键。这样做，如果后续需要使用spark等框架分析这个表时，是非常方便的。

2. 创建表时，可以增加两个字段create_time和update_time。create_time存储记录的创建时间，update_time存储记录的最后一次变更时间，方便后续排查数据的变更情况。如果是使用MySQL，需要5.7及以上版本，具体语法示例如下：

CREATE TABLE test1 (    id INT  unsigned NOT NULL AUTO_INCREMENT,    name VARCHAR(225),    price DECIMAL(10,2),    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,    update_time TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,    PRIMARY KEY (id)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

3. 可以考虑增加一个逻辑删除列，存储记录的生效状态。这样在删除数据时，可以进行逻辑删除，即把状态为改为失效，而不是真的把数