Database Statements

• SHOW [DATABASES / SCHEMAS / CHARACTER SET / CHARSET / COLLATION]

• CREATE [DATABASE / SCHEMA] {IF NOT EXISTS} {database_name}

• USE {database_name}

• DROP [DATABASE / SCHEMA] {IF EXISTS} {database_name}

• ALTER [DATABASE / SCHEMA] {database_name} [CHARACTER SET / CHARSET [=] utf8 COLLATE [=] utf8_unicode_ci]

• SELECT [version() / database()]

• BACKUP DATABASE database_name TO DISK = "filepath" [WITH DIFFERENTIAL];

Comment

# This comment continues to the end of the line

-- This comment continues to the end of the line

/* This is an in-line or multiple-line comment */

Get the Version of Database

SELECT version();

Create a New Database

CREATE DATABASE `database_name`;

CREATE SCHEMA `database_name`;

CREATE DATABASE IF NOT EXISTS `database_name`;

CREATE SCHEMA IF NOT EXISTS `database_name`;

List all Database Name

SHOW DATABASES;

Get the Character Set and Collection

SHOW CHARACTER SET;

SHOW COLLATION;

Create a New Database including Character Set and Collection

CREATE DATABASE `database_name` 
CHARACTER SET = utf8;

CREATE DATABASE `database_name` 
COLLATE = utf8_unicode_ci;

CREATE DATABASE `database_name`
CHARACTER SET = utf8 
COLLATE = utf8_unicode_ci;

CREATE DATABASE `database_name`
CHARACTER SET utf8 
COLLATE utf8_unicode_ci;

CREATE DATABASE IF NOT EXISTS `database_name`
CHARACTER SET = utf8 
COLLATE = utf8_unicode_ci;

Use a Database

USE `database_name`;

Get the currently selected Database

SELECT database();

Drop a Database

DROP DATABASE `database_name`;

DROP SCHEMA `database_name`;

DROP DATABASE IF EXISTS `database_name`;

Alter Database

ALTER DATABASE `database_name` 
CHARACTER SET = utf8;

ALTER DATABASE `database_name` 
COLLATE = utf8_unicode_ci;

ALTER DATABASE `database_name` 
CHARACTER SET = utf8 
COLLATE = utf8_unicode_ci;

ALTER DATABASE `database_name` 
CHARACTER SET utf8 
COLLATE utf8_unicode_ci;

ALTER DATABASE `database_name` 
CHARACTER SET = utf8mb4 
COLLATE = utf8mb4_unicode_ci;

Backup Database

BACKUP DATABASE database_name 
TO DISK = "D:\backups\backup_database_name.bak";

BACKUP DATABASE database_name 
TO DISK = "D:\backups\backup_database_name.bak" 
WITH DIFFERENTIAL;

Notes:

• SCHEMA is a synonym for DATABASE

• CHARSET is a synonym for CHARACTER SET

• Character Set: A character set is a set of symbols and encodings.

• Collation: A collation is a set of rules for comparing characters in a character set.

• Differential backup only the changes after the last backup.