MySQL Basic Command List

Command for Create the database:
CREATE DATABASE database_name;
CREATE SCHEMA database_name;

Command For Creating The Database IF DATABASE NOT EXIST:

CREATE DATABASE IF NOT EXISTS database_name;
CREATE SCHEMA IF NOT EXISTS database_name;

To show the database:
SHOW DATABASES;

To show the particular database:
SHOW DATABASES LIKE “%search_text%”

To Delete the Database:
DROP DATABASE database_name;

To Delete the DATABASE if database available:
DROP DATABASE IF EXISTS database_name;

To select the database or change the database:
USE database_name;

To show all the tables of database:
SHOW TABLES;

To show the specific tables from database:
SHOW TABLES LIKE '%search_text%'

To show tables from another database:
SHOW TABLES FROM database_name;

For searching:
SHOW TABLES FROM database_name LIKE “%search_text%”;

To describe the table:
DESCRIBE table_name;

To show the create syntax of the Database:
SHOW CREATE DATABASE database_name;

To List all the Character Set of the MySQL:
SHOW CHARACTER SET;

Note: Character set shows the type of the character, which are storing in the database. Such as chinees, european, swidish, latin, arabic etc.

To show the sytax of create table:
SHOW CREATE TABLE table_name;

Show all the columns and the type of the table:
SHOW COLUMNS FROM table_name;

Note: It works just like a DESCRIBE table_name;

Show all the Open Tables and its status:
SHOW OPEN TABLES;

Show the error generated by Query and other operations:
SHOW ERRORS;

Note : It show the Error number and description also.

Count the number of Errors:
SHOW COUNT(*) ERRORS;
SELECT COUNT(*) ERRORS;
SELECT @@error_count;

Show all the warnings:
SHOW WARNINGS;

To Count the warnings:
SELECT COUNT(*) WARNINGS;
SHOW COUNT(*) WARNINGS;
SELECT @@warning_count;

Note: warning_count is the variable of MySQL and we can use the variable of MySQL using the “@@” symbol. Show the syntax is:

Syntax:
@@variable_name;

To show the Privileges of the current login user:
SHOW PRIVILEGES;

Note: It will display all the privileges of the current login user means that what type of operation he can be done. Some examples of privileges are ALTER, UPDATE, DROP, CREATE, DELETE etc.

To show all the Processlist:
SHOW PROCESSLIST;

Note: It will display all the process, users, states and the database which are currently running.

To show all the indexes of the tables:
1).
SHOW INDEX FROM table_name;
SHOW INDEX FROM student;

2).
SHOW INDEX FROM table_name FROM database_name;
SHOW INDEX FROM student FROM school;

3).
SHOW INDEX FROM database_name.table_name;
SHOW INDEX FROM school.student;

SHOW all the columns or fields name of the table:
1).
SHOW COLUMNS FROM table_name
SHOW COLUMNS FROM table_name FROM database_name;
SHOW COLUMNS FROM database_name.tablename;

2).
SHOW FIELDS FROM table_name;
SHOW FIELDS FROM table_name FROM database_name;
SHOW FIELDS FROM database_name.tablename;

3). DESC table_name;
4). DESCRIBE table_name;

To show all the database engines of MySQL:
1). SHOW ENGINES;
2). SHOW STORAGE ENGINES;
3). SHOW TABLE TYPES;

To show the status of any Engine:
SHOW ENGINE engine_name STATUS;
Example:
SHOW ENGINE INNODB STATUS;

To show the Logs for any Engine:
SHOW ENGINE engine_name LOGS;
Example:
SHOW ENGINE INNODB LOGS;

To Status of the tables of databse:
SHOW TABLE STATUS;

Note: It displays all the data of the table structure with advance options.