Opw

PHP + MySQL – 48hrs

Higher Certificate in OpenSource Web Developer (MySQL + PHP)

課程簡介 在開放原始碼運動內，除了操作系統的原始碼被公開外，另一熱門軟件的原始碼也是被大眾期待公開的，這軟件便是資料庫軟 件。其中最為有名的便是 MySQL 軟件，利用 MySQL 配合 Linux 或 UNIX 作業系統，便能建構出完整的系統架構，達到企業對 資訊管理的需求。 當 MySQL 的資料庫被建立後，如何建立動態網頁存取資料便成了下一個重要的課題，而配合這方面的程式語言便是 PHP Supporting Language。利用 PHP，我們可以很容易編寫動態網頁，製作出實用的網上程式。

課程目標 本課程針對教授 MySQL 資料庫的安裝及管理，學員除了學習基本 SQL 指令的運用外，更可學懂資料庫保安、資料複製、資料備 份及還原。修畢課程，更可考取 MySQL 的 Core Certification 及 Certified MySQL Professional 的專業認證。除了 MySQL 的專業認證外，學員亦可考取 Zend 推出的 PHP 認證

課程內容 Module 1: PHP Programming The Basics of PHP

— — — — — — — —

Introducing PHP PHP and HTML PHP and XML PHP and databases Creating a PHP page Using PHP tags Language basics

— — — — — — —

Identifiers

— — —

Attributes

— — —

Cookies

— — —

Reordering

— — —

Regular expressions

— —

Closing a file

— — — —

Date calculations

— —

Formatting an e-mail message

— —

Extracting data

Operators Conditional structures Iteration and loops Functions Variable functions Including files

Constants and variables

Object-oriented PHP

— — —

The basics of object oriented programming Classes Instantiation and constructors

Accessing objects, methods and attributes Implementing inheritance

PHP and the Web

— — — —

Creating HTML content using PHP Client side and server side embedding PHP in HTML Creating forms

The superarrays: accessing global information Session management

GET and POST: accessing form variables

Arrays

— — — —

Variable collections: introducing arrays Numerically indexed and associative arrays Multidimensional arrays

Serializing arrays Manipulating arrays

Sorting

Strings and Regular Expressions

— — —

Formatting strings Comparing strings Modifying string contents

Extract in data from strings Joining and splitting strings

File Manipulation

— — —

Opening a file Writing to a file

File locking

Reading from a file

Managing Date and Time

— — — —

Dates in PHP Getting the current date Converting a string into a date More date functions

Dates and databases Using the calendar functions The UNIX epoch and the dangers of UNIX dates

Managing E-mail

— —

Sending e-mail Managing the headers

Attaching a file to a message

PHP and Databases

— — —

When to use a database Types of databases supported by PHP Inserting data

Joining data

Stream and Network Programming

— —

Using file wrappers Connecting to a remote host using sockets

— —

Blocking and non-blocking calls

— —

Validating input

— — —

Avoiding bugs

—

Invoking Client programs

— — — —

mysql output formats

— —

Connector/ODBC

— — —

Numeric Data Types

— —

Using Qualifed Names

— —

Creating Databases

— — — —

Emptying Tables

— — —

Rows to Retrieve

— — —

Comments in SQL Statements

— — —

The UPDATE Statement

Creating and using streams

Security

— — —

Trusted and untrusted input Managing authentication

Preventing XSS attacks

Escaping data for database and shell programming

Debugging and Performance Management

— — —

Common programming mistakes Programming logic Creating good code

Avoiding performance pitfalls Troubleshooting performance bottlenecks

Module 2: MySQL Database Development Client/Server Concepts

— —

Server SQL modes General MySQL Architecture

The mysql Client Program

— — — — —

The MySQL Prompts Editing keys in mysql Client Commands and SQL statements Statement Terminators

Using Script Files with MySQL Using mysql interactively Using server-side help

Using the --safe-updates Option

Connectors

— —

MySQL Client Interfaces Connector/J

Connector/NET

Data Types

— — — —

Column Attributes The BIT Data Type Temporal Data Types

String Data Types Handling Missing or Invalid Data Values

Using the AUTO_INCREMENT Column Attribute

Identifiers

— —

Reserved Words as Identifiers Identifier Syntax

Case Sensitivity

Databases

— — —

Database properties Altering Databases

Dropping Databases

Obtaining Database Metatdata

Tables and Indexes

— — — —

Indexes Table Properties Obtaining Table and Index Metatadata Altering tables

Dropping Indexes Creating Tables Dropping Tables

Querying for Data

— — —

Aggregating Results Using SELECT to retrieve data Using UNION

Columns to Retrieve Grouping Results

SQL expressions

— — — —

Components of SQL expressions String Expressions Numeric Expressions

Temporal Expressions Functions in SQL expressions

NULL values

Updating Data

— — —

Update Operations Privileges Required for Update Statements The INSERT Statement

The DELETE and TRUNCATE TABLE Statements The REPLACE Statement

Joins

— — —

Multiple-Table UPDATE and DELETE Statements Writing Outer Joins

— —

Resolving Name Clashes Using Qualifiers and Aliase

— — — —

Using Subqueries in Updates

— — —

Checking views

—

Import and Export Operations

—

User Variable Properties

— —

Preparing

— — — — —

Stored Routine Privileges and Execution Security

— — —

Referring to Old and New Column Values

— —

Using SHOW and DESCRIBE to Obtain Metadata

— —

The perror Utility

— —

Normalization

Writing Inner Joins

Overview

Subqueries

— — — —

Subqueries as Scalar Expressions Converting Subqueries to Joins Comparison Using Row Subqueries Correlated Subqueries

Types of Subqueries Using Subqueries in the FROM Clause Comparing Subquery Results to Outer Query Columns

Views

— — — —

Dropping a view Displaying information about views Reasons to use views

Privileges for views Creating views

Altering views

Importing and Exporting Data

— —

Importing and Exporting Using SQL Importing and Exporting Data from the Command Line

User Variables

—

User Variable Syntax

Prepared Statements

— — —

Executing Using Prepared Statements from the mysql Client

Benefits of Prepared Statements

Deallocating Prepared Statements

Stored Routines

— — — — —

Defining Stored Routines Obtaining Stored Routine Metadata Differences Between Stored Procedures and Function Dropping Stored Routines Creating Stored Routines

The Namespace for Stored Routines Invoking Stored Routines Benefits of Stored Routines Altering Stored Routines

Triggers

— — — —

Destroying Restrictions on Triggers Trigger Concepts

Creating Reasons to Use Triggers

Privileges Required for Triggers

Obtaining Database Metadata

— —

Using mysqlshow to Obtain Metadata Using INFORMATION_SCHEMA to Obtain Metadata

Overview of Metadata Access Methods

Debugging MySQL Applications

— —

The SHOW ERRORS Statement Interpreting Error Messages

The SHOW WARNINGS Statement

Basic Optimizations

— — —

Overview of Optimization Principles Choosing Appropriate Storage Engines Using Indexes for Optimization

General Query Enhancement