Database Design Manual: using MySQL for Windows (Springer Professional Computing)

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. TeamLiB......Page 0 Cover......Page 1 Contents......Page 8 Who Can Use This Book?......Page 11 MySQL Databases......Page 12 Tables......Page 13 Client-server Systems......Page 16 Structured Query Language......Page 17 Installing the Graphical Client Tool......Page 19 MySQL Directory Structure......Page 20 Starting the MySQL Service......Page 21 Connecting to the MySQL Service......Page 23 Connecting with the Graphical Tool......Page 25 MySQLGUI Quick Tour......Page 27 The Datatype......Page 29 Numeric Types......Page 30 Character Types......Page 34 Storing Text......Page 36 Date Types......Page 37 Other Types......Page 39 SET......Page 40 ENUM......Page 41 Redundant Data......Page 43 The Primary Key......Page 44 Foreign Keys......Page 45 Referential Integrity......Page 46 NULL......Page 47 CREATE DATABASE......Page 48 CREATE TABLE......Page 49 Identifying Foreign Keys......Page 50 Create from Select Statement......Page 52 CREATE TABLE LIKE......Page 53 INSERT......Page 55 INSERT – Multiple Rows......Page 56 INSERT – All Columns......Page 57 INSERT – Columns from a Query......Page 58 Inserting Data with Scripts......Page 59 Directly Inserting Data......Page 62 SELECT......Page 65 SELECT DISTINCT......Page 67 WHERE......Page 68 MySQL Operators......Page 69 Subqueries......Page 81 Information in Multiple Tables......Page 83 Cross Joins......Page 84 Equi-join......Page 85 Equi-joins on More Tables......Page 88 INNER JOIN – Another Format......Page 89 Outer Joins......Page 91 Altering Data......Page 97 UPDATE Revisited......Page 107 REPLACE......Page 108 DUPLICATE KEY UPDATE......Page 110 DELETE FROM......Page 113 DROP TABLE......Page 115 DROP......Page 116 Count......Page 117 GROUP BY......Page 118 MAX......Page 121 MIN......Page 122 Using Multiple Aggregates......Page 123 HAVING......Page 125 Using Date and Time......Page 127 Working with Dates......Page 143 Common Date Queries......Page 144 The User Table......Page 145 The tables_priv Table......Page 151 The Grant Tables......Page 152 Restricting Users......Page 155 Other Passwords......Page 156 How Well Does It Run?......Page 161 Examining Queries......Page 162 Looking at Cross-joins Again......Page 164 Indexing......Page 166 CREATE INDEX......Page 167 OPTIMIZE TABLE......Page 168 CHECK TABLE......Page 169 REPAIR TABLE......Page 170 ANALYZE TABLE......Page 171 Open Database Connectivity......Page 173 Installing MyODBC Drivers......Page 174 Setting Up a Data Source......Page 176 Example Connections......Page 180 MySQL and ASP......Page 181 Creating a Data Source in ColdFusion......Page 186 Accessing the Data Source in ColdFusion......Page 188 IndigoPerl......Page 193 A Quick PHP Script......Page 196 Accessing MySQL......Page 197 Working with Perl......Page 201 Defining a Perl and HTML Document......Page 202 Generating the Query......Page 203 Processing the Query Results......Page 204 Coping with Errors......Page 205 Reformatting the Output......Page 206 MySQL Control Centre......Page 209 PHPMyAdmin......Page 213 C......Page 215 F......Page 216 M......Page 217 S......Page 218 Y......Page 219 Essential SQL fast provides a comprehensive guide to SQL, using the Windows-based version of MySQL. MySQL is a freely available, multi-platform database management system, making it ideal for learning the techniques and concepts of database design. Using numerous worked examples and illustrations, Matthew Norman describes how to set up the database management system MySQL. In doing so he covers various topics, including: installing and testing MySQL; using the graphical tools; database design techniques and concepts; retrieving, changing and deleting data; and how to make other products communicate with MySQL