mysql

mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet;

If you created the file on Windows with an editor that uses \r\n as a line terminator, you should use this statement instead:

mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet    -> LINES TERMINATED BY '\r\n';

hint: mysql> LOAD DATA LOCAL INFILE '<dir>/pet.txt' INTO TABLE pet FIELDS terminated by '<delimiter>';

I used the full path name 'c:/<path to file>' and it worked fine, but my defaults aren't THE defaults. :-)

mysql> SELECT * FROM pet WHERE (species = 'cat' AND sex = 'm')    -> OR (species = 'dog' AND sex = 'f');+-------+--------+---------+------+------------+-------+| name  | owner  | species | sex  | birth      | death |+-------+--------+---------+------+------------+-------+| Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  || Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |+-------+--------+---------+------+------------+-------+

mysql> SELECT owner FROM pet;+--------+| owner  |+--------+| Harold || Gwen   || Harold || Benny  || Diane  || Gwen   || Gwen   || Benny  || Diane  |+--------+
Notice that the query simply retrieves the          owner column from each record, and some of          them appear more than once. To minimize the output, retrieve          each unique output record just once by adding the keyword          DISTINCT:        
mysql> SELECT DISTINCT owner FROM pet;+--------+| owner  |+--------+| Benny  || Diane  || Gwen   || Harold |+--------+
mysql> SELECT name, birth FROM pet ORDER BY birth;+----------+------------+| name     | birth      |+----------+------------+| Buffy    | 1989-05-13 || Bowser   | 1989-08-31 || Fang     | 1990-08-27 || Fluffy   | 1993-02-04 || Claws    | 1994-03-17 || Slim     | 1996-04-29 || Whistler | 1997-12-09 || Chirpy   | 1998-09-11 || Puffball | 1999-03-30 |+----------+------------+
          On character type columns, sorting—like all other          comparison operations—is normally performed in a          case-insensitive fashion. This means that the order is          undefined for columns that are identical except for their          case. You can force a case-sensitive sort for a column by          using BINARY like so:          ORDER BY BINARY          col_name.        
          The default sort order is ascending, with smallest values          first. To sort in reverse (descending) order, add the          DESC keyword to the name of the column you          are sorting by:        
mysql> SELECT name, birth FROM pet ORDER BY birth DESC;+----------+------------+| name     | birth      |+----------+------------+| Puffball | 1999-03-30 || Chirpy   | 1998-09-11 || Whistler | 1997-12-09 || Slim     | 1996-04-29 || Claws    | 1994-03-17 || Fluffy   | 1993-02-04 || Fang     | 1990-08-27 || Bowser   | 1989-08-31 || Buffy    | 1989-05-13 |+----------+------------+Query: SELECT numbers from table order by numbers

We have as result the following:

+----------+

| numbers  | 

+----------+

| 1        |

| 10       | 

| 20       |

| 30       |

| 50       |

| 500      |

| 1000     |

| 3000     |

+----------+



We must make a Query, turning the numeric(integer) field to text and

ordered at this last one, Example:

Query: SELECT left(numbers, 11) as numbersSTR from table order by numbersSTR

We have as result the following:

+------------+

| numbersSTR | 

+------------+

| 1          |

| 10         | 

| 1000       |

| 20         |

| 30         |

| 3000       |

| 50         |

| 500        |

+------------+

 
mysql> select number from (table) order by number;

+--------+

| number |

+--------+

| 1      |

| 10     |

| 2      |

| 3      |

| 4      |

| 5      |

| 6      |

| 7      |

| 8      |

| 9      |

+--------+



Use this:

mysql> select number from (table) order by (number+0);

+--------+

| number |

+--------+

| 1      |

| 2      |

| 3      |

| 4      |

| 5      |

| 6      |

| 7      |

| 8      |

| 9      |

| 10     |

+--------+

The (field + 0 ) converts the text/character in the field into an integer.