5. More MySQL

What We Will Cover

Elucidations

Homework Questions?

Quiz Questions?

Questions from last class?

^ top

5.1: Importing Non-SQL Data

Objectives

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
    • Convenient way to save and load data and databases
  • Today we will cover how to load data from an non-SQL text file
    • Examples of these files include CSV (Comma-separated variable) files
    • Sometimes convenient for loading 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 single (') or double (") quote marks

More Information

^ top

5.1.2: Loading Text Data Using phpMyAdmin

  • phpMyAdmin provides a user interface for importing text data
  • Uses MySQLs LOAD DATA INFILE command

Using phpMyAdmin

  1. Select the database to which you want to import data
  2. Select the table to which you want to import data
  3. At the bottom of the page, you will find a link labeled

    4. Insert data from a textfile into table

  4. Click on the link to display the load data form
  5. Enter a filename or press the Browse... button to select a text file
  6. Select other options as appropriate for your data
  7. Press the Submit button to load the data into a file

^ top

5.1.3: Loading Text Data Using SQL

  • You can alter a table using a SQL statement
  • Syntax: see LOAD DATA INFILE Syntax
  • Important to know the options available in case the data is not formatted well
  • Can use with just two parameters: source data file and database name
  • For example, you create a new table in the test database
    • CREATE TABLE investors
(
    LastName varchar(50),
    FirstName varchar(50),
    MoneyToInvest varchar(50),
    PhoneNumber varchar(10)
);
  • Now you want to insert some data from a spreadsheet you created
  • Saving the spreadsheet in tab-delimited format, you have the following
    • 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 information into a file: /home/cis165/investors.txt
  • To insert the data, you use the following SQL command
    • LOAD DATA INFILE '/home/yourLogin/investors.txt'
  INTO TABLE investors;
  • If you get an error, you probably have a directory or file permissions problem
    • chmod 755 .
chmod 755 investors.txt
  • You can look at the data you just entered:
    • SELECT * FROM investors;
  • Now you get additional notes in a comma-delimited (.csv) format
  • You save the data into a file: /home/cis65/investnew.txt
    • New investors:,,,
Last,First,Amount,Phone
Kustow,Jock,1000000,6175553300
Fulton,Aaron,500000,5085559551
Beverly,James,200000,3125553322
Pedalo,Antonio,1500000,8185556432
  • You could try our previous LOAD DATA INFILE operation using the new file name
  • However, you realize you need to specify more parameters to LOAD DATA INFILE
    • LOAD DATA INFILE '/home/yourLogin/investnew.txt'
  INTO TABLE investors
  FIELDS TERMINATED BY ','
  IGNORE 2 LINES;
  • Further information: LOAD DATA INFILE Syntax

^ 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

Further 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
  • Also, you can use SQL statements to load data
    • Provides the most flexibility
  • After loading the data, you can match it to existing tables using INSERT .. SELECT

^ top

Exercise 5.1

Instructions:

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

Specifications

  1. Start a text file named exercise5.txt.
  2. Prepare the exercise header as described in the HowTo on submitting exercises
  3. Label this exercise: Exercise 5.1
  4. Create a new table in the test database:
    5. CREATE TABLE investors
(
    LastName varchar(50),
    FirstName varchar(50),
    MoneyToInvest varchar(50),
    PhoneNumber varchar(10)
);
  5. Save this information into a file: /home/cis165/investors.txt
    6. 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
  6. To insert the data into MySQL, use the following SQL command:
    7. LOAD DATA INFILE '/home/cis165/investors.txt'
  INTO TABLE investors;
  7. If you get an error, check and correct your file permissions
    8. chmod 755 .
chmod 755 investors.txt
  8. Look at the data you just entered:
    9. SELECT * FROM Investors;
  9. Copy and paste the results of this query into your exercise5.txt file.

^ top

5.2: Indexing and Foreign Keys

Objectives

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

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
  • 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 other books by some author?
  • You cannot sort two ways at once
  • 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 an index is not used, the database engine executes a table scan
    • The database engine must start with first record
    • It reads through every entry in the table until it finds the rows it needs
  • If an index is used, data can be located in log(n) steps
    • Maximum number of read operations drops logarithmically
  • For a table with 1000 rows, this is at least 100 times faster than using a table scan

MySQL Indexes

  • MySQL supports indexes defined on any column in a table
  • Tables with no indexes have data rows that are not stored in any particular order
  • This structure is sometimes called a heap
  • Depending on the table type, MySQL can have both clustered and non-clustered indexes

Clustered Indexes

  • Clustered indexes sort and store the data rows based on their primary-key values
  • Data rows are stored in order sorted by the primary index key
  • There can be only one primary key, and thus one clustered index, per table

NonClustered Indexes

  • Nonclustered indexes have a structure completely separate from the data rows
  • Nonclustered indexes are stored in a B-Tree
  • The first entry in the index is called the root node
  • Each branch node contains a list of ordered values and pointers to the next (child) node
  • Nodes with no children are called leaf nodes
  • 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:

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

Further 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
    2. SELECT *
FROM Customer
WHERE CustomerID = 12345
  2. Queries that use a range of values to return rows
    3. SELECT *
FROM Orders
WHERE CreateDate > '10/1/2001'
AND CreateDate < '11/15/2001'
  3. Queries that use a join operation
    4. 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

Further 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

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, the database would not need to find the data in the actual table
  • The database would just use the data stored in the tree leaves

Narrow vs. Wide Indexes

  • 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

^ 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 15 keys and a total index length of 256 bytes
  • 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
  • You can have at least 16 keys in a table
  • 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)
) TYPE=InnoDB;

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 for CREATE INDEX is:
    • DROP INDEX indexName ON tableName;
  • For example:
    • DROP INDEX SupplierNameIdx on Suppliers;

Further Information

^ top

5.2.5: Foreign Key Constraints

  • Primary keys and foreign keys work together to form relationships between tables
  • Foreign key in one table points to a primary key in another table
  • You can set up a foreign key constraint between the tables
    • Constraints generate error messages if data does not meet requirements
    • 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 constraints are supported in the InnoDB table type
  • Requirements for creating a foreign key constraint include:
    • Both tables must be the InnoDB table type
    • Both the foreign key and referenced key must be indexed
    • Columns in the foreign key and the referenced key must have similar data types
    • Size and the signedness of integer types has to be the same
    • Length of string types need not be the same
  • Syntax of 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
  • Can also 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]
  • Can also list foreign key constraints:
    • SHOW TABLE STATUS
  FROM databaseName
  LIKE 'tableName'
  • Will explore foreign key relationships in the next exercise

Further 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
  • Often convenient to add indexes after a table is created
  • Indexes should be added sparingly
    • Requires more storage space
    • Makes an INSERT slower
  • Can create and drop indexes using SQL or phpMyAdmin
  • Indexes are required before implementing foreign key constraints
  • At this time, foreign key constraints are supported by InnoDB tables

^ top

Exercise 5.2

In this exercise, we look at indexes and foreign keys. Note that not every version of MySQL has InnoDB installed. To check if your version of MySQL has InnoDB installed, you can use the following SQL query:

SHOW VARIABLES LIKE 'have_innodb'

This will produce a result set like:

Variable_name Value
 have_innodb  DISABLED

If your database installation does not have InnoDB installed, you will need to change the table type in the following CREATE TABLE statements to another type such as MyISAM.

TYPE=MyISAM;

Specifications

  1. Label this exercise: Exercise 5.2
  2. Create a new database named Doctors using phpMyAdmin
  3. Select the SQL tab
  4. Enter the following code to create a new table named Patients
    5. DROP TABLE IF EXISTS `patients`;
CREATE TABLE Patients (
  ID int unsigned NOT NULL auto_increment PRIMARY KEY,
  Name varchar(50),
  Address varchar(100)
) TYPE=InnoDB;
  5. Verify the table was created successfully
  6. Enter the following data into Patients table by executing the SQL statement
    7. INSERT INTO Patients VALUES
(NULL,'James Bond','221A Baker St.'),
(NULL,'John Jones','123 Lake St'),
(NULL,'Issac Newton','2345 Apple St.');
  7. Use the following code to create a new table named Visits
    8. DROP TABLE IF EXISTS `visits`;
Create table Visits (
  ID int unsigned NOT NULL auto_increment PRIMARY KEY,
  PatientID int unsigned NOT NULL,
  VisitDate datetime,
  INDEX patidx (PatientID),
  FOREIGN KEY (PatientID) REFERENCES Patients(ID)
    ON DELETE CASCADE
    ON UPDATE CASCADE
) TYPE=InnoDB;
  8. Enter some data into your Visits table
    9. INSERT INTO Visits VALUES
(NULL,1,'99/1/1'),
(NULL,1,'01/12/25'),
(NULL,2,'97/12/30'),
(NULL,3,'99/1/1'),
(NULL,3,'99/1/1');
  9. Assuming we are using InnoDB table types, if we try to insert a patient who does not exist we get an error like:
    10. INSERT INTO Visits VALUES (NULL,4,'99/1/1');

-> ERROR 1216: Cannot add or update a child row:
   a foreign key constraint fails
  10. Write one quiz question about the material just covered and save it in your exercise5.txt file

^ top

5.3: Grouped Queries and Aggregate Functions

Objectives

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 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

    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;

Further Information

^ top

5.3.2: Grouping Data

Performing Calculations

  • You can perform arithmetic on the columns if they contain numerical data
    • For Example:

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

    SELECT *, (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 OrderID

Grouping Common Values

  • Grouping creates groups of rows that have common values
  • The SELECT command performs any calculations for the entire group
    • SELECT columnNames FROM tableName
[WHERE criteria]
GROUP BY groupFieldList
[HAVING searchCriteria]

Using GROUP BY

  • The GROUP BY command allows grouping data by common column values
  • You can use it for arithmetic if desired
    • For Example:

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

  • To obtain the total dollar value, you must:
    • Multiply price times quantity for every item
    • Then add these results for each item in the order
    • Repeating the 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 search criteria
  • The HAVING clause operates like the WHERE clause, but on the rows returned after grouping
  • Thus, we can 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.

    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

  • Following query uses several features already discussed
  • Illustrates each major clause in SELECT statement
  • Also shows order in which these clauses generally must appear
    • For Example

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

    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 use a column alias as discussed in Lesson 2.3.4

^ top

5.3.4: Summary

  • SQL aggregation functions allow you to calculate statistics
  • Can use the GROUP BY clause to group records
  • HAVING clause allows restricting records after grouping

^ top

Exercise 5.3

In this exercise we aggregate data in a query.

  1. Label this exercise: Exercise 5.3
  2. Write a query that lists the supplier number, supplier name and average selling price of the products sold by every supplier.

^ top

5.4: Other MySQL Functions

Objectives

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
  • MySQL includes several useful functions beyond those used in GROUP BY
  • Some of the more useful functions described in this section

^ top

5.4.1: Control-Flow Functions

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);

Further 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

Further Information

^ top

5.4.3: Numeric Functions

  • Mathematical functions manipulate numbers
  • MySQL provides 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);

Further 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;

Further Information

^ top

5.4.5: Formatting the Date and Time

  • Two additional date and time functions 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;

Further Information

^ top

5.4.6: Encryption Functions

  • MySQL has several encryption and decryption functions
  • Some encryption functions work only one way
    • Can encrypt but cannot decrypt
  • Great safety feature for stored passwords
    • Can never retrieve a password in readable form
    • 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';

Further 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

Further Information

^ top

Exercise 5.4

In this exercise we explore storing encrypted data in tables, using the LAST_USER_ID() function and saving SQL data in variables.

Specifications

  1. Label this exercise: Exercise 5.4
  2. If not already created, create a new users2 table in Artzy
    3. DROP TABLE IF EXISTS users2;
CREATE TABLE users2 (
  userid INT UNSIGNED NOT NULL AUTO_INCREMENT,
  userpwd char(40),
  username varchar(16),
  PRIMARY KEY(userid)
);
  3. Select the new users2 table in phpMyAdmin
  4. Execute the following SQL commands in one session on the users2 table
    5. INSERT INTO users2
  VALUES(NULL, MD5('password'), 'Wilma');
SET @lastID = LAST_INSERT_ID();
INSERT INTO users2
  VALUES(NULL, ENCODE('password', 'salt'), 'Betty');
SELECT @lastID;

    Note that we are performing these queries in one session because user variables disappear when the client session ends. Each set of phpMyAdmin queries is a new session because of the way web-based applications work.

    Also note the value of the @lastID column. The value it returns is the value of the first user entered, which is Wilma in this case.

    The four statements executed are:

    1. The first query inserts Wilma into the users2 table using MD5 encryption for the password
      2. INSERT INTO users2
  VALUES(NULL, MD5('password'), 'Wilma');
    2. The second command saves the AUTO_INCREMENT value of Wilma's insertion into a variable name @lastID.
      3. SET @lastID = LAST_INSERT_ID();
    3. The next command inserts Betty into the users2 table using the ENCODE function.
      4. INSERT INTO users2
  VALUES(NULL, ENCODE('password', 'salt'), 'Betty');
    4. The last command retrieves the AUTO_INCREMENT value of Wilma's insertion. 
      5. SELECT @lastID;

      You have to retrieve a saved value because the LAST_INSERT_ID() function would return Betty's primary key and not Wilma's.

  5. Select the contents of the users2 table and copy the table of data that phpMyAdmin shows using the following query into your exercise5.txt file.
    6. SELECT * FROM users2
  6. Note the values stored for both Wilma's and Betty's password. Which looks like the more secure encryption to you? Record the answer to this question into your exercise5.txt file.
  7. When finished, drop the table
    8. DROP TABLE users2;

^ top

Wrap Up

^ top

Home | WebCT | Announcements | Course info | Expectations | Schedule
Project | Help | FAQ's | HowTo's | Links

Last Updated: March 08 2006 @15:41:15