MySQL Tutorial
Introduction Installation of MySQL Getting MySQL server information RDBMS Concepts Data Definition Language Data types Managing Databases Managing Tables Managing Views Inserting records in a table Updating records in a table Deleting records from a table Retrieving Records Data Control Language Variables in MySQL Procedures in MySQL Built-In functions in MySQL User Defined functions in MySQL Triggers in MySQL Cursors in MySQL Transaction control Event scheduler Tuning MySQL Server Performance Managing users Importing and Exporting data Database Testing Workbench PHPMyAdminData types in MySQL
MySQL supports below types of data types.- Numeric Data Type
- String
- Date and Time
Numeric data type
- bit – single bit
- bool and boolean and TINYINT = 1 byte. So you can store signed numbers in the range from -128 to 127. You can store the unsigned numbers in from 0 to 255. If you store 0, it is treated as false. If you store 1, it is treated as true.
- SMALLINT = 2 bytes. So you can store signed numbers in the range from -32768 to 32767. You can store the unsigned numbers in from 0 to 65535
- MEDIUMINT = 3 bytes. So you can store signed numbers in the range from -8388608 to 8388607. You can store the unsigned numbers in from 0 to 16777215
- INT = 4 bytes. So you can store signed numbers in the range from -2147483648 to 2147483647. You can store the unsigned numbers in from 0 to 4294967295
- BIGINT = 8 bytes. So you can store signed numbers in the range from -9223372036854775808 to 9223372036854775807. You can store the unsigned numbers in from 0 to 18446744073709551615.
CREATE TABLE DATATYPES(
DataId INT(20) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
Age INT(2) zerofill
);
Insert into DATATYPES (Age) values(4);
If we display the record, Age values will be padded with 0. 04- FLOAT – 4 bytes
- DOUBLE – 8 bytes
- DECIMAL – Decimal type can be used in scenarios where you need to preserve exact precision. It is generally used to store financial data as we need a lot of accuracy.
- For example DECIMAL(6,2) would be used to store values from -9999.99 to 9999.99.
- Here 6 is the precision and 2 is the scale.
- Precision means the total number of digits in a number. Scale means the number of digits to the right of the decimal point in a number.
- Float uses 7 digits for precision. Double use 15 digits for precision. Decimal used 28 digits for precision.
- SERIAL is an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE.
String data type
- CHAR – CHAR data type is used when you know that column value will be of fixed size. For example – Usually states are displayed in 2 character format. So we can use CHAR(2) as a datatype. Also we can store maximum 255 characters with this data type. CHAR is much faster than VARCHAR.
- VARCHAR – Main difference between CHAR and VARCHAR is that size is not fixed in VARCHAR. Let us say you want to store the name of the person. We know that length of the name is not fixed. But we also know that Maximum length could be say 100 characters. Then We can use VARCHAR data type like VARCHAR(100). Note that 100 is the max size. If the actual length of the name is say 20 characters then only 20 bytes are used to store the data + 1 or 2 bytes as a prefix to hold length of the actual value. Also we can store upto 65,535 characters with this data type.
- BINARY and VARBINARY – These data types are similar to the char and varchar. Only difference is that data is stored in binary format.
- TINYTEXT – 255 bytes
- TEXT – 65535 bytes. VARCHAR has a variable max size of 65535 bytes.
- MEDIUMTEXT – 16,777,215 bytes
- LONGTEXT – 4,294,967,295 bytes
- BLOB stands for binary large objects. We use this data type to store images, audio, video etc.
- TINYBLOB, BLOB, MEDIUMBLOB and LONGBLOB are similar to the TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT. Only difference is that they store the binary data.
- ENUM – used to store constant values
- SET –
Date and Time Data types
- DATE – This data type stores only date and displays it in the YYYY-MM-DD format
- DATETIME – This data type stores both date and time and displays it in the YYYY-MM-DD HH:MM:SS format
- TIME – This data type stores only time and displays it in the format – HH:MM:SS
- YEAR – This data type stores only year and display it in the format YYYY
- TIMESTAMP – This data type is useful when we want to view data in different time zones. For example, let us say we inserted the record with current timestamp in X time zone. Now if someone tries to view the record in different time zone, he will see the timestamp updated for his time zone.
CREATE TABLE TESTTIMESTAMP(
Stamp TIMESTAMP,
DateInserted DATETIME
);
SET time_zone=’+00:00′;INSERT INTO TESTTIMESTAMP VALUES(CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
Now change the time zone and view records. SET time_zone =’+05:00′; select * from TESTTIMESTAMP; You will notie that timestamp value changes as we change the time zone but date time value does not change even if we change the time zone.Convert the data type of variables
We can use CAST and CONVERT functions to convert the data type of variables.CAST Function
SELECT (4.5 + CAST(‘2’ AS decimal)); SELECT CONCAT(‘Converting 4 into string ‘,CAST(4 AS CHAR));CONVERT function
SELECT (5 + CONVERT(‘6’, signed int)); SELECT CONCAT(‘Converting number to string’,CONVERT(1.2,CHAR));Converting charcter sets
SELECT CONVERT(_latin1’str1′ USING ascii); SELECT CAST(_latin1’str1′ AS CHAR CHARACTER SET ascii); Converting string to date SELECT STR_TO_DATE(’09/01/1986′, ‘%d/%m/%Y’);Web development and Automation testing
solutions delivered!!