My life, MySQL and Me

Create a Comma Separated Values (CSV) Document

Did you know that it’s a cinch to create a .csv-formatted file from a MySQL database, simply by executing a little bit of the proper SQL on your table? Don’t believe it? Well, believe it because, it’s TRUE!

The following SQL query is copied directly from the official MySQL 5.0 documentation chapter covering the SELECT statement. (Note: the complete MySQL documentation is freely available to the public at dev.mysql.com):

SELECT a,b,a+b INTO OUTFILE '/tmp/result.txt'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  FROM test_table;

UPDATE with JOIN

UPDATE table01 LEFT JOIN table02⁽³⁾

the following snippet illustrates a succussful query in which the goal is to modify a data cell located in table01 of a data type which does NOT appear in table02.

The only logical relationship between table01 and table02 is a Foreign Key. This Key is associated with the main field of interest in Table02, in this case a product Item; a guitar. The key represents the corresponding Manufacturer, but not by that manufacturer’s name, but by an id in the manufacturer’s table, table01 which of course does share the same row with the manufacturer’s name in it’s own table.

The need for this JOIN presents itself when there is a need to modify the manufacturer name but we’re dealing only with the details of the item itself. We have the item’s own id. With that id, we can identify which row of the item database should be targeted, and through that targeting we can identify that foreign key which we can carry over into the manufacturer table and finally pinpoint the appropriate data for updating.

This special JOIN^(3b) looks like this:

“UPDATE table_01 LEFT JOIN table_02
ON table_01.man_id=table_02.man_id 
SET table_01.man_name='New Guitar Manufacturer'
WHERE (`table_02`.`guitar_id` =12) 
LIMIT 1;”

^(3b)The SQL shown here is based loosely on a user-submitted example, listed in Ch.13 of the MySQL5 Reference Manual.

Command Line MySQL Administration:

Log in to MySQL in Linux via the command line using the following syntax.

user@localhost bin]$ /usr/local/bin/mysql -uusername -ppassword

This is important to know, as one might expect to use a “space” between the -u or -p arguments, but it is not a typo above. the username, and password are supposed to be directly after the argument, no spaces between!

Other NoviceNotes Database Topics:

• Relational Database, MySQL and MS Access

Getting to Know your Open-source Database:

Note: This is NOT an official or authorized representation of the MySQL manual, and should not be copied. This text is published for the purpose that the Web Log author might reference this material from any location, at any time. The material written below has been edited extensively from its original form to serve as nothing more than a Quick Reference for the author. IF YOU SEEK to learn about MySQL, please leave this page now and select the Official Documentation for the version of MySQL database you are using.

Key Terms:

JOIN⁴

Simply put, a join is a mechanism used to associate tables within a SELECT statement (and thus the name join). Using a special syntax, multiple tables can be joined so a single set of output is returned, and the join associates the correct rows in each table on-the-fly.

For example†:

SELECT tbl-field_one, tbl-field_two, tbl-field_three, tbl-field_four
FROM table_one, table_two
WHERE table_one.tbl-field_two = table_two.tble-field_one
ORDER by table_one.tbl-field_one

VIEW⁽²⁾

A view belongs to a database. By default, a new view is created in the default database. To create the view explicitly in a given database, specify the name as db_name.view_name when you create it.

mysql> CREATE VIEW test.v AS SELECT * FROM t;

Base tables and views share the same namespace within a database, so a database cannot contain a base table and a view that have the same name.

Views must have unique column names with no duplicates, just like base tables. By default, the names of the columns retrieved by the SELECT statement are used for the view column names. To define explicit names for the view columns, the optional column_list clause can be given as a list of comma-separated identifiers. The number of names in column_list must be the same as the number of columns retrieved by the SELECT statement.

A view can be created from many kinds of SELECT statements. It can refer to base tables or other views. It can use joins, UNION, and subqueries. The SELECT need not even refer to any tables. The following example defines a view that selects two columns from another table, as well as an expression calculated from those columns:

mysql> CREATE TABLE t (qty INT, price INT);

mysql> INSERT INTO t VALUES(3, 50);
mysql> CREATE VIEW v AS SELECT qty, price, qty*price AS value FROM t;
mysql> SELECT * FROM v;
+------+-------+-------+
| qty  | price | value |
+------+-------+-------+
|    3 |    50 |   150 |
+------+-------+-------+

A view definition is subject to the following restrictions:

• The SELECT statement cannot contain a subquery in the FROM clause.
• The SELECT statement cannot refer to system or user variables.
• The SELECT statement cannot refer to prepared statement parameters.
• Within a stored routine, the definition cannot refer to routine parameters or local variables.
• Any table or view referred to in the definition must exist. However, after a view has been created, it is possible to drop a table or view that the definition refers to. In this case, use of the view results in an error. To check a view definition for problems of this kind, use the CHECK TABLE statement.
• The definition cannot

  refer to a TEMPORARY table, and you cannot create a TEMPORARY view.

• The tables named in the view definition must already exist.
• You cannot associate a trigger with a view.

MySQL 5.0 Manual 13.2.4 Excerpt

This excerpt, or paraphrased info is discussed in detail at MySQL.com under the section: INSERT Query

INSERT⁽¹⁾

If INSERT inserts a row into a table that has an AUTO_INCREMENT column, you can find the value used for that column by using the SQL LAST_INSERT_ID() function. PHP uses the mysql_insert_id() function to return this data. However, you should note that the two functions do not always behave identically. The behavior of INSERT statements with respect to AUTO_INCREMENT columns is discussed further in Section 12.9.3, “Information Functions”, and Section 22.2.3.37, “mysql_insert_id()”.

The INSERT statement supports the following modifiers:

• [...SNIP...]

• If you use the IGNORE keyword, errors that occur while executing the INSERT statement are treated as warnings instead. For example, without IGNORE, a row that duplicates an existing UNIQUE index or PRIMARY KEY value in the table causes a duplicate-key error and the statement is aborted. With IGNORE, the row still is not inserted, but no error is issued. Data conversions that would trigger errors abort the statement if IGNORE is not specified. With IGNORE, invalid values are adjusted to the closest values and inserted; warnings are produced but the statement does not abort. You can determine with the mysql_info() C API function how many rows were actually inserted into the table.

• If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row is performed. See Section 13.2.4.3, “INSERT ... ON DUPLICATE KEY UPDATE Syntax”.

MySQL Manual Refrences:
⁽¹⁾MySQL 5.0 Reference Manual: 3.2, Entering Queries. MySQL AB. (Rev. 5256) 2007-03-08. Available at http://dev.mysql.com/doc/refman/5.0/en/entering-queries.html ; Accessed 2007-Mar-08
⁽²⁾MySQL 5.0 Reference Manual: 19.2, CREATE VIEW Syntax. MySQL AB. (revision: n/a). Available at http://dev.mysql.com/doc/refman/5.0/en/create-view.html; Accessed 2007-Mar-20