wonderful-sql：(1)环境搭建、初识数据库

教程地址：https://linklearner.com/learn/detail/70

《SQL基础教程（第2版）》：https://www.ituring.com.cn/book/1880

《DataWhale SQL 基础教程》：https://fastly.jsdelivr.net/gh/datawhalechina/wonderful-sql@main/materials/SQL%E5%9F%BA%E7%A1%80%E6%95%99%E7%A8%8B-V2.0.pdf

wonderful-sql：https://github.com/datawhalechina/wonderful-sql

这是笔者参与开源学习《wonderful-sql》的学习记录，希望通过分享自己的学习过程能够帮助到零基础的同学快速入门 SQL。在笔记的开头部分，笔者想请大家同我一样扪心自问：我为什么要学 SQL？ 或许您会说“群友在学，我也想学啊！”，如果仅仅只是“想学”，那笔者觉得可以放弃了，何必“从人门到放弃呢”？当然，这也不是绝对的，“想学”是我们开始学习的动力，往往更深层次的原因是我们需要，就笔者而言，目前面临着被低代码平台“革命”的窘境，我司会 SQL 的产品经理已经替代了我们一部分工作，用 BI 工具加 SQL 完成了好几个系统了。因此，笔者也只能硬着头皮赶紧补一补 SQL，以挽救我可能不长久的饭碗了。您呢，为什么要学 SQL？

本次学习借助开源学习社区 DataWhale，不仅有优秀的助教辅导，还有优秀的小伙伴一起学习，计划大概如下：

本地环境搭建

教程中以 MySQL 为例已经讲的很详细了：https://linklearner.com/learn/detail/70?detail=104&index=2，如果需要使用其他数据库如 PostgreSQL 请参考B站 Master_lisa 的视频教程，相信各RDBMS（PostgreSQL/DB2/MySQL/Oracle/SQL Server 软件安装步骤大体都差不多。

笔者也尝试在 Windows 11 专业版 Insider Preview 上安装 MySQL 8.0.34, MySQL 官网下载速度比某网盘要快，实在受不了网盘的龟速。
同样的，作为初学者，笔者也是选择的 Full 模式安装，值得注意的是在 Type and NetWorking这个步骤我们需要勾选最下边的Show Advanced and Logging Options框, 然后点击 Next，因为 MySQL 8.0 加强了安全等级，但在我们学习过程中会用到第三方工具连接，因此会选用等级低一点的Use Legacy Authentication Method(Retain MySQL 5.x Compatibility)，也就是沿用 MySQL 5.x 的加密方式。

接着是创建 root 用户的密码，为了养成好的习惯，笔者不建议使用简单的密码，那就设置为强密码 DataWhale@1024 吧，当然请保管好数据库重要信息(PS:可千万别提交到公域)。

此外由于windows系统是大小写不敏感的, 请大家务必使用第一个选项Lower Case.

安装完毕可进行测试连接。

最后一步可复制安装信息，了解完整的安装过程。

安装过程结束之后会默认打开 SQL 命令行窗口，可以通过 help 指令查看相关帮助。

我们可以使用 MySQL Command Line Cli 进行本机测试连接，

也可以安装 Navicat 等第三方工具进行连接，此处以 Navicat Lite 为例

初识数据库

数据库的概念

Oracle 给的定义：

数据库是结构化信息或数据的有序集合，一般以电子形式存储在计算机系统中。 通常由数据库管理系统(DBMS) 来控制。 在现实中，数据、DBMS 及关联应用一起被称为数据库系统，通常简称为数据库。

笔者的理解，数据库是结构化数据及其承载系统的统称，比如商品数据和存储数据的 MySQL。人们常说的删库跑路，往往一个rm -rf /* 将数据、MySQL 删得干干净净。

数据库的分类

参考链接：https://cs.fit.edu/~pbernhar/dbms.html

• SQL 语句分类

• SQL的基本书写规则

1. SQL语句要以分号（ ; ）结尾
2. SQL里有保留字和非保留字之分。根据标准，保留字决不能用做其他标识符。
3. 标识符的命名规范：
   标识符需要为字母、下划线、数字（0-9）或美元符号（$）。
   标识符必须以字母（a-z）或下划线（_）开头。
4. SQL 的注释符为 --

数据库基本语法

我们打开本地的 MySQL 命令行终端登入root账号，并通过Navicat lite连接数据库，开始SQL基本语法的学习。

• 查看数据库

SHOW DATABASES;

当前MySQL版本（8.0.34）内置了一些默认的数据库表，不过笔者对 world 这个库里的 country 表的内容表示严正抗议 ，好在不影响学习。

• 创建数据库

CREATE DATABASE <数据库名称> ;

如创建一个 shop 库,注意库名称的命名规范，并以半角状态下的 ;结尾

CREATE DATABASE shop;

• 创建表

CREATE TABLE < 表名 >
( < 列名 1> < 数据类型 > < 该列所需约束 > ,
  < 列名 2> < 数据类型 > < 该列所需约束 > ,
  < 列名 3> < 数据类型 > < 该列所需约束 > ,
  < 列名 4> < 数据类型 > < 该列所需约束 > ,
  .
  .
  .
  < 该表的约束 1> , < 该表的约束 2> ,……);

当我们直接创建表的时候，会抛出异常，因为我们没有先选择库：

ERROR 1046 (3D000): No database selected

现在我们通过USE <数据库>再执行新增表命令创建一个带有字段的商品表：

CREATE TABLE product
(product_id CHAR(4) NOT NULL,
 product_name VARCHAR(100) NOT NULL,
 product_type VARCHAR(32) NOT NULL,
 sale_price INTEGER,
 purchase_price INTEGER,
 regist_date DATE,
 PRIMARY KEY (product_id));

如果是手敲的话，注意关键字的拼写以及符号，需要执行时才以;结尾。
上面的语句翻译成大白话就是：创建商品表，包含唯一标识、商品名称、商品类别、价格、折扣价、上架日期，当然还定义了一些约束条件，比如商品名称最大长度为100且不能为NULL。

• 删除表

DROP TABLE < 表名 > ;

我们使用SHOW TABLES查看当前数据库下的表，通过DROP TABLE product删除 product表：

DROP TABLE product;

此时，数据库shop下就没有表了：

• 添加列

ALTER TABLE < 表名 > ADD COLUMN < 列的定义 >;

为了演示后续的操作，我们新建一个 Addressbook 表：

USE shop;
CREATE TABLE addressbook
(regist_no INTEGER NOT NULL,
name VARCHAR(128) NOT NULL,
address VARCHAR(256) NOT NULL,
tel_no CHAR(10),
mail_address CHAR(20),
PRIMARY KEY (regist_no));

接着我们为 Addressbook 添加 postal_code(邮政编码)，定长字符串类型（长度为 8），不能为 NULL：

ALTER TABLE addressbook ADD postal_code CHAR(8) NOT NULL;

• 删除列

ALTER TABLE < 表名 > DROP COLUMN < 列名 >;

我们尝试删除表中的 tel_no：

ALTER TABLE addressbook DROP COLUMN  tel_no;

此时如果navicat lite 中已经打开了表，无论怎么刷新都无法发现 tel_no 已经被删除了，需要重新打开表。

• 删除行

-- 注意添加 WHERE 条件，否则将会删除所有的数据
DELETE FROM product WHERE COLUMN_NAME='XXX';

• 清空表

-- truncate 相比 drop、delete 清除数据要快
TRUNCATE TABLE <表名>

冷知识：drop和delete语句是逐行删除数据，即逐行检查要删除的数据，并将其从表中删除。这个过程需要一定的时间，尤其是当表包含大量数据时，删除时间会相应增加。
而truncate语句是直接截断表，将表中的所有行都删除，不保留任何数据。由于是直接截断表，因此truncate操作的速度比drop和delete快。但是，truncate操作是不可逆的，一旦执行就无法恢复数据，因此在使用之前需要谨慎考虑。

• 更新数据

-- 注意添加 where 条件，否则将会将所有的行按照语句修改
UPDATE <表名>
   SET <列名> = <表达式> [, <列名2>=<表达式2>...]  
 WHERE <条件>  -- 可选，非常重要
 ORDER BY 子句  --可选
 LIMIT 子句; --可选

• 插入数据

-- 对表进行全列 INSERT 时，可以省略表名后的列清单
INSERT INTO <表名> (列1, 列2, 列3, ……) VALUES (值1, 值2, 值3, ……);  

• 复制表

INSERT INTO <复制表表名>(列1, 列2, 列3, ……)
SELECT 列1, 列2, 列3, ……
  FROM <被复制表表名>; 

索引

我们常常听说“亿级数据秒级查询”，索引是实现基础。
创建索引的语法：

-- 建表时创建索引
-- INDEX关键字可以改用其他关键字，如FULLTEXT、SPATIAL和UNIQUE等，具体取决于数据库管理系统的支持情况
CREATE TABLE <表名>(  
<示例字段 ID> INT NOT NULL,   
<示例字段 username> VARCHAR(16) NOT NULL,  
INDEX [索引名] (username(length))  
);  

-- 索引也可以在表创建之后单独创建
CREATE INDEX indexName ON <表名> (列名);
-- 或者
ALTER TABLE tableName ADD INDEX indexName(columnName)

冷知识：索引可以提高查询性能，但会占用磁盘空间并影响数据修改的操作，因此应该根据具体的需求和情况来决定是否使用索引，以及使用哪种类型的索引

练习

1.1 编写一条 CREATE TABLE 语句，用来创建一个包含表 1-A 中所列各项的表 addressbook （地址簿），并为 regist_no （注册编号）列设置主键约束

上文中已经写过：

CREATE TABLE addressbook
(regist_no INTEGER NOT NULL,
name VARCHAR(128) NOT NULL,
address VARCHAR(256) NOT NULL,
tel_no CHAR(10),
mail_address CHAR(20),
PRIMARY KEY (regist_no));

1.2 假设在创建练习1.1中的 addressbook 表时忘记添加如下一列 postal_code （邮政编码）了，请编写 SQL 把此列添加到 addressbook 表中。
列名 ： postal_code
数据类型 ：定长字符串类型（长度为 8）
约束 ：不能为 NULL

ALTER TABLE addressbook ADD postal_code CHAR(8) NOT NULL;

1.3 编写SQL 语句来删除 addressbook 表。

drop table addressbook;
-- 或者
delete table addressbook;
-- 或者
truncate table addressbook

1.4 是否可以编写 SQL 语句来恢复删除掉的 Addressbook 表？

如果您在删除 Addressbook 表时没有执行 COMMIT 操作，则可以尝试使用 SQL语句来恢复该表。

-- 回滚未提交的更改  
ROLLBACK;  
  
-- 从以前版本的数据中恢复表  
CREATE TABLE Addressbook_temp LIKE Addressbook;  
INSERT INTO Addressbook_temp SELECT * FROM Addressbook;

题外话

谈到数据库，不得不说说个人信息泄露的问题，感觉大部分人都是“裸奔”，身份证、电话号码、住址等重要信息被扒的一览无余。归集到底都是数据库泄露导致的，多年以前笔者无意间在 Github 上发现过有开发者直接将数据库重要信息“开源”，这简直比撞库严重得多。好在，截止发稿时，Github 已经优化了搜索，笔者从一些泄露扫描工具 issue 来看，应该是 4 月就进行了改版，没那么容易搜到泄露信息了。
记住：小心使得万年船!