MySQL 테이블 생성, 데이터 추가, 조회(3)

📢집약과 정렬

집약 : 열의 데이터의 합계, 평균 같은 집계값을 구하기 위한 함수

- count : 검색된 행의 개수
- sum : 합계
- avg : 평균
- max : 최대값
- min : 최소값
- group by : 특정 필드를 기준으로 묶음
- having : 그룹의 조건
- order by : 정렬. 기준을 여러개 줄 수 있음.
각 정렬 기준마다 asc, desc인지 결정해줘야 함.
- limit : 시작번지, 개수 => 시작번지부터 개수만큼 추출

 

📢실습(1)

----------------------------데이터 더 추가하기-----------
insert into student(num, name, age, address, major, score)
values(1001,'홍길동',23,'suwon','korean',89);
insert into student(num, name, age, address, major, score)
values(1002,'김길성',24,'seoul','english',87);
insert into student(num, name, age, address, major, score)
values(1003,'이순신',22,'suwon','computer',57);
insert into student(num, name, age, address, major, score)
values(1004,'박웅',23,'incheon','english',67);
insert into student(num, name, age, address, major, score)
values(1005,'이정희',21,'incheon','math',97);
insert into student(num, name, age, address, major, score)
values(1006,'최명화',23,'seoul','korean',67);
insert into student(num, name, age, address, major, score)
values(1007,'정지호',22,'jeju','computer',77);
insert into student(num, name, age, address, major, score)
values(1008,'김주호',23,'seoul','management',84);
insert into student(num, name, age, address, major, score)
values(1009,'김동주',24,'incheon','computer',99);

-- 성적으로 상위 3명만 나타내기
select * from student order by score desc limit 0,3;

-- 학과가 computer인 인원수
select count(major) as 'com_count' from student where major='computer';

-- 각 학과의 인원수(group 써야 함)
select major, count(major) as '학과별인원수' from student group by major;
--=> count(*)을 써도 상관 없지만, 데이터가 많아질 수록 성능 이슈가 있을 수 있으므로 내가 검색하고자 하는 행을 넣어주는게 좋음.

-- 각 학과별 점수 합계
select major, count(major) as '학과별인원수', sum(score) as '학과별 점수합계'
from student
group by major
order by major asc;

--  DB에서는 축약형 연산자 +=  ++ 쓸 수 없음. => 이거때문에 모든 학생들 나이를 1살로 만들어 버려서 다시 넣었다..ㅠㅠ

 

📢실습(2)

--student 테이블에서 다음 조건을 수행
--1. major가 'computer'인 학생 검색(모든 데이터 검색)
select * from student where major='computer';

 

--2. major가 'computer'이고, score가 70점 이상인 학생 검색
-- 이름만 검색 / 별칭으로 컴공성적우수생
select name as '컴공성적우수생', score from student where score >=70 and major='computer';

 

 

--3. score 70~90 사이인 학생 수
select count(score) as '성적 70~90사이' from student where score between 70 and 90;

 

 

--4. suwon / seoul에 사는 학생만 검색
select * from student where address in ('suwon', 'seoul');

 

--5. 지역을 중복없이 출력
select distinct address from student;

 

--6. 성이 박씨인 학생 검색
select * from student where name like '박%';

 

--7. 김주호 학생의 major를 english로 변경
update student set major='english' where name='김주호';

 

--8. 김길성 학생 데이터 삭제
delete from student where name='김길성';

 

📢실습(3)

캡쳐하기 번거로워서 그냥 복붙..

--1. 주소별 인원수 (주소, 인원수 출력) 
mysql> select address, count(address) as '인원수' from student group by address;
+---------+--------+
| address | 인원수 |
+---------+--------+
| suwon   |      3 |
| busan   |      2 |
| seoul   |      3 |
| incheon |      3 |
| jeju    |      1 |
+---------+--------+

--2. major가 computer인 score 합계(major, 합계 출력)
select major, sum(score) as 'score합계' from student where major='computer';
+----------+-----------+
| major    | score합계 |
+----------+-----------+
| computer |       297 |
+----------+-----------+

--3. 각 학과별 score 합계와 평균 (major, 합계, 평균 출력)
select major, sum(score) as '합계', avg(score) as '평균' from student group by major;
+------------+------+---------+
| major      | 합계 | 평균    |
+------------+------+---------+
| korea      |  334 | 83.5000 |
| computer   |  297 | 74.2500 |
| management |   75 | 75.0000 |
| english    |  151 | 75.5000 |
| math       |   97 | 97.0000 |
+------------+------+---------+

--4. 성적이 90점 이상인 학생들을 별도 저장하는 테이블을 생성
--테이블명 = student_best
create table if not exists student_best like student;
mysql> desc student_best;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| num     | int         | NO   | PRI | NULL    |       |
| name    | varchar(20) | NO   |     | NULL    |       |
| age     | int         | YES  |     | 20      |       |
| address | varchar(20) | YES  |     | NULL    |       |
| major   | varchar(20) | YES  |     | NULL    |       |
| score   | int         | YES  |     | 0       |       |
| grade   | int         | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+


--5. 성적이 90점 이상인 학생들만 복사(delete는 안해도됨)
insert into student_best
select * from student where score >= 90;
mysql> select * from student_best;
+------+--------+------+---------+----------+-------+-------+
| num  | name   | age  | address | major    | score | grade |
+------+--------+------+---------+----------+-------+-------+
|  112 | 김순이 |   22 | suwon   | korea    |    94 |     2 |
| 1005 | 이정희 |   21 | incheon | math     |    97 |     1 |
| 1009 | 김동주 |   24 | incheon | computer |    99 |     4 |
+------+--------+------+---------+----------+-------+-------+

--6. 주소별 인원수가 3이상인 주소를 출력
-- 그룹의 결과가 5이상인 값을 출력
-- 그룹의 조건 having
select address, count(address) c from student 
group by address
having c >= 3;