Friday, January 26, 2018

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
Drop an existing column in a table
Add index with a specific name to a table on a column.
Add primary key into a table.
Remove primary key from a table.
Deleting table structure and data permanently.
Get information about th table or a column.

Working with Index

Creating an index with the specified name on a table
Removing a specified index from table

Querying Data

Query all data from a table
Query specified data which is shown in the column list from a table
Query unique records
Query data with a filter using a WHERE clause.
Change the output of the column name using column alias.
Query data from multiple tables using inner join or left join
Counting rows in a table.
Sorting ascending or descending based on one or more columns using ORDER BY clause.
Group rows using GROUP BY clause.
Filter group of rows using both GROUP BY and HAVING clauses.

Modifying Data

Insert a new row into a table
Insert multiple rows into a table
Update data for all rows
Update data for a set of rows specified by a condition in WHERE clause.
Delete rows specified by a condition

Search

Search for data using LIKE operator:
Text search using a regular expression with RLIKE operator.

No comments:

Post a Comment