MySQL

From Wikibooks, the open-content textbooks collection

Jump to: navigation, search

[edit] Synopsis

MySQL is a free, widely used SQL engine. It can be used as a fast database as well as a rock-solid SGDB using a modular engine architecture.

Wikipedia has related information at MySQL.


A printable version of MySQL is available. (edit it)

The purpose of this wikibook is to provide a practical knowledge on using the database from two points of view:

  • application developer: SQL basics, phpMyAdmin, query optimization
  • system administrator: installation, security, maintenance, failover, high availability

Best of all, this book is freely available for everybody to use and share, under the GNU Free Documentation License.

[edit] Contents

Wikibook Development Stages
Sparse text Image:00%.svg Developing text Image:25%.svg Maturing text Image:50%.svg Developed text Image:75%.svg Comprehensive text: Image:100%.svg


This is the table of contents, with the current progress. Click on a chapter title to go to its separate page.

[edit] Introduction

  1. Development stage: 50% (as of ) What is SQL?
  2. Development stage: 50% (as of ) Why MySQL?

[edit] Table types

  1. Development stage: 50% (as of ) MyISAM and InnoDB
  2. Development stage: 25% (as of ) Merge Table
  3. Development stage: 00% (as of ) MEMORY / HEAP
  4. Development stage: 00% (as of ) BDB
  5. BLACKHOLE
  6. Development stage: 25% (as of ) Miscellaneous

[edit] Language

The basic SQL commands

  1. Development stage: 75% (as of ) Browsing the databases
  2. Development stage: 50% (as of ) Specifying table names
  3. Development stage: 50% (as of ) Definitions
  4. Development stage: 50% (as of ) User Variables
  5. Development stage: 50% (as of ) Alias
  6. Development stage: 50% (as of ) Queries
  7. Development stage: 50% (as of ) Data manipulation
  8. Development stage: 50% (as of ) Table manipulation
  9. Development stage: 50% (as of ) Using/Dealing with NULL
  10. Development stage: 75% (as of ) Reserved Words
  11. Development stage: 50% (as of ) Data Types
  12. Development stage: 25% (as of ) Functions
  13. Development stage: 50% (as of ) Exercises
  1. Development stage: 50% (as of ) Pivot table or a crosstab report

[edit] Comparison

  • With other databases
  • With SQL92
  • Writing portable, non-MySQL-specific code

[edit] Administration

  1. Development stage: 50% (as of ) Installation
  2. Development stage: 50% (as of ) Start the service
  3. Development stage: 25% (as of ) Configuration
  4. Development stage: 50% (as of ) Privileges
  5. Development stage: 50% (as of ) Processes
  6. Security
  7. Development stage: 50% (as of ) Backup
  8. Logs
  9. Development stage: 25% (as of ) Admin Tools

[edit] Databases manipulation

  1. Development stage: 50% (as of ) Creation
  2. Development stage: 50% (as of ) Deletion
  3. Development stage: 75% (as of ) Rename
  4. Development stage: 50% (as of ) Copy
  5. Development stage: 00% (as of ) Migration from other databases
  6. Development stage: 50% (as of ) Tools for data modeling

[edit] Optimization

  • KEY/INDEX, EXPLAIN, ANALYZE TABLE table, BENCHMARK(count, expression), difference between KEY and PRIMARY KEY


[edit] Maintenance and evolution

  • ALTER TABLE, CREATE INDEX
  • tricks: IF NOT EXIST...
  • Software:
    • mysql -f (cf. tikiwiki's forgiving 1.10 update procedure)
    • Savane's migration scripts, in the update/version directory
  • Charsets: what are Latin1 and UTF-8? How to cleanly convert a database from Latin1 to UTF-8 (not just mysqldump|iconv, also ALTER TABLE table MODIFY col VARCHAR(100) CHARACTER SET utf8 + SET NAMES 'utf8' in PHP), phpMyAdmin bugs (need to add SET NAMES utf8 in some circumstances)

[edit] Appendixes

[edit] License

  1. GNU Free Documentation License

[edit] Suggestions

Case study: I was suggested to use SPIP (a system to manage articles and news) for a sample study. Mediawiki might be interesting as well :)


Add a list of CLIENTS to use with MYSQL, I dont know of any good ones, MYSQL GUI ones are ok, but I need some others

[edit] Links

[edit] Related wikibooks

[edit] Compatible sources

The following sources are released under the GFDL and hence good candidates for inclusion in the wikibook, as well as joint writing efforts:

[edit] External

Retrieved from "http://en.wikibooks.org/wiki/MySQL"
Personal tools
In other languages