MySQL-Database

Teppo RäisänenOulu 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 prefer3. 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

CREATE TABLE Class (ID INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(10) UNIQUE NOT NULL

) TYPE=INNODB;

CREATE TABLE tablename (field1 datatype additional attributes,field2 datatype additional attributes,

) TYPE=INNODB;

Example:

Syntax:

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