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.
Author(s): Uday Khedker, Amitabha Sanyal, Bageshri Sathe
Series: Professional Computing
Edition: 1
Publisher: Springer
Year: 2003
Language: English
Pages: 220
Database Design Manual: using MySQL for Windows......Page 1
Cover Page......Page 2
Title Page......Page 4
Copyright......Page 5
Dedication......Page 6
Contents (linked)......Page 8
Who Can Use This Book?......Page 12
MySQL Databases......Page 13
Tables......Page 14
Columns......Page 15
Relations......Page 16
Client-server Systems......Page 17
Queries......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
Integers......Page 31
BOOL......Page 32
DECIMAL......Page 33
Floating Point Numbers......Page 34
Character Types......Page 35
VARCHAR......Page 36
Storing Text......Page 37
TIMESTAMP......Page 38
DATE......Page 39
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
LIKE......Page 71
SELECT AS (aliases)......Page 72
NOT......Page 74
CONCAT......Page 76
ORDER BY......Page 79
IN......Page 80
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
LEFT JOIN......Page 92
RIGHT JOIN......Page 94
UNION......Page 96
UPDATE......Page 98
ALTER......Page 101
Get it Right the First Time!......Page 107
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
NOW......Page 128
CURRENT_TIME......Page 129
MONTH......Page 130
DAYNAME......Page 131
WEEKDAY......Page 132
DAYOFYEAR......Page 133
YEARWEEK......Page 134
HOUR......Page 135
DATE_ADD......Page 136
DATE_SUB......Page 138
TO_DAYS......Page 139
TIME_FORMAT......Page 141
DATE_FORMAT......Page 142
Working with Dates......Page 144
Common Date Queries......Page 145
The User Table......Page 146
Creating a User......Page 148
Restricting a User’s Access......Page 149
The tables_priv Table......Page 152
The Grant Tables......Page 153
REVOKE......Page 155
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
Sending a Query......Page 199
Accessing the Records......Page 200
Testing the Script......Page 201
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
Index......Page 216
