MySQL Create Table

create table in my sql

MySQL Create Table : A table is used to organize data in the form of rows and columns and used for both storing and displaying records in the structure format. It is similar to worksheets in the spreadsheet application. A table creation command requires three things:

  • Name of the table
  • Name of the fields
  • Definitions for each field

Syntax of MySQL Create Table:

CREATE TABLE table_name (column_name column_type);

Now, we will create the following table in the Edusite database.

create table edusite_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 ( edusite_id )

);

Here, a few items need explanation −

  • Field Attribute NOT NULL
  • Field Attribute AUTO_INCREMENT
  • Keyword PRIMARY KEY

Creating Tables from Command Prompt

It is easy to create a table from the my sql> prompt. You will use the SQL command CREATE TABLE to create a table.

Example

Here is an example, which will create tutorials_tbl −

root@host# mysql -u root -p

Enter password:*******

mysql> use Edusite;

Database changed

mysql> CREATE TABLE edusite_tbl(

   -> edusite_id INT NOT NULL AUTO_INCREMENT,

   -> edusite_title VARCHAR(100) NOT NULL,

   -> edusite_author VARCHAR(40) NOT NULL,

   -> submission_date DATE,

   -> PRIMARY KEY ( edusite_id )

   -> );

Query OK, 0 rows affected (0.16 sec)

mysql>

MySQL Create Table 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 a proper SQL command to create a table.

Example

The following program 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 edusites_tbl( ".

            "edusite_id INT NOT NULL AUTO_INCREMENT, ".

            "edusite_title VARCHAR(100) NOT NULL, ".

            "edusite_author VARCHAR(40) NOT NULL, ".

            "submission_date DATE, ".

            "PRIMARY KEY ( edusite_id )); ";

         mysql_select_db( 'EDUSITES' );

         $retval = mysql_query( $sql, $conn );

         

         if(! $retval ) {

            die('Could not create table: ' . mysql_error());
</body>
</html>

Spread the love

Leave a Comment

Your email address will not be published. Required fields are marked *