CSCU9B3 Practical 1 Page 1 CSCU9B3 Practical 1: Introduction to MySQL and phpMyAdmin Computing Science, University of Stirling In all the practicals a pen and paper will be very useful so that you can make notes as you go. Things you do in this session will be useful later, so all notes will have further use. Finding and Logging in to MySQL MySQL is a database management system (DBMS). Unlike MS Access (with which you might be familiar), most databases do not have an integrated user interface. They communicate with software at a level that is generally hidden from the user. However, there is usually software available to make accessing the database easier. In this practical we will learn about one such program, phpMyAdmin. phpMyAdmin is a browser-based interface to the MySQL database engine. It is written in the scripting language, PHP and allows you to manage a database via a web browser. Each student has a space on the database that is separate from all other students’ space. You can see the phpMyAdmin interface for your space in a web browser by opening up Chrome, IE (or another browser) and going to the web address: http://wamp0.cs.stir.ac.uk/phpmyadmin/ You will be asked to log in with your MySQL username and password: your username is either your normal STUDENTS user ID or your 3 letter CS username (if you have one), with the initial password set to the same. Once you have phpMyAdmin running, you should change your password to something more secure, via the General Settings tab. Select your database (the one called by your username; NOT information_schema) from the panel on the left. The phpMyAdmin interface now shows the tables in your database in a panel on the left of the screen and allows you to explore the currently selected table via a set of tabs on the right of the screen (though initially you will not have any tables): CSCU9B3 Practical 1 Page 2 In this first practical session, we will explore the MySQL interface, create some tables and enter some data. The data is from a simple company database containing two tables: Table: Employees Name Employee Number Date of Birth Salary Full Time (%) John Jones 234621 1972-07-04 45345.95 100 Sam Smith 374837 1970-08-23 44000.50 50 Table: Projects Project Name Manager Description Full Days Worked Thrust 234621 Build a rocket to the moon 100 Gold 374837 Turn lead into gold 200 Win 234621 Win Wimbledon 20 Create a Table You should see a box like this on the main page. Use this to create your first table. Call it Employees and enter the correct number of columns (also called fields). Click Go to move to the next step. Define the Columns You should see a new web page with a row for each column in your new table. This is where you tell the database about your columns. Today, we will only look at the first three attributes: the column name and its type and length. Appropriate names are given in the table in this document, above (Name, Employee Number … ). The data types might be new to you. Just as variables in a programming language like Java have types, so do columns in a database. You must pick the right type for each entry in your table. There are many choices in the drop down list, but we will just look at the following: VARCHAR INT DECIMAL DATE You can read about these types at either of the sites (the first, official site is very detailed): CSCU9B3 Practical 1 Page 3 https://dev.mysql.com/doc/refman/5.7/en/data-types.html https://www.w3schools.com/sql/sql_datatypes.asp Spend some time reading about each of the types above. Ask if you do not understand any of them. Once you have understood them, choose the right type for each column in your table. Note that Salary needs exactly 2 decimal places and has a maximum value of 99999.99. When you have chosen the types and their lengths (if necessary), click the Save button to create your table. You will likely find that lots of warnings / errors appear. Check if anything seems to relate to your columns, and if not, simply select “Ignore all” (some of the messages are warnings of things that we will worry about later on, but not just now). Now do the same to create the Projects table. Preparing to Enter or Change Data in a Table To edit a table, first you need to make one column of each table designated as “Unique”: 1. Select the table you want to edit e.g. Employees 2. Select the Structure tab 3. Click the checkbox for the “Employee number” and select “Unique” from the list underneath the column definitions. 4. Now if you “Browse” this table you will see that you can edit each row of data. You can do the same for Projects, perhaps choosing “Project Name” to be unique. If you ever need to delete a table and start again, first select that table in your database list and then go to the Operations tab, where you will find a “drop table” command. Entering Data Now you need to put some data into your tables. 1. Select the Employees table 2. Click on the Insert tab and use the form to enter the data given above 3. Try to enter data of the wrong type to see what happens 4. When the data is entered, you can view it in the Browse tab. Once you have the Employees data inserted, do the same for the Projects table. Make sure you have entered the data exactly as it is on this sheet, otherwise the questions later may not work properly. Searching the Tables Your tables are very small, but for larger tables, you would need to search for specific data rather than browsing. So let’s try some searching… CSCU9B3 Practical 1 Page 4 1. Select the table you want to search from the left panel – Employees in this case. 2. Click on the Search tab for the search page to bring up: 3. Every column in your table has an operator and a value for the search. 4. You’ll see there is a list of options for the operator in a search. Operators work differently depending on the data type. For example, you’ll see > and < for numeric data types, but not for character strings. 5. The value field is where you tell the database what you want to search for. 6. Try a few simple searches: a. Search for the details of Sam Smith b. Search for all the full time employees c. Search for all employees born after 1967-01-01 d. Search for all the employees with a salary over 40000. 7. You can also search for entries that are similar to a given search value using the LIKE operator. Use the ‘%’ as a wild card. For example Name LIKE % Smith will search for all the names ending in Smith. You try: a. Search for all the names ending in Smith b. Search for all the names with ‘Jon’ somewhere in their name. CHECKPOINT 1: demonstrate the final search above (step 7b) More Searching If you click “Options” on the Search page you find some extra ways of specifying and refining a search. You can: Choose which fields are shown in the search results (use Shift or Ctrl to select more than one when clicking on column names). Select only distinct rows (only one example of any given set of values) Specify how many results are shown per page Sort the results in ascending or descending order Add further clauses to the search term (this can be ignored for now). Now try the following searches: 1. List the projects that employee number 234621 is manager for. 2. Show just the name of all the projects with more than 150 full days worked. 3. List the employee numbers of any employee working on a project with fewer than 400 days worked. Only show each employee number once in the list. 4. List all the projects in order of time taken, with the longest running project first. CSCU9B3 Practical 1 Page 5 Next time… In the next practical you will write SQL statements directly to create and search tables, rather than using the “forms” interface provided by phpMyAdmin.
