MySQL语句

一、规范：

1、关键字与函数名称全部大写；

2、数据库名称、表名称、字段名称全部小写；

3、SQL语句必须以分号结尾；

二、数据库指令；

创建数据库：

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [DEFAULT] CHARACTER SET [=] charset_name;

root&&mysql>>>>CREATE DATABASE test1;
Query OK, 1 row affected (0.05 sec)

创建数据库，并设置编码格式：

root&&mysql>>>>CREATE DATABASE t2 CHARACTER SET gbk;
Query OK, 1 row affected (0.01 sec)

root&&mysql>>>>SHOW CREATE DATABASE t2;
+----------+------------------------------------------------------------+
| Database | Create Database                                            |
+----------+------------------------------------------------------------+
| t2       | CREATE DATABASE `t2` /*!40100 DEFAULT CHARACTER SET gbk */ |
+----------+------------------------------------------------------------+
1 row in set (0.00 sec)

查看当前服务下的数据库列表：SHOW DATABASES;

root&&mysql>>>>SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test1              |
+--------------------+
5 rows in set (0.02 sec)

查看某个数据库：SHOW CREATE DATABASE test1;

root&&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 t2 CHARACTER SET = utf8;

root&&mysql>>>>ALTER DATABASE t2 CHARACTER SET = utf8;
Query OK, 1 row affected, 1 warning (0.05 sec)

root&&mysql>>>>SHOW CREATE DATABASE;
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 '' at line 1
root&&mysql>>>>SHOW CREATE DATABASE t2;
+----------+-------------------------------------------------------------+
| Database | Create Database                                             |
+----------+-------------------------------------------------------------+
| t2       | CREATE DATABASE `t2` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-------------------------------------------------------------+
1 row in set (0.00 sec)

删除数据库：DROP DATABASE test1；

root&&mysql>>>>DROP DATABASE test1;
Query OK, 0 rows affected (0.07 sec)

root&&mysql>>>>SHOW DATABASES
    -> SHOW DATABASES;
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 'SHOW DATABASES' at line 2
root&&mysql>>>>SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| t2                 |
+--------------------+
5 rows in set (0.00 sec)