Skip to main content

How To Create a Table In MySQL Database

A table in SQL is simply a collection of data in columns and rows. It forms a very important part of a database as it holds all the information in it.

In this tutorial we will create a table that contains seven columns which will store product information (record) like product id, product name, manufacturer, price, quantity, purchase date and entry date. 

To create a table in MySql database we use the create table syntax shown below.



CREATE TABLE products
(
product_id CHAR(6) PRIMARY KEY,
product_name VARCHAR(100) NULL,
manufacturer varchar(100),
price INT NOT NULL,
quantity int NOT NULL,
purchase_date DATE,
entry_date DATETIME
)



Understanding the create table syntax above

  • create table is a statement used in creating tables
  • sales_record is our chosen name for the table
  • item_id, item_name, quantity, price, date_sold and entry_date form the columns of our table. Notice the use of underscore to bind column names that contained more than one word. Another way of handling such case is by joining the two words together like this productName (do not use hyphen as the system will see it as a subtraction sign and will trigger an error).


CONSTRAINTS: see full list and description here

  • primary key is a used to identify a record (row) uniquely. It allows no duplicate entry.
  • null allows null values. You can also initiate this constraint by leaving out the NULL as done in manufacturer column above.
  • not null does not allow null values (a value must be supplied in each row for that column)


DATATYPES: see full list and description here

  • char short for character is a datatype that allows values in string format
  • varchar like char accepts values in string format (varchar stands for VariableCharacter) 
  • (6) and (100) in front char and varchar denotes the maximum number of character they can take respectively. Char datatype can take any number of character from 0 to 255 while varchar can take up to 65,535 characters.
  • int is a datatype that allows only integer values
  • date like its name sounds allows data in date format

Congratulations! You just created a database and an empty table where you can store your products information...thumbs up!


Now to have a look of the table you just created, type and run the query (SQL code) below in your MySQL environment and you will see something  that looks like picture below the query.



SELECT * FROM products











So now let's move over to how you can feed in data into the table. Check out the tutorial below to find out how.





Comments

Post a Comment