by Curtiss   |   last updated April 11, 2012   |   programming · Software ·

I didn’t take any notes during the first day of class, as we really only went over a lot of introductory information. However, I started taking notes at the beginning of day two, and tried to be pretty thorough. I hope they help a bit.

Day 2

Data Type Overview

Three Major Categories:

• Numeric
  • Integer
    • Tinyint
      • 1 byte
    • Smallint
      • 2 bytes
    • Mediumint
      • 3 bytes
    • Int
      • 4 bytes
    • Bigint
      • 8 bytes
      • Bigints are used for all internal mathematic operations within MySQL
  • Floating-point (approximate numbers)
    • Float (4 bytes)
    • Double (8 bytes)
    • Floats are generally inaccurate. For instance, inserting a value of “0.99” will actually create a value of 0.99000000953674
    • Floats will not be limited by the amount of digits you “set” when creating the column. The amount of digits you set is simply used for formatting
  • Fixed-point
    • Decimal
    • Fixed
    • Fixed-point digits will be limited by the amount of digits you “set” when creating the column. For instance, if you specify a column as DEC(4,2), it will not allow you to store the number 100. The largest value it can store is 99.99
  • BIT
    • Column width is the number of bits per value
• Character/Binary (strings)
  • Binary elements are made up of characters, although you don’t generally manipulate the individual characters
  • CHAR – Stores a fixed number of character locations, no matter what value you give it.
    • Limited to a length of 255 characters
    • More efficient in MyISAM, because you are storing a set number of characters each time – also helps prevent fragmentation issues
  • VARCHAR – Stores a variable number of characters, up to the limit that you set when creating the table
    • If the strictness mode is turned on, it will throw an error when attempting to insert a string longer than your limit
    • If strictness mode is turned off, it will truncate the string before inserting it into the database
    • Limited to a maximum length of 65,555 characters
  • TINYTEXT – up to 255 characters
  • TEXT – up to 65,535 characters
  • MEDIUMTEXT – up to 16,777,215 characters
  • LONGTEXT – up to 4,294,967,295 characters
  • ENUM – enumarated values
    • You specify the possible values, which are treated as their numerical equivalents when evaluated
    • You can store up to 65,535 possible values for an enumerated column
    • Only one of the enumerated values can be selected in each row
  • SET – list of string values
    • You specify the possible values, as you do in ENUM
    • However, with SET, you can select multiple values for each row
    • The numerical values of a SET are stored as BIT switches rather than consecutive numbers
• Temporal
  • TIME
    • HH:MM:SS
    • Is not necessarily an indication of time of day. This can accept times longer than 23:59:59, because it is simply an indication of amount of time
    • Can be negative or positive
    • ‘-838:59:59’ to ‘838:59:59’
  • YEAR
    • Two or four digits
    • Indicating a two-digit value will be interpreted in a range from 1970-2069
    • 1901-2155 (for year(4)), 1970-2069 (for year(2))
  • DATE
    • ‘YYYY-MM-DD’ – can only accept dates in this format
    • ‘1000-01-01’ TO ‘9999-12-31’
  • DATETIME
    • ‘YYYY-MM-DD HH:MM:SS’ – can only accept datetimes in this format
    • Time in this data type is limited to time of day. Highest value is 23:59:59 and lowest value is 0
    • ‘1000-01-01 00:00:00 to ‘9999=12-31 23:59:59’
  • TIMESTAMP
    • If given no value when creating or updating a timestamp column, it will automatically assign the current system date/time
    • No point in having more than one timestamp column, as they will all end up with the same value
    • ‘1970-01-01 00:00:00’ to mid-year 2037

You want to choose the most appropriate data type (whichever you will be using in that column the most)

Each table must have at least one column. The columns must have a name and a data type.

Precision and scale of numbers must be considered carefully when creating numeric data types

Numbers must be set up as either signed or unsigned. Signed numbers will use an equal amount of negative and positive values. In other words, a tinyint (1 byte) will range from -128 to 127 if set as signed. Unsigned will range from 0 to 255. If you will not be using negative values, you should set the column as unsigned.

To store a binary number in a column, you should specify it by beginning your value with “b”, then wrapping the value in single quotes. For example: INSERT INTO bits VALUES(b’0101′);

To store a hexidecimal number in a column, you should specify it by beginning your value with “x”.

Character Sets

A character set is a named encoded character

Each character is mapped to a specific number in a character set.

ASCII is the same as Latin1, which is close to the lower bit range of UTF-8

A character set is actually specified on a column-by-column basis within MySQL.

Character set controls sort order, based on the numerical equivalent of each character

Binary String Data Types

Binary strings are strings of characters that are intended to be interpreted as a whole, rather than by character

Binary Types

• Binary
• Variable Binary
• Tinyblob
• Blob
• Mediumblob
• Longblob

No character set or collation is associated with binary data

The Meaning of NULL

• NULL can set data types to allow missing values
• NULL can be an empty query result
• Conceptually has several different meanings
  • no value
  • unknown value
  • missing value
  • out of range
  • not applicable
  • no column
• Two categories
  • Unknown
  • Not applicable

Primary key columns cannot allow NULL

Unique columns can allow NULL

SQL Expressions

Numeric expressions

• Literal Values
  • Exact-value
  • Approximate value
  • Numerical expressions with NULL usually return NULL
• Expressions with NULL will return NULL
• Results depend on literal values
• SELECT 1.1 + 2.2 = 3.3, 1.1E0 + 2.2E0 = 3.3E0;
  • 1, 0 (true, false)

Mixing numbers with strings

• MySQL is capable of converting strings into numerical values, but it uses extra resources
  • You can compare numbers with strings (1=’1′)
  • You can perform mathematical operations on numbers and strings (1+’1′, 1-‘1’, etc.)
  • You should avoid that if possible

Comparison Operations

• x >=5 AND x<=10 is not necessarily the same as x BETWEEN 5 AND 10
  • The first expression will not necessarily be optimized when querying the database
  • The second expression will always be optimized
• BETWEEN will include the indices (so it will return anything with 5 as the key and 10 as the key, and everything in between)
• When using BETWEEN, you must put the lowest value first

Literal strings are quoted

• You can generally use single or double quotes to wrap your literal strings
• If ANSI_QUOTES is enabled through MySQL, then values must be wrapped by single quotes and references to columns, etc. must be wrapped by double quotes
• If ANSI_QUOTES is enabled, wrapping values with double quotes will cause the query to fail

String comparisons

• Depends on character set and collation
• The default is set to be case-insensitive
• Therefore, an expression like “Hello”=”hello” will evaluate as true
• You can change the character encoding when establishing a connection
  • SET collation_connection = latin1_german2_ci;
  • To compare two strings that are not identical, but are equal (“Hello” vs. “hello” or “Dusseldorf” vs. “Dⁿsseldorf”), you can set the collation to latin1_bin (which will not convert binary information to characters when comparing) or latin1_general_cs (which will still consider accented characters to be the same as their unaccented equivalents, but will not consider lowercase characters to be equal to uppercase characters)
• Using the “LIKE” operator
  • Percent character (%) – Any character(s) wildcard – the wildcard can match multiple characters
    • LIKE ‘w%ll’ will match “well”, “wall”, “whole lot of hell”
  • Underscore character (_) – Will only match a single character
    • LIKE ‘d_g’ will match ‘dog’, but will not match ‘dung’
• You can use regular expressions to compare strings in MySQL
  • REGEXP
  • RLIKE

Temporal Operations

• INTERVAL – Add or subtract a specific amount of time to a TIME or DATETIME data type
  • http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_interval
• STR_TO_DATE – Attempts to convert an improperly formatted date into MySQL-formatted date
  • http://dev.mysql.com/doc/refman/6.0/en/date-and-time-functions.html#function_str-to-date
  • STR_TO_DATE(‘Apr. 4, 1982’, ‘%b %e, %Y’)

Functions can be invoked in MySQL expressions

Within MySQL, functions should be followed directly by an open parens. By default, no space is allowed between function name and parens

• You can use an IF function

IF(1 > 0 , ‘YES’, ‘NO’);

SELECT name FROM country

ORDER BY IF(code=’USA’,1,2), name;

Will order by whether or not the name is equal to “USA” first, then will order by name

• You can also use “CASE” statements
  CASE case_expr WHEN when_expr THEN result WHEN when_expr THEN result ELSE result ENDSELECT name FROM country
  ORDER BY CASE code
  WHEN ‘USA’ THEN 1
  WHEN ‘CAN’ THEN 2
  WHEN ‘MEX’ THEN 3
  ELSE 4 END, nameSELECT CASE
  WHEN Code = ‘USA’ THEN ‘United States’
  WHEN Continent = ‘Europe’ THEN ‘Europe’
  ELSE ‘Rest of the world’
  END AS Area,
  SUM(GNP), SUM(Population) FROM Country GROUP BY Area;

Numerical Functions

• Mathematical operations (add, subtract, divide, etc.)
• Common functions
  • TRUNCATE()
  • FLOOR() – Returns the greatest integer that is less than the given value
  • CEILING() – Returns the smallest integer that is greater than the given value
  • ROUND()
    • By default, this will round to the nearest integer
    • If you specify a second parameter, it will round to the nearest number with the specified number of decimal places. If you insert a negative number, it will round away from the decimal (in other words, ROUND(15.75,-1) will round to 20 – the nearest 10)
  • RAND() – Returns a random number

String Functions

• LENGTH/CHAR_LENGTH
  • LENGTH returns the number of bytes in a character string
  • CHAR_LENGTH returns the number of characters in a character string
• ELT() – Allows you to select an item from a list
  • ELT(column_value,’1st’,’2nd’,’3rd’…)
  • If column_value is equal to 1, then the function will return “1st”, if column_value is equal to 2, then the function will return “2nd”, etc.
  • ELT is 1-based, not 0-based
• CONCAT()/CONCAT_WS()
  • CONCAT() will simply combine all of the given arguments with no separators
    • If NULL is returned by any of the given arguments, then the entire CONCAT string will return NULL
  • CONCAT_WS() will combine all of the given arguments with a separator, which is specified as the first argument in the function
    • This function will simply skip NULL elements and not insert a separator for NULL elements
    • To concatenate a group of elements that may contain NULL anywhere, you can use CONCAT_WS(”,your_values), using a blank separator
• STRCOMP (string compare) – compare two strings to see if they are equal, less than or equal to
  • Will return -1 if the first one is less than the second
  • Will return 0 if they are equal
  • Will return 1 if the first one is greater than the second

Temporal Functions

• NOW() – Returns the current MySQL timestamp from the system clock
• GET_FORMAT() – Shows us how dates and times are generally formatted in a specific region of the world
• CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP

NULL-Related Functions

• ISNULL()/IFNULL()
  • ISNULL returns 0 if something is not null and 1 if it is
  • IFNULL accepts two parameters. The first parameter is the item that will be evaluated and the second item is the string that will be returned if the first returns as null
  • SELECT IFNULL(Continent,’Grand Total’) ContinentName, SUM(Population) FROM Country
    GROUP BY Continent WITH ROLLUP;

MySQL Comments

• You can use a hash character to create a comment that will end at the end of the line
• You can use C-style comments to comment multiple lines of code
• Within MySQL, you can set up conditional comments that will show up as comments in all other SQL DBMAs, but will be executed within MySQL
  • /*! This is not a comment to MySQL, but is in all other SQL DBMAs */
  • /*!50002 This comment will only be executed by MySQL version 5.0002 */

Metadata

Using “INFORMATION_SCHEMA”, you can gather various metadata information about your database. Some of the information you can select is:

+----------------------+ 
| COLUMN_NAME          | 
+----------------------+ 
| TABLE_CATALOG        | 
| TABLE_SCHEMA         | 
| TABLE_NAME           | 
| VIEW_DEFINITION      | 
| CHECK_OPTION         | 
| IS_UPDATABLE         | 
| DEFINER              | 
| SECURITY_TYPE        | 
| CHARACTER_SET_CLIENT | 
| COLLATION_CONNECTION | 
+----------------------+

You can also use SHOW and DESCRIBE commands to show specific information from the database.

From the command prompt (outside of the MySQL client), you can call a program called “mysqlshow” to show you the information in your MySQL installation.

C:\Documents and Settings\Student>mysqlshow -u root -p 
Enter password: **** 
+--------------------+ 
|     Databases      | 
+--------------------+ 
| information_schema | 
| mysql              | 
| test               | 
| test2              | 
| world              | 
+--------------------+ 

C:\Documents and Settings\Student>mysqlshow -u root -p world 
Enter password: **** 
Database: world 
+-----------------+ 
|     Tables      | 
+-----------------+ 
| city            | 
| country         | 
| countrylanguage | 
| students        | 
+-----------------+

Database Design and Information

A database is simply a directory within the filesystem

Schema is considered a synonym of database in MySQL

Two ways to approach database design:

1. You need to convert something else (another kind of database, a spreadsheet, etc.)
2. You are creating the database from scratch

Four basic relational types:

1. one pointing to one (1->1)
2. one pointing to many (1->M)
3. many pointing to one (M->1)
4. many pointing to many (M->M)

Within a database, you can only structurally manage a M->1 relationship (1->1 is a subset of that)

If you have a M->M relationship, then you should set up an intermediary table to relate between the two tables

Normalization

First Normal Form (1NF) – contains no repeating groups within rows

Second Normal Form (2NF) – normalize at the first level and every non-key (supporting) value is dependent on the primary key value

Third Normal Form (3NF) – normalized at the first and second level, dependent solely on the primary key and no other non-key (supporting) value

Identifier Syntax

• Alias
• Database
• Column
• Index

May be quoted or unquoted

Can use any alphanumeric characters, including $ and _. Anything using any characters other than that must be quoted.

You can access tables from other databases on the same server by qualifying the tablename:

SELECT * FROM world.Country;

Building Tables

General syntax for creating a table:

• CREATE TABLE <table> (
  <column_name> <column type> [<column options>],
  [<column_name> <column_type> [<column_options>],…,]
  [<index list>]
  )[<table options>];
• CREATE TABLE CountryLanguage (
  CountryCode CHAR(3) NOT NULL,
  Language CHAR(30) NOT NULL,
  IsOfficial TINYINT(1) NOT NULL DEFAULT 0,
  Percentage FLOAT(3,1) NOT NULL,
  PRIMARY KEY(CountryCode, Language)
  ) ENGINE = MyISAM COMMENT=’Lists Language Spoken’;
• Several options available
  • ENGINE
  • COMMENT
  • CHARACTER SET (CHARSET)
  • COLLATE
• Cannot create an auto_increment column that is not indexed