CSC 455

Fall 2009

Lab 13 MySql  Part 1 Getting Started

  1. Using a terminal client such as PuTTY or SSH, log onto: otherone.cis.uncw.edu with your normal uncw email username and password. Note that nothing will display as you type your password – keep typing and press Enter.
  2.   If this is the first time you have logged in to otherone, a mysql database and user will be generated, and a password will be mailed to you. Check your UNCW e-mail to find that password.
  3. Start the mySql client as: mysql -u your_username –p
  4. Enter your your database password. Each student has a separate database account, so your database will not conflict with other student databases.

5.    Strings can be specified using either single or double quotes. Each sql statement is terminated by a semi-colon.

  1. You can change your mySql password with the following command:

set password = password(“new_password”);

      Note that your otherone password is a separate password from your mySql password.  You may set them to match if you choose.

  1. The database you will use has the same name as your userid. Connect to your database by typing use userid;  
  2. In this lab, you will create the company database that has been used in the text and class examples. 
  3. Use the create table command to create the DEPARTMENT table as follows:

 

create table DEPARTMENT (

  Dname        varchar(25) not null,

  Dnumber      integer(4),

  Mgrssn       char(9),

  Mgrstartdate date,

  primary key (Dnumber),

  key (Dname)) TYPE = INNODB;

  1. Check  your work with the following commands: show tables; and then describe DEPARTMENT;

11. Then use the insert into command to add one row of data into it:

 

insert into DEPARTMENT values ("Research", 5, "333445555", "1978-05-22");

  1. You can check that by typing:

select * from DEPARTMENT;

  1. Quit mySql by typing \q or exit;

 

  1. Obviously, there is a lot of typing involved, and if you make a mistake, you have to redo it all.  Another way to execute SQL commands is to create a file in an editor.  Then you invoke mysql using the file you created.  Create a file called company1.sql using Notepad locally or a text editor on otherone.  It should contain the SQL commands to select your database, set up the EMPLOYEE table, and populate it with two records:  (Note that this file can contain multiple SQL commands as long as they are separated by ; )

use yourdatabase; 

 

create table EMPLOYEE (

  Fname    varchar(15) not null,

  Minit    varchar(1),

  Lname    varchar(15) not null,

  Ssn      char(9),

  Bdate    date,

  Address  varchar(50),

  Sex      char,

  Salary   decimal(10,2),

  Super_ssn char(9),

  Dno      integer(4),

  primary key (Ssn),

  foreign key (Super_ssn) references EMPLOYEE(Ssn),

  foreign key (Dno) references DEPARTMENT(Dnumber)

) type = innodb;

 

insert into EMPLOYEE values

("Evan","E","Wallis","222222200","1958-01-16","134 Pelham, Milwaukee, WI","M","92000.00",null,"5"),

("Josh","U","Zell","222222201","1954-05-22","266 McGrady, Milwaukee, WI","M","56000.00","222222200","5");

  1.  Save this file locally as company1.sql by changing the Save as type to All Files (This will get rid of the default .txt extension.) 
  2. Use an FTP client to upload the .sql file to otherone 
  3. Invoke mysql using the file you created as follows $ mysql -u your_username -p < company1.sql   This method is the preferred way for working in MySQL at the command level because editing is much easier.
  4. If you need to make changes to the file, you also need to re-upload it, if it is stored locally.
  5. Start mysql again, select your database to use, and execute the show tables; command again and then describe EMPLOYEE;
  6. Quit mySql by typing \q or exit and log out from otherone.
  7. This will be continued next time….