SQL - Syntax
- Intro
- Products
- SQL Commands
- Compile
- DDL
- DML
- Conditions
- Others
- Links

SQL - Syntax

Intro

Structured Query Language
a computer language for storing, manipulating and retrieving data stored in relational database

Products

Oracle Database
Microsoft SQL Server
MySQL (Oracle Corporation)
IBM DB2

SQL Commands

DDL: Data Definition Language

CREATE
ALTER
DROP

DML: Data Manipulation Language

SELECT
INSERT
UPDATE
DELETE

DCL: Data Control Language

GRANT
REVOKE

Compile

Load sql file

@\path_of_SQL_file

DDL

Create DB

CREATE DATABASE dbname;

Create Table

CREATE TABLE table_name
(
	 column_name1 data_type(size),
	 column_name2 data_type(size),
	 column_name3 data_type(size),
	 ....
);

ALTER

ALTER TABLE table_name
ADD column_name datatype

ALTER TABLE table_name
DROP COLUMN column_name

DROP

DROP INDEX index_name
DROP TABLE table_name
DROP DATABASE database_name
TRUNCATE TABLE table_name

DML

SELECT & WHERE

SELECT column_name,column_name
FROM table_name
WHERE column_name operator value;

ORDER BY

SELECT column_name, column_name
FROM table_name
ORDER BY column_name ASC|DESC, column_name ASC|DESC;

INSERT INTO

INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);

UPDATE

UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;

DELETE

DELETE FROM table_name
WHERE some_column=some_value;

DELETE FROM table_name;
DELETE * FROM table_name;

Conditions

AND & OR

SELECT * FROM Customers
WHERE Country='Germany'
AND (City='Berlin' OR City='München');

SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern;
(pattern e.g. 's%')

specify multiple values in a WHERE clause
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...);

BETWEEN

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

WildCard Characters

%
_
[cahrlist]
[!charlist]

JOIN

LEFT/RIGHT/INNER/CROSS JOIN
combine one table with another

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;

Constraints

NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY
CHECK
DEFAULT

--Create Table + Constraint
CREATE TABLE table_name
(column_name1 data_type(size) constraint_name,
 column_name2 data_type(size) constraint_name,
 column_name3 data_type(size) constraint_name,
 ....);

--Create a constraint on the given column
ALTER TABLE Persons
ADD UNIQUE (P_Id)

--Allow naming of a UNIQUE constraint
ALTER TABLE Persons
ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)

Others

BACKUP

rollback;
commit;
truncate table table_name; (can't be roll back)

Operators

=, <, >, <=, >=, <>
BETWEEN
LIKE
IN, NOT IN
IS NULL, IS NOT NULL

Aggregate Functions

AVG()
COUNT()
FIRST()
LAST()
MAX()
MIN()
SUM()

Scalar functions

UCASE()
LCASE()
MID()
LEN()
ROUND()
NOW()
FORMAT()