MySQL in a Nutshell

MySQL in a Nutshell

by Russell J. T. Dyer
Released May 2005
Publisher(s): O'Reilly Media, Inc.
ISBN: 9781449379063

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

MySQL is the world's most popular open source database. MySQL is designed for speed, power, and flexibility in mission-critical, heavy-use environments and modest applications as well. It's also surprisingly rich in features. If you're a database administrator or programmer you probably love the myriad of things MySQL can do, but sometimes wish there wasn't such a myriad of things to remember. With MySQL in a Nutshell by your keyboard, you can drill down into the full depth of MySQL's capabilities quickly and easily.MySQL in a Nutshell is the indispensable desktop reference to all MySQL functions. Programming language APIs for PHP, Perl, and C are covered, as well as all the popular MySQL utilities.This invaluable resource clearly documents the details that experienced users need to take full advantage of this powerful database management system. Better yet, this wealth of information is packed into the concise, comprehensive, and extraordinarily easy-to-use format for which the in a Nutshell guides are renowned.In addition to providing a thorough reference to MySQL statements and functions, the administrative utilities, and the most popular APIs, MySQL in a Nutshell includes several tutorial chapters to help newcomers get started. Moreover, each chapter covering an API begins with a brief tutorial so that, regardless of your level of experience in any given area, you will be able to understand and master unfamiliar territory.MySQL in a Nutshell distills all the vital MySQL details you need on a daily basis into one convenient, well-organized book. It will save you hundreds of hours of tedious research or trial and error and put the facts you need to truly tap MySQL's capabilities at your fingertips.

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
      3. Conventions Used in This Book
      4. Using Code Examples
      5. Request for Comments
      6. Safari Enabled
      7. Acknowledgments
    3. 1. Introduction to MySQL
      1. 1.1. The Value of MySQL
      2. 1.2. The MySQL Package
      3. 1.3. Licensing
      4. 1.4. Mailing Lists
      5. 1.5. Books and Other Publications
    4. 2. Installing MySQL
      1. 2.1. Choosing a Distribution
      2. 2.2. Unix Source Distributions
      3. 2.3. Unix Binary Distributions
      4. 2.4. Linux RPM Distributions
      5. 2.5. Macintosh OS X Distributions
      6. 2.6. Novell NetWare Distributions
      7. 2.7. Windows Distributions
      8. 2.8. Postinstallation
    5. 3. MySQL Basics
      1. 3.1. The mysql Client
      2. 3.2. Creating a Database and Tables
      3. 3.3. Show Me
      4. 3.4. Inserting Data
      5. 3.5. Selecting Data
      6. 3.6. Ordering, Limiting, and Grouping
      7. 3.7. Analyzing and Manipulating Data
      8. 3.8. Changing Data
      9. 3.9. Deleting Data
      10. 3.10. Searching Data
      11. 3.11. Importing Data in Bulk
      12. 3.12. Command-Line Interface
      13. 3.13. Conclusion
    6. 4. SQL Statements
      1. 4.1. Statements Grouped by Type
        1. 4.1.1. Data Manipulation
        2. 4.1.2. Database and Table Schema
        3. 4.1.3. Database and Table Properties
        4. 4.1.4. Table Administration
        5. 4.1.5. User Administration
        6. 4.1.6. Server Administration
        7. 4.1.7. Replication
      2. 4.2. Statements and Clauses in Alphabetical Order
        1. ALTER DATABASE
        2. ALTER TABLE
        3. ALTER VIEW
        4. ANALYZE TABLE
        5. BACKUP TABLE
        6. CACHE INDEX
        7. CHANGE MASTER TO
        8. CHECK TABLE
        9. CHECKSUM TABLE
        10. COMMIT
        11. CREATE DATABASE
        12. CREATE INDEX
        13. CREATE TABLE
        14. CREATE VIEW
        15. DELETE
        16. DESCRIBE
        17. DO
        18. DROP DATABASE
        19. DROP INDEX
        20. DROP TABLE
        21. DROP USER
        22. DROP VIEW
        23. EXPLAIN
        24. FLUSH
        25. GRANT
        26. HANDLER
        27. INSERT
        28. JOIN
        29. KILL
        30. LOAD DATA FROM MASTER
        31. LOAD DATA INFILE
        32. LOAD INDEX INTO CACHE
        33. LOAD TABLE...FROM MASTER
        34. LOCK TABLES
        35. OPTIMIZE TABLE
        36. PURGE MASTER LOGS
        37. RENAME TABLE
        38. REPAIR TABLE
        39. REPLACE
        40. RESET
        41. RESET MASTER
        42. RESET SLAVE
        43. RESTORE TABLE
        44. REVOKE
        45. ROLLBACK
        46. ROLLBACK TO SAVEPOINT
        47. SAVEPOINT
        48. SELECT
        49. SET
        50. SET PASSWORD
        51. SET SQL_LOG_BIN
        52. SET TRANSACTION
        53. SHOW BINLOG EVENTS
        54. SHOW CHARACTER SET
        55. SHOW COLLATION
        56. SHOW COLUMNS
        57. SHOW CREATE DATABASE
        58. SHOW CREATE TABLE
        59. SHOW CREATE VIEW
        60. SHOW DATABASES
        61. SHOW ENGINES
        62. SHOW ERRORS
        63. SHOW GRANTS
        64. SHOW INDEX
        65. SHOW INNODB STATUS
        66. SHOW LOGS
        67. SHOW MASTER LOGS
        68. SHOW MASTER STATUS
        69. SHOW PRIVILEGES
        70. SHOW PROCESSLIST
        71. SHOW SLAVE HOSTS
        72. SHOW SLAVE STATUS
        73. SHOW STATUS
        74. SHOW TABLE STATUS
        75. SHOW TABLES
        76. SHOW VARIABLES
        77. SHOW WARNINGS
        78. START SLAVE
        79. START TRANSACTION
        80. STOP SLAVE
        81. TRUNCATE TABLE
        82. UNION
        83. UNLOCK TABLES
        84. USE
    7. 5. String Functions
      1. 5.1. String Functions Grouped by Type
        1. 5.1.1. Converting
        2. 5.1.2. Formatting
        3. 5.1.3. Expressions
        4. 5.1.4. Extracting
        5. 5.1.5. Manipulating
        6. 5.1.6. Security
      2. 5.2. String Functions in Alphabetical Order
        1. AES_DECRYPT( )
        2. AES_ENCRYPT( )
        3. ASCII( )
        4. BIN( )
        5. BINARY
        6. BIT_LENGTH( )
        7. CHAR( )
        8. CHAR_LENGTH( )
        9. CHARACTER_LENGTH( )
        10. COMPRESS( )
        11. CONCAT( )
        12. CONCAT_WS( )
        13. CONV( )
        14. DECODE( )
        15. DES_DECRYPT( )
        16. DES_ENCRYPT( )
        17. ELT( )
        18. ENCODE( )
        19. ENCRYPT( )
        20. EXPORT_SET( )
        21. FIELD( )
        22. FIND_IN_SET( )
        23. HEX( )
        24. INET_ATON( )
        25. INET_NTOA( )
        26. INSERT( )
        27. INSTR( )
        28. LCASE( )
        29. LEFT( )
        30. LENGTH( )
        31. LOAD_FILE( )
        32. LOCATE( )
        33. LOWER( )
        34. LPAD( )
        35. LTRIM( )
        36. MAKE_SET( )
        37. MATCH ( ) AGAINST( )
        38. MD5( )
        39. MID( )
        40. OCT( )
        41. OCTET_LENGTH( )
        42. OLD_PASSWORD( )
        43. ORD( )
        44. PASSWORD( )
        45. POSITION( )
        46. QUOTE( )
        47. REPEAT( )
        48. REPLACE( )
        49. REVERSE( )
        50. RIGHT( )
        51. RPAD( )
        52. RTRIM( )
        53. SHA( )
        54. SHA1( )
        55. SOUNDEX( )
        56. SPACE( )
        57. STRCMP( )
        58. SUBSTRING( )
        59. SUBSTRING_INDEX( )
        60. TRIM( )
        61. UCASE( )
        62. UNCOMPRESS( )
        63. UNCOMPRESSED_LENGTH( )
        64. UNHEX( )
        65. UPPER( )
    8. 6. Date and Time Functions
      1. 6.1. Date and Time Functions Grouped by Type
        1. Determining the Date and Time
        2. Extracting and Formatting the Date and Time
        3. Calculating and Modifying the Date and Time
      2. 6.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. STR_TO_DATE( )
        40. SUBDATE( )
        41. SUBTIME( )
        42. SYSDATE( )
        43. TIME( )
        44. TIMEDIFF( )
        45. TIMESTAMP( )
        46. TIMESTAMPDIFF( )
        47. TIMESTAMPADD( )
        48. TIME_FORMAT( )
        49. TIME_TO_SEC( )
        50. TO_DAYS( )
        51. UNIX_TIMESTAMP( )
        52. UTC_DATE( )
        53. UTC_TIME( )
        54. UTC_TIMESTAMP( )
        55. WEEK( )
        56. WEEKDAY( )
        57. WEEKOFYEAR( )
        58. YEAR( )
        59. YEARWEEK( )
    9. 7. Mathematical and Aggregate Functions
      1. 7.1. Functions in Alphabetical Order
        1. ABS( )
        2. ACOS( )
        3. ASIN( )
        4. ATAN( )
        5. ATAN2( )
        6. AVG( )
        7. BIT_AND( )
        8. BIT_OR( )
        9. BIT_XOR( )
        10. CEIL( )
        11. CEILING( )
        12. COS( )
        13. COT( )
        14. COUNT( )
        15. CRC32( )
        16. DEGREES( )
        17. EXP( )
        18. FLOOR( )
        19. FORMAT( )
        20. GREATEST( )
        21. GROUP_CONCAT( )
        22. LEAST( )
        23. LN( )
        24. LOG( )
        25. LOG2( )
        26. LOG10( )
        27. MAX( )
        28. MIN( )
        29. MOD( )
        30. PI( )
        31. POW( )
        32. POWER( )
        33. RADIANS( )
        34. RAND( )
        35. ROUND( )
        36. SIGN( )
        37. SIN( )
        38. SQRT( )
        39. STD( )
        40. STDDEV( )
        41. SUM( )
        42. TAN( )
        43. TRUNCATE( )
        44. VARIANCE( )
    10. 8. Flow Control Functions
      1. 8.1. Functions in Alphabetical Order
        1. CASE( )
        2. IF( )
        3. IFNULL( )
        4. NULLIF( )
    11. 9. Miscellaneous Functions
      1. 9.1. Functions in Alphabetical Order
        1. ANALYSE( )
        2. BENCHMARK( )
        3. BIT_COUNT( )
        4. CAST( )
        5. CHARSET( )
        6. COALESCE( )
        7. COERCIBILITY( )
        8. COLLATION( )
        9. CONNECTION_ID( )
        10. CONVERT( )
        11. CURRENT_USER( )
        12. DATABASE( )
        13. FOUND_ROWS( )
        14. GET_LOCK( )
        15. INTERVAL( )
        16. IS_FREE_LOCK( )
        17. IS_USED_LOCK( )
        18. ISNULL( )
        19. LAST_INSERT_ID( )
        20. MASTER_POS_WAIT( )
        21. RELEASE_LOCK( )
        22. SESSION_USER( )
        23. SYSTEM_USER( )
        24. USER( )
        25. UUID ( )
        26. VERSION( )
    12. 10. MySQL Server and Client
      1. mysql
      2. mysqld
      3. mysqld_multi
      4. mysqld_safe
    13. 11. Command-Line Utilities
      1. comp_err
      2. isamchk
      3. make_binary_distribution
      4. msql2mysql
      5. my_print_defaults
      6. myisamchk
      7. myisamlog
      8. myisampack
      9. mysqlaccess
      10. mysqladmin
      11. mysqlbinlog
      12. mysqlbug
      13. mysqlcheck
      14. mysqldump
      15. mysqldumpslow
      16. mysqlhotcopy
      17. mysqlimport
      18. mysqlshow
      19. perror
    14. 12. Perl API
      1. 12.1. Using Perl DBI with MySQL
        1. 12.1.1. Connecting to MySQL
        2. 12.1.2. Executing an SQL Statement
        3. 12.1.3. Capturing Data
        4. 12.1.4. Disconnecting from MySQL
        5. 12.1.5. Temporarily Storing Results
      2. 12.2. Perl DBI Method and Function 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_sources( )
        15. disconnect( )
        16. do( )
        17. dump_results( )
        18. err( )
        19. errstr( )
        20. execute( )
        21. execute_array( )
        22. execute_for_fetch( )
        23. fetch( )
        24. fetchall_arrayref( )
        25. fetchall_hashref( )
        26. fetchrow_array( )
        27. fetchrow_arrayref( )
        28. fetchrow_hashref( )
        29. finish( )
        30. foreign_key_info( )
        31. func( )
        32. get_info( )
        33. installed_versions( )
        34. last_insert_id( )
        35. looks_like_number( )
        36. neat( )
        37. neat_list( )
        38. parse_dsn( )
        39. parse_trace_flag( )
        40. parse_trace_flags( )
        41. ping( )
        42. prepare( )
        43. prepare_cached( )
        44. primary_key( )
        45. primary_key_info( )
        46. quote( )
        47. quote_identifier( )
        48. rollback( )
        49. rows( )
        50. selectall_arrayref( )
        51. selectall_hashref( )
        52. selectcol_arrayref( )
        53. selectrow_array( )
        54. selectrow_arrayref( )
        55. selectrow_hashref( )
        56. set_err( )
        57. state( )
        58. table_info( )
        59. table_info_all( )
        60. tables( )
        61. trace( )
        62. trace_msg( )
        63. type_info( )
        64. type_info_all( )
      3. 12.3. Attributes for Handles
        1. 12.3.1. Attributes for All Handles
        2. 12.3.2. Attributes Only for Database Handles
        3. 12.3.3. Attributes Only for Statement Handles
        4. 12.3.4. DBI Dynamic Attributes
    15. 13. PHP API
      1. 13.1. Using PHP with MySQL
        1. 13.1.1. Connecting to MySQL
        2. 13.1.2. Querying MySQL
      2. 13.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_stat( )
        46. mysql_tablename( )
        47. mysql_thread_id( )
        48. mysql_unbuffered_query( )
    16. 14. C API
      1. 14.1. Using C with MySQL
        1. 14.1.1. Connecting to MySQL
        2. 14.1.2. Querying MySQL
      2. 14.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_client_version( )
        28. mysql_get_host_info( )
        29. mysql_get_proto_info( )
        30. mysql_get_server_info( )
        31. mysql_get_server_version( )
        32. mysql_info( )
        33. mysql_init( )
        34. mysql_insert_id( )
        35. mysql_kill( )
        36. mysql_list_dbs( )
        37. mysql_list_fields( )
        38. mysql_list_processes( )
        39. mysql_list_tables( )
        40. mysql_more_results( )
        41. mysql_next_result( )
        42. mysql_num_fields( )
        43. mysql_num_rows( )
        44. mysql_options( )
        45. mysql_ping( )
        46. mysql_query( )
        47. mysql_real_connect( )
        48. mysql_real_escape_string( )
        49. mysql_real_query( )
        50. mysql_reload( )
        51. mysql_rollback( )
        52. mysql_row_seek( )
        53. mysql_row_tell( )
        54. mysql_select_db( )
        55. mysql_set_server_option( )
        56. mysql_shutdown( )
        57. mysql_sqlstate( )
        58. mysql_stat( )
        59. mysql_store_result( )
        60. mysql_thread_id( )
        61. mysql_thread_safe( )
        62. mysql_use_result( )
        63. mysql_warning_count( )
      3. 14.3. C API Datatypes
    17. A. Datatypes
      1. A.1. Numeric Datatypes
      2. A.2. Accurate Numeric Types
      3. A.3. Approximate Numeric Datatypes
      4. A.4. Date and Time Datatypes
      5. A.5. String Datatypes
    18. B. Operators
      1. B.1. Arithmetic Operators
      2. B.2. Relational Operators
      3. B.3. Logical Operators
      4. B.4. Bitwise Operators
    19. C. Environment Variables
    20. Index
    21. About the Author
    22. Colophon
    23. SPECIAL OFFER: Upgrade this ebook with O’Reilly

Product information

  • Title: MySQL in a Nutshell
  • Author(s): Russell J. T. Dyer
  • Release date: May 2005
  • Publisher(s): O'Reilly Media, Inc.
  • ISBN: 9781449379063