SQL Tutorial

12 Sep

SQL Tutorial

Resource: www.dirhost.com
Author: Web-O-Gramming
Date: 22 April 2000
 
A SQL tutorial for people using Cold Fusion or ASP. 
Covers basic functionality such as SELECT, DELETE, 
INSERT, LIKE, UPDATE, WHERE

Intro
This Tutorial was made to give you some working knowledge of SQL.

It does not include advanced topics like stored procedures,etc.

If you want to use a database on you site then you MUST know SQL.

SQL stands for structured query language. It is pronounced sequel

(like a movie sequel.) SQL allows you to manipulate databases

through the internet. This tutorial will show you how to retrieve,

modify, delete and add new records to a database.

A basic sql statement is formed like this:

DIRECTIVE fields [WHERE ((something op something)
   [conj] (somthing op something))];
text inside [ brackets ] is optional.
Directive - (see below)
fields - the fields to be used. If you want to use all, put an asterisk * 
WHERE - see below
op - operator:  =   !=
conj - conjunction for having two or more criteria:  AND,OR,NOT

Directives
A directive is a SQL command that tells the parser to do something.

The directives that will be discussed are SELECT, DELETE,

INSERT, UPDATE, WHERE, FROM, IS, LIKE.

DIRECTIVE

DESCRIPTION

SELECT

Select records from the database

DELETE

deletes records

INSERT INTO VALUES

insert a new record

UPDATE SET

modifies a record

WHERE

do . . . to records matching criteria

FROM

in what table in the database to perform the directive

LIKE

criteria for WHERE that does something to all records where fieldname is LIKE %foobar% meananing do somethink to a record where the data in FIELDNAME being the name of the field is similar or contains foobar.

IS

basically the same thing as =.


WHERE
The WHERE directive performs SELECT,DELETE,INSERT,

UPDATE on all records that match the specifies criteria. it comes after

SELECT, DELETE, INSERT, UPDATE and before

Retrieving Records

SELECT * FROM table
To retrieve records we need to use the SELECT directive.

Then you need to specify the fields to retrieve inside brackets [ ]

or you can use an asterisk * to select all fields. The use the FROM

directive followed by the table name in which to get the records from.

As an example, I will take all fields from a table named examples.

SELECT * FROM examples;

Deleting Records
To delete a record, you need to use the DELETE directive followed

my the fields to delete followed by FROM and the table name to delete

from plus WHERE and the criteria. As an example, I’ll delete all records

from a table called phonebook where the age of the person is under 18.

DELETE * FROM phonebook WHERE ((age < 18));

Adding New Records
To add a new record, you use the INSERT INTO directive followed

by the table name and then the fields to add data inside ( parenthesis )

and seperated by commas. Then you type VALUES followd by the data

that should go into the fields inside ( parenthesis ) and seperated by commas.

As an example i will place some foney data into a table called phonebook.

INSERT INTO phonebook (name,age,number) 
       VALUES ("John Doe","32","610-555-1212);

Modifying Records
When modifying records, the UPDATE SET directive is used.

First type UPDATE, followed by the table name in which the records

to be updated exist. Then type SET followed by each fieldname & “=” &

the data to be put inside the field. Then you probably want to use WHERE

to only update certain records and not every one..

For multiple field modifications seperate each by a comma.

As an exampl, I will update phonebook with some new data.

UPDATE phonebook SET name=''Jane Doe'',age=''17'',phone=''484-555-1212'';

LIKE
The LIKE directive is very usefull when doing searches.

Say i want to find the word foobar in a field named keywords in table whatever.

I would do it like this:

SELECT * FROM whatever WHERE keywords LIKE ''%foobar%'';

The percent % is used to represent any possible character,number.

So this would select all records that have foobar in their keywords field.

IS
The IS directive is basically the same thing as equal to =.

You use it in place of LIKE if you only want to find the exact specified text.

Example:

SELECT * FROM whatever WHERE ID IS 10;


Notes!

· You are not allowed to put quotes or apostrophes in SQL

when using it with Cold Fusion Server.

· In ASP you must use double apostrophes when wrapping text.

Quotes and single apostrophes are not allowed

— oOo —

About these ads

One Response to “SQL Tutorial”

  1. fahmi November 29, 2007 at 6:05 am #

    May u give me a complete article about asp. If u have indonesian and english version, I want all.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 65 other followers

%d bloggers like this: