MySQL的高级用法(1)

chouy

  
这个是原始表格，以后所有的讲解都是以下面的内容为标准进行查询。

mysql> SELECT * FROM pet;
+----------+--------+---------+------+------------+------------+
| name     | owner  | species | sex  | birth      | death      |
+----------+--------+---------+------+------------+------------+
| Fluffy   | Harold | cat     | f    | 1993-02-04 | NULL       |
| Claws    | Gwen   | cat     | m    | 1994-03-17 | NULL       |
| Buffy    | Harold | dog     | f    | 1989-05-13 | NULL       |
| Fang     | Benny  | dog     | m    | 1990-08-27 | NULL       |
| Bowser   | Diane  | dog     | m    | 1979-08-31 | 1995-07-29 |
| Chirpy   | Gwen   | bird    | f    | 1998-09-11 | NULL       |
| Whistler | Gwen   | bird    | NULL | 1997-12-09 | NULL       |
| Slim     | Benny  | snake   | m    | 1996-04-29 | NULL       |
| Puffball | Diane  | hamster | f    | 1999-03-30 | NULL       |
+----------+--------+---------+------+------------+------------+

这句好像是可以在mysql提示符下加载文本文件。

mysql> LOAD DATA LOCAL INFILE 'pet.txt' INTO TABLE pet;
以下是几个高级查找语句。

mysql> SELECT * FROM pet WHERE species = 'snake' OR species = 'bird';
+----------+-------+---------+------+------------+-------+
| name     | owner | species | sex  | birth      | death |
+----------+-------+---------+------+------------+-------+
| Chirpy   | Gwen  | bird    | f    | 1998-09-11 | NULL  |
| Whistler | Gwen  | bird    | NULL | 1997-12-09 | NULL  |
| Slim     | Benny | snake   | m    | 1996-04-29 | NULL  |
+----------+-------+---------+------+------------+-------+
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 * FROM pet WHERE (species = 'dog' OR species = 'cat') AND owner = 'harold';

Chat Messenger:
mysql> DELETE FROM `messenger` WHERE time_row  
time_row : integer
Delete all messages down 30 seconds

以下是消除重复：

mysql> SELECT DISTINCT owner FROM pet;
mysql> SELECT name, species, birth FROM pet
    -> ORDER BY species, birth DESC;
+----------+---------+------------+
| name     | species | birth      |
+----------+---------+------------+
| Chirpy   | bird    | 1998-09-11 |
| Whistler | bird    | 1997-12-09 |
| Claws    | cat     | 1994-03-17 |
| Fluffy   | cat     | 1993-02-04 |
| Fang     | dog     | 1990-08-27 |
| Bowser   | dog     | 1989-08-31 |
| Buffy    | dog     | 1989-05-13 |
| Puffball | hamster | 1999-03-30 |
| Slim     | snake   | 1996-04-29 |
+----------+---------+------------+
If you want to explicity specify the order of which 'order by' comes back in, like if you had a priority field that had the values "Low" "High" or "Medium" .. do this:

mysql> select * from tablename order by priority='High' DESC, priority='Medium' DESC,
    -> priority='Low' DESC;

Sometimes you might want to sort names. If you have First and Last names in one field, seperated by a blank, you can do this by:

mysql> SELECT * FROM my_addressbook ORDER BY SUBSTRING_INDEX(name, ' ', -1) ASC

** Order By number Like this Number was a Text **
Some times you need to order by a column that contains numbers, but as
if it would be text, example:
Field Name: Numbers
Type: Integer(11)
Data:
+----------+
| numbers  |
+----------+
| 10       |
| 500      |
| 1        |
| 3000     |
| 20       |
| 50       |
| 30       |
| 1000     |
+----------+

Normal Query.

mysql> SELECT numbers from table order by numbers
+----------+
| 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:

mysql> SELECT left(numbers, 11) as numbersSTR from table order by numbersSTR
+------------+
| numbersSTR |
+------------+
| 1          |
| 10         |
| 1000       |
| 20         |
| 30         |
| 3000       |
| 50         |
| 500        |
+------------+

以下方法也可以，但是据说效率太低。
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.

A common question on the mailing lists is how to sort results in a particular order. Just use the FIELD function:
mysql> SELECT * FROM tickets
    -> ORDER BY FIELD(priority, 'High', 'Normal', 'Low', 'The Abyss');

mysql> SELECT name, birth, CURDATE(),
-> (YEAR(CURDATE())-YEAR(birth))
-> - (RIGHT(CURDATE(),5)
-> AS age
-> FROM pet;
+----------+------------+------------+------+
| name     | birth      | CURDATE()  | age  |
+----------+------------+------------+------+
| Fluffy   | 1993-02-04 | 2003-08-19 | 10   |
| Claws    | 1994-03-17 | 2003-08-19 | 9    |
| Buffy    | 1989-05-13 | 2003-08-19 | 14   |
| Fang     | 1990-08-27 | 2003-08-19 | 12   |
| Bowser   | 1989-08-31 | 2003-08-19 | 13   |
| Chirpy   | 1998-09-11 | 2003-08-19 | 4    |
| Whistler | 1997-12-09 | 2003-08-19 | 5    |
| Slim     | 1996-04-29 | 2003-08-19 | 7    |
| Puffball | 1999-03-30 | 2003-08-19 | 4    |
+----------+------------+------------+------+




本文来自ChinaUnix博客，如果查看原文请点：http://blog.chinaunix.net/u/10921/showart_80710.html