CS 212 Software Development

CS 212-01, CS 212-02 • Fall 2019

SQL Intro: Getting Started

This demo will introduce various basic SQL concepts and statements via example. The tables we will be building in this demo are similar to:

userid name
1 Cathy
2 Alice
3 Emily
4 Billy
5 David
id area number description userid
1 555 111-1111 Work 1
2 555 222-2222 Cell 1
3 555 333-3333 Home 2
4 555 444-4444 Home 4
5 555 555-5555 Cell 5

The exact tables will depend on which point you are at in the demo script.

Database Accounts

Everyone has been assigned a database account on our lab MySQL/MariaDB database server.

The database accounts are in the form user## where ## is a number between 01 and 30. The database for that account is the same as the username.

See which database account you have been assigned on the Database Accounts Canvas page. Your initial password will be your USF username. Make sure to change your password after logging in for the first time.

Connecting to Database

First, log into one of the CS lab computers. See the Using CS Lab Computers guide for details.

Then, you will need to run the mysql command to connect to the SQL database on campus. The command looks like:

mysql -h sql.cs.usfca.edu -u user# -D user# -p

Here is what each part means:

  • The mysql part runs the MySQL command-line tool.

  • The -h sql.cs.usfca.edu specifies the hostname of our database server. You can actually type -h sql and the cs.usfca.edu part will be assumed since you are on the CS lab network.

  • The -u user# part will specify the database account username. This is different from your CS or USF usernames. See the Database Accounts page for your assigned database account and replace user# with your database username.

  • The -D user# part specifies the specific database to use. You only have one database for this class, and it has the same name as your database account username.

  • The -p (or --password) at the end will cause mysql to prompt you for a password once you hit Enter. Remember, you will not see the characters you type for your password for privacy reasons.

You will use this command a lot for this class, so you may want to create an alias as a shortcut.

Next: Creating Tables