Mysql数据库基本操作实战

一 实战

步骤1：登陆数据库：

mysql -h localhost -u root -p

E:\database\Mysql\MySQL 5.7从零开始学\课本源代码\03>mysql -h localhost -u root -p
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.22 MySQL Community Server (GPL)


Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.


Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.


Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

步骤2：创建数据库zookeep：

CREATE DATABASE zookeep;

SHOW DATABASES;

mysql> CREATE DATABASE zookeep;
Query OK, 1 row affected (0.00 sec)


mysql> SHOW DATABASES;
+----------------------------+
| Database                   |
+----------------------------+
| information_schema         |
| apolloconfigdb             |
| apolloportaldb             |
| cakin                      |
| comperformance             |
| crmdb                      |
| csvt                       |
| dbgirl                     |
| dbperson                   |
| demo                       |
| djangoblog                 |
| hibernate                  |
| hrm_db                     |
| hrsystem                   |
| javaee                     |
| jdbctemplate               |
| leecx                      |
| liuyan                     |
| microservice-auth          |
| microservice-exam          |
| microservice-gateway       |
| microservice-user          |
| mybatis                    |
| mysql                      |
| mysqldb                    |
| oa_db                      |
| online_inf                 |
| performance_schema         |
| security00                 |
| security01                 |
| shiro                      |
| spiders                    |
| spring                     |
| spring-cloud               |
| springboot                 |
| springbootmybatis          |
| springboottest             |
| springdatajpa              |
| springdatajpaquery         |
| springdatajpaspecification |
| student                    |
| sys                        |
| test                       |
| test1                      |
| test2                      |
| test3                      |
| test4                      |
| weixin                     |
| work                       |
| zoo                        |
| zookeep                    |
+----------------------------+
51 rows in set (0.00 sec)

步骤3：选择当前数据库为zookeep：

USE zookeep;

mysql> USE zookeep;
Database changed

查看数据库zoo的信息：

SHOW CREATE DATABASE zookeep;

mysql> SHOW CREATE DATABASE zookeep;
+----------+------------------------------------------------------------------+
| Database | Create Database                                                  |
+----------+------------------------------------------------------------------+
| zookeep  | CREATE DATABASE `zookeep` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+------------------------------------------------------------------+
1 row in set (0.00 sec)

步骤4：删除数据库zoo：

DROP DATABASE zoo;

mysql> DROP DATABASE zoo;
Query OK, 0 rows affected (0.00 sec)

步骤5：查看存储引擎

SHOW ENGINES;

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

二 存储引擎选择

功能	MyISAM	Memory	InnoDB	Archive
存储限制	256TB	RAM	64TB	None
支持事务	No	No	Yes	No
支持全文索引	Yes	No	No	No
支持数索引	Yes	Yes	Yes	No
支持哈希索引	No	Yes	Yes	No
支持数据缓存	No	NA	Yes	No
支持外键	No	No	Yes	No