Managing & Using MySQL. Open Source SQL Databases for Managing Information & Web Sites. 2nd Edition (e-book)

Managing & Using MySQL. Open Source SQL Databases for Managing Information & Web Sites. 2nd Edition (e-book) - Opis

MySQL is a popular and robust open source database product that supports key subsets of SQL on both Linux and Unix systems. MySQL is free for nonprofit use and costs a small amount for commercial use. Unlike commercial databases, MySQL is affordable and easy to use. This book includes introductions to SQL and to relational database theory. If you plan to use MySQL to build web sites or other Linux or Unix applications, this book teaches you to do that, and it will remain useful as a reference once you understand the basics. Ample tutorial material and examples are included throughout.This book has all you need to take full advantage of this powerful database management system. It takes you through the whole process from installation and configuration to programming interfaces and database administration. This second edition has a greatly enhanced administration chapter that includes information on administrative tools, server configuration, server startup and shutdown, log file management, database backup and restore, and database administration and repair. In addition, a new chapter on security describes data, server, and client-server security, while a chapter on extending MySQL provides an overview of MySQL internals and describes the use of MySQL user-defined functions.If you know C/C++, Java, Perl, PHP, or Python, you can write programs to interact with your MySQL database. In addition, you can embed queries and updates directly in an HTML file so that a web page becomes its own interface to the database. Managing and Using MySQL includes chapters on the programming (...) więcej language interfaces, and it also includes a complete reference section with specific function calls for each language.Also included in the reference section are references to the SQL language, and details of the MySQL system variables, programs, and utilities. New to the second edition is a reference to the internal MySQL tables, which will be of particular interest to those who want to work extensively with MySQL security. Spis treści: Managing and Using MySQL, 2nd EditionSPECIAL OFFER: Upgrade this ebook with OReillyA Note Regarding Supplemental FilesPrefaceAudiencePurposeUsing This BookConventions Used in This BookComments and QuestionsAcknowledgmentsFrom Randy YargerFrom George ReeseFrom Tim KingI. Introduction1. MySQL1.1. Relational Databases1.2. The History of MySQL1.3. MySQL Design1.4. MySQL Features1.5. MySQL Applications1.6. What You Get2. Installation2.1. Preparation2.2. Unix Installation2.2.1. Binary (Tarball) Distributions2.2.2. Binary (RPM) Distributions2.2.3. Source Distributions2.3. Windows Installation2.3.1. Windows 9x Startup2.3.2. Windows NT/2000 Startup3. SQL According to MySQL3.1. SQL Basics3.1.1. The SQL Story3.1.2. The Design of SQL3.1.3. Sending SQL to MySQL3.2. Database Creation3.3. Table Management3.4. MySQL Data Types3.4.1. Numeric Types3.4.2. Character Types3.4.3. Binary Data Types3.4.4. Enumerations and Sets3.4.5. Other Kinds of Data3.5. Indexing3.6. Managing Data3.6.1. Inserts3.6.2. Sequence Generation3.6.3. Updates3.6.4. The WHERE Clause3.6.5. Deletes3.7. Queries3.7.1. Joins3.7.2. Aliasing3.7.3. Ordering and Grouping3.7.3.1. Basic ordering3.7.3.2. Localized sorting3.7.3.3. Grouping3.7.4. Limiting Results3.8. SQL Operators3.8.1. Logical Operators3.8.2. Nulls Idiosyncrasies3.8.3. Membership Tests3.8.4. Pattern Matching3.9. Advanced Features3.9.1. Full Text Searching3.9.1.1. The Basics3.9.1.2. Relevance values3.9.1.3. Boolean mode3.9.1.4. Tips3.9.2. Transactions3.9.3. Table Locking3.9.4. Functions3.9.4.1. Date functions3.9.4.2. String functions3.9.5. Outer Joins3.9.6. Unions3.9.7. Batch Processing3.9.7.1. Command-line loads3.9.7.2. The LOAD command3.9.7.3. Pulling data from MySQL4. Database Administration4.1. Configuration4.1.1. File Locations4.1.2. File Content4.2. Server Startup and Shutdown4.2.1. Unix/Linux4.2.1.1. SVR44.2.1.2. Other Unix4.2.2. Mac OS X4.2.3. Windows NT/20004.3. Logging4.3.1. The Error Log4.3.2. The Binary Log4.3.3. The Slow Query Log4.3.4. Log Rotation4.4. Backup4.4.1. mysqldump4.4.2. mysqlhotcopy4.5. Recovery4.5.1. mysqldump Recovery4.5.2. mysqlhotcopy Recovery4.6. Table Maintenance and Crash Recovery4.6.1. Checking a Table4.6.2. Repairing a Table4.6.3. Scheduled Table CheckingII. MySQL Administration5. Performance Tuning5.1. An Approach to Performance Tuning5.2. Application Tuning5.2.1. Host Application Tuning5.2.2. SQL Query Tuning5.2.2.1. Index guidelines5.2.2.2. EXPLAIN SELECT5.2.2.3. Other options5.3. Database Server Tuning5.4. Operating System/Hardware Tuning6. Security6.1. Database Security6.1.1. User Management6.1.2. Privilege Management6.1.2.1. GRANT and REVOKE6.1.2.2. The security tables6.1.3. Recovering from Password and Permission Problems6.2. System Security6.2.1. Operating System Security6.2.2. Hardware Security6.2.3. Network Security6.2.3.1. Network topology6.2.3.2. Encryption6.2.3.3. Direct compromise6.3. Application Security6.3.1. The Application Server6.3.1.1. User management6.3.1.2. Resource protection6.3.2. Client Applications7. Database Design7.1. Database Design Primer7.1.1. Database Entities7.1.2. Entity Attributes7.1.3. Data Model7.2. Normalization7.2.1. First Normal Form7.2.2. The Unique Identifier7.2.3. Relationships7.2.4. Second Normal Form7.2.5. Kinds of Relationships7.2.6. Refining Relationships7.2.7. More 2NF7.2.8. Third Normal Form7.3. A Logical Data-Modeling Methodology7.4. Physical Database Design7.4.1. Tables and Columns7.4.2. Foreign KeysIII. MySQL Programming8. Database Applications8.1. Architecture8.1.1. Client/Server Architecture8.1.1.1. Application logic8.1.1.2. Fat and thin clients8.1.2. Distributed Application Architecture8.1.3. Web Architecture8.2. Connections and Transactions8.2.1. Connections8.2.2. Transactions8.2.2.1. Transaction isolation levels8.2.2.2. Using READ UNCOMMITTED8.3. Object/Relational Modeling9. Perl9.1. Introduction to DBI9.1.1. Basic Perl Example9.1.2. Setting Up the Database and Program9.1.3. Error Handling and Attributes9.1.4. Introducing Bind Variables and Optimizations9.2. DBI and CGI9.2.1. Introduction to Perl CGI9.2.2. The Model/View/Controller Methodology9.2.3. A Sample CGI/DBI Program9.3. A General Model for Maintainable Perl Programs9.3.1. A Model for Relational Data9.3.2. Implementing the Model9.3.2.1. The Publisher class9.3.2.2. Methods that build and execute SQL9.3.2.3. Methods that handle WHERE clauses9.3.2.4. Getter/setter methods9.3.2.5. Primary key select method9.3.2.6. Constructors9.3.2.7. The DB class9.3.2.8. The mysql class9.3.2.9. The Cache class9.3.3. Example of the Model's Use10. Python10.1. DB-API10.1.1. The Database Connection10.1.2. Cursors10.1.3. Parameterized SQL10.1.4. Other Objects10.2. Proprietary Operations10.3. Applied DB-API11. PHP11.1. Introducing PHP11.1.1. A Short Language Primer11.2. Installing PHP11.2.1. Getting Started Under Unix11.2.1.1. Installation problems11.2.2. Getting Started Under Microsoft Windows11.3. Accessing the MySQL DBMS with PHP11.3.1. The Wedding Gift Registry Database11.3.2. Opening and Using a Database Connection11.3.3. Handling Results11.3.4. Frequently Used MySQL Library Functions11.3.5. Handling MySQL Errors11.3.6. Include Files11.4. Securing User Data11.5. Managing Sessions11.6. Writing Data with PHP11.7. Using the HTML Environment11.8. Where to Find Out More12. C API12.1. API Overview12.1.1. The Connection12.1.2. Queries and Results12.1.3. Closing the Connection12.2. The C API in Practice12.2.1. Support Functions12.2.2. Quote Retrieval12.2.3. Adding Symbols12.3. Advanced Issues13. Java13.1. The JDBC API13.1.1. The JDBC Architecture13.1.2. Connecting to MySQL13.1.2.1. Data source connectivity13.1.2.2. Driver manager connectivity13.1.3. Maintaining Portability Using Properties Files13.1.3.1. Properties files13.1.3.2. Data sources revisited13.2. Simple Database Access13.2.1. Queries and Result Sets13.2.2. Error Handling and Clean Up13.3. Dynamic Database Access13.3.1. Metadata13.3.2. Processing Dynamic SQL13.4. A Guest Book Servlet14. Extending MySQL14.1. User-Defined Functions14.1.1. Standard Functions14.1.1.1. The init routine14.1.1.2. The main routine14.1.1.3. The deinit routine14.1.2. Aggregate Functions14.1.2.1. init14.1.2.2. reset14.1.2.3. add14.1.2.4. main14.1.2.5. deinit14.1.2.6. Aggregate example14.1.3. Calling a UDF14.2. Alternative Character Sets14.2.1. Simple Character Sets14.2.2. Complex Character SetsIV. MySQL Reference15. SQL Syntax for MySQL15.1. Basic Syntax15.1.1. Literals15.1.2. Identifiers15.1.3. Comments15.2. SQL CommandsALTER TABLEANALYZE TABLECREATE DATABASECREATE FUNCTIONCREATE INDEXCREATE TABLEDELETEDESCRIBEDESCDROP DATABASEDROP FUNCTIONDROP INDEXDROP TABLEEXPLAINFLUSHGRANTINSERTKILLLOADLOCKOPTIMIZEREPLACEREVOKESELECTSETSHOWTRUNCATEUNLOCKUPDATEUSE16. MySQL Data Types16.1. Numeric Data TypesBIGINTDECDECIMALDOUBLEDOUBLE PRECISIONFLOATINTINTEGERMEDIUMINTNUMERICREALSMALLINTTINYINT16.2. String Data TypesBLOBCHARCHARACTERCHARACTER VARYINGLONGBLOBLONGTEXTMEDIUMBLOBMEDIUMTEXTNCHARNATIONAL CHARNATIONAL CHARACTERNATIONAL VARCHARTEXTTINYBLOBTINYTEXTVARCHAR16.3. Date Data TypesDATEDATETIMETIMETIMESTAMPYEAR16.4. Complex Data TypesENUMSET17. Operators and Functions17.1. Operators17.1.1. Rules of Precedence17.1.2. Arithmetic Operators17.1.3. Comparison Operators17.1.4. Logical Operators17.2. Functions17.2.1. Aggregate Functions17.2.2. General Functions18. MySQL PHP API Reference18.1. Data Types18.2. Functionsmysql_affected_rowsmysql_change_usermysql_closemysql_connectmysql_create_dbmysql_data_seekmysql_db_namemysql_drop_dbmysql_errnomysql_errormysql_escape_stringmysql_fetch_arraymysql_fetch_assocmysql_fetch_fieldmysql_fetch_lengthsmysql_fetch_objectmysql_fetch_rowmysql_field_flagsmysql_field_namemysql_field_lenmysql_field_seekmysql_field_tablemysql_field_typemysql_free_resultmysql_get_client_infomysql_get_host_infomysql_get_proto_infomysql_get_server_infomysql_insert_idmysql_list_dbsmysql_list_fieldsmysql_list_tablesmysql_num_fieldsmysql_num_rowsmysql_pconnectmysql_querymysql_resultmysql_select_dbmysql_tablenamemysql_unbuffered_query19. C Reference19.1. Data Types19.2. Functionsmysql_affected_rowsmysql_change_usermysql_character_set_namemysql_closemysql_connectmysql_create_dbmysql_data_seekmysql_debugmysql_drop_dbmysql_dump_debug_infomysql_eofmysql_errnomysql_errormysql_escape_stringmysql_fetch_fieldmysql_fetch_field_directmysql_fetch_fieldsmysql_fetch_lengthsmysql_fetch_rowmysql_field_countmysql_field_seekmysql_field_tellmysql_free_resultmysql_get_client_infomysql_get_host_infomysql_get_proto_infomysql_get_server_infomysql_infomysql_initmysql_insert_idmysql_killmysql_list_dbsmysql_list_fieldsmysql_list_processesmysql_list_tablesmysql_num_fieldsmysql_num_rowsmysql_odbc_escape_stringmysql_odbc_remove_escapemysql_optionsmysql_pingmysql_querymysql_read_query_resultmysql_real_connectmysql_real_escape_stringmysql_real_querymysql_refreshmysql_reloadmysql_row_seekmysql_row_tellmysql_select_dbmysql_send_querymysql_shutdownmysql_ssl_ciphermysql_ssl_clearmysql_ssl_setmysql_statmysql_store_resultmysql_thread_idmysql_thread_safemysql_use_result20. The Python DB-API20.1. Module: MySQLdb20.1.1. Module Attributesapilevelparamstylequote_convthreadsafetytype_conv20.1.2. Module MethodsMySQL.connect()20.1.3. Connection Attributesdb20.1.4. Connection Methodsclose()commit()cursor()rollback( )20.1.5. Cursor Attributesarraysizedescriptionrowcount20.1.6. Cursor Methodscallproc( )Method: close( )execute( )executemany( )Method: fetchall( )fetchmany( )fetchone( )insert_id( )*nextset( )setinputsizes( )setoutputsize( )IndexAbout the AuthorsColophonSPECIAL OFFER: Upgrade this ebook with OReilly mniej