MYSQL developer

MySQL for Developers

Student Guide – Volume

Contents

1 Introduction to MySQL

Course Goals

1-2 Course Lesson Map

1-5 Introductions

1-8 Classroom Environment

1-9 Why MySQL Makes Sense for Oracle

1-10 Industry Leaders Rely on MySQL

1-11 MySQL Database Server Editions

1-12 MySQL Enterprise Edition

1-13 MySQL Connectors and APIs

1-14 MySQL Services

1-15 Community Support

1-16 Oracle Premier Support for MySQL

1-17 MySQL-Supported Operating Systems

1-18 MySQL Websites

1-19 Oracle University: MySQL Curriculum

1-20 MySQL Certification

1-21 MySQL Online Documentation

1-22 Example Databases

1-23 Summary 

2 MySQL Client and Server Concepts

Objectives

2-2 MySQL Architecture

2-3 Client Programs

2-4 MySQL Non-Client Utility Programs

2-5 MySQL Server

2-6 Server Process

2-7 Connection Layer

2-8 Communication Protocols

2-9 SQL Layer

2-10 SQL Statement Processing

2-11 Storage Layer

2-12 Storage Engine: Overview

2-13 Features That Are Dependent on Storage Engine

2-14 Storage Engines and SQL

2-15 How MySQL Uses Disk Space

2-16 How MySQL Uses Memory

2-18 Downloading MySQL Products

2-19 Starting the MySQL Command-Line Client

2-20 Using Script Files with MySQL

2-21 Databases in MySQL

2-22 Databases in MySQL: Real-World Example

2-23 Databases in MySQL: Fragments

2-24 Summary

2-25 Practices 

3 MySQL Clients

Objectives

3-2 Client Programs

3-3 Client Programs: Options

3-4 Command-Line Options

3-5 Options with Parameters

3-6 Options for Connecting

3-7 –protocol Option

3-8 –host and –port Options

3-9 –socket and –shared-memory-base-name Options

3-10 –compress Option

3-11 Options for User Identification

3-12 Using the –safe-updates Option

3-14 Option Files

3-15 Viewing Option File Locations

3-17 Standard Option File Locations

3-18 Specifying Additional Option Files

3-19 Overriding How a Program Reads Option Files

3-20 Obscuring Authentication Options

3-22 Login Paths

3-23 mysql Client Program

3-24 Using mysql Interactively

3-25 Statement Terminators

3-26 \G Statement Terminator

3-28 Statements on Multiple Lines

3-30 mysql Prompts

3-31 Changing the Primary Prompt

3-32 Line Editing in mysql

3-33 Formatting Output

3-34 mysql Commands

3-35 Sample STATUS Output

3-36 Logging mysql Sessions

3-37 Accessing Help

3-38 Quiz

3-40 MySQL Workbench

3-41 MySQL Workbench: Core Functionality

3-43 MySQL Workbench: Home Window

3-44 MySQL Workbench: SQL Development

3-45 MySQL Workbench: Data Modeling Features

3-46 MySQL Workbench: Model Window

3-47 MySQL Workbench: Model Overview Panel

3-48 MySQL Workbench: EER Diagram Editor

3-49 MySQL Workbench: Server Administration

3-50 MySQL Workbench: Server Administration Features

3-51 MySQL Workbench: Data Modeling

3-52 MySQL Utilities

3-53 Quiz 3-54 Summary

3-55 Practices 

4 Connectors and APIs

Objectives

4-2 MySQL Connectors  

4-3 Connectors Developed by Oracle

4-4 MySQL Community Connectors

4-6 Programming MySQL

4-7 Connector

4-9 Downloading Connector

J 4-10 Installing Connector

J 4-11 Registering Connector

J 4-12 Connecting to MySQL

4-13 Using Java to Execute SQL

4-14 Example: executeUpdate()

4-15 Example: executeQuery()

4-16 MySQL and PHP

4-17 Example: PHP and mysqli

4-18 Example: PHP and PDO_MySQL

4-20 Quiz

4-21 Embedding a MySQL Database

4-22 NoSQL: Overview

4-23 NoSQL Compared to RDBMS

4-24 memcached

4-25 MySQL and NoSQL

4-26 memcached Plugin to InnoDB

4-27 Installing the InnoDB memcached Plugin

4-28 Developing a memcached Application

4-29 Basic memcached Operations

4-31 MySQL and memcached with Java

4-32 MySQL and memcached with PHP

4-34 Quiz 4-36 Summary

4-37 Practices 

5 Data Types

Objectives

5-2 Data Types: Overview

5-3 Creating Tables with Data Types

5-4 Numeric Data Types

5-5 Numeric: Integer Data Types

5-6 Numeric: Comparing Integer Data Types

5-7 Numeric: Floating-Point Data Types

5-8 Numeric: MySQL-Specific Floating-Point Data Types

5-10 Numeric: Comparing Floating-Point Data Types

5-11 Numeric: Fixed-Point Data Types

5-12 Numeric: Fixed-Point Data Types (Notes Only)

5-13 Numeric: Bit Data Types 5-14 Temporal Data Types

5-15 Comparing Temporal Data Types

5-17 Character String Data Types

5-18 Character String: Considerations

5-19 Character String: Text Class Data Types

5-20 Character String: Comparing Text Class Data Types

5-22 Character String: Integer Class Data Types

5-23 Quiz

5-24 Character Set and Collation Support

5-25 Available Character Sets

5-26 Available Collations

5-27 Binary String Data Types

5-28 Binary String: Comparing Data Types

5-29 Setting Data Types to NULL

5-30 When to Use NULL

5-31 When Not to Use NULL

5-32 Spatial Data Type Extensions

5-33 Spatial Data Type Extensions: Storage Engines

5-34 Spatial Data Type Extensions: Data Types

5-35 Spatial Data Type Extensions: Populating Spatial Columns

5-36 Summary

5-37 Practices 

6 SQL Expressions Objectives

6-2 SQL Expressions

6-3 SQL Expressions: Examples

6-4 Numeric Expressions

6-5 Numeric Expressions: Results

6-6 String Expressions

6-7 String Expressions: Concatenation

6-8 Case-Sensitivity in String Comparisons

6-9 Case-Sensitivity in String Comparisons: Changing Collation

6-11 String Pattern Matching

6-12 String Pattern Matching: LIKE Versus NOT LIKE

6-13 String Pattern Matching: Regular Expressions

6-14 String Pattern Matching: RLIKE Syntax

6-15 String Pattern Matching: RLIKE Character Class

6-16 String Pattern Matching: Regular Expression

6-17 String Pattern Matching: Ranges of Characters

6-19 String Pattern Matching: Escaping Characters

6-20 Full-Text Search: Overview

6-22 Full-Text Search: Natural Language Mode

6-23 Full-Text Search: Boolean Mode

6-24 Full-Text Search: Boolean Mode Operators

6-25 Temporal Expressions

6-26 Temporal Expressions: Date Components

6-27 Temporal Expressions: Interval Arithmetic

6-28 Quiz 6-29 Function Expressions

6-30 Function Expressions: Spaces in Function Names

6-31 Comparison Functions: Using LEAST() and GREATEST()

6-32 Comparison Functions: Using INTERVAL()

6-33 Flow Control Functions

6-34 Flow Control Functions: IF() with ORDER BY

6-35 Flow Control Functions: SUM(IF()

6-36 Flow Control Functions: IF(NULL)

6-37 Flow Control Functions: CASE Functions

6-38 Flow Control Functions: CASE Function Syntax

6-39 Flow Control Functions: CASE Value Comparison

6-40 Flow Control Functions: Condition Evaluation

6-41 Numeric Functions

6-42 Numeric Functions: ABS() and SIGN()

6-43 Numeric Functions: FLOOR() and CEILING()

6-44 Numeric Functions: ROUND()

6-45 Numeric Functions: Trigonometric Examples

6-46 String Functions

6-47 String Functions: Numeric Category (INSTR, LOCATE, POSITION)

6-48 String Functions: Numeric Category (LENGTH, CHAR_LENGTH, CONVERT)

6-49 String Functions: Numeric Category (STRCMP)

6-50 String Functions: String Category (CONCAT, CONCAT_WS, LEFT, RIGHT)

6-51 String Functions: String Category (SUBSTRING, SUBSTRING_INDEX)

6-52 String Functions: String Category (LTRIM, RTRIM, TRIM)

6-53 String Functions: String Category (INSERT, REPLACE)

6-54 String Functions: Character Set and Collation

6-55 Quiz

6-56 Temporal Functions

6-57 Temporal Functions: Function Types

6-58 Temporal Functions: Extracting Temporal Data (NOW, CURDATE, CURTIME, DAYNAME)

6-59 Temporal Functions: Extracting Temporal Data (GET_FORMAT, DATE)

6-60 Temporal Functions: Extracting Temporal Data (YEAR, MONTH, DAYOFMONTH, DAYOFYEAR, HOUR, MINUTE, SECOND)

6-61 Temporal Functions: Manipulating Temporal Data (NOW, DATE_FORMAT, INTERVAL)

6-62 Temporal Functions: Manipulating Temporal Data (MAKEDATE, MAKETIME)

6-63 NULL-Related Functions: ISNULL and IFNULL

6-64 NULL-Related Functions: CONCAT and CONCAT_WS

6-65 Combining SELECT Results with UNION

6-66 Comments in SQL Statements

6-67 Comments in SQL Statements: As Part of SQL

6-68 Comments on Database Objects

6-69 Summary

6-70 Practices 

7 Obtaining Metadata

Objectives

7-2 Metadata Access Methods

7-3 INFORMATION_SCHEMA Database

7-4 Using INFORMATION_SCHEMA: Examples

7-7 Using SHOW

7-8 Using SHOW DATABASES

7-9 Using SHOW TABLES

7-10 Using SHOW COLUMNS

7-11 Using SHOW INDEX

7-12 SHOW CHARACTER SET and SHOW COLLATION

7-13 Using SHOW with LIKE and WHERE

7-14 Using DESCRIBE

7-15 mysqlshow Command 7

-16 Quiz 7-18 Summary

7-19 Practices 

8 Databases

Objectives

8-2 Database Properties

8-3 Database Data Directory: Features

8-4 Data Directory

8-5 Best Practices for Design

8-7 Database Modeling

8-8 Structure Diagram (ERD)

8-9 Cardinality Diagram (ERD)

8-10 Cardinality Diagram: Examples

8-11 Keys

8-12 Cardinality: ERD Example

8-13 SQL Example

8-14 Quiz

8-15 Normalization

8-16 Reasons to Normalize a Database

8-17 Advantages of Normalization

8-19 Disadvantages of Normalization

8-20 Normal Forms

8-21 Normalization Process: Example

8-22 First Normal Form: 1NF

8-23 Second Normal Form: 2NF

8-24 Third Normal Form: 3NF

8-25 Normalized “Furniture Store” Database

8-28 Normalization: SQL Example with world_innodb

8-29 Normalization: Explanation of SQL Example

8-30 Identifiers

8-31 Identifier Rules: Unquoted and Quoted

8-32 Identifier Rules: Alias

8-33 Identifiers: Reserved Words

8-34 Identifiers: Avoid Errors with Reserved Word

8-35 Using Qualified Names

8-36 Case-Sensitivity

8-37 Creating Databases

8-38 Using and Displaying Databases

8-39 Altering Databases

8-40 Dropping Databases

8-41 Summary

8-42 Practices 

9 Tables

Objectives

9-2 Creating a Table

9-3 Table Properties

9-5 Column Options

9-7 Table Constraints

9-8 Using the SHOW CREATE TABLE Statement

9-9 Creating New Tables from Existing Tables

9-10 Creating a New Table by Using an Existing Table

9-11 Creating a New Table by Using an Existing Table: Results

9-12 Copying an Existing Table Structure

9-13 Comparing SELECT Versus LIKE Results

9-14 Creating a Temporary Table

9-15 Adding a Table Column

9-16 Removing a Table Column

9-18 Modifying a Table Column

9-19 Changing Columns

9-20 Renaming Tables

9-21 Removing Tables

9-22 Quiz 9-23 Foreign Keys

9-24 Foreign Keys in the world_innodb Database

9-25 Foreign Key world_innodb: Examples

9-28 Referential Integrity

9-30 Foreign Key Constraints

9-32 Creating Foreign Key Constraints

10 Manipulating Table Data

Objectives

10-2 Inserting Data in a Table

10-3 Inserting Data in a Table: INSERT with SET

10-4 Inserting Data in a Table: INSERT with SELECT

10-5 Inserting Data in a Table: INSERT with LAST_INSERT_ID

10-6 Retrieving the Last Insert ID by Using Java

10-7 DELETE Statement

10-9 DELETE with ORDER BY and LIMIT: Descending

10-10 DELETE with ORDER BY and LIMIT: Ascending

10-11 UPDATE Statement

10-12 UPDATE Statement: Column Assignment Effects

10-13 UPDATE Statement Ordering

11 Transactions

Objectives

11-2 What Is a Transaction?

11-3 Handling Partial Operations

11-4 The ACID Properties of Transactions

11-5 Transaction Control Statements

11-6 The autocommit Mode

11-7 The autocommit Mode (Notes Only)

11-8 Controlling the autocommit Mode

11-9 Statements Causing an Implicit COMMIT

11-10 Consistency Issues

11-11 Isolation Levels

12 Joining Tables

Objectives

12-2 Joining Tables

12-3 Single Table Query Limitation

12-4 Single Table Query Limitation (Notes Only)

12-5 Combining Two Simple Tables

12-6 Combining Two Simple Tables: “Glue” Columns Together

12-7 Combining Two Simple Tables: Cartesian Product

12-8 Combining Two Simple Tables: Cartesian Product (Notes Only)

12-9 Combining Two Simple Tables: Cartesian Product Dimensions 

13 Table Subqueries

Objectives

13-2 Subqueries

13-3 Subquery: Example

13-4 Example Subquery (Notes Only)

13-5Subquery Categories

13-6 Scalar Subqueries

13-7 Scalar Subqueries (Notes Only)

13-8 Scalar Subquery: Example

13-9 Scalar Subquery: Example (Notes Only)

13-10 Row Subqueries

13-11 Row Subqueries: Example

13-12 Row Subqueries with Empty Sets

14 Views

Objectives

14-2 Views

14-3 Benefits of Views

14-4 Creating Views

14-5 View: Example

14-7 Naming Columns Explicitly

14-8 Updatable Views

14-10 Insertable Views

14-12 Updating Multitable Views

14-13 Using WITH CHECK OPTION

14-14 Checking Views

14-15 Altering Views

14-17 Dropping Views

14-18 Obtaining View Metadata with INFORMATION_SCHEMA 

15 Prepared Statements

Objectives

15-2 Why Use Prepared Statements?

15-3 User-Defined Variables

15-4 Preparing Statements

15-5 Executing a Prepared Statement

15-7 Deallocating a Prepared Statement

15-9 Limitations of Prepared Statements

15-10 Preparing Statements with Connectors

15-11 Preparing Statements with Java

15-12 Preparing Statements with Java: Example

15-13 Preparing Statements with PHP

15-14 Preparing Statements with PHP: Example

15-15 Quiz 

16 Stored Routines

Objectives

16-2 What Is a Stored Routine?

16-3 Uses of Stored Routines

16-4 Issues with Stored Routines

16-6 Creating Stored Procedures

16-7 Creating Stored Functions

16-8 Compound Statements

16-9 The DECLARE Statement

16-11 Using SELECT…INTO

16-13 Using SET

16-14 Parameter Declarations

16-15 Executing Stored Routines

16-17 Stored Routines in Databases 16-18

17 Triggers

Objectives

17-2 What Are Triggers?

17-3 Trigger Use Cases

17-4 Creating Triggers

17-5 Trigger Events

17-6 Using BEFORE Triggers

17-7 Using AFTER Triggers

17-8 Trigger Metadata

17-9 Trigger Error Handling

17-10 Deleting Triggers

17-11 Trigger Restrictions

17-12 Scheduling Database Operations

17-13 Using the MySQL Event Scheduler

17-14 Quiz

17-15 Summary

17-16 Practices 

18 Handling Errors and Warnings

Objectives

18-2 SQL Modes

18-3 Setting SQL Modes 

Important SQL Modes

18-5 The NO_ENGINE_SUBSTITUTION Default SQL Mode

18-7 Strict Mode

18-8 Interpreting Error Messages

18-9 Using SHOW WARNINGS

18-10 Using SHOW ERRORS

18-12 perror Utility

18-13 Declaring Error Handlers

18-14 Declaring Named Conditions

18-15 RESIGNAL 18-16 SIGNAL

18-17 Using MySQL Diagnostics

18-18 Retrieving Diagnostic Information

18-19 Error Handling when Using Connectors

18-20 Java: The SQLException Class

18-21 Using SQLException

18-22 PHP: mysqli Error-Handling Functions

18-23 PHP: The mysqli_sql_exception Class

18-24 Using mysqli Error Handling

18-25 Using mysqli_sql_exception and Exception Handling

18-26 Quiz

18-27 Summary

18-28 Practice

19 Optimization and Monitoring

Objectives

19-2 Why Optimize?

19-3 Optimization Strategies

19-4 SQL Parser and Query Optimizer

19-6 Using Indexes for Optimization

19-8 General Index Types

19-9 Special Index Types

19-10 Creating Indexes

19-11 Primary Key Indexes

19-13 Naming Indexes

19-14 Adding Indexes to Existing Tables

19-15 Using Index Prefixes

19-16 Indexing Without Prefixes

19-17 Indexing with Prefixes

19-18 Evaluating Index Prefixes

19-19 Leftmost Index Prefixes

19-20 Dropping Indexes

19-22 Using EXPLAIN to Analyze Queries

19-23 How EXPLAIN Works

19-24 EXPLAIN Output Columns

19-26 EXPLAIN for Joins

19-29 EXPLAIN Types

19-30 Extra Column: Signs of Efficient Queries

19-32 Extra Column: Signs of Inefficient Queries

19-33 Alternative EXPLAIN Outputs

19-34 Visual EXPLAIN

19-35 Quiz

19-36 Query Rewriting Techniques

19-37 Avoiding Column Expressions in Comparisons

19-38 Creating Indexes to Support Joins

19-39 Comparing Similar Data Types

19-40 Avoiding Leading Wildcards in “LIKE” Patterns

19-41 Considering Computed Columns

19-42 Optimizing Queries by Limiting Output

19-43 Using Summary Tables

19-44 Optimizing Data Modification Statements 19-46 Quiz

19-47 MySQL Enterprise Monitor

19-48 Summary

19-49 Practices 

20 Conclusion

Course Goals

20-2 MySQL Curriculum Path

20-4 MySQL Resources

20-5 Your Evaluation

20-6 Thank You

20-7 Q&A Session