Monday, December 5, 2016

My experience with creating database in MySQL

I'm using Mac OS X Lion v10.7 and writing MySQL script to generate tables for my assignment project, Library Management.

Creating table

create table member(
 id varchar(16) not null,
 first_name varchar(128) not null,
 last_name varchar(128) not null,
 sex enum('M', 'F') not null,
 date_created timestamp not null,
 date_updated timestamp not null,
 primary key (id)
) engine=innodb default charset=utf8;

create table book (
 id bigint not null,
 ddc varchar(45) not null,
 title varchar(255) not null,
 author varchar(128) not null,
 year_published smallint not null,
 primary key (id)
) engine=innodb default charset=utf8;

create table borrow (
 member_id varchar(16) not null,
 book_id bigint not null,
 date_borrowed timestamp not null,
 date_returned timestamp,
 primary key (member_id, book_id, date_borrowed),
 foreign key (member_id) references member(id),
 foreign key (book_id) references book(id)
) engine=innodb default charset=utf8;

- There are a set of integer data types: tinyint(-128 to 127), smallint(-32768 to 32767), mediumint(-8388608 to 8388607)int(-2147483648 to 2147483647), and bigint.
- The option "default charset=utf8" allows me to store characters in many languages such as Spanish.
- From MySQL v5.7 and up, innodb is the default storage engine. It can handle concurrent transactions while myisam cannot.




No comments:

Post a Comment