MySQL in a Nutshell, 2nd Edition

MySQL in a Nutshell, 2nd Edition

by Russell J. T. Dyer
Released April 2008
Publisher(s): O'Reilly Media, Inc.
ISBN: 9781449379377

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

When you need to find the right SQL keyword or MySQL client command-line option right away, turn to this convenient reference, known for the same speed and flexibility as the system it covers so thoroughly. MySQL is packed with so many capabilities that the odds of remembering a particular function or statement at the right moment are pretty slim. With MySQL in a Nutshell, you get the details you need, day in and day out, in one concise and extremely well organized book.

The new edition contains all the commands and programming information for version 5.1, including new features and language interfaces. It's ideal for anyone using MySQL, from novices who need to get up to speed to advanced users who want a handy reference. Like all O'Reilly Nutshell references, it's easy to use and highly authoritative, written by the editor of the MySQL Knowledge Base at MySQL AB, the creator and owner of MySQL.

Inside, you'll find:

  • A thorough reference to MySQL statements, functions, and administrative utilities
  • Several tutorial chapters to help newcomers get started
  • Programming language APIs for PHP, Perl, and C
  • Brief tutorials at the beginning of each API chapter to help anyone, regardless of experience level, understand and master unfamiliar territory
  • New chapters on replication, triggers, and stored procedures
  • Plenty of new examples of how MySQL is used in practice
  • Useful tips to help you get through the most difficult subjects
Whether you employ MySQL in a mission-critical, heavy-use environment or for applications that are more modest, this book puts a wealth of easy-to-find information at your fingertips, saving you hundreds of hours of trial and error and tedious online searching. If you're ready to take advantage of everything MySQL has to offer, MySQL in a Nutshell has precisely what it takes.

Publisher resources

View/Submit Errata

Table of contents

  1. MySQL in a Nutshell
    1. SPECIAL OFFER: Upgrade this ebook with O’Reilly
    2. Preface
      1. The Purpose of This Book
      2. How This Book Is Organized
        1. Part I, Introduction and Tutorials
        2. Part II, SQL Statements and Functions
        3. Part III, MySQL Server and Client Tools
        4. Part IV, MySQL API
        5. Appendixes
      3. Conventions Used in This Book
      4. Using Code Examples
      5. Request for Comments
      6. Safari® Enabled
      7. Acknowledgments
    3. I. Introduction and Tutorials
      1. 1. Introduction to MySQL
        1. The Value of MySQL
        2. The MySQL Package
        3. Licensing
        4. Mailing Lists
        5. Books and Other Publications
      2. 2. Installing MySQL
        1. Choosing a Distribution
        2. Unix Source Distributions
        3. Unix Binary Distributions
        4. Linux RPM Distributions
        5. Macintosh OS X Distributions
        6. Novell NetWare Distributions
        7. Windows Distributions
        8. Postinstallation
      3. 3. MySQL Basics
        1. The mysql Client
        2. Creating a Database and Tables
        3. Show Me
        4. Inserting Data
        5. Selecting Data
        6. Ordering, Limiting, and Grouping
        7. Analyzing and Manipulating Data
        8. Changing Data
        9. Deleting Data
        10. Searching Data
        11. Importing Data in Bulk
        12. Command-Line Interface
        13. Conclusion
    4. II. SQL Statements and Functions
      1. 4. Security and User Statements and Functions
        1. Statements and Functions
        2. SQL Statements in Alphabetical Order
          1. CREATE USER
          2. DROP USER
          3. FLUSH
          4. GRANT
          5. RENAME USER
          6. RESET
          7. REVOKE
          8. SET PASSWORD
          9. SHOW GRANTS
          10. SHOW PRIVILEGES
        3. Functions in Alphabetical Order
          1. AES_DECRYPT()
          2. AES_ENCRYPT()
          3. CURRENT_USER()
          4. DECODE()
          5. DES_DECRYPT()
          6. DES_ENCRYPT()
          7. ENCODE()
          8. ENCRYPT()
          9. MD5()
          10. OLD_PASSWORD()
          11. PASSWORD()
          12. SESSION_USER()
          13. SHA()
          14. SHA1()
          15. SYSTEM_USER()
          16. USER()
      2. 5. Database and Table Schema Statements
        1. Statements and Clauses in Alphabetical Order
          1. ALTER DATABASE
          2. ALTER SCHEMA
          3. ALTER SERVER
          4. ALTER TABLE
          5. ALTER VIEW
          6. CREATE DATABASE
          7. CREATE INDEX
          8. CREATE SCHEMA
          9. CREATE SERVER
          10. CREATE TABLE
          11. CREATE VIEW
          12. DESCRIBE
          13. DROP DATABASE
          14. DROP INDEX
          15. DROP SERVER
          16. DROP TABLE
          17. DROP VIEW
          18. RENAME DATABASE
          19. RENAME TABLE
          20. SHOW CHARACTER SET
          21. SHOW COLLATION
          22. SHOW COLUMNS
          23. SHOW CREATE DATABASE
          24. SHOW CREATE TABLE
          25. SHOW CREATE VIEW
          26. SHOW DATABASES
          27. SHOW INDEXES
          28. SHOW SCHEMAS
          29. SHOW TABLE STATUS
          30. SHOW TABLES
          31. SHOW VIEWS
      3. 6. Data Manipulation Statements and Functions
        1. Statements and Functions Grouped by Characteristics
          1. Data Manipulation Statements
          2. Transaction Statements
          3. Related Functions
        2. Statements and Clauses in Alphabetical Order
          1. BEGIN
          2. COMMIT
          3. DELETE
          4. DO
          5. EXPLAIN
          6. HANDLER
          7. HELP
          8. INSERT
          9. JOIN
          10. LIMIT
          11. LOAD DATA INFILE
          12. RELEASE SAVEPOINT
          13. REPLACE
          14. ROLLBACK
          15. ROLLBACK TO SAVEPOINT
          16. SAVEPOINT
          17. SELECT
          18. SET
          19. SET TRANSACTION
          20. SHOW ERRORS
          21. SHOW WARNINGS
          22. START TRANSACTION
          23. TRUNCATE
          24. UNION
          25. UPDATE
          26. USE
          27. XA
        3. Functions in Alphabetical Order
          1. ANALYSE()
          2. BENCHMARK()
          3. DATABASE()
          4. FOUND_ROWS()
          5. LAST_INSERT_ID()
          6. ROW_COUNT()
          7. SCHEMA()
      4. 7. Table and Server Administration Statements and Functions
        1. Statements and Clauses in Alphabetical Order
          1. ALTER SERVER
          2. ANALYZE TABLE
          3. BACKUP TABLE
          4. CACHE INDEX
          5. CHECK TABLE
          6. CHECKSUM TABLE
          7. CREATE SERVER
          8. FLUSH
          9. KILL
          10. LOAD INDEX INTO CACHE
          11. LOCK TABLES
          12. OPTIMIZE TABLE
          13. REPAIR TABLE
          14. RESET
          15. RESTORE TABLE
          16. SET
          17. SHOW ENGINE
          18. SHOW ENGINES
          19. SHOW OPEN TABLES
          20. SHOW PLUGINS
          21. SHOW PROCESSLIST
          22. SHOW STATUS
          23. SHOW TABLE STATUS
          24. SHOW VARIABLES
          25. UNLOCK TABLES
        2. Functions in Alphabetical Order
          1. CONNECTION_ID()
          2. GET_LOCK()
          3. IS_FREE_LOCK()
          4. IS_USED_LOCK()
          5. RELEASE_LOCK()
          6. UUID()
          7. VERSION()
      5. 8. Replication Statements and Functions
        1. Merits of Replication
        2. Replication Process
        3. The Replication User Account
        4. Configuring the Servers
        5. Copying Databases and Starting Replication
          1. Using mysqldump
          2. Alternative Methods for Making Copies
        6. Starting Replication
        7. Backups with Replication
        8. SQL Statements and Functions in Alphabetical Order
          1. CHANGE MASTER TO
          2. LOAD DATA FROM MASTER
          3. LOAD TABLE...FROM MASTER
          4. MASTER_POS_WAIT()
          5. PURGE MASTER LOGS
          6. RESET MASTER
          7. RESET SLAVE
          8. SET GLOBAL SQL_SLAVE_SKIP_COUNTER
          9. SET SQL_LOG_BIN
          10. SHOW BINLOG EVENTS
          11. SHOW BINARY LOGS
          12. SHOW MASTER LOGS
          13. SHOW MASTER STATUS
          14. SHOW SLAVE HOSTS
          15. SHOW SLAVE STATUS
          16. START SLAVE
          17. STOP SLAVE
        9. Replication States
          1. Master BinLog Dump Thread States
          2. Slave I/O Thread States
          3. Slave SQL Thread States
      6. 9. Stored Routines Statements
        1. Statements in Alphabetical Order
          1. ALTER EVENT
          2. ALTER FUNCTION
          3. ALTER PROCEDURE
          4. ALTER TRIGGER
          5. BEGIN...END
          6. CALL
          7. CLOSE
          8. CREATE EVENT
          9. CREATE FUNCTION
          10. CREATE PROCEDURE
          11. CREATE TRIGGER
          12. DECLARE
          13. DELIMITER
          14. DROP EVENT
          15. DROP FUNCTION
          16. DROP PREPARE
          17. DROP PROCEDURE
          18. DROP TRIGGER
          19. EXECUTE
          20. FETCH
          21. OPEN
          22. PREPARE
          23. SHOW CREATE EVENT
          24. SHOW CREATE FUNCTION
          25. SHOW CREATE PROCEDURE
          26. SHOW EVENTS
          27. SHOW FUNCTION CODE
          28. SHOW FUNCTION STATUS
          29. SHOW PROCEDURE CODE
          30. SHOW PROCEDURE STATUS
          31. SHOW TRIGGERS
      7. 10. Aggregate Clauses, Aggregate Functions, and Subqueries
        1. Aggregate Functions in Alphabetical Order
          1. AVG()
          2. BIT_AND()
          3. BIT_OR()
          4. BIT_XOR()
          5. COUNT()
          6. GROUP_CONCAT()
          7. MAX()
          8. MIN()
          9. STD()
          10. STDDEV()
          11. STDDEV_POP()
          12. STDDEV_SAMP()
          13. SUM()
          14. VAR_POP()
          15. VAR_SAMP()
          16. VARIANCE()
        2. Subqueries
          1. Single Field Subqueries
          2. Multiple Fields Subqueries
          3. Results Set Subqueries
      8. 11. String Functions
        1. String Functions Grouped by Type
          1. Character Sets and Collation
          2. Converting
          3. Formatting
          4. Expressions
          5. Extracting
          6. Manipulating
        2. String Functions in Alphabetical Order
          1. ASCII()
          2. BIN()
          3. BINARY
          4. BIT_LENGTH()
          5. CAST()
          6. CHAR()
          7. CHAR_LENGTH()
          8. CHARACTER_LENGTH()
          9. CHARSET()
          10. COALESCE()
          11. COERCIBILITY()
          12. COLLATION()
          13. COMPRESS()
          14. CONCAT()
          15. CONCAT_WS()
          16. CONVERT()
          17. CRC32()
          18. ELT()
          19. EXPORT_SET()
          20. FIELD()
          21. FIND_IN_SET()
          22. HEX()
          23. INSERT()
          24. INSTR()
          25. INTERVAL()
          26. LCASE()
          27. LEFT()
          28. LENGTH()
          29. LOAD_FILE()
          30. LOCATE()
          31. LOWER()
          32. LPAD()
          33. LTRIM()
          34. MAKE_SET()
          35. MATCH() AGAINST()
          36. MID()
          37. OCTET_LENGTH()
          38. ORD()
          39. POSITION()
          40. QUOTE()
          41. REPEAT()
          42. REPLACE()
          43. REVERSE()
          44. RIGHT()
          45. RPAD()
          46. RTRIM()
          47. SOUNDEX()
          48. SPACE()
          49. STRCMP()
          50. SUBSTR()
          51. SUBSTRING()
          52. SUBSTRING_INDEX()
          53. TRIM()
          54. UCASE()
          55. UNCOMPRESS()
          56. UNCOMPRESSED_LENGTH()
          57. UNHEX()
          58. UPPER()
      9. 12. Date and Time Functions
        1. Date and Time Functions Grouped by Type
          1. Determining the Date or Time
          2. Extracting and Formatting the Date or Time
          3. Calculating and Modifying the Date or Time
        2. Date and Time Functions in Alphabetical Order
          1. ADDDATE()
          2. ADDTIME()
          3. CONVERT_TZ()
          4. CURDATE()
          5. CURRENT_DATE()
          6. CURRENT_TIME()
          7. CURRENT_TIMESTAMP()
          8. CURTIME()
          9. DATE()
          10. DATE_ADD()
          11. DATE_FORMAT()
          12. DATE_SUB()
          13. DATEDIFF()
          14. DAY()
          15. DAYNAME()
          16. DAYOFMONTH()
          17. DAYOFWEEK()
          18. DAYOFYEAR()
          19. EXTRACT()
          20. FROM_DAYS()
          21. FROM_UNIXTIME()
          22. GET_FORMAT()
          23. HOUR()
          24. LAST_DAY()
          25. LOCALTIME()
          26. LOCALTIMESTAMP()
          27. MAKEDATE()
          28. MAKETIME()
          29. MICROSECOND()
          30. MINUTE()
          31. MONTH()
          32. MONTHNAME()
          33. NOW()
          34. PERIOD_ADD()
          35. PERIOD_DIFF()
          36. QUARTER()
          37. SEC_TO_TIME()
          38. SECOND()
          39. SLEEP()
          40. STR_TO_DATE()
          41. SUBDATE()
          42. SUBTIME()
          43. SYSDATE()
          44. TIME()
          45. TIME_FORMAT()
          46. TIME_TO_SEC()
          47. TIMEDIFF()
          48. TIMESTAMP()
          49. TIMESTAMPADD()
          50. TIMESTAMPDIFF()
          51. TO_DAYS()
          52. UNIX_TIMESTAMP()
          53. UTC_DATE()
          54. UTC_TIME()
          55. UTC_TIMESTAMP()
          56. WEEK()
          57. WEEKDAY()
          58. WEEKOFYEAR()
          59. YEAR()
          60. YEARWEEK()
      10. 13. Mathematical Functions
        1. Functions in Alphabetical Order
          1. ABS()
          2. ACOS()
          3. ASIN()
          4. ATAN()
          5. ATAN2()
          6. BIT_COUNT()
          7. CEIL()
          8. CEILING()
          9. CONV()
          10. COS()
          11. COT()
          12. DEGREES()
          13. EXP()
          14. FLOOR()
          15. FORMAT()
          16. GREATEST()
          17. INET_ATON()
          18. INET_NTOA()
          19. LEAST()
          20. LN()
          21. LOG()
          22. LOG2()
          23. LOG10()
          24. MOD()
          25. OCT()
          26. PI()
          27. POW()
          28. POWER()
          29. RADIANS()
          30. RAND()
          31. ROUND()
          32. SIGN()
          33. SIN()
          34. SQRT()
          35. TAN()
          36. TRUNCATE()
      11. 14. Flow Control Functions
        1. Functions in Alphabetical Order
          1. CASE
          2. IF()
          3. IFNULL()
          4. ISNULL()
          5. NULLIF()
    5. III. MySQL Server and Client Tools
      1. 15. MySQL Server and Client
        1. mysql Client
          1. mysql
        2. mysqld Server
          1. mysqld
        3. mysqld_multi
          1. mysqld_multi
        4. mysqld_safe
          1. mysqld_safe
      2. 16. Command-Line Utilities
        1. comp_err
        2. make_binary_distribution
        3. msql2mysql
        4. my_print_defaults
        5. myisam_ftdump
        6. myisamchk
        7. myisamlog
        8. myisampack
        9. mysql_convert_table_format
        10. mysql_find_rows
        11. mysql_fix_extensions
        12. mysql_fix_privilege_tables
        13. mysql_setpermission
        14. mysql_tableinfo
        15. mysql_upgrade
        16. mysql_waitpid
        17. mysql_zap
        18. mysqlaccess
        19. mysqladmin
        20. mysqlbinlog
        21. mysqlbug
        22. mysqlcheck
        23. mysqldump
        24. mysqldumpslow
        25. mysqlhotcopy
        26. mysqlimport
        27. mysqlshow
        28. mysqlslap
        29. perror
        30. replace
        31. resolveip
        32. resolve_stack_dump
    6. IV. APIs and Connectors
      1. 17. C API
        1. Using C with MySQL
          1. Connecting to MySQL
          2. Querying MySQL
        2. Functions in Alphabetical Order
          1. mysql_affected_rows()
          2. mysql_autocommit()
          3. mysql_change_user()
          4. mysql_character_set_name()
          5. mysql_close()
          6. mysql_commit()
          7. mysql_connect()
          8. mysql_create_db()
          9. mysql_data_seek()
          10. mysql_debug()
          11. mysql_drop_db()
          12. mysql_dump_debug_info()
          13. mysql_eof()
          14. mysql_errno()
          15. mysql_error()
          16. mysql_escape_string()
          17. mysql_fetch_field()
          18. mysql_fetch_field_direct()
          19. mysql_fetch_fields()
          20. mysql_fetch_lengths()
          21. mysql_fetch_row()
          22. mysql_field_count()
          23. mysql_field_seek()
          24. mysql_field_tell()
          25. mysql_free_result()
          26. mysql_get_client_info()
          27. mysql_get_character_set_info()
          28. mysql_get_client_version()
          29. mysql_get_host_info()
          30. mysql_get_proto_info()
          31. mysql_get_server_info()
          32. mysql_get_server_version()
          33. mysql_get_ssl_cipher()
          34. mysql_hex_string()
          35. mysql_info()
          36. mysql_init()
          37. mysql_insert_id()
          38. mysql_kill()
          39. mysql_library_end()
          40. mysql_library_init()
          41. mysql_list_dbs()
          42. mysql_list_fields()
          43. mysql_list_processes()
          44. mysql_list_tables()
          45. mysql_more_results()
          46. mysql_next_result()
          47. mysql_num_fields()
          48. mysql_num_rows()
          49. mysql_options()
          50. mysql_ping()
          51. mysql_query()
          52. mysql_real_connect()
          53. mysql_real_escape_string()
          54. mysql_real_query()
          55. mysql_reload()
          56. mysql_refresh()
          57. mysql_rollback()
          58. mysql_row_seek()
          59. mysql_row_tell()
          60. mysql_select_db()
          61. mysql_set_character_set()
          62. mysql_set_local_infile_default()
          63. mysql_set_local_infile_handler()
          64. mysql_set_server_option()
          65. mysql_shutdown()
          66. mysql_sqlstate()
          67. mysql_ssl_set()
          68. mysql_stat()
          69. mysql_store_result()
          70. mysql_thread_end()
          71. mysql_thread_id()
          72. mysql_thread_init()
          73. mysql_thread_safe()
          74. mysql_use_result()
          75. mysql_warning_count()
        3. C API Datatypes
      2. 18. Perl API
        1. Using Perl DBI with MySQL
          1. Connecting to MySQL
          2. Executing an SQL Statement
          3. Capturing Data
          4. Disconnecting from MySQL
          5. Temporarily Storing Results
        2. Perl DBI Reference
          1. available_drivers()
          2. begin_work()
          3. bind_col()
          4. bind_columns()
          5. bind_param()
          6. bind_param_array()
          7. bind_param_inout()
          8. can()
          9. clone()
          10. column_info()
          11. commit()
          12. connect()
          13. connect_cached()
          14. data_diff()
          15. data_sources()
          16. data_string_desc()
          17. data_string_diff()
          18. disconnect()
          19. do()
          20. dump_results()
          21. err()
          22. errstr()
          23. execute()
          24. execute_array()
          25. execute_for_fetch()
          26. fetch()
          27. fetchall_arrayref()
          28. fetchall_hashref()
          29. fetchrow_array()
          30. fetchrow_arrayref()
          31. fetchrow_hashref()
          32. finish()
          33. foreign_key_info()
          34. func()
          35. get_info()
          36. installed_drivers()
          37. installed_versions()
          38. last_insert_id()
          39. looks_like_number()
          40. neat()
          41. neat_list()
          42. parse_dsn()
          43. parse_trace_flag()
          44. parse_trace_flags()
          45. ping()
          46. prepare()
          47. prepare_cached()
          48. primary_key()
          49. primary_key_info()
          50. private_attribute_info()
          51. quote()
          52. quote_identifier()
          53. rollback()
          54. rows()
          55. selectall_arrayref()
          56. selectall_hashref()
          57. selectcol_arrayref()
          58. selectrow_array()
          59. selectrow_arrayref()
          60. selectrow_hashref()
          61. set_err()
          62. state()
          63. statistics_info()
          64. swap_inner_handle()
          65. table_info()
          66. tables()
          67. take_imp_data()
          68. trace()
          69. trace_msg()
          70. type_info()
          71. type_info_all()
        3. Attributes for Handles
          1. Attributes for All Handles
          2. Attributes Only for Database Handles
          3. Attributes Only for Statement Handles
          4. DBI Dynamic Attributes
      3. 19. PHP API
        1. Using PHP with MySQL
          1. Connecting to MySQL
          2. Querying MySQL
        2. PHP MySQL Functions in Alphabetical Order
          1. mysql_affected_rows()
          2. mysql_change_user()
          3. mysql_client_encoding()
          4. mysql_close()
          5. mysql_connect()
          6. mysql_create_db()
          7. mysql_data_seek()
          8. mysql_db_name()
          9. mysql_db_query()
          10. mysql_drop_db()
          11. mysql_errno()
          12. mysql_error()
          13. mysql_escape_string()
          14. mysql_fetch_array()
          15. mysql_fetch_assoc()
          16. mysql_fetch_field()
          17. mysql_fetch_lengths()
          18. mysql_fetch_object()
          19. mysql_fetch_row()
          20. mysql_field_flags()
          21. mysql_field_len()
          22. mysql_field_name()
          23. mysql_field_seek()
          24. mysql_field_table()
          25. mysql_field_type()
          26. mysql_free_result()
          27. mysql_get_client_info()
          28. mysql_get_host_info()
          29. mysql_get_proto_info()
          30. mysql_get_server_info()
          31. mysql_info()
          32. mysql_insert_id()
          33. mysql_list_dbs()
          34. mysql_list_fields()
          35. mysql_list_processes()
          36. mysql_list_tables()
          37. mysql_num_fields()
          38. mysql_num_rows()
          39. mysql_pconnect()
          40. mysql_ping()
          41. mysql_query()
          42. mysql_real_escape_string()
          43. mysql_result()
          44. mysql_select_db()
          45. mysql_set_charset()
          46. mysql_stat()
          47. mysql_tablename()
          48. mysql_thread_id()
          49. mysql_unbuffered_query()
    7. V. Appendixes
      1. A. Data Types
        1. Numeric Data Types
          1. BIT
          2. TINYINT
          3. BOOL, BOOLEAN
          4. SMALLINT
          5. MEDIUMINT
          6. INT, INTEGER
          7. BIGINT, SERIAL
          8. FLOAT
          9. DOUBLE, DOUBLE PRECISION
          10. DEC, DECIMAL, FIXED, NUMERIC
        2. Date and Time Data Types
        3. String Data Types
          1. CHAR
          2. VARCHAR
          3. BINARY
          4. VARBINARY
          5. TINYBLOB
          6. TINYTEXT
          7. BLOB
          8. TEXT
          9. MEDIUMBLOB
          10. MEDIUMTEXT
          11. LONGBLOB
          12. LONGTEXT
          13. ENUM
          14. SET
      2. B. Operators
        1. Arithmetic Operators
        2. Relational Operators
        3. Logical Operators
        4. Bitwise Operators
        5. Regular Expressions
      3. C. Server and Environment Variables
    8. Index
    9. About the Author
    10. Colophon
    11. SPECIAL OFFER: Upgrade this ebook with O’Reilly

Product information

  • Title: MySQL in a Nutshell, 2nd Edition
  • Author(s): Russell J. T. Dyer
  • Release date: April 2008
  • Publisher(s): O'Reilly Media, Inc.
  • ISBN: 9781449379377