Structure Query Language: It is a tool which is used to communicate with the database
Types of SQL statements
1. Data Definition Language(DDL)
e.g. CREATE, ALTER, DROP, RENAME, TRUNCATE
2. Data Manipulation Language(DML)
e.g. SELECT, INSERT, UPDATE, DELETE
3. Data Control Language(DCL)
e.g. GRANT & REVOKE
4. Transaction Control Language(TCL)
e.g. COMMIT ROLLBACK
Data Definition Language(DDL)
A set of statements that allow the user to define or modify data structures and objects, such as tables.
The CREATE statement
It is used for creating entire databases and database objects like tables.
How to create a database in MySQL?
CREATE DATABASE sales;
How to create a table in SQL.
CREATE TABLE <table_name>(
<col_name> <data_type>
<col2_name> <data_type>);
For example:
CREATE TABLE sales (
purchase_number INT,
customer_id INT,
item_code VARCHAR(10)
);
The ALTER statement
It is used when altering existing objects
ALTER TABLE sales
ADD COLUMN date_of_purchase DATE;
ALTER TABLE sales
ADD PRIMARY KEY(purchase_number);
ALTER TABLE sales
ADD UNIQUE KEY (item_code);
ALTER TABLE sales
DROP INDEX item_code;
ALTER TABLE sales
CHANGE COLUMN customer_id customer_id INT DEFAULT 0;
ALTER TABLE sales
CHANGE customer_id cust_id INT;
ALTER TABLE sales
MODIFY cust_id INT NOT NULL;
The DROP statement
It is used for deleting a database object.
DROP TABLE sales;
The RENAME statement
It allows you to rename an object.
RENAME TABLE sales TO sale;
The TRUNCATE statement
Instead of deleting an entire table through DROP, we can also remove its data and continue to have the table as an object in the database.
TRUNCATE TABLE customers;
Data Manipulation Language (DML)
Its statements allow us to manipulate the data in the tables of a database
The SELECT statement
It is used to retrieve data from database objects, like tables
SELECT * FROM sales;
The INSERT statement
It is used to insert data into tables
INSERT INTO… VALUES…;
INSERT INTO sales (purchase_number, date_of_purchase) VALUES(1, ‘2017-10-11’);
INSERT INTO sales VALUES(1, ‘2017-10-11’);
The UPDATE statement
It allows you to renew existing data from your tables.
UPDATE sales
SET date_of_purchase = '2017-12-12'
WHERE purchase_number = 1;
The DELETE statement
With DELETE, you can specify precisely what you would like to be removed functions similarly to the TRUNCATE statement
TRUNCATE vs. DELETE
TRUNCATE allows us to remove all the records contained in a table whereas
with DELETE, you can specify precisely what you would like to be removed.
DELETE FROM sales;
TRUNCATE TABLE sales;
DELETE FROM sales
WHERE
purchase_number = 1;
Next Topic: SQL DATA TYPE
