🚩 20241230_수업내용(지난 수업 내용 총정리/order by/group by/)

지난 주 수업 복습~*

 

데이터베이스란

데이터(문자, 숫자, 이미지, 음악, 영화 등등)

데이터베이스는 데이터를 저장하기 위한 프로그램

 

저장하는 방법은 메모장, 엑셀, 한글, 인터넷, 폴더

(저장한 데이터를 관리하려면) 목록(리스트화) 데이터를 언제 저장하였고 어떤 데이터이고 어떤 내용이고 언제 저장했고 언제 수정했고 누가 저장했고 누가 수정하였고 누가 열어볼 수 있는 권한이 있고 등등을 기록해놓아야 데이터를 체계적으로 관리할 수 있다.

 

데이터베이스 종류

mysql, oracle, maria, sqlLite, ms-sql, 등

= DBMS -> database management system

---

데이터베이스 사용하기

1. root 계정으로 사용 안하기 (슈퍼 관리자 계정이기에 보안 문제 있음)

 

2. 일반 계정이나, 별도의 관리자 계정 생성하여 사용

- 계정 만들기 sql질의문 (query 문)

   create user 계정명 identified by '비밀번호';

 

3. 계정에 권한 부여

- sql질의문 (query문)

   grant all privileges on DB명.테이블명 to 계정명

- 전체 데이터베이스에 권한은

   grant all privileges on *.* to 계정명; (모든 권한을 주겠다)

   grant create, drop on *.* to 계정명; (만들기(create)와 삭제(drop) 할 수 있는 권한을 주겠다)

- 권한 해제

   revoke all privileges on *.* from 계정명 (누구로부터 뺏어오겠나)

 

4. 데이터베이스를 생성하기 ( 일반계정일 경우 사용할 수 있는 데이터베이스 공간이 필요하다. )

데이터베이스를 저장하기 위해서는 먼저 데이터베이스가 필요하다. 데이터베이스는 체계적으로 저장되어 있는 데이터의 모음

- 일반적으로 데이터베이스 이름은 일반 계정명과 동일하게 사용한다.

  ex. 계정명 skyblue , 데이터베이스명 skyblue

  create database 데이터베이스명;

 

5. 데이터 저장 및 관리, 수정, 삭제, 조회를 위해 일반계정으로 접속

- cmd(터미널)을 통해 접속

  로컬 접속 - mysql 명령어로 접속

　　1. mysql -u 계정명 -p

　　2. 비밀번호 입력

- 네트워크 접속-> 주소(mysql설치된 컴퓨터의 IP나 도메인명 port 번호) -u 계정명 -p

- 프로그램을 통해 접속 (네트워크 접속 방법)

   mysql 설치된 컴퓨터 주소 (로컬인경우 localhost)

   데이터베이스명

   계정명과 비밀번호

 

6. 테이블 만들기 - 실제 데이터가 저장되는 곳

- create table 테이블명 (컬럼명1 데이터타입, 컬럼명2 데이터타입, 컬럼명3 데이터타입);

- 테이블에는 하나 이상의 primary key 설정하기

- 왜? 테이블 컬럼들에 저장되는 데이터를 묶어서 레코드라고 한다. 하나의 레코드는 다른 레코드들과 구분이 되어야(유일값) 관리하기에 용이하고 데이터베이스의 무결성 충족시켜줘야 하므로 primary key가 필요하다.

　-> 데이터들의 일관성 유지

　-> 데이터의 모호함 제거

　-> 중복데이터 관리

　-> 레코드 검색에 용이

  

7. 데이터 저장

- insert into 테이블명 (컬럼명1, 컬럼명2) values (데이터1, 데이터2);

- 컬럼명은 데이터가 저장될 컬럼명을 작성, 데이터는 컬럼명의 위치에 맞춰서 작성

- 모든 컬럼에 데이터를 저장하는 경우

   ㄴ insert into 테이블명 values (데이터1, 데이터2, 데이터3);

 

8. 조회, 삭제, 수정

- 삭제 : delete from 테이블명; -> 테이블의 전체 데이터 삭제

              delete from 테이블명 where 컬럼명=값; -> 해당컬럼에 저장된 데이터중 값과 일치하는 모든 레코드 삭제

- 수정 : update 테이블명 set 컬럼명=변경값; -> 테이블 전체 변경 (해당 컬럼 데이터들이 변경값으로 )

              update 테이블명 set a=b where b='가'; -> 테이블에서 b컬럼의 데이터중에서 '가'와 일치하는 모든 레코드들이 a 컬럼 데이터를 10으로 변경

- 조회 : select 컬럼명1, 컬럼명2 from 테이블명; -> 테이블에서 컬럼1, 컬럼2의 모든 데이터 출력

              select * from 테이블명; -> 테이블의 전체 데이터 출력

              select age, name, tel from member where age>30; -> 나이가 30초과된 사람들의 나이, 이름, 전화번호 출력

---

연산자

산술연산자, 비교연산자, 논리연산자

 

* 산술연산자 : +, -, *, /, %

* 비교연산자 : >, <, >=, <=, ==, !=, <>

* 논리연산자 : and, or, not

---

함수

count, sum, avg, max, min

함수는 함수이름() 형식을 갖춰야 실행된다.

ex. count(), sum(), avg()

 

like -> 포함 데이터 조건

　컬럼명 like 'ab%'; - ab로 시작하는 데이터

　\ <- 역슬러쉬 특수한 데이터를 포함하여 사용하려면 역슬러쉬 사용

 

 

👀 문제 풀어보기
1. 컴퓨터공학과에 속한 학생들의 이름(s_name)과 학번(s_no)를 조회하세요.
= select s_name, s_no from student where dept = '컴퓨터공학과';

2. 각 학년별 학생 수를 출력하세요
= select count(year) from student where year=2;

3. 이름에 '김'이 포함된 학생들의 이름과 학번을 조회하세요
= select s_name, s_no from student where s_name like '김%';

4. enrollment 테이블에서 학번이 9911001인 학생의 성적을 A+로 변경하세요.
= update enrollment set grade='A+' where s_no='9911001';

5. 컴퓨터공학과 소속이며 2학년인 학생들의 이름과 학번을 조회하세요.
= select s_name, s_no from student where dept='컴퓨터공학과' and year='2';

6. lecture 테이블에서 T337 강의실에서 열리는 강의의 강의 번호(c_no)와 강의 요일(day_week)을 조회하세요.
= select c_no, day_week from lecture where room_no='T337';

---

1. 정렬(order by)

내림차순(기본/ desc ), 오름차순(asc)

ㄴ 기준 컬럼을 정해야 정렬을 할 수 있다.

모든 데이터들이 year를 기준으로 순서가 바뀜

 

내림차순 정렬

 

2. 묶기 (group by)

위 아래 같은 의미

 

인원 수 카운팅하기

 

평균값 내보기

 

any_value (임의로)

 

group_concat (한줄로 그룹화시켜 보기)

 

having 조건으로 묶기

그룹화 뒤 특정 조건을 지어주려면 사용

 

- 각 강좌별 시험 본 학생이 5명 이상인 강좌번호는?

select c_no from enrollment group by c_no having count(*)>=5;

 ====> 5개 이상인 그룹만 출력해라

 

- 각 강좌별 시험점수의 평균이 80점 이상인 강좌번호는?

select c_no from enrollment group by c_no having avg(merits)>=80;

where 뒤에 는 그룹화해주는 컬럼은 가능하지만 다른 컬럼은 불가능

 

- 값이 없는 데이터값만 뽑아내기

select p_no, p_name from professor where p_address = '' or p_address is null;

 

- null값만 뽑아내려면 - p_address is null;

- 테이블의 컬럼에 데이터가 저장되지 않은 상태 값

- null 또는 ''(빈값)

 

그룹화(group by)의 목적
여러개의 데이터들을 하나의 집합(그룹)으로 묶어 처리할 목적
group by는 집계 함수를 적용하는 것이 일반적
집계함수란 count, sum, avg와 같은 함수
데이터들을 그룹화 시키면 식별, 가독성을 높일 수 있다.
ex. '2학년 전체를 출력하시오' < 이건 그냥 where만 사용하면 됨.

👀 문제 풀어보기
1. 강의 테이블에서 요일 별 강의는 몇개씩인가?
= elect day_week, count(c_no) as 강의갯수 from lecture group by day_week;

2. 교수 테이블에서 교수, 부교수, 조교수는 각 각 몇명인가?
= select count(p_no), grade from professor group by grade;

3. 교수들의 평균 연봉

4. 교수 테이블에서 소속학과별 교수, 조교수, 부교수는 총 몇명인가?
= select count(p_no) as 교수인원 from professor group by dept_no;

5. 학생테이블에서 각학년별 학생수를 계산하고, 학생 수가 많은 학년부터 적은 학년순으로 정렬하여 출력
= select year, count(*) as std_count from student group by year order by std_count desc;

6. lecture 테이블에서 각학과별로 강의수를 계산하고, 강의수가 적은 학과 순으로 정렬
= select room_no, count(*) as 강의수 from lecture group by room_no order by 강의수;

---

2. 두개 이상의 테이블 조회

------------- 둘 중 데이터 중 하나의 데이터를 확실하게 알 수 있는 상황에서 사용가능

 

김수철 교수는 컴퓨터공학과 교수이다.

방법1 (학과/번호를 알고 있다는 전제 하에)

- 두개 이상의 테이블을 사용하는 경우 임시의 테이블 별명을 설정해주어야한다.

- 컬럼명 앞에 <테이블 별명+ . >을 붙여 테이블명과 연결해준다.

 

 

방법2 (교수 이름-김종훈이라는 정보만 알고 있을때)

- 정보 대조하여 출력하기

 

학생 테이블 등록 테이블에서 이미자의 성적 조회

- 두 테이블에서 '이미자'와 일치하는 정보를 찾기

 

- 컴퓨터공학과 학생들의 이름, 성적과 점수 조회하기

 

- C언어 강좌를 수강한 학생들의 성적 조회 / 학번, 성적, 학점 출력하기

---

------------- 둘 중 데이터 중 하나의 데이터를 확실하게 알 수 없는 상황에서 사용가능

- 점수가 80점 미만인 학생의 학과 출력 (학과명, 교수)

 

- 강의 시작시간이 1 또는 3인 강의를 수강한 학생들의 점수는? (강좌번호, 점수 출력)

1. 어떤 테이블에 있는 컬럼을 쓸건지 지정해줘야함.

강좌번호 컬럼(강의 lecture / 등록 enrollment) 에 중복값이 존재하기 때문에 c_no의 유일값을 가지는 강좌테이블(course) 을 가지고와야함

 

- 신미영 학생의 지도교수는 누구이며 교수의 연구실 위치와 전화번호를 출력하세요

 

- 각 학과별 최고점수 구하기

- 교수별 강의수 구하기