MySQL数据库管理和开发

MySQL

课程简介:
MySQL® is the open source community’s most popular Relational Database Management System (RDBMS) offering, and is a key part of LAMP – LinuxTM, ApacheTM, MySQL®, PHP/Perl/Python®. Many Fortune 500 companies adopt MySQL to reap the benefits of an open source, platform-independent RDMS, such as simplifying conversion from other platforms and lowering database Total Cost of Ownership by 90%. This class encourages the student to explore database fundamentals, as well as MySQL features. Students learn the basics of MySQL use and the programming of stored routines and triggers. Students also participate in database design discussions, perform administrative functions, learn about optimization and performance tuning, and explore various APIs. This course covers MySQL 5.5.

培训对象:
Application and web developers, or system administrators.

预备知识:
Prior experience installing software and programming in any language, such as HTML,
is recommended but not required.

课时:4 days

课程简介:
1. Course Introduction
Course Objectives Course Overview Using the Workbook Suggested References
2. Introduction to Database Concepts and MySQL
Features of a Relational Database Where does SQL Fit in? Database Access
Why MySQL?
The History of MySQL
3. Installation, Configuration, and Upgrading
MySQL Software
MySQL Software Features Preparing to Install MySQL Available Client Software After the Download Configuring the Server Starting the Server
The Initial User Accounts Verifying Server Operation Upgrading
Copying a Database between
Architectures Environment Variables
4. Database Design
Developing the Design of a Database Database Entities
The Primary Key
Foreign Key Relationships
Data Models and Normalization Second Normal Form (2NF)
Third Normal Form (3NF) and Beyond Translating a Data Model into a
Database Design
5. The mysql Command-Line Tool
Running the mysql Client Customizing the mysql Prompt mysql Commands
Using the Help Command Some Useful mysql Options Working with a Database Examining Table Definitions Other SHOW Options
6. DDL – Data Definition Language
DDL & DML Overview Building Table Definitions Identifiers
Column Definitions Numeric Datatypes ENUM and SET Types Date and Time Datatypes AUTO_INREMENT
UNIQUE Constraints
Primary Keys
Modifying Tables
Foreign Keys
Renaming and Dropping Tables
7. DML – Data Manipulation Language
DDL & DML Overview Data Values: Numbers
Data Values: Strings Working with NULL Values Bulk Loading of Data
Bulk Data Format
Working with Special Values in Bulk
Data
Adding New Table Rows with INSERT Copying Rows
UPDATE
REPLACE
Removing Table Rows
Transactions
InnoDB: Using Transactional Processing Locking Tables
8. Queries – The SELECT Statement
SELECT Syntax Summary
Choosing Data Sources and Destinations
for SELECT Presentation of Table Data with
SELECT
Being Selective about Which Rows are
Displayed User-Defined Variables
Expressions and Functions
Control Flow Operators and Functions Function Names
Comparison Operators and Functions String Functions
Numeric Operators and Functions Date and Time Functions
Forcing Data Interpretation Miscellaneous Functions
9. Building a Result Set from Several Sources
UNION
Combining Data from Two Tables Using WHERE to Choose Matching
Rows INNER JOIN
OUTER JOINs
Multiple Tables, Fields, Joins, and
Ordering
SELECT * and USING Columns
10. Advanced SQL Techniques
MySQL Pattern Matching
Multipliers, Anchors, and Grouping GROUP BY
Aggregates
Subqueries
Subquery Comparisons and Quantifiers Other Subqueries
Subquery Alternatives and Restrictions InnoDB Multi-Table Updates and
Deletes Building a VIEW
Updatable VIEWs
11.MySQL Storage Engines
Storage Engine Overview
Other Storage Engine Types
The Basics of Commonly Used Storage
Engines
MyISAM Limits and Features MyISAM Data File Format InnoDB and Hardware Limitations InnoDB Shared Tablespace
Configuration
InnoDB Per-Table Tablespaces InnoDB Data Management
MEMORY and FEDERATED MERGE and ARCHIVE
12. Utilities
Client Overview
Specifying Options for Command-Line
Clients Client Option Files
Checking Tables with myisamchk and mysqlchk
Using myisamchk and mysqlchk for Repairs
mysqlshow and mysqlimport Using mysqldump
The MySQL Workbench – General MySQL Workbench – Execution MySQL Administration via the
Workbench
Data Modeling with the Workbench
SQL Development
Third Party Tools
13. Administering a Database and Users
The Server-Side Programs Starting the MySQL Server Using SET for Server Options Table Management
Server Log Files
mysqladmin
Backup and Restore Miscellaneous Functions
User Account Management Understanding User Privileges User Account Rights Management User Account Privileges Managing Access to the Database
Environment
14. Database Programmability
Stored Routines: Basic Concepts Routine Creation and Use
Flow Control Statement
Writing Blocks of Code Triggers
Stored Routines, Triggers, and the Binary Log
Table HANDLERs
Prepared Statements
15. Optimization and Performance Tuning
Hardware Limitations Optimizing the MySQL Server’s
Interaction with the External
World
Adjusting the MySQL Server
Configuration Optimizing Your Database
Table Partitioning
Optimizing Queries
The Use of Indexes to Support Queries Thinking about JOIN Queries
Query Sorts, Indexes, and Short-
Circuiting
INSERT, UPDATE, DELETE, and
Table Locks
Some General Optimizations
Optimizations Specific to MyISAM
Optimizations Specific to InnoDB
16. MySQL Programming Interfaces
Database Application Architectures Connecting MySQL to ODBC Connecting MySQL to MS/Office and
MS/Access
Connecting to MySQL from Perl Programming Perl to MySQL Connecting to MySQL from PHP Programming PHP to MySQL

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注