Database setup using CS server

This is a short introduction to help you setup and test access to your database using UVA CS server. For more information on SQL commands, please refer to database textbook(s).
(Please tell me if you find any errors or omissions in the document —Upsorn Praphamontripong, 4-February-2024)

phpMyAdmin is a free and open-source software written in PHP intended to handle the administration of MySQL with the use of a web browser. It supports a wide range of operations such as creating, modifying or deleting databases, tables, fields or rows; executing SQL statements; or managing users and permissions. For more information, please refer to https://www.phpmyadmin.net/.


VPN requirement

If you are using the CS MySQL on Grounds, with UVA WiFi / network, you do not need VPN.

If you are using the CS MySQL off Grounds, with non-UVA WiFi / network, you need VPN. Please follow the instructions on the Information Technology Service's VPN page to install and connect to VPN.


Access phpMyAdmin

  1. Open a web browser, enter a URL    http://mysql01.cs.virginia.edu/phpmyadmin/   to access phpMyAdmin dashboard
  2. Enter your UVA computingID for a username. Password will be sent through Collab announcement and also distributed in class. Then, click the  Go  button to log in

    UVA CS phpMyAdmin log in page

    Once you have successfully connected to phpMyAdmin, you should see a screen similar to the following

    UVA CS phpMyAdmin dashboard


Change password

  1. On the  phpMyAdmin  screen, click the  Home icon, then click on  Change Password  link

    Screen to change password

  2. Enter your new password and click the  Go  button

    Screen to change password

Do NOT use your Netbadge or any official password


Create a database

Per the CS configuration, you are required to use your computingID as your database name. To create a database, there are several options.

You may use the  Create database  feature.
  • On the  phpMyAdmin  screen, select the  Databases  tab. Alternatively, you may click the  New  link on the left panel.
  • Under the  Create database,  enter your computingID for a Database name
  • Click the  Create  button

screen showing the create database screen of phpMyAdmin


You may run the SQL command to create a database.
  • On the  phpMyAdmin  screen, select the  SQL  tab
  • Enter  CREATE DATABASE your_computingID; 
    Note: SQL commands are not case sensitive. This example uses uppercase and lowercase simply to make it easy to read.
  • Click the  Go  button to run the command.
    • For Mac users, you may press  Control+Enter  to run
    • For Windows users, you may press  Control+Enter  to run

screen showing the SQL command to create a database


Create a table

Let's create a table named  entries.  To create a table, there are several options.

You may use the  Create table  feature.
  • On the  phpMyAdmin  screen, select your database.
  • Select the  Structure  tab.
  • Under the  Create table,  enter a table name and the number of columns.
  • Click the  Go  button. This will prompt you to enter the column information.

screen showing the create table screen of phpMyAdmin


You may run the SQL command to create a table.
  • On the  phpMyAdmin  screen, select the  SQL  tab
  • Enter the following code
    USE your_database_name; 
    CREATE TABLE entries (guestName VARCHAR(255), content VARCHAR(255), 
        entryID INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(entryID));

    screen showing the SQL command to create a table

    If you already selected the  your_computingID  database (on the left panel), no need to include USE your_database_name in your SQL to run.

  • Click the  Go  button to run the command.
    • For Mac users, you may press  Control+Enter  to run
    • For Windows users, you may press  Control+Enter  to run

Insert data

To insert data into a table, there are several options.

You may use the  Insert  feature.
  • On the  phpMyAdmin  screen, select the  your_computingID  database, select the  entries  table.
  • Select the  Insert  tab.
  • For each record of data to be inserted, enter the value for each column.
  • Click the  Go  button.

screen showing the insert data screen of phpMyAdmin


You may run the SQL command to insert data.
  • On the  phpMyAdmin  screen, select the  your_computingID  database, select the  entries  table.
  • Select the  SQL  tab
  • Enter the following code
    INSERT INTO entries (guestName, content) values ("Humpty", "Humpty's here!");
    INSERT INTO entries (guestName, content) values ("Dumpty", "Dumpty's here too!");

    screen showing the SQL command to insert data into a table

  • Click the  Go  button to run the command.
    • For Mac users, you may press  Control+Enter  to run
    • For Windows users, you may press  Control+Enter  to run

Retrieve data

To retrieve data from a table, there are several options.

You may use the  Browse  feature.
  • On the  phpMyAdmin  screen, select the  your_computingID  database, select the  entries  table.
  • Select the  Browse  tab. This will display all existing records of the table.

screen showing the Browse screen of phpMyAdmin


You may run the SQL command to retrieve data.
  • On the  phpMyAdmin  screen, select the  your_computingID  database, select the  entries  table.
  • Select the  SQL  tab
  • Enter the following code
    SELECT * FROM entries;

    screen showing the SQL command to retrieve data from a table

  • Click the  Go  button to run the command.
    • For Mac users, you may press  Control+Enter  to run
    • For Windows users, you may press  Control+Enter  to run

Import SQL file

  • Create a blank file named  friendbook.sql.  Paste the following content in the file
    CREATE TABLE friends
       (friendName VARCHAR(255),
        phone VARCHAR(255),
        entryID INT NOT NULL AUTO_INCREMENT,
        PRIMARY KEY(entryID));
    
    INSERT INTO friends (friendName, phone) values ("Humpty", "111-111-1111");
    INSERT INTO friends (friendName, phone) values ("Dumpty", "222-222-2222");  
  • On the  phpMyAdmin  screen, select the your_computingID database
  • Select the  Import  tab

    screen showing how to import SQL file

  • Choose the .sql file to import

    screen showing how to import SQL file

  • Click the  Go  button to run the command.
    • For Mac users, you may press  Control+Enter  to run
    • For Windows users, you may press  Control+Enter  to run

Export SQL file (back up your database)

  • On the  phpMyAdmin  screen, select the your_computingID database
  • Select the  Export  tab

    screen showing how to export SQL file

  • Click the  Go  button to run the command.

    screen showing how to export SQL file

    • For Mac users, you may press  Control+Enter  to run
    • For Windows users, you may press  Control+Enter  to run

Copyright © 2024 Upsorn Praphamontripong

Released under the Creative Commons License CC-BY-NC-SA 4.0 license.

Last updated 2024-02-04 7:58