LAMPsig MySQL Class :: Class #3

_Instructor:_
*David Rolston :: david_AT_gizmola_(dot)_com*

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

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


*_CLASS #3 SYLLABUS_*

Class 3 – Data Definition Language.
(Chapter 4 / MySQL-M 1.2.4, 1.5.6.1, 3.3.2, 3.6.9, 11, 13.2, 13.5.4, 14, 15)
[NOTE: MySQL-M 7 covers optimization and how to use INDEXES effectively. Optimization techniques are not covered in this class.]
_20% exam material_

1. General Database and Table Properties
   1. MySQL associates each database on the server with a directory under the data directory. The directory has the same name as the database it represents. The directory contains all the files associated with the database, i.e., indexes and tables.
   2. Databases cannot be nested, i.e., one database cannot contain another.
   3. Tables consist of rows and columns. A table can be empty, i.e., it can have 0 rows,but it must have at least one column.
   4. Every table is associate with a format file in the database directory. The format file name is the same as the table name followed with a .frm extension. Depending on the storage engine MySQL might create additional files
      1. MyISAM storage engine creates data and index file(s) named tablename.MYD and tablename.MYI respectively
      2. InnoDB storage engine creates the .frm file, but stores the data and index information in an InnoDB tablespace
   
   
2. Storage Engine and Table Types
   1. MyISAM Tables
      1. represented on disk with .frm format file, an .MYD datafile and an .MYI index file
      2. most flexible AUTO-INCREMENT column handling
      3. can be set up to handle MERGE tables
      4. can be converted to compressed, read-only tables
      5. supports FULL TEXT searching
      6. uses table level locking for query contentions and write queries.
   2. InnoDB Tables
      1. represented on disk with .frm format file in database directory, as well as data and index storage in the InnoDB tablespace. The table space is shared by all InnoDB tables.
      2. Supports transactions with full ACID compliance
      3. provides auto-recovery after server or host crash
      4. supports foreign keys and referential integrity, including cascaded deletes and updates
      5. uses multi-versioning and row-level locking for query contentions.
   3. MERGE Tables
      1. a MERGE table is a collection of identically structured MyISAM tables represented on disk by an .frm format file and an .MRG file located in the database directory
      2. a query on a MERGE table acts as a query on all the MyISAM tables of which it consists.
      3. A MERGE table creates a logical entity that can exceed the maximum MyISAM table size
   4. BDB (Berkeley DB) Tables (Note: BDB storage engine not enabled in LAMPsig/Knoppix LiveCD installation)
      1. represented on disk by an .frm format file and a .db file that stores data and index information located in database directory.
      2. Supports transactions with full ACID compliance
      3. uses page-level locking for query contention.
   5. HEAP (Memory) Tables
      1. represented on disk by a .frm format file in database directory. Table data and indexes are stored in memory
      2. In-memory storage results in fast performance
      3. HEAP table contents do not survive a restart of the server. The structure survives, but it contains 0 data rows after a restart
   
   
3. Limits on Database Components
   1. MySQL doesn't place limits on number of databases, but the OS or filesystem might due to limits on number of sub-directories allowed in filesystem tree.
      1. MySQL databases are represented on disk in sub-directories
   2. MySQL doesn't place limits on number of files in directory, tho the OS or filesystem might.
      1. MyISAM tables are represented on disk in files under the database subdirectory
   3. MySQL does place limits on size of individual tables. Techniques around this limitation would include:
      1. MERGE tables (for MyISAM storage engine)
      2. RAID setup (for datafiles only as index tables are stored in a single file)
      3. Convert MyISAM tables to InnoDB tables as InnoDB allows for larger datafiles.
      4. For OS or filesystem limitations see if OS dependent adjustments are possible.
   
   
4. Identifier Syntax. Identifiers identify a specific database elements, e.g, databases, tables, table columns, aliases, and (sometimes) indexes.
   1. Legal Characters
   2. Qualifiers for Table and Column Names. Sometimes qualifiers are necessary to resolve ambiguity.
      1. SELECT * FROM Country;
         SELECT * FROM world.Country;
      2. SELECT Name FROM Country;
         SELECT Country.Name FROM Country;
         SELECT world.Country.Name FROM world.Country;
   
   
5. CREATE and DROP DATABASE
   
   
6. CREATE TABLE
   1. Syntax
   2. Temporary
   
   
7. DROP TABLE
   
   
8. ALTER TABLE
   1. Adding and Dropping Columns
   2. Modifying Existing Columns
   3. Renaming a Table
   4. Specifying Multiple Alterations
   
   
9. Creating and Dropping Indexes
   1. Four types of Indexes
      1. non-unique index
      2. UNIQUE
      3. PRIMARY KEY
      4. FULL TEXT
   2. Defining at Table-Creation Time
   3. Creating and Using Primary Keys
   4. Modifying Indexes of Existing Tables
   
   
10. Column Types
    1. Numeric / Integer
       1. TINYINT
       2. SMALLINT
       3. MEDIUMINT
       4. INT
       5. BIGINT
    2. Numeric / Floating-Point and Fixed-Decimal
       1. FLOAT
       2. DOUBLE
       3. DECIMAL
    3. String
       1. Binary and Nonbinary String Characteristics
       2. CHAR
       3. VARCHAR
       4. BLOB
       5. TEXT
       6. ENUM
       7. SET
    4. Date and Time
       1. DATE
       2. TIME
       3. DATETIME
       4. TIMESTAMP
       5. YEAR
    
    
11. Column Options
    1. UNSIGNED
    2. ZEROFILL
    3. AUTO_INCREMENT
    4. BINARY
    5. NULL and NOT NULL
    6. DEFAULT
    7. PRIMARY KEY and UNIQUE
    
    
12. Using AUTO_INCREMENT
    
    
13. Automatic Type Conversion and Value Clipping
    
    
14. DESCRIBE and SHOW to display table structures