MySQL 5.0 Certification Study Guide

Index

[SYMBOL] [A] [B] [C] [D] [E] [F] [G] [H] [I] [J] [K] [L] [M] [N] [O] [P] [Q] [R] [S] [T] [U] [V] [W] [Y] [Z] id output columns (EXPLAIN statements)

IDENTIFIED BY clauses     GRANT statementsidentifiers (SQL statements)     case sensitivity 2nd     function names as     qualified names 2nd     reserved words as 2nd

         case sensitivity

     syntax of 2ndIDs (servers)     server replication, configuring 2nd

IF EXISTS clause (DROP DATABASE statements)

IF NOT EXISTS clauses

     CREATE TABLE statements

IF statements     stored routine conditional testing

IF() functions 2nd IFNULL() functions 2nd

IGNORE SPACE mode (SQL mode) implicit locks 2nd     lock levelsimporting/exporting data     LOAD DATA INFILE statements 2nd

         data file format specifiers 2nd 3rd 4th

         Deleted field

         duplicate table records 2nd

         efficiency of

         FIELDS clauses

         LINES clauses 2nd

         loading specific table columns 2nd

         privilege requirements

         Records field

         Skipped field

         skipping data file lines

         skipping table column values

         specifying data file locations 2nd 3rd

         syntax of         transforming table column values 2nd

         Warnings field

     mysqldump command-line client 2nd

         --all-databases option

         --databases option         --fields-enclosed-by option 2nd         --fields-escaped-by option

         --fields-terminated-by option 2nd         --lines-terminated-by option         --no-create-info option         --tab option 2nd

     mysqlimport command-line client 2nd 3rd 4th 5th         --fields-enclosed-by option         --fields-escaped-by option         --fields-terminated-by option 2nd         --ignore option         --lines-terminated-by option 2nd         --local option 2nd

         --replace option

     NULL values 2nd     SELECT INTO OUTFILE statements 2nd 3rd         data file format specifiers 2nd 3rd 4th

         FIELDS clauses

         LINES clauses 2nd

IN keywords

     column comparisons 2nd IN parameters 2nd

IN() functions 2ndINDEX clauses

     CREATE TABLE statements INDEX DIRECTORY option (CREATE TABLE statements) 2ndindex files     tables, moving in 2nd 3rd index merge values (type output columns)

index subquery values (type output columns)

index values (type output columns)

indexes

     benefits of 2nd

    BTREE indexes

         MEMORY storage engines

         MEMORY tables

     column prefixes 2nd 3rd 4th 5th

    creating

         composite indexes

         defining indexes at creation 2nd

         primary keys 2nd 3rd 4th

         unique indexes 2nd

     defining     defining at index creation 2nd

     deleting 2nd

     design principles 2nd 3rd

     DROP INDEX statements

     FULLTEXT 2nd 3rd         case sensitivity         column prefixes

         MATCH operators         searches         syntax of     FULLTEXT indexes

    hash indexes         MEMORY storage engines    HASH indexes         MEMORY tables     indexing algorithms, choosing 2nd 3rd     leftmost index prefixes 2nd     metadata, displaying 2nd 3rd 4th 5th

    MyISAM table indexes

         preloading to key caches     naming         displaying names

     nonunique 2nd

     nonunique indexes

     NOT NULL columns 2nd

     NULL values     over indexing

     overlapping indexes     PRIMARY KEY

     primary keys         creating 2nd 3rd 4th    queries         EXPLAIN statements 2nd         horizontal limitations

         LIMIT clauses

         optimization strategies 2nd 3rd

         summary tables 2nd 3rd 4th

         WHERE clauses 2nd

     SHOW INDEX statements

     SPATIAL

     SPATIAL indexes

     table update performance

     tables, adding to 2nd

     UNIQUE 2nd

     unique indexes

         creating 2nd

    updates

         disabling Information Browser (MySQL Query Browser)

     function browser

     parameter browser

     syntax browser

     transaction browser INFORMATION SCHEMA database 2nd     access syntax 2nd 3rd

     limitations of     metadata, accessing 2nd 3rd 4th 5th    ROUTINES table         displaying stored routine metadata 2nd

     versus SHOW statements 2nd     VIEWS table 2ndINFORMATION SCHEMA databases     DESCRIBE statements 2nd     SHOW statements 2ndINFORMATION SCHEMA TABLES statements     table storage engines, determining

INNER JOIN keywords

     inner joins, writing 2nd inner joins     comma (,) operators, writing via 2nd 3rd 4th 5th 6th 7th 8th

     COUNT() functions

     FROM clauses 2nd

     GROUP BY clauses

     HAVING clauses     INNER JOIN keywords, writing via 2nd

     ORDER BY clauses     subqueries, converting to 2nd 3rd

     USING() functions 2nd     WHERE clauses 2nd 3rd 4th innodb buffer pool size option (InnoDB storage engine) innodb buffer pool size system variables innodb data file path option (InnoDB storage engines)

innodb data home dir option (InnoDB storage engines) 2nd

innodb flush log at trx commit option (InnoDB storage engine) 2nd

InnoDB Hot Backup program

innodb log buffer size option (InnoDB storage engine)

innodb log buffer size system variables

innodb log file size option (InnoDB storage engine)

innodb log files in group option (InnoDB storage engine)

InnoDB storage engine

    tablespaces/log files

         MySQL Server storage

InnoDB storage engine log buffers

     MySQL Server memory usage

InnoDB storage engines

     --skip-innodb option     --without-innodb option

     ACID compliance 2nd

     autocommit mode, disabling 2nd 3rd

     buffer pools, configuring

     disabling     foreign keys 2nd 3rd 4th 5th     innodb buffer pool size option

     innodb data file path option     innodb data home dir option 2nd     innodb flush log at trx commit option 2nd     innodb log buffer size option

     innodb log file size option     innodb log files in group option     isolation levels, setting at server startup 2nd 3rd     locking characteristics 2nd 3rd     log files     log files, configuring 2nd 3rd     operational characteristics 2nd

     READ COMMITTED isolation levels 2nd 3rd

     READ UNCOMMITTED isolation levels 2nd     REPEATABLE READ isolation levels 2nd 3rd     SERIALIZABLE isolation levels 2nd

     shared tablespaces 2nd

     shared tablespaces, configuring 2nd

         explicit control

         tablespace characteristics 2nd     SHOW ENGINEINNODB STATUS statements 2nd

     SHOW TABLE STATUS statements     status information, viewing 2nd

     table management     table optimization 2nd 3rd     transaction savepoints, setting     transactional model 2nd 3rd 4th    transactions

         dirty reads

         nonrepeatable reads

         phantoms 2nd

     transactions, explicit table locks

     transactions, implicitly ending

InnoDB tables

     --innodb file per table option

     binary backups 2nd

         portablility 2nd

    buffer pools

         MySQL Server optimization 2nd

     index design principles

    log buffers

         MySQL Server optimization     maintenance 2nd

     optimization

     optimizing 2nd 3rd

     query storage engines

     shared tablespaces 2ndInnoDB tablespace files     multiple server management

INOUT parametersinput data handling    invalid values         handing in nonstrict mode 2nd

         handing in strict mode     missing values 2nd     MySQL value checking process, overview of 2nd     type conversion    value restrictions         ALLOW INVALID DATES SQL mode         ERROR FOR DIVISION BY ZERO SQL mode

         INSERT IGNORE SQL mode

         NO ZERO DATE SQL mode         NO ZERO IN DATE SQL mode         overriding 2nd

         STRICT ALL TABLES SQL mode 2nd

         STRICT TRANS TABLES SQL mode 2nd

         TRADITIONAL SQL mode 2nd 3rd

         UPDATE IGNORE SQL modeinput-line editing

     mysql client INSERT IGNORE SQL mode

INSERT statements     data updates 2nd 3rd     NULL values     ON DUPLICATE KEY UPDATE clauses 2nd     optimization 2nd

     records with duplicate key values 2nd

     required privileges

     SET clauses

     syntax of 2nd

     tables, adding multiple records to 2nd 3rd

     VALUES keyword 2nd

inserts

    concurrent inserts

         MyISAM tables

installing

    MySQL

         complete installations 2nd

         components of 2nd 3rd

         performance optimization strategies         Windows security measures

    MySQL Query Browser

         Linux installations 2nd

         Windows installations

INT integer data types 2nd     AUTO INCREMENT column attributes 2nd integer data types

     BIGINT 2nd         AUTO INCREMENT column attributes 2nd     INT 2nd         AUTO INCREMENT column attributes 2nd

     MEDIUMINT 2nd         AUTO INCREMENT column attributes 2nd     SMALLINT 2nd         AUTO INCREMENT column attributes 2nd     TINYINT 2nd         AUTO INCREMENT column attributes 2ndintegrity checks

     InnoDB tables 2nd

     table checks interactive mode (mysql client)     command-line, invoking from

     error statements

     output format, changing

     statement terminators 2nd 3rd 4th

    statements         canceling

         command-line executioninternal server tables

     MySQL Server memory usage INTERVAL() functionsinvalid values (input data handling)     nonstrict mode, handling in 2nd     strict mode, handling in

invoking

     client programs

         default option values

     stored functions

    stored procedures

         CALL statements

IS FREE LOCK() function

IS NOT NULL operators 2nd

IS NULL operators 2nd

IS USED LOCK() function

ISAM storage engines

ISNULL() functions 2nd

isolation levels (InnoDB storage engines)

     READ COMMITTED 2nd 3rd     READ UNCOMMITTED 2nd

     REPEATABLE READ 2nd 3rd

     SERIALIZABLE 2nd

     setting at server startup 2nd 3rd

ITERATE statements     stored routine transfer control