MySQL

 
 
MySQL is a fast, multi-threaded, multi-user open source SQL database server.

The MySQL database is released under the GNU General Public License (GPL). MySQL can be used for free when you are not distributing any part of the MySQL system. For example: If you run a commercial web site using the MySQL Database Software as long as MySQL is not distributed.

Please read the MySQL manual for situations where a commercial license is needed.

More information about MySQL can be found at: https://www.mysql.com

MySQL online manual can be found at: https://dev.mysql.com/doc/index.html

The latest MySQL version can be downloaded from: https://dev.mysql.com/downloads/index.html







Create MySQL database and tables.



Information
none

Operating system used
Windows XP Home Edition Version 5.1 SP 2

Software prerequisites
MySQL 3.23 or higher

Procedure
  1. There are three ways to create databases and tables:

    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