My SQLDatabase Jouni Juntunen Oulu University of Applied

My. SQL-Database Jouni Juntunen 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, DB 2, Microsoft SQL Server, My. SQL, . . .

My. SQL • Originally developed by Swedish My. SQL 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 My. SQL • Press Admin (My. Sql) to open PHPMy. Admin • Select language that you prefer • Create new database

Exercise 1. 1. Open php. My. Admin 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 Product. Group 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 ( field 1 datatype additional attributes, field 2 datatype additional attributes, ) TYPE=INNODB;

Creating tables with SQL • SQL-statements are NOT case-sensitive in My. SQL (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, BIGINT, FLOAT, DOUBLE • Dates and time – DATE, TIME, DATETIME, TIMESTAMP, YEAR • Strings and character – VARCHAR, TEXT, BLOB, ENUM • Boolean – TINYINT (0=false, 1=true), with new My. SQL version BOOL and BOOLEAN is also available

CREATE TABLE – additional attributes • • • PRIMARY KEY AUTO_INCREMENT UNSIGNED NOT NULL UNIQUE

Storage Engines in My. SQL • Inno. DB – Foreign key constraints can be used • My. ISAM – Extremely fast, no foreign key constraints • Blackhole – For testing • . . .

Example: defining different datatypes and attributes create table Person( Person. Id 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 Order. Row) • 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: – Message. ID (primary key) – Message (text) – User (varchar) – Saved (timestamp)