Transcript SQL - Jim Rome`s Interactive Site

Introduction to Databases and SQL
Paul C. Shipe
TGA
August 2010
0.0 Overview
 The following are our goals for this Introduction to
Databases and SQL:





Introduction to Relational Databases
ANSI/ISO SQL Standard
Hands On SQL
Data Modeling
Advanced Topics
0.1 Overview
 The following are a few resources which were used in
preparing this Introduction:

MySQL Tutorial, by Luke Welling, Laura Thomson

Learning MySQL , (from O’REILLY)

MySQL 5.0 Reference Manual

Oracle Database Administrator’s Guide (10.2)

Oracle Database 10g High Availability, White Paper
1.0 Databases and MySQL
 Basically, a database stores information in some
structured format.
 There are many different kinds of databases. Some
of the more traditional databases include:





Flat File Text Databases
Relational Databases
Object Oriented Databases
Object-Relational Databases
XML Databases
1.1 Databases and MySQL
 Some of the more popular relational and object-relational
databases include:
Oracle
DB2
SQLServer
MySQL
PostgreSQL
Sybase
Informix
...
1.2 Databases and MySQL
 We will use MySQL for our examples.
 Let’s look at the MySQL Web site, and get an idea of
the different types of resources that are available,
mostly for free: http://www.mysql.com





Documentation > View next to “English v5.0”
Downloads > MySQl 6.0 - Alpha > Select from OS
Documentation > Articles > White Papers
Type ‘create table’ in the Search Field
The mysql web site can be extremely useful
2.0 ANSI/ISO SQL Standards
 SQL (Structured Query Language) is the language of relational
databases.
 In the computer industry, like many other industries, there are
standards which are important to provide consistency. (Imagine if
every database had a completely different language to learn, or if C++
or Java were not the same from platform to platform.)
 The American National Standards Institute (ANSI) and the International
Organization for Standardization (ISO) have worked on SQL
Standards.

SQL-86
Remember Set Theory?

SQL-89
Structured Query Language (SQL) is just a

SQL-92
way of creating a Venn diagram that will

SQL:1999
select the rows of interest from the database

SQL:2003
tables.

SQL:2006
 A good review of the SQL Standards can be found at Wikepedia.
2.1 ANSI/ISO SQL-99 Standard
 Although databases may vary from one to another on the basic
structure of their implementation, most of the aspects of SQL
are consistent from one database to another.
 There are basically two parts to the SQL Language, DDL or the
Data Definition Language, and DML or the Data Manipulation
Language.
 The DDL Language involves, creating, modifying and granting
permissions on:

Databases

Users

Tables

Indexes

...
2.2 ANSI/ISO SQL-99 Standard
 The DML Language involves manipulating data inside the
database. Statements such as the following comprise DML:





Insert
Update
Delete
Select *
...
 The SQL Standard specifies the precise format of each of the
statements, which make up the Standard.
3.0 Databases,Users,Permissions
We will use Appendix A to Setup and
Configure the MySQL database.
3.1 Databases,Users,Permissions
 Open a DOS Window in the Upper Left, we will use this Window to log into MySQL as the ‘root’
user. Password should be TGA_2010
 Open a DOS Window in the Lower Right, we will use this Window to log into MySQL as the
‘teachers’ user.
 In the upper left:
C:\>mysql -u root -p
Enter password: ********
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.1.49-community MySQL Community Edition (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show databases;
+--------------------+
| Database
|
+--------------------+
| information_schema |
| mysql
|
| test
|
+--------------------+
3 rows in set (0.00 sec)
Don't forget the semicolon
3.2 Databases,Users,Permissions
mysql> create database grades;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database
|
+--------------------+
| information_schema |
| grades
|
| mysql
|
| test
|
+--------------------+
4 rows in set (0.00 sec)
mysql> use grades;
Database changed
mysql> show tables;
Empty set (0.00 sec)
mysql> create table stuff
-> (stuff_id int not null primary key,
-> stuff_name varchar(30)
-> ) engine=InnoDB;
Query OK, 0 rows affected, 1 warning (0.05 sec)
3.3 Databases,Users,Permissions
mysql> show tables;
+------------------+
| Tables_in_grades |
+------------------+
| stuff
|
+------------------+
1 row in set (0.00 sec)
mysql> describe stuff;
+------------+-------------+------+-----+---------+-------+
| Field
| Type
| Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| stuff_id
| int(11)
| NO
| PRI | NULL
|
|
| stuff_name | varchar(30) | YES |
| NULL
|
|
+------------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
note the apostrophes
mysql> grant all on test.* to teachers@localhost identified by 'TGA_2010';
Query OK, 0 rows affected (0.02 sec)
Next, go to the Lower Right Window, where we will log on as the user
‘teachers’.
3.4 Databases,Users,Permissions
C:\>mysql -u teachers -p
Enter password: ********
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.0.51b-community-nt MySQL Community Edition (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> use test;
Database changed
mysql> use grades;
ERROR 1044 (42000): Access denied for user 'teachers'@'localhost' to database 'g
rades'
Next go to the Upper Left Window, and grant the user ‘teachers’ privileges on grades.
mysql> grant all on grades.* to teachers@localhost;
Query OK, 0 rows affected (0.00 sec)
When creating a User, the DBA does not have to grant all, nor grant to *.
3.5 Databases,Users,Permissions
Finally, In the Lower Right Window:
mysql> use grades;
Database changed
mysql> describe stuff;
+------------+-------------+------+-----+---------+-------+
| Field
| Type
| Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| stuff_id
| int(11)
| NO
| PRI | NULL
|
|
| stuff_name | varchar(30) | YES |
| NULL
|
|
+------------+-------------+------+-----+---------+-------+
2 rows in set (0.02 sec)
mysql>
3.6 Databases,Users,Permissions
 A Few Data Types in MySQL:
Numerical
int
float
double
String and Text
char
varchar
blob
Date
date
time
datetime
timestamp
3.7 Databases,Users,Permissions
 A Few Privileges in MySQL
User
create
delete
index
lock tables
show databases
Administrator
all
drop
process
shutdown
create temporary tables
execute
insert
select
update
alter
file
reload
with grant option
4.0 Basic SQL
• Close all MySQL Monitors. Type ‘quit;’.
• In the Lower Right Window, logon as ‘teachers’ again.
C:\>mysql -u teachers -p
Enter password: ********
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 5.0.51b-community-nt MySQL Community Edition (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> use grades;
Database changed
mysql> select * from stuff;
Empty set (0.06 sec)
mysql> insert into stuff values (1,'Mouse');
Query OK, 1 row affected (0.05 sec)
mysql> insert into stuff values (2,'Keyboard');
Query OK, 1 row affected (0.01 sec)
mysql> insert into stuff values (3,'Monitor');
Query OK, 1 row affected (0.00 sec)
4.1 Basic SQL
mysql> select * from stuff;
+----------+------------+
| stuff_id | stuff_name |
+----------+------------+
|
1 | Mouse
|
|
2 | Keyboard
|
|
3 | Monitor
|
+----------+------------+
3 rows in set (0.00 sec)
mysql> update stuff set stuff_name='CPU' where stuff_id=3;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from stuff;
+----------+------------+
| stuff_id | stuff_name |
+----------+------------+
|
1 | Mouse
|
|
2 | Keyboard
|
|
3 | CPU
|
+----------+------------+
3 rows in set (0.00 sec)
mysql> delete from stuff where stuff_name = 'CPU';
Query OK, 1 row affected (0.00 sec)
4.2 Basic SQL
mysql> select * from stuff;
+----------+------------+
| stuff_id | stuff_name |
+----------+------------+
|
1 | Mouse
|
|
2 | Keyboard
|
+----------+------------+
2 rows in set (0.00 sec)
mysql> quit
Bye
C:\>
5.0 Transactions
 One of the problems with many people accessing and changing the same
database information, is keeping the data consistent.
 Transactions provide a way of grouping a set of SQL Statements so that they
either all succeed or all fail. A transaction ends with either a commit or a
rollback.
 The default in MySQL is that each statement you type will autocommit.
 Logon as the user ‘root’ in the Upper Left, and as the user ‘teachers’ in the
Lower Right:
Lower Right as ‘teachers’
mysql> use grades;
Database changed
mysql>
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
Setting autocommit=0 will cause mysql to “not” automatically commit.
5.1 Transactions
mysql> select * from stuff;
+----------+------------+
| stuff_id | stuff_name |
+----------+------------+
|
1 | Mouse
|
|
2 | Keyboard
|
+----------+------------+
2 rows in set (0.00 sec)
mysql> insert into stuff values (3,'Hard Drive');
Query OK, 1 row affected (0.00 sec)
mysql> select * from stuff;
+----------+------------+
| stuff_id | stuff_name |
+----------+------------+
|
1 | Mouse
|
|
2 | Keyboard
|
|
3 | Hard Drive |
+----------+------------+
3 rows in set (0.00 sec)
Now Go to the Upper Left Window and select from stuff.
5.2 Transactions
mysql> use grades;
Database changed
mysql> select * from stuff;
+----------+------------+
| stuff_id | stuff_name |
+----------+------------+
|
1 | Mouse
|
|
2 | Keyboard
|
+----------+------------+
2 rows in set (0.03 sec)
Since the transaction started by the ‘teachers’ session has not
committed the transaction, it is not available to other sessions.
Now in the Lower Right Window, commit the transaction.
mysql> commit;
Query OK, 0 rows affected (0.03 sec)
5.3 Transactions
Now Select again in the Upper Left Window as root.
mysql> select * from stuff;
+----------+------------+
| stuff_id | stuff_name |
+----------+------------+
|
1 | Mouse
|
|
2 | Keyboard
|
|
3 | Hard Drive |
+----------+------------+
3 rows in set (0.00 sec)
Now again in the Lower Right as ‘teachers’.
mysql> insert into stuff values (4,'Printer');
Query OK, 1 row affected (0.00 sec)
mysql> select * from stuff;
+----------+------------+
| stuff_id | stuff_name |
+----------+------------+
|
1 | Mouse
|
|
2 | Keyboard
|
|
3 | Hard Drive |
|
4 | Printer
|
+----------+------------+
4 rows in set (0.00 sec)
5.4 Transactions
mysql> rollback;
Query OK, 0 rows affected (0.02 sec)
mysql> select * from stuff;
+----------+------------+
| stuff_id | stuff_name |
+----------+------------+
|
1 | Mouse
|
|
2 | Keyboard
|
|
3 | Hard Drive |
+----------+------------+
3 rows in set (0.00 sec)
mysql> quit
Bye
C:\>
5.5 Transactions
• A Few Types of MySQL Storage Engines:

MyISAM
Default, No Transactions, No Foreign Keys

InnoDB
Transactions, Foreign Keys

Berkeley DB
Similar to InnoDB, but not as widely used

HEAP
Stored in Memory, Fast, Unsafe
6.0 Data Modeling
 Terms
 Entities are Things.
 Relationships are links between entities. (1 to 1, 1 to many,many to 1,many to
many)
 Tables (Relations) hold a set of data in tabular form.
 Columns (Attributes) belonging to tables describe the attributes that each data
item has.
 Rows in tables hold data items for each column in a table.
 Keys are used to identify a single row.
 Normalization is a process for improving database design.
6.1 Data Modeling
Consider the following employee,
department, skills set of data:
Empid
Name
Job
Deptid
Department
Skills
10
Hamilton Hunt
Programmer
100
Computers
Java,C,Perl
20
Joe James
DBA
200
Systems
Oracle,MySQL
30
Frank Farmer
Web Developer
100
Computers
Java,XML
40
Reynolds Rap
Accountant
500
Accounting
Excel,123
50
Sam Smith
CEO
600
Management
Managing
6.2 Data Modeling
7.0 Implement Data Model
 Type the following into separate files using the Crimson editor.
create table emp
(empid int not null,
name
varchar(100),
job
varchar(100),
deptid int not null,
primary key (empid),
foreign key (deptid) references dept (deptid)
) engine=INNODB;
create table dept
(deptid int not null primary key,
deptname varchar(30)
) engine=INNODB;
create table skill
(empid int not null,
skill varchar(30) not null,
primary key (empid,skill),
foreign key (empid) references emp (empid)
) engine=INNODB;
A primary key constraint declares one or more
columns whose values uniquely identify each
record (row) in the table.
Their values must be unique and not null.
A foreign key constraint defines one or
more columns in a table as referencing
columns to a unique or primary key in
another table.
Foreign keys can prevent
the entry of data for which there is no
matching value in the related table.
7.1 Implement Data Model
 Run each file by using the following command three times
mysql –u teachers –p grades < file.sql
 Then go into MySQL




Verify that the tables are there. (show tables;)
Look at the table on 6.1. Create an insert.sql file to insert this data.
Use the command above to run the script.
Use select statements to check that the data is there
select * from emp;
select * from dept;
select * from skill;
8.0 Queries
 Try a few queries on the new tables:
use grades;
select * from emp;
select empid,name from emp;
Shorthand
aliases
select * from emp where job='Programmer';
select empid, name, e.deptid, d.deptname from emp e, dept d where
e.deptid = d.deptid;
select name,s.skill from emp e,skill s where e.empid = s.empid;
Try ‘tee filename’ to spool to a file. ‘notee’ to stop spooling.
8.1 Queries
• A Few Additional Queries
select count(*) from emp;
select max(deptid) from emp;
select deptid, count(*) from emp group by deptid;
select * from emp where deptid in
(select deptid from dept where deptname='Accounting');
select now();
8.2 Queries
• A Few Additional Queries
select name, deptid*2 from emp;
select name, sqrt(deptid) from emp;
select concat("My Name is: ",name) from emp;
select table_name, engine from information_schema.tables;
Try to duplicate the data on 6.1. Can you?
8.3 Queries
• MySQl also supports
Compression
Encryption
Spatial Datatypes
Regular Expressions
Limited XML Support (More in 6.0)
9.0 Stored Procedures
Beginning with MySQL 5.0, the ability to program with stored procedures and
functions has been included. This provides a powerful programming
capability for database development. Consider the problem of inserting a new
employee into our emp database. By using a stored procedure, several SQL
statements can be grouped together and be committed or rolled back depending
on success or failure.
Type the following into a file and run it in MySQL
delimiter //
create procedure insert_new_employee
(in empid int,in name varchar(30),in job varchar(30),
in deptid int,in skill varchar(30))
begin
insert into emp values(empid,name,job,deptid);
insert into skill values (empid,skill);
commit;
end //
9.1 Stored Procedures
Make sure to set autocommit=0
mysql> call insert_new_employee(70,'Paul Payne','Accountant',500,'Quattro');
-> //
Select * from emp;
Select * from skill;
In the above example, we provided the value of 70 for the empid. The way
that it would actually be done is that the stored procedure would search
the table for the largest empid, increment, and assign that value
programmatically. Also, there would be error trapping which would
detect an error and rollback instead of commit in that case.
Our example has included only SQL, but a nice programming environment
exists for the stored procedures and functions.
10.0 MySQL Administrator
•
•
•
•
We will Use Appendix B to install MySQL GUI Tools.
Use “localhost” as the Server Host.
Type in the root password.
Use the GUI Tool to find:
Users
Databases
Tables
...
• Try using the MySQL Query Browser.
11.0 mysqldump
We will use the mysqldump command to backup a database and copy
it to a new database.
mysqldump --opt –u teachers –p grades > backup.sql
Look at the backup.sql file.
Create a new database, and then Recover the backup.
As root, you will need to create the newdatabase and
grant access to teachers.
mysql –u teachers –p newdatabase < backup.sql
Check that all tables and data are there.
use newdatabase;
show tables;
select * from emp;
12.0 Other Concepts
 Indexes. Indexes are smaller support tables which order a particular
column or columns of a table to make disk access faster.
 Stored Procedures. A stored procedure is a group of SQL statements
that form a logical unit and perform a particular task.
 Partitioning. Partitioning addresses the key problem of supporting very
large tables and indexes by allowing users to decompose them into
smaller and more manageable pieces called partitions.
 Clusters. For database systems which have very large numbers of
users accessing the data, clusters of computers can be used to access
the database, preventing bottlenecks which can occur when a system is
hosted by just one computer.
 Replication. Replication allows you to take one database, make an
exact copy of it on another server, and set one of them (the slave) to
take all its updates from the other (the master). The slave reads the
master's binary logs, which store all statements that change a database,
and repeats these on its database, keeping the two in exact sync.
13.0 What A DBA Does
 Installing and Upgrading the Database Server/Tools
 Allocating system storage.
 Creating database storage structures, database objects,
and modifying as needed.
 Enrolling users and maintaining security.
 Monitoring database access.
 Monitoring database performance and optimizing as
necessary.
 Planning for backup and recovery.
 Backing up and Restoring as Needed.
14.0 Loading Grades Schema
DROP TABLE IF EXISTS `students`;
CREATE TABLE `students` (
`Student` varchar(60) NOT NULL,
`WeightedAverage` float(6,2) NOT NULL,
`Rank` varchar(15) NOT NULL default '1/1',
PRIMARY KEY USING BTREE (`Student`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `students` (`Student`,`WeightedAverage`,`Rank`) VALUES
('Blackstone, Harry',90.40,'1/4'),
('Burton, Lance',62.80,'4/4'),
('Copperfield, David',84.20,'3/4'),
('Houdini, Harry',88.80,'2/4');
14.1 Loading Grades Schema
CREATE TABLE `grades` (
`student` varchar(70) NOT NULL,
`test_date` datetime NOT NULL,
`grade` int(11) NOT NULL,
`weight` float(4,2) NOT NULL default '1.00',
`key` int(11) NOT NULL,
PRIMARY KEY USING BTREE (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `grades` (`student`,`test_date`,`grade`,`weight`,`key`) VALUES
('Copperfield, David','2008-09-15 00:00:00',85,1.00,0),
('Copperfield, David','2008-10-12 00:00:00',81,1.00,1),
('Copperfield, David','2008-11-02 00:00:00',79,1.00,2),
('Copperfield, David','2008-12-15 00:00:00',88,2.00,3),
('Blackstone, Harry','2008-09-15 00:00:00',90,1.00,4),
('Blackstone, Harry','2008-10-12 00:00:00',87,1.00,5),
('Blackstone, Harry','2008-11-02 00:00:00',93,1.00,6),
('Blackstone, Harry','2008-12-15 00:00:00',91,2.00,7),
('Burton, Lance','2008-09-15 00:00:00',57,1.00,8),
('Burton, Lance','2008-10-12 00:00:00',65,1.00,9),
('Burton, Lance','2008-11-02 00:00:00',60,1.00,10),
('Burton, Lance','2008-12-15 00:00:00',66,2.00,11),
('Houdini, Harry','2008-09-15 00:00:00',84,1.00,12),
('Houdini, Harry','2008-10-12 00:00:00',89,1.00,13),
('Houdini, Harry','2008-11-02 00:00:00',91,1.00,14),
('Houdini, Harry','2008-12-15 00:00:00',90,2.00,15);
14.2 Loading Grades Schema
CREATE TABLE `passwords` (
`name` varchar(45) NOT NULL,
`password` varchar(45) NOT NULL,
PRIMARY KEY (`name`),
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `passwords` (`name`,`password`) VALUES
('Dennis+Reed','1100+College+St%2C+MUW29'),
('Jean+Green','3161+Prutsman+Rd.'),
('Jim+Rome','A+very+good+password%2F'),
('Larry+Hamlin','5+Ector+Street%2C+Apt.+G'),
('Laura+Morris','4+Chadwick+Wade+Drive'),
('Norissa+Martin','684+Gale+Drive%2C+Apt 2'),
('Rosalie+Wolfe','59713+US+Hwy+50');
15.0 CSAT Work
16.0 Indexes and Performance
We will create the wfs_feature table below, and populate it with
many dummy records using a stored procedure to demonstrate
the performance improvements possible by using indexes.
create table wfs_feature
(feature_id int not null primary key,
feature_type varchar(64),
feature_long float,
feature_lat float,
feature_owner varchar(30)
) engine=InnoDB;
16.1 Indexes and Performance
delimiter //
create procedure populate_wfs_feature
(in startid int,in endid int)
begin
declare rndnum int;
declare loopvar int;
set loopvar=startid;
while loopvar < endid+1 do
select floor(1+(rand()*9)) into rndnum;
if rndnum=1 then
insert into wfs_feature
elseif rndnum=2 then
insert into wfs_feature
elseif rndnum=3 then
insert into wfs_feature
elseif rndnum=4 then
insert into wfs_feature
elseif rndnum=5 then
insert into wfs_feature
elseif rndnum=6 then
insert into wfs_feature
elseif rndnum=7 then
insert into wfs_feature
elseif rndnum=8 then
insert into wfs_feature
elseif rndnum=9 then
insert into wfs_feature
elseif rndnum=10 then
insert into wfs_feature
end if;
set loopvar=loopvar+1;
end while;
end //
values(loopvar,'snet:Node',-77.7,38.6,'ORNL');
values(loopvar,'snet:Sensor',-75.7,39.6,'Ft Bragg');
values(loopvar,'snet:Observation',-79.7,33.6,’LA');
values(loopvar,'snet:Group',-77.7,38.6,’ORNL');
values(loopvar,'snet:Feature',-72.7,39.6,’DC');
values(loopvar,'snet:LocationHistory',-77.7,38.6,'ORNL');
values(loopvar,'snet:Sensor',-77.7,38.6,'Kentucky');
values(loopvar,'snet:Sensor',-77.7,38.6,'SanDiego');
values(loopvar,'snet:Sensor',-77.7,38.6,'Ontario');
values(loopvar,'snet:Sensor',-77.7,38.6,'Detroit');
16.2 Indexes and Performance
mysql> delimiter ;
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> call populate_wfs_feature(1,5000000);
Query OK, 1 row affected (3 min 37.81 sec)
mysql> select count(*) from wfs_feature;
+----------+
| count(*) |
+----------+
| 1000000 |
+----------+
1 row in set (2.83 sec)
mysql> select count(*) from wfs_feature where feature_type='snet:Sensor' and feature_owner='Kentucky';
+----------+
| count(*) |
+----------+
|
110537 |
+----------+
1 row in set (3.38 sec)
mysql> create index comp1 on wfs_feature(feature_type,feature_owner);
Query OK, 1000000 rows affected (1 min 9.26 sec)
Records: 1000000 Duplicates: 0 Warnings: 0
mysql> select count(*) from wfs_feature where feature_type='snet:Sensor' and feature_owner='Kentucky';
+----------+
| count(*) |
+----------+
|
110537 |
+----------+
1 row in set (0.73 sec)
17.0 MySQL Connector/J (Client/Server)
To Install MYSQL Connector/J:
Copy mysql-connector-java-5.1.6-bin to $JAVA_HOME\jre\lib\ext
Next:
Insert the code on the next page into “select.java”
javac select.java
java select
17.1 MySQL Connector/J (Client/Server)
import java.sql.*;
public class select
{
public static void main(String argv[])
{
try {
Statement stmt;
ResultSet rs;
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/grades?user=teachers&password=ARC_2008");
stmt = con.createStatement( );
rs = stmt.executeQuery("SELECT * from dept");
System.out.println("\nDeptid
Deptname\n");
while(rs.next( ))
{
int a= rs.getInt("deptid");
String str = rs.getString("deptname");
System.out.print(" " + a);
System.out.print("
" + str);
System.out.print("\n");
}
stmt.close( );
con.close( );
}
catch( Exception e )
{ e.printStackTrace( ); }
}
}
Appendix A.1 Installation of MySQL 5.0
•
•
•
•
•
•
•
•
•
Go to http://www.mysql.com
Click on Downloads at the top
Click on “Download >>” under MySQL Community Server
Click on Windows
Select “Pick a Mirror” for “Windows ZIP/Setup.EXE (x86)”
Click “No Thanks”
Download mysql-5.0.51b-win32.exe into c:\mysql
Double-click this file to extract Setup.exe into c:\mysql
Double-click Setup.exe to start the MySQL Installer
Click Run
Click Next on the Welcome Screen
Select “Complete” and click Next
Click Install
Appendix A.2 Installation of MySQL 5.0
Click Next on the Two Informational Screens
Ensure that Wizard Configuration is selected and click Finish
Wizard Configuration
Click Next to Configure
Select “Standard Configuration” and click Next
Make sure that the following two are checked
Install as a Windows Service
Include the bin directory in the PATH
Then click Next
Type in the root password twice, and click Next
Click Execute
Click Finish
• Check Start > All Programs > MySQL > MySQL Server 5.0
Appendix A.1 Installation of MySQL 5.0
•
•
•
•
Go to http://www.mysql.com/downloads/mysql/
Select the Windows version
Download mysql-5.1.49-win32.msi into c:\mysql
Double-click this file to start the MySQL Installer
Click Next on the Welcome Screen
Select “Complete” and click Next
Click Install
Allow the install (if Windows 7 or Vista)
Click Next on the Enterprise screen
Click next on the monitor service
Appendix A.2 Installation of MySQL 5.0
Click Next on the Two Informational Screens
Ensure that Configuration Wizard is selected and click Finish
Wizard Configuration
Click Next to Configure. Allow MySQL to make changes.
Select “Standard Configuration” and click Next
Make sure that the following two are checked
Install as a Windows Service
Include the bin directory in the PATH
Then click Next
Type in the root password twice, and click Next. Use TGA_2010
for the password. Do NOT check enable root access or create
anonymous account
Click Execute
Click Finish
• Check Start > All Programs > MySQL > MySQL Server 5.0
Installation of MySQL Workbench
Go to http://www.mysql.com/downloads/workbench/#downloads
Make sure that Windows is selected for the platform and download
mysql-workbench-gpl-5.2.25-win32.msi
Double-click the downloaded file to install it






Allow the install
Click Next
Choose Complete and Next
Click Install
Allow the install
Uncheck the launch MySQL Workbench and click Finish
Appendix B.1 Installation of MySQL Tools
•
•
•
•
•
•
•
•
Go to http://www.mysql.com
Click on Downloads at the top
Scroll down to MySQL Tools
Click “MySQL GUI Tools”
Select “Pick a Mirror” for “Windows (x86)”
Click “No Thanks”
Download mysql-gui-tools-5.0-r12-win32.msi into c:\mysql
Double-click this file to install the Tools
Click Run
Click Next on the Welcome Screen
Accept the License and click Next
Click Next on the “Destination Folder” Screen
Appendix B.2 Installation of MySQL Tools
Select “Complete” and click Next
Click Install
Click Next on two Informational Screens
Click Finish
• Check Start > All Programs > MySQL
• Run the MySQL Administrator
Login as
localhost
root
password