mysql linux selected_MySQL的查询语句--SELECT

本来想总结高可用集群的另外几个实验呢，回头看看别人总结的内容，好精细，而且扩展了好多内容，惭愧的不行，还是先跳过了，呵呵~~~

介绍了简单的数据库操作等，接下来从细节入手，来介绍mysql的查询语句；

在这里导入了一个jiaowu数据库，来实现以下例题的操作：

先来看下这个数据库所包含的内容

mysql>show databases;

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

| Database |

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

| information_schema |

| jiaowu |

| mysql |

| test |

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

4 rows in set (0.00 sec)

l> use jiaowu;

Database changed

mysql>show tables;

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

| Tables_in_jiaowu |

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

| courses |

| scores |

| students |

| tutors |

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

4 rows in set (0.00 sec)mysql>select * from students;

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

| SID | Name | Age | Gender | CID1 | CID2 | TID | CreateTime |

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

| 1 | GuoJing | 19 | M | 2 | 7 | 3 | 2012-04-06 10:00:00 |

| 2 | YangGuo | 17 | M | 2 | 3 | 1 | 2012-04-06 10:00:00 |

| 3 | DingDian | 25 | M | 6 | 1 | 7 | 2012-04-06 10:00:00 |

| 4 | HuFei | 31 | M | 8 | 10 | 5 | 2012-04-06 10:00:00 |

| 5 | HuangRong | 16 | F | 5 | 9 | 9 | 2012-04-06 10:00:00 |

| 6 | YueLingshang | 18 | F | 8 | 4 | NULL | 2012-04-06 10:00:00 |

| 7 | ZhangWuji | 20 | M | 1 | 7 | NULL | 2012-04-06 10:00:00 |

| 8 | Xuzhu | 26 | M | 2 | 4 | NULL | 2012-04-06 10:00:00 |

| 9 | LingHuchong | 22 | M | 11 | NULL | NULL | 2012-04-06 10:00:00 |

| 10 | YiLin | 19 | F | 18 | NULL | NULL | 2012-04-06 10:00:00 |

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

10 rows in set (0.00 sec)mysql>select * from tutors;

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

| TID | Tname | Gender | Age |

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

| 1 2 | HuangYaoshi | M | 63 |

| 3 | Miejueshitai | F | 72 |

| 4 | OuYangfeng | M | 76 |

| 5 | YiDeng | M | 90 |

| 6 | YuCanghai | M | 56 |

| 7 | Jinlunfawang | M | 67 |

| 8 | HuYidao | M | 42 |

| 9 | NingZhongze | F | 49 |

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

9 rows in set (0.00 sec)

这是以下例题中会用到的数据，可以先参考下；

首先是mysql查询语句：

查询的分类：

单表查询：简单查询

多表查询：联结查询

子查询：复杂查询

联合查询

select语句：

常用函数：

##field--表示字段

count(*) 总行数

mysql>select count(*) from tutors;

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

| count(*) |

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

| 9 |

+---------+

1 row in set (0.00 sec)

max(field) 返回最大值

mysql>select max(age) from tutors;

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

| max(age) |

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

| 93 |

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

1 row in set (0.00 sec)

min(field) 返回最小值

avg(field) 平均值

mysql>select avg(age) from tutors;

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

| avg(age) |

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

| 67.5556 |

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

1 row in set (0.00 sec)

sum() 记和

mysql>select sum(1+2);

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

| sum(1+2) |

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

| 3 |

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

1 row in set (0.01 sec)

select 是挑选列的，where是挑选行的，二者结合起来才是将一个实体的属性整体显示出来

where 后面指定的是条件：

可以指定的条件有：

算术比较：

> , < , = , ！,>= , <=, <=> (取得的结果是空值也不会出错)

mysql>select name,age from students where age>=20;

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

| name | age |

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

| DingDian | 25 |

| HuFei | 31 |

| ZhangWuji | 20 |

| Xuzhu | 26 |

| LingHuchong | 22 |

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

5 rows in set (0.00 sec)

组合逻辑比较：

and

or

not(!)

mysql>select name,age from students where ! (age<=25);

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

| name | age |

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

| HuFei | 31 |

| Xuzhu | 26 |

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

2 rows in set (0.00 sec)

其他条件比较：

beween …… and ……

mysql>select name,age from students where age between 24 and 30

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

| name | age |

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

| DingDian | 25 |

| Xuzhu | 26 |

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

2 rows in set (0.00 sec)

in 查询的字段在指定的列表中

mysql>select name,age from students where age in (18,20,25);

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

| name | age |

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

| DingDian | 25 |

| YueLingshang | 18 |

| ZhangWuji | 20 |

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

3 rows in set (0.01 sec)

is null：查询是空值的

mysql>select name from students where cid2 is null;

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

| name |

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

| LingHuchong |

| YiLin |

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

2 rows in set (0.00 sec)

is not null

like : 做通配符的匹配

%：匹配任意长度的任意字符

_: 匹配单个字符

regexp|rlike : 正则表达式的匹配

order by: 排序，默认是升序的asc

desc：降序

mysql>select name,age from students where age in (22,18,25)order by age desc;

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

| name | age |

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

| DingDian | 25 |

| LingHuchong | 22 |

| YueLingshang | 18 |

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

3 rows in set (0.00 sec)

distinct: 显示结果的唯一性，附在select之后(以下面的例子解说，cid1相同的只显示了一次)

mysql>select distinct cid1 from students order by cid1 desc;

+------+

| cid1 |

+------+

| 18 |

| 11 |

| 8 |

| 6 |

| 5 |

| 2 |

| 1 |

+------+

7 rows in set (0.00 sec)

group by: 将取得的结果进行分组，通常分组的结果是用来做聚合运算的

having: 对分组的结果进行条件过滤

mysql>select avg(age),cid1 from students group by cid1;

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

| avg(age) | cid1 |

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

| 20.0000 | 1 |

| 20.6667 | 2 |

| 16.0000 | 5 |

| 25.0000 | 6 |

| 24.5000 | 8 |

| 22.0000 | 11 |

| 19.0000 | 18 |

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

7 rows in set (0.00 sec)

limit：限定显示的行数

eg：limit 1,2;表示跳过第一行再显示两行

mysql>select avg(age),cid1 from students group by cid1 limit 1,2;

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

| avg(age) | cid1 |

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

| 20.6667 | 2 |

| 16.0000 | 5 |

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

2 rows in set (0.00 sec)