MySQL Database Training  Print Course Details Go to Course Objectives & Outline >>  Sign Up for Course

Course Number:DB.024 Duration: 3 days (full-day sessions) OR Weekend/Evening sessions Training Overview: This course introduces you to the world of SQL database engines, and MySQL in particular - the component parts of the SQL system and how they interact. This course will cover the SQL language (learning the MySQL flavour) and how it's used from Java and other programming languages. This course also covers the administration of MySQL including user account and security controls, and database design so that you'll be putting together well thought-out, maintainable, expandible databases when you've concluded this course. Location and Pricing: Most of our courses are conducted online OR at our training facility and are customized to meet our students needs. We also provide customized onsite training at our clients' locations. Pricing for an online course depends on if its a weekend/evening course or a full day course. The weekend/evening courses are most cost-effective as they are spread over a period of weeks giving you ample time to complete the hands-on exercises. To receive a customized proposal that will fit your schedule and price quote, please contact us  Training Prerequisites: All attendees should have a high level of comfort using computers. Prior relational database experience is helpful but not required. Training format: This class is 60% lecture, 40% hands-on, with one-on-one dedicated time-slots with the instructor. Training Materials: All students receive a copy of related courseware / material. Software Needed on Each Student PC: MySQL 5.0 or later Additional software - please contact us for a detailed setup sheet Training Objectives: Master relational database fundamentals, as implemented in MySQL. Discover how to write progressively more advanced SELECT queries for retrieving exactly the data you need from MySQL. Add, modify, or remove data using INSERT, UPDATE, and DELETE statements. Learn how to create and use MySQL views and stored procedures Administration of MySQL including user account and security controls Designing Databases and creating necessary components Training Outline: Installing MySQL Installing MySQL Operating System Selection Downloading and Installing MySQL On Windows Running the MySQL Configuration Wizard Installing MySQL On Linux and Unix Installing MySQL On Mac Verifying a Downloaded File Verifying a MySQL Installation Launching the MySQL Server Installing From Source Files Setting up MySQL Versions of the MySQL Server Mysql Directory Structure Securing the MySQL Server: Set root Password Delete Anonymous Accounts Creating an Account for Basic Use General Information using MySQL Monitor Configuring MySQL MySQL Programs and Executables MySQL Programs and Executables Common Options to Various Commands Specifying Options Options on the Command Line : Display options from files : Explain Error Codes User Interfaces MySQL Overview of User Interfaces MySQL Help - Information Browser Establishing a Connection Object Browser MySQL Query Browser The Script Editor MySQL Administrator MySQL Migration Toolkit phpMyAdmin MySQL Monitor Using MySQL Monitor (SQL Command Interpreter) Some Useful Options Formatting and Output Options Commands for Interactive Mode Executing a Script from the Monitor Using the mysql Command to run a Script See Database Status Designing Databases Relational Database Concepts Physical Model: Mapping The ER Model To A Relational Database Database Design Principles First Normal Form Film Rentals - sakila Warning: Abnormal Normal Form! Basic Modeling Process: Creating a Data Model Second Normal Form Database Normalization Third Normal From Creating Databases and Components Creating Databases Deferred Checking Creating Tables Defining Default Values Confirm a Table is temporary Creating Column Definitions Defining Indexes when Creating Tables Data Types in MySQL Statement Timestamp Data Type Constraints Full-Text Indexes Creating Tables Altering Tables Managing Indexes Deleting Indexes Creating and Indexing Tables Identifiers in MySQL Identifiers in MySQL Identifier Names Schema Object Names Using Double Quotes Some Restrictions Identifier Qualifiers Mapping of Identifiers to Filenames Setting Identifier Case Sensitivity Avoiding Data Transfer Problems Show Database Information SHOW and DESCRIBE Statements Using DESCRIBE Statement INFORMATION_SCHEMA Tables Showing Databases Basic Select Statements Basic SQL Syntax Rules SELECT Basics Introduction to the sakila Database Basic Select Examples SELECTing Specific Columns The WHERE Clause and Basic Operator Symbols Exercise: Using the WHERE clause to check for equality or inequality Exercise: Checking for NULL WHERE and ORDER BY Exercise: Using WHERE and ORDER BY Together Using Aliases Selecting Distinct Records Sorting Records Exercise: Sorting Results The LIMIT Clause Processing the First N or Last N Records Using Expressions in MySQL Statements MySQL Expressions The IN Operator Operators in MySQL The LIKE Operator Concatenation The REGEXP Operator Arithmetic Operators Use of BINARY with Strings Operator Precedence The NOT Operator Comparison Operators Order of Evaluation The WHERE Clause and Operator Words Summary Exercise: More SELECTs with WHERE Using CASE Exercise :Using Control Flow Functions in a SELECT Statement Exercise: Working with CASE Logical Operators Data Conversion Functions Exercise: Writing SELECTs with Multiple Conditions Using MySQL Functions Numeric Functions Functions to Compare Data Current Date/Time Control Flow Functions Date Addition/Subtraction Functions String Functions Date Extraction Methods Exercise: Using String functions to find first word. Date/Time Functions Date Formatting Methods Exercise: Working with Dates and Strings Exercise: Working with Case and Dates Inserting, Updating and Deleting Records in MySQL Inserting Data in a MySQL Database <set option> Alternative of INSERT Exercise: Inserting Records Using REPLACE Statement to Add Data ORDER BY and LIMIT Clauses in UPDATE Exercise: Replacing Records in sakila INSERT with ON DUPLICATE KEY UPDATE Syntax Some Advanced MySQL modifiers to DELETE statement Exercise: Replacing Records in sakila Updating Data in MySQL ORDER BY and LIMIT in DELETE Statement Using DELETE Statement in MySQL Updating Multiple Tables using Joins Exercise: Delete reservations Exercise: Delete reservations Using TRUNCATE Statement to Delete Data <FROM JOIN DELETE> Alternative Joining Tables in an UPDATE Statement <using join delete> Clause Exercise: using UPDATE Statements to Modify Data in multiple Joined Tables Joining Tables in a DELETE Statement Deleting from multiple tables Exporting and Importing Data in MySQL EXPORTING: Data Out of a Table Use Field and Line Separators Dump a Row to a File Creating a New Table Using a ResultSet Copying Data into an Existing Table General Loading Rules Using the LOAD DATA to Import Data More Loading Examples Exporting and Importing Data Using the mysqlimport Utility to Import Data Using Separators with mysqlimport Utility Importing Data via mysqlimport Conclusion

Sign Up for Course  Print Course Details