D2a4d8d5231d78709ce4941f424f3c97
MySQL开发手册(二) 数据库操作

(1)查看数据库列表


  • 查看数据库:show databases;
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

(2)创建数据库


  • 创建数据库:CREATE {DATABASE} [IF NOT EXISTS] db-name [DEFAULT] CHARACTER SET [=] charset_name;
  • 查看数据库详情:show create database db-name;
  • 查看执行的错误信息:show warnings;
  • 注意
    • 重复创建同名的数据库会抛出Error,加一下if not exists就可以避免
    • 数据库默认的编码是utf8
mysql> create database d1;
Query OK, 1 row affected (0.02 sec)

mysql> create database d1;
ERROR 1007 (HY000): Can't create database 'd1'; database exists
mysql> show warnings;
+-------+------+---------------------------------------------+
| Level | Code | Message                                     |
+-------+------+---------------------------------------------+
| Error | 1007 | Can't create database 'd1'; database exists |
+-------+------+---------------------------------------------+
1 row in set (0.00 sec)

mysql> create database if not exists d1;
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+---------------------------------------------+
| Level | Code | Message                                     |
+-------+------+---------------------------------------------+
| Note  | 1007 | Can't create database 'd1'; database exists |
+-------+------+---------------------------------------------+
1 row in set (0.00 sec)

mysql> create database d2 character set = gbk;
Query OK, 1 row affected (0.05 sec)

mysql> show create database d1;
+----------+-------------------------------------------------------------------------------------------+
| Database | Create Database                                                                           |
+----------+-------------------------------------------------------------------------------------------+
| d1       | CREATE DATABASE `d1` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ |
+----------+-------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create database d2;
+----------+------------------------------------------------------------+
| Database | Create Database                                            |
+----------+------------------------------------------------------------+
| d2       | CREATE DATABASE `d2` /*!40100 DEFAULT CHARACTER SET gbk */ |
+----------+------------------------------------------------------------+
1 row in set (0.00 sec)

(3)修改数据库


  • 修改数据库:ALTER {DATABASE} db-name [DEFAULT] CHARACTER SET [=] charset_name;
mysql> alter database d1 character set = gbk;
Query OK, 1 row affected (0.04 sec)

mysql> show create database d1;
+----------+------------------------------------------------------------+
| Database | Create Database                                            |
+----------+------------------------------------------------------------+
| d1       | CREATE DATABASE `d1` /*!40100 DEFAULT CHARACTER SET gbk */ |
+----------+------------------------------------------------------------+
1 row in set (0.00 sec)

(4)删除数据库


  • 删除数据库:DROP {DATABASE | SCHEMA} [IF EXISTS] db-name ;
mysql> drop database d1;
Query OK, 0 rows affected (0.07 sec)

mysql> drop database d2;
Query OK, 0 rows affected (0.04 sec)

mysql> drop database if exists d2;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
Empty set (0.00 sec)

(5)打开数据库


  • 打开数据库:use db-name;
  • 查看当前所在数据库:select database();
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> use mysql;
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() |
+------------+
| mysql      |
+------------+
1 row in set (0.00 sec)

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)

笔记资源下载链接 ,支持PDF、ENEX印象笔记、HTML,方便大家整理导入

© 著作权归作者所有
这个作品真棒,我要支持一下!
主要帮助小白掌握MySQL数据库的增删改查操作,最终提供一个工具类语句手册和完善的学习笔记
0条评论
top Created with Sketch.