SQL - Day 4

Today, we'll learn about all about how to create, delete and modify a table in MySQL!

SQL can be divided into 3 categories: Data Definition Language (DDL), Data Manipulation Language (DML) and Data Control Language (DCL)

Data Manipulation Language (DML)

SELECT, INSERT, UPDATE, DELETE

Select we have already seen.

INSERT:

INSERT INTO movies (id, name, year, rankscore) VALUES ('3984224', 'Random movie', '2002', 9.2), ('900000', 'Dark Netflix', '2020', 10);

If you want to insert data from an another table, you can use Nested sub-query.

See page

INSERT INTO phone_book2 SELECT * FROM phone_book WHERE name IN ('John Doe', 'Peter Doe')

UPDATE:

UPDATE movies SET name='Not so random movie', rankscore=9.1 where id='3984224';

DELETE:

DELETE FROM movies where id='3984224';

---

Data Definition Language (DDL)

CREATE, TRUNCATE, ALTER, DROP

CREATE TABLE language (id INT PRIMARY, language VARCHAR(50) NOT NULL);

Id: integer and is a primary key (primary is constraint)

language: characters of max length as 50 and can not be null (not null is constraint)

Different Data Types:

Source: https://www.journaldev.com/16774/sql-data-types

SQL Constraints:

https://www.w3schools.com/sql/sql_constraints.asp

The following constraints are commonly used in SQL:

• NOT NULL - Ensures that a column cannot have a NULL value

• UNIQUE - Ensures that all values in a column are different

• PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table

• FOREIGN KEY - Uniquely identifies a row/record in another table

• CHECK - Ensures that all values in a column satisfies a specific condition

• DEFAULT - Sets a default value for a column when no value is specified

• INDEX - Used to create and retrieve data from the database very quickly

TRUNCATE: Removes all the rows of the table

TRUNCATE TABLE movies; // Same as DELETE FROM movies;

ALTER: Add, modify, Drop

ALTER TABLE language ADD Country VARCHAR(50) NOT NULL;

ALTER TABLE language MODIFY Country VARCHAR(60) ; <can also remove constraint here>

ALTER TABLE language DROP Country;

DROP:

Delete removes only the data in consideration (the table structure remains). Drop removes the entire table.

DROP TABLE language IF EXISTS;

---

Data Control Language (DCL)

To restrict a certain user to not able to alter the data/access/write privileges

GRANT, REVOKE

GRANT, REVOKE (LINKS)

GRANT :

GRANT ALL ON db1.*TO'jeffrey'@'localhost';

GRANT 'role1','role2' TO 'user1'@'localhost','user2'@'localhost';

GRANT SELECT ON world.*TO 'role3'; //only select access to world table to role 3

REVOKE INSERT ON *.* FROM 'jeffrey'@'localhost'; //revoke insert right from all databases and tables for jeffery (first * in *.* means data bases and next star means tables)

REVOKE 'role1','role2' FROM 'user1'@'localhost','user2'@'localhost';

REVOKE SELECT ON world .* FROM 'role3';

Guys! That's all. We're done with the basics of SQL. This is the last post of the SQL series.

Next up - I will be posting some progress on my coding challenges. Started a 30-day coding challenge at Code Academy plus planning to solve riddles in Python Challenge

See you tomorrow! :)