section
qa
answer
MySQL Commands
Access MySQL server from the mysql client using a username and password (MySQL will prompt for a password):
mysql -u [username] -p;
MySQL Commands
Access a specific database using a username and password:
mysql -u [username] -p [database];
MySQL Commands
Exit
exit;
MySQL Commands
Export data using mysqldump tool
mysqldump -u [username] -p [database] > data_backup.sql;
MySQL Commands
To clear MySQL screen console window on Linux, you use the following command:
mysql> system clear;
Working with Database
Create a database with a specified name if it does not exist in database server
CREATE DATABASE [IF NOT EXISTS] database_name;
Working with Database
Use database or change current database to another database you are working with
USE database_name;
Working with Database
Drop a database with specified name permanently. All physical file associated with the database is no longer exists.
DROP DATABASE [IF EXISTS] database_name;
Working with Database
Show all available databases in the MySQL database server
show databases;
Working with Table
Lists all tables in a current database.
show tables;
Working with Table
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,...);
Working with Table
Altering table structure
Add a new column into a table
Working with Table
Drop an existing column in a table
ALTER TABLE table DROP [COLUMN];
Working with Table
Add index with a specific name to a table on a column.
ALTER TABLE table ADD INDEX [name](column, ...);
Working with Table
Add primary key into a table.
ALTER TABLE table ADD PRIMARY KEY (column,...)
Working with Table
Remove primary key from a table.
ALTER TABLE table DROP PRIMARY KEY
Working with Table
Deleting table structure and data permanently.
DROP TABLE [IF EXISTS] table [, name2, ...] [RESTRICT | CASCADE]
Working with Table
Get information about th table or a column.
DESCRIBE table [column]
Working with Index
Creating an index with the specified name on a table
CREATE [UNIQUE|FULLTEXT] INDEX index_name ON table (column,...)
Working with Index
Removing a specified index from table
DROP INDEX index_name
Querying Data
Query all data from a table
SELECT * FROM table
Querying Data
Query specified data which is shown in the column list from a table
SELECT column, column2…. FROM table;
Querying Data
Query unique records
SELECT DISTINCT (column) FROM table;
Querying Data
Query data with a filter using a WHERE clause.
SELECT * FROM table WHERE condition;
Querying Data
Change the output of the column name using column alias.
SELECT column_1 AS new_column_1, ... FROM table
Querying Data
Query data from multiple tables using inner join or left join
SELECT * FROM table_1 INNER JOIN table_2 ON conditions
Querying Data
Query data from multiple tables using inner join or left join
SELECT * FROM table1 LEFT JOIN table2 ON conditions
Querying Data
Counting rows in a table.
SELECT COUNT (*) FROM table
Querying Data
Sorting ascending or descending based on one or more columns using ORDER BY clause.
SELECT column, column2, ... FROM table ORDER BY column ASC [DESC], column2 ASC [DESC],...;
Querying Data
Group rows using GROUP BY clause.
SELECT * FROM table GROUP BY column_1, column_2, ...;
Querying Data
Filter group of rows using both GROUP BY and HAVING clauses.
SELECT * FROM table GROUP BY column_1 HAVING condition;
Modifying Data
Insert a new row into a table
INSERT INTO table(column1,column2,...) VALUES(value_1,value_2,...);
Modifying Data
Insert multiple rows into a table
INSERT INTO table(column1,column2,...) VALUES(value_1,value_2,...),(value_1,value_2,...), (value_1,value_2,...)...
Modifying Data
Update data for all rows
UPDATE table SET column_1 = value_1, ...
Modifying Data
Update data for a set of rows specified by a condition in WHERE clause.
UPDATE table SET column_1 = value_1, ... WHERE condition
Modifying Data
Update with join
UPDATE table_1, table_2 INNER JOIN table_1 ON table_1.column_1 = table_2.column_1 SET column_1 = value_1, WHERE condition
Modifying Data
Delete all rows in a table
DELETE FROM table;
Modifying Data
Delete rows specified by a condition
DELETE FROM table
Modifying Data
Delete with join
DELETE table_1, table2 FROM table_1 INNER JOIN table_2 ON table_1.column_1 = table_2.column_2 WHERE condition;
Search
Search for data using LIKE operator:
SELECT * FROM table WHERE column LIKE '%value%'
Search
Text search using a regular expression with RLIKE operator.
SELECT * FROM table WHERE column RLIKE 'regular_expression'
FAQ
How to convert data to the different format?
DATE_FORMAT(a.data, '%d.%m.%Y %h:%i:%s') AS date_pub