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(3)
the following snippet illustrates a succussful query in which the goal is to modify a data cell located in
table01of a data type which does NOT appear in
The only logical relationship between
table02is 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,
table01which 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:
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.
- 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.
- 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_namewhen you create it.
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
SELECTstatement are used for the view column names. To define explicit names for the view columns, the optional
column_listclause can be given as a list of comma-separated identifiers. The number of names in
column_listmust be the same as the number of columns retrieved by the
A view can be created from many kinds of
SELECTstatements. It can refer to base tables or other views. It can use joins,
UNION, and subqueries. The
SELECTneed 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:
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:
SELECTstatement cannot contain a subquery in the
SELECTstatement cannot refer to system or user variables.
SELECTstatement 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
- The definition cannot
refer to a
TEMPORARYtable, and you cannot create a
- 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
INSERTinserts a row into a table that has an
AUTO_INCREMENTcolumn, 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
INSERTstatements with respect to
AUTO_INCREMENTcolumns is discussed further in Section 12.9.3, “Information Functions”, and Section 126.96.36.199, “
INSERTstatement supports the following modifiers:
If you use the
IGNOREkeyword, errors that occur while executing the
INSERTstatement are treated as warnings instead. For example, without
IGNORE, a row that duplicates an existing
PRIMARY KEYvalue 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
IGNOREis 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
PRIMARY KEY, an
UPDATEof the old row is performed. See Section 188.8.131.52, “
INSERT ... ON DUPLICATE KEY UPDATESyntax”.
† – Commentary from your author at Novice·Notes
Key Terms Refrences:
UPDATE Syntax, MySQL 5.1 Reference Manual, Ch 13 Sect 2.10; MySQL AB Â© 1995-2007; Available at: http://dev.mysql.com/doc/refman/5.1/en/update.html#update ; Accessed: May-20, 2007
4MySQL:Understanding Joins Tutorials. BrainBell.com. Rev (n/a). Available at: http://www.brainbell.com/tutorials/MySQL/Understanding_Joins.htm . Accessed: Mar-15-2007.
MySQL Manual Refrences:
(1)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
(2)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