by Curtiss   |   last updated February 28, 2008   |   programming · Software ·

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;