IQSOFT - John Brice Oktatóközpont
IT Leadership Development Program - ITLDP

MySQL for Developers NEW

Tanfolyam célja

This course gives in-depth knowledge of the skills, tools, and issues involved in writing applications with the MySQL database server. It forms the core of the MySQL Developer learning path, and covers the programming topics in MySQL, from queries, data types, and functions through to transactions, triggers, and stored procedures. The course examines the tools used by MySQL development professionals, and uses real-world examples in explaining how to generate complex queries, and consume them in your application. Students perform lab work with common programming languages, connecting to MySQL databases and executing queries and stored routines. In addition, it will prepare students for the Oracle Certified Professional, MySQL 5 Developer certification.

Learn to:

  • Use MySQL tools to connect to databases
  • Examine database structure and contents
  • Generate database structure diagrams
  • Execute complex queries
  • Manipulate numeric, text, spatial, and temporal data
  • Execute complex subqueries and joins

A Live Virtual Class (LVC) is exclusively for registered students; unregistered individuals may not view an LVC at any time. Registered students must view the class from the country listed in the registration form. Unauthorized recording, copying, or transmission of LVC content may not be made.

Course objectives:

  • Manage the structural characteristics of your databases and the tables within your databases
  • Use the SELECT statement to retrieve information from database tables
  • Use expressions in SQL statements to retrieve more detailed information
  • Use SQL statements to modify the contents of database tables
  • Write join expressions in your SQL statements to obtain information from multiple tables
  • Use subqueries in your SQL statements
  • Create views using SELECT statements to produce virtual tables of specific data
  • Perform bulk data import and export operations
  • Create user defined variables, prepared statements and stored routines
  • Create and manage triggers
  • Use the INFORMATION_SCHEMA database to access metadata
  • Write and debug MySQL applications
  • Configure, Monitor, and Optimize MySQL using Performance Schema and metadata queries
  • Understand the steps necessary to invoke MySQL client programs
  • Use the general-purpose mysql client program and the MySQL Workbench graphical user interface (GUI) to run queries, retrieve results, and generate diagrams
  • Understand the steps necessary to invoke MySQL client programs; Use the general-purpose mysql client program and the MySQL Workbench graphical user interface (GUI) to run queries.
Tematika
  • Introduction
    •     MySQL Product
    •     MySQL Services and Support
    •     MySQL Resources
    •     Example Databases
  • MySQL Client/Server Concepts
    •     MySQL General Architecture
    •     Diskspace use
    •     Memory use
  • MySQL Clients
    •     Invoking Client Programs
    •     Using Option Files
    •     The MySQL Client
    •     MySQL Workbench
    •     MySQL Connectors
    •     Third Party APIs
  • Querying for Table Data
    •     The SELECT Statement
    •     Aggregating Query Results
    •     Using UNION
    •     Import and Export Data
  • Handling Errors and Warnings
    •     SQL Modes
    •     Handling Missing or Invalid Data Values
    •     Interpreting Error Messages
    •     Conditions and Signals
    •     Error Handling when using Connectors
  • Data Types
    •     Data Type Overview
    •     Numeric Data Types
    •     Character String Data Types
    •     Binary String Data Types
    •     Temporal Data Types
    •     Spatial Data Types
    •     NULLs
  • SQL Expressions
    •     SQL Comparisons
    •     Functions in SQL Expressions
    •     Comments in SQL Statements
  • Obtaining Metadata
    •     Metadata Access Methods
    •     The INFORMATION_SCHEMA Database/Schema
    •     Using SHOW and DESCRIBE
    •     The mysqlshow Command
  • Database
    •     Database Properties
    •     Good Design Practices
    •     Identifiers
    •     Creating Databases
    •     Altering Databases
    •     Dropping Databases
  • Tables
    •     Creating Tables
    •     Table Properties
    •     Column Options
    •     Creating Tables Based on Existing Tables
    •     Altering Tables
    •     Dropping Tables
    •     Foreign Keys
  • Manipulating Table Data
    •     The INSERT Statement
    •     The DELETE Statement
    •     The UPDATE Statement
    •     The Replace Statement
    •     INSERT with ON DUPLICATE KEY UPDATE
    •     The TRUNCATE TABLE Statement
  • Transactions
    •     What is a Transaction
    •     Transaction Commands
    •     Isolations Levels
    •     Locking
  • Joins
    •     What is a Join?
    •     Joining Tables in SQL
    •     Basic Join Syntax
    •     Inner Joins
    •     Outer Joins
    •     Other Types of Joins
    •     Joins in UPDATE and DELETE statements
  • Subqueries
    •     Types of Subqueries
    •     Table Subquery Operators
    •     Correlated and Non-Correlated Subqueries
    •     Converting Subqueries to Joins
  • Views
    •     What are Views?
    •     Creating Views
    •     Updating Views
    •     Managing Views
    •     Obtaining View Metadata
  • Prepared Statements
    •     Why use Prepared Statements
    •     Using Prepared Statements for the mysql client
    •     Executing a Prepared Statement
    •     Deallocating a Prepared Statement
    •     Programming with Prepared Statements
  • Stored Routines
    •     What is a Stored Routine?
    •     Creating Stored Routines
    •     Compound Statements
    •     Assign Variables
    •     Parameter Declaration
    •     Execute Stored Routines
    •     Stored Routine Characteristics
    •     Examine Stored Routines
  • Triggers
    •     What are Triggers
    •     When to Use Triggers
    •     How to Delete Triggers
    •     Restrictions on Triggers
  • Optimization and Monitoring
    •     Overview of Optimization Principles
    •     Using Indexes for Optimization
    •     Using EXPLAIN to Analyze Queries
    •     Using Performance Schema to Analyze Performance
    •     Query rewriting techniques
    •     Optimizing Queries by Limiting Output
    •     Using Summary Tables
    •     Optimizing Updates
  • Conclusion
Kinek ajánljuk
Előfeltételek

Kötelező előfeltételek:

  • Experience with having created tables and queries within relational Databases, and comfort with basic SQL statements and another programming language.
  • MySQL for Beginners NEW

Ajánlott előfeltételek:

  • Some exposure to programming with databases.
  • Experience with Java or PHP would be useful.
Vizsgák
  • MySQL 5.6 Developer (1Z0-882)

Kapcsolódó tanfolyamok