MySQL ddl语句的使用

2023-07-11 20:32:04 168

前言

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

DDL	数据定义语言	create、drop、alter	数据定义语言 create、drop、alter 语句。
DML	数据操纵语言	insert、delete、update	定义对数据库记录的增、删、改操作。
DQL	数据库查询语言	select	定义对数据库记录的查询操作。
DCL	数据库控制语言	grant、remove	定义对数据库、表、字段、用户的访问权限和安全级别。（授权grant,收回权限revoke等）。
TCL	事务控制语言	setautocommit=0、 starttransaction、 savepoint、commit、rollback	定义对数据库的事务操作。

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

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

数据库管理

创建数据库

createdatabase[ifnotexists]dbname;

删除数据库

dropdatabases[ifexists]dbname;

完整的写法如下：

dropdatabases[ifexists]o_dbname;
createdatabasen_dbname;

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

测试一下：

mysql>showdatabases;
+--------------------+
|Database|
+--------------------+
|information_schema|
|buyerparty|
|buyerparty1|
|git_jeeshop|
|jz|
|kdmy|
|kdmygf|
|localsdk|
|mgrcentercontrol|
|mysql|
|performance_schema|
|stroke_data|
|test|
+--------------------+
13rowsinset

mysql>dropdatabaseifexiststest1;
QueryOK,0rowsaffected

mysql>createdatabasetest1;
QueryOK,1rowaffected

mysql>createdatabasetest1;
1007-Can'tcreatedatabase'test1';databaseexists

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

表管理

创建表

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

createtabletbname(
column_name_1column_type_1[(n)][constraints][comment'comment1'],
column_name_2column_type_2[(n)][constraints][comment'comment2'],
column_name_3column_type_3[(n)][constraints][comment'comment3']
)[table_options];

语法说明

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

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

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

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

约束说明

notnull：非空约束

mysql>usetest;
Databasechanged

mysql>createtableifnotexists`user1`(ageintcomment'年龄',namechar(5)comment'姓名'notnull);
QueryOK,0rowsaffected

mysql>insertintouser1values(8,null);
1048-Column'name'cannotbenull

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

defaultvalue：提供字段默认值

mysql>usetest;
Databasechanged

mysql>createtableifnotexists`user2`(ageintnotnulldefault0comment'年龄',namechar(50)comment'姓名'notnull);
QueryOK,0rowsaffected

mysql>insertintouser2(name)values('brand');
QueryOK,1rowaffected
mysql>select*fromuser2;
+-----+-------+
|age|name|
+-----+-------+
|0|brand|
+-----+-------+
1rowinset

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

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

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

mysql>usetest;
Databasechanged

mysql>createtableifnotexists`user3`(idintprimarykey,ageintnotnulldefault0comment'年龄',namechar(50)comment'姓名'notnull);
QueryOK,0rowsaffected

mysql>insertintouser3values(1,20,'brand');
QueryOK,1rowaffected

mysql>insertintouser3values(1,22,'sol');
1062-Duplicateentry'1'forkey'PRIMARY'

mysql>insertintouser3values(2,22,'sol');
QueryOK,1rowaffected

mysql>select*fromuser3;
+----+-----+-------+
|id|age|name|
+----+-----+-------+
|1|20|brand|
|2|22|sol|
+----+-----+-------+
2rowsinset

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

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

createtableifnotexists`user4`(idint,ageintnotnulldefault0comment'年龄',namechar(50)comment'姓名'notnull,primarykey(id,name));

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

mysql>createtableifnotexists`class`(classidintprimarykey,classnamevarchar(50));
QueryOK,0rowsaffected

mysql>createtableifnotexists`user4`(idintprimarykey,ageintcomment'年龄',namechar(50)comment'姓名',cidintnotnull,foreignkey(cid)referencesclass(classid));
QueryOK,0rowsaffected

mysql>insertinto`user4`values(1,20,'brand',1);
1452-Cannotaddorupdateachildrow:aforeignkeyconstraintfails(`test`.`user4`,CONSTRAINT`user4_ibfk_1`FOREIGNKEY(`cid`)REFERENCES`class`(`classid`))

mysql>insertinto`class`values(1,'grad3');
QueryOK,1rowaffected

mysql>insertinto`user4`values(1,20,'brand',1);
QueryOK,1rowaffected

mysql>selecta.ageas'年龄',a.nameas'学生姓名',b.classnameas'班级'fromuser4aleftjoinclassbona.cid=b.classid;
+------+----------+--------+
|年龄|学生姓名|班级|
+------+----------+--------+
|20|brand|grad3|
+------+----------+--------+
1rowinset

几点说明：

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

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

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

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

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

mysql>createtable`user5`(idintprimarykey,namevarchar(50),identchar(18)uniquekey);
QueryOK,0rowsaffected

mysql>createtable`user6`(idintprimarykey,namevarchar(50),identchar(18)notnull,sexintnotnull,uniquekey(ident,sex));
QueryOK,0rowsaffected

mysql>insertinto`user5`values(1,'brand','012345678901234567');
QueryOK,1rowaffected
mysql>insertinto`user5`values(2,'sol','012345678901234567');
1062-Duplicateentry'012345678901234567'forkey'ident'

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

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

auto_inc：标识自动增长

mysql>createtable`user7`(idintauto_incrementprimarykey,namevarchar(50));
QueryOK,0rowsaffected

mysql>insertinto`user7`(name)values('brand'),('sol'),('helen');
QueryOK,3rowsaffected

Records:3Duplicates:0Warnings:0
mysql>select*from`user7`;
+----+-------+
|id|name|
+----+-------+
|1|brand|
|2|sol|
|3|helen|
+----+-------+
3rowsinset

auto_increment说明：

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

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

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

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

删除表

droptable[ifexists]tname;

修改表名、备注

altertableo_tnamerename[to]n_tname;
altertabletnamecomment'memo';

复制表

仅复制架构

createtabletnamelikefrom_tname;

mysql>select*from`user7`;
+----+-------+
|id|name|
+----+-------+
|1|brand|
|2|sol|
|3|helen|
+----+-------+
3rowsinset

mysql>createtable`user8`like`user7`;
QueryOK,0rowsaffected

mysql>select*from`user8`;
Emptyset

复制架构+数据

createtabletname[as]selectcolumn1,column2,...fromfrom_tname[wherecondition];

mysql>select*from`user7`;
+----+-------+
|id|name|
+----+-------+
|1|brand|
|2|sol|
|3|helen|
+----+-------+
3rowsinset

mysql>createtable`user9`selectid,namefrom`user7`;
QueryOK,3rowsaffected
Records:3Duplicates:0Warnings:0

mysql>select*from`user9`;
+----+-------+
|id|name|
+----+-------+
|1|brand|
|2|sol|
|3|helen|
+----+-------+
3rowsinset

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

管理字段

添加字段

altertabletnameaddcolumncolumn_namecolumn_type[constraints];

mysql>select*from`user9`;
+----+-------+
|id|name|
+----+-------+
|1|brand|
|2|sol|
|3|helen|
+----+-------+
3rowsinset

mysql>altertable`user9`addcolumnnewcolumnintnotnulldefault0;
QueryOK,0rowsaffected
Records:0Duplicates:0Warnings:0

mysql>select*from`user9`;
+----+-------+-----------+
|id|name|newcolumn|
+----+-------+-----------+
|1|brand|0|
|2|sol|0|
|3|helen|0|
+----+-------+-----------+
3rowsinset

修改字段

altertabletnamemodifycolumncol_namenew_col_type[constraints];--修改类型、约束，不能修改字段名
altertabletnamechangecolumncol_namenew_col_namenew_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||
+-----------+-------------+------+-----+---------+-------+
3rowsinset

mysql>altertable`user9`modifycolumnnamevarchar(100);
QueryOK,3rowsaffected
Records:3Duplicates:0Warnings: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||
+-----------+--------------+------+-----+---------+-------+
3rowsinset

mysql>desc`user9`;
+-----------+--------------+------+-----+---------+-------+
|Field|Type|Null|Key|Default|Extra|
+-----------+--------------+------+-----+---------+-------+
|id|int(11)|NO||0||
|name|varchar(100)|YES||NULL||
|newcolumn|int(11)|NO||0||
+-----------+--------------+------+-----+---------+-------+
3rowsinset

mysql>altertable`user9`changecolumnnamename1varchar(100);
QueryOK,0rowsaffected
Records:0Duplicates:0Warnings: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||
+-----------+--------------+------+-----+---------+-------+
3rowsinset

删除字段

altertabletnamedropcolumncol_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||
+-----------+--------------+------+-----+---------+-------+
3rowsinset

mysql>altertable`user9`dropcolumnnewcolumn;
QueryOK,0rowsaffected
Records:0Duplicates:0Warnings:0

mysql>desc`user9`;
+-------+--------------+------+-----+---------+-------+
|Field|Type|Null|Key|Default|Extra|
+-------+--------------+------+-----+---------+-------+
|id|int(11)|NO||0||
|name1|varchar(100)|YES||NULL||
+-------+--------------+------+-----+---------+-------+
2rowsinset

以上就是MySQLddl语句的使用的详细内容，更多关于MySQLddl语句的资料请关注毛票票其它相关文章！

MySQL ddl语句的使用

热门推荐

随机推荐