Web Applications Web Applications DevelopmentDevelopment

Lecture 1Lecture 1

My SQLMy SQL

Robin Robin BoswellBoswell

Contents

• What is MySQL?– Comparison with Access

• Running MySQL in the labs• SQL

– Data Definition language• Creating tables• Data types

– Data manipulation language• Select, Insert, …

– This is mostly revision of CM2020

MySQL• A popular OpenSource SQL Database

management system• > 10 million installations• Developed and supported by MySQL AB

– www.mysql.com

• Emphasis on fast query processing• Early versions lacked essential features

– Views, procedural code, support for relational integrity

– These are all present in version 5.0 onwards

Comparison with Access

Access• GUI: QBE, Simple table

creation, “drag & drop” forms,…

• Windows only

• Non-standard SQL

• Not particularly fast

MySQL• Command line interface

• Available on Windows, Linux, Macintosh…

• ANSI SQL

• Fast

Getting started: connecting to the server

mysql –u 0123435 –h scomp-wasp –D012345 -p

UserId DatabaseHostname

Initial commands

• show database; # list available databases• use databasename;

– E.g. use 0123456;• show tables;• exit

SQL

• Data definition language– Creating tables

• Data manipulation language– Reading, writing and updating tables

Data Definition commands

Creating Tables

CREATE TABLE pet (name VARCHAR(20),owner VARCHAR(20),species VARCHAR(20),sex CHAR(1),birth DATE,death DATE);

Data TypesSee chapter 10 of the manual for more details on data types

Strings

• CHAR(N), VARCHAR(N)– CHARs are padded to length N– VARCHARs are variable length N

• BLOB– Large binary files, e.g. images

• TEXT(N)– TINYTEXT TEXT MEDIUMTEXT LONGTEXT– Long text strings, e.g. text typed by user into box

Another String type: Enum

CREATE TABLE Driver (

Title ENUM (‘Mr’, ‘Mrs’, ‘Ms’),DriverID CHAR(6),Name VARCHAR(20),Points INT

);

Data Types

DATE, DATETIMECREATE TABLE Driver (

Title ENUM (‘Mr’, ‘Mrs’, ‘Ms’),DriverID CHAR(6),DateOfBirth DATE, -- YYYY-MM-DD, e.g. ‘1959-07-04’Name VARCHAR(20),

);

CREATE TABLE Order (OrderID CHAR(8),ProductID CHAR(8)Number INTDate DATETIME -- YYYY-MM-DD HH-MM-SS

-- e.g. ‘2007-09-30 09-30-15’);

Data Types

Data TypesNumeric• INT

– Integers• FLOAT, DOUBLE

– Floating point numbers– N.B. These are approximate values

• DECIMAL(P, S) # Precision, Scale– Exact values– Example:

Suppose cost 5000, e.g., cost = 3289.75 Appropriate data-type for cost is:

cost DECIMAL(6, 2)

Constraints

Table definitions can include Constraints

Constraints implement Data Integrity

Recall: • Data Integrity ensures data is correct

Column Constraints

Primary keys can be implemented as column constraints

CREATE TABLE Driver (

Title ENUM (‘Mr’, ‘Mrs’, ‘Ms’),DriverID CHAR(6) PRIMARY KEY,Name VARCHAR(20)

);

MySQL implements primary key integrity

Table constraints

CREATE TABLE Driver (

Title ENUM (‘Mr’, ‘Mrs’, ‘Ms’),DriverID CHAR(6),CONSTRAINT pkdriv PRIMARY KEY DriverID);

CREATE TABLE Grades (StudentID CHAR(10),ModuleID CHAR(7),Grade CHAR(1),CONSTRAINT pkgrade PRIMARY KEY (StudentID, ModuleID)

);

A composite primary key must be declared as a table constraint, not as part of a column definition.

Foreign Keys

Staff

StaffCode StaffName Dept*S001 Taylor D003S002 Gibbon D003S003 Russell D002

Department

DCode DNameD001 ClassicsD002 PhilosophyD003 HistoryD004 Economics

Declaring Foreign Keys

CREATE TABLE Staff (Staffcode CHAR(4),StaffName VARCHAR(20),Dept CHAR(4) REFERENCES Department(Deptcode)

);

CREATE TABLE Department (Deptcode CHAR(4),Deptname VARCHAR(20),CONSTRAINT dep_con1 PRIMARY KEY (Deptcode)

);

CREATE TABLE Staff2 (-- An alternative way of declaring a FKStaffcode CHAR(4),StaffName VARCHAR(20),Dept CHAR(4),FOREIGN KEY (Dept) REFERENCES Department

);

Optional, if it’s the primary key

Can be multiple valued, to match composite primary key

Referential Integrity

Staff

StaffCode StaffName Dept*S001 Taylor D003S002 Gibbon D003S003 Russell D002S004 Penman D005

Department

DCode DNameD001 ClassicsD002 PhilosophyD003 HistoryD004 Economics

Referential integrity means that for every value of the foreign key there must be a matching value in the table it links to.

Integrity constraints in MySQL

MySQL 5.0 implements primary key integrity and referential integrity on foreign keys.

MySQL 5.0 doesn’t implement any other forms of integrity checking

CREATE TABLE Driver (Title ENUM (‘Mr’, ‘Mrs’, ‘Ms’),DriverID CHAR(6) PRIMARY KEY,Name VARCHAR(20),Points INT check (Points < 8)

);This will be

ignored

Properties of FK links

Staff

StaffCode StaffName Dept*S1 Fred D1S2 Bill D1S3 Jim D2

Department

DCode DNameD1 ArtD2 ComputingD3 Business

What happens to the Staff table if the Art department is closed, or changes its DCode?

Link Properties: On Delete, On Update

DID Name D1 Art D2 Computing

DeptSID Name S1 Fred S2 Bill S3 Jim

Staff

DID*D1D1D2

On delete: Cascade

NULL

On delete: Set Default

On delete: Set Null

Link properties

DID Name D1 Art D2 Computing

DeptSID Name S1 Fred S2 Bill S3 Jim

Staff

DID*D1D1D2

On update: Cascade

NULL

On delete: Set Default

On update: Set Null

D42D42D42 D0x79fc

Link properties

Link Properties: On Delete, On Update

Setting link properties in SQL

CREATE TABLE Department (Deptcode CHAR(4),Deptname VARCHAR(20),CONSTRAINT dep_con1 PRIMARY KEY (Deptcode)

);

CREATE TABLE Staff (Staffcode CHAR(4),StaffName VARCHAR(20),Dept CHAR(4)

FOREIGN KEY (Dept) REFERENCES DepartmentON DELETE SET NULLON UPDATE SET NULL

);

Data Manipulation commands• SELECT• INSERT• APPEND• DELETE

27

The Select Command

SELECT <field list>FROM <table list>WHERE <condition>;

SELECT *FROM MarksWHERE Name = ‘Bloggs’ OR Name = ‘Donald’

Bloggs 50 67 90 62 Donald 46 50 89 67

Pick columns

Pick rows

Name RDB Java Business Intranet

Absolom 45 80 87 60Bloggs 50 67 90 62Carver 55 56 91 63 Donald 46 50 89 67

Marks

Pick tables

28

The Select Command

Name RDB Java Business Intranet

Absolom 45 80 87 60Bloggs 50 67 90 62Carver 55 56 91 63Donald 46 50 89 67

Marks

SELECT Name, JavaFROM Marks;

Absolom 80Bloggs 67 Carver 56 Donald 50

SELECT <field list>FROM <table list>WHERE <condition>;

Pick columns

Pick rows

Pick tables

29

The Select Command

SELECT Name, JavaFROM MarksWHERE Name = ‘Carver’;

Carver 56

Name RDB Java Business Intranet

Absolom 45 80 87 60Bloggs 50 67 90 62Carver 55 56 91 63Donald 46 50 89 67

Marks

SELECT <field list>FROM <table list>WHERE <condition>;

Pick columns

Pick rows

Pick tables

Regular Expressions – Pattern Matching

SELECT *FROM DriverWHERE PostCode like ‘AB%’

selects drivers whose post-code starts “AB…”

% represents any number of characters_ represents exactly one character

Use the operators LIKE or REGEXP in the WHERE field of SELECT

LIKE is standard SQL (See manual section 3.3.4.7)REGEXP is an extra feature provided by My SQL (11.4.1)

LIKE

Inserting, Modifying and Deleting Data

• Insert – Load a record at a time

• Load – Import a table from a file

• Update – Change the value of a field

• Delete - Delete one or more records

INSERT

CREATE TABLE Driver (

Title ENUM (‘Mr’, ‘Mrs’, ‘Ms’),DriverID CHAR(6) PRIMARY KEY,Name VARCHAR(20)

);

INSERT INTO DRIVER VALUES -- A complete row(‘Mr’, ‘D00123’, ‘Smith’);

INSERT INTO DRIVER (DriverID, Name) VALUES-- Specified values(‘D00124’, ‘Jones’);

LOAD

CREATE TABLE Driver (

DriverID CHAR(6) PRIMARY KEY,

Title ENUM (‘Mr’, ‘Mrs’, ‘Ms’),Name VARCHAR(20)

);

LOAD DATA LOCAL INFILE ‘driver.txt’ -- Tab-separated fieldsINTO TABLE Driver ;

File stored on client

Load is similar to the “import table” feature in Access

See section 12.2.5 for more details

D00001 Mr BoggisD00002 Mrs BoggisD00003 Mr ErnieD00004 Ms Tracy

Update

UPDATE TABLESET …WHERE…

Example-- From now on , all male drivers will be-- called BoggisUPDATE Driver

SET Name = ‘Boggis’WHERE Title = ‘Mr’;

Delete

DELETE FROM TABLEWHERE…

Example

-- Delete all male drivers

DELETE FROM DriverWHERE Title = ‘Mr’;

Selecting from multiple tables

38

Cartesian Product in SQL

MC1 Roast BeefMC2 Roast LambMC3 Chicken Tikka

P1 Ice CreamP2 Apple Crumble

Main Pudding

SELECT *FROM Main, Pudding;

MC1 Roast Beef P1 Ice CreamMC1 Roast Beef P2 Apple CrumbleMC1 Roast Lamb P1 Ice CreamMC2 Roast Lamb P2 Apple CrumbleMC3 Chicken Tikka P1 Ice CreamMC3 Chicken Tikka P2 Apple Crumble

39

Inner Join in My SQL

ID p_nameP1 AramisP2 AthenaP3 Oracle

ProjID s_name Prj#*S1 Jones P1S2 Carey P1S3 Fuller P2S4 Mack P2

Staff

SELECT *FROM Staff, ProjWHERE Staff.Prj# =

Staff.ID s_name Staff.Prj# Proj.ID p_name S1 Jones P1 P1Aramis S2 Carey P1 P1 Aramis S3 Fuller P2 P2 Athena S4 Mack P2 P2 Athena

Proj.ID;

SELECT *FROM Staff INNER JOIN ProjON Staff.Prj# = Proj.ID;

These two statements are equivalent

40

Natural Join in SQL

ID p_nameP1 AramisP2 AthenaP3 Oracle

ProjID s_name Prj#*S1 Jones P1S2 Carey P1S3 Fuller P2S4 Mack P2

Staff

SELECT Staff.ID, s_name, Staff.Prj#, p_nameFROM Staff, ProjWHERE Staff.Prj# = Proj.ID

Staff.ID s_name Staff.Prj# p_name S1 Jones P1 Aramis S2 Carey P1 Aramis S3 Fuller P2 Athena S4 Mack P2 Athena

41

Outer Joins

ID p_nameP1 AramisP2 AthenaP3 Oracle

Proj

ID s_name Prj#*S1 Jones P1S2 Carey P1S3 Fuller P2S4 Mack P2

Staff

Inner and Natural joins return only those rows which match on a given field

Outer Join also returns rows from one table which have no match in the other table;

SELECT * FROM Staff RIGHT JOIN Proj ON Staff.Prj# = Proj.ID;

Staff.ID s_name Staff.Prj# Proj.ID p_name S1 Jones P1 P1Aramis S2 Carey P1 P1 Aramis S3 Fuller P2 P2 Athena S4 Mack P2 P2 Athena NULL NULL NULL P3 Oracle

Other features of the Select command

Ordering and Grouping

43

OrderingLoan table:

Loan# catno Memno LoanDate DueDate Fine

L0002 B0001 M0001 05/10/97 04/12/97 £62.10

L0003 B0002 M0001 05/12/97 05/03/98 £53.00

L0004 B0003 M0001 05/12/97 05/03/98 £53.00

L0006 B0004 M0002 13/12/97 13/03/98 £52.20

L0008 B0000 M0002 16/01/98 16/04/98 £48.80

L0009 B0005 M0003 18/08/99 18/11/99 £75.00

SELECT Loan#, catno FROM LoanORDER BY catno;

Loan# Catno

L0008 B0000

L0002 B0001

L0003 B0002

L0004 B0003

L0006 B0004

L0009 B0005

44

Ordering on > 1 fieldLoan table:

Loan# catno Memno LoanDate DueDate Fine

L0002 B0001 M0001 05/10/97 04/12/97 £62.10

L0003 B0002 M0001 05/12/97 05/03/98 £53.00

L0004 B0003 M0001 05/12/97 05/03/98 £53.00

L0006 B0004 M0002 13/12/97 13/03/98 £52.20

L0008 B0000 M0002 16/01/98 16/04/98 £48.80

L0009 B0005 M0003 18/08/99 18/11/99 £75.00

SELECT Memno, Fine FROM LoanORDER BY Memno, Fine;

Memno Fine

M0001 £53.00

M0001 £53.00

M0001 £62.10

M0002 £48.80

M0002 £52.20

M0003 £75.00

SELECT Memno, Fine FROM LoanORDER BY Memno, Fine DESC;

Memno Fine

M0001 £62.10

M0001 £53.00

M0001 £53.00

M0002 £52.20

M0002 £48.80

M0003 £75.00

Aggregate Operators

• COUNT counts records

• SUM adds values

• AVG calculates average value

46

Grouping

SELECT memno, COUNT(*) AS num_loans FROM Loan;

Loan# Book# Memno L0002 B0001 M0001

L0003 B0002 M0001

L0004 B0003 M0001

L0006 B0004 M0002

L0008 B0000 M0002

How many loans does each member have?

Memno num_loans M0001 5 M0001 5 M0001 5 M0002 5 M0002 5

47

Grouping

SELECT memno, COUNT(*) AS num_loans FROM Loan

Loan# Book# Memno L0002 B0001 M0001

L0003 B0002 M0001

L0004 B0003 M0001

L0006 B0004 M0002

L0008 B0000 M0002

Memno num_loans M0001 3 M0002 2

One entry in results table for each different value of memno

Aggregates are evaluated separately for each group

How many loans does each member have?

GROUP BY memno;

More Grouping

memno catno fine

M0001 B0002 £53.00

M0001 B0003 £53.00

M0002 B0004 £52.20

M0003 B0005 £75.00

SELECT memno, SUM(fine) AS total_fine FROM LoanGROUP BY memno ;

memno total_fineM0001 £106.00M0002 £52.20M0003 £75.00

What is the total fine paid by each member?

SQL Summary

• Data definition language– Creating tables– Setting data types– Defining constraints

• Data manipulation language– Reading, writing and updating records in

tables

For more details, see the MySQL manual