Web Applications Development Lecture 1 My SQL

1
Web Applications DevelopmentLecture 1My SQL
Robin Boswell
2
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

3
MySQL

• A popular OpenSource SQL Database management
  system
• gt 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

4
Comparison with Access

• MySQL
• Command line interface
• Available on Windows, Linux, Macintosh
• ANSI SQL
• Fast

• Access
• GUI QBE, Simple table creation, drag drop
  forms,
• Windows only
• Non-standard SQL
• Not particularly fast

5
Getting started connecting to the server

• mysql u 0123435 h scomp-wasp D012345 -p

Hostname
UserId
Database
6
(No Transcript)
7
Initial commands

• show database list available databases
• use databasename
• E.g. use 0123456
• show tables
• exit

8
SQL

• Data definition language
• Creating tables
• Data manipulation language
• Reading, writing and updating tables

9
Data Definition commands
10
Creating Tables
CREATE TABLE pet ( name VARCHAR(20), owner V
ARCHAR(20), species VARCHAR(20), sex CHAR(1),
birth DATE, death DATE)
11
Data Types

• See 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

12
Data Types

• Another String type Enum
• CREATE TABLE Driver (
• Title ENUM (Mr, Mrs, Ms),DriverID CHAR(6
  ),Name VARCHAR(20),Points INT
• )

13
Data Types

• DATE, DATETIME
• CREATE 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 INT
• Date DATETIME -- YYYY-MM-DD HH-MM-SS
• -- e.g. 2007-09-30 09-30-15
• )

14
Data Types

• Numeric
• 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)

15
Constraints

• Table definitions can include Constraints
• Constraints implement Data Integrity
• Recall
• Data Integrity ensures data is correct

16
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

17
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.
18
Foreign Keys
19
Declaring Foreign Keys
CREATE TABLE Department ( Deptcode
CHAR(4), Deptname VARCHAR(20), CONSTRAINT
dep_con1 PRIMARY KEY (Deptcode))
Optional, if its the primary key
CREATE TABLE Staff ( Staffcode
CHAR(4), StaffName VARCHAR(20), Dept CHAR(4)
REFERENCES Department(Deptcode))
CREATE TABLE Staff2 (-- An alternative way of
declaring a FK Staffcode CHAR(4), StaffName VAR
CHAR(20), Dept CHAR(4), FOREIGN KEY (Dept)
REFERENCES Department )
Can be multiple valued, to match composite
primary key
20
Referential Integrity
Staff StaffCode StaffName DeptS001 Taylor D003
S002 Gibbon D003S003 Russell D002S004 Penm
an D005
Referential integrity means that for every value
of the foreign key there must be a matching
value in the table it links to.
21
Integrity constraints in MySQL

• MySQL 5.0 implements primary key integrity and
  referential integrity on foreign keys.
• MySQL 5.0 doesnt 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 lt 8)
• )

This will be ignored
22
Properties of FK links
Staff StaffCode StaffName DeptS1 Fred D1S2 B
ill D1S3 Jim D2
Department DCode DNameD1 ArtD2 ComputingD3 Busi
ness
What happens to the Staff table if the Art
department is closed, or changes its DCode?
23
Link Properties On Delete, On Update
NULL
Link properties
On delete Cascade
On delete Set Null
On delete Set Default
24
Link Properties On Delete, On Update
Dept
DID Name D1 Art
D2 Computing
D42D42
D42
D0x79fc
NULL
Link properties
On update Cascade
On update Set Null
On delete Set Default
25
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 Department ON
DELETE SET NULL ON UPDATE SET NULL )
26
Data Manipulation commands

• SELECT
• INSERT
• APPEND
• DELETE

27
The Select Command
SELECT ltfield listgtFROM lttable listgtWHERE
ltconditiongt
Marks
Name RDB Java Business
Intranet Absolom 45 80 87
60Bloggs 50 67 90
62Carver 55 56 91
63 Donald 46 50 89
67
SELECT FROM Marks WHERE Name Bloggs OR
Name Donald
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
SELECT Name, Java FROM Marks
29
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
SELECT Name, Java FROM Marks WHERE Name
Carver
30
Regular Expressions Pattern Matching
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
represents any number of characters_
represents exactly one character
SELECT FROM DriverWHERE PostCode like AB
selects drivers whose post-code starts AB
31
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

32
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)

33
LOAD
Load is similar to the import table feature in
Access See section 12.2.5 for more details

• 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
34
Update

• UPDATE TABLESET WHERE
• Example
• -- From now on , all male drivers will be
• -- called Boggis
• UPDATE DriverSET Name BoggisWHERE Title
  Mr

35
Delete

• DELETE FROM TABLEWHERE
• Example
• -- Delete all male drivers
• DELETE FROM DriverWHERE Title Mr

36
Selecting from multiple tables
37
Cartesian Product in SQL
Main
Pudding
MC1 Roast BeefMC2 Roast LambMC3 Chicken
Tikka
P1 Ice CreamP2 Apple Crumble
SELECT FROM Main, Pudding
38
Cartesian Product in SQL
Main
Pudding
MC1 Roast BeefMC2 Roast LambMC3 Chicken
Tikka
P1 Ice CreamP2 Apple Crumble
SELECT FROM Main, Pudding
39
Inner Join in My SQL
These two statements are equivalent
SELECT FROM Staff, Proj WHERE Staff.Prj
SELECT FROM Staff INNER JOIN ProjON Staff.Prj
Proj.ID
Proj.ID
40
Natural Join in SQL
SELECT Staff.ID, s_name, Staff.Prj, p_name FROM
Staff, Proj WHERE Staff.Prj Proj.ID
41
Outer Joins
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
42
Other features of the Select command

• Ordering and Grouping

43
Ordering
Loan table Loan catno Memno LoanDate
DueDate Fine L0002 B0001 M0001 05/10/97 04/12/9
7 62.10 L0003 B0002 M0001 05/12/97 05/03/98 53.
00 L0004 B0003 M0001 05/12/97 05/03/98 53.00 L0
006 B0004 M0002 13/12/97 13/03/98 52.20 L0008 B0
000 M0002 16/01/98 16/04/98 48.80 L0009 B0005 M0
003 18/08/99 18/11/99 75.00
Loan Catno L0008 B0000 L0002 B0001 L0003
B0002 L0004 B0003 L0006 B0004 L0009 B0005
SELECT Loan, catno FROM LoanORDER BY catno
44
Ordering on gt 1 field
Loan table Loan catno Memno LoanDate
DueDate Fine L0002 B0001 M0001 05/10/97 04/12/9
7 62.10 L0003 B0002 M0001 05/12/97 05/03/98 53.
00 L0004 B0003 M0001 05/12/97 05/03/98 53.00 L0
006 B0004 M0002 13/12/97 13/03/98 52.20 L0008 B0
000 M0002 16/01/98 16/04/98 48.80 L0009 B0005 M0
003 18/08/99 18/11/99 75.00
Memno Fine M0001 53.00 M0001 53.00 M000
1 62.10 M0002 48.80 M0002 52.20 M0003 75
.00
SELECT Memno, Fine FROM LoanORDER BY Memno,
Fine
Memno Fine M0001 62.10 M0001 53.00 M000
1 53.00 M0002 52.20 M0002 48.80 M0003 75
.00
SELECT Memno, Fine FROM LoanORDER BY Memno,
Fine DESC
45
Aggregate Operators

• COUNT counts records
• SUM adds values
• AVG calculates average value

46
Grouping
How many loans does each member have?
SELECT memno, COUNT() AS num_loans FROM Loan
47
Grouping

• 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?
SELECT memno, COUNT() AS num_loans FROM Loan
GROUP BY memno
One entry in results table for each different
value of memno Aggregates are evaluated
separately for each group
48
More Grouping
What is the total fine paid by each member?
SELECT memno, SUM(fine) AS total_fine FROM
Loan GROUP BY memno
49
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