数据库的基本操作

登陆数据库： mysql -h localhost -u root -p

C:\Windows\system32>mysql -h localhost -u root -p
Enter password: ********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 43
Server version: 5.5.40 MySQL Community Server (GPL)

Copyright (c) 2000, 2014, 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.

mysql>

查看存在的数据库： SHOW DATABASES;

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| netcan             |
| performance_schema |
| test               |
| wordpress          |
+--------------------+
6 rows in set (0.00 sec)

创建数据库：CREATE DATABASE <database_name>;

mysql> create database canet;
Query OK, 1 row affected (0.00 sec)

查看数据库的定义：SHOW CREATE DATABASE <database_name>;

mysql> show create database canet;
+----------+---------------------------------------------------------------+
| Database | Create Database                                               |
+----------+---------------------------------------------------------------+
| canet    | CREATE DATABASE `canet` /*!40100 DEFAULT CHARACTER SET gbk */ |
+----------+---------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| canet              |
| mysql              |
| netcan             |
| performance_schema |
| test               |
| wordpress          |
+--------------------+
7 rows in set (0.00 sec)

删除数据库： DROP DATABASE <database_name>;

mysql> drop database canet;
Query OK, 0 rows affected (0.11 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| netcan             |
| performance_schema |
| test               |
| wordpress          |
+--------------------+
6 rows in set (0.00 sec)

显示系统所支持的引擎： SHOW ENGINES;

mysql> show engines \G;
*************************** 1. row ***************************
	Engine: FEDERATED
	Support: NO
	Comment: Federated MySQL storage engine
Transactions: NULL
		XA: NULL
Savepoints: NULL
*************************** 2. row ***************************
	Engine: MRG_MYISAM
	Support: YES
	Comment: Collection of identical MyISAM tables
Transactions: NO
		XA: NO
Savepoints: NO
*************************** 3. row ***************************
	Engine: MyISAM
	Support: DEFAULT
	Comment: MyISAM storage engine
Transactions: NO
		XA: NO
Savepoints: NO
*************************** 4. row ***************************
	Engine: BLACKHOLE
	Support: YES
	Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
		XA: NO
Savepoints: NO
*************************** 5. row ***************************
	Engine: CSV
	Support: YES
	Comment: CSV 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
*************************** 7. row ***************************
	Engine: ARCHIVE
	Support: YES
	Comment: Archive storage engine
Transactions: NO
		XA: NO
Savepoints: NO
*************************** 8. row ***************************
	Engine: InnoDB
	Support: YES
	Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
		XA: YES
Savepoints: YES
*************************** 9. row ***************************
	Engine: PERFORMANCE_SCHEMA
	Support: YES
	Comment: Performance Schema
Transactions: NO
		XA: NO
Savepoints: NO
9 rows in set (0.00 sec)