MySQL Learning note 004

And then the previous write

Select a special column

For a chestnut: I want to give these pet people a day, I need to know the names of pet and their birthdays. What am I going to do?

The operation is as follows:

SELECT name, birth from pet;

mysql> SELECT Name, birth
from Pet;
+----------+------------+
| name | Birth |
+----------+------------+
| Whistler | 1997-12-09 |
| Puffball | 1999-03-30 |
| Fluffy | 1993-02-04 |
| Claws | 1994-03-17 |
| Buffy | 1989-05-13 |
| Fang | 1990-08-27 |
| Bowser | 1989-08-31 |
| Chirpy | 1998-09-11 |
| Slim | 1996-04-29 |
+----------+------------+
9 Rows in Set (0.00 sec)

Mysql>

So that I can give these pet's birthday.

One more chestnut: No story, I just want to see who the owners of these little angels are?

The operation is as follows:

SELECT owner from Pet;

Mysql> SELECT Owner
from Pet;
+--------+
| Owner |
+--------+
| Gwen |
| Diane |
| Harold |
| Gwen |
| Harold |
| Benny |
| Diane |
| Gwen |
| Benny |
+--------+
9 Rows in Set (0.00 sec)

Mysql>

You will find that you query to the owner there are a lot of repeat ah, this is very annoying.

To get their values to appear once, add a keyword DISTINCT

SELECT DISTINCT owner from Pet;

Mysql> SELECT DISTINCT Owner
from Pet;
+--------+
| Owner |
+--------+
| Gwen |
| Diane |
| Harold |
| Benny |
+--------+
4 rows in Set (0.00 sec)

Mysql>

It looks more comfortable.

Bring a chestnut again: to inquire about the date of birth of the dog and cat

The operation is as follows:

SELECT name, species, birth from pet WHERE species = ' dog ' OR species = ' cat ';

mysql> SELECT name, species, birth
From pet
WHERE species = ' dog ' OR species = ' cat ';
+--------+---------+------------+
| name | Species | Birth |
+--------+---------+------------+
| Fluffy | Cat | 1993-02-04 |
| Claws | Cat | 1994-03-17 |
| Buffy | Dog | 1989-05-13 |
| Fang | Dog | 1990-08-27 |
| Bowser | Dog | 1989-08-31 |
+--------+---------+------------+
5 rows in Set (0.00 sec)

Mysql>

Category lines

Young man, you may have noticed that the result of the previous pest is not well sequenced. This is for your old Sakamoto to see, is to criticized (although your stupid old Sakamoto may not understand). So, there's an order by this keyword

Give a chestnut: sort the date of birth of pet. Don't ask why, bored.

The operation is as follows:

SELECT name, birth from pet ORDER by birth;

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 |
+----------+------------+
9 rows in Set (0.18 sec)

Mysql>

It looks like it's better (and eggs).

By the way one more mouth, there is an order by BINARY Col_name himself Baidu check .

The default sort is ascending, with the smallest value in the first row.

If (want to sort in descending order) {

Add the desc (descending) keyword to a sorted column name

｝

The operation is as follows:

SELECT name, birth from pet ORDER by birth DESC;

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 |
+----------+------------+
9 Rows in Set (0.00 sec)

Mysql>

You can also sort multiple columns, and you can sort different columns in different directions. (The book is very complicated.)

For example: Sort the animals in ascending order, then sort the animals according to their birthdays in descending order (youngest animal in front)

The operation is as follows:

SELECT name, species, birth from pet ORDER by species, birth DESC;

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 |
+----------+---------+------------+
9 rows in Set (0.08 sec)

Mysql>

My understanding: Level two sorting, ascending row animal V, descending row age.

Note: (The book is really for you to break the heart) The DESC keyword, only affects the birth, does not affect the species.

Just write it down here. ヾ (≧o≦) AW ~

MySQL Learning note 004