管理数据库和表
本文介绍如何使用SQL命令管理MySQL的数据库和表
首先经过前几篇文章中对于SQL前置知识的理解,我们知道,数据库和表的关系类似于文件系统中的文件夹和文件,一个数据库中可以有多个表,想要使用表并对其中的数据进行管理,首先应该对其所属数据库进行管理。
管理数据库
创建数据库
在MySQL中,创建数据库的SQL命令相对简单,基本语法如下:
CREATEDATABASE 数据库名;
数据库名应该保证唯一,如果你创建的数据库已经存在,可以添加
IF NOT EXISTS
子句,这样如果数据库已经存在,MySQL不会报错,而是继续执行后续的命令:
CREATEDATABASEIFNOTEXISTS 数据库名;
在创建数据库的过程中可以指定数据库的字符集和排序规则,可以使用
CHARACTER SET
和
COLLATE
子句从5.5.3版本开始,使用
utf8mb4
作为默认字符集,以及相应的
utf8mb4_0900_ai_ci
排序规则,以支持更广泛的Unicode字符,包括emoji和其他复杂的多字节字符。
CREATEDATABASE 数据库名 CHARACTERSET utf8mb4 COLLATE utf8mb4_unicode_ci;
要查看当前服务器的默认字符集和排序规则,你可以运行以下SQL命令:
SHOW VARIABLES LIKE'character_set%';SHOW VARIABLES LIKE'collation%';
在命令行工具中,你可以直接输入这些命令来创建数据库。如果你使用的是图形界面工具,如Navicat等,通常会有相应的图形界面操作来帮助你创建数据库,其底层执行的也是类似的SQL命令。
【示例】创建一个数据库mysql_study,作为我们日后学习mysql时使用的数据库,设置数据库编码为utf8
CREATEDATABASE mysql_study CHARACTERSET utf8mb4 COLLATE utf8mb4_unicode_ci;
如果创建之后在navicat中没有显示,右键连接,刷新
删除数据库
要删除一个数据库,可以使用
DROP DATABASE
命令。如果希望在数据库不存在时避免错误,可以添加
IF EXISTS
选项。
DROPDATABASEIFEXISTS 数据库名;
在数据库中执行删除操作的时候,尤其是删除一整个数据库,你应该非常的谨慎,多确认几遍总不是坏事,或者是提前建立好备份。
备份数据库
在MySQL中,备份数据库通常可以通过逻辑备份和物理备份两种方式进行。以下是这两种方法的具体操作:
逻辑备份
逻辑备份是将数据库中的数据以SQL语句的形式导出到文件中。最常用的工具是
mysqldump
命令。
使用mysqldump命令备份
mysqldump -u 用户名 -p 数据库名 > 备份文件名.sql
-u
后跟数据库用户名。-p
表示需要输入密码,系统会提示你输入。数据库名
是你要备份的数据库名称。备份文件名.sql
是你想保存的备份文件名,通常以.sql结尾。
使用图形化工具备份
如果你更倾向于图形界面操作,可以使用MySQL Workbench、Navicat等工具:
- 连接到你的数据库。
- 寻找“备份”或“导出”功能。
- 选择数据库和备份格式(通常是SQL)。
- 指定备份文件保存的位置和名称。
- 开始备份过程。
【示例】在navicat中备份我们刚创建的数据库mysql_study
物理备份
物理备份是直接复制数据库的物理文件,如数据文件和日志文件。这种方式通常需要数据库处于锁定或非常有限活动的状态,并且操作较为复杂,一般由专业的数据库管理员执行,或者使用像Percona XtraBackup这样的工具来进行热备份(即在数据库运行时备份)。
管理表
对于数据库内部的表操作(如创建、修改表结构、删除表等),则涉及到另一套命令,包括
CREATE TABLE
,
ALTER TABLE
,
DROP TABLE
等。
创建表
列名和数据类型使用空格隔开,而不同列之间使用逗号隔开
CREATE TABLE 表名 (
列名1 数据类型,
列名2 数据类型,
...
PRIMARY KEY (主键列名)
);
NULL
NULL值就是没有值或缺值。允许NULL值的列也允许在插入行时不给出该列的值。不允许NULL值的列不接受该列没有值的行,换句话说,在插入或更新行时,该列必须有值。每个表列或者是NULL列,或者是NOT NULL列,这种状态在创建时由表的定义规定。请看下面的例子:
CREATETABLE orders
(
cust_id intNOTNULLAUTO_INCREMENT,
order_date datetimeNOTNULL,
order_num intNOTNULL,PRIMARYKEY(cust_id ))ENGINE=InnoDB;
在navicat中,可以通过右键表——>【设计表】来查看和管理表中的字段信息
主键
主键值必须唯一。即,表中的每个行必须具有唯一的主键值。如果主键使用单个列,则它的值必须唯一。如果使用多个列,则这些列的组合值必须唯一。作为主键的列,不可以有NULL属性。主键可以在创建表时定义(如这里所示),或者在创建表之后定义.
主键在船建表之后的类似的语句定义:
PRIMARYKEY(列名1[,列名2, 列名3...])
AUTO_INCREMENT
在上面创建orders表的命令中,有一行为:
cust_id intNOTNULLAUTO_INCREMENT,
AUTO_INCREMENT告诉MySQL,本列每当增加一行时自动增量。每次执行一个INSERT操作时,MySQL自动对该列增量(从而才有这个关键字AUTO_INCREMENT),给该列赋予下一个可用的值。这样给每个行分配一个唯一的cust_id,从而可以用作主键值。
每个表只允许一个AUTO_INCREMENT列,而且它必须被索引(如,通过使它成为主键)。
覆盖AUTO_INCREMENT 如果一个列被指定为AUTO_INCREMENT,则它需要使用特殊的值吗?你可以简单地在INSERT语句中指定一个值,只要它是唯一的(至今尚未使用过)即可,该值将被用来替代自动生成的值。后续的增量将开始使用该手工插入的值。
那么,如何在使用AUTO_INCREMENT列时获得这个值呢?可使用last_insert_id()函数获得这个值,如下所示:
SELECT last_insert_id()
此语句返回最后一个AUTO_INCREMENT值,然后可以将它用于后续的MySQL语句。
默认值
如果在插入行时没有给出值,MySQL允许指定此时使用的默认值。默认值用CREATE TABLE语句的列定义中的DEFAULT关键字指定。
MySQL不允许使用函数作为默认值,它只支持常量。
许多数据库开发人员使用默认值而不是NULL列,特别是对用于计算或数据分组的列更是如此。
引擎类型
上述建表例子中使用的CREATE TABLE语句全都以ENGINE=InnoDB语句结束。
MySQL有一个具体管理和处理数据的内部引擎。在你使用CREATE TABLE语句时,该引擎具体创建表,而在你使用SELECT语句或进行其他数据库处理时,该引擎在内部处理你的请求。多数时候,此引擎都隐藏在DBMS内,不需要过多关注它。
MySQL具有多种引擎。它打包多个引擎,这些引擎都隐藏在MySQL服务器内,全都能执行CREATE TABLE和SELECT等命令。不同的引擎具有各自不同的功能和特性,为不同的任务选择正确的引擎能获得良好的功能和灵活性。
以下是几个需要常用的引擎:
- InnoDB是一个可靠的事务处理引擎,它不支持全文本搜索;
- MEMORY在功能等同于MyISAM,但由于数据存储在内存(不是磁盘)中,速度很快(特别适合于临时表);
- MyISAM是一个性能极高的引擎,它支持全文本搜索,但不支持事务处理。
在没有特殊需求(全文搜索等)的要求下,使用默认引擎即可,从MySQL 5.5版本开始,InnoDB成为默认存储引擎。也不需要特别指出。在涉及到这些特殊需求的情况下,我会为你详细讲解
修改表结构
添加列、删除列、修改列定义等
-- 添加列
ALTER TABLE 表名
ADD 列名 数据类型;
-- 删除列
ALTER TABLE 表名
DROP COLUMN 列名;
-- 修改列定义(列名、数据类型、NULL等)
ALTER TABLE 表名
MODIFY 列名 新数据类型;
使用ALTER TABLE要极为小心,应该在进行改动前做一个完整的备份(模式和数据的备份)。数据库表的更改不能撤销,如果增加了不需要的列,可能不能删除它们。类似地,如果删除了不应该删除的列,可能会丢失
该列中的所有数据。
删除表
DROP TABLE IF EXISTS 表名;
删除表没有确认,也不能撤销,执行这条语句将永久删除该表,请谨慎使用。
重命名表
使用RENAME TABLE语句可以重命名一个表:
RENAMETABLE 旧表名 TO 新表名;
RENAME TABLE所做的仅是重命名一个表。可以使用下面的语句对多个表重命名:
RENAME TABLE backup_customers TO customers,
backup_vendors To vendors,
backup_products TO products;
版权归原作者 麦田里的稻草人w 所有, 如有侵权,请联系我们删除。