MySQL ddl语句的使用

站长资源 2024/11/24 佚名

17 1538 17

DDR爱好者之家 Design By 杰米

前言

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)。我们用它对数据库、表进行一些管理操作（创建、删除、修改等），比如：建库、删库、建表、修改表、删除表、对字段的增删改等，库表结构的管理。

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

数据库管理

创建数据库

create database [if not exists] dbname;

删除数据库

drop databases [if exists] dbname;

完整的写法如下：

drop databases [if exists] o_dbname;
create database n_dbname;

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

测试一下：

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

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

表管理

创建表

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

 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：非空约束

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：提供字段默认值

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：标识主键约束
设置该字段为表的主键，全局唯一标识录，插入重复时报错。

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

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)，这种叫组合主键（或复合主键），举个栗子：

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为外键表，当前表和外键表有一个字段约束成外键。

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：标识唯一值约束
可以设置一个到多个字段，不允许重复值，重复会报违反唯一约束，导致插入失败。

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

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：标识自动增长

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、如果表数据被清空并重启数据库，自增列会从初始值开始。

删除表

drop table [if exists] tname;

修改表名、备注

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

复制表

仅复制架构

create table tname like from_tname;

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

复制架构+数据

create table tname [as] select column1,column2,... from from_tname [where condition];

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

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

管理字段

添加字段

alter table tname add column column_name column_type [constraints];

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

修改字段

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示例：

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

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

删除字段

alter table tname drop column col_name;

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语句的资料请关注其它相关文章！

MySQL,ddl语句,MySQL,ddl

DDR爱好者之家 Design By 杰米

广告合作：本站广告合作请联系QQ：858582 申请时备注：广告合作（否则不回）
免责声明：本站资源来自互联网收集,仅供用于学习和交流,请遵循相关法律法规,本站一切资源不代表本站立场,如有侵权、后门、不妥请联系本站删除！

DDR爱好者之家 Design By 杰米

评论“MySQL ddl语句的使用”

暂无评论...

MySQL ddl语句的使用

专业级的MySQL开发设计规范及SQL编写规范

Navicat如何远程连接云服务器数据库

评论“MySQL ddl语句的使用”

《魔兽世界》大逃杀！60人新游玩模式《强袭风暴》3月21日上线

更新日志

友情链接