고정 헤더 영역
상세 컨텐츠
본문
728x90
반응형
# 성적처리
- 데이터베이스 : university
- 성적테이블 : grade_table2
- 자료형 정수 숫자 범위 지정
- INT : TINYINT(0~255 1바이트) < SMALLINT(-32768~32767 2바이트) < INT(4바이트) < BIGINT(8바이트)
필드(Filed)1 idx INT NOT NULL AUTO_INCREMENT PK
필드(Filed)2 학번 VARCHAR(6) NOT NULL CHECK(length(학번)=6) PK => 예시 ] '240001'
필드(Filed)3 국어 TINYINT NOT NULL CHECK(국어 BETWEEN 0 AND 100)
필드(Filed)4 영어 TINYINT NOT NULL CHECK(영어 BETWEEN 0 AND 100)
필드(Filed)5 수학 TINYINT NOT NULL CHECK(수학 BETWEEN 0 AND 100)
- 인코딩 utf설정 안한 상태에서 테이블 만들고
- 이후에 ALERT로 추가하기
- 10개 데이터 셋 등록
--인코딩 utf8 설정없이 테이블 생성
CREATE TABLE grade_table2(
idx INT NOT NULL AUTO_INCREMENT,
학번 VARCHAR(6) NOT NULL CHECK(length(학번)=6),
국어 INT NOT NULL CHECK(국어 BETWEEN 0 AND 100),
영어 INT NOT NULL CHECK(영어 BETWEEN 0 AND 100),
수학 INT NOT NULL CHECK(수학 BETWEEN 0 AND 100),
PRIMARY KEY(idx, 학번)
);
-- 테이블 생성 확인
-- 1. DESC 테이블명
MariaDB [university]> DESC grade_table2;
+--------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------+------+-----+---------+----------------+
| idx | int(11) | NO | PRI | NULL | auto_increment |
| 학번 | varchar(6) | NO | PRI | NULL | |
| 국어 | int(11) | NO | | NULL | |
| 영어 | int(11) | NO | | NULL | |
| 수학 | int(11) | NO | | NULL | |
+--------+------------+------+-----+---------+----------------+
5 rows in set (0.011 sec)
-- 인코딩 UTF8 변경
ALTER TABLE grade_table2 CHARSET=`UTF8`;
-- 테이블 생성 확인
-- 2. SHOW CREATE TABLE 테이블명
SHOW CREATE TABLE grade_table2;
MariaDB [university]> SHOW CREATE TABLE grade_table2;
| grade_table2 | CREATE TABLE `grade_table2` (
`idx` int(11) NOT NULL AUTO_INCREMENT,
`학번` varchar(6) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL CHECK (octet_length(`학번`) = 6),
`국어` int(11) NOT NULL CHECK (`국어` between 0 and 100),
`영어` int(11) NOT NULL CHECK (`영어` between 0 and 100),
`수학` int(11) NOT NULL CHECK (`수학` between 0 and 100),
PRIMARY KEY (`idx`,`학번`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci |
1 row in set (0.001 sec)
-- 데이터 입력
INSERT INTO grade_table2(학번,국어,영어,수학) VALUES
('240001','78','46','79'),
('240002','97','90','90'),
('240003','81','88','80'),
('240004','76','88','70'),
('240005','88','78','88'),
('240006','97','77','98'),
('240007','98','86','100'),
('240008','100','40','68'),
('240009','67','85','76'),
('240010','55','55','94');
-- 데이터 목록 확인 SELECT * FROM 테이블명;
MariaDB [university]> SELECT * FROM grade_table2;
+-----+--------+--------+--------+--------+
| idx | 학번 | 국어 | 영어 | 수학 |
+-----+--------+--------+--------+--------+
| 1 | 240001 | 78 | 46 | 79 |
| 2 | 240002 | 97 | 90 | 90 |
| 3 | 240003 | 81 | 88 | 80 |
| 4 | 240004 | 76 | 88 | 70 |
| 5 | 240005 | 88 | 78 | 88 |
| 6 | 240006 | 97 | 77 | 98 |
| 7 | 240007 | 98 | 86 | 100 |
| 8 | 240008 | 100 | 40 | 68 |
| 9 | 240009 | 67 | 85 | 76 |
| 10 | 240010 | 55 | 55 | 94 |
+-----+--------+--------+--------+--------+
10 rows in set (0.000 sec)
-- 조회 성적처리
-- 1. 총점계산
-- (국어+영어+수학) AS `총점`
-- 총점을 연산하고 , idx, 학번,국어, 수학, 총점
SELECT *, (국어+영어+수학) AS `총점` FROM grade_table2;
MariaDB [university]> SELECT *, (국어+영어+수학) AS `총점` FROM grade_table2;
+-----+--------+--------+--------+--------+--------+
| idx | 학번 | 국어 | 영어 | 수학 | 총점 |
+-----+--------+--------+--------+--------+--------+
| 1 | 240001 | 78 | 46 | 79 | 203 |
| 2 | 240002 | 97 | 90 | 90 | 277 |
| 3 | 240003 | 81 | 88 | 80 | 249 |
| 4 | 240004 | 76 | 88 | 70 | 234 |
| 5 | 240005 | 88 | 78 | 88 | 254 |
| 6 | 240006 | 97 | 77 | 98 | 272 |
| 7 | 240007 | 98 | 86 | 100 | 284 |
| 8 | 240008 | 100 | 40 | 68 | 208 |
| 9 | 240009 | 67 | 85 | 76 | 228 |
| 10 | 240010 | 55 | 55 | 94 | 204 |
+-----+--------+--------+--------+--------+--------+
10 rows in set (0.000 sec)
-- 2. 평균 연산
-- 소수점 자리 2자리 출력 ROUND(점수, 2) 반올림함수
-- ROUND((국어+영어+수학)/3, 소숫점2자리) AS `평균`
SELECT *, (국어+영어+수학) AS `총점`, ROUND((국어+영어+수학)/3 , 2) AS `평균` FROM grade_table2;
MariaDB [university]> SELECT *, (국어+영어+수학) AS `총점`, ROUND((국어+영어+수학)/3 , 2) AS `평균` FROM grade_table2;
+-----+--------+--------+--------+--------+--------+--------+
| idx | 학번 | 국어 | 영어 | 수학 | 총점 | 평균 |
+-----+--------+--------+--------+--------+--------+--------+
| 1 | 240001 | 78 | 46 | 79 | 203 | 67.67 |
| 2 | 240002 | 97 | 90 | 90 | 277 | 92.33 |
| 3 | 240003 | 81 | 88 | 80 | 249 | 83.00 |
| 4 | 240004 | 76 | 88 | 70 | 234 | 78.00 |
| 5 | 240005 | 88 | 78 | 88 | 254 | 84.67 |
| 6 | 240006 | 97 | 77 | 98 | 272 | 90.67 |
| 7 | 240007 | 98 | 86 | 100 | 284 | 94.67 |
| 8 | 240008 | 100 | 40 | 68 | 208 | 69.33 |
| 9 | 240009 | 67 | 85 | 76 | 228 | 76.00 |
| 10 | 240010 | 55 | 55 | 94 | 204 | 68.00 |
+-----+--------+--------+--------+--------+--------+--------+
10 rows in set (0.000 sec)
-- 3. 석차 연산 --- 연습필요!!!!
-- RANK() OVER(석차 정렬기준 평균 내림차순)
SELECT *, (국어+영어+수학) AS `총점`, ROUND((국어+영어+수학)/3 , 2) AS `평균` , RANK() OVER(ORDER BY `평균` DESC) AS `석차` FROM grade_table2;
MariaDB [university]> SELECT *, (국어+영어+수학) AS `총점`, ROUND((국어+영어+수학)/3 , 2) AS `평균` , RANK() OVER(ORDER BY `평균` DESC) AS `석차` FROM grade_table2;
+------+--------+--------+--------+--------+--------+--------+--------+
| idx | 학번 | 국어 | 영어 | 수학 | 총점 | 평균 | 석차 |
+------+--------+--------+--------+--------+--------+--------+--------+
| 7 | 240007 | 98 | 86 | 100 | 284 | 94.67 | 1 |
| 2 | 240002 | 97 | 90 | 90 | 277 | 92.33 | 2 |
| 6 | 240006 | 97 | 77 | 98 | 272 | 90.67 | 3 |
| 5 | 240005 | 88 | 78 | 88 | 254 | 84.67 | 4 |
| 3 | 240003 | 81 | 88 | 80 | 249 | 83.00 | 5 |
| 4 | 240004 | 76 | 88 | 70 | 234 | 78.00 | 6 |
| 9 | 240009 | 67 | 85 | 76 | 228 | 76.00 | 7 |
| 8 | 240008 | 100 | 40 | 68 | 208 | 69.33 | 8 |
| 10 | 240010 | 55 | 55 | 94 | 204 | 68.00 | 9 |
| 1 | 240001 | 78 | 46 | 79 | 203 | 67.67 | 10 |
+------+--------+--------+--------+--------+--------+--------+--------+
10 rows in set (0.000 sec)
-- 4-1. 석차 연산
-- RANK() OVER(석차 정렬기준 평균 내림차순) 정렬기준 추가 (idx 오름차순)
SELECT *, (국어+영어+수학) AS `총점`, ROUND((국어+영어+수학)/3 , 2) AS `평균` , RANK() OVER(ORDER BY `평균` DESC) AS `석차`
FROM grade_table2
ORDER BY idx ;
MariaDB [university]> SELECT *, (국어+영어+수학) AS `총점`, ROUND((국어+영어+수학)/3 , 2) AS `평균` , RANK() OVER(ORDER BY `평균` DESC) AS `석차`
-> FROM grade_table2
-> ORDER BY idx ;
+------+--------+--------+--------+--------+--------+--------+--------+
| idx | 학번 | 국어 | 영어 | 수학 | 총점 | 평균 | 석차 |
+------+--------+--------+--------+--------+--------+--------+--------+
| 1 | 240001 | 78 | 46 | 79 | 203 | 67.67 | 10 |
| 2 | 240002 | 97 | 90 | 90 | 277 | 92.33 | 2 |
| 3 | 240003 | 81 | 88 | 80 | 249 | 83.00 | 5 |
| 4 | 240004 | 76 | 88 | 70 | 234 | 78.00 | 6 |
| 5 | 240005 | 88 | 78 | 88 | 254 | 84.67 | 4 |
| 6 | 240006 | 97 | 77 | 98 | 272 | 90.67 | 3 |
| 7 | 240007 | 98 | 86 | 100 | 284 | 94.67 | 1 |
| 8 | 240008 | 100 | 40 | 68 | 208 | 69.33 | 8 |
| 9 | 240009 | 67 | 85 | 76 | 228 | 76.00 | 7 |
| 10 | 240010 | 55 | 55 | 94 | 204 | 68.00 | 9 |
+------+--------+--------+--------+--------+--------+--------+--------+
10 rows in set (0.000 sec)
-- 4-2. 석차 연산
-- RANK() OVER(석차 정렬기준 평균 내림차순) 정렬기준 추가 (석차 오름차순)
SELECT *, (국어+영어+수학) AS `총점`, ROUND((국어+영어+수학)/3 , 2) AS `평균` , RANK() OVER(ORDER BY `평균` DESC) AS `석차`
FROM grade_table2
ORDER BY `석차` ;
MariaDB [university]> SELECT *, (국어+영어+수학) AS `총점`, ROUND((국어+영어+수학)/3 , 2) AS `평균` , RANK() OVER(ORDER BY `평균` DESC) AS `석차`
-> FROM grade_table2
-> ORDER BY `석차` ;
+------+--------+--------+--------+--------+--------+--------+--------+
| idx | 학번 | 국어 | 영어 | 수학 | 총점 | 평균 | 석차 |
+------+--------+--------+--------+--------+--------+--------+--------+
| 7 | 240007 | 98 | 86 | 100 | 284 | 94.67 | 1 |
| 2 | 240002 | 97 | 90 | 90 | 277 | 92.33 | 2 |
| 6 | 240006 | 97 | 77 | 98 | 272 | 90.67 | 3 |
| 5 | 240005 | 88 | 78 | 88 | 254 | 84.67 | 4 |
| 3 | 240003 | 81 | 88 | 80 | 249 | 83.00 | 5 |
| 4 | 240004 | 76 | 88 | 70 | 234 | 78.00 | 6 |
| 9 | 240009 | 67 | 85 | 76 | 228 | 76.00 | 7 |
| 8 | 240008 | 100 | 40 | 68 | 208 | 69.33 | 8 |
| 10 | 240010 | 55 | 55 | 94 | 204 | 68.00 | 9 |
| 1 | 240001 | 78 | 46 | 79 | 203 | 67.67 | 10 |
+------+--------+--------+--------+--------+--------+--------+--------+
10 rows in set (0.000 sec)
-- 5. 석차 연산
-- RANK() OVER(석차 정렬기준 평균 내림차순) 정렬기준 추가 (평균 내림차순)
SELECT *, (국어+영어+수학) AS `총점`, ROUND((국어+영어+수학)/3 , 2) AS `평균` , RANK() OVER(ORDER BY `평균` DESC) AS `석차`
FROM grade_table2
ORDER BY `평균` DESC ;
MariaDB [university]> SELECT *, (국어+영어+수학) AS `총점`, ROUND((국어+영어+수학)/3 , 2) AS `평균` , RANK() OVER(ORDER BY `평균` DESC) AS `석차`
-> FROM grade_table2
-> ORDER BY `평균` DESC ;
+------+--------+--------+--------+--------+--------+--------+--------+
| idx | 학번 | 국어 | 영어 | 수학 | 총점 | 평균 | 석차 |
+------+--------+--------+--------+--------+--------+--------+--------+
| 7 | 240007 | 98 | 86 | 100 | 284 | 94.67 | 1 |
| 2 | 240002 | 97 | 90 | 90 | 277 | 92.33 | 2 |
| 6 | 240006 | 97 | 77 | 98 | 272 | 90.67 | 3 |
| 5 | 240005 | 88 | 78 | 88 | 254 | 84.67 | 4 |
| 3 | 240003 | 81 | 88 | 80 | 249 | 83.00 | 5 |
| 4 | 240004 | 76 | 88 | 70 | 234 | 78.00 | 6 |
| 9 | 240009 | 67 | 85 | 76 | 228 | 76.00 | 7 |
| 8 | 240008 | 100 | 40 | 68 | 208 | 69.33 | 8 |
| 10 | 240010 | 55 | 55 | 94 | 204 | 68.00 | 9 |
| 1 | 240001 | 78 | 46 | 79 | 203 | 67.67 | 10 |
+------+--------+--------+--------+--------+--------+--------+--------+
10 rows in set (0.000 sec)
-- 6. 성적 상위 5명 출력
-- Limit 5
SELECT *, (국어+영어+수학) AS `총점`, ROUND((국어+영어+수학)/3 , 2) AS `평균` , RANK() OVER(ORDER BY `평균` DESC) AS `석차`
FROM grade_table2
ORDER BY `평균` DESC LIMIT 5;
MariaDB [university]> SELECT *, (국어+영어+수학) AS `총점`, ROUND((국어+영어+수학)/3 , 2) AS `평균` , RANK() OVER(ORDER BY `평균` DESC) AS `석차`
-> FROM grade_table2
-> ORDER BY `평균` DESC LIMIT 5;
+------+--------+--------+--------+--------+--------+--------+--------+
| idx | 학번 | 국어 | 영어 | 수학 | 총점 | 평균 | 석차 |
+------+--------+--------+--------+--------+--------+--------+--------+
| 7 | 240007 | 98 | 86 | 100 | 284 | 94.67 | 1 |
| 2 | 240002 | 97 | 90 | 90 | 277 | 92.33 | 2 |
| 6 | 240006 | 97 | 77 | 98 | 272 | 90.67 | 3 |
| 5 | 240005 | 88 | 78 | 88 | 254 | 84.67 | 4 |
| 3 | 240003 | 81 | 88 | 80 | 249 | 83.00 | 5 |
+------+--------+--------+--------+--------+--------+--------+--------+
5 rows in set (0.000 sec)
-- 7. 성적 하위 5명 출력
SELECT *, (국어+영어+수학) AS `총점`, ROUND((국어+영어+수학)/3 , 2) AS `평균` , RANK() OVER(ORDER BY `평균` DESC) AS `석차`
FROM grade_table2
ORDER BY `평균` LIMIT 5;
MariaDB [university]> SELECT *, (국어+영어+수학) AS `총점`, ROUND((국어+영어+수학)/3 , 2) AS `평균` , RANK() OVER(ORDER BY `평균` DESC) AS `석차`
-> FROM grade_table2
-> ORDER BY `평균` LIMIT 5;
+------+--------+--------+--------+--------+--------+--------+--------+
| idx | 학번 | 국어 | 영어 | 수학 | 총점 | 평균 | 석차 |
+------+--------+--------+--------+--------+--------+--------+--------+
| 1 | 240001 | 78 | 46 | 79 | 203 | 67.67 | 10 |
| 10 | 240010 | 55 | 55 | 94 | 204 | 68.00 | 9 |
| 8 | 240008 | 100 | 40 | 68 | 208 | 69.33 | 8 |
| 9 | 240009 | 67 | 85 | 76 | 228 | 76.00 | 7 |
| 4 | 240004 | 76 | 88 | 70 | 234 | 78.00 | 6 |
+------+--------+--------+--------+--------+--------+--------+--------+
5 rows in set (0.000 sec)
-- 8. [집계] 피벗=>과목별 총점
-- SUM()
SELECT SUM(`국어`) AS `국어총점`, SUM(`영어`) AS `영어총점`, SUM(`수학`) AS `수학총점`
FROM grade_table2;
MariaDB [university]> SELECT SUM(`국어`) AS `국어총점`, SUM(`영어`) AS `영어총점`, SUM(`수학`) AS `수학총 점`
-> FROM grade_table2;
+--------------+--------------+--------------+
| 국어총점 | 영어총점 | 수학총점 |
+--------------+--------------+--------------+
| 837 | 733 | 843 |
+--------------+--------------+--------------+
1 row in set (0.000 sec)
-- 9. [집계] =>과목별 평균
-- AVG()
SELECT AVG(`국어`) AS `국어평균`, AVG(`영어`) AS `영어평균`, AVG(`수학`) AS `수학평균`
FROM grade_table2;
MariaDB [university]> SELECT AVG(`국어`) AS `국어평균`, AVG(`영어`) AS `영어평균`, AVG(`수학`) AS `수학평 균`
-> FROM grade_table2;
+--------------+--------------+--------------+
| 국어평균 | 영어평균 | 수학평균 |
+--------------+--------------+--------------+
| 83.7000 | 73.3000 | 84.3000 |
+--------------+--------------+--------------+
1 row in set (0.000 sec)
-- 응용 + ROUND 소수반올림 함수
SELECT ROUND(AVG(`국어`), 2) AS `국어평균`, ROUND(AVG(`영어`), 2) AS `영어평균`, ROUND(AVG(`수학`), 2) AS `수학평균`
FROM grade_table2;
MariaDB [university]> SELECT ROUND(AVG(`국어`), 2) AS `국어평균`, ROUND(AVG(`영어`), 2) AS `영어평균`, ROUND(AVG(`수학`), 2) AS `수학평균`
-> FROM grade_table2;
+--------------+--------------+--------------+
| 국어평균 | 영어평균 | 수학평균 |
+--------------+--------------+--------------+
| 83.70 | 73.30 | 84.30 |
+--------------+--------------+--------------+
1 row in set (0.000 sec)
-- 10. [집계] => 과목별 최대값
-- MAX()
SELECT MAX(`국어`) AS `국어최대값`, MAX(`영어`) AS `영어최대값`, MAX(`수학`) AS `수학최대값`
FROM grade_table2;
MariaDB [university]> SELECT MAX(`국어`) AS `국어최대값`, MAX(`영어`) AS `영어최대값`, MAX(`수학`) AS `수 학최대값`
-> FROM grade_table2;
+-----------------+-----------------+-----------------+
| 국어최대값 | 영어최대값 | 수학최대값 |
+-----------------+-----------------+-----------------+
| 100 | 90 | 100 |
+-----------------+-----------------+-----------------+
1 row in set (0.000 sec)
-- 11. [집계] => 과목별 최소값
-- MIN()
SELECT MIN(`국어`) AS `국어최소값`, MIN(`영어`) AS `영어최소값`, MIN(`수학`) AS `수학최소값`
FROM grade_table2;
MariaDB [university]> SELECT MIN(`국어`) AS `국어최소값`, MIN(`영어`) AS `영어최소값`, MIN(`수학`) AS `수 학최소값`
-> FROM grade_table2;
+-----------------+-----------------+-----------------+
| 국어최소값 | 영어최소값 | 수학최소값 |
+-----------------+-----------------+-----------------+
| 55 | 40 | 68 |
+-----------------+-----------------+-----------------+
1 row in set (0.000 sec)728x90
반응형
