Transcript Examples of using MySQL from the Unix Prompt

What is MySQL?
•MySQL is a relational database management system (A relational
database stores data in separate tables rather than putting all the data in one
big storeroom. This adds speed and flexibility. The tables are linked by
defined relations making it possible to combine data from several tables on
request. The SQL part of MySQL stands for "Structured Query Language"
- the most common standardized language used to access databases)
•MySQL is Open Source Software (Open Source means that it is possible
for anyone to use and modify. Anybody can download MySQL from the
Internet and use it without paying anything. Anybody so inclined can study
the source code and change it to fit their needs)
•The official way to pronounce MySQL is ``My Ess Que Ell''
To use MySQL, you will have had to created a MySQL server by making use of the Guava web
application (https://www.wiu.edu/guava/)
Once you are logged in, under the Servers menu option, select the Install New Server.
Once your MySQL server is created, an e-mail will be sent to your ECOM mail account telling
you your MySQL user name, password, and what port you should use to connect.
(NOTE!!! Save that e-mail!!!)
One way to work with your MySQL server is via a command line interface from
your Unix account on ECOM. (Later on, we will learn how to work with it via a
web interface..)
To get to the command interface, log in to your Unix account via SSH, then run
this command from the Unix prompt:
mysql -h mysql.wiu.edu -P [the port you were assigned] -u root –p
You’ll be prompted for your password (the one you got e-mailed, not your Unix
account password!!!)
You should wind up at the mysql prompt (mysql> )
From the command line there are several MySQL commands we can use as well as
issuing SQL statements.
Above, I used the “show databases” command to see what databases I have
available.
To create a new database we can use the SQL CREATE statement. Above I
created a new database, “class_sched_db”
To work with a database (run queries, etc) we first need to “change” to it (tell the
DBMS which database we want to work with)
Above I used this command to change to the class_sched_db database:
use class_sched_db
Above I used the “show tables” command to get a list of tables in the
class_sched_db database. Notice how it returns an empty set.
I haven’t created any tables yet!!!!!
Above, I used the CREATE statement to create a new table, the “faculty” table.
Once I created it, I used the show tables command again and now I get a result back.
Using the “describe” command, I can see the information related to the
“faculty” table
Above, I use the INSERT statement to put some values into my new
table.
Now, I run a simple SELECT against my table and see the contents.
Above is a sample of using a text file of commands (in this case a file full of
INSERT statements) to do a task. The example above, populated all of my
tables with test data.
USE class_sched_db;
INSERT INTO faculty VALUES('2143', 'Birkin');
INSERT INTO faculty VALUES('3487', 'Berndt');
INSERT INTO faculty VALUES('4756', 'Collins');
Above is an example of the commands we can
place in a text file for execution...
Above is an example of “deleting” a row from a table.
Once we’re done with our work, we leave the MySQL interface by
typing “quit”.