SQL usage
SQL has following subsets:
- Data Definition Language (DDL)
- Data Manipulation Language (DML)
- Data Query Language (DQL)
- Data Control Language (DCL)
Data Definition Language (DDL)
The SQL DDL category provides commands for defining, deleting and modifying tables in a database. Use the following commands in this category.
-- DDL Commands
-- Create Table
CREATE TABLE member (id int);
-- Delete Table
DROP TABLE member;
-- Recreate Table
CREATE TABLE member (id int, age int);
-- Alter Table changes the structure of the table: add column
ALTER TABLE member ADD (name char(10));
-- Alter Table and drop column
ALTER TABLE member DROP COLUMN age ;
-- Alter Table and drop column
ALTER TABLE member MODIFY COLUMN name char(20);
-- Alter Table and add primary key
ALTER TABLE member ADD primary key (id);
-- Truncate Table to remove all the entries of the table but not deleting the table
TRUNCATE TABLE member;
Data Query Language (DQL)
DQL provides commands to retrieve and query from database.
-- DQL Commands
-- Select the whole Table
SELECT * FROM member;
-- Select a column from Table
SELECT name FROM member;
-- Select multiple column from Table
SELECT name, age FROM member;
Data Manipulation Language (DML)
DML provides commands to insert, update delete data in the database
-- DML Commands
-- Insert into Table one
INSERT INTO member (id, age, name) VALUES (1, 27, "Abrar");
-- Insert into Table multiple
INSERT INTO member (id, age, name)
VALUES
(1, 27, "Abrar"),
(1, 27, "Fahim"),
(1, 27, "Patwari");
-- Insert into select query
INSERT INTO country (countryName) SELECT country FROM member;
-- Update 1 data into Table
UPDATE member SET name = 'x' WHERE id = 1;
-- Update multiple data into Table
UPDATE member SET name = 'x', age = 26 WHERE id = 1;
-- Delete data from Table
DELETE FROM member WHERE id = 2;
Data Control Language (DCL)
DCL deals with the rights and permissions of users of a database system. Following commands are used:
GRANT
: Gives privileges required to allow users to access and manipulate the database.
REVOKE
: Remove permissions
Transaction Control Language (TCL)
It allows SQL statements to be grouped together into logical transactions.
COMMIT
: Command to save all the work done to the DB.
ROLLBACK
: Command to restore a database to the last committed state.