Transcript Details

Working with MySQL
Creating tables
• We know how to create a database in
MySQL – an empty database
• Next step is to add tables to the database
• After adding tables, we can enter data into
the tables
• Remember; design your tables BEFORE
using MySQL to enter them 
SWC – 2011
2
Creating tables
Before using a
database, we
must connect to it
Right-click on
database, and
choose ”Connect”
SWC – 2011
3
Creating tables
Enter whatever we
chose when creating the database
SWC – 2011
4
Creating tables
Now we can access
the tables in the
database – but there
are none yet
SWC – 2011
5
Creating tables
Right-click on ”Tables”,
and choose ”Create
table”
SWC – 2011
6
Creating tables
SWC – 2011
7
Creating tables
• Recall what we need in order to define a
table
– A proper name for the table
– A set of field definitions – a name and a type
for each field
– A key field – one or more fields the
combination of which is always unique
SWC – 2011
8
Creating tables
Enter a proper name
for the table
SWC – 2011
9
Creating tables
• For each data field (also called column),
we must add quite a lot of information:
– Column name: Name of the data field
– Key: Is the field part of the key field
– Index: Leave as-is…
– Null: Can we omit the value
– Unique: Must the value be unique
– Data type: Proper type of data
– Size: How large can the data be
SWC – 2011
10
Creating tables
• Example: a Name field
– Column name: Name
– Key: Yes
– Null: No
– Unique: (decided by choosing ”Key”)
– Data type: VARCHAR (means ”String”)
– Size: 50 (how long can a name be…?)
SWC – 2011
11
Creating tables
• Example: a Phone field
– Column name: Phone
– Key: No
– Null: No
– Unique: No
– Data type: INT (number)
SWC – 2011
12
Creating tables
SWC – 2011
13
Creating tables
SWC – 2011
14
Creating tables
A database
called School
A table called
student
Five data
fields
Red indicates
a key field
SWC – 2011
15
Creating tables
We can add more
data fields later
Right-click on the
table, choose ”Add
Column…”
SWC – 2011
16
Creating tables
Exercise
–
–
–
–
Create a database called WebShop in MySQL
Create three tables in WebShop, called Item, Customer, Sale
Definitions of the tables are given below (key fields are in red)
Remember to choose proper data types!
Item
ItemNumber
ItemName
Price
Weight
NumberInStock
IsFragile
Description
Customer
CustomerNumber
Name
Address
ZipCode
E-mail
ClubMember
LastSale
SWC – 2011
Sale
TransactionNumber
ItemNumber
Date
NumberOfItems
TotalAmount
SentToCustomer
PaymentReceived
17
Adding data
With the table definition in place, we can
now begin to add
data to the table
Right-click on the
table, choose ”View
Data…”
SWC – 2011
18
Adding data
Data in the
table is
shown here
(none yet….)
SWC – 2011
19
Adding data
Press this
icon to add
new data
SWC – 2011
20
Adding data
Type in data for
each record
SWC – 2011
21
Adding data
SWC – 2011
22
Adding data
Table now contains 3 records
SWC – 2011
23
Adding data
And so on,
and so on…
SWC – 2011
24
Adding data
• What can go wrong…?
• We might try to type in
some data, that does
not match the type
specification for a
particular field
• MySQL will complain…
SWC – 2011
25
Adding data
What is wrong
here…?
SWC – 2011
26
Adding data
SWC – 2011
27
Adding data
• The error messages can be more or less
easy to understand…
• It is almost always a matter of trying to
enter data of the wrong type
• Might also be data of incorrect size
• Some errors are more subtle, like a wrong
date (30-02-1988)
SWC – 2011
28
Adding data
What is this…?
An SQL query!
SWC – 2011
29
Adding data
Exercise
– Add a few records to the tables created in the earlier exercise.
Try to experiment with the data, such that you also try to add
some data with errors. Observe the errors messages that
MySQL returns when data has errors
– Do you now feel comfortable working with MySQL, with regards
to creating databases, tables and fields, and entering data into
tables? If not, then try to invent some more tables, enter them
into a database, and enter some data into the tables
SWC – 2011
30