Chapter 2

1. Chapter 2 Using Mysql to Manage Data Mysql Paul Dubios 4th edition

2. MySQL Storage Engines ARCHIVE Archival storage (no modification of rows after insertion) BLACKHOLE Engine that discards writes and returns empty reads CSV Storage in comma-separated values format EXAMPLE Example ("stub") storage engine Falcon Transactional engine FEDERATED Engine for accessing remote tables InnoDB Transactional engine with foreign keys MEMORY In-memory tables MERGE Manages collections of MyISAM tables MyISAM The default storage engine NDB The engine for MySQL Cluster Mysql Paul Dubios 4th edition

3. Database Engines • SHOW ENGINES; • Transactions column indicates whether an engine supports transactions. • XA whether an engine supports distributed transactions • Savepoints - partial transaction rollback. Mysql Paul Dubios 4th edition

4. Indexing • Index single columns or construct composite indexes that include multiple columns. • An index can be constrained to contain only unique values or allowed to contain duplicate values. • You can have more than one index on a table to help optimize different queries on the table that are based on different columns. • For string data Index a column prefix rather than the entire column Mysql Paul Dubios 4th edition

5. Indexing • FULLTEXT or SPATIAL index - you must use a MyISAM table. • Index a TEXT column, you must use MyISAM or InnoDB. • ALTER TABLE tbl_name ENGINE = InnoDB; Mysql Paul Dubios 4th edition

6. Types of Indexs • A unique index - disallows duplicate values • A regular (non-unique) index - allows duplicates • A FULLTEXT index – only MyIsam • A SPATIAL index - only with MyISAM tables for the spatial data types • A HASH index - default index type for MEMORY tables Mysql Paul Dubios 4th edition

7. Creating Indexes • CREATE TABLE tbl_name ( ... column definitions ... INDEX index_name (index_columns), UNIQUE index_name (index_columns), PRIMARY KEY (index_columns), FULLTEXT index_name (index_columns), SPATIAL index_name (index_columns), ... ); Mysql Paul Dubios 4th edition

8. Drop Index • DROP INDEX index_name ON tbl_name; Mysql Paul Dubios 4th edition

9. Alter table • ALTER TABLE tbl_name action [, action] ... ; • SHOW CREATE TABLE ; • Change a column's data type • ALTER TABLE mytbl MODIFY i MEDIUMINT UNSIGNED; • Rename a column without changing its data type • CHANGE old_namenew_name followed by the column's current definition. Mysql Paul Dubios 4th edition

10. Alter table • Convert a table to use a different storage engine • ALTER TABLE tbl_name ENGINE = engine_name; • Rename a table • ALTER TABLE tbl_name RENAME TO new_tbl_name; Mysql Paul Dubios 4th edition

11. Subqueries • SELECT * FROM score WHERE event_idIN (SELECT event_id FROM grade_event WHERE category = 'T'); Mysql Paul Dubios 4th edition

12. Subqueries • SELECT * FROM score WHERE event_id = (SELECT event_id FROM grade_event WHERE date = '2008-09-23' AND category = 'Q'); Mysql Paul Dubios 4th edition

13. Subqueries • SELECT last_name, first_name, city, state FROM president WHERE (city, state) = (SELECT city, state FROM president WHERE last_name = 'Adams' AND first_name = 'John'); Mysql Paul Dubios 4th edition

14. Subqueries • SELECT * FROM student WHERE student_idIN (SELECT student_id FROM absence); • SELECT * FROM student WHERE student_idNOT IN (SELECT student_id FROM absence); Mysql Paul Dubios 4th edition

15. Subqueries • SELECT last_name, first_name, city, state FROM president WHERE (city, state) IN (SELECT city, state FROM president WHERE last_name = 'Roosevelt'); Mysql Paul Dubios 4th edition

16. Subqueries • SELECT last_name, first_name, birth FROM president WHERE birth <= ALL (SELECT birth FROM president); • SELECT last_name, first_name, city, state FROM president WHERE (city, state) = ANY (SELECT city, state FROM president WHERE last_name = 'Roosevelt'); Mysql Paul Dubios 4th edition

17. Views • A view is a virtual table. • It acts like a table but actually contains no data. • It is defined in terms of base ("real") tables or other views and provides alternative ways to look at table data. • Used to simplify applications. Mysql Paul Dubios 4th edition

18. Views • A simple view can be nothing more than a way to select a subset of a table's columns. You DO NOT want to use SELECT *. • You define a view that retrieves only the desired columns Mysql Paul Dubios 4th edition

19. Views • CREATE VIEW vpres AS SELECTlast_name, first_name, city, state FROM president; • SELECT * FROM vpres; • SELECT * FROM vpres WHERE last_name = 'Adams'; • SELECT * FROM vpres WHERE suffix <> ''; Mysql Paul Dubios 4th edition

20. Views • CREATE VIEW vstudent AS SELECT student.student_id, name, date, score, category FROM grade_event INNER JOIN score INNER JOIN student ON grade_event.event_id = score.event_id AND score.student_id = student.student_id; Mysql Paul Dubios 4th edition

21. Views • SELECT * FROM vstudent; • SELECT * FROM vstudent WHERE name = 'emily'; Mysql Paul Dubios 4th edition

22. Delete • DELETE FROM nameoftable WHERE id > 100; Mysql Paul Dubios 4th edition

23. Update • UPDATE score SET score = score + 1 WHERE event_id = (SELECT event_id FROM grade_event WHERE date = '2008-09-23' AND category = 'Q'); Mysql Paul Dubios 4th edition

24. Transactions • A transaction is a set of SQL statements that execute as a unit and that can be canceled if necessary. • Either all the statements execute successfully, or none of them have any effect. • This is achieved through the use of commit and rollback capabilities. • If all of the statements in the transaction succeed, you commit it to record their effects permanently in the database. • If an error occurs during the transaction, you roll it back to cancel it. • Any statements executed up to that point within the transaction are undone, leaving the database in the state it was in prior to the point at which the transaction began. Mysql Paul Dubios 4th edition

25. Transactions • Commit and rollback provide the means for ensuring that halfway-done operations don't make their way into your database and leave it in a partially updated (inconsistent) state. • Example: in a financial transfer where money from one account is placed into another account. • Suppose that Bill writes a check to Bob for $100.00 and Bob cashes the check. Bill's account should be decremented by $100.00 and Bob's account incremented by the same amount • UPDATE account SET balance = balance - 100 WHERE name = 'Bill'; UPDATE account SET balance = balance + 100 WHERE name = 'Bob'; • If a crash occurs between the two statements, the operation is incomplete. Mysql Paul Dubios 4th edition

26. Transactions • The rollback capabilities of transaction support enable you to handle this situation properly by undoing the effect of the statements that executed before the error occurred. Mysql Paul Dubios 4th edition

27. Transactions • Another use for transactions is to make sure that the rows involved in an operation are not modified by other clients while you're working with them. • MySQL automatically performs locking for single SQL statements to keep clients from interfering with each other Mysql Paul Dubios 4th edition

28. Transactions • A transaction group statements into a single execution unit to prevent concurrency problems that could otherwise occur in a multiple-client environment. • Transactional systems typically are characterized as providing ACID properties. • ACID is an acronym for Atomic, Consistent, Isolated, and Durable Mysql Paul Dubios 4th edition

29. Transactions • Atomicity: The statements a transaction consists of form a logical unit. You can't have just some of them execute. • Consistency: The database is consistent before and after the transaction executes. In other words, the transaction doesn't make a mess of your database. • Isolation: One transaction has no effect on another. • Durability: When a transaction executes successfully to completion, its effects are recorded permanently in the database. Mysql Paul Dubios 4th edition