SQL with MySQL

The MySQL Database System^[1,2]

"MySQL is a free, efficient, widely used database system that implements SQL. It is available for all popular computing platforms." For example, windows, Linux, etc.

Getting Started in MySQL

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

Go to the following web page: https://www.cs.uregina.ca/
Click on the MySQL link
Log-on using your Computer Science UserID and password
Read the Licence Agreement and accept it
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.

Structured Query Language (SQL)^[2,4]

What is SQL^[5]?

"SQL (pronounced "ess-que-el") stands for Structured Query Language. SQL is used to communicate with a database. According to ANSI (American National Standards Institute), it is the standard language for relational database management systems. SQL statements are used to perform tasks such as update data on a database, or retrieve data from a database. Some common relational database management systems that use SQL are: Oracle, Sybase, Microsoft SQL Server, Access, Ingres, etc. Although most database systems use SQL, most of them also have their own additional proprietary extensions that are usually only used on their system. However, the standard SQL commands such as "Select", "Insert", "Update", "Delete", "Create", and "Drop" can be used to accomplish almost everything that one needs to do with a database. This tutorial will provide you with the instruction on the basics of each of these commands as well as allow you to put them to practice using the SQL Interpreter." -- from WWW.SQLCOURSES.COM

SQL DML and DDL^[4,6]

SQL can be divided into two parts: The Data Manipulation Language (DML) and the Data Definition Language (DDL). DML commands in SQL can be used for querying and updating data. DDL commands create or delete a table in database.

DML	DDL
SELECT - extracts data from a database	CREATE DATABASE - creates a new database
UPDATE - updates data in a database	ALTER DATABASE - modifies a database
DELETE - deletes data from a database	CREATE TABLE - creates a new table
INSERT INTO - inserts new data into a database	ALTER TABLE - modifies a table
	DROP TABLE - deletes a table
	CREATE INDEX - creates an index (search key)
	DROP INDEX - deletes an index

Creating and Dropping Tables

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

for a description of the types see: MySQL Data Types (W3Schools.com)
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)

References

MySQL Reference Manuals -- MySQL Official Site
International Organization for Standardization: "ISO/IEC 9075-14:2008" -- ISO SQL 2008
Normal Forms -- William Kent, A Simple Guide to Five Normal Forms in Relational Database Theory, Communications of the ACM, Vol. 26, No. 2, 1983
W3Schools SQL Introduction -- learning SQL Language
SQL Courses
MySQL Tutorial