Using DELETE to delete rows from an SQL Database - Tutorial

Using DELETE to remove a row from your SQL Database is simple. You basically tell the delete command the name of the table, and you help it find which row to delete. Let us delete "John Smith" from our database, because he is planning to move out of the country and no longer will use our website.

DELETE FROM Persons WHERE Name='John Smith', Level='Level 3'

And that will delete any John Smith that we have in our database, so long as John also has a Level 3. Be very careful to specify your full deletion requirements. You don't want to accidentally delete the wrong people, or an entire table's worth of information! 'DELETE FROM Persons' or 'DELETE * FROM Persons' would both, in most cases delete all the information in the table. It still leaves the table structure, so it is useful if you need to start with fresh people and the same types of information.

UPDATE information in a database - SQL Tutorial

Updating information in a database is simple also. Last time you can see that we left off Jack's age. We found out it was his 23rd birthday last month, so now we can add this to the database. We'll do that simply by using an UPDATE statement to update his information.

UPDATE Persons SET Age=23 WHERE Name='Jack Smith'

If you're scared of there being two Jack Smiths, you might also add , Level='Level 8 to that statement. If you have two Level 8 Jack Smiths, then you really aught to learn a little about creating ids, so that you can only update the correct Jack.

INSERT information into your database with SQL

Inserting information into your database is also very simple. Just use the INSERT keyword, along with the data values that you want to put into your database. This creates a new row, or 'record', of information.

INSERT INTO Persons VALUES ('Jerry Smith', '32', 'Level 21')

This creates a new row which includes Jerry Smith, his age, and his level. You can also leave information empty in a new row, by telling SQL which fields you are adding. Do this with a slightly modifying INSERT INTO statement.

INSERT INTO Persons (Name, Level) VALUES ('Jack Smith', 'Level 8')

Notice that we don't know how old Jack is? This simply leaves Jack's age empty in the database. Sometimes, your database may not allow an empty (also called a NULL or NIL) value, so you might get an error. In that case, you might need to set something like 0 for a default age.

Searching through database with SELECT... WHERE

SELECT Name FROM Persons WHERE Age < 18

Let's say your website is now only for people 18 and older. You need to find everyone in your database that is under 18, so you can remove them or something. First, you use a select statement to return the information of all people under 18. How? With a WHERE age < 18. This returns anybody who's age is less than 18. You can also find other things, for example if you just want people named "John Smith" do this:

SELECT Name,Age,Level FROM Persons WHERE Name='John Smith'

Data Definition Language (DDL) - SQL Databases

The DDL part of SQL means that SQL creates databases and puts parameters on information. You don't want to have information such as "John Smith" "19" and "Level 5" floating around without definitions - your programs wouldn't know what to do with the information, and they wouldn't know what's what! DDL involves creating, modifying, or deleting tables.

CREATE TABLE - creates a new table
ALTER TABLE - changes things in an old table
DROP TABLE - deletes an entire table (and the data) from the database
CREATE INDEX - creates a search key (index) which you can use in SELECT and other statements
DROP INDEX - removes a search key (index)

Data Manipulation Language (DML) - Part of SQL

SQL allows you to manipulate data, which is really important. Let's say John's rank goes down and we need to change it to "Level 3" instead of "Level 5". We would use the UPDATE command to change his information.

SELECT - Retrieves information from the database for you.
UPDATE - Updates information in the database for you.
DELETE - Deletes data specified in the database.
INSERT INTO - inserts a new row of data in the database.

Querying a database with SELECT

The thing done most often to databases is querying. This means asking the database for a row of information, called a 'record'. This contains one joined set of values, for example "John Smith" "19" "Level 5" would correspond to values such as "Name", "Age", "Rank" in the database.

SELECT Name FROM Persons

That select query would then return a 'result set' of all names within the database, such as "John Smith" "Jake Prower" and "Mike Huggabee".

SELECT Name,Age FROM Persons

If you need to get more information, such as Name and Age, then do something like the above, which will return a couple sets of data (arrays, you would probably call them), one set with the Names, and the other with the Ages (but they will be linked in those sets).

SELECT DISTINCT Name FROM Persons

If you use the DISTINCT keyword, sql will only return a set of unique names. You won't get two "John Smith"s.

As I Study SQL

This place will be a resource of all the material I've found while learning SQL. I've studied SQL in the past, but now I am putting my knowledge all in one easy-to-access place. SQL is a standardized language for accessing and manipulating computer databases. Structured Query Language is a programming language that allows you to access computer databases, execute queries, and modify data within the database.

Major SQL keywords are SELECT, UPDATE, DELETE, INSERT, WHERE, and a few others. Most database structures (MySQL, Oracle, etc), though, have their own proprietary extensions.