SQL Statements

LIKE

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. Syntax: SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern;

ORDER BY

The ORDER BY keyword is used to sort the result-set by one or more columns. The ORDER BY keyword sorts the records in ascending order by default. To sort the records in a descending order, you can use the DESC keyword. Syntax: SELECT column_name,column_name FROM table_name ORDER BY column_name,column_name ASC|DESC;

DISTINCT

The SELECT DISTINCT statement is used to return only distinct (different) values. Syntax: SELECT DISTINCT column_name,column_name FROM table_name;

INSERT INTO

It is possible to write the INSERT INTO statement in two forms. The first form does not specify the column names where the data will be inserted, only their values: The second form specifies both the column names and the values to be inserted:

ALIASES

SQL aliases are used to give a database table, or a column in a table, a temporary name. Basically aliases are created to make column names more readable. Syntax: SELECT column_name(s) FROM table_name AS alias_name;

INJECTION

SQL injection is a technique where malicious users can inject SQL commands into an SQL statement, via web page input. Injected SQL commands can alter SQL statement and compromise the security of a web application. Server Code Syntax: txtUserId = getRequestString("UserId"); txtSQL = "SELECT * FROM Users WHERE UserId = " + txtUserId; Server Result Syntax: SELECT * FROM Users WHERE UserId = 105 or 1=1

SELECT

Selects all records on a database. The result is stored in a result table, called the result- set. Syntax: SELECT column_name,column_name FROM table_name;

SEMICOLON

Some database systems require a semicolon at the end of each SQL statement. Semicolon is the standard way to separate each SQL statement in database systems that allow more than one SQL statement to be executed in the same call to the server.

AND & OR

The AND & OR operators are used to filter records based on more than one condition. Syntax: SELECT * FROM Customers WHERE Country='Germany' AND (City='Berlin' OR City='München'); The AND operator displays a record if both the first condition AND the second condition are true. Syntax: SELECT * FROM Customers WHERE Country='Germany' AND City='Berlin'; The OR operator displays a record if either the first condition OR the second condition is true. Syntax: SELECT * FROM Customers WHERE City='Berlin' OR City='München';

BETWEEN

The BETWEEN operator selects values within a range. The values can be numbers, text, or dates. Syntax: SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2;

DELETE

The DELETE statement is used to delete records in a table. The WHERE clause specifies which record or records that should be deleted. Syntax; DELETE FROM table_name WHERE some_column=some_value;

IN

The IN operator allows you to specify multiple values in a WHERE clause. Syntax: SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,...);

SELECT TOP

The SELECT TOP clause is used to specify the number of records to return. Can be very useful on large tables with thousands of records. MySQL Syntax: SELECT TOP number|percent column_name(s) FROM table_name; ORACLE Syntax: SELECT column_name(s) FROM table_name WHERE ROWNUM <= number;

UPDATE

The UPDATE statement is used to update existing records in a table The WHERE clause specifies which record or records that should be updated. If you omit the WHERE clause, all records will be updated! Syntax; UPDATE table_name SET column1=value1,column2=value2,... WHERE some_column=some_value;

WHERE CLAUSE

The WHERE clause is used to filter records, is used to extract only those records that fulfill a specified criterion. Syntax: SELECT column_name,column_name FROM table_name WHERE column_name operator value;

JOINS

clause is used to combine rows from two or more tables, based on a common field between them.

INNER JOIN

selects all rows from both tables as long as there is a match between the columns in both tables. Syntax: SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name=table2.column_name;

WILDCARDS

wildcard characters are used with the SQL LIKE operator. SQL wildcards are used to search for data within a table. With SQL, the wildcards are: % A substitute for zero or more characters; _ A substitute for a single character... SELECT * FROM Customers WHERE City LIKE '_erlin'; [charlist] Sets and ranges of characters to match... SELECT * FROM Customers WHERE City LIKE '[bsp]%'; [^charlist] or [!charlist] Matches only a character NOT specified within the brackets... SELECT * FROM Customers WHERE City LIKE '[!bsp]%';