数据定义

本文共--字 阅读约--分钟 | 浏览: -- Last Updated: 2021-04-18

一、数据库模式定义

1.创建数据库 CREATE DATABASE

语法：

CREATE {DATABASE|SCHEMA} [IF NOT EXISTS] db_name
[DEFAULT] CHARACTER SET [=] charset_name 
|[DEFAULT] COLLATE [=]collation_name

语法说明：

  • [] 表示可选项
  • | 用于分隔花括号中的选择
  • db_name 用于数据库命名
  • DEFAULT 用于指定默认值
  • CHARACTER SET 用于指定数据库字符集
  • COLLATE 用于指定字符集校对规则；
  • IF NOT EXISTS 用于创建数据库前进行判断；

举例：创建一个名为 mysql_test 的数据库；

CREATE DATABASE mysql_test;

2.选择数据库 USE

语法：USE db_name;

说明：只有使用 USE命令指定某个数据库为当前数据库之后，才能对该数据库及其存储对象执行各种后续操作。

3.修改数据库 ALTER DATABASE

语法：

ALTER {DATABASE|SCHEMA} [db_name] alter_specification

举例：修改已有数据库 mysql_test 的默认字符集改为 gb2312 和校对规则 改为 gb2312_chinese_ci；

ALTER DATABASE mysql_test
DEFAULT CHARACTER SET gb2312
DEFAULT COLLATE gb2312_chinese_ci;

4.删除数据库 DROP DATABASE

语法：

DROP {DATABASE|SCHEMA} [IF EXISTS] db_name;

-- eg
DROP DATABASE IF EXISTS mysql_test;

5.查看数据库 SHOW DATABASE

语法

SHOW { DATABASE |SCHEMA} [LIKE 'pattern' | WHERE expr];

说明：

  • LIKE 用于匹配指定的数据库名称；
  • WHERE 用于指定数据库名称查询范围的条件；
  • 需要注意的是，只会列出当前用户权限范围内所能查看到的所有数据库的名称；

举例：

-- 查看数据库下的所有信息
SHOW DATABASE;

-- 查看数据库下所有my开头的表
SHOW DATABASE LIKE 'my%';

二、表定义

1.创建表 CREATE TABLE

语法：

CREATE [TEMPORARY] TABLE tbl_name 
(
字段名1 数据类型 [列级完整性约束条件] [默认值]
[,……]
表级完整性约束条件
)[ENGINE=引擎类型];

说明：

  • TEMPORARY: 表示创建的表为临时表，只能对创建它的用户可见，当断开与该数据库的连接时，MySQL 会自动删除它们。

  • AUTO_INCREMENT: 为整型的列设置自增属性，当插入 NULL 值或数字 0 到一个 AUTO_INCREMENT 列中时，该列的值会被自动设置为此前表中该列的最大值加1。一个表只能有一个AUTO_INCREMENT 列，并且它必须被索引。当一个表列被指定为AUTO_INCREMENT 后，其值是可以被覆盖的，只要该值是唯一的。

  • NULL: NULL为默认设置，如果不指定NOT NULL，则认为指定的是NULL，不能将NULL值与空串混淆；

  • PRIMARY KEY: 主键值必须唯一；主键一定为NOT NULL；

举例：在一个已有数据库 mysql_test 中新建一个包含客户姓名、性别、地址、联系方式等内容的客户基本信息表，要求将客户的 id 号指定为该表的主键。

CREATE TABLE customers
(
cust_id INT NOT NULL AUTO_INCREMENT,
cust_name CHAR(50) NOT NULL,
cust_sex CHAR(1) NOT NULL DEFAULT '0',
cust_address CHAR(50),
cust_contact CHAR(50),
PRIMARY KEY(cust_id)
);

2.更新表 ALTER TABLE

2.1、子句：增加列 ADD COLUMN

语法：

ALTER TABLE tbl_name
ADD [COLUMN] 字段名 数据类型 [列级完整性约束条件] [默认值 ] [FIRST|AFTER col_name];

说明：

  • AFTER用于在某列之后添加新列，也可以通过FIRST将新列作为原表的第一列；
  • 类似地，可以通过ADD PRIMARY KEY、ADD FOREIGN KEY、ADD INDEX 为原表添加一个主键、外键和索引；

举例：向数据库 mysql_test 的表 customers 中添加一列，命名为 cust_city，用于描述用户所在城市，要求其不能为 NULL默认值为字符串 ‘Wuhan’，且该列位于 cust_sex列之后；

ALTER TABLE mysql_test.customers
ADD COLUMN cust_city CHAR(5) NOT NULL DEFAULT 'Wuhan' AFTER cust_sex;

2.2、子句：修改列 CHANGE COLUMN

语法：

-- 可以同时修改多个列，用逗号隔开
ALTER TABLE tbl_name
CHANGE [COLUMN] 原字段名1 新字段名1 数据类型 [列级完整性约束条件] [默认值]

举例：将数据库 mysql_test 的表 customers 的 cust_sex 列重命名为 sex, 且将其数据类型更改为字符长度为1的字符数据类型CHAR(1), 允许其为NULL，默认值为字符常量'M'。

ALTER TABLE mysql_test.customers
CHANGE COLUMN cust_sex sex CHAR(1) NULL DEFAULT 'M';

2.3、子句：修改或删除指定列的默认值 ALTER COLUMN

语法：

ALTER TABLE tbl_name
ALTER [COLUMN] 字段名1 SET DEFAULT 默认值

举例：将数据库 mysql_test 的表 customers 的 cust_city 列的默认值修改为字符常量 ‘Beijing’；

ALTER TABLE mysql_test.customers
ALTER COLUMN cust_city SET DEFAULT 'Bejing';

2.4、子句：修改指定列的数据类型或者位置 MODIFY COLUMN

语法：

ALTER TABLE tbl_name
MODIFY [COLUMN] 字段名1 数据类型 [FIRST|AFTER 字段名2];

举例：将数据库 mysql_test 的表 customers 的 cust_name 列的数据类型更改为字符长度为 20 的定长字符数据类型CHAR(20)，并将此列设置成表的第一列。

ALTER TABLE mysql_test.customers
MODIFY COLUMN cust_name CHAR(20) FIRST;

2.5、子句：删除列 DROP COLUMN

语法：

ALTER TABLE tbl_name
DROP [COLUMN] 字段名

类似地，可以通过DROP PRIMARY KEY、DROP FOREIGNKEY、DROP INDEX来删除原表的主键、外键和索引。

举例：删除数据库 mysql_test 的表 customers 的 cust_contact 列。

ALTER TABLE mysql_test.customers
DROP COLUMN cust_contact;

2.6、子句：修改表名 RENAME TO

语法：

ALTER TABLE tbl_name
RENAME [TO] new_tbl_name

举例：

ALTER TABLE mysql_test.customers
RENAME TO mysql_test.back_customers;

使用RENAME TABLE 同时修改多个表名，语法：

RENAME TABLE
tbl_name TO new_tbl_name
[,tbl_name2 TO new_tbl_name2] …;

举例：

RENAME TABLE mysql_test.back_customers TO mysql_test.customers;

重命名的时候，一定是数据库名.表名

3.删除表 DROP TABLE

语法：

DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [,tbl_name2] [RESTRICT|CASCADE];

语法说明：

  • RESTRICT: 确保只有不存在相关视图和 完整性约束的表才能删除；
  • CASCADE: 任何相关视图和完整性约束一并被删除；

4.查看表 SHOW TABLE

9.1、SHOW TABLE 显示指定数据库中存放的所有表名

SHOW [FULL] TABLES [{FROM|IN} db_name] [LIKE 'pattern' |WHERE expr];

-- 显示数据库 mysql_test 中所有的表名。
SHOW TABLES FROM mysql_test;

9.2、SHOW COLUMNS 显示指定数据表的结构

SHOW [FULL] COLUMNS [{FROM|IN} tbl_name] [{FROM|IN} db_name] [LIKE 'pattern'|WHERE expr];

9.3、DESCRIBE 显示指定数据表的结构

-- 语法
{DESCRIBE|DESC} tbl_name [col_name|wild]

-- 显示数据库 mysql_test 中表 customers 的结构。
DESC mysql_test.customers;

三、索引定义

1.创建索引 CREATE INDEX

语法：

CREATE [UNIQUE] INDEX index_name ON tbl_name(index_col_name, ...);

说明：

  • index_col_name的格式为：col_name [(length)] [ASC|DESC]
  • 不能创建主键；索引顺序默认为升序 ASC；

举例1：在数据库 mysql_test 的表 customers 上，根据客户姓名列的前三个字符创建一个升序索引 index_customers；

CREATE INDEX index_customers ON mysql_test.customers(cust_name(3)ASC);

举例2：在数据库 mysql_test 的表 customers 上，根据客户姓名和客户 id 号创建一个组合索引 index_cust；

CREATE INDEX index_cust ON mysql_test.customers(cust_name, cust_id);

2.创建索引 CREATE TABLE

在 CREATE TABLE 语句可以在创建表的时候一起创建索引，除了索引，还有其他的也可以在创建表的时候一起创建，如下：

  • 创建主键:[CONSTRAINT [symbol]] PRIMARY KEY(index_col_name, ...)
  • 创建普通索引:{INDEX|KEY} [index_name] (index_col_name, ...)
  • 创建唯一索引:[CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] (index_col_name, ...)
  • 创建外键:[CONSTRAINT [symbol]] FOREIGN KEY [index_name ] (index_col_name, ...)

举例：在已有数据库 mysql_test 上新建一个包含产品卖家 id 号、姓名、地址、联系方式、售卖产品类型、当月销量等内容的产品卖家信息表 seller，要求在创建表的同时，为该表添加由卖家 id 号和售卖产品类型组成的联合主键，并在当月销量上创建索引。

CREATE TABLE seller
(
seller_id INT NOT NULL AUTO_INCREMENT,
seller_name CHAR(4) NOT NULL,
seller_address CHAR(20) NOT NULL,
seller_contact CHAR(20) NOT NULL,
product_type INT NULL,
sales INT NULL,
PRIMARY KEY(seller_id, product_tyoe),
INDEX index_seller(sales)
)
-- 创建索引 索引名index_seller 根据sales创建

3.创建索引 ALTER TABLE

与 CREATE TABLE 一样， ALTER TABLE 也可以在更新表的时候，创建索引以及其他一些，如下：

  • 创建主键:ADD [CONSTRAINT [symbol]] PRIMARY KEY( index_col_name, ...)
  • 创建普通索引: ADD {INDEX|KEY} [index_name] (index_col_name, ...)
  • 创建唯一索引:ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] (index_col_name, ...)
  • 创建外键: ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name ] (index_col_name, ...)

这些语法只是相当于 CREATE TABLE 的对应语法前面加了一个 ADD；

举例：在数据库 mysql_test 的表 seller 的姓名列上添加一个非唯一索引，取名为 index_seller_name 。

ALTER TABLE mysql_test.seller
ADD INDEX index_seller_name(seller_name);

使用操作表格的子句的时候，CREATE TABLE、ALTER TABLE创建索引的一般格式都是：(ADD) 哪种索引 索引名(列名)

使用 CREATE INDEX的时候，格式是CREATE INDEX 索引名 ON 表名(列名)

4.查看索引 SHOW INDEX

语法：

SHOW {INDEX|INDEXES|KEYS]
{ FROM|IN tbl_name }
{ FROM|IN db_name }
[WHERE expr];

5.删除索引 DROP INDEX / ALTER TABLE

-- DROP INDEX
DROP INDEX index_name ON tbl_name

-- ALTER TABLE
ALERT TABLE tb_name
DROP INDEX index_name;

同样的，ALTER TABLE 在更新表的时候不仅能够删除索引，还能删除一些其他东西：

-- 删主键
ALERT TABLE tb_name
DROP PRIMARY KEY;

-- 删外键
ALERT TABLE tb_name
DROP FOREIGN KEY;

-- 删多个
ALTER TABLE mysql_test.customers
DROP PRIMARY KEY,
DROP INDEX index_customers