正在浏览栏目导航：首页 > 数据库 > MySQL

Mysql数据库操作新手入门，手把手的教会你

时间:2008-03-08 13:25:01 来源:phpwind 作者:FengLin 点击:次出处:技术无忧

技术无忧.com

2.6 高级查询方法

2.6.1 记录查询

查询emp表中，emp_name为啸天的全部信息
mysql> select * from emp where emp_name='啸天';

查询emp表中，emp_sal，工资在5000以上的全部信息
mysql> select * from emp where emp_sal>5000;

    查询结果显示如下：
    +--------+----------+---------+---------+------------+---------+
    | emp_id | emp_name | emp_age | emp_sal | emp_bir   | emp_sex |
    +--------+----------+---------+---------+------------+---------+
    | 100001 | 红枫   |     29 |   9000 | 1977-01-01 | male   |
    | 100002 | 丽鹃   |     27 |   8000 | 1979-12-31 | fmale   |
    +--------+----------+---------+---------+------------+---------+
    2 rows in set (0.00 sec)

查询emp表中在1978年1月1日之后出生的
mysql> select * from emp where emp_bir>'1978-01-01';

    查询结果显示如下：
    +--------+----------+---------+---------+------------+---------+
    | emp_id | emp_name | emp_age | emp_sal | emp_bir   | emp_sex |
    +--------+----------+---------+---------+------------+---------+
    | 100005 | 啸天   |     27 |   4000 | 1979-07-10 | male   |
    | 100002 | 丽鹃   |     27 |   8000 | 1979-12-31 | fmale   |
    +--------+----------+---------+---------+------------+---------+
    2 rows in set (0.00 sec)

查询emp表中在1979年12月1日之前出生，工资在5000以上的
mysql> select * from emp where emp_bir<'1979-12-01' and emp_sal>5000;

2.6.2 字段查询

CEO查看员工工资情况
mysql> select emp_name,emp_sal from emp;

    查询结果显示如下：
    +----------+---------+
    | emp_name | emp_sal |
    +----------+---------+
    | 啸天   |   4000 |
    | 红枫   |   9000 |
    | 丽鹃   |   8000 |
    +----------+---------+
    3 rows in set (0.00 sec)

查看1978年后出生的人的姓名、工资和性别
mysql> select emp_name,emp_sal,emp_sex from emp where emp_bir>"1977-12-31";

    查询结果显示如下：
    +----------+---------+---------+
    | emp_name | emp_sal | emp_sex |
    +----------+---------+---------+
    | 啸天   |   4000 | male   |
    | 丽鹃   |   8000 | fmale   |
    +----------+---------+---------+
    2 rows in set (0.00 sec)

2.6.3 查询结果排序

用ORDER BY语句对emp表中所有员工工资高低顺序查询结果（默认是从低到高——升序）
mysql> select * from emp order by emp_sal;

    查询结果显示如下：
    +--------+----------+---------+---------+------------+---------+
    | emp_id | emp_name | emp_age | emp_sal | emp_bir   | emp_sex |
    +--------+----------+---------+---------+------------+---------+
    | 100005 | 啸天   |     27 |   4000 | 1979-07-10 | male   |
    | 100002 | 丽鹃   |     27 |   8000 | 1979-12-31 | fmale   |
    | 100001 | 红枫   |     29 |   9000 | 1977-01-01 | male   |
    +--------+----------+---------+---------+------------+---------+
    3 rows in set (0.00 sec)

用DESC关键字来进行从高到低排序——降序
mysql> select * from emp order by emp_sal desc;

    查询结果显示如下：
    +--------+----------+---------+---------+------------+---------+
    | emp_id | emp_name | emp_age | emp_sal | emp_bir   | emp_sex |
    +--------+----------+---------+---------+------------+---------+
    | 100001 | 红枫   |     29 |   9000 | 1977-01-01 | male   |
    | 100002 | 丽鹃   |     27 |   8000 | 1979-12-31 | fmale   |
    | 100005 | 啸天   |     27 |   4000 | 1979-07-10 | male   |
    +--------+----------+---------+---------+------------+---------+
    3 rows in set (0.00 sec)

2.6.4 查询结果数量的限制

用LIMIT查看emp表中工资收入排名前两个员工的资料：
mysql> select * from emp order by emp_sal desc limit 2;

    查询结果显示如下：
    +--------+----------+---------+---------+------------+---------+
    | emp_id | emp_name | emp_age | emp_sal | emp_bir   | emp_sex |
    +--------+----------+---------+---------+------------+---------+
    | 100001 | 红枫   |     29 |   9000 | 1977-01-01 | male   |
    | 100002 | 丽鹃   |     27 |   8000 | 1979-12-31 | fmale   |
    +--------+----------+---------+---------+------------+---------+
    2 rows in set (0.00 sec)

查看工资排名第2到第3的员工资料：
mysql> select * from emp order by emp_sal desc limit 1,2;

查询结果显示如下：
+--------+----------+---------+---------+------------+---------+
| emp_id | emp_name | emp_age | emp_sal | emp_bir   | emp_sex |
+--------+----------+---------+---------+------------+---------+
| 100002 | 丽鹃   |     27 |   8000 | 1979-12-31 | fmale   |
| 100005 | 啸天   |     27 |   4000 | 1979-07-10 | male   |
+--------+----------+---------+---------+------------+---------+
2 rows in set (0.01 sec)

使用rand()抽样调查，随机抽取2个员工，查看其资料
mysql> select * from emp order by rand() limit 2;

如如下结果：（随机的）

+--------+----------+---------+---------+------------+---------+
| emp_id | emp_name | emp_age | emp_sal | emp_bir   | emp_sex |
+--------+----------+---------+---------+------------+---------+
| 100005 | 啸天   |     27 |   4000 | 1979-07-10 | male   |
| 100001 | 红枫   |     29 |   9000 | 1977-01-01 | male   |
+--------+----------+---------+---------+------------+---------+
2 rows in set (0.01 sec)

2.6.5 查询结果的字段联合和重新命名

mysql> select concat(emp_id," ",emp_name) from emp;

    查询结果：
    +------------------------------+
    | concat(emp_id," ",emp_name) |
    +------------------------------+
    | 100005 啸天           |
    | 100001 红枫           |
    | 100002 丽鹃           |
    +------------------------------+
    3 rows in set (0.00 sec)

用AS关键字重新给输出结果命名标题
mysql> select concat(emp_id," ",emp_name) as info from emp;

    查询结果如下显示：
    +----------------+
    | info       |
    +----------------+
    | 100005 啸天 |
    | 100001 红枫   |
    | 100002 丽鹃 |
    +----------------+
    3 rows in set (0.00 sec)收藏:http://www.pc51.net/data/MySQL/2008-03-08/mysql10257.html