MySQL数据库的order by;group by 字段having条件; distinct不重复；的使用demo

CREATE TABLE `emp` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NULL,
  `salary` bigint(20) unsigned DEFAULT NULL,
  `did` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

mysql> insert into emp values(null,'ly',100,1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into emp values(null,'zk',110,1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into emp values(null,'ww',110,1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into emp values(null,'zl',90,1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into emp values(null,'zl',100,2);
Query OK, 1 row affected (0.00 sec)

mysql> insert into emp values(null,'zl',100,2);
Query OK, 1 row affected (0.00 sec)

mysql> insert into emp values(null,'zl',110,2);
Query OK, 1 row affected (0.00 sec)

mysql> select * from emp;
+----+------+--------+------+
| id | name | salary | did  |
+----+------+--------+------+
|  1 | ly   |    100 | 1    |
|  2 | zk   |    110 | 1    |
|  3 | ww   |    110 | 1    |
|  4 | zl   |     90 | 1    |
|  5 | zl   |    100 | 2    |
|  6 | zl   |    100 | 2    |
|  7 | zl   |    110 | 2    |
+----+------+--------+------+
7 rows in set (0.00 sec)

mysql> select * from emp group by did;分组查询
+----+------+--------+------+
| id | name | salary | did  |
+----+------+--------+------+
|  1 | ly   |    100 | 1    |
|  5 | zl   |    100 | 2    |
+----+------+--------+------+
2 rows in set (0.00 sec)

mysql> select * from emp order by did desc ,salary asc;//按照部分降序，工资升序
+----+------+--------+------+
| id | name | salary | did  |
+----+------+--------+------+
|  5 | zl   |    100 | 2    |
|  6 | zl   |    100 | 2    |
|  7 | zl   |    110 | 2    |
|  4 | zl   |     90 | 1    |
|  1 | ly   |    100 | 1    |
|  2 | zk   |    110 | 1    |
|  3 | ww   |    110 | 1    |
+----+------+--------+------+
7 rows in set (0.00 sec)

mysql> select avg(salary) from emp order by did;//每个部分的avg()工资，该句不对
+-------------+
| avg(salary) |
+-------------+
|    102.8571 |
+-------------+
1 row in set (0.00 sec)

mysql> select * from emp order by did;
+----+------+--------+------+
| id | name | salary | did  |
+----+------+--------+------+
|  1 | ly   |    100 | 1    |
|  2 | zk   |    110 | 1    |
|  3 | ww   |    110 | 1    |
|  4 | zl   |     90 | 1    |
|  5 | zl   |    100 | 2    |
|  6 | zl   |    100 | 2    |
|  7 | zl   |    110 | 2    |
+----+------+--------+------+
7 rows in set (0.00 sec)

mysql> select did,avg(salary) from emp group by did;//查询部分的平均工资
+------+-------------+
| did  | avg(salary) |
+------+-------------+
| 1    |    102.5000 |
| 2    |    103.3333 |
+------+-------------+
2 rows in set (0.00 sec)

mysql> select a.id,a.name,a.salary,a.did from emp a,(select did,avg(salary) salary from emp group by did) b where a.salary>b.salary;//部分的工资大于平均该部分的工资信息；id就是人
+----+------+--------+------+
| id | name | salary | did  |
+----+------+--------+------+
|  2 | zk   |    110 | 1    |
|  2 | zk   |    110 | 1    |
|  3 | ww   |    110 | 1    |
|  3 | ww   |    110 | 1    |
|  7 | zl   |    110 | 2    |
|  7 | zl   |    110 | 2    |
+----+------+--------+------+
6 rows in set (0.01 sec)

mysql> select a.id,a.name,a.salary,a.did from emp a where a.salary>(select avg(salary) from emp where did=a.did);

//查法2，后面的did使用前面a.did的变量值
+----+------+--------+------+
| id | name | salary | did  |
+----+------+--------+------+
|  2 | zk   |    110 | 1    |
|  3 | ww   |    110 | 1    |
|  7 | zl   |    110 | 2    |
+----+------+--------+------+
3 rows in set (0.00 sec)

mysql> select avg(salary) from emp where did=1;
+-------------+
| avg(salary) |
+-------------+
|    102.5000 |
+-------------+
1 row in set (0.00 sec)

mysql> select a.id,a.name,a.salary,a.did from emp a where a.salary>(select avg(salary) from emp group by did having did=a.did);//查法3，后面的did使用前面a.did的变量值 ;group by did having did=a.did)
+----+------+--------+------+
| id | name | salary | did  |
+----+------+--------+------+
|  2 | zk   |    110 | 1    |
|  3 | ww   |    110 | 1    |
|  7 | zl   |    110 | 2    |
+----+------+--------+------+
3 rows in set (0.00 sec)
mysql> select distinct did from emp where salary<100;//一条记录
+------+
| did  |
+------+
| 1    |
+------+
1 row in set (0.00 sec)

mysql> select distinct did from emp  where did not in(select distinct did from emp where salary<100);

//假设did是人，salary是分数，查询一个人的各科都大于100份的记录的人；
+------+
| did  |
+------+
| 2    |
+------+

mysql> select distinct did ,name,salary,id from emp  where did not in(select distinct did from emp where salary<100);

//组合记录
+------+------+--------+----+
| did  | name | salary | id |
+------+------+--------+----+
| 2    | zl   |    100 |  5 |
| 2    | zl   |    100 |  6 |
| 2    | zl   |    110 |  7 |
+------+------+--------+----+
3 rows in set (0.01 sec)

Oracle没有limit m,n；但是有字段between m and n