库操作

系统安装默认自带的库

　　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)

　　

　

 

 

 

 

 

 

 

 

 

 

 

 

 

转载于:https://www.cnblogs.com/Jiangchuanwei/p/8657144.html