##############
What is MySQL:
##############
By: infotinks
This is just a copy paste of some articles from here. All credit goes to them. I only claim that I formatted it like this an cancotanated it all like so
WORK CITED/BIBLIOGRAPHY: http://www.w3schools.com/sql/default.asp
### How data stored in relational db? ###
* Data is stored in tables
* Relational database has tables
* Data stored in tables has name, table has columns and rows
* Record equals row
* columns have a name
* structured like English
A query is a command window. Where you can type a command or two and execute them. You can put keywords like GO and — to control the flow of the commands
There is also a GUI thing you can do for every Command line query, here how ever I will just cover the querys
To execute commands above use GO n. where is an integer like GO 10. Will repeat the commands above the GO 10 times
##############
Make DATABASE:
##############
CREATE DATABASE database_name
###########
Make Table:
###########
NOTE:
Make sure to specify the key, and NULL or NOT NULL, you will see what this means after reading this section
===Simplest Form:===
CREATE TABLE table_name
(
column_name1 data_type,
column_name2 data_type,
column_name3 data_type,
….
)
===Example:===
CREATE TABLE Persons
(
P_Id int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
CONSTRAINTS ON MAKING A TABLE
#############################
When making a table can have 6 different constraints (important ones are NULL and PRIMARY KEY, so ill start with those):
### NOT NULL ###
By default, a table column can hold NULL values… Thus if not specified like in above simple case its like saying NULL, thus meaning can hold empty/null values
The NOT NULL constraint enforces a field to always contain a value. This means that you cannot insert a new record, or update a record without adding a value to this field.
===example:===
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
### UNIQUE ###
Meaning cant have more than one of the same value, like the ID of people. Cant have entry number 12 and entry number 12 again.
The UNIQUE constraint uniquely identifies each record in a database table.
The UNIQUE and PRIMARY KEY constraints both provide a guarantee for uniqueness for a column or set of columns.
A PRIMARY KEY constraint automatically has a UNIQUE constraint defined on it.
===Example:===
Slightly different form in SQL Server/Oracle/MS Access vs MySQL:
===MySQL:===
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
UNIQUE (P_Id)
)
===SQL Server / Oracle / MS Access:===
CREATE TABLE Persons
(
P_Id int NOT NULL UNIQUE,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
===Multiple Example (SAME FOR ALL SQL programs):===
===MySQL / SQL Server / Oracle / MS Access:===
To allow naming of a UNIQUE constraint, and for defining a UNIQUE constraint on multiple columns, use the following SQL syntax:
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)
)
### PRIMARY KEY ###
The PRIMARY KEY constraint uniquely identifies each record in a database table.
Primary keys must contain unique values.
A primary key column cannot contain NULL values.
Each table should have a primary key, and each table can have only ONE primary key.
===Example:===
Its different with MySQL vs SQL Server/Oracle/MS access:
===MySQL:===
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (P_Id)
)
===SQL Server / Oracle / MS Access:===
CREATE TABLE Persons
(
P_Id int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
===Multiple Example (SAME FOR ALL SQL programs):===
===MySQL / SQL Server / Oracle / MS Access:===
To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on multiple columns, use the following SQL syntax:
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
)
### FOREIGN KEY ###
SQL FOREIGN KEY Constraint
A FOREIGN KEY in one table points to a PRIMARY KEY in another table.
Let’s illustrate the foreign key with an example. Look at the following two tables:
The “Persons” table:
P_Id, LastName, FirstName, Address, City
1, Hansen, Ola, Timoteivn 10, Sandnes
2, Svendson, Tove, Borgvn 23, Sandnes
3, Pettersen, Kari, Storgt 20, Stavanger
The “Orders” table:
O_Id, OrderNo, P_Id
1, 77895, 3
2, 44678, 3
3, 22456, 2
4, 24562, 1
Note that the “P_Id” column in the “Orders” table points to the “P_Id” column in the “Persons” table.
The “P_Id” column in the “Persons” table is the PRIMARY KEY in the “Persons” table.
The “P_Id” column in the “Orders” table is a FOREIGN KEY in the “Orders” table.
The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables.
The FOREIGN KEY constraint also prevents that invalid data form being inserted into the foreign key column, because it has to be one of the values contained in the table it points to.
SQL FOREIGN KEY Constraint on CREATE TABLE
The following SQL creates a FOREIGN KEY on the “P_Id” column when the “Orders” table is created:
===MySQL:===
CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
)
===SQL Server / Oracle / MS Access:===
CREATE TABLE Orders
(
O_Id int NOT NULL PRIMARY KEY,
OrderNo int NOT NULL,
P_Id int FOREIGN KEY REFERENCES Persons(P_Id)
)
===Multiple Example (SAME FOR ALL SQL programs):===
===MySQL / SQL Server / Oracle / MS Access:===
To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns, use the following SQL syntax:
CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
)
### CHECK ###
The CHECK constraint is used to limit the value range that can be placed in a column.
If you define a CHECK constraint on a single column it allows only certain values for this column.
If you define a CHECK constraint on a table it can limit the values in certain columns based on values in other columns in the row.
===MySQL:===
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CHECK (P_Id>0)
)
===SQL Server / Oracle / MS Access:===
CREATE TABLE Persons
(
P_Id int NOT NULL CHECK (P_Id>0),
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
===Multiple Example (SAME FOR ALL SQL programs):===
===MySQL / SQL Server / Oracle / MS Access:===
To allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple columns, use the following SQL syntax:
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT chk_Person CHECK (P_Id>0 AND City=’Sandnes’)
)
### DEFAULT ###
The DEFAULT constraint is used to insert a default value into a column.
The default value will be added to all new records, if no other value is specified.
===NOTE SAME ON ALL SQL Programs:===
===My SQL / SQL Server / Oracle / MS Access:===
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255) DEFAULT ‘Sandnes’
)
==WITH GETDATE:===
The DEFAULT constraint can also be used to insert system values, by using functions like GETDATE():
===Example:===
CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
OrderDate date DEFAULT GETDATE()
)
More Contraints:
################
### Auto Increment ###
Auto-increment allows a unique number to be generated when a new record is inserted into a table.
AUTO INCREMENT a Field
Very often we would like the value of the primary key field to be created automatically every time a new record is inserted.
We would like to create an auto-increment field in a table.
===Syntax for MySQL===
The following SQL statement defines the “P_Id” column to be an auto-increment primary key field in the “Persons” table:
CREATE TABLE Persons
(
P_Id int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (P_Id)
)
===Syntax for SQL Server===
The following SQL statement defines the “P_Id” column to be an auto-increment primary key field in the “Persons” table:
CREATE TABLE Persons
(
P_Id int PRIMARY KEY IDENTITY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
===Syntax for Access===
The following SQL statement defines the “P_Id” column to be an auto-increment primary key field in the “Persons” table:
CREATE TABLE Persons
(
P_Id PRIMARY KEY AUTOINCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
===Syntax for Oracle===
In Oracle the code is a little bit more tricky.
You will have to create an auto-increment field with the sequence object (this object generates a number sequence).
Use the following CREATE SEQUENCE syntax:
CREATE SEQUENCE seq_person
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 10
##############################
Put/INSERT entries from table:
##############################
2 Forms/ways/methods to do this:
### Form 1: ###
INSERT INTO table_name
VALUES (value1, value2, value3,…)
—Example:—
INSERT INTO Persons
VALUES (4,’Nilsen’, ‘Johan’, ‘Bakken 2’, ‘Stavanger’)
### Form 2: ###
INSERT INTO table_name (column1, column2, column3,…)
VALUES (value1, value2, value3,…)
—Example:—
INSERT INTO Persons (P_Id, LastName, FirstName)
VALUES (5, ‘Tjessem’, ‘Jakob’)
################
Change an entry:
################
UPDATE table_name
SET column1=value, column2=value2,…
WHERE some_column=some_value
—example:—
UPDATE Persons
SET Address=’Nissestien 67′, City=’Sandnes’
WHERE LastName=’Tjessem’ AND FirstName=’Jakob’
###################################
Deleting/DELETE entries from table:
###################################
DELETE FROM table_name
WHERE some_column=some_value
—example:—
DELETE from table
WHERE firstname=’koss’
#######
SELECT:
#######
Used to display results
SELECT column FROM table
Can select 1 or more colums, if more seperate with comma
All *
SELECT column1,column2 FROM table
This tutorial has colums and tables in quotes like so, but Im not using them, because it tends to work without quotes as well… double quotes here by the way… when you set values to string use single quotes
SELECT “column1″,”column2” FROM “table”
To Select all
SELECT * FROM table
################
SELECT DISTINCT:
################
To select Unique values:
SELECT DISTINCT column FROM table
######
WHERE:
######
Can put WHERE to select subset, its like a filter
SELECT Column FROM table WHERE condition
NOTE with WHERES can have AND/OR
SELECT * FROM Persons
WHERE FirstName=’Tove’
OR FirstName=’Ola’
—
SELECT * FROM Persons
WHERE FirstName=’Tove’
AND LastName=’Svendson’
######
ORDER:
######
If want to order the above
add ORDER BY column [ASC|DESC] to the end
SELECT Column FROM table WHERE condition ORDER BY column [ASC|DESC]
OR verticly with line feeds
SELECT “Column_Name”
FROM “Table_Name”
WHERE “Condition”
ORDER BY “Column_Name” [ASC|DESC]
ASC=Ascending… grows, starts from low goes up– 1 to 1000, a to z
DESC=Descending… shrinks, starts from highest and goes down– 1000 to 1, z to a
#########
GROUP BY:
#########
SELECT column1, FUNCTION(column2) FROM table GROUP BY column1
or represented like this
SELECT column1,
FUNCTION(column2)
FROM table
GROUP BY column1
First talk about functions — SUM, AVG, MIN, MAX, COUNT operates on column
—Example:—
Date, Store, Sales_Amount
Highest Sales entry
SELECT MAX(Sales_amount)
Highest Sales per store
SELECT store, MAX(Sales_amount)
its not enough
need Group By
We want to tell SQL to group same names, thus telling us the info for each store
SELECT c1, FUNCTION(c2) FROM t1 GROUP BY c1
#######
HAVING:
#######
Goes together with GROUP BY quiet well
Also can filter based on results of a function, not just the entry. so instead of WHERE where it looks at the entry we will use HAVING(CONDITION based on FUNCTION) which filters based on result of function
SELECT c1, FUNCTION(c2) FROM t1 GROUP BY c1 HAVING(CONDITION based on FUNCTION)
—-
SELECT c1,
FUNCTION(c2)
FROM t1
GROUP BY c1
HAVING(CONDITION based on FUNCTION)
HAVING needs to go after the GROUP BY or else we would use WHERE
Or in other words:
Any filtering result based on a group by needs to be calculated after the grouping happens so thats why
HAVING goes after GROUP BY
HAVING SUM(sales)>1500
### Example: ###
—Table Store_Information:—
store_name, Sales, Date
Los Angeles, $1500, Jan-05-1999
San Diego, $250, Jan-07-1999
Los Angeles, $300, Jan-08-1999
Boston, $700, Jan-08-1999
—HAVING example:—
SELECT store_name, SUM(sales)
FROM Store_Information
GROUP BY store_name
HAVING SUM(sales) > 1500
—Result:—-
store_name, SUM(Sales)
Los Angeles, $1800
Read more: http://www.1keydata.com/sql/sqlhaving.html#ixzz2D6YoU3re
”It would group all the stores and add them up and then give the MAX
##############################
Example with WHERE and HAVING:
##############################
We have the following “Orders” table:
O_Id, OrderDate, OrderPrice, Customer
1, 2008/11/12, 1000, Hansen
2, 2008/10/23, 1600, Nilsen
3, 2008/09/02, 700, Hansen
4, 2008/09/03, 300, Hansen
5, 2008/08/30, 2000, Jensen
6, 2008/10/04, 100, Nilsen
Now we want to find if any of the customers have a total order of less than 2000.
We use the following SQL statement:
SELECT Customer,SUM(OrderPrice) FROM Orders
GROUP BY Customer
HAVING SUM(OrderPrice)<2000
The result-set will look like this:
Customer, SUM(OrderPrice)
Nilsen, 1700
Now we want to find if the customers “Hansen” or “Jensen” have a total order of more than 1500.
We add an ordinary WHERE clause to the SQL statement:
SELECT Customer,SUM(OrderPrice) FROM Orders
WHERE Customer=’Hansen’ OR Customer=’Jensen’
GROUP BY Customer
HAVING SUM(OrderPrice)>1500
The result-set will look like this:
Customer, SUM(OrderPrice)
Hansen, 2000
Jensen, 2000
######################
Order of the Commands:
######################
SELECT … FROM
WHERE
GROUP BY
HAVING
ORDER BY
#######################
ALTER: Changing Tables:
#######################
### The ALTER TABLE Statement ###
The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.
===SQL ALTER TABLE Syntax===
To add a column in a table, use the following syntax:
ALTER TABLE table_name
ADD column_name datatype
To delete a column in a table, use the following syntax (notice that some database systems don’t allow deleting a column):
ALTER TABLE table_name
DROP COLUMN column_name
To change the data type of a column in a table, use the following syntax:
—SQL Server / MS Access:—
ALTER TABLE table_name
ALTER COLUMN column_name datatype
—-My SQL / Oracle:—
ALTER TABLE table_name
MODIFY column_name datatype
### SQL ALTER TABLE Example ###
Look at the “Persons” table:
P_Id, LastName, FirstName, Address, City
1, Hansen, Ola, Timoteivn 10, Sandnes
2, Svendson, Tove, Borgvn 23, Sandnes
3, Pettersen, Kari, Storgt 20, Stavanger
Now we want to add a column named “DateOfBirth” in the “Persons” table.
We use the following SQL statement:
ALTER TABLE Persons
ADD DateOfBirth date
Notice that the new column, “DateOfBirth”, is of type date and is going to hold a date. The data type specifies what type of data the column can hold. For a complete reference of all the data types available in MS Access, MySQL, and SQL Server, go to our complete Data Types reference.
The “Persons” table will now like this:
P_Id, LastName, FirstName, Address, City, DateOfBirth
1, Hansen, Ola, Timoteivn 10, Sandnes
2, Svendson Tove, Borgvn 23, Sandnes
3, Pettersen, Kari, Storgt 20, Stavanger
### Change Data Type Example ###
Now we want to change the data type of the column named “DateOfBirth” in the “Persons” table.
We use the following SQL statement:
ALTER TABLE Persons
ALTER COLUMN DateOfBirth year
Notice that the “DateOfBirth” column is now of type year and is going to hold a year in a two-digit or four-digit format.
### DROP COLUMN Example ###
Next, we want to delete the column named “DateOfBirth” in the “Persons” table.
We use the following SQL statement:
ALTER TABLE Persons
DROP COLUMN DateOfBirth
The “Persons” table will now like this:
P_Id, LastName, FirstName, Address, City
1, Hansen, Ola, Timoteivn 10, Sandnes
2, Svendson, Tove, Borgvn 23, Sandnes
3, Pettersen, Kari, Storgt 20, Stavanger
CONSTRAINTS and ALTER
#####################
### ALTER with NOT NULL ###
N/A
### ALTER with UNIQUE ###
===MySQL / SQL Server / Oracle / MS Access:===
To create a UNIQUE constraint on the “P_Id” column when the table is already created, use the following SQL:
ALTER TABLE Persons
ADD UNIQUE (P_Id)
===MySQL / SQL Server / Oracle / MS Access:===
To allow naming of a UNIQUE constraint, and for defining a UNIQUE constraint on multiple columns, use the following SQL syntax:
ALTER TABLE Persons
ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)
### ALTER with PRIMARY KEY ###
===MySQL / SQL Server / Oracle / MS Access:===
To create a PRIMARY KEY constraint on the “P_Id” column when the table is already created, use the following SQL:
ALTER TABLE Persons
ADD PRIMARY KEY (P_Id)
===MySQL / SQL Server / Oracle / MS Access:===
To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on multiple columns, use the following SQL syntax:
ALTER TABLE Persons
ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
Note: If you use the ALTER TABLE statement to add a primary key, the primary key column(s) must already have been declared to not contain NULL values (when the table was first created).
### ALTER with FOREIGN KEY ###
===MySQL / SQL Server / Oracle / MS Access:===
To create a FOREIGN KEY constraint on the “P_Id” column when the “Orders” table is already created, use the following SQL:
ALTER TABLE Orders
ADD FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
===MySQL / SQL Server / Oracle / MS Access:===
To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns, use the following SQL syntax:
ALTER TABLE Orders
ADD CONSTRAINT fk_PerOrders
FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
### ALTER with CHECK ###
===MySQL / SQL Server / Oracle / MS Access:===
To create a CHECK constraint on the “P_Id” column when the table is already created, use the following SQL:
ALTER TABLE Persons
ADD CHECK (P_Id>0)
===MySQL / SQL Server / Oracle / MS Access:===
To allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple columns, use the following SQL syntax:
ALTER TABLE Persons
ADD CONSTRAINT chk_Person CHECK (P_Id>0 AND City=’Sandnes’)
### ALTER with DEFAULT ###
To create a DEFAULT constraint on the “City” column when the table is already created, use the following SQL:
===MySQL:===
ALTER TABLE Persons
ALTER City SET DEFAULT ‘SANDNES’
===SQL Server / MS Access:===
ALTER TABLE Persons
ALTER COLUMN City SET DEFAULT ‘SANDNES’
===Oracle:===
ALTER TABLE Persons
MODIFY City DEFAULT ‘SANDNES’
##################################
DROP: Removing things from tables:
##################################
Indexes, tables, and databases can easily be deleted/removed with the DROP statement.
===The DROP INDEX Statement===
The DROP INDEX statement is used to delete an index in a table.
—DROP INDEX Syntax for MS Access:—
DROP INDEX index_name ON table_name
—DROP INDEX Syntax for MS SQL Server:—
DROP INDEX table_name.index_name
—DROP INDEX Syntax for DB2/Oracle:—
DROP INDEX index_name
—-DROP INDEX Syntax for MySQL:—
ALTER TABLE table_name DROP INDEX index_name
===The DROP TABLE Statement===
The DROP TABLE statement is used to delete a table.
DROP TABLE table_name
===The DROP DATABASE Statement===
The DROP DATABASE statement is used to delete a database.
DROP DATABASE database_name
===The TRUNCATE TABLE Statement===
What if we only want to delete the data inside the table, and not the table itself?
Then, use the TRUNCATE TABLE statement:
TRUNCATE TABLE table_name
#### DROP with NOT NULL ###
NA
#### DROP with UNIQUE ###
To drop a UNIQUE constraint, use the following SQL:
===MySQL:===
ALTER TABLE Persons
DROP INDEX uc_PersonID
==SQL Server / Oracle / MS Access:===
ALTER TABLE Persons
DROP CONSTRAINT uc_PersonID
#### DROP with PRIMARY KEY ###
To drop a PRIMARY KEY constraint, use the following SQL:
===MySQL:===
ALTER TABLE Persons
DROP PRIMARY KEY
===SQL Server / Oracle / MS Access:===
ALTER TABLE Persons
DROP CONSTRAINT pk_PersonID
#### DROP with FOREIGN KEY ###
To drop a FOREIGN KEY constraint, use the following SQL:
===MySQL:===
ALTER TABLE Orders
DROP FOREIGN KEY fk_PerOrders
===SQL Server / Oracle / MS Access:===
ALTER TABLE Orders
DROP CONSTRAINT fk_PerOrders
#### DROP with CHECK ###
< NOTE WHEN I MADE THIS THE MySQL was at the bottom and the SQL SERVER/ORACLE/MS ACCESS were at the top, I moved the sections around to be consistent with the other DROP CONSTRAINTS >
To drop a CHECK constraint, use the following SQL:
===MySQL:===
ALTER TABLE Persons
DROP CHECK chk_Person
===SQL Server / Oracle / MS Access:===
ALTER TABLE Persons
DROP CONSTRAINT chk_Person
#### DROP with DEFAULT ###
To drop a DEFAULT constraint, use the following SQL:
===MySQL:===
ALTER TABLE Persons
ALTER City DROP DEFAULT
===SQL Server / Oracle / MS Access:===
ALTER TABLE Persons
ALTER COLUMN City DROP DEFAULT
#############################
INDEXES: Speeding up Queries:
#############################
The CREATE INDEX statement is used to create indexes in tables.
Indexes allow the database application to find data fast; without reading the whole table.
### Indexes ###
An index can be created in a table to find data more quickly and efficiently.
The users cannot see the indexes, they are just used to speed up searches/queries.
Note: Updating a table with indexes takes more time than updating a table without (because the indexes also need an update). So you should only create indexes on columns (and tables) that will be frequently searched against.
===SQL CREATE INDEX Syntax:===
Creates an index on a table. Duplicate values are allowed:
CREATE INDEX index_name
ON table_name (column_name)
===SQL CREATE UNIQUE INDEX Syntax:===
Creates a unique index on a table. Duplicate values are not allowed:
CREATE UNIQUE INDEX index_name
ON table_name (column_name)
Note: The syntax for creating indexes varies amongst different databases. Therefore: Check the syntax for creating indexes in your database.
### CREATE INDEX Example ###
The SQL statement below creates an index named “PIndex” on the “LastName” column in the “Persons” table:
CREATE INDEX PIndex
ON Persons (LastName)
If you want to create an index on a combination of columns, you can list the column names within the parentheses, separated by commas:
CREATE INDEX PIndex
ON Persons (LastName, FirstName)
##########
FUNCTIONS:
##########
SQL has many built-in functions for performing calculations on data.
### SQL Aggregate Functions ###
SQL aggregate functions return a single value, calculated from values in a column.
Useful aggregate functions:
AVG() – Returns the average value
COUNT() – Returns the number of rows
FIRST() – Returns the first value
LAST() – Returns the last value
MAX() – Returns the largest value
MIN() – Returns the smallest value
SUM() – Returns the sum
###SQL Scalar functions ###
They change the way it looks in a way
SQL scalar functions return a single value, based on the input value.
Useful scalar functions:
UCASE() – Converts a field to upper case
LCASE() – Converts a field to lower case
MID() – Extract characters from a text field
LEN() – Returns the length of a text field
ROUND() – Rounds a numeric field to the number of decimals specified
NOW() – Returns the current system date and time
FORMAT() – Formats how a field is to be displayed
Tip: The aggregate functions and the scalar functions will be explained in details in the next chapters.
EXAMPLES OF SOME OF THE FUNCTIONS
#################################
### The AVG() Function ###
The AVG() function returns the average value of a numeric column.
SQL AVG() Syntax
SELECT AVG(column_name) FROM table_name
===SQL AVG() Example===
We have the following “Orders” table:
O_Id OrderDate OrderPrice Customer
1 2008/11/12 1000 Hansen
2 2008/10/23 1600 Nilsen
3 2008/09/02 700 Hansen
4 2008/09/03 300 Hansen
5 2008/08/30 2000 Jensen
6 2008/10/04 100 Nilsen
Now we want to find the average value of the “OrderPrice” fields.
We use the following SQL statement:
SELECT AVG(OrderPrice) AS OrderAverage FROM Orders
The result-set will look like this:
OrderAverage
950
Now we want to find the customers that have an OrderPrice value higher than the average OrderPrice value.
We use the following SQL statement:
SELECT Customer FROM Orders
WHERE OrderPrice>(SELECT AVG(OrderPrice) FROM Orders)
The result-set will look like this:
Customer
Hansen
Nilsen
Jensen
### SQL COUNT() Function ###
===SQL COUNT(column_name) Syntax===
The COUNT(column_name) function returns the number of values (NULL values will not be counted) of the specified column:
SELECT COUNT(column_name) FROM table_name
===SQL COUNT(*) Syntax===
The COUNT(*) function returns the number of records in a table:
SELECT COUNT(*) FROM table_name
SQL COUNT(DISTINCT column_name) Syntax
The COUNT(DISTINCT column_name) function returns the number of distinct values of the specified column:
SELECT COUNT(DISTINCT column_name) FROM table_name
Note: COUNT(DISTINCT) works with ORACLE and Microsoft SQL Server, but not with Microsoft Access.
SQL COUNT(column_name) Example
We have the following “Orders” table:
O_Id OrderDate OrderPrice Customer
1 2008/11/12 1000 Hansen
2 2008/10/23 1600 Nilsen
3 2008/09/02 700 Hansen
4 2008/09/03 300 Hansen
5 2008/08/30 2000 Jensen
6 2008/10/04 100 Nilsen
Now we want to count the number of orders from “Customer Nilsen”.
We use the following SQL statement:
SELECT COUNT(Customer) AS CustomerNilsen FROM Orders
WHERE Customer=’Nilsen’
The result of the SQL statement above will be 2, because the customer Nilsen has made 2 orders in total:
CustomerNilsen
2
===SQL COUNT(*) Example===
If we omit the WHERE clause, like this:
SELECT COUNT(*) AS NumberOfOrders FROM Orders
The result-set will look like this:
NumberOfOrders
6
which is the total number of rows in the table.
SQL COUNT(DISTINCT column_name) Example
Now we want to count the number of unique customers in the “Orders” table.
We use the following SQL statement:
SELECT COUNT(DISTINCT Customer) AS NumberOfCustomers FROM Orders
The result-set will look like this:
NumberOfCustomers
3
which is the number of unique customers (Hansen, Nilsen, and Jensen) in the “Orders” table.
## The UCASE() Function ###
The UCASE() function converts the value of a field to uppercase.
SQL UCASE() Syntax
SELECT UCASE(column_name) FROM table_name
===Syntax for SQL Server===
SELECT UPPER(column_name) FROM table_name
===SQL UCASE() Example===
We have the following “Persons” table:
P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger
Now we want to select the content of the “LastName” and “FirstName” columns above, and convert the “LastName” column to uppercase.
We use the following SELECT statement:
SELECT UCASE(LastName) as LastName,FirstName FROM Persons
The result-set will look like this:
LastName FirstName
HANSEN Ola
SVENDSON Tove
PETTERSEN Kari
### The ROUND() Function ###
The ROUND() function is used to round a numeric field to the number of decimals specified.
===SQL ROUND() Syntax===
SELECT ROUND(column_name,decimals) FROM table_name
Parameter Description
column_name Required. The field to round.
decimals Required. Specifies the number of decimals to be returned.
SQL ROUND() Example
We have the following “Products” table:
Prod_Id ProductName Unit UnitPrice
1 Jarlsberg 1000 g 10.45
2 Mascarpone 1000 g 32.56
3 Gorgonzola 1000 g 15.67
Now we want to display the product name and the price rounded to the nearest integer.
We use the following SELECT statement:
SELECT ProductName, ROUND(UnitPrice,0) as UnitPrice FROM Products
The result-set will look like this:
ProductName UnitPrice
Jarlsberg 10
Mascarpone 33
Gorgonzola 16
### The NOW() Function ###
The NOW() function returns the current system date and time.
===SQL NOW() Syntax===
SELECT NOW() FROM table_name
===SQL NOW() Example===
We have the following “Products” table:
Prod_Id ProductName Unit UnitPrice
1 Jarlsberg 1000 g 10.45
2 Mascarpone 1000 g 32.56
3 Gorgonzola 1000 g 15.67
Now we want to display the products and prices per today’s date.
We use the following SELECT statement:
SELECT ProductName, UnitPrice, Now() as PerDate FROM Products
The result-set will look like this:
ProductName UnitPrice PerDate
Jarlsberg 10.45 10/7/2008 11:25:02 AM
Mascarpone 32.56 10/7/2008 11:25:02 AM
Gorgonzola 15.67 10/7/2008 11:25:02 AM
### The FORMAT() Function ###
The FORMAT() function is used to format how a field is to be displayed.
===SQL FORMAT() Syntax===
SELECT FORMAT(column_name,format) FROM table_name
Parameter Description
column_name Required. The field to be formatted.
format Required. Specifies the format.
===SQL FORMAT() Example===
We have the following “Products” table:
Prod_Id ProductName Unit UnitPrice
1 Jarlsberg 1000 g 10.45
2 Mascarpone 1000 g 32.56
3 Gorgonzola 1000 g 15.67
Now we want to display the products and prices per today’s date (with today’s date displayed in the following format “YYYY-MM-DD”).
We use the following SELECT statement:
SELECT ProductName, UnitPrice, FORMAT(Now(),’YYYY-MM-DD’) as PerDate
FROM Products
The result-set will look like this:
ProductName UnitPrice PerDate
Jarlsberg 10.45 2008-10-07
Mascarpone 32.56 2008-10-07
Gorgonzola 15.67 2008-10-07
###################################
SQL Quick Reference From W3Schools:
###################################
### AND / OR ###
SELECT column_name(s)
FROM table_name
WHERE condition
AND|OR condition
### ALTER TABLE ###
ALTER TABLE table_name
ADD column_name datatype
or
ALTER TABLE table_name
DROP COLUMN column_name
### AS (alias) ###
SELECT column_name AS column_alias
FROM table_name
or
SELECT column_name
FROM table_name AS table_alias
### BETWEEN ###
SELECT column_name(s)
FROM table_name
WHERE column_name
BETWEEN value1 AND value2
### CREATE DATABASE ###
CREATE DATABASE database_name
### CREATE TABLE ###
CREATE TABLE table_name
(
column_name1 data_type,
column_name2 data_type,
column_name2 data_type,
…
)
### CREATE INDEX ###
CREATE INDEX index_name
ON table_name (column_name)
or
CREATE UNIQUE INDEX index_name
ON table_name (column_name)
### CREATE VIEW ###
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
### DELETE ###
DELETE FROM table_name
WHERE some_column=some_value
or
DELETE FROM table_name
(Note: Deletes the entire table!!)
DELETE * FROM table_name
(Note: Deletes the entire table!!)
### DROP DATABASE ###
DROP DATABASE database_name
### DROP INDEX ###
DROP INDEX table_name.index_name (SQL Server)
DROP INDEX index_name ON table_name (MS Access)
DROP INDEX index_name (DB2/Oracle)
ALTER TABLE table_name
DROP INDEX index_name (MySQL)
### DROP TABLE ###
DROP TABLE table_name
### GROUP BY ###
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
### HAVING ###
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value
### IN ###
SELECT column_name(s)
FROM table_name
WHERE column_name
IN (value1,value2,..)
### INSERT INTO ###
INSERT INTO table_name
VALUES (value1, value2, value3,….)
or
INSERT INTO table_name
(column1, column2, column3,…)
VALUES (value1, value2, value3,….)
### INNER JOIN ###
SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name
### LEFT JOIN ###
SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name
### RIGHT JOIN ###
SELECT column_name(s)
FROM table_name1
RIGHT JOIN table_name2
ON table_name1.column_name=table_name2.column_name
### FULL JOIN ###
SELECT column_name(s)
FROM table_name1
FULL JOIN table_name2
ON table_name1.column_name=table_name2.column_name
### LIKE ###
SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern
### ORDER BY ###
SELECT column_name(s)
FROM table_name
ORDER BY column_name [ASC|DESC]
### SELECT ###
SELECT column_name(s)
FROM table_name
### SELECT * ###
SELECT *
FROM table_name
### SELECT DISTINCT ###
SELECT DISTINCT column_name(s)
FROM table_name
### SELECT INTO ###
SELECT *
INTO new_table_name [IN externaldatabase]
FROM old_table_name
or
SELECT column_name(s)
INTO new_table_name [IN externaldatabase]
FROM old_table_name
### SELECT TOP ###
SELECT TOP number|percent column_name(s)
FROM table_name
### TRUNCATE TABLE ###
TRUNCATE TABLE table_name
### UNION ###
SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2
### UNION ALL ###
SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2
### UPDATE ###
UPDATE table_name
SET column1=value, column2=value,…
WHERE some_column=some_value
### WHERE ###
SELECT column_name(s)
FROM table_name
WHERE column_name operator value