O'Reilly logo

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

MySQL and mSQL

MySQL and mSQL

by Randy Yarger, George Reese, Tim King
Publisher: O'Reilly Media, Inc.
Release Date: July 1999
ISBN: 1565924347
View table of contents
Start reading

Book Description

MySQL and mSQL are popular and robust database products that support key subsets of SQL on both Linux and Unix systems. Both products are free for nonprofit use and cost a small amount for commercial use. Even a small organization or web site has uses for a database. Perhaps you keep track of all your customers and find that your information is outgrowing the crude, flat-file format you started with. Or you want to ask your web site's visitors for their interests and preferences and put up a fresh web page that tallies the results. Unlike commercial databases, MySQL and mSQL are affordable and easy to use. If you know basic C, Java, Perl, or Python, you can quickly write a program to interact with your database. In addition, you can embed queries and updates right in an HTML file so that a web page becomes its own interface to the database. This book is all you need to make use of MySQL or mSQL. It takes you through the whole process from installation and configuration to programming interfaces and basic administration. Includes reference chapters and ample tutorial material. Topics include:

  • Introductions to simple database design and SQL

  • Building, installation, and configuration

  • Basic programming APIs for C, C++, Java (JDBC), Perl, and Python

  • CGI programming with databases in C and Perl

  • Web interfaces: PHP, W3-mSQL, Lite, and mSQLPerl

Table of Contents

  1. MySQL & mSQL
    1. Preface
      1. Audience
      2. Purpose
      3. Using This Book
      4. On MySQL and mSQL
      5. Conventions Used in This Book
      6. Comments and Questions
      7. Acknowledgments
        1. From Randy Yarger
        2. From George Reese
        3. From Tim King
    2. I. Getting Started with MySQL and mSQL
      1. 1. Introduction to Relational Databases
        1. What Is a Database?
        2. What Is a Relational Database?
        3. Applications and Databases
          1. Databases and the Web
        4. MySQL and mSQL
          1. The History of mSQL
            1. David Hughes
            2. From PostQUEL translator to RDBMS
          2. The History of MySQL
            1. MySQL or mSQL?
      2. 2. Database Design
        1. Database Design
          1. Database Entities
          2. Entity Attributes
          3. Data Model
        2. Normalization
          1. First Normal Form (1NF)
          2. The Unique Identifier
          3. Relationships
          4. Second Normal Form (2NF)
          5. Kinds of Relationships
          6. Refining Relationships
          7. More 2NF
          8. Third Normal Form (3NF)
        3. A Logical Data Modeling Methodology
        4. Physical Database Design
      3. 3. Installation
        1. MySQL
          1. Installing from Source
          2. Installing a Binary Distribution
        2. mSQL
      4. 4. MySQL
        1. Design
        2. Installing MySQL
        3. Running MySQL
        4. Database Administration
          1. The mysqladmin Utility
            1. Database creation
            2. Database destruction
            3. Database renaming and copying
            4. Server status
            5. Server shutdown
            6. Command line options for mysqladmin
          2. Backups
          3. Security
            1. The user table
            2. The db table
            3. The host table
            4. The tables_priv and columns_priv tables
            5. The stages of access control
            6. The mysqlaccess utility
            7. Making changes
        5. MySQL Utilities
          1. Command Line Tools
          2. Third Party Tools
            1. Database conversion utilities
            2. CGI interfaces
            3. Client applications
            4. Programming interfaces
            5. Miscellaneous
        6. Performance Tuning
          1. The MySQL Data Structure
            1. Repairing damaged tables
            2. Removing and replacing keys
          2. Troubleshooting
      5. 5. mSQL
        1. Design
        2. mSQL Versions
        3. Installing mSQL
        4. Running mSQL
          1. Running Multiple Daemons
          2. The mSQL Configuration File
        5. Database Administration
          1. The msqladmin Utility
            1. Database creation
            2. Database destruction
            3. Database renaming and copying
            4. Server status
            5. Server shutdown
            6. Reloading server changes
            7. Command line options for msqladmin
          2. Backups
          3. Security
        6. mSQL Utilities
          1. Command Line Tools
          2. Third Party Support
            1. Database conversion utilities
            2. CGI interfaces
            3. Client applications
            4. Programming interfaces
            5. Miscellaneous
      6. 6. SQL According to MySQL and mSQL
        1. SQL Basics
          1. The SQL Story
          2. The Design of SQL
        2. Creating and Dropping Tables
        3. SQL Datatypes
          1. Numeric Types
          2. Character Types
            1. Variable-length character fields in MySQL
            2. Variable-length character fields in mSQL
          3. Binary Datatypes
          4. Enumerations and Sets
          5. Other Kinds of Data
        4. Indices
        5. Sequences and Auto-Incrementing
          1. MySQL Sequences
          2. mSQL Sequences
        6. Managing Data
          1. Inserts
          2. Updates
          3. The WHERE Clause
          4. Deletes
        7. Queries
          1. Joins
          2. Aliasing
          3. Grouping and Ordering
        8. Extended Functionality
          1. MySQL Features
          2. mSQL Features
      7. 7. Other Mid-Range Database Engines
        1. What Is “Free”?
        2. What MySQL and mSQL Lack
          1. Transactions
          2. Triggers
          3. Stored Procedures
          4. Subselects
          5. Objects
        3. PostgreSQL
        4. GNU SQL
        5. Beagle
        6. Making Comparisons
    3. II. Database Programming
      1. 8. Database Application Architectures
        1. The Client/Server Architecture
        2. Data Processing
        3. Object/Relational Modeling
        4. The Three-tier Architecture
      2. 9. CGI Programming
        1. What Is CGI?
        2. HTML Forms
        3. The CGI Specification
          1. Environment Variables
          2. Command Line
          3. Standard Input
          4. Accepting Input in Perl
          5. Accepting Input in C
          6. Standard Output
        4. Important Considerations for CGI Scripts
          1. State Retention
          2. Security
          3. Further Reading
        5. CGI and Databases
      3. 10. Perl
        1. DBI
        2. An Example DBI Application
        3. Msql.pm
        4. MysqlPerl
      4. 11. Python
        1. Basic Connectivity
          1. Queries
          2. Updates
        2. Dynamic Connectivity
          1. MySQL Statement Handlers
          2. Database Meta-data
      5. 12. PHP and Other Support for Database-driven HTML
        1. Alternatives for Dynamic Content on the Web
        2. W3-mSQL
          1. W3-Auth
          2. Installation
          3. W3-mSQL Example
        3. PHP
        4. Embedded Perl
          1. ePerl
          2. EmbPerl
      6. 13. C and C++
        1. The Two APIs
        2. Object-oriented Database Access in C++
          1. The Database Connection
            1. Connecting to the database
            2. Disconnecting from the database
            3. Making Calls to the database
            4. Other Connection behaviors
          2. Error Handling Issues
          3. Result Sets
            1. Navigating results
            2. Cleaning up and row count
          4. Rows
      7. 14. Java and JDBC
        1. What Is JDBC?
          1. The Database Connection
          2. Maintaining Portability Using Properties Files
        2. Simple Database Access
          1. Error Handling and Clean Up
        3. Dynamic Database Access
          1. Meta Data
          2. Processing Dynamic SQL
        4. A Guest Book Servlet
    4. III. Reference
      1. 15. SQL Reference
        1. MySQL SQL
          1. ALTER/MODIFY
          2. CREATE
          3. DELETE
          4. DESCRIBE
          5. DESC
          6. DROP
          7. EXPLAIN
          8. FLUSH
          9. GRANT
          10. INSERT
          11. KILL
          12. LOAD
          13. LOCK
          14. OPTIMIZE
          15. REPLACE
          16. REVOKE
          17. SELECT
          18. SET
          19. SHOW
          20. UNLOCK
          21. UPDATE
          22. USE
        2. mSQL SQL
          1. CREATE
          2. DELETE
          3. DROP
          4. INSERT
          5. SELECT
          6. UPDATE
      2. 16. MySQL and mSQL System Variables
        1. MySQL System Variables
          1. Environment Variables
          2. Command line variables
          3. The MySQL Configuration File
        2. mSQL System Variables
          1. Environment Variables
          2. The mSQL Configuration File
            1. general
            2. system
            3. w3-msql
      3. 17. MySQL and mSQL Programs and Utilities
        1. MySQL Utilities
          1. isamchk
          2. isamlog
          3. mysql
          4. mysqlaccess
          5. mysqladmin
          6. mysqlbug
          7. mysqld
          8. mysqldump
          9. mysqlimport
          10. mysqlshow
        2. mSQL Utilities
          1. msql
          2. msql2d
          3. msqladmin
          4. msqldump
          5. msqlexport
          6. msqlimport
          7. relshow
      4. 18. PHP and Lite Reference
        1. PHP
          1. abs
          2. addslashes
          3. asort
          4. bindec
          5. ceil
          6. chdir
          7. chgrp
          8. chimed
          9. chown
          10. chop
          11. chr
          12. clearstack
          13. clearstatcache
          14. closedir
          15. closelog
          16. cos
          17. count
          18. crypt
          19. date
          20. dblist
          21. decbin
          22. dexhex
          23. decoct
          24. doubleval
          25. echo
          26. end
          27. ereg
          28. eregi
          29. ereg_replace
          30. eregi_replace
          31. escapeshellcmd
          32. eval
          33. exec
          34. exit
          35. exp
          36. fclose
          37. feof
          38. fgets
          39. fgetss
          40. file
          41. fileatime
          42. filectime
          43. filegroup
          44. fileinode
          45. filemtime
          46. fileowner
          47. fileperms
          48. filesize
          49. filetype
          50. floor
          51. flush
          52. fopen
          53. fputs
          54. fpassthru
          55. fseek
          56. fsockopen
          57. ftell
          58. getaccdir
          59. getenv
          60. gethostbyname
          61. gethostbyaddr
          62. getimagesize
          63. getlastaccess
          64. getlastbrowser
          65. getlastemail
          66. getlasthost
          67. getlastmod
          68. getlastref
          69. getlogdir
          70. getmyinode
          71. getmypid
          72. getmyuid
          73. getrandmax
          74. getstartlogging
          75. gettoday
          76. gettotal
          77. gettype
          78. gmdate
          79. header
          80. hexdec
          81. htmlspecialchars
          82. imagearc
          83. imagechar
          84. imagecharup
          85. imagecolorallocate
          86. imagecolortransparent
          87. imagecopyresized
          88. imagecreate
          89. imagecreatefromgif
          90. imagedestroy
          91. imagefill
          92. imagefilledpolygon
          93. imagefilledrectangle
          94. imagefilltoborder
          95. imagegif
          96. imageinterlace
          97. imageline
          98. imagepolygon
          99. imagerectangle
          100. imagesetpixel
          101. imagestring
          102. imagestringup
          103. imagesx
          104. imagesy
          105. include
          106. initsyslog
          107. intval
          108. isset
          109. key
          110. link
          111. linkinfo
          112. log
          113. log10
          114. logas
          115. mail
          116. max
          117. md5
          118. microtime
          119. min
          120. mkdir
          121. mktime
          122. msql
          123. mysql
          124. mysql_affected_rows
          125. msql_close
          126. mysql_close
          127. msql_connect
          128. mysql_connect
          129. msql_createdb
          130. mysql_createdb
          131. msql_dbname
          132. mysql_dbname
          133. msql_dropdb
          134. mysql_dropdb
          135. msql_fieldflags
          136. mysql_fieldflags
          137. msql_fieldlen
          138. mysql_fieldlen
          139. msql_fieldname
          140. mysql_fieldname
          141. msql_fieldtype
          142. mysql_fieldtype
          143. msql_freeresult
          144. mysql_freeresult
          145. mysql_insert_id
          146. msql_listdbs
          147. mysql_listdbs
          148. msql_listfields
          149. mysql_listfields
          150. msql_listtables
          151. mysql_listtables
          152. msql_numfields
          153. mysql_numfields
          154. msql_numrows
          155. msql_numrows
          156. msql_regcase
          157. msql_result
          158. mysql_result
          159. msql_tablename
          160. mysql_tablename
          161. next
          162. octdec
          163. opendir
          164. openlog
          165. ord
          166. parse_str
          167. passthru
          168. pclose
          169. phpinfo
          170. phpversion
          171. popen
          172. pos
          173. pow
          174. prev
          175. putenv
          176. quotemeta
          177. rand
          178. readdir
          179. readfile
          180. readlink
          181. reg_match
          182. reg_replace
          183. reg_search
          184. rename
          185. reset
          186. return
          187. rewind
          188. rewinddir
          189. rmdir
          190. rsort
          191. setcookie
          192. seterrorreporting
          193. setlogging
          194. setshowinfo
          195. settype
          196. shl
          197. shr
          198. sin
          199. sleep
          200. sort
          201. soundex
          202. sprintf
          203. sqrt
          204. srand
          205. strchr
          206. strstr
          207. strtr
          208. stripslashes
          209. strlen
          210. strrchr
          211. strtok
          212. strtolower
          213. strtoupper
          214. strval
          215. substr
          216. symlink
          217. syslog
          218. system
          219. tan
          220. tempnam
          221. time
          222. umask
          223. uniqid
          224. unlink
          225. unset
          226. urldecode
          227. urlencode
          228. usleep
          229. virtual
        2. Lite
          1. chdir
          2. chmod
          3. chop
          4. close
          5. ctime
          6. echo
          7. fprintf
          8. gethostbyaddr
          9. gethostbyname
          10. getpid
          11. getpwnam
          12. getpwuid
          13. includeFile
          14. kill
          15. link
          16. mkdir
          17. msqlConnect
          18. msqlClose
          19. msqlDataSeek
          20. msqlEncode
          21. msqlFetchRow
          22. msqlFieldSeek
          23. msqlFreeResult
          24. msqlInitFieldList
          25. msqlListDBs
          26. msqlListField
          27. msqlListTables
          28. msqlNumRows
          29. msqlQuery
          30. msqlSelectDB
          31. msqlStoreResult
          32. open
          33. pid
          34. printf
          35. read
          36. readln
          37. readtok
          38. rename
          39. rmdir
          40. setContentType
          41. sleep
          42. split
          43. stat
          44. strftime
          45. strseg
          46. sub
          47. substr
          48. symlink
          49. system
          50. test
          51. time
          52. time2unixtime
          53. tr
          54. truncate
          55. umask
          56. unixtime2*
          57. unlink
          58. urlEncode
      5. 19. C Reference
        1. MySQL C API
          1. Datatypes
        2. mysql_affected_rows
          1. mysql_affected_rows
        3. mysql_close
          1. mysql_close
        4. mysql_connect
          1. mysql_connect
        5. mysql_create_db
          1. mysql_create_db
        6. mysql_data_seek
          1. mysql_data_seek
        7. mysql_debug
          1. mysql_debug
        8. mysql_drop_db
          1. mysql_drop_db
        9. mysql_dump_debug_info
          1. mysql_dump_debug_info
        10. mysql_eof
          1. mysql_eof
        11. mysql_errno
          1. mysql_errno
        12. mysql_error
          1. mysql_error
        13. mysql_escape_string
          1. mysql_escape_string
        14. mysql_fetch_field
          1. mysql_fetch_field
        15. mysql_fetch_field_direct
          1. mysql_fetch_field_direct
        16. mysql_fetch_fields
          1. mysql_fetch_fields
        17. mysql_fetch_lengths
          1. mysql_fetch_lengths
        18. mysql_fetch_row
          1. mysql_fetch_row
        19. mysql_field_seek
          1. mysql_field_seek
        20. mysql_field_tell
          1. mysql_field_tell
        21. mysql_free_result
          1. mysql_free_result
        22. mysql_get_client_info
          1. mysql_get_client_info
        23. mysql_get_host_info
          1. mysql_get_host_info
        24. mysql_get_proto_info
          1. mysql_get_proto_info
        25. mysql_get_server_info
          1. mysql_get_server_info
        26. mysql_info
          1. mysql_info
        27. mysql_init
          1. mysql_init
        28. mysql_insert_id
          1. mysql_insert_id
        29. mysql_kill
          1. mysql_kill
        30. mysql_list_dbs
          1. mysql_list_dbs
        31. mysql_list_fields
          1. mysql_list_fields
        32. mysql_list_processes
          1. mysql_list_processes
        33. mysql_list_tables
          1. mysql_list_tables
        34. mysql_num_fields
          1. mysql_num_fields
        35. mysql_num_rows
          1. mysql_num_rows
        36. mysql_ping
          1. mysql_ping
        37. mysql_query
          1. mysql_query
        38. mysql_real_connect
          1. mysql_real_connect
        39. mysql_real_query
          1. mysql_real_query
        40. mysql_reload
          1. mysql_reload
        41. mysql_row_tell
          1. mysql_row_tell
        42. mysql_select_db
          1. mysql_select_db
        43. mysql_shutdown
          1. mysql_shutdown
        44. mysql_stat
          1. mysql_stat
        45. mysql_store_result
          1. mysql_store_result
        46. mysql_thread_id
          1. mysql_thread_id
        47. mysql_use_result
          1. mysql_use_result
        48. mSQL C API
          1. Datatypes
        49. msqlConnect
          1. msqlConnect
        50. msqlSelectDB
          1. msqlSelectDB
        51. msqlQuery
          1. msqlQuery
        52. msqlStoreResult
          1. msqlStoreResult
        53. msqlFreeResult
          1. msqlFreeResult
        54. msqlFetchRow
          1. msqlFetchRow
        55. msqlDataSeek
          1. msqlDataSeek
        56. msqlNumRows
          1. msqlNumRows
        57. msqlFetchField
          1. msqlFetchField
        58. msqlFieldSeek
          1. msqlFieldSeek
        59. msqlNumFields
          1. msqlNumFields
        60. msqlClose
          1. msqlClose
        61. msqlListDBs
          1. msqlListDBs
        62. msqlListTables
          1. msqlListTables
        63. msqlListFields
          1. msqlListFields
        64. msqlListIndex
          1. msqlListIndex
      6. 20. Python Reference
        1. Module: MySQL
          1. Method: MySQL.connect( )
          2. Method: connection.selectdb( )
          3. Method: connection.do( )
          4. Method: connection.query( )
          5. Method: statement_handle.affectedrows( )
          6. Method: statement_handle.numrows( )
          7. Method: statement_handle.fields( )
          8. Method: statement_handle.fetchrows( )
          9. Method: connection.listdbs( )
          10. Method: connection.listtables( )
        2. Module: mSQL
          1. Method: mSQL.connect( )
          2. Method: connection.selectdb( )
          3. Method: connection.query( )
          4. Method: connection.listdbs( )
          5. Method: connection.listtables( )
          6. Attribute: connection.serverinfo
          7. Attribute: connection.hostname
      7. 21. Perl Reference
        1. Installation
        2. DBI.pm API
          1. use
          2. DBI::available_drivers
          3. DBI::bind_col
          4. DBI::bind_columns
          5. DBI::bind_param
          6. DBI::connect
          7. DBI::data_sources
          8. DBI::do
          9. DBI::disconnect
          10. DBI::dump_results
          11. $DBI::err
          12. $DBI::errstr
          13. DBI::execute
          14. DBI::fetchall_arrayref
          15. DBI::fetchrow_array
          16. DBI::fetchrow_arrayref, DBI::fetch
          17. DBI::fetchrow_hashref
          18. DBI::finish
          19. DBI::func
          20. DBI::neat
          21. DBI::neat_list
          22. DBI::prepare
          23. DBI::quote
          24. DBI::rows
          25. DBI::state
          26. DBI::trace
          27. DBI::commit, DBI::rollback, DBI::ping
          28. Attributes
        3. Msql.pm API
          1. use Msql
          2. Msql::connect
          3. Msql::createdb
          4. Msql::database
          5. Msql::dropdb
          6. Msql::errmsg
          7. Msql::getsequenceinfo
          8. Msql::host
          9. Msql::listdbs
          10. Msql::listfields
          11. Msql::listindex
          12. Msql::listtables
          13. Msql::query
          14. $Msql::QUIET
          15. Msql::quote
          16. Msql::selectdb
          17. Msql::shutdown
          18. Msql::Statement::as_string
          19. Msql::Statement::dataseek
          20. Msql::Statement::fetchcol
          21. Msql::Statement::fetchhash
          22. Msql::Statement::fetchrow
          23. Msql::Statement::isnotnull
          24. Msql::Statement::isnum
          25. Msql::Statement::isprikey
          26. Msql::Statement::length
          27. Msql::Statement::listindices
          28. Msql::Statement::maxlength
          29. Msql::Statement::name
          30. Msql::Statement::numfields
          31. Msql::Statement::numrows
          32. Msql::Statement::table
          33. Msql::Statement::type
          34. Msql::sock
          35. Msql::*_TYPE
          36. $Msql::VERSION
        4. Mysql.pm API
          1. Mysql::connect
          2. Mysql::errno
          3. Mysql::FIELD_TYPE_*
          4. Mysql::Statement::affectedrows
          5. Mysql::Statement::info
          6. Mysql::Statement::insertid
          7. Mysql::Statement::isblob
          8. Mysql::query
      8. 22. JDBC Reference
        1. Array
        2. Blob
        3. CallableStatement
        4. Clob
        5. Connection
        6. DatabaseMetaData
        7. Date
        8. Driver
        9. DriverManager
        10. DriverPropertyInfo
        11. PreparedStatement
        12. Ref
        13. ResultSet
        14. ResultSetMetaData
        15. Statement
        16. Struct
        17. Time
        18. Timestamp
        19. Types
    5. Index
    6. Colophon