휴먼교육센터 개발자과정 7일차

조하영·2022년 8월 4일

휴먼교육센터 코딩공부

목록 보기

7/66

실습

create database hm0804; //데이터베이스 생성

use hm0804; // hm0804 데이터 베이스 사용

create table member( //테이블 만들기
no int primary key auto_increment,
name varchar(4),
addr varchar(10),
grade varchar(1),
point int default 10,
wdate timestamp default now()
);

insert into member values(default,'kim','suwon',1,400,default); //데이터삽입
insert into member values(default,'suji','city suwon',2,200,default);
insert into member values(default,'lee','seoul',3,20,default);
insert into member values(default,'choi','seoul',2,120,default);
insert into member values(default,'jee','yongin',1,20,default);
insert into member values(default,'lee','yongin',2,300,default);

select * from member; //자료가 제대로 삽입되었는지 조회
+----+------+------------+-------+-------+---------------------+
| no | name | addr | grade | point | wdate |
+----+------+------------+-------+-------+---------------------+
| 1 | kim | suwon | 1 | 400 | 2022-08-04 10:24:59 |
| 2 | suji | city suwon | 2 | 200 | 2022-08-04 10:27:29 |
| 3 | lee | seoul | 3 | 20 | 2022-08-04 10:27:29 |
| 4 | choi | seoul | 2 | 120 | 2022-08-04 10:27:30 |
| 5 | jee | yongin | 1 | 20 | 2022-08-04 10:28:48 |
| 6 | lee | yongin | 2 | 300 | 2022-08-04 10:28:49 |
+----+------+------------+-------+-------+---------------------+

현재 사용중인 데이터베이스에서 만들어진 테이블을 모두 보는 명령어
show tables; //사용중인 데이터 베이스(hm0804)에
만들어진 모든 테이블 명 확인
+------------------+
| Tables_in_hm0804 |
+------------------+
| member |
+------------------+

테이블 생성시 명령어 확인: show create table 테이블명;
show create table member; //만든 테이블 확인
+--------+------------------------------------------------------------------------+
| Table | Create Table
+--------+------------------------------------------------------------------------+
| member | CREATE TABLE member (
no int NOT NULL AUTO_INCREMENT,
name varchar(4) DEFAULT NULL,
addr varchar(10) DEFAULT NULL,
grade varchar(1) DEFAULT NULL,
point int DEFAULT '10',
wdate timestamp NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+--------+------------------------------------------------------------------------+

*데이터 사전
데이터 사전에는 데이터베이스에 데이터(일반 사용자)를 제외한 모든 정보가 있다.
테이블, 인덱스, 뷰, 무결성 제약(기본키,외래키), 함수, 프로시저, 트리거 등

테이블: 생성, 삭제, 수정

인덱스: 목차를 생성, select의 연산 속도에 이점,
insert, update, delete 속도를 희생, 생성과 삭제가 있음

뷰: 가상 테이블. 물리적인 테이블과 가상의 테이블을 구분하고 뷰를 만드는 이유를 이해해야 함.
테이블은 중복을 제거하여 무결성을 보장하기 위해 분리가 되어야 한다.
하지만 사용자는 합쳐진 테이블을 봐야할 때가 있다.
이때 뷰를 생성(분리된 테이블을 합쳐 가상의 테이블을 만드는 기술)
중요한 이론 개발자는 뷰를 잘 이해하고 활용해야 한다.

무결성제약: 무결성이 무엇이며 이를 보장하기 위한 방법(속성지정, 기본키, 외래키)

개발에 있어 가장 중요한 기술은 데이터 조작(insert, select, update, delete)

select문

select 문의 순서와 구조 이해
[]의 기호는 필수는 아니다. 사용해야 한다면 순서를 지켜야 한다.

6 select //검색하고 싶은 컬럼
1 from //검색할 대상 테이블
2 [where] //검색할 대상 테이블에서 튜플을 선택하기 위한 조건
3 [group by][having] //2번 작업후 그룹에 대한 분석
4 [order by] //1,2,3의 작업 결과를 정렬(오름차순, 내림차순)
5 [limit a,b] //4번까지의 작업에서 원하는 행의 수 지정
a번째 이후부터 b개의 데이터를 출력

샘플예제)

1. 모든사람의 이름과 주소와 학년을 출력
select name, addr, grade from member;
+------+------------+-------+
| name | addr | grade |
+------+------------+-------+
| kim | suwon | 1 |
| suji | city suwon | 2 |
| lee | seoul | 3 |
| choi | seoul | 2 |
| jee | yongin | 1 |
| lee | yongin | 2 |
+------+------------+-------+

2. 1학년의 이름과 주소와 포인트와 등록일을 출력
select name, addr, point, wdate from member where grade=1;
+------+--------+-------+---------------------+
| name | addr | point | wdate |
+------+--------+-------+---------------------+
| kim | suwon | 400 | 2022-08-04 10:24:59 |
| jee | yongin | 20 | 2022-08-04 10:28:48 |
+------+--------+-------+---------------------+

3. 포인트가 높은 순서부터 출력, 단 화면에는 name, addr, grade, point로 출력
select name, addr, grade, point from member order by point desc;
+------+------------+-------+-------+
| name | addr | grade | point |
+------+------------+-------+-------+
| kim | suwon | 1 | 400 |
| lee | yongin | 2 | 300 |
| suji | city suwon | 2 | 200 |
| choi | seoul | 2 | 120 |
| lee | seoul | 3 | 20 |
| jee | yongin | 1 | 20 |
+------+------------+-------+-------+

4. 1학년과 3학년의 주소와 포인트와 학년을 출력,
이때 포인트가 낮은 학생부터 출력
select addr, point, grade from member
where grade=1 or grade=3 order by point asc;
+--------+-------+-------+
| addr | point | grade |
+--------+-------+-------+
| seoul | 20 | 3 |
| yongin | 20 | 1 |
| suwon | 400 | 1 |
+--------+-------+-------+

8. 모든 학생의 포인트에 100을 합하여 모든 데이터 출력(단, 저장된 데이터를 변경하면 안됨)
select no, name, addr, grade, point+100, wdate from member;
+----+------+------------+-------+-----------+---------------------+
| no | name | addr | grade | point+100 | wdate |
+----+------+------------+-------+-----------+---------------------+
| 1 | kim | suwon | 1 | 500 | 2022-08-04 10:24:59 |
| 2 | suji | city suwon | 2 | 300 | 2022-08-04 10:27:29 |
| 3 | lee | seoul | 3 | 120 | 2022-08-04 10:27:29 |
| 4 | choi | seoul | 2 | 220 | 2022-08-04 10:27:30 |
| 5 | jee | yongin | 1 | 120 | 2022-08-04 10:28:48 |
| 6 | lee | yongin | 2 | 400 | 2022-08-04 10:28:49 |
+----+------+------------+-------+-----------+---------------------+

9. 포인트를 -100 했을 경우 0보다 큰 사람의 이름과 주소와 포인트를 출력
select name, addr, point-100 from member where point-100 > 0;
+------+------------+-----------+
| name | addr | point-100 |
+------+------------+-----------+
| kim | suwon | 300 |
| suji | city suwon | 100 |
| choi | seoul | 20 |
| lee | yongin | 200 |
+------+------------+-----------+

10. [심화] 포인트가 높은 사람부터 검색, 출력은 포인트가 세번째로 높은 사람의 모든 정보를 출력.
select * from member order by point desc limit 2,1;
+----+------+------------+-------+-------+---------------------+
| no | name | addr | grade | point | wdate |
+----+------+------------+-------+-------+---------------------+
| 2 | suji | city suwon | 2 | 200 | 2022-08-04 10:27:29 |
+----+------+------------+-------+-------+---------------------+

11. [추가] 포인트가 높은 사람부터 출력, 단 포인트가 같을 경우는 번호가 낮은 사람부터 출력

order by절에 컬럼의 우선순위부여하는 법
select [컬럼명] from [테이블명]
order by [첫째 기준 컬럼][정렬기준], [둘째 기준 컬럼][정렬기준];

select * from member order by point desc, no asc;
+----+------+------------+-------+-------+---------------------+
| no | name | addr | grade | point | wdate |
+----+------+------------+-------+-------+---------------------+
| 1 | kim | suwon | 1 | 400 | 2022-08-04 10:24:59 |
| 6 | lee | yongin | 2 | 300 | 2022-08-04 10:28:49 |
| 2 | suji | city suwon | 2 | 200 | 2022-08-04 10:27:29 |
| 4 | choi | seoul | 2 | 120 | 2022-08-04 10:27:30 |
| 3 | lee | seoul | 3 | 20 | 2022-08-04 10:27:29 |
| 5 | jee | yongin | 1 | 20 | 2022-08-04 10:28:48 |
+----+------+------------+-------+-------+---------------------+

select 조건 연산자

1. 산술연산자(숫자, 문자 연산): +(덧셈), -(뺄셈), *(곱셈), /(나눈 후 몫을 반환), %(나눈후 나머지 반환)
2. 비교연산자(참과 거짓을 구분): =(같다), <(작다), >(크다), <=(작거나 같다), >=(크거나 같다),
<>(같지 않다), between and(이상 이하), in(합집합) 예) grade in (1,3)->grade=1 or grade=3
3. 논리연산자: and(교집합), or(합집합) 예) where 조건1 and 조건2
4. like: 특정패턴( %, )에 사용
%(모든문자 의미), (하나의 문자 의미)
예) where name like 'kim%'; (kim으로 시작하는 이름)
where name like '_kim'; (kim 앞에 한자리 문자가 있는 이름)

함수를 사용하여 튜플 조작하기

함수의 생김새는 함수명과 소괄호 예)max()
함수를 공부하는 방법
1. 함수의 정의 이해
2. 함수의 사용법 이해
3. 함수의 결과값 이해
4. 함수를 select, from, where, group by 어느 절에 이용할지 판단

이 판단은 select 정의를 참고하여 판단

속성이 숫자인지 문자인지 판단하여 함수를 공부

숫자이면 일반적인 산술연산, 문자이면 부분 잘라내기 등

예) select count(no) as '명' from user;
1. 함수의 정의: 카운트 한다.
2. 함수 사용법: 괄호안에 카운트할 컬럼명을 넣는다.
3. 결과는 숫자값이다.
4. select 절에 사용되었으므로 출력으로 사용된다.
5. 숫자나 문자 상관없다.

과제

create table user(
no int primary key auto_increment,
name varchar(10),
addr varchar(10),
point double,
grade varchar(1),
jumin varchar(8));

insert into user values (default,'kimsu','suwon',99.12,1,'820405-1');
insert into user values (default,'leesu','suwon city',89.00,2,'970805-2');
insert into user values (default,'choihee','seoul',88.21,1,'910204-2');
insert into user values (default,'leesil','suwon',77.62,4,'850405-1');
insert into user values (default,'james','seoul',60.22,1,'871105-1');
insert into user values (default,'parksuji','suwon',90.12,3,'880405-2');
insert into user values (default,'kimrae','yougin',89.96,3,'820105-1');
insert into user values (default,'sangJin','youngin',99.31,3,'990420-2');
insert into user values (default,'Leechan','incheon',79.12,2,'970605-2');
insert into user values (default,'kimmi','incheon',79.92,1,'810505-1');
insert into user values (default,'ryusu','seoul',85.32,4,'861205-2');
insert into user values (default,'Gosu',null,82.13,4,'810715-1');

+----+----------+------------+-------+-------+----------+
| no | name | addr | point | grade | jumin |
+----+----------+------------+-------+-------+----------+
| 1 | kimsu | suwon | 99.12 | 1 | 820405-1 |
| 2 | leesu | suwon city | 89 | 2 | 970805-2 |
| 3 | choihee | seoul | 88.21 | 1 | 910204-2 |
| 4 | leesil | suwon | 77.62 | 4 | 850405-1 |
| 5 | james | seoul | 60.22 | 1 | 871105-1 |
| 6 | parksuji | suwon | 90.12 | 3 | 880405-2 |
| 7 | kimrae | yougin | 89.96 | 3 | 820105-1 |
| 8 | sangJin | youngin | 99.31 | 3 | 990420-2 |
| 9 | Leechan | incheon | 79.12 | 2 | 970605-2 |
| 10 | kimmi | incheon | 79.92 | 1 | 810505-1 |
| 11 | ryusu | seoul | 85.32 | 4 | 861205-2 |
| 12 | Gosu | NULL | 82.13 | 4 | 810715-1 |
+----+----------+------------+-------+-------+----------+

목표문제

1. 모든 사람들의 이름과 점수와 생년월일을 검색하시오.
select name '이름', point '점수', left(jumin,6) '생년월일' from user;
+----------+--------+--------------+
| 이름 | 점수 | 생년월일 |
+----------+--------+--------------+
| kimsu | 99.12 | 820405 |
| leesu | 89 | 970805 |
| choihee | 88.21 | 910204 |
| leesil | 77.62 | 850405 |
| james | 60.22 | 871105 |
| parksuji | 90.12 | 880405 |
| kimrae | 89.96 | 820105 |
| sangJin | 99.31 | 990420 |
| Leechan | 79.12 | 970605 |
| kimmi | 79.92 | 810505 |
| ryusu | 85.32 | 861205 |
| Gosu | 82.13 | 810715 |
+----------+--------+--------------+

4. 현재 점수에서 +10으로 보정한 점수를 출력하시오.
단. 100점을 넘을 수 있습니다. 원본 데이터에 반영되지 않습니다.
select no, name, addr, point+10, grade, jumin from user;
+----+----------+------------+----------+-------+----------+
| no | name | addr | point+10 | grade | jumin |
+----+----------+------------+----------+-------+----------+
| 1 | kimsu | suwon | 109.12 | 1 | 820405-1 |
| 2 | leesu | suwon city | 99 | 2 | 970805-2 |
| 3 | choihee | seoul | 98.21 | 1 | 910204-2 |
| 4 | leesil | suwon | 87.62 | 4 | 850405-1 |
| 5 | james | seoul | 70.22 | 1 | 871105-1 |
| 6 | parksuji | suwon | 100.12 | 3 | 880405-2 |
| 7 | kimrae | yougin | 99.96 | 3 | 820105-1 |
| 8 | sangJin | youngin | 109.31 | 3 | 990420-2 |
| 9 | Leechan | incheon | 89.12 | 2 | 970605-2 |
| 10 | kimmi | incheon | 89.92 | 1 | 810505-1 |
| 11 | ryusu | seoul | 95.32 | 4 | 861205-2 |
| 12 | Gosu | NULL | 92.13 | 4 | 810715-1 |
+----+----------+------------+----------+-------+----------+

5. 1학년의 점수를 +1점씩 올려서 출력하세요. (단, 원본 데이터에 반영되지 않습니다.)
select no, name, addr, point+1, grade, jumin from user where grade = 1;
+----+---------+---------+---------+-------+----------+
| no | name | addr | point+1 | grade | jumin |
+----+---------+---------+---------+-------+----------+
| 1 | kimsu | suwon | 100.12 | 1 | 820405-1 |
| 3 | choihee | seoul | 89.21 | 1 | 910204-2 |
| 5 | james | seoul | 61.22 | 1 | 871105-1 |
| 10 | kimmi | incheon | 80.92 | 1 | 810505-1 |
+----+---------+---------+---------+-------+----------+

8. 4학년의 점수를 10%로 올린 점수를 계산하세요.
(단, 원본데이터는 반영되지 않는다)
select point 1.1 from user where grade=4;
+-------------+
| point 1.1 |
+-------------+
| 85.382 |
| 93.852 |
| 90.343 |
+-------------+

9. 점수가 낮은 학생부터 출력하세요. 번호, 이름, 주소, 포인트
select no, name, addr, point from user order by point asc;
+----+----------+------------+-------+
| no | name | addr | point |
+----+----------+------------+-------+
| 5 | james | seoul | 60.22 |
| 4 | leesil | suwon | 77.62 |
| 9 | Leechan | incheon | 79.12 |
| 10 | kimmi | incheon | 79.92 |
| 12 | Gosu | NULL | 82.13 |
| 11 | ryusu | seoul | 85.32 |
| 3 | choihee | seoul | 88.21 |
| 2 | leesu | suwon city | 89 |
| 7 | kimrae | yougin | 89.96 |
| 6 | parksuji | suwon | 90.12 |
| 1 | kimsu | suwon | 99.12 |
| 8 | sangJin | youngin | 99.31 |
+----+----------+------------+-------+

10. 학년을 오름차순으로 정렬하시오, 단 학년이 동일 할 경우 포인트가 높은 사람이 먼저 출력됩니다.
출력은 번호, 이름, 학년, 포인트
select no, name, grade, point from user order by grade asc, point desc;
+----+----------+-------+-------+
| no | name | grade | point |
+----+----------+-------+-------+
| 1 | kimsu | 1 | 99.12 |
| 3 | choihee | 1 | 88.21 |
| 10 | kimmi | 1 | 79.92 |
| 5 | james | 1 | 60.22 |
| 2 | leesu | 2 | 89 |
| 9 | Leechan | 2 | 79.12 |
| 8 | sangJin | 3 | 99.31 |
| 6 | parksuji | 3 | 90.12 |
| 7 | kimrae | 3 | 89.96 |
| 11 | ryusu | 4 | 85.32 |
| 12 | Gosu | 4 | 82.13 |
| 4 | leesil | 4 | 77.62 |
+----+----------+-------+-------+

12. 현재의 점수에서 -10을 보정한 결과 80점 이상인 사람의 이름과 점수, 보정한 결과 점수를 출력하세요.
select name, point, point-10 from user where point-10>=80;
+----------+-------+----------+
| name | point | point-10 |
+----------+-------+----------+
| kimsu | 99.12 | 89.12 |
| parksuji | 90.12 | 80.12 |
| sangJin | 99.31 | 89.31 |
+----------+-------+----------+

본인을 한번 칭찬하세요 목표달성..
출력 형태는 임의대로 결정하되 결과값이 정확하면 됩니다

추가문제 <mysql 함수>

13. 학생은 총 몇명입니까?]
select count(no) from user;
+-----------+
| count(no) |
+-----------+
| 12 |
+-----------+

14. 1학년은 총 몇명입니까?
select count(no) from user where grade=1;
+-----------+
| count(no) |
+-----------+
| 4 |
+-----------+

15. 모든 학생의 이름과 학년을 출력하시오. 단 이름은 모두 소문자로만 출력하시오.
select lower(name), grade from user;
+-------------+-------+
| lower(name) | grade |
+-------------+-------+
| kimsu | 1 |
| leesu | 2 |
| choihee | 1 |
| leesil | 4 |
| james | 1 |
| parksuji | 3 |
| kimrae | 3 |
| sangjin | 3 |
| leechan | 2 |
| kimmi | 1 |
| ryusu | 4 |
| gosu | 4 |
+-------------+-------+

16. 2학년 학생의 이름과 학년과 주소를 출력하시오. 보안을 위해서 주소는 앞글자 2글자만 출력하시오.
select name, grade, left(addr,2) from user where grade=2;
+---------+-------+--------------+
| name | grade | left(addr,2) |
+---------+-------+--------------+
| leesu | 2 | su |
| Leechan | 2 | in |
+---------+-------+--------------+

17. 모든 학생의 점수의 일의 자리를 절삭하시오. - 반올림 없음 (단, 원본 데이터에 반영되지 않습니다.)
select truncate(point,-1) from user;
+--------------------+
| truncate(point,-1) |
+--------------------+
| 90 |
| 80 |
| 80 |
| 70 |
| 60 |
| 90 |
| 80 |
| 90 |
| 70 |
| 70 |
| 80 |
| 80 |
+--------------------+

18. 모든 학생의 점수를 소수 첫째자리 반올림하여 출력하시오. (단, 원본 데이터에 반영되지 않습니다.)
select round(point) from user;
+--------------+
| round(point) |
+--------------+
| 99 |
| 89 |
| 88 |
| 78 |
| 60 |
| 90 |
| 90 |
| 99 |
| 79 |
| 80 |
| 85 |
| 82 |
+--------------+

19. 2학년은 모두 몇명입니까?
select count(no) from user where grade=2;
+-----------+
| count(no) |
+-----------+
| 2 |
+-----------+

20. 1학년중 80점 이상은 몇명입니까?
select count(no) from user where grade=1 and point >=80;
+-----------+
| count(no) |
+-----------+
| 2 |
+-----------+

21. 3학년의 평균은 몇점입니까?
select avg(point) from user where grade=3;
+------------+
| avg(point) |
+------------+
| 93.13 |
+------------+

22. 전체 학생 중 최고점은 몇점입니까?
select max(point) from user;
+------------+
| max(point) |
+------------+
| 99.31 |
+------------+

23. 2학년 중 가장 낮은 점수를 획득한 점수는 몇점입니까?
select min(point) from user where grade=2;
+------------+
| min(point) |
+------------+
| 79.12 |
+------------+

못푸는 문제

24. 보안을 위해서 주소를 모두 출력하지 않고 앞의 세 글자만 출력하고 뒤에 *를 하나 붙힌다.
(단, 원본 데이터에 반영되지 않습니다.)
예)suwon >> su
select concat(left(addr,2),'') from user;
+--------------------------+
| concat(left(addr,2),'') |
+--------------------------+
| su |
| su |
| se |
| su |
| se |
| su |
| yo |
| yo |
| in |
| in |
| se |
| NULL |
+--------------------------+

26. 생년월일을 그대로 출력하지 말고
xx 년 xx 월 xx 일 형식으로 출력한다. (단, 원본 데이터에 반영되지 않습니다. 성별은 무시한다)
select concat(left(jumin,2),'년',substring(jumin,3,2),'월', substring(jumin,5,2),'일') '생년월일' from user;
+-----------------+
| 생년월일 |
+-----------------+
| 82년04월05일 |
| 97년08월05일 |
| 91년02월04일 |
| 85년04월05일 |
| 87년11월05일 |
| 88년04월05일 |
| 82년01월05일 |
| 99년04월20일 |
| 97년06월05일 |
| 81년05월05일 |
| 86년12월05일 |
| 81년07월15일 |
+-----------------+

27. 이름, 포인트, 학년, 생년월일, 성별을 출력한다. 성별은 생년월일로 판단하며 마지막 숫자가1이면 남
2이면 여라고 표시한다.

방법1.case when then 사용
select name '이름', point '점수', grade '학년', left(jumin,6) '생년월일',
(case
when right(jumin,1)=1 then '남'
when right(jumin,1)=2 then '여'
end) '성별'
from user;

방법2.replace() 사용
select name '이름', point '점수', grade '학년', left(jumin,6) '생년월일',
replace(replace(right(jumin,1), '1', '남'), '2', '여') '성별' from user;

+----------+--------+--------+--------------+--------+
| 이름 | 점수 | 학년 | 생년월일 | 성별 |
+----------+--------+--------+--------------+--------+
| kimsu | 99.12 | 1 | 820405 | 남 |
| leesu | 89 | 2 | 970805 | 여 |
| choihee | 88.21 | 1 | 910204 | 여 |
| leesil | 77.62 | 4 | 850405 | 남 |
| james | 60.22 | 1 | 871105 | 남 |
| parksuji | 90.12 | 3 | 880405 | 여 |
| kimrae | 89.96 | 3 | 820105 | 남 |
| sangJin | 99.31 | 3 | 990420 | 여 |
| Leechan | 79.12 | 2 | 970605 | 여 |
| kimmi | 79.92 | 1 | 810505 | 남 |
| ryusu | 85.32 | 4 | 861205 | 여 |
| Gosu | 82.13 | 4 | 810715 | 남 |
+----------+--------+--------+--------------+--------+

조하영

공부하는 개발자

이전 포스트

휴먼교육센터 개발자과정 6일차

다음 포스트