MYSQL 教程:§6, 查询

§6, 查询

常用的查询格式:

SELECT columns

FROM tables

[WHERE conditions]

[GROUP BY group

[HAVING group_conditions]]

[ORDER BY sort_columns]

[LIMIT limits];

可以使用别名,如下

select name as employeeName

from employee;

使用where 子句的时候不允许使用别名

消除重复

select count(distinct job) from employee;

排序

mysql> select count(*), job

    -> from employee

    -> group by job;

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

| count(*) | job                   |

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

|        1 | DBA                   |

|        2 | Programmer            |

|        1 | Systems Administrator |

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

3 rows in set (0.00 sec)

mysql> select count(*), job

    -> from employee

    -> group by job desc;

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

| count(*) | job                   |

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

|        1 | Systems Administrator |

|        2 | Programmer            |

|        1 | DBA                   |

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

3 rows in set (0.00 sec)

HAVING 一般是针对组

select count(*), job

from employee

group by job

having count(*)=1;

以上为雇员职业数为1的查询

选取6-8行,可以和order by配合选取最后面的

select *

from employeeSkills

limit 5, 3;

从第5个开始,选3个