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 documentation for a tutorial and Chapter 6 for an SQL reference.

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

/usr/local/bin/mysql -p -h eagle jbs_db

Translation: execute the mysql script, indicating that you will be prompted for your password, connecting to host eagle, using the current login as the user name, and using the jbs_db database.

Substitute you database name for jbs_db; it is your logon, as used for your members directory,  plus "_db".  You may be able to abbreviate /usr/local/bin/mysql to simply mysql, depending on your path.

1. Change your password

SET PASSWORD=PASSWORD("new_password");

Your initial password is "secret".  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 jbs_db;

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 -- jbs_db, 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 INT AUTO_INCREMENT PRIMARY KEY,
NameFirst VARCHAR(64),
NameMiddlle VARCHAR(64),
NameLast VARCHAR(64),
Address VARCHAR(64),
City VARCHAR(64),
State VARCHAR(64),
Zip VARCHAR(64),
PhoneWork VARCHAR(64),
PhoneHome VARCHAR(64),
PhoneFax VARCHAR(64),
Email VARCHAR(64),
URL VARCHAR(128),
Notes TEXT
);

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. Delete a row from your table

DELETE FROM Person WHERE PersonID = 2;

10. Logout

exit;