花于陌上开个人站

第四章 数据表与视图 v1.0 ​

一、数据类型 ​

1.数值类型 ​

MySQL 支持所有标准 SQL 数值数据类型，且支持 UNSIGNED 无符号化。

如：TINYINT 数值范围为 (-128，127)，TINYINT UNSIGNED 数值范围为 (0，255)。

2.日期和时间类型 ​

MySQL 支持 DATE、TIME、DATETIME、TIMESTAMP 和 YEAR 等日期和时间类型。

3.字符串类型 ​

MySQL 支持 CHAR、VARCHAR、TEXT、BINARY、VARBINARY 等字符串类型。

二、数据表相关语句 ​

1.创建数据表 ​

sql
CREATE TABLE [IF NOT EXISTS] <表名>(
  <列名> <列数据类型> [列级完整性约束条件],
  [表级完整性约束条件]
)[表选项][分区选项];

常见的列级约束和表级约束如下：

sql
[PRIMARY KEY|NOT NULL|UNIQUE KEY]  # 主键约束|非空约束|唯一约束
[AUTO_INCREMENT]                  # 自增约束
[DEFAULT default_value]            # 默认约束（设置默认值）
[CHECK <表达式>]                  # 检查约束
[COMMENT "comment"]              # 注释

注：一个表只能有一个自增约束，并且该键通常定义为主键。

sql
# 定义外键约束
[CONSTRAINT <约束名>] FOREIGN KEY (<参照列>) REFERENCES <被参照表>(<被参照列>);
# 定义主键约束
[CONSTRAINT <约束名>] PRIMARY KEY(<列名列表>);
# 定义联合唯一约束
[CONSTRAINT <约束名>] UNIQUE KEY(<列名列表>);
# 定义检查约束
[CONSTRAINT <约束名>] CHECK <表达式>;

常用的表选项如下：

sql
[ENGINE=InnoDB]
[DEFAULT CHARSET=utf8]
[AUTO_INCREMENT=1]  # 主键增长初始值，默认为1
[COMMENT="comment"]

2.修改数据表 ​

sql
ALTER TABLE <表名> [操作选项];
sql
RENAME [TO] <新表名>;  # 修改表名
# 添加字段
ADD COLUMN <列名> <类型> [列级约束条件];  # 在末尾添加新列
ADD COLUMN <列名> <类型> [列级约束条件] FIRST;  # 在开头添加新列
ADD COLUMN <列名> <类型> [列级约束条件] AFTER <列名>;  # 在某列后添加新列
# 修改字段
CHANGE COLUMN <旧列名> <新列名> <新列类型> [列级约束条件];  # 修改列
MODIFY <列名> <新类型>;  # 修改指定列的数据类型
DROP COLUMN <列名>;  # 删除某列
# 添加约束
ADD [表级完整性约束条件];
# 删除约束
DROP [表级完整性约束条件];

3.删除数据表 ​

sql
DROP TABLE [IF EXISTS] 表名1 [,表名2, 表名3 ...];

4.数据表操作 ​

sql
DESCRIBE(DESC) <表名|视图名>;  # 查看表或视图的列
SHOW CREATE TABLE <表名>;      # 查看表的创建语句
SHOW CREATE VIEW <视图名>;     # 查看视图的创建语句

三、视图 ​

1.视图创建、修改与删除 ​

sql
CREATE VIEW <视图名>[(<列名列表>)] AS <SELECT语句>;
ALTER VIEW <视图名>[(<列名列表>)] AS <SELECT语句>;
DROP VIEW <视图名1>[,<视图名2>,…];

创建视图时，用户除了拥有 CREATE VIEW 权限外，还需要拥有操作中涉及的基础表和其他视图的相关权限。SELECT 语句不能引用系统变量、用户变量、预处理语句参数，也不能包含 FROM 子句中的子查询。

2.不可更新的视图 ​

可以使用 UPDATE、DELETE 或 INSERT 等语句更新可更新视图对应的基本表内容。若视图包含以下结构中的任何一种，通常不可更新：

  • 聚合函数 SUM()、MIN()、MAX()、COUNT() 等。
  • DISTINCT 关键字。
  • GROUP BY 子句。
  • HAVING 子句。
  • UNION 或 UNION ALL 运算符。
  • 位于选择列表中的子查询。
  • FROM 子句中的不可更新视图或多个表。
  • WHERE 子句中的子查询，且引用了 FROM 子句中的表。
  • ALGORITHM 选项为 TEMPTABLE。