SQL Syntax Basic

April 11, 2021 1 minute read

Retrieve from Table

SELECT statement

A Data Manipulation Language (DML) statement for reading and editing data

To Retrieve all data from a table
```
select * from tablename
```
Retrieve certain columns from table
```
select col1, col2, col3 from tablename
```

Retrieve the result set: Where Clause

Add condition to result result

select col1, col2 from tablename WHERE col1='item1'

Here listed all the supported comparison operators in WHERE Clause

sign	operation
=	Equal to
>	Greater than
>=	Greater or equal
<=	Lesser or equal
><	Not equal to

INSERT statment

Add row(s) to a table

INSERT INTO tablename (column1, column2, ... )
VALUES (value1, value2, ... ) [,(value11, value22, ...)]

In the VALUES clause, multiple entreis are allowed to add multiple rows into the table at once

UPDATE statement

Update or Alter the data in a table

UPDATE tablename
SET column1 = value1, column2 = value2, ...
WHERE condition

If WHERE is omitted, the statement will alter the value in given column for all the record

DELETE statement

Remove 1 or more rows from the table

DELETE FROM tablename
WHERE condition

If WHERE is omitted, the statement will delete all the record

Create or Alter a Table

CREATE statement

Create a table in a relational database

CREATE TABLE tablename
  (
    col_name_1 datatype optional_params,
    col_name_2 datatype,
    ...
    col_name_n datatype,
  )

ALTER statement

Add or remove columns, keys, constraints
Modify data type

It’s possible to perform multiple same type operations but not different types

ALTER TABLE tablename
  ADD COLUMN col_name_1 datatype
  ...
  ADD COLUMN col_name_2 datatype;

Each type requires an independent ALTER statement

ALTER TABLE tablename
  ADD COLUMN col_name data_type column_constraint;

ALTER TABLE tablename
  DROP COLUMN col_name;

ALTER TABLE tablename
  ALTER COLUMN col_name SET DATA TYPE data_type;

ALTER TABLE tablename
  RENAME COLUMN current_column_name TO new_column_name;

DROP statement

Delete an existing table

DROP TABLE tablename;

Truncate statement

Delete all rows in table not the table itself

TRUNCATE TABLE tablename
  IMMEDIATE;

Build-In Functions

COUNT

Retrieve the number of rows matching the query

SELECT count(*) FROM tablename where predicate

DISTINCT

Remove duplicate values from a result set

SELECT DISTINCT col FROM tablename WHERE predicate

LIMIT

Restrict the number of rows retrieved from the table

SELECT * FROM tablename WHERE predicate LIMIT 5

Xiaotian Derrick Yang