Database systems
Database systems
Lecture 2 – Data Types
Roman Danel
2016
Data types
•
Numeric
•
String
•
Date and Time
•
Others (binary, spatial, XML…)
Numeric data types
•
Integer
•
Real (with decimal point)
–
Decimal, Numeric
–
Float - floating point
Numeric in MySQL (Integer)
•
Tinyint
–
−128 až 127 nebo 0 až 255 pro UNSIGNED, 1 byte
•
Smallint
–
−32768 to 32767 or 0 to 65535 for UNSIGNED, 2 bytes
•
MediumInt
–
−8388608 to 8388607 or 0 to 16777215 for UNSIGNED, 3 bytes
•
Int
–
−2147483648 to2147483647 or 0 to 4294967295 for UNSIGNED, 4
bytes
•
Bigint
–
−9223372036854775808 to 9223372036854 or 0 to
18446744073709551615 pro UNSIGNED, 8 bytes
Numeric Data Types in MySQL
•
Float(p)
•
Float(M,D)
•
Double(M,D)
•
Decimal(M,D) -
large numbers in floating point
stored as a string
(1 byte per numeral)
Numeric – Microsoft SQL Server
Numeric – Microsoft SQL Server
Numeric – Microsoft SQL Server
Numeric
•
Numeric(5,2) = 99.99
String Data Types - MySQL
•
Char(m), m=0-255, longer will be „lost“
•
Varchar(m), m=0-255
•
Tinyblob - 0 to 255 Bytes
•
Blob - 0 to 65535 Bytes
•
Mediumblob, Longblob
•
TinyText - 0 až 255 bajtů
•
Text, MediumText, LongText
•
Enum -
enumeration values; the values in the
column can be assigned just one value from a list
of values
Date Data Types
•
DATE
•
TIME
•
DATETIME
•
SMALLDATETIME
Date – Microsoft SQL Server
Others Data Types – MS SQL
•
Bit
– 1/0, memmory allocation - 1 bit
•
Timestamp
–
It is a data type that generates
unique binary number, which is used for version
resolution lines "rowversion". This data type does
not store date and time! To store the date and
time
should be
use
d
datetime2. In memory
occupies 8 bytes.
•
Uniqueidentifier
–
It is a data type that, for
example, in conjunction with the function NEWID
() generates a unique identifier. In memory it
takes up 16 bytes.
Value NULL
•
Value, which is not intended
•
Property of the column: NULL/NOT NULL
•
Example in SELECT:
SELECT * from
table
where
column_name
IS NULL;
Primary Key
Primary Key
–
defined over one or more
columns of the table. The main purpose of a
primary key is
ensure the uniqueness of
records in a table
.
Primary key values can not contain duplicate or
null.
Automatically generated number
•
MySQL – AUTO_INCREMENT
•
MS SQL – IDENTITY
•
Oracle – sequence
•
Access – Automatic Number
create table
Table_name
(
column_name
int IDENTITY(1,1) primary key,
Default Value
•
Apply if during operation insert the value is
not determined
•
If we want to avoid NULL values
Column Properties
•
UNIQUE
•
NULL/NOT NULL
•
PRIMARY KEY
•
DEFAULT
Converting Data Types
•
CONVERT
•
CAST
–
CAST ( $157.27 AS VARCHAR(10) )
–
Syntax for CAST:
•
CAST ( expression AS data_type [ (length ) ])
–
Syntax for CONVERT:
•
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
This content provides a comprehensive overview of various data types used in database systems, including numeric, string, date and time, and others. It covers different categories such as numeric data types in MySQL, Microsoft SQL Server data types, and string data types in MySQL. The images included visually represent the data types discussed, making it easier to understand and differentiate between them.
Uploaded on Feb 17, 2025 | 0 Views
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
You are allowed to download the files provided on this website for personal or commercial use, subject to the condition that they are used lawfully. All files are the property of their respective owners.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.
E N D
Presentation Transcript
Database systems Lecture 2 Data Types Roman Danel 2016
Data types Numeric String Date and Time Others (binary, spatial, XML )
Numeric data types Integer Real (with decimal point) Decimal, Numeric Float - floating point
Numeric in MySQL (Integer) Tinyint 128 a 127 nebo 0 a 255 pro UNSIGNED, 1 byte Smallint 32768 to 32767 or 0 to 65535 for UNSIGNED, 2 bytes MediumInt 8388608 to 8388607 or 0 to 16777215 for UNSIGNED, 3 bytes Int 2147483648 to2147483647 or 0 to 4294967295 for UNSIGNED, 4 bytes Bigint 9223372036854775808 to 9223372036854 or 0 to 18446744073709551615 pro UNSIGNED, 8 bytes
Numeric Data Types in MySQL Float(p) Float(M,D) Double(M,D) Decimal(M,D) - large numbers in floating point stored as a string (1 byte per numeral)
Numeric Microsoft SQL Server Data Type Range Memmory Allocation -263a 263 bigint 8 bytes -231a 231 int 4 bytes -215a 215 smallint 2 bytes tinyint 0 a 255 1 byte Data Type Precision Memmory Allocation decimal 1-9 5 bytes decimal 10-19 9 bytes decimal 20-28 13 bytes decimal 29-38 17 bytes
Numeric Microsoft SQL Server Data type Range Memory Allocation -922,337,203,685,477.58 to 922,337,203,685,477.58 money 8 bytes - 214,748.3648 to 214,748.3647 smallmoney 4 bytes
Numeric Microsoft SQL Server Data type Range Memory Allocation float(n) 1-24 4 bajty float(n) 25-53 9 bajt
Numeric Numeric(5,2) = 99.99
String Data Types - MySQL Char(m), m=0-255, longer will be lost Varchar(m), m=0-255 Tinyblob - 0 to 255 Bytes Blob - 0 to 65535 Bytes Mediumblob, Longblob TinyText - 0 a 255 bajt Text, MediumText, LongText Enum - enumeration values; the values in the column can be assigned just one value from a list of values
Date Data Types DATE TIME DATETIME SMALLDATETIME
Date Microsoft SQL Server Data type Range Memory Allocation date 0001-01-01 to 9999-12-31 3 bytes Time 00:00:00.000 to 23:59:59.999 5 bytes Rozsah pro datum 01.01.1753 a 31.12.9999 Rozsah pro as 00:00:00 a 23:59:59.997 Datetime 8 bytes Range for Date 01.01.0001 to 31.12.9999 Range for time 00:00:00 to 23:59:59.9999999 According precission from 6 to 8 bytes Datetime2(n) Range for Date 01.01.0001 to 31.12.9999 Range for time00:00:00 to 23:59:59.9999999 Time zone-14:00 a +14:00 Datetimeoffset 10 bytes Range for Date 01.01.1900 to 06.06.2079 Range for time 00:00:00 to 23:59:00 Smalldatetime 4 bytes
Others Data Types MS SQL Bit 1/0, memmory allocation - 1 bit Timestamp It is a data type that generates unique binary number, which is used for version resolution lines "rowversion". This data type does not store date and time! To store the date and time should be used datetime2. In memory occupies 8 bytes. Uniqueidentifier It is a data type that, for example, in conjunction with the function NEWID () generates a unique identifier. In memory it takes up 16 bytes.
Value NULL Value, which is not intended Property of the column: NULL/NOT NULL Example in SELECT: SELECT * from table where column_name IS NULL;
Primary Key Primary Key defined over one or more columns of the table. The main purpose of a primary key is ensure the uniqueness of records in a table. Primary key values can not contain duplicate or null.
Automatically generated number MySQL AUTO_INCREMENT MS SQL IDENTITY Oracle sequence Access Automatic Number create table Table_name ( column_name int IDENTITY(1,1) primary key,
Default Value Apply if during operation insert the value is not determined If we want to avoid NULL values
Column Properties UNIQUE NULL/NOT NULL PRIMARY KEY DEFAULT
Converting Data Types CONVERT CAST CAST ( $157.27 AS VARCHAR(10) ) Syntax for CAST: CAST ( expression AS data_type [ (length ) ]) Syntax for CONVERT: CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
