Intro to Databases - SQL - Structured Query Language
(Page 3 of 8 )
Structured Query Language (better known as SQL) is the database computer language. The American National Standards Institute (ANSI) has released a standard for it. Each database company has varying amounts of compliance with the standard but generally speaking they all follow the standards for the basics. That however should not stop you from verifying the syntax of the following SQL against your databases documentation.
The basic SQL commands that I will quickly go over is CREATE, INSERT, SELECT, UPDATE, and DELETE. Coding examples of each will be provided here and when we discuss using the two common options in PHP for working with databases.
CREATE
The CREATE command is used to create a table. The command's format is basically:
Usually the CREATE command has to be customized to the database that is running it. So look up your databases documentation and search for CREATE. There you will find the exact sytax used by your database and possibly a link to the allowed column types.
MySQL Example:
CREATE TABLE Customer ( CUST_ID INTEGER NOT NULL UNIQUE AUTO_INCREMENT, CUST_F_NAME VARCHAR(15) NOT NULL, CUST_L_NAME VARCHAR(30) NOT NULL, CUST_B_DATE DATE, PRIMARY KEY(CUST_ID));
INSERT
The INSERT command is used to insert a record into a table in the database. That sounds simple enough, right. The basic structure of the command is:
Now there are a couple of things you should know about the insert statement.
1. You do not have to list the column names in any particular order but the information that is to be inserted into those columns must be in the same position of the clause.
2. All the table's column names do not have to be listed in the INSERT statement, just the ones that have data to be inserted. Oh and remember that a column that is listed as NOT NULL must have a value to be inserted.
3. The listing of the column names is considered optional by some databases. I would recommend leaving it in. At the very least it allows the code to be self documenting. Also if it is left out then it is normally assumed that all the columns will have data input into them so you will have to give a value for every column.
INSERT INTO Customer (CUST_F_NAME, CUST_ID, CUST_L_NAME) VALUES ("John", 1, "Smith");
SELECT
The SELECT command is used to retrieve information from the database. This in many ways is the work horse of SQL. The basic structure of the command is:
SELECT COLUMN1_NAME, COLUMN2_NAME FROM Table;
Again this sounds pretty easy, right. The statement above will retrieve all of the values in the given 2 columns of the table. But what if you only want the information contained in those 2 columns from 1 record? How would you do that?
The SELECT statement has various modifiers for it (some will be used again later in other commands) that the databases may/may not support in the given ANSI syntax. The basic modifiers that I will cover include WHERE, ORDER BY, and GROUP BY. There are many more modifiers available so check your database documentation to learn more about them for your database. All the various modifiers may be stacked together in various combinations to get exactly the results you want.
A WHERE clause/modifier is used to closely specify what results you want from a SELECT statement. Translation - It allows you more control over which records are to be returned from the SQL query. With it you can put conditions on which records to return. Remember you can have multiple conditions. These conditions are to be joined together with the logical AND (both conditions must be true for the whole thing to be true) and the logical OR (only one condition must be true for the whole thing to be true). With multiple conditions please use parenthesis to properly group the conditions.
SELECT F_NAME, L_NAME FROM Employee WHERE JOB = 'PROGRAMMER'; SELECT SALARY, F_NAME, L_NAME FROM Employee WHERE SALARY =>100000; SELECT F_NAME, L_NAME FROM Employee WHERE ((DEPT <> 'MARKETING') AND (SALARY =>100000));
The ORDER BY clause/modifier is used to specify the order the results are returned in. They can be returned in ASCENDING or DESCENDING alphabetic or numerical order as appropriate. This is what was hinted at in the "Concepts" area. Example:
SELECT SALARY, F_NAME, L_NAME FROM Employee WHERE SALARY >100000 ORDER BY SALARY ASC; SELECT F_NAME, L_NAME FROM Employee WHERE JOB = 'PROGRAMMER' ORDER BY L_NAME DESC;
The GROUP BY clause/modifier is used to group the results by a given field. So all the programmers will be together and all the network engineers will be together and all the...
NOTE: When using GROUP BY and ORDER BY together the GROUP BY statement comes first.
SELECT SALARY, F_NAME, L_NAME FROM Employee WHERE SALARY >100000 GROUP BY SALARY ORDER BY L_NAME ASC; SELECT F_NAME, L_NAME FROM Employee WHERE JOB IS NOT 'PROGRAMMER' GROUP BY JOB ORDER BY L_NAME DESC;
UPDATE
The UPDATE command is used to modify a record already in the table. So an INSERT places the record in the table and an UPDATE changed the information contained by the record. There is a difference so please make note of it. The basic syntax of the command is:
UPDATE table SET COLUMN1 = VALUE1, COLUMN2 = ...;
The above statement will then set the entire column(s) to the designated value(s). But what is you only want to change certain columns? Then we add a WHERE modifier (Note the explaination given above - under SELECT) to better specify which records we want changed.
UPDATE Employee SET JOB = 'analyst' WHERE NAME = 'John';
DELETE
The DELETE command is used to delete an entire record(s) from the table, so use this with caution. The basic command syntax is:
DELETE FROM table;
Be VERY careful when using the above code. It will delete ALL records from the table. To only delete one or more records without deleting all the records you MUST have a WHERE modifier (Note the explaination given above - under SELECT). The WHERE modifier will allow you to better specify which records we want deleted.
DELETE FROM Employee WHERE NAME = 'John';
Have I lost anyone yet? For a more in depth explaination of these SQL commands and a full explaination of all the ANSI SQL commands, please go to www.sqlcourse.com for the basics and www.sqlcourse2.com for the more advanced stuff.
/ 3 