Sunday, 31 January 2016

SQL in ten minutes

Introduction

You've probably heard of SQL. What does it mean, you ask. Well, SQL (Structured Query Language) is a very handy tool for database programmers (including ASP programmers). Without SQL, it would be very difficult to perform many common tasks relating to databases. In this article, I will teach you the basics and the common uses of SQL.

What can it do for me?

SQL is capable of achieving many tasks. Those tasks include:
  • Querying a table. To query a table means we select only the records that we need, according to some criteria we set.
  • Updating a table. Using SQL we can update a group of records that meet certain conditions.
  • Deleting records. SQL allows us to delete records in a breeze, whether it's only one record or a group of records.
  • Adding new records. Well, in this area SQL doesn't help us so much, but it's good to know how to use it for inserting records too.
In the next few paragraphs, you'll learn how exactly these four tasks are accomplished. Let's start!

The SQL statement

A SQL statement is a couple of words that tell the database handler what we want to do. The basic structure of a common SQL statement is:
DIRECTIVE fields FROM/INTO table [WHERE (field OP string) 
                                           [conj] (field OP string)]
Explanation:
  • DIRECTIVE - A SQL directive is a word describing what you want to do.
  • fields - This is a list of the fields on which you are going to execute the SQL statement.
  • FROM/INTO - If you query the table, it's FROM. If you insert a new record, it's INTO.
  • table - On which table we are working.
  • [ ] - Text inside the brackets is optional.
  • conj - This is a logical operator, allows us to make more complex SQL statements.
  • OP - A SQL operator.
  • string - The string you are searching for.
Confusing? Not at all! I will explain each of these weird things in the next paragraphs, along with examples to make you understand it.

The directives

Well, as you can see in the SQL statement structure, every SQL statement must start with a directive. So lets start learning those directives!

SELECT

With the SELECT directive, you can make your queries. Here are some examples:
SELECT * from Workers
The simplest SQL statement. This will select ALL records from the "Workers" table. The asterisk (*) sign means all fields. It will select all records because we have not included a WHERE clause.
SELECT WorkerName from Workers
This will select only the WorkerName field from the table "Workers".
SELECT FirstName,Phone from Phonebook
This statement will select the FirstName and Phone fields of the "Phonebook" table.
OK, so this was really nice. But if I want to show only the people who have got a Mercedes? No problem at all! There are two operators that allows us to query and select only records which meet our criteria. These are IS and LIKE. IS means that we want to select only the records in which a field is exactly what we want. Example :
SELECT * from Phonebook WHERE Car IS 'Mercedes'
This statement will select only the people who own a Mercedes (two people). Note that we don't use quotes in the field and table name, only in the search string. I also used * to select all fields. Note the use of a single quote (') and not double quote("). This is because the SQL statement is a string itself, and strings in programming languages are delimited in double quotes. Another example:
SELECT Car from Phonebook WHERE City IS 'NY'
Here we'll get a table showing the cars of the people living in New York in our phonebook. Well I told you before about IS and LIKE. You know how to use IS already, so let's start using LIKE too. LIKEallows us to define criteria for search. To use LIKE, you will need to know the meaning of a wildcard. A wildcard tells the computer that any other character (or characters) can be placed instead. For example, if the wildcard is @, then saying something like A@ means we search for the names startingwith A. But if we say @A, then we mean names ending with A, because the A is after the wildcard. InSQL, the wildcard is not @, but %. Example for the usage of LIKE:
SELECT * from Phonebook WHERE Car LIKE 'M%'
This SQL will get us only the people who own a car brand beginning with "M". Note that if there was a car brand which is named "M" it will be selected too, because the % can be anything, even nothing - that means that there can be some characters after the M, but if there are no more chars its ok too.
SELECT * from Phonebook WHERE FirstName LIKE '%ou%'
This will select only the people who have a first name containing the sequence "ou".
SELECT * from Phonebook WHERE LastName LIKE '%'
This will simply select all records because % means everything.
SELECT * from Phonebook WHERE Car IS 'Mercedes' AND FirstName LIKE '%D%'
Here we used the conj you heard before. It will select for us, only the people who own a MercedesAND that their first name contains a D.
SELECT * from Workers WHERE ((Department IS 'Sales') OR (Wage > 3000))
Ok - Here we used parenthesis to make reading this statement easier. Also, we used the > operator to select only those workers who work in the "Sales" department OR those workers with a wage of 3000 and higher. You can also use the < and = operators (= is like IS).

DELETE

Well, you know how to query tables. Now to deleting some records. The DELETE directive works exactly the same as SELECT, only it deletes records instead of selecting them. Examples:
DELETE from Workers WHERE FirstName IS 'Moses'
The statement will delete all the workers who are named "Moses". Note that you don't have to write *because you can't delete only certain fields. When you delete a record, or records, all the fields must be deleted. You may, however, put an *, but its not obligatory.
DELETE from Phonebook WHERE City IS 'NY'
Are you nutz!? To delete all your friends from New York!?
DELETE from Phonebook WHERE ((Car IS 'Mercedes') OR (Car IS 'Jaguar'))
Yep, we all feel jealous sometimes for our friends having (very) expensive cars!

UPDATE

So you wanna update some records ha? You've come to the right place! Well, the UPDATE directive is a bit different from SELECT and DELETE directives. This is how you write an UPDATE statement:
UPDATE table SET fields=values
  • table - The table name which we are updating records.
  • fields - The field name, and
  • values - The new values for the fields.
Example:
UPDATE Phonebook SET Lastname='Doe'
This will update all the records in your phonebook, so the last name of all your friends will be "Doe".
UPDATE Phonebook SET Car='None' WHERE 
         ((Car IS 'Ferrari') OR (Car IS 'Lambourghini'))
All your friends with race cars will now have NO car at all! You can also update several fields at once:
UPDATE Phonebook SET FirstName='Bill',LastName='Gates'
This will change all your friends to Bill Gates. It won't, however, make them acquire his budget.

INSERT

This is our last directive. INSERT will help you to add records to your tables. This is how we use it:
INSERT INTO table (fields) VALUES(values)
  • table - What table we are working on
  • fields - The list of fields that we want to give initial values, separated by commas.
  • values - The initial values we want to assign for the fields in the list.
Examples:
INSERT INTO Phonebook (FirstName,LastName) VALUES ('Bill','Gates')
Here we add a new record, and fill in the FirstName and LastName fields of the new record with the values "Bill" and "Gates". The other fields will be empty for now.
INSERT INTO Phonebook (FirstName,Phone) VALUES ('Tim','717-233-344')
I think this explains itself.

To sum up

So, what have we learned in this article? If you've read the whole thing, you now know how to use SQLto:
  • Query your tables.
  • Delete some records from your tables.
  • Update your records.
  • Insert new records.
SQL can do more complicated tasks, but this will give you a good start with SQL. I hope you find this useful!
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)