MySQL的高级用法(1)

 

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

 

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 < (UNIX_TIMESTAMP() - 1800)"

 

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    |
+----------+------------+------------+------+