3、MySQL-创建数据库

一、SQL规范

1、数据库名、表名、关键字需要大写

2、SQL命令支持折行操作，不能将单词、名称、或者成对的引号折行来写

3、当名称和MYSQL保留字冲突的时候，需要用' ' 写上括号名称

4、和名称相关的，不要起特殊字符，也不要起中文，最好名称含义明确，以字母开始，可以包含数字、字母、下划线

二、开启输出日志的形式

\T 文件保存位置以及文件名称

\t  结束日志输出

mysql> \T F:\programming\JavaEE\MySQL\MyDemo\mySqlLog1.txt

Logging to file 'F:\programming\JavaEE\MySQL\MyDemo\mySqlLog1.txt'

mysql> SELECT VERSION();

+------------+

| VERSION()  |

+------------+

| 5.6.21-log |

+------------+

1 row in set (0.00 sec)

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2015-03-03 01:28:03 |
+---------------------+
1 row in set (0.06 sec)

mysql> \t
Outfile disabled.

三、创建数据库

1、CREATE {DATABASE|SCHEMA} db_name;

mysql> CREATE DATABASE learndb1;
Query OK, 1 row affected (0.04 sec)

2、如果已经存在该数据库，不弹出警告

CREATE {DATABASE|SCHEMA} [IF NOT EXISTS] db_name;

mysql> CREATE DATABASE IF NOT EXISTS learndb1;
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+-------+------+---------------------------------------------------+
| Level | Code | Message                                           |
+-------+------+---------------------------------------------------+
| Note  | 1007 | Can't create database 'learndb1'; database exists |
+-------+------+---------------------------------------------------+
1 row in set (0.00 sec)

3、创建数据库的同时指定编码方式

CREATE {DATABASE|SCHEMA} [IF NOT EXISTS] db_name  [[DEFAULT] CHARACTER SET [=] 字符集] ;

DEFAULT 和 = 都可以省略

mysql> CREATE DATABASE IF NOT EXISTS learndb2  DEFAULT CHARACTER SET = 'GBK';
Query OK, 1 row affected (0.01 sec)

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

四、查看数据库

SHOW DATABASES;

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| learndb1           |
| mysql              |
| osms               |
| performance_schema |
| sakila             |
| test               |
| world              |
+--------------------+
8 rows in set (0.12 sec)

五、查看上一步操作产生的警告

SHOW WARNINGS;

六、查看已经创建好的数据库的编码方式

SHOW CREATE {DATABASE|SCHEMA} db_name;

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