Database Design Manual: using MySQL for Windows

Matthew Norman9781852337162, 1852337168

Databases are now an integral part of the internet and many web sites use databases in the background to control their content. MySQL is one of the most commonly used open source database management systems. Due to it’s bundling with PHP, MySQL has proved a popular choice for many sites as it enables a database driven, content managed website to be set up with little or no software costs. This book will show you how to design and use databases for the web using MySQL as a tool to learn SQL. Key Topics Installing and testing MySQL SQL basics, and using SQL to communicate with databases Database design techniques and concepts Using MySQL with PHP, Apache and Perl How to make other products communicate with MySQL Features and Benefits. Gives an insight into how databases work in relation to the web. Introduces general SQL techniques by means of MySQL. Explains the concepts behind a structured query language and how it can be used to communicate with databases. Provides an introduction to database design and how an efficiently designed database can improve the performance of MySQL.

---

Table of contents :
TeamLiB……Page 1
Cover……Page 2
Contents……Page 9
Who Can Use This Book?……Page 12
MySQL Databases……Page 13
Tables……Page 14
Client-server Systems……Page 17
Structured Query Language……Page 18
Installing the Graphical Client Tool……Page 20
MySQL Directory Structure……Page 21
Starting the MySQL Service……Page 22
Connecting to the MySQL Service……Page 24
Connecting with the Graphical Tool……Page 26
MySQLGUI Quick Tour……Page 28
The Datatype……Page 30
Numeric Types……Page 31
Character Types……Page 35
Storing Text……Page 37
Date Types……Page 38
Other Types……Page 40
SET……Page 41
ENUM……Page 42
Redundant Data……Page 44
The Primary Key……Page 45
Foreign Keys……Page 46
Referential Integrity……Page 47
NULL……Page 48
CREATE DATABASE……Page 49
CREATE TABLE……Page 50
Identifying Foreign Keys……Page 51
Create from Select Statement……Page 53
CREATE TABLE LIKE……Page 54
INSERT……Page 56
INSERT – Multiple Rows……Page 57
INSERT – All Columns……Page 58
INSERT – Columns from a Query……Page 59
Inserting Data with Scripts……Page 60
Directly Inserting Data……Page 63
SELECT……Page 66
SELECT DISTINCT……Page 68
WHERE……Page 69
MySQL Operators……Page 70
Subqueries……Page 82
Information in Multiple Tables……Page 84
Cross Joins……Page 85
Equi-join……Page 86
Equi-joins on More Tables……Page 89
INNER JOIN – Another Format……Page 90
Outer Joins……Page 92
Altering Data……Page 98
UPDATE Revisited……Page 108
REPLACE……Page 109
DUPLICATE KEY UPDATE……Page 111
DELETE FROM……Page 114
DROP TABLE……Page 116
DROP……Page 117
Count……Page 118
GROUP BY……Page 119
MAX……Page 122
MIN……Page 123
Using Multiple Aggregates……Page 124
HAVING……Page 126
Using Date and Time……Page 128
Working with Dates……Page 144
Common Date Queries……Page 145
The User Table……Page 146
The tables_priv Table……Page 152
The Grant Tables……Page 153
Restricting Users……Page 156
Other Passwords……Page 157
How Well Does It Run?……Page 162
Examining Queries……Page 163
Looking at Cross-joins Again……Page 165
Indexing……Page 167
CREATE INDEX……Page 168
OPTIMIZE TABLE……Page 169
CHECK TABLE……Page 170
REPAIR TABLE……Page 171
ANALYZE TABLE……Page 172
Open Database Connectivity……Page 174
Installing MyODBC Drivers……Page 175
Setting Up a Data Source……Page 177
Example Connections……Page 181
MySQL and ASP……Page 182
Creating a Data Source in ColdFusion……Page 187
Accessing the Data Source in ColdFusion……Page 189
IndigoPerl……Page 194
A Quick PHP Script……Page 197
Accessing MySQL……Page 198
Working with Perl……Page 202
Defining a Perl and HTML Document……Page 203
Generating the Query……Page 204
Processing the Query Results……Page 205
Coping with Errors……Page 206
Reformatting the Output……Page 207
MySQL Control Centre……Page 210
PHPMyAdmin……Page 214
C……Page 216
F……Page 217
M……Page 218
S……Page 219
Y……Page 220