MySQL Basics

MySQL is said to be the most popular of the open source DBMS.  Its main advantages are that it is free and it is fast.  It is distributed by the company, MySQL AB, and their web site has lots of information about the product including technical documentation.  See especially Chapter 3 of their Reference Manual for a tutorial and Chapter 6 for an SQL reference.  You will also find documentation for their Administrative Console and Query Browser.

The comments below summarize some of the more common commands you will need to logon, create a table, and insert records, and query the table.  After that, instructions for connecting from a Java program will be given.

---

0. Logon

mysql -p -u jbs -h tomcat5 

Translation: execute the mysql script, indicating that you will be prompted for your password, connecting to host eagle, as the user name jbs.  Your password is initially set to a default value that has been emailed to you.

1. Change your password

SET PASSWORD=PASSWORD("new_password");

Your initial password is to a default value that has been emailed to you.  You should change it to something else.

2. Show your current logon

SELECT USER();

Confirm that you are logged on as the user you think you are.

3. Use a particular database

USE jbsdb;

If you did not specify the database when you logged on or somehow got out of your defined database, you can move your working context to the appropriate database -- jbsdb, in this case -- using the above command.

4. Show your current tables

SHOW TABLES;

See your current tables.

5. Create a simple table

CREATE TABLE Person
(
personID VARCHAR(16)PRIMARY KEY,
owner VARCHAR(16),
nameFirst VARCHAR(64),
nameMiddle VARCHAR(64),
nameLast VARCHAR(64),
address1 VARCHAR(128),
address2 VARCHAR(128),
city VARCHAR(64),
state VARCHAR(32),
zip VARCHAR(16),
phoneWork VARCHAR(64),
phoneHome VARCHAR(64),
phoneFax VARCHAR(64),
phoneCell VARCHAR(64),
email VARCHAR(64),
url VARCHAR(128)
);

This requires some SQL.  Read an SQL tutorial or text for basics.  Above is a "cookbook" example.

6. Insert some data into your table

INSERT INTO Person ( nameFirst, nameLast, city ) 
VALUES ( "john", "smith", "carrboro" );

Pay attention to upper and lower case for names and values.

7. See the data in your table

SELECT * FROM Person;

8. Select a single  row from your table

SELECT * FROM Person WHERE PersonID = 2;

9. Select one or more rows from your table, using "wildcards"

SELECT * FROM Person WHERE PersonID LIKE '%2%';

10. Update a column value in a table row

UPDATE  Person  SET NameMiddle = 'Joe' WHERE PersonID  = '2';

 11. Delete a row from your table

DELETE FROM Person WHERE PersonID = 2;

12. Delete the table

DROP TABLE Person;

13. Logout

exit;