手机版
你好,游客 登录 注册 搜索
背景:
阅读新闻

MySQL的查询语句--SELECT

[日期:2012-12-02] 来源:Linux社区  作者:林夕jing [字体: ]

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

这里先来把MySQL查询语句综合了一下: 见 http://www.linuxidc.com/Linux/2012-12/75270.htm

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

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

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

  1. mysql> show databases;
  2. +--------------------+
  3. | Database |
  4. +--------------------+
  5. | information_schema |
  6. | jiaowu |
  7. | mysql |
  8. | test |
  9. +--------------------+
  10. 4 rows in set (0.00 sec)

l> use jiaowu;

Database changed

  1. mysql> show tables;
  2. +------------------+
  3. | Tables_in_jiaowu |
  4. +------------------+
  5. | courses |
  6. | scores |
  7. | students |
  8. | tutors |
  9. +------------------+
  10. 4 rows in set (0.00 sec)
  1. mysql> select * from students;
  2. +-----+--------------+------+--------+------+------+------+---------------------+
  3. | SID | Name | Age | Gender | CID1 | CID2 | TID | CreateTime |
  4. +-----+--------------+------+--------+------+------+------+---------------------+
  5. | 1 | GuoJing | 19 | M | 2 | 7 | 3 | 2012-04-06 10:00:00 |
  6. | 2 | YangGuo | 17 | M | 2 | 3 | 1 | 2012-04-06 10:00:00 |
  7. | 3 | DingDian | 25 | M | 6 | 1 | 7 | 2012-04-06 10:00:00 |
  8. | 4 | HuFei | 31 | M | 8 | 10 | 5 | 2012-04-06 10:00:00 |
  9. | 5 | HuangRong | 16 | F | 5 | 9 | 9 | 2012-04-06 10:00:00 |
  10. | 6 | YueLingshang | 18 | F | 8 | 4 | NULL | 2012-04-06 10:00:00 |
  11. | 7 | ZhangWuji | 20 | M | 1 | 7 | NULL | 2012-04-06 10:00:00 |
  12. | 8 | Xuzhu | 26 | M | 2 | 4 | NULL | 2012-04-06 10:00:00 |
  13. | 9 | LingHuchong | 22 | M | 11 | NULL | NULL | 2012-04-06 10:00:00 |
  14. | 10 | YiLin | 19 | F | 18 | NULL | NULL | 2012-04-06 10:00:00 |
  15. +-----+--------------+------+--------+------+------+------+---------------------+
  16. 10 rows in set (0.00 sec)
  1. mysql> select * from tutors;
  2. +-----+--------------+--------+------+
  3. | TID | Tname | Gender | Age |
  4. +-----+--------------+--------+------+
  5. | 1 2 | HuangYaoshi | M | 63 |
  6. | 3 | Miejueshitai | F | 72 |
  7. | 4 | OuYangfeng | M | 76 |
  8. | 5 | YiDeng | M | 90 |
  9. | 6 | YuCanghai | M | 56 |
  10. | 7 | Jinlunfawang | M | 67 |
  11. | 8 | HuYidao | M | 42 |
  12. | 9 | NingZhongze | F | 49 |
  13. +-----+--------------+--------+------+
  14. 9 rows in set (0.00 sec)

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

首先是mysql查询语句:

查询的分类:

单表查询:简单查询

多表查询:联结查询

子查询:复杂查询

联合查询

 

select语句:

常用函数:

##field--表示字段

count(*) 总行数

  1. mysql> select count(*) from tutors;
  2. +----------+
  3. | count(*) |
  4. +----------+
  5. | 9 |
  6. +---------+
  7. 1 row in set (0.00 sec)

max(field) 返回最大值

  1. mysql> select max(age) from tutors;
  2. +----------+
  3. | max(age) |
  4. +----------+
  5. | 93 |
  6. +----------+
  7. 1 row in set (0.00 sec)

min(field) 返回最小值

avg(field) 平均值

  1. mysql> select avg(age) from tutors;
  2. +----------+
  3. | avg(age) |
  4. +----------+
  5. | 67.5556 |
  6. +----------+
  7. 1 row in set (0.00 sec)

sum() 记和

  1. mysql> select sum(1+2);
  2. +----------+
  3. | sum(1+2) |
  4. +----------+
  5. | 3 |
  6. +----------+
  7. 1 row in set (0.01 sec)

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

where 后面指定的是条件:

可以指定的条件有:

算术比较:

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

  1. mysql> select name,age from students where age>=20;
  2. +-------------+------+
  3. | name | age |
  4. +-------------+------+
  5. | DingDian | 25 |
  6. | HuFei | 31 |
  7. | ZhangWuji | 20 |
  8. | Xuzhu | 26 |
  9. | LingHuchong | 22 |
  10. +-------------+------+
  11. 5 rows in set (0.00 sec)

组合逻辑比较:

and

or

not(!)

  1. mysql> select name,age from students where ! (age <=25);
  2. +-------+------+
  3. | name | age |
  4. +-------+------+
  5. | HuFei | 31 |
  6. | Xuzhu | 26 |
  7. +-------+------+
  8. 2 rows in set (0.00 sec)

其他条件比较:

beween …… and ……

  1. mysql> select name,age from students where age between 24 and 30
  2. +----------+------+
  3. | name | age |
  4. +----------+------+
  5. | DingDian | 25 |
  6. | Xuzhu | 26 |
  7. +----------+------+
  8. 2 rows in set (0.00 sec)

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

  1. mysql> select name,age from students where age in (18,20,25);
  2. +--------------+------+
  3. | name | age |
  4. +--------------+------+
  5. | DingDian | 25 |
  6. | YueLingshang | 18 |
  7. | ZhangWuji | 20 |
  8. +--------------+------+
  9. 3 rows in set (0.01 sec)

is null:查询是空值的

  1. mysql> select name from students where cid2 is null;
  2. +-------------+
  3. | name |
  4. +-------------+
  5. | LingHuchong |
  6. | YiLin |
  7. +-------------+
  8. 2 rows in set (0.00 sec)

is not null

like : 做通配符的匹配

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

_: 匹配单个字符

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

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

desc:降序

  1. mysql> select name,age from students where age in (22,18,25)order by age desc;
  2. +--------------+------+
  3. | name | age |
  4. +--------------+------+
  5. | DingDian | 25 |
  6. | LingHuchong | 22 |
  7. | YueLingshang | 18 |
  8. +--------------+------+
  9. 3 rows in set (0.00 sec)

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

  1. mysql> select distinct cid1 from students order by cid1 desc;
  2. +------+
  3. | cid1 |
  4. +------+
  5. | 18 |
  6. | 11 |
  7. | 8 |
  8. | 6 |
  9. | 5 |
  10. | 2 |
  11. | 1 |
  12. +------+
  13. 7 rows in set (0.00 sec)

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

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

  1. mysql> select avg(age),cid1 from students group by cid1;
  2. +----------+------+
  3. | avg(age) | cid1 |
  4. +----------+------+
  5. | 20.0000 | 1 |
  6. | 20.6667 | 2 |
  7. | 16.0000 | 5 |
  8. | 25.0000 | 6 |
  9. | 24.5000 | 8 |
  10. | 22.0000 | 11 |
  11. | 19.0000 | 18 |
  12. +----------+------+
  13. 7 rows in set (0.00 sec)

limit:限定显示的行数

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

  1. mysql> select avg(age),cid1 from students group by cid1 limit 1,2;
  2. +----------+------+
  3. | avg(age) | cid1 |
  4. +----------+------+
  5. | 20.6667 | 2 |
  6. | 16.0000 | 5 |
  7. +----------+------+
  8. 2 rows in set (0.00 sec)
linux
本文评论   查看全部评论 (0)
表情: 表情 姓名: 字数

       

评论声明
  • 尊重网上道德,遵守中华人民共和国的各项有关法律法规
  • 承担一切因您的行为而直接或间接导致的民事或刑事法律责任
  • 本站管理人员有权保留或删除其管辖留言中的任意内容
  • 本站有权在网站内转载或引用您的评论
  • 参与本评论即表明您已经阅读并接受上述条款