MySQL基础（二）数据库、表的创建及操作

这一部分主要使用SQL中的DDL，数据库定义语言（data definition language），对数据库，表进行新建，修改，和删除。

数据库（database）

显示所有数据库

SHOW DATABASES;

创建数据库

CREATE DATABASE db_name;

每创建一个数据库，就会在Data目录下新建一个以数据库名字命名（db_name）的文件夹。说明：Data默认在程序的解压目录下，比如：C:\ProgramData\MySQL\MySQL Server 5.7\Data，可以通过my.ini配置文件来修改Data目录的路径，参考MySQL基础（一）

创建数据库时可以指定字符编码：

CREATE DATABASE db_name CHARACTER SET utf8;  -- 注意是utf8，而不是utf-8，否则报错
CREATE DATABASE db_name CHARSET utf8;  -- 也可以连写；

那么在该数据库下创建的表，默认都用这个编码。

查看数据库创建信息

SHOW CREATE DATABASE db_name;

修改字符编码

ALTER DATABASE db_name CHARSET utf8;

删除数据库

DROP DATABASE db_name;

使用数据库

USE db_name;

使用数据库后（进入了Data下的对应数据库目录），就可以在该数据库下进行表操作。没有退出数据库一说，都是通过USE在不同数据库间切换。

表（table）

创建表（CREATE TABLE）

创建表的基本语法语法如下:

CREATE TABLE 表名
(
  字段名  类型(宽度)  [可选约束条件]，
  字段名  类型(宽度)  [可选约束条件]，
  字段名  类型(宽度)  [可选约束条件]
)[可选表选项]；

比如创建一张顾客表

CREATE TABLE Customers
(
  cust_id        INT        PRIMARY KEY  AUTO_INCREMENT,  -- 定义主键，并自增
  cust_name      CHAR(50)   NOT NULL,  -- 各字段之间以逗号分隔
  cust_address   CHAR(50)   NULL,
  cust_phone     CHAR(11)   NOT NULL  -- 最后一个字段不加逗号
)ENGINE=InnoDB;  -- 语句以分号结束

说明

• 建议表名首字母大写
• 创建语句时，缩进用空格，不要用tab键
• 对于整数类型（比如 INT），不需要指定宽度。不同的数字类型具有不同的存值范围，具体下面会说。
• 对于字符串类型（比如 CHAR, VARCHAR），需要指定宽度，即可存字符串的最大长度。注意，这里的宽度是按字符来算的，一个中文汉字，一个英文字母，一个数字，都是一个字符（在MySQL4.1之前的老版本中宽度是按字节来算的，而一个中文汉字在utf8编码中占3个字节）。存值时，如果字符串长度短于宽度，CHAR（定长字符串）类型会在字符串后面补空格，而VARCHAR（可变长字符串）则按实际字符串存储。
• 往表中插入数据时，如果数字大小或字符串长度溢出，结果取决于当前Server SQL的模式（关于模式，请咨询公司DBA，希望详细了解的参考官网Server SQL Modes）
  
  • 在严格模式下会报错
  • 在非严格模式下，数据会被截断存储：具体来说，如果该字段是数字类型，并且存值范围是 0 ~ 255，而插入数字为256或更大，那么只会截断存储为255；如果该字段是字符串类型，指定宽度是10个字符，那么只会截断存储前10个字符

可选约束条件

多个约束条件之间以空格分隔

• PRIMARY KEY 主键字段，非空且唯一
• FOREIGN KEY 外键字段
• NOT NULL 非空
• NULL 允许为空（默认）
• DEFAULT 默认值，比如设置默认性别：DEFAULT ‘male’
• UNIQUE 字段的值唯一
• AUTO_INCREMENT 自增，用于主键字段，要求字段类型必须为整数。定义自增后，插入数据可以不指定该字段的值。

可选表选项

多个表选项之间以空格分隔

• ENGINE=InnoDB 指定存储引擎
• DEFAULT CHARSET=utf8 设置默认编码，默认继承数据库的编码，也可以在my.ini配置中设置默认编码
• AUTO_INCREMENT=2 指定自增步长为2

MySQL中的数据类型

整数类型

类型	存储空间	最小值	最大值
	(Bytes)	(Signed/Unsigned)	(Signed/Unsigned)
TINYINT 小整数	1	-128	127
2的8次幂减一		0	255
SMALLINT	2	-32768	32767
		0	65535
MEDIUMINT	3	-8388608	8388607
		0	16777215
INT 大整数	4	-2147483648	2147483647
2的32次幂减一		0	4294967295
BIGINT 极大整数	8	-9223372036854775808	9223372036854775807
		0	18446744073709551615

注意：默认类型是Signed有符号整数，但是也可以手动指定为Unsigned无有符号整数，比如：INT UNSIGNED

定点数和浮点数

类型	存储空间	范围	说明	适用
DECIMAL(M,D) 定点数	4	DECIMAL(5,2)：-999.99 ~ 999.99	M表示最大位数，范围是1~65；D表示小数点后有几位，范围0~30，且必须小于M；精度始终准确	价格，工资，身高，体重等
FLOAT 单精度	4		随着小数的增多，精度变得不准确
DOUBLE双精度	8		随着小数的增多，精度比float要高，但也会变得不准确

说明：

• 关于定点数存储空间：每9位用4个字节存储，剩余不足9位的，存储空间递减：7-9位使用4Bytes、5-6位使用3Bytes、3-4位使用2Bytes、1-2位使用1Bytes、0位使用0Bytes
• 对于精确数值计算时需要用到定点数，其能够存储精确值的原因在于其内部按照字符串存储
• FLOAT和DOUBLE也支持(M, D)

日期和时间类型

Data Type	“Zero” Value
DATE	'0000-00-00'
TIME	'00:00:00'
DATETIME	'0000-00-00 00:00:00'
TIMESTAMP	'0000-00-00 00:00:00'
YEAR	0000

DATETIME与TIMESTAMP区别：在实际应用的很多场景中，MySQL的这两种日期类型都能够满足我们的需要，存储精度都为秒，但在某些情况下，会展现出他们各自的优劣：

• DATETIME的日期范围是1001——9999年，TIMESTAMP的时间范围是1970——2038年。
• DATETIME存储时间与时区无关，TIMESTAMP存储时间与时区有关，显示的值也依赖于时区。在mysql服务器，操作系统以及客户端连接都有时区的设置。
• DATETIME使用8字节的存储空间，TIMESTAMP的存储空间为4字节。因此，TIMESTAMP比DATETIME的空间利用率更高。
• DATETIME的默认值为null；TIMESTAMP的字段默认不为空（not null）,默认值为当前时间（CURRENT_TIMESTAMP），如果不做特殊处理，并且update语句中没有指定该列的更新值，则默认更新为当前时间。

字符串类型

类型	宽度范围	说明	特点
CHAR	0-255	定长字符串，存值时，如果字符串长度短于宽度，则在字符串后面补空格；查询时，结果会自动删除尾部的空格	速度快，浪费空间
VARCHAR	0-65535	可变长字符串，最大65KB。储数据的真实内容，不会用空格填充；并且真实数据前有1-2Bytes的前缀，来表示真实数据的bytes字节数	节省空间，速度慢
TEXT	0-65535	多数情况下同VARCHAR。有更多长度选择 TINYTEXT（255B）, TEXT,MEDIUMTEXT（16MB）, and LONGTEXT(4GB)，	不支持默认值；索引需要指定前缀长度
BINARY		同CHAR，只是存储内容为二进制/字节字符串
VARBINARY		同VARCHAR，只是存储内容为二进制/字节字符串
BLOB		字节字符串（byte strings），多数情况下同VARBINARY。更多长度选择 TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB.	不支持默认值；索引需要指定前缀长度

比较常用的就是CHAR 和 VARCHAR。出于性能优化，可以考虑以下几点：

• 创建表时，定长的类型往前放（比如性别），变长的往后放（比如地址或描述信息）
• 过大的数据或者二进制文件不要直接保存到数据库，比如图片，视频等，找一个文件服务器，数据库中只存该文件的路径或URL

枚举和集合类型

枚举（ENUM）是在给定的范围内选一个值，而集合（SET）是可以选多个值

-- 枚举类型
CREATE TABLE Shirts 
(
  name CHAR(30),
  size ENUM('x-small', 'small', 'medium', 'large', 'x-large')
);
-- 插入数据时，只能从上述几个尺寸中选择               
INSERT INTO Shirts (name, size)
VALUES ('dress shirt','large'), ('t-shirt','medium'),('polo shirt','small'); 

-- 集合类型
CREATE TABLE Investigation
(
  name CHAR(30),
  hobby SET('reading', 'running', 'swimming', 'hiking')
);

INSERT INTO Investigation (name, hobby)
VALUES ('Lena', 'reading, swimming');
-- 如果插入数据不在集合内或拼写错误，会报错 Data truncated for column 'hobby'...
-- 插入结果如下：
+------+------------------+
| name | hobby            |
+------+------------------+
| Lena | reading,swimming |
+------+------------------+

查看表

查看所有表

SHOW TABLES;

查看表创建信息

将打印出创建表时使用的语句

SHOW CREATE TABLE Customers;  

查看具体表结构信息

DESC Customers;
SHOW COLUMNS FROM Customers;  -- 同上，用哪个都行，结果如下

+--------------+----------+------+-----+---------+----------------+
| Field        | Type     | Null | Key | Default | Extra          |
+--------------+----------+------+-----+---------+----------------+
| cust_id      | int(11)  | NO   | PRI | NULL    | auto_increment |
| cust_name    | char(50) | NO   |     | NULL    |                |
| cust_address | char(50) | YES  |     | NULL    |                |
| cust_phone   | char(11) | NO   |     | NULL    |                |
+--------------+----------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

修改表（ALTER TABLE）

理想情况下，不要在表中包含数据时对表进行修改。应该在表的设计过程中充分考虑未来可能的需求，避免今后对表的结构做大的改动。使用ALTER TABLE对表进行修改前，应做好表结构和数据的备份。

新增/删除主键

删除主键（DROP）

如果该字段有自增约束（AUTO_INCREMENT），需要先删除自增约束，才能删除主键。

删除自增：

-- 取消自增
ALTER TABLE Customers
MODIFY cust_id INT PRIMARY KEY NOT NULL;
-- ERROR 1068 (42000): Multiple primary key defined
-- cust_id 在键建表时已经是主键，修改时不能再次指定为主键，所以报错

ALTER TABLE Vips
MODIFY cust_id INT NOT NULL;

删除主键：

ALTER TABLE Customers
DROP PRIMARY KEY;  -- 可以删除主键或联合主键

新增主键（ADD）

ALTER TABLE Customers
ADD PRIMARY KEY (cust_id);

新增联合主键（ADD）

主键通常定义在一列上，但也可以使用多个列作为主键，只要列值的组合能唯一标识表示表中的一行即可。

ALTER TABLE Customers
ADD PRIMARY KEY (cust_name, cust_phone); -- 新增联合主键的方式和新增主键的方式一样

mysql> DESC Customers; -- 查看
+--------------+----------+------+-----+---------+-------+
| Field        | Type     | Null | Key | Default | Extra |
+--------------+----------+------+-----+---------+-------+
| cust_id      | int(11)  | NO   |     | NULL    |       |
| cust_name    | char(50) | NO   | PRI | NULL    |       |
| cust_address | char(50) | YES  |     | NULL    |       |
| cust_phone   | char(11) | NO   | PRI | NULL    |       |
+--------------+----------+------+-----+---------+-------+

对已有主键增加自增约束

ALTER TABLE Customers
MODIFY cust_id INT NOT NULL AUTO_INCREMENT;

增加字段（ADD）

新增字段时，要注意所增加字段的数据类型，并考虑使用NULL和DEFAULT约束

格式：

ALTER TABLE 表名
ADD 新增字段名 类型 [可选约束];  -- 新增字段，默认新增至最后
ADD 新增字段名 类型 [可选约束] [FIRST];  -- 新增字段放到第一列
ADD 新增字段名 类型 [可选约束] [AFTER 字段名];  -- 新增字段放到某列之后

示例：

-- 为 Customers 表增加 cust_email 字段
ALTER TABLE Customers
ADD cust_email CHAR(255) NULL;

修改字段类型和约束条件（MODIFY）

-- 修改 Customers 表 cust_email 字段类型为VARCHAR(255) 约束为NOT NULL
ALTER TABLE Customers
MODIFY cust_email VARCHAR(255) NOT NULL;

修改字段名（CHANGE）

格式：

ALTER TABLE 表名
CHANGE 旧字段名 旧字段名 类型 [约束条件];

示例：

-- 修改字段名 cust_email 为 cust_memo，约束为 NULL
ALTER TABLE Customers
CHANGE cust_email cust_memo VARCHAR(255) NULL;

删除字段（DROP）

ALTER TABLE 表名
DROP 字段名;

修改表字符编码

格式：

ALTER TABLE 表名 CHARSET 字符编码；

示例：

-- 修改Customers表字符编码为 gbk
ALTER TABLE Customers CHARSET gbk;

修改完成后，可以通过SHOW CREATE TABLE Customers;语句查看修改结果。

修改表名

 -- 修改 Customers 表名为 Vips
 RENAME TABLE Customers to Vips;

修改完成后可以通过 SHOW TABLES; 语句查看修改结果

修改存储引擎

-- 修改 Customers 表存储引擎为 MyISAM
ALTER TABLE Customers
ENGINE=MyISAM;

删除表

DROP TABLE 表名；