SQL is a standard language for storing, manipulating and retrieving data in databases.
Our SQL tutorial will teach you how to use SQL in: MySQL, SQL Server, MS Access, Oracle, Sybase, Informix, Postgres, and other database systems.
SQL is a standard language for accessing and manipulating databases.
Introduction to SQL
What is SQL?
- SQL stands for Structured Query Language
- SQL lets you access and manipulate databases
- SQL became a standard of the American National Standards Institute (ANSI) in 1986, and of the International Organization for Standardization (ISO) in 1987
What Can SQL do?
- SQL can execute queries against a database
- SQL can retrieve data from a database
- SQL can insert records in a database
- SQL can update records in a database
- SQL can delete records from a database
- SQL can create new databases
- SQL can create new tables in a database
- SQL can create stored procedures in a database
- SQL can create views in a database
- SQL can set permissions on tables, procedures, and views
SQL is a Standard – BUT….
Although SQL is an ANSI/ISO standard, there are different versions of the SQL language.
However, to be compliant with the ANSI standard, they all support at least the major commands (such as SELECT, UPDATE, DELETE, INSERT, WHERE) in a similar manner.
Note: Most of the SQL database programs also have their own proprietary extensions in addition to the SQL standard!
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
SQL Syntax
Database Tables
A database most often contains one or more tables. Each table is identified by a name (e.g. “Customers” or “Orders”). Tables contain records (rows) with data.
In this tutorial we will use the well-known Northwind sample database (included in MS Access and MS SQL Server).
Keep in Mind That…
- SQL keywords are NOT case sensitive: select is the same as SELECT
Semicolon after SQL Statements?
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.
Some of The Most Important SQL Commands
- 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
SQL SELECT Statement
The SELECT statement is used to select data from a database.
The data returned is stored in a result table, called the result-set.
SELECT Syntax
SELECT column1, column2, … FROM table_name;
Here, column1, column2, … are the field names of the table you want to select data from. If you want to select all the fields available in the table, use the following syntax:
SELECT * FROM table_name;
SQL SELECT DISTINCT Statement
The SELECT DISTINCT statement is used to return only distinct (different) values.
Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different (distinct) values.
SELECT DISTINCT Syntax
SELECT DISTINCT column1, column2, … FROM table_name;
SQL WHERE Clause
The WHERE clause is used to filter records.
The WHERE clause is used to extract only those records that fulfill a specified condition.
WHERE Syntax
SELECT column1, column2, … FROM table_name WHERE condition;
Note: The WHERE clause is not only used in SELECT statement, it is also used in UPDATE, DELETE statement, etc.!
SQL AND, OR and NOT Operators
The WHERE clause can be combined with AND, OR, and NOT operators.
The AND and OR operators are used to filter records based on more than one condition:
- The AND operator displays a record if all the conditions separated by AND are TRUE.
- The OR operator displays a record if any of the conditions separated by OR is TRUE.
The NOT operator displays a record if the condition(s) is NOT TRUE.
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;
SQL ORDER BY Keyword
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 Syntax
SELECT column1, column2, … FROM table_name ORDER BY column1, column2, … ASC|DESC;
SQL INSERT INTO Statement
The INSERT INTO statement is used to insert new records in a table.
INSERT INTO Syntax
It is possible to write the INSERT INTO statement in two ways.
The first way specifies both the column names and the values to be inserted:
INSERT INTO table_name (column1, column2, column3, …) VALUES (value1, value2, value3, …);
If you are adding values for all the columns of the table, you do not need to specify the column names in the SQL query. However, make sure the order of the values is in the same order as the columns in the table. The INSERT INTO syntax would be as follows:
INSERT INTO table_name VALUES (value1, value2, value3, …);
SQL NULL Values
A field with a NULL value is a field with no value.
If a field in a table is optional, it is possible to insert a new record or update a record without adding a value to this field. Then, the field will be saved with a NULL value.
Note: A NULL value is different from a zero value or a field that contains spaces. A field with a NULL value is one that has been left blank during record creation!
How to Test for NULL Values?
It is not possible to test for NULL values with comparison operators, such as =, <, or <>.
We will have to use the IS NULL and IS NOT NULL operators instead.
IS NULL Syntax
SELECT column_names FROM table_name WHERE column_name IS NULL;
IS NOT NULL Syntax
SELECT column_names FROM table_name WHERE column_name IS NOT NULL;
SQL UPDATE Statement
The UPDATE statement is used to modify the existing records in a table.
UPDATE Syntax
UPDATE table_name SET column1 = value1, column2 = value2, … WHERE condition;
Note: Be careful when updating records in a table! Notice the WHERE clause in the UPDATE statement. The WHERE clause specifies which record(s) that should be updated. If you omit the WHERE clause, all records in the table will be updated!
SQL DELETE Statement
The DELETE statement is used to delete existing records in a table.
DELETE Syntax
DELETE FROM table_name WHERE condition;
Note: Be careful when deleting records in a table! Notice the WHERE clause in the DELETE statement. The WHERE clause specifies which record(s) should be deleted. If you omit the WHERE clause, all records in the table will be deleted!
SQL TOP, LIMIT or ROWNUM Clause
The SELECT TOP clause is used to specify the number of records to return.
The SELECT TOP clause is useful on large tables with thousands of records. Returning a large number of records can impact on performance.
Note: Not all database systems support the SELECT TOP clause. MySQL supports the LIMIT clause to select a limited number of records, while Oracle uses ROWNUM.
SQL Server / MS Access Syntax:
SELECT TOP number|percent column_name(s) FROM table_name WHERE condition;
MySQL Syntax:
SELECT column_name(s) FROM table_name WHERE condition LIMIT number;
Oracle Syntax:
SELECT column_name(s) FROM table_name WHERE ROWNUM <= number;
SQL MIN() and MAX() Functions
The MIN() function returns the smallest value of the selected column.
The MAX() function returns the largest value of the selected column.
MIN() Syntax
SELECT MIN(column_name) FROM table_name WHERE condition;
MAX() Syntax
SELECT MAX(column_name) FROM table_name WHERE condition;
SQL COUNT(), AVG() and SUM()Functions
The COUNT() function returns the number of rows that matches a specified criteria.
The AVG() function returns the average value of a numeric column.
The SUM() function returns the total sum of a numeric column.
COUNT() Syntax
SELECT COUNT(column_name) FROM table_name WHERE condition;
AVG() Syntax
SELECT AVG(column_name) FROM table_name WHERE condition;
SUM() Syntax
SELECT SUM(column_name) FROM table_name WHERE condition;
SQL LIKE Operator
The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.
There are two wildcards used in conjunction with the LIKE operator:
- % – The percent sign represents zero, one, or multiple characters
- _ – The underscore represents a single character
Note: MS Access uses a question mark (?) instead of the underscore (_).
The percent sign and the underscore can also be used in combinations!
LIKE Syntax
SELECT column1, column2, … FROM table_name WHERE columnN LIKE pattern;
Tip: You can also combine any number of conditions using AND or OR operators.
Here are some examples showing different LIKE operators with ‘%’ and ‘_’ wildcards:
LIKE Operator | Description |
---|---|
WHERE CustomerName LIKE ‘a%’ | Finds any values that start with “a” |
WHERE CustomerName LIKE ‘%a’ | Finds any values that end with “a” |
WHERE CustomerName LIKE ‘%or%’ | Finds any values that have “or” in any position |
WHERE CustomerName LIKE ‘_r%’ | Finds any values that have “r” in the second position |
WHERE CustomerName LIKE ‘a_%_%’ | Finds any values that start with “a” and are at least 3 characters in length |
WHERE ContactName LIKE ‘a%o’ | Finds any values that start with “a” and ends with “o” |
SQL Wildcards Characters
A wildcard character is used to substitute any other character(s) in a string.
Wildcard characters are used with the SQL LIKE operator. The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.
There are two wildcards used in conjunction with the LIKE operator:
- % – The percent sign represents zero, one, or multiple characters
- _ – The underscore represents a single character
Note: MS Access uses a question mark (?) instead of the underscore (_).
In MS Access and SQL Server you can also use:
- [charlist] – Defines sets and ranges of characters to match
- [^charlist] or [!charlist] – Defines sets and ranges of characters NOT to match
The wildcards can also be used in combinations!
Here are some examples showing different LIKE operators with ‘%’ and ‘_’ wildcards:
LIKE Operator | Description |
---|---|
WHERE CustomerName LIKE ‘a%’ | Finds any values that starts with “a” |
WHERE CustomerName LIKE ‘%a’ | Finds any values that ends with “a” |
WHERE CustomerName LIKE ‘%or%’ | Finds any values that have “or” in any position |
WHERE CustomerName LIKE ‘_r%’ | Finds any values that have “r” in the second position |
WHERE CustomerName LIKE ‘a_%_%’ | Finds any values that starts with “a” and are at least 3 characters in length |
WHERE ContactName LIKE ‘a%o’ | Finds any values that starts with “a” and ends with “o” |
SQL IN Operator
The IN operator allows you to specify multiple values in a WHERE clause.
The IN operator is a shorthand for multiple OR conditions.
IN Syntax
SELECT column_name(s) FROM table_name WHERE column_name IN (value1, value2, …);
or:
SELECT column_name(s) FROM table_name WHERE column_name IN (SELECT STATEMENT);
SQL BETWEEN Operator
The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates.
The BETWEEN operator is inclusive: begin and end values are included.
BETWEEN Syntax
SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2;
SQL Aliases
SQL aliases are used to give a table, or a column in a table, a temporary name.
Aliases are often used to make column names more readable.
An alias only exists for the duration of the query.
Alias Column Syntax
SELECT column_name AS alias_name FROM table_name;
Alias Table Syntax
SELECT column_name(s) FROM table_name AS alias_name;
SQL Joins
A JOIN clause is used to combine rows from two or more tables, based on a related column between them.
Different Types of SQL JOINs
Here are the different types of the JOINs in SQL:
- (INNER) JOIN: Returns records that have matching values in both tables
- LEFT (OUTER) JOIN: Return all records from the left table, and the matched records from the right table
- RIGHT (OUTER) JOIN: Return all records from the right table, and the matched records from the left table
- FULL (OUTER) JOIN: Return all records when there is a match in either left or right table
SQL INNER JOIN Keyword
The INNER JOIN keyword selects records that have matching values in both tables.
INNER JOIN Syntax
SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;
SQL LEFT JOIN Keyword
The LEFT JOIN keyword returns all records from the left table (table1), and the matched records from the right table (table2). The result is NULL from the right side, if there is no match.
LEFT JOIN Syntax
SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;
Note: In some databases LEFT JOIN is called LEFT OUTER JOIN.
SQL RIGHT JOIN Keyword
The RIGHT JOIN keyword returns all records from the right table (table2), and the matched records from the left table (table1). The result is NULL from the left side, when there is no match.
RIGHT JOIN Syntax
SELECT column_name(s) FROM table1 RIGHT JOIN table2 ON table1.column_name = table2.column_name;
Note: In some databases RIGHT JOIN is called RIGHT OUTER JOIN.
SQL FULL OUTER JOIN Keyword
The FULL OUTER JOIN keyword return all records when there is a match in either left (table1) or right (table2) table records.
Note: FULL OUTER JOIN can potentially return very large result-sets!
FULL OUTER JOIN Syntax
SELECT column_name(s) FROM table1 FULL OUTER JOIN table2 ON table1.column_name =table2.column_name;
SQL Self JOIN
A self JOIN is a regular join, but the table is joined with itself.
Self JOIN Syntax
SELECT column_name(s) FROM table1 T1, table1 T2 WHERE condition;
SQL UNION Operator
The UNION operator is used to combine the result-set of two or more SELECT statements.
- Each SELECT statement within UNION must have the same number of columns
- The columns must also have similar data types
- The columns in each SELECT statement must also be in the same order
UNION Syntax
SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2;
UNION ALL Syntax
The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL:
SELECT column_name(s) FROM table1 UNION ALL SELECT column_name(s) FROM table2;
Note: The column names in the result-set are usually equal to the column names in the first SELECT statement in the UNION.
SQL GROUP BY Statement
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.
GROUP BY Syntax
SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) ORDER BY column_name(s);
SQL HAVING Clause
The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.
HAVING Syntax
SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) HAVING condition ORDER BY column_name(s);
SQL EXISTS Operator
The EXISTS operator is used to test for the existence of any record in a subquery.
The EXISTS operator returns true if the subquery returns one or more records.
EXISTS Syntax
SELECT column_name(s) FROM table_name WHERE EXISTS (SELECT column_name FROM table_name WHERE condition);
SQL ANY and ALL Operators
The ANY and ALL operators are used with a WHERE or HAVING clause.
The ANY operator returns true if any of the subquery values meet the condition.
The ALL operator returns true if all of the subquery values meet the condition.
ANY Syntax
SELECT column_name(s) FROM table_name WHERE column_name operator ANY (SELECT column_name FROM table_name WHERE condition);
ALL Syntax
SELECT column_name(s) FROM table_name WHERE column_name operator ALL (SELECT column_name FROM table_name WHERE condition);
Note: The operator must be a standard comparison operator (=, <>, !=, >, >=, <, or <=).
SQL SELECT INTO Statement
The SELECT INTO statement copies data from one table into a new table.
SELECT INTO Syntax
Copy all columns into a new table:
SELECT * INTO newtable [IN externaldb] FROM oldtable WHERE condition;
Copy only some columns into a new table:
SELECT column1, column2, column3, … INTO newtable [IN externaldb] FROM oldtable WHERE condition;
The new table will be created with the column-names and types as defined in the old table. You can create new column names using the AS clause.
SQL INSERT INTO SELECT Statement
The INSERT INTO SELECT statement copies data from one table and inserts it into another table.
- INSERT INTO SELECT requires that data types in source and target tables match
- The existing records in the target table are unaffected
INSERT INTO SELECT Syntax
Copy all columns from one table to another table:
INSERT INTO table2 SELECT * FROM table1 WHERE condition;
Copy only some columns from one table into another table:
INSERT INTO table2 (column1, column2, column3, …) SELECT column1, column2, column3, … FROM table1 WHERE condition;
SQL CASE Statement
The CASE statement goes through conditions and return a value when the first condition is met (like an IF-THEN-ELSE statement). So, once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the ELSE clause.
If there is no ELSE part and no conditions are true, it returns NULL.
CASE Syntax
CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 WHEN conditionN THEN resultN ELSE result END;
SQL Stored Procedures for SQL Server
A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again.
So if you have an SQL query that you write over and over again, save it as a stored procedure, and then just call it to execute it.
You can also pass parameters to a stored procedure, so that the stored procedure can act based on the parameter value(s) that is passed.
Stored Procedure Syntax
CREATE PROCEDURE procedure_name AS sql_statement GO;
Execute a Stored Procedure
EXEC procedure_name;
SQL Comments
Comments are used to explain sections of SQL statements, or to prevent execution of SQL statements.
Single Line Comments
Single line comments start with –.
Any text between — and the end of the line will be ignored (will not be executed).
Multi-line Comments
Multi-line comments start with /* and end with */.
Any text between /* and */ will be ignored.
Tratto dal sito: https://www.w3schools.com/sql/default.asp