2.表管理

本章目标

• SQL概述
• 创建表
• 修改表
• 注释

本章内容

一、 SQL概述

SQL是Structured Query Language(结构化查询语言)的缩写。SQL是专为数据库而建立的操作命令集，是一种功能齐全的数据库语言。在使用它时，只需要发出“做什么”的命令，“怎么做”是不用使用者考虑的。在windows下SQL不区分大小写，但Linux下是会区分的。

1、SQL语法组成

也有人把select操作单独提出来一个DQL

2、 数据库表

在关系数据库中，数据库表是一系列二维数组的集合，用来代表和储存数据对象之间的关系。它由纵向的列和横向的行组成

3、数据类型

官网参考：https://dev.mysql.com/doc/refman/8.0/en/data-types.html

通过员工信息表来保存公司所有雇员的基本资料，包括员工编号、姓名、性别、电话和住址等等：

• 电话号码一般使用什么数据类型存储?
• 性别一般使用什么数据类型存储?
• 年龄信息如何存储?
• 照片信息一般使用什么数据类型存储? 不会直接保存照片，而是保存照片或视频的存储路径
• 薪水一般使用什么数据类型存储?

MySQL数据类型：

• 数值列类型
• 字符串列类型
• 日期和时间列类型

3.1 数值类型

MySQL 支持所有标准 SQL 数值数据类型。

这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL 和 NUMERIC)，以及近似数值数据类型(FLOAT、REAL 和 DOUBLE PRECISION)。

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

BIT数据类型保存位字段值，并且支持引擎 MyISAM、MEMORY、InnoDB 和 BDB表。

作为 SQL 标准的扩展，MySQL 也支持整数类型 TINYINT、MEDIUMINT 和 BIGINT。下面的表显示了需要的每个整数类型的存储和范围。

类型	大小	范围（有符号）	范围（无符号）	用途
TINYINT	1 Bytes	(-128，127)	(0，255)	小整数值
SMALLINT	2 Bytes	(-32 768，32 767)	(0，65 535)	大整数值
MEDIUMINT	3 Bytes	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
INT或INTEGER	4 Bytes	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
BIGINT	8 Bytes	(-9,223,372,036,854,775,808， 9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值
FLOAT	4 Bytes	(-3.402 823 466 E+38， -1.175 494 351 E-38)， 0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38， 3.402 823 466 E+38)	单精度 浮点数值
DOUBLE	8 Bytes	(-1.797 693 134 862 315 7 E+308， -2.225 073 858 507 201 4 E-308)， 0， (2.225 073 858 507 201 4 E-308， 1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308， 1.797 693 134 862 315 7 E+308)	双精度 浮点数值
DECIMAL	DECIMAL(M,D)	依赖于M和D的值	依赖于M和D的值	小数值

对DECIMAL(M,D) ，M是表示有效数字数的精度,D是表示小数点后的位数 （6，2）

3.2 日期和时间类型

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。

每个时间类型有一个有效值范围和一个”零”值，当指定不合法的MySQL不能表示的值时使用”零”值。

TIMESTAMP类型有专有的自动更新特性，将在后面描述。

类型	大小 ( bytes)	范围	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	‘-838:59:59’/‘838:59:59’	HH:MM:SS	时间值或持续时间
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4	1970-01-01 00:00:00/2038 结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

3.3 字符串类型

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。

类型	大小	用途
CHAR	0-255 bytes	定长字符串
VARCHAR	0-65535 bytes	变长字符串
TINYBLOB	0-255 bytes	不超过 255 个字符的二进制字符串
TINYTEXT	0-255 bytes	短文本字符串
BLOB	0-65 535 bytes	二进制形式的长文本数据
TEXT	0-65 535 bytes	长文本数据
MEDIUMBLOB	0-16 777 215 bytes	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215 bytes	中等长度文本数据
LONGBLOB	0-4 294 967 295 bytes	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295 bytes	极大文本数据

注意：char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符。

CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。

BLOB 是一个二进制大对象，可以容纳可变数量的数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。

有 4 种 TEXT 类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型，可存储的最大长度不同，可根据实际情况选择。

二、创建表

1、 语法

 create table 表名
 （
 列名1  列类型  [<列的完整性约束>],
 列名2  列类型  [<列的完整性约束>],
         ... ...
 ) ENGINE=InnoDB AUTO_INCREMENT=1000 DEFAULT CHARSET=utf8;

AUTO_INCREMENT定义列为自增的属性，一般用于主键，数值会自动加1。 ENGINE 设置存储引擎，CHARSET 设置编码

具体示例

 CREATE TABLE  employee(
    id bigint COMMENT '编号',
    emp_name VARCHAR(20) NOT NULL,
    address VARCHAR(60) NOT NULL,
    birthday DATE,
   )ENGINE=InnoDB DEFAULT CHARSET=utf8;

2、数据表命名规范（参考）

2.1、数据表命名规范

参考,参考2

• 采用26个英文字母(区分大小写)和0-9的自然数(经常不需要)+下划线组成，命名简洁明确，多个单词用下划线’_’分隔
• 全部小写命名，禁止出现大写（默认情况下，MySQL在Windows上是大小写不敏感的，但在Linux上是大小写敏感的,参考）
• 禁止使用数据库关键字，如：name，time ，datetime，password等
• 表名称不应该取得太长（一般不超过三个英文单词）
• 表的名称一般使用名词或者动宾短语
• 用单数形式表示名称，例如，使用 employee，而不是 employees
• 明细表的名称为：主表的名称+字符dtl（detail缩写）
• 例如：采购定单的名称为：po_order，则采购定单的明细表为：po_orderdtl
• 表必须填写描述信息（使用SQL语句建表时）

2.2、字段名命名示例

• 使用下划线进行分隔，提升可读性。

   例如，emp_name，user_id，is_friend
  

• 使用完整名称，提高可读性。

   例如，uid修改为user_id，pid修改为person_id。
  

2.3、字段设计

• 表被索引列必须定义为not null，并设置default值。
• 禁止使用float、double类型，建议使用decimal或者int替代。
• 禁止使用blob、text类型保留大文本、文件、图片，建议使用其他方式存储，MySQL只保存指针信息。
• 禁止使用varchar类型作为主键。

3、 显示表结构

语法：

 describe  表名；
 或
 desc      表名；

用于显示表的创建后结构

查看表的所有信息：

 show create table 表名;

显示表的创建语句

 CREATE TABLE `employee` (
   `id` bigint DEFAULT NULL,
   `emp_name` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
   `sex` char(2) COLLATE utf8mb4_general_ci DEFAULT NULL,
   `address` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
   `age` int DEFAULT NULL,
   `birthday` date DEFAULT NULL,
   `salary` decimal(8,2) DEFAULT NULL
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

COLLATE 用来指定排序的规则 utf8mb4_general_ci，用于VARCHAR，CHAR，TEXT类型告知mysql如何对该列进行排序和比较

三、 修改表

1、 基本语法

 ALTER TABLE tbl_name
     [alter_option [, alter_option] ...]
     [partition_options]

详细参数可看：https://dev.mysql.com/doc/refman/8.0/en/alter-table.html

2、 增加列[add 列名]

加的列在表的最后面: alter table 表名 add 列名 列类型 列参数

 alter table person add birthday date ;

• 把新列加在某列后面:alter table 表名 add 列名 列类型 列参数 after 某列

 alter table person add gender char(1)  after   username;

• 把新列加在最前面:alter table 表名 add 列名 列类型 列参数 first

 alter table person add id int  first;

3、 修改列[modify 列名]

修改列类型: alter table 表名 modify 列名 新类型 新参数

 alter table person modify username varchar(20);

重命名列名: alter table 表名 change 旧列名 新列名 新类型 新参数

 alter table person change username user_name varchar(25);

4、 删除列[drop 列名]

alter table 表名 drop 列名

alter table person drop id;

5、 修改表名

alter table 表名 rename 表名

alter table person rename person2;

6、删除表操作

drop  table  [if exists]  tab_name [,tab_name]...

从数据库中删除给定的表。如果给出if exists 子句，则删除不存在的表不会出错。

四、注释

1、 从‘#’字符从行尾

desc empoyee;     # This comment continues to the end of line

2、 从‘– ’序列到行尾

desc empoyee;     -- This comment continues to the end of line

请注意‘– ’(双破折号)注释风格要求第2个破折号后面至少跟一个空格符(例如空格、tab、换行符等等)。该语法与标准SQL注释语法稍有不同

3、从/序列到后面的/序列

mysql> desc  /* this is an in-line comment */ + empoyee;
mysql> SELECT empoyee;
/*
this is a
multiple-line comment
*/

结束序列不一定在同一行中，因此该语法允许注释跨越多行