Creating Database in MySQL
Implementing Database in MySQL – Part 2
Foreword: In this part of the series, I explain how to create a database in MySQL, using the PurePerl MySQL Command Line Tool.
By: Chrysanthus Date Published: 14 Apr 2015
Introduction
Working with a Database
To work with a MySQL database you have to follow the following steps:
- Create the database (directory).
- Start the database server (in memory) that will run the database.
- Start the database (put it in memory).
- Connect user to the database (in memory).
- Use the database (create tables, save data, retrieve data, etc).
- Disconnect from the database server.
- Stop the database (remove from memory).
- Stop the database server, if you have to.
Note: when you complete anything you do with the database such as create table or send data, the result is automatically saved to disk (everything being equal); there is no command or statement to save changes. However, in some cases you can have the changes in memory and then flush the changes to the database in disk (see later).
Example
Consider the database of a wholesale company that orders products from manufacturers and sells to shops in the cities. Let the database name be, wholesale. We shall create the database for this company called, wholesale. This database is to track the ordering and sales of products for shops (supermarkets) by the wholesale company.
Creating a Database
- If the MySQL server is not already started, start it as indicated in the previous part of the series.
- Open the command prompt window and type, cd c:\ Enter, to go to the root directory of your computer.
- Start the command line tool, Mysqlcom.pl (possibly in the C:\Perl\lib directory) and connect to the server with the following command sequence: Type the following line and press the Enter key:
connect "root", "localhost", 3306;
You should see, Password: _ .
Next, type the password, sql and press the Enter Key. You should be connected after that.
You should now have the Mysql> prompt. From now onward, you will be typing SQL statements until you type, CLOSE to close the connection and QUIT, to quit the command line tool.
- To create the database, type the following SQL statement (ending with a semicolon) and press Enter.
CREATE DATABASE wholesale;
A confirmation message should appear. It may not be very explicit. Now, CREATE and DATABASE are reserved words for MySQL. Reserved words are words that you do not use arbitrarily. wholesale is the name of the database. A database in MySQL is implemented as a directory containing files that correspond to tables in the database. The database directory is created in the MySQL data directory.
In MySQL you cannot use a database after you have just created it. You have to state explicitly that you want to use the database. The USE SQL statement is used for this.
- Execute (type and press Enter) the following statement, which will allow you to use the database you have just created:
USE wholesale;
A confirmation message should be displayed; it may not be very explicit. However, it means you have changed from whatever database you were with, to this one.
Any connection opened has to be closed. Type the following at the tool prompt and press the Enter Key:
CLOSE;
You should have a confirmation message saying that the connection is closed. Now, quit the command line tool.
Quitting the Command Line Tool
To quit the command line tool, type the following and press Enter:
QUIT
You should see, Bye. Typing the semicolon for CLOSE and QUIT is optional. Even typing the semicolon for connect is optional. But typing the semicolon for the CREATE DATABASE statement and “USE wholesale;”, is not optional. This is because, connect, close and quit are functions, while Create Database or “USE wholesale;” is a statement.
We have learned how to create a database and select it for use. Let us stop here for now, and continue in the next part of the series.
Chrys
Related Links
Implementing Database in MySQLProgramming in MySQL
Backup Basics in MySQL
MySQL Access Privileges
Regular Expressions in MySQL
Date and Time in MySQL
Event in MySQL
MySQL Transaction
PurePerl MySQL API Prepared Statements
More Related Links
PurePerl MySQL Command Line Tool
Major in Website Design
Perl Course - Optimized
Web Development Course
BACK NEXT