系统安装默认自带的库
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)