服务器之家：专注于服务器技术及软件下载分享
分类导航

Mysql|Sql Server|Oracle|Redis|MongoDB|PostgreSQL|Sqlite|DB2|mariadb|

服务器之家 - 数据库 - Mysql - MySQL ddl语句的使用

MySQL ddl语句的使用

2021-03-05 19:22翁智华 Mysql

这篇文章主要介绍了MySQL ddl语句的使用，帮助大家更好的理解和使用MySQL，感兴趣的朋友可以了解下

前言

SQL的语言分类主要包含如下几种：

 

DDL 数据定义语言 create、drop、alter 数据定义语言 create、drop、alter 语句 。
DML 数据操纵语言 insert、delete、update 定义对数据库记录的增、删、改操作。
DQL 数据库查询语言 select 定义对数据库记录的查询操作。
DCL 数据库控制语言 grant、remove

定义对数据库、表、字段、用户的访问权限和安全级别。

（授权grant,收回权限revoke等）。
TCL 事务控制语言

set autocommit=0、

start transaction、

savepoint、commit、rollback

 定义对数据库的事务操作。

 

这小节主要了解下数据定义语言DDL(Data Define Language)。我们用它对数据库、表进行一些管理操作（创建、删除、修改等），比如：建库、删库、建表、修改表、删除表、对字段的增删改等，库表结构的管理。

接下来我们逐一来说明（下文[]中的内容属于可选项）。

数据库管理

创建数据库

?
1
create database [if not exists] dbname;

删除数据库

?
1
drop databases [if exists] dbname;

完整的写法如下：

?
1
2
drop databases [if exists] o_dbname;
create database n_dbname;

 o_dbname 代表旧的数据库名，n_dbname 代表新的数据库名。

测试一下：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
mysql> show databases;
+--------------------+
| Database   |
+--------------------+
| information_schema |
| buyerparty   |
| buyerparty1  |
| git_jeeshop  |
| jz     |
| kdmy    |
| kdmygf    |
| localsdk   |
| mgrcentercontrol |
| mysql    |
| performance_schema |
| stroke_data  |
| test    |
+--------------------+
13 rows in set
 
mysql> drop database if exists test1;
Query OK, 0 rows affected
 
mysql> create database test1;
Query OK, 1 row affected
 
mysql> create database test1;
1007 - Can't create database 'test1'; database exists

通过上面的测试可以知道：删除之前要先判断数据库是否存在，否则会报出异常；同时创建之前也要判断是否存在，如果存在则会提示已存在。

表管理

创建表

在数据库中一张表的基本语法格式如下：

?
1
2
3
4
5
create table tbname(
column_name_1 column_type_1[(n)] [constraints] [comment 'comment1'],
column_name_2 column_type_2[(n)] [constraints] [comment 'comment2'],
column_name_3 column_type_3[(n)] [constraints] [comment 'comment3']
)[table_options];

语法说明

1、column_name是指字段名；column_type指的是字段类型（CHAR、INT等）；n代表字段宽度，可选；constraints 约束，可选；comment 为字段备注，可以对字段详细描述。

2、同一个表里面，column_name不能相同 

3、字段名和类型为必选，其他均为可选参数

4、类型限制了 字段 的存储格式，必须以给定的数据类型来存储，并可以额外添加的约束

约束说明

not null：非空约束

?
1
2
3
4
5
6
7
8
mysql> use test;
Database changed
 
mysql> create table if not exists `user1`(age int comment '年龄',name char(5) comment '姓名' not null);
Query OK, 0 rows affected
 
mysql> insert into user1 values(8,null);
1048 - Column 'name' cannot be null

建表的时候，对name字段做了非空约束，这时候传入的值为null，就会有错误提示。所以非空约束的目的是保证字段不为空。

default value：提供字段默认值

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> use test;
Database changed
 
mysql> create table if not exists `user2`(age int not null default 0 comment '年龄',name char(50) comment '姓名' not null);
Query OK, 0 rows affected
 
mysql> insert into user2(name) values('brand');
Query OK, 1 row affected
mysql> select * from user2;
+-----+-------+
| age | name |
+-----+-------+
| 0 | brand |
+-----+-------+
1 row in set

设置了默认值之后，如果在写入数据时，不指定值，他会自动取默认值0。

primary key：标识主键约束
设置该字段为表的主键，全局唯一标识录，插入重复时报错。

有两种表现方式：一种是直接在字段约束中跟上；一种是字段都声明完了之后，在结尾加上，与上一个字段之间用逗号隔开。

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
mysql> use test;
Database changed
 
mysql> create table if not exists `user3`(id int primary key,age int not null default 0 comment '年龄',name char(50) comment '姓名' not null);
Query OK, 0 rows affected
 
mysql> insert into user3 values(1,20,'brand');
Query OK, 1 row affected
 
mysql> insert into user3 values(1,22,'sol');
1062 - Duplicate entry '1' for key 'PRIMARY'
 
mysql> insert into user3 values(2,22,'sol');
Query OK, 1 row affected
 
mysql> select * from user3;
+----+-----+-------+
| id | age | name |
+----+-----+-------+
| 1 | 20 | brand |
| 2 | 22 | sol |
+----+-----+-------+
2 rows in set

如上，主键必须保持值的唯一性，如果插入重复值，会提示违反主键约束

另外一种方式是在字段声明的尾部，可以支持多个主键，用逗号隔开并且不可重复，格式：primary key(字段1,字段2,字段n)，这种叫组合主键（或复合主键），举个栗子：

?
1
create table if not exists `user4`(id int,age int not null default 0 comment '年龄',name char(50) comment '姓名' not null,primary key(id,name));

foreign key：标识外键约束
语法：foreign key(t1_columnname) references t2(columnname),t1 为当前表，t2为外键表，当前表和外键表有一个字段约束成外键。

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
mysql> create table if not exists `class`(classid int primary key,classname varchar(50));
Query OK, 0 rows affected
 
mysql> create table if not exists `user4`(id int primary key,age int comment '年龄',name char(50) comment '姓名',cid int not null,foreign key(cid) references class(classid));
Query OK, 0 rows affected
 
mysql> insert into `user4` values(1,20,'brand',1);
1452 - Cannot add or update a child row: a foreign key constraint fails (`test`.`user4`, CONSTRAINT `user4_ibfk_1` FOREIGN KEY (`cid`) REFERENCES `class` (`classid`))
 
mysql> insert into `class` values(1,'grad 3');
Query OK, 1 row affected
 
mysql> insert into `user4` values(1,20,'brand',1);
Query OK, 1 row affected
 
mysql> select a.age as '年龄',a.name as '学生姓名',b.classname as '班级' from user4 a left join class b on a.cid = b.classid;
+------+----------+--------+
| 年龄 | 学生姓名 | 班级 |
+------+----------+--------+
| 20 | brand | grad 3 |
+------+----------+--------+
1 row in set

几点说明：

1、插入user4表的时候，会检查关联的外键classid的值是否存在，如果不存在就会报错误。如上述代码中第三段，classid=1的值在class表中不存在。

2、建立外键关系的两张表的对应字段，类型需要保持一致。

3、设置为外键的字段不能为本表的主键，而关联表的字段需要为主键。（所以外键cid关联到class表的classid字段为主键）。

unique key：标识唯一值约束
可以设置一个到多个字段，不允许重复值，重复会报违反唯一约束，导致插入失败。

同样的有两种定义方式，一种是直接在字段后设置，一种是定义完所有字段之后再设置。以下例子：

?
1
2
3
4
5
6
7
8
9
10
mysql> create table `user5` (id int primary key,name varchar(50),ident char(18) unique key);
Query OK, 0 rows affected
 
mysql> create table `user6` (id int primary key,name varchar(50),ident char(18) not null,sex int not null,unique key(ident,sex));
Query OK, 0 rows affected
 
mysql> insert into `user5` values(1,'brand','012345678901234567');
Query OK, 1 row affected
mysql> insert into `user5` values(2,'sol','012345678901234567');
1062 - Duplicate entry '012345678901234567' for key 'ident'

第二段中演示了支持多字段，用逗号隔开，语法格式：unique key(字段1,字段2,字段n)；

第三段重复输入了ident的值，他就提示重复输入了。

auto_inc：标识自动增长

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> create table `user7` (id int auto_increment primary key,name varchar(50));
Query OK, 0 rows affected
 
mysql> insert into `user7`(name) values ('brand'),('sol'),('helen');
Query OK, 3 rows affected
 
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from `user7`;
+----+-------+
| id | name |
+----+-------+
| 1 | brand |
| 2 | sol |
| 3 | helen |
+----+-------+
3 rows in set

auto_increment 说明：

1、auto_increacement 的字段为自动增长，默认值从1开始，每次+1

2、自动增长字段的初始值、步长可以在mysql中进行设置，比如设置初始值为1万，步长每次增长10

3、自增列当前值存储在内存中，数据库重启后，会查询当前表中自增列max为当前值。

4、如果表数据被清空并重启数据库，自增列会从初始值开始。

删除表

?
1
drop table [if exists] tname;

修改表名、备注

?
1
2
alter table o_tname rename [to] n_tname;
alter table tname comment 'memo'; 

复制表

仅复制架构

?
1
create table tname like from_tname; 
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> select * from `user7`;
+----+-------+
| id | name |
+----+-------+
| 1 | brand |
| 2 | sol |
| 3 | helen |
+----+-------+
3 rows in set
 
mysql> create table `user8` like `user7`;
Query OK, 0 rows affected
 
mysql> select * from `user8`;
Empty set

复制架构+数据

?
1
create table tname [as] select column1,column2,... from from_tname [where condition]; 
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
mysql> select * from `user7`;
+----+-------+
| id | name |
+----+-------+
| 1 | brand |
| 2 | sol |
| 3 | helen |
+----+-------+
3 rows in set
 
mysql> create table `user9` select id,name from `user7`;
Query OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0
 
mysql> select * from `user9`;
+----+-------+
| id | name |
+----+-------+
| 1 | brand |
| 2 | sol |
| 3 | helen |
+----+-------+
3 rows in set

数据和架构都被复制过来了，这个超实用。

管理字段

添加字段

?
1
alter table tname add column column_name column_type [constraints];
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
mysql> select * from `user9`;
+----+-------+
| id | name |
+----+-------+
| 1 | brand |
| 2 | sol |
| 3 | helen |
+----+-------+
3 rows in set
 
mysql> alter table `user9` add column newcolumn int not null default 0;
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0
 
mysql> select * from `user9`;
+----+-------+-----------+
| id | name | newcolumn |
+----+-------+-----------+
| 1 | brand |   0 |
| 2 | sol |   0 |
| 3 | helen |   0 |
+----+-------+-----------+
3 rows in set

修改字段

?
1
2
alter table tname modify column col_name new_col_type [constraints]; -- 修改类型、约束，不能修改字段名
alter table tname change column col_name new_col_name new_col_type [constraints]; -- 修改字段名、类型、约束

以下分别是modify和change示例：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
mysql> desc `user9`;
+-----------+-------------+------+-----+---------+-------+
| Field  | Type  | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id  | int(11)  | NO |  | 0  |  |
| name  | varchar(50) | YES |  | NULL |  |
| newcolumn | int(11)  | NO |  | 0  |  |
+-----------+-------------+------+-----+---------+-------+
3 rows in set
 
mysql> alter table `user9` modify column name varchar(100);
Query OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0
 
mysql> desc `user9`;
+-----------+--------------+------+-----+---------+-------+
| Field  | Type   | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| id  | int(11)  | NO |  | 0  |  |
| name  | varchar(100) | YES |  | NULL |  |
| newcolumn | int(11)  | NO |  | 0  |  |
+-----------+--------------+------+-----+---------+-------+
3 rows in set
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
mysql> desc `user9`;
+-----------+--------------+------+-----+---------+-------+
| Field  | Type   | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| id  | int(11)  | NO |  | 0  |  |
| name  | varchar(100) | YES |  | NULL |  |
| newcolumn | int(11)  | NO |  | 0  |  |
+-----------+--------------+------+-----+---------+-------+
3 rows in set
 
mysql> alter table `user9` change column name name1 varchar(100);
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0
 
mysql> desc `user9`;
+-----------+--------------+------+-----+---------+-------+
| Field  | Type   | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| id  | int(11)  | NO |  | 0  |  |
| name1  | varchar(100) | YES |  | NULL |  |
| newcolumn | int(11)  | NO |  | 0  |  |
+-----------+--------------+------+-----+---------+-------+
3 rows in set

删除字段

?
1
alter table tname drop column col_name; 
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
mysql> desc `user9`;
+-----------+--------------+------+-----+---------+-------+
| Field  | Type   | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| id  | int(11)  | NO |  | 0  |  |
| name1  | varchar(100) | YES |  | NULL |  |
| newcolumn | int(11)  | NO |  | 0  |  |
+-----------+--------------+------+-----+---------+-------+
3 rows in set
 
mysql> alter table `user9` drop column newcolumn;
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0
 
mysql> desc `user9`;
+-------+--------------+------+-----+---------+-------+
| Field | Type   | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | int(11)  | NO |  | 0  |  |
| name1 | varchar(100) | YES |  | NULL |  |
+-------+--------------+------+-----+---------+-------+
2 rows in set

以上就是MySQL ddl语句的使用的详细内容，更多关于MySQL ddl语句的资料请关注服务器之家其它相关文章！

原文链接：https://www.cnblogs.com/wzh2010/p/13842993.html

  • mysql
  • DDL
  • 语句

    • 延伸 · 阅读

    精彩推荐
    • MysqlMYSQL更新优化实录

      MYSQL更新优化实录

      本文给大家分享的是个人在对mysql数据进行刷新的时候遇到的一个效率非常低下的问题的解决全过程，主要是针对sql语句来进行优化，有查找问题的方法，...

      MYSQL教程网3162020-05-16
    • Mysql在MySQL中自定义参数的使用详解

      在MySQL中自定义参数的使用详解

      今天小编就为大家分享一篇在MySQL中自定义参数的使用详解，具有很好的参考价值，希望对大家有所帮助。一起跟随小编过来看看吧...

      长弓Smile1392020-12-16
    • Mysqlmysql正则表达式（regexp和rlike）的搜索功能实例分析

      mysql正则表达式（regexp和rlike）的搜索功能实例分析

      这篇文章主要介绍了mysql正则表达式（regexp和rlike）的搜索功能,结合实例形式分析了mysql正则表达式使用regexp和rlike的搜索功能相关原理与实现技巧,需要的朋...

      luyaran9102020-12-28
    • Mysql简述MySql四种事务隔离级别

      简述MySql四种事务隔离级别

      这篇文章主要介绍了MySql四种隔离级别，帮助大家更好的理解和学习MySQL，感兴趣的朋友可以了解下...

      小诸葛8272021-01-27
    • Mysqllinux修改mysql数据库文件的路径

      linux修改mysql数据库文件的路径

      在网上找了好多，没有确定哪个是最终的答案，由于网站在运行中，实在不敢轻易动手，怎么奈我是个菜鸟呢！先把找到的东西简单记录一下，回头再说...

      MYSQL教程网1822019-11-28
    • MysqlMySQL 启动报错:File ./mysql-bin.index not found (Errcode: 13)

      MySQL 启动报错:File ./mysql-bin.index not found (Errcode: 13)

      这篇文章主要介绍了MySQL 启动报错:File ./mysql-bin.index not found (Errcode: 13)的解决方法,需要的朋友可以参考下 ...

      MYSQL教程网4452020-04-15
    • MysqlMysql8.0使用窗口函数解决排序问题

      Mysql8.0使用窗口函数解决排序问题

      窗口的概念非常重要，它可以理解为记录集合，窗口函数也就是在满足某种条件的记录集合上执行的特殊函数。这篇文章主要介绍了Mysql8.0使用窗口函数解...

      追梦人++9822020-12-30
    • MysqlMac 下 MySQL5.7.22的安装过程

      Mac 下 MySQL5.7.22的安装过程

      这篇文章主要介绍了Mac 下 MySQL5.7.22的安装过程,非常不错，具有参考借鉴价值，需要的朋友可以参考下...

      芥末无疆sss4452020-08-31
    最近更新
    编辑推荐
    阅读排行
    热门标签
    150 　 1044 　 1062 　 1053 　 1045 　 1449 　 1005 　 1819 　 2013 　 1290 　 1862 　 1864 　 2006 　 2003 　 1406 　 1615 　 10048 　 1577 　 1786 　 1215 　 2002 　 1146 　 1366 　 10061 　 1067 　 1135 　 1665 　 1175 　 1130 　 1698 　
    © 2019-2021 服务器之家(www.zzvips.com) 版权所有关于我们联系我们版权申明网站地图