Pages

Data Type for MySQL

This tutorial will tell you about data type which is used in MySQL server. The version of MySQL which I used to write this tutorial is  5.7.15. So there might be some chance that some of the data type not supported in your MySQL version.

So first thing is that what is Data type ?

In general as we know that there are some number and characters.
Number represent the some values and values further can be divided into categories like integer, real.

In computer science there must be some equivalent representation to represent these values in computer or in a program. so every computer language have its equivalent representation to repreasent these number.

so now we look into MySQL data type.


we can categorized the MySQL data type -

1. Numeric Data Type
2. Date and Time Data Type
3. String (characters and bytes) Data Type
4. Spatial Data Type


Numeric Data Type :

 

This data type is used for storing the number value.
This can be further categorized-

TINYINT -

size : A 1-byte integer. 
range : signed integer is -128 to 127 and for un-signed is 0 to 255.

SMALLINT -

size : A 2-byte integer.
range : signed integer is -32,768 to 32,767 and for un-signed is 0 to 65,535.

MEDIUMINT -

size : A 3-byte integer.
range : for this signed integer is -8,388,608 to 8,388,607 and for un-signed is 0 to 16,777,215.


INT -

size : A 4-byte integer.
range : signed integer is -2,147,483,648 to  -2,147,483,647 and for un-signed is 0 to 4,294,967,295.

BIGINT -

size : A 4-byte integer.
range : for this signed integer is -9,223,372,036,854,775,808 to
 9,223,372,036,854,775,807  and for un-signed is 0 to 18,446,744,073,709,551,615
 

DECIMAL -

A fixed point number (M,D).

M is the maximum number of digits is 65 and default 10
D is the maximum number of decimal points is 30  and default 0

FLOAT -

A small floating-point-number

 

 

DOUBLE  -

A double precision floating-point-number.

 

 

BIT -

A bit  data type is used to store bit-field value field Type (M), storing M of bits per value. default is 1 and maximum 64


BOOLEAN -

Like tinyint , a value zero considered as false and non-zero considered as true 


Date and Time Data Type

 

DATE -

format : YYYY-MM-DD
size: 3 bytes storage required
range : '1000-01-01' to '9999-12-31'


DATETIME - 

format : YYYY-MM-DD HH:MI:SS
size : 8 bytes storage required 
range : '1000-01-01 00:00:00' to '9999-12-31 23:59:59'

TIMESTAMP - 

format : YYYY-MM-DD HH:MI:SS 
size : 4 bytes storage required
range : '1970-01-01 00:00:00' to mid-year 2037


YEAR - 

format : YYYY or YY 
size : 1 byte storage required
range : 1901 to 2155 (for YEAR(4)), 1970 to 2069 (for YEAR(2))

MySQL begineer tutorial part-3

Hello Friends,

This  is the third series of MySQL beginner tutorial. I hope this third tutorial will enhance your knowledge of SQL with MySQL databse.



In previous two tutorial (tutorial-1 and tutorial-2) we learn how to connect to MySQL server and creation of data base and table.

we are using college database for our tutorial. In this part we learn some more SQL statements.

SQL statements and clause which we learn in this tutorial are-

INSERT statement
SELECT
SELECT with LIMIT clause
Conditional SELECT using WHERE clause
WHERE clause with AND, OR, NOT conditional operator


I hope this will add some new knowledge about SQL statement


INSERT statement : 

 

INSERT statement is used for populating the table, or we can say that INSERT statement is used for creating a record or tuple in table.

Since we have college database and student table, so we insert a student detail in our student table.

syntax for INSERT statement


INSERT INTO tablename (columnname 1, column2name2,.....) VALUES (columnvalue1,columnvakue2,...)

 Now in student table--

mysql>INSERT INTO tablename (roll_no, name, sex, age) VALUES ('101', 'RAM', 'M', 24);
mysql>INSERT INTO tablename (roll_no, name, sex, age) VALUES ('102', 'Neha', 'F', 22);

so now we have successfully insert two rows in student table.

NOTE : here one thing is for notice , that in values for roll_no, name., and sex we use single quotes ('') to put the value and for age we did not use qoutes since when we creating we define roll_no, name, as varchar and sex as char datatype and for age we define int datatype so when we insert a value for char or varchar datatype we use '' and for number in this case int we does not use ''.

you can check the datatypes of column for student table using DESC commnd that I have already told you.




SELECT statement :



SELECT statement is used for viewing the data of a particular table, so now we see the data of student table

syntax for SELECT statement


SELECT column[s] FROM table-name.
mysql > SELECT roll_no, name, sex, age FROM student;





selecting the particular column
SELECT roll_no FROM student;




selecting all column using *
mysql > SELECT * FROM student;


SELECT with LIMIT clause---------------

suppose we have table that contains 100s of record and we want to show only top 2 results, in this case we uses LIMIT clause to limit no of records

SELECT roll_no, name FROM student LIMIT 2; // this will return only first 10 record of the table.



WHERE clause in SELECT statement

WHERE clause is used for filtering the result of select statement.

suppose we want to select the recode which match to a particular condition

SELECT sex, age from student WHERE name='Akriti'.




SELECT roll_no, name from student where sex ='F'






WHERE clause with AND, OR, NOT conditional operator









I hope this third tutorial will help you to gain some knowledge about SQL and using it with MySQL.

If you have any confusion or suggestion you can type your query and suggestion in comment section and I will like to solve your query.

So we meet next time with next series of this tutorial.