SQL/MySQL - 01 - Introduction

1. DATABASE

• Collection of Data
• Methods for accessing and manipulating that data
• MySQL, Oracle, PostgreSql, MongoDB, etc

2. SQL

• SQL = Structured Query Language
  • a programming language specifically designed for working with databases
  • allows you to write queries that the computer can execute and then provide database insights in return
  • It allows you to create, manipulate, share Data from Relational Database Management Systems.
• for business problems involving the processing of large amounts of data

2.1 Query

• a piece of code that inducing the computer to execute a certain operation that will deliver the desired output.

• relational algebra allows us to retrieve data efficiently

• types of programming:

  • procedural
  • object-oriented
  • declarative => SQL (focus on “what”)
  • functional

• main components of SQL’s syntax:

  • Data Definition Language(DDL)
  • Data Manipulation Language(DML)
  • Data Control Language(DCL)
  • Transaction Control Language(TCL)

2.2 DDL

• SQL’s syntax
  • comprises several types of statements that allow you to perform various commands and operations
• a set of statements that allow the user to define or modify data structures and objects, such as tables
• the CREATE statement
  • used for creating entire databases and database objects as tables

CREATE object_type object_name;
CREATE TABLE object_name(column_name data_type);

eg:

CREATE TABLE sales(purchase_number INT);

• the ALTER statement
  • used when altering existing objects
  • ADD
  • REMOVE
  • RENAME
    eg:

ALTER TABLE sales
ADD COLUMN date_of_purchase DATE;

• the DROP statement
  • used for deleting a database object

DROP object_type object_name;

• the RENAME statement
  • allows you to rename an object

RENAME object_type object_name TO new_object_name;

• 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 object_type object_name;

2.3 DML

• its statements allow us to manipulate the data in the tables of a database
• the SELECT statement
  • used to retrieve data from database objects, like tables
    eg:

SELECT * FROM sales;

• the INSERT statement
  • used to insert data into tables
  • INSERT ... INTO ... VALUES...;
    eg:

INSERT INTO sales (purchase_number, date_of_purchase) VALUES(1, '2017-10-11');
INSERT INTO sales (purchase_number, date_of_purchase) VALUES(2, '2017-10-27');

• the UPDATE statement
  • allows you to renew existing data of your tables
    eg:

UPDATE sales
SET data_of_purchase = '2017-12-12' WHERE purchase_number = 1;

• the DELETE statement
  • functions similarly to the TRUNCATE statements
  • TRUNCATE vs. DELETE
    • TRUNCATE allows us to remove all the records contained in a table
    • with DELETE, you can specify precisely what you would like to be removed
      eg:

DELETE FROM sales; = TRUNCATE TABLE sales;

DELETE FROM sales
WHERE
    purchase_number = 1;

keywords

SELECT... FROM...
INSERT INTO... VALUES
UPDATE... SET... WHERE
DELETE... FROM... WHERE

2.4 DCL

• the GRANT and REVOKE statements
  • allow us to manage the rights users have in a database
• the GRANT statement
  • gives (or grants) certain permissions to users
  • one can grant a specific type of permission, like complete or partial access
  • these rights will be assigned to a person who has a username registered at the local server('localhost': IP 127.0.0.1)
  • big companies and corporations don’t use this type of server, and their databases lay on external, more powerful servers.

GRANT type_of_permission ON database_name.table_name TO 'username'@'localhost'

• Database administrators
  • people who have complete rights to a database
  • they can grant access to users and can revoke it
• the REVOKE clause
  • use to revoke permissions and privileges of database users
  • the exact opposite of GRANT

REVOKE type_of_permission ON database_name.table_name FROM 'username'@'localhost'

2.5 TCL

• not every change you make to a database is saved automatically
• the COMMIT statement
  • related to INSERT, DELETE, UPDATE
  • saves the transaction in the database
  • will save the changes you’ve made
  • will let other users have access to the modified version of the database
  • changes cannot be undone
    eg:
• you want to change the last name of the 4th customer from ‘Winnfield’ to ‘Johnson’

UPDATE customers
SET last_name = 'Johnson' 
WHERE customer_id = 4;
COMMIT;

• the ROLLBACK clause
  • the clause that will let you make a step back
  • allows you to undo any changes you have made but don’t want to be saved permanently
  • the last change(s) made will not count
  • reverts to the last non-committed state

UPDATE customers
SET last_name = 'Johnson' 
WHERE customer_id = 4;
COMMIT;

ROLLBACK;

3. SQL’s syntax:

- Data Definition Language(DDL)
    - creation of data
- Data Manipulation Language(DML)
    - manipulation of data
- Data Control Language(DCL)
    - assignment and removal of permissions to use this data
- Transaction Control Language(TCL)
    - saving and restoring changes to a database