SQL（Structured Query Language）教程（下）

目录

第十四章 CREATE DB & ALTER TB

14.1 创建和删除数据库 Creating and Dropping Databases 

14.2 创建表 Creating Tables 

14.3 修改 Altering Tables 

第十五章 创建关系 Creating Relationships 

15.1 通过创建外键来创建表间关系

15.2 更改主键和外键约束Altering Primary and Foreign Key Constraints  

第十六章 高级更改

16.1 字符集和排序规则 Character Sets and Collations 

16.2 存储引擎 Storage Engines 

第十七章、高效的索引 Indexing for High Performance

17.1 介绍 Introduction 

17.2 索引 Indexes 

17.3 创建索引 Creating Indexes 

​         17.4 查看索引 Viewing Indexes 

17.5 前缀索引 Prefix Indexes

17.6 全文索引 Fulltext Indexes 

17.7 组合索引 Composite Indexes 

17.8 组合索引的列顺序 Order of Columns in Composite Indexes 

17.9 索引无效时 When Indexes are Ignored 

17.10 使用索引排序 Using Indexes for Sorting 

17.11 覆盖索引 Covering Indexes 

17.12  维护索引 Index Maintenance 

1、重复索引（duplicate index）：

2.、冗余索引（redundant index）：

3、 无用索引（unused index）:

17.13 性能最佳实践 (文档) Performance Best Practices

第十八章、保护数据库 Securing Databases 

1 创建一个用户 Creating a User 

3. 查看用户 Viewing Users 

4. 删除用户Dropping Users

5. 修改密码 Changing Passwords

6. 权限许可 Granting Privileges 

7. 查看权限 Viewing Privileges 

8. 撤销权限 Revoking

---

第十四章 CREATE DB & ALTER TB

14.1 创建和删除数据库 Creating and Dropping Databases 

用 workbench 的向导来创建和修改数据库能够提高效率，但作为 DBA (Database Administrator 数据库管理员)，你必须要能理解并审核相关代码，确保其不会对数据库有不利影响，而且也有能力手动写代码完成创建和修改数据库的操作，可以不依赖工具。

这节课讲创建和删除数据库：

CREATE DATABASE IF NOT EXISTS sql_store2;
DROP DATABASE IF EXISTS sql_store2

14.2 创建表 Creating Tables 

案例

以在 sql_store2 中建表 customers 为例，注意创建表之前还是要先用 USE 选择数据库，不然不知道你是要在哪个数据库中创建表

USE sql_store2;

DROP TABLE IF EXISTS customers;
CREAT TABLE customers
-- 没有就创建，有的话就推倒重建

或

CREATE TABLE IF NOT EXISTS customers
-- 没有就创建，有的话就不做改变

(
    -- 只挑选几个字段来建立
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL, 
    points INT NOT NULL DEFAULT 0,
    email VARCHAR(255) NOT NULL UNIQUE
    -- UNIQUE 确保 email 值唯一，即每个用户的 email 必须不一样
)

注意

左侧栏导航窗口选择某表中的列时，下面的 Object Info 可以查看列的数据类型

小结

• 如上，创建对象（不管是数据库还是表）有两种方式，DROP …… IF EXIXTS ……； CREAT …… 和 CREAT …… IF NOT EXISTS ……，注意两种方式的区别在于，当原对象存在时，前者是推倒重建，后者是保持原状放弃创建
• 括号中设置列的方式为 列名 数据类型 各种列性质，列间逗号分隔，常用的列性质有 PRIMARY KEYNOT NULLDEFAULT 0UNIQUE

14.3 修改 Altering Tables 

这节学习如何更改已存在的表，包括增删列和修改列类型和属性

USE sql_store2;
ALTER TABLE customers
    ADD [COLUMN] last_name VARCHAR(50) NOT NULL [AFTER first_name],
    ADD city VARCHAR(50) NOT NULL,
    MODIFY [COLUMN] first_name VARCHAR(60) DEFAULT '',
    DROP [COLUMN] points;

COLUMN 是可选的，有的人喜欢加上以增加可读性

AFTER first_name 是可选的，不加的话默认将新列添加到最后一列

ADD 增加列

DROP 删除列

MODIFY 修改已有列时其实感觉好像是是重置该列（= DROP + ADD），所以注意要列出该列全部类型和属性信息，如上例中将 first_name 修改为 VARCHAR(60) 类型并将默认值修改为空字符串''，但忘了加 NOT NULL，刷新后发现 first_name 不再有 NOT NULL 属性

列名最好不要有空格，但如果有的话可用反引号包裹，如 `last name`

注意

修改表永远不要直接在生产环境中进行，要首先在测试环境进行，确保没有错误和不良影响后再到生产环境进行修改

第十五章 创建关系 Creating Relationships 

15.1 通过创建外键来创建表间关系

案例

第26节在新的 store2 数据库中创建了 customers 表，这里我们接着创建 orders 表，并在表中添加 customer_id 外键来建立表间关系

CREATE DATABASE IF NOT EXISTS sql_store2;
USE sql_store2;
DROP TABLE IF EXISTS customers;
CREATE TABLE customers
(……);  
-- 在Workbench里可点击加减号来展开或收起代码块

DROP TABLE IF EXISTS orders;
CREATE TABLE orders
(
    order_id    INT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date  DATE NOT NULL,
    -- 在添加完所有列之后添加外键
    FOREIGN KEY fk_orders_customers (customer_id)
        REFERENCES customers (customer_id)
        ON UPDATE CASCADE
        -- 也有人主张用 NO ACTION / RESTRICT
        ON DELETE NO ACTION
        -- 禁止删除有订单的顾客
)

外键名的命名习惯：

fk（foreign key 的缩写）_子表名_父表名

设置外键的语法结构：

FOREIGN KEY 外键名 (外键字段)
        REFERENCES 父表 (主键字段)
        -- 设置外键约束：
        ON UPDATE CASCADE
        ON DELETE NO ACTION

关于外键约束

ON DELETE 设置为 NO ACTION / RESTRICT 可以防止删除有的订单的顾客，这没什么问题；而对于 ON UPDATE，也有人主张同样应该设为 NO ACTION / RESTRICT，因为主键是永远不应该被更改的，理论上Mosh支持这个观点，但实际世界并不完美，由于意外或系统错误等原因，主键是有可能改变的，所以Mosh一般设置为CASCADE，让外键随着主键的更改而更改，但你要设置为 NO ACTION / RESTRICT 也同样有道理。另外，想查看外键约束的可选项以及想通过菜单选择来更改外键约束的话，可以打开某列的设计模式，在 Foreign Keys 标签页里进行选择

表间依赖

还有注意一点，运行以上SQL文件从头创建 sql_store2数据库以及customers和orders两张表时，第一次运行没问题，但要再次运行的化会报以下错误：

/* Error Code: 1217. Cannot delete or update a parent row: 
a foreign key constraint fails*/

这是因为建立主外键关系后， customers 现在和 orders 是父子表，orders 表依赖于 customers 表，所以必须先删除 orders 表才能删除 customers 表，所以应该把 orders 表的 DROP 语句放到最前面：

CREATE DATABASE IF NOT EXISTS sql_store2;
USE sql_store2;
-- 删表时先删子表
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS customers;

-- 建表时先建父表（我觉得应该是）
CREATE TABLE customers
(……);

CREATE TABLE orders
(……);

这样运行再多次也没问题了，总是可以从头建立sql_store2数据库和customers、orders两张表 （不过为什么不在最开头创建数据库的语句里用 DROP DATABASE IF EXISTS sql_store2; CREATE DATABASE sql_store2 这种直接将整个数据库推倒重建的方式呢？）

15.2 更改主键和外键约束Altering Primary and Foreign Key Constraints  

这一节学习如何在已经存在的表间创建和删除关系，还是用 ALTER TABLE 语句 + ADD、DROP 关键词，和27节修改表里一样，只不过这里增删的是不是列而是外键：

USE sql_store2;
ALTER TABLE orders 
    DROP FOREIGN KEY fk_orders_customers,  -- orders_ibfk_1
    ADD FOREIGN KEY fk_orders_customers (customer_id)
        REFERENCES customers (customer_id)
        ON UPDATE CASCADE
        ON DELETE NO ACTION;

注意

不知道为什么，我这里不管是之前第28节创建orders表时设置外键还是这里通过修改ADD增加外键，外键名明明写的是 fk_orders_customers，实际上都会变成 orders_ibfk_1，要去设计模式手动修改才行，可能是bug

另外也可以通过类似的 ALTER TABLE 语句增删主键：

USE sql_store2;
ALTER TABLE orders
    ADD PRIMARY KEY (order_id,……,……),
    -- 可设置多个主键，在括号内用逗号隔开
    DROP PRIMARY KEY;
    -- 删除主键不用声明，会直接删除所有主键

另外，像增删主键这种既可以用菜单点击也可以用代码运行实现的操作（Workbench里这种操作相当多了），当忘记相关SQL代码写法时，可以通过菜单点击方式操作然后在 Review the SQL script 那一步看一看，就知道代码怎么写的了

第十六章 高级更改

16.1 字符集和排序规则 Character Sets and Collations 

 某个表的默认字符集、某个列的默认字符集

字符是以数字序列的形式储存于电脑中的，字符集是数字序列与字符相互转换的字典，不同的字符集支持不同的字符范围，有些支持拉美语言字符，有些也支持亚洲语言字符，有些支持全世界所有字符，查看MySQL支持的所有字符集：

SHOW CHARSET;

其中 armscii8 支持亚美尼亚语，big5 支持繁体中文，gb2312 和 gbk 支持简体中文，而 utf-8支持全世界的语言，utf-8 也是MySQL自版本5之后的默认字符集。

还可以看到字符集描述，默认排序规则，最大长度

排序规则（collation n. 校对，整理，排序规则）指的是某语言内字符的排序方式，utf-8 的默认排序规则是 utf8_general_ci，其中 ci 表示 case insensitive 大小写不敏感，即MySQL在排序时不会区分大小写，这在大部分时候都是适用的，比如用户输入名字的时候大小写不固定，我们希望只按照字符顺序而不管大小写来对名字进行排序。总之，99.9% 的情况下都不需要更改默认排序规则。

最大长度指的是对该字符集来说，给每个字符预留的最大字节数，如 latin1 是 1 字节，utf-8 就是 3 Byte，前面说过，在utf-8里，拉丁字符使用 1 字节，欧洲和中东字符使用 2 字节，亚洲语言的字符使用 3 字节，所以 utf-8 给每个字符预留 3 字节。

对于字符集来说，大部分时候用默认的 utf-8 就行了。但有时，我们可以通过更改字符集来减少空间占用，例如，我们某个特定的应用（对应的数据库）/特定表/特定列是只能输入英文字符的，那如果将该列的字符集从 utf-8 改为 latin1，占用空间就会缩小到原来的 1/3，以字段类型为 CHAR(10)（固定预留10个字符）且有 1 百万条记录为例，占用空间就会从约 30MB 减到 10MB。接下来讲如何用菜单和代码方式更改库/表/列的字符集。

菜单方式更改字符集

右键 sql_store2 数据库，点击 Schema Inspector，可以查看整个数据库以及各表各列的字符集和排序规则，Schema Inspector 也能查看该数据库的主键外键、视图、触发器、储存程序、事务、函数等各方面情况

要修改库或者表和列的字符集，直接点开库或者表的设计模式（扳手按钮）在里面选择更改即可，一般我们会让表和列的字符集和整个库保持一致，毕竟一个应用要不然是国际化的要不然就不是。

代码方式更改字符集

总的来说就是将设置字符集的语句 CHARACTER SET 字符集名 加在之前那些创建/更改数据库/表/列语句的合适位置即可

1. 在创建或修改数据库时设置或修改数据库的字符集

CREATE/ALTER DATABASE db_name 
    CHARACTER SET latin1

2. 在创建或修改表时设置或修改表的字符集

CREATE/ALTER TABLE table1
(……) 
CHARACTER SET latin1

3. 在创建或修改表时设置或修改列的字符集

就是将 CHARACTER SET latin1 加在列设置语句的字段类型和字段性质之间

CREATE TABLE IF NOT EXISTS customers
(
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) CHARACTER SET latin1 NOT NULL, 
    points INT NOT NULL DEFAULT 0,
    email VARCHAR(255) NOT NULL UNIQUE
)

或

USE sql_store2;
ALTER TABLE customers
    MODIFY first_name VARCHAR(50) CHARACTER SET latin1 NOT NULL,
    ADD    last_name  VARCHAR(50) CHARACTER SET latin1 NOT NULL AFTER first_name;

16.2 存储引擎 Storage Engines 

在MySQL中我们有若干种储存引擎，储存引擎决定了我们数据的储存方式以及可用的功能

展示可用的储存引擎：

SHOW ENGINES;

储存引擎有很多，我们真正需要知道只有两个：MyISAM 和 InnoDB

MyISAM 是曾经很流行的引擎，但自 MySQL5.5 之后，默认引擎就改为 InnoDB了，InnoDB支持更多的功能特性，包括事务、外键等等，所以最好使用 InnoDB

引擎是表层级的设置，每个表都可以设置不同的引擎（虽然这没必要）

外键是十分重要的，它可以增加引用一致性/完整性（referential integrity），如果我们有一个老数据库的引擎是MyISAM，我们想要给它设置外键，就必须要将其引擎升级为InnoDB，可以在表的设计模式里选择更改，也可以用修改表的代码：

ALTER TABLE customers
ENGINE = InnoDB;

注意

改变引擎是一个代价极高（expensive）的操作，它会重建整个表，在此期间无法方法访问数据。所以，除非有特殊的理由，不然不要在生产环境中改变储存引擎

第十七章、高效的索引 Indexing for High Performance

17.1 介绍 Introduction 

索引对大型和高并发数据库非常有用，因为它可以显著提升查询的速度

这一章我们将学习关于索引的一切，它们是如何工作的，以及我们如何创造索引来提升查询速度，学习和理解这一章对于程序员和数据库管理员十分重要

准备

打开 load_1000_customers.sql 并运行，该文件会向 sql_store 库的 customers 表插入上千条记录，这样我们就能看出索引对查询效率的影响

17.2 索引 Indexes 

原理和作用

以寻找所在州（state）为 'CA' 的顾客为例，如果没索引，MySQL 就必须扫描筛选所有记录。索引，就好比书籍最后的那些关键词索引一样，按字母排序，这样就能按字母迅速找到需要的关键词所在的页数&