首页 > 代码库 > Mysql学习笔记004

Mysql学习笔记004

  接着前面的写

选择特殊的列

举个栗子:我想给这些pet们过身日,我就要知道pet们的名字和他们的生日。我该怎么办?

操作如下:

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>

这样我就可以给这些pet们过生日了。

 

再举一个栗子:没有故事,我就是想看一看这些小天使们的主人都有谁?

操作如下:

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会发现you查询到的 owner 中有好多是重复的诶,这好烦啊。

为了让他们值出现一次 ,加一个关键字 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>

这下子看着舒服多了

 

再带条件举一个栗子:要想查询狗和猫的出生日期 

操作如下:

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>

 

分类行

  年轻人,你可能注意到了,前面的栗子中的结果,是没有排好序的。这要是给你的老坂看的话,是要挨批的(虽然你愚蠢的老坂可能看不懂)。所以,就有了ORDER BY 这个关键字

举个栗子:给pet们的出生日期排序。 不要问为什么,无聊。

操作如下:

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>

这样看起来就better了呢(然并卵)。

 顺便多一句嘴,有一个ORDER BY BINARY col_name 自己百度自己查

默认的排序是升序,最小的值在第一行。 

if(想要以降序排列){

    在排序的列名上增加DESC(降序)关键字

操作如下:

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>

 

还可以对多个列进行排序, 并且可以按不同的方向对不同的列进行排序。(书上写的好复杂啊)

举个例子:按升序对动物的种类进行排序,然后按降序根据生日对各动物种类进行排序(最年轻的动物在最前面)

操作如下:

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>

我的理解:二级排序,升序排动物五中,降序排年龄。

注意:(书真是为了你们操碎了心)DESC 关键字, 只影响了birth, 不影响species。

 

先写到这里吧。ヾ(≧O≦)〃嗷~

技术分享

 

Mysql学习笔记004