Basic MySQL queries

MySQL common queries for beginners

1. MySQL Queries
DDL (Data definition language)
1) DATABASE
a) Create database
b) Show databases list
c) Select/Use database
d) Delete/Dropdatabase
2) TABLE
a) Create table
b) Show tables list
c) Describe table(table structure)
d) Delete/Drop table
e) Rename table
f) Column-
i) Add a column
ii) Rename a column
iii) Delete a column
iv) Modify datatype or size
g) Constraints-
i) Add constraint
(1) Not null
(2) Unique
(3) Primary key
(4) Foreign key
ii) Drop constraint
(1) Not null
(2) Unique
(3) Primary key
(4) Foreign key
3) INDEX
a) Create index
b) Create unique index
c) Show indices
d) Drop index
4) VIEW
a) Create view
b) Show views
c) Modify view
d) Drop view

2. DML (Data Manipulation language)
1. INSERT
a. Insert into all columns
b. Insert into specific columns
2. SELECT
a. Select all data (all rows all columns – full table)
b. Select specific columns
c. Select all data based on condition
d. Select data in ascending/descending order
3. UPDATE
a. Update values which satisfy condition
b. Update all values of a specific column(same values for a particular column)
4. DELETE
a. Delete specific rows based on condition
b. Clear all data of a table(delete all rows)
DDL (Data definition language)
1) DATABASE
a) CREATE DATABASE<database_name>;
b) SHOW DATABASES ;
c) USE<database_name>;
d) DROP DATABASE<database_name>;
2) TABLE
a) CREATE TABLE <table_name>
(< column_name1><datatype>[<constraint>],
< column_name2><datatype>[<constraint>],…);
b) SHOW TABLES;
c) DESCRIBE<table_name>;
d) DROP TABLE<table_name>;
e) ALTER TABLE<old_table_name>
RENAME TO<new_table_name>;
f) COLUMN-
i) Add a column-
ALTER TABLE <table name>
ADD <new_column_name><datatype>[<constraint>];
ii) Rename a column-
ALTER TABLE <table name>
CHANGE <old_column_name><new_column_name><datatype>[<constraint>];
iii) Delete-

3. ALTER TABLE <table name>
DROP <column_name>;
iv) Modify datatype or size -
ALTER TABLE <table name>
MODIFY <column_name><datatype>[<constraint>];
g) Constraints-
i) Add constraint
(1) Not null-
ALTER TABLE <table name>
MODIFY <column_name><datatype>NOT NULL;
(2) Unique-
ALTER TABLE <table name>
ADD CONSTRAINT <constraint_name> UNIQUE (<column_name>);

(3) Primary key-
ALTER TABLE <table name>
MODIFY <column_name><datatype>PRIMARY KEY;

ii) Drop constraint
(1) Not null-
ALTER TABLE <table name>
MODIFY <column_name><datatype>NULL;
(2) Unique
ALTER TABLE <table name>
DROP INDEX <constraint_name>;
(3) Primary key-
ALTER TABLE <table name>
DROP PRIMARY KEY;
3) INDEX
a) CREATE INDEX<index_name>
ON<table_name> (<column_name>);
b) CREATE UNIQUE INDEX<index_name>
ON<table_name> (<column_name>);
c) SHOW INDEX
ON<table_name>;
d) ALTER TABLE<table_name>
DROP INDEX<index_name>;
4) VIEW
a) CREATE VIEW <view_name>AS
SELECT<column_name1>, <column_name2>,…
FROM<table_name>
WHERE<condition>;

4. b) SHOWTABLES;
c) CREATE OR REPLACE VIEW<view_name>AS
SELECT<column_name1>, <column name2>,…
FROM<table_name>;
WHERE<condition>;
d) DROP VIEW<view_name>;

5. DML (Data Manipulation language)
1. INSERT-
a. INSERT INTO <table_name>
VALUES(<column1_value>,< column2_value>,……);
b. INSERTINTO<table_name> (<column1>, <column2>,…)
VALUES(<column1_value>, <column2_value>,……);
2. SELECT-
a. SELECT *
FROM<table_name>;
b. SELECT<column1, column2,….>
FROM <table_name>;
c. SELECT *
FROM<table_name>
WHERE <condition>;
d. SELECT *
FROM<table_name>
ORDER BY <column1><ascending/descending>;
3. UPDATE-
a. UPDATE<table_name>
SET<column_name>=<new_value>;
b. UPDATE<table_name>
SET<column_name>=<new_value>
WHERE<condition>;
4. DELETE-
a. DELETE
FROM <table_name>
WHERE <condition>;
b. DELETE
FROM <table_name>;

Identifiers-
An Identifier is essentially a name of a database, table, or table column.
Rules-
1. First letter must be alphabet (a-z, A-Z).
2. Second and other letters can be numbers(0-9), alphabet (a-z, A-Z) and _(underscore).