Mysql Data types in summary.

 

MySQL Commands (Statements)

•       MySQL commands (also referred to as statements) fall into two main categories:

•       Data Definition Language (DDL)

•       Data Manipulation Language (DML)

•       The main Data Definition commands are CREATE, DROP, ALTER & RENAME

•       The main Data Manipulation commands are SELECT, INSERT, UPDATE & DELETE

•       MySQL data types fall into three main categories:

•       string (character)

•       Numeric

•       date/time

•       Each type has a number of subtypes, each with its own:

•       storage requirements

•       behavioural properties

 

MySQL Data Definition Statements

 

CREATE DATABASE (Syntax)

CREATE DATABASE [IF NOT EXISTS] db_name;

CREATE DATABASE IF NOT EXISTS fortytwo; USE fortytwo;

•       Creates a database with a given name.

•       Database naming rules apply.

•       [IF NOT EXISTS] optional but useful ( don’t include parenthesis)

•       Creates directory in MySQL data directory, but not tables.

DROP DATABASE

DROP DATABASE [IF EXISTS] db_name;

•       Deletes a database including all resident tables.

•       USE WITH GREAT CARE!

•       [IF EXISTS] optional but useful ( don’t include parenthesis)

CREATE TABLE

CREATE TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)];

CREATE TABLE IF NOT EXISTS company (

 ABN                            INTEGER(8) UNSIGNED NOT NULL,

 company_name            VARCHAR(50), address        VARCHAR(100), phone         VARCHAR(10), PRIMARY KEY (ABN));

•       Keep syntax neat and tidy by placing each column definition on a separate line, separated by a comma (,).

•       All column names and definitions are contained within a single set of parenthesis ().

•       Complete syntax with a semi-colon (;).

•       Creates table with a given name within the database.

•       Table naming rules apply

•       [IF NOT EXISTS] optional but useful ( don’t include parenthesis)

•       A table cannot be created unless at least one column is also created within the table.

•       Column naming rule:

•       can be up to 64 characters long

•       may contain alpha-numeric characters, an underscore (_) and a dollar sign ($).

•       reserved words and complete numbers must not be used as column names unless enclosed in back-ticks (`).

•       Each column name must be accompanied by a data type

•       Attributes such as NOT NULL, ZEROFILL and UNSIGNED may also be assigned where applicable.

•       A rule of order applies to column attributes, this being that type-specific attributes should always precede general attributes.

•       For example, when a Numeric type is used, the UNSIGNED and ZEROFILL attributes must precede NULL or NOT NULL.

ALTER TABLE

ALTER [IGNORE] TABLE tbl_name alter_spec [, alter_spec ...];

•       Changes the structure of an existing table.

•       Add/delete columns, create/destroy indexes, change column types & rename columns.

•       Alter specifications.

•       [IGNORE] optional but useful ( don’t include parenthesis)

RENAME TABLE

RENAME TABLE tbl_name TO new_tbl_name;

•       Renames existing table.

•       Can be used to rename multiple tables:
RENAME TABLE dog TO cat, up TO down, in TO out;

•       Tables cannot be renamed to a name that is already in use.

DROP TABLE

DROP TABLE [IF EXISTS] tbl_name [, tbl_name,...] ;

DROP TABLE IF EXISTS company;

•       Removes one or more tables.

•       All table data is also deleted so USE WITH GREAT CARE!

•       [IF EXISTS] optional but useful ( don’t include parenthesis).

•       Removes a table and all it’s objects from the MySQL data directory.

•       CAREFUL!!! Unless transactional tables are being used in conjunction with the BEGIN and COMMIT commands, this action is permanent.

CREATE INDEX

CREATE [UNIQUE|FULLTEXT] INDEX index_name ON tbl_name (col_name[(length)],... );

•       Allows the adding of indexes to a current table.

•       Indexes can also be added to an existing table within the ALTER TABLE syntax.

•       Indexes can also be created at the time of table creation within the CREATE TABLE syntax.

DROP INDEX

DROP INDEX index_name ON tbl_name;

•       Drops a designated index from a table.

•       Indexes can also be dropped from a table within the ALTER TABLE syntax.

 

MySQL Data Manipulation Statements

 

SELECT

SELECT column_1 [,column_2,…] FROM tbl_name [WHERE condition];

SELECT * FROM workstation;

SELECT computerID, computerDescription FROM workstation WHERE computerDescription = ‘Apple Cube’;

SELECT computerID, computerDescription
FROM workstation
WHERE computerID <= 10;Displays the contents of every column for every tuple in the table indicated.

•       Used to retrieve rows selected from one or more tables.

•       FROM indicates the tables from which to retrieve rows.

•       WHERE indicates conditions that rows must meet to be returned.

INSERT

INSERT INTO tbl_name (first_column,...last_column) VALUES (first_value,...last_value);

INSERT INTO company

            (ABN, company_name,address,phone)VALUES

            (10723485,'PIBT','24 Pearson St Churchlands', '0892758976');

•       First the columns into which data is to be entered are named. The data values to be placed into these columns are then named in the SAME order.

•       Literals are typed in without inverted commas (‘ ‘). Strings are typed in with inverted commas, eg. (‘PIBT’) for company_name.

            Aka

INSERT INTO company

VALUES

            (10723485,'PIBT','24 Pearson St Churchlands', '0892758976');

•         In the event that all columns in a tuple are to receive data, and the data is stipulated in the same order that those columns appear in the table, stating the column names first is not necessary.

•       Inserts new rows into an existing table.

•       The columns into which the values are to be inserted are named first.

•       VALUES indicates the data to be entered into the named columns.

•       Rule of order applies.

•       When using the full version of the INSERT statement,
INSERT INTO tbl_name (column1, column2…)
            VALUES (value1, value2…);
the values must appear in the same order as they appear in the column name declarations preceding them.

•       When the shorthand version of the INSERT statement is being used, a value must be declared for every column in the table and in the same order as the columns appear in the table.

•       Value data type must match the data type of the column into which it is being entered.

•       In the event that values are only being entered into some, not all, of a tables columns, the omitted columns must either be set to NULL or to AUTO_INCREMENT if numeric.

•       String and date values must be entered in single (‘) or double (“) quotes.

UPDATE

UPDATE tbl_name SET column_name = new_value [,next_column = new_value_2,...] WHERE column_name operator value [and|or column operator value];

UPDATE employee SET lastName = 'Skywalker' WHERE firstName = 'Darth';

In the table called ‘employee’, find the tuple that contains the string ‘Darth’ and in that same tuple, change the string in ‘lastName’ from whatever it currently is to ‘Skywalker’.

•       Warning: UPDATE statements cannot be undone once executed so run a SELECT statement with the same WHERE clause first to make sure you know how many rows should be affected!!!

•       Updates columns in existing table rows with new values.

•       SET indicates the columns that will be affected by the change.

•       WHERE indicates which rows will actually be changed in the fields indicated.

•       Operators are usually mathematical in nature (=, >, < etc).

DELETE

DELETE FROM tbl_name [WHERE column_name operator value]
[and|or column operator value];

DELETE FROM employee WHERE lastName = 'Mouse';

•       Deletes rows from table that satisfy the condition given by WHERE.

•       DELETE with no WHERE clause results in all rows being deleted.

•       Where tables reference one another via primary-foreign key relationships and InnoDB has not been applied to these tables, data anomalies are likely to result from data deletions.

 

 

MySQL Column (Data) Types

•       The string data type is designed to store a range of data objects other than those that are either numbers or dates.

•       Although the string data type is most commonly used for its ability to store character data, its generality allows it to hold data objects that are not associated with any particular character set.

•       Data objects that are not associated with any particular character set are known as binary strings. Binary strings are stored and processed on a byte-by-byte basis.

•       Data objects that are associated with a character set (such as ASCII, EBCDIC, Unicode etc) are known as non-binary strings. These are stored and processed on a character-by-character basis.

 

String Data Types

•       MySQL uses six (6) primary string types, these being CHAR, VARCHAR, BLOB, TEXT, ENUM and SET.

•       The BLOB string type is comprised of four (4) specific types, these being the TINYBLOB, BLOB, MEDIUMBLOB and LONGBLOB.

•       The TEXT string type is also comprised of four (4) specific types, these being TINYTEXT, TEXT, MEDIUMTEXT and LONGTEXT.

MySQL Column Types – Strings

•         The string types are:

•         CHAR

•         VARCHAR

•         BLOB

•         TEXT

•         ENUM

•         SET

CHAR

•       Commonly used string type.

•       CHAR is a fixed-length string type, ie it takes up a fixed portion of memory, even if the size of the value entered is smaller.

•       Values entered of a size less than a CHAR’s declared length (M) is stored with padding spaces to the right.

•       The scale (M) declaration for CHAR is optional. If left out, it defaults to 1.

•       Allowable size range is 1 to 255.

VARCHAR

•       Another commonly used type of string.

•       VARCHAR is a variable-length string type. As such, values entered into a column declared VARCHAR only occupy the amount of memory required to store them (M + 1 byte).

•       When the value entered in a VARCHAR column is less than its declared maximum size, the trailing spaces are removed before storage in memory.

•       The scale (M) declaration for VARCHAR is compulsory.

•       Allowable size range is 1 to 255.

CHAR vs VARCHAR – Key Points

•       Use CHAR when values entered into a column have little variance, especially if MyISAM or ISAM tables are being used. In this case, rows are processed more efficiently.

•       Use VARCHAR when values entered into a column have significant variance, as this uses less memory space (an key processing-efficiency consideration).

•       Do not mix CHAR and VARCHAR within the same table. In cases where this is done, MySQL usually alters the CHAR column to a VARCHAR without notification.

•       Further, CHAR cannot be used in tables that also contain BLOB or TEXT columns, for these too are variable length string types.

CHAR, VARCHAR – Syntax

In this case, as the unit code is always an alph-numeric mix, seven characters in length (eg UI101), CHAR is the most appropriate string type to use. Note that no variable-length string types exist within the table.

CHAR

CREATE TABLE unit_det (
            unit_code CHAR(7),
            date_enrolled DATE);

As the length of student addresses will always vary greatly, VARCHAR is the most sensible string type to use. The difference between address length and declared max (100) will be stripped off at storage time.

VARCHAR

CREATE TABLE stud_det (
            stu_num INT(8),
            stu_add VARCHAR(100));

BLOB

•       The BLOB type is designed to hold binary strings.

•       These can include images, sounds and a range of other file types that are not associated with a particular character set.

•       BLOB is versatile in that it can store data objects ranging from a very small size to a very large size.
(4Gbytes max in theory, 1Gbyte max in practice).

•       BLOB takes four forms, which vary only in capacity.

•       BLOB values are case sensitive.

BLOB – Type Capacities

•       The BLOB faimily has four (4) members, these being:

–       TINYBLOB

–       BLOB

–       MEDIUMBLOB

–       LONGBLOB

 

 

TEXT

•       The TEXT string type is very similar to BLOB, except that it is solely concerned with non-binary string data objects (those associated with specific character sets), along with specific operations that pertain to these.

•       TINYTEXT

•       TEXT

•       MEDIUMTEXT

•       LONGTEXT

 

•       Their capacities are shown in the table below:

BLOB vs TEXT – Key Points

•       Choose BLOB and TEXT types carefully, as the larger capacity types take up a great deal of memory space. Set columns to the specific type that will adequately cover the anticipated size of the objects to be placed into them, and no more.

•       BLOB and TEXT columns can be used as indexes, but only in the following circumstances:

–        The table concerned is of the MyISAM type. InnoDB and ISAM do not support indexing of these data types.

–        A prefix size is specified. Indexes only work effectively if they are small in size (this facilitates rapid processing). As BLOB and TEXT fields can be extremely large, using them in their entirety negates a column’s value as an indexing instrument.

BLOB

In this case, students can upload a variety of files to a unit database. These objects can include images or sounds. The system is set up to accept files < 16Mbytes.

CREATE TABLE stud_contr (
            contr_num CHAR(7),
            contr MEDIUMBLOB);

In this case, the database is the back-end of a news article management system. Articles are always between 300 and 1,000 words in length and never exceed 64Kbytes in size.

TEXT

CREATE TABLE articles (
            art_num INT(5),
            article TEXT);

ENUM

•       An ENUM ( enumeration) is a string object whose contents is normally chosen from a list of allowed values that are enumerated ( numbered, listed) explicitly in the column specification at table creation time.

•       Only one legal value can occupy an ENUM column at any point in time. In other words, the selection must be mutually exclusive. A legal value is one chosen from the developer’s defined list of options.

•       Values from the list of allowable elements in the column specification are numbered beginning with 1.

•       An enumeration can have a maximum of 65,535 elements.

Example 1
A column that records if a loan applicant has taken out a loan with the bank on a previous occasion.

previous_loan ENUM(‘No’, ‘Yes’);

Example 2
A column records the color of product that a customer has elected.

product_colour ENUM(‘red’, ‘blue’, ‘green’);

 

SET

•       A SET is a string object that will allow zero or more values to exist within it, each of which must be chosen from a list of allowed values specified when the table is created.

•       Values in a SET are not mutually exclusive, ie more than one value can be selected.

•       SET column values that consist of multiple set members are specified with members separated by commas (,). A consequence of this is that SET member values cannot themselves contain commas.

•       A SET can have a maximum of 64 different members.

Example 1
A column that records the peripherals a customer would like to bundle with the purchase of a PC.

opt_ext_req SET(‘printer’, ‘scanner’, ‘modem’);

Example 2
A column that records those titles a customer would like to include in their bulk subscription.

titles_req SET(‘APC’, ‘PC Authority’, ‘Mac User’, ‘Computer World’, ‘IT Monthly’, ‘Linux World’);

 

Numeric Data Types

•       Numeric data types fall into two broad categories, these being integer values and floating-point values.

INTEGER: An integer is a numeric value that does not use a decimal point. In other words, it has no fractional component, eg 64, 127, 2116.

FLOATING-POINT: A floating-point value has both a whole and a fractional component, which are separated by a decimal point, eg 27.63, 0.2489, 26.0

Numeric

Exact:Numeric,Decimal,Integer

Approximate: Float,Real,Double Precision

The NUMERIC and DECIMAL types are implemented as the same type by MySQL, as permitted by the SQL92 standard. They are used for values for which it is important to preserve exact precision, for example with monetary data. When declaring a column of one of these types the precision and scale can be (and usually is) specified; for example:

salary DECIMAL(5,2)

•       The whole component of a floating-point value is known as its precision, which is represented as M.

•       The fractional component of a floating-point value is known as its scale, which is represented as D.

salary FLOAT(4,2); precision (M)        scale (D) 4 is precision 2 is scale

•       A value of 1 to 255 can be stipulated for M.

•       A value of 0 to 30 can be stipulated for D.

•       Both of these value ranges refer to maximum display size, not the amount of memory required to store the value.

•       To allow fine control over the way in which these broad numeric categories can be used and stored, they are divided into several specific types, each with their own unique properties. These are shown below:

Integer:TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT

Floating-Point:FLOAT,DOUBLE,DECIMAL

 

Caution with Integers

•       Oracle only has one Integral numeric type which is called “INTEGER”

•       If you look at sql code and any of the columns have the type of “INTEGER”, then this code has been adapted from Oracle, and should be modified to use MySQL integral types

•       Note that if you do not amend the column types then MySQL will parse the code, but will waste space by making all integral values use up 4 bytes, when a smaller integer type will often be sufficient

•       Also note that inappropriate column types may cost you marks in the second assignment!

Numeric Data Types

TINYINT

•       A very small number.

•       Requires 1 byte (8 bits) of memory storage space.

•       Can accommodate -128 to 127 when negative values need to be used ( signed values).

•       When negative values will not be used ( unsigned values), TINYINT can accommodate values of 0 – 255.

SMALLINT

•       A small number.

•       Requires 2 bytes (16 bits) of memory storage space.

•       -32768 to 32767 when signed.

•       0 to 65535 unsigned.

MEDIUMINT

•       A medium-sized number.

•       Requires 3 bytes (24 bits) of memory storage space.

•       -8388608 to 8388607 when signed.

•       0 to 16777215 unsigned.

INT

•       A standard number.

•       Requires 4 bytes (32 bits) of memory storage space.

•       -2147683648 to 2147683647 when signed.

•       0 to 4294967295 unsigned.

BIGINT

•       A large number.

•       Requires 8 bytes (64 bits) of memory storage space.

•       -263 to 263-1 when signed.

•       0 to 264-1 unsigned.

FLOAT

•       A single-precision floating-point number.

•       Requires 4 bytes (32 bits) of memory storage space.

•       Range expressed as minimum and maximum non-zero values.

•       When expressed using scientific notation, equivalent to:
+/- 1.175494351E-38 (min) to +/- 3.402823466E+38 (max)

•       Appropriate for most standard floating-point requirements.

DOUBLE
(aka DOUBLE PRECISION, REAL)(also know as real)

•       A double-precision floating-point number.

•       Requires 8 bytes (64 bits) of memory storage space.

•       Range expressed as minimum and maximum non-zero values.

•       When expressed using scientific notation, equivalent to:
+/- 2.2250738585072014E-308 (min) to
+/- 1.7976931348623157E+308 (max)

•       Used for exceptionally high-precision requirements or extremely large ranges of values.

DECIMAL
(aka NUMERIC)

•       A floating-point number represented as a string.

•       Memory storage space required varies according to the precision (M) and scale (D) declared.

•       Range also dictated by M and D declared.

•       Example:- DECIMAL(4,1) allows range of:
            -999.9 to 9999.9 (minus sign included in M)

•       DECIMAL is especially useful for currency calculations.

 

Numeric Data Types –Column Attributes

ZEROFILL

•       Adds leading zeros to the unused display width of a column containing a numeric data type.

•       Can be used with ALL numeric data types.

•       Used to achieve display consistency within a column, i.e., ensures the same minimum number of digits in every tuple of a particular column.

•       In the event that an entered value exceeds the ZEROFILL stipulation, the column display expands accordingly.

•       Always fill in 0 to before value

mysql>CREATE TABLE stud_reg (
      =>stud_num INT(8) ZEROFILL);

mysql>INSERT INTO stud_reg VALUES 1;
mysql>INSERT INTO stud_reg VALUES 10;
mysql>INSERT INTO stud_reg VALUES 100;
mysql>INSERT INTO stud_reg VALUES 1000;

mysql>SELECT stud_num FROM stud_reg;

UNSIGNED

•       Disallows negative values.

•       Commonly applied to integer types.

•       Should be used when entered values will never be negative, eg days a student attends a course throughout a year.

•       If not used in this circumstance, leads to memory wastage and a reduced range of possible values that can be used. This is an important database design consideration.

UNSIGNED not used

CREATE TABLE stud_reg (
=>stud_num INT(8) ZEROFILL,
=>days_att TINYINT);

As TINYINT has not be specified as UNSIGNED, it is signed, ie has a negtaive component to its range as well as positive. This range is as follows:

-128 to 127

However, as this column will only ever have positive values entered into it, this negative range will take up memory space, yet never be used.

UNSIGNED used

CREATE TABLE stud_reg (
=>stud_num INT(8) ZEROFILL,
=>days_att TINYINT UNSIGNED);

As TINYINT has now been specified as UNSIGNED, it only has a positive component. Its range is now as follows:

0 to 255

The range of usable values has now effectively doubled and memory wastage has been avoided.

AUTO_INCREMENT

•       Used to create unique identifier values serially.

•       Can be stipulated for integer column types only.

•       Generally commences at 1 and increments by 1.

•       Automatically generates the next unique identifier in a series when a NULL value is entered into the column to which AUTO_INCREMENT is applied.

•       AUTO_INCREMENT only applied to one column per table.

•       Columns to which the AUTO_INCREMENT attribute is assigned should also:

•       Be set to NOT NULL*

•       Be declared a Primary Key or Unique Key

•       Be declared UNSIGNED

Example

CREATE TABLE stud_reg (stud_num INT(8)UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY (stud_num));

 

* MySQL Version 3.23 and later automatically treat AUTO_INCREMENT columns as being NOT NULL.

 

 

Date and Time Data Types

•       MySQL provides five (5) data types for temporal values.

•       These are DATE, DATETIME, TIME, TIMESTAMP and YEAR.

•       Each temporal data type has its own legal range of values and storage requirements.

•       MySQL represents dates year first, eg 10 July, 2003 is represented in the database as 2003-07-10.

•       Temporal data types are selected according to criteria such as storage efficiency, required precision of date-related calculations and the need for brevity.

Date/Time

•       The date/time types are:

•       DATETIME

•       DATE

•       TIMESTAMP

•       TIME

•       YEAR

Column type     Zero value

DATETIME     '0000-00-00 00:00:00'

DATE '0000-00-00'

TIMESTAMP 00000000000000 (length depends on display size)

TIME  '00:00:00'

YEAR 0000

 

DATE

•       Legal range of values ‘1000-01-01’ to ‘9999-12-31’.

•       Requires three (3) bytes of memory storage.

•       Represented in alpha format as CCYY-MM-DD where CCYY (C=century, Y=year) represents the year, MM represents the month and DD represents the day.

•       Zero value is ‘0000-00-00’.

•       The zero value is inserted when an illegal entry is made, or when no value is entered but the NOT NULL attribute has been set.

TIME

•       Legal range of values ‘-838:59:59’ to ‘838:59:59’.

•       Requires three (3) bytes of memory storage.

•       Represented in alpha format as hh:mm:ss where hh represents the hour, mm represents the minute and ss represents the second.

•       Zero value is ’00:00:00’.

•       Represents elapsed time rather than fixed time, thus explaining the large range of possible values.

DATETIME

•       Legal range of values
            ‘1000-01-01 00:00:00’ to
            ‘9999-12-31 23:59:59’

•       Requires eight (8) bytes of memory storage.

•       Represented in alpha format as
            CCYY-MM-DD hh:mm:ss

•       Zero value is ‘0000-00-00 00:00:00’.

TIMESTAMP

•       Legal range of values ‘1970-01-01 00:00:00’ to an uncertain point in ‘2037’

•       Requires four (4) bytes of memory storage.

•       Zero value is ‘00000000000000’ for TIMESTAMP(14).

•       TIMESTAMP(M) has seven specifications these being: TIMESTAMP(2|4|6|8|10|12|14).

•       TIMESTAMP(2) is equivalent to YY.

•       TIMESTAMP(14) is equivalent to CCYYMMDDhhmmss.

•       TIMESTAMP defaults to (14) when M is not stated.

YEAR

•       Legal range of values ‘1901 to 2155’ if YEAR(4) is stipulated, ‘1970 to 2069’ if YEAR(2) is stipulated.

•       Requires one (1) byte of memory storage.

•       Zero value is ‘0000’ for YEAR(4).

•       YEAR(2) only displays last two digits, ie YY.

•       YEAR(4) displays as four digits, ie CCYY.

•       YEAR defaults to (4) when M is not stated.

Considerations When Selecting Column Data Types

•       Determine the data type of the values a column will hold.

•       If numeric, determine if negative values will be required. If not, use the UNSIGNED attribute.

•       Determine how data type selection will impact upon processing performance. For example, if CHAR is used when values will vary in size considerably, memory wastage is likely to occur. Use VARCHAR instead.

•       Determine if values will be subject to comparison. If string objects are to be compared for example, determine if this will be done on a case sensitive or non-case sensitive basis.

•       Determine if a column is to be indexed. Not all data types make ideal indexes. For example, BLOB does not make an ideal index, but if used, needs to have special constraints applied to it.

•       Generally speaking, always look for the long term consequences column data type selection will have upon the efficiency and stability of a database’s operation.

 Take Notes For  learning purposes