Introduction

SQL stands for Structured Query Language.

SQL lets you access and manipulate database.

Using SQL in Your Web Site

To build a web site that shows data from a database, you will need:

  • An RDBMS database program (i.e. MS Access, SQL Server, MySQL)
  • To use a server-side scripting language, like PHP or ASP
  • To use SQL to get the data you want
  • To use HTML / CSS to style the page

DBMS

The data in RDBMS is stored in database objects called tables. A table is a collection of related data entries and it consists of columns and rows.

A filed is a column in table that is designed to maintain specific information about every record in the table.

A record is a row that exists in the table.

Table

A database most often contain one or more tables. Tables contain records (rows) with data.

SQL Statements

  • SELECT - extracts data from a database
  • UPDATE - updates data in a database
  • DELETE - deletes data from a database
  • INSERT INTO - inserts new data into a database
  • CREATE DATABASE - creates a new database
  • ALTER DATABASE - modifies a database
  • CREATE TABLE - creates a new table
  • ALTER TABLE - modifies a table
  • DROP TABLE - deletes a table
  • CREATE INDEX - creates an index (search key)
  • DROP INDEX - deletes an index

SELECT

The select allows you to select the filed(column) from the table. If you want to select all columns you should use select * from table_name

1
2
SELECT column1, column2, ...
FROM table_name;

select distinct statement can satisfy you to list distinct(different) values of the columns, which means it delete the repetitive values. 

1
2
SELECT DISTINCT column1, column2, ...
FROM table_name;

WHERE

This statement add some conditions so that can extract the specify records you want. 

1
2
3
SELECT column1, column2, ...
FROM table_name
WHERE condition;

Note: The WHERE clause is not only used in SELECT statements, it is also used in UPDATE, DELETE, etc.!

Text Fields vs. Numeric Fields

SQL requires single quotes around text values (most database systems will also allow double quotes).

However, numeric fields should not be enclosed in quotes:

1
2
3
4
5
SELECT * FROM Customers
WHERE Country='Mexico';

SELECT * FROM Customers
WHERE CustomerID=1;

Operators in The WHERE Clause

  • AND, OR, NOT

    There can be contain more than one record by using the and , or ,not

    • and

      If the all the conditions separated by and are turn, the result is true. That means the extracted records are display by where if they fulfill all conditions. 

      1
      WHERE condition1 AND condition2 AND condition3 ...;

    • or

      If the any of the conditions separated by or are turn, the result is true. That means the extracted records are display by where if they fulfill one of the conditions. 

      1
      WHERE condition1 OR condition2 OR condition3 ...;

    • not

      The NOT operator displays a record if the condition(s) is NOT TRUE.

      1
      WHERE NOT condition;

  • The following operators can be used in the WHERE clause:

    Operator Description
    = Equal
    > Greater than
    < Less than
    >= Greater than or equal
    <= Less than or equal
    <> Not equal. Note: In some versions of SQL this operator may be written as !=
    BETWEEN Between a certain range
    LIKE Search for a pattern
    IN To specify multiple possible values for a column

LIKE

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

There are two wildcards often used in conjunction with the LIKE operator:

  • The percent sign (%) represents zero, one, or multiple characters
  • The underscore sign (_) represents one, single character

Note: MS Access uses an asterisk (*) instead of the percent sign (%), and a question mark (?) instead of the underscore (_).

1
2
3
SELECT column1, column2, ...
FROM table_name
WHERE columnN LIKE pattern;

Example

1
2
SELECT * FROM Customers
WHERE CustomerName LIKE 'a%';

NOT LIKE

The following SQL statement selects all customers with a CustomerName that does NOT start with “a”:

1
2
SELECT * FROM Customers
WHERE CustomerName NOT LIKE 'a%';

IN

The IN operator is a shorthand for multiple OR conditions. 

1
2
3
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);

NOT IN

1
2
SELECT * FROM Customers
WHERE Country NOT IN ('Germany', 'France', 'UK');

BETWEEN

The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates.

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

NOT BETWEEN

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

NULL

The IS NULL operator is used to test for empty values (NULL values).

1
2
3
SELECT CustomerName, ContactName, Address
FROM Customers
WHERE Address IS NULL;

The IS NOT NULL operator is used to test for non-empty values (NOT NULL values).

1
2
3
SELECT CustomerName, ContactName, Address
FROM Customers
WHERE Address IS NOT NULL;

ORDER BY

The ORDER BY keyword is used to sort the result-set in ascending or descending order.

The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword.

ORDER BY Country DESC;

1
2
3
SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;

  • ORDER BY Several Columns

The following SQL statement selects all customers from the “Customers” table, sorted by the “Country” and the “CustomerName” column. This means that it orders by Country, but if some rows have the same Country, it orders them by CustomerName:

1
2
SELECT * FROM Customers
ORDER BY Country, CustomerName;

INSERT INTO

There are two ways you can insert new record into a table.

  1. Specify both the column names and the values to be inserted:

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

  2. If you add a whole record you needn’t specify the column name. 

    1
    2
    INSERT INTO table_name
    VALUES (value1, value2, value3, ...);

UPDATE

The UPDATE statement is used to modify the existing records in a table.

1
2
3
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Be careful when updating records. If you omit the WHERE clause, ALL records will be updated!

DELETE

The DELETE statement is used to delete existing records in a table.

1
DELETE FROM table_name WHERE condition;

If you omit the WHERE clause, all records in the table will be deleted!

JOIN

A JOIN clause is used to combine rows from two or more tables, based on a related column between them.

inner join

left join

right join

full join

INNER JOIN

The INNER JOIN keyword selects records that have matching values in both tables.

1
2
3
4
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

Let’s look at a selection from the “Orders“ table:

OrderID CustomerID OrderDate
10308 2 1996-09-18
10309 37 1996-09-19
10310 77 1996-09-20

Then, look at a selection from the “Customers“ table:

CustomerID CustomerName ContactName Country
1 Alfreds Futterkiste Maria Anders Germany
2 Ana Trujillo Emparedados y helados Ana Trujillo Mexico
3 Antonio Moreno Taquería Antonio Moreno Mexico

Notice that the “CustomerID” column in the “Orders” table refers to the “CustomerID” in the “Customers” table. The relationship between the two tables above is the “CustomerID” column.

1
2
3
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;

and it will produce something like this:

OrderID CustomerName OrderDate

LEFT JOIN

The LEFT JOIN keyword returns all records from the left table (table1), and the matching records from the right table (table2).

1
2
3
4
SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

RIGHT JOIN

The RIGHT JOIN keyword returns all records from the right table (table2), and the matching records from the left table (table1). 

1
2
3
4
SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;

FULL JOIN

The FULL OUTER JOIN keyword returns all records when there is a match in left (table1) or right (table2) table records.

1
2
3
4
5
SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name
WHERE condition;

SELF JOIN

A self join is a regular join, but the table is joined with itself.

1
2
3
4
SELECT column_name(s)
FROM table1 T1, table1 T2
WHERE condition;
#T1 and T2 are different table aliases for the same table.

GROUP BY

The GROUP BY statement groups rows that have the same values into summary rows, like “find the number of customers in each country” and we group by the ‘country’. So the GROUP BY statement is often used with aggregate functions (COUNT(), MAX(), MIN(), SUM(), AVG()) to group the result-set by one or more columns.

1
2
3
4
5
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);

The following SQL statement lists the number of customers in each country:

1
2
3
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country;

Having

HAVING statement usually follows by the GROUP BY statement to show how many records in each group. 

1
2
3
4
5
6
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);

The following SQL statement lists the number of customers in each country. Only include countries with more than 5 customers:

1
2
3
4
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5;

Function

MIN()

Export the smallest value of the selected column. 

1
2
3
SELECT MIN(column_name)
FROM table_name
WHERE condition;

MAX()

Export the largest value of the selected column.

1
2
3
SELECT MAX(column_name)
FROM table_name
WHERE condition;

COUNT()

The count function return the number of the rows of selected column. 

1
2
3
SELECT COUNT(column_name)
FROM table_name
WHERE condition;

AVG()

The AVG() function returns the average value of a numeric column. 

1
2
3
SELECT AVG(column_name)
FROM table_name
WHERE condition;

SUM()

The SUM() function returns the total sum of a numeric column. 

1
2
3
SELECT SUM(column_name)
FROM table_name
WHERE condition;

Aliases

SQL aliases are used to give a table, or a column in a table, a temporary name.

An alias is created with the AS keyword.

Alias Column Syntax

1
2
SELECT *column_name* AS *alias_name*
FROM *table_name;*

Alias Table Syntax

1
2
SELECT *column_name(s)*
FROM *table_name* AS *alias_name;*