MySQL ddl语句用法详细说明
这篇文章主要为大家详细介绍了MySQL ddl语句用法示例,具有一定的参考价值,可以用来参考一下。
感兴趣的小伙伴,下面一起跟随数据库教程的小编来看看吧!
前言
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 2 | <code> create database [ if not exists] dbname;</code> |
MySQL ddl语句的使用
删除数据库
代码如下:
1 2 | <code> drop databases [ if exists] dbname;</code> |
MySQL ddl语句的使用
完整的写法如下:
代码如下:
1 2 3 | <code> drop databases [ if exists] o_dbname; create database n_dbname;</code> |
MySQL ddl语句的使用
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 29 | <code> 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</code> |
MySQL ddl语句的使用
通过上面的测试可以知道:删除之前要先判断数据库是否存在,否则会报出异常;同时创建之前也要判断是否存在,如果存在则会提示已存在。
表管理
创建表
在数据库中一张表的基本语法格式如下:
代码如下:
1 2 3 4 5 6 | <code> 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];</code> |
MySQL ddl语句的使用
语法说明
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 9 | <code> 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</code> |
MySQL ddl语句的使用
建表的时候,对name字段做了非空约束,这时候传入的值为null,就会有错误提示。所以非空约束的目的是保证字段不为空。
default value:提供字段默认值
代码如下:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | <code> 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</code> |
MySQL ddl语句的使用
设置了默认值之后,如果在写入数据时,不指定值,他会自动取默认值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 24 | <code> 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</code> |
MySQL ddl语句的使用
如上,主键必须保持值的唯一性,如果插入重复值,会提示违反主键约束
另外一种方式是在字段声明的尾部,可以支持多个主键,用逗号隔开并且不可重复,格式:primary key(字段1,字段2,字段n),这种叫组合主键(或复合主键),举个栗子:
代码如下:
1 2 | <code> 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));</code> |
MySQL ddl语句的使用
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 23 | <code> 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</code> |
MySQL ddl语句的使用
几点说明:
1、插入user4表的时候,会检查关联的外键classid的值是否存在,如果不存在就会报错误。如上述代码中第三段,classid=1的值在class表中不存在。
2、建立外键关系的两张表的对应字段,类型需要保持一致。
3、设置为外键的字段不能为本表的主键,而关联表的字段需要为主键。(所以外键cid关联到class表的classid字段为主键)。
unique key:标识唯一值约束可以设置一个到多个字段,不允许重复值,重复会报违反唯一约束,导致插入失败。
同样的有两种定义方式,一种是直接在字段后设置,一种是定义完所有字段之后再设置。以下例子:
代码如下:
1 2 3 4 5 6 7 8 9 10 11 | <code> 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' </code> |
MySQL ddl语句的使用
第二段中演示了支持多字段,用逗号隔开,语法格式:unique key(字段1,字段2,字段n);
第三段重复输入了ident的值,他就提示重复输入了。
auto_inc:标识自动增长
代码如下:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | <code> 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</code> |
MySQL ddl语句的使用
auto_increment 说明:
1、auto_increacement 的字段为自动增长,默认值从1开始,每次+1
2、自动增长字段的初始值、步长可以在mysql中进行设置,比如设置初始值为1万,步长每次增长10
3、自增列当前值存储在内存中,数据库重启后,会查询当前表中自增列max为当前值。
4、如果表数据被清空并重启数据库,自增列会从初始值开始。
删除表
代码如下:
1 2 | <code> drop table [ if exists] tname;</code> |
MySQL ddl语句的使用
修改表名、备注
代码如下:
1 2 3 | <code> alter table o_tname rename [to] n_tname; alter table tname comment 'memo' ; </code> |
MySQL ddl语句的使用
复制表
仅复制架构
代码如下:
1 2 | <code> create table tname like from_tname; </code> |
MySQL ddl语句的使用
代码如下:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | <code> 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</code> |
MySQL ddl语句的使用
复制架构+数据
代码如下:
1 2 | <code> create table tname [ as ] select column1,column2,... from from_tname [where condition]; </code> |
MySQL ddl语句的使用
代码如下:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | <code> 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</code> |
MySQL ddl语句的使用
数据和架构都被复制过来了,这个超实用。
管理字段
添加字段
代码如下:
1 2 | <code> alter table tname add column column_name column_type [constraints];</code> |
MySQL ddl语句的使用
代码如下:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | <code> 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</code> |
MySQL ddl语句的使用
修改字段
代码如下:
1 2 3 | <code> 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]; -- 修改字段名、类型、约束</code> |
MySQL ddl语句的使用
以下分别是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 24 | <code> 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</code> |
MySQL ddl语句的使用
代码如下:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | <code> 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</code> |
MySQL ddl语句的使用
删除字段
代码如下:
1 2 | <code> alter table tname drop column col_name; </code> |
MySQL ddl语句的使用
代码如下:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | <code> 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</code> |
MySQL ddl语句的使用
以上就是MySQL ddl语句的使用的详细内容,更多关于MySQL ddl语句的资料请关注512笔记其它相关文章!
注:关于MySQL ddl语句用法示例的内容就先介绍到这里,更多相关文章的可以留意