Transcript slides

MySQL-Database
Teppo Räisänen
Oulu University of Applied Sciences
School of Business and Information Management
Relational Database Management
System (RDBMS)
• Tools for:
– Creating database
– Managing database
– Updating information
– ...
• Widely used RDBMS products
– Oracle, DB2, Microsoft SQL Server, MySQL,...
MySQL
• Originally developed by Swedish MySQL AB
using Open source
• Owned by Sun Microsystems nowadays
• Homepage http://www.mysql.com
• Newest version 5.x
Managing (local) database server
• Start Apache and MySQL
• Press Admin (MySql) to
open PHPMyAdmin
• Select language that you prefer
• Create new database
Exercise 1.
1. Open phpMyAdmin to manage local
database server
2. Choose language that you prefer
3. Create new Database called Demox
Demox-database
Create tables
• Use graphical tool or SQL-statements
Defining fields with graphical tool
Exercise 2.
• Create table ProductGroup by using graphical
tool
CREATE TABLE - example
Example:
CREATE TABLE Class (
ID INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(10) UNIQUE NOT NULL
) TYPE=INNODB;
Syntax:
CREATE TABLE tablename (
field1 datatype additional attributes,
field2 datatype additional attributes,
) TYPE=INNODB;
Creating tables with SQL
• SQL-statements are NOT case-sensitive in
MySQL (e. g. CREATE == create)
• There are many different syntax for doing
things in SQL
• Naming tables and fields also varies
CREATE TABLE – data types
• Numbers
– TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT, FLOAT,
DOUBLE
• Dates and time
– DATE, TIME, DATETIME, TIMESTAMP, YEAR
• Strings and character
– VARCHAR, CHAR, TEXT, BLOB, ENUM
• Boolean
– TINYINT (0=false, 1=true), with new MySQL version BOOL
and BOOLEAN is also available
CREATE TABLE – additional attributes
•
•
•
•
•
PRIMARY KEY
AUTO_INCREMENT
UNSIGNED
NOT NULL
UNIQUE
Storage Engines in MySQL
• InnoDB – Foreign key constraints can be used
• MyISAM – Extremely fast, no foreign key
constraints
• Blackhole – For testing
• ...
Example: defining different
datatypes and attributes
create table Person(
PersonId smallint unsigned auto_increment primary key,
Name varchar(50) not null,
Email varchar(100) unique not null,
Saved timestamp default current_timestamp
on update current_timestamp,
Gender enum(’Male’,’Female’)
) type=innodb;
Exercise 3.
• Create table Customer using SQL
Creating relationships
CREATE TABLE Student (
ID INT AUTO_INCREMENT PRIMARY KEY,
...
class_ID INT NOT NULL,
INDEX idx_class_ID (class_ID),
FOREIGN KEY (class_ID) REFERENCES Class(ID)
ON DELETE RESTRICT
)
Exercise 4.
• Create table product using SQL-statement so
that relationship between tables
productgroup and product is created
Exercise 5.
• Create rest of the database Demox (tables Order and
OrderRow)
• When creating table Order use syntax demox.Order
(databasename.table), because order is reserved word in SQL
(and therefore plain order won’t work).
Exercise 6.
• Create new database called guestbook
• Create table message with fields:
– MessageID (primary key)
– Message (text)
– User (varchar)
– Saved (timestamp)