working with mysql. swc 2011 2 creating tables we know how to create a database in mysql an empty...
DESCRIPTION
SWC – Creating tables Before using a database, we must connect to it Right-click on database, and choose ”Connect”TRANSCRIPT
Working with MySQL
SWC – 2011 2
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 3
Creating tables
Before using a database, we must connect to it
Right-click on database, and choose ”Connect”
SWC – 2011 4
Creating tables
Enter whatever we chose when crea-ting the database
SWC – 2011 5
Creating tables
Now we can access the tables in the database – but there are none yet
SWC – 2011 6
Creating tables
Right-click on ”Tables”, and choose ”Create table”
SWC – 2011 7
Creating tables
SWC – 2011 8
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 9
Creating tables
Enter a proper name for the table
SWC – 2011 10
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 11
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 12
Creating tables• Example: a Phone field
– Column name: Phone– Key: No– Null: No– Unique: No– Data type: INT (number)
SWC – 2011 13
Creating tables
SWC – 2011 14
Creating tables
SWC – 2011 15
Creating tables
A database called School
A table called student
Five data fields
Red indicates a key field
SWC – 2011 16
Creating tables
We can add more data fields later
Right-click on the table, choose ”Add Column…”
SWC – 2011 17
Creating tablesExercise
– 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
Sale TransactionNumber ItemNumber Date NumberOfItems TotalAmount SentToCustomer PaymentReceived
SWC – 2011 18
Adding data
With the table defini-tion in place, we can now begin to add data to the table
Right-click on the table, choose ”View Data…”
SWC – 2011 19
Adding data
Data in the table is shown here (none yet….)
SWC – 2011 20
Adding data
Press this icon to add new data
SWC – 2011 21
Adding data
Type in data for each record
SWC – 2011 22
Adding data
SWC – 2011 23
Adding data
Table now con-tains 3 records
SWC – 2011 24
Adding data
And so on, and so on…
SWC – 2011 25
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 26
Adding data
What is wrong here…?
SWC – 2011 27
Adding data
SWC – 2011 28
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 29
Adding data
What is this…?An SQL query!
SWC – 2011 30
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