The other day, I discovered a minor inconvenience in the way MS Access handles the “Yes/No” (or boolean) data type.

You should be aware, when working with MS Access, that any “Yes/No” field in your table can only accept boolean true and boolean false (or “true” and “false”, “yes” and “no”, 1 and 0, etc.). It will not accept NULL.

Standard programming practice is, when there is not a “default” value for a field, to insert NULL if the user does not explicitly declare a value. Unfortunately, the fact that Access throws an error if you try to insert NULL into a “Yes/No” field really puts a damper on that logic.

As the title says, this is just a quick warning for people that may be using MS Access for their databases. I’m not sure if the same is true for MS SQL or not, but it’s obviously a non-factor with MySQL, since MySQL doesn’t offer a boolean data type.

AJAX or Asynchronous JavaScript and XML, is an innovative way of using existing technologies to create highly interactive web applications. AJAX allows portions of the page to be updated without having to refresh and reload the entire page. It can increase site performance significantly and provide cutting edge user interfaces. Unfortunately it can also be a source of concern for delivering fully accessible web sites.

(more…)

I’ve seen a lot of articles on the Internet explaining how to export a page to Excel using VBScript. Unfortunately, they all seem to have problems in their methodology.

Many of the articles are helpful, but they don’t seem to include all of the information you need in order to export HTML content successfully from a VBScript page into an Excel document.

I’ve finally found a method to successfully export my data to Excel, and I hope it will help some of you, too.

(more…)

As many of you are aware, it is rather difficult to create an accessible “quick link” menu (sometimes also called “jump menus”) because most of them require javascript.

For those of you that don’t know what a quick link menu is, it’s generally a dropdown menu, made from a typical form “select” element. Then, when a user chooses one of the options within that select element, the page automatically redirects to the location associated with that option.

For instance, in the top corner of all HTMLCenter pages, there is a quick link menu called “Top Links”.

For a lot of users, these types of menus are extremely useful. Designers generally use quick link menus for one of two purposes: a) to provide a quick, easily locatable list of the top content on their Web site or b) to provide a list of the headers on a long page, allowing the users to quickly “jump” to the appropriate section of the page.

The problem, though, as I mentioned at the beginning of this post, is that they all require javascript in order to work properly. That’s great for the users that have javascript enabled, but it really can cause problems for those that don’t have javascript.

I have come up with a solution to that problem, though. With the solution described below, the “quick link” menu is initially written as an unordered list. Then, I use javascript to convert it to a standard “jump menu”. That way, if the user doesn’t have javascript within his/her browser, they get a nicely formatted unordered list of the links instead.

(more…)

My supervisor at work e-mailed a nice accessibility resource a few weeks ago. The “Division of Instructional Technology” at the University of Wisconsin has put together a good collection of videos and podcasts related to Web accessibility. The in-house videos are narrated and led by a blind man named Neal Ewers, who works for the Trace Research and Development Center at the University of Wisconsin, Madison.

The videos are extremely interesting to watch, and very informative. If you have considered improving your Web site’s accessibility, I highly recommend visiting the site and watching each of the videos.

Last week at work, we unveiled one of the new Web sites I’ve been working on for a while.  In doing so, we removed the old Web site completely from the server and replaced it with the new site.

Of course, when we did so, a lot of our old links became obsolete, so I had to find an effective solution to keep our regular visitors from going bananas when they tried to access some of their favorite pages.  That’s when I began to look into some options for redirecting without having to create placeholder pages just for those redirects.  After quite a few fruitless searches, I was able to piece together a fairly good solution, and also discovered an easy way to achieve URL rewriting on an IIS server (similar to the way you would with htaccess on an apache server).

First, you need to set up your “404″ so that it points to a VBScript page.  That’s the most important part.  Once you’ve got that done, navigate to a page that you know does not exist on your server, just so you can test the behavior of your 404 page.

(more…)

On day 5, we went through all of the rest of the material in the course. By the end of the day, we had been provided all of the information we should need in order to pass both MySQL Developer examinations. My notes from the final day of class follow.

Day 5

DECLARE statement

• The declare statement defines items local to a routine
  • Local variables
  • Conditions and handlers
  • Cursors
• Declare only allowed inside a BEGIN…END
• Declarations must follow a specific order
  • Variables
  • Conditions - error handler - captures errors that occur in the procedure/function and determines how they should be handled
    • DECLARE condition_name CONDITION FOR condition_value;
    • SQLSTATE Condition value
      • DECLARE null_not_allowed CONDITION FOR SQLSTATE ‘23000′;
      • MySQL errors are strings, so they need to be quoted
    • Continue handler
      • DECLARE CONTINUE HANDLER FOR SQLSTATE ‘23000′ SET @x=1;
      • A continue handler allows the procedure to continue even if the condition is met
    • Exit handler
      • An exit handler will stop the procedure/function when the condition is met (an error is generated)
  • Cursors
    • A control structure within stored routines for record retrieval
      • One row at a time
    • A cursor is basically a pointer pointing to the current row we are fetching
    • Cursors are mostly used within loops that fetch and process the rows
    • Asensitive - MySQL decides how to process the information that’s fetched
    • Read-only
    • Non-scrolling
      • The cursor strictly moves from one row to the next
      • You cannot skip records
      • You cannot go backwards
    • Cursors must be declared before declaring handlers
    • OPEN opens a previously declared cursor
    • FETCH
      • obtains the next row using the specified open cursor, and advances the cursor pointer
      • When there is no next row, an error will result
        • You would use that error as a condition to break out of the loop
    • A cursor is destroyed automatically at the end of the BEGIN…END block
    • If you choose to close the cursor ahead of time, you can use the CLOSE command
    • Prepared statements can only have one cursor, and it is not named
  • Handlers

Triggers

• Named database objects
• Activated when

Creating triggers

• CREATE TRIGGER trigger_name { BEFORE | AFTER }
  { INSERT | UPDATE | DELETE }
  ON table_name
  FOR EACH ROW
  triggered_statement
• CREATE TRIGGER City_AD AFTER DELETE ON City
  FOR EACH ROW
  INSERT INTO DeletedCity (ID, Name) VALUES (OLD.ID, OLD.Name);
• OLD and NEW are keywords that can be used within triggers

Trigger error handling

MySQL handles errors during trigger execution as follows:

• Failed BEFORE triggers
  • Operation on corresponding row is not performed
• After trigger execution
  • BEFORE trigger events and the row operation will execute successfully in non-transactional tables
  • In transactional tables, a rollback of all changes made by the statement will occur

Disallowed statements

• SQL prepared statements
• Explicit or implicit COMMIT or ROLLBACK
• Return a result set
• FLUSH
• Recursive - cannot modify a table that is in use (reading or writing)
  • These types of things must be taken care of in BEFORE statements, rather than AFTER statements, because the table will not yet be open at that point

Storage Engines

SQL Parser and storage engines

• Client sends requests to the server as SQL
• Two-tier processing
  • Upper tier includes SQL parser and optimizer
  • Lower tier comprises a set of storage engines
• SQL itself is not engine-specific, generally
  • Some exceptions include COMMIT, ROLLBACK, CREATE TABLE (which needs an engine declaration and can allow foreign key information for transactional tables), etc.
• Steps to parse and return
  • Parse
  • Optimize
  • Retrieve/Store
    • This is where things like ORDER BY, GROUP BY, etc. are performed
• Storage medium
• Transactional capabilities
  • InnoDB allows transactions while MyISAM does not
• Locking
  • InnoDB locks on a row-by-row basis
  • MyISAM locks on a table-by-table basis
• Backup and recovery
  • MyISAM allows you to physically copy individual tables, etc. from one place in your filesystem to another place
  • InnoDB stores its information for all databases on the server in a single file, so it is more difficult to copy individual items
• Optimization
• Special features
• MySQL server operates same for all storage engines
  • SQL commands independent of engine

Available Storage Engines

• MySQL provides and maintains several storage engines
• Also compatible with many third party engines
• MySQL developed:
  • MyISAM
    • Incredibly fast - even more so when you use compression, fixed-width columns and good indexing
    • Data stored in table
    • Table-level locking
    • Does not support transactions
  • Falcon
  • NDB/Cluster
  • Memory
    • Data is in memory
    • Fastest engine that exists, but nothing is stored anywhere on disk
  • Archive
    • Highly compressed
    • Only supports INSERT and queries
  • Federated
    • creates an alias of an existing database on a separate server
  • Blackhole
  • CSV
  • Example - doesn’t do anything, but is in the code to show people a template of how to create an engine
• Third party engines
  • InnoDB
    • Transactional
    • Foreign key constraints

• • • Row-level locking
    • Backups
  • solidDB
  • InfoBright BrightHouse
  • Nitro
  • PBXT

You can set the storage engine when creating a table, but you are not required to do so. If you do not specify the engine, it will be set to the database default

MyISAM Storage Engine

• MyISAM is the MySQL default
• Manages tables with specific characteristics
  • Represented by three files
  • Most flexible AUTO_INCREMENT
  • Fast, compressed, read-only tables save space
  • Manages contention between queries
    • By default, writing to disk takes precedence over reading from disk, but that can be overridden
    • SELECT statements can be set to a higher priority with the HIGH PRIORITY switch
    • UPDATE, DELETE and INSERT statements can be set to a lower priority with LOW PRIORITY
    • INSERT statements can also be set to an extremely low priority with DELAYED
  • Portable storage format
  • Specify number rows for a table
  • Disable updating of non-unique indexes and enable the indexes
    • DISABLE KEYS/ENABLE KEYS
    • Indexes of non-unique keys can be disabled so that the indexes are not re-created each time a row is changed. The indexes will be rebuilt when the keys are reenabled
  • Tables take up very little space
• Three row storage formats
  • Fixed-row format
  • Dynamic row format
  • Compressed format
    • Tables must be deliberately compressed
    • Compressed tables are read-only
      • Tables must be decompressed to modify
    • Using the myisampack utility
      • Includes a mixture of “True” compression and a set of optimizations
      • Each record compressed separately with small cost to decompress
      • Must be performed on the host machine
    • Use myisamchk afterward to update the indexes
    • Always backup tables prior to running utilities
• Table-level locking
• Acquiring locks
• Tables with no holes support concurrent inserts
  • If a table is completely defragmented, any number of inserts can all be performed at the same time
• Tables with holes do not support concurrent inserts by default
• Can change priority of statements that retrieve or modify data
• Write request not processed until current readers finished

InnoDB Storage Engine

• Manages tables with specific characteristics
  • Represented on disk by a .frm format file as well as data and index storage
  • Supports transactions
  • ACID compliant
    • Satisfies ACID conditions
    • General locking properties
      • Does not need to set locks to achieve consistent reads
      • Uses row-level locking per concurrency properties
      • May acquire row locks as necessary to improve concurrency
      • Deadlock is possible
    • Supports two locking modifiers
    • Repeatable Read isolation level allows modifiers
  • Auto-recovery after crash
  • MVCC and non-locking reads
  • Supports foreign keys and referential integrity
  • Supports consistent and online logical backup
• Tablespace for storing all table contents together
• Log files for recording transaction activity
• Format file (.frm)
• Logical storage area can contain multiple files
• Table-specific file (.ibd)
  • –innodb-file-per-table
• Manages InnoDB-specific log files
• Log files used for auto-recovery

Memory Engine

• Uses tables stored in memory
  • Tables are temporary
• Must have fixed-length rows
• Manages tables with specific characteristics
• Formerly HEAP engine
• Memory indexing options
  • Uses HASH indexes by default
  • BTREE is preferable for some operators
    • Ranges

Optimization

• Server processes queries more efficiently and performs better
• Optimization strategies
  • Use indexing properly
    • Large tables require indexing for efficiency
    • Benefits of indexes
      • Contain sorted values
      • Use less disk I/O
      • Enforce uniqueness constraints
    • Downsides of indexing
      • Any time anything in the table is changed, the indexes have to be rebuilt
      • Can slow down some data manipulations
      • Uses additional space
    • Types of indexes
      • Three general types
        • Primary key
        • Unique
          • Allows NULLs, but other than that requires the value of the cell to be different than anything else in the column
        • Non-unique
          • Allows NULLs and allows duplicate entries
    • Primary versus unique
      • Primary cannot contain NULL
      • Only one primary key is allowed per table
    • Adding indexes
      • ALTER TABLE table_name ADD PRIMARY KEY(column_name);
      • CREATE INDEX index_name ON table_name(column_name);
    • Removing indexes
      • ALTER TABLE table_name DROP PRIMARY KEY;
      • ALTER TABLE table_name DROP INDEX index_name;
      • DROP INDEX index_name ON table_name;
      • DROP INDEX `PRIMARY` ON table_name;
        • PRIMARY must be contained within back tick quotes, because it is a name. If it is not within back ticks, it will be treated as a keyword that doesn’t belong in that syntax
        • PRIMARY is the official name of the primary key in a table
    • Using index prefixes
      • Several column types
      • Use only specified, leading part of column values
      • Can make the index work faster, because it only searches a specific portion of the column value
      • CREATE INDEX part_of_name ON customer (name(10));
      • Can be used on composite indexes, as well
        • INDEX (column_name1(15),column_name2(10))
    • You can show index information by using the SHOW INDEX command
      • Full text indexes
        • Only useful with MyISAM tables
        • Definition can be given with CREATE TABLE, ALTER TABLE, CREATE INDEX
        • Create index after table creation for large datasets
        • Use MATCH()…AGAINST() syntxt
        • SELECT title FROM books WHERE MATCH(title) AGAINST (’prince potter’)
  • Well-written queries
    • Using EXPLAIN to determine query processing
    • Returns useful information
      • Shows if index is required
      • Shows if index is being used
      • Analyzes query rewrites
    • EXPLAIN works with SELECT queries
    • EXPLAIN for JOINs
      • Joins tend to increase amount of server processing
      • EXPLAIN can help reduce server impact
      • The type column indicates the join type
      • type column output
        • system
        • const
        • eq_ref
        • ref
        • ref_or_null
        • index_merge
        • unique_subquery
        • index_subquery
        • range
        • index
        • ALL
    • The extra column (page 21-22)
      • using index
      • where used
      • distinct
      • not exists
    • Inefficient query “extra” output
      • using filesort
      • using temporary
      • range checked for each record
    • Indicators of worst performance
      • using filesort
      • using temporary
    • Rewrite query and run EXPLAIN again
    • Efficiency principles
      • No indexed columns within an expression
      • Beneficial for joins that compare columns from two tables
      • Use same value as column data type
        • WHERE id=18 (if column is a number)
        • WHERE id=’18′ (if column includes strings)
      • Pattern matching
        • WHERE name LIKE ‘de%’
        • WHERE name >= ‘de’ AND name < ‘df’ - not as good as example above
        • WHERE name LIKE ‘%de’ - full table scan
      • Rewrite using a trigger that maintains an additional column
        • WHERE LENGTH(column)=5 - bad
        • WHERE column_length=5 - column_length is a separate column that’s been automatically generated to show the value that would be returned by LENGTH(column)
      • Reduce amount of output a query produces
        • Use the LIMIT clause
          • Reduces information going over the network
          • Allows server to terminate query processing earlier
        • Use the WHERE clause
        • More improvement with index or column
    • Techniques for updating tables
      • Use DELETE and UPDATE same as SELECT
      • Multi-row INSERT
      • INSERT INTO t (id, name) VALUES (1,’Bea’),(2,’Belle’),(3,’Bernice’);
      • Better performance with a transaction
      • LOAD DATA INFILE faster than multi-row INSERT
        • Even faster with MyISAM if you disable keys before running the file and enabling them when finished
      • Use REPLACE rather than DELETE plus INSERT
  • Generating summary tables
    • Select records to generate summaries
    • Summary table strategy
    • Several benefits to this strategy
    • Table-locking table will be available more
      • The use of the temporary table will not affect the original table from which it was derived
    • Consider making a memory table
    • CREATE TABLE ContinentGNP
      SELECT Continent, AVG(GNP) AS AvgGnp
      FROM Country GROUP BY Continent
    • Disadvantages
      • Values are only good until changed
      • Storing data twice
  • Choose best matching storage engine
    • Decide query types during table creation
    • Choose storage engine with locking level needed
    • InnoDB good for a mix of retrievals and updates
    • MyISAM table structure dependent on the higher priority between speed or disk
      • Choose fixed-length or variable-length according to needs
      • Use read-only tables if possible
    • CHAR columns take more space than VARCHAR
      • considerably faster in MyISAM, but CHAR is no different (in speed) than VARCHAR in InnoDB
    • Use Memory for temporary information

* Within a MyISAM table, you can temporarily disable all non-unique keys so that new indexes will not be built while they are turned off. Use the statement ALTER TABLE table_name DISABLE KEYS. You can turn them back on with ALTER TABLE table_name ENABLE KEYS.

On day 4, we continued where we had left off on day 3. We covered subqueries, views, prepared statements, importing and exporting data, stored routines and some of the basic functions that are available within stored routines. My notes from day 4 follow.

Day 4

Subqueries

Subqueries are the reason behind the name “structured query language,” as subqueries are “structured queries”.

• A subquery is a query nested inside another query
• Enclosed inside parentheses in order to specify that the subquery should be completed first
• SELECT Language
  FROM CountryLanguage
  WHERE CountryCode = (
  SELECT Code
  FROM Country
  WHERE Name=’Finland’);

Why use a subquery?

Result Table Types:

• Scalar - Single row with a single column
• Row - Single row with one or more columns
• Column - Single column with one or more rows
• Table - One or more rows with one or more columns
• Empty - No row or column data

Correlated and non-correlated

• Correlated
  • References outer query
  • Cannot stand alone
  • Tend to be inefficient from a time standpoint, as the engine has to go back and forth between the inner and outer query
  • Tend to be more RAM efficient than JOINs, because it does not have to store a separate memory table from which to pull the final results
  • SELECT Country.Name
    (SELECT COUNT(*) FROM City
    WHERE CountryCode=Country.Code)
    AS CityCount FROM Country;
• Non-correlated
  • Does not reference outer query
  • Can stand alone
  • Ideally, a non-correlated subequery would only be read once
  • SELECT Name FROM City WHERE CountryCode IN
    (SELECT Code FROM Country
    WHERE Continent=’Oceania’);

Subquery placement

• If you place a subquery in the “column” portion of the query, then you can return and use only a scalar result
  • SELECT name,
    (SELECT MAX(Population) FROM City
    WHERE City.CountryCode = Country.Code)
    AS LargestCity
    FROM Country LIMIT 8;
• Inside the FROM clause, you could return and use a table result, which can also include column, row and scalar results
  • Every column must have a name
  • Subquery can be a table query
    • Even if all values are not used
  • The following example is not something we could return with a JOIN
    • SELECT AVG(cont_sum)
      FROM (SELECT Continent, SUM(Population) AS ContinentSum
      FROM Country
      GROUP BY Continent)
      AS t;
  • Any time you have a subquery in a FROM clause, you must alias the created table, even if you don’t use that alias name anywhere else in the query
• In the WHERE clause, when using an “equals” comparison, you can return and use scalar or row results
  • In order to use row results, you would have to use a tupple
    • (SELECT x, y FROM mytable WHERE key = 5) = (value, value)
    • (x, y) = (z, a)
      • Either or both sides of the tupple equation can be subqueries, themselves
  • Compares outer query with subquery results to see if they are the same
  • SELECT Continent, Name, Population
    FROM Country c
    WHERE Population = (SELECT MAX(Population)
    FROM Country c2
    WHERE c.Continent = c2.Continent
    AND Population > 0
    );
    • In the example above, you do not need the “> 0″, because simply stating the name of the column will evaluate as 0 or 1 (false or true)
• In the WHERE clause, when using greater than or less than comparisons, you can only use and return a scalar result
  • SELECT
• In the WHERE clause, when using IN, ALL, ANY, SOME, etc., you can use and return a table result (or any other type of result)
  • In order to use a table result, you would need to use a tupple
    • Using a tupple would look something like:
      WHERE CountryCode, Name, Population IN (SELECT Code, Name, Population FROM Country…)
    • A tupple will compare from left to right, matching the columns in the left list up with the columns in the results returned by the right set
  • SELECT Name, Population FROM City
    WHERE CountryCode IN(
    SELECT Code FROM Country
    WHERE Continent = ‘Europe’)
    ORDER BY Name LIMIT 10;
  • SELECT Name FROM City
    WHERE Population >
    ALL (SELECT Population FROM City
    WHERE CountryCode = ‘CHN’);
    • The example above will compare each result returned against all of the results returned in the subquery. If any of the results in the subquery do not match the criteria, the whole result will return false
    • The specific example above could have been optimized to read something like:
      • SELECT Name FROM City WHERE Population > (SELECT MAX(Population) FROM City WHERE CountryCode = ‘CHN’);
  • SELECT Name
    FROM Country
    WHERE Continent = ‘Europe’
    AND Code = ANY(SELECT CountryCode
    FROM CountryLanguage
    WHERE Language=’Spanish’)
    ORDER BY Name;
• In the WHERE clause, when using EXISTS, you can only return an empty result set (you are simply checking to see if you get any results or not, you are not using any information from the query itself
  • In current versions of MySQL, this type of result is not optimized - MySQL will complete the subquery before returning 0 or 1 (true or false)
  • In future versions, this type of result should be optimized, so that the engine will stop the subquery as soon as a result is returned, and will then return a 1
  • SELECT Continent FROM Country WHERE EXISTS
    (SELECT * FROM City WHERE Code = CountryCode AND Population > 8000000)
    GROUP BY Continent;
    • Will return a list of each continent that contains at least one country with a population over 8000000
  • SELECT Continent FROM Country WHERE NOT EXISTS
    (SELECT * FROM City WHERE Code = CountryCode AND Population > 8000000)
    GROUP BY Continent;
    • Is not the opposite of the query above
    • Will return a list of each continent that contains at least one country with a population under 8000000

Other Subquery Uses

Using Subqueries in UPDATE and DELETE statements

You can use subqueries to locate rows on which you want to perform updates or you want to delete

• Subqueries are not limited to SELECT statements
• They can be used in DELETE and UPDATE statements
  • DELETE FROM City WHERE CountryCode IN (
    SELECT Code FROM Country WHERE LifeExpectancy < 70.0);
• Generally, when dealing with DELETE and UPDATE statements, the subquery is used within the WHERE clause

Converting subqueries to JOINs

• Joins can be more efficient than subqueries
  • Convert a subquery to a join if the subquery is running slowly
• Subquery versus JOIN
  • Subquery:
    • SELECT Name FROM Country
      WHERE Code IN (
      SELECT CountryCode FROM CountryLanguage);
  • JOIN:
    • SELECT DISTINCT Name FROM Country
      JOIN CountryLanguage ON Code=CountryCode;

Views

• Database object defined in terms of a SELECT statement
  • SELECT statement masquerading as a table
  • Will stay in the database until it is destroyed
• Selected from base tables or views
• Updatable
• Views are nestable

CREATE VIEW statement

• Define a view
• General syntax:
  • http://dev.mysql.com/doc/refman/5.0/en/create-view.html
  • CREATE VIEW view_name AS select_statement
    • CREATE VIEW CityView AS SELECT ID, Name FROM City;
• Optional parts of a CREATE VIEW statement
  • OR REPLACE
    • CREATE OR REPLACE CityView AS SELECT ID, Name FROM City;
    • Will replace an existing view of the same name if it exists, will create a new view if not
  • ALOGRITHM = { UNDEFINED | MERGE | TEMPTABLE }
    • UNDEFINED
    • MERGE
    • TEMPTABLE (TempTable)
      • Will create a temporary table if the view is too large to fit into RAM
  • DEFINER = { user | CURRENT_USER }
  • SQL SECURITY { DEFINER | INVOKER }
    • The definer, by default, is the person who created the view
  • column_list
    • By default, if the column_list is not specified, the names of the columns in the original table will be used as the column names in the view
    • You can optionally define new column names for each of those
    • CREATE VIEW v
      AS SELECT Country.Name AS CountryName, City.Name AS CityName
      FROM Country, City WHERE CountryCode=Code;
  • WITH CHECK OPTION (see insertable views below)
    • Places constraints on allowable modifications
    • Checks the WHERE conditions for updates
      • If the update or insert statement would result in something that would not be visible in the view, then the CHECK OPTION will stop the statement from being executed successfully
    • Example:
      • CREATE VIEW LargePop AS
        SELECT Name, Population FROM Country
        WHERE Population >= 100000000
        WITH CHECK OPTION;
      • SELECT * FROM LargePop;
      • UPDATE LargePop SET Population = Population + 1 WHERE Name=’Nigeria’

It is possible to update a table through a view

• Can use UPDATE and DELETE
  • Updating and deleting information from a view will update or delete that information inside of the base table on which the view is based.
  • Information in the view does not actually exist in the view - it only exists inside the base table(s) on which the view is based
• Must be one-to-one relationship
• Updatability examples
  • CREATE VIEW EuropePop AS
    SELECT Name, Population FROM Country
    WHERE Continent = ‘Europe’;
  • UPDATE EuropePop SET Population = Population + 1 WHERE Name = ‘San Marino’;

Insertable Views

• An updateable view can be insertable
  • Must meet additional requirements
  • No duplicate view column names
  • Must contain all columns from base table without default value
  • Cannot be derived columns (calculated, etc.)
• Inserting views with AUTO_INCREMENT
  • LAST_INSERT_ID() will not work if the AUTO_INCREMENT column is not visible through the view

Checking Views

• When you define a view, the object referenced must exist
• A view can become invalid if a referenced object is removed
• Using CHECK TABLE will show that the view is no longer valid

Altering Views

• You can use ALTER VIEW to change an existing view
• Is basically the same as CREATE OR REPLACE

INFORMATION_SCHEMA

• Views table in database
• SELECT * FROM INFORMATION_SCHEMA.VIEWS
  WHERE TABLE_NAME = ‘CityView’
  AND TABLE_SCHEMA = ‘world’\G

SHOW statements

• Display Metadata
• SHOW CREATE VIEW specifically for views
• DESCRIBE
• SHOW TABLE STATUS
• SHOW TABLES
• SHOW FULL TABLES

Prepared statements

• Useful for running multiple similar queries
• Can use same structure and change data values
• Enhanced performance
  • Statement parsed only once by server
  • May require fewer conversions
  • Less traffic between server and client

Prepared statements from MySQL

• Aids in testing and debugging
• Session-bound
  • Prepared statements are tied to sessions and will be automatically destroyed when the session is closed
• User defined variables pass values from one statement to another
  • Connection specific
  • Also known as @ variables
    • variables stay in RAM until the connection is closed
    • variables will not persist across connections
  • Use SET statement to define
  • Example syntax
    • SET @var_name = expr [, @var_name = expr]…
    • SET @myvar = 12, @myothervar = 5*2
  • Expression can evaluate to an integer, real, string or NULL
  • Coercibility is implicit
    • Uses the type of the string that we supply
    • If we do not supply a type, it will inherit the charset and collation of the connection
• PREPARE my_stmt FROM
  ‘SELECT COUNT(*) FROM CountryLanguage WHERE CountryCode = ?’;
• SET @code = ‘ESP’; EXECUTE my_stmt USING @code;
• Using variables inside the prepared statement
  • You can have more than one question mark inside the prepared statement
  • They will be parsed in the order in which they appear within the statement
    • To supply multiple variables to be assigned to the multiple unknowns, you would separate the variable names with commas in the execute statement
• If you want to destroy the prepared statement before closing the connection
  • DEALLOCATE PREPARE my_stmt;
  • DROP PREPARE my_stmt;
  • You do not have to use an unknown (question mark) inside a prepared statement

Preparing a statement

PREPARE namepop FROM ‘

SELECT Name, Population
FROM Country
WHERE Code = ?

‘;

• Not all SQL statements can be prepared
• Limited to the following
  • SELECT
  • Data modification: INSERT, REPLACE, UPDATE, DELETE
  • SET, DO and many SHOW statements
  • CREATE TABLE statements
• As of MySQL 5.1, many more features were added

Exporting and Importing Data

Import Data using SQL

LOAD DATA INFILE

• Imports data into a table from a file
• Uses similar clauses and format specifiers as SELECT…INTO OUTFILE
• MySQL assumes file is located on server host
  • In database data directory
• Tab delimited or comma separated files
• Characteristics to know about input file
• CSV example:
  • LOAD DATA INFILE ‘C:/City.txt’ INTO TABLE City
    FIELDS TERMINATED BY ‘,’
    ENCLOSED BY ‘”‘
    LINES TERMINATED BY ‘\n’;
• Specifying data file location as client host
  • LOAD DATA LOCAL INFILE ‘C:/City.txt’ INTO TABLE City;
  • You have to have the proper security permissions in order to do this
• Skipping or transforming column values
  • LOAD DATA INFILE ‘C:/City.txt’ INTO TABLE City
    IGNORE 2 LINES;
• Duplicate records
  • Can control duplicate records with IGNORE and REPLACE
  • Behavior differs slightly
  • IGNORE and REPLACE are mutually exclusive
    • REPLACE will delete the original record and create a new one with the new information if it finds a duplicated key
    • IGNORE will skip the new record if its key already exists in the table
• To alter the structure of the data in order to make it fit into your table properly
  • LOAD DATA INFILE ‘C:/people.txt’ INTO TABLE People
    FIELDS TERMINATED BY ‘,’
    ENCLOSED BY ‘”‘
    LINES TERMINATED BY ‘\n’
    IGNORE 2 LINES
    (@skip, @firstname, @lastname, @date, address, email, …)
    • The items enclosed in parentheses above are in the order in which they appear within the data file that I’m loading
    • The items not preceded by @ symbols are the names of the columns in my table
    • The variable @skip in the example above is an undefined variable, which, by default, is equal to NULL
      • In this example, we are simply using that variable name to specify a column that does not need to be inserted into our table
  • SET Name = CONCAT(@lastname,’, ‘,@lastname),
    Birthdate = str_to_date(@date,’%b %e %Y’);
    • The date in the example above would have been in the format of Sep 6 2001

Export Data using SQL

SELECT with INTO OUTFILE

• Writes result set directly into a file
• MySQL assumes filepath to be in database data directory, unless otherwise specified
  • Path is relative to the host machine on which the SQL server is running
• SELECT * INTO OUTFILE ‘C:/City.txt’ FROM City;
• INTO OUTFILE Changes SELECT Operation
  • File written to server host, instead of over the network to client
  • If the file already exists, this command will generate an error rather than overwriting or appending
• Specifiers
  • Default is tab delimited with newline terminators
  • Can change specifiers for all columns
    • FIELDS TERMINATED BY ’string’
      • defines data values within a line
    • ENCLOSED BY ‘char’
    • ESCAPED BY ‘char’
    • LINES TERMINATED BY ’string’
  • Terminator definitions
    • \N - NULL
    • \O - NUL (zero) byte
    • \b - backspace
  • Line terminator specifiers
    • Newline character is the default
  • CSV format text file example
    • SELECT * INTO OUTFILE ‘C:/City.csv’
      FIELDS TERMINATED BY ‘,’
      ENCLOSED BY ‘”‘
      LINES TERMINATED BY ‘\r’
      FROM City;

Export using the mysqldump database backup client

• MySQL utility to export (dump) table contents
  • Full structure
  • Data only
  • Table structure only
  • In standard format
  • MySQL specifics for optimized speed
  • Compressed

• Three ways to invoke mysqldump
  • mysqldump [options] db_name [tables]
  • mysqldump [options] –databases db_name1 [db_name2 db_name3...]
  • mysqldump [options] –all-databases
• Can only be executed from the shell
• Need to redirect the output into a file, otherwise it just prints the dump onto the screen
  • mysqldump -uroot -p<password> world >C:/world_dump.sql
• File contains commands needed to recreate tables and data
• Export to a specific table in a database
  • mysqldump -uroot -p<password> world CountryLanguage

Import using the mysqlimport client

• MySQL utility to load data files into tables
• Command line interface to LOAD DATA INFILE
• mysqlimport options db_name input_file …
• Can only be executed from the shell
• The table the information is being imported into must have the same name as the file you are loading from
• Options
  • –help
  • –lines-terminated-by=string
  • –fields-terminated by=string…

Import data with the source command

• Import table data
  • mysql -u root world < CountryLanguage.sql
  • Path is relative to the local machine
• Loading the data
  • SOURCE C:/CountryLanguage.sql
  • Source uses a path relative to the client machine (your machine, not the server)
• The default is to not write over existing databases, unless specified inside the script file that you’re loading

Stored Routines

What is a stored routine?

• Set of SQL statements that can be stored in server
• Types
  • Stored procedures
    • A procedure is invoked using a CALL statement, and can only pass back values using output variables
    • Procedures are database-specific
  • Stored functions
    • A function can be called from inside a statement and can only return a scalar value
    • Are invoked like a normal function
• We use stored routines for the following reasons
  • Performance
  • Security
    • Minimal data access
    • Single location processing
  • Client applications
    • One application
    • One programming language
      • Instead of trying to translate your functions, etc. into each of the different languages you’re using to access the data, you can simply write it in MySQL as a stored routine
  • Function libraries
• Stored routine issues
  • Increased server load
    • Because MySQL is doing all of the work, none of it is being passed off to other sources
  • Limited development tools
    • You can only use MySQL commands within your stored routines
  • Limited functionality and speed
    • There is no way to compile routines, etc.
  • No debugging/profiling capabilities
    • You can trace by downloading a specific version of the source and compiling it yourself, but that is not recommended

Creating Stored Routines

Create procedure

• CREATE PROCEDURE procedure_name() procedure_statement
  • Single statement example:
  • CREATE PROCEDURE world_record_count ()
    SELECT ‘country count ‘,COUNT(*) FROM Country;

Create function

• CREATE FUNCTION function_name() RETURNS return_type function_statement
  • Single parameter example
    • CREATE FUNCTION ThankYou(s CHAR(20)) RETURNS CHAR(50) RETURN CONCAT(’Thank You, ‘,s,’!');

Compound statements

• Delimiter and BEGIN…END
  • DELIMITER // - sets the alias of the go command as “//”
    CREATE PROCEDURE world_record_count() - creates the procedure
    BEGIN - opens our list of commands
    SELECT ‘country count ‘, COUNT(*) FROM Country; - a statement that will be run
    SELECT ‘city count ‘, COUNT(*) FROM City; - another statement
    SELECT ‘countrylanguage count’, COUNT(*) FROM countrylanguage; - another statement
    END// - ends our list of commands and then tells the server to execute
    DELIMITER ; - sets the delimiter back to a semi-colon

Assign Variables

• DECLARE
  • Declaring
  • Scope
    • Variables declared within a function definition are only valid within that function defnition
  • DELIMITER //
    CREATE FUNCTION add_tax (total_charge FLOAT(9,2)
    RETURNS FLOAT(10,2)
    BEGIN
    DECALRE tax_rate FLOAT(3,2) DEFAULT 0.07;
    RETURN total_charge + total_charge * tax_rate;
    RETURN Fbill;
    END //
    DELIMITER ;
• SELECT … INTO
  • Global variables
    • SELECT SUM(population) FROM country INTO @worldpop;
      …is equivalent to…
      SELECT SUM(population) INTO @WorldPop FROM country;
    • SELECT SUM(population), AVG(population) FROM Country INTO @worldpop, @worldavg;
  • Local Variables
    • SELECT COUNT(*) FROM city INTO Total_Cities;
      …is equivalent to…
      SELECT COUNT(*) INTO Total_Cities FROM City;
• SET
  • The SET statement allows the user to assign a value to a user defined variable using either = or := as the assignment operator

Variable Scope

• Local Variable
• Routine parameter
• Local variable in an inner block
• Local variable in an outer block
• DELIMITER //
  CREATE PROCEDURE precedence(opal INT)
  BEGIN
  DECLARE opal INT DEFAULT 0;
  SELECT opal FROM test_table;
  BEGIN
  DECLARE opal INT DEFAULT 1;
  SELECT opal FROM test_table;
  END;
  END//
  DELIMITER ;
• Variables must be defined immediately after the BEGIN statement in the block

Parameter declarations

• Stored procedures
  • IN (Default)
    • Indicates an input parameter which is passed in from the caller to the procedure
  • OUT
    • Indicates an output parameter which is set by the procedure and passed to the caller after the procedure terminates
    • NULLs the variable when it’s passed to the function
    • Can be used to set a “success/failure” flag
  • INOUT
    • Indicates a parameter that can act as an IN and an OUT parameter
  • OUT and INOUT variables will be passed ByRef (meaning that anything that happens to the variable inside of the procedure will happen to the original variable)
• Stored functions
  • For stored functions, only IN is available

Execute Stored Routines

• Executing procedures
• Executing functions
• Implications of database association
  • You need to declare the appropriate database in which the routine is stored
  • If the database is deleted, the routine is deleted, too

Alterable stored routine characteristics

• ALTER PROCEDURE/FUNCTION
  • SQL SECURITY
    • DEFINER
    • INVOKER
  • Data use (SQL)
    • CONTAINS SQL
    • NO SQL
    • READS SQL DATA
    • MODIFIES SQL DATA
  • COMMENT

Non-alterable stored routine characteristics

• Non-alterable
  • DETERMINISTIC
    • The routine always produces the same result when invoked with a given set of input parameter values
  • NOT DETERMINISTIC (default)
    • The routines results can possibly change when invoked with a given set of input parameter values
  • LANGUAGE
    • Provides the parser with the programming language to use when parsing the stored routine created
    • This cannot change because MySQL is the only language available

Examine Stored Routines

• SHOW CREATE PROCEDURE/FUNCTION
• SHOW PROCEDURE/FUNCTION STATUS
• INFORMATION_SCHEMA.ROUTINES

Delete Stored Routines

DROP PROCEDURE [IF EXISTS] procedure_name;
DROP FUNCTION [IF EXISTS] function_name;

Flow control statements

• Statements and constructs that control order of operation execution
• Common flow controls
  • Choices
    • IF
      • IF(test condition) THEN
        …
        ELSEIF(test condition) THEN
        …
        ELSE
        …
        END IF;
      • ELSEIF needs to be all one word
    • CASE
      • CASE provides a means of developing complex conditional constructs
      • CASE works on the principle of comparing a given value with specified constants and acting upon the first constant that is matched
      • CASE case_value
        WHERE when_value THEN
        …
        ELSE
        …
        END CASE;
      • CASE
        WHEN test_condtion THEN
        …
        ELSE
        …
        END CASE
      • If none of the cases are met and there is no “else” specified, it will throw an error
      • In the first example, it is strictly a binary comparison, meaning that case-sensitivity is a factor
  • Loops
    • REPEAT
      • The repeat statement repeats and SQL statement until the search condition that is set becomes true
      • A repeat statement is always run at least once
      • Labels: Begin and End
      • begin_label: REPEAT
        …
        UNTIL test_condition
        END REPEAT end_label;
    • WHILE
      • WHILE repeats an SQL statement while a set condition is true
      • The statement list can consist of one or more statements
      • begin_label: WHILE test_condition DO
        …
        END WHILE end_label;
    • LOOP
      • The statements within the loop are repeated until the loop is exited; usually this is accomplished with a LEAVE statement
      • begin_label: LOOP
        …
        IF…THEN
        LEAVE label;
        END IF
        END LOOP end_label;
• Other Label Flow Control Constructs
  • LEAVE
    • This statement is used to exit any labeled flow control construct within a LOOP, REPEAT or WHILE statement
    • LEAVE can also be used in Labeled BEGIN…END compound statements
  • ITERATE
    • This statement simply means “do the LOOP (or REPEAT or WHILE) again”

During the second half of day 3, we began to delve into the information that will be presented on the MySQL Developer II examination. My notes from that session follow.

Day 3 - Second Half

Joins

• Combining information from multiple tables
• Three categories of joins
  • Inner join - identifies combinations of matching rows from two tables
    • SELECT * FROM m, w WHERE m.HHid = w.HHid; or SELECT * FROM m JOIN w ON m.HHid = w.HHid; will pull only matching records from each table:

    • +------+------+-------+------+
      | Name | HHid | Name  | HHid |
      +------+------+-------+------+
      | Bob  |    1 | Carol |    1 |
      | Ted  |    2 | Alice |    2 |
      +------+------+-------+------+

    • SELECT * FROM m JOIN w USING (HHid); will combine the field “HHid” from both tables and move it all the way to the left of the resultset (will only work if the reference column is named the same in both tables)

    • +------+------+-------+
      | HHid | Name | Name  |
      +------+------+-------+
      |    1 | Bob  | Carol |
      |    2 | Ted  | Alice |
      +------+------+-------+

    • SELECT * FROM m RIGHT JOIN w ON m.HHid = w.HHid WHERE m.Name IS NULL; will return all of the records from the right table (w) that do not have matches in the left table:

    • +------+------+-------+------+
      | Name | HHid | Name  | HHid |
      +------+------+-------+------+
      | NULL | NULL | Sally |    4 |
      | NULL | NULL | Ann   |    7 |
      | NULL | NULL | Linda |    5 |
      +------+------+-------+------+

  • Outer join - the same as inner join, but it also finds mismatches
    • SELECT * FROM m LEFT JOIN w ON m.HHid = w.HHid; will pull all of the information from the left table and any matching information from the right table. Any information that appears in the left table (m) that does not have a match in the right table (w) will be represented as NULL in the right side:

    • +------+------+-------+------+
      | Name | HHid | Name  | HHid |
      +------+------+-------+------+
      | Ed   |    6 | NULL  | NULL |
      | Ted  |    2 | Alice |    2 |
      | Al   |    3 | NULL  | NULL |
      | Bob  |    1 | Carol |    1 |
      +------+------+-------+------+

    • SELECT * FROM m RIGHT JOIN w ON m.HHid=w.HHid; will pull all of the information from the right table (w) and any matching information from the left table (m). Any information that appears in the right table (w) that does not have a match in the left table (m) will be represented as NULL in the left side:

    • +------+------+-------+------+
      | Name | HHid | Name  | HHid |
      +------+------+-------+------+
      | NULL | NULL | Sally |    4 |
      | Bob  |    1 | Carol |    1 |
      | Ted  |    2 | Alice |    2 |
      | NULL | NULL | Ann   |    7 |
      | NULL | NULL | Linda |    5 |
      +------+------+-------+------+

  • Cross join - combines all of the records from one table with all the records of another table
  • • SELECT * FROM m, w; will match all records in m table with all records from w table:

    • +------+------+-------+------+
      | Name | HHid | Name  | HHid |
      +------+------+-------+------+
      | Ed   |    6 | Sally |    4 |
      | Ted  |    2 | Sally |    4 |
      | Al   |    3 | Sally |    4 |
      | Bob  |    1 | Sally |    4 |
      | Ed   |    6 | Carol |    1 |
      | Ted  |    2 | Carol |    1 |
      | Al   |    3 | Carol |    1 |
      | Bob  |    1 | Carol |    1 |
      | Ed   |    6 | Alice |    2 |
      | Ted  |    2 | Alice |    2 |
      | Al   |    3 | Alice |    2 |
      | Bob  |    1 | Alice |    2 |
      | Ed   |    6 | Ann   |    7 |
      | Ted  |    2 | Ann   |    7 |
      | Al   |    3 | Ann   |    7 |
      | Bob  |    1 | Ann   |    7 |
      | Ed   |    6 | Linda |    5 |
      | Ted  |    2 | Linda |    5 |
      | Al   |    3 | Linda |    5 |
      | Bob  |    1 | Linda |    5 |
      +------+------+-------+------+

• Inner Join replaces comma separator (INNER JOIN is alias of JOIN, assuming the word “RIGHT” or “LEFT” does not precede the word “JOIN”)
• Useful clauses
  • FROM
  • ON
  • USING()
  • WHERE
    • SELECT COUNT(*), Name
      FROM CountryLanguage INNER JOIN Country
      ON CountryCode = Code
      GROUP BY Name
      HAVING COUNT(*) > 10;
• When retrieving information from multiple tables, you have to qualify any columns that might appear in multiple tables
• Self Joins
  • Joining a table to itself is ambiguous and can generate multiple errors
  • To properly perform a self-join, you need to alias your tablename:
    SELECT t1.IndepYear, t1.Name, t2.Name
    FROM Country AS t1 JOIN Country AS t2
    ON t1.IndepYear = t2=IndepYear AND t1.Name = ‘Qatar’;

Once you specify an alias for a table, you must use the alias to refer to that table from there on out within the statement

Multi-table updates and deletes

MySQL allows join syntax in UPDATE and DELETE

• UPDATE Operations
  • UPDATE t1, t2 SET t1.name=t2.name WHERE t1.id=t2.id;
• DELETE Operations
  • DELETE FROM t1 FROM t1, t2 WHERE t1.id=t2.id;
  • DELETE FROM t1 USING t1, t2 WHERE t1.id=t2.id;
• DELETE Operations that remove information from both tables
  • DELETE t1, t2 FROM t1, t2 WHERE t1.id=t2.id
  • DELETE FROM t1, t2 USING t1, t2 WHERE t1.id=t2.id;

During the first half of day 3, we finished up the material that will be present on the MySQL Developer I examination. I’ve included my notes below:

Day 3

Manipulating Table Structure

Creating Tables

You can create tables from existing tables by adding “SELECT” OR “LIKE” queries to your CREATE TABLE statement.

Some examples:

• CREATE TABLE `myTable` SELECT * FROM `myExistingTable`;
  • This will create a duplicate of the data in the existing table, but will not copy additional properties of the table, indices, etc. It will copy the column names, data types, etc., though.
• CREATE TABLE `myTable` SELECT * FROM `myExistingTable` WHERE Population > 2000000;
  • This will create a duplicate of the existing table (with the same caveats listed above), but will only populate it with specific data that is pulled from the SELECT query
• CREATE TABLE `myTable` SELECT * FROM `myExistingTable` WHERE 0;
  • This will create a duplicate of the existing table without populating any data. Again, it will not copy additional properties, etc.
• CREATE TABLE `myTable` SELECT `col1`, `col2` FROM `myExistingTable`;
  • This will create a new table using only the columns that are requested in the SELECT query and will populate the table
  • You can specify “table-wide” features in the CREATE TABLE statement (ENGINE, CHARSET, COLLATE, etc., but cannot specify column-specific information, like PRIMARY KEY, INDEX, etc.)
• CREATE TABLE `myTable` LIKE `myExistingTable`;
  • This will create an exact duplicate of the structure of your existing table
  • Foreign key constraints are not copied with LIKE statements

Temporary Tables

You can create a temporary table for temporary use.

• Temporary tables are only available during the current session. They will be removed from the server when your session is terminated for any reason (time-out, server shuts down, you close your connection, etc.)
• Temporary tables are only visible to the current session
  • They do not show up in the SHOW TABLES statement
• The name of a temporary table can be the same as the name of an existing table in your database
  • Doing so will cause you not to be able to access the original table while the temporary table exists

Altering Table Structures

• You can add new columns to your table using the ADD statement
  • ALTER TABLE EU_Countries ADD Id INT(6) NOT NULL;
  • ALTER TABLE EU_Countries ADD Id INT(6) NOT NULL AFTER Name;
  • http://dev.mysql.com/doc/refman/5.0/en/alter-table.html
• You can remove columns from your table using the DROP statement
  • ALTER TABLE EU_Countries DROP Id;
• You can modify column properties
  • ALTER TABLE EU_Countries MODIFY NewPopulation BIGINT(12) NOT NULL;

You can perform multiple alterations at the same time, adding, dropping, modifying, etc. all in one statement.

If you have multiple operations to perform, you should make sure to consolidate them into a single ALTER statement, because each ALTER statement completely rebuilds the table.

During execution of an ALTER statement, a write-lock is enforced on the table, but users can still read the information.

You can also use the CHANGE clause instead of the MODIFY clause, but it has a different syntax:

http://dev.mysql.com/doc/refman/5.0/en/alter-table.html

Renaming Tables

You can rename a table with an ALTER TABLE statement, as well.

ALTER TABLE t1 RENAME to t2;

Or, you can simply use the RENAME TABLE statement:

RENAME TABLE t1 TO t2;

Using the RENAME TABLE statement, you can rename multiple tables at once:

RENAME TABLE t1 TO t2, t2 TO t1, tmp TO t2;

Removing Tables

Using the DROP command, you can remove a table:

DROP TABLE t1

You can drop multiple tables at once

Foreign Keys

A foreign key is a column in a table that points/refers to a column in a parent table

• Foreign keys should be indexed
• InnoDB engine supports foreign key constraints, protecting the integrity of those foreign keys
  • In order to take advantage of the foreign key constraints, both the parent table and the child table must use the InnoDB engine
  • CREATE TABLE CountryParent
    (Code CHAR(3) NOT NULL,
    Name CHAR(52) NOT NULL,
    PRIMARY KEY (Code)
    ) ENGINE=InnoDB;CREATE TABLE CityChild (
    ID INT NOT NULL AUTO_INCREMENT,
    Name CHAR(35) NOT NULL,
    CountryCode CHAR(3) NOT NULL,
    PRIMARY KEY(ID),
    FOREIGN KEY(CountryCode)
    REFERENCES CountryParent(Code)
    ON UPDATE CASCADE
    ON DELETE CASCADE
    ) ENGINE = InnoDB;
  • Using ON UPDATE CASCADE will cause all of the child’s information to be updated automatically if the parent’s information is updated
  • Using ON DELETE CASCADE will cause all of the child’s information to be automatically removed if the parent’s information is removed
• Both columns (the parent table’s column and the child table’s foreign key column) should have the same data type
• http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html

Manipulating Table Data

Inserting information into tables

• Using the INSERT statement, you can insert new rows into a table
  INSERT INTO table_name [(column_list)] VALUES (value_list);
• You can insert multiple rows of information with a single INSERT statement:
  INSERT INTO table_name [(column_list)] VALUES (value_list1),(value_list2),(value_list3);
• You can use a different syntax for the INSERT statement:
  INSERT INTO table_name SET column_name=’value‘, column_name=’value‘…;
• You can also insert information from one table into another table:
  INSERT INTO table2 SELECT * FROM table1;
  INSERT INTO table2 [(column_list)] SELECT [(column_list)] FROM table1;
• LAST_INSERT_ID() will retrieve the last auto_incremented id from the last insert that you performed
  • If you perform a multiple row insert, LAST_INSERT_ID() will return the first ID from your insert statement, rather than retrieving the highest value

Removing information from tables

• Using the DELETE statement, you can remove existing rows from a table
• DELETE FROM table_name [WHERE where_condition][ORDER BY...][LIMIT row_count];

Altering information in tables

• Using the UPDATE statement, you can change the information in specific rows
• UPDATE table_name SET column_name = column_value [WHERE...] [ORDER BY...] [LIMIT...];
• You can use the REPLACE INTO command to perform updates and inserts at the same time if you name a PRIMARY KEY or UNIQUE element within your column list
  • If an item specified in your values already exists as a unique item in your table, that row will be deleted and a new one will be created with the new information you supply
    • It will not use any of the information that was in the previous version of the row. It will only insert the information you supply
  • If no match is found, it will insert a new row
• INSERT with ON DUPLICATE KEY command
  • http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

Emptying a table

TRUNCATE TABLE will efficiently empty out a table

Transactions

What is a transaction?

• When concurrent statements are needed
• Mechanism for grouping multiple statements
  • groups multiple statements together that will all be executed together
• All or none succeed
  • If an error occurs on any of the statements within your transaction, then the entire transaction will fail
• Execute if all good
• Cancel if error or incomplete

A good example of transactions is a bank transfer

• In order to transfer funds from a checking account to a savings account, it needs to be handled as a single transaction:
  • First, you need to make sure that there are sufficient funds in the checking account
  • Next, you need to withdraw the money from the checking account
  • Finally, you need to add that money into the savings account
• If any of those actions fail, you need to void the entire transaction

You can use the SHOW ENGINES command to see which database engines support transactions

ACID

• Atomic
  • All statements execute successfully or are canceled as a unit
• Consistent
  • Database that is in a consistent state when a transaction begins, is left in a consistent state by the transaction
  • If you subtract something from one area and add it to another area, you need to be sure that the total is still the same as it was before you started
• Isolated
  • One transaction does not affect another
• Durable
  • All changes made by transaction that complete successfully are recorded properly in database
  • Changes are not lost

Transaction Commands

• START TRANSACTION
  • Begins the transaction, meaning that any statements that fall between “START TRANSACTION” and “COMMIT” will be treated as a single transaction
  • START TRANSACTION temporarily suspends AUTOCOMMIT, meaning that nothing will be committed until you explicitly commit the information
  • Is only necessary if AUTOCOMMIT is turned on
  • Can be aliased as “BEGIN” or “BEGIN WORK”
  • Whenever you use “START TRANSACTION”, it implicitly commits any items that came before
• COMMIT
  • Execute and finalize all of the statements in the transaction
• ROLLBACK
  • Cancel all of the statements that are part of the transaction
• SET AUTOCOMMIT
  • AUTOCOMMIT is, by default, set to 1
  • If AUTOCOMMIT is turned on, each statement is treated as a transaction and will be automatically committed if the statement executes successfully
    • Statements that include multiple commands will be rolled back entirely if anything within that statement fails

Isolation Levels

• Concurrent transactions can cause problems
• Storage engines implement isolation levels
  • Controls level of visibility between transactions
  • May vary per database servers
• Three common problems
  • “Dirty” read - seeing changes that have not been committed
  • Non-repeatable read
  • Phantom row - rows that have been added from another thread during your transaction
• InnoDB implements four isolation levels
  • Read uncommitted
    • You can see changes from another thread’s transaction that have not yet been committed
  • Read committed
    • You cannot see changes that have not been committed, but after it’s been committed, you will see the change, even if you have read the row during your transaction
  • Repeatable read
    • You will not see changes from others’ transactions on any rows that you have read during your transaction
  • Serializable
    • Other transactions will not affect your transaction in any way. Rows selected by one transaction cannot be changed by other transactions until the original transaction has completed
• Setting the isolation level
  • Use the –transaction-isolation option
    • SET TRANSACTION ISOLATION LEVEL
    • SET TX_ISOLATION
• MVCC - Multi-version concurrency control
  • A method of isolating sessions from each other during transactions
  • http://en.wikipedia.org/wiki/Multiversion_concurrency_control

Locking Concepts

• A locking mechanism prevents problems with concurrent data access
• Locks are managed by the server
  • Allows access to one client and locks others out
• InnoDB supports two types of locking
  • Lock in share mode - locks each row with a shared lock
    • Other people can still read the row(s), but no one (including yourself) can update anything within those rows until all locks are released
    • If you attempt to read a row by using a WHERE clause that includes something other than an index, then all rows in the table will be locked
    • SELECT * FROM Country WHERE Code=’AUS’ LOCK IN SHARE MODE\G
  • For update - locks each row with an exclusive lock
    • Other people can still read the row, but only you have permission to update the row until you release the lock
    • SELECT counter_field FROM child_codes FOR UPDATE;
      UPDATE child_codes SET counter_field = counter_field + 1;