MCSR Seminar for
The University of Mississippi
UM CIS 490: Web Programming


"Beginning MySQL on Willow" Objectives:

1. Understand a little of the general role of database management systems and administrators in IT departments
2. Connect (and authenticate) to a MySQL Database from a Unix Command Line
3. Use the SQL SHOW statement to see a list of available databases
4. Use the SQL USE statement to use specific MySQL database
5. Use the SQL SHOW statement to list the tables in a specific MySQL database
6. Use the SQL SELECT statement to list selected columns from the rows in a MySQL table
7. Use the SQL WHERE clause to select rows conditionally from a MySQL table
8. Use the SQL Aggregate Functions SUM() and AVERAGE() to summarize MySQL table data
9. Use the SQL AND and OR operators to join several conditions in a MySQL where clause
10. Use the SQL SELECT statement to query multiple tables at once


Background of MySQL:
• Database Management Systems (DBMS) are software systems that facilitate the storage of data in standardized, optimized formats, and manage concurrent access to that data by multiple applications.
  
• Types of DBMS include relation (RDBMS) and object-oriented (OODBMS).
  
• Examples of RDBMS include Oracle, Sybase, Informix, DB2, SQL Server, and MySQL.
  
• SQL is the industry standard language for querying, updating, and manipulating RDBMS data and structures.
  
• SQL may be used by programmers and database administrators to interact directly with a database, through a command-line or graphical user interface, OR maybe be imbedded into programming language applications, to allow those applications to query or update data on behalf of an end-user or business application.
• Database administration is sometimes done by application developers (or systems administrators) in small IT shops, but in most mid-sized to large IT organizations, DB administration is the specialized responsibility of job families of database administrators, who determine the database organization, construct the table and index structures, optimize the structures for performance, and monitor these databases in production. These DBAs interface more with systems administrators and applications development teams, but less with end-users.
• MySQL is free, and available to download and install on a number of different environment: (Linux, Unix, Mac OS, Windows).
  
• MySQL lags behind the major commercial RDBMS implementations in feature richness, and is less optimized, but is a good platform for learning basic RDBMS development techniques.
  
Connect (and authenticate) to a MySQL Database from a Unix Command Line

$ /usr/local/bin/mysql -u USERNAME-p
Enter password:


Use the SQL SHOW statement to see a list of available databases

mysql> show databases;
+-----------+
| Database |
+-----------+
| cis490_db |
| instruct |
| test |
+-----------+
3 rows in set (0.13 sec)

mysql>


Use the SQL USE statement to use specific MySQL database

mysql> use instruct;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql>


Use the SQL SHOW statement to list the tables in a specific MySQL database

mysql> show tables;
+--------------------+
| Tables_in_instruct |
+--------------------+
| dept |
| emp |
| salgrade |
+--------------------+
3 rows in set (0.00 sec)

mysql>


Use the SQL DESCRIBE statement to view the structure of a MySQL table

mysql> describe dept;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| deptno | int(2) | | PRI | 0 | |
| dname | varchar(50) | | | | |
| location | varchar(50) | | | | |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.02 sec)

mysql> describe emp;
+----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| empno | int(4) | | PRI | 0 | |
| ename | varchar(50) | | | | |
| job | varchar(50) | | | | |
| mgr | int(4) | YES | | NULL | |
| hiredate | date | YES | | NULL | |
| sal | decimal(10,2) | YES | | NULL | |
| comm | decimal(10,2) | YES | | NULL | |
| deptno | int(2) | | | 0 | |
+----------+---------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

mysql> describe salgrade;



Use the SQL SELECT statement to list selected columns from the rows in a MySQL table

mysql> select * from dept;
+--------+------------+----------+
| deptno | dname | location |
+--------+------------+----------+
| 10 | Accounting | New York |
| 20 | Research | Dallas |
| 30 | Sales | Chicago |
| 40 | Operations | Boston |
+--------+------------+----------+
4 rows in set (0.05 sec)

mysql>


mysql> select ename, deptno, sal from emp;
+--------+--------+---------+
| ename | deptno | sal |
+--------+--------+---------+
| SMITH | 20 | 800.00 |
| ALLEN | 30 | 1600.00 |
| WARD | 30 | 1250.00 |
| JONES | 20 | 2975.00 |
| BLAKE | 30 | 2850.00 |
| CLARK | 10 | 2450.00 |
| SCOTT | 20 | 3000.00 |
| KING | 10 | 5000.00 |
| TURNER | 30 | 1500.00 |
| ADAMS | 20 | 1100.00 |
| JAMES | 30 | 950.00 |
| MILLER | 10 | 1300.00 |
| FORD | 20 | 3000.00 |
| MARTIN | 30 | 1250.00 |
+--------+--------+---------+
14 rows in set (0.00 sec)

mysql>

Use the SQL WHERE clause to select rows conditionally from a MySQL table

mysql> select ename, deptno, sal from emp
-> where deptno = 10;
+--------+--------+---------+
| ename | deptno | sal |
+--------+--------+---------+
| CLARK | 10 | 2450.00 |
| KING | 10 | 5000.00 |
| MILLER | 10 | 1300.00 |
+--------+--------+---------+
3 rows in set (0.00 sec)



Use the SQL Aggregate Functions SUM() and AVG() to summarize MySQL table data

mysql> select SUM(sal) from emp where deptno = 10;
+----------+
| SUM(sal) |
+----------+
| 8750.00 |
+----------+
1 row in set (0.00 sec)

mysql> select AVG(sal) from emp where deptno = 10;
+-------------+
| AVG(sal) |
+-------------+
| 2916.666667 |
+-------------+
1 row in set (0.00 sec)

mysql>



Use the SQL AND and OR operators to join several conditions in a MySQL where clause


mysql> select ename, deptno, sal
-> from emp
-> where deptno=10
-> OR deptno=20;
+--------+--------+---------+
| ename | deptno | sal |
+--------+--------+---------+
| SMITH | 20 | 800.00 |
| JONES | 20 | 2975.00 |
| CLARK | 10 | 2450.00 |
| SCOTT | 20 | 3000.00 |
| KING | 10 | 5000.00 |
| ADAMS | 20 | 1100.00 |
| MILLER | 10 | 1300.00 |
| FORD | 20 | 3000.00 |
+--------+--------+---------+
8 rows in set (0.03 sec)

mysql>

mysql> select ename, deptno, sal
-> from emp where deptno=10
-> AND sal>2500
-> ;
+-------+--------+---------+
| ename | deptno | sal |
+-------+--------+---------+
| KING | 10 | 5000.00 |
+-------+--------+---------+
1 row in set (0.01 sec)

mysql>



Use the SQL SELECT statement and the SQL operators to query multiple MySQL tables at once.


mysql> select ename, sal, dname
-> from emp, dept
-> where emp.deptno = dept.deptno
-> and sal>2500;
+-------+---------+------------+
| ename | sal | dname |
+-------+---------+------------+
| KING | 5000.00 | Accounting |
| JONES | 2975.00 | Research |
| SCOTT | 3000.00 | Research |
| FORD | 3000.00 | Research |
| BLAKE | 2850.00 | Sales |
+-------+---------+------------+
5 rows in set (0.00 sec)

mysql>

Basic Exercises:


1. How many tables are in the database cis490_db?
2. In the instruct database, how many fields are in the table salgrade?
3. In the instruct database, how many records (rows) are in the table salgrade?
4. Write and execute an SQL statement to show the highest commission amount received by any employee.
5. Employees working in how many locations (and which ones) received commissions?


Hints to Basic Exercises:


1. How many tables are in the database cis490_db? (USE and SHOW)
2. In the instruct database, how many fields are in the table salgrade? (DESCRIBE)
3. In the instruct database, how many records (rows) are in the table salgrade? (SELECT and COUNT)
4. Write and execute an SQL statement to show the highest commission abount received by any employee. (Use the MAX() aggregate SQL function.)
5. Employees working in how many locations (and which ones) received commissions? (join the emp and dept tables)


Answers to Basic Exercises:


1. How many tables are in the database cis490_db?
   use cis490_db;
show tables;

2. In the instruct database, how many fields of type int are in the table salgrade?
   use instruct;
describe salgrade;
3. In the instruct database, how many records (rows) are in the table salgrade?
   use instruct;
select * from rows;
OR
select count(*) from rows;
4. Write and execute an SQL statement to show the highest commission amount of the employee with the greatest commission. (Use the MAX() aggregate SQL function.) mysql> select MAX(comm) from emp;
   
5. Employees working in how many locations (and which ones) received commissions? (join the emp and dept tables)
   mysql> select ename, location, comm
-> from emp, dept
-> where comm > 0
-> and dept.deptno = emp.deptno;