사용한 함수구분
집계함수 count, max, min, avg
숫자함수 round, truncate
문자함수 lower, concat, left, substring, right
조건함수 case
IT는 기본이론을 전제로 맛을 느껴야 한다.
기본이론
함수: 특정 기능을 수행하는 것
함수의 생김새: 함수명(매개변수)
함수 재정의: 매개변수를 입력받아 특정기능을 수행
함수의 사용: 특정기능을 수행하고 그 결과를 응용
예) count(no)- no라는 매개변수를 입력하고, count라는 함수는 숫자를 센 후
숫자를 센 결과값을 리턴받아서 사용한다.
*내가 분석하고자 하는 속성의 타입에 맞춰서 함수를 선택하는 능력을 길러야 함
Mysql 함수 종류
집계함수
숫자함수
문자함수
조건함수: 참과 거짓을 구분하는 함수
날짜함수
가.릴레이션- 다른말로 테이블, 관계형 데이터 베이스에서 정보를 구분하여 저장하는
기본단위, 데이터를 속성과 튜플로 정렬하여 관리한다.
나.속성- 하나의 릴레이션(테이블)은 어떤 개체를 표현하고 저장되는데 사용되는데
내가 저장하고 싶은 개체의 항목을 속성이라고 함. 칼럼과 같은 의미.
다.튜플- 릴레이션(테이블)의 각행을 튜플(레코드)이라하며
릴레이션이 나타내는 엔티티의 특정 인스턴스에 관한 값들의 모임이다
라.기본키의 특징
기본키(Primary Key; PK)는 주 키 또는 프라이머리 키라고 하며,
후보키 중에서 특별히 선정된 키로 중복된 값을 가질 수 없으며, 후보키의 성질을 갖는다.
즉, 유일성과 최소성을 가지며 튜플을 식별하기 위해 반드시 필요한 키이다.
index를 생성하여 select 속도 향상을 준다. but insert, update, delete 속도는 하향.
마.외래키의 특징
다른 테이블의 기본키를 참조하여 부모-자식 관계를 정의하며,
데이터 변경이 발생하면 정의한 제약조건에 따라 함께 영향을 받는 특징이 있다.
바.무결성의 종류 3가지와 각 무결성과 연관성 있는 것
데이터를 신뢰하기 위한 것으로 개체무결성, 참조무결성, 도메인 무결성이 있다.
개체 무결성: 하나의 테이블에 중복된 행이 존재하지 않도록 규정하는 것(PK,Unique)
참조 무결성: 행을 입력, 수정, 삭제할 때 연관되는 다른 테이블과의 데이터가 정확하게 유지되도록 규정하는 것(FK)
도메인 무결성: 속성이 정의되어 있는 영역을 벗어나지 않도록 규정하는 것(Check, Default, Not null)
사.ERD란 무엇인가
Entity Relationship Diagram (ERD)는 개념적인 관계도로
시스템의 엔티티들이 무엇이 있는지 어떤 관계가 있는지를 나타내는 다이어그램이다.
아.요구사항을 분석 후
개념적인 ERD를 그립니다.
ERD의 대응관계를 분석하여 실제 테이블을 만들고, 테이블을 관리 합니다.
실제 테이블을 관리하기 위해 어떤 이론을 정리하고 적용해야 하는지 서술하시오.
개체 정의: 실제 데이터를 저장하려는 단위
개체는 속성을 가질 수 있다.(숫자(정수, 실수), 문자, 날짜)
속성들은 가질 수 있는 값이 있다.
속성은 제약조건을 가진다.(primary key, unique, not null)
개체는 속성들이 모여서 이루어 진다.
여기까지가 테이블의 생성에 관련된 이론
튜플: 실제 저장한 데이터 단위
튜플은 데이터 조작어로 CRUD가 있다.
R(select문)의 구조-
select //검색할 컬럼
from //검색할 테이블
where //튜플을 선택하기 위한 조건
group by //where에서 선택된 릴레이션에서 그룹을 한다. having //그룹을 형성하기 위한 조건
order by //정렬
limit
각 절에 대한 정의를 기억하고 쿼리 작동 순서를 기억.
먼저 개체와 관계를 확인
도메인의 무결성을 위해 테이블간 속성들의 타입을 확인하고 개체 무결성을 위해 기본키와 유니크를 지정한
후에 참조 무결성을 위해 각 테이블 간의 관계를 확인하여 물리적인 테이블을 만들어야 한다.
자.<집계함수> 0804 테이블에서 최고 점수를 획득한 사람의 이름을 찾아 낼 수 있는가?
1.집계함수를 사용하여 서브쿼리 사용
select name from user where point = (select max(point) from user);
2.집계함수를 사용하지 않고 order by 사용
select name from user order by point desc limit 0,1;
문제
조건, 그룹은 1,2,3,4가 있다
가. 1그룹에서 최고 점은 무엇입니까?
select max(point) from user where grade=1;
나. 1, 2 그룹을 합쳐서 최고 점은 무엇입니까?
select max(point) from user where grade=1 or grade=2;
다. 각 그룹별 최고점은무엇인가요?
select max(point) from user group by grade;
라. 1 그룹에서 최고점은 무엇인가? 단, 점수가 90미만 중에서 최고점을 찾으시오.
select max(point) from user where grade=1 and point<90;
마. 각 그룹별 최고점은 무엇인가? 단 . 그룹별 평균이 82미만인 그룹은 최고점을 찾지 마시오.
select max(point), grade, round(avg(point),2) from user group by grade having avg(point)>=82;
A. 다음 문제중에 그룹을 할 필요가 있는 것은 ?
나,다,마
B. 위 문제에서 조건이 있는 경우와 그 조건에 튜플의 조건인지 그룹의 조건인지 분석하시오.
라= 튜플의 조건
마= 그룹의 조건
과제
create table employee(
no int auto_increment, / 기본키 /
id varchar(4) unique, / 직원번호 앞두글자는 근무지역코드 뒷두글자는 개인 코드 /
name varchar(10) not null,
addr varchar(10),
salary int, /기본급/
workingyear int, /입사후 근무년수 /
part varchar(1), /부서코드 /
indata timestamp default now(), /최종수정일/
constraint PK_employee primary key (no)
);
insert into employee values (default, '2705','홍길동','서울시',2000,2,'A', now());
insert into employee values (default, '3706','홍지요','서울특별시',2200,3,'A', now());
insert into employee values (default, '2707','나길수','수원시',2200,2,'B', now());
insert into employee values (default, '4710','오늘도','수원정자',2800,2,'B', now());
insert into employee values (default, '2711','즐겁게','용인',3200,5,'C', now());
insert into employee values (default, '4709','내마음','천안',2800,4,'C', now());
insert into employee values (default, '3708','넌정말','수원인계',4000,6,'A', now());
insert into employee values (default, '2717','왜그래','서울논현',3400,3,'B', now());
insert into employee values (default, '2716','좋은맘','용인시',2200,2,'C', now());
insert into employee values (default, '2718','김파파','천안시',2600,2,'C', now());
insert into employee values (default, '3712','최선을','수원시',2800,3,'A', now());
insert into employee values (default, '4713','언제나','서울시',3200,3,'B', now());
insert into employee values (default, '4715','내가짱','용인시',3600,4,'B', now());
insert into employee values (default, '2714','공부노','수원시',4500,7,'A', now());
문제1. 직원의 이름과 주소와 근무년수를 출력하시오.
select name, addr, workingyear from employee;
+-----------+-----------------+-------------+
| name | addr | workingyear |
+-----------+-----------------+-------------+
| 홍길동 | 서울시 | 2 |
| 홍지요 | 서울특별시 | 3 |
| 나길수 | 수원시 | 2 |
| 오늘도 | 수원정자 | 2 |
| 즐겁게 | 용인 | 5 |
| 내마음 | 천안 | 4 |
| 넌정말 | 수원인계 | 6 |
| 왜그래 | 서울논현 | 3 |
| 좋은맘 | 용인시 | 2 |
| 김파파 | 천안시 | 2 |
| 최선을 | 수원시 | 3 |
| 언제나 | 서울시 | 3 |
| 내가짱 | 용인시 | 4 |
| 공부노 | 수원시 | 7 |
+-----------+-----------------+-------------+
문제2. 서울에 거주 하는 직원의 이름과 주소, 근무년수, 부서코드를 출력하시오.
select name, addr, workingyear,part from employee where addr like '%서울%';
+-----------+-----------------+-------------+------+
| name | addr | workingyear | part |
+-----------+-----------------+-------------+------+
| 홍길동 | 서울시 | 2 | A |
| 홍지요 | 서울특별시 | 3 | A |
| 왜그래 | 서울논현 | 3 | B |
| 언제나 | 서울시 | 3 | B |
+-----------+-----------------+-------------+------+
문제3. 기본급이 3000 이상 4000미만인 직원의 이름, 기본급, 부서코드를 출력하시오.
select name, salary, part from employee where salary>=3000 and salary <4000;
+-----------+--------+------+
| name | salary | part |
+-----------+--------+------+
| 즐겁게 | 3200 | C |
| 왜그래 | 3400 | B |
| 언제나 | 3200 | B |
| 내가짱 | 3600 | B |
+-----------+--------+------+
문제4. 기본급이 2500이상인 모든 직원의 이름과, 주소, 기본급, 근무년차를 출력하시오.
select name, addr, salary, workingyear from employee where salary>=2500;
+-----------+--------------+--------+-------------+
| name | addr | salary | workingyear |
+-----------+--------------+--------+-------------+
| 오늘도 | 수원정자 | 2800 | 2 |
| 즐겁게 | 용인 | 3200 | 5 |
| 내마음 | 천안 | 2800 | 4 |
| 넌정말 | 수원인계 | 4000 | 6 |
| 왜그래 | 서울논현 | 3400 | 3 |
| 김파파 | 천안시 | 2600 | 2 |
| 최선을 | 수원시 | 2800 | 3 |
| 언제나 | 서울시 | 3200 | 3 |
| 내가짱 | 용인시 | 3600 | 4 |
| 공부노 | 수원시 | 4500 | 7 |
+-----------+--------------+--------+-------------+
문제5. 기본급이 가장 많은 사람 3명의 정보를 출력하시오. (이름, 주소, 기본급)
select name, addr, salary from employee order by salary desc limit 0,3;
+-----------+--------------+--------+
| name | addr | salary |
+-----------+--------------+--------+
| 공부노 | 수원시 | 4500 |
| 넌정말 | 수원인계 | 4000 |
| 내가짱 | 용인시 | 3600 |
+-----------+--------------+--------+
문제6. 부서별 기본급의 평균을 구하시오, 단 평균급여가 2400 미만은 카운팅하지 마시오.
select avg(salary),part from employee group by part having avg(salary)>=2400;
+-------------+------+
| avg(salary) | part |
+-------------+------+
| 3100.0000 | A |
| 3040.0000 | B |
| 2700.0000 | C |
+-------------+------+
문제7. 기본급이 5번째로 많은 사람부터 7째로 많은 사람까지의 정보를 출력하시오. (이름, 주소, 기본급)
select name, addr, salary from employee order by salary desc limit 4,3;
+-----------+--------------+--------+
| name | addr | salary |
+-----------+--------------+--------+
| 즐겁게 | 용인 | 3200 |
| 언제나 | 서울시 | 3200 |
| 오늘도 | 수원정자 | 2800 |
+-----------+--------------+--------+
문제8. 입사년도가 가장 작은 사람의 이름과 기본급과 근무년수를 출력하시오.
select name, salary, workingyear from employee where workingyear
=(select min(workingyear) from employee);
+-----------+--------+-------------+
| name | salary | workingyear |
+-----------+--------+-------------+
| 홍길동 | 2000 | 2 |
| 나길수 | 2200 | 2 |
| 오늘도 | 2800 | 2 |
| 좋은맘 | 2200 | 2 |
| 김파파 | 2600 | 2 |
+-----------+--------+-------------+
문제9. 근무년수가 가장 높은 사람은 몇년입니까?
select max(workingyear) from employee;
+------------------+
| max(workingyear) |
+------------------+
| 7 |
+------------------+
문제10. 기본급의 평균을 구하시오. (소숫점 2째자리 이하는 버린다)
select truncate(avg(salary),1) from employee;
+-------------------------+
| truncate(avg(salary),1) |
+-------------------------+
| 2964.2 |
+-------------------------+
문제11. 부서별 기본급 평균을 구하시오, 단 평균급여가 2400이상인 그룹만 평균을 구하시오.
select avg(salary),part from employee group by part having avg(salary)>=2400;
+-------------+------+
| avg(salary) | part |
+-------------+------+
| 3100.0000 | A |
| 3040.0000 | B |
| 2700.0000 | C |
+-------------+------+
문제12. A파트와 B파트인 사람의 이름, 주소, 급여, 부서코드를 출력하되 기본급이 3000이하만 출력하시오.
select name, addr, salary, part from employee where (part='A' or part='B') and salary <=3000;
+-----------+-----------------+--------+------+
| name | addr | salary | part |
+-----------+-----------------+--------+------+
| 홍길동 | 서울시 | 2000 | A |
| 홍지요 | 서울특별시 | 2200 | A |
| 나길수 | 수원시 | 2200 | B |
| 오늘도 | 수원정자 | 2800 | B |
| 최선을 | 수원시 | 2800 | A |
+-----------+-----------------+--------+------+
문제13. C파트의 평균 급여를 출력하시오. (소숫점 2째자리까지 표현한다. 반올림)
select round(avg(salary),2) from employee where part='C';
+----------------------+
| round(avg(salary),2) |
+----------------------+
| 2700.00 |
+----------------------+
문제14. 홍길동의 이름을 오길동으로 개명하였다. 이를 처리 하시오.
update employee set name='오길동' where name='홍길동';
문제15. 나길수가 부서 이동을 했다. 직원번호는 3799이고 부서코드는 C이다. 이를 처리 하시오.
update employee set id='3799', part='C' where name='나길수';
문제16. 근무년수가 5년이상인 사람의 보너스를 출력하시오. 보너스는 근무년수 - 5 * 기본급이다.
출력은 이름 보너스 근무년수로 출력하시오.
select name, (workingyear-5)*salary bonus, workingyear from employee where workingyear>=5;
+-----------+-------+-------------+
| name | bonus | workingyear |
+-----------+-------+-------------+
| 즐겁게 | 0 | 5 |
| 넌정말 | 4000 | 6 |
| 공부노 | 9000 | 7 |
+-----------+-------+-------------+
문제17. 직원번호가 27이면 근무지가 서울, 37이면 수원, 47이면 용인이다.
모든 직원의 정보를 출력하시오. 출력은 직원코드 이름 부서코드 근무지
select id, name, part, (case when left(id,2)='27' then '서울'
when left(id,2)='37' then '수원' when left(id,2)='47' then '용인' end) '근무지' from employee;
+------+-----------+------+-----------+
| id | name | part | 근무지 |
+------+-----------+------+-----------+
| 2705 | 오길동 | A | 서울 |
| 3706 | 홍지요 | A | 수원 |
| 3799 | 나길수 | C | 수원 |
| 4710 | 오늘도 | B | 용인 |
| 2711 | 즐겁게 | C | 서울 |
| 4709 | 내마음 | C | 용인 |
| 3708 | 넌정말 | A | 수원 |
| 2717 | 왜그래 | B | 서울 |
| 2716 | 좋은맘 | C | 서울 |
| 2718 | 김파파 | C | 서울 |
| 3712 | 최선을 | A | 수원 |
| 4713 | 언제나 | B | 용인 |
| 4715 | 내가짱 | B | 용인 |
| 2714 | 공부노 | A | 서울 |
+------+-----------+------+-----------+
문제18. 주소를 통일 하려고 합니다.
서울에 거주 하는 모든 직원의 주소는 서울로 통일합니다. 이를 처리하세요.
update employee set addr='서울' where addr like '%서울%';
+----+------+-----------+--------------+--------+-------------+------+---------------------+
| no | id | name | addr | salary | workingyear | part | indata |
+----+------+-----------+--------------+--------+-------------+------+---------------------+
| 1 | 2705 | 오길동 | 서울 | 2000 | 2 | A | 2022-08-08 14:57:24 |
| 2 | 3706 | 홍지요 | 서울 | 2200 | 3 | A | 2022-08-08 14:57:24 |
| 3 | 3799 | 나길수 | 수원시 | 2200 | 2 | C | 2022-08-08 14:57:24 |
| 4 | 4710 | 오늘도 | 수원정자 | 2800 | 2 | B | 2022-08-08 14:57:24 |
| 5 | 2711 | 즐겁게 | 용인 | 3200 | 5 | C | 2022-08-08 14:57:24 |
| 6 | 4709 | 내마음 | 천안 | 2800 | 4 | C | 2022-08-08 14:57:24 |
| 7 | 3708 | 넌정말 | 수원인계 | 4000 | 6 | A | 2022-08-08 14:57:24 |
| 8 | 2717 | 왜그래 | 서울 | 3400 | 3 | B | 2022-08-08 14:57:24 |
| 9 | 2716 | 좋은맘 | 용인시 | 2200 | 2 | C | 2022-08-08 14:57:24 |
| 10 | 2718 | 김파파 | 천안시 | 2600 | 2 | C | 2022-08-08 14:57:24 |
| 11 | 3712 | 최선을 | 수원시 | 2800 | 3 | A | 2022-08-08 14:57:24 |
| 12 | 4713 | 언제나 | 서울 | 3200 | 3 | B | 2022-08-08 14:57:24 |
| 13 | 4715 | 내가짱 | 용인시 | 3600 | 4 | B | 2022-08-08 14:57:24 |
| 14 | 2714 | 공부노 | 수원시 | 4500 | 7 | A | 2022-08-08 14:57:25 |
+----+------+-----------+--------------+--------+-------------+------+---------------------+
문제19. 부서코드별 인원수를 구하시오.
select count(), part from employee group by part;
+----------+------+
| count() | part |
+----------+------+
| 5 | A |
| 5 | C |
| 4 | B |
+----------+------+
문제20. 부서코드별 최고급여와 최저급여를 출력하시오.
select max(salary) '최고급여' , min(salary) '최저급여',part '부서' from employee group by part;
+--------------+--------------+--------+
| 최고급여 | 최저급여 | 부서 |
+--------------+--------------+--------+
| 4500 | 2000 | A |
| 3200 | 2200 | C |
| 3600 | 2800 | B |
+--------------+--------------+--------+
문제21. 부서코드별 부서코드, 최고기본급, 최저기본급, 평균기본급을 출력하시오. 소수점2째짜리까지 표현(반올림)
select part '부서코드', max(salary) '최고급여' , min(salary) '최저급여',
round(avg(salary),2) '평균급여' from employee group by part;
+--------------+--------------+--------------+--------------+
| 부서코드 | 최고급여 | 최저급여 | 평균급여 |
+--------------+--------------+--------------+--------------+
| A | 4500 | 2000 | 3100.00 |
| C | 3200 | 2200 | 2600.00 |
| B | 3600 | 2800 | 3250.00 |
+--------------+--------------+--------------+--------------+
