LAMPsig MySQL Class :: Class #5

LAMPsig Meetings

3rd Saturday of the Month 1-3pm
Tom Bradley Youth and Family Center
5213 West Pico Blvd.
Los Angeles, CA 90019

Monday, June 6. 2005

LAMPsig MySQL Class :: Class #5

Class #5 of the MySQL class is scheduled for this Monday, June 6 at 7:00pm at CalTek and will cover Insert and Replace Statements.

*Complete syllabus for Class #5 are continued in the further reading section.*

_Instructor:_
*David Benjamin :: david_AT_peterbenjamin_(dot)_com*

_Class Contact:_
*Sharon Lake :: sharon_AT_linuxchixla_(dot)_org*

_Lab Help:_
*Steve Glasser :: steve_AT_fpig_(dot)_net*

*_CLASS #5 SYLLABUS_*

Class 5 – (Chap 7 / MySQL-M 13.1.1, 13.1.4, 13.1.6, 13.1.9, 13.1.10 Additional references on optimization (not covered in class): 7.2.16, 7.2.17)
_10% exam material_

INSERT Statement. Primary difference between INSERT and REPLACE is how duplicate records are handled. Violations of unique key values in INSERT are ignored and not inserted, but REPLACE will first delete the record containing the duplicate value, and then insert a new record.
1. Inserting Single Record
  1. INSERT INTO table_name (column_list) VALUES (value_list);
  2. INSERT INTO table_name SET column_name1 = value1, column_name2 = value2;
  3. INSERT INTO table_name VALUES (values_list);
    - value_list must match column(s) number and column(s) order
  4. INSERT INTO table_name () VALUES ();
    - Creates a row into table_name using the default values
2. Inserting Multiple Records with a Single INSERT Statement
  1. INSERT INTO table_name (column1, column2) VALUES (value1a, value1b), (value2a, value2b);
    - MySQL will return extra information with multiple-row inserts.
      - Records: number of rows inserted
      - Duplicates: how many records were ignored because they contained duplicate unique key values.
        
        Value can be non-zero if statement includes the IGNORE keyword
      - Warnings: number of problems found in data values ... can occur if values are converted.
3. Single Record and Multiple Record are handled somewhat differently for purposed of error-handling. See Section 4.10.6, “Automatic Type Conversion and Value Clipping.”

REPLACE statement
1. Inserting Single Record
  1. REPLACE INTO table_name (column_list) VALUES(value_list);
  2. REPLACE INTO table_name SET column_name1 = value1, column_name2 = value2;
2. Inserting Multiple Records with Single REPLACE Statement
  1. REPLACE INTO table_name (column1, column2) VALUES (value1a, value1b), (value2a, value2b);
    - MySQL will return extra information with multiple-row inserts
      - Query OK, X rows affected
        
        X may be greater than the number of rows inserted as duplicate unique key rows are first deleted, and then inserted.
  2. Replaces into tables with multiple columns with unique values may cause unexpected row deletion.

UPDATE Statement. DANGER WILL ROBINSON!! Issuing an UPDATE statement without a WHERE clause updates every row in the table! As a safety you can start MySQL with the --safe-updates option
1. UPDATE table_name SET column_name = value WHERE some_expression; UPDATE table_name SET column_name1 = value1, column_name2 = value2 WHERE some_expression;
2. Using UPDATE with ORDER BY and LIMIT

Handling Illegal Values
1. Numeric: out of range values are clipped to nearest value in range
2. String: long strings are truncated to fit in column
3. Invalid values are converted to column default
4. NULL

DELETE and TRUNCATE Statement
1. DELETE FROM table_name; / DELETE FROM table_name WHERE some_expression;
  1. Can be used to either delete all the rows from a table, or just selected rows when used with a WHERE clause
  2. Usually executed more slowly than TRUNCATE
  3. Returns true row count indicating number of records deleted.
2. TRUNCATE table_name; / TRUNCATE TABLE table_name;
  1. Always completely empties a table
  2. Executes quickly
  3. Might return row count of zero rather than actual number of rows deleted
3. Using DELETE with ORDER BY and LIMIT

Recap of Some Theories: A look at transactions
1. Transactions: What are they and why do they matter A transaction is an isolated sequence of queries that can either all be saved to the database or all canceled and ignored. When a transaction is committed any changes within a transaction are made permanent. When a transaction is rolled back all changes are lost and the database reverts back to the state of the last successfully committed transaction.
2. ACID Compliance
  1. Atomicity: database modifications must follow an all or nothing rule. Each transaction is said to be atomic. If one part of the transaction fails, the entire transaction fails. It is critical that the database management system maintain the atomic nature of transactions in spite of any DBMS, operating system or hardware failure.
  2. Consistency: only valid data will be written to the database. If, for some reason, a transaction is executed that violates the database's consistency rules, the entire transaction will be rolled back and the database will be restored to a state consistent with those rules.
  3. Isolation: requires that multiple transactions occurring at the same time not impact any other execution.
  4. Durability: ensures that any transaction committed to the database will not be lost. Durability is ensured through the use of database backups and transaction logs that facilitate the restoration of committed transactions in spite of any subsequent software or hardware failures.

Posted by Sharon Lake in Classes: MySQL at 19:00 | Comments (0) | Trackbacks (0)
View as PDF: This entry | This month | Full blog

Trackbacks

Trackback specific URI for this entry

No Trackbacks

Comments

Display comments as (Linear | Threaded)

No comments

Add Comment

Name
Email
Homepage
In reply to
Comment	E-Mail addresses will not be displayed and will only be used for E-Mail notifications To prevent automated Bots from commentspamming, please enter the string you see in the image below in the appropriate input box. Your comment will only be submitted if the strings match. Please ensure that your browser supports and accepts cookies, or your comment cannot be verified correctly. Enter the string from the spam-prevention image above: BBCode format allowed
	Remember Information? Subscribe to this entry