View and vote on the article here: An Introduction to SQL
An Introduction to SQL| Category | | | Summary | | | Body | An Introduction to SQL
By Darkwater685
Structured Query Language, or SQL (pronounced as the series of the three letters S-Q-L) is a standard language for database management and manipulation created by the American National Standards Institute (ANSI). Numerous database systems including Oracle and Microsoft SQL Server use SQL along with other, proprietary languages
SQL essentially is a set of command for manipulation of tables in a database. First of all you need to understand the setup of tables to learn SQL. A table is made up of a table name and rows and columns of data. Below is a four-column table from an imaginary CD database:
CD_dbase <--Table Name
Singer Name goodness CD <--column names
Cash Johnny Ballad of Ira Hayes double plus 0025 <--first row of data
Cash Johnny Man in Black double plus 0025 <--second data row
Cash Johnny Folsom Prison very good 0025
Keith Toby Beer for my horses double plus 0020
Wagner Ric Ride of the Valk double plus 0036
Creating Tables
To create a table, the create table command is used. This command has the following syntax:
create table tablename
(column1 data type [constraint],
column2 data type [constraint],
column3 data type [constraint]);
All SQL statements must end in a ; if the semi-colon is not present, it is likely that all hell will break loose, or at least your command will utterly befuddle the computer. Another must is to include all strings (i.e. several letters or letters and numbers) with single quotation marks ('). This does NOT apply to column or table names, however. SQL is also very forgiving about blank space. Use white area like you would in HTML in order to make the code neater and easier to read.
The first line of the above code is self-explanatory: it creates a table with whatever name you give it. The table name, as with column names, must begin with a letter, but can be followed by letters, numbers or underscores (no spaces). Titles have a maximum length of 30 characters. Obviously, names of SQL commands (like create) should not be used.
The second line above is where the data structure is actually created. Always start your column listing with an open parenthesis, separate column names with commas, and end the listing with a close parenthesis. (of course end the statement with a semi-colon too!) Besides creating columns with various names, you must specify the type of data that the column will contain and how long that data can be. The following data types are used:
char(size) A character string with the fixed size specified in parenthesis
varchar(size) Variable-length character string with a maximum size specified in parenthesis
number(size) Number value with a maximum number of columns specified in parenthesis (i.e. varchar(2) will allow 1,2,02,99 to be entered)
date Date values accepted
number(size,d) Number value with a maximum number of digits d to the right of the decimal
The optional constraints used in columns further restrict what values a column can contain. There are numerous constraints available, but the three most common are:
unique: all entries in a column must have unique values
not null: the column can not be left empty
primary key: provides a unique identification for each record (or row)
In order to create the table called CD_dbase, the following would be used:
Create table CD_dbase
(Singer varchar(15),
Name varchar(25),
goodness varchar(15),
CD number(4));
Now, isn't that cool? But what good is an empty table? Let us venture onward and see how to add stuff to this wonderful table just created.
Inserting into a table
It should come as no surprise that to insert rows of data into a table, the insert command. The syntax of insert is:
insert into tablename
(first_column,...last_column)
values (firstvalue,...last_value);
The first line consists of commands telling the computer to insert the data that follows into the specified table. Just like the create table command, the first line is followed by an open parenthesis and the statement is also ended with );. The column names into which the data is to be inserted come next and are separated by commas. When you are finished listing the columns for the data, this is enclosed with a close parenthesis. The data values come after the term values the data itself separated by comas and enclosed in parentheses.
The code to insert Cash Johnny Folsom Prison very good 0025 into table CD_dbase follows:
insert into CD_dbase
(Singer, Name, goodness, CD)
values (Cash Johnny','Folsom Prison','very good',0025);
Selecting and Display of Information from Tables.
To select data form a table using SQL, one uses the select command. The Select command's syntax is:
select column1 [,column2, column 3] from table name
[where condition];
Note: when you see commands in [], they are optional commands
The Select command, like the rest of SQL is very rational. You are telling the computer to select data from whichever column you want from what table you are using. If you want to specify what info to retrieve, you supply conditions.
Conditions allow you to return limited sets of information. The basic SQL conditions are:
= Equal to ex: where city = Athens'
> Greater than ex: where pay > 1000
< Less than ex: where pay < 1000
>= Greater than or equal ex: where pay >= 1000
<= Less than or equal ex: where pay <= 1000
<> Not equal to ex: where pay <> 1000
LIKE: the like (in all caps) allows selection of data rows that are like, but not exactly the value you call for. Ex: city LIKE %A' will return all rows containing cities starting with A. The % is the wild card character (often used with like as in %bubba%' which would return: billy bubba', bubba', bubba joe', etc)
The * can be used in place of column names or other parameters to say, Select every thing. To print out the entire table CD_dbase, for example one would use:
Select * from CD_dbase;
Example of recalling select data from a table using SQL:
Select * from CD_dbase
Where singer = Cash, Johnny';
Output would be:
Singer Name goodness CD
Cash Johnny Ballad of Ira Hayes double-plus 0025
Cash Johnny Man in Black double-plus 0025
Cash Johnny Ragged Old Flag very good 0025
Cash Johnny Folsom Prison very good 0025
Updating and Deleting Records
The conclusion to this tutorial uses some fairly complex programmer thought to update records. Once you master updating and the use of the where statement, the delete statement will seem easy because it is very similar.
To update a database, you use the update statement. First look at the syntax and then I will explain what is going on.
update tablename
set colum_nname = new_value
where column_name OPERATOR value;
Like the other SQL commands, the first line tells what table is going to get updated. The second line simply tells what to do if the conditions listed in the 3rd line are met. For example set Singer = 'Willie and Toby' would set all entries in the name column that met certain requirements (such as where Singer= Keith Toby') to the value Willie and Toby. As many columns can be change with one statement. Each new column is separated from the others by a comma. Also and/or statements can be added to the when statement to add additional requirements for the update. The OPERATOR can be any of the conditions used in selecting data (=,>,<, and combinations). You may also make arithmetic statements in the values to change the data to (i.e. set salary = salary+3000 if all your employees just got a $3K raise)
For our example we will correct a problem in our database. The song Beer for My Horses was sung by both Willie Nelson and Toby Keith, but the database gives only Toby credit for the song. No one in their right mind could ever forget such a great man as Willie Nelson, so lets add him to the database.
update CD_dbase
set Singer = 'Willy and Toby'
where Singer = 'Keith Toby';
Deleting records is very similar to updating, the syntax of the command is:
delete from tablename
where column1 OPERATOR value
[and/or column2 OPERATOR value];
The values for the statement are the same as the update command. Again the logical style of SQL is obvious and the code does just what it looks like it will. The Delete from statement is useful for deleting a row or a couple rows. There is a very easy way to delete the entire table. This is called a drop. The syntax is so simple it needs no explination:
drop table tablename
And boom! Just like that your table is history. Like your table, this tutorial is finished as well. I hope that this tutorial has given you a good introduction to SQL. For further reading, I suggest http://sqlcourse.com/ and http://www.sqlcourse2.com/ (which is a more advanced). If you have no database or software to test your SQL on, sqlcourse.com has an on-line SQL interpreter, which can be used to practice your new skills on. The online interpreter at (http://sqlcourse.com/cgi-bin/interpreter.cgi)
I would like to thank the people at sqlcourse.com for their great tutorials that got me started in SQL. Also I would like to thank the great musicians that allowed me to keep my sanity while writing this tutorial.
Written by Darkwater685 (Feb 21 2004)
Member of CAU Knowledge-Bank Tutorial Writers |
|
This article was imported from the CyberArmy University site. (original author: Darkwater685)
There are no replies to this post yet.
|