5. More MySQL

What We Will Cover

Elucidations

Homework Questions?

Quiz Questions?

  • Login to Blackboard and go to the course homepage
  • For a quick view, click the "My Grades" link on the left menu
  • To view your answers and compare them to the correct answers:
    1. Click the "Assessments" link on the left menu
    2. Click the View All Submissions button
    3. For the assessment you want to review, click the hyperlink under the Attempt column.

Questions from last class?

^ top

5.1: Importing Non-SQL Data

Learner Outcomes

At the end of the lesson the student will be able to:

  • Import data from a text file into MySQL

^ top

5.1.1: About Importing Data

  • Previously we covered importing and exporting data as SQL statements
  • SQL statements are a convenient way to save and load database data while maintaining the database structure
  • Today we will cover how to load data from a non-SQL text file into a database
  • Examples of these types of files include:
    • CSV (Comma-separated variable) files
    • Text files with spaces or other characters between fields
  • You sometimes need to load data from other applications

About the Text File

  • Data loaded via text files must be organized into rows and columns
  • Each column of data must be separated by a separator character such as a tab, space or comma
  • Each row must be separated by a terminator character as well
    • Usually a newline character, designated as a '\n'
  • Fields can be enclosed in optional single (') or double (") quote marks

More Information

^ top

5.1.2: Loading Text Data Using phpMyAdmin

Using phpMyAdmin to Import Data

  1. Select the database to which you want to import data
  2. Select the table to which you want to import data
  3. Select the Import tab.

    You will see the Import form.

  4. Enter a filename or press the Browse... button to select a text file
  5. In the section labeled Format of imported file, select the CSV using LOAD DATA radio button.

    You will see a list of Options appear. Note that you can select the CSV button as well. This format lets you load data directly using phpMyAdmin rather than the MySQL LOAD DATA command. However, this method is slower for large files.

  6. Select other options as appropriate for your data
  7. Press the Go button to load the data from the file

Partial Screen Shot of Import Form in phpMyAdmin

Screen shot of importing data

^ top

5.1.3: Example of Loading Text Data

  • Let us look at an example of how to import data
  • Suppose we wanted to track a list of potential investors for our business
  • We create a table to store our data: 
    CREATE TABLE investor_data
(
    LastName varchar(50),
    FirstName varchar(50),
    MoneyToInvest varchar(50),
    PhoneNumber varchar(10)
);
  • Now you want to insert some data from a spreadsheet you created
  • You export the spreadsheet in tab-delimited format and create a text file 
    Davolio	Nancy	1000000	6175552200
Fuller	Andrew	500000	5085559822
Leverling	Janet	200000	3125553444
Peacock	Margaret	1500000	8185552225
Buchanan	Steven	750000	6305559929
Suyama	Michael	100000	3605552211
King	Robert	60000	6145550909
Callahan	Laura	600000	3305556767
Dodsworth	Anne	2000000	2165555454
  • You save the file in a convenient temporary location like the desktop
  • In phpMyAdmin we select the investors table
  • Then we select the Import tab.
  • We press the browse button and locate our text file
  • In the section labeled Format of imported file, we select the CSV radio button.
  • Then we indicate that the terminator of the fields is a tab using: \t
  • And finally, we press the Submit button to load the data

Escape Sequences

  • To read a text file, you need to understand standard escape sequences
  • Individual fields within a record are often delimited by commas, tabs or other special characters
  • Yet specifying a tab character is hard in a browser because the tab key is used to move between form fields
  • Thus, if you want to indicate that a tab separates each field you must enter the escape sequence: \t
  • Another problem you can run into is indicating the end of a record
  • Each record (like a line) are often delimited by end-of-line characters
  • The end of line characters differ depending on the operating system:
    • Macintosh (before OS-X): \r
    • Unix (including Mac OS-X): \n
    • Windows: \r\n
  • Depending on the operating system, text files require different entries in the form field labeled "Lines terminated by"
  • phpMyAdmin allows us to use the word auto to detect end-of-line characters
  • If you have problems with this feature, below are the three common escape sequences you will need to use instead

Common Escape Sequences

Sequence Meaning
\n New line
\r Carriage return
\t Horizontal tab

^ top

5.1.4: Using INSERT with SELECT

  • After you load data from an external source, you often need to restructure the data
  • For example, you may need to:
    • Change the order of the columns
    • Use only records meeting a criteria
  • One way to do this is to use an INSERT ... SELECT statement 
    INSERT [INTO] someTable (columnList)
   SELECT ...;
  • For example, you want to copy the investors data into an existing table
  • The table may have more or less columns than the investors table
  • INSERT ... SELECT allows you to choose which records to insert and the order of the insertion into the other table

INSERT ... SELECT Example

  • As an example of using INSERT ... SELECT let us create another table to copy our investor data into: 
    CREATE TABLE investor
(
    ID INT AUTO_INCREMENT PRIMARY KEY,
    LastName VARCHAR(100) NOT NULL,
    FirstName VARCHAR(100) NOT NULL,
    Phone VARCHAR(10) NOT NULL
);
  • Then we create a SQL statement to copy some of the data from one table to another: 
    INSERT INTO investor (LastName, FirstName, Phone)
SELECT LastName, FirstName, PhoneNumber
FROM investor_data
  • As we can see, we were able to selectively copy data from one table to another

More Information

^ top

5.1.5: Summary

  • You can import non-SQL data as a text file
  • The data still must be organized into rows and columns separated with terminators
  • phpMyAdmin has a form to specify parameters for loading data
  • After loading the data, you can match it to existing tables using INSERT .. SELECT

Quick Quiz

  1. You can import text and Excel files directly into MySQL.


  2. Which of the following is NOT a standard escape sequence?


  3. The INSERT command can be combined with the command to selectively copy data from one table into another.

Check Yourself

  1. What types of files can MySQL import? (5.1.1)
  2. What structure does the file of imported data need to have? (5.1.1)
  3. What tab in phpMyAdmin do you use for importing data? (5.1.2)
  4. What keyboard character does the '\t' escape sequence represent? (5.1.3)
  5. After you import data, how do you move data from one table to another? (5.1.4)

^ top

Exercise 5.1

Instructions:

In this exercise we explore how to load non-SQL data into MySQL.

Specifications

  1. Open the XAMPP Control Panel by double-clicking the icon on the desktop or by using the Apache Friends entry in the start menu.

    XAMPP icon

  2. Start the Apache and MySQL modules, if they are not already running.

    XAMPP control panel

  3. Open a new Web browser tab or window and enter localhost/phpmyadmin in the address field.

    You should see phpMyAdmin running in the browser window.

  4. Select the test database from the drop-down list on the left.
  5. Select the SQL tab.

    In the right frame you will see a large text area for entering SQL statements.

  6. Create a new table in the test database by entering the following code and pressing the Go button: 
    CREATE TABLE investor_data
(
    LastName varchar(50),
    FirstName varchar(50),
    MoneyToInvest varchar(50),
    PhoneNumber varchar(10)
);
  7. Save this information into a file named investors.txt in a convenient location on your computer like the Desktop: 
    Davolio	Nancy	1000000	6175552200
Fuller	Andrew	500000	5085559822
Leverling	Janet	200000	3125553444
Peacock	Margaret	1500000	8185552225
Buchanan	Steven	750000	6305559929
Suyama	Michael	100000	3605552211
King	Robert	60000	6145550909
Callahan	Laura	600000	3305556767
Dodsworth	Anne	2000000	2165555454
  8. Select the investor_data table you just created and then click the Import tab.

    You will see a form labeled Import.

  9. Press the Browse... button and select the investors.txt file you just saved.
  10. In the section labeled Format of imported file, select the CSV using LOAD DATA radio button.

    You will see a list of Options appear.

  11. Change the Fields terminated by text field to: \t

    For more information on the \t escape character see section: 5.1.3: Example of Loading Text Data.

  12. Press the Go button to load the data from the file.

    phpMyAdmin will show you the MySQL command it used to load the data.

  13. Click the Browse tab and look at the data you loaded.

    You should see four columns of data with the headings LastName, FirstName, MoneyToInvest and PhoneNumber.

  14. Select the SQL tab and enter the following SQL code to create another table: 
    CREATE TABLE investor
(
    ID INT AUTO_INCREMENT PRIMARY KEY,
    LastName VARCHAR(100) NOT NULL,
    FirstName VARCHAR(100) NOT NULL,
    Phone VARCHAR(10) NOT NULL
);
  15. Then use the following SQL statement to copy some of the data from one table to another: 
    INSERT INTO investor (LastName, FirstName, Phone)
SELECT LastName, FirstName, PhoneNumber
FROM investor_data
WHERE MoneyToInvest >= 500000

    For more information lesson: 5.1.4: Using INSERT with SELECT.

  16. Select the investor table from the index on the left and then click the Browse tab

    You should see four columns of data with the headings ID, LastName, FirstName and PhoneNumber.

  17. Export the test database, with the investor and investor_data tables, as a CSV file and save it to the desktop using the name: test.csv.

    For more information see lesson: 2.1.5: Importing and Exporting Data. However, you will use the CSV export button rather than the SQL button.

  18. Submit the test.csv file to Blackboard as part of assignment 5.

As time permits, be prepared to answer the Check Yourself questions in the section: 5.1.5: Summary.

^ top

5.2: Indexing and Foreign Keys

Learner Outcomes

At the end of the lesson the student will be able to:

  • Design optimized indexes
  • Implement foreign key constraints

^ top

5.2.1: About Indexes

  • An index is a structure associated with a table that speeds retrieval of rows
  • By analogy, an index is like a card catalog for a library
  • If the library has only a few books, a card catalog is not needed
    • You just look at each book on the shelf
  • However, if the library has many books, using a card catalog makes finding books much faster
  • How long would it take to find a book in a public library if you had to search every title?
  • You could sort books by title to make finding them faster
  • But what if you wanted to find all the books by some particular author?
  • You cannot sort two ways at once, so you need another way to look things up
  • Just like a card catalog in a library, an index makes finding rows of table much faster

Indexes and Database Performance

  • Indexes speed up database queries when you have more than a few rows of data
  • When an index is not used, the database engine executes a table scan
  • With a table scan, the database must:
    • Start with first record
    • Reads every entry in the table until it finds the rows it needs
  • If an index is used, data can be located in just a few (log(n)) steps
  • For a table with 1000 rows, using an index is about 100 times faster than using a table scan

MySQL Indexes

  • MySQL lets you define and index on any column in a table
  • Tables without indexes have data rows that are not stored in any particular order
  • This structure is sometimes called a heap
  • There are two methods for indexing data used by most databases: clustered and non-clustered

How Indexes Work

  • Indexes are stored in a structure called a B-Tree
  • The first entry in the index is called the root node
  • Below the root node are branch nodes
  • Each branch node contains a list of ordered values and pointers to the next branch (child) node
  • Nodes with no children are called leaf nodes
  • The index data is stored in the leaves (leaf nodes) of the tree
  • This structure allows us to quickly find an item in the tree
  • For example:

    In the following B-Tree, find the name Richards starting at the root node:

    B-tree example

  • What nodes are traversed to find Grant? (answer)
  • How much faster is it to find an entry in a small index like this one? (answer)
  • How much faster is it to find an entry in a table of one thousand records? (answer)

More Information

^ top

5.2.2: When Indexes Help

  • Certain types of queries tend to benefit more from indexing than others
  • Some of the common types of queries benefiting most from indexes include:
  1. Queries that perform an exact match 
    SELECT *
FROM Customer
WHERE CustomerID = 12345
  2. Queries that use a range of values to return rows 
    SELECT *
FROM Orders
WHERE OrderDate > '2001-02-01'
AND OrderDate <= '2002-02-01'
  3. Queries that use a join operation 
    SELECT Orders.OrderID, Orders.OrderDate,
    Employees.FirstName, Employees.LastName
FROM Orders, Employees
WHERE Orders.EmployeeID = Employees.EmployeeID
  4. Queries that return rows in sorted order

More Information

^ top

5.2.3: Designing Indexes

  • Indexes take resources to maintain
  • One obvious resource is the memory space to store indexes
  • Besides space, the database must insert new index entries in the correct location
  • Having too many indexes can degrade write performance in a table
  • The designer's goal is to find a balance that covers queries without degrading overall performance

Narrow vs. Wide Indexes

  • You can create indexes using more than one column
  • MySQL allows indexes with up to 15 columns
  • You can think of the index as a concatenation of all the columns
  • Narrow indexes use few columns
  • Wide indexes use many columns
  • Narrow indexes take less storage space than wide indexes
  • Wide indexes cover more columns than narrow indexes
  • This is a classic design tradeoff between storage and speed

Covering Indexes

  • Covered query: when an index includes all of the columns requested in a query
  • For instance, consider a query of first and last names
  • A single composite index of last and first names could contain data from both columns in its nodes
  • In a query of last and first names, the database would not need to find the data in the actual table
  • The database would just use the index data stored in the tree leaves

^ top

5.2.4: Creating and Dropping Indexes

  • Maximum number of keys and the maximum index length depends on the table type
  • All tables can have at least 16 indexes and a total index length of 256 bytes
  • Most storage engines have higher limits
  • For instance, the default MyISAM table allows 64 indexes per table and 16 columns per index
  • You can create indexes in various ways

Using phpMyAdmin

  1. Select the database in which you want to add or drop an index
  2. Select the table to which you want to add or drop an index
  3. Part way down the Structure page, you will find Indexes
  4. Enter the number of columns you want an index to include
  5. Press the Go button to open the Create a new index form
  6. Enter a name for the index
  7. Select the Index type you want
    • INDEX: Use if the column can have duplicate entries
    • UNIQUE: Use if the column has unique entries in every row
    • FULL TEXT: For indexing every word of a CHAR, VARCHAR or TEXT field (MyISAM tables only)
  8. Select the field or fields you want to index
  9. Enter a size prefix, if appropriate
    • Size allows you to index part of a field for CHAR and VARCHAR types
    • For example: the first 10 characters of a VARCHAR column
  10. Press the Save button to create the indexes

Creating Indexes When Creating Tables

  • You often create indexes when you create the table
  • General syntax: 
    CREATE TABLE tableName
    ...
    [UNIQUE] INDEX indexName (colName[(length)],... ));
  • Note that we can add multiple-column indexes
  • An alternate word for INDEX is KEY
  • For example: 
    DROP TABLE IF EXISTS Suppliers;
CREATE TABLE Suppliers (
    ID int unsigned auto_increment PRIMARY KEY,
    SupplierName varchar(50) NOT NULL,
    SupplierCode varchar(10),
    UNIQUE INDEX SupplierCode_idx (SupplierCode)
);

CREATE INDEX Statement

  • CREATE INDEX allows you to add indexes to existing tables
  • Syntax for CREATE INDEX is: 
    CREATE [UNIQUE] INDEX indexName
    ON tableName (colName[(length)],... ));
  • For example: 
    CREATE INDEX SupplierNameIdx
    on Suppliers (SupplierName(10));
  • Creates an index on the first 10 characters of SupplierName column

DROP INDEX Statement

  • DROP INDEX drops the index specified
  • Syntax: 
    DROP INDEX indexName ON tableName;
  • For example: 
    DROP INDEX SupplierNameIdx on Suppliers;

More Information

^ top

Exercise 5.2

Instructions:

In this exercise we explore how to create and delete indexes.

Specifications

  1. Make sure that Apache and MySQL are running using the XAMPP control panel. Then open a new Web browser tab or window and enter localhost/phpmyadmin in the address field.

    You should see phpMyAdmin running in the browser window.

  2. Select the artzy database from the drop-down list on the left.

    You should see the artzy database with a list of its tables.

  3. Select the orders table from the table list on the left.

    You should be in the Structure tab of the orders table. If not, click the Structure tab at the top of the page.

  4. Scroll down the page until you see the Indexes area. You should see two current indexes named PRIMARY and CustomerID_idx.
  5. We need to add an index for the AddressID field. Next to the text field labeled, "Create an index on", click the Go button.

    You will see a new form labeled: Create a new index. For more information see section: 5.2.4: Creating and Dropping Indexes.

  6. In the Index name field, enter the name: AddressId_idx. Also, select the AddressID field from the drop-down list labeled Field. Then press the Save button.

    You will return to the Structure tab where you can see your new index listed.

  7. Notice the SQL query at the top of the page. This is the SQL statement that created the index. You need to save this query.
  8. Create a file named indexes.txt and copy the ALTER TABLE... SQL statement from phpMyAdmin into the file.
  9. Select the orderitems table from the table list on the left.

    You should be in the Structure tab of the orderitems table. If not, click the Structure tab at the top of the page.

  10. Locate the Indexes area. You should see three current indexes named PRIMARY, ProductID_idx and OrderID_idx.
  11. Click the drop icon red X icon for the OrderID_idx index. For the confirming dialog, click the OK button.
  12. Notice the SQL query at the top of the page. This is the SQL statement that dropped the index. Copy this query and paste it into the indexes.txt file.
  13. Submit the indexes.txt file to Blackboard as part of assignment 5.

As time permits, finish reading the following sections and be prepared to answer the Check Yourself questions in the section: 5.2.6: Summary.

^ top

5.2.5: Foreign Key Constraints

  • Primary keys and foreign keys work together to form relationships between tables
  • The foreign key in one table points to the primary key in another table
  • You can set up a foreign key constraint between the tables
  • A foreign key constraint is a referential constraint that prevents you from entering an incorrect foreign key value
    • Constraints generate error messages if data does not meet requirements
    • Using a constraint guards the integrity of your data
  • You cannot insert a row with a foreign key constraint (except NULL) if there is no primary key with that value

    Foreign key example

  • Foreign key constraints are supported in the InnoDB table type
  • Note that InnodDB is not installed in the classroom or on the WebHawks server
  • Requirements for creating a foreign key constraint include:
    1. Both tables must be the InnoDB table type
    2. Both the foreign key and referenced key must be indexed
    3. Columns in the foreign key and the referenced key must have similar data types
    4. Size and the signedness of integer types has to be the same
    5. Length of string types need not be the same
  • Syntax of the foreign key constraint definition: 
    FOREIGN KEY (index_col_name, ...)
  REFERENCES table_name (index_col_name, ...)
  [ON DELETE {CASCADE | SET NULL | NO ACTION | RESTRICT}]
  [ON UPDATE {CASCADE | SET NULL | NO ACTION | RESTRICT}]
  • If you specify a SET NULL action, make sure you have not declared the columns in the child table NOT NULL
  • You can add a foreign key constraint to an existing table: 
    ALTER TABLE yourtablename
  ADD [CONSTRAINT symbol] FOREIGN KEY (...)
  REFERENCES anothertablename(...)
  [on_delete_and_on_update_actions]
  • Also, you can also list foreign key constraints: 
    SHOW TABLE STATUS
  FROM databaseName
  LIKE 'tableName'

More Information

^ top

5.2.6: Summary

  • Indexes speed up data retrieval from tables with a large number of rows
  • You can add indexes during the creation of a table
  • Also, you can choose to add indexes after a table is created
  • Indexes should be added carefully because they:
    • Require more storage space
    • Make an INSERT slower
  • You can create and drop indexes using SQL or phpMyAdmin
  • Indexes are required before implementing foreign key constraints
  • Version 4.X of MySQL supports foreign key constraints only with the InnoDB table type
  • However, we do not have InnoDB installed and so you cannot implement foreign key constraints

Quick Quiz

  1. A database engine performs a(n) when a suitable index is NOT defined.
  2. The first node accessed in a B-tree is called the root node.


  3. Which of the following is an index with multiple columns?


Check Yourself

  1. What is a table scan? (5.2.1)
  2. What is an index? (5.2.1)
  3. Why is an index faster searching for data rows that a table scan? (5.2.1)
  4. What is a root node? (5.2.1)
  5. What is a leaf node? (5.2.1)
  6. When do indexes most help query performance? (5.2.2)
  7. What is a narrow index? (5.2.3)
  8. What is a wide index? (5.2.3)
  9. What is a covered query? (5.2.3)
  10. What is a SQL statement for adding an index named Address_idx to an existing table named customers? (5.2.4)
  11. What is a foreign key constraint? (5.2.5)

^ top

5.3: Grouped Queries and Aggregate Functions

Learner Outcomes

At the end of the lesson the student will be able to:

  • Use SQL aggregation functions
  • Group records and calculate statistics for the records
  • Use an alias in queries

Before We Start

  • Make sure Artzy database is still in good condition
  • Import the artzy.txt database into MySQL.

^ top

5.3.1: Using Aggregate Functions

  • Aggregate functions summarize results of query rather than returning all the rows
  • Common aggregate functions include:
    AVG(colName)returns average value of a given column
    COUNT(colName)returns the total number of non-null values in a column
    COUNT(*)returns number of rows in a table
    MIN(colName)returns smallest value in a given column
    MAX(colName)returns largest value in a given column
    SUM(colName)returns total of numeric values in a given column
  • We will look at some examples of each function

Using Count

  • You can use COUNT to count the number of rows in a table
  • For example:

    How many products are in the Products table?

  • Translating the query into SQL: 
    SELECT COUNT(*)
FROM products

Using SUM

  • You can use SUM to find the total of all values of a column
  • For example:

    Find the number of products and how much it would cost to buy one unit of each of the products.

  • Translating the query into SQL: 
    SELECT COUNT(*), SUM(Price)
FROM products

Using AVG, MAX and MIN

  • You use AVG, MAX and MIN like you use SUM
  • Only a different statistic is returned
  • Following is query showing use of these functions: 
    SELECT SUM(Price), AVG(Price), MAX(Price), MIN(Price)
FROM products

More Information

^ top

5.3.2: Grouping Data

  • Recall from lesson 2.2.2 that you can perform arithmetic on the columns containing numerical data
  • For example:

    List the order number and the total dollar value for every order item.

  • Translating this into SQL: 
    SELECT OrderID, (PriceEach * Quantity)
FROM orderitems;
  • Note that orders can have several items listed
    • Customer are ordering more than one item at a time
  • It would be convenient to group rows containing common values like the OrderID

Grouping Common Values

  • Grouping combines rows of related values into a smaller set of rows
  • Syntax: 
    SELECT columnNames FROM tableName
[WHERE criteria]
GROUP BY groupFieldList
[HAVING searchCriteria]
  • Here we have introduced two new keywords: GROUP BY and HAVING
  • We will look at how to use both of them

Using GROUP BY

  • The GROUP BY command returns a list that is grouped by one or more columns
  • You can use it for arithmetic if desired
  • The SELECT command performs any calculations for the entire group
  • For example:

    List the order number and the total dollar value of every customer's order.

  • To obtain the total dollar value, we must:
    1. Multiply price times quantity for every item
    2. Then add these results for each item in the order
    3. Repeat this process for each order
  • You cannot simply use SUM(Price * Quantity) because this will total all orders and not individual orders
  • To get the results you want, you must use the GROUP BY command to calculate and sum individual totals 
    SELECT OrderID, SUM(PriceEach * Quantity)
FROM orderitems
GROUP BY OrderID
ORDER BY OrderID;
  • When rows are grouped, one line is produced for each group
  • Note that each order has the total value calculated

Using HAVING

  • You use the HAVING clause with GROUP BY to further restrict the query results
  • The HAVING clause operates like the WHERE clause, but only on the rows returned after grouping
  • This lets us refine the result sets of a GROUP BY query using HAVING
  • For example:

    List the order number and the total dollar value of every order whose total value is at least $15.

  • Translating this into SQL: 
    SELECT OrderID, SUM(PriceEach * Quantity)
FROM orderitems
GROUP BY OrderID
HAVING SUM(PriceEach * Quantity) >= 15
ORDER BY OrderID;

HAVING vs. WHERE

  • WHERE clause filters rows before grouping
  • HAVING clause filters rows after grouping

^ top

5.3.3: Comprehensive Example

  • Let us create a SQL query using all the features we have discussed
  • Such a query illustrates each major clause in SELECT statement
  • Also, you can see the order in which these clauses generally must appear
  • Suppose we want to:

    List the customer number, order number, order date and order total for every order with a total of over $15.

  • Translating this into SQL we have: 
    SELECT customers.ID, orders.ID, OrderDate,
  SUM(Quantity * PriceEach)
FROM customers, orders, orderitems
WHERE customers.ID = orders.CustomerID
AND orders.ID = orderitems.OrderID
GROUP BY customers.ID, orders.ID, OrderDate
HAVING SUM(Quantity * PriceEach) > 15
ORDER BY orders.ID;
  • Notice the ambiguity of the column names in the output produced by the query
  • To remove this ambiguity, you can use a column alias as discussed in Lesson 2.3.4
  • For example: 
    SELECT customers.ID AS CustID,
  orders.ID AS OID, OrderDate,
  SUM(Quantity * PriceEach) AS Total
FROM customers, orders, orderitems
WHERE customers.ID = orders.CustomerID
AND orders.ID = orderitems.OrderID
GROUP BY customers.ID, orders.ID, OrderDate
HAVING Total > 15
ORDER BY orders.ID;
  • Notice that you can use the alias in the HAVING clause but not the WHERE clause

^ top

5.3.4: Summary

  • SQL aggregation functions allow you to calculate an entire returned column
  • Also, you can calculate a group or records
  • To group records by a column, you use the GROUP BY clause
  • After grouping you use the HAVING clause to restrict the records returned after grouping

Quick Quiz

  1. The COUNT(*) function counts all rows in a table.


  2. To calculate the average UnitPrice in the "products" table you use:


  3. You must also use the GROUP BY clause when using the HAVING clause


Check Yourself

  1. What in a SQL statement to determine the total number of rows in the "products" table of the artzy database? (5.3.1)
  2. What is a SQL statement to determine the highest value of the ID field of the "products" table of the artzy database? (5.3.1)
  3. What is a SQL statement to return the lowest "UnitPrice" in the "products" table of the artzy database? (5.3.1)
  4. What is the purpose of the GROUP BY clause? (5.3.2)
  5. What is the purpose of the HAVING clause (5.3.2)
  6. What other clause does the HAVING clause act like? (5.3.2)

^ top

Exercise 5.3

In this exercise we aggregate data in a query.

Specifications

  1. Make sure that Apache and MySQL are running using the XAMPP control panel. Then open a new Web browser tab or window and enter localhost/phpmyadmin in the address field.

    You should see phpMyAdmin running in the browser window.

  2. Select the artzy database from the drop-down list on the left.

    You should see the artzy database with a list of its tables.

  3. Create a file named aggregate.txt and write a query for the following problem. When your query works, save it in the aggregate.txt file.

    List the supplier number, supplier name and average selling price of the products sold by every supplier

  4. Submit the aggregate.txt file to Blackboard as part of assignment 5.

As time permits, be prepared to answer the Check Yourself questions in the section: 5.3.4: Summary.

^ top

5.4: Other MySQL Functions

Learner Outcomes

At the end of the lesson the student will be able to:

  • Use MySQL's non-standard functions in queries
  • Use the CONCAT() function
  • Format dates and times
  • Use the LAST_INSERT_ID function

^ top

5.4.1: Control-Flow Functions

  • MySQL includes several useful functions beyond those used in GROUP BY
  • These functions are useful at time
  • However, we most often use PHP for the functionality they provide

IFNULL

  • NULL is a special value
  • Sometimes need to test for NULL values
  • You can use IFNULL(expr1, expr2)
    • If expr1 is not NULL, returns expr1
    • Else returns expr2
  • For example: 
    SELECT IFNULL(1, 0);
  -> 1
SELECT IFNULL(1/0, 10);
  -> 10

IF

  • MySQL logical functions return 1 for True, 0 for False or NULL for unknown
  • These same logical values applied to conditional expressions
  • For example, conditional expression IF(expr1,expr2,expr3)
    • Evaluates expr1 to determine if True
    • Considered True if expr1 <> 0 and expr1 <> NULL
    • If True, then executes expr2
    • Otherwise considered False and executes expr3
  • Some examples: 
    SELECT IF(1, 2, 3);
  -> 2
SELECT IF(1 < 2, 'yes', 'no');
  -> 'yes'
  • What is returned by the following? 
    SELECT IF(2, 2, 3);

More Information

^ top

5.4.2: String Functions

  • Used for manipulating text and character columns
  • To use in a query, you must modify your query to apply the function
  • For example: 
    SELECT colName FROM tableName
  • With a function added becomes: 
    SELECT FunctionName(colName) FROM tableName
  • Functions are all case-insensitive
  • Note that you cannot have spaces between the name and opening parenthesis

Some Commonly-Used String Functions

LENGTH(colName)returns the length of the string in the column
CONCAT(colName1,colName2,...)appends multiple columns together
TRIM(colName)removes spaces at the beginning and end
UPPER(colName)capitalizes all the letters
LOWER(colName)converts all letters to lower case
LEFT(colName,n)returns the leftmost n characters from a column
RIGHT(colName,n)returns the rightmost n characters from a column
MID(colName,start,length)returns length characters from column beginning with start characters
REPLACE(colName,fromStr,toStr)returns a string with all occurrences of fromStr replaced with toStr

Some Examples

  • Find the longest product name in the products table: 
    SELECT LENGTH(ProductName), ProductName
FROM products
ORDER BY LENGTH(ProductName) DESC;
  • List all the customers in the form: Last, First: 
    SELECT CONCAT(LName,', ',FName) AS Name
FROM customers;
  • Note the use of the alias Name

More Information

^ top

5.4.3: Numeric Functions

  • MySQL provides a large collection of mathematical functions

Some Commonly-Used Numeric Functions

ABS(colName)returns the absolute value
CEILING(colName)returns the next-highest integer
FLOOR(colName)returns the integer value
FORMAT(colName,d)returns formatted numbers with d decimal places and commas every three spaces
RAND(colName)returns a random number between 0 and 1.0
ROUND(x,d)returns x rounded to d decimal places
SQRT(colName)returns the calculated square root

Some Examples

  • Display the product names and prices, formatting the amounts as dollars: 
    SELECT ProductName, CONCAT('$',FORMAT(Price,2)) AS Price
FROM products;
  • Round each price from the previous query to the nearest dollar: 
    SELECT ProductName, CONCAT('$',ROUND(Price,0)) AS Price
FROM products;
  • Retrieve all the customer names in random order and execute the query multiple times: 
    SELECT ID, LName, FName
FROM customers
ORDER BY RAND(ID);

More Information

^ top

5.4.4: Date and Time Functions

  • MySQL includes many functions to work with date and time

Some Date and Time Functions

HOUR(colName)returns just the hour value of a date
MINUTE(colName)returns just the minute value of a date
SECOND(colName)returns just the second value of a date
DAYNAME(colName,d)returns just the name of the day of a date
DAYOFMONTH(colName)returns just the numerical day value of a date
MONTHNAME(x,d)returns just the name of the month of a date
MONTH(colName)returns just the numerical month value of a date
YEAR(colName)returns just the year value of a date
CURDATE()returns the current date
CURTIME()returns the current time
NOW()returns the current date and time
UNIX_TIMESTAMP()returns the number of seconds since the epoch or since the date specified
ADDDATE(colName,INTERVAL expr type)returns the value of expr units added to colName
SUBDATE(colName,INTERVAL expr type)returns the value of expr units subtracted from colName

Some Examples

  • Show the current date and time: 
    SELECT NOW();
  • Display every order dated from February: 
    SELECT * FROM orders
WHERE MONTH(OrderDate) = 2;
  • Show the order number, order date and an expiration date of 30 days past the order date: 
    SELECT ID, OrderDate,
  ADDDATE(OrderDate, INTERVAL 30 DAY) AS Expiration
FROM orders;

More Information

^ top

5.4.5: Formatting the Date and Time

  • Two additional date and time functions are often used
  • DATE_FORMAT works on both date and time values
  • TIME_FORMAT works only on time values
  • General syntax: 
    DATE_FORMAT(date, formatSpecifier)
  • Where the format is a special value as follows:
    Specifier Description
    %M Month name (January..December)
    %W Weekday name (Sunday..Saturday)
    %D Day of the month with English suffix (0th, 1st, 2nd, 3rd, etc.)
    %Y Year, numeric, 4 digits
    %y Year, numeric, 2 digits
    %X Year for the week where Sunday is the first day of the week, numeric, 4 digits, used with '%V'
    %x Year for the week, where Monday is the first day of the week, numeric, 4 digits, used with '%v'
    %a Abbreviated weekday name (Sun..Sat)
    %d Day of the month, numeric (00..31)
    %e Day of the month, numeric (0..31)
    %m Month, numeric (00..12)
    %c Month, numeric (0..12)
    %b Abbreviated month name (Jan..Dec)
    %j Day of year (001..366)
    %H Hour (00..23)
    %k Hour (0..23)
    %h Hour (01..12)
    %I Hour (01..12)
    %l Hour (1..12)
    %i Minutes, numeric (00..59)
    %r Time, 12-hour (hh:mm:ss [AP]M)
    %T Time, 24-hour (hh:mm:ss)
    %S Seconds (00..59)
    %s Seconds (00..59)
    %p AM or PM
    %w Day of the week (0=Sunday..6=Saturday)
    %U Week (00..53), where Sunday is the first day of the week
    %u Week (00..53), where Monday is the first day of the week
    %V Week (01..53), where Sunday is the first day of the week. Used with '%X'
    %v Week (01..53), where Monday is the first day of the week. Used with '%x'
    %% A literal `%'.
  • Note that formatting in MySQL is usually faster than formatting in another application

Some Examples

  • Return the current date and time as: Month DD, YYYY - HH:MM: 
    SELECT DATE_FORMAT(NOW(), '%M %e %Y - %l:%i') AS Now;
  • Display the time using 24-hour notation: 
    SELECT TIME_FORMAT(CURTIME(), '%T') AS Time;
  • Show every order, ordered by the date, formatting the date as Abbreviated-weekday Day Month Abbreviated-year: 
    SELECT DATE_FORMAT(OrderDate, '%a %b %e %Y') AS 'Date'
FROM orders
ORDER BY OrderDate;

More Information

^ top

5.4.6: Encryption Functions

  • MySQL has several encryption and decryption functions
  • Some encryption functions work only one way
    • You can encrypt but cannot decrypt
  • One way encryption is a great safety feature for stored passwords
    • You can never retrieve a password in readable form
    • This ensure that only the user know the password
  • What do you do if the user forgets a password?

Creating Passwords

  • Can use the PASSWORD(str) function to encrypt passwords
    • Used by MySQL to store user passwords
    • MySQL documentation does not recommend using this function
    INSERT INTO users(UserName, Userpwd)
VALUES('fredflint', PASSWORD('password'));
  • For added safety, can use ENCRYPT(str[,salt])
    • Uses the underlying UNIX crypt function
    • Thus, not useable on all systems
    • Returns NULL if not available on the system
    • Salt helps to randomize the encryption process
    INSERT INTO users(UserName, Userpwd)
VALUES('barney', Encrypt('password', 'salt'));
  • Another very secure one-way algorithm is MD5(string)
    • Calculates an MD5 128 bit checksum for the string
    • Returns value as 32 character hex string
    • Since Userpwd only stores 16 bytes, cannot use the extra data
    INSERT INTO users(UserName, Userpwd)
VALUES('wilma', MD5('password'));
  • Another one-way algorithm with 160-bit encryption is SHA(string)
    • Version of the SHA1 algorithm
    • Considered cryptographically more secure than MD5
    • Returns a 40 character hex string
    • Since Userpwd only stores 16 bytes, cannot use the extra data
    INSERT INTO users(UserName, Userpwd)
VALUES('betty', SHA('password'));

Encrypting and Decrypting Data

  • Use the ENCODE(str,passStr) and DECODE(cryptStr,passStr) functions for this example
  • First we will create a new users2 table in the Artzy database: 
    CREATE TABLE users2 (
  userid INT UNSIGNED NOT NULL AUTO_INCREMENT,
  userpwd char(40),
  username TINYBLOB,
  PRIMARY KEY(userid)
);
  • Will use the username field for encryption and decryption
  • ENCODE returns a string that should be stored in a BLOB column type
  • We can insert a new user into users: 
    INSERT INTO users2
VALUES(NULL, MD5('password'),
  ENCODE('wilma', 'salt'));
  • To retrieve the users information: 
    SELECT userid
FROM users2
WHERE userpwd = MD5('password')
AND DECODE(username, 'salt') = 'wilma';

More Information

^ top

5.4.7: Other Functions and User Variables

  • MySQL has number of other functions that are often useful in queries
  • DATABASE() returns the currently-selected database name: 
    SELECT DATABASE();
  • USER() returns the MySQL user name: 
    SELECT USER();
  • LAST_INSERT_ID() returns the last automatically generated value inserted into an AUTO_INCREMENT column
    • Value retained by server for each connection
    • If many rows inserted at the same time, returns ID of first insertion
    SELECT LAST_INSERT_ID();

User Variables

  • Note that MySQL supports user variables using @variablename
  • Use the SET command to store a value
  • For example: 
    SET @lastID = LAST_INSERT_ID();
  • Can then use the variable in SQL statements: 
    SELECT @lastID;
  • Variables don't have to be initialized but contain NULL by default

More Information

^ top

5.4.8: Summary

  • In this section we looked at many of MySQL's non-standard functions
  • While not standard SQL, they can be useful
  • Also, many other database systems implement similar functions
  • In addition to functions, we looked at user variables as well
  • A user variable stores data in a memory location that can then be used in other queries
  • As an example, we looked at saving the value of an AUTO_INCREMENT field 
    SET @lastID = LAST_INSERT_ID();

Check Yourself

  1. What are MySQL's control flow functions and when are they useful?
  2. What is the syntax of the MySQL function used to concatenate (join) multiple strings?
  3. Why would you want to use MySQL's date and time functions?
  4. What is at least one use of a one-way encryption function, where you can encrypt data but not decrypt data.
  5. What function is used to extract the last automatically generated value inserted into an AUTO_INCREMENT column?

^ top

Exercise 5.4

Take one minute to read and answer over the following Quick Quiz questions. There is no need to turn these in.

Quick Quiz

  1. All of MySQL's functions are standard SQL.


  2. To format the date and time, you use the function.
  3. To temporarily store the value of the last automatically generated AUTO_INCREMENT value you can use the SQL statement:


^ top

Wrap Up

Due Next:
A5-Load Data and Index Tables (3/23/09)
Quiz 5 and Discussion Chapter 6 (3/23/09)
When class is over, please shut down your computer if it is on

^ top

Home | WebCT | Announcements | Course info | Expectations | Schedule
Project | Help | FAQ's | HowTo's | Links
Last Updated: November 03 2009 @22:38:41