[MySQL] DML-SELECT

DML ( Data Manipulation Language ) : 데이터 조작어

• SELECT: 검색
• INSERT: 등록
• UPDATE: 수정
• DELETE: 삭제

SELECT

mysql> DESC DEPARTMENT;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| deptno   | int(11)     | NO   | PRI | NULL    |       |
| name     | varchar(14) | YES  |     | NULL    |       |
| location | varchar(13) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM DEPARTMENT;
+--------+------------+----------+
| deptno | name       | location |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
4 rows in set (0.01 sec)

mysql> SELECT DEPTNO, NAME FROM DEPARTMENT;
+--------+------------+
| DEPTNO | NAME       |
+--------+------------+
|     10 | ACCOUNTING |
|     20 | RESEARCH   |
|     30 | SALES      |
|     40 | OPERATIONS |
+--------+------------+
4 rows in set (0.00 sec)

mysql> SELECT DEPTNO 부서번호, NAME 부서명 FROM DEPARTMENT;
+----------+------------+
| 부서번호 | 부서명     |
+----------+------------+
|       10 | ACCOUNTING |
|       20 | RESEARCH   |
|       30 | SALES      |
|       40 | OPERATIONS |
+----------+------------+
4 rows in set (0.00 sec)

mysql> SELECT DEPTNO AS 부서번호, NAME AS 부서명 FROM DEPARTMENT;
+----------+------------+
| 부서번호 | 부서명     |
+----------+------------+
|       10 | ACCOUNTING |
|       20 | RESEARCH   |
|       30 | SALES      |
|       40 | OPERATIONS |
+----------+------------+
4 rows in set (0.00 sec)

---

CONCAT

mysql> DESC EMPLOYEE;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| empno    | int(11)      | NO   | PRI | NULL    |       |
| name     | varchar(10)  | YES  |     | NULL    |       |
| job      | varchar(9)   | YES  |     | NULL    |       |
| boss     | int(11)      | YES  | MUL | NULL    |       |
| hiredate | varchar(12)  | YES  |     | NULL    |       |
| salary   | decimal(7,2) | YES  |     | NULL    |       |
| comm     | decimal(7,2) | YES  |     | NULL    |       |
| deptno   | int(11)      | YES  | MUL | NULL    |       |
+----------+--------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

mysql> SELECT CONCAT(EMPNO, '-', DEPTNO) AS '사번-부서번호' FROM EMPLOYEE;
+---------------+
| 사번-부서번호 |
+---------------+
| 7782-10       |
| 7839-10       |
| 7934-10       |
| 7369-20       |
| 7566-20       |
| 7788-20       |
| 7876-20       |
| 7902-20       |
| 7499-30       |
| 7521-30       |
| 7654-30       |
| 7698-30       |
| 7844-30       |
| 7900-30       |
+---------------+
14 rows in set (0.00 sec)

---

DISTINCT

mysql> SELECT DEPTNO FROM EMPLOYEE;
+--------+
| DEPTNO |
+--------+
|     10 |
|     10 |
|     10 |
|     20 |
|     20 |
|     20 |
|     20 |
|     20 |
|     30 |
|     30 |
|     30 |
|     30 |
|     30 |
|     30 |
+--------+
14 rows in set (0.00 sec)

mysql> SELECT DISTINCT DEPTNO FROM EMPLOYEE;
+--------+
| DEPTNO |
+--------+
|     10 |
|     20 |
|     30 |
+--------+
3 rows in set (0.00 sec)

---

ORDER BY

mysql> SELECT EMPNO, NAME FROM EMPLOYEE;
+-------+--------+
| EMPNO | NAME   |
+-------+--------+
|  7369 | SMITH  |
|  7499 | ALLEN  |
|  7521 | WARD   |
|  7566 | JONES  |
|  7654 | MARTIN |
|  7698 | BLAKE  |
|  7782 | CLARK  |
|  7788 | SCOTT  |
|  7839 | KING   |
|  7844 | TURNER |
|  7876 | ADAMS  |
|  7900 | JAMES  |
|  7902 | FORD   |
|  7934 | MILLER |
+-------+--------+
14 rows in set (0.01 sec)

mysql> SELECT EMPNO, NAME FROM EMPLOYEE ORDER BY NAME;
+-------+--------+
| EMPNO | NAME   |
+-------+--------+
|  7876 | ADAMS  |
|  7499 | ALLEN  |
|  7698 | BLAKE  |
|  7782 | CLARK  |
|  7902 | FORD   |
|  7900 | JAMES  |
|  7566 | JONES  |
|  7839 | KING   |
|  7654 | MARTIN |
|  7934 | MILLER |
|  7788 | SCOTT  |
|  7369 | SMITH  |
|  7844 | TURNER |
|  7521 | WARD   |
+-------+--------+
14 rows in set (0.00 sec)

mysql> SELECT EMPNO, NAME FROM EMPLOYEE ORDER BY NAME DESC;
+-------+--------+
| EMPNO | NAME   |
+-------+--------+
|  7521 | WARD   |
|  7844 | TURNER |
|  7369 | SMITH  |
|  7788 | SCOTT  |
|  7934 | MILLER |
|  7654 | MARTIN |
|  7839 | KING   |
|  7566 | JONES  |
|  7900 | JAMES  |
|  7902 | FORD   |
|  7782 | CLARK  |
|  7698 | BLAKE  |
|  7499 | ALLEN  |
|  7876 | ADAMS  |
+-------+--------+
14 rows in set (0.00 sec)

mysql> SELECT EMPNO, NAME FROM EMPLOYEE ORDER BY 2 DESC;
+-------+--------+
| EMPNO | NAME   |
+-------+--------+
|  7521 | WARD   |
|  7844 | TURNER |
|  7369 | SMITH  |
|  7788 | SCOTT  |
|  7934 | MILLER |
|  7654 | MARTIN |
|  7839 | KING   |
|  7566 | JONES  |
|  7900 | JAMES  |
|  7902 | FORD   |
|  7782 | CLARK  |
|  7698 | BLAKE  |
|  7499 | ALLEN  |
|  7876 | ADAMS  |
+-------+--------+
14 rows in set (0.00 sec)

mysql> SELECT EMPNO, NAME FROM EMPLOYEE ORDER BY 1 DESC;
+-------+--------+
| EMPNO | NAME   |
+-------+--------+
|  7934 | MILLER |
|  7902 | FORD   |
|  7900 | JAMES  |
|  7876 | ADAMS  |
|  7844 | TURNER |
|  7839 | KING   |
|  7788 | SCOTT  |
|  7782 | CLARK  |
|  7698 | BLAKE  |
|  7654 | MARTIN |
|  7566 | JONES  |
|  7521 | WARD   |
|  7499 | ALLEN  |
|  7369 | SMITH  |
+-------+--------+
14 rows in set (0.00 sec)

---

WHERE

mysql> SELECT * FROM EMPLOYEE WHERE EMPNO = 7654;
+-------+--------+----------+------+------------+---------+---------+--------+
| empno | name   | job      | boss | hiredate   | salary  | comm    | deptno |
+-------+--------+----------+------+------------+---------+---------+--------+
|  7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
+-------+--------+----------+------+------------+---------+---------+--------+
1 row in set (0.00 sec)

mysql> SELECT * FROM EMPLOYEE WHERE DEPTNO = 30;
+-------+--------+----------+------+------------+---------+---------+--------+
| empno | name   | job      | boss | hiredate   | salary  | comm    | deptno |
+-------+--------+----------+------+------------+---------+---------+--------+
|  7499 | ALLEN  | SALESMAN | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER  | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7900 | JAMES  | CLERK    | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
+-------+--------+----------+------+------------+---------+---------+--------+
6 rows in set (0.00 sec)

mysql> SELECT * FROM EMPLOYEE WHERE JOB = 'SALESMAN';
+-------+--------+----------+------+------------+---------+---------+--------+
| empno | name   | job      | boss | hiredate   | salary  | comm    | deptno |
+-------+--------+----------+------+------------+---------+---------+--------+
|  7499 | ALLEN  | SALESMAN | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
+-------+--------+----------+------+------------+---------+---------+--------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM EMPLOYEE WHERE DEPTNO IN (10, 30);
+-------+--------+-----------+------+------------+---------+---------+--------+
| empno | name   | job       | boss | hiredate   | salary  | comm    | deptno |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
9 rows in set (0.00 sec)

mysql> SELECT * FROM EMPLOYEE WHERE DEPTNO BETWEEN 10 AND 20;
+-------+--------+-----------+------+------------+---------+------+--------+
| empno | name   | job       | boss | hiredate   | salary  | comm | deptno |
+-------+--------+-----------+------+------------+---------+------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 | NULL |     20 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 | NULL |     20 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 | NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1982-12-09 | 3000.00 | NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
|  7876 | ADAMS  | CLERK     | 7788 | 1983-01-12 | 1100.00 | NULL |     20 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 | NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 | NULL |     10 |
+-------+--------+-----------+------+------------+---------+------+--------+
8 rows in set (0.00 sec)

mysql> SELECT * FROM EMPLOYEE WHERE DEPTNO = 10 OR DEPTNO = 20;
+-------+--------+-----------+------+------------+---------+------+--------+
| empno | name   | job       | boss | hiredate   | salary  | comm | deptno |
+-------+--------+-----------+------+------------+---------+------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 | NULL |     20 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 | NULL |     20 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 | NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1982-12-09 | 3000.00 | NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
|  7876 | ADAMS  | CLERK     | 7788 | 1983-01-12 | 1100.00 | NULL |     20 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 | NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 | NULL |     10 |
+-------+--------+-----------+------+------------+---------+------+--------+
8 rows in set (0.00 sec)

---

LIKE, 와일드 카드

mysql> SELECT * FROM EMPLOYEE WHERE NAME LIKE 'A%';
+-------+-------+----------+------+------------+---------+--------+--------+
| empno | name  | job      | boss | hiredate   | salary  | comm   | deptno |
+-------+-------+----------+------+------------+---------+--------+--------+
|  7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 |     30 |
|  7876 | ADAMS | CLERK    | 7788 | 1983-01-12 | 1100.00 |   NULL |     20 |
+-------+-------+----------+------+------------+---------+--------+--------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM EMPLOYEE WHERE NAME LIKE '_A%';
+-------+--------+----------+------+------------+---------+---------+--------+
| empno | name   | job      | boss | hiredate   | salary  | comm    | deptno |
+-------+--------+----------+------+------------+---------+---------+--------+
|  7521 | WARD   | SALESMAN | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7900 | JAMES  | CLERK    | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
+-------+--------+----------+------+------------+---------+---------+--------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM EMPLOYEE WHERE NAME LIKE '%A__';
+-------+-------+----------+------+------------+---------+--------+--------+
| empno | name  | job      | boss | hiredate   | salary  | comm   | deptno |
+-------+-------+----------+------+------------+---------+--------+--------+
|  7521 | WARD  | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 |     30 |
|  7698 | BLAKE | MANAGER  | 7839 | 1981-05-01 | 2850.00 |   NULL |     30 |
|  7782 | CLARK | MANAGER  | 7839 | 1981-06-09 | 2450.00 |   NULL |     10 |
|  7876 | ADAMS | CLERK    | 7788 | 1983-01-12 | 1100.00 |   NULL |     20 |
+-------+-------+----------+------+------------+---------+--------+--------+
4 rows in set (0.00 sec)

---

UPPER, UCASE, LOWER, LCASE

mysql> SELECT UPPER('aSdF'), UCASE('AsDf');
+---------------+---------------+
| UPPER('aSdF') | UCASE('AsDf') |
+---------------+---------------+
| ASDF          | ASDF          |
+---------------+---------------+
1 row in set (0.00 sec)

mysql> SELECT LOWER('aSdF'), LCASE('AsDf');
+---------------+---------------+
| LOWER('aSdF') | LCASE('AsDf') |
+---------------+---------------+
| asdf          | asdf          |
+---------------+---------------+
1 row in set (0.00 sec)

mysql> SELECT NAME FROM EMPLOYEE;
+--------+
| NAME   |
+--------+
| SMITH  |
| ALLEN  |
| WARD   |
| JONES  |
| MARTIN |
| BLAKE  |
| CLARK  |
| SCOTT  |
| KING   |
| TURNER |
| ADAMS  |
| JAMES  |
| FORD   |
| MILLER |
+--------+
14 rows in set (0.00 sec)

mysql> SELECT LOWER(NAME) FROM EMPLOYEE;
+-------------+
| LOWER(NAME) |
+-------------+
| smith       |
| allen       |
| ward        |
| jones       |
| martin      |
| blake       |
| clark       |
| scott       |
| king        |
| turner      |
| adams       |
| james       |
| ford        |
| miller      |
+-------------+
14 rows in set (0.00 sec)

---

SUBSTRING

mysql> SELECT SUBSTRING('ABCD', 2);
+----------------------+
| SUBSTRING('ABCD', 2) |
+----------------------+
| BCD                  |
+----------------------+
1 row in set (0.00 sec)

mysql> SELECT SUBSTRING('ABCD', 2, 2);
+-------------------------+
| SUBSTRING('ABCD', 2, 2) |
+-------------------------+
| BC                      |
+-------------------------+
1 row in set (0.00 sec)

---

LPAD, RPAD

mysql> SELECT LPAD('ABC', 5, '*');
+---------------------+
| LPAD('ABC', 5, '*') |
+---------------------+
| **ABC               |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT RPAD('ABC', 5, '#');
+---------------------+
| RPAD('ABC', 5, '#') |
+---------------------+
| ABC##               |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT LPAD(NAME, 7, '.') FROM EMPLOYEE;
+--------------------+
| LPAD(NAME, 7, '.') |
+--------------------+
| ..SMITH            |
| ..ALLEN            |
| ...WARD            |
| ..JONES            |
| .MARTIN            |
| ..BLAKE            |
| ..CLARK            |
| ..SCOTT            |
| ...KING            |
| .TURNER            |
| ..ADAMS            |
| ..JAMES            |
| ...FORD            |
| .MILLER            |
+--------------------+
14 rows in set (0.00 sec)

mysql> SELECT LPAD(NAME, 7, ' ') FROM EMPLOYEE;
+--------------------+
| LPAD(NAME, 7, ' ') |
+--------------------+
|   SMITH            |
|   ALLEN            |
|    WARD            |
|   JONES            |
|  MARTIN            |
|   BLAKE            |
|   CLARK            |
|   SCOTT            |
|    KING            |
|  TURNER            |
|   ADAMS            |
|   JAMES            |
|    FORD            |
|  MILLER            |
+--------------------+
14 rows in set (0.00 sec)

mysql> SELECT LPAD(NAME, 7, '12345') FROM EMPLOYEE;
+------------------------+
| LPAD(NAME, 7, '12345') |
+------------------------+
| 12SMITH                |
| 12ALLEN                |
| 123WARD                |
| 12JONES                |
| 1MARTIN                |
| 12BLAKE                |
| 12CLARK                |
| 12SCOTT                |
| 123KING                |
| 1TURNER                |
| 12ADAMS                |
| 12JAMES                |
| 123FORD                |
| 1MILLER                |
+------------------------+
14 rows in set (0.00 sec)

---

TRIM, LTRIM, RTRIM

mysql> SELECT TRIM(' ASDF');
+---------------+
| TRIM(' ASDF') |
+---------------+
| ASDF          |
+---------------+
1 row in set (0.00 sec)

mysql> SELECT LTRIM(' ASDF');
+----------------+
| LTRIM(' ASDF') |
+----------------+
| ASDF           |
+----------------+
1 row in set (0.00 sec)

mysql> SELECT RTRIM(' ASDF');
+----------------+
| RTRIM(' ASDF') |
+----------------+
|  ASDF          |
+----------------+
1 row in set (0.00 sec)

---

ABS

mysql> SELECT ABS(123);
+----------+
| ABS(123) |
+----------+
|      123 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT ABS(-123);
+-----------+
| ABS(-123) |
+-----------+
|       123 |
+-----------+
1 row in set (0.00 sec)

---

함수

MOD(N, M): N을 M으로 나눈 나머지

ROUND(X): 반올림

FLOOR(X): 버림

POW(X, Y), POWER(X, Y): X의 Y승

GREATEST(X, Y, Z, ...): 최댓값

LEAST(X, Y, Z, ...): 최솟값

CURDATE(), CURRENT_DATE: YYYY-MM-DD or YYYYMMDD

CURTIME(), CURRENT_TIME: HH:MM:SS or HHMMSS

NOW(), SYSDATE(), CURRENT_TIMESTAMP: YYYY--MM--DD HH:MM:SS or YYYYMMDDHHMMSS

DATE_FORMAT(DATE, FORMAT): DATE를 FORMAT 형식으로 반환

PERIOD_DIFF(P1, P2): P1, P2(: YYMM or YYYYMM)의 개월수 차이 반환

---

CAST 형변환

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2022-04-16 14:07:39 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT CAST(NOW() AS DATE);
+---------------------+
| CAST(NOW() AS DATE) |
+---------------------+
| 2022-04-16          |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT 1-2;
+-----+
| 1-2 |
+-----+
|  -1 |
+-----+
1 row in set (0.00 sec)

 

mysql> SELECT CAST(1-2 AS UNSIGNED);
+-----------------------+
| CAST(1-2 AS UNSIGNED) |
+-----------------------+
|  18446744073709551615 |
+-----------------------+
1 row in set (0.00 sec)

---

그룹함수

COUNT(expr)	NULL이 아닌 값들 ROW 갯수 반환
COUNT(DISTINCT expr, [expr...])	NULL이 아닌 중복되지 않은 ROW 갯수 반환
COUNT(*)	ROW의 갯수 반환
AVG(expr)	expr의 평균값 반환
MIN(expr)	expr의 최솟값 반환
MAX(expr)	expr의 최댓값 반환
SUM(expr)	expr의 합계 반환
GROUP_CONCAT(expr)	concatenated한 문자 반환
VARIANCE(expr)	expr의 분산 반환
STDDEV(expr)	expr의 표준 편차 반환

---

COUNT

mysql> SELECT * FROM EMPLOYEE;
+-------+--------+-----------+------+------------+---------+---------+--------+
| empno | name   | job       | boss | hiredate   | salary  | comm    | deptno |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1982-12-09 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1983-01-12 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)

mysql> SELECT COUNT(*) FROM EMPLOYEE;
+----------+
| COUNT(*) |
+----------+
|       14 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(BOSS) FROM EMPLOYEE;
+-------------+
| COUNT(BOSS) |
+-------------+
|          13 |
+-------------+
1 row in set (0.01 sec)

mysql> SELECT COUNT(COMM) FROM EMPLOYEE;
+-------------+
| COUNT(COMM) |
+-------------+
|           4 |
+-------------+
1 row in set (0.00 sec)

---

AVG, SUM, GROUP BY

mysql> SELECT AVG(SALARY), SUM(SALARY) FROM EMPLOYEE;
+-------------+-------------+
| AVG(SALARY) | SUM(SALARY) |
+-------------+-------------+
| 2073.214286 |    29025.00 |
+-------------+-------------+
1 row in set (0.00 sec)

mysql> SELECT DEPTNO, AVG(SALARY), SUM(SALARY) FROM EMPLOYEE GROUP BY DEPTNO;
+--------+-------------+-------------+
| DEPTNO | AVG(SALARY) | SUM(SALARY) |
+--------+-------------+-------------+
|     10 | 2916.666667 |     8750.00 |
|     20 | 2175.000000 |    10875.00 |
|     30 | 1566.666667 |     9400.00 |
+--------+-------------+-------------+
3 rows in set (0.00 sec)