MySQL Commands

SHOW DATABASES; To see the list of database

CREATE DATABASE database_name; To create data with with any name
DROP DATABASE database_name To remove the database from mysql server
USE database_name For using/going inside any database for working
SHOW TABLES; To see the list of all the created tables
CREATE TABLE table_name To create a table with any name and with any no. of
( Comumn1 datatype constraints, columns.
Comumn2 datatype constraints, datatype may be – int, varchar(), date, decimal()
Column3 datatype, constraints may be – primary key, not null, unique,
------- default, check
);
DESCRIBE table_name; OR DESC table_name; To view the table structure/schemas
DROP TABLE table_name; To remove table from database
ALTER TABLE table_name To add a column in already created table
ADD column_name datatype constraints;
ALTER TABLE table_name To remove a column from already created table
DROP COLUMN column_name;
ALTER TABLE table_name To change datatype and constraints of already created
MODIFY COLUMN column_name datatype constraints; table column
INSERT INTO table_name To add record in a table by their column name
(column1, column2, column3, …….)
VALUES (column1_value, column2_value, ……..);
INSERT INTO table_name To add records in a Table – All column values must be
VALUES (column1_value, column2_value, ……..); passes as continuous manner they created in table
UPDATE table_name To change the value(s) of columns of a table record(s)
SET column1=column1_value, column2=column2_value
WHERE condition_column=column_value;
DELETE FROM table_name To delete the record(s) of a table
WHERE condition_column=column_value;
SELECT * FROM table_name; To show all columns value of all records from a table
SELECT column1, column2,….. FROM table_name To view specific columns values from table records
SELECT column1, column2,…. The WHERE clause is used to filter records.
FROM table_name It is used to extract only those records that fulfill a
WHERE conditions; specified condition with =,<,<=,>,>=,<>(not equal)
SELECT column1, column2,…. The ORDER BY keyword is used to sort the result-set in
FROM table_name ascending (ASC) or descending (DESC) order. By default
ORDER BY column_name ASC/DESC; in ASC order.
SELECT MIN(column_name) The MIN() function returns the smallest value of the
FROM table_name WHERE condition; selected column.
SELECT MAX(column_name) The MAX() function returns the largest value of the
FROM table_name WHERE condition; selected column.
SELECT COUNT(column_name) The COUNT() function returns the number of rows that
FROM table_name WHERE condition; matches a specified criterion.
SELECT AVG(column_name) The AVG() function returns the average value of a
FROM table_name WHERE condition; numeric column.
SELECT SUM(column_name) The SUM() function returns the total sum of a numeric
FROM table_name WHERE condition; column.
SELECT column1, column2, ... The LIKE operator is used in a WHERE clause to search
FROM table_name for a specified pattern in a column.
WHERE columnN LIKE pattern; There are two wildcards often used in conjunction with
the LIKE perator:
WHERE ADDRESS LIKE ‘%a’ The percent sign (%) represents zero, one, or multiple
WHERE ADDRESS LIKE ‘a%’ characters
WHERE ADDRESS LIKE ‘_a%’ The underscore sign (_) represents one, single character
SELECT column1,colimn2,……. The IN operator allows you to specify multiple values in a
FROM table_name WHERE clause.
WHERE column_name IN (value1, value2, ...); IT is shorthand for multiple OR conditions.
SELECT column1,colimn2,……. The BETWEEN operator selects values within a given
FROM table_name range. The values can be numbers, text, or dates.
WHERE column_name BETWEEN value1 AND value2; Begin and end values are included.
SELECT column_name AS alias_name Aliases are used to give a table, or a column in a table, a
FROM table_name; temporary name.
SELECT COUNT(CustomerID), column_need_to_group The GROUP BY statement groups rows that have the
FROM table_name same values.
GROUP BY column_need_to_group; The GROUP BY statement is often used with aggregate
functions (COUNT(), MAX(), MIN(), SUM(), AVG()) to
group the result-set by one or more columns.
SELECT COUNT(CustomerID), Country The HAVING clause was added to SQL because the
FROM Customers WHERE keyword cannot be used with aggregate
GROUP BY Country functions.
HAVING COUNT(CustomerID) > 5;
SELECT column_name(s) The INNER JOIN keyword selects records that have
FROM table1 matching values in both tables.
INNER JOIN table2
ON table1.column_name = table2.column_name;

SELECT column_name(s) The LEFT JOIN keyword returns all records from the left
FROM table1 table (table1), and the matching records (if any) from the
LEFT JOIN table2 right table (table2).
ON table1.column_name = table2.column_name;

SELECT column_name(s) The RIGHT JOIN keyword returns all records from the
FROM table1 right table (table2), and the matching records (if any)
RIGHT JOIN table2 from the left table (table1).
ON table1.column_name = table2.column_name;

SELECT column_name(s) The CROSS JOIN or full join keyword returns all records
FROM table1 from both tables (table1 and table2).
CROSS JOIN table2;

Select all the different countries from the "Customers" The DISTINCT statement is used to return only distinct
table: (different) values.
SELECT DISTINCT Country FROM Customers;

RDBMS – Relational Database Management System

DDL – Data Definition Language (create, alter, drop)
DML – Data Manipulation Language (insert, update, delete, select)
Key – Column having NOT NULL and not duplicate values
Primary Key, Candidate Kay, Alternate Key, Foreign Key
Table – Relation Row – Tuple Column – Attibutes
Cardinality – Total no of rows/tuples
Degree – Total no of columns/attributes