Perl - SQL, Structured Query Language
In a Nutshell - CIW Course Section 2, Part B3, Chapter 6
Structured Query Language
I have never fully understood this, apparently obsessive, need to categorise everything within this course. But this chapter continues the trend. Apparently SQL has three types of command which fall into these categories: Data Definition Language (DDL), Data Query Language (DQL) and Data Manipulation Language (DML). I have been using SQL (pronounced Ess-Cue-El, not See-Quell) for many years without needing to think of it in these terms. Mind you, I also used the incorrect pronunciation for most of those years.
The DML commands are to do with creating and modifying the table structure or schema and I don't intend to dwell on these here. I suspect most of you will use MySQL and this has very good GUI Administrative tool for doing this work. I may expand on it elsewhere if I get the chance. It also has, which I only discovered the other day, a passable query browser tool.
I have checked the syntax of the examples used on this page with both MySQL and Microsoft SQL Server.
SELECT Statement
SELECT Fieldname FROM MyTable
The SELECT statement is likely to be your most used SQL statement, as this is what will return data records from your database table(s). Fieldname can be one or more fieldnames, more than one will be comma-delimited. It may also be the asterisk (*) which is a wildcard denoting all fields. The FROM clause is used to identify the database table or query that should be used as the source.
SELECT * FROM MyTable WHERE ID = 100
This example introduces the WHERE clause. This will specify a record, or range of records, to be included in the result. Without it, all records from the table or query will be returned.
INSERT Statement
This statement will allow you to add records to the database table. You specify the fieldnames you wish to supply values for, there is no WHERE clause.
INSERT MyTable (FirstName, LastName)
VALUES ('Tom', 'Cruise')
If the table contains columns which do not allow Null values, then you must populate them with the INSERT statement. Omitting them will result in an error. I have used single quotes to delimit the string values as this is mandatory with Microsoft SQL, but MySQL will also accept double quotes.
UPDATE Statement
This allows the modification of data in one or more records. It will, generally, rely on judicious use of the WHERE clause the limit the number of rows affected.
UPDATE MyTable
SET FirstName = 'John'
WHERE ID = 2
This will modify the firstname value of the record with ID = 2. The ID column we haven't mentioned yet, this is a column containing a unique numeric value for each record. We have set Ozzy Osbourne's first name to John which is, in fact, his real name. The SET clause can update multiple fields.
UPDATE MyTable
SET FirstName = 'Wilma', LastName = 'Flintstone'
WHERE LastName LIKE 'Cru%'
This example uses the LIKE keyword which matches string values. It supports the wildcard character (%) to identify one or more characters. This wildcard applies to both MySQL and Microsoft SQL, but Microsoft Access uses the (*) character for the same thing.
DELETE Statement
I think MySQL uses a stricter syntax for this statement than Microsoft does. Having come from a Microsoft SQL background, I had to refer to the documentation for this.
DELETE FROM MyTable
WHERE ID = 1
This syntax works for both flavours of SQL but the FROM keyword is optional in Microsoft SQL.