Table of Contents
* Installing MySQL
o Installing MySQL
o Operating System Selection
o Downloading and Installing MySQL On Windows
o Installing MySQL On Linux and Unix
o Installing MySQL On Mac
o Verifying a Downloaded File
o Verifying a MySQL Installation
o Launching the MySQL Server
o Installing From Source Files
* Setting up MySQL
o Versions of the MySQL Server
o MySQL Directory Structure
o Securing the MySQL Server: Set root Password
o Delete Anonymous Accounts
o Creating an Account for Basic Use
o General Information using MySQL Monitor
o Configuring MySQL
* MySQL Programs and Executables
o MySQL Programs and Executables
o Common Options to Commands
o Specifying Options
o Options on the Command Line
o my_print_defaults: Display options from files
o perror: Explain Error Codes
* User Interfaces MySQL
o Overview of User Interfaces
o Establishing a Connection
o MySQL Query Browser
o MySQL Administrator
o MySQL Migration Toolkit
o phpMyAdmin
* MySQL Monitor
o Using MySQL Monitor (SQL Command Interpreter)
o Some Useful Options
o Formatting and Output Options
o Commands for Interactive Mode
o Executing a Script from the Monitor
o Using the mysql Command to run a Script
o See Database Status
* Designing Databases
o Relational Database Concepts
o Database Design Principles
o Film Rentals - sakila
o Basic Modeling Process: Creating a Data Model
o Database Normalization
* Creating Databases and Components
o Creating Databases
o Creating Tables
o Confirm a Table is temporary
o Creating Column Definitions
o Data Types in MySQL
o Timestamp Data Type
o Constraints
o Creating Tables
o Altering Tables
o Managing Indexes
o Creating and Indexing Tables
* Identifiers in MySQL
o Identifiers in MySQL
o Schema Object Names
o Some Restrictions
o Identifier Qualifiers
o Mapping of Identifiers to Filenames
o Identifier Case Sensitivity
* Show Database Information
o SHOW and DESCRIBE Statements
o INFORMATION_SCHEMA Tables
* MySQL Table Types
o MySQL Table Types (Storage Engines)
o Setting/Changing Engine
o MyISAM Tables
o InnoDB Tables
o FOREIGN KEY Constraints in InnoDB
o Choosing - MyISAM or InnoDB?
o HEAP or MEMORY Tables
o Temporary Tables
o MERGE Tables
o Other Table Types
o Table Files Storage
* Basic Select Statements
o Basic SQL Syntax Rules
o SELECT Basics
o Introduction to the sakila Database
o Basic Select Examples
o SELECTing Specific Columns
o The WHERE Clause and Basic Operator Symbols
o Using the WHERE clause to check for equality or inequality
o Checking for NULL
o WHERE and ORDER BY
o Using WHERE and ORDER BY Together
o Using Aliases
o Selecting Distinct Records
o Sorting Records
o Sorting Results
o The LIMIT Clause
* Using Expressions in MySQL Statements
o MySQL Expressions
o Operators in MySQL
o Concatenation
o Arithmetic Operators
o Operator Precedence
o Comparison Operators
o The WHERE Clause and Operator Words
o More SELECTs with WHERE
o Using CASE
o Working with CASE
o Logical Operators
o Writing SELECTs with Multiple Conditions
o Using MySQL Functions
o Functions to Compare Data
o Control Flow Functions
o String Functions
o Using String functions to find first word.
o Date/Time Functions
o Working with Dates and Strings
o Working with Case and Dates
* Inserting, Updating and Deleting Records in MySQL
o Inserting Data in a MySQL Database
o Inserting Records
o Using REPLACE Statement to Add Data
o Replacing Records in sakila
o INSERT with ON DUPLICATE KEY UPDATE Syntax
o Replacing Records in sakila
o Updating Data in MySQL
o Using DELETE Statement in MySQL
o Delete reservations
o Delete reservations
o Using TRUNCATE Statement to Delete Data
o Joining Tables in an UPDATE Statement
o using UPDATE Statements to Modify Data in multiple Joined Tables
o Joining Tables in a DELETE Statement
* Data Validation in MySQL
o Data Validation
o MySQL SQL Modes
o Strict SQL Mode
o Special Combination Modes
o Data Validation for Temporal Types
* Advanced Queries
o More on The SELECT Statement
o Using Variables in a SELECT Statement
o Creating Joins in Your SQL Statements
o Inner Joins and Cross Joins
o Multi-table Joins
o Outer Joins
o Left Outer Join
o Unions
o Using Unions
o Creating Views
o Dropping Views
o Benefits of Views
o Creating a View
o The SELECT Statement Options
o Subqueries
o Subqueries
* Aggregate Functions and Grouping
o Grouping Data using GROUP BY
o Aggregate Functions
o Working with Aggregate Functions
o GROUP_CONCAT: Aggregate Functions
o Multiple Column GROUPING
o GROUP BY WITH ROLLUP
o Aggregate Joined tables
o Filtering Aggregates using HAVING Clause
o Filtering Aggregated results using Having
o Miscellaneous Grouping Concepts and General Constraints
o Finding late customers
* Programming with MySQL
o Stored procedures
o Creating Routines
o Specification Options
o Deleting Routines
o Changing Routines
o Listing Existing Routines
o Determining the Code of an SP
o Programming Routines - Inside the Routines
o Using Variables
o General Programming Syntax Rules
o Invoking Procedures
o Parameters to Procedures
o Function Parameters
o Results of Procedures (SELECT)
o Function Results
o Invoking Functions
o IF-THEN-ELSE Branching
o Branching Using CASE
o REPEAT-UNTIL Loop
o LOOP
o Create a stored procedure
o Using MySQL Query Browser
o Error Handling via Handlers
o Triggers
o Create a trigger
* Managing Transactions in MySQL
o Managing Transactions
o Introducing Transactions
o Working Without Transactions
o Transactional Databases
o Performing a Basic Transaction
o Initiate a few transactions
o Adding Savepoints to Your Transaction
o The SAVEPOINT Statement
o Define savepoints in a transaction and rollback or commit to these savepoints.
o Data Problems in a Transaction
o Take a business action based on a dirty read
o Transaction Isolation Levels
o Change transaction isolation levels.
o Locking Nontransactional Tables
o Setting the Autocommit Mode
o Statements that Automatically Commit Transactions
o SELECT ... LOCK IN SHARE MODE
o Locking via SELECT ... FOR UPDATE
* Exporting and Importing Data in MySQL
o EXPORTING: Data Out of a Table
o Use Field and Line Separators
o Dump a Row to a File
o CTAS: Creating a New Table Using a ResultSet
o Copying Data into an Existing Table
o Using the LOAD DATA to Import Data
o Exporting and Importing Data
o Using the mysqlimport Utility to Import Data
o Importing Data via mysqlimport
* Configuring MySQL and Specifying Options
o Configuration Options
o Viewing MySQL's Configuration Parameters
o Some Examples of Option Usage
o Rules for Specifying Options
o Using Options
o Setting Options in Configuration Files
o Using Options in Files
o Configuration Templates
o Location of Configuration Files
o Options in Environment/System Variables
o Main Configuration Groups
o Key Configuration Options
o Client Configuration Options
o Server Configuration Options
o Engine-Specific Configuration Options
o Buffer and Cache Allocation Configuration Options
o Review Options in Pre-configured Files
* Securing MySQL Installation
o First Things First
o Network
o Securing the mysqld Daemon
o Files, Directories, and Processes
o Securing Accounts
o Limit connections with direct update
o Data Storage and Encryption
* Managing Users in MySQL
o Managing Security and Access Control
o The Two Stages of Access Control
o Granting Privileges
o Revoking Privileges
o Managing Users
o Managing Users
o MySQL Privilege System
o Privilege Scope Levels - Where Is Access Information Stored?
o The user Table - Global Privilege Scope
o Checking Users table
o Database Privilege Scope - The db Table
o Checking Users table
o Table Privilege Scope - The tables_priv Table
o Managing Users' Table level permissions
o Column Privilege Scope - The columns_priv Table
o The Routine scope: The procs_priv Table
o Tools for Setting the Access Privileges
o Refreshing Grant Caches
o Reviewing User Privileges
o Managing Users for Specific Tables
o The GRANT OPTION Privilege
* Administering MySQL
o View Information
o Using MySQLadmin Tool
o Flush Caches and Settings
o View and Kill Threads
o View and Kill Threads
o Server Management
o MySQL Administrator User Interface
* Data Backup and Restore in MySQL
o Managing Backup and Recovery
o Backing up or Exporting Databases Using mysqldump
o Using mysqldump
o mysqldump - Options
o Backing up a Single Database
o Contents of the Backup SQL Script
o Backing up a Database
o Backing up select tables from a Database
o Restoring Your Database
o Updating the Restored Database From Binary Log Files
o Restoring a Database From a Backup File
o Recovering Corrupt MyISAM Tables
o Backing Up and Recovering an InnoDB Database
* Optimizing Databases and Their Objects
o Optimizing Databases
o Improving Application Design
o Precomputing Queries
o Materialized Views
o Transactions
o Indexing
o MyISAM Index Collecting Statistics
o Table Analysis
* Optimizing and Tuning Queries
o Query Execution Plan
o Analyzing the output from EXPLAIN Statement
o Explain Non-Indexed and Indexed queries
o Join Optimization
o Create a Cartesion product.
o Create a Cartesion product.
o When to Index
o SQL Queries: Other Optimizations
o Improving execution plan via Indexed queries
o The Slow Query Log
o Using Options
* Tuning MySQL for Performance
o System Characteristics
o Tuning Server Parameters
o MySQL Query Cache
o Set up query caching
o The MyISAM Key Cache
o Examining Thread Information
* Replication in MySQL
o Replicating MySQL Databases
o MySQL Replication Model
o Setting up Replication
o Implementing Replication - Details
o Managing Replication
o Replication Configuration Options
www.learn-mysql-tutorial.com
