HomeTrainingMySQL Database Training CourseMySQL Database Training Course Table of Contents

MySQL Database Training Course Table of Contents

  1. MYSQL – INTRODUCTION………………………………………………………………………….. 1

What is a Database?……………………………………………………………………………………… 1

RDBMS Terminology…………………………………………………………………………………….. 1

MySQL Database…………………………………………………………………………………………. 2

  1. MYSQL – INSTALLATION……………………………………………………………………………. 3

Installing MySQL on Linux/UNIX……………………………………………………………………….. 3

Installing MySQL on Windows………………………………………………………………………….. 4

Verifying MySQL Installation……………………………………………………………………………. 4

Post-installation Steps…………………………………………………………………………………… 5

Running MySQL at Boot Time…………………………………………………………………………… 6

  1. MYSQL – ADMINISTRATION……………………………………………………………………….. 7

Running and Shutting down MySQL Server…………………………………………………………… 7

Setting Up a MySQL User Account…………………………………………………………………….. 7

Administrative MySQL Command……………………………………………………………………… 9

  1. MYSQL – PHP SYNTAX…………………………………………………………………………….. 11
  2. MYSQL – CONNECTION……………………………………………………………………………. 12

MySQL Connection Using MySQL Binary……………………………………………………………. 12

MySQL Connection Using PHP Script………………………………………………………………… 12

  1. MYSQL – CREATE DATABASE……………………………………………………………………. 15

Create Database Using mysqladmin…………………………………………………………………. 15

Create a Database Using PHP Script…………………………………………………………………. 15

  1. MYSQL – DROP DATABASE………………………………………………………………………. 17

Drop a Database using mysqladmin…………………………………………………………………. 17

Drop Database using PHP Script………………………………………………………………………. 17

  1. MYSQL – SELECT DATABASE…………………………………………………………………….. 19

Selecting MySQL Database from the Command Prompt………………………………………….. 19

Selecting a MySQL Database Using PHP Script…………………………………………………….. 19

  1. MYSQL – DATATYPES………………………………………………………………………………. 21

Numeric Data Types……………………………………………………………………………………. 21

Date and Time Types…………………………………………………………………………………… 22

String Types……………………………………………………………………………………………… 22

  1. MYSQL – CREATE TABLES………………………………………………………………………… 24

Creating Tables from Command Prompt……………………………………………………………. 24

Creating Tables Using PHP Script…………………………………………………………………….. 25

  1. MYSQL – DROP TABLES…………………………………………………………………………… 27

Dropping Tables from the Command Prompt………………………………………………………. 27

Dropping Tables Using PHP Script……………………………………………………………………. 27

  1. MYSQL – INSERT QUERY………………………………………………………………………….. 29

Inserting Data from the Command Prompt…………………………………………………………. 29

Inserting Data Using a PHP Script…………………………………………………………………….. 30

  1. MYSQL – SELECT QUERY………………………………………………………………………….. 33

Fetching Data from a Command Prompt……………………………………………………………. 33

Fetching Data Using a PHP Script…………………………………………………………………….. 34

Releasing Memory……………………………………………………………………………………… 37

  1. MYSQL – WHERE CLAUSE………………………………………………………………………… 39

Fetching Data from the Command Prompt…………………………………………………………. 40

Fetching Data Using a PHP Script…………………………………………………………………….. 41

  1. MYSQL – UPDATE QUERY………………………………………………………………………… 43

Updating Data from the Command Prompt………………………………………………………… 43

Updating Data Using a PHP Script……………………………………………………………………. 44

  1. MYSQL – DELETE QUERY………………………………………………………………………….. 45

Deleting Data from the Command Prompt…………………………………………………………. 45

Deleting Data Using a PHP Script…………………………………………………………………….. 45

  1. MYSQL – LIKE CLAUSE…………………………………………………………………………….. 47

Using the LIKE clause at the Command Prompt……………………………………………………. 47

Using LIKE clause inside PHP Script………………………………………………………………….. 48

  1. MYSQL – SORTING RESULTS…………………………………………………………………….. 50

Using ORDER BY clause at the Command Prompt…………………………………………………. 50

Using ORDER BY clause inside a PHP Script…………………………………………………………. 51

  1. MYSQL – USING JOIN………………………………………………………………………………. 53

Using Joins at the Command Prompt………………………………………………………………… 53

Using Joins in a PHP Script…………………………………………………………………………….. 54

MySQL LEFT JOIN……………………………………………………………………………………….. 55

  1. MYSQL – NULL VALUES…………………………………………………………………………… 57

Using NULL values at the Command Prompt……………………………………………………….. 57

Handling NULL Values in a PHP Script……………………………………………………………….. 59

  1. MYSQL – REGEXPS………………………………………………………………………………….. 61
  2. MYSQL – TRANSACTIONS…………………………………………………………………………. 63

Properties of Transactions…………………………………………………………………………….. 63

COMMIT and ROLLBACK………………………………………………………………………………. 63

Transaction-Safe Table Types in MySQL…………………………………………………………….. 64

  1. MYSQL – ALTER COMMAND…………………………………………………………………….. 65

Dropping, Adding or Repositioning a Column………………………………………………………. 65

Altering (Changing) a Column Definition or a Name………………………………………………. 66

Altering (Changing) a Column’s Default Value……………………………………………………… 67

Altering (Changing) a Table Type…………………………………………………………………….. 68

Renaming (Altering) a Table…………………………………………………………………………… 69

  1. MYSQL – INDEXES…………………………………………………………………………………… 70

Simple and Unique Index……………………………………………………………………………… 70

ALTER command to add and drop INDEX……………………………………………………………. 70

ALTER Command to add and drop the PRIMARY KEY……………………………………………… 71

  1. MYSQL – TEMPORARY TABLES………………………………………………………………….. 72

What are Temporary Tables?…………………………………………………………………………. 72

Dropping Temporary Tables…………………………………………………………………………… 73

  1. MYSQL – CLONE TABLES………………………………………………………………………….. 74
  2. MYSQL – DATABASE INFO………………………………………………………………………… 76

Obtaining and Using MySQL Metadata………………………………………………………………. 76

Obtaining the Number of Rows Affected by a Query……………………………………………… 76

Listing Tables and Databases…………………………………………………………………………. 77

  1. MYSQL – USING SEQUENCES……………………………………………………………………. 79

Using AUTO_INCREMENT Column……………………………………………………………………. 79

Renumbering an Existing Sequence………………………………………………………………….. 80

  1. MYSQL – HANDLING DUPLICATES…………………………………………………………….. 82

Preventing Duplicates from Occurring in a Table………………………………………………….. 82

Counting and Identifying Duplicates…………………………………………………………………. 83

Eliminating Duplicates from a Query Result………………………………………………………… 84

Removing Duplicates Using Table Replacement…………………………………………………… 84

  1. MYSQL – SQL INJECTION………………………………………………………………………….. 86

Preventing SQL Injection………………………………………………………………………………. 87

The LIKE Quandary……………………………………………………………………………………… 87

  1. MYSQL – DATABASE EXPORTS………………………………………………………………….. 88

Exporting Data with the SELECT … INTO OUTFILE Statement……………………………………. 88

Exporting Tables as Raw Data………………………………………………………………………… 88

Copying Tables or Databases to Another Host……………………………………………………… 90

  1. MYSQL – DATABASE IMPORT…………………………………………………………………… 92

Importing Data with LOAD DATA…………………………………………………………………….. 92

Importing Data with mysqlimport……………………………………………………………………. 92

Handling Quotes and Special Characters……………………………………………………………. 93