Mysql基本操作(数据库操作)

数据库操作（DDL）

#创建数据库
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [[DEFAULT]CHARACTER SET [=] chatset_name]
#example
CREATE DATABASE maizi1;
CREATE DATABASE IF NOT EXISTS maizi3 DEFAULT CHARACTER SET = 'gbk';

不要重复创建数据库，如果重复创建，可以在语句中添加IF NOT EXISTS，但是会出现warning,使用SHOW WARNINGS;查看上一步操作产生的警告

#例子
mysql> CREATE DATABASE maizi2;
Query OK, 1 row affected (0.03 sec)

mysql> CREATE DATABASE maizi2;
ERROR 1007 (HY000): Can't create database 'maizi2'; database exists

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

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

查看当前所有数据库

SHOW DATABASES;

查看指定数据库的定义

SHOW CREATE {DATABASES|SCHEMA} db_name
#example
mysql> SHOW CREATE DATABASE maizi1;
+----------+-----------------------------------------------------------------+
| Database | Create Database                                                 |
+----------+-----------------------------------------------------------------+
| maizi1   | CREATE DATABASE `maizi1` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.00 sec)

修改指定数据库的编码方式

ALTER {DATABASE|SCHEMA} db_name [DEFAULT] CHARACTER SET [=] charset_name
#example
ALTER DATABASE maizi3 DEFAULT CHARACTER SET utf8;

打开指定数据库

USE db_name

查看当前打开数据库

SELECT DATABASE();

删除指定数据库

DROP {DATABASE|SCHEMA} [IF EXISTS] db_name
#example