Skip to main content
The table creation command requires:
  • Name of the table
  • Names of fields
  • Definitions for each field

Syntax:

Here is generic SQL syntax to create a MySQL table:
CREATE TABLE table_name (column_name column_type);
Now, we will create following table in TUTORIALS database.
tutorials_tbl(
   tutorial_id INT NOT NULL AUTO_INCREMENT,
   tutorial_title VARCHAR(100) NOT NULL,
   tutorial_author VARCHAR(40) NOT NULL,
   submission_date DATE,
   PRIMARY KEY ( tutorial_id )
);
Here few items need explanation:
  • Field Attribute NOT NULL is being used because we do not want this field to be NULL. So if user will try to create a record with NULL value, then MySQL will raise an error.
  • Field Attribute AUTO_INCREMENT tells MySQL to go ahead and add the next available number to the id field.
  • Keyword PRIMARY KEY is used to define a column as primary key. You can use multiple columns separated by comma to define a primary key.

Creating Tables from Command Prompt:

This is easy to create a MySQL table from mysql> prompt. You will use SQL command CREATE TABLE to create a table.

Example:

Here is an example, which creates tutorials_tbl:
root@host# mysql -u root -p
Enter password:*******
mysql> use TUTORIALS;
Database changed
mysql> CREATE TABLE tutorials_tbl(
   -> tutorial_id INT NOT NULL AUTO_INCREMENT,
   -> tutorial_title VARCHAR(100) NOT NULL,
   -> tutorial_author VARCHAR(40) NOT NULL,
   -> submission_date DATE,
   -> PRIMARY KEY ( tutorial_id )
   -> );
Query OK, 0 rows affected (0.16 sec)
mysql>
NOTE: MySQL does not terminate a command until you give a semicolon (;) at the end of SQL command.

Creating Tables Using PHP Script:

To create new table in any existing database you would need to use PHP function mysql_query(). You will pass its second argument with proper SQL command to create a table.

Example:

Here is an example to create a table using PHP script:
<html>
<head>
<title>Creating MySQL Tables</title>
</head>
<body>
<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
  die('Could not connect: ' . mysql_error());
}
echo 'Connected successfully<br />';
$sql = "CREATE TABLE tutorials_tbl( ".
       "tutorial_id INT NOT NULL AUTO_INCREMENT, ".
       "tutorial_title VARCHAR(100) NOT NULL, ".
       "tutorial_author VARCHAR(40) NOT NULL, ".
       "submission_date DATE, ".
       "PRIMARY KEY ( tutorial_id )); ";
mysql_select_db( 'TUTORIALS' );
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
  die('Could not create table: ' . mysql_error());
}
echo "Table created successfully\n";
mysql_close($conn);
?>
</body>
</html>

Comments

Post a Comment

Popular posts from this blog

C is a general-purpose, procedural, imperative computer programming language developed in 1972 by Dennis M. Ritchie at the Bell Telephone Laboratories to develop the UNIX operating system. C is the most widely used computer language. It keeps fluctuating at number one scale of popularity along with Java programming language, which is also equally popular and most widely used among modern software programmers. Audience This tutorial is designed for software programmers with a need to understand the C programming language starting from scratch. This tutorial will give you enough understanding on C programming language from where you can take yourself to higher level of expertise. Prerequisites Before proceeding with this tutorial, you should have a basic understanding of Computer Programming terminologies. A basic understanding of any of the programming languages will help you in understanding the C programming concepts and move fast on the learning track. Execute
Post a Comment
Read more
C is a general-purpose, high-level language that was originally developed by Dennis M. Ritchie to develop the UNIX operating system at Bell Labs. C was originally first implemented on the DEC PDP-11 computer in 1972. In 1978, Brian Kernighan and Dennis Ritchie produced the first publicly available description of C, now known as the K&R standard. The UNIX operating system, the C compiler, and essentially all UNIX application programs have been written in C. C has now become a widely used professional language for various reasons − Easy to learn Structured language It produces efficient programs It can handle low-level activities It can be compiled on a variety of computer platforms Facts about C C was invented to write an operating system called UNIX. C is a successor of B language which was introduced around the early 1970s. The language was formalized in 1988 by the American National Standard Institute (ANSI). The UNIX OS was totally written in C. Today C
Post a Comment
Read more
Properly defining the fields in a table is important to the overall optimization of your database. You should use only the type and size of field you really need to use; don't define a field as 10 characters wide if you know you're only going to use 2 characters. These types of fields (or columns) are also referred to as data types, after the  type of data  you will be storing in those fields. MySQL uses many different data types broken into three categories: numeric, date and time, and string types. Numeric Data Types: MySQL uses all the standard ANSI SQL numeric data types, so if you're coming to MySQL from a different database system, these definitions will look familiar to you. The following list shows the common numeric data types and their descriptions: INT  - A normal-sized integer that can be signed or unsigned. If signed, the allowable range is from -2147483648 to 2147483647. If unsigned, the allowable range is from 0 to 4294967295. You can specify a widt
Post a Comment
Read more