1. 创建数据库
语法
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [create_specification] ... create_specification: [DEFAULT] CHARACTER SET [=] charset_name | [DEFAULT] COLLATE [=] collation_name 示例
MariaDB [(none)]> create database db1; Query OK, 1 row affected (0.00 sec) #显示CREATE DATABASE创建命名数据库的语句 MariaDB [(none)]> show create database db1; +----------+----------------------------------------------------------------+ | Database | Create Database | +----------+----------------------------------------------------------------+ | db1 | CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET latin1 */ | +----------+----------------------------------------------------------------+ 1 row in set (0.00 sec) [root@centos7 ~]# cat /var/lib/mysql/db1/db.opt default-character-set=latin1 default-collation=latin1_swedish_ci MariaDB [(none)]> create database db1; ERROR 1007 (HY000): Can't create database 'db1'; database exists MariaDB [(none)]> create database IF NOT EXISTS db1; Query OK, 1 row affected, 1 warning (0.00 sec) MariaDB [(none)]> show warnings ; +-------+------+----------------------------------------------+ | Level | Code | Message | +-------+------+----------------------------------------------+ | Error | 1007 | Can't create database 'db1'; database exists | +-------+------+----------------------------------------------+ 1 row in set (0.00 sec) 指定字符集创建新数据库
MariaDB [(none)]> create database if not exists db2 character set 'utf8mb4'; Query OK, 1 row affected (0.00 sec) MariaDB [(none)]> show create database db2; +----------+-----------------------------------------------------------------+ | Database | Create Database | +----------+-----------------------------------------------------------------+ | db2 | CREATE DATABASE `db2` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ | +----------+-----------------------------------------------------------------+ 1 row in set (0.00 sec) [root@centos7 ~]# cat /var/lib/mysql/db2/db.opt default-character-set=utf8mb4 default-collation=utf8mb4_general_ci
2. 修改数据库
语法
ALTER {DATABASE | SCHEMA} [db_name] alter_specification ... ALTER {DATABASE | SCHEMA} db_name UPGRADE DATA DIRECTORY NAME alter_specification: [DEFAULT] CHARACTER SET [=] charset_name | [DEFAULT] COLLATE [=] collation_name范例
MariaDB [(none)]> ALTER DATABASE db1 character set utf8; Query OK, 1 row affected (0.00 sec) MariaDB [(none)]> show create database db1; +----------+--------------------------------------------------------------+ | Database | Create Database | +----------+--------------------------------------------------------------+ | db1 | CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET utf8 */ | +----------+--------------------------------------------------------------+ 1 row in set (0.00 sec) [root@centos7 ~]# cat /var/lib/mysql/db1/db.opt default-character-set=utf8 default-collation=utf8_general_ci
3. 删除数据库
语法
DROP {DATABASE | SCHEMA} [IF EXISTS] db_name范例
MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | db2 | | mysql | | performance_schema | +--------------------+ 4 rows in set (0.00 sec) MariaDB [(none)]> drop database db2 ; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | +--------------------+ 3 rows in set (0.00 sec) [root@centos7 ~]# ll /var/lib/mysql/ total 28700 -rw-rw----. 1 mysql mysql 16384 Feb 22 11:22 aria_log.00000001 -rw-rw----. 1 mysql mysql 52 Feb 22 11:22 aria_log_control -rw-rw----. 1 mysql mysql 18874368 Feb 22 11:22 ibdata1 -rw-rw----. 1 mysql mysql 5242880 Feb 22 11:22 ib_logfile0 -rw-rw----. 1 mysql mysql 5242880 Feb 22 11:22 ib_logfile1 drwx------. 2 mysql mysql 4096 Feb 22 11:22 mysql srwxrwxrwx. 1 mysql mysql 0 Feb 22 11:22 mysql.sock drwx------. 2 mysql mysql 4096 Feb 22 11:22 performance_schema
4. 查看数据库列表
语法
SHOW {DATABASES | SCHEMAS} [LIKE 'pattern' | WHERE expr]范例
MariaDB [(none)]> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | +--------------------+ 3 rows in set (0.00 sec)
5. 选择数据库
语法
USE db_name主要功能切换到对应数据库中。
范例
USE db1; SELECT COUNT(*) FROM mytable; #如果不使用USE db1, 每次需要查询对应表时,需要使用以下方式,表明使用对应数据库中的表。 select COUNT(*) FROM db1.mytable;