MySQL for Beginners

Student Guide

D61918GC30
Edition 3.0
May 2013
D82065
Author Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Disclaimer
Mark Lewin
This document contains proprietary information and is protected by copyright and
Technical Contributors other intellectual property laws. You may copy and print this document solely for your
own use in an Oracle training course. The document may not be modified or altered
and Reviewers in any way. Except where your use constitutes "fair use" under copyright law, you
Andrew Morgan may not use, share, download, upload, copy, print, display, perform, reproduce,
publish, license, post, transmit, or distribute this document in whole or in part without
Martin Hansson the express authorization of Oracle.
Bob Falsco The information contained in this document is subject to change without notice. If you
Mat Keep find any problems in the document, please report them in writing to: Oracle University,
500 Oracle Parkway, Redwood Shores, California 94065 USA. This document is not
Georgi Kodinov warranted to be error-free.
Mike Lischke
Restricted Rights Notice
Guilhem Bichot
If this documentation is delivered to the United States Government or anyone using
Ford Brockman
the documentation on behalf of the United States Government, the following notice is
Sanjay Manwani applicable:
Roy Lyseng U.S. GOVERNMENT RIGHTS
Craig McBride The U.S. Government’s rights to use, modify, reproduce, release, perform, display, or
disclose these training materials are restricted by the terms of the applicable Oracle
Jeremy Smyth license agreement and/or the applicable U.S. Government contract.

Trademark Notice
Editors
Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names
Smita Kommini may be trademarks of their respective owners.

Vijayalakshmi Narasimhan
Raj Kumar
Arijit Ghosh

Graphic Designer
Rajiv Chandrabhanu

Publishers
Sujatha Nagendra
Srividya Rameshkumar
Contents

1 Introduction to MySQL
Course Goals 1-2
Course Lesson Map 1-4
Introductions 1-6
Classroom Environment 1-7
MySQL Is Everywhere 1-8
Why MySQL Makes Sense for Oracle 1-9
Industry Leaders Rely on MySQL 1-10
MySQL Database Server Editions 1-11
MySQL Enterprise Edition 1-12
MySQL Connectors and APIs 1-13
MySQL Services 1-14
Community Support 1-15
Oracle Premier Support for MySQL 1-16
MySQL-Supported Operating Systems 1-17
MySQL Websites 1-18
MySQL Curriculum Footprint 1-19
MySQL Certification 1-20
MySQL Online Documentation 1-21
Example Databases 1-22
Summary 1-23
Practice 1-1 Overview: Accessing MySQL Resources Online 1-24

2 MySQL Server and Client

Objectives 2-2
MySQL Client/Server Model 2-3
Communication Protocols 2-5
MySQL Connectors 2-7
LAMP Stack 2-9
Installing MySQL 2-10
MySQL Server and Command-Line Client 2-11
Starting the MySQL Command-Line Client 2-12
Startup Command-Line Options 2-13
Keyboard Editing 2-14
tee File 2-16

iii
 Quiz 2-17
Summary 2-18
Practice 2-1 Overview: Installing and Starting the MySQL Server 2-19
Practice 2-2 Overview: Using the Keyboard Editing and Tee Commands 2-20
Practice 2-3 Overview: Installing the world_innodb Database 2-21

3 Database Basics
Objectives 3-2
RDBMS: Overview 3-3
Spreadsheet Versus Database 3-4
Entities and Relationships 3-5
Relationship Categories 3-6
RDBMS Database Structure 3-7
Using SQL with Databases 3-8
SQL Statements: Data Definition Language 3-9
SQL Statements: Data Manipulation Language 3-10
MySQL: Benefits 3-11
Summary 3-12
Practice 3-1 Overview: Quiz – Database Basics 3-13
Practice 3-2 Overview: Identifying the Structure of a Table 3-14

4 Database Design
Objectives 4-2
Database Modeling 4-3
ERD: Structure Diagram 4-4
ERD: Cardinality Diagram 4-5
Cardinality Diagram: Examples 4-6
Keys 4-7
Cardinality ERD: Example 4-8
Normalization 4-9
Advantages of Normalization 4-10
Disadvantages of Normalization 4-11
Eliminating Data Inconsistencies 4-12
Normal Forms 4-14
Normalization Process: Example 4-15
First Normal Form: 1NF 4-16
Second Normal Form: 2NF 4-17
Third Normal Form: 3NF 4-18
Normalized “Furniture Stores” Database 4-20
Quiz 4-21
Database Design Considerations 4-22

iv
 Database Design Plan 4-23
Database Design Plan Diagram 4-24
Viewing a Database 4-25
Evaluating a Database Design 4-26
Summary 4-29
Practice 4-1 Overview: Quiz – Database Design 4-30
Practice 4-2 Overview: Evaluating a Database 4-31
Practice 4-3 Overview: Creating a Structure Diagram 4-32

5 Table Data Types

Objectives 5-2
Data Types as Part of Database Design 5-3
Data Type Categories 5-4
Numeric Data Types 5-5
Numeric: Integer Data Types 5-6
Numeric: Integer Data Types Comparison 5-7
Numeric: Floating-Point Data Types 5-8
Numeric: Floating-Point Data Types Comparison 5-9
Numeric: Fixed-Point Data Types 5-10
Numeric: Bit Data Types 5-11
Temporal Data Types 5-12
Temporal Data Types Comparison 5-14
Character String Data Types 5-15
Character String: Text Class Data Types 5-16
Character String: Text Class Data Types Comparison 5-17
Character String: Integer Class Data Types 5-18
Character Set and Collation Support 5-19
Binary String Data Types 5-21
Binary String: Data Types Comparison 5-22
Quiz 5-23
Choosing Data Types 5-24
Setting Data Types to NULL 5-25
When to Use NULL 5-26
Summary 5-27
Practice 5-1 Overview: Quiz – Data Types 5-28
Practice 5-2 Overview: Explaining the Use of Data Types 5-29

6 Database and Table Creation

Objectives 6-2
Creating a Database 6-3
MySQL Naming Conventions 6-4

v
 Creating a Table 6-5
Showing How a Table Was Created 6-7
Column Options 6-8
Table Options 6-9
Table Indexes 6-10
MySQL Indexing 6-11
Showing Table Indexes 6-12
Table Constraints 6-13
Quiz 6-14
Summary 6-15
Practice 6-1 Overview: Displaying Table Creation Information 6-16
Practice 6-2 Overview: Creating a Database 6-17

7 Basic Queries
Objectives 7-2
SELECT Statement 7-3
Using SELECT Clauses 7-5
Using SELECT with DISTINCT 7-6
SELECT DISTINCT with Null Values 7-8
SELECT with WHERE 7-9
SELECT with ORDER BY 7-13
SELECT with ORDER BY with ASC and DESC 7-15
SELECT with LIMIT 7-17
SELECT with LIMIT and Skip Count 7-18
SELECT with LIMIT and ORDER BY 7-19
SELECT Usage Tips 7-20
Troubleshooting 7-21
SQL Modes for Syntax Checking 7-23
Common SQL Modes 7-24
Quiz 7-25
MySQL Workbench for SQL Development 7-26
Summary 7-27
Practice 7-1 Overview: Performing Basic Queries 7-28
Practice 7-2 Overview: Performing Basic Queries by Using MySQL
Workbench 7-29
Practice 7-3 Overview: Performing Basic Queries on the Pets Database 7-30

8 Database and Table Maintenance

Objectives 8-2
DROP DATABASE Statement 8-3
Creating a New Table by Using an Existing Table 8-4

vi
 Confirming the Creation of a New Table 8-5
Copying an Existing Table Structure 8-6
Creating a Temporary Table 8-7
DROP TABLE Statement 8-8
Adding a Table Column 8-9
Removing a Table Column 8-11
Modifying a Table Column 8-12
Modifying a Table Column: Row Changes 8-13
Adding Indexes and Constraints 8-14
Adding a Column Index 8-15
Dropping a Column Index 8-17
Quiz 8-18
Summary 8-19
Practice 8-1 Overview: Removing a Database 8-20
Practice 8-2 Overview: Creating a New Table and Removing a Table 8-21
Practice 8-3 Overview: Altering Table Columns 8-22
Practice 8-4 Overview: Modifying Table Indexes and Constraints 8-23
Practice 8-5 Overview: Further Practice 8-24

9 Table Data Manipulation

Objectives 9-2
Manipulation of Table Row Data 9-3
INSERT Statement 9-4
Using INSERT for Multiple Rows 9-5
Column Value Assignment with INSERT 9-6
REPLACE Statement 9-7
REPLACE Results 9-8
REPLACE Algorithm 9-9
Quiz 9-10
UPDATE Statement 9-11
UPDATE Statement Ordering 9-12
UPDATE with LIMIT 9-13
When UPDATE Has No Effect 9-14
Difference Between UPDATE and REPLACE 9-15
DELETE Statement 9-16
DELETE with ORDER BY and LIMIT 9-17
Summary 9-19
Practice 9-1 Overview: Inserting and Replacing Table Row Data 9-20
Practice 9-2 Overview: Modifying and Deleting Table Row Data 9-21
Practice 9-3 Overview: Manipulating Table Row Data in the Pets Database 9-22

vii
10 Functions
Objectives 10-2
Functions in MySQL Expressions 10-3
Using Functions 10-4
Types of Functions 10-5
String Functions 10-6
String Functions (Numeric): Examples (CHAR_LENGTH, INSTR, STRCMP) 10-7
String Functions: Examples (CONCAT, REVERSE, LEFT, RIGHT) 10-8
String Functions: Examples (LOWER, UPPER, LPAD, RPAD) 10-9
String Functions: Examples (TRIM) 10-10
String Functions: Examples (SUBSTRING) 10-11
String Functions: Examples (SUBSTRING_INDEX) 10-12
Temporal Functions 10-13
Temporal Functions: Date/Time Formats 10-14
Temporal Functions: Function Types 10-15
Temporal Functions: Examples 10-16
Numeric Functions 10-18
Numeric Functions: Examples 10-19
Numeric Functions: Additional Functions 10-20
Control Flow Functions 10-21
Control Flow Functions: CASE Functions 10-22
Control Flow Functions: CASE Function Syntax 10-23
Control Flow Functions: CASE Function Examples 10-24
Quiz 10-25
Aggregate Functions 10-26
Aggregate Function Types 10-27
Aggregate Functions: COUNT Function Examples 10-28
Aggregate Functions: GROUP BY Clause 10-29
Aggregate Functions: GROUP BY Clause and GROUP_CONCAT Function 10-30
Aggregate Functions: GROUP BY and HAVING Clauses 10-31
Aggregate Functions: GROUP BY Clause and WITH ROLLUP Modifier 10-32
Aggregate Functions: Super-Aggregate Operation 10-33
Spaces in Function Names 10-34
Summary 10-35
Practice 10-1 Overview: Quiz 10-36
Practice 10-2 Overview: Using Built-In, String, and Temporal Functions 10-37
Practice 10-3 Overview: Using Numeric and Control Flow Functions 10-38
Practice 10-4 Overview: Using Aggregate Functions 10-39

viii
11 Exporting and Importing Data
Objectives 11-2
Exporting Data 11-3
Exporting with a Query 11-4
Exporting with a Query: INTO OUTFILE 11-5
Exporting with a Query: CSV Format 11-6
Exporting with a MySQL Utility 11-7
Specifying Entities to Dump 11-8
mysqldump Utility Options 11-9
Dumping to a Text File 11-10
Text File Contents 11-11
Importing Data 11-12
Importing from a Data File 11-13
Importing with the LOAD DATA INFILE Statement 11-14
Importing with LOAD DATA INFILE: CSV Format 11-15
Importing with LOAD DATA INFILE: File Control 11-16
Importing with a MySQL Utility 11-17
mysqlimport Utility Options 11-18
mysqlimport Option: Examples 11-19
Summary 11-20
Practice 11-1 Overview: Quiz 11-21
Practice 11-2 Overview: Exporting Files by Using a Query 11-22
Practice 11-3 Overview: Importing Files from a Data File 11-23
Practice 11-4 Overview: Backing Up Database Files with a Utility 11-24

12 Joining Tables
Objectives 12-2
Combining Multiple Tables 12-3
Table Joins 12-4
Cross Joins 12-5
Multiple Tables in the FROM Clause 12-6
INNER JOIN Keyword 12-8
JOIN Keyword 12-9
Outer Joins 12-10
Finding Mismatches with LEFT JOIN 12-12
Finding Mismatches with RIGHT JOIN 12-13
Outer Joins: USING and NULL 12-14
Table Name Aliases 12-15
Quiz 12-16
Summary 12-17
Practice 12-1 Overview: Performing Inner and Outer Joins 12-18

ix
 Practice 12-2 Overview: Creating Queries Requiring Joins 12-19
Practice 12-3 Overview: Additional Optional Practice 12-20

13 Table Subqueries
Objectives 13-2
Subquery: Overview 13-3
Basic Subquery: Example 13-4
Advantages of Using a Subquery 13-5
Placement of Subqueries 13-6
Subquery Categories 13-7
Non-Correlated Subquery: Example 13-8
Correlated Subquery: Example 13-9
Subquery Result Table Types 13-10
Subquery Type/Placement Chart 13-11
Scalar Subqueries 13-12
Column Designation Subquery 13-13
FROM Subquery 13-14
WHERE Subquery 13-15
WHERE Subquery with Operators 13-16
Subqueries in Comparisons 13-17
WHERE with IN or NOT IN 13-18
WHERE with ALL, ANY, and SOME 13-19
ANY: Example 13-20
ANY: Example Subquery 13-21
WHERE with EXISTS and NOT EXISTS 13-22
Finding Table Mismatches 13-23
Modifying Table Data With Subqueries 13-24
Quiz 13-25
Subquery Tips 13-26
Converting Joins to Subqueries 13-27
Summary 13-28
Practice 13-1 Overview: Performing Different Types of Subqueries 13-29
Practice 13-2 Overview: Performing Several Advanced Subqueries 13-30

14 MySQL Graphical User Interface Tools

Objectives 14-2
MySQL GUIs 14-3
MySQL Workbench 14-4
MySQL Workbench Functionality 14-5
MySQL Workbench: SQL Development 14-7
MySQL Workbench: Data Modeling 14-8

x
 MySQL Workbench: Model Editor 14-9
MySQL Workbench: Model Overview Panel 14-10
MySQL Workbench: Data Modeling EER Diagram 14-11
MySQL Workbench: Server Administration 14-12
MySQL Workbench: Server Administration Features 14-13
MySQL Enterprise Monitor 14-14
Enterprise Monitor Features 14-15
Enterprise Monitor: Dashboard 14-17
Enterprise Monitor: Advisors 14-18
Quiz 14-20
Summary 14-21
Practice 14-1 Overview: Creating a Data Model by Using MySQL Workbench 14-22
Practice 14-2 Overview: Creating a Server Instance by Using MySQL
Workbench 14-23
Practice 14-3 Overview: Viewing MySQL Enterprise Monitor Demonstrations 14-24
Practice 14-4 Overview: (Optional) Viewing the MySQL Workbench
Demonstration 14-25
Practice 14-5 Overview: (Optional) Creating a Model for the Pets Database by
Using MySQL Workbench 14-26

15 Supplementary Information
Objectives 15-2
Storage Engines and MySQL 15-3
Storage Engines Available on the Server 15-4
Displaying Storage Engine Setting: Using SHOW CREATE TABLE 15-5
Displaying Storage Engine Setting: Using SHOW TABLE STATUS LIKE 15-6
Setting the Storage Engine to InnoDB 15-7
InnoDB Storage Engine 15-8
Other Storage Engines 15-9
Quiz 15-10
Creating Views 15-11
CREATE VIEW: Examples 15-12
Displaying View Information 15-13
Views: Showing Table Types 15-14
View Definition Restrictions 15-15
Quiz 15-16
Transactions 15-17
Transactions: ACID 15-18
Transaction Diagram 15-19
Transaction SQL Control Statements 15-20
Starting a Transaction 15-21

xi
 Quiz 15-22
Retrieving Metadata 15-23
Metadata: SHOW Statements 15-24
Metadata: INFORMATION_SCHEMA Database 15-25
Metadata: INFORMATION_SCHEMA Tables 15-26
Metadata: Viewing INFORMATION_SCHEMA 15-28
Metadata: SCHEMATA 15-29
Quiz 15-30
MySQL Performance Schema 15-31
Performance Schema: Implementation 15-32
Performance Schema: SHOW Database Tables 15-33
Quiz 15-34
MySQL Enterprise Backup 15-35
MySQL Enterprise Backup: Preparation 15-36
MySQL Enterprise Backup: Implementation 15-37
MySQL Enterprise Backup: Running mysqlbackup 15-38
MySQL Enterprise Backup: Some mysqlbackup Options 15-39
MySQL Enterprise Backup: Recovering or Restoring a Database 15-40
Quiz 15-41
Summary 15-42
Practice 15-1 Overview: Displaying Storage Engine Information 15-43
Practice 15-2 Overview: Displaying and Creating Views 15-44
Practice 15-3 Overview: Obtaining Metadata 15-45
Practice 15-4 Overview: (Optional) Creating a Backup of MySQL Databases 15-46

16 Conclusion
Course Goals 16-2
MySQL Curriculum Path 16-4
MySQL Resources 16-5
Your Evaluation 16-6
Thank You 16-7
Q&A Session 16-8

xii