1
CREATE DATABASE
CREATE DATABASE databasename;
2
DROP DATABASE
DROP DATABASE databasename;
3
BACKUP DATABASE
BACKUP DATABASE databasename
TO DISK = 'filepath';
4
Datatypes
CREATE TABLE table_name (
column1 int,
column2 varchar(50),
....
);
5
DROP TABLE
DROP TABLE table_name;
6
ALTER TABLE
ALTER TABLE table_name
ADD column_name datatype;
7
CREATE TABLE
CREATE TABLE table_name (
column1 datatype constraint,
column2 datatype constraint,
column3 datatype constraint,
....
);
8
Constraint
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255) NOT NULL,
Age int
);
9
UNIQUE
CREATE TABLE Persons (
ID int NOT NULL UNIQUE,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int
);
10
NOT NULL
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
PRIMARY KEY (ID)
);
11
REFERENCES
CREATE TABLE Orders (
OrderID int NOT NULL,
OrderNumber int NOT NULL,
PersonID int,
PRIMARY KEY (OrderID),
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);
12
FOREIGN KEY
CREATE TABLE Orders (
OrderID int NOT NULL,
OrderNumber int NOT NULL,
PersonID int,
PRIMARY KEY (OrderID),
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);
13
DEFAULT
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
City varchar(255) DEFAULT 'Sandnes'
);
14
CREATE INDEX
CREATE INDEX index_name
ON table_name (column1, column2, ...);
15
AUTO INCREMENT Field
CREATE TABLE Persons (
Personid int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
PRIMARY KEY (Personid)
);
16
OrderDate
SELECT * FROM Orders WHERE OrderDate='2008-11-11'
17
CREATE VIEW
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;
18
injection
txtUserId = getRequestString("UserId");
txtSQL = "SELECT * FROM Users WHERE UserId = " + txtUserId;
19
SELECT
SELECT column1, column2, ...
FROM table_name;
20
WHERE condition
SELECT column1, column2, ...
FROM table_name
WHERE condition;
21
AND,OR,NOT Syntax
AND Syntax
SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 AND condition3 ...;
OR Syntax
SELECT column1, column2, ...
FROM table_name
WHERE condition1 OR condition2 OR condition3 ...;
NOT Syntax
SELECT column1, column2, ...
FROM table_name
WHERE NOT condition;
22
ORDER BY
SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;
23
INSERT INTO table
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
24
UPDATE
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
25
DELETE
DELETE FROM table_name WHERE condition;
26
SUM()AVG()
SELECT COUNT(column_name)
FROM table_name
WHERE condition;
AVG() Syntax
SELECT AVG(column_name)
FROM table_name
WHERE condition;
The SUM() function returns the total sum of a numeric column.
SUM() Syntax
SELECT SUM(column_name)
FROM table_name
WHERE condition;
27
MAX()MIN()
MIN() Syntax
SELECT MIN(column_name)
FROM table_name
WHERE condition;
MAX() Syntax
SELECT MAX(column_name)
FROM table_name
WHERE condition;
28
GROUP BY
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);
29
HAVING condition
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);
30
EXISTS Syntax
SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition);
31
Comments
--Select all:
SELECT * FROM Customers;
