MariaDB creates a table

May 16, 2021 MariaDB

In this chapter, we'll learn how to create a table. B efore you create a table, determine its name, field name, and field definition.

Here is the general syntax for table creation:

CREATE TABLE table_name (column_name column_type);

View the commands used to create tables in the PRODUCTS database -

databaseproducts_ tbl(
   product_id INT NOT NULL AUTO_INCREMENT,
   product_name VARCHAR(100) NOT NULL,
   product_manufacturer VARCHAR(40) NOT NULL,
   submission_date DATE,
   PRIMARY KEY ( product_id )
);

The example above uses "NOT NULL" as a field property to avoid errors caused by empty values. T he property AUTO_INCREMENT indicates that MariaDB adds the next available value to the ID field. T he keyword primary key defines the column as the primary key. M ultiple columns separated by commas define the primary key.

The two main methods for creating tables are to use command prompts and PHP scripts.

Command prompt

Perform tasks using the CREATE TABLE command, as shown below -

root@host# mysql -u root -p
Enter password:*******
mysql> use PRODUCTS;
Database changed
mysql> CREATE TABLE products_tbl(
   -> product_id INT NOT NULL AUTO_INCREMENT,
   -> product_name VARCHAR(100) NOT NULL,
   -> product_manufacturer VARCHAR(40) NOT NULL,
   -> submission_date DATE,
   -> PRIMARY KEY ( product_id )
   -> );
mysql> SHOW TABLES;
+------------------------+
| PRODUCTS               |
+------------------------+
| products_tbl           |
+------------------------+

Make sure that all commands end with a sign.

PHP creates a table script

PHP provides the most information for table mysql_query (). I ts second argument contains the necessary SQL commands -

<html>
   <head>
      <title>Create a MariaDB Table</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 products_tbl( ".
            "product_id INT NOT NULL AUTO_INCREMENT, ".
            "product_name VARCHAR(100) NOT NULL, ".
            "product_manufacturer VARCHAR(40) NOT NULL, ".
            "submission_date DATE, ".
            "PRIMARY KEY ( product_id )); ";
      
         mysql_select_db( 'PRODUCTS' );
         $retval = mysql_query( $sql, $conn );
      
         if(! $retval ) {
            die('Could not create table: ' . mysql_error());
         }
         echo "Table created successfully
";
         
         mysql_close($conn);
      ?>
   </body>
</html>

After successfully creating the table, you will see the output below -

mysql> Table created successfully

MariaDB creates a table

Table of contents

Command prompt

PHP creates a table script

Cookie Consent