6、MySQL核心DDL语句

动漫IT 06-06 101

前言：

现时我们对“mysql中ddl语句”大体比较关怀，你们都想要分析一些“mysql中ddl语句”的相关知识。那么小编也在网摘上汇集了一些关于“mysql中ddl语句””的相关资讯，希望各位老铁们能喜欢，各位老铁们快快来了解一下吧！

命令类型

服务器端命令

获取命令帮助

数据库管理

查看数据库基础变量

SQL组成

创建

修改

删除

表管理

创建

示例一

示例二

示例三

一个常见的创建表结构的示例

表修改

改名

表结构修改

添加/删除字段

修改字段

键管理

索引

索引管理

视图

视图操作

命令类型

服务器端命令

DDL：数据定义语言，主要用于管理数据库组件，例如表、索引、视图、用户、存储过程

CREATE、ALTER、DROP

DML：数据操纵语言，主要用管理表中的数据，实现数据的增、删、改、查；

INSERT， DELETE， UPDATE， SELECT

获取命令帮助

mysql> help CREATE...

mysql>HELP CREATE DATABASE

数据库管理

查看数据库基础变量

数据库的存储引擎类型：SHOW ENGINES;

查看mysql支持的字符集：show character set;

查看指定表的存储引擎：show table status [where Name='tab_name'|like '%...%'];

查看支持的所有字符集：SHOW CHARACTER SET;

查看支持的所有排序规则：SHOW COLLATION;

查看索引：show indexes from tab_name;

SQL组成

DDL：数据库模式定义语言，关键字：create、alter、dorp

DML：数据操纵语言，关键字：Insert、delete、update、alter、dorp

DCL：数据库控制语言，关键字：grant、remove

DQL：数据库查询语言，关键字：select

创建

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name;

[DEFAULT] CHARACTER SET [=] charset_name

[DEFAULT] COLLATE [=] collation_name #定义排序规则

# 创建数据库

[(none)]>create database if not exists cce charset = utf8 collate=utf8_general_ci;

# 创建表

[(none)]>show create database cce;

+----------+--------------------------------------------------------------+

| Database | Create Database |

+----------+--------------------------------------------------------------+

| cce | CREATE DATABASE `cce` /*!40100 DEFAULT CHARACTER SET utf8 */ |

+----------+--------------------------------------------------------------+

修改

ALTER {DATABASE | SCHEMA} [db_name] [DEFAULT] CHARACTER SET [=] charset_name [DEFAULT] COLLATE [=] collation_name

# 默认字符集

[(none)]>alter database cce charset=latin1 collate=latin1_swedish_ci;

Query OK, 1 row affected (0.00 sec)

[(none)]>show create database cce;

+----------+----------------------------------------------------------------+

| Database | Create Database |

+----------+----------------------------------------------------------------+

| cce | CREATE DATABASE `cce` /*!40100 DEFAULT CHARACTER SET latin1 */ |

+----------+----------------------------------------------------------------+

1 row in set (0.00 sec)

删除

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

# 删除库

[(none)]>drop database if exists cce;

Query OK, 0 rows affected (0.00 sec)

表管理

创建

语法二：create table [if not exists] tab_name (tab_definition,...)[tab_option] select_statement # 直接创建表，并将查询语句的结果得到的数据插入到新表中；

语法三：create table [if not exists] tab_name ｛like old_tab_name｝# 复制某个存在的表的表结构来创建新表，只有结构没有数据；

示例一

[mysql]>create table if not exists cce(id int auto_increment,name char(32) not null,gender enum('M','G') not null,primary key(id)) engine=innodb default charset=utf8;

Query OK, 0 rows affected (0.41 sec)

[mysql]>desc cce;

+--------+---------------+------+-----+---------+----------------+

+--------+---------------+------+-----+---------+----------------+

| id | int(11) | NO | PRI | NULL | auto_increment |

+--------+---------------+------+-----+---------+----------------+

示例二

# 创建一个新表

[cce]>create table cce(id int);

Query OK, 0 rows affected (0.48 sec)

# 插入测试数据，可以看出都插入成功了

[cce]>insert into cce values(1),(2),(3);

Query OK, 3 rows affected (0.05 sec)

Records: 3 Duplicates: 0 Warnings: 0

# 基于上面的新表结构和数据再创建一个表

[cce]>create table new_cce select * from cce;

Query OK, 3 rows affected (0.73 sec)

Records: 3 Duplicates: 0 Warnings: 0

# 查看数据

[cce]>select * from new_cce;

+------+

| id |

+------+

| 1 |

| 2 |

| 3 |

+------+

3 rows in set (0.00 sec)

示例三

# 创建一个新的数据库

[(none)]>create database cce;

Query OK, 1 row affected (0.01 sec)

[(none)]>use cce;

Database changed

# 查看要基于创建的表结构

[cce]>desc mysql.cce;

+--------+---------------+------+-----+---------+----------------+

+--------+---------------+------+-----+---------+----------------+

| id | int(11) | NO | PRI | NULL | auto_increment |

+--------+---------------+------+-----+---------+----------------+

3 rows in set (0.00 sec)

# 根据已存在的表创建表

[cce]>create table cce like mysql.cce;

Query OK, 0 rows affected (0.61 sec)

# 查看创建后的表结构

[cce]>desc cce;

+--------+---------------+------+-----+---------+----------------+

+--------+---------------+------+-----+---------+----------------+

| id | int(11) | NO | PRI | NULL | auto_increment |

+--------+---------------+------+-----+---------+----------------+

3 rows in set (0.01 sec)

一个常见的创建表结构的示例

id使用smallint字段类型来限制并且是无符号不能为空自增主键，name使用char来限制，并且长度只给3测试下，age使用tinyint来给定，因为一般这个字段都用不到太大的值，gender使用枚举类型并且默认值为m不能为空，最后将name字段定义为索引实现加速查询；

[cce]>create table cce(id smallint unsigned not null auto_increment,name char(3) not null,age tinyint unsigned not null,gender enum('m','f') default 'm' not null,index(name),primary key(id)) engine=innodb default charset=utf8;

[cce]>insert into cce(name,age) values ('蔡大爷蔡大爷蔡大爷',18)

ERROR 1406 (22001): Data too long for column 'name' at row 1 # 提示插入的name字段的数据太长，插入失败

表修改

改名

MariaDB [cce]> alter table cce rename to wj;

或者：

MariaDB [cce]> rename table wj to cce;

表结构修改

表修改语法：ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name [alter_specification [, alter_specification] ...]

添加/删除字段

添加：ADD [COLUMN] col_name data_type [FIRST | AFTER col_name ] #标红的表示在什么之后

MariaDB [cce]> alter table students add class varchar(100) not null after sex;

删除：DROP [COLUMN] col_name

MariaDB [cce]> alter table students drop class;

修改字段

change：可以将老的字段名改成新的字段名，然后还可以将字段定义修改；

语法：CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name]

示例：MariaDB [cce]> alter table cce change new_cce Name varchar(100) not null;

modify：指引修改字段定义的规则；

语法：MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name] #

示例：MariaDB [cce]> alter table cce modify name varchar(100) not null;

# 字段相关

[cce]>desc cce;

+--------+----------------------+------+-----+---------+----------------+

+--------+----------------------+------+-----+---------+----------------+

| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |

| age | tinyint(3) unsigned | NO | | NULL | |

| gender | enum('m','f') | NO | | m | |

+--------+----------------------+------+-----+---------+----------------+

4 rows in set (0.00 sec)

# 新增字段，在指定字段后面新增after，使用first无法在指定字段前面增加

[cce]>alter table cce add city char(32) not null after age;

Query OK, 0 rows affected (1.38 sec)

Records: 0 Duplicates: 0 Warnings: 0

# 查看新增之后的结构

[cce]>desc cce;

+--------+----------------------+------+-----+---------+----------------+

+--------+----------------------+------+-----+---------+----------------+

| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |

| age | tinyint(3) unsigned | NO | | NULL | |

| gender | enum('m','f') | NO | | m | |

+--------+----------------------+------+-----+---------+----------------+

5 rows in set (0.00 sec)

键管理

示例：MariaDB [cce]> alter table students add primary key(name,id);

删除：

主键：DROP PRIMARY KEY

外键：DROP FOREIGN KEY fk_symbol

示例：MariaDB [cce]> alter table students drop primary key;

直接创建表，并将查询语句的结果插入到新创建的表中；

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name

[(create_definition,...)]

[table_options]

[partition_options]

select_statement

示例：

查看表的信息：

MariaDB [cce]> show table status where Name='test'\G

修改引擎：MariaDB [cce]> alter table cce engine='InnoDB';

复制某存在的表的结构来创建新的空表；

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name

{ LIKE old_tbl_name | (LIKE old_tbl_name) }

示例：

索引

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。打个比方，如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。建立索引会占用磁盘空间的索引文件。

索引管理

索引是特殊的数据结构；

索引：要有索引名称；

查看索引信息：mysql> SHOW INDEXES FROM tbl_name;

创建：CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [BTREE|HASH] ON tbl_name (col1, col2,,...)

示例：

MariaDB [cce]> create index index_name on students(class,sex);

MariaDB [cce]> show index from students;

删除：DROP INDEX index_name ON tbl_name

示例：MariaDB [cce]> drop index index_name on students;

新增：MariaDB [cce]> alter table cce add index(age);

总体创建示例：

MariaDB [cce]>create table if not exists cce (id smallint unsigned not null auto_increment primary key,name varchar(100)not null,age tinyint unsigned not null,gender enum('f','m') default 'm',index(name));

# 构建联合索引

[cce]>alter table cce add index(name,age);

Query OK, 0 rows affected (0.47 sec)

Records: 0 Duplicates: 0 Warnings: 0

# 删除联合索引

[cce]>alter table cce drop index name;

# 添加唯一索引

[cce]>alter table cce add unique key(age);

# 删除唯一索引

[cce]>show index from cce\G

*************************** 2. row ***************************

Table: cce

Non_unique: 0

Key_name: age

Seq_in_index: 1

Column_name: age

Collation: A

Cardinality: 1

Sub_part: NULL

Packed: NULL

Null:

Index_type: BTREE

Comment:

Index_comment:

[cce]>alter table cce drop index age;

Query OK, 0 rows affected (0.32 sec)

Records: 0 Duplicates: 0 Warnings: 0

# 添加主键索引

[cce]>alter table cce add primary key(id);

Query OK, 0 rows affected (0.32 sec)

Records: 0 Duplicates: 0 Warnings: 0

# 删除主键索引使用modify

视图

视图：例如公司有一个员工信息表，它从存储了各个员工的性别年龄姓名工资等信息，假如说其他人想查找我们的公司员工的所有档案，但是我们不期望能看到工资这一段，我们该如何操作呢？我们可以在这个表中建立一个虚表这个表当中包含了除工资以外的其他信息，而且我们直接告诉他你去查这张虚表就行了。所以就无法看到我们的视图；

关系型数据库中的数据是由一张一张的二维关系表所组成，简单的单表查询只需要遍历一个表，而复杂的多表查询需要将多个表连接起来进行查询任务。对于复杂的查询事件，每次查询都需要编写MySQL代码效率低下。为了解决这个问题，数据库提供了视图（view）功能；

视图实际就是一串存储下来的select语句，删除视图不会影响原来的表，通畅都是用来展示使用的；

视图操作

虚表：存储下来的SELECT语句；

创建：CREATE VIEW view_name [(column_list)] AS select_statemen

修改：ALTER VIEW view_name [(column_list)] AS select_statement

删除：DROP VIEW [IF EXISTS] view_name [, view_name] ...

[cce]>show tables;

+---------------+

| Tables_in_cce |

+---------------+

| cce |