linuxCBT - DBMS (mysql 5) Training

LinuxCBT DBMS Edition feat. MySQL 5 focuses on the ubiquitous, high-performance, low-maintenance, highly-available and scaleable MySQL 5 Database Management System (DBMS).



LinuxCBT DBMS Edition establishes the foundation for successful deployments and administration of MySQL 5 by exploring many of its features including but not limited to DML & DDL Statements, Terminal Monitor, shell-based clients, graphical clients, MyODBC, storage engines, data imports & exports, and PHP integration.



LinuxCBT DBMS Edition is unparalleled in content, depth and expertise. It entails 30-hours, or 4+ days of classroom training. LinuxCBT DBMS Edition prepares you for successfully deploying MySQL-based DBMS solutions.



Let LinuxCBT DBMS Edition cost-effectively sharpen your MySQL 5 DBMS skills!



Recommended Prerequisites for:



* Any LinuxCBT Operating System Course /Classic|EL-4|SUSE|Debian Editions/

o Open mind & determination to master Linux and related open-source applications

o Basic understanding of networking concepts

o Access to a PC to follow the exercises



Modules:



* MySQL Installation

o Introduction to MySQL 5

o Discuss DBMS system specifications

o Identify existing MySQL-installed components

o Explore file-system layout

o Identify & discuss the various downloadable formats /source|binary|packaged/

o Download MySQL packages to a local repository

o Discuss the key DBMS components

o Confirm md5sums of components

o Install MySQL server package and examine defaults

o Identify key server-side administrative utilities

o Inspect the contents of the default DBMS data-directory

o Install MySQL Client package and explore its contents

*

* Terminal Monitor Shell Client

o Discuss Terminal Monitor concepts

o Explain the default privileges configuration

o Connect to the DBMS using anonymous privileges

o Explore Terminal Monitor's help facility and useful escape sequences

o Identify the default schemas

o Execute useful SQL functions

o Demonstrate how to execute SQL queries from the shell in non-interactive mode

*

* User Administration

o Identify the currently logged-in user

o Connect to MySQL DBMS as root and explore the interface

o Attempt to connect to the DBMS as invalid users and discuss the results

o Attempt to connect from a remote host and evaluate results

o Discuss the primary methods of tightening the default security scheme

o Tighten the default privileges for the root user

o Explore the global 'user' grant table & discuss attributes

o Secure anonymous user accounts

o Remove anonymous privileges and confirm enhanced security policy

o Discuss and explore the grant tables and applicable attributes

o Identify the ancillary grant tables that permit granular filtration

o Create new DBMS users and confirm connectivity

o Remove superfluous user accounts

o Discuss permissible host values when defining users

o Create remote super users and test connectivity

o Discuss privileges scopes concepts & applications

o Identify grant tables related to privileges scopes

o Use GRANT to create and manipulate user-privileges

o Create unprivileged accounts and explore capabilities

o Use REVOKE to remove privileges

o Use DROP to discard superfluous accounts

o Maintain user passwords using SET and UPDATE

*

* Show Commands & Options Files

o Explain the usages of key SHOW commands

o Expose DBMS privileges for currently authenticated user

o Expose DBMS privileges for other defined users

o List all databases available on the system

o Show the DDL statements used to create various MySQL-managed databases

o Examine the default supported character sets

o Reveal the structure of defined tables

o Show the DDL statements used to create various tables

o List the supported Storage Engines by the current binary-build of MySQL

o Examine the MyISAM Storage Engine tables on the file system & discuss key file types

o Show the schemas of various tables and discuss the results

o Examine the currently executing processes, including connected users

o Initiate & KILL connections listed in the process-list table

o Examine key status variables on the running server

o Show table status information, revealing used-space, length, create-time, etc.

o Discuss the default search-path for options files by MySQL clients

o Examine and secure the MySQL history file to prevent superfluous disclosure of queries

o Define a personal MySQL options file

o Explain the supported section headers in MySQL options files

o Define useful run-time directives for MySQL clients and examine results

o Define a useful, custom MySQL prompt on a global and per-user basis & examine usefulness

o Set default database variable in per-user options files and examine results

*

* Key MySQL Client Utilities

o Focus on mysql terminal monitor utility

o Discuss & demonstrate mysql client modes

o Discuss command-line option types

o Output queries to standard /HTML|XML|TXT/ formats

o Exclude column-headers from output for easy import into other applications

o Use options file to control startup variables

o Focus on mysqladmin utility

o Compare & contrast mysqladmin batch-mode to mysql interactive mode

o Confirm the status of DBMS servers using mysqladmin

o Ascertain remote DBMS process list

o Create & drop remote databases using mysqladmin

o Administer remote user credentials using mysqladmin

o Focus on mysqldump utility

o Discuss mysqldump applications, features & benefits

o Dump all databases and related objects to an ASCII text backup file

o Examine the resulting dump file

o Dump select databases and examine resulting file

o Dumpy DBMS databases to a remote MySQL instance using mysqldump

o Focus on mysqlimport utility

o Create database container and table structure for subsequent imports

o Create matching data-feed for import by mysqlimport

o Import data-feed using default delimiters and record-separators

o Discuss mysqlimport defaults regarding data-directory and table names

o Examine importing duplicate records

*

* Key Data Manipulation Language (DML) Statements

o Discuss common DML statements

o Focus on SELECT DML statement

o Optimize SELECT statements with WHERE clauses to define criteria

o Discuss SELECT operators /=|=|!=|etc/

o Perform pattern matching using WHERE clause with LIKE operator & wildcards

o Influence the order of SELECT output using ORDER BY

o Extend criteria definition and optimize queryies using AND clause

o Return result-set INTO output ASCII text file using SELECT INTO

o Redirect result-set to AWK and extract interesting fields

o Use LIMIT with SELECT to return n number of rows

o Return total number of rows including LIMIT value

o Alias column headers using SELECT

o Concatenate results returned using SELECT

o Focus on INSERT DML statement

o Explain supported INSERT statements /VALUES|SET|SELECT/

o Describe table structure to determine column constraints

o INSERT VALUES specifying column names

o INSERT VALUES one-to-one without defined column headers

o Use SET with INSERT to indicate interesting columns

o Use VALUES to INSERT multiple rows in one statement

o Discuss INSERT SELECT applications

o Create table structure for using INSERT SELECT

o Populate newly-created table with data

o Use SELECT INSERT to move data between tables & evaluate results

o Focus on UPDATE DML statement

o Perform global table UPDATE with simple DML statement

o Specify columns to be updated and evaluate affected rows

o UPDATE interesting rows using criteria

o UPDATE n number of records using the LIMIT criteria operator

o Focus on DELETE DML statement

o Remove interesting records using WHERE clause and applicable critieria

o Intro to the ALTER Data Definition Language (DDL) statement

*

* Key Data Types

o Focus on Numeric data-types

o Use describe to expose defined data-types for columns

o Discuss unsigned and auto incremented column attributes

o Discuss /TINYINT|SMALLINT|MEDIUMINT|INT|BIGINT/ capacity and applications

o Discuss FLOAT capacity and applications

o Discuss DOUBLE capacity and applications

o Discuss DECIMAL capacity and applications

o Discuss BIT capacity and applications

o Define a table with TINYINT column

o Populate TINYINT column with INSERT and exceed its limits and evaluate resutls

o Use ALTER to increase the capacity of the TINYINT column to SMALLINT

o Populate SMALLINT column and evaluate results

o ALTER table and add columns with greater numeric capacity

o Define a DECIMAL column and store values

o Focus on String data-types

o Explain features and limitations of CHAR type fields

o INSERT strings with unpreserved characters and evaluate results

o Compare & contrast VARCHAR & CHAR type fields; fixed vs. varaible-length

o ALTER table to convert interesting fields from CHAR to VARCHAR

o Create Perl script to generate values for VARCHAR columns and evaluate results

o Discuss /TEXT|TINYTEXT|MEDIUMTEXT|LONGTEXT/ column types

o Discuss /BLOB|TINYBLOB|MEDIUMBLOB|LONGBLOB/ column types

o ALTER table to support LONGTEXT and import data and evaluate results

o Discuss /BINARY|VARBINARY/ usages and limits

o Explain the usages of ENUM column types

o Explore predefined ENUM column types in system tables

o Define ENUM fields, insert data and evaluate DBMS behavior

o Focus on Date data-types

o Discuss DATE column type features (resolution & range)

o ALTER table structure to support DATE-based column

o INSERT invalid date and evaluate results

o Discuss DATETIME column type features (resolution & range)

o ALTER table structure to support DATETIME-based column

o INSERT valid date & time value and evaluate results

o Discuss how MySQL implements dummy dates

o ALTER table structure to support TIMESTAMP-based column

o Discuss TIMESTAMP column type features (resolution & range)

o Discuss NULL-enabled auto-timestamp (dummy-time) invocated columns

o Explain MySQL's default date syntax

o Insert Date & Time values into MySQL columns using mixed delimiters

o Insert Date & Time values into MySQL columns without delimiters

o Recap supported data-types

*

* Key Data Definition Language (DDL) Statements

o Focus on CREATE DDL statement

o Describe how MySQL maps database containers to the file system

o CREATE DATABASE container to house new tables

o Define the requirements /auto_increment|primary key|etc/ of new base tables

o Define the CREATE TABLE syntax for new base tables based on selected data types

o Execute statement and evaluate results of recently-created tables

o Use SHOW CREATE TABLE to examine DDL syntax for reference

o CREATE TABLE with NOT-NULL column enforcements

o Populate tables with useful values and evaluate results

o INSERT VALUES with DEFAULT for auto-populated columns and evaluate results

o Duplicate table structure using CREATE TABLE and evaluate results

o Populate destination table with data from the source table using INSERT

o Discuss Temporary tables features and applications

o CREATE TEMPORARY TABLES and populate with useful data

o Rename tables using RENAME TABLE as an alternative to ALTER TABLE

o Focus on DROP DDL statement

o Describe the objects that are affected using DROP

o DROP DBMS objects and evaluate file system implications

o Focus on CREATE INDEX DDL statement

o Discuss the importance of indexes to improved data-retrieval

o Discuss MyISAM storage engine per-index limits

o Identify default MyISAM index files

o Explore defined indices

o Define UNIQUE indices

o Compare & contrast table & index storage requirements

o Generate data-set to be imported into an indexed column

o Compare & contrast query-execution times with and without indices

*

* JOINS

o Discuss the features & benefits of using JOINS to access normalized data

o Normalize the data structure, using ALTER TABLE, to prepare for using joins

o Use seq to generate normalized data and import using mysqlimport

o Discuss the various types of supported JOINS /EQUI|NATURAL|LEFT|RIGHT|INNER|OUTER/

o Define and execute EQUIJOINS

o Define and execute NATURAL JOINS

o Define JOINS using fully-justified DBMS object names

o Use table and column aliases with JOINS

*

* VIEWS & TRIGGERS

o Discuss the features & benefits of using VIEWS

o Use DESCRIBE to identify interesting VIEW candidate-columns

o Explain VIEW-creation syntax

o Create useful VIEWS and evaluate results

o Create VIEWS using column-aliases

o Update VIEWS using CREATE OR REPLACE VIEW & ALTER VIEW

o Create VIEWS based on EQUIJOINS

o Demonstrate how to UPDATE base-table data using VIEWS

o Remove superfluous VIEWS using DROP VIEW

o Focus on TRIGGERS

o Discuss MySQL's TRIGGER implementation rules

o Discuss TRIGGER t