mysql 修改表所属的库_mysql创建修改删除表和库

最新推荐文章于 2023-12-03 18:54:54 发布

张涵赫

最新推荐文章于 2023-12-03 18:54:54 发布

阅读量355

点赞数

文章标签： mysql 修改表所属的库

本文链接：https://blog.csdn.net/weixin_42501688/article/details/113281370

版权

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

一、库的创建、修改、删除

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

一、库的创建和删除

1、创建

用CREATE DATABASE指令。

我们先看一下帮助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

试一下：MariaDB [hell]> CREATE DATABASE testdb;

Query OK, 1 row affected (0.03 sec)

MariaDB [(none)]> SHOW DATABASES;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| test |

| testdb |

+--------------------+

5 rows in set (0.00 sec)

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

在库已存在的情况下这样会报错，并退出语句，在执行一个语句脚本的时候不想这样退出。

可以加上IF NOT EXISTS。语句不会退出，不过会有一个警告。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)]>

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

[DEFAULT] CHARACTER SET [=] charset_name

| [DEFAULT] COLLATE [=] collation_nameMariaDB [(none)]> CREATE DATABASE testdb2 CHARACTER SET = utf8 COLLATE = utf8_bin;

Query OK, 1 row affected (0.00 sec)

用 SHOW CHARACTER SET; 和SHOW COLLATION;来查询字符集和排序规则。

排序规则是依附于字符集的。同一个符集有多个排序规则。

查看当前会话默认字符集：可以通过修改变量或配置文件来修改默认字符集。MariaDB [(none)]> SHOW VARIABLES LIKE 'character%';

+--------------------------+----------------------------------------+

| Variable_name | Value |

+--------------------------+----------------------------------------+

| character_set_client | utf8 |

| character_set_connection | utf8 |

| character_set_database | latin1 |

| character_set_filesystem | binary |

| character_set_results | utf8 |

| character_set_server | latin1 |

| character_set_system | utf8 |

| character_sets_dir | /usr/local/mysql5.5.46/share/charsets/ |

+--------------------------+----------------------------------------+

8 rows in set (0.00 sec)

2、修改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指令。MariaDB [(none)]> HELP DROP DATABASE;

Name: 'DROP DATABASE'

Description:

Syntax:

DROP {DATABASE | SCHEMA} [IF EXISTS] db_nameMariaDB [(none)]> DROP DATABASE testdb;

Query OK, 0 rows affected (0.00 sec)

这个直接删除就可以了。

最后说一点：

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

最后最好还是不要重命名。

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

1、创建

CREATE TABLE

看一下帮助，这个有点多，不过主要分的是几个部分。

下面就是总的部分，创建表的主要指令。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) }

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

举个例子看看：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。

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

定义创建表的信息。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 条件约束

字段定义的说明部分：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

再举个例子：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。

MariaDB [testdb1]> CREATE TABLE tab8 (ID INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, Name CHAR(25) NOT NULL,age TINYINT UNSIGNED NOT NULL, gender ENUM('M','N') NOT NULL, ps VARCHAR(50)) ENGINE=MyISAM COMMENT 'test table';

Query OK, 0 rows affected (0.05 sec)

MariaDB [testdb1]> SHOW TABLE STATUS LIKE 'tab8'\G

*************************** 1. row ***************************

Name: tab8

Engine: MyISAM

Version: 10

Row_format: Dynamic

Rows: 0

Avg_row_length: 0

Data_length: 0

Max_data_length: 281474976710655

Index_length: 1024

Data_free: 0

Auto_increment: 1

Create_time: 2015-11-01 04:12:47

Update_time: 2015-11-01 04:12:47

Check_time: NULL

Collation: utf8_general_ci

Checksum: NULL

Create_options:

Comment: test table

1 row in set (0.00 sec)

MariaDB [testdb1]>

MariaDB [testdb1]> DESC tab8;

+--------+---------------------+------+-----+---------+----------------+

+--------+---------------------+------+-----+---------+----------------+

| ID | int(10) unsigned | NO | PRI | NULL | auto_increment |

| age | tinyint(3) unsigned | NO | | NULL | |

| ps | varchar(50) | YES | | NULL | |

+--------+---------------------+------+-----+---------+----------------+

5 rows in set (0.00 sec)

MariaDB [testdb1]>

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

试一下：MariaDB [testdb1]> CREATE TABLE tab9 SELECT * FROM tab8;

Query OK, 3 rows affected (0.02 sec)

Records: 3 Duplicates: 0 Warnings: 0

MariaDB [testdb1]> DESC tab9;

+--------+---------------------+------+-----+---------+-------+

+--------+---------------------+------+-----+---------+-------+

| ID | int(10) unsigned | NO | | 0 | |

| age | tinyint(3) unsigned | NO | | NULL | |

| ps | varchar(50) | YES | | NULL | |

+--------+---------------------+------+-----+---------+-------+

5 rows in set (0.00 sec)

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

第三种方式是专门用来复制表结构的。MariaDB [testdb1]> CREATE TABLE tab10 LIKE tab8;

Query OK, 0 rows affected (0.01 sec)

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

2、修改

alter tableMariaDB [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] 转换字符集及排序规则，还有表项(与创建表时候的表项一样)。

举例子说明吧：

ADDADD [COLUMN] col_name column_definition

[FIRST | AFTER col_name ]

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

只添加单个字段可以这样，如果一下添加多个字段就要用括号括起来，并用逗号分割各个字段。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'。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字段的后面。每次改名必须要重新定义字段。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: 0MariaDB [testdb1]> DESC tab8;

+--------+---------------------+------+-----+---------+----------------+

+--------+---------------------+------+-----+---------+----------------+

| ID | int(10) unsigned | NO | PRI | NULL | auto_increment |

| age | tinyint(3) unsigned | NO | | NULL | |

| gender | enum('M','N') | NO | | M | |

| ps | varchar(50) | YES | | NULL | |

| PPS | char(39) | YES | | NULL | |

+--------+---------------------+------+-----+---------+----------------+

9 rows in set (0.00 sec)

MODIFY：gender的enum类型值写错了，改一下。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重新赋值。如：MariaDB [testdb1]> UPDATE tab8 SET gender = 'F' WHERE gender=''; #这里表示是空字符，如果用gender IS NULL,就不行了。

Query OK, 2 rows affected (0.00 sec)

Rows matched: 2 Changed: 2 Warnings: 0

DROP：删除字段。MariaDB [testdb1]> ALTER TABLE tab8 DROp ps;

Query OK, 4 rows affected (0.00 sec)

Records: 4 Duplicates: 0 Warnings: 0

RENAME:MariaDB [testdb1]> ALTER TABLE tab8 RENAME TO tab0;

Query OK, 0 rows affected (0.01 sec)

MariaDB [testdb1]>

修改表名有个专门的指令，就叫做RENAME.mysql> RENAME TABLE old_name TO new_name;

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

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

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

3、删除MariaDB [testdb1]> help drop table;

Name: 'DROP TABLE'

Description:

Syntax:

DROP [TEMPORARY] TABLE [IF EXISTS]

tbl_name [, tbl_name] ...

[RESTRICT | CASCADE]

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

简单的使用，谢谢大家。

张涵赫

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql 修改表所属的库_mysql创建修改删除表和库

数据库就跟一个操作系统一样，使用管理员用户要小心点。一、库的创建、修改、删除二、表的创建、修改、删除一、库的创建和删除1、创建用CREATE DATABASE指令。我们先看一下帮助MariaDB[hell]>helpCREATEDATABASE;Name:'CREATEDATABASE'Description:Syntax:CREATE{DATABASE|SCHEMA}...
复制链接

扫一扫