MySql Cheat Sheet

Login to MySQL

$ mysql –u root –p 

Password: cloudera 
(password will not visible but we have to type)

Show databases;

mysql> show databases;

Use Database

 $ use <database name> Ex:$ use retail_db;

Exit from mysql

mysql> quit;

Working with Database

Create database

create database [If not exists] testdatabase;

Drop database

drop database [If exists] testdatabase;

Create table or 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,
   ...
);

Show tables;

mysql> show tables;

Table schema

mysql> desc table <table name>;               Ex: mysql> desc departments;

List all rows

mysql>select * from departments;

Create table

create table EmpHistory(EmpId int,FirstName string,LastName string)

Load Data from abc.sql file

mysql> use db_name;
mysql> source /home/cloudera/workspace/MasterProject/Products.sql

Altering table structure

There are many actions that you can use with the ALTER TABLE statement as follows:

Add a new column into a table

1	ALTER TABLE table ADD [COLUMN];

Drop an existing column in a table

1	ALTER TABLE table DROP [COLUMN];

Add index with a specific name to a table on a column.

1	ALTER TABLE table ADD INDEX [name](column, ...);

Add primary key into a table.

1	ALTER TABLE table ADD PRIMARY KEY (column,...)

Remove primary key from a table.

1	ALTER TABLE table DROP PRIMARY KEY

Deleting table structure and data permanently.

1 2	DROP TABLE [IF EXISTS] table [, name2, ...] [RESTRICT | CASCADE]

Get information about th table or a column.

1	DESCRIBE table [column]

Working with Index

Creating an index with the specified name on a table

1 2	CREATE [UNIQUE|FULLTEXT] INDEX index_name ON table (column,...)

Removing a specified index from table

1	DROP INDEX index_name

Querying Data

Query all data from a table

1	SELECT * FROM table

Query specified data which is shown in the column list from a table

1 2	SELECT column, column2…. FROM table;

Query unique records

1 2	SELECT DISTINCT (column) FROM table;

Query data with a filter using a WHERE clause.

1 2 3

SELECT * FROM table WHERE condition;

Change the output of the column name using column alias.

1 2	SELECT column_1 AS new_column_1, ... FROM table

Query data from multiple tables using inner join or left join

1 2 3

SELECT * FROM table_1 INNER JOIN table_2 ON conditions

1 2 3

SELECT * FROM table1 LEFT JOIN table2 ON conditions

Counting rows in a table.

1 2	SELECT COUNT (*) FROM table

Sorting ascending or descending based on one or more columns using ORDER BY clause.

1 2 3

SELECT column, column2, ... FROM table ORDER BY column ASC [DESC], column2 ASC [DESC],...;

Group rows using GROUP BY clause.

1 2 3

SELECT * FROM table GROUP BY column_1, column_2, ...;

Filter group of rows using both GROUP BY and HAVING clauses.

1 2 3 4

SELECT * FROM table GROUP BY column_1 HAVING condition;

Modifying Data

Insert a new row into a table

1 2	INSERT INTO table(column1,column2,...) VALUES(value_1,value_2,...);

Insert multiple rows into a table

1 2 3 4

INSERT INTO table(column1,column2,...) VALUES(value_1,value_2,...),       (value_1,value_2,...),       (value_1,value_2,...)...

Update data for all rows

1 2 3

UPDATE table SET column_1 = value_1,     ...

Update data for a set of rows specified by a condition in WHERE clause.

1 2 3 4

UPDATE table SET column_1 = value_1,     ... WHERE condition

Update with join

1 2 3 4

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

Delete all rows in a table

1	DELETE FROM table;

Delete rows specified by a condition

1 2	DELETE FROM table WHERE condition;

Delete with join

1 2 3 4

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:

1 2	SELECT * FROM table WHERE column LIKE '%value%'

Text search using a regular expression with RLIKE operator.

1 2	SELECT * FROM table WHERE column RLIKE 'regular_expression'