MySQL

This page is brought to you by Wikimedia Laboratories
Jump to: navigation, search

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-logo.png

Wikipedia has related information at MySQL.


Document-print.svg

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.

Contents

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


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

Introduction

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

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

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

Comparison

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

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

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

Optimization

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


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)

Appendixes

License

  1. GNU Free Documentation License

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

Links

Related wikibooks

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:

External

Retrieved from "http://en.labs.wikimedia.org/w/index.php?title=MySQL&oldid=7173"
Personal tools
Namespaces

Variants
Actions
Navigation
FlaggedRevs
Print/export
Toolbox
In other languages