一、初识数据库

数据库是将大量数据保存起来，通过计算机加工而成的可以进行高效访问的数据集合。该数据集合称为数据库（Database，DB）。用来管理数据库的计算机系统称为数据库管理系统（Database Management System，DBMS）。

本次学习所使用的环境： MySQL 8.0.21.0

百度网盘链接：https://pan.baidu.com/s/1SOtMoVqqRXwa2qD0siHcIg

提取码：80lf

1.0 准备Workbench

（1）启动MySQL服务

连接 MySQL 并执行 SQL 查询

在安装成功MySQL后, 我们可以通过右键计算机，管理，服务，找到对应MySQL（如下图）

查找并开启或关闭MySQL服务. 开启服务后, MySQL Server将以后台服务的形式在你的电脑上运行. 如果想要进行查询, 还需要使用命令行工具或者其他更加友好的应用程序连接到MySQL服务。

（2）连接数据库

输入安装是为root用户配置的秘密即可连接到数据库。

但是使用命令行在做数据查询时, 在查询结果的显示和导出方面有诸多不便, 特别是对 SQL 查询不熟练的时候, 这种方式不利于我们进行查询语句的修改和调试. 因此本次学习将选择查询界面更加友好的客户端工具(使用MySQL Workbench)来连接数据库, 这种通过命令行连接的方式暂时不再使用。

常见的可用于连接MySQL数据库并进行查询等操作的管理工具包括开源软件 MySQL Workbench, HeidiSQL和DBeaver, 以及商业软件Navicat(有免费版和14天试用版),SQLyog(有免费的社区版)和DataGrip等等。

（3）使用Workbench连接MySQL

MySQL Workbench 是 MySQL 官方的客户端工具, 支持windows, macOS和Linux. 对于windows用户, 我们在安装 MySQL 的时候由于选择的是完整安装模式, 因此也同时安装了这个工具, 对于macOS的用户, 可以在https://dev.mysql.com/downloads/workbench/选择macOS版本进行下载安装. MySQL Workbench 是一款功能强大的数据库管理工具, 它既可以用于设计数据库, 也可以用于连接数据库进行查询, 我们这个课程主要使用它的连接数据库进行查询的功能。

打开MySQL Workbench（关于其其他使用参考博客）后, 使用快捷键 ctrl+u,或者点击菜单栏里的 Database->Connect to Database, 进入数据库连接的设置界面, 如下图所示:

window系统的mysql服务器默认大小写不敏感。下次打开时可以直接点击保存的数据库连接（不需要先设置再连接）：

（4）创建学习用的数据库

根据《SQL基础教程》提供的MySQL版本的数据库,数据表的创建以及数据导入的代码, 经过一些修改, 创建了一份 sql 脚本, 运行该脚本可以一步到位地创建本文档所需的数据库shop及其中所有的表,并插入本教程所需要的所有数据。

下述SQL脚本可用于创建本教程所使用的示例数据库 shop 以及数据库中表的创建和数据的插入。

详见 ./materials/shop.sql

SQL 脚本的一些要点-- v 2020-09-08

存储引擎使用 InnoDB, 字符集改为 utf8mb4 以更好地支持中文。

所有表名所使用的英文字母都改为小写(后续章节中,SQL 查询中的表名也需要相应修改为小写)

所有列名所使用的英文字母确认为小写(后续章节中,SQL 查询中的列名也需要相应修改为小写)

存在问题的数据, 例如 inventoryproduct 表的 inventory_id 列应为 P 开头的, 已修正为正确的数据。

需测试 SQL 脚本在命令行及各个客户端中是否能被正确执行.

MySQL Workbench 已测试通过(在win10 使用 MySQL Workbench 8.0.21)

DBeaver 已测试通过(使用"执行 SQL 脚本(CTR+x)") (在win10 使用 DBeaver7.2.0)

HeidiSQL 已测试通过(在win10 使用HeidiSQL 11.0.0)

navicat 已测试通过(在win10&win7 使用 navicat premium 15.0.17)

sqlyog 已测试通过(在win10 使用 SQLyog 12.09)

命令行 win10 下测试未通过. 插入中文数据时提示" Data too long for column ‘product_name’ at row 1"

1.1 DBMS的种类

DBMS 主要通过数据的保存格式（数据库的种类）来进行分类，现阶段主要有以下 5 种类型.

层次数据库（Hierarchical Database，HDB）

关系数据库（Relational Database，RDB）

Oracle Database：甲骨文公司的RDBMS

SQL Server：微软公司的RDBMS

DB2：IBM公司的RDBMS

PostgreSQL：开源的RDBMS

MySQL：开源的RDBMS

如上是5种具有代表性的RDBMS，其特点是由行和列组成的二维表来管理数据，这种类型的 DBMS 称为关系数据库管理系统（Relational Database Management System，RDBMS）。

面向对象数据库（Object Oriented Database，OODB）

XML数据库（XML Database，XMLDB）

键值存储系统（Key-Value Store，KVS），举例：MongoDB

使用 SQL 语言的数据库管理系统，也就是关系数据库管理系统（RDBMS）的操作方法。

1.2 RDBMS的常见系统结构

使用 RDBMS 时，最常见的系统结构就是客户端 / 服务器类型（C/S类型）这种结构（图 1-3）

二、初识 SQL

数据库中存储的表结构类似于excel中的行和列，在数据库中，行称为记录，它相当于一条记录，列称为字段，它代表了表中存储的数据项目。

根据对 RDBMS 赋予的指令种类的不同，SQL 语句可以分为以下三类.

DDL ：DDL（Data Definition Language，数据定义语言） 用来创建或者删除存储数据用的数据库以及数据库中的表等对象。DDL 包含以下几种指令。

CREATE ： 创建数据库和表等对象

DROP ： 删除数据库和表等对象

ALTER ： 修改数据库和表等对象的结构

DML :DML（Data Manipulation Language，数据操纵语言） 用来查询或者变更表中的记录。DML 包含以下几种指令。

SELECT ：查询表中的数据

INSERT ：向表中插入新数据

UPDATE ：更新表中的数据

DELETE ：删除表中的数据

DCL ：DCL（Data Control Language，数据控制语言） 用来确认或者取消对数据库中的数据进行的变更。除此之外，还可以对 RDBMS 的用户是否有权限操作数据库中的对象（数据库表等）进行设定。DCL 包含以下几种指令。

COMMIT ： 确认对数据库中的数据进行的变更

ROLLBACK ： 取消对数据库中的数据进行的变更

GRANT ： 赋予用户操作权限

REVOKE ： 取消用户的操作权限

实际使用的 SQL 语句当中有 90% 属于 DML。

2.1 SQL的基本书写规则

SQL语句要以分号（ ; ）结尾

SQL 不区分关键字的大小写，但是插入到表中的数据是区分大小写的

win 系统默认不区分表名及字段名的大小写

linux / mac 默认严格区分表名及字段名的大小写

* 本次学习统一调整表名及字段名的为小写。

常数的书写方式是固定的

‘abc’, 1234, ‘26 Jan 2010’, ‘10/01/26’, ‘2010-01-26’…

单词需要用半角空格或者换行来分隔

SQL 语句的单词之间需使用半角空格或换行符来进行分隔，且不能使用全角空格作为单词的分隔符，否则会发生错误，出现无法预期的结果。

PS：查阅./materials/附录1 - SQL 语法规范 ，养成规范的书写习惯。

在写SQL语句时，要求按照如下规范进行：

SQL 语句要以分号（;）结尾

SQL 不区分关键字的大小写 ，这对于表名和列名同样适用。

插入到表中的数据是区分大小写的。例如，数据Computer、COMPUTER 或computer，三者是不一样的。

常数的书写方式是固定的，在SQL 语句中直接书写的字符串、日期或者数字等称为常数。常数的书写方式如下所示。

SQL 语句中含有字符串的时候，需要像’abc’这样，使用单引号（’）将字符串括起来，用来标识这是一个字符串。

SQL 语句中含有日期的时候，同样需要使用单引号将其括起来。日期的格式有很多种（‘26 Jan 2010’ 或者’10/01/26’ 等）。

在SQL 语句中书写数字的时候，不需要使用任何符号标识，直接写成1000 这样的数字即可。

单词之间需要用半角空格或者换行来分隔。

SQL中的注释主要采用--和/* ... */的方式，第二种方式可以换行。在MySQL下，还可以通过#来进行注释。

【命名规则】

在数据库中，只能使用半角英文字母、数字、下划线（_）作为数据库、表和列的名称 。

名称必须以半角英文字母作为开头。

名称不能重复，同一个数据库下不能有2张相同的表。

2.2 数据库的创建（ CREATE DATABASE 语句）

语法：

CREATE DATABASE < 数据库名称 > ;

创建本课程使用的数据库

CREATE DATABASE shop;

复习：

（1）数据库操作：

1 > create database dbname;                    # 创建数据库，数据库名为dbname
2 > CREATE DATABASE `todo` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;  # 创建todo数据库，并指定字符集
3 > show databases;                            # 显示所有数据库
4 > alter database character set utf8;         # 修改数据库字符集
5 > use dbname;                                # 选择数据库
6 > status;                                    # 查看当前选择（use）的数据库
7 > drop database dbname;                      # 删除数据库

查看数据库：

1. 查看所有存在的数据库

SHOW DATABASES [LIKE '数据库名'];;

LIKE从句是可选项，用于匹配指定的数据库名称。LIKE 从句可以部分匹配，也可以完全匹配。示例：

SHOW DATABASES;
-- 结果如下：
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| shop               |
| sys                |
+--------------------+
5 rows in set (0.01 sec)

-- %表示任意0个或多个字符，可匹配任意类型和长度的字符。
SHOW DATABASES LIKE 'S%';
-- 结果如下
+---------------+
| Database (S%) |
+---------------+
| shop          |
| sys           |
+---------------+
2 rows in set (0.00 sec)

查看创建的数据库

SHOW CREATE DATABASE <数据库名>;

示例：

SHOW CREATE DATABASE shop;
-- 或者
SHOW CREATE DATABASE shop \G
-- 结果如下
*************************** 1. row ***************************
       Database: shop
Create Database: CREATE DATABASE `shop` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */
1 row in set (0.00 sec)

CHARACTER SET utf8mb4表示编码字符集为utf8mb4。

（2）数据表操作：

1 > show tables;                               # 显示所有表
2 > describe tablename;                        # 表结构详细描述
3 > desc tablename;                            # 同 describe 命令一样
4 > create table newtable like oldtable;       # 复制表结构
5 > insert into newtable select * from oldtable;  #复制表数据
6 > rename table tablelname to new_tablelname  # 重命名表，同时命名多个表用逗号“,”分割
7 > drop table tablename;                      # 删除表

2.3 表的创建（ CREATE TABLE 语句）

语法：

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

创建本课程用到的商品表

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));

2.4 命名规则

只能使用半角英文字母、数字、下划线（_）作为数据库、表和列的名称

名称必须以半角英文字母开头

表1-3　商品表和 product 表列名的对应关系

2.5 数据类型的指定

数据库创建的表，所有的列都必须指定数据类型，每一列都不能存储与该列数据类型不符的数据。

四种最基本的数据类型

INTEGER 型

用来指定存储整数的列的数据类型（数字型），不能存储小数。

CHAR 型

用来存储定长字符串，当列中存储的字符串长度达不到最大长度的时候，使用半角空格进行补足，由于会浪费存储空间，所以一般不使用。

VARCHAR 型

用来存储可变长度字符串，定长字符串在字符数未达到最大长度时会用半角空格补足，但可变长字符串不同，即使字符数未达到最大长度，也不会用半角空格补足。

DATE 型

用来指定存储日期（年月日）的列的数据类型（日期型）。

MySQL 支持所有标准 SQL 数值数据类型，包括：

（1）数值类型

数值包含的类型如下：

整型数据：TINYINT、INTEGER、SMALLINT、MEDIUMINT、DECIMAL 、NUMERIC 和BIGINT。

浮点型数据：DECIMAL、FLOAT、REAL 和 DOUBLE PRECISION)。

其中，关键字INT是INTEGER的同义词，关键字DEC是的同义词。

不同关键字的主要区别就是表示的范围或精度不一样。具体如下表：

（2）日期和时间类型

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。具体如下表：

（3）字符串类型

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。具体如下表：

char声明的是定长字符串。若实际中字符串长度不足，则会在末尾使用空格进行填充至声明的长度。

varchar声明的是可变长字符串。存储过程中，只会按照字符串的实际长度来存储，但会多占用一位来存放实际字节的长度。

2.6 约束的设置

约束是除了数据类型之外，对列中存储的数据进行限制或者追加条件的功能。

NOT NULL是非空约束，即该列必须输入数据。

PRIMARY KEY是主键约束，代表该列是唯一值，可以通过该列取出特定的行的数据。

2.7 表的删除和更新

• 删除表的语法：

DROP TABLE < 表名 > ;

• 删除 product 表

需要特别注意的是，删除的表是无法恢复的，只能重新插入，请执行删除操作时要特别谨慎。

DROP TABLE product;

• 添加列的 ALTER TABLE 语句

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

如添加一列可以存储100位的可变长字符串的 product_name_pinyin 列

ALTER TABLE product ADD COLUMN product_name_pinyin VARCHAR(100);

• 删除列的 ALTER TABLE 语句

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

• 删除 product_name_pinyin 列

ALTER TABLE product DROP COLUMN product_name_pinyin;

• 删除表中特定的行（语法）

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

ALTER TABLE 语句和 DROP TABLE 语句一样，执行之后无法恢复。误添加的列可以通过 ALTER TABLE 语句删除，或者将表全部删除之后重新再创建。

【扩展内容】

• 清空表内容

TRUNCATE TABLE TABLE_NAME;

优点：相比drop / delete，truncate用来清除数据时，速度最快。

• 数据的更新

基本语法：

UPDATE <表名>
   SET <列名> = <表达式> [, <列名2>=<表达式2>...];  
 WHERE <条件>;  -- 可选，非常重要。
 ORDER BY 子句;  --可选
 LIMIT 子句; --可选

使用 update 时要注意添加 where 条件，否则将会将所有的行按照语句修改

-- 修改所有的注册时间
UPDATE product
   SET regist_date = '2009-10-10';  
-- 仅修改部分商品的单价
UPDATE product
   SET sale_price = sale_price * 10
 WHERE product_type = '厨房用具';  

使用 UPDATE 也可以将列更新为 NULL（该更新俗称为NULL清空）。此时只需要将赋值表达式右边的值直接写为 NULL 即可。

-- 将商品编号为0008的数据（圆珠笔）的登记日期更新为NULL  
UPDATE product
   SET regist_date = NULL
 WHERE product_id = '0008';  

和 INSERT 语句一样， UPDATE 语句也可以将 NULL 作为一个值来使用。

但是，只有未设置 NOT NULL 约束和主键约束的列才可以清空为NULL。如果将设置了上述约束的列更新为 NULL，就会出错，这点与INSERT 语句相同。

多列更新

UPDATE 语句的 SET 子句支持同时将多个列作为更新对象。

-- 基础写法，一条UPDATE语句只更新一列
UPDATE product
   SET sale_price = sale_price * 10
 WHERE product_type = '厨房用具';
UPDATE product
   SET purchase_price = purchase_price / 2
 WHERE product_type = '厨房用具';  

该写法可以得到正确结果，但是代码较为繁琐。可以采用合并的方法来简化代码。

-- 合并后的写法
UPDATE product
   SET sale_price = sale_price * 10,
       purchase_price = purchase_price / 2
 WHERE product_type = '厨房用具';  

需要明确的是，SET 子句中的列不仅可以是两列，还可以是三列或者更多。

2.8 向 product 表中插入数据

为了学习 INSERT 语句用法，我们首先创建一个名为 productins 的表，建表语句如下：

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

基本语法：

INSERT INTO <表名> (列1, 列2, 列3, ……) VALUES (值1, 值2, 值3, ……);

对表进行全列 INSERT 时，可以省略表名后的列清单。这时 VALUES子句的值会默认按照从左到右的顺序赋给每一列。

-- 包含列清单
INSERT INTO productins (product_id, product_name, product_type, sale_price, purchase_price, regist_date) VALUES ('0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15');
-- 省略列清单
INSERT INTO productins VALUES ('0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15');  

原则上，执行一次 INSERT 语句会插入一行数据。插入多行时，通常需要循环执行相应次数的 INSERT 语句。其实很多 RDBMS 都支持一次插入多行数据

-- 通常的INSERT
INSERT INTO productins VALUES ('0002', '打孔器', '办公用品', 500, 320, '2009-09-11');
INSERT INTO productins VALUES ('0003', '运动T恤', '衣服', 4000, 2800, NULL);
INSERT INTO productins VALUES ('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20');
-- 多行INSERT （ DB2、SQL、SQL Server、 PostgreSQL 和 MySQL多行插入）
INSERT INTO productins VALUES ('0002', '打孔器', '办公用品', 500, 320, '2009-09-11'),
                              ('0003', '运动T恤', '衣服', 4000, 2800, NULL),
                              ('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20');  
-- Oracle中的多行INSERT
INSERT ALL INTO productins VALUES ('0002', '打孔器', '办公用品', 500, 320, '2009-09-11')
           INTO productins VALUES ('0003', '运动T恤', '衣服', 4000, 2800, NULL)
           INTO productins VALUES ('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20')
SELECT * FROM DUAL;  
-- DUAL是Oracle特有（安装时的必选项）的一种临时表A。因此“SELECT *FROM DUAL” 部分也只是临时性的，并没有实际意义。  

INSERT 语句中想给某一列赋予 NULL 值时，可以直接在 VALUES子句的值清单中写入 NULL。想要插入 NULL 的列一定不能设置 NOT NULL 约束。

INSERT INTO productins (product_id, product_name, product_type, sale_price, purchase_price, regist_date) VALUES ('0006', '叉子', '厨房用具', 500, NULL, '2009-09-20');  

还可以向表中插入默认值（初始值）。可以通过在创建表的CREATE TABLE 语句中设置DEFAULT约束来设定默认值。

CREATE TABLE productins
(product_id CHAR(4) NOT NULL,
（略）
sale_price INTEGER
（略） DEFAULT 0, -- 销售单价的默认值设定为0;
PRIMARY KEY (product_id));  

可以使用INSERT … SELECT 语句从其他表复制数据：

注意：在task01中，在使用1.2.8末尾insert…select从其他表中复制数据之前，可以先执行复制表结构的语句，否则会有没有这个表的提示。create table productcopy as select * from product;。

-- 将商品表中的数据复制到商品复制表中
INSERT INTO productcopy (product_id, product_name, product_type, sale_price, purchase_price, regist_date)
SELECT product_id, product_name, product_type, sale_price, purchase_price, regist_date
  FROM Product;  

• 用表插入数据sql如下：

- DML ：插入数据
STARTTRANSACTION;
INSERT INTO product VALUES('0001', 'T恤衫', '衣服', 1000, 500, '2009-09-20');
INSERT INTO product VALUES('0002', '打孔器', '办公用品', 500, 320, '2009-09-11');
INSERT INTO product VALUES('0003', '运动T恤', '衣服', 4000, 2800, NULL);
INSERT INTO product VALUES('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20');
INSERT INTO product VALUES('0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15');
INSERT INTO product VALUES('0006', '叉子', '厨房用具', 500, NULL, '2009-09-20');
INSERT INTO product VALUES('0007', '擦菜板', '厨房用具', 880, 790, '2008-04-28');
INSERT INTO product VALUES('0008', '圆珠笔', '办公用品', 100, NULL, '2009-11-11');
COMMIT;

结果workbench上显示（右键表，选择select rows即可）：

2.9 索引

索引的作用

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

打个比方，如果合理的设计且使用索引的 MySQL 是一辆兰博基尼的话，那么没有设计和使用索引的 MySQL 就是一个人力三轮车。

拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。

索引创建了一种有序的数据结构，采用二分法搜索数据时，其复杂度为  ，1000多万的数据只要搜索23次，其效率是非常高效的。

（1）如何创建索引

创建表时可以直接创建索引，语法如下：

CREATE TABLE mytable(  
ID INT NOT NULL,   
username VARCHAR(16) NOT NULL,  
INDEX [indexName] (username(length))  
);  

也可以使用如下语句创建：

-- 方法1
CREATE INDEX indexName ON table_name (column_name)
-- 方法2
ALTER table tableName ADD INDEX indexName(columnName)

（2）索引分类

主键索引

建立在主键上的索引被称为主键索引，一张数据表只能有一个主键索引，索引列值不允许有空值，通常在创建表时一起创建。

唯一索引

建立在UNIQUE字段上的索引被称为唯一索引，一张表可以有多个唯一索引，索引列值允许为空，列值中出现多个空值不会发生重复冲突。

普通索引

建立在普通字段上的索引被称为普通索引。

前缀索引

前缀索引是指对字符类型字段的前几个字符或对二进制类型字段的前几个bytes建立的索引，而不是在整个字段上建索引。前缀索引可以建立在类型为char、varchar、binary、varbinary的列上，可以大大减少索引占用的存储空间，也能提升索引的查询效率。

全文索引

利用“分词技术”实现在长文本中搜索关键字的一种索引。

语法：SELECT * FROM article WHERE MATCH (col1，col2，...) AGAINST (expr [ search _ modifier ])

1、MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引；

2、MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引;

3、只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。

4、如果可能，请尽量先创建表并插入所有数据后再创建全文索引，而不要在创建表时就直接创建全文索引，因为前者比后者的全文索引效率要高。

单列索引

建立在单个列上的索引被称为单列索引。

联合索引（复合索引、多列索引）

建立在多个列上的索引被称为联合索引，又叫复合索引、组合索引。

三、语法规范

-- 样例一
(SELECT flora.species_name
        ,AVG(flora.height) AS average_height
        ,AVG(flora.diameter) AS average_diameter
   FROM flora
  WHERE flora.species_name = 'Banksia'
     OR flora.species_name = 'Sheoak'
     OR flora.species_name = 'Wattle'
  GROUP BY flora.species_name, flora.observation_date)
  UNION ALL
(SELECT botanic.species_name
        ,AVG(b.height) AS average_height
        ,AVG(b.diameter) AS average_diameter
   FROM botanic_garden_flora AS botanic
  WHERE botanic.species_name = 'Banksia'
     OR botanic.species_name = 'Sheoak'
     OR botanic.species_name = 'Wattle'
  GROUP BY botanic.species_name, botanic.observation_date);
-- 样例二
SELECT botanic.species_name
       ,AVG(b.height) AS average_height
       ,AVG(b.diameter) AS average_diameter
  FROM botanic_garden_flora AS botanic
 WHERE botanic.species_name IN (SELECT species_name
                                  FROM flora 
                                 WHERE height >= 3) 
 GROUP BY botanic.species_name, botanic.observation_da;
-- 样例三
SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.product_type, P.purchase_price
  FROM shopproduct  AS SP 
 INNER JOIN -- product
   (SELECT product_name, product_type, purchase_price
      FROM Product ) AS P 
    ON SP.product_id=P.product_id
 WHERE P.product_type='衣服';

SQL语法规范总得的原则是：清楚、易读并且层次清晰。

常见注意事项如下：

MySQL本身不区分大小写，但强烈要求关键字大写，表名、列名用小写；

创建表时，使用统一的、描述性强的字段命名规则保证字段名是独一无二且不是保留字的，不要使用连续的下划线，不用下划线结尾；最好以字母开头

关键字右对齐，且不同层级的用空格或缩进控制，使其区分开，见样例二；

列名少的时候写在一行里无伤大雅；多的时候以及涉及到CASE WHEN 或者聚合计算的时候，建议分行写；个人习惯是逗号在列名前面，方便之后删除某些列，放列名后亦可；

表别名和列别名尽量用有具体含义的词组，不要用a b c，不然以后review的时候会非常痛苦；

运算符前后都加一个空格；

当用到多个表时，请在所有列名前写上引用的表别名，不要嫌麻烦；

每条命令用分号结尾；

养成随手写注释的习惯，注释方法：

单行注释 #注释文字
单行注释 -- 注释文字
多行注释：/* 注释文字 */

四、作业

4.1 创表语句

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

表1-A　表 Addressbook （地址簿）中的列

mysql> 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));

4.2 操作题

假设在创建练习1.1中的 Addressbook表时忘记添加如下一列 postal_code （邮政编码）了，请编写 SQL 把此列添加到 Addressbook 表中。

列名 ： postal_code

数据类型 ：定长字符串类型（长度为 8）

约束 ：不能为 NULL

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

4.3 填空题

请补充如下 SQL 语句来删除 Addressbook 表。

(DROP) table Addressbook;

4.4 判断题

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

【答】无法用命令恢复。

如果直接drop表或者库的话。在binlog日志里是不会记录删除的数据的，所以没办法通过上面这样来恢复数据。

这种时候就要找一个起始点，以删除操作之前的点为结束点，将数据binlog重新执行一遍，相当于重新重复所有的操作，来恢复数据。以最近的备份数据的备份时间来作为起始点，drop操作的前一个操作为结束点。

五、时间规划