mysql的相关操作

查看当前登录用户：

mysql> select USER();
+----------------+
| USER()         |
+----------------+
| root@localhost |
+----------------+
1 row in set

 

查看数据库版本：

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.5.22    |
+-----------+
1 row in set

 

当前的日期时间：

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2016-08-03 12:01:17 |
+---------------------+
1 row in set

 

创建数据库：

mysql> CREATE DATABASE mydb1;
Query OK, 1 row affected (0.07 sec)

 

如果数据库中已存在该库，那么创建的时候会显示错误信息；如果不想看到错误信息，可以用if判断一下：

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

mysql> SHOW WARNINGS;
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                   |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Error | 1064 | 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 'WARINGS' at line 1 |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

 

查看有哪些数据库：

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| aaa                |
| aliyun             |
| blog               |

 

查看数据库的定义信息，比如编码信息：

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

 

创建数据库时，指定编码方式：

mysql> CREATE DATABASE mydb2 DEFAULT CHARACTER SET 'gbk';
Query OK, 1 row affected (0.07 sec)

mysql> CREATE DATABASE mydb3 CHARACTER SET 'gbk';
Query OK, 1 row affected (0.00 sec)

 

修改现在库的编码方式：

mysql> ALTER DATABASE mydb3 DEFAULT CHARACTER SET utf8;
Query OK, 1 row affected (0.01 sec)

 

在管理一个数据库前，需要先打开该数据库：

mysql> use mydb1;
Database changed

 

查看当前打开的是哪个数据库：

mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| mydb1      |
+------------+
1 row in set (0.00 sec)

 

删除指定数据库：

mysql> DROP DATABASE mydb1;
Query OK, 0 rows affected (0.18 sec)

mysql> DROP DATABASE IF EXISTS mydb2;
Query OK, 0 rows affected (0.00 sec)