Recap of Week 2

Your resume may get a little pep by documenting that you have experience with Oracle, BUT how much better will it be when you can put Oracle, PostGresql and MySQL development experience in there? A little mo' better.

This week we did some hand work using MySQL command line. It can be as much of a chore as any command line editing and programming. Thankfully we were able to may use of Notepad (and edit) to create text files and then import the code to SQL. Here are the steps we took to get it started:
  1. Take the extremely valuable CDRom and put into your electronic cup holder (cdrom drive).
  2. Load mysql-3.23.47-win.zip (or PHPTriad-which include the Apache web server and PHP scripting language)- run SETUP.EXE use default install options.
    NOTE: I also included WinZip if you can't view the .zip files. Install this first.
  3. Start the MySQL service.
    1. Open Explorer, go to c:\mysql\bin and then run winmysqladmin.exe
    2. Don't worry if the screen flashes by real fast, it just went to the systray (lower right corner of your screen where the clock sits)
    3. The First time you run it, put in a username and password (something you can remember)
    4. If you want to bring the program up again, right click over the stoplight in the systray then 'Show Me'
    5. MySQL is running! (you also could have started it through mysqld, but GUI is easy)
  4. Now let's start typing commands! Go to the Start Button then 'Run', type in command to bring up a dos screen
  5. in the DOS window type cd \ then press [Return]
  6. now cd \mysql\bin   You should see c:\mysql\bin at the prompt.
  7. type dir if you wish to see all of the programs there. We are only interested in mysql.exe at this time.
  8. Type mysql and press [Enter] or if you have an old keyboard [Return].
  9. Wow! You are in SQL!!
NOTE: When typing commands in MySQL, you cannot scroll through your history in Win98 like in Linux.
Good news is that this does work in WindowsNT4.0, Windows2000 and WindowsXP!

Ok, now what? How about we check out what is already there. Remember that a relational database is a collection of tables. At the MySQL prompt, type show databases;.(don't forget the semi-colon) You should see 2 databases already here. One is called test the other is MySQL. We don't want to touch anything in the MySql database that's already there, but we can take a quick peak at it.

There are a couple of ways to open a database in MySQL. One is to type mysql -ppassword database at the c:\mysql\bin prompt. The password is if you secured your MySQL database, and database is the name of the database (collection of tables) you want to open. Well, we already are out of the c:\ prompt and running in MySQL so let us use another method. Type connect mysql to open the database called mysql.

Now that you are within that database, type show tables;. If you are worried if what you are seeing is correct, then refer to the screen shot below.

What this means is that there are several tables within this database. Let us take a closer look at the anatomy of table called db. Type describe db;
Here is a summary of commands to date: 

C:\>cd \mysql\bin

C:\mysql\bin>mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 3.23.47-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> connect mysql
Connection id:    3
Current database: mysql

mysql> show databases
    -> ;
+----------+
| Database |
+----------+
| mysql    |
| test     |
+----------+
2 rows in set (0.00 sec)

mysql> show tables;
+-----------------+
| Tables_in_mysql |
+-----------------+
| columns_priv    |
| db              |
| func            |
| host            |
| tables_priv     |
| user            |
+-----------------+
6 rows in set (0.01 sec)

mysql> describe db;
+-----------------+-----------------+------+-----+---------+-------+
| Field           | Type            | Null | Key | Default | Extra |
+-----------------+-----------------+------+-----+---------+-------+
| Host            | char(60) binary |      | PRI |         |       |
| Db              | char(64) binary |      | PRI |         |       |
| User            | char(16) binary |      | PRI |         |       |
| Select_priv     | enum('N','Y')   |      |     | N       |       |
| Insert_priv     | enum('N','Y')   |      |     | N       |       |
| Update_priv     | enum('N','Y')   |      |     | N       |       |
| Delete_priv     | enum('N','Y')   |      |     | N       |       |
| Create_priv     | enum('N','Y')   |      |     | N       |       |
| Drop_priv       | enum('N','Y')   |      |     | N       |       |
| Grant_priv      | enum('N','Y')   |      |     | N       |       |
| References_priv | enum('N','Y')   |      |     | N       |       |
| Index_priv      | enum('N','Y')   |      |     | N       |       |
| Alter_priv      | enum('N','Y')   |      |     | N       |       |
+-----------------+-----------------+------+-----+---------+-------+
13 rows in set (0.75 sec)

mysql>

Now..Let's create or own database...
1. Type create database school; then exit mysql. This takes you back to C:\mysql\bin prompt 


mysql> create database school;
Query OK, 1 row affected (0.76 sec)

mysql> exit
Bye

C:\mysql\bin>

2. Now flip back to Windows and open Notepad. We will use this to type up a bunch of SQL commands and then pipe them to MySQL as a batch (not botched) job.

3. Type the following in Notepad:

Untitled-Notepad
File   Edit   Format   View   Help 
create table student (
sid INT(5),
lname varchar(20),
fname varchar (20),
major char(3)
);

File SaveAs c:\mysql\bin\stud.txt

Now back to that DOS window....

at the c:\mysql\bin prompt, type mysql school < stud.txt
It may look like nothing happened, but let us check that out. Now type mysql school to get into the school database. At the mysql> prompt, type show tables. It should look like the following (try a describe student as well). 


C:\mysql\bin>mysql school <stud.txt

C:\mysql\bin>mysql school
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7 to server version: 3.23.47-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show tables
    -> ;
+------------------+
| Tables_in_school |
+------------------+
| student          |
+------------------+
1 row in set (0.00 sec)

mysql> describe student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| sid   | int(5)      | YES  |     | NULL    |       |
| lname | varchar(20) | YES  |     | NULL    |       |
| fname | varchar(20) | YES  |     | NULL    |       |
| major | char(3)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql>

We need to put data into this table now. Normally functions like that should be done through a nice GUI or webform. But we love the command line, and so we will use that. Ok, we will use notepad again so we don't have to type every command line by line.

Untitled-Notepad
File   Edit   Format   View   Help 
INSERT INTO student VALUES
 (92221, 'Perry', 'Randall', 'med');
INSERT INTO student VALUES
 (92223, 'Miller', 'Amos', 'cis');
 INSERT INTO student VALUES
 (92224, 'Yoder', 'Levi', 'act');
Then save this file as c:\mysql\bin\values.sql
Go to mysql> prompt and type \. values.sql

In previous examples, we imported data by typing mysql student < stud.txt while at the c:\ prompt.

Command lines can be very unforgiving and tedious. But after some practice, it becomes very comfortable and quick. If you want to experience an excellent GUI frontend for managing MySQL databases, then try the MySQL graphical manager from EMS (It is on the CD I gave you). It is so friendly that you can even import data directly from MS Excel files! Try it out but don't get too comfortable there. You need to cut your teeth on the command line.

Don't forget to do your homework, dig through sourceforge and play around with Oracle or MySQL on your own PC. See you next week.