Uncategorized

SQL Data types

Data types may have different names depending on the type of database in use.

MySQL is the database of choice in this context. There are three data types mainly used in MySQL string data types and date and time types and numeric types.

MySQL string data types:

Data type

Description
CHAR()IT is comprised of a fixed set of strings and may contain other data types like numbers, letters and special characters. Parameters specified, states the column length in characters. It ranges from 0 to 255.
Varchar()

It’s a variable-length string that can contain other data types like special characters, numbers and letters. The parameter passed in specifies the max column length in characters. It ranges from 0 to 65535

Binary( )

It stores data in binary byte strings. The parameters passed specifies the column length.  
Varbinary( )

Same as Varchar(), only that it stores binary strings of byte data. The passed parameters specify the max length of columns in bytes.

textIt is used to store any type of text data

Numeric Data Types

BIT( )

It’s a bit value data type. The total number of bits per value is set out in the parameters. The parameter can hold a value between 1 and 64.

BOOLNumeral zero is usually identified as a false value and nonzero values are identified as true values.

SMALLINT( )

A small integer value. The unsigned range is from 0 to 65535 while the Signed range is from -32768 to 32767. The parameters spell out the max display width (normally 255)

MEDIUMINT( )

Represents a medium integer. The signed range is from -8388608 to 8388607. The unsigned range is from 0 to 16777215. The passed parameter specifies the max display width ( normally 255)

INT( )

Represents a medium integer. The unsigned range is from 0 to 4294967295 while the Signed range is from -2147483648 to 2147483647. The parameter specifies the max display width ( normally 255)

INTEGER( )

Same as the int()

FLOAT()

It’s a floating point digit. MySQL makes use of the parameter value to identify if to use data types DOUBLE or FLOAT in the resulting data type. If the parameter ranges from 0 to 24, the data type automatically becomes FLOAT() and if the parameter ranges from 25 to 53, the data type is set to DOUBLE()

DECIMAL()


Represents an exact fixed-point number. The total count of digits is specified in the first parameter. And the number of digits after the decimal point is spelt out the second parameter.

Date and Time Data Types

DATE

Represents a date. Format of, YYYY-MM-DD. The default inbuilt range from ‘1000-01-01’ to ‘9999-12-31’

Timestamp()

The timestamp data type has both the date part and time portion.

TIME( )

The time data type comes in the format of hh:mm:ss. The default inbuilt range is from -838:59:59 to  838:59:59

YEARThe year data type comes in a four-digit format. Values range from 1901 to 2155, and 0000.

SQL Operators

Arithmetic operators and their meaning in SQL

OperatorDescription
Subtract
*Multiply
+Add
%Modulo
/ Divide

Comparison operators in SQL

=Equal to
Greater Than
Less than

Bitwise operators in SQL

|OR
&AND
+=Add equals
-=Subtract equals
*=Multiply equals

Logical Operators in SQL

BETWEENReturns a TRUE only when all the operands are within the specified range of comparisons.
ANDReturns a TRUE only when all the conditions are separated by AND returns a TRUE
EXISTSReturns a TRUE only when the subquery returns one or several records
ANYReturns TRUE only when any of the subquery values meet the condition.
NOTReturns a record only when the condition or set of conditions is not true
ORReturns a  true only when any of the conditions separated by ‘OR’ returns a TRUE
INReturns a TRUE only when the operand is equal to one of a list of the stated expressions.
LIKEReturns a TRUE only when the operand matches a certain predefined pattern

You might also like

Leave a Reply

Your email address will not be published. Required fields are marked *