Have a look at the following line of code from the post Storing and Retrieving Data from MySQL Database.
CREATE TABLE phno(
name varchar( 50 ) ,
phnum varchar( 20 )
)
As I told you we have to declare the column data types while creating tables depending on the values we want to store. MySQL has wide variety of data types, out of which I’m stating some of the important ones below:
-
CHAR: For storing character and string data, faster than VARCHAR.
-
VARCHAR: For storing character and string data. Slower but efficient in storing variable length string as only the needed memory is used no matter how much we allocate.
-
INT: For storing regular integers. Same as INTEGER, other related data types are TINYINT, SMALLINT, MEDIUMINT, BIGINT.
-
FLOAT: For storing floating point values, it is 4 bytes long. There is also a DOUBLE which is 8 bytes long.
-
DATE: For storing YYYY-MM-DD date values.
Besides data types we can also assign column fields some properties to change the way they store data. These are some of the useful ones:
-
NOT NULL: This specifies that a particular field cannot be empty. When inserting data you must provide data for this field or else MySQL generates error. It is used to make sure value for certain column is always provided.
-
AUTO_INCREMENT: This property can be used with integer field to automatically make MySQL insert serial numbers to each row inserted.
-
PRIMARY KEY: primary key is very important property of a table. It helps MySQL speed up query processing. Usually, serial number column field is defined as a primary key but it can be any field which is unique for each row.
The following SQL code illustrates how the above properties are used:
CREATE TABLE temp(
id integer AUTO_INCREMENT PRIMARY KEY ,
name varchar( 20 ) NOT NULL
)
It is a good idea to always have an extra column for each table. This column should be defined as both AUTO_INCREMENT and PRIMARY KEY.
Previous Articles: