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

The Windows Firewall with Advanced Security is a firewall that runs on the Windows Server 2012 and is turned on by default. The Firewall settings within Windows Server 2012 are managed from within the  Windows Firewall Microsoft Management Console . To set Firewall settings perform the following steps − Step 1  − Click on the Server Manager from the task bar → Click the Tools menu and select Windows Firewall with Advanced Security. Step 2  − To see the current configuration settings by selecting  Windows Firewall Properties  from the MMC. This  allows access to modify the settings  for each of the three firewall profiles, which are –  Domain, Private and Public  and IPsec settings. Step 3  − Applying custom rules, which will include the following two steps − Select either  Inbound Rules  or  Outbound Rules  under  Windows Firewall with Advanced Security  on the left side of the management console...
Post a Comment
Read more
In this chapter, we will see how to Install Active Directory in Windows Server 2012 R2. Many of us who have worked with the previous version run  DCPROMO.EXE  to install it, but in the 2012 version, it is recommended by Microsoft not to use it anymore. To continue with Installation follow the steps given below. Step 1  − Go to “Server Manager” → Manage → Add Roles and Feature. Step 2  − Click the Next button. Step 3  − As we are installing AAD on this machine, we will select “Role-based or featurebased Installation” → Next. Step 4  − Click on “Select a server from the server pool”, this is the case when it will be installed locally. Step 5  − Check mark in the box next to  Active Directory Domain Services . A box will be explaining additional roles services or features which are also required to install domain services. Step 6  − Click Add Features. Step 7  − Check “Group Policy Management” → Next. Step 8  ...
Post a Comment
Read more