Beginning Databases With Mysql

by Neil Matthew

• ISBN13: 9781861006929
• ISBN10: 1861006926
• Format: Trade Paper
• Copyright: 2002-02-01
• Publisher: Springer-Verlag New York Inc

Summary

Offers the database programmer with a range of options and capabilities rarely seen in other database servers. A complete tutorial on MySQL features and functions. Softcover.

Table of Contents

Introduction	1	(6)
Introduction To MySQL	7	(16)
Programming with Data	7	(2)
Flat File Databases	9	(1)
What Is a Database?	10	(1)
Database Types	11	(4)
Hierarchical Database Model	11	(1)
Network Database Model	11	(2)
Relational Database Model	13	(2)
Query Languages	15	(2)
Database Management Systems	17	(1)
What Is MySQL?	18	(2)
A Short History of MySQL	19	(1)
The MySQL Architecture	19	(1)
Open source Licensing	20	(1)
Resources	21	(2)
Relational Database Principles	23	(26)
Spreadsheets	23	(4)
Some Terminology	24	(1)
Limitations of Spreadsheets	25	(2)
What's Different About a Database?	27	(2)
Choosing Columns	27	(1)
Choosing a Data Type for Each Column	27	(1)
Identifying Rows Uniquely	28	(1)
Order of Rows	29	(1)
Putting Data into a Database	29	(4)
Access Across a Network	30	(1)
Slicing and Dicing Data	31	(2)
Database Design with Multiple Tables	33	(1)
Relationships Between Tables	34	(3)
Designing Tables	37	(2)
Some Basic Rules of Thumb	37	(1)
Rule one - Break Down the Data into Columns	38	(1)
Rule Two - Have a Unique Way of Identifying Each Row	38	(1)
Rule Three - Remove Repeating Information	38	(1)
Rule Four - Get the Naming Right	39	(1)
The Customer/Order Database	39	(5)
Extending Beyond Two Tables	40	(2)
Completing the Initial Design	42	(2)
Some Basic Data Types	44	(3)
Null	45	(2)
The Sample Database	47	(1)
Summary	47	(2)
Installing and Getting Started with MySQL	49	(30)
Install or Upgrade?	49	(2)
Installing MySQL from Linux/Unix Binaries	51	(4)
Anatomy of a MySQL Installation	53	(2)
Installing MySQL from Source Code	55	(15)
Starting MySQL	59	(3)
Creating the Database	62	(2)
Creating the Tables	64	(2)
Removing the Tables	66	(1)
Populating the Tables	66	(3)
Stopping MySQL	69	(1)
Installing MySQL On Windows	70	(7)
Installing MySQL from Windows Binaries	70	(2)
Compiling MySQL on Windows	72	(1)
Configuring MySQL for Windows	72	(3)
Starting MySQL Automatically	75	(1)
Using MySQL On Windows	76	(1)
Summary	77	(2)
Accessing Your Data	79	(40)
Using the mysql Command Line Tool	80	(3)
Simple Select Statements	83	(8)
Overriding Column Names	86	(1)
Controlling the Order of Rows	86	(2)
Suppressing Duplicates	88	(3)
Performing Calculations	91	(1)
Choosing the Rows	92	(15)
More Complex Conditions	95	(2)
Pattern Matching	97	(1)
Limiting the Result	98	(1)
Comparisons Using Other Types	99	(1)
Checking Null Values	99	(3)
Checking Dates and Time	102	(1)
Date and Time Functions	103	(3)
Selection Based On Dates and Times	106	(1)
Multiple Table Joins	107	(10)
Relating Two Tables	107	(5)
Aliasing Table Names	112	(1)
Relating Three Tables	113	(4)
Summary	117	(2)
MySQL Graphical Tools	119	(36)
mysql	119	(7)
Starting mysql	120	(1)
Commands in mysql	120	(1)
Command History	121	(1)
Scripting mysql	121	(1)
Examining the Database	122	(1)
Command Line Quick Reference	123	(2)
Internal Commands Quick Reference	125	(1)
KSql and KMySQL	126	(6)
Table Browser	127	(2)
HTML Export	129	(2)
Form Designer	131	(1)
MySQLGUI	132	(3)
ODBC	135	(5)
Microsoft Access	140	(6)
Linked Tables	140	(4)
Data Entry	144	(1)
Reports	145	(1)
Microsoft Excel	146	(5)
Resources	151	(1)
Summary	152	(3)
Changing Your Data	155	(24)
Adding Data To the Database	155	(17)
The INSERT Statement	156	(2)
Safer INSERT Statements	158	(2)
Alternative INSERT Syntax	160	(1)
Inserting Data into AUTO_INCREMENT Columns	160	(3)
Accessing the Last AUTO_INCREMENT Value	163	(1)
Inserting NULL Values	163	(2)
The LOAD DATA Command	165	(3)
Loading Data Using mysqlimport	168	(1)
Loading Data Directly from Another Application	169	(3)
Updating Data in the Database	172	(3)
A Word of Warning	173	(2)
Deleting Data from the Database	175	(2)
Summary	177	(2)
Advanced Data Selection	179	(30)
Aggregate Functions	180	(11)
COUNT()	180	(2)
GROUP BY and COUNT(*)	182	(2)
HAVING and COUNT(*)	184	(3)
COUNT(column name)	187	(1)
The MIN() Function	188	(1)
The MAX() Function	189	(1)
The SUM() Function	190	(1)
The AVG() Function	190	(1)
The Union Join	191	(2)
The Self Join	193	(2)
The Outer Join	195	(3)
Subqueries	198	(6)
Types of Subquery	200	(1)
Correlated Subqueries	201	(3)
Replacing Subqueries with Alternatives	204	(3)
Summary	207	(2)
Data Definition and Manipulation	209	(44)
Data Types	210	(8)
Boolean	210	(1)
Character	211	(3)
Number	214	(3)
Temporal	217	(1)
Operators	218	(8)
Operator Precedence and Associativity	219	(5)
Converting Between Types	224	(2)
Magic Variables	226	(1)
Built-in Functions	226	(4)
Comparison Functions	227	(1)
Numeric Functions	227	(1)
String Handling Functions	228	(2)
Date and Time Functions	230	(1)
Manipulating Tables	230	(14)
Creating Tables	231	(1)
Column Constraints	231	(3)
Table Constraints	234	(2)
Creating Tables Using SELECT	236	(2)
The MySQL Table Types	238	(2)
Altering Tables	240	(2)
Deleting Tables	242	(1)
Temporary Tables	243	(1)
Foreign Key Constraints	244	(7)
Declaring Foreign Key Constraints	245	(5)
Foreign Key Constraint Options	250	(1)
Deferrable	251	(1)
Summary	251	(2)
Transactions and Locking	253	(26)
What Are Transactions?	254	(4)
The ACID Rules	257	(1)
Transactions with Single Users	258	(1)
Transactions with Multiple Users	259	(6)
ANSI Isolation Levels	259	(1)
Undersirable Phenomena	259	(5)
ANSI/ISO definitions	264	(1)
Transaction Limitations	264	(1)
MySQL and Transactions	265	(9)
MySQL with MylSAM Tables	265	(4)
MySQL with InnoDB Tables	269	(5)
Deadlocks	274	(2)
Summary	276	(3)
MySQL Administration	279	(32)
Starting and Stopping Database Server	279	(6)
Windows	280	(2)
Linux	282	(2)
Server Versions	284	(1)
Adding and Removing Databases	285	(1)
Configuring Users	285	(5)
The Administrator Login - Root	285	(5)
Managing User Access and Privileges	290	(7)
Creating Users	290	(5)
Removing Rights from Users	295	(2)
Server Logs	297	(1)
Control Files	297	(7)
Server Defaults	298	(1)
Initial Configuration Files	298	(3)
InnoDB Files	301	(3)
Exploring Your Databases	304	(1)
Backing Up Your Data	304	(4)
Summary	308	(3)
Database Design	311	(30)
Understanding the Problem	311	(1)
What Is a Good Database Design?	312	(3)
Stages In Database Design	315	(10)
Gather Information	315	(1)
Logical Design	315	(2)
Determining Entities	317	(1)
Convert Entities To Tables	317	(4)
Determine Relationships and Cardinality	321	(1)
Drawing Entity Relationship Diagrams	321	(1)
Viewing the Example Database	322	(3)
Convert To a Physical Model	325	(7)
Establish Primary Keys	326	(1)
Establish Foreign Keys	327	(2)
Establish Data Types	329	(2)
Complete Table Definitions	331	(1)
Check the Design	332	(1)
Normal Forms	332	(3)
First Normal Form	333	(1)
Second Normal Form	334	(1)
Third Normal Form	334	(1)
Common Problem Patterns	335	(3)
Many-to-Many	335	(1)
Hierarchy	335	(1)
Recursive Relationships	336	(2)
Resources	338	(1)
Summary	339	(2)
Accessing MySQL from C and C++	341	(44)
Using the libmysqlclient Library	342	(2)
Database Connections	344	(4)
API Error Checking	348	(3)
Using a Makefile	349	(1)
More Information	349	(2)
Managing the Server Connection	351	(2)
Executing SQL with libmysqlclient	353	(5)
Transactions	358	(6)
Extracting Data from Queries	358	(6)
Scanning a Result Set	364	(1)
Living Without Cursors	365	(4)
API Summary	367	(2)
Using MySQL with C++	369	(1)
Mysql++	369	(14)
Installing Mysql++	369	(1)
Compiling with Mysql++	370	(2)
Connection Objects	372	(2)
Exceptions	374	(1)
Queries	375	(1)
Result Sets	376	(5)
Query Objects	381	(1)
Template Queries	382	(1)
Resources	383	(1)
Summary	383	(2)
Accessing MySQL from PHP	385	(40)
Adding MySQL Support to PHP	385	(2)
Using the PHP API for MySQL	387	(7)
Database Connections	390	(1)
mysql_connect()	390	(1)
Persistent Connections	391	(1)
Closing Connections	392	(1)
Connection Information	392	(1)
Executing Queries	393	(1)
Working with Result Sets	394	(14)
Extracting Values from Result Sets	396	(7)
Field Information	403	(5)
Freeing Result Sets	408	(1)
Error Handling	408	(15)
Using PEAR's Database Abstraction Interface	416	(1)
PEAR's Database Abstraction Interface	417	(1)
Using PEAR's DB Interface	417	(1)
Referencing The DB.php file	417	(1)
Creating a Data Source Name (DSN)	418	(1)
Connecting To the Database	418	(1)
Persistence of Connections	418	(1)
Issuing Queries To the Database	418	(1)
Extracting Information from the Result Objects	419	(3)
Query Preparation and Execution	422	(1)
Summary	423	(2)
Accessing MySQL from Perl	425	(38)
The Perl DBI	426	(34)
Installing DBI and the MySQL DBD	427	(2)
Using DBI	429	(29)
Using DBIx:Easy	458	(2)
Summary	460	(3)
Accessing MySQL from Java	463	(46)
JDBC Overview	464	(1)
JDBC Drivers	464	(2)
JDBC-ODBC Bridge	465	(1)
Native-API / Partly Java Driver	465	(1)
Net-protocol / All Java Driver	465	(1)
Native-protocol / All Java Driver	465	(1)
Building the MySQL JDBC Driver	466	(1)
DriverManager and Driver	466	(6)
java.sql.DriverManager	467	(1)
Managing Drivers	467	(1)
Managing Connections	468	(1)
Managing JDBC Logging	469	(1)
Managing Login Timeouts	469	(1)
java.sql.Driver	470	(2)
Connections	472	(4)
Creating Statements	472	(1)
Handling Transactions	473	(1)
Database Meta data	474	(1)
Retrieving MySQL Meta data	474	(2)
JDBC Result Sets	476	(6)
Result Set Types and Concurrency	476	(1)
Type	476	(1)
Concurrency	477	(1)
Traversing Result Sets	477	(1)
Scrolling Result Sets	477	(1)
Querying the Cursor Position	478	(1)
Fetch Direction and Size	479	(1)
Accessing Result Sets Data	479	(1)
Updatable Result Sets	480	(1)
Deleting Data	480	(1)
Updating Data	480	(1)
Inserting Data	481	(1)
Other Relevant Methods	482	(1)
JDBC Statements	482	(9)
Statements	483	(1)
Executing SQL Statements	483	(1)
Querying Results and Result Sets	484	(1)
Handling SQL Batches	484	(1)
Miscellaneous Methods	485	(1)
An Example JDBC Client	485	(2)
Prepared Statements	487	(1)
Executing SQL Statements	487	(1)
Updating Data	488	(1)
An Example using Prepared Statements	489	(2)
SQL Exceptions and Warnings	491	(1)
A JDBC GUI Application	491	(15)
Class Diagram	492	(1)
Item	492	(1)
Item TableModel	492	(1)
ItemPanel	493	(1)
ItemApp	493	(1)
System Interaction	494	(1)
View Item Details	494	(1)
Adding New Item	494	(2)
Deleting Item	496	(10)
Summary	506	(3)
Further Information and Resources	509	(8)
Non-Relational Storage	509	(1)
Database Terminology	510	(2)
Resources	512	(3)
Web Resources	512	(1)
General Tools	513	(1)
Books	513	(2)
Summary	515	(2)
Appendix A: MySQL Database Limits	517	(4)
Database Size: No Limit	518	(1)
Table Size: 64TB-16PB	518	(1)
Rows in a Table: 2^64	518	(1)
Table Indexes: 32	518	(1)
Column Size: 16MB-4GB	519	(1)
Columns in a Table: 1,000	519	(1)
Row Size: 4GB	519	(1)
Other Limitations	519	(2)
Appendix B: MySQL Data Types	521	(6)
Exact Number Types	521	(1)
Approximate Number Types	522	(1)
Temporal Types	523	(1)
Character Types	523	(1)
Miscellaneous Types	524	(3)
Appendix C: MySQL SQL Syntax	527	(12)
MySQL SQL Commands	527	(1)
MySQL SQL Syntax	528	(11)
Appendix D: mysql Reference	539	(4)
mysql Command Line Options	539	(2)
Internal Commands	541	(2)
Appendix E: Database Schema and Tables	543	(4)
Appendix F: Large Objects Support in MySQL	547	(4)
BLOBs	547	(2)
Binary Data in BLOBs	548	(1)
MyODBC BLOB Definition	548	(1)
Using GROUP BY On a BLOB	548	(1)
Programming BLOBs	549	(2)
Index	551