1	mySQL and Relational Databases A brief introduction by Jeff Smith Copyright © 2004. All rights reserved.
2	mySQL and Relational Databases What is a Database? Database An organized collection of data grouped by common attributes A collection of tables
3	What is a Table ? Table Rows (records) Columns (fields) Field value A field value is the intersection of a row and a column A USER table could store user information while a table called MODEL might store information about weather models
4	Table Concepts and Terms -1 A table is divided into rows, and each row is often referred to as a Record. So if you look up your information in a telephone book you are reviewing your record. Each “row” contains several pieces of information: your name, address, and phone number. Each column in a table is a category of information referred to as a Field. In a telephone book, one column might be the names, another might be the phone numbers. A single item of data, such as a single phone number, is called a data value.
5	Table Concepts and Terms -2
6	Keys of a Table -1 Primary key Unique identifier which identifies a row Foreign key A primary key in another table - used to lookup information (provide referential integrity) Example: USERS and MODEL_GROUP tables Each has primary key – USER_ID and MODEL_GROUP_ID USER_ID is also stored in the MODEL_GROUP table to relate this model group to its user – USER_ID is a foreign key in the MODEL_GROUP table
7	Keys of a Table -2 USER ------------------- USER_ID (PK) USER_NAME PASSWORD FIRST_NAME LAST_NAME
8	Indexes Primary Index Unique key for each record Used to enforce primary keys The primary key can also be composite (comprised of more than one table column) Secondary Indexes Used to speed up ordering and searching For example, if you had thousands of users in the USER table and frequently searched for users by their last name, you might add a secondary index to last name in order to speed up these searches.
9	Referential and Domain Integrity Referential Integrity Where a field or group of fields in one table must refer to the key in another table E.g. All models could be required to have a user. If you tried to enter a model record with a bogus USER_ID, the database would refuse to accept the record (and generate an error) Domain Integrity Ensuring field values are within a valid domain E.g. start_year field in model table must be > 1950 Some databases allow you to define integrity rules in the database - engine then rejects invalid records mySQL 5 supports this capability
10	About mySQL Open source! Here’s some mySQL hype: The MySQL database server is the world's most popular open source database. Over five million installations use MySQL to power high-volume Web sites and other critical business systems — including industry-leaders like The Associated Press, Google, NASA, Sabre Holdings and Suzuki. Download it for free from the web: http://www.mysql.com/ mySQL runs on several platforms, including Windows and Linux
11	Setting up a mySQL Database - 1 Remember, database is a collection of tables You must first create database before you can add tables to it With mySQL, you can either create a database using a GUI tool like MySQL Administrator The latest version of mySQL is version 5, but as of 12/10/04 it is still in Beta, so you might be better off with the version 4 release
12
13	Setting up a mySQL Database - 2 Alternatively, you can create a db with a script CREATE TABLE USER ( USER_ID int NOT NULL auto_increment, USERNAME varchar(20) default NULL, PASSWORD varchar(20) default NULL, PRIMARY KEY (USER_ID), UNIQUE KEY LOGIN (USERNAME, PASSWORD) ) TYPE=InnoDB; # # Table structure for table MODEL_GROUP # CREATE TABLE MODEL_GROUP ( MODEL_GROUP_ID int NOT NULL auto_increment, …..
14	Setting up a Database - 3 You can run this script from a command line or a GUI
15	Basic SQL SQL stands for Structured Query Language. It is a fairly standard way to communicate with relational databases such as mySQL, Postgres, Oracle, SQL Server, etc. There are four common SQL commands: SELECT -retrieves the specified records INSERT -adds a new row (or record) UPDATE -changes values in the specified row(s) DELETE -removes the specified rows
16	Basic SQL, SELECT statement -1 Select statements generally retrieve data from one or more database tables. SELECT column_names FROM table_names [ WHERE search_condition ] [ GROUP BY group_expression ] [HAVING condition] [ ORDER BY order_condition [ ASC \| DESC ] ] Note: The statements in square brackets are optional.
17	Basic SQL, SELECT statement -2 For example, to select the first name and last name of user number 122: SELECT FirstName, LastName FROM Users WHERE UserID = 122 Result: FIRSTNAME LASTNAME Jeff Smith
18	Basic SQL, SELECT statement -3 For example, to select all the fields for a particular user: SELECT * FROM Users WHERE UserName = ‘jssmith’ Result: USERID USERNAME PASSWORD FIRSTNAME LASTNAME 122 jssmith password Jeff Smith
19	Basic SQL, SELECT statement -4 You can order the results of your SELECT statement with the ORDER BY clause SELECT * FROM user WHERE userid > 120 AND userid < 135 ORDER BY lastname DESC Result: USERID USERNAME PASSWORD FIRSTNAME LASTNAME 121 kzeller tree13 Kathy Zeller 122 jssmith password Jeff Smith 134 tarroyo apple01 Thomas Arroyo
20	Basic SQL, SELECT statement -5 You can also join tables with your search criteria. For example, to find all the model groups for user jssmith: SELECT model_group.* FROM model_group, user WHERE user.username = ‘jssmith’ AND user.user_id = model_group.user_id Result: MODEL_GROUP_ID USER_ID MODEL_GROUP_NAME 11 122 Jeff’s Ensemble
21	Basic SQL, SELECT statement -6 Sometimes SQL statements can get verbose. As a shorthand, you can specify aliases for the table names. SELECT MG.* FROM model_group MG, user U WHERE U.username = ‘jssmith’ AND U.user_id = MG.user_id Result: MODEL_GROUP_ID USER_ID MODEL_GROUP_NAME 11 122 Jeff’s Ensemble
22	Basic SQL, INSERT statement Insert statements insert data into a table INSERT INTO table_name(column_names) VALUES(value_list) For example: INSERT INTO USER(username, password) VALUES (‘jssmith’, ‘password’)
23	Basic SQL, UPDATE statement An update statement updates (changes) an existing record (row) in a table UPDATE table_name SET column_name1=value1, ... , column_nameN=valueN [ WHERE search_condition ] For example: UPDATE model_group SET model_group_name = ‘my ensemble’ WHERE model_group_id = 134
24	Basic SQL, DELETE statement Delete statement remove data from a table DELETE FROM table_name [ WHERE search_condition ] For example: DELETE FROM model_group WHERE model_group_id = 134
25	Summary SQL is a simple, reliable, and standard way to save and retrieve information mySQL is open source (basically free for non-commercial uses) mySQL supports Windows, UNIX, Linux, etc. and so has widespread support mySQL is mature and stable There are some fairly slick tools to use with mySQL (notably MySQL Administrator and Query Browser)