How to use the database server babbage.cs.abo.fi A MySQL database server for those attending this year's course is installed on the server babbage.cs.abo.fi. All the users are allowed to see all the databases there. A user has all priviledges to those databases that are called username_database. If your username is xxxx, you can create, change and drop (delete) databases calles xxxx_bank, xxxx_jobs, xxxx_courses etc. This is how you work with the system: You log on to babbage.cs.abo.fi using your normal userID and Unix-password (if you have not defined a separate password for unix, it is the same as your Windows password). You can use a communication program such as Putty och ssh Secure Shell, or just sit at one of the computers in the Linux-class (the penguin class) and use a terminal window. You get one by clicking the right mouse button and choosing Konsole. Or you can click the F-icon (lower left corner), the choose Applications, then Utilities, then Terminal. When you have this terminal window, you must use Linux-commands. You type the commands and push “enter”. Mouse-clicks do not work here. This is how you connect to babbage: ssh [email protected] Inside Åbo Akademi, using our network, it is enought to write ssh babbage From home or elsewhere, you must access babbage from tuxedo.abo.fi. First log onto tuxedo (normal userID, normal password), then write ssh [email protected] The computer now asks your password, and you give it the same Windows password. You know that you are logged onto babbage when the system prompt looks like this: [soini@float ~]$ ssh babbage Here I connect to babbage from my own machine called float. soini@babbage's password: This is not shown. The cursor does not move. This is how it should be! Just write it! Last login: Tue Feb 22 17:28:32 2011 from float.cs.abo.fi /usr/local/bin/settings: No such file or directory. Never mind this! [soini@babbage ~]$ Here you can see that you are on babbage. The system prompt is the dollar sign $. When you are on babbage, but not yet running the mysql system, you can e g copy files to your directory. To copy the file jobdb3.sql that you need for exercise 4, write the following: $ wget http://users.abo.fi/soini/jobdb3.sql Note: You must copy these files outside the mysql-system. You cannot do it there! To get other files from the Swedish course page, you could write e g $ wget http://users.abo.fi/mats/databaser2012/databases/university.sql $ wget http://users.abo.fi/mats/databaser2012/databases/university_data_small.sql Now you can start the mysql-system: mysql -u xxxx -p Replace xxxx above with your own userID, and now give the password that Mats mailed to you or I gave you. You must write it and push 'enter'; cut and paste won't do! This is how I start mysql (my userID is soini): [soini@babbage ~]$ mysql -u soini -p Enter password: Here I write my password - it does not show! Welcome to the MySQL monitor. Commands end with ; or \g. This is important! When you have written the whole query, finish with ';' and push enter. Your MySQL connection id is 106 Server version: 5.1.54 MySQL Community Server (GPL) by Remi Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.This software comes with ABSOLUTELY NO WARRANTY. This is free software,and you are welcome to modify and redistribute it under the GPL v2 license Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> This is the mysql prompt. After this you can write your query. If the query is long, you can push 'enter' at any time to get a new line. The query is finished when you write ';' and push 'enter'. Here are some examples: mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | ... | sakila | This database you may play with. | soini_banken | These are my databases. | soini_courses | | world | This database you may play with. +--------------------+ 68 rows in set (0.00 sec) You can create a new database by writing the command create database xxxx_university; This you only need to do once for each database. You choose to use the database by writing use xxxx_university; database. This you must do whenever you wish to use this You populate (fill in) the database with schemas (university.sql) and data (university_data_small.sql) by writing source university.sql; source university_data_small.sql; This you only need to do once; then the information stays there if you do not delete it or drop the database. The first file has the structure for your database, the second has the contents. This is how you write a query: select * from course; This is how you write a longer query; the '->' prompt is written by the system to let you know that you can keep on writing. This prompt is shown until you write a ';' and push 'enter': When you want to use your database next time, simply write mysql> use soini_university; 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> select * push enter -> from course natural join teaches push enter -> where ID = 10101 and push enter -> semester = 'Fall'; push enter You can drop (delete) your database if you wish - then it is no longer available: drop database xxxx_university; Other useful commands are • show databases; shows you all the databases by all the users • show tables; shows you which tables there are in the database you use • describe table_name; to see all the attributes and their descriptions in the table called table_name To end your session, write exit; or quit; Don't forget to log out of babbage! And the computer you are currently working on! GOOD LUCK!