数据库的基本操作
2.1 创建数据库
MySQL安装好之后,⾸先需要创建数据库,这是使⽤MySQL各种功能的前提。本章 将详细介绍数据的基本操作,主要内容包括:创建数据库、删除数据库、不同类型 的数据存储引擎和存储引擎的选择。
MySQL安装完成之后,将会在其data⽬录下⾃动创建⼏个必需的数据库,可以使⽤ SHOW DATABASES; 语句来查看当前所有存在的数据库,如下。
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.19 sec)
可以看到,数据库列表中包含了4个数据库, mysql是必需的,它描述⽤户访问权 限,⽤户经常利⽤ test数据库做测试的⼯作,其他数据库将在后⾯的章节中介绍。 创建数据库是在系统磁盘上划分⼀块区域⽤于数据的存储和管理,如果管理员在设 置权限的时候为⽤户创建了数据库,则可以直接使⽤,否则,需要⾃⼰创建数据 库。 MySQL中创建数据库的基本SQL语句格式为:
CREATE DATABASE [IF NOT EXISTS] <数据库名> [[DEFAULT] CHARACTER SET < 字符集名>] [[DEFAULT] COLLATE <校对规则名>];
<数据库名>:创建数据库的名称。 MySQL 的数据存储区将以⽬录⽅式表示 MySQL 数据库,因此数据库名称必须符合操作系统的⽂件夹命名规则,注意在 MySQL 中 不区分⼤⼩写。
IF NOT EXISTS:在创建数据库之前进⾏判断,只有该数据库⽬前尚不存在时才能 执⾏操作。此选项可以⽤来避免数据库已经存在⽽重复创建的错误。
[DEFAULT] CHARACTER SET:指定数据库的默认字符集。
mysql> CREATE DATABASE test_db;
Query OK, 1 row affected (0.02 sec) mysql> show create database xiyou;
+----------+------------------------------------------------------- ----------+
| Database | Create Database
|
+----------+------------------------------------------------------- ----------+
| xiyou | CREATE DATABASE `xiyou` /*!40100 DEFAULT CHARACTER SET
utf8 */ |
+----------+------------------------------------------------------- ----------+
1 row in set (0.00 sec)
2.2 删除数据库
删除数据库是将已经存在的数据库从磁盘空间上清除,清除之后,数据库中的所有 数据也将⼀起被删除。删除数据库语句和创建数据库的命令相似, MySQL中删除数 据库的基本语法格式为:
DROP DATABASE database_name;
“ database_name”为要删除的数据库的名称,如果指定的数据库不存在,则删除出 错。
mysql> DROP DATABASE test_db;
Query OK, 0 rows affected (0.04 sec)
2.3 数据库存储引擎
数据库存储引擎是数据库底层软件组件,数据库管理系统(DBMS)使⽤数据引擎 进⾏创建、查询、更新和删除数据操作。不同的存储引擎提供不同的存储机制、索 引技巧、锁定⽔平等功能,使⽤不同的存储引擎,还可以获得特定的功能。现在许 多不同的数据库管理系统都⽀持多种不同的数据引擎。 MySQL的核⼼就是存储引 擎。
2.3.1 MySQL存储引擎简介
MySQL提供了多个不同的存储引擎,包括处理事务安全表的引擎和处理⾮事务安全 表的引擎。在MySQL中,不需要在整个服务器中使⽤⼀种引擎,针对具体要求可以 对每⼀个表使⽤不同的存储引擎。 MySQL5.5⽀持的存储引擎有: InnoDB、
MyISAM 、Memory等。查看引擎的命令⽤
SHOW ENGINES\G
mysql>SHOW ENGINES\G
*************************** 1. row ***************************
Engine: CSV Support: YES
Comment: CSV storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 2. row ***************************
Engine: PERFORMANCE_SCHEMA
Support: YES
Comment: Performance Schema
Transactions: NO
XA: NO
Savepoints: NO
*************************** 3. row ***************************
Engine: InnoDB Support: DEFAULT
Comment: Supports transactions, row-level locking, and foreign
keys
Transactions: YES
XA: YES
Savepoints: YES
*************************** 4. row ***************************
Engine: MRG_MYISAM
Support: YES
Comment: Collection of identical MyISAM tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 5. row ***************************
Engine: MyISAM Support: YES
Comment: MyISAM storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 6. row ***************************
Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary
tables
Transactions: NO
XA: NO
Savepoints: NO
6 rows in set (0.00 sec)
查看默认存储引擎:
mysql> show variables like 'default_storage_engine';
+------------------------+--------+
| Variable_name | Value |
+------------------------+--------+
| default_storage_engine | InnoDB |
+------------------------+--------+
1 row in set (0.02 sec)
⼀、存储引擎简介
I. 存储引擎说⽩了就是数据存储的格式,不同的存储引擎功能不同,占⽤的空间 ⼤⼩不同,读取性能也不同
J. 数据库存储引擎是数据库底层软件组件,不同的存储引擎提供不同的存储机制
K. 在 MySQL 中,不需要在整个服务器中使⽤同⼀种存储引擎,可以对每⼀个表 使⽤不同的存储引擎
L. MySQL ⽀持多种存储引擎,如 InnoDB 、MyISAM 、Memory 、Merge 、
Archive 、CSV 、Federated 等等 MyISAM 存储引擎特点:
I. MySQL 5.5 之前使⽤ MyISAM 引擎, MySQL 5.5 之后使⽤ InnoDB 引擎
J. MyISAM 引擎读取速度较快,占⽤资源相对较少,不⽀持事务,不⽀持外键约 束,但⽀持全⽂索引
K. 读写互相阻塞,也就是说读数据的时候你就不能写数据,写数据的时候你就不 能读数据
L. MyISAM 引擎只能缓存索引,⽽不能缓存数据 MyISAM 适⽤场景:
I. 不需要事务⽀持的业务,例如转账就不⾏
J. 适⽤于读数据⽐较多的业务,不适⽤于读写频繁的业务 K. 并发相对较低、数据修改相对较少的业务
L. 硬件资源⽐较差的机器可以考虑使⽤ MyISAM 引擎 InnoDB 存储引擎特点:
I. 事务型数据库的⾸选引擎,⽀持事务安全表,⽀持⾏锁定和外键, MySQL 5.5.5 版本之后, InnoDB 作为默认存储引擎
J. 具有提交、回滚和崩溃恢复能⼒的事务安全存储引擎,能处理巨⼤数据量,性 能及效率⾼,完全⽀持外键完整性约束
K. 具有⾮常⾼效的缓存特性,能缓存索引也能缓存数据,对硬件要求⽐较⾼
L. 使⽤ InnoDB 时,将在 MySQL 数据⽬录下创建⼀个名为 ibdata1 的 10MB ⼤⼩ 的⾃动扩展数据⽂件,以及两个名为 ib_logfile0 和 ib_logfile1 的 5MB ⼤⼩的
⽇志⽂件
InnoDB 适⽤场景:
I. 需要事务⽀持的业务、⾼并发的业务
J. 数据更新较为频繁的场景,⽐如 BBS 、SNS、微博等
K. 数据⼀致性要求较⾼的业务,⽐如充值转账、银⾏卡转账 Memory 存储引擎特点:
I. Memory 存储引擎将表中的数据存储到内存中,为查询和引⽤其他表数据提供 快速访问
J. Memory 存储引擎执⾏ HASH 和 BTREE 索引,不⽀持 BLOB 和 TEXT 列,⽀ 持 AUTO_INCREMENT 列和对可包含 NULL 值得列的索引
K. 当不再需要 Memory 表的内容时,要释放被 Memory 表使⽤的内存,应该执⾏ DELETE FROM 或 TRUNCATE TABLE ,或者删除整个表
⼆、存储引擎的选择
I. 如果要提供提交、回滚和崩溃恢复能⼒的事务安全能⼒,并要求实现并发控 制, InnoDB 是个很好的选择
J. 如果数据表主要⽤来插⼊和查询记录,则 MyISAM 引擎能提供较⾼的处理效率 K. 如果只是临时存放数据,数据量不⼤,并且不需要较⾼的安全性,可以选择将 数据保存在内存中的 Memory 引擎, MySQL 使⽤该引擎作为临时表,存放查
询的中间结果
L. 如果只有 INSERT 和 SELECT 操作,可以选择 Archive 引擎,⽀持⾼并发的插 ⼊操作,如记录⽇志信息可以使⽤ Archive 引擎
功能
MyISAM
Memory
InnoDB
存储限制
256TB
RAM
64TB
⽀持事务
No
No
Yes
⽀持全⽂索引
Yes
No
No
⽀持数索引
Yes
Yes
Yes
⽀持哈希缓存
No
Yes
No
⽀持数据缓存
No
N/A
Yes
⽀持外键
No
No
Yes
课后填充:
在mysql中,每个数据库最多可创建20亿个表,⼀个表允许定义1024列,每⾏ 的最⼤⻓度为8092字节(不包括⽂本和图像类型的⻓度)。当表中定义有
varchar 、nvarchar或varbinary类型列时,如果向表中插⼊的数据⾏超过8092 字节时将导致语句失败,并产⽣错误信息。 SQL Server对每个表中⾏的数量没 有直接限制,但它受数据库存储空间的限制。每个数据库的最⼤空间
1048516TB,所以⼀个表可⽤的最⼤空间为1048516TB减去数据库类系统表和 其它数据库对象所占⽤的空间。理论上⽆限⼤ 就看你硬⽚够不够⼤ ⼤多数情 况先是你的硬⽚不够