Main components of SQL’s Syntax:
Data Definition Language (DDL)
A set of statements that allow user to modify data structures and objects e.g. tables
- The CREATE statement
CREATE object_type object_name;
CREATE TABLE object_name(column_name data_type);
CREATE TABLE sales(purchase_no INT);
- The ** ALTER** statement
Used when altering existing objects;
ADD, REMOVE, RENAME
-- Add a column in a table
ALTER TABLE sales
ADD COLUMN date_of_purchase DATA;
-- delete an entire table
DROP TABLE customer;
-- Rename a table
RENAME TABLE sales TO customer;
- The TRUNCATE statement
Instead of deleting an entire table through
DROP
, you can remove its data and continue to have as an abject in the database.
Data Manipulation Language (DML)
Statements that allow to manipulate the data in the tables of a database.
- The SELECT statement
SELECT … FROM..
-- deliver all the records in the table.
SELECT* FROM sales;
-- Select specific specific data from the database.
SELECT row FROM sales;
The INSERT statement
Used insert data into tables INSERT .. INTO .. VALUES …;
-- Choose specific cell to insert values
INSERT INTO sales (purchase_number, date_of_purchase)
VALUES(1, “12-09-2020”);
-- Insert value
INSERT INTO sales VALUES(1, “12-09-2020”);
- The UPDATE statement
UPDATE.. SET.. WHERE
Allows you to renew existing data of your table
UPDATE sales
SET date_of_purchase = “16-11_2020”
WHERE purchase_number =1;
- The DELETE statement
DELETE… FROM… WHERE
Specify what is to be removed in a table.
DELETE FROM sales
WHERE purchase_number =1;
Data Control Language (DCL)
GRANT and REVOKE statements. They allow rights of people using the database and used by administrators.
- The GRANT statement Gives or grants certain permissions to users.
GRANT type_of_permission ON database_name.table_name TO ‘username’@‘localhost’
-- Creates user `ochwada` whose password is "password"
CREATE USER ‘ochwada’@‘localhost’ IDENTIFIED BY ‘password’;
-- Allow the user to use all SELECT Statements on the table 'customer' from the 'sales' database.
GRANT SELECT ON sales.customers To ‘ochwada’@‘localhost’;
-- Use all statements and all tables in the 'sales' database.
GRANT ALL ON sales.* To ‘ochwada’@‘localhost’;
- The REVOKE Clause Used to revoke permissions and privileges of database users.
REVOKE type_of_permission ON database_name.table_name TO ‘username’@‘localhost’
-- Only the use of SELECT statement will be REVOKED for the table customers in the sales database.
REVOKE SELECT ON sales.customers To ‘ochwada’@‘localhost’;
-- use of all statements in the 'sales' database will be revoked.
REVOKE ALL ON sales.* To ‘ochwada’@‘localhost’;
Transaction Control Language (TCL)
Not every change you make to a database is saved automatically.
- The COMMIT statement
Related to
INSERT, DELETE, UPDATE
. It will save the changes you’ve made permanently Allow other users to have access to modified version of the database.
UPDATE sales
SET date_of_purchase = “16-11_2020”
WHERE purchase_number =1;
COMMIT;
- The ROLLBACK Clause The clause that will let you make a step back . Allow you to undo any changes you have made but don’t want to be saved permanently .
UPDATE sales
SET date_of_purchase = “16-11_2020”
WHERE purchase_number =1;
COMMIT;
ROLLBACK;
Top comments (0)