[mysql] order by, limit, sum, avg, max, count, having,with rollup

order by : 정렬(오름차순-asc, 내림차순-desc)

limit : 5개만 출력

mysql> select emp_no, hire_date from employees order by hire_date asc limit 5;
+--------+------------+
| emp_no | hire_date  |
+--------+------------+
| 110022 | 1985-01-01 |
| 110511 | 1985-01-01 |
| 110303 | 1985-01-01 |
| 110085 | 1985-01-01 |
| 110183 | 1985-01-01 |
+--------+------------+
5 rows in set (0.08 sec)

limit 100,5 : 100번째부터 5개 출력

mysql> select emp_no, hire_date from employees order by hire_date asc limit 100,5;
+--------+------------+
| emp_no | hire_date  |
+--------+------------+
| 241003 | 1985-02-02 |
|  88913 | 1985-02-02 |
|  43969 | 1985-02-02 |
| 204237 | 1985-02-02 |
|  96308 | 1985-02-02 |
+--------+------------+
5 rows in set (0.09 sec)

mysql> use sqlDB;

select를 통한 테이블 복사 ( 제약조건은 복사되지않음. only 데이터만)

mysql> create table buytbl2 (select * from buytbl);
Query OK, 12 rows affected (0.01 sec)

Records: 12  Duplicates: 0  Warnings: 0

mysql> select * from buytbl2;
+-----+--------+-----------+-----------+-------+--------+
| num | userid | prodname  | groupname | price | amount |
+-----+--------+-----------+-----------+-------+--------+
|   1 | KBS    | 운동화    | NULL      |    30 |      2 |
|   2 | KBS    | 노트북    | 전자      |  1000 |      1 |
|   3 | JYP    | 모니터    | 전자      |   200 |      1 |
|   4 | BBK    | 모니터    | 전자      |   200 |      5 |
|   5 | KBS    | 청바지    | 의류      |    50 |      3 |
|   6 | BBK    | 메모리    | 전자      |    80 |     10 |
|   7 | SSK    | 책        | 서적      |    15 |      5 |
|   8 | EJW    | 책        | 서적      |    15 |      2 |
|   9 | EJW    | 청바지    | 의류      |    50 |      1 |
|  10 | BBK    | 운동화    | NULL      |    30 |      2 |
|  11 | EJW    | 책        | 서적      |    15 |      1 |
|  12 | BBK    | 운동화    | NULL      |    30 |      2 |
+-----+--------+-----------+-----------+-------+--------+
12 rows in set (0.01 sec)

mysql> select * from buytbl order by userid;
+-----+--------+-----------+-----------+-------+--------+
| num | userid | prodname  | groupname | price | amount |
+-----+--------+-----------+-----------+-------+--------+
|   4 | BBK    | 모니터    | 전자      |   200 |      5 |
|   6 | BBK    | 메모리    | 전자      |    80 |     10 |
|  10 | BBK    | 운동화    | NULL      |    30 |      2 |
|  12 | BBK    | 운동화    | NULL      |    30 |      2 |
|   8 | EJW    | 책        | 서적      |    15 |      2 |
|   9 | EJW    | 청바지    | 의류      |    50 |      1 |
|  11 | EJW    | 책        | 서적      |    15 |      1 |
|   3 | JYP    | 모니터    | 전자      |   200 |      1 |
|   1 | KBS    | 운동화    | NULL      |    30 |      2 |
|   2 | KBS    | 노트북    | 전자      |  1000 |      1 |
|   5 | KBS    | 청바지    | 의류      |    50 |      3 |
|   7 | SSK    | 책        | 서적      |    15 |      5 |
+-----+--------+-----------+-----------+-------+--------+
12 rows in set (0.00 sec)

sum()

mysql> select userid, sum(amount) from buytbl group by userid;
+--------+-------------+
| userid | sum(amount) |
+--------+-------------+
| BBK    |          19 |
| EJW    |           4 |
| JYP    |           1 |
| KBS    |           6 |
| SSK    |           5 |
+--------+-------------+
5 rows in set (0.00 sec)

mysql> select userid, sum(price*amount) from buytbl group by userid;
+--------+-------------------+
| userid | sum(price*amount) |
+--------+-------------------+
| BBK    |              1920 |
| EJW    |                95 |
| JYP    |               200 |
| KBS    |              1210 |
| SSK    |                75 |
+--------+-------------------+
5 rows in set (0.00 sec)

mysql> select userid, sum(price*amount) from buytbl group by userid order by 2;
+--------+-------------------+
| userid | sum(price*amount) |
+--------+-------------------+
| SSK    |                75 |
| EJW    |                95 |
| JYP    |               200 |
| KBS    |              1210 |
| BBK    |              1920 |
+--------+-------------------+
5 rows in set (0.00 sec)

mysql> select userid, sum(price*amount) from buytbl group by userid order by 2 desc;
+--------+-------------------+
| userid | sum(price*amount) |
+--------+-------------------+
| BBK    |              1920 |
| KBS    |              1210 |
| JYP    |               200 |
| EJW    |                95 |
| SSK    |                75 |
+--------+-------------------+
5 rows in set (0.00 sec)

avg()

mysql> select userid, avg(price*amount) from buytbl group by userid order by 2 desc;
+--------+-------------------+
| userid | avg(price*amount) |
+--------+-------------------+
| BBK    |          480.0000 |
| KBS    |          403.3333 |
| JYP    |          200.0000 |
| SSK    |           75.0000 |
| EJW    |           31.6667 |
+--------+-------------------+
5 rows in set (0.00 sec)

mysql> select userid, count(*), avg(price*amount) from buytbl group by userid order by 2 desc;
+--------+----------+-------------------+
| userid | count(*) | avg(price*amount) |
+--------+----------+-------------------+
| BBK    |        4 |          480.0000 |
| KBS    |        3 |          403.3333 |
| EJW    |        3 |           31.6667 |
| JYP    |        1 |          200.0000 |
| SSK    |        1 |           75.0000 |
+--------+----------+-------------------+
5 rows in set (0.00 sec)

max()

mysql> select userid, count(*), max(price*amount) from buytbl group by userid order by 2 desc;
+--------+----------+-------------------+
| userid | count(*) | max(price*amount) |
+--------+----------+-------------------+
| BBK    |        4 |              1000 |
| KBS    |        3 |              1000 |
| EJW    |        3 |                50 |
| JYP    |        1 |               200 |
| SSK    |        1 |                75 |
+--------+----------+-------------------+
5 rows in set (0.00 sec)

usertbl에서 가장큰키, 가장작은키 쿼리

mysql> select name, height

         from usertbl

         where height =( select max(height) from usertbl ) or

                  height=( select min(height) from usertbl );

+-----------+--------+
| name      | height |
+-----------+--------+
| 조용필    |    166 |
| 성시경    |    186 |
+-----------+--------+
2 rows in set (0.00 sec)

count()

mysql> select count(mobile1) from usertbl;
+----------------+
| count(mobile1) |
+----------------+
|              8 |
+----------------+
1 row in set (0.00 sec)

mysql> select count(emp_no) from employees.employees;
+---------------+
| count(emp_no) |
+---------------+
|        300024 |
+---------------+
1 row in set (0.05 sec)

mysql> select count(*) from employees.employees;
+----------+
| count(*) |
+----------+
|   300024 |
+----------+
1 row in set (0.05 sec)

having : group by 이후의 조건

mysql> select userid, sum(price*amount) from buytbl group by userid;
+--------+-------------------+
| userid | sum(price*amount) |
+--------+-------------------+
| BBK    |              1920 |
| EJW    |                95 |
| JYP    |               200 |
| KBS    |              1210 |
| SSK    |                75 |
+--------+-------------------+
5 rows in set (0.00 sec)

mysql> select userid, sum(price*amount) from buytbl group by userid having sum(price*amount)>1000;
+--------+-------------------+
| userid | sum(price*amount) |
+--------+-------------------+
| BBK    |              1920 |
| KBS    |              1210 |
+--------+-------------------+
2 rows in set (0.00 sec)

rollup : 소계, 총계

mysql> select num, groupname,  sum(price*amount) from buytbl group by groupname, num  with rollup;
+-----+-----------+-------------------+
| num | groupname | sum(price*amount) |
+-----+-----------+-------------------+
|   1 | NULL      |                60 |
|  10 | NULL      |                60 |
|  12 | NULL      |                60 |
| NULL | NULL      |               180 |
|   7 | 서적      |                75 |
|   8 | 서적      |                30 |
|  11 | 서적      |                15 |
| NULL | 서적      |               120 |
|   5 | 의류      |               150 |
|   9 | 의류      |                50 |
| NULL | 의류      |               200 |
|   2 | 전자      |              1000 |
|   3 | 전자      |               200 |
|   4 | 전자      |              1000 |
|   6 | 전자      |               800 |
| NULL | 전자      |              3000 |
| NULL | NULL      |              3500 |
+-----+-----------+-------------------+
17 rows in set (0.00 sec)

합계만 출력 : num 제거

mysql> select groupname,  sum(price*amount) from buytbl group by groupname  with rollup;
+-----------+-------------------+
| groupname | sum(price*amount) |
+-----------+-------------------+
| NULL      |               180 |
| 서적      |               120 |
| 의류      |               200 |
| 전자      |              3000 |
| NULL      |              3500 |
+-----------+-------------------+
5 rows in set (0.00 sec)