SQL commends cheat sheet 1 (W3school)

1. SELECT:

Format:

select column1, column 2 from dataset

select * from dataset

select distinct column from dataset

select column1, column2 from table_name where (conditions)

Conditions: AND, OR, LIKE, BETWEEN, IN

For example,

WHERE city like ‘s%’ (start with ‘s’.)
WHERE value IN (value1, value2, vlaue3)
WHERE column_name BETWEEN value1 AND value2

SELECT * FROM Products
WHERE Price NOT BETWEEN 10 AND 20;

SELECT * FROM Products
WHERE ProductName BETWEEN 'C' AND 'M';

SELECT * FROM Orders
WHERE OrderDate BETWEEN #07/04/1996# AND #07/09/1996#;

2. ORDER BY:

Format:

order by column1 ASC|DEC, column2 ASC|DEC

3. INSERT INTO:

Format:

insert into table_name values(v1,v2,…)

Examples:

INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES (‘Cardinal’,’Tom B. Erichsen’,’Skagen 21′,’Stavanger’,’4006′,’Norway’);

INSERT INTO Customers (CustomerName, City, Country)

VALUES (‘Cardinal’, ‘Stavanger’, ‘Norway’);

Note: the CustomerID field will of course also be updated automatically.

4.UPDATE:

Example:

UPDATE Customers

SET City=’New York’, ContactName=’Alfred’

WHERE CustomerID=1;

5. DELECT

Format:

DELECT FROM table_name

WHERE some_coln=some_value AND …

Note: DELECT FROM table_name will delect all

6. SQL injection

Example:

SELECT * FROM Users WHERE UserID=105 or 1=1;

Customers input:

User Name: John Doe

Password: myPass

uName = getRequestString(“UserName”);
uPass = getRequestString(“UserPass”);

sql = ‘SELECT * FROM Users WHERE Name =”‘ + uName + ‘” AND Pass =”‘ + uPass + ‘”‘

RESULT

SELECT * FROM Users WHERE Name =”John Doe” AND Pass =”myPass”

Customers input:

User Name: ” or “”=”

Password: ” or “”=”

Then result will be:

SELECT * FROM Users WHERE Name =”” or “”=”“ AND Pass=”” or “”=””

Since “=” is always true, it will return users.

Example:

SELECT * FROM Users; DROP TABLE Suppliers

txtUserId = getRequestString(“UserId”);
txtSQL = “SELECT * FROM Users WHERE UserId = ” + txtUserId;

Userid: 105; DROP TABLE Suppliers

Then the result will be:

SELECT * FROM Users WHERE UserId = 105; DROP TABLE Suppliers

7. Parameters for Protection

http://www.w3schools.com/sql/sql_injection.asp

8. LIMIT, TOP

Example:

SELECT *
FROM Persons
LIMIT 5;

Another way to get 5 rows:

SELECT column_name(s)
FROM table_name
WHERE ROWNUM <= number; (ROWNUM is a key word.)

SELECT TOP 2 * FROM Customers;

SELECT TOP 50 PERCENT * FROM Customers;

Wildcard	Description
%	A substitute for zero or more characters
_	A substitute for a single character
[charlist]	Sets and ranges of characters to match
[^charlist] or [!charlist]	Matches only a character NOT specified within the brackets

SELECT * FROM Customers
WHERE City LIKE ‘[bsp]%’; (start with b, or s or p.)

SELECT * FROM Customers
WHERE City LIKE ‘[a-c]%’; (start with a, b, c)

SELECT * FROM Customers
WHERE City LIKE ‘[!bsp]%’; (not start with b s p.)

Another way:

SELECT * FROM Customers

WHERE City NOT LIKE ‘[bsp]%’;

9. Alias

SELECT column_name AS alias_name
FROM table_name;

SELECT column_name(s)
FROM table_name AS alias_name;

SELECT CustomerName, Address+’, ‘+City+’, ‘+PostalCode+’, ‘+Country AS Address
FROM Customers;

Tip: It requires double quotation marks or square brackets if the column name contains spaces:

SELECT CustomerName, CONCAT(Address,’, ‘,City,’, ‘,PostalCode,’, ‘,Country) AS Address
FROM Customers;

SELECT o.OrderID, o.OrderDate, c.CustomerName
FROM Customers AS c, Orders AS o
WHERE c.CustomerName=”Around the Horn” AND c.CustomerID=o.CustomerID;

10. JOIN

INNER JOIN: Returns all rows when there is at least one match in BOTH tables
LEFT JOIN: Return all rows from the left table, and the matched rows from the right table
RIGHT JOIN: Return all rows from the right table, and the matched rows from the left table
FULL JOIN: Return all rows when there is a match in ONE of the tables

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
(INNER) JOIN Customers
ON Orders.CustomerID=Customers.CustomerID;

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;
SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;

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