Summary SQL Notes

Types of SQL Commands
There are five types of SQL commands: DDL, DML, DCL, TCL, and DQL.




1. Data Definition Language (DDL)
o DDL changes the structure of the table like creating a table, deleting a table,
altering a table, etc.
o All the command of DDL are auto-committed that means it permanently save
all the changes in the database.

Here are some commands that come under DDL:

• CREATE
• ALTER
• DROP
• TRUNCATE

,2. Data Manipulation Language
DML commands are used to modify the database. It is responsible for all form of
changes in the database.

The command of DML is not auto-committed that means it can't permanently save
all the changes in the database. They can be rollback.

Here are some commands that come under DML:

• INSERT
• UPDATE
• DELETE

3. Data Control Language
DCL commands are used to grant and take back authority from any database user.

Here are some commands that come under DCL:

• Grant
• Revoke

4. Transaction Control Language
TCL commands can only use with DML commands like INSERT, DELETE and UPDATE
only.

These operations are automatically committed in the database that's why they
cannot be used while creating tables or dropping them.

Here are some commands that come under TCL:

• COMMIT
• ROLLBACK
• SAVEPOINT

,5. Data Query Language
DQL is used to fetch the data from the database.

It uses only one command:

• SELECT



CREATE DATABASE command
The CREATE DATABASE statement is used to create a new SQL database.

Syntax:
• CREATE DATABASE databasename;
Example:
• CREATE DATABASE testDB;


USE DATABASE command
The SQL USE statement is used to select any existing database in the SQL schema.
Syntax:
• USE DatabaseName;


SHOW DATABASES
The SHOW statement is used to show the databases available on MYSQL server.

Syntax:
• SHOW DATABASES;

, Note: The SHOW SCHEMAS command is also used to show the databases available
on MySQL server.
Syntax:

• SHOW SCHEMAS;


CREATE TABLE command
The CREATE TABLE statement is used to create a new table in a database.

Syntax:
• CREATE TABLE table_name (
column1 datatype,
column2 datatype,
column3 datatype,
....
);
Example:

• CREATE TABLE Persons (
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);



DESCRIBE command
The DESCRIBE command is used to show the structure of our table.

Syntax: DESCRIBE/DESC table_name;