Databases and SQL - Adding Information To A Database
(Page 2 of 5 )
One interacts with a database by making queries to the database server. A query is a request to the server to perform some specific action on one or more tables. Such actions could be creating a table, adding information to a table, retrieving information from a table and even dropping tables or deleting entire databases!
The Structured Query Language (SQL) is a set of commands used to communicate with a database. Its syntax is simple and its queries read very much like natural language.
Create
The CREATE command can be used to create new databases and tables. To create a database, issue the CREATE command followed by DATABASE and the database name. SQL statements terminate with a semi-colon.
CREATE DATABASE myDatabase;
If there is more than one database available to you, you can select one to work with by issuing the USE command followed by the name of the desired database.
USE myDatabase;
To create a table, issue the CREATE command followed by TABLE, the table name, and then in parenthesis a list of field/column names and their corresponding data types. A comma separates each field and data type pair.
CREATE TABLE addresses (last_name VARCHAR(50), first_name VARCHAR(50), address VARCHAR(50), city VARCHAR(50), state CHAR(2), zip_code CHAR(5));
The first example creates a table named addresses that stores a person’s name and address. The second example creates a table named employees that stores an employee's identification number, name, hire date and any special comments.
Different database servers support various types of data such as integers, floating point decimals, dates and binary files. For a complete list of data types available for a particular server, you will need to refer to each server's documentation. There are also many resources available on the Internet.
Insert
The INSERT command can be used to add records to a table. To add a record, issue the INSERT command followed by INTO, the desired table, VALUES and then the corresponding values surrounded by parentheses. Commas should separate the values, strings should be quoted and the entire query statement is terminated with a semi-colon.
INSERT INTO addresses VALUES ("Smith", "Harold", "321 Elm St.", "Portsmouth", "RI", "02871");
INSERT INTO employees VALUES (355, "Harold", "Smith", "2003-03-30", NULL);
The first example adds Harold's information to the addresses table. The second example adds Harold's information to the employees table. NULL is a special keyword used to represent an empty field, so in this case there are no special comments to record.
