STEP 1: Using Remote Desktop logon to mondego.calit2.ics.uci.edu STEP 2: At command line, go to the following directory C:\teaching\inf102\sql> STEP 3: Run mysql commandline client tool, enter password = 123456 ---------------------- OUTPUT ------------------------ C:\teaching\inf102\sql> mysql -u crista inf102db -p Enter password: ****** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 560 to server version: 4.1.14-nt Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> ------------------------------------------------------ STEP 4: restart the environment to beginning point type: 'source 0-initialize.sql' ---------------------- OUTPUT ------------------------ mysql>source 0-initialize.sql Database changed ERROR 1051 (42S02): Unknown table 'customers' ERROR 1051 (42S02): Unknown table 'products' ERROR 1051 (42S02): Unknown table 'orders' ERROR 1051 (42S02): Unknown table 'orderdetails' Logging to file 'output.txt' mysql> ------------------------------------------------------ STEP 5: Shows how to create simple tables - type: 'source 1-tblCustomers.sql' ---------------------- OUTPUT ------------------------ mysql> source 1-tblCustomers.sql; Query OK, 0 rows affected (0.05 sec) ------------------------------------------------------ STEP 6: make sure that table has been created NOTES: - THIS STEP CAN BE SKIPPED - OUTPUT IS LOGGED IN 'output.txt' - type: 'show tables'; ---------------------- OUTPUT ------------------------ mysql> show tables; +--------------------+ | Tables_in_inf102db | +--------------------+ | customers | +--------------------+ 1 row in set (0.00 sec) ------------------------------------------------------ STEP 7: make sure that the table has been created NOTES: - THIS STEP CAN BE SKIPPED - OUTPUT IS LOGGED IN 'output.txt' - type: 'show columns from Customers;' ---------------------- OUTPUT ------------------------ mysql> show columns from Customers; +-------------+-----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+-----------+------+-----+---------+-------+ | CompanyName | char(100) | YES | | NULL | | | ContactName | char(100) | YES | | NULL | | | Address | char(200) | YES | | NULL | | | City | char(50) | YES | | NULL | | +-------------+-----------+------+-----+---------+-------+ 4 rows in set (0.00 sec) ------------------------------------------------------ STEP 8: Shows how to fill tables with data using INSERT sql statements ---------------------- OUTPUT ------------------------ mysql> source 2-datCustomers.sql Query OK, 1 row affected (0.01 sec) Query OK, 1 row affected (0.00 sec) Query OK, 1 row affected (0.01 sec) Query OK, 1 row affected (0.02 sec) ------------------------------------------------------ STEP 9: Shows how to write simple SELECT queries to retrieve data from a table NOTES: - THIS STEP CAN BE SKIPPED - OUTPUT IS LOGGED IN 'output.txt' - open 3-qryCustomers.sql, explains the queries to them - run all query by typing : 'source 3-qryCustomers.sql' - open "output.txt" to show the query results ---------------------- OUTPUT ------------------------ mysql> source 3-qryCustomers.sql; +----------------------------+--------------------+-------------------------+-------------+ | CompanyName | ContactName | Address | City | +----------------------------+--------------------+-------------------------+-------------+ | Alfreds Futterkiste | Maria Anders | Obere Str. 57 | Berlin | | Berglunds snabbköp | Christina Berglund | Berguvsvägen 8 | Luleå | | Centro comercial Moctezuma | Francisco Chang | Sierras de Granada 9993 | México D.F. | | Ernst Handel | Roland Mendel | Kirchgasse 6 | Graz | +----------------------------+--------------------+-------------------------+-------------+ 4 rows in set (0.00 sec) +----------------------------+--------------------+ | CompanyName | ContactName | +----------------------------+--------------------+ | Berglunds snabbköp | Christina Berglund | | Centro comercial Moctezuma | Francisco Chang | | Alfreds Futterkiste | Maria Anders | | Ernst Handel | Roland Mendel | +----------------------------+--------------------+ 4 rows in set (0.00 sec) +--------------------+ | ContactName | +--------------------+ | Christina Berglund | +--------------------+ 1 row in set (0.01 sec) ------------------------------------------------------ STEP 10: Shows how to alter the schema of a table. In this example, a primary key CustomerId is added into table Customers. This value of this collumn is managed by mySQL. Notes: the value of a primary key (e.g., CustomerId) is unique in the table. ---------------------- OUTPUT ------------------------ mysql> source 4-altCustomers.sql Query OK, 4 rows affected (0.09 sec) Records: 4 Duplicates: 0 Warnings: 0 ------------------------------------------------------ STEP 11: Shows how to write more complicate SELECT statement that retrieve information from more than one table - open '5-tblProducts.sql', explain the Products schema to the students - type: 'source 5-tblProducts.sql' ---------------------- OUTPUT ------------------------ mysql> source 5-tblProducts.sql Query OK, 0 rows affected (0.03 sec) Query OK, 1 row affected (0.02 sec) Query OK, 1 row affected (0.00 sec) Query OK, 1 row affected (0.01 sec) Query OK, 1 row affected (0.02 sec) ------------------------------------------------------ - open '6-tblOrders.sql', explain the Orders and OrderDetails schema to the students - type: '6-tblOrders.sql' ---------------------- OUTPUT ------------------------ mysql> source 6-tblOrders.sql Query OK, 0 rows affected (0.05 sec) Query OK, 0 rows affected (0.03 sec) Query OK, 1 row affected (0.02 sec) Query OK, 1 row affected (0.02 sec) Query OK, 1 row affected (0.00 sec) ------------------------------------------------------ - open '7-qryOrders.sql', explain the queries to the student - type: '7-qryOrders.sql' ---------------------- OUTPUT ------------------------ mysql> source 7-qryOrders.sql +---------+--------------+---------------------+-----------+ | OrderId | ContactName | CompanyName | TotalCost | +---------+--------------+---------------------+-----------+ | 1 | Maria Anders | Alfreds Futterkiste | 3800 | +---------+--------------+---------------------+-----------+ 1 row in set (0.00 sec) +-------------+-------+---------+ | ProductName | Price | Quatity | +-------------+-------+---------+ | HP Desktop | 1000 | 2 | | Sony Laptop | 1800 | 1 | +-------------+-------+---------+ 2 rows in set (0.00 sec) ------------------------------------------------------ STEP 12: exit and finish - type: 'exit' ---------------------- OUTPUT ------------------------ mysql> exit Bye C:\teaching\inf102\sql> ------------------------------------------------------