Difference between revisions of "MySQL Schema and State"

From CSE330 Wiki
Jump to: navigation, search
(Schema)
(Data Types)
Line 53: Line 53:
 
* Text types differ from character types in that behind the scenes, they are not actually stored with the rest of the table row; they are useful for longer blocks of text, like forum posts.
 
* Text types differ from character types in that behind the scenes, they are not actually stored with the rest of the table row; they are useful for longer blocks of text, like forum posts.
 
* Blobs are for binary data, like files.
 
* Blobs are for binary data, like files.
* Enumerables are for something that could have one of a set number of values (not more than 65535), like what State a user is from.  An enum is actually stored as an integer in the database, making queries more efficient.
+
* Enumerables are for something that could have one of a set number of values (not more than 65535), like what State a user is from (Alabama, Alaska, and so on).  Enums are also useful for storing boolean data, like '''ENUM('yes','no')'''.  An enum is actually stored as an integer in the database, making queries more efficient.
  
 
The syntax for these data types is:
 
The syntax for these data types is:
Line 62: Line 62:
 
* '''TEXT''', a text block at most 65535 characters long
 
* '''TEXT''', a text block at most 65535 characters long
 
* '''BLOB''', a binary string at most 65 KB in length
 
* '''BLOB''', a binary string at most 65 KB in length
* '''ENUM('a', 'b', 'c')''', an enumerable with choices a, b, or c
+
* '''MEDIUMBLOB''', a binary string at most 16 MB in length
 +
* '''LONGBLOB''', a binary string at most 4 GB in length
 +
* '''ENUM('a', 'b', 'c')''', an enumerable with choices a, b, and c
  
 
===== Numeric Data Types =====
 
===== Numeric Data Types =====
Line 76: Line 78:
 
* '''INT''', an integer ''n'' where -2,147,483,648 ≤ n ≤ 2,147,483,647
 
* '''INT''', an integer ''n'' where -2,147,483,648 ≤ n ≤ 2,147,483,647
 
* '''INT UNSIGNED''', an integer ''n'' where 0 ≤ n ≤ 4,294,967,295
 
* '''INT UNSIGNED''', an integer ''n'' where 0 ≤ n ≤ 4,294,967,295
* '''INT''', an integer ''n'' where -9,223,372,036,854,775,808 ≤ n ≤ 9,223,372,036,854,775,807
+
* '''BIGINT''', an integer ''n'' where -9,223,372,036,854,775,808 ≤ n ≤ 9,223,372,036,854,775,807
* '''INT UNSIGNED''', an integer ''n'' where 0 ≤ n ≤ 18,446,744,073,709,551,615
+
* '''BIGINT UNSIGNED''', an integer ''n'' where 0 ≤ n ≤ 18,446,744,073,709,551,615
 
* '''DECIMAL(#a, #b)''', a decimal number at most ''#a'' digits long, ''#b'' of which are after the decimal point.  For example, ''DECIMAL(5,2)'' can contain ''n'' where -999.99 ≤ n ≤ 999.99
 
* '''DECIMAL(#a, #b)''', a decimal number at most ''#a'' digits long, ''#b'' of which are after the decimal point.  For example, ''DECIMAL(5,2)'' can contain ''n'' where -999.99 ≤ n ≤ 999.99
  
In general, when choosing the size of an integer, you should use the ''smallest integer for which you know you will never "run out of room"''.
+
When choosing the size of an integer, you should use the ''smallest integer for which you know you will never "run out of room"''.  In most everyday circumstances, '''MEDIUMINT''' should be the biggest you'll need.
  
 +
===== Temporal Data Types =====
  
 +
MySQL provides a class of data types that specialize in handling dates and times.  The syntax for using date types is:
 +
 +
* '''DATE''', a date ''d'' where <code>1000-01-01</code> ≤ d ≤ <code>9999-12-31</code>
 +
* '''DATETIME''', a date with time ''d'' where <code>1000-01-01 00:00:00</code> ≤ d ≤ <code>9999-12-31 23:59:59</code>
 +
* '''TIMESTAMP''', a date with time ''d'' where <code>1970-01-01 00:00:01 UTC</code> ≤ d ≤ <code>2038-01-19 03:14:07 UTC</code> (note: <code>1970-01-01 00:00:01 UTC</code> is the Unix epoch)
 +
* '''TIME''', a length of time ''t'' where <code>-838:59:59</code> ≤ t ≤ <code>838:59:59</code> (note: you cannot store time to a precision greater than 1 second)
 +
* '''YEAR''', a year ''y'' where 1901 ≤ y ≤ 2155
 +
 +
Note that '''TIMESTAMP''' is a highly versatile data type that can automatically change whenever a row is updated.  For more information, refer to [http://dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html the MySQL documentation].
  
 
<source lang="mysql">
 
<source lang="mysql">

Revision as of 19:27, 22 August 2012

MySQL is a powerful database that allows for complex logic when manipulating data. This guide is an introduction and a reference to common SQL queries for schema and state manipulation.

This guide assumes that you already have a working copy of MySQL. If you don't, read Introduction to MySQL first.

Schema

The schema of your database refers to the way that your data is structured and to how bits of data relate to each other. Every database in your MySQL server has a separate schema, and separate databases should not typically interact with one another.

Databases

To create a database on your MySQL server, simply run the query:

create database DATABASENAME;

You can delete a database using the query:

drop database DATABASENAME;

When you're at the MySQL prompt, use the following query to select a database to which to run select, insert, update, and delete queries:

use DATABASENAME;

Tables

A database contains one or more tables. Tables contain columns and rows, which you may sometimes call fields and entries.

When you create a table, you should provide information about all of that table's columns within the create table query. Here is the general syntax for the query:

create table TABLENAME (
	FIELDNAME DATATYPE OPTIONS,
	FIELDNAME DATATYPE OPTIONS,
	FIELDNAME DATATYPE OPTIONS,
	FIELDNAME DATATYPE OPTIONS
) TABLE_OPTIONS;

Data Types

Just like a programming language, MySQL has data types. There are dozens of data types; some of the most common ones are documented below.

String Data Types

There are four main types of string data types: character, text, blob, and enumerable.

  • Character types are useful for short strings, like usernames and e-mail addresses.
  • Text types differ from character types in that behind the scenes, they are not actually stored with the rest of the table row; they are useful for longer blocks of text, like forum posts.
  • Blobs are for binary data, like files.
  • Enumerables are for something that could have one of a set number of values (not more than 65535), like what State a user is from (Alabama, Alaska, and so on). Enums are also useful for storing boolean data, like ENUM('yes','no'). An enum is actually stored as an integer in the database, making queries more efficient.

The syntax for these data types is:

  • VARCHAR(#), a string at most # characters long, where 0 ≤ # ≤ 255
  • CHAR(#), a string exactly # characters long, where 0 ≤ # ≤ 255
  • TINYTEXT, a text block at most 255 characters long
  • TEXT, a text block at most 65535 characters long
  • BLOB, a binary string at most 65 KB in length
  • MEDIUMBLOB, a binary string at most 16 MB in length
  • LONGBLOB, a binary string at most 4 GB in length
  • ENUM('a', 'b', 'c'), an enumerable with choices a, b, and c
Numeric Data Types

There are two types of numeric data types that you need to worry about: integers and decimals. The difference between the two should be pretty self-explanatory. The syntax for using these data types is:

  • TINYINT, an integer n where -128 ≤ n ≤ 127
  • TINYINT UNSIGNED, an integer n where 0 ≤ n ≤ 255
  • SMALLINT, an integer n where -32,768 ≤ n ≤ 32,767
  • SMALLINT UNSIGNED, an integer n where 0 ≤ n ≤ 65,535
  • MEDIUMINT, an integer n where -8,388,608 ≤ n ≤ 8,388,607
  • MEDIUMINT UNSIGNED, an integer n where 0 ≤ n ≤ 16,777,215
  • INT, an integer n where -2,147,483,648 ≤ n ≤ 2,147,483,647
  • INT UNSIGNED, an integer n where 0 ≤ n ≤ 4,294,967,295
  • BIGINT, an integer n where -9,223,372,036,854,775,808 ≤ n ≤ 9,223,372,036,854,775,807
  • BIGINT UNSIGNED, an integer n where 0 ≤ n ≤ 18,446,744,073,709,551,615
  • DECIMAL(#a, #b), a decimal number at most #a digits long, #b of which are after the decimal point. For example, DECIMAL(5,2) can contain n where -999.99 ≤ n ≤ 999.99

When choosing the size of an integer, you should use the smallest integer for which you know you will never "run out of room". In most everyday circumstances, MEDIUMINT should be the biggest you'll need.

Temporal Data Types

MySQL provides a class of data types that specialize in handling dates and times. The syntax for using date types is:

  • DATE, a date d where 1000-01-01 ≤ d ≤ 9999-12-31
  • DATETIME, a date with time d where 1000-01-01 00:00:00 ≤ d ≤ 9999-12-31 23:59:59
  • TIMESTAMP, a date with time d where 1970-01-01 00:00:01 UTC ≤ d ≤ 2038-01-19 03:14:07 UTC (note: 1970-01-01 00:00:01 UTC is the Unix epoch)
  • TIME, a length of time t where -838:59:59 ≤ t ≤ 838:59:59 (note: you cannot store time to a precision greater than 1 second)
  • YEAR, a year y where 1901 ≤ y ≤ 2155

Note that TIMESTAMP is a highly versatile data type that can automatically change whenever a row is updated. For more information, refer to the MySQL documentation.

Keys

State