Access MySQL server from the mysql client using a username and password (MySQL will prompt for a password):
mysql -u [username] -p;
Exit
exit;
Show all available databases in the MySQL database server
show databases;
Create a database
CREATE DATABASE database_name;
Use database or change current database to another database you are working with
USE database_name;
Drop a database with specified name permanently.
DROP DATABASE database_name;
Lists all tables in a current database.
show tables;
Create a new table or a temporary table
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table(
key type(size) NOT NULL PRIMARY KEY AUTO_INCREMENT,
c1 type(size) NOT NULL,
c2 type(size) NULL,
...
);
Query all data from a table
SELECT * FROM table
Query specified data which is shown in the column list from a table
SELECT column, column2….
FROM table;
Query unique records
SELECT DISTINCT (column)
FROM table;
Query data with a filter using a WHERE clause.
SELECT *
FROM table
WHERE condition;
Search for data using LIKE operator:
SELECT * FROM table
WHERE column LIKE '%value%'
Text search using a regular expression with RLIKE operator.
SELECT * FROM table
WHERE column RLIKE 'regular_expression'
Export data using mysqldump tool
mysqldump -u [username] -p [database] > data_backup.sql;
Import data from mysqldump
mysql -u username -p database_name < file.sql