ANSI SQL Tutorialby Jim Campbell
American National Standards Institute (ANSI) provides common standards for computer programming languages such as Structured Query Language (SQL). SQL has four standard commands: insert, update, delete and select. These four commands control the data placed in SQL tables. Developers can insert new data, update current data and remove records. Knowing some basic ANSI SQL statements helps you get started coding database functions.
Select data from your tables. The "select" command is the most commonly used statement, because it retrieves any data located in the database. The following is an example of an SQL select command:
select * from customer
The "*" sign tells the SQL engine to retrieve all records from the table. Replace "customer" with the name of your database. You can also filter records using the "where" clause. The following code is an example of retrieving the customer with the ID of 1:
select * from customer where ID=1
Insert records into your database. This command is used to add records to your tables. Each table column contains a piece of information for your database. For instance, the following code adds a customer to the "customer" table:
insert into customer (firstname, lastname) values ('Joe', 'Smith')
The "firstname" and "lastname" values are the column names in your database table. The "Joe" and "Smith" values are the actual data saved in the table.
Edit data in the table. The "update" statement changes the current value in your table with the newly defined values. The following code is an example of an update statement:
update customer set firstname='John' where customer_ID=1
In this command, the customer whose ID is "1" is updated to "John." If you do not add the "where" clause to the command, the entire database is changed and all records have a "firstname" value of "John." For this reason, it is critical to always use the "where" clause.
The "delete" SQL command is not used as often, because most administrators prefer to archive data instead of deleting it. However, some instances call for the delete statement, such as removing duplicate records from tables. The following command deletes records from the customer table:
delete from customer where lastname='Smith'
The statement above deletes all records where the customer's last name is "Smith." It's also important to use a "where" clause in this statement, because without it all records in the table are erased.
- photo_camera laptop with database record on 15.4" wide screen image by .shock from Fotolia.com