DEV Community

Cover image for SQL Basic - What you need to know! (Basics)
Ochwada Linda
Ochwada Linda

Posted on

SQL Basic - What you need to know! (Basics)

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);
Enter fullscreen mode Exit fullscreen mode
  • 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;
Enter fullscreen mode Exit fullscreen mode
  • 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;      
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode
  • 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;
Enter fullscreen mode Exit fullscreen mode
  • The DELETE statement DELETE… FROM… WHERE Specify what is to be removed in a table.
DELETE FROM sales
WHERE purchase_number =1;
Enter fullscreen mode Exit fullscreen mode

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;

Enter fullscreen mode Exit fullscreen mode
  • 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;

Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode
  • 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;
Enter fullscreen mode Exit fullscreen mode

Top comments (0)