Mysql DDL常见操作汇总

2023-07-15 20:49:03 164

库的管理

创建库

createdatabase[ifnotexists]库名;

删除库

dropdatabases[ifexists]库名;

建库通用的写法

dropdatabaseifexists旧库名;
createdatabase新库名;

示例

mysql>showdatabaseslike'javacode2018';
+-------------------------+
|Database(javacode2018)|
+-------------------------+
|javacode2018|
+-------------------------+
1rowinset(0.00sec)

mysql>dropdatabaseifexistsjavacode2018;
QueryOK,0rowsaffected(0.00sec)

mysql>showdatabaseslike'javacode2018';
Emptyset(0.00sec)

mysql>createdatabasejavacode2018;
QueryOK,1rowaffected(0.00sec)

showdatabaseslike‘javacode2018';列出javacode2018库信息。

表管理

创建表

createtable表名(
字段名1类型[(宽度)][约束条件][comment'字段说明'],
字段名2类型[(宽度)][约束条件][comment'字段说明'],
字段名3类型[(宽度)][约束条件][comment'字段说明']
)[表的一些设置];

注意：

在同一张表中，字段名不能相同
宽度和约束条件为可选参数，字段名和类型是必须的
最后一个字段后不能加逗号
类型是用来限制字段必须以何种数据类型来存储记录
类型其实也是对字段的约束(约束字段下的记录必须为XX类型)
类型后写的约束条件是在类型之外的额外添加的约束

约束说明

notnull：标识该字段不能为空

mysql>createtabletest1(aintnotnullcomment'字段a');
QueryOK,0rowsaffected(0.01sec)

mysql>insertintotest1values(null);
ERROR1048(23000):Column'a'cannotbenull
mysql>insertintotest1values(1);
QueryOK,1rowaffected(0.00sec)

mysql>select*fromtest1;
+---+
|a|
+---+
|1|
+---+
1rowinset(0.00sec)

**defaultvalue：**为该字段设置默认值，默认值为value

mysql>droptableIFEXISTStest2;
QueryOK,0rowsaffected(0.01sec)

mysql>createtabletest2(
->aintnotnullcomment'字段a',
->bintnotnulldefault0comment'字段b'
->);
QueryOK,0rowsaffected(0.02sec)

mysql>insertintotest2(a)values(1);
QueryOK,1rowaffected(0.00sec)

mysql>select*fromtest2;
+---+---+
|a|b|
+---+---+
|1|0|
+---+---+
1rowinset(0.00sec)

上面插入时未设置b的值，自动取默认值0

**primarykey：**标识该字段为该表的主键，可以唯一的标识记录，插入重复的会报错

两种写法，如下：

方式1：跟在列后，如下：

mysql>droptableIFEXISTStest3;
QueryOK,0rowsaffected,1warning(0.00sec)

mysql>createtabletest3(
->aintnotnullcomment'字段a'primarykey
->);
QueryOK,0rowsaffected(0.01sec)

mysql>insertintotest3(a)values(1);
QueryOK,1rowaffected(0.01sec)

mysql>insertintotest3(a)values(1);
ERROR1062(23000):Duplicateentry'1'forkey'PRIMARY'

方式2：在所有列定义之后定义，如下：

mysql>droptableIFEXISTStest4;
QueryOK,0rowsaffected,1warning(0.00sec)

mysql>createtabletest4(
->aintnotnullcomment'字段a',
->bintnotnulldefault0comment'字段b',
->primarykey(a)
->);
QueryOK,0rowsaffected(0.02sec)

mysql>insertintotest4(a,b)values(1,1);
QueryOK,1rowaffected(0.00sec)

mysql>insertintotest4(a,b)values(1,2);
ERROR1062(23000):Duplicateentry'1'forkey'PRIMARY'

插入重复的值，会报违法主键约束

方式2支持多字段作为主键，多个之间用逗号隔开，语法：primarykey(字段1,字段2,字段n)，示例：

mysql>droptableIFEXISTStest7;
QueryOK,0rowsaffected,1warning(0.00sec)

mysql>
mysql>createtabletest7(
->aintnotnullcomment'字段a',
->bintnotnullcomment'字段b',
->PRIMARYKEY(a,b)
->);
QueryOK,0rowsaffected(0.02sec)

mysql>
mysql>insertintotest7(a,b)VALUES(1,1);
QueryOK,1rowaffected(0.00sec)

mysql>insertintotest7(a,b)VALUES(1,1);
ERROR1062(23000):Duplicateentry'1-1'forkey'PRIMARY'

foreignkey：为表中的字段设置外键

语法：foreignkey(当前表的列名)references引用的外键表(外键表中字段名称)

mysql>droptableIFEXISTStest6;
QueryOK,0rowsaffected(0.01sec)

mysql>droptableIFEXISTStest5;
QueryOK,0rowsaffected(0.01sec)

mysql>
mysql>createtabletest5(
->aintnotnullcomment'字段a'primarykey
->);
QueryOK,0rowsaffected(0.02sec)

mysql>
mysql>createtabletest6(
->bintnotnullcomment'字段b',
->ts5_aintnotnull,
->foreignkey(ts5_a)referencestest5(a)
->);
QueryOK,0rowsaffected(0.01sec)

mysql>insertintotest5(a)values(1);
QueryOK,1rowaffected(0.00sec)

mysql>insertintotest6(b,test6.ts5_a)values(1,1);
QueryOK,1rowaffected(0.00sec)

mysql>insertintotest6(b,test6.ts5_a)values(2,2);
ERROR1452(23000):Cannotaddorupdateachildrow:aforeignkeyconstraintfails(`javacode2018`.`test6`,CONSTRAINT`test6_ibfk_1`FOREIGNKEY(`ts5_a`)REFERENCES`test5`(`a`))

说明：表示test6中ts5_a字段的值来源于表test5中的字段a。

注意几点：

两张表中需要建立外键关系的字段类型需要一致
要设置外键的字段不能为主键
被引用的字段需要为主键
被插入的值在外键表必须存在，如上面向test6中插入ts5_a为2的时候报错了，原因：2的值在test5表中不存在

uniquekey(uq)：标识该字段的值是唯一的

支持一个到多个字段，插入重复的值会报违反唯一约束，会插入失败。

定义有2种方式。

方式1：跟在字段后，如下：

mysql>droptableIFEXISTStest8;
QueryOK,0rowsaffected,1warning(0.00sec)

mysql>
mysql>createtabletest8(
->aintnotnullcomment'字段a'uniquekey
->);
QueryOK,0rowsaffected(0.01sec)

mysql>
mysql>insertintotest8(a)VALUES(1);
QueryOK,1rowaffected(0.00sec)

mysql>insertintotest8(a)VALUES(1);
ERROR1062(23000):Duplicateentry'1'forkey'a'

方式2：所有列定义之后定义，如下：

mysql>droptableIFEXISTStest9;
QueryOK,0rowsaffected,1warning(0.00sec)

mysql>
mysql>createtabletest9(
->aintnotnullcomment'字段a',
->uniquekey(a)
->);
QueryOK,0rowsaffected(0.01sec)

mysql>
mysql>insertintotest9(a)VALUES(1);
QueryOK,1rowaffected(0.00sec)

mysql>insertintotest9(a)VALUES(1);
ERROR1062(23000):Duplicateentry'1'forkey'a'

方式2支持多字段，多个之间用逗号隔开，语法：primarykey(字段1,字段2,字段n)，示例：

mysql>droptableIFEXISTStest10;
QueryOK,0rowsaffected,1warning(0.00sec)

mysql>
mysql>createtabletest10(
->aintnotnullcomment'字段a',
->bintnotnullcomment'字段b',
->uniquekey(a,b)
->);
QueryOK,0rowsaffected(0.01sec)

mysql>
mysql>insertintotest10(a,b)VALUES(1,1);
QueryOK,1rowaffected(0.00sec)

mysql>insertintotest10(a,b)VALUES(1,1);
ERROR1062(23000):Duplicateentry'1-1'forkey'a'

auto_increment：标识该字段的值自动增长（整数类型，而且为主键）

mysql>droptableIFEXISTStest11;
QueryOK,0rowsaffected,1warning(0.00sec)

mysql>
mysql>createtabletest11(
->aintnotnullAUTO_INCREMENTPRIMARYKEYcomment'字段a',
->bintnotnullcomment'字段b'
->);
QueryOK,0rowsaffected(0.01sec)

mysql>
mysql>insertintotest11(b)VALUES(10);
QueryOK,1rowaffected(0.00sec)

mysql>insertintotest11(b)VALUES(20);
QueryOK,1rowaffected(0.00sec)

mysql>select*fromtest11;
+---+----+
|a|b|
+---+----+
|1|10|
|2|20|
+---+----+
2rowsinset(0.00sec)

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

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

注意：
自增长列当前值存储在内存中，数据库每次重启之后，会查询当前表中自增列的最大值作为当前值，如果表数据被清空之后，数据库重启了，自增列的值将从初始值开始

我们来演示一下：

mysql>deletefromtest11;
QueryOK,2rowsaffected(0.00sec)

mysql>insertintotest11(b)VALUES(10);
QueryOK,1rowaffected(0.00sec)

mysql>select*fromtest11;
+---+----+
|a|b|
+---+----+
|3|10|
+---+----+
1rowinset(0.00sec)

上面删除了test11数据，然后插入了一条，a的值为3，执行下面操作：

删除test11数据，重启mysql，插入数据，然后看a的值是不是被初始化了？如下：

mysql>deletefromtest11;
QueryOK,1rowaffected(0.00sec)

mysql>select*fromtest11;
Emptyset(0.00sec)

mysql>exit
Bye

C:\Windows\system32>netstopmysql
mysql服务正在停止..
mysql服务已成功停止。


C:\Windows\system32>netstartmysql
mysql服务正在启动.
mysql服务已经启动成功。


C:\Windows\system32>mysql-uroot-p
Enterpassword:*******
WelcometotheMySQLmonitor.Commandsendwith;or\g.
YourMySQLconnectionidis2
Serverversion:5.7.25-logMySQLCommunityServer(GPL)

Copyright(c)2000,2019,Oracleand/oritsaffiliates.Allrightsreserved.

OracleisaregisteredtrademarkofOracleCorporationand/orits
affiliates.Othernamesmaybetrademarksoftheirrespective
owners.

Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement.

mysql>usejavacode2018;
Databasechanged
mysql>select*fromtest11;
Emptyset(0.01sec)

mysql>insertintotest11(b)value(100);
QueryOK,1rowaffected(0.00sec)

mysql>select*fromtest11;
+---+-----+
|a|b|
+---+-----+
|1|100|
+---+-----+
1rowinset(0.00sec)

删除表

droptable[ifexists]表名;

修改表名

altertable表名rename[to]新表名;

表设置备注

altertable表名comment'备注信息';

复制表

createtable表名like被复制的表名;

如：

mysql>createtabletest12liketest11;
QueryOK,0rowsaffected(0.01sec)

mysql>select*fromtest12;
Emptyset(0.00sec)

mysql>showcreatetabletest12;
+--------+-------+
|Table|CreateTable
+--------+-------+
|test12|CREATETABLE`test12`(
`a`int(11)NOTNULLAUTO_INCREMENTCOMMENT'字段a',
`b`int(11)NOTNULLCOMMENT'字段b',
PRIMARYKEY(`a`)
)ENGINE=InnoDBDEFAULTCHARSET=utf8|
+--------+-------+
1rowinset(0.00sec)

复制表结构+数据

createtable表名[as]select字段,...from被复制的表[where条件];

如：

mysql>createtabletest13asselect*fromtest11;
QueryOK,1rowaffected(0.02sec)
Records:1Duplicates:0Warnings:0

mysql>select*fromtest13;
+---+-----+
|a|b|
+---+-----+
|1|100|
+---+-----+
1rowinset(0.00sec)

表结构和数据都过来了。

表中列的管理

添加列

altertable表名addcolumn列名类型[列约束];

示例：

mysql>droptableIFEXISTStest14;
QueryOK,0rowsaffected,1warning(0.00sec)

mysql>
mysql>createtabletest14(
->aintnotnullAUTO_INCREMENTPRIMARYKEYcomment'字段a'
->);
QueryOK,0rowsaffected(0.02sec)

mysql>altertabletest14addcolumnbintnotnulldefault0comment'字段b';
QueryOK,0rowsaffected(0.03sec)
Records:0Duplicates:0Warnings:0

mysql>altertabletest14addcolumncintnotnulldefault0comment'字段c';
QueryOK,0rowsaffected(0.05sec)
Records:0Duplicates:0Warnings:0

mysql>insertintotest14(b)values(10);
QueryOK,1rowaffected(0.00sec)

mysql>select*fromtest14;c
+---+----+---+
|a|b|c|
+---+----+---+
|1|10|0|
+---+----+---+
1rowinset(0.00sec)

修改列

altertable表名modifycolumn列名新类型[约束];
或者
altertable表名changecolumn列名新列名新类型[约束];

2种方式区别：modify不能修改列名，change可以修改列名

我们看一下test14的表结构：

mysql>showcreatetabletest14;
+--------+--------+
|Table|CreateTable|
+--------+--------+
|test14|CREATETABLE`test14`(
`a`int(11)NOTNULLAUTO_INCREMENTCOMMENT'字段a',
`b`int(11)NOTNULLDEFAULT'0'COMMENT'字段b',
`c`int(11)NOTNULLDEFAULT'0'COMMENT'字段c',
PRIMARYKEY(`a`)
)ENGINE=InnoDBAUTO_INCREMENT=2DEFAULTCHARSET=utf8|
+--------+--------+
1rowinset(0.00sec)

我们将字段c名字及类型修改一下，如下:

mysql>altertabletest14changecolumncdvarchar(10)notnulldefault''comment'字段d';
QueryOK,0rowsaffected(0.01sec)
Records:0Duplicates:0Warnings:0

mysql>showcreatetabletest14;;;
+--------+--------+
|Table|CreateTable|
+--------+--------+
|test14|CREATETABLE`test14`(
`a`int(11)NOTNULLAUTO_INCREMENTCOMMENT'字段a',
`b`int(11)NOTNULLDEFAULT'0'COMMENT'字段b',
`d`varchar(10)NOTNULLDEFAULT''COMMENT'字段d',
PRIMARYKEY(`a`)
)ENGINE=InnoDBAUTO_INCREMENT=2DEFAULTCHARSET=utf8|
+--------+--------+
1rowinset(0.00sec)

删除列

altertable表名dropcolumn列名;

示例：

mysql>altertabletest14dropcolumnd;
QueryOK,0rowsaffected(0.05sec)
Records:0Duplicates:0Warnings:0

mysql>showcreatetabletest14;
+--------+--------+
|Table|CreateTable|
+--------+--------+
|test14|CREATETABLE`test14`(
`a`int(11)NOTNULLAUTO_INCREMENTCOMMENT'字段a',
`b`int(11)NOTNULLDEFAULT'0'COMMENT'字段b',
PRIMARYKEY(`a`)
)ENGINE=InnoDBAUTO_INCREMENT=2DEFAULTCHARSET=utf8|
+--------+--------+
1rowinset(0.00sec)

到此这篇关于MysqlDDL常见操作汇总的文章就介绍到这了,更多相关MysqlDDL操作内容请搜索毛票票以前的文章或继续浏览下面的相关文章希望大家以后多多支持毛票票！

声明：本文内容来源于网络，版权归原作者所有，内容由互联网用户自发贡献自行上传，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任。如果您发现有涉嫌版权的内容，欢迎发送邮件至：czq8825#qq.com（发邮件时，请将#更换为@）进行举报，并提供相关证据，一经查实，本站将立刻删除涉嫌侵权内容。

Mysql DDL常见操作汇总

热门推荐

随机推荐