MySQL for this Class

• You have access to a MySQL DBMS
  • It is installed on a linux macine CSCI313
  • You log into this machine with your regular credentials.
  • From outside, you must log into cslab100, cslab101 first.
• I will store files in my account
  • ~dbennett/313
  • ch2 contains the files needed to create the codd tables for this chapter.
  • ls ~dbennett/313/ch2
  • EDIT THE SCRIPTS AND CHANGE dbennett TO YOUR NAME
  • cp ~dbennett/313/ch2/* .
• You MySQL account
  • Your username should be the same as your system user name.
  • Your password should be mmddyyyy of your birthday.
• Accessing the database
  • mysql is a command line tool to access the database.
  • For interactive use:
    • mysql -u username -p
  • To execute a "script"
    • mysql -u username -p < scriptname
  • There are many more options to the mysql interface
    • We will probably discuss some of them.
    • You should read about it
      • man mysql
      • The MySQL.com pages
  • There are other interface options, but we will not explore them this semester
    • phpMyAdmin is a nice web based gui
    • MySQL Workbench is the MySQL tool.
• Kicking the tires
  • At this point, you will be working at a command line prompt.
  • mysql >
  • you just type commands in SQL
  • and occasionally some mysql (interface) commands.
  • A note on case:
    • It does not matter with generic sql reserved words
    • SELECT or select or SeLeCT are the same.
    • It matters with database names.
    • it matters with table names.
    • It sort of matters with field (column) names.
  • All SQL commands are terminated by a semicolon (;)
    • show databses;
    • select 3+5;
    • show create table dbennett.INVENTORY;
    • select CURDATE();
    • select * from mysql.user;
  • mysql (interface) commands are not
    • help
    • pager less
    • nopager
    • source filename (must give full path to filename)
    • use database
  • The command line in mysql (interface) has editable history
  • And you can ask it to d neat things
    • pager less
    • select * from mysql.user \G
• Must do: Change your password
  • set password =PASSWORD("text of password");
  • The reference page.
• You have been given a single database.
  • This is common in many free or low cost on line server providers
  • We will just build multiple tables with different purposes.
  • The first set of tables I named to match the exercises (I think)
• So to get started
  • Log in
  • Change your password
  • use username;
  • show tables;
  • exit;
  • Copy the files from my directory to your directory
  • Edit and change dbennett to your name
  • mysql -u yourname -p < codd1.sql
  • mysql -u yourname -p < codd2.sql
  • log in again, use the right database
  • show tables;
  • select * from INVENTORY;