Create Phonebook Database Using Command-Line

Start command-line client and login into the database server

mysql  -h localhost -u root -prootpassword

mysql  -h <host> -u<user> -p<password>

Execute a file containing SQL commands

source doitall.sql

source <filename>

Drop (delete) a database

  Note: Create the file dropdb.sql with the following MySQL commands in it.

DROP DATABASE IF EXISTS phonebook;

DROP DATABASE IF EXISTS <dbname>

Create a new database

  Note: Create the file createdb.sql with the following MySQL commands in it.

DROP DATABASE IF EXISTS phonebook;

CREATE DATABASE phonebook;

Select a database, create database table

  Note: Create the file createtable.sql with the following MySQL commands in it.

USE phonebook;

CREATE TABLE     info(
id               int            not null auto_increment,
firstname        varchar(128),
lastname         varchar(128),
phonenumber      varchar(32),
PRIMARY KEY      (id)
)                ENGINE=InnoDB;

Display database table information

  Note: Create the file displaytable.sql with the following MySQL commands in it.

USE phonebook;

DESCRIBE info;

Insert data into database

  Note: Create the file addddata.sql with the following MySQL commands in it.

USE phonebook;

INSERT INTO info (firstname,lastname,phonenumber)
       VALUES ('Tim','Smith','888-888-8888');
INSERT INTO info (firstname,lastname,phonenumber)
       VALUES ('Judy','Jones','999-999-9999');
INSERT INTO info (firstname,lastname,phonenumber)
       VALUES ('John','Smith','777-777-7777');

INSERT INTO info (firstname,lastname,phonenumber)
       VALUES ('firstname','lastname','phonenumber');

Return data from database

  Note: Create the file returndata.sql with the following MySQL commands in it.

USE phonebook;

SELECT firstname,lastname FROM info WHERE phonenumber='888-888-8888';

SELECT phonenumber FROM info WHERE lastname='Jones';

SELECT * FROM info;

Do it all in one file of SQL commands

  Note: Create the file doitall.sql with the following MySQL commands in it.

DROP DATABASE IF EXISTS phonebook;

CREATE DATABASE phonebook;

USE phonebook;

CREATE TABLE     info(
id               int            unsigned not null auto_increment,
firstname        varchar(128),
lastname         varchar(128),
phonenumber      varchar(32),
PRIMARY KEY      (id)
)                ENGINE=InnoDB;

INSERT INTO info (firstname,lastname,phonenumber)
       VALUES ('Tim','Smith','888-888-8888');
INSERT INTO info (firstname,lastname,phonenumber)
       VALUES ('Judy','Jones','999-999-9999');
INSERT INTO info (firstname,lastname,phonenumber)
       VALUES ('John','Smith','777-777-7777');

SELECT firstname,lastname FROM info WHERE phonenumber='888-888-8888';

SELECT phonenumber FROM info WHERE lastname='Smith';

SELECT * FROM info;