数据库的基本操作

查看数据库

---

语法格式

SHOW {DATABASES | SCHEMAS}
    [LIKE 'pattern' | WHERE expr]

#查看全部数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

show databases [like '库名'| where 表达式];

例查看有my的数据库

show databases like 'my%'

创建数据库

---

MySQL安装好之后⾸先需要创建数据库这是使⽤MySQL各种功能的前提。本章将详细介绍数据的基本操作主要内容包括创建数据库、删除数据库、不同类型的数据存储引擎和存储引擎的选择。

MySQL安装完成之后将会在其data⽬录下⾃动创建⼏个必需的数据库可以使⽤SHOW DATABASES 语句来查看当前所有存在的数据库如下。

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 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.00 sec)
​
mysql> show create database test_db;
+----------+--------------------------------------------------------------------+
| Database | Create Database                                                    |
+----------+--------------------------------------------------------------------+
| test_db  | CREATE DATABASE `test_db` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+--------------------------------------------------------------------+
1 row in set (0.00 sec)

删除数据库

---

删除数据库是将已经存在的数据库从磁盘空间上清除清除之后数据库中的所有数据也将⼀起被删除。删除数据库语句和创建数据库的命令相似MySQL中删除数据库的基本语法格式如下。

DROP {DATABASE | SCHEMA} [IF EXISTS] 库名

​

注IF EXISTS表示如果存在则删除库。

如果指定的数据库不存在则删除出错。

mysql> drop database test_db;
Query OK, 0 rows affected (0.02 sec)

字符集/字符校验

---

#查看字符校验

show collation;

#查看字符集

show character set;

#查看字符集

SHOW CHARACTER SET [LIKE 'pattern' | WHERE expr]show character set [like '字符集名' | where 表达式];

#查看字符校验

SHOW COLLATION [LIKE 'pattern' | WHERE expr] show collation [like '字符校验名' | where 表达式]; 

修改数据库

---

ALTER {DATABASE | SCHEMA} [db_name]
    alter_option ...

alter_option: {
    [DEFAULT] CHARACTER SET [=] charset_name | [DEFAULT] COLLATE [=] collation_name
}

#修改库字符集

alter database 库名 default character set = 字符集名;

#修改字符校验

alter database test collate 字符校验名;

帮助命令

---

#帮助

help

#帮助分类

help contents

#查看定义语言

help data definition

#查看操作语言

help data manipulation

#查看创建库格式

help create database

数据库存储引擎

---

数据库存储引擎是数据库底层软件组件数据库管理系统DBMS使⽤数据引擎进⾏创建、查询、更新和删除数据操作。不同的存储引擎提供不同的存储机制、索引技巧、锁定⽔平等功能使⽤不同的存储引擎还可以获得特定的功能。现在许多不同的数据库管理系统都⽀持多种不同的数据引擎。MySQL的核⼼就是存储引擎。

2.3.1 MySQL存储引擎简介MySQL提供了多个不同的存储引擎包括处理事务安全表的引擎和处理⾮事务安全表的引擎。在MySQL中不需要在整个服务器中使⽤⼀种引擎针对具体要求可以对每⼀个表使⽤不同的存储引擎。MySQL5.5⽀持的存储引擎有InnoDB、MyISAM、Memory等。

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.01 sec)

查看默认存储引擎

mysql> show variables like 'default_storage_engine';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | InnoDB |
+------------------------+--------+
1 row in set (0.06 sec)

存储引擎简介

1. 存储引擎说⽩了就是数据存储的格式不同的存储引擎功能不同占⽤的空间⼤⼩不同读取性能也不同。

2. 数据库存储引擎是数据库底层软件组件不同的存储引擎提供不同的存储机制。

3. 在 MySQL 中不需要在整个服务器中使⽤同⼀种存储引擎可以对每⼀个表使⽤不同的存储引擎。

4. MySQL ⽀持多种存储引擎如 InnoDB 、MyISAM 、Memory 、Merge 、Archive 、CSV 、Federated 等等。

MyISAM 存储引擎特点

1. MySQL 5.5 之前使⽤ MyISAM 引擎MySQL 5.5 之后使⽤ InnoDB 引擎。

2. MyISAM 引擎读取速度较快占⽤资源相对较少不⽀持事务不⽀持外键约束但⽀持全⽂索引。

3. 读写互相阻塞也就是说读数据的时候你就不能写数据写数据的时候你就不能读数据。

4. MyISAM 引擎只能缓存索引⽽不能缓存数据。

MyISAM 适⽤场景

1. 不需要事务⽀持的业务例如转账就不⾏。

2. 适⽤于读数据⽐较多的业务不适⽤于读写频繁的业务。

3. 并发相对较低、数据修改相对较少的业务。

4. 硬件资源⽐较差的机器可以考虑使⽤ MyISAM 引擎。

InnoDB 存储引擎特点

1. 事务型数据库的⾸选引擎⽀持事务安全表⽀持⾏锁定和外键MySQL5.5.5 版本之后InnoDB 作为默认存储引擎。

2. 具有提交、回滚和崩溃恢复能⼒的事务安全存储引擎能处理巨⼤数据量性能及效率⾼完全⽀持外键完整性约束。

3. 具有⾮常⾼效的缓存特性能缓存索引也能缓存数据对硬件要求⽐较⾼。

4. 使⽤ InnoDB 时将在 MySQL 数据⽬录下创建⼀个名为 ibdata1 的 10MB ⼤⼩的⾃动扩展数据⽂件以及两个名为 ib_logfile0 和 ib_logfile1 的 5MB ⼤⼩的⽇志⽂件。

InnoDB 适⽤场景

1. 需要事务⽀持的业务、⾼并发的业务。

2. 数据更新较为频繁的场景⽐如 BBS、SNS、微博等。

3. 数据⼀致性要求较⾼的业务⽐如充值转账、银⾏卡转账。

Memory 存储引擎特点

1. Memory 存储引擎将表中的数据存储到内存中为查询和引⽤其他表数据提供快速访问。

2. Memory 存储引擎执⾏ HASH 和 BTREE 索引不⽀持 BLOB 和 TEXT 列⽀持 AUTO_INCREMENT 列和对可包含 NULL 值得列的索引。

3. 当不再需要 Memory 表的内容时要释放被 Memory 表使⽤的内存应该执⾏DELETE FROM 或 TRUNCATE TABLE 或者删除整个表。

存储引擎的选择

1. 如果要提供提交、回滚和崩溃恢复能⼒的事务安全能⼒并要求实现并发控制InnoDB 是个很好的选择。

2. 如果数据表主要⽤来插⼊和查询记录则 MyISAM 引擎能提供较⾼的处理效率。

3. 如果只是临时存放数据数据量不⼤并且不需要较⾼的安全性可以选择将数据保存在内存中的 Memory 引擎MySQL 使⽤该引擎作为临时表存放查询的中间结果。

4. 如果只有 INSERT 和 SELECT 操作可以选择 Archive 引擎⽀持⾼并发的插⼊操作如记录⽇志信息可以使⽤ Archive 引擎。

总结

---

在mysql中每个数据库最多可创建20亿个表⼀个表允许定义1024列每⾏的最⼤⻓度为8092字节不包括⽂本和图像类型的⻓度。

当表中定义有varchar、nvarchar或varbinary类型列时如果向表中插⼊的数据⾏超过8092字节时将导致语句失败并产⽣错误信息。SQL Server对每个表中⾏的数量没有直接限制但它受数据库存储空间的限制。

每个数据库的最⼤空间1048516TB所以⼀个表可⽤的最⼤空间为1048516TB减去数据库类系统表和其它数据库对象所占⽤的空间。理论上⽆限⼤ 就看你硬⽚够不够⼤ ⼤多数情况先是你的硬⽚不够。