Sams Teach Yourself MySQL in 21 Days, Second Edition

Sams Teach Yourself MySQL in 21 Days, Second Edition

by Tony Butcher
Released December 2002
Publisher(s): Sams
ISBN: 0672323923

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 most widely installed open-source database. Approximately 3.5 million MySQL servers are in use and more than 45% of databases used on Linux worldwide are MySQL databases.

Its latest version, MySQL 4.1 closes the gap between MySQL and other, more sophisticated databases. Version 4.1 permits the use of MySQL as an embedded database, which means it can be compiled into another application and used in a range of devices.

The second edition of Sams Teach Yourself MySQL in 21 Days has been completely rewritten by a new author. The book enables a reader to create powerful databases using MySQL. He willnot only learn how to integrate MySQL into a database-driven Web site, but also how to embed MySQL into other compiled applications.

Table of contents

  1. Copyright
  2. About the Author
  3. Acknowledgments
  4. We Want to Hear from You!
  5. Introduction
  6. At a Glance
    1. Introduction to MySQL
      1. What Is MySQL?
      2. The Commercial View of MySQL
      3. Summary
      4. Q&A
    2. Installing MySQL
      1. Installation Overview
      2. Installing on Linux
      3. Installing on OS X
      4. Installing on Windows
      5. Summary
      6. Q&A
      7. Workshop
    3. Getting Started with Database Design
      1. The Need for Business Analysis
      2. Analyzing Business Needs
      3. Relationship Modeling
      4. Normalization
      5. Summary
      6. Q&A
      7. Workshop
    4. Getting Hands-On with MySQL
      1. The Essential Programs and Files of MySQL
      2. MySQL Users
      3. Installing ODBC
      4. Summary
      5. Q&A
      6. Workshop
    5. MySQL Data Types
      1. Overview of Data and Column Types
      2. Numeric Column Types
      3. String Column Types
      4. Date and Time
      5. Summary
      6. Q&A
      7. Workshop
    6. Manipulating a Database
      1. Using the mysql Client Program
      2. Creating, Dropping, and Selecting a Database
      3. Manipulating Tables
      4. Summary
      5. Q&A
      6. Workshop
    7. Inserting and Updating Data
      1. Inserting Data Using INSERT
      2. Inserting and Replacing Data Using REPLACE
      3. Performing Updates
      4. Importing Data
      5. Summary
      6. Q&A
      7. Workshop
  7. At a Glance
    1. Querying Data
      1. The Basics of SELECT Queries
      2. More Advanced Uses of SELECT
      3. Deleting Rows with DELETE
      4. Summary
      5. Q&A
      6. Workshop
    2. Joins and Indexes
      1. Joining Tables
      2. Indexes
      3. Managing Indexes in MySQL
      4. Summary
      5. Q&A
      6. Workshop
    3. Operators and Functions in MySQL
      1. How to Use This Lesson
      2. Operators
      3. Logical and Conditional Functions
      4. String Functions
      5. Date and Time Functions
      6. Encryption, Encoding, and Checksum Functions
      7. Summary
      8. Q&A
      9. Workshop
    4. Using PHP
      1. What Is PHP?
      2. A Quick Guide to PHP
      3. The PHP API for MySQL
      4. Summary
      5. Q&A
      6. Workshop
    5. Using the Perl DBI
      1. What Is Perl?
      2. The Perl Interface to MySQL
      3. Installing DBI and DBD on Linux
      4. DBI Methods
      5. Connecting to MySQL with the DBI
      6. Basics of Database Queries
      7. Handling the Resultset
      8. Queries That Return No Resultset
      9. Handling Query Errors
      10. When to Use Perl
      11. Summary
      12. Q&A
      13. Workshop
    6. Using the C API
      1. C and MySQL
      2. When to Use C, and When Not To
      3. Datatypes
      4. Compiling and Linking
      5. Connecting to Your Database
      6. Disconnecting from a Database
      7. Handling Errors
      8. A Simple Connection Script
      9. Running Queries That Return a Resultset
      10. A Simple Query Program
      11. Handling Resultsets
      12. Queries That Return No Resultset
      13. Summary
      14. Q&A
      15. Workshop
    7. Security
      1. The MySQL User Privilege System
      2. Managing User Privileges with GRANT and REVOKE
      3. Keeping a MySQL Installation Secure
      4. Making Secure Connections to MySQL
      5. Summary
      6. Q&A
      7. Workshop
  8. At a Glance
    1. Administration
      1. Managing a Database with mysqladmin
      2. Setting Variables for MySQL
      3. Other Administration Tools
      4. The MySQL Data Directory
      5. Backing Up
      6. Creating Fast, Compressed Tables with myisampack
      7. Summary
      8. Q&A
      9. Workshop
    2. Repair and Maintenance
      1. Checking and Fixing a Corrupted Database
      2. How to Use myisamchk
      3. Checking, Repairing, and Optimizing Using SQL Commands
      4. Summary
      5. Q&A
      6. Workshop
    3. Transactions and Table Locking
      1. What Is Table Locking?
      2. How MySQL Uses Table Locks
      3. Cooperative Locks
      4. Transaction-Safe Table Types
      5. Summary
      6. Q&A
      7. Workshop
    4. Optimizing Performance
      1. Why Optimize Performance?
      2. What Do We Mean by Performance Anyway?
      3. Factors Affecting Performance
      4. Commands for Optimization
      5. Summary
      6. Q&A
      7. Workshop
    5. Replication
      1. What Is Replication?
      2. Replication in MySQL
      3. Setting Up a Master-Slave Replication System
      4. Customizing Replication
      5. SQL Commands for Replication
      6. Replication Topologies
      7. Summary
      8. Q&A
      9. Workshop
    6. Embedding the MySQL Server
      1. What Is an Embedded System?
      2. How to Create an Embedded System
      3. Building Embedded Server Programs
      4. Summary
      5. Q&A
      6. Workshop
    7. Extending MySQL's Function Library
      1. Why Create a New Function?
      2. An Overview of Installing UDFs
      3. Hands-on: Creating a Simple Function
      4. Writing Your Own UDFs
      5. Summary
      6. Q&A
      7. Workshop
  9. Appendices
    1. Data Type Reference
      1. Numeric Types
      2. String Types
      3. Date and Time Types
    2. SQL Reference
      1. ALTER [IGNORE] TABLE table_name action_list
      2. ANALYZE TABLE table_name[,table_name...]
      3. BACKUP TABLE table_name[,table_name...] TO '/backup/directory'
      4. BEGIN
      5. CHECK TABLE table_name[,table_name,...] [options]
      6. COMMIT
      7. CREATE DATABASE [IF NOT EXISTS] database_name
      8. CREATE [AGGREGATE] FUNCTION function_nameRETURNS {STRING|REAL|INTEGER} SONAME shared_library_name
      9. CREATE [UNIQUE|FULLTEXT] INDEX index_nameON table_name (column_list)
      10. CREATE [TEMPORARY] TABLE[IF NOT EXISTS] table_name[(create_definition,...)][table_options] [select_statement]
      11. CROSS JOIN
      12. DELETE
      13. DESCRIBE table_name [column_name]
      14. DESC
      15. DROP DATABASE [IF EXISTS] database_name
      16. DROP FUNCTION function_name
      17. DROP INDEX index_name ON table_name
      18. DROP TABLE [IF EXISTS] table_name[,table_name...]
      19. EXPLAIN SELECT select_statement
      20. EXPLAIN table_name
      21. FLUSH flush_option[,flush_option...]
      22. GRANT
      23. HANDLER
      24. INNER JOIN
      25. INSERT
      26. KILL thread_id
      27. LEFT JOIN
      28. LEFT OUTER JOIN
      29. LOAD DATA INFILE
      30. LOCK TABLES
      31. NATURAL JOIN
      32. NATURAL LEFT JOIN
      33. NATURAL LEFT OUTER JOIN
      34. NATURAL RIGHT JOIN
      35. NATURAL RIGHT OUTER JOIN
      36. OPTIMIZE TABLE table_name[,table_name...]
      37. RENAME TABLE table_name TO new_table_name[, table_name2 TO new_table_name2 ...]
      38. REPAIR TABLE table_name[,table_name,...] [options]
      39. REPLACE
      40. RESTORE TABLE table_name[,table_name...] FROM '/backup/directory'
      41. REVOKE privileges [(column_list)][,privileges [(column_list)]...]ON {database_name.table_name}FROM username@hostname
      42. RIGHT JOIN
      43. RIGHT OUTER JOIN
      44. ROLLBACK
      45. SELECT
      46. SET [OPTION] option=value,...
      47. SHOW keyword parameters
      48. TRUNCATE TABLE table_name
      49. UNION
      50. UNLOCK TABLES
      51. UPDATE
      52. USE database_name
    3. Function and Operator Reference
      1. &, Bitwise AND
      2. &&, Logical AND
      3. |, Bitwise OR
      4. ||, Logical OR
      5. x<<n, Bitwise Left-Shift
      6. x>>n, Bitwise Right-Shift
      7. ~, Bitwise Inversion
      8. !, Not
      9. +, Addition
      10. -, Subtraction
      11. *, Multiplication
      12. /, Division
      13. %, Modulo
      14. -, Unary Minus
      15. =, Equals
      16. <=>, NULL-Safe Equals
      17. <>, Not Equal To
      18. >, Greater Than
      19. >=, Greater Than or Equal To
      20. <, Less Than
      21. <=, Less Than or Equal To
      22. ABS(x)
      23. ACOS(x)
      24. ADDDATE(date,INTERVAL expression type)
      25. AES_DECRYPT(string,key)
      26. AES_ENCRYPT(encrypted_string,key)
      27. AND, Logical
      28. ASCII(string)
      29. ASIN(x)
      30. ATAN(x)
      31. ATAN(x,y)
      32. ATAN2(x,y)
      33. AVG(expression)
      34. BENCHMARK(count,expression)
      35. expression BETWEEN min AND max
      36. BIN(n)
      37. BINARY
      38. BIT_AND(expression)
      39. BIT_COUNT(expression)
      40. BIT_OR(expression)
      41. CASE
      42. CEILING(x)
      43. CHAR(n1[,n2...])
      44. CHAR_LENGTH(string)
      45. CHARACTER_LENGTH(string)
      46. COALESCE(value[,...])
      47. CONCAT(string1[,string2,...])
      48. CONCAT_WS(separator,string1[,string2,...])
      49. CONNECTION_ID()
      50. CONV(n,base1,base2)
      51. COS(x)
      52. COT(x)
      53. COUNT(expression)
      54. COUNT(DISTINCT expression[,expression...])
      55. CURDATE()
      56. CURRENT_DATE
      57. CURRENT_TIME
      58. CURRENT_TIMESTAMP
      59. CURTIME()
      60. DATABASE()
      61. DATE_ADD(date,INTERVAL expression type)
      62. DATE_FORMAT(date,format)
      63. DATE_SUB(date,INTERVAL expression type)
      64. DAYNAME()
      65. DAYOFMONTH(date)
      66. DAYOFWEEK(date)
      67. DAYOFYEAR()
      68. DECODE(encoded_string,password)
      69. DEGREES(x)
      70. DES_DECRYPT(encrypted_string [, key_string])
      71. DES_ENCRYPT(string [, (key_number | key_string)])
      72. ELT(n,string1[,string2,string3...])
      73. ENCODE(string,password)
      74. ENCRYPT(string[,salt])
      75. EXP(x)
      76. EXPORT_SET(bits,on_str,off_str[,separator][,num_bits])
      77. EXTRACT(type FROM date)
      78. FIELD(string,string1[,string2,string3...])
      79. FIND_IN_SET(string,string_set)
      80. FLOOR(x)
      81. FORMAT(x,d)
      82. FROM_DAYS(num)
      83. FROM_UNIXTIME(unix_timestamp[,format])
      84. GET_LOCK(string,timeout)
      85. GREATEST(x1,x2[,...])
      86. HEX(n)
      87. HOUR(time)
      88. IF(test_expression,expression_true,expression_false)
      89. IFNULL(expression1,expression2)
      90. expression IN (value[,...])
      91. INET_ATON(expression)
      92. INET_NTOA(expression)
      93. INSERT(string,position,length,substr)
      94. INSTR(string,substr)
      95. INTERVAL(x,n0,n1[,n2,...])
      96. expression IS NOT NULL
      97. expression IS NULL
      98. ISNULL(expression)
      99. LAST_INSERT_ID([expression])
      100. LCASE(string)
      101. LEAST(x1,x2[,...])
      102. LEFT(string,length)
      103. LENGTH(string)
      104. string LIKE pattern [ESCAPE escape_character]
      105. LOAD_FILE(filename)
      106. LOCATE(substr,string[,position])
      107. LOG()
      108. LOG10(x)
      109. LOWER()
      110. LPAD(string,length,padding)
      111. LTRIM(string)
      112. MAKE_SET(bits,string1[,string2...])
      113. MASTER_POS_WAIT(log_name,log_pos)
      114. MATCH (column1[,column2,...] AGAINST (string)
      115. MAX(expression)
      116. MD5(string)
      117. MID(string,position,length)
      118. MIN(expression)
      119. MINUTE(time)
      120. MOD(n,m)
      121. MONTH()
      122. MONTHNAME()
      123. NOT IN
      124. string NOT LIKE pattern [ESCAPE escape_character]
      125. string NOT REGEXP pattern
      126. string NOT RLIKE pattern
      127. NOT
      128. NOW()
      129. NULLIF(expression1,expression2)
      130. OCT(n)
      131. OCTET_LENGTH(string)
      132. OR
      133. ORD()
      134. PASSWORD(string)
      135. PERIOD_ADD(period,months)
      136. PERIOD_DIFF(period1,period2)
      137. PI()
      138. POSITION(substr IN string)
      139. POW()
      140. POWER(x,y)
      141. QUARTER()
      142. RADIANS(x)
      143. RAND([n])
      144. string REGEXP pattern
      145. RELEASE_LOCK(string)
      146. REPEAT(string,count)
      147. REPLACE(string,from_string,to_string)
      148. REVERSE(string)
      149. RIGHT(string,length)
      150. string RLIKE pattern
      151. ROUND(x)
      152. ROUND(x,d)
      153. RPAD()
      154. RTRIM()
      155. SEC_TO_TIME(seconds)
      156. SECOND()
      157. SESSION_USER()
      158. SHA(string)
      159. SIGN(x)
      160. SIN()
      161. SOUNDEX(string)
      162. SPACE(length)
      163. SQRT(x)
      164. STD(expression)
      165. STDDEV()
      166. STRCMP(string1,string2)
      167. SUBDATE(date,INTERVAL expression type)
      168. SUBSTRING(string,delimiter,count)
      169. SUBSTRING_INDEX(string,delimiter,count)
      170. SUM(expression)
      171. SYSDATE()
      172. SYSTEM_USER()
      173. TAN()
      174. TIME_FORMAT(time)
      175. TIME_TO_SEC(time)
      176. TO_DAYS(date)
      177. TRIM([[LEADING|TRAILING|BOTH] [substring] FROM] string)
      178. TRUNCATE(x,d)
      179. UCASE(string)
      180. UNIX_TIMESTAMP([date])
      181. UPPER(string)
      182. USER()
      183. VERSION()
      184. WEEK(date[,first])
      185. WEEKDAY(date)
      186. YEAR(date)
      187. YEARWEEK(date[,first])
    4. The PHP API
      1. mysql_affected_rows([link_identifier])
      2. mysql_close([link_identifier])
      3. mysql_connect([server[,username[,password[,new_link]]]])
      4. mysql_create_db(database_name[,link_identifier])
      5. mysql_data_seek(result,row_number)
      6. mysql_db_name(result,offset[,field])
      7. mysql_db_query(database_name,query[,link_identifier][,result mode])
      8. mysql_drop_db(database_name[,link_identifier])
      9. mysql_errno([link_identifier])
      10. mysql_error([link_identifier])
      11. mysql_escape_string(raw_string)
      12. mysql_fetch_array(result)
      13. mysql_fetch_assoc(result)
      14. mysql_fetch_field(result,[offset])
      15. mysql_fetch_lengths(result)
      16. mysql_fetch_object(result)
      17. mysql_fetch_row(result)
      18. mysql_field_flags(result,offset)
      19. mysql_field_len(result,offset)
      20. mysql_field_name(result,offset)
      21. mysql_field_seek(result,offset)
      22. mysql_field_table(result,offset)
      23. mysql_field_type(result,offset)
      24. mysql_free_result(result)
      25. mysql_get_client_info()
      26. mysql_get_host_info([link_identifier])
      27. mysql_get_proto_info([link_identifier])
      28. mysql_get_server_info([link_identifier])
      29. mysql_insert_id([link_identifier])
      30. mysql_list_dbs([link_identifier])
      31. mysql_list_fields(database_name,table_name[,link_identifier])
      32. mysql_list_tables(database_name,[,link_identifier])
      33. mysql_num_fields(result)
      34. mysql_num_rows(result)
      35. mysql_pconnect([server[,username[,password]]])
      36. mysql_query(query[,link_identifier][,result_mode])
      37. mysql_real_escape_string(raw_string[,link_identifier])
      38. mysql_result(result,row_offset[,field])
      39. mysql_select_db(database_name[,link_identifier])
      40. mysql_tablename(result,offset)
      41. mysql_unbuffered_query(query[,link_identifier][,result_mode])
  10. Index

Product information

  • Title: Sams Teach Yourself MySQL in 21 Days, Second Edition
  • Author(s): Tony Butcher
  • Release date: December 2002
  • Publisher(s): Sams
  • ISBN: 0672323923