CBSE Today

MySQL Create Table Command

MySQL Create Table Command to create a new table in the database. MySQL, we need the following information.

  1. Name of the database
  2. Name of the table0
  3. Name of every attribute along with its data type, size, and constrains

The general syntax of the Create Table command is as follows

Create table  ( column_name datatype size constraints ,
column_name datatype size constraints , column_name datatype size constraints ,
);

Where table name is a valid identifier and column_name is user-defined attributes.

Now suppose you want to create the following table student in a database school.

S.NoField NameData Type & SizeConstraints
1admno  integer (6)Primary key
2namechar(30)Not Null
3father_namevarchar(40)
4addressvarchar(100)

Creating Tables from Command Prompt

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

Example

mysql>use school ;
mysql> create table student (admno int(6) primary key, name char(30) NOT NULL,
                             father_name  varchar(30), address varchar(100)
                       );

The primary key constraint makes sure that the column only contains unique values. Once the table is generated make it sure it is very much there using the following two commands

mysql> show tables;
mysql> describe student

NOTE: Describe command is used to show the structure of the table along with its attribute names, data type, and constraints.

Create a Table using Like Clause

Like clause is used with Create table command to create a new table using the structure of any existing table. Suppose you want to create a table duplicate that has the same structure as the Table student

create table suplicate Like Student

Use describe command to show the structure of this newly created Table

mysql> desc duplicate;
+--------+------------+------+-----+---------+-------+
| Field  | Type       | Null | Key | Default | Extra |
+--------+------------+------+-----+---------+-------+
| admno  | char(10)   | NO   | PRI | NULL    |       |
| name   | char(30)   | NO   |     | NULL    |       |
| stream | char(20)   | YES  |     | NULL    |       |
| marks  | float(5,2) | YES  |     | NULL    |       |
| grade  | char(2)    | YES  |     | NULL    |       |
| fees   | float(7,2) | YES  |     | NULL    |       |
| gender | char(1)    | YES  |     | NULL    |       |
| gameID | int(4)     | YES  |     | NULL    |       |
+--------+------------+------+-----+---------+-------+

create a table with structure and data of another Table

Use AS clause with the Create Table command to create a new table from any existing table. Suppose we want to create a new table ‘Female’ that has the same structure of the ‘student’ table but contains only the records of FEMALE students

create table FEMALE as SELECT * from student where gender='F';

Cross verify the above command using the following commands

mysql> desc female;
+--------+------------+------+-----+---------+-------+
| Field  | Type       | Null | Key | Default | Extra |
+--------+------------+------+-----+---------+-------+
| admno  | char(10)   | NO   |     | NULL    |       |
| name   | char(30)   | NO   |     | NULL    |       |
| stream | char(20)   | YES  |     | NULL    |       |
| marks  | float(5,2) | YES  |     | NULL    |       |
| grade  | char(2)    | YES  |     | NULL    |       |
| fees   | float(7,2) | YES  |     | NULL    |       |
| gender | char(1)    | YES  |     | NULL    |       |
| gameID | int(4)     | YES  |     | NULL    |       |
+--------+------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

mysql> select * from female;
+-------+--------+--------+-------+-------+---------+--------+--------+
| admno | name   | stream | marks | grade | fees    | gender | gameID |
+-------+--------+--------+-------+-------+---------+--------+--------+
| 101   | rakesh | sci    | 96.56 | A     | 1850.35 | F      |      2 |
| 104   | mannat | human  | 67.50 | A     | 1420.15 | F      |      4 |
| 105   | unnati | comm   | 77.50 | B     | 1650.75 | F      |      1 |
| 111   | punya  | comm   | 86.56 | B     | 1650.75 | F      |      1 |
+-------+--------+--------+-------+-------+---------+--------+--------+
4 rows in set (0.00 sec)

Rename MySQL Table using Command

MySQL also provide command to rename any existing table. The syntax is as follows

mysql>RENAME table  to 

Example 
mysql> RENAME table student to Admission;
</pre
The above command will change the name of student table into Admission.

Create Table with Auto_increment column Constraint

If you want to place auto-increment constraint on any column then make it very sure that the same column is the primary key and it does not contain any duplicate values.

Example

Create Table Student ( admno int(11) primary key Auto_increment, name char(30), address char(100), DOB date);

Tip: Auto_increment feature does not work with char or varchar or float type values

Create a Database Table using Python Script

Python Program to create a new database table in any existing database, Python module MySQLdb is used to make connections between Python and MySQL.

Example

The following program is an example to create a table using Python script −

import MySQLdb
db = MySQLdb.connect("localhost", "root", "", "school")
cursor = db.cursor()
query ='''create table student ( admno int(6) primary key, 
                         name char(30) NOT NULL, 
                         father_name varchar(30), 
                         address  varchar(100)); '''
cursor.execute(query)
db.close()
print('Student Table Generated')

Besides, this Tutorial on the MySQL Create Table command, My SQL assignment on Create command will help you a lot to understand the basics of the MySQL Create Table command. If you have any queries, please do not hesitate to email is your queries.

Exit mobile version