
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
There are no reviews yet.