MySQL的查询语句--SELECT


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

这里先来把mysql查询语句综合了一下： 见

介绍了简单的数据库操作等，接下来从细节入手，来介绍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)
  • 1
  • 2
  • 下一页

相关内容