Data types in database with description

Data types

   

Mysql can handle the following data types:

 Numeric values

String values

Data and time values

The null values

Numeric column types

 The mysql can represent both integers (number with no fractional part) and floating point numbers (those with fractional part).

Numeric column attributes

   When declaring a numeric column not only can the column name and type be specified but also some column attributes.

Column_ name column_ type [column _attributes] [genersl_arribute]

Zero fill: it can be used with any numeric column type. Whenever displaying a value of a column with zero fill specified “the output will be padded with leading zero up to the display width of the column.

Auto_ increment

  We can use the auto_ increment attribute to generate a unique alto numbered identifier.

  Mysql permits only one auto_ increment column per table and requires that it is also declared as a key.

‘By default an auto increment column will have it’s start of sequence valueset to 1. you can override this start of sequence number in your table declaration by setting _increment =number.

Create table invoices (

 Invoice_num int auto_ increment not null primary key”

Amount decimal (9, 2)

Auto increment= 100000

Char defines a fixed length column type, wheras varchar and the text and blob types are variable length types.

Char and Varchar are intended for small strings up to 255 characters long, whereas text and blob types are intended for longer strings.

CHAR AND VARCHAR COLUMN TYPES:

 The char and Varchar column types both take the m parameter, which sets the maximum length permissible. M must be between 1and 255.

  The main difference between char and varchar is the way they use their storage . a char(m) type always  uses M bytes of storage ,value being padded out to the right with spaces to the specified length ,m. when you retrived a value from a char field , the traling spaces will be removed.

BINARY COLOMN ATTRIBUTES;

   We can specify binary column attribute for char and varchar columns. Thos causes values ih those column to be treated as binary strings.

 Values in those columns will then be handled in a case sensitive way in comparision and sorting operation.

BLOB AND TEXT COLUMN TYPES :

 BLOB AND TEXT ARE FAMILIES OF COLUMN TYPES INTENTED AS HIGH CAPACITY BINARY AND TEXTUAL STORAGE.

 Blob means binary large object and this family of types is intended for storing binary data. Whereas text types are intended for large amounts of text.

ENUM AND SET COLUMN TYPES;

 The enum ans set column types can be used to hold column values chosen from a given set of strings. The possible strings are declared at table creation time, and after that only these values may be inserted into the column.

ENUM

 We can use enum column type where we want the column to contain exactly one of the allowed members specified at table creation time.

      Create table my table (

          Number enum (“one”, “two”,” three”,) null)

The enum column would be allowed to hold any one of the string values: null,””,”one”,”two”, and “three”.

For each enum column a corresponding index exits which is a numeric representation of the number of each possible member, starting from 1.

 SET

 The set column type work’s in much the same way as enum. However, enum can hold only one of the enumerative members; SET can hold any number of members, including all of them.

CREATE TABLE PIZZA (

Topping set (pepperose”,”prawns”, anchovies”,) null)

Insert into pizza values (““),

(“pepperose”,”anchovies”)

DATE

   Date values can range from 1000-01-01 to 9999-12-31 and occupy 3 byres of storage. we can specify dates as yyy-mm-dd, yyyy/mm/d or almost any punctuates separating the components of date .

TIME

    Time values occupy 3 bytes of storage .time values can range not just from 00:00:00 to 23:59:59 but also can include negative time; in fact from 838:59:59 to 838:59:59. This allows us to represent not just time of day but also the lapsed time between two events, or even degrees, minutes and Seconds of longitude and latitude.

We can use any punctuation marks as delimiters to separate the hours, minutes and seconds. Thus 08:32:20 could be represented as 08-32-20 or we can do without leading zero as 8-32-20.

We can specify time delimiters as is 08_32_20 or 83220. mysql reads time from the right expecting seconds to be declared but not  necessarily pairs . So 8-32 or 0832 will be interpreted as 8 minutes 32 seconds.

 

 TIMESTAMP; is a useful field format where by a timestamp column will be set to the current system date and time whenever that row is updated or inserted into the table

 A timestamp column occupies 4 byres of storage and although it can be declared to display between 2 and 14 digits of date and time, it always hold the same data internally.