SQL – Datatypes
Good understanding of SQL Datatypes are important to better understand SQL Syntax.
SQL Datatypes:
For understanding purpose lets consider the following table.
| Roll_no | Student_name | Subject | Marks |
|---|---|---|---|
| 1 | John | Math | 90 |
| 2 | Rita | Math | 95 |
| 3 | Sam | Math | 84 |
| 4 | Jack | Math | 72 |
| 5 | Pom | Math | 99 |
If you are new to SQL database and table, read following article
Difference between database and table in SQL
The above table contains five records and four columns.
First column name is Roll_no
Second Column Name is Student_name
Third Column Name is Subject
Fourth Column Name is Marks
Roll_no column contains roll number of students which is an Integer always
Student_name and Subject column contains Student name and subject which are string always
Last column, Marks column contains marks secured by students which is an integer always.
From the above table:
- Roll_no column is always Integer
- Student_name column is always String
- Subject column is always String
- Marks column is always Integer
In SQL World, to define datatype of a column we use keywords. Keywords are reserved words to represent a particular datatype or function or task.
To represent a column with integer datatype the keyword is – INT
To represent a column with string datatype the keyword is – VARCHAR
Are there only two datatypes exist?
There are more than two data types exist. But for our learning purpose we are going to focus on these two datatypes only. Once we understand basics, then we will proceed further in other chapters.
Can I store any length data in the table?
No.
In MySQL: Maximum length of INT value can be stored is – 2147483648 max value and -2147483648 min value (So any number in between these two value can be stored in MySQL table)
In MySQL: Maximum length of VARCHAR value can be stored is 65,535 (So string value less than 65535 can be stored in MySQL table)
EXAMPLE:
Length of “Hello I am Gyanol” is 10. (spaces are counted too)
Length of 9999 is 4
Before creating a table in the database, we must be aware of column names and its datatype. And also we must be aware of maximum length of varchar data value and maximum length of INT data value that is we are going to store in the table.