Software | HTMLCenter - Web Help and Web Development Blog

Archive for the ‘software’ Category

Mar 30 Karaoke Revolution - American Idol Encore for the Wii Posted at 1:49 pm | No Comments »

A few weeks ago, we purchased “Karaoke Revolution - American Idol Encore” for my Nintendo Wii. Most of my family is obsessed with American Idol, and thought the game would be a lot of fun to play.

Unfortunately, the game just didn’t even come close to living up to our expectations.
(more…)

Tags: american idol, game, htmlcenter, karaoke, nintendo wii, review
Posted in plugs, software

Mar 5 MySQL Developer Training - Day 5 Posted at 5:10 pm | No Comments »

On day 5, we went through all of the rest of the material in the course. By the end of the day, we had been provided all of the information we should need in order to pass both MySQL Developer examinations. My notes from the final day of class follow.

Day 5

DECLARE statement

The declare statement defines items local to a routine

Local variables

Conditions and handlers

Cursors

Declare only allowed inside a BEGIN…END

Declarations must follow a specific order

Variables

Conditions - error handler - captures errors that occur in the procedure/function and determines how they should be handled

DECLARE condition_name CONDITION FOR condition_value;

SQLSTATE Condition value

DECLARE null_not_allowed CONDITION FOR SQLSTATE ‘23000′;

MySQL errors are strings, so they need to be quoted

Continue handler

DECLARE CONTINUE HANDLER FOR SQLSTATE ‘23000′ SET @x=1;

A continue handler allows the procedure to continue even if the condition is met

Exit handler

An exit handler will stop the procedure/function when the condition is met (an error is generated)

Cursors

A control structure within stored routines for record retrieval

One row at a time

A cursor is basically a pointer pointing to the current row we are fetching

Cursors are mostly used within loops that fetch and process the rows

Asensitive - MySQL decides how to process the information that’s fetched

Read-only

Non-scrolling

The cursor strictly moves from one row to the next

You cannot skip records

You cannot go backwards

Cursors must be declared before declaring handlers

OPEN opens a previously declared cursor

FETCH

obtains the next row using the specified open cursor, and advances the cursor pointer

When there is no next row, an error will result

You would use that error as a condition to break out of the loop

A cursor is destroyed automatically at the end of the BEGIN…END block

If you choose to close the cursor ahead of time, you can use the CLOSE command

Prepared statements can only have one cursor, and it is not named

Handlers

Triggers

Named database objects

Activated when

Creating triggers

CREATE TRIGGER trigger_name { BEFORE | AFTER }
{ INSERT | UPDATE | DELETE }
ON table_name
FOR EACH ROW
triggered_statement

CREATE TRIGGER City_AD AFTER DELETE ON City
FOR EACH ROW
INSERT INTO DeletedCity (ID, Name) VALUES (OLD.ID, OLD.Name);

OLD and NEW are keywords that can be used within triggers

Trigger error handling

MySQL handles errors during trigger execution as follows:

Failed BEFORE triggers

Operation on corresponding row is not performed

After trigger execution

BEFORE trigger events and the row operation will execute successfully in non-transactional tables

In transactional tables, a rollback of all changes made by the statement will occur

Disallowed statements

SQL prepared statements

Explicit or implicit COMMIT or ROLLBACK

Return a result set

FLUSH

Recursive - cannot modify a table that is in use (reading or writing)

These types of things must be taken care of in BEFORE statements, rather than AFTER statements, because the table will not yet be open at that point

Storage Engines

SQL Parser and storage engines

Client sends requests to the server as SQL

Two-tier processing

Upper tier includes SQL parser and optimizer

Lower tier comprises a set of storage engines

SQL itself is not engine-specific, generally

Some exceptions include COMMIT, ROLLBACK, CREATE TABLE (which needs an engine declaration and can allow foreign key information for transactional tables), etc.

Steps to parse and return

Parse

Optimize

Retrieve/Store

This is where things like ORDER BY, GROUP BY, etc. are performed

Storage medium

Transactional capabilities

InnoDB allows transactions while MyISAM does not

Locking

InnoDB locks on a row-by-row basis

MyISAM locks on a table-by-table basis

Backup and recovery

MyISAM allows you to physically copy individual tables, etc. from one place in your filesystem to another place

InnoDB stores its information for all databases on the server in a single file, so it is more difficult to copy individual items

Optimization

Special features

MySQL server operates same for all storage engines

SQL commands independent of engine

Available Storage Engines

MySQL provides and maintains several storage engines

Also compatible with many third party engines

MySQL developed:

MyISAM

Incredibly fast - even more so when you use compression, fixed-width columns and good indexing

Data stored in table

Table-level locking

Does not support transactions

Falcon

NDB/Cluster

Memory

Data is in memory

Fastest engine that exists, but nothing is stored anywhere on disk

Archive

Highly compressed

Only supports INSERT and queries

Federated

creates an alias of an existing database on a separate server

Blackhole

CSV

Example - doesn’t do anything, but is in the code to show people a template of how to create an engine

Third party engines

InnoDB

Transactional

Foreign key constraints

Row-level locking

Backups

solidDB

InfoBright BrightHouse

Nitro

PBXT

You can set the storage engine when creating a table, but you are not required to do so. If you do not specify the engine, it will be set to the database default

MyISAM Storage Engine

MyISAM is the MySQL default

Manages tables with specific characteristics

Represented by three files

Most flexible AUTO_INCREMENT

Fast, compressed, read-only tables save space

Manages contention between queries

By default, writing to disk takes precedence over reading from disk, but that can be overridden

SELECT statements can be set to a higher priority with the HIGH PRIORITY switch

UPDATE, DELETE and INSERT statements can be set to a lower priority with LOW PRIORITY

INSERT statements can also be set to an extremely low priority with DELAYED

Portable storage format

Specify number rows for a table

Disable updating of non-unique indexes and enable the indexes

DISABLE KEYS/ENABLE KEYS

Indexes of non-unique keys can be disabled so that the indexes are not re-created each time a row is changed. The indexes will be rebuilt when the keys are reenabled

Tables take up very little space

Three row storage formats

Fixed-row format

Dynamic row format

Compressed format

Tables must be deliberately compressed

Compressed tables are read-only

Tables must be decompressed to modify

Using the myisampack utility

Includes a mixture of “True” compression and a set of optimizations

Each record compressed separately with small cost to decompress

Must be performed on the host machine

Use myisamchk afterward to update the indexes

Always backup tables prior to running utilities

Table-level locking

Acquiring locks

Tables with no holes support concurrent inserts

If a table is completely defragmented, any number of inserts can all be performed at the same time

Tables with holes do not support concurrent inserts by default

Can change priority of statements that retrieve or modify data

Write request not processed until current readers finished

InnoDB Storage Engine

Manages tables with specific characteristics

Represented on disk by a .frm format file as well as data and index storage

Supports transactions

ACID compliant

Satisfies ACID conditions

General locking properties

Does not need to set locks to achieve consistent reads

Uses row-level locking per concurrency properties

May acquire row locks as necessary to improve concurrency

Deadlock is possible

Supports two locking modifiers

Repeatable Read isolation level allows modifiers

Auto-recovery after crash

MVCC and non-locking reads

Supports foreign keys and referential integrity

Supports consistent and online logical backup

Tablespace for storing all table contents together

Log files for recording transaction activity

Format file (.frm)

Logical storage area can contain multiple files

Table-specific file (.ibd)

–innodb-file-per-table

Manages InnoDB-specific log files

Log files used for auto-recovery

Memory Engine

Uses tables stored in memory

Tables are temporary

Must have fixed-length rows

Manages tables with specific characteristics

Formerly HEAP engine

Memory indexing options

Uses HASH indexes by default

BTREE is preferable for some operators

Ranges

Optimization

Server processes queries more efficiently and performs better

Optimization strategies

Use indexing properly

Large tables require indexing for efficiency

Benefits of indexes

Contain sorted values

Use less disk I/O

Enforce uniqueness constraints

Downsides of indexing

Any time anything in the table is changed, the indexes have to be rebuilt

Can slow down some data manipulations

Uses additional space

Types of indexes

Three general types

Primary key

Unique

Allows NULLs, but other than that requires the value of the cell to be different than anything else in the column

Non-unique

Allows NULLs and allows duplicate entries

Primary versus unique

Primary cannot contain NULL

Only one primary key is allowed per table

Adding indexes

ALTER TABLE table_name ADD PRIMARY KEY(column_name);

CREATE INDEX index_name ON table_name(column_name);

Removing indexes

ALTER TABLE table_name DROP PRIMARY KEY;

ALTER TABLE table_name DROP INDEX index_name;

DROP INDEX index_name ON table_name;

DROP INDEX `PRIMARY` ON table_name;

PRIMARY must be contained within back tick quotes, because it is a name. If it is not within back ticks, it will be treated as a keyword that doesn’t belong in that syntax

PRIMARY is the official name of the primary key in a table

Using index prefixes

Several column types

Use only specified, leading part of column values

Can make the index work faster, because it only searches a specific portion of the column value

CREATE INDEX part_of_name ON customer (name(10));

Can be used on composite indexes, as well

INDEX (column_name1(15),column_name2(10))

You can show index information by using the SHOW INDEX command

Full text indexes

Only useful with MyISAM tables

Definition can be given with CREATE TABLE, ALTER TABLE, CREATE INDEX

Create index after table creation for large datasets

Use MATCH()…AGAINST() syntxt

SELECT title FROM books WHERE MATCH(title) AGAINST (’prince potter’)

Well-written queries

Using EXPLAIN to determine query processing

Returns useful information

Shows if index is required

Shows if index is being used

Analyzes query rewrites

EXPLAIN works with SELECT queries

EXPLAIN for JOINs

Joins tend to increase amount of server processing

EXPLAIN can help reduce server impact

The type column indicates the join type

type column output

system

const

eq_ref

ref

ref_or_null

index_merge

unique_subquery

index_subquery

range

index

ALL

The extra column (page 21-22)

using index

where used

distinct

not exists

Inefficient query “extra” output

using filesort

using temporary

range checked for each record

Indicators of worst performance

using filesort

using temporary

Rewrite query and run EXPLAIN again

Efficiency principles

No indexed columns within an expression

Beneficial for joins that compare columns from two tables

Use same value as column data type

WHERE id=18 (if column is a number)

WHERE id=’18′ (if column includes strings)

Pattern matching

WHERE name LIKE ‘de%’

WHERE name >= ‘de’ AND name < ‘df’ - not as good as example above

WHERE name LIKE ‘%de’ - full table scan

Rewrite using a trigger that maintains an additional column

WHERE LENGTH(column)=5 - bad

WHERE column_length=5 - column_length is a separate column that’s been automatically generated to show the value that would be returned by LENGTH(column)

Reduce amount of output a query produces

Use the LIMIT clause

Reduces information going over the network

Allows server to terminate query processing earlier

Use the WHERE clause

More improvement with index or column

Techniques for updating tables

Use DELETE and UPDATE same as SELECT

Multi-row INSERT

INSERT INTO t (id, name) VALUES (1,’Bea’),(2,’Belle’),(3,’Bernice’);

Better performance with a transaction

LOAD DATA INFILE faster than multi-row INSERT

Even faster with MyISAM if you disable keys before running the file and enabling them when finished

Use REPLACE rather than DELETE plus INSERT

Generating summary tables

Select records to generate summaries

Summary table strategy

Several benefits to this strategy

Table-locking table will be available more

The use of the temporary table will not affect the original table from which it was derived

Consider making a memory table

CREATE TABLE ContinentGNP
SELECT Continent, AVG(GNP) AS AvgGnp
FROM Country GROUP BY Continent

Disadvantages

Values are only good until changed

Storing data twice

Choose best matching storage engine

Decide query types during table creation

Choose storage engine with locking level needed

InnoDB good for a mix of retrievals and updates

MyISAM table structure dependent on the higher priority between speed or disk

Choose fixed-length or variable-length according to needs

Use read-only tables if possible

CHAR columns take more space than VARCHAR

considerably faster in MyISAM, but CHAR is no different (in speed) than VARCHAR in InnoDB

Use Memory for temporary information

* Within a MyISAM table, you can temporarily disable all non-unique keys so that new indexes will not be built while they are turned off. Use the statement ALTER TABLE table_name DISABLE KEYS. You can turn them back on with ALTER TABLE table_name ENABLE KEYS.

Tags: development, htmlcenter, MySQL, open-source, optimization, software, storage engines, stored routines, training, triggers, variables
Posted in programming, software

Mar 3 MySQL Developer Training - Day 4 Posted at 3:16 am | No Comments »

On day 4, we continued where we had left off on day 3. We covered subqueries, views, prepared statements, importing and exporting data, stored routines and some of the basic functions that are available within stored routines. My notes from day 4 follow.

Day 4

Subqueries

Subqueries are the reason behind the name “structured query language,” as subqueries are “structured queries”.

A subquery is a query nested inside another query

Enclosed inside parentheses in order to specify that the subquery should be completed first

SELECT Language
FROM CountryLanguage
WHERE CountryCode = (
SELECT Code
FROM Country
WHERE Name=’Finland’);

Why use a subquery?

Result Table Types:

Scalar - Single row with a single column

Row - Single row with one or more columns

Column - Single column with one or more rows

Table - One or more rows with one or more columns

Empty - No row or column data

Correlated and non-correlated

Correlated

References outer query

Cannot stand alone

Tend to be inefficient from a time standpoint, as the engine has to go back and forth between the inner and outer query

Tend to be more RAM efficient than JOINs, because it does not have to store a separate memory table from which to pull the final results

SELECT Country.Name
(SELECT COUNT(*) FROM City
WHERE CountryCode=Country.Code)
AS CityCount FROM Country;

Non-correlated

Does not reference outer query

Can stand alone

Ideally, a non-correlated subequery would only be read once

SELECT Name FROM City WHERE CountryCode IN
(SELECT Code FROM Country
WHERE Continent=’Oceania’);

Subquery placement

If you place a subquery in the “column” portion of the query, then you can return and use only a scalar result

SELECT name,
(SELECT MAX(Population) FROM City
WHERE City.CountryCode = Country.Code)
AS LargestCity
FROM Country LIMIT 8;

Inside the FROM clause, you could return and use a table result, which can also include column, row and scalar results

Every column must have a name

Subquery can be a table query

Even if all values are not used

The following example is not something we could return with a JOIN

SELECT AVG(cont_sum)
FROM (SELECT Continent, SUM(Population) AS ContinentSum
FROM Country
GROUP BY Continent)
AS t;

Any time you have a subquery in a FROM clause, you must alias the created table, even if you don’t use that alias name anywhere else in the query

In the WHERE clause, when using an “equals” comparison, you can return and use scalar or row results

In order to use row results, you would have to use a tupple

(SELECT x, y FROM mytable WHERE key = 5) = (value, value)

(x, y) = (z, a)

Either or both sides of the tupple equation can be subqueries, themselves

Compares outer query with subquery results to see if they are the same

SELECT Continent, Name, Population
FROM Country c
WHERE Population = (SELECT MAX(Population)
FROM Country c2
WHERE c.Continent = c2.Continent
AND Population > 0
);

In the example above, you do not need the “> 0″, because simply stating the name of the column will evaluate as 0 or 1 (false or true)

In the WHERE clause, when using greater than or less than comparisons, you can only use and return a scalar result

SELECT

In the WHERE clause, when using IN, ALL, ANY, SOME, etc., you can use and return a table result (or any other type of result)

In order to use a table result, you would need to use a tupple

Using a tupple would look something like:
WHERE CountryCode, Name, Population IN (SELECT Code, Name, Population FROM Country…)

A tupple will compare from left to right, matching the columns in the left list up with the columns in the results returned by the right set

SELECT Name, Population FROM City
WHERE CountryCode IN(
SELECT Code FROM Country
WHERE Continent = ‘Europe’)
ORDER BY Name LIMIT 10;

SELECT Name FROM City
WHERE Population >
ALL (SELECT Population FROM City
WHERE CountryCode = ‘CHN’);

The example above will compare each result returned against all of the results returned in the subquery. If any of the results in the subquery do not match the criteria, the whole result will return false

The specific example above could have been optimized to read something like:

SELECT Name FROM City WHERE Population > (SELECT MAX(Population) FROM City WHERE CountryCode = ‘CHN’);

SELECT Name
FROM Country
WHERE Continent = ‘Europe’
AND Code = ANY(SELECT CountryCode
FROM CountryLanguage
WHERE Language=’Spanish’)
ORDER BY Name;

In the WHERE clause, when using EXISTS, you can only return an empty result set (you are simply checking to see if you get any results or not, you are not using any information from the query itself

In current versions of MySQL, this type of result is not optimized - MySQL will complete the subquery before returning 0 or 1 (true or false)

In future versions, this type of result should be optimized, so that the engine will stop the subquery as soon as a result is returned, and will then return a 1

SELECT Continent FROM Country WHERE EXISTS
(SELECT * FROM City WHERE Code = CountryCode AND Population > 8000000)
GROUP BY Continent;

Will return a list of each continent that contains at least one country with a population over 8000000

SELECT Continent FROM Country WHERE NOT EXISTS
(SELECT * FROM City WHERE Code = CountryCode AND Population > 8000000)
GROUP BY Continent;

Is not the opposite of the query above

Will return a list of each continent that contains at least one country with a population under 8000000

Other Subquery Uses

Using Subqueries in UPDATE and DELETE statements

You can use subqueries to locate rows on which you want to perform updates or you want to delete

Subqueries are not limited to SELECT statements

They can be used in DELETE and UPDATE statements

DELETE FROM City WHERE CountryCode IN (
SELECT Code FROM Country WHERE LifeExpectancy < 70.0);

Generally, when dealing with DELETE and UPDATE statements, the subquery is used within the WHERE clause

Converting subqueries to JOINs

Joins can be more efficient than subqueries

Convert a subquery to a join if the subquery is running slowly

Subquery versus JOIN

Subquery:

SELECT Name FROM Country
WHERE Code IN (
SELECT CountryCode FROM CountryLanguage);

JOIN:

SELECT DISTINCT Name FROM Country
JOIN CountryLanguage ON Code=CountryCode;

Views

Database object defined in terms of a SELECT statement

SELECT statement masquerading as a table

Will stay in the database until it is destroyed

Selected from base tables or views

Updatable

Views are nestable

CREATE VIEW statement

Define a view

General syntax:

http://dev.mysql.com/doc/refman/5.0/en/create-view.html

CREATE VIEW view_name AS select_statement

CREATE VIEW CityView AS SELECT ID, Name FROM City;

Optional parts of a CREATE VIEW statement

OR REPLACE

CREATE OR REPLACE CityView AS SELECT ID, Name FROM City;

Will replace an existing view of the same name if it exists, will create a new view if not

ALOGRITHM = { UNDEFINED | MERGE | TEMPTABLE }

UNDEFINED

MERGE

TEMPTABLE (TempTable)

Will create a temporary table if the view is too large to fit into RAM

DEFINER = { user | CURRENT_USER }

SQL SECURITY { DEFINER | INVOKER }

The definer, by default, is the person who created the view

column_list

By default, if the column_list is not specified, the names of the columns in the original table will be used as the column names in the view

You can optionally define new column names for each of those

CREATE VIEW v
AS SELECT Country.Name AS CountryName, City.Name AS CityName
FROM Country, City WHERE CountryCode=Code;

WITH CHECK OPTION (see insertable views below)

Places constraints on allowable modifications

Checks the WHERE conditions for updates

If the update or insert statement would result in something that would not be visible in the view, then the CHECK OPTION will stop the statement from being executed successfully

Example:

CREATE VIEW LargePop AS
SELECT Name, Population FROM Country
WHERE Population >= 100000000
WITH CHECK OPTION;

SELECT * FROM LargePop;

UPDATE LargePop SET Population = Population + 1 WHERE Name=’Nigeria’

It is possible to update a table through a view

Can use UPDATE and DELETE

Updating and deleting information from a view will update or delete that information inside of the base table on which the view is based.

Information in the view does not actually exist in the view - it only exists inside the base table(s) on which the view is based

Must be one-to-one relationship

Updatability examples

CREATE VIEW EuropePop AS
SELECT Name, Population FROM Country
WHERE Continent = ‘Europe’;

UPDATE EuropePop SET Population = Population + 1 WHERE Name = ‘San Marino’;

Insertable Views

An updateable view can be insertable

Must meet additional requirements

No duplicate view column names

Must contain all columns from base table without default value

Cannot be derived columns (calculated, etc.)

Inserting views with AUTO_INCREMENT

LAST_INSERT_ID() will not work if the AUTO_INCREMENT column is not visible through the view

Checking Views

When you define a view, the object referenced must exist

A view can become invalid if a referenced object is removed

Using CHECK TABLE will show that the view is no longer valid

Altering Views

You can use ALTER VIEW to change an existing view

Is basically the same as CREATE OR REPLACE

INFORMATION_SCHEMA

Views table in database

SELECT * FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = ‘CityView’
AND TABLE_SCHEMA = ‘world’\G

SHOW statements

Display Metadata

SHOW CREATE VIEW specifically for views

DESCRIBE

SHOW TABLE STATUS

SHOW TABLES

SHOW FULL TABLES

Prepared statements

Useful for running multiple similar queries

Can use same structure and change data values

Enhanced performance

Statement parsed only once by server

May require fewer conversions

Less traffic between server and client

Prepared statements from MySQL

Aids in testing and debugging

Session-bound

Prepared statements are tied to sessions and will be automatically destroyed when the session is closed

User defined variables pass values from one statement to another

Connection specific

Also known as @ variables

variables stay in RAM until the connection is closed

variables will not persist across connections

Use SET statement to define

Example syntax

SET @var_name = expr [, @var_name = expr]…

SET @myvar = 12, @myothervar = 5*2

Expression can evaluate to an integer, real, string or NULL

Coercibility is implicit

Uses the type of the string that we supply

If we do not supply a type, it will inherit the charset and collation of the connection

PREPARE my_stmt FROM
‘SELECT COUNT(*) FROM CountryLanguage WHERE CountryCode = ?’;

SET @code = ‘ESP’; EXECUTE my_stmt USING @code;

Using variables inside the prepared statement

You can have more than one question mark inside the prepared statement

They will be parsed in the order in which they appear within the statement

To supply multiple variables to be assigned to the multiple unknowns, you would separate the variable names with commas in the execute statement

If you want to destroy the prepared statement before closing the connection

DEALLOCATE PREPARE my_stmt;

DROP PREPARE my_stmt;

You do not have to use an unknown (question mark) inside a prepared statement

Preparing a statement

PREPARE namepop FROM ‘

SELECT Name, Population
FROM Country
WHERE Code = ?

‘;

Not all SQL statements can be prepared

Limited to the following

SELECT

Data modification: INSERT, REPLACE, UPDATE, DELETE

SET, DO and many SHOW statements

CREATE TABLE statements

As of MySQL 5.1, many more features were added

Exporting and Importing Data

Import Data using SQL

LOAD DATA INFILE

Imports data into a table from a file

Uses similar clauses and format specifiers as SELECT…INTO OUTFILE

MySQL assumes file is located on server host

In database data directory

Tab delimited or comma separated files

Characteristics to know about input file

CSV example:

LOAD DATA INFILE ‘C:/City.txt’ INTO TABLE City
FIELDS TERMINATED BY ‘,’
ENCLOSED BY ‘”‘
LINES TERMINATED BY ‘\n’;

Specifying data file location as client host

LOAD DATA LOCAL INFILE ‘C:/City.txt’ INTO TABLE City;

You have to have the proper security permissions in order to do this

Skipping or transforming column values

LOAD DATA INFILE ‘C:/City.txt’ INTO TABLE City
IGNORE 2 LINES;

Duplicate records

Can control duplicate records with IGNORE and REPLACE

Behavior differs slightly

IGNORE and REPLACE are mutually exclusive

REPLACE will delete the original record and create a new one with the new information if it finds a duplicated key

IGNORE will skip the new record if its key already exists in the table

To alter the structure of the data in order to make it fit into your table properly

LOAD DATA INFILE ‘C:/people.txt’ INTO TABLE People
FIELDS TERMINATED BY ‘,’
ENCLOSED BY ‘”‘
LINES TERMINATED BY ‘\n’
IGNORE 2 LINES
(@skip, @firstname, @lastname, @date, address, email, …)

The items enclosed in parentheses above are in the order in which they appear within the data file that I’m loading

The items not preceded by @ symbols are the names of the columns in my table

The variable @skip in the example above is an undefined variable, which, by default, is equal to NULL

In this example, we are simply using that variable name to specify a column that does not need to be inserted into our table

SET Name = CONCAT(@lastname,’, ‘,@lastname),
Birthdate = str_to_date(@date,’%b %e %Y’);

The date in the example above would have been in the format of Sep 6 2001

Export Data using SQL

SELECT with INTO OUTFILE

Writes result set directly into a file

MySQL assumes filepath to be in database data directory, unless otherwise specified

Path is relative to the host machine on which the SQL server is running

SELECT * INTO OUTFILE ‘C:/City.txt’ FROM City;

INTO OUTFILE Changes SELECT Operation

File written to server host, instead of over the network to client

If the file already exists, this command will generate an error rather than overwriting or appending

Specifiers

Default is tab delimited with newline terminators

Can change specifiers for all columns

FIELDS TERMINATED BY ’string’

defines data values within a line

ENCLOSED BY ‘char’

ESCAPED BY ‘char’

LINES TERMINATED BY ’string’

Terminator definitions

\N - NULL

\O - NUL (zero) byte

\b - backspace

Line terminator specifiers

Newline character is the default

CSV format text file example

SELECT * INTO OUTFILE ‘C:/City.csv’
FIELDS TERMINATED BY ‘,’
ENCLOSED BY ‘”‘
LINES TERMINATED BY ‘\r’
FROM City;

Export using the mysqldump database backup client

MySQL utility to export (dump) table contents

Full structure

Data only

Table structure only

In standard format

MySQL specifics for optimized speed

Compressed

Three ways to invoke mysqldump

mysqldump [options] db_name [tables]

mysqldump [options] –databases db_name1 [db_name2 db_name3...]

mysqldump [options] –all-databases

Can only be executed from the shell

Need to redirect the output into a file, otherwise it just prints the dump onto the screen

mysqldump -uroot -p<password> world >C:/world_dump.sql

File contains commands needed to recreate tables and data

Export to a specific table in a database

mysqldump -uroot -p<password> world CountryLanguage

Import using the mysqlimport client

MySQL utility to load data files into tables

Command line interface to LOAD DATA INFILE

mysqlimport options db_name input_file …

Can only be executed from the shell

The table the information is being imported into must have the same name as the file you are loading from

Options

–help

–lines-terminated-by=string

–fields-terminated by=string…

Import data with the source command

Import table data

mysql -u root world < CountryLanguage.sql

Path is relative to the local machine

Loading the data

SOURCE C:/CountryLanguage.sql

Source uses a path relative to the client machine (your machine, not the server)

The default is to not write over existing databases, unless specified inside the script file that you’re loading

Stored Routines

What is a stored routine?

Set of SQL statements that can be stored in server

Types

Stored procedures

A procedure is invoked using a CALL statement, and can only pass back values using output variables

Procedures are database-specific

Stored functions

A function can be called from inside a statement and can only return a scalar value

Are invoked like a normal function

We use stored routines for the following reasons

Performance

Security

Minimal data access

Single location processing

Client applications

One application

One programming language

Instead of trying to translate your functions, etc. into each of the different languages you’re using to access the data, you can simply write it in MySQL as a stored routine

Function libraries

Stored routine issues

Increased server load

Because MySQL is doing all of the work, none of it is being passed off to other sources

Limited development tools

You can only use MySQL commands within your stored routines

Limited functionality and speed

There is no way to compile routines, etc.

No debugging/profiling capabilities

You can trace by downloading a specific version of the source and compiling it yourself, but that is not recommended

Creating Stored Routines

Create procedure

CREATE PROCEDURE procedure_name() procedure_statement

Single statement example:

CREATE PROCEDURE world_record_count ()
SELECT ‘country count ‘,COUNT(*) FROM Country;

Create function

CREATE FUNCTION function_name() RETURNS return_type function_statement

Single parameter example

CREATE FUNCTION ThankYou(s CHAR(20)) RETURNS CHAR(50) RETURN CONCAT(’Thank You, ‘,s,’!');

Compound statements

Delimiter and BEGIN…END

DELIMITER // - sets the alias of the go command as “//”
CREATE PROCEDURE world_record_count() - creates the procedure
BEGIN - opens our list of commands
SELECT ‘country count ‘, COUNT(*) FROM Country; - a statement that will be run
SELECT ‘city count ‘, COUNT(*) FROM City; - another statement
SELECT ‘countrylanguage count’, COUNT(*) FROM countrylanguage; - another statement
END// - ends our list of commands and then tells the server to execute
DELIMITER ; - sets the delimiter back to a semi-colon

Assign Variables

DECLARE

Declaring

Scope

Variables declared within a function definition are only valid within that function defnition

DELIMITER //
CREATE FUNCTION add_tax (total_charge FLOAT(9,2)
RETURNS FLOAT(10,2)
BEGIN
DECALRE tax_rate FLOAT(3,2) DEFAULT 0.07;
RETURN total_charge + total_charge * tax_rate;
RETURN Fbill;
END //
DELIMITER ;

SELECT … INTO

Global variables

SELECT SUM(population) FROM country INTO @worldpop;
…is equivalent to…
SELECT SUM(population) INTO @WorldPop FROM country;

SELECT SUM(population), AVG(population) FROM Country INTO @worldpop, @worldavg;

Local Variables

SELECT COUNT(*) FROM city INTO Total_Cities;
…is equivalent to…
SELECT COUNT(*) INTO Total_Cities FROM City;

SET

The SET statement allows the user to assign a value to a user defined variable using either = or := as the assignment operator

Variable Scope

Local Variable

Routine parameter

Local variable in an inner block

Local variable in an outer block

DELIMITER //
CREATE PROCEDURE precedence(opal INT)
BEGIN
DECLARE opal INT DEFAULT 0;
SELECT opal FROM test_table;
BEGIN
DECLARE opal INT DEFAULT 1;
SELECT opal FROM test_table;
END;
END//
DELIMITER ;

Variables must be defined immediately after the BEGIN statement in the block

Parameter declarations

Stored procedures

IN (Default)

Indicates an input parameter which is passed in from the caller to the procedure

OUT

Indicates an output parameter which is set by the procedure and passed to the caller after the procedure terminates

NULLs the variable when it’s passed to the function

Can be used to set a “success/failure” flag

INOUT

Indicates a parameter that can act as an IN and an OUT parameter

OUT and INOUT variables will be passed ByRef (meaning that anything that happens to the variable inside of the procedure will happen to the original variable)

Stored functions

For stored functions, only IN is available

Execute Stored Routines

Executing procedures

Executing functions

Implications of database association

You need to declare the appropriate database in which the routine is stored

If the database is deleted, the routine is deleted, too

Alterable stored routine characteristics

ALTER PROCEDURE/FUNCTION

SQL SECURITY

DEFINER

INVOKER

Data use (SQL)

CONTAINS SQL

NO SQL

READS SQL DATA

MODIFIES SQL DATA

COMMENT

Non-alterable stored routine characteristics

Non-alterable

DETERMINISTIC

The routine always produces the same result when invoked with a given set of input parameter values

NOT DETERMINISTIC (default)

The routines results can possibly change when invoked with a given set of input parameter values

LANGUAGE

Provides the parser with the programming language to use when parsing the stored routine created

This cannot change because MySQL is the only language available

Examine Stored Routines

SHOW CREATE PROCEDURE/FUNCTION

SHOW PROCEDURE/FUNCTION STATUS

INFORMATION_SCHEMA.ROUTINES

Delete Stored Routines

DROP PROCEDURE [IF EXISTS] procedure_name;
DROP FUNCTION [IF EXISTS] function_name;

Flow control statements

Statements and constructs that control order of operation execution

Common flow controls

Choices

IF

IF(test condition) THEN
…
ELSEIF(test condition) THEN
…
ELSE
…
END IF;

ELSEIF needs to be all one word

CASE

CASE provides a means of developing complex conditional constructs

CASE works on the principle of comparing a given value with specified constants and acting upon the first constant that is matched

CASE case_value
WHERE when_value THEN
…
ELSE
…
END CASE;

CASE
WHEN test_condtion THEN
…
ELSE
…
END CASE

If none of the cases are met and there is no “else” specified, it will throw an error

In the first example, it is strictly a binary comparison, meaning that case-sensitivity is a factor

Loops

REPEAT

The repeat statement repeats and SQL statement until the search condition that is set becomes true

A repeat statement is always run at least once

Labels: Begin and End

begin_label: REPEAT
…
UNTIL test_condition
END REPEAT end_label;

WHILE

WHILE repeats an SQL statement while a set condition is true

The statement list can consist of one or more statements

begin_label: WHILE test_condition DO
…
END WHILE end_label;

LOOP

The statements within the loop are repeated until the loop is exited; usually this is accomplished with a LEAVE statement

begin_label: LOOP
…
IF…THEN
LEAVE label;
END IF
END LOOP end_label;

Other Label Flow Control Constructs

LEAVE

This statement is used to exit any labeled flow control construct within a LOOP, REPEAT or WHILE statement

LEAVE can also be used in Labeled BEGIN…END compound statements

ITERATE

This statement simply means “do the LOOP (or REPEAT or WHILE) again”

Tags: development, export, htmlcenter, import, MySQL, open-source, prepared statements, software, stored routines, subqueries, training
Posted in programming, software

Mar 1 MySQL Developer Training - Day 3 - Second Half Posted at 12:11 pm | No Comments »

During the second half of day 3, we began to delve into the information that will be presented on the MySQL Developer II examination. My notes from that session follow.

Day 3 - Second Half

Joins
Combining information from multiple tables
Three categories of joins
Inner join - identifies combinations of matching rows from two tables
SELECT * FROM m, w WHERE m.HHid = w.HHid; or SELECT * FROM m JOIN w ON m.HHid = w.HHid; will pull only matching records from each table:
+------+------+-------+------+
| Name | HHid | Name  | HHid |
+------+------+-------+------+
| Bob  |    1 | Carol |    1 |
| Ted  |    2 | Alice |    2 |
+------+------+-------+------+
SELECT * FROM m JOIN w USING (HHid); will combine the field “HHid” from both tables and move it all the way to the left of the resultset (will only work if the reference column is named the same in both tables)
+------+------+-------+
| HHid | Name | Name  |
+------+------+-------+
|    1 | Bob  | Carol |
|    2 | Ted  | Alice |
+------+------+-------+
SELECT * FROM m RIGHT JOIN w ON m.HHid = w.HHid WHERE m.Name IS NULL; will return all of the records from the right table (w) that do not have matches in the left table:
+------+------+-------+------+
| Name | HHid | Name  | HHid |
+------+------+-------+------+
| NULL | NULL | Sally |    4 |
| NULL | NULL | Ann   |    7 |
| NULL | NULL | Linda |    5 |
+------+------+-------+------+
Outer join - the same as inner join, but it also finds mismatches
SELECT * FROM m LEFT JOIN w ON m.HHid = w.HHid; will pull all of the information from the left table and any matching information from the right table. Any information that appears in the left table (m) that does not have a match in the right table (w) will be represented as NULL in the right side:
+------+------+-------+------+
| Name | HHid | Name  | HHid |
+------+------+-------+------+
| Ed   |    6 | NULL  | NULL |
| Ted  |    2 | Alice |    2 |
| Al   |    3 | NULL  | NULL |
| Bob  |    1 | Carol |    1 |
+------+------+-------+------+
SELECT * FROM m RIGHT JOIN w ON m.HHid=w.HHid; will pull all of the information from the right table (w) and any matching information from the left table (m). Any information that appears in the right table (w) that does not have a match in the left table (m) will be represented as NULL in the left side:
+------+------+-------+------+
| Name | HHid | Name  | HHid |
+------+------+-------+------+
| NULL | NULL | Sally |    4 |
| Bob  |    1 | Carol |    1 |
| Ted  |    2 | Alice |    2 |
| NULL | NULL | Ann   |    7 |
| NULL | NULL | Linda |    5 |
+------+------+-------+------+
Cross join - combines all of the records from one table with all the records of another table
SELECT * FROM m, w; will match all records in m table with all records from w table:
+------+------+-------+------+
| Name | HHid | Name  | HHid |
+------+------+-------+------+
| Ed   |    6 | Sally |    4 |
| Ted  |    2 | Sally |    4 |
| Al   |    3 | Sally |    4 |
| Bob  |    1 | Sally |    4 |
| Ed   |    6 | Carol |    1 |
| Ted  |    2 | Carol |    1 |
| Al   |    3 | Carol |    1 |
| Bob  |    1 | Carol |    1 |
| Ed   |    6 | Alice |    2 |
| Ted  |    2 | Alice |    2 |
| Al   |    3 | Alice |    2 |
| Bob  |    1 | Alice |    2 |
| Ed   |    6 | Ann   |    7 |
| Ted  |    2 | Ann   |    7 |
| Al   |    3 | Ann   |    7 |
| Bob  |    1 | Ann   |    7 |
| Ed   |    6 | Linda |    5 |
| Ted  |    2 | Linda |    5 |
| Al   |    3 | Linda |    5 |
| Bob  |    1 | Linda |    5 |
+------+------+-------+------+
Inner Join replaces comma separator (INNER JOIN is alias of JOIN, assuming the word “RIGHT” or “LEFT” does not precede the word “JOIN”)

Useful clauses

FROM

ON

USING()

WHERE

SELECT COUNT(*), Name
FROM CountryLanguage INNER JOIN Country
ON CountryCode = Code
GROUP BY Name
HAVING COUNT(*) > 10;

When retrieving information from multiple tables, you have to qualify any columns that might appear in multiple tables

Self Joins

Joining a table to itself is ambiguous and can generate multiple errors

To properly perform a self-join, you need to alias your tablename:
SELECT t1.IndepYear, t1.Name, t2.Name
FROM Country AS t1 JOIN Country AS t2
ON t1.IndepYear = t2=IndepYear AND t1.Name = ‘Qatar’;
Once you specify an alias for a table, you must use the alias to refer to that table from there on out within the statement

Multi-table updates and deletes

MySQL allows join syntax in UPDATE and DELETE

UPDATE Operations

UPDATE t1, t2 SET t1.name=t2.name WHERE t1.id=t2.id;

DELETE Operations

DELETE FROM t1 FROM t1, t2 WHERE t1.id=t2.id;

DELETE FROM t1 USING t1, t2 WHERE t1.id=t2.id;

DELETE Operations that remove information from both tables

DELETE t1, t2 FROM t1, t2 WHERE t1.id=t2.id

DELETE FROM t1, t2 USING t1, t2 WHERE t1.id=t2.id;

Tags: development, htmlcenter, joins, MySQL, open-source, software, training
Posted in programming, software

Feb 28 MySQL Developer Training - Day 3 - First Half Posted at 12:01 pm | No Comments »

During the first half of day 3, we finished up the material that will be present on the MySQL Developer I examination. I’ve included my notes below:

Day 3

Manipulating Table Structure

Creating Tables

You can create tables from existing tables by adding “SELECT” OR “LIKE” queries to your CREATE TABLE statement.

Some examples:

CREATE TABLE `myTable` SELECT * FROM `myExistingTable`;

This will create a duplicate of the data in the existing table, but will not copy additional properties of the table, indices, etc. It will copy the column names, data types, etc., though.

CREATE TABLE `myTable` SELECT * FROM `myExistingTable` WHERE Population > 2000000;

This will create a duplicate of the existing table (with the same caveats listed above), but will only populate it with specific data that is pulled from the SELECT query

CREATE TABLE `myTable` SELECT * FROM `myExistingTable` WHERE 0;

This will create a duplicate of the existing table without populating any data. Again, it will not copy additional properties, etc.

CREATE TABLE `myTable` SELECT `col1`, `col2` FROM `myExistingTable`;

This will create a new table using only the columns that are requested in the SELECT query and will populate the table

You can specify “table-wide” features in the CREATE TABLE statement (ENGINE, CHARSET, COLLATE, etc., but cannot specify column-specific information, like PRIMARY KEY, INDEX, etc.)

CREATE TABLE `myTable` LIKE `myExistingTable`;

This will create an exact duplicate of the structure of your existing table

Foreign key constraints are not copied with LIKE statements

Temporary Tables

You can create a temporary table for temporary use.

Temporary tables are only available during the current session. They will be removed from the server when your session is terminated for any reason (time-out, server shuts down, you close your connection, etc.)

Temporary tables are only visible to the current session

They do not show up in the SHOW TABLES statement

The name of a temporary table can be the same as the name of an existing table in your database

Doing so will cause you not to be able to access the original table while the temporary table exists

Altering Table Structures

You can add new columns to your table using the ADD statement

ALTER TABLE EU_Countries ADD Id INT(6) NOT NULL;

ALTER TABLE EU_Countries ADD Id INT(6) NOT NULL AFTER Name;

http://dev.mysql.com/doc/refman/5.0/en/alter-table.html

You can remove columns from your table using the DROP statement

ALTER TABLE EU_Countries DROP Id;

You can modify column properties

ALTER TABLE EU_Countries MODIFY NewPopulation BIGINT(12) NOT NULL;

You can perform multiple alterations at the same time, adding, dropping, modifying, etc. all in one statement.

If you have multiple operations to perform, you should make sure to consolidate them into a single ALTER statement, because each ALTER statement completely rebuilds the table.

During execution of an ALTER statement, a write-lock is enforced on the table, but users can still read the information.

You can also use the CHANGE clause instead of the MODIFY clause, but it has a different syntax:

http://dev.mysql.com/doc/refman/5.0/en/alter-table.html

Renaming Tables

You can rename a table with an ALTER TABLE statement, as well.

ALTER TABLE t1 RENAME to t2;

Or, you can simply use the RENAME TABLE statement:

RENAME TABLE t1 TO t2;

Using the RENAME TABLE statement, you can rename multiple tables at once:

RENAME TABLE t1 TO t2, t2 TO t1, tmp TO t2;

Removing Tables

Using the DROP command, you can remove a table:

DROP TABLE t1

You can drop multiple tables at once

Foreign Keys

A foreign key is a column in a table that points/refers to a column in a parent table

Foreign keys should be indexed

InnoDB engine supports foreign key constraints, protecting the integrity of those foreign keys

In order to take advantage of the foreign key constraints, both the parent table and the child table must use the InnoDB engine

CREATE TABLE CountryParent
(Code CHAR(3) NOT NULL,
Name CHAR(52) NOT NULL,
PRIMARY KEY (Code)
) ENGINE=InnoDB;CREATE TABLE CityChild (
ID INT NOT NULL AUTO_INCREMENT,
Name CHAR(35) NOT NULL,
CountryCode CHAR(3) NOT NULL,
PRIMARY KEY(ID),
FOREIGN KEY(CountryCode)
REFERENCES CountryParent(Code)
ON UPDATE CASCADE
ON DELETE CASCADE
) ENGINE = InnoDB;

Using ON UPDATE CASCADE will cause all of the child’s information to be updated automatically if the parent’s information is updated

Using ON DELETE CASCADE will cause all of the child’s information to be automatically removed if the parent’s information is removed

Both columns (the parent table’s column and the child table’s foreign key column) should have the same data type

http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html

Manipulating Table Data

Inserting information into tables

Using the INSERT statement, you can insert new rows into a table
INSERT INTO table_name [(column_list)] VALUES (value_list);

You can insert multiple rows of information with a single INSERT statement:
INSERT INTO table_name [(column_list)] VALUES (value_list1),(value_list2),(value_list3);

You can use a different syntax for the INSERT statement:
INSERT INTO table_name SET column_name=’value‘, column_name=’value‘…;

You can also insert information from one table into another table:
INSERT INTO table2 SELECT * FROM table1;
INSERT INTO table2 [(column_list)] SELECT [(column_list)] FROM table1;

LAST_INSERT_ID() will retrieve the last auto_incremented id from the last insert that you performed

If you perform a multiple row insert, LAST_INSERT_ID() will return the first ID from your insert statement, rather than retrieving the highest value

Removing information from tables

Using the DELETE statement, you can remove existing rows from a table

DELETE FROM table_name [WHERE where_condition][ORDER BY...][LIMIT row_count];

Altering information in tables

Using the UPDATE statement, you can change the information in specific rows

UPDATE table_name SET column_name = column_value [WHERE...] [ORDER BY...] [LIMIT...];

You can use the REPLACE INTO command to perform updates and inserts at the same time if you name a PRIMARY KEY or UNIQUE element within your column list

If an item specified in your values already exists as a unique item in your table, that row will be deleted and a new one will be created with the new information you supply

It will not use any of the information that was in the previous version of the row. It will only insert the information you supply

If no match is found, it will insert a new row

INSERT with ON DUPLICATE KEY command

http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

Emptying a table

TRUNCATE TABLE will efficiently empty out a table

Transactions

What is a transaction?

When concurrent statements are needed

Mechanism for grouping multiple statements

groups multiple statements together that will all be executed together

All or none succeed

If an error occurs on any of the statements within your transaction, then the entire transaction will fail

Execute if all good

Cancel if error or incomplete

A good example of transactions is a bank transfer

In order to transfer funds from a checking account to a savings account, it needs to be handled as a single transaction:

First, you need to make sure that there are sufficient funds in the checking account

Next, you need to withdraw the money from the checking account

Finally, you need to add that money into the savings account

If any of those actions fail, you need to void the entire transaction

You can use the SHOW ENGINES command to see which database engines support transactions

ACID

Atomic

All statements execute successfully or are canceled as a unit

Consistent

Database that is in a consistent state when a transaction begins, is left in a consistent state by the transaction

If you subtract something from one area and add it to another area, you need to be sure that the total is still the same as it was before you started

Isolated

One transaction does not affect another

Durable

All changes made by transaction that complete successfully are recorded properly in database

Changes are not lost

Transaction Commands

START TRANSACTION

Begins the transaction, meaning that any statements that fall between “START TRANSACTION” and “COMMIT” will be treated as a single transaction

START TRANSACTION temporarily suspends AUTOCOMMIT, meaning that nothing will be committed until you explicitly commit the information

Is only necessary if AUTOCOMMIT is turned on

Can be aliased as “BEGIN” or “BEGIN WORK”

Whenever you use “START TRANSACTION”, it implicitly commits any items that came before

COMMIT

Execute and finalize all of the statements in the transaction

ROLLBACK

Cancel all of the statements that are part of the transaction

SET AUTOCOMMIT

AUTOCOMMIT is, by default, set to 1

If AUTOCOMMIT is turned on, each statement is treated as a transaction and will be automatically committed if the statement executes successfully

Statements that include multiple commands will be rolled back entirely if anything within that statement fails

Isolation Levels

Concurrent transactions can cause problems

Storage engines implement isolation levels

Controls level of visibility between transactions

May vary per database servers

Three common problems

“Dirty” read - seeing changes that have not been committed

Non-repeatable read

Phantom row - rows that have been added from another thread during your transaction

InnoDB implements four isolation levels

Read uncommitted

You can see changes from another thread’s transaction that have not yet been committed

Read committed

You cannot see changes that have not been committed, but after it’s been committed, you will see the change, even if you have read the row during your transaction

Repeatable read

You will not see changes from others’ transactions on any rows that you have read during your transaction

Serializable

Other transactions will not affect your transaction in any way. Rows selected by one transaction cannot be changed by other transactions until the original transaction has completed

Setting the isolation level

Use the –transaction-isolation option

SET TRANSACTION ISOLATION LEVEL

SET TX_ISOLATION

MVCC - Multi-version concurrency control

A method of isolating sessions from each other during transactions

http://en.wikipedia.org/wiki/Multiversion_concurrency_control

Locking Concepts

A locking mechanism prevents problems with concurrent data access

Locks are managed by the server

Allows access to one client and locks others out

InnoDB supports two types of locking

Lock in share mode - locks each row with a shared lock

Other people can still read the row(s), but no one (including yourself) can update anything within those rows until all locks are released

If you attempt to read a row by using a WHERE clause that includes something other than an index, then all rows in the table will be locked

SELECT * FROM Country WHERE Code=’AUS’ LOCK IN SHARE MODE\G

For update - locks each row with an exclusive lock

Other people can still read the row, but only you have permission to update the row until you release the lock

SELECT counter_field FROM child_codes FOR UPDATE;
UPDATE child_codes SET counter_field = counter_field + 1;

Tags: development, foreign key, htmlcenter, MySQL, open-source, relational database, software, tables, training, transaction
Posted in programming, software

Feb 26 MySQL Developer Training - Day 2 Posted at 3:06 pm | 1 Comment »

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:

You need to convert something else (another kind of database, a spreadsheet, etc.)

You are creating the database from scratch

Four basic relational types:

one pointing to one (1->1)

one pointing to many (1->M)

many pointing to one (M->1)

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

Tags: development, htmlcenter, MySQL, open-source, software, training
Posted in programming, software

Feb 24 Sun Microsystems to Acquire MySQL Posted at 7:39 pm | 1 Comment »

A few weeks ago, I took a week-long training course on MySQL development. The course was fantastic, but extremely intensive. I learned quite a bit about using MySQL.

In addition to all of the MySQL development I picked up during the course, I was also privy to some “inside” information about Sun’s possible acquisition of MySQL.

First of all, although it is 99.9% sure that Sun will acquire MySQL, it’s not an absolute certainty, yet. There is still quite a bit of negotiating to be done before the acquisition goes through.

Secondly, although a lot of people seem to think this acquisition spells doom for MySQL and its open-source, free software, it should be an extremely good thing for MySQL and Sun’s products. Rumor has it that Sun decided to acquire MySQL because of the incredible experience MySQL has monetizing open-source software. Sun has had trouble with that proposition since the beginning. MySQL has done fairly well with it.

Sun decided to pick up MySQL as much for their business sense in the open-source software world as for the product they produce.

On a side note, while this probably won’t do anything phenomenal for those of us interacting with MySQL through PHP, developers using Java will probably have a whole lot more support and tools at their fingertips than ever before.

Over the next week or two, I will be posting my notes from the MySQL developer course. They might not make a whole lot of sense, but hopefully they’ll do somebody some good. I know they’ll do a lot of good for me in the future. Hopefully I’ll be able to get certified in MySQL before too much longer.

Tags: acquisition, business, htmlcenter, java, MySQL, open-source, software, sun
Posted in programming, software

Feb 22 Free YouTube to iPod Converter Posted at 6:41 pm | 4 Comments »

I came across a cool, free utility the other day while I was searching for ways to get video prepared for my Zune (which I’ll review at a later date). The utility is simply called “Free YouTube to iPod Converter”. The utility was developed by DVDVideoSoft and it is extremely useful.

Basically, the software was designed with one purpose in mind, and it does that very well. It converts Flash Video files to video that’s compatible with your mobile devices. It’s main purpose is to convert YouTube videos to the iPod mp4 video format, but it does do a few other things, too.

First of all, not only can it convert FLV files from your hard drive into mp4 video files, it is also capable of extracting an FLV directly from YouTube. All you do is plug in the URL for the video you want to convert, and the utility accesses YouTube automatically, finds the video file embedded in the page, and extracts it for conversion. Unfortunately, it seem to only work on YouTube; it doesn’t seem to be capable of extracting FLV files from other video sites.

In addition to being able to convert FLV files to iPod video (giving you four options for the quality of the output video), it can also convert your FLV files to three quality levels of PSP video, three quality levels of mobile phone-compatible mp4 video and four different quality levels of mp3 audio.

The only issue I’ve come across in the software so far (and some people may consider this a pro for the software as opposed to a con), is that it doesn’t throw any errors out if it it fails to download the entire FLV file from YouTube. Instead, the progress indicator jumps from wherever it encountered the problem straight to 100%, and the utility tells you that it’s done converting the video. Sometimes you get the whole video, other times you only end up with a few seconds of video before it cuts off.

You can download the utility for free and view a tutorial explaining how to use the software from the main project Web site. If you have a digital media player, a PSP or a video-capable mobile phone, you should definitely get ahold of this application.

Tags: flv, freeware, htmlcenter, ipod, mp4, psp, utility, video, youtube, zune
Posted in software

Jan 12 BluePrint for Outlook Posted at 9:53 pm | No Comments »

For those of you that have installed Office 2007 on your computers, you’ve probably noticed a big problem in the new Outlook.

In previous versions of Outlook, HTML-formatted e-mail messages used the Internet Explorer print dialog, while text-only messages used the print dialog written for Outlook. The print dialog that’s been written for Outlook only gives you three options when choosing which pages to print: all, even or odd.

(more…)

Posted in software

Dec 3 A Free New Tool From TechSmith Posted at 6:10 pm | 1 Comment »

First of all, I am still sick, so this is going to be a short post, but I wanted to get something posted and I wanted to get this information out there for people.

TechSmith, the company that brought us Camtasia Studio, has released a new tool that’s completely free. The tool is called “Jing”, and from the limited time I’ve had to play with it, it seems wonderful.

Jing isn’t completely new. In fact, it’s been around for about six months. However, a new release was made on Nov. 28, 2007 that, according to the changelog, includes a lot of monumental improvements. I’m not sure how long Jing will be a free product, but as long as it is, I encourage you to try it out.

(more…)

Posted in software

Categories

Archives

Archive for the ‘software’ Category

Mar 30 Karaoke Revolution - American Idol Encore for the Wii Posted at 1:49 pm | No Comments »

Mar 5 MySQL Developer Training - Day 5 Posted at 5:10 pm | No Comments »

Day 5

DECLARE statement

Triggers

Creating triggers

Trigger error handling

Disallowed statements

Storage Engines

Available Storage Engines

MyISAM Storage Engine

InnoDB Storage Engine

Memory Engine

Optimization

Mar 3 MySQL Developer Training - Day 4 Posted at 3:16 am | No Comments »

Day 4

Subqueries

Why use a subquery?

Other Subquery Uses

Views

CREATE VIEW statement

It is possible to update a table through a view

Insertable Views

Checking Views

Altering Views

INFORMATION_SCHEMA

SHOW statements

Prepared statements

Prepared statements from MySQL

Preparing a statement

Exporting and Importing Data

Import Data using SQL

Export Data using SQL

Export using the mysqldump database backup client

Import using the mysqlimport client

Import data with the source command

Stored Routines

What is a stored routine?

Creating Stored Routines

Compound statements

Assign Variables

Variable Scope

Parameter declarations

Execute Stored Routines

Alterable stored routine characteristics

Non-alterable stored routine characteristics

Examine Stored Routines

Delete Stored Routines

Flow control statements

Mar 1 MySQL Developer Training - Day 3 - Second Half Posted at 12:11 pm | No Comments »

Day 3 - Second Half

Joins

Multi-table updates and deletes

Feb 28 MySQL Developer Training - Day 3 - First Half Posted at 12:01 pm | No Comments »

Day 3

Manipulating Table Structure

Creating Tables

Temporary Tables

Altering Table Structures

Renaming Tables

Removing Tables

Foreign Keys

Manipulating Table Data

Inserting information into tables

Removing information from tables

Altering information in tables

Emptying a table

Transactions

ACID

Transaction Commands

Isolation Levels

Locking Concepts

Feb 26 MySQL Developer Training - Day 2 Posted at 3:06 pm | 1 Comment »

Day 2

Data Type Overview

Character Sets

Binary String Data Types