340 likes | 636 Vues
John Lavigne COT 4810 Tuesday, March 25, 2008. MySQL. Databases. “A comprehensive collection of related data organized for convenient access, generally in a computer.” - dictionary.com Useful to store information in similar formats Information stored in tables with common categories.
E N D
John Lavigne COT 4810 Tuesday, March 25, 2008 MySQL
Databases • “A comprehensive collection of related data organized for convenient access, generally in a computer.” - dictionary.com • Useful to store information in similar formats • Information stored in tables with common categories
Why use Databases? • They are useful for storing especially large amounts of information? • When you want to use something else (arrays maybe?)
Advantages of Relational Databases • Speed – Quick Storage and Retrieval of Information • Thoroughness – can report results as thorough as the information stored • Accuracy – Accurate and consistent Results • Reporting – Information can be efficiently gathered and analyzed
SQL • Created in the 1970s • Evolved as Databases grew in size • Standard set by ISO/IEC 9075:1992, also known as ANSI SQL-92
SQL • Common implementations: • Oracle • MS SQL Server • PostgreSQL • Informix • MySQL
MySQL (ver. 5.1) • Maintained by MySQL AB • AB stands for Aktiebolag, German for “Stock Company” (like Inc.)
MySQL (ver. 5.1) • Maintained by MySQL AB • AB stands for Aktiebolag, German for “Stock Company” (like Inc.) • Named after co-developer Monty Widenius' daughter, My
MySQL (ver. 5.1) • Maintained by MySQL AB • AB stands for Aktiebolag, German for “Stock Company” (like Inc.) • Named after co-developer Monty Widenius' daughter, My • Started as an mSQL extension, but developers decided it wasn't fast enough
Why use mySQL? • Stability • Web Applications – for many reads and few writes • Open Source • Low Overhead – Can run on machines using less than 32 MB of RAM • Large Table Size – can store up to 8 TB per table (limited by computer file sizes before it reaches this limit)
API • Application Programming Interfaces • Support for multiple threads • Multiple languages available • C • C++ • JDBC/Java • Perl • PHP • And more...
API for C • Most Common • Most Developed • Installing this API • http://dev.mysql.com/doc/refman/5.1/en/apis.html • C API statements will return 0 on successful execution
Basic Structure for C API Programs 1. #include <mysql/mysql.h> 2. Define MYSQL variable 3. mysql_init(); 4. mysql_options(); // optionally set options 5. mysql_real_connect(); // connect to the database 6. Program statements 7. mysql_close()
Structure for mySQL statements • Functions used to emulate SQL statements • All standard SQL statements are valid, with extensions • Standard SQL statements are capitalized • MySQL extensions are lower-case • For the following slides: • <statement> - statement • [<statement>] - optional statement • (<statement>) - 1 or more times
Creating a Database • CREATE DATABASE [if not exists] <DBName> • mysql_create_db(&mysql, “DBName”)
Creating a Table • CREATE [temporary] TABLE [if not exists] <TableName> [(<column definition>, ...)] [<table options>][<select statement>]
Inserting Data • INSERT [low-priority | delayed] [ignore] [into] <tablename> [(<columnname>, ...)] VALUES (<insert expression>,)
Other commands • Strmov(query_def, "SELECT Name,EMail FROM Registration WHERE MembershipType='TasteMODE'"); • if(mysql_exec_sql(&mysql,record)==0) { /*on successful statement, execute code here*/ }
Tools • MyAccess – can access MySQL databases • msql2mysql – converts mSQL databases to mySQL
References • MySQL Bible – pdf available at • http://gestalt.twbbs.org/doc_tmp/e-book/MySQL_Bible.pdf • MySQL 5.1 Reference Manual, • available online at http://dev.mysql.com/doc/refman/5.1/en/index.html • C API Examples - http://www.geocities.com/jahan.geo/mysql_c_by_example.html#CheckingClientLibraryVersion
Questions • 1. Where can you download and install the API for the C language? • 2. What is the function that can be called to access the database?