mysql增加修改删除库和表

42121 · 发表于 2015-11-2 08:46:11

数据库就跟一个操作系统一样，使用管理员用户要小心点。

一、库的创建和删除
二、表的创建、修改、删除

一、库的创建和删除
1、创建
用CREATE DATABASE指令。

我们先看一下帮助

1
2
3
4
5
6
7
8
9
10

MariaDB [hell]> help CREATE DATABASE;
Name: 'CREATE DATABASE'
Description:
Syntax:
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
[create_specification] ...

create_specification:
[DEFAULT] CHARACTER SET [=] charset_name
| [DEFAULT] COLLATE [=] collation_name

试一下：

1
2
3
4
5
6
7
8
9
10
11
12
13

DATABASE关键字换成SCHEMA也可以，在Oracle数据库中是Schema。

在库已存在的情况下这样会报错，并退出语句，在执行一个语句脚本的时候不想这样退出。
可以加上IF NOT EXISTS。语句不会退出，不过会有一个警告。

1
2
3
4
5
6
7
8
9
10
11
12
13
14

MariaDB [(none)]> CREATE DATABASE testdb;
ERROR 1007 (HY000): Can't create database 'testdb'; database exists    #报错并退出
MariaDB [(none)]> CREATE DATABASE IF NOT EXISTS testdb;
Query OK, 1 row affected, 1 warning (0.00 sec)

MariaDB [(none)]> SHOW WARNINGS;
+-------+------+-------------------------------------------------+
| Level | Code | Message                                        |
+-------+------+-------------------------------------------------+
| Note  | 1007 | Can't create database 'testdb'; database exists |
+-------+------+-------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]>

可以修改字符集和排序规则，而不是使用默认的。

1
2
3

create_specification:
[DEFAULT] CHARACTER SET [=] charset_name
| [DEFAULT] COLLATE [=] collation_name

1 2	MariaDB [(none)]> CREATE DATABASE testdb2 CHARACTER SET = utf8 COLLATE = utf8_bin; Query OK, 1 row affected (0.00 sec)

用 SHOW CHARACTER SET; 和SHOW COLLATION;来查询字符集和排序规则。
排序规则是依附于字符集的。同一个符集有多个排序规则。
查看当前会话默认字符集：可以通过修改变量或配置文件来修改默认字符集。

1
2
3
4
5
6
7
8
9
10
11
12
13
14

2、修改

1
2
3
4
5
6
7
8
9
10
11
12

MariaDB [testdb1]> help alter database;
Name: 'ALTER DATABASE'
Description:
Syntax:
ALTER {DATABASE | SCHEMA} [db_name]
alter_specification ...
ALTER {DATABASE | SCHEMA} db_name
UPGRADE DATA DIRECTORY NAME

alter_specification:
[DEFAULT] CHARACTER SET [=] charset_name
| [DEFAULT] COLLATE [=] collation_name

一般就是修改字符集和排序规则。
UPGRADE DATA DIRECTORY NAME

用于重新更新数据库的数据字典的。一般只是在数据库版本升级以后不兼容才会用。

3、删除数据库
用DROP DATABASE指令。

1
2
3
4
5

MariaDB [(none)]> HELP DROP DATABASE;
Name: 'DROP DATABASE'
Description:
Syntax:
DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

1 2	MariaDB [(none)]> DROP DATABASE testdb; Query OK, 0 rows affected (0.00 sec)

这个直接删除就可以了。

最后说一点：
如果要重命名数据库，最好的办法就是把表全部导出来，然后删除库，再新建库，再导入表。不过代价也是很大的。最好是不要重命名。不要直接在文件系统中重命名数据库名，也就是目录名。因为一些元数据是不会变的。因此千万不要直接修改身为数据库的目录名。
最后最好还是不要重命名。

二、表的创建、修改、删除
1、创建

CREATE TABLE
看一下帮助，这个有点多，不过主要分的是几个部分。
下面就是总的部分，创建表的主要指令。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

MariaDB [(none)]> help create table;
Name: 'CREATE TABLE'
Description:
Syntax:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name #TEMPORARY临时表，在内存中的。只有管理员可以使用。
(create_definition,...)       #定义创建表的信息，主要是与字段相关的。在一个大括号。
[table_options]                #关于表的选项。
[partition_options]          #划分表，这个就不说了。

Or:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)]
[table_options]
[partition_options]
select_statement

Or:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
{ LIKE old_tbl_name | (LIKE old_tbl_name) }

三种创建方式。第一种就是一般创建，第二种是通过查询到的数据填充到表里，表结构自动定义，第三种是根据另一张表的结构来自动创建表。暂时先只说第一种方式的。

举个例子看看：

1
2
3
4
5
6

MariaDB [(none)]> use testdb1; #默认数据库
Database changed
MariaDB [testdb1]> CREATE TABLE tab1 (Name char(20)); #创建表
Query OK, 0 rows affected (0.04 sec)

MariaDB [testdb1]>

创建一个简单的表tab1。括号里面的就是定义的表的信息了，Name字段，char数据类型，长度20。

看一下下面这个，就是定义的格式了。

定义创建表的信息。

1
2
3
4
5
6
7
8
9
10
11
12
13
14

create_definition:
col_name column_definition
  | [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)
   [index_option] ...
  | {INDEX|KEY} [index_name] [index_type] (index_col_name,...)
   [index_option] ...
  | [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY]
   [index_name] [index_type] (index_col_name,...)
   [index_option] ...
  | {FULLTEXT|SPATIAL} [INDEX|KEY] [index_name] (index_col_name,...)
   [index_option] ...
  | [CONSTRAINT [symbol]] FOREIGN KEY
   [index_name] (index_col_name,...) reference_definition
  | CHECK (expr)

包括字段名称和字段定义或者是约束和索引。
约束和索引可以先不去管它，只要知道这里是专门单独的给各个字段定义约束和索引的。

而在字段定义中也可以给所对应的字段定义一些约束。
上面的约束和索引包括：

上面的CONSTRAINT是可以省略的。
PRIMARY KEY 定义主键约束
UNIQUE 唯一键约束

FULLTEXT|SPATIAL 是用来定义全文和空间索引。

FOREIGN KEY 外键约束
CHECK 条件约束

字段定义的说明部分：

1
2
3
4
5
6
7

column_definition:
data_type [NOT NULL | NULL] [DEFAULT default_value]
   [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
   [COMMENT 'string']
   [COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}]
   [STORAGE {DISK|MEMORY|DEFAULT}]
   [reference_definition]

字段的定义又包括数据类型和类型修改符。数据类型太多了，就不帖出来了。

AUTO_INCREMENT: 自动增长
UNIQUE 唯一键约束
PRIMARY KEY 主键终束
COMMENT：注释信息

使用大致就是：
CREATE TABLE table_name (col_name data_type 类型修饰 | 约束） table_option

再举个例子：

1
2
3
4
5
6
7
8
9
10

MariaDB [testdb1]> CREATE TABLE tab2 (Name CHAR(20) NOT NULL UNIQUE);
Query OK, 0 rows affected (0.05 sec)

MariaDB [testdb1]> CREATE TABLE tab6 (ID int UNSIGNED AUTO_INCREMENT PRIMARY KEY,Name CHAR(20));
Query OK, 0 rows affected (0.02 sec)

MariaDB [testdb1]> CREATE TABLE tab7 (ID int UNSIGNED AUTO_INCREMENT,Name CHAR(20),PRIMARY KEY (id));
Query OK, 0 rows affected (0.09 sec)

MariaDB [testdb1]>

第一个是创建有非空和唯一约束的字段Name。
第二个的UNSIGNED是int数据类型的一部分，表示无符号的。并且是直接在字段中定义了主键约束。
第三个在字段之外单独给字段定义主键约束。

还有表选项，主要会用到的：
ENGINE [=] engine_name
AUTO_INCREMENT [=] value 指定AUTO_INCREMENT从几开始的。
[DEFAULT] CHARACTER SET [=] charset_name 字符集。
[DEFAULT] COLLATE [=] collation_name
COMMENT [=] 'string' 给表加注释
DELAY_KEY_WRITE [=] {0 | 1} 延迟写入，合并多个写入请求，一次性写入。
ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT} 表格式。
TABLESPACE tablespace_name [STORAGE {DISK|MEMORY|DEFAULT}]
可以指定所存放的表空间文件，还可以指定放在DESK或MEMORY内存中。DEFAULT一般就是指DISK。

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
30
31
32
33
34
35
36
37
38
39

第二种方式是用SELECT来从别的表获取数据，再用这些数据填充出来一张表。不过跟原来的表结构就不同了。这样，我们先在刚才的表里插入一些数据。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

MariaDB [testdb1]> INSERT INTO tab8 (Name,age,gender) VALUES ('XIAOA',25,'M'),('ZHANGSAN',30,'M'),('XIAOHUI',28,'N');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

MariaDB [testdb1]> SELECT * FROM tab8;
+----+----------+-----+--------+------+
| ID | Name    | age | gender | ps |
+----+----------+-----+--------+------+
|  4 | XIAOA |  25 | M    | NULL |
|  5 | ZHANGSAN |  30 | M    | NULL |
|  6 | XIAOHUI  |  28 | N    | NULL |
+----+----------+-----+--------+------+
3 rows in set (0.00 sec)

MariaDB [testdb1]>

试一下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

跟原来的表对比一下就发现ID的主键约束没有了，自动增长也没有了。

第三种方式是专门用来复制表结构的。

1 2	MariaDB [testdb1]> CREATE TABLE tab10 LIKE tab8; Query OK, 0 rows affected (0.01 sec)

tab10与tab8的结构一模一样，不过是空的。不过如果想把tab8的数据复制过来，可以用INSERT INTO 的SELECT自动的加入数据。

2、修改

alter table

1
2
3
4
5
6
7

MariaDB [testdb1]> help alter table;
Name: 'ALTER TABLE'
Description:
Syntax:
ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name
[alter_specification [, alter_specification] ...]
[partition_options]

在alter_specification里面主要就是：
ADD新字段或索引，ALTER给字段修改或增加默认值，change字段重命名、定义属性还有字段的位置（排在第几个字段），MODIFY修改字段定义和位置，DROP删除字段，rename表重命名，ORDER BY指定排序标准的字段，CONVERT TO CHARACTER SET charset_name [COLLATE collation_name] 转换字符集及排序规则，还有表项（与创建表时候的表项一样）。

举例子说明吧：
ADD

1 2	ADD [COLUMN] col_name column_definition [FIRST \| AFTER col_name ]

ADD添加字段。class字段，数据类型 char，默认值'math'。上面的first是把字段放到第一个位置，after就是在某个字段之后。

1
2
3

MariaDB [testdb1]> ALTER TABLE tab8 ADD class CHAR(30) DEFAULT 'math';
Query OK, 4 rows affected (0.02 sec)
Records: 4 Duplicates: 0 Warnings: 0

只添加单个字段可以这样，如果一下添加多个字段就要用括号括起来，并用逗号分割各个字段。

1
2
3

MariaDB [testdb1]> ALTER TABLE tab8 ADD (weight tinyint unsigned, height tinyint unsigned);
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0

ALTER：给gender字段添加默认值为'M'。

1
2
3

MariaDB [testdb1]> ALTER TABLE tab8 ALTER gender SET DEFAULT 'M';
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

CHANGE：class字段改为course，并放到Name字段的后面。每次改名必须要重新定义字段。

1
2
3

MariaDB [testdb1]> ALTER TABLE tab8 CHANGE class course char(30) default 'math' AFTER Name;
Query OK, 4 rows affected (0.02 sec)
Records: 4 Duplicates: 0 Warnings: 0

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

MODIFY：gender的enum类型值写错了，改一下。

1
2
3

MariaDB [testdb1]> ALTER TABLE tab8 MODIFY gender ENUM('M','F');
Query OK, 4 rows affected, 2 warnings (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 2

有警告，因为我们表里面有数据，原来为N的值变成空了。可以用update重新赋值。如：

1
2
3

MariaDB [testdb1]> UPDATE tab8 SET gender = 'F' WHERE gender='';
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0

DROP：删除字段。

1
2
3

MariaDB [testdb1]> ALTER TABLE tab8 DROp ps;
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0

RENAME:

1
2
3
4

MariaDB [testdb1]> ALTER TABLE tab8 RENAME TO tab0;
Query OK, 0 rows affected (0.01 sec)

MariaDB [testdb1]>

修改表名有个专门的指令，就叫做RENAME.

1	mysql> RENAME TABLE old_name TO new_name;

修改表项中的存储引擎的时候注意一下。

修改引擎的背后工作机制是创建一个所指定引擎的新表，并把老的表中的数据导入新表中，是存在风险的。

都是简单的用法，复杂的就朋友们自己研究吧。

3、删除

1
2
3
4
5
6
7

MariaDB [testdb1]> help drop table;
Name: 'DROP TABLE'
Description:
Syntax:
DROP [TEMPORARY] TABLE [IF EXISTS]
tbl_name [, tbl_name] ...
[RESTRICT | CASCADE]

这个就不举例子了，注意的是CASCADE，有时候一个表是被另一个表所关联的。加上CASCADE就是把所有关联的表也一起删除。

简单的使用，谢谢大家。

账号		自动登录	找回密码
密码			立即注册

大疆运维招人啦，

C++ :try 语句块和异常处理

C++的多态

Red Hat RHCE 8 (EX294) Cert Guide

Java/C++ 区别：看完这一篇，就够用！

别再用过时库了！这 13 个顶级 C++ 库才是

c++ size_t 和 int 的区别

[经验分享] mysql增加修改删除库和表

相关帖子

扫码加入运维网微信交流群