COM372: Notes, Chapter 10
Working with Your MySQL Database
pages 241-263
SQL
SQL means Structured Query Language. It provides an instructions set to read from databases, and to add, modify, or delete records within database tables (also known as querying). It is relatively standard across all databases (there is an ANSI—American National Standards Institute, which sets US standards for among many other things, computer languages— standard), with a few proprietary idiosyncrasies from Microsoft, of course, and even MySQL has its non-standard quirks. SQL includes language to define databases (used in the last chapter, also called data definition language) and for querying (data manipulation language).
Select, Insert, Update, and Delete: These commands allow you to read, add to, modify, and remove records in your database. Select (read) reads whatever records you want. Query means “read.” Insert, Update, and Delete—the action commands—change the database.
SELECT …FROM…
To read, you specify the names
of fields and tables that contain them, using Select fieldNames From tableNames. The wildcard * can be used to select all field names, E.g.,
SELECT FirstName, LastName FROM AddressBook
SELECT * FROM BudgetData
Aliases: When naming tables, we can add an alias, an abbreviation which makes reference to table-specific fields simpler to write. For example, Select * from tblCustomers C, tblOrders O Where C.customer = O.customer uses two aliases to create a simple matching condition for customer names in both tables.
One goal of SQL is to only select the data you want, so that you are not filling server memory unnecessarily. Use Select * judiciously.
Aggregate Functions. In addition to returning values for selected fields, SQL can return aggregate functions, including counts of the number of records for which there are fields, sums of numerical data, maximum and minimum values, and averages.
- Count | (*) | (Distinct | All) | (Field Name)
- (count(*) gives a row count regardless of null values
- distinct gives distinct values in that column only)
- Sum ([Distinct] Field Name)
- Avg ([Distinct] Field Name)
- Max ([Distinct] Field Name)
- Min ([Distinct] Field Name)
- Std = stddev
The distinct option ignores duplicate rows. All is the default.
From: This subclause specifies the source table(s). The subclause can include more than one table, where the tables are related by at least one common field.
Where. The Where subclause in a SQL statement allows you to set conditions on the records you select.
Select * From AddressBook Where LastName = ‘ Jones'
Select AccountName, Balance From tblLedger Where Balance > 1000
String data gets surrounded by quotes. Dates are set off by #'s. The entire string will be set off by double quotes, so use single quotes within the string. E.g.
strComm = “ Select * From tblData Where strName Like ‘Smith' ”
Where operators. The Where subclause of a SQL expression has its own set of operators, which establish the conditions of the clause. These include
Comparison Operators
- Equality (=)
- Non-equality (<> or !=)
- Less-Than (<), Greater-Than (>)
- Greater-than or equal (>=), less-than or equal (<=)
Logical Operators
- Is Null
- Between
- In (test whether a value is in a particular set)
- Like (check for string pattern match)
- Exists
- Unique or Distinct
- All, Any
Conjunctive Operators
- And
- Or
Negation
- Not
Arithmetic
- + Addition
- - Subtraction
- * Multiplication
- / Division
Joins: Joins allow us to select data from two or more tables based on fields held in common between the tables. See 248-251 for examples.
Order By: This clause sorts the data alphabetically or numerically by the fields specified, e.g., SELECT * FROM tblCustomers ORDER BY lastname. The default sort order is ascending (ASC); this can be reversed using descending (DESC), as in ...ORDER BY lastname desc;
Group by: You can create sets or data based on sets by using the group by clause. With aggregate functions (above), the functions will be for the groups, not for the entire data set. The addition of a having clause allows a further stipulation on the aggregate (see page 255).
Subqueries: You can write SQL instructions to perform subqueries (looking inside the results of one query for another answer using a second (sub) query. This is covered on 256-258. More often, however, we'll use PHP programming to accomplish the same things.
INSERT
Adding data to a table in a database is done with the INSERT command, which takes a list of the fields in one record of the table and matches them to values, using
INSERT [INTO] table [(column1, column2,...)] VALUES
as, for example,
INSERT INTO tblCustomers VALUE ( , 'John Smith', '1 Elm Street', 'Centerdale')
This was the first of the Book-O-Rama tables, which we set up in chapter 9. Notice that there are four values being entered, but that the first (before the first comma) is omitted. We set this up as a primary key, using the AUTO_INCREMENT specification; MySQL will provide a unique integer for us, so we never want to force a value (which may be a duplicate).
In phpMyAdmin, once you have logged in, you choose a database from the left-panel dropdown, which will bring up a list of tables:
You then fill in the form and hit "go..."
...which generates the INSERT SQL for you. Notice that the id (auto_increment) was also generated.
UPDATE
To modify data, you'll use the UPDATE command, which uses the SET clause to create name and (new) value pairs, as in UPDATE tblCustomers SET firstname='mariah' WHERE customerid = 43587. Again, we'll do this within the context of a PHP script (next chapter). In phpMyAdmin, you merely locate the record in a table and perform an edit operation on the fields required, which generates the appropriate SQL instructions.
DELETE
Deleting can be dangerous (you can delete the wrong record or all records if you are sloppy, and there is no back button to undo your error), so be careful to include an accurate condition, as in DELETE FROM tblcustomers WHERE customerid = 4344, to delete only a particular customer.