mysql数据库的操作
mysql 语句的规范
关键字与函数名称全部大写
数据库名称、表名称、字段名称全部小写
sql语句必须以分号结尾
数据库操作
1 创建数据库
大括号参数必须写,中括号参数可写可不写
CREATE {DTABASE | SCHEMA} [IF NOT EXISTS] db_name
[DEFAULT] CHARACTER SET [=] charset_name
mysql> create database `test`;创建数据库
Query OK, 1 row affected (0.00 sec)
mysql> CREATE DATABASE `test`; `test`数据库已经存在,再次创建数据库报错
ERROR 1007 (HY000): Can't create database 'test'; database exists
mysql> CREATE DATABASE IF NOT EXISTS`test`;
Query OK, 1 row affected, 1 warning (0.01 sec)
加上 IF NOT EXISTS 判断条件之后就不会error,但是会提示warning()
mysql> CREATE DATABASE IF NOT EXISTS `test`;
Query OK, 1 row affected, 1 warning (0.00 sec)
2 查看创建数据库时的命令
mysql> SHOW CREATE DATABASE `test`;
+----------+---------------------------------------------------------------+
| Database | Create Database |
+----------+---------------------------------------------------------------+
| test | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+---------------------------------------------------------------+
1 row in set (0.00 sec)
3 创建数据库的时候并设置编码格式
mysql> CREATE DATABASE `test1` character set = gbk;
Query OK, 1 row affected (0.00 sec)
mysql> SHOW CREATE DATABASE `test1`;
+----------+---------------------------------------------------------------+
| Database | Create Database |
+----------+---------------------------------------------------------------+
| test1 | CREATE DATABASE `test1` /*!40100 DEFAULT CHARACTER SET gbk */ |
+----------+---------------------------------------------------------------+
1 row in set (0.00 sec)
4 修改数据库的编码方式
ALTER {DATABASE | SCHEMA} [db_name]
[DEFAULT] CHARACTER SET [=] charset_name
mysql> ALTER DATABASE `test1` CHARACTER SET = utf8;
Query OK, 1 row affected (0.00 sec)
5 删除数据库
DROP {DATABASE | SCHEMA} [IF EXISTS] db_name
mysql> DROP DATABASE `test1`;
Query OK, 0 rows affected (0.08 sec)
查看服务器下有哪些数据库
SHOW DATABASES;