MySQL

Create MySQL database and tables.

1. There are three ways to create databases and tables:
   
   • In batch mode, running a script.
   • Interactively by entering SQL commands.
   • Using a GUI tool, see Installing MySQL Control Center 0.8.9
   
   
   1. Batch Mode
      You can run mysql in batch mode to create databases and tables. Download an example batch script "createmobilefishdb.sql".
      
      To execute the script, enter:
      C:\..\mysql\bin>mysql -uroot -pmysecret < createmobilefishdb.sql
      
      
   2. Interactively
      If instead you want to create the database and tables interactively, do the following:
      
      Open command line MySQL monitor, type:
      C:\..\mysql\bin>mysql -uroot -pmysecret
      
      To create a database mobilefishdb, type:
      mysql> create database mobilefishdb;
      mysql> use mobilefishdb;
      
      To create USER table, type:
      mysql>create table user (
      -> userid int auto_increment,
      -> username varchar(255) not null,
      -> firstname varchar(255) not null,
      -> lastname varchar(255) not null,
      -> title varchar(255) null,
      -> gender enum('m', 'f') null,
      -> date_of_birth date null,
      -> password varchar(255) not null,
      -> email varchar(255) not null,
      -> phone varchar(255) null,
      -> primary key (userid)
      ->);
      
      
      
      
      
      To create ADDRESS table, type:
      mysql>create table address (
      -> addressid int auto_increment,
      -> userid int unsigned not null references user (userid),
      -> street varchar(255) not null,
      -> housenumber varchar(255) not null,
      -> zip varchar(255) not null,
      -> city varchar(255) not null,
      -> state varchar(255) not null,
      -> country varchar(255) not null,
      -> primary key (addressid)
      ->);
      
      Populate initial data in USER table (for user 1), type:
      mysql>insert into user (userid, username, firstname, lastname,
      ->title, gender, date_of_birth, password, email, phone)
      ->values (null, 'johndoe', 'john', 'doe', 'ing.', 'm', '1970-06-30',
      ->'secret', '[email protected]', '001-99999999');
      
      Populate initial data in ADDRESS table (for user 1), type:
      mysql>insert into address (addressid, userid, street, housenumber,
      ->zip, city, state, country)
      ->values (null, last_insert_id(), 'fishstreet', '12', '1111 aa',
      ->'amsterdam', 'noord-holland', 'netherland');
      
      Populate initial data in USER table (for user 2), type:
      mysql>insert into user (userid, username, firstname, lastname,
      ->title, gender, date_of_birth, password, email, phone)
      ->values (null, 'joesmo', 'joe', 'smo', null, null, '1970-10-09',
      ->'password', 'password', '[email protected]', '002-55555555');
      
      Populate initial data in ADDRESS table (for user 2), type:
      mysql>insert into address (addressid, userid, street, housenumber,
      ->zip, city, state, country)
      ->values (null, last_insert_id(), 'stationstreet', '78', '3333 zz',
      ->'den haag', 'zuid-holland', 'netherland');
      
      Exit command line MySQL monitor, type:
      quit
      
      
2. To display how the tables are defined, type:
   C:\..\mysql\bin>mysql -uroot -pmysecret
   mysql> use mobilefishdb
   mysql> desc user;
   mysql> desc address;
   mysql> quit
   
   
3. To show all tables within the database mobilefishdb, type:
   C:\..\mysql\bin>mysql -uroot -pmysecret
   mysql> use mobilefishdb
   mysql> show tables;
   mysql> quit
   
   
4. If you don't want to have the "mobilefishdb" database you can remove it with:
   C:\..\mysql\bin>mysqladmin -uroot -pmysecret drop mobilefishdb
   
   
5. To terminate MySQL server, type:
   C:\..\mysql\bin>mysqladmin -uroot -pmysecret shutdown