库操作
系统安装默认自带的库
information_schema： 虚拟库，不占用磁盘空间，存储的是数据库启动后的一些参数，如用户表信息、列信息、权限信息、字符信息等
performance_schema： MySQL 5.5开始新增一个数据库：主要用于收集数据库服务器性能参数，记录处理查询请求时发生的各种事件、锁等现象
mysql： 授权库，主要存储系统用户的权限信息
一、查看数据库
【1】查看所有数据库
语句：show databases;
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | Cy_NewErp | | hugo | | mysql | | performance_schema | | sys | +--------------------+ 6 rows in set (0.01 sec)
【2】查看数据库的创建方式
语句：show create database db_name; db_name为库名
mysql> show create database hugo; +----------+-----------------------------------------------------------------+ | Database | Create Database | +----------+-----------------------------------------------------------------+ | hugo | CREATE DATABASE `hugo` /*!40100 DEFAULT CHARACTER SET latin1 */ | +----------+-----------------------------------------------------------------+ 1 row in set (0.02 sec)
【3】查看当前使用的库
select database()
二、创建数据数据库
创建数据库（在磁盘上对应的文件夹）
语句：create database [if not exists] db_name [default] character set [=] charset_name
mysql> CREATE DATABASE test; Query OK, 1 row affected (0.00 sec) mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | Cy_NewErp | | hugo | | mysql | | performance_schema | | sys | | test | +--------------------+ 7 rows in set (0.00 sec)
当我们创建存在的库时，会出现报错
mysql> SHOW DATABASE test; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DATABASE test' at line 1
mysql> CREATE DATABASE IF NOT EXISTS test; Query OK, 1 row affected, 1 warning (0.01 sec) mysql> SHOW WARNINGS; --查看警告 +-------+------+-----------------------------------------------+ | Level | Code | Message | +-------+------+-----------------------------------------------+ | Note | 1007 | Can't create database 'test'; database exists | +-------+------+-----------------------------------------------+ 1 row in set (0.00 sec)
--我的电脑创建出来的库字符集默认是latin1 mysql> SHOW CREATE DATABASE test; +----------+-----------------------------------------------------------------+ | Database | Create Database | +----------+-----------------------------------------------------------------+ | test | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET latin1 */ | +----------+-----------------------------------------------------------------+
mysql> CREATE DATABASE IF NOT EXISTS test1 CHARACTER SET UTF8; Query OK, 1 row affected (0.00 sec) mysql> SHOW CREATE DATABASE test1; +----------+----------------------------------------------------------------+ | Database | Create Database | +----------+----------------------------------------------------------------+ | test1 | CREATE DATABASE `test1` /*!40100 DEFAULT CHARACTER SET utf8 */ | +----------+----------------------------------------------------------------+ 1 row in set (0.00 sec)
三、修改数据库
语句：alter [database | schema] [db_name] [default] character set [=] charset_name
mysql> SHOW CREATE DATABASE test; +----------+-----------------------------------------------------------------+ | Database | Create Database | +----------+-----------------------------------------------------------------+ | test | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET latin1 */ | +----------+-----------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> ALTER DATABASE test CHARACTER SET = UTF8; Query OK, 1 row affected (0.00 sec) mysql> SHOW CREATE DATABASE test; +----------+---------------------------------------------------------------+ | Database | Create Database | +----------+---------------------------------------------------------------+ | test | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8 */ | +----------+---------------------------------------------------------------+ 1 row in set (0.00 sec)
四、删除数据库
语句：drop database [if exists] db_name;
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | Cy_NewErp | | hugo | | mysql | | performance_schema | | sys | | test | | test1 | +--------------------+ 8 rows in set (0.01 sec) --删除存在的库的时候会报错 mysql> DROP DATABASE test2; ERROR 1008 (HY000): Can't drop database 'test2'; database doesn't exist --显示错误 mysql> SHOW ERRORS; +-------+------+-----------------------------------------------------+ | Level | Code | Message | +-------+------+-----------------------------------------------------+ | Error | 1008 | Can't drop database 'test2'; database doesn't exist | +-------+------+-----------------------------------------------------+ 1 row in set (0.00 sec) --删除test1库，当不确定这个库是否存在时，可以加可选项 mysql> DROP DATABASE IF NOT EXISTS test1; Query OK, 0 rows affected (0.00 sec)
五、使用数据库
【1】切换数据库：
语句：use db_name;
注意：进入到某个数据库后没办法再退回之前状态，但可以通过use进行切换
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | Cy_NewErp | | hugo | | mysql | | performance_schema | | sys | | test | +--------------------+ 7 rows in set (0.00 sec) mysql> USE test; Database changed
【2】查看当前使用的数据库
语句：select database();
mysql> SELECT DATABASE(); +------------+ | DATABASE() | +------------+ | test | +------------+ 1 row in set (0.00 sec)
切换数据库，可以用use切换
mysql> USE sys; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> SELECT DATABASE(); +------------+ | DATABASE() | +------------+ | sys | +------------+ 1 row in set (0.00 sec)
