MySQL for Developers I Course Overview
This MySQL Developer training course is designed for MySQL Developers who have a good understanding of a MySQL database and experience using SQL commands. The course provides practical experience in more advanced MySQL commands and SQL statements.
Skills Gained
The delegate will practise:
- Using advanced features of the Mysql Client
- Using advanced data types
- Managing the structure of databases and tables
- Managing and using indexes
- Writing complex SQL query statements
- Using advanced SQL expressions
- Using advanced SQL functions
- Performing advanced Insert, Update, Delete, Replace and Truncate Operations
Who will the Course Benefit?
MySQL Developers who have a basic understanding of a MySQL database and SQL commands, as covered on the Introduction to MySQL course.
Course Objectives
To provide the skills needed to write more advanced queries and database maintenance statements on a MySQL database.
Examinations
Together with the
Introduction to MySQL course, the content of this
MySQL for Developers I course covers the topics required for the
MySQL 5 Developer Certified Professional Exam, Part I (1Z0-871).
Before taking any exam, ensure you have the recommended experience. The Oracle website lists all exam requirements and these are updated regularly.
Exams are not included as part of the course.
Requirements
A working knowledge of MySQL is required. This can be gained by attendance on the Introduction to MySQL course.
Pre-Requisite Courses
Follow-On Courses
- MySQL for Developers II
- MySQL Database Administration
- PHP Programming & MySQL for Web Development
- Perl Programming
NOTE: Course technical content is subject to change without notice.
MySQL for Developers I Training Course
Course Contents - DAY 1
Course Introduction
- Administration and Course Materials
- Course Structure and Agenda
- Delegate and Trainer Introductions
Session 1: CLIENT/SERVER CONCEPTS
- MySQL client/server architecture
- Server modes
- Using client programs
- Logging in
- Configuration files
- Precedence
- SQL modes
- Exercises: Using client/server
Session 2: THE MySQL CLIENT PROGRAM
- Using MySQL interactively
- The MySQL prompts
- Client commands and SQL statements
- Editing
- Selecting a database
- Help
- Safe updates
- Using script files
- Using a source file
- Redirecting output into a file
- Command line execution
- Mysql output formats
- Overriding the defaults
- HTML and XML output
- Exercises: Using the MySQL client program
MySQL for Developers I Training Course
Course Contents - DAY 1 (Purchasing continued)
Session 3: DATA TYPES
- Bit data type
- Numeric data types
- Using Auto_increment
- Character string data types
- Binary string data types
- Enum and Set data typesTemporal data types
- Timezone support
- Handling Missing Or Invalid Data Values
- SQL_MODE options
- Exercises: Using data types
Session 4: IDENTIFIERS
- Using Quotes with Identifier naming
- Case sensitivity in Identifier naming
- Qualifying column with table and database names
- Using reserved words as identifiers
- Function names
- Exercises: Using identifiers
Session 5: DATABASES
- Database properties
- Creating a database
- Selecting a database
- Altering databases
- Dropping databases
- Obtaining database metadata
- The SHOW command
- The INFORMATION_SCHEMA database
- The SHOW CREATE command
- Exercises: Using databases
MySQL for Developers I Training Course
Course Contents - DAY 2
Session 6: TABLES AND INDEXES
- Table properties
- Creating tables
- Create table using Select or Like
- Temporary tables and memory tables
- Altering tables
- Adding columns
- Changing column widths and types
- Renaming columns
- Dropping columns
- Adding constraints
- Dropping constraints
- Renaming tables
- Changing the table storage engine
- Multiple alterations
- Droping tables
- Emptying tables
- Obtaining table metadata
- Show create table
- The information_schema
- Index introduction
- Structure of a mysql index
- Creating and dropping indexes
- Creating an index
- Altering a table to add an index
- Specifying index type
- Dropping indexes
- Obtaining Index Metadata
- Exercises: Creating, altering and dropping tables/indexes
Session 7: QUERYING FOR DATA
- The SQL select statement and MYSQL differences
- Advanced order by
- Order by and collation
- Order by with enum datatype
- Order by with set datatype
- Ordering with distinct and group by
- Special features of union
- Limit and order by clauses
- Group By clause
- Group_concat
- Using Rollup in a Group By clause
- Exercises: Querying for data
Session 8: SQL EXPRESSIONS AND FUNCTIONS
- Components of expressions
- Nulls
- Numeric expressions
- String expressions
- Temporal expressions
- Comparison functions
- Flow control functions
- Numeric functions
- String functions
- Temporal functions
- Exercises: Using expressions and functions
MySQL for Developers I Training Course
Course Contents - DAY 2 (Purchasing continued)
Session 9: UPDATING DATA
- Update operations and privileges
- Inserting rows
- Insert using a set clause
- Inserting duplicate values
- Replacing rows
- Updating rows
- Update using the order by and limit clauses
- Deleting rows
- The delete and truncate statements
- Exercise: Inserting, updating, replacing and deleting data
Session 10: CONNECTORS
- MySQL client interfaces
- Connector/j
- Connector/odbc
- Connector/net
