×
Namespaces

Variants
Actions

How to create and drop a table in MySQL database

From Nokia Developer Wiki
Jump to: navigation, search
Article Metadata
Article
Created: Maveric (27 May 2010)
Last edited: hamishwillee (31 Jul 2012)


Contents

Introduction

The MySQL database is a widely used relational database, installation of which comes with different web server bundles like WAMP (Windows) and LAMP (Linux). It can ofcourse also be installed separately Mobile devices can access databases on web servers as wired computers can, reading and writing data from them e.g. to populate fields or even tables in the application or to send data for remote server database for analysis and storage.

In this Wiki article we are going to take a database in use, we will grant USAGE right to the user for that database, then we will create a simple table in MySQL, then delete it. Understanding database creation and deletion (dropping) is essential for administrating any database for any purpose.

Note :

The phpMyAdmin console is an alternative way to perform these tasks, but it is not in the scope of this article.


Prerequisites

This WiKi article assumes, but not requires:

database user called “maveric” exists
database called “users” exists
database user “maveric” has been granted with SELECT, INSERT, UPDATE and DELETE privileges

What is required however is, that a user with above granted privileges does exist. If you do not know how to do that, you can follow the steps in the related WiKi article:

[Link: Wiki article to create database and set privileges in MySQL]


This article is written from the perspective that the MySQL will reside on a local server (localhost).


Follow these steps to add a new user to your database and to set the required administrative privileges:


Start the MySQL server

If the MySQL server has not yet been started, please do so. In e.g. WAMP this can be done by opening it’s menu (left-click), selecting MySQL, Service, Start/Resume.

Note: if the server is not running, you will not be able to login.


Start The MySQL console (monitor)

It can be opened from the WAMP menu or alternatively enter “mysql” in the command prompt of the directory that the progam resides (e.g. C:\wamp\bin\mysql\mysql5.1.36\bin)

Log in as user “root” (must have been created with password earlier):

C:\wamp\bin\mysql\mysql5.1.36\bin>mysql -u root –p users;

Note: the option –p users here sets the active database to “users” and the password will be still asked.

After a successful login, the MySQL monitor starts and displays the greeting similar to the following:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.1.36-community MySQL Community Server (GPL)
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql>


Grant the database usage privilege

It was presumed, that the user “maveric” was previousy granted with SELECT, INSERT,UPDATE, DELETE privileges, but not USAGE privilege. If that is not granted, the user cannot access the database, meaning cannot take it in use and perform the previously granted SQL commands.

As the root user, issue the following command:

>mysql GRANT USAGE on users.* TO maveric@localhost;


A successful operation would result to a message like:

Query OK, 0 rows affected (0.00 sec)

The USAGE grant will now enable the user to take the specific database in use, but for creating tables we have to add one more privilege, named CREATE.

>mysql GRANT CREATE on users.* TO maveric@localhost;

A successful operation would result to a message like:

Query OK, 0 rows affected (0.00 sec)


Exit the MySQL monitor by issuing the command “quit;”, to which the MySQL will say “Bye”.

mysql> quit;
Bye


Take the database in use

1. Log in to the “users” database as user “maveric”, now possessing the required privilege to take it in use:

C:\wamp\bin\mysql\mysql5.1.36\bin>mysql -u maveric –p

Enter your password, then press Enter. After logged in, change the database to “users”:

>mysql use users;

After a successful database change the following message appears:

Database changed


Create a table into the database in use

Note: we assume this table is not already existing in the “users” database. Also, we are not in this article going more deeply in data types, variables etc. The purpose is just to show the process of creation and deletion of a table. A separate Wiki article is going to handle the table creation and population in more detail, including the SQL commands SELECT, INSERT, UPDATE and DELETE.


In the “users” database we will create a table named “customers”, with the following SQL statemant. While writing in the MySQL monitor, you can copy paste the below lines one-by-one and the whole clause will execute only after the semicolon sign “;”.


CREATE TABLE customers ( customer_id INT(10), name VARCHAR(40), birthdate DATE, );

This would be a table where each customer has properties like customer_id (numeric, length 10), name (alphanumeric, length 40), birthdate (type of date / e.g. 05/27/2010).

mysql> CREATE TABLE customers (customer_id INT(10), name VARCHAR(40), birthdate DATE);

Successful table creation is showed with message:

Query OK, 0 rows affected (0.06 sec)


To check that the table really does exist, and the accompanying SQL sentence that does create it, issue the following command:

>mysql SHOW CREATE TABLE customers;

The result should look similar to this:

+-----------+--------------------------------------------------------
-----------------------------------------------------------------+
| Table | Create Table
|
+-----------+--------------------------------------------------------
-----------------------------------------------------------------+
| customers | CREATE TABLE `customers` (
`customer_id` int(10) DEFAULT NULL,
`name` varchar(40) DEFAULT NULL,
`birthdate` date DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-----------+--------------------------------------------------------
-----------------------------------------------------------------+



Delete the created table

The table will be deleted, in MySQL it is called “dropping”, and the command for it is “DROP TABLE”. This will remove both the data in the table as well as the table definition, so extra caution should be exercised using this statement.

Because of the criticality of issuing a DROP TABLE command, the user must have privileges to it. At this point we did not issue that yet, so it needs to be done now.

Quit the monitor application.

>mysql quit;

Start the monitor application as user root, provide the necessary password.

>mysql -u root -p

Change database to “users” and grant the DROP privilege to user “maveric”:

mysql> use users;
 
Database changed
 
mysql> GRANT DROP on users.* TO maveric@localhost;
 
Query OK, 0 rows affected (0.00 sec)

Quit the monitor and login as user “maveric”, then change to database “users” again (see previous steps on how to do this).

When the database has been changed, issue the DROP TABLE command on the previously created table “customers”:

mysql> DROP TABLE customers;
 
Query OK, 0 rows affected (0.00 sec)

Check that the table was dropped by issuing

mysql> SHOW CREATE TABLE customers;

You should receive error similar to this:

ERROR 1146 (42S02): Table 'users.customers' doesn't exist




Next steps

You could check the WiKi article (under construction) where I am handling manipulation of table data, types of data including how to select rows from the tables, insert, update and delete information in them.


Related Articles

Articles on How to install WAMP and MySQL server to create Mobile server use



--Maveric 13:00, 27 May 2010 (UTC)

This page was last modified on 31 July 2012, at 07:11.
206 page views in the last 30 days.

Was this page helpful?

Your feedback about this content is important. Let us know what you think.

 

Thank you!

We appreciate your feedback.

×