Intro
I'm not the best at this, but I thought we were lacking a SQL tutorial...
Test table
My Little Test Table called "test" for this great exercise contains the following
FirstName LastName EyeColor
Paul Esson Brown
John Smith Blue
John Howard Green
Kim Beazley Gray
SELECT
This selects data from a table in SQL. The basic syntax of select is as follows:
SELECT field FROM table
So
SELECT * FROM testwill produce the table an output of everything in the test table
SELECT FirstName FROM testWill Produce
Paul
John
John
Kim
As you can clearly see, this part is quite easy.
WHERE
If we don't want to know about two johns we can try
SELECT DISTINCT FirstName FROM testThis Will Produce
Paul
John
Kim
John Smith is the John Selected, since John Howard is after him in the database.
SELECT LastName FROM test WHERE FirstName = "John"Will Produce
Smith
Howard
WHERE can be used with lots of different logical operators, such as
= is equal to
<> or != is not equal to
> greater than
< less than
>= less than or equal to
<= ....
IN A list follows this, if its in the list then its used
such as
SELECT FirstName FROM test WHERE LastName IN ("Esson", "Smith")NOT IN ........
BETWEEN Value is used if its between two values
SELECT ItemName FROM stockTable WHERE Quantity BETWEEN 100 AND 50NOT BETWEEN ......
IS NULL Where the Field is Null
IS NOT NULL ......
LIKE Where the field is like an expression _ represents
one char % represents multiple
SELECT FirstName FROM test WHERE FirstName LIKE "P_ul"This will output Paul
NOT LIKE ........
Now lets order our table by FirstName
SELECT * FROM test ORDER BY FirstNameand we get our table but ordered by FirstName
INSERT
INSERT INTO table (field1, field2, etc....) VALUES (value1, value2, etc...)That was quick!
AND
Yet again I do not want to look at Howard another easy way of forgeting about him is to use and
SELECT FirstName,LastName FROM test WHERE FirstName = "John" AND LastName != "Howard"This Produces John Smith
Now ends Pauls basic SQL made hard tutorial...
