19: Parser Implementation (3): Understand SQL grammar

2018/11/3

A parse need to recognize and check the grammar. So, let's see the grammar of SQL.

There are four basic SQL grammar with fundamental operations:

• Read the data -- SELECT
• Insert new data -- INSERT
• Update existing data -- UPDATE
• Remove data -- DELETE

It's a CRUD (Create, Read, Update, Delete) schema, and it's very like HTTP requests -- POST, GET, PUT, DELETE.

Also, there are two syntax CREATE and DELETE, which are for creating and deleting databases, tables.

General Forms of SQL syntax

SQL is not a complicated language, so there are general forms for the 2 + 4 grammar.

CREATE

CREATE DATABASE database_name

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
    ....
)

CREATE DATABASE testDB

CREATE TABLE Persons (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255)
);

SELECT

SELECT column-names
FROM table-name
WHERE condition
ORDER BY sort-order

SELECT FirstName, LastName, City, Country
FROM Customer
WHERE City = 'Tokio'
ORDER BY LastName

INSERT

INSERT table-name (column-names)
VALUES (column-values)

INSERT Supplier (Name, City, Country)
VALUES ('National Taiwan University', 'Taipei', 'Taiwan')

UPDATE

UPDATE table-name
SET column-name = column-value
WHERE condition

UPDATE OrderItem
SET Quantity = 22
WHERE Id = 38833

DELETE

DELETE table-name
WHERE condition

DELETE User
WHERE Email = 'phy.tiger@gmail.com'

DROP

DROP DATABASE database_name

DROP TABLE table_name

DROP DATABASE testDB

DROP TABLE Shippers