My SQL John Lavigne COT 4810 Tuesday March

My. SQL John Lavigne COT 4810 Tuesday, March 25, 2008

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 analyzed

SQL Created in the 1970 s 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 Postgre. SQL Informix My. SQL

My. SQL (ver. 5. 1) Maintained by My. SQL AB AB stands for Aktiebolag, German for “Stock Company” (like Inc. )

My. SQL (ver. 5. 1) Maintained by My. SQL AB AB stands for Aktiebolag, German for “Stock Company” (like Inc. ) Named after co-developer Monty Widenius' daughter, My

My. SQL (ver. 5. 1) Maintained by My. SQL AB AB stands for Aktiebolag, German for “Stock Company” (like Inc. ) Named after co-developer Monty Widenius' daughter, My Started as an m. SQL extension, but developers decided it wasn't fast enough

Why use my. SQL? 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 my. SQL statements Functions used to emulate SQL statements All standard SQL statements are valid, with extensions Standard SQL statements are capitalized My. SQL 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] <Table. Name> [(<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 Membership. Type='Taste. MODE'"); if(mysql_exec_sql(&mysql, record)==0) { /*on successful statement, execute code here*/ }

Tools My. Access – can access My. SQL databases msql 2 mysql – converts m. SQL databases to my. SQL

References My. SQL Bible – pdf available at My. SQL 5. 1 Reference Manual, http: //gestalt. twbbs. org/doc_tmp/ebook/My. SQL_Bible. pdf 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#Checking. Client. Library. Version

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?