This course will teach you a lot to start using this incredibly popular MySQL database and gain useful skills in the process. In learning about MySQL you will also gain an understanding of relational databases in general. Furthermore, you will harness that power by learning SQL and using it to build databases, populate them with data and query that data through extensive hands-on practices.

Introduction to MySQL

Essential database concepts and terminology | Installing MySQL | Connecting to and disconnecting from MySQL server | Creating and dropping databases | Creating, updating, and dropping tables | Adding data into a table | Returning data and deleting data from a table | Understanding basic statistical and date functions | Joining more than one table

 

Data Types & Table Types

Numeric, string, and date column types | MySQL command-line options | Logical, arithmetic, comparison, and bit operators | Exploring options for connecting to MySQL | Understanding table types

 

Advanced SQL

Logical, arithmetic, comparison, and bit operators | Advanced joins | Joining results with UNION | Removing records with DELETE and TRUNCATE | User variables | Running MySQL in batch mode | Performing transactions with BEGIN and COMMIT | Consistent reads | Table locks | Read locks for updating and for sharing

 

Indexes & Query Optimization

Creating and using indexes | Primary keys, unique indexes, full-text indexes, and ordinary indexes | Full-text searches | Dropping or changing an index | Auto increment fields | Analyzing queries with EXPLAIN | Optimizing SELECT statements

 

Designing Database

 

Understanding Relational Databases

The hierarchical database model | The network database model | The relational database model | Learning basic terms | Table keys and foreign keys | Views

 

Database Normalization

1st normal form | 2nd normal form | 3rd normal form | Boyce-Codd normal form | 4th normal form | 5th normal form | Denormalization

 

MySQL Administration

 

Basic Administration

The MySQL utilities | Starting and shutting down MySQL | Starting mysqld automatically upon booting up | Configuring MySQL | Logging | Rotating logs | Optimizing, checking, analyzing, and repairing tables

 

Database Backup

The BACKUP and RESTORE commands | Backing up by directly copying files | mysqldump | mysqlhotcopy | Using SELECT INTO to back up | Using LOAD DATA INFILE to restore | Security issues with LOAD DATA LOCAL | Using the binary update log | Backing up InnoDB tables | Replication as a means of backup

 

Database Replication

Setting up replication | Configuring slave and master files | Understanding slave and master SQL statements

 

Database Security

Security when connecting | Changing and assigning passwords | Managing users and permissions | The MySQL permission tables | GRANT and REVOKE | Dangerous privileges | Application and system security | Security issues with LOAD DATA LOCAL