## Display:

Daughter -
Dog -

Success! The LEFT JOIN preserved every family member, including those who don't yet have a favorite meal in the food table! Please feel free to play around with LEFT JOIN until you feel like you have a solid grasp of it. This stuff isn't easy!

Another example for 'JOIN', 'LEFT JOIN' and 'RIGHT JOIN' is here:

http://www.wellho.net/mouth/158_MySQL-LEFT-JOIN-and-RIGHT-JOIN-INNER-JOIN-and-OUTER-JOIN.html

### mysql, simple commands 2008

Selecting a database:mysql> USE database;Listing databases:mysql> SHOW DATABASES;Listing tables in a db:mysql> SHOW TABLES;Describing the format of a table:mysql> DESCRIBE table;Creating a database:mysql> CREATE DATABASE db_name;Creating a table:mysql> CREATE TABLE table_name (field1_name TYPE(SIZE), field2_name TYPE(SIZE));Ex: mysql> CREATE TABLE pet (name VARCHAR(20), sex CHAR(1), birth DATE);Load tab-delimited data into a table:mysql> LOAD DATA LOCAL INFILE "infile.txt" INTO TABLE table_name;(Use \n for NULL)Inserting one row at a time:mysql> INSERT INTO table_name VALUES ('MyName', 'MyOwner', '2002-08-31');(Use NULL for NULL)Retrieving information (general):mysql> SELECT from_columns FROM table WHERE conditions;All values: SELECT * FROM table;Some values: SELECT * FROM table WHERE rec_name = "value";Multiple critera: SELECT * FROM TABLE WHERE rec1 = "value1" AND rec2 = "value2";Reloading a new data set into existing table:mysql> SET AUTOCOMMIT=1; # used for quick recreation of tablemysql> DELETE FROM pet;mysql> LOAD DATA LOCAL INFILE "infile.txt" INTO TABLE table;Fixing all records with a certain value:mysql> UPDATE table SET column_name = "new_value" WHERE record_name = "value";Selecting specific columns:mysql> SELECT column_name FROM table;Retrieving unique output records:mysql> SELECT DISTINCT column_name FROM table;Sorting:mysql> SELECT col1, col2 FROM table ORDER BY col2;Backwards: SELECT col1, col2 FROM table ORDER BY col2 DESC;Date calculations:mysql> SELECT CURRENT_DATE, (YEAR(CURRENT_DATE)-YEAR(date_col)) AS time_diff [FROM table];MONTH(some_date) extracts the month value and DAYOFMONTH() extracts day.Pattern Matching:mysql> SELECT * FROM table WHERE rec LIKE "blah%";(% is wildcard - arbitrary # of chars)Find 5-char values: SELECT * FROM table WHERE rec like "_____";(_ is any single character)Extended Regular Expression Matching:mysql> SELECT * FROM table WHERE rec RLIKE "^b$";(. for char, [...] for char class, * for 0 or more instances^ for beginning, {n} for repeat n times, and$ for end)(RLIKE or REGEXP)To force case-sensitivity, use "REGEXP BINARY"Counting Rows:mysql> SELECT COUNT(*) FROM table;Grouping with Counting:mysql> SELECT owner, COUNT(*) FROM table GROUP BY owner;(GROUP BY groups together all records for each 'owner')Selecting from multiple tables:(Example)mysql> SELECT pet.name, comment FROM pet, event WHERE pet.name = event.name;(You can join a table to itself to compare by using 'AS')Currently selected database:mysql> SELECT DATABASE();Maximum value:mysql> SELECT MAX(col_name) AS label FROM table;Auto-incrementing rows:mysql> CREATE TABLE table (number INT NOT NULL AUTO_INCREMENT, name CHAR(10) NOT NULL);mysql> INSERT INTO table (name) VALUES ("tom"),("dick"),("harry");Adding a column to an already-created table:mysql> ALTER TABLE tbl ADD COLUMN [column_create syntax] AFTER col_name;Removing a column:mysql> ALTER TABLE tbl DROP COLUMN col;(Full ALTER TABLE syntax available at mysql.com.)Batch mode (feeding in a script):# mysql -u user -p <> source batch_file;Backing up a database with mysqldump:# mysqldump --opt -u username -p database > database_backup.sql(Use 'mysqldump --opt --all-databases > all_backup.sql' to backup everything.)(More info at MySQL's docs.)Join tables:# SELECT * FROM cars JOIN colors ON colors.car_ID=cars.id# SELECT * FROM cars AS cr JOIN colors AS cl ON cl.car_ID=cr.idAfter the ON, we state on wich columns the tables should join. Every color has a reference to a car by the "car_ID" column. Every car has anID, and these two columns are the link between the two tables.

## Saturday, 15 November 2008

### HOWTO: [Lyx] replace the name of bibliography with reference

in lyx, to replace the name of bibliography with reference, using report as document class

\renewcommand{\bibname}{References}
marked as TeX

==================================

\newcommand{cmd}[args][opt]{def}
\renewcommand{cmd}[args][opt]{def}
\providecommand{cmd}[args][opt]{def} -- LaTeX2e

These commands define (or redefine) a command.


* args An integer from 1 to 9 denoting the number of arguments of the command being defined. The default is for the command to have no arguments.

* opt (LaTeX2e only) If present, then the first of the number of arguments specified by args is optional with a default value of opt; if absent, then all of the arguments are required.

* def The text to be substituted for every occurrence of cmd; a parameter of the form #n in cmd is replaced by the text of the nth argument when this substitution takes place.

Examples
\newcommand{\water}{H$_2$O}

This would allow one to write, e.g.,

The formula for water is \water.

or

\water\ is the formula for water.

Note, in the second case, the trailing \ followed by a blank is required to ensure a blank space after the H2O; LaTeX ignores the blank following a command, so the space has to be specifically inserted with the \.

As a second example consider

\newcommand{\hypotenuse}{$a^{2}+b^{2}$}

Note that this will produce the desired formula in text (paragraph) mode because of the $...$ in the definition. In math mode, however, the first $in the definition will cause LaTeX to leave math mode, causing problems. In LaTeX 2.09 a standard trick for getting around this is to put the math-mode expression in an \mbox, viz., \newcommand{\hypotenuse}{\mbox{$a^{2}+b^{2}\$}}

In LaTeX2e the \ensuremath command has been provided to alleviate this problem. The argument of the \ensuremath command is always processed in math mode, regardless of the current mode. Using this mechanism the above could be written as

\newcommand{\hypotenuse}{\ensuremath{a^{2}+y^{2}}}

## Thursday, 6 November 2008

### HOWTO: find

From man:
-exec command ;
Execute command; true if 0 status is returned. All following arguments to find are taken to be arguments to the command until an argument consisting of ;' is encountered. The string {}' is replaced by the current file name being processed everywhere it occurs in the arguments to the command, not just in arguments where it is alone, as in some versions of find. Both of these constructions might need to be escaped (with a \') or quoted to protect them from expansion by the shell. The command is executed in the starting directory.

find . -type f -exec file '{}' \; # '{}' or {}, and there is a space before '\';

Runs ‘file’ on every file in or below the current directory. Notice
that the braces are enclosed in single quote marks to protect them from
interpretation as shell script punctuation. The semicolon is similarly
protected by the use of a backslash, though single quotes could have
been used in that case also.

-----------------------------------------------------------------

{}' is replaced by the current file name being processed;
\ is used for the iteration of found files.
; is to tell -exec that the job is done.

Example: find . -exec grep 'title="Phoca Gallery"' {} \; -ls
Normally above example suits our search jobs.

## Monday, 3 November 2008

### Confusing terminology in Fedora and Ubuntu

What is called 'orphan', and what is 'obsolete'? Fedora and Ubuntu give us contradictive and confusing definition.

Let's have a look here.

=========================================
Fedora:

"package-cleanup(1)"
--orphans
List installed packages which are not available from currenly configured repositories.
--leaves
List leaf nodes in the local RPM database. Leaf nodes are RPMs that are not relied upon by any other RPM.

=========================================
Ubuntu:

--'synaptic-status-installed(local or obsolete)'
Display only packages that are not (for longer) included in one of the specified repositories.

--"deborphan"
deborphan finds "orphaned" packages on your system. It determines which packages have no other packages depending on their installation and shows you a list of these packages.

=========================================

Accordingly, orphans(fedora)=obsolete(ubuntu), leaves(fedora)=orphan(ubuntu).

London, United Kingdom