SQL

Getting Started in MySQL

You first have to have a database created for you on hercules:

1. Go to the following web page: https://www.cs.uregina.ca/
2. Click on the MySQL link
3. Log-on using your Computer Science UserID and password
4. Read the Licence Agreement and accept it
5. Choose a password for your database (something different from your CS password)

.....Wait 15 minutes before trying to access your database

Running MySQL at the Command Prompt

On Hercules

• mysql -p username
• help
• all commands in sql end with a semi-colon (;)

Note: must log in hercules, if not change to hercules with following command

• sd1[1]% ssh   hercules [ENTER]

  
  

On Windows

• mysql -u root -p
• type password provided by instructor
• show databases;
• create database myname;
• use myname;

For more on MySQL, go to dev.mysql.com .

Creating and Dropping Tables

• CREATE TABLE Person
  (
     FirstName varchar(15),
     LastName varchar(15),
     ID int 
  );

• for a description of the types see: http://www.w3schools.com/php/php_mysql_create.asp
• show tables;
• describe Person;
• drop table Person;

Inserting and Removing Records from a Table

• INSERT INTO Person (FirstName, LastName, ID)
   VALUES ('Peter', 'Griffin', '568975871');

• delete from Person
  where ID='568975871'
• delete Person;
• A quick way of creating and inserting data is to store the commands in a file. The following creates four tables (code taken from Java How to Program by Deitel and Deitel):
  source books.sql;

Displaying the Contents of a Table (through Queries)

The following is by no means complete. It is meant to get you started with SQL queries.

• To display all fields and records in Person:
  SELECT * FROM Person;
• To display two columns and all the records in Person:
  SELECT FirstName, LastName from Person;
• To display all fields but only those people whose last name is Jones:
  SELECT * FROM Person
  WHERE LastName='Jones';
• To display only those people whose ID is greater than 568975871
  SELECT * FROM Person
  WHERE ID>568975871;
• To display all fields and records in ascending (ASC) order based on LastName (descending order is DESC):
  SELECT * FROM Person
  ORDER by LastName ASC;
• To display only those people whose ID is greater than 568975871, and less than 800000000
  SELECT * FROM Person
  WHERE ID>568975871 AND ID<800000000;
  (You can also use OR for multiple comparisons)