적어도 한번 이상 장학금을 받은 학생의 학번을 출력하라

"

select stu_no

from fee 

where jang_total is not null;

"


1,500,000원 이상 장학금을 받은 학생의 학번과 이름을 출력하라

"

select distinct stu_no, jang_total

from fee 

where jang_total >= 1500000;"


"select distinct student.stu_no, student.stu_name, jang_total

from fee, student

where fee.stu_no = student.stu_no

and jang_total >= 1500000

group by student.stu_no "

성별이 남자가 아닌 학생의 학번과 이름을 출력하라

"select * 

from student 

where substring(id_num,8,1) = 2;"

1985년부터 1988년 사이에 출생한 학생의 학번과 이름, 출생년도를 출력하라

"select * 

from student 

where birth_year between 1985 and 1988;

"

적어도 한 번은 장학금을 받고 1985년 이후에 출생한 학생의 학번, 이름, 장학금 총액을 출력하라

"select distinct student.stu_no, student.stu_name, student.birth_year, jang_total

from fee, student

where fee.stu_no = student.stu_no

and jang_total is not null 

and birth_year > 1985

group by stu_no"

장학금 수령총액이 500,000원에서 2,000,000원 사이에 포함되는 각 학생의 학번을 출력하라

"

select * 

from fee 

where jang_total between 500000 and 2000000;

"

장학금 수령총액이 500,000원에서 2,000,000원 사이에 포함되지 않는 학생의 학번을 출력하라

"select * 

from fee 

where jang_total not between 500000 and 2000000;"

입학년도가 2002년부터 2006년까지 입학한 학생의 학번과 입학년도를 출력하라

"select stu_no, att_year

from attend

where att_year between 2002 and 2006"

출생년도가 1975 년부터 1987년 까지 태어난 학생의 학번과 이름, 출생년도를 출력하라

"

select stu_no, stu_name, birth_year 

from student 

where birth_year between 1975 and 1987

"

나이가 19세부터 23세 사이인 학생의 학번을 출력하라 (다시 확인)

서울과 평택에 거주하지 않는 학생의 번호, 이름, 우편번호를 출력하라.

"

select stu_no, stu_name 

from student 

where post_no not in (

select post_no 

from post 

where post_address like '%평택%' or post_address like '%서울%'

)

"

영문이름이 문자 Kim 로 시작하는 각 학생의 학번과 이름을 출력하라

"

select *

from student

where stu_ename like 'Kim%';

"

영문이름름이 13문자로 구성된 각 학생의 이름과 학번을 출력하라

"

select stu_no, stu_name

from student 

where length(stu_ename) = 13

"

영문이름이 13문자 이상으로 구성된 각 학생의 학번과 이름을 출력하라

"

select stu_no, stu_name ,length(stu_ename)

from student 

where length(stu_ename) >= 13

"

학생의 영문이름에서 앞에서 4번째 문자가 문자 o 인 각 학생의 학번과 이름을 출력하라(다시 확인)

적어도 한 번 이상 장학금을 지급 받은 학생의 학번과 이름을 출력하라

"select stu_no

from fee 

where jang_total is not null;"

1,000,000이상의 장학금을 적어도 한 번 이상 지급 받은 학생의 학번과 이름을 출력하라

"

select distinct student.stu_no, stu_name, jang_total

from fee, student 

where fee.stu_no = student.stu_no

and fee.jang_total >= 1000000 

group by student.stu_name

"

적어도 한 번 이상 장학금을 지급 받았고 동아리에 가입을 하지 않은 학생의 학번과 이름을 출력하라 (다시확인)

"select distinct student.stu_no, student.stu_name, jang_total

from fee, student, circle 

where fee.stu_no = student.stu_no 

and circle.stu_no = student.stu_no

and fee.jang_total >1

and student.stu_no not in 

(

select stu_no from circle

)

group by student.stu_name

"

남학생 중 나이가 가장 많은 학생의 학번과 이름 출생년도를 출력하라

"

select * 

from student 

where substring(id_num,8,1) = 1 

and birth_year = (select min(birth_year) from student)

"

적어도 한 번의 장학금을 받은 학생의 학번, 이름을 출력하라 (in 연산자를 사용하지 않고 작성)

select * from fee where jang_total > 1;

적어도 한 과과목이상 수강신청을 한 학생의 이름과 학번을 출력하라

"

select stu_no, stu_name from student where stu_no in (

select distinct stu_no from attend)

"

수강신청을 하였으나 보관성적에 존재하지 않은 학생의 학번과 수강년도, 학기, 수강과목을 출력하라. (다시확인..)

"select stu_no, stu_name 

from student 

where stu_no in (select stu_no from attend)

and stu_no not in (select stu_no from score)

"

동아아리에 소속되지 않은 학생의 학번과 이름을 출력하라.

"

select stu_no, stu_name from student where stu_no not in (select stu_no from circle)

"

'데이터 베이스 > mysql 문제' 카테고리의 다른 글

10 연습문제  (0) 2017.11.15
9 mysql 서브쿼리 부속질의  (0) 2017.11.14
8 where 절  (0) 2017.11.14
7 from 절에서 테이블 명세  (0) 2017.11.13
6 select 절  (0) 2017.11.13
5 mysql 함수 & 문제  (0) 2017.11.10

부속 질의어는 in 연산자 다음에 사용할 수도 있고, 관계 연산자 다음에도 사용할 수도 있다.

수강신청을을 한 학생의 학번과 이름을 출력하라.

"

select stu_no, stu_name 

from student 

where stu_no in (select stu_no from attend where att_div = 'Y');

"

"=" 연산자는 부속 질의어가 항상 정확히 하나의 값만을 반환할 때 유효하다

김유미 1983년생 보다 나이가 더 많은 각 학생의 학번과 이름, 주민등록번호를 출력하라.

"

select stu_no, stu_name, id_num 

from student

where birth_year  < (select birth_year from student where stu_name = '김유미')

"

부속질의어를 사용하는 3번째 방법은 all과 any 연산자를 사용하는 것이다.

이러한 연산자는 부속 질의어에서 in 연산자를 사용하는 것과 유사하다.

그리고 any 연산자 대신에 some 연산자를 사용할 수 있는데, any 연산자와 some 연산자는 동의어다.

"ALL 연산자의 사용

• ALL 연산자는 IN 연산자와는 달리 어떤 특정한 값이 아닌 범위로 비교연산을 처리한다.

• ALL 연산자는 서브쿼리에서 리턴되는 모든 값을 만족하면 조건이 성립된다.


가장 나이가 많은 학생의 학번, 이름, 출생년도를 출력하라

(단, 가장 나이가 많은 학생은 다른 학생의 출생년도보다 출생년도가 더 적거나 같은 출생년도를 가진 학생이다.)

"select stu_no, stu_name, birth_year 

from student 

where birth_year <= all (select birth_year from student);"

"select stu_no, stu_name, birth_year 

from student 

where birth_year in (select min(birth_year) from student)


"

부속 질의어의 중간 결과는 모든 학생의 생년으로 구성되어 있다.

select 명령문에서 sql은 각 학생의 생년월일이 중간 결과에 기록되어 있는 각 생년월일 보다  작거나 동일한가 보게 된다.

in 연산자를 사용하여 이러한 조건이 참인지 거짓인지 아니면 알 수 없음인지 명확하게 보여 줄 수 있으며, 또 all 연산자를 사용해서도 보여줄 수 있다.

"ANY 연산자의 사용

• ANY 연산자는 IN 연산자와 달리 어떤 특정한 값이 아닌 범위로 비교연산을 처리한다.

• ANY 연산자는 서브쿼리에서 리턴되는 어떠한 값이라도 만족을 하면 조건이 성립된다.

"

any 연산자는 all 연산자의 반대가 되는 연산자이다.

가장 나이가 많은 학생을 제외한 나머지 모든 학생의 학번, 이름, 주민등록번호를 출력하라.

"select stu_no, stu_name, birth_year 

from student 

where birth_year > any (select birth_year from student);"

이거는 왜 다를까...

"

select stu_no, stu_name, birth_year 

from student 

where birth_year not in (select max(birth_year) from student)

;"

학번 20001015인 학생이 등록한 등록금의 납부총액보다 더 많은 등록금을 낸 학생의 학번을 출력하라.

이때 20001015번은 결과에서 제외 한다.

음..

"

select distinct stu_no 

from fee 

where stu_no <> '20001015' 

and fee_pay > any 

(select fee_pay from fee where stu_no = '20001015')

"

"EXISTS 연산자의 사용

• 검색된 결과가 하나라도 존재하면 메인쿼리 조건절이 참이다.

• NOT EXISTS는 서브쿼리에서 검색된 결과가 하나도 존재하지 않으면 메인쿼리 조건절은 참이다.


등록을을 한 학생의 학번과 이름을 출력하라

"select stu_no, stu_name 

from  student 

where stu_no in 

(select stu_no from fee);

"

exists를 사용해서 출력

등록하지 않은 학생의 학번과 이름을 출력하라

"select stu_no, stu_name

from student

where exists 

(select * from fee where stu_no = student.stu_no)"

조건에서 열 명세 student.stu_no은 주 질의어의 명령문에서 사용했던 테이블을 참조하게 된다.

이와 같은 이유는 상호 관련된 부속 질의어를 호출하기 때문이다.

즉, 지정된 열 명세를 사용함으로써 부속 질의어와 주 질의어간에 관계성을 확립한다.

정확하게 이명령문의 의미는 무엇인가? sql은 student 테이블에 있는 모든 학생에 대하여 부속 질의어의 결과로서 행을 반환하는지 반환하지 않는지 결정한다.

다시말하면, where exists 의 결과가 있는지 조사한다는 것이다.

만약 fee 테이블이 학생과 관련된 동일한 학번인 행이 적어도 하나 이상 있다면 그 행은 조건을 만족하게 된다

부정조건

java 길라잡이 동아리에 가입한 학생의 학번과 이름을 출력하라.

select stu_no, stu_name from circle where cir_name = 'Java길라잡이';

java 길라잡이 동아리에 가입 하지 않은 학생의 학번과 이름을 출력하라.

select stu_no, stu_name from circle where cir_name <> 'Java길라잡이';

조건 앞에 not 연산자를 위치시킴으로써 부정 조건을 가지는 select 명령문을 만들 수 있다.

select stu_no, stu_name from circle where not cir_name =  'Java길라잡이';

등록테이블에서 장학코드가 11이 아닌 학생의 학번과 장학코드, 장학금 총액을 출력하라

select stu_no, jang_code, jang_total from fee where jang_code <> 11;

fee 테이블에서 장학코드가 11이거나 null 값을 가지고 있는 학생은 제외되었으므로 원하는 데이터가 출력되지 않은 것을 알 수가 있다.

등록테이블에서 장학코드가 11이 아닌 학생의 학번과 장학코드, 장학금 총액을 출력하라

select stu_no, jang_code, jang_total from fee where jang_code not in (select jang_code from fee where jang_code in (11));

등록테이블에서 장학코드가 11이 아닌 학생의 학번과 장학코드, 장학금 총액을 출력하라

단 not in 이용하고 장학코드가 null 인 학생도 포함하여 출력하라.

"

select stu_no, jang_code, jang_total 

from fee 

where jang_code not in (select jang_code from fee where jang_code in (11)) 

or jang_code is null;"

'데이터 베이스 > mysql 문제' 카테고리의 다른 글

10 연습문제  (0) 2017.11.15
9 mysql 서브쿼리 부속질의  (0) 2017.11.14
8 where 절  (0) 2017.11.14
7 from 절에서 테이블 명세  (0) 2017.11.13
6 select 절  (0) 2017.11.13
5 mysql 함수 & 문제  (0) 2017.11.10

이번장의 내용

and, or, not 과 결합된 조건

between 연산자

in 연산자

like 연산자

null 연산자

부속 질의어와 함께 사용되는 in 연산자

부속 질의어와 함께 사용되는 관계 연산자

any와 all 연산자

exists 연산자

관계 연산자를 사용하는 조건

성별이 여자인 학생의 학번과 이름, 주민등록번호를 출력하라

"select * 

from student

where 2 = substring(id_num,8,1);"

야간인 학생들의 학번과 이름을 출력하라

"select * 

from student

where juya = '야';"


학번이 20071405인 학생의 학번과 이름을 나타내어라

"select stu_no, stu_name 

from student

where stu_no = '20071405';"

휴대폰을 가지고 있는 학생의 학번과 이름, 휴대폰 번호를 나타내어라

"

select stu_no, stu_name, phone_no

from student

where phone_no is not null;"

관계 연산에서 수식은 비교할 수 있는 데이터를 가지고 있어야 한다.

예를 들면, 수치 수식과 문자 수식은 비교하는 것은 언제나 허용되는 것은 아니다.

다음의 조건에서 값24는 자동적으로 문자수치 데이터로 변환되기 때문에 비교가 허용된다.

where stu_name = 24

그러나 다음의 조건에서 date 열은 날짜 자료형을 가지고 있지만 수치 24는 수치 값이므로 비교가 허용되지 않는다.

where date = 24

문자자 수치 값에서 어떤 영수치 값이 다른 문자 수치 값에 비하여 영문자의 ASCII 코드 값보다 더 적다면 다른 값 보다 적다고 할 수 있다.

Jim < pete TRUE

truck >= trek TRUE

jim = JIM FALSE

날짜 자료형에서 어떤 날짜 값이 다른 날짜 값보다 이전의 값이라면 더 적다고 할 수 있다.

1985/1208 < 1985/12/09 TRUE

1980/05/02 > 1979/12/31 TRUE

AND, OR, NOT을 이용한 다중 조건

1985년 이후에 출생한 여학생의 학번, 이름, 주민등록번호를 출력하라

"select stu_no, stu_name

from student

where 2 = substring(id_num,8,1)

and birth_year > 1985;

"


휴대폰번호가 016, 018, 019로 시작하는 휴대폰을 소지한 학생의 학번과 이름, 휴대전화번호를 나타내어라            

"select stu_no, stu_name, phone_no

from student 

where substring(phone_no, 1,3) in (016,018,019)

"

"select stu_no, stu_name, phone_no

from student 

where substring(phone_no, 1,3) = '016'

or substring(phone_no, 1,3) = '018'

or substring(phone_no, 1,3) = '019';

"


성별이 남자가 아닌 학생의 학번 이름을 나타내어라


select stu_no, stu_name

from student 

where 2 = substring(id_num, 8,1);



select stu_no, stu_name

from student 

where 1 <> substring(id_num, 8,1)


성별이 남자이거나 1988년에 출생한 학생의 학번, 이름, 주민번호를 나타내어라. 그러나 1988년도에 출생한 남학생은 제외 한다.

"

select stu_no, stu_name, id_num

from student 

where (substring(id_num,8,1) =1 or substring(id_num,1,2) = 88)

and not(substring(id_num,8,1) =1 and substring(id_num,1,2) = 88);


"


between 연산자

주어진 값의 범위에 어떤 값이 포함되어 있는 지를 결정하도록 하는 연산자

81년부터 87년 사이에 출생한 각 학생의 학번과 이름, 출생년도를 출력하라

select * 

from student 

where birth_year >= 1981 

and birth_year <= 1987;



select * 

from student 

where birth_year between 1981 and 1987

"


출생년도가 1981년부터 1987년 사이에 태어난 학생의 학번과 이름, 출생년도를 출력하라, 단 출생년도는 오름차순으로 출력하라

"select * 

from student 

where birth_year between 1981 and 1987

order by birth_year;

"

in 연산자

우편번호가 135-794, 150-051, 550-260에 해당되는 각 학생의 학번, 이름, 현주소의 우편번호를 출력하라

"

select stu_no, stu_name, post_no

from student 

where post_no = '135-794' 

or  post_no = '150-051'

or post_no = '550-260';

"

"

select stu_no, stu_name, post_no

from student 

where post_no in ( '135-794', '150-051', '550-260')

"

81, 83, 87 년에 출생한 각 학생의 학번과 주민등록번호를 출력하라

"

select stu_no, id_num 

from student 

where substring(id_num,1,2) in (81 ,83,87);

"

규칙

수식의 자료형을 비교할 수 있어야 한다.

통계 함수는 수식으로 사용할 수 없다.

like 연산자

영문이름이 문자 p로 시작하는 학생의 학번과 이름, 영문이름을 나타내어라

"

select * 

from student 

where stu_ename like 'P%';

"

영문이름의 끝에서 두 번째 문자가 u인 학생의 학번과 이름을 나타내어라

"select * 

from student 

where stu_ename like '%u_';

"

영문이름이 문자 K로 시작하지 않는 학생의 학번과 이름을 나타내어라

"select * 

from student 

where NOT (stu_ename like 'K%');"

null 연산자

null 연산자는 특정한 열이 값을 가지고 있지 않은 행을 선택할 때 사용한다


휴대폰을 가지고 있는 학생의 학번과 이름, 휴대폰 번호를 나타내어라

"

select * 

from student 

where phone_no is not null;

"


휴대폰을 가지고 있지 않은 학생의 학번과 이름, 휴대폰 번호가 null 인 경우에는 '휴대폰 없음' 나타내어라

"select stu_no, stu_name, ifnull(phone_no,""폰없음"") 

from student 

where phone_no is null;"

학생의 휴대폰 번호가  017이 아닌 모든 학생의 학번과 이름, 휴대폰 번호를 출력하라

단 , 휴대폰이 없는 학생도 포함되어 출력되어야 한다.

"select stu_no, stu_name, ifnull(phone_no,""폰없음"") 

from student 

where phone_no <> 017 

or phone_no is null;

"

부속질의어에서 in 연산

이러한 방법은 매우 조잡하다 또한 fee 테이블이 서로 다른 많은 학생의 학번을 가지고 있다면 적절하지 못하다

등록을 한 각 학생의 학번, 이름을 출력하라 (in 연산자 이용) 따라서 이러한 문제점을 적절히 다룰 수 있도록 sql은 명령문 내부에 select 명령문을 포함할 수 있는 기능을 제공한다.

"select stu_no, stu_name

from student 

where stu_no in (학번들~~~~)"


부속질의어를 이용하여 등록을 한 각 학생의 학번, 이름을 출력하라

"select stu_no, stu_name

from student 

where stu_no in (select distinct stu_no from student)"

적어도 한 번의 장학금을 받았던 학생의 학번과 이름을 출력하라

"select stu_no, stu_name

from student 

where stu_no in (

select distinct stu_no from fee 

where jang_total is not null

);

"

20061011 인 학생이 가입한 동아리를 제외한 다른 동아리에 적어도 한 번 가입을 한 학생의 학번과 이름을 출력하라.

"select stu_no, stu_name 

from circle 

where cir_name not in (select cir_name 

from circle 

where stu_no = 20061011);

"

휴대폰을 가지고 있는 학생을 출력하라 ( 단 , 휴대폰이 있어도 야간인 학생은 제외한다.)

"select stu_no, stu_name 

from student 

where phone_no is not null 

and juya not in ('야');

"

'데이터 베이스 > mysql 문제' 카테고리의 다른 글

10 연습문제  (0) 2017.11.15
9 mysql 서브쿼리 부속질의  (0) 2017.11.14
8 where 절  (0) 2017.11.14
7 from 절에서 테이블 명세  (0) 2017.11.13
6 select 절  (0) 2017.11.13
5 mysql 함수 & 문제  (0) 2017.11.10

from 절에서 테이블 명세

mysql은 테이블 명세를 두 부분으로 구성한다.

테이블 소유자의 이름 다음에는 테이블의 이름만 오게 된다.

즉, from절에서 다른 사람이 생성한 테이블을 참소 할 수 있다는 것을 의미하며, 이때 테이블 이름 앞에 소유자의 이름을 지정해야 한다.

하지만 사용자가 테이블의 소유자라면 소유자의 이름은 지정할 필요가 없다.

JJY가 생성한 STUDENT 테이블의 전체 내용을 KIM 이 보고자 할 때 적절한 SELECT 문을 완성하여라

SELECT * FROM JJY.STUDENT (소유자의 이름과 테이블 사이에는 마침표로 구분되어 있음)

JJY.STUDENT는 테이블 명세이다.

소유자를 지정하는 것은 가끔 테이블 이름의 자격으로써 참조 된다.

따라서 위의 예제에서 테이블 이름 STUDENT는 소유자의 이름은 JJY의 자격을 갖게 된다.

만약 JJY라는 사용자가 STUDENT 테이블의 내용을 보고자 한다면 위의 명령문을 사용할 수도 있지만 다음과 같이 소유자의 이름을 사용하지 않을 수도 있다.

SELECT * FROM STUDENT

-열 명세

SELECT 명령문에서 열의 이름 앞에 열이 포함되어 있는 테이블의 이름을 지정 할 수 있다.

이러한 것을 열의 자격이라 한다.

 학생의 학번을 나타내어라

SELECT STU_NO FROM STUDENT;

SELECT STUDENT.STU_NO FROM STUDENT;

다중중테이블 명세

ATTEND 테이블은 각 학생의 수강년도, 학기, 수강과목코드, 교수코드에 관한 정보는 가지고 있지만 이름에 대한 정보는 가지고 있지 않기 때문에 학생 신상 테이블인 STUDENT 테이블에서 가져와야 한다.

그러므로 2개의 테이블이 모두 필요하다.

따라서 FROM 절에 두 테이블 모두 지정해야 한다.

각 학생의 학번과 이름, 수강년도, 학기, 수강과목 코드, 교수 코드를 나타내어라

"SELECT student.stu_no, stu_name, att_year, att_term, sub_code, prof_code

FROM student, attend 

WHERE student.stu_no = attend.stu_no;

"

여기서 from 절까지만 실행했을 때 중간결과는 270개의 행이 나온다.

중간 결과 테이블에 있는 전체 열은 한 테이블의 전체 열과 다른 테이블의 전체열을 더한 것이며, 전체 행의 수는 한 테이블의 행의 수와 다른 테이블에 있는 행의 수를 곱한 것과 같다.

이와 같은 결과를 관련된 테이블의 카티션 프로덕트라고 한다.

from 절의 중간 결과 행의수 : a테이블의 행수 * b 테이블의 행수

from 절의 중간 결과 열의수: a테이블의 열수 + b 테이블의 열수

예를 들면 student 테이블의 행수가 15행이고, 열의 수가 13열, attend 테이블의 행수가 18이고, 열의 수가 11열 이면 행수는 270, 열 수는 24열이 된다.

앞의 예제 where 절에서 보는 것 처럼 stu_no 열 앞에 테이블 이름을 식별하는 것이 필요하다.

그렇치 않으면 SQL이 stu_no 열을 사용하는데 있어서 어떤 테이블의 stu_no를 사용할 것인지 명확히 알지 못한다.

결론적으로, from 절에서 두 개 이상의 테이블이 지정될 때, 각 테이블에서 사용하고 있는 열의 이름이 같은 이름으로 중복되어 사용되는 경우에는 테이블 명세.열의 명세 의 형식으로 사용해야 한다.

학생들의 학번, 이름, 수강신청구분을 나타내어라. 단. 수강신청구분은 attend 테이블에 있다.

"select student.stu_no, student.stu_name, attend.att_div from 

student, attend 

where student.stu_no = attend.stu_no"

가명

from 절에 여러개의 테이블 명세가 사용되는 경우에 가명을 사용하는 것이 더 편리할 때가 있다.

가명은 테이블의 임시 대체 이름이다.

앞의 예제에서 열에 접근권한을 줄 때 전체 테이블의 이름을 지정해야만 한다.

이 때 테이블의 이름을 사용하는 대신에 가명을 사용할 수가 있는데, 가명은 from 절에서 선언한다.

학생들의 학번, 이름, 수강신청구분을 나타내어라. 단, 학적 테이블의 가명을 S, 수강 테이블의 가명은 A로 정의한다.


from 절의 다양한 예제

수강테이블의 학번과 이름, 수강과목코드, 교수코드, 교수명을 나타내어라

"select s.stu_no, s.stu_name, a.sub_code, p.prof_name from 

student s, attend a, professor p 

where s.stu_no = a.stu_no 

and a.prof_code = p.prof_code

"

학적테이블의 학번과 이름, 수강테이블의 수강과목코드, 교과목테이블의 교과목명을 나타내어라

select s.stu_no, s.stu_name, a.sub_code, sb.sub_name

from student s, attend a, subject sb 

where s.stu_no = a.stu_no 

and a.sub_code = sb.sub_code;

학적테이블의 학번과 이름, 보관성적테이블의 성적 취득년도, 학기, 신청학점, 취득학점, 평점평균을 나타내어라.

"select s.stu_no, s.stu_name, a.sub_code, sb.sub_name

from student s, attend a, subject sb 

where s.stu_no = a.stu_no 

and a.sub_code = sb.sub_code;


학적테이블의 학번과 이름, 수강테이블의 수강신청년도, 학기, 수강신청유무를 나타내어라

select S.stu_no, S.stu_name, A.att_div from 

student S, attend A 

where S.stu_no = A.stu_no

최종 결과를 보면 중복된 데이터가 많이 존재한다는 것을 확인 할 수 있다.

그러면 중복된 값을 자동으로 제거할 수 있는 명령어가 select 다음에 바로 distinct 라는 단어를 사용해야 한다.

학적테이블의 학번과 이름, 수강테이블의 수강신청년도, 학기, 수강신청유무를 나타내어라 (중복은 배재한다.)

"select distinct S.stu_no, S.stu_name,A.att_div from 

student S, attend A 

where S.stu_no = A.stu_no

;"

적어도 한번 이상 장학금을 받은 학생의 이름을 구하라

"select  s.stu_name

from student s, fee f

where s.stu_no = f.stu_no

and f.jang_total is not null;"

"select distinct s.stu_name

from student s, fee f

where s.stu_no = f.stu_no

and f.jang_total is not null;"

등록한 학생의 이름, 등록년도, 학기, 장학코드, 장학금총액, 등록구분을 나타내어라

"select s.stu_name, f.fee_year, f.jang_code, f.jang_total, f.fee_div

from student s, fee f 

where s.stu_no = f.stu_no

and f.fee_div = 'Y'"

2007년에 등록한 학생의 학번과 이름을 나타내어라

"select distinct s.stu_no, s.stu_name

from student s, fee f 

where s.stu_no = f.stu_no

and f.fee_year = '2007';"

반드시 가명을 사용해야 하는 경우

반드시 가명을 사용해야 하는 경우가 있다.

이러한 상황은 from 절에서 동일한 테이블 이름이 한번 이상 사용될 때 발생한다.

예)

박정인 학생보다 나이가 더 많은 학생의 이름과 생년을 나타내어라.

"select s.stu_name, s.birth_year

from student s, student st 

where st.stu_name = '박정인'

and s.birth_year < st.birth_year;

"

열을 유일하게 식별하기 위해서 열의 이름 앞에 테이블 이름을 지정했다.

다시말하면 from 절에 동일한 이름을 가지는 두개의 테이블을 참조하면 반드시 가명이 사용되어야 한다.

만약 박정인의 출생년도를 미리 알고 있다면 가명을 사용하지 않아도 되지만 이를 알 수 없기 때문에 가명을 사용한다

만약 select 절에 s.birth_year 대신에 st.birth_year를 사용하면 결과는 어떻게 될가?

김유신, 박도준, 이상길, 연개소문 이름이 전부 박정인으로 출력되고, 출생년도가 박정인의 출생년도인 1983으로 출력되 ㄹ것이다.

그래서 select 절의 가명을 올바르게 기입해 주어야 한다.

'데이터 베이스 > mysql 문제' 카테고리의 다른 글

9 mysql 서브쿼리 부속질의  (0) 2017.11.14
8 where 절  (0) 2017.11.14
7 from 절에서 테이블 명세  (0) 2017.11.13
6 select 절  (0) 2017.11.13
5 mysql 함수 & 문제  (0) 2017.11.10
4 mysql 테이블 생성  (0) 2017.11.09

select 명령문은 많은 절로 구성되어 있다.

각 select  명령문은 select 와 from 이라는 절을 가지기 때문에 적어도 2개의 절을 가지고 있다.

그리고 wehre , grop by , order by 같은 절은 선택적으로 사용된다.

절의 순서는 고정되어 있다. 예를 들면 grop by 절은 where 또는 from절 앞에 올 수 없다.

그리고 order by 절이 사용된다면 이 절은 항상 나중에 사용된다.

having절은 grop by 절이 사용되어야 만이 사용할 수 있다.

- select 명령문 모든 절을 포함한 수행과정

sql이 select 명령문을 수행할 때되는 단계를 설명하고 있다.

등록 테이블 fee 에서 장학금을 지급 받은 학생의 학번과 장학금 내역을 출력하라

select stu_no, jang_total 

from fee 

where jang_total > 0 ;"

이 예제에서는 from 절을 먼저 수행하면 등록 테이블에 있는 전체 행의 복사본을 만들어 내고, 3번의 where 절에서 조건에 맞는 행을 선택  후 열의 값을 찾아 내어 출력한다.

등록 테이블에서 장학금을 1,000,000 이상 지급 받은 학생 중에서 2회 이상 지급받은 학생의 학번과, 지급받은 횟수를 학번 내림차순으로 출력하라.

"select stu_no, count(jang_total) 

from fee 

where jang_total > 1000000

group by stu_no 

having count(*) > 1 

order by stu_no desc;

"

각절이 수행되는 순서는 from 절 where 절 group by 절 having 절 select 절 order by 절 순으로 처리가 진행된다.

각 절이 수행되면 0 또는 그 이상의 행과 1 또는 그 이상의 열로 구성된 하나의 결과 테이블(임시 테이블)이 생성된다.

첫 번째 절을 제외한 모든 절은 0 또는 그 이상의 행과 1 또는 그 이상의 열로 구성된 테이블을 가진다는 것이다.

첫 번째 절은 데이터베이스로 부터 데이터를 찾아내고, from 절은 데이터 베이스로 부터 하나 이상의 테이블을 입력으로 취한다.

1 from  절

from 절에서 fee 테이블만 사용되었다.

sql은 이 테이블에서만 작업을 수행한다는 것이다.

이 절의 중간 결과 테이블은 fee 테이블에서 학번, 등록년도, 학기, 장학코드, 장학금액에 대한 정확한 자료는 다음과 같다

2.where 절

조건으로 jang_total > 1000000 이라는 where 절이 사용된다면 jang_total 열에 있는 값이  1000000원 이상이 되는 모든 행은 조건을 만족하게 된다.

3group by 절

group by 절은 그룹별로 검색을 할 때 사용된다. group by 절을 사용할 때는 그룹함수를 같이 사용해야 한다.

대표적인 그룹 함수는 count, avg, max min sum이 있다.

위 결과에서 학번으로 그룹을 만들면

select stu_no, count(*) from fee where jang_total > 1000000 group by stu_no;

이 예제에서는 동일한 학번을 가진 행을 하나의 그룹으로 묶고 동일한 행의 개수를 count(*) 열에 출력하는 예제다.

즉, fee 테이블에는 장학금은 1,000,000원 이상 받은 학생중 stu_no로 그룹을 만들어 각 학생별로 1,000,000 이상 장학금을 받은 횟수를 출력하는 예제 이다.

이와 같이 group by 절을 사용하여 하나의 테이블을 그룹으로 분류하여 데이터를 처리할 수 있다.

4.having 절

having 절과 where절을 서로 비교할 수 있는데, 그 차이점은 where 절은 from 절에서 생성된 중간 테이블에서 동작하고, having 절은 group by 절에서 생성된 중간 테이블에서 동작한다.

사실 수행된 과정은 동일하다.

having 절을 포함 시켰을 때 sql은 조건을 참조하여 행을 선택한다.

이러한 경우 조건은 다음과 같다

count(*) > 1

5 select 절

select 절은 최종 결과 테이블에 표현될 열을 지정하기 위해서 사용된다.

다시말하면 select 절은 열을 선택하는 것이다.

2장에서 설명한 학사 관리 예제의 fee 테이블은 stu_no, fee_year, fee_term, fee_enter 등 11개의 열로 구성된다.

따라서 select 절을 이용하여 fee 테이블 중에서 stu_no와 fee_year 만 선택하여 출력할 수 있다.

select stu_no, fee_year from fee

select 절은 다른 절을 이용해 만들어진 중간 결과 테이블에서 사용자가 보고 싶은 절을 선택하여 출력할 수 있다.

아래 예제는 fee 테이블에서 장학금을 1,000,000원 이상 받은 학생의 stu_no, fee_year, fee_term을 출력하는 예제다

select stu_no, fee_yaer, fee_term from fee where jang_total > 1000000;

6 order by 절

이 절은 마지막으로 수행되는 절로 중간 결과 테이블의 내용에 영향을 주지 않는다.

마지막까지 선택된 행을 정렬한다.

따라서 stu_no 열에 있는 데이터는 order by 절에 의해서 정렬된다.

select 절에서 나온 결과 값은 입력된 데이터 순으로 출력되었으므로 정렬을 원하면 order by 절을 사용한다.

결과 값은 학번 내림차순이므로 결과 값이 달라질 것이다.

'데이터 베이스 > mysql 문제' 카테고리의 다른 글

8 where 절  (0) 2017.11.14
7 from 절에서 테이블 명세  (0) 2017.11.13
6 select 절  (0) 2017.11.13
5 mysql 함수 & 문제  (0) 2017.11.10
4 mysql 테이블 생성  (0) 2017.11.09
3 mysql 데이터 타입 및 필수 기능  (0) 2017.11.09

1학년 신입생의 경우에만 입학금을 내고 재학생의 경우에는 입학금을 내지 않으므로 등록금 총액은 입학금 (fee_enter) + 수업료 (fee_price) 가 된다. 그러나 입학금이 null 인경우에는 가산을 할 수 없으므로

ifnull (fee_enter,0) 과 같이 ifnull 함수를 사용하여 null 값을 0으로 변환하여 가산을 할 수 있다.

등록금의 총액을 변경하여라

use haksa;

select * from fee;

update fee set fee_total = ifnull(fee_enter, 0) + fee_price;

납입금 총액은 등록금 총액 - 장학금 총액 이다. 납입금 총액을 변경하라

update fee set fee_pay = fee_total - ifnull(jang_total,0);

- 영수치함수

우편번호가 150- 051인 동이름과 지역전화번호를 출력하라

select post_dong, ddd from post where post_no = '150-051';

-숫자처리 함수

ROUND, TRUNCATE 함수

ROUND 함수는 숫자를 소수점 이하 자릿수에서 반올림 한다. 자릿수를 생략하면 소숫점이 5 이상일 때 반올림/자릿수를 지정하면 지정한 자리수에서 반올림한다.

TRUNCATE 함수

TRUNCATE 함수는 숫자를 소수점 이하 자릿수에서 버린다. n은 자릿수를 소숫점 이하로 정하며, 해당숫자가 자릿수보다 소숫점이 모자랄 경우 0값으로 대치한다.

만일 자릿수를 소숫점 이전으로 정하면 소숫점 이하는 버리고, n의 자릿수만큼 0 값으로 처리된다.

TRUNCATE 함수는 반드시 자릿수를 명시해 주어야 한다.

select round(123456.789,2);

소수점 아래 두 번째 자리에서 반올림한 결과이다


select truncate(123456.789,2);

소수점 아래 두번째 자리에서 삭제한 결과이다.

select round(12345678.901,-3);

소수점을 기준으로 왼쪽 3자리는 0으로 채워지고, 네번째 자리에 반올림한 결과다

select truncate(12345678.901,-3);

소수점 기준으로 왼쪽 3자리를 0으로 채워 절삭한 결과다


floor, ceil 함수

floor 함수는 위에서 다룬 trunc 함수와 유사하여  소수점 아래의 수를 무조건 절삭하여 정수 값을 반환하고, ceil 함수는 소수점 아래의 수는 무시하고 무조건 올림을 하여 정수를 반환한다.

두 함수는 뒤의 인수에 대해 무조건 정수를 반환 하므로 두번째 인수는 쓰지 않는다.

select floor(123456.789), ceil(123456.123);

mod 함수 :  첫번째 인수를 두번째 인수로 나누어 나머지를 반환한다.

select mod(10,3) , mod(5.8)

두번째 인수가 첫 번째 인수보다 크거나 0일 경우 결과 값은 첫번째 인수를 반환한다.

일반 수식 계산은 0으로 나눌 경우 에러가 출력되지만, mod 함수에서는 두 번째 인수가 0이라도 에러를 출력하지 않는다.

abs 함수 절대값을 구하는 식이다.

select abs(124),  abs(-124)

pow 함수 또는 power 함수

제곱의 값을 구하는 함수이며 소숫점이 있는 경우에도 실행이 된다.

select pow(2,4), pow(-2.5 ,2), pow(1.5, 6)

첫번째는 2의 4승을 구한 값이고, 두번째는 소숫점을 포함한 음수지만 양수로 처리되어 나오며, 세번째는 소수점을 포함한 1.5의 6승 값을 구한 것이다.

greatest, least 함수 가장 큰 값과 가장 작은 값을 구하는 함수

select greatest(1,2,3,4,5,6), least(1,2,3,4,5,6);

문제: 재학생들의 등록금액에 비례한 장학금의 비율은 몇 % 인가?

select fee_total ""등록금액"", jang_total ""장학금액"", ifnull(jang_total,0) / fee_total* 100 ""비율"" 

from fee;

select fee_total ""등록금액"", jang_total ""장학금액"", round(ifnull(jang_total,0) / fee_total* 100,2) ""비율"" 

from fee;"

스칼라 함수

스칼라 함수는 연산을 수행하기 위해서 사용된다.

스칼라 함수는 전달 인수를 사용하지 않거나 하나 이상의 전달 인수를 사용한다.

문자 처리 함수는 문자열 조작에 관한 함수들이 있다.

concat 함수는 두 문자열을 연결시켜 합쳐주고

substring 함수는 지정된 위치에서 지정된 길이만큼의 문자열을 추출한다.

length 함수는 문자열의 길이를 정수 값으로 반환한다.

instr 함수는 문자열에서 특정 문자의 위치를 반환하고 lpad 함수는 왼쪽에 지정된 문자를 지정된 길이 만큼 채워주고, rpad 함수는 오른쪽에 지정된 문자를 지정된 길이만큼 채워준다.

문자 처리 함수의 또 한 종류는 대소문자를 변화해주는 문자열 조작에 관한 함수들이 있다.

lower 함수는 문자열을 모두 소문자로 바꾸어 주고, upper 함수는 문자열을 모두 대문자로 바꾸어준다. intcap 함수는 문자열에 속한 각 단어별로 첫 글자를 대문자로, 나머지 부분은 소문자로 바꾸어 준다.

select concat('data','base');

select substring('korea',1,3);

select length('my life');

select instr('korea','e');

select LPAD('KOREA',15,'*');

select RPAD('KOREA',20,'*');

select lower('Korea');

select upper('Korea');

문제-학번이 20001001인 학생의 학번, 이름, 영문이름을 출력하라

- 단 영문이름은 대문자로 출력하라

"select * from student where stu_no = 20001001;



select stu_no, stu_name, upper(stu_ename) from student where stu_no = 20001001;


"

문제- 2학년 학생의 번호와 이름, 영문이름, 그리고 영문이름의 길이를 나타내어라

"

select stu_no, stu_ename, stu_name, length(stu_ename) '길이' from student where grade = 2;


"

select stu_no, stu_ename, stu_name, length(rtrim(stu_ename)) '길이' from student where grade = 2;

문제 - 영문이름의 길이가 정확히 12자인 각 학생의 번호와 영문이름을 출력하라

"

select stu_no, stu_name from student where length(rtrim(stu_ename)) = 12;

"

현주소의 우편번호가 550으로 시작하는 전남여수시에 거주하는 학생의 학번과 이름, 우변번호를 나타내어라

select student.stu_no, student.stu_name, student.post_no, post.post_address

from student, post

where student.post_no = post.post_no

and substring(post.post_no,1,3) = 550;

학번이 20001021 인 학생의 학번과 이름, 우편번호, 주소를 출력하라.

단, 주소출력에는 공백부분을 삭제하여 출력하고 concat 함수를 이용한다.

"

select student.stu_no, student.stu_name, student.post_no, concat(rtrim(post.post_address), rtrim(student.address))  

from student, post

where student.post_no = post.post_no 

and student.stu_no = 20001021;"

날짜및 시간처리

now() , sysdate() 현재 날짜와 시간을 반환

curdate() , current_date(); 현재 날짜를 반환 한다.

curtime(), current_time() 현재 시간을 반환 한다.

dayofmonth(date) 몇일인지를 리턴한다.

dayofweek(date), weekday(date) 숫자로 요일을 리턴 한다.

select now() , sysdate(); --  현재 날짜와 시간을 반환

select curdate() , current_date(); -- 현재 날짜를 반환 한다.

select curtime(), current_time()  -- 현재 시간을 반환 한다.

select dayofmonth(now());

select dayofweek(now()), weekday(now())  -- 숫자로 요일을 리턴 한다.

select dayofmonth(now()) , dayofweek(now());

select dayofyear(now()); 1년중 몇일이 지났는지 리턴 한다.

date_add date_sub

date_add 함수는 날짜에서 기준값 만큼 더한 값이고, date_sub 함수는 날짜에서 기준값 만큼 뺀값을 나타 낸다.

기준값( year, month, day, hour, minute, second

"select date_add(now(), interval 3 day), date_sub(now(), interval 3 day);

"

year, month 함수

year 함수는 날짜의 연도를 출력하면 month는 날짜의 월을 출력한다.

"select year(now()), month(now());

"

date_format(날짜, '형식')

문제 - 교수테이블에서 교수코드, 교수이름, 임용일자를 년도(4자리), 월(영문) , 일(0이 포함된 날짜) 형식으로 출력하라.


select prof_code, prof_name,  date_format(Create_date,'%Y %M %d') from professor;

문제

- 영문이름의 길이가 정확히 11자인 학생의 학번과 영문이름을 출력하라

select stu_no, stu_ename from student where length(stu_ename)='11';

등록일자가 2006년 2월 18 일인 학생의 학번과 등록년도, 학기, 등록일자를 출력하라

select stu_no, fee_year, fee_date 

from fee 

where fee_date = '2006-02-18';

학적 테이블에서 학번과 이름, 주민등록번호를 출력하라 . 단 여학생은 제외시키고

"select stu_no, stu_name, id_num 

from student 

where substring(id_num,8,1) = 1; 

"

'데이터 베이스 > mysql 문제' 카테고리의 다른 글

7 from 절에서 테이블 명세  (0) 2017.11.13
6 select 절  (0) 2017.11.13
5 mysql 함수 & 문제  (0) 2017.11.10
4 mysql 테이블 생성  (0) 2017.11.09
3 mysql 데이터 타입 및 필수 기능  (0) 2017.11.09
2 학사관리 예제 설명  (0) 2017.11.08

create table 테이블 생성

alter table 테이블과 열 변경

drop table 테이블 삭제

rename 테이블 이름 변경

새로운 테이블의 생성

#우편번호테이블

create table post(

post_no varchar(7) Not null, #우편번호

post_dong char(30) Not null, #동이름

post_address char(60) Not null, #주소

ddd char(4), #DDD 전화지역번호

primary key (post_no)

)engine = innoDB;


다음은 학사 데이터 베이스에서 post 테이블을 생성하는 create table 명령문의 예제를 보여주고 있다.

테이블의 이름은 post이며, 이 이름은 학사 데이터베이스내에 포함되어 있는 모든 테이블에 대하여 유일한 이름이어야 한다.

그리고 create table 명령어를 입력한 사용자가 자동적으로 테이블의 소유자가 된다.

테이블의 테이블 스키마는 열의 정의와 몇개의 키에 대한 무결성 규칙을 가지고 있다.

열의 정의는 열의 이름과 자료형을 가지고 있으며, not null 과 가능하다면 대체 키를 지정해도 된다.

열의 자료형을 지정하는 것은 필수적인 것이다.

자료형에 의해서 열에 어떤 값이 입력되는 가를 나타낼 수 있다.

다시 말하면 열의 자료형은 입력할 수 있는 값을 제한 하는 것이다.

각 열에는 not null 무결성 규칙이 지정되어야 한다.

null 값은 값이 알려지지 않다. 또는 값이 존재하지 않다로 비유할 수 있다.

이 수가 0인 경우와 또 공백인 경우와 혼동해서는 안된다.

그래서 반드시 하나의 값을 가져야 한다.

기본키키는 값이 항상 유일한 열 또는 열의 집합이다

따라서 기본키에는 null 값이 허용되지 않는다.

위에서는 post_no 열이 post 테이블의 기본 키이다.

기본키로 정의된 열은 not null로 정의 되어야 한다.

그리고 마지막 열에 있는 열의 정의와 primary key 단어 사이에는 구분을 위한 콤마를 사용한다.

과목명, 과목 코드, 과목인원, 과목 개설 일자를 기록하기 위한 diplomas 테이블을 생성하라. course, student, end_date 열을 사용하여 조합 기본 키를 생성한다.

"CREATE TABLE DIPLOMAS

(COURSE VARCHAR(20) NOT NULL,

STUDENT VARCHAR(10) NOT NULL,

COU_NUM INT(2),

END_DATE DATE NOT NULL,

PRIMARY KEY (COURSE, STUDENT, END_DATE));



insert into diplomas values('웹프로그래밍','공자',2,'2007/07/25');

insert into diplomas values('웹프로그래밍','맹자',3,'2007/07/25');"

3개의 열을 사용하여 기본 키를 정의함으로써 학생은 지정된 일자에 한 과목에 대하여 취득할 수 있다.

기본키를 정의할 때 규칙

각 테이블에는 오직 하나의 기본키만 정의 할 수 있다.

관계형 모델에서는 각 테이블에 하나의 기본키를 정의하도록 하고 있다.

그러나 sql에서는 이러한 것을 따르도록 하지 않기 때문에 기본키 없이 테이블을 생성 할 수 있다.

그러나 각 기본 테이블에 대하여 하나의 기본 키를 지정하는 것이 바람직하다.

이와 같은 이유는 기본 키로 테이블을 갱신할 때 유일성을 조사하기 위해서다

또 기본 키를 사용하지 않는 이유는 테이블에 동일한 데이터를 갖는 행을 저장할 수 있도록 하기 위해서이다.

이렇게 할 경우 문제점이 발생한다.

그 이유는 2개의 행을 서로 구분할 수 없으므로 select 명령문에서 동일한 조건을 만족하게 되며, update 명령문에서 2개의 행은 함께 갱신되기 때문에 문제가 발생한다.

이러한 상황에서는 데이터가 잘못될 가능성이 높다.

테이블에서 서로 다른 행은 동일한 값을 기본 키로 가질 수 없다.

-테이블 명세와 소유자

많은 sql 명령문에서 테이블 명세는 접근할 테이블을 구별하기 위해서 사용된다.

예를 들면 다음의 select 명령문에서 키워드 from 다음에는 접근하고자하는 테이블을 기술한다.

여기서 student는 테이블 명세이다.

select * from student

즉, 테이블을 구분짓는 유일한 이름이다.

mysql에서 테이블의 이름은 데이터베이스 내에서 유일하다.

그래서 다른 사람이 소유자인 테이블을 접근하고자 할 때도 아무런 문제가 발생하지 않는다.

테이블 명세는 바로 테이블 이름으로 구성되어 있다.

그러나 많은 sql 제품에서는 또 다른 규칙을 적용하는데, 즉 테이블 이름은 사용자에게만 유일하다는 것이다.

따라서 2명의 사용자는 서로 동일한 이름을 가지는 테이블을 생성할 수 있다.

이러한 제품에서는 테이블을 접근하기 위한 명령문을 입력한 사용자가 student 테이블의 소유자라고 가정하고 있다.

그래서 다른 사용자가 소유자인 테이블을 접근하려면 테이블 명세는 소유자의 이름을 포함할 수 있도록 확장해야 한다.

다음의 select 명령문에서는 jjy 라는 사용자가 소유인 student 테이블에 접근하는 질의어를 보여주고 있다.

select * from jjy.student

그래서 소유자의 이름없이 테이블을 지정하면 sql은 사용자가 자신이 소유자인 테이블을 접근한다고 가정한다.

- 테이블과 열의 이름

하나의 스키마는 2개의 테이블을 동일한 이름으로 가질 수 없다.

하나의 테이블에서 2개의 열이 동일한 이름을 가질 수 없다.

- 테이블 삭제

drop table 명령문은 테이블을 삭제하기 위해서 사용된다.

테이블이 삭제되면 sql은 모든 무결성 규칙에 따라 카탈로그 테이블로부터 테이블의 명세표, 인덱스, 데이터 값 그리고 테이블에 부여된 권한을 제거한다.

diplomas 테이블을 삭제하여라

drop table diplomas;

- 테이블의 구조 변경 (열의 추가)

update , insert, delete 명령문은 테이블의 내용을 변경하기 위해서 사용된다.

또한 sql은 테이블의 구조를 변경할 수 있는 기능을 제공한다.

이는 다음과 같은 alert table 명령문을 사용한다.

diplomas 테이블에 성별 sex char(2)로 열을 추가하라

alert table diplomas add sex char(2) ;

그러면 diplomas 테이블은 sex라는 열을 char(2) 데이터형의 크기로 추가되고 sql에서 열에 값을 채울 때 유일하게 가능한 값은 null 이다.

확인

select * from diplomas

-테이블 구조의 변경

어떤 제품에서는 alert table 명령문의 기능으로 열을 추가하는데 제약사항을 두지 않고 있다.

즉, 이 명령으로 다음과 같은 몇 개의 서로 다른 기능을 수행할 수 있다.

테이블에 새로운 열을 추가한다

기존에 존재하는 열을 삭제한다.

자료형의 길이를 변경한다.

기존에 존재하는 열의 자료형을 특별한 조건하에서 변경할 수 있다.

기본 키와 같은 새로운 무결성 규칙을 추가할 수 있다.

기존에 존재하는 무결성 규칙을 삭제할 수 있다.

diplomas 테이블의 sex 열의 길이를 2에서 4로 증가 시켜라

alert table diplomas modify sex char(4);

열에 있는 모든 값이 새로운 자료형의 규칙을 만족한다면 열은 새로운 자료형을 받아들인다.

diplomas 테이블에서 sex의 자료형을 char에서 int로 변경해라

alert table diplomas modify sex int;

테이블에서 기본키가 정의되어있지 않을 때 기본키를 추가할 수 있다.

alter table diplomas add primary key (student);

-테이블 이름 변경

테이블뿐만 아니라 뷰, 시퀀스 등의 mysql 객체의 이름을 바꿀 수 있다.

post 테이블을 test_post 테이블명으로 변경하라

alert table post rename test_post;

'데이터 베이스 > mysql 문제' 카테고리의 다른 글

6 select 절  (0) 2017.11.13
5 mysql 함수 & 문제  (0) 2017.11.10
4 mysql 테이블 생성  (0) 2017.11.09
3 mysql 데이터 타입 및 필수 기능  (0) 2017.11.09
2 학사관리 예제 설명  (0) 2017.11.08
1 mysql 데이터 생성 삽입  (0) 2017.11.08

앞에서 설명한 학사 관리 데이터 베이스를 실제 mysql 상에서 구현하고 이에 필요한 필수적인 기능들에 대해 설명한다.

먼저 mysql을 이용하여 데이터 베이스를 생성하기 위해서는 mysql에 데이터 베이스를 생성해야 한다.

하나의 mysql 에는 여러개의 데이터 베이스가 존재할 수 있으며, 각각의 데이터 베이스 이름은 사용자가 지정할 수 있다.

데이터 베이스 생성

create database haksa;

확인

show databases;

db변경

use haksa;

인사 테이블 insa 생성 및 데이터 입력

create table insa(bunho int(1) auto_increment, name char(8) not null, e_name char(4) not null, town char(6) not null, primary key(bunho));

데이터 삽입

insert into insa values('1','홍길동','Hong', '순천');


insert into insa values('2','제갈공명','Je', '여수');


insert into insa values('3','순자','Soon', '부산');


insert into insa values('4','이순신','Lee', '여수');

확인

select * from insa;

commit/rollback 작업

commit : 변경된 데이터를 데이터베이스에 적용시킨다.

rollback: 변경된 데이터를 취소 시킨다. 직전에 Commit 이 수행된 지점까지 취소시킨다.

주의 사항으로 mysql은 명령어를 실행하면 자동으로 Commit을 하게 되어 있다.

우선 AutoCommit을 하지 않도록 한다.

set autocommit = 0;

insa 테이블의 내용 변경 : 번호 4번도시를 한산도로 변경

update insa set town = '한산도' where bunho = 4;

변경된 인사테이블 질의

"select * from insa;

"

변경된 데이터 복구 작업 : rollback

rollback;

확인

select * from insa;

타운이 여수인 데이터를 '대구'로 변경

update insa set town = '대구' where town = '여수';

인사테이블 내용 데이터베이스에 저장

commit;

데이터 복구해보기

rollback

복구가 안됨

savepoint/truncate 작업

savepoint는 변경된 지점의 위치를 저장한다.

savepoint로 저장점을 저장하고 insert, delete, update 작업을 수행 후 rollback to 저장점을 수행하면 그 위치까지 다시 복구 시쿨 수 있다.

번호 2의 도시 town 을 '' 여수로 변경

update insa set town='여수' where bunho = 2;

세입이브 포인트 지정

savepoint aa;

번호 3번행 삭제 delete 작업

delete from insa where bunho = 3;

insa 테이블 savepoint aa 까지 복구

rollback to aa;

질의

select * from insa;

truncate 작업 : insa 테이블의 삭제 처리 ( 모든 행이 삭제 처리됨)

truncate table insa;

rollback

복구되지 않음~!

sql 데이터 형

sql 에서 사용되는 데이터 타입을 설명한다. 데이터베이스의 목표는 데이터를 효율적으로 관리하고 이용할 수 있도록 하는 것이므로 데이터 베이스를 이해하는 데 있어 데이터 타입의 이해는 반드시 필요하다.

1 숫자 데이터 형

정수 데이터 형 (int)

mysql 은 정수형 데이터를 저장하기 위해서 int라는 데이터 형을 제공한다.

0, 음수, 양수를 저장한다.

실수형 데이터 float

소수점을 포함하여 값을 저장할 수 있다.

2 문자 데이터 char, varchar blob

char 데이터 형

1바이트에서 255바이트까지의 고정 길이 문자열을 저장하고, 정의된 저장공간보다 입력데이터가 짧으면 나머지 공간은 공백으로 채워지고, 정의된 길이 보다 입력 데이터가 길면 길이에 맞게 잘린 데이터가 입력 된다.

그러므로 테이블 생성시 저장할 데이터의 최대 크기로 정의해야만 데이터 손실을 막을 수 있다.

varchar 데이터형

char와 비슷하지만  char 는 정의된 저장공간 보다 긴 문자열이 입력 되면 에러를 발생시키지 않고 초과되는 데이터를 잘라서 입력하지만,  varchar에서는 에러 값을 리턴한다.

최대로 정의할 수 있는 데이터 길이는 255바이트까지 저장할 수 있고, 메모 등의 다양한 길이의 데이터에 적절하고, 가변적인 길이의 문자열을 저장하기 때문에, 문자열을 저장하기 위하여 선호되는 데이터형이다.

하지만 자료구조의 원리로 볼때 char가 varchar보다 검색 속도가 훨씬 빠르다.

blob , text  데이터형

blob과 text는 65,535 이상의 거대한 텍스트 데이터를 저장할 때 사용하면 된다. 다면 blob은 검색시 대소문자를 구분하고 , text는 대소문자의 구분 없이 검색할 수 있다.

날짜 데이터형

mysql은 날짜 및 시간 데이터를 저장하기 위해서, date 데이터 형을 제공한다.

사용자들은 sysdate라는 함수를 사용해서 현재 os날짜를 조회 할 수 있다.

예) select now();

select 는 산술 계산의 결과나 날짜 등을 볼 수 있다

위의 질의 결과로 얻은 날자는 보통 우리 나라 사람들이 사용하는 년 월 일 형식으로 나타난다. 

date 데이터 형에는 세기 , 년  월, 일 시간 분 초에 대한 정보가 포함되어 있다.

바이너리

음성 , 화상이미지, 동영상과 같은 데이터를 저장하기 위해서 바이너리 데이터형으로 raw와 long raw 데이터형을 사용하고 제약점으로는 내장함수를 사용할 수 없다.

raw 데이터형 데이터를 255바이트 까지 수용할 수 있으나 저장공간의 제한점 때문에 많이 사용하지 않는다.

long raw 데이터형~

2진형 데이터를 2gb 까지 수용할 수 있다.

blob 데이터형

이진형 데이터를 4gb까지 수용할 수 있다.

학사관리 예제 만들기~

질의 테이블

1.성별이 남자인 각 학생의 학번, 이름, 영문이름, 학년, 주민등록번호를 영문이름 순서로 출력하라

select stu_no, stu_name, stu_ename, grade, id_num from student

where SUBSTRING(id_num,8,1) =1

order by stu_ename;

성별이 남자인 학생을 찾는 조건은 where substring(칼럼, 시작위치, 몇번째)  = 1 이며, 이 함수는 id_num 의 8번째 칼럼 1자리 만큼을 선택하기 위한 함수다.

각학생의 학번, 이름, 영문이름, 학년, 주민등록번호 를 학적 테이블로 부터 구하는 명령문으로써, 정렬은 영문 이름의 순서 order by stu_ename 로 이루어진다.

from 다음에는 질의를 원하는 테이블 이름을 지정하며 where절 뒤에는 원하는 조건을 적으면 된다.

select 는 탐색하고자 하는 열을 선택할 수 있도록 하고, order by  다음에는 출력될 데이터가 정렬되는 방법을 지정한다.

2.학년이 1학년이고 성별이 남자인 각 학생의 학번과 이름을 출력하는데, 출력 순서는 학번 내림차순이다.

select stu_no, stu_name 

from student

where substring(id_num,8,1) = 1 

and grade = 1 

order by stu_no desc;

행의 갱신과 삭제

교과목 중 운영체제의 생성년도를 2006년도로 변경하라

update subject set create_year = '2006' where sub_name = '운영체제';

조회

select * from subject;

삭제처리 작업은 테이블의 내용을 제거할 때 행 단위로 이루어진다. 

만약에 sample 이라는 테이블의 모든 행을 삭제한다면 다음과 같다

delete from sample;

앞의 예제는 sample 테이블의 모든 행을 삭제처리 하나 테이블 자체가 없어진 것은 아니다.

테이블의 명세표, 인덱스, 부여된 권한 등 환경테이블 정보 자체를 없애버릴 때는 drop 명령어를 다음과 같이 사용한다.

drop table sample;

과목명이 uml 인과목을 삭제하라

delete from subject where sub_name = 'UML';

원래대로 변경

교과목 중 운영체제의 생성년도를 2002년으로 변경해라

테이블에는 데이터를 가지고 있는 행이 실제로 저장되어 있는데, 많은양의 기억 장소를 차지 하기 때문에 많은 테이블은 많은 기억 공간이 요구된다

반면 VIEW 테이블은 사용자가 여러 테이블에 있는 데이터를 보기 편한 새로운 테이블을 만들 수 있는 기능이다.

따라서 뷰 테이블은 데이터가 실제 저장되는 테이블이 아닌 사용자에게 보여주기 위한 뷰 전용 테이블이라고 할 수 있다.

뷰 테이블은 필요에 따라 사용자가 재 정의하여 생성할 수 있으며 어떤 기억공간을 차지 하지 않는다.

따라서 뷰는 가상 테이블이라고도 한다

뷰는 실제 데이터 행을 가지고 있는 것 처럼 동작하지만 데이터 행을 가지지는 않는다.

3.학생 테이블의 학번, 이름 , 출생년도 , 나이를 출력하라 

SELECT stu_no, stu_name,  substring(id_num, 1,2) from student;


SELECT stu_no, stu_name, year(now()) - (year(now()) - substring(id_num, 1,2)) from student;


select year(now());

2번째 방법

select stu_no, stu_name, birth_year ""출생년도"", year(now()) - birth_year + 1 ""나이"" from student;

 " " 는 주석이고 now() 함수는 현재의 년, 월, 일, 시간 을 나타내는 함수 이고 year() 함수는 년도를 나타내는 함수이다.

따라서 now함수로 현재의 날짜를 추출한 다음 다시 year 함수로 년도만을 추출 한다.

나이는 현재의 년도에서 출생년도를 감산하여 1을 더한 값으로 얻을 수 있다.

예를 들면 현재의 날짜가 2007년 7월 25일 일때 1981년생의 나이를 계산하면 다음과 같다

나이 = 현재 연도 - 출생년도 + 1 = 2017 - 1981 + 1

여기서 나이를 필요로 하는 경우에 매번 계산을 별도로 처리해 주어야 하는 불편을 겪게될 것이다.

이러한 데이터는 student 테이블의 출생년도를 계산하여 ages 라는 view 테이블을 작성하여 다음부터는 간단하게 검색하여 사용할 수 있다.

학적 테이블의 학번, 이름 나이로 구성된 ages 뷰테이블을 생성하라

create view ages(stu_no, stu_name, age) as 

select stu_no, stu_name, year(now()) - birth_year +1 from student;

select * from ages;

ages 뷰의 내용은 데이터베이스에 저장되지느 않지만 select 명령문이 실행되는 순간 유도된다

따라서 뷰의 사용은 기억공간을 사용하는 부가적인 비용은 전혀 없으며, 다른 테이블에 이미 저장된 데이터만으로 구성할 수 있다.

언제 사용하나?

반복되는 명령문이나 루틴을 간단히 사용하고자 할때

테이블의 출력방법을 재구성 하고자 할때

여러 단계에서 select 명령문이 사용될때

데이터를 보호하고자 할때

'데이터 베이스 > mysql 문제' 카테고리의 다른 글

6 select 절  (0) 2017.11.13
5 mysql 함수 & 문제  (0) 2017.11.10
4 mysql 테이블 생성  (0) 2017.11.09
3 mysql 데이터 타입 및 필수 기능  (0) 2017.11.09
2 학사관리 예제 설명  (0) 2017.11.08
1 mysql 데이터 생성 삽입  (0) 2017.11.08

학사관리에서 사용하는 데이터 베이스로 학생의 신상과 등록, 수강, 성적을 기록하는 데이터베이스에 대하여 설명하고자 한다.

- 테이블은 총 9개가 있다.

student 학생 신상 테이블

attend 수강

fee 등록테이블

score 성적

subject 교과목

professor 교수

post 우편

circle 동아리

department 학과

학생테이블은 학생 신상에 관한 데이터로서 이름, 주소, 주민등록번호 같은 내용을 가지고 있다.

primary key는 stu_no 학번이고 이 column은 중복 데이터를 생성할 수 없다.

테이블은 과거의 데이터를 가지지 않기 때문에, 어떤 학생이 다른 집으로 이사 가면 과거의 주소는 새로운 주소로 대치 된다.

다시 말하면, 과거 주소는 다른 곳에서 보유하지 않는다.

attend 테이블은 학생의 수강신청에 관한 데이터로서 수강신청 교과목, 이수구분, 교수코드 같은 내용을 가지고 있다 .

primary_key는 stu_no 학번, att_year 수강년도 att_term 수강학기, sub_code 교과목 코드, prof_code 교수코드 att_jae 재수강 구분 코드로 만들어진 조합키이고 이 칼럼들의 조합키는 중복 데이터를 생성할 수 없다.

이 조합키를 생성하는 이유는 한 학생이 학기마다 여러 과목의 수강신청을 해야 되기 때문이다.

fee 테이블은 학생의 등록금 납입에 관한 데이터로서 입학금, 등록금액, 장학금과 같은 내용을 가지고 있다.

primary_key는 stu_no 학번, fee_year등록년도 fee_term 학기 코드로 만들어진 조합키 이고, 이 colum 들의 조합키는 중복 데이터를 생성할 수 없다.

이 조합키를 생성하는 이유는 한 학생이 등록금을 여러 학기 동안 등록금을 내기 때문이다.

score 테이블은 학생의 성적을 학기별로 정리하여 보관하는 데이터로서 총 신청학점, 취득학점, 평점평균과 같은 내용을 가지고 있다.

primary_key는 stu_no 학번 sco_year 성적취득년도, sco_term 학기 코드로 만들어진 조합키이고 이 칼럼들의 조합키는 중복 데이터를 생성할 수 없다.

이 조합키를 생성하는 이유는 한 학생의 성적처리는 여러 학기동안 처리되어야 하기 때문이다.

subject 테이블은 학생이 수강신청 할 때 수강 교과목을 정리하여 보관하는 데이터로서 과목명, 개설 년도와 같은 내용을 가지고 있다. primary key는 sub_code 과목코드 이다.

professor 테이블은 학생이 수강신청 할 때 담당교수를 정리하여 보관하는 데이터로서 교수명 같은 내용을 가지고 있다. primary_key는 prof_code 교수 코드 이다.

post 테이블은 학적 현주소의 우편번호를 관리하기 위하여 보관하는 데이터로서 주소와 전화의 지역번호와 같은 내용을가지고 있다.

primary_key는 post_no 우편번호 코드 이다.

현주소의 주소 중에서도 시.도, 읍면동은 기재하지 않고, 나머지 주소만 기재할 수 있도록 하기 위함이다.

circle 테이블은 학생들의 동아리 활동을 관리하기 위하여 보관하는 데이터로서 일련번호, 동아리 이름, 학번, 회장과 같은 내용을 가지고 있다.

primary_key 는 cir_num  일련번호 이다.

president 회장인 경우에 0, 부회장인 경우 1, 일반회원은 2 값을 가진다.

department 테이블은 학과의 정보를 관리하기 위하여 보관하는 데이터로서 학과 코드, 학과명 , 영문학과명과 같은 내용을 가지고 있다.

primary_key는 dept_code 학과 코드 이다.

무결성 규칙

앞에서 보여준 9개의 테이블 내용은 무결성 규칙을 만족해야 한다.

예를들면 두 학생은 동일한 학번을 가질 수 없고, attend,fee 테이블에 있는 모든 학생은 student 테이블에 존재해야 한다

각 테이블의 기본 키는 반드시 정의되어야 한다

'데이터 베이스 > mysql 문제' 카테고리의 다른 글

6 select 절  (0) 2017.11.13
5 mysql 함수 & 문제  (0) 2017.11.10
4 mysql 테이블 생성  (0) 2017.11.09
3 mysql 데이터 타입 및 필수 기능  (0) 2017.11.09
2 학사관리 예제 설명  (0) 2017.11.08
1 mysql 데이터 생성 삽입  (0) 2017.11.08

예제 표

이름연락처idcity
홍길동010-1234-1234102수원
이말자010-1234-1235103서울
이고순010-1234-1236104대전

테이블 생성하기

create table example (name varchar(10), phone varchar(15), id varchar(10), city varchar(10));

조회

select * from example;

테이블 확인하기

show tables;

테이블 다시 생성하기

create table example2 (name varchar(10), phone varchar(15), id varchar(10), city varchar(10));

이메일 열 추가하기

alter table example2 add email varchar(20);

테이블 내용 확인하기

desc example2;

city 열 삭제하기

alter table example2 drop city;

테이블 내용 확인하기

desc example2;

열 변경하기 (id열을 id_number로 바꿈)

alter table example2 change id id_number varchar(10);

테이블 내용확인하기

desc example2;

테이블 삭제하기

drop table example2;

이제 생성한 테이블에 데이터를 삽입하는 방법을 보자

상기 테이블 생성 예제에서 테이블의 정의는 테이블의 이름과 각 열의 이름과 데이터 타입으로 이루어져 있다.

따라서 앞 예제에서 테이블의 열을 정의한 것이며 데이터를 삽입함으로써 행이 추가 된다.

데이터 삽입 칼럼 개수랑 같이 적어줘야 한다.

insert into example(name, phone, id, city) values('홍길동', '010-1234-1234', '102' , '수원');

데이터 확인

select * from example;

데이터 추가 삽입

insert into example(name, phone, id, city) values('이말자','02-231-1568','103','서울');


insert into example(name, phone, id, city) values('이고순','02-245-1721','104','대전');

데이터 확인

select * from example;

**데이터 검색하기

데이터 베이스의 중요한 목적중 하나는 대용량 데이터에서 사용자가 원하는 데이터를 빠른 속도로 검색하는 것에 있다.


이름이 홍길동인 사람 검색하기

select * from example where name = '홍길동';

id가 103 이상인 사람 검색하기

select * from example where id >= 103;

대전에 사는 사람 검색하기

select * from example where city = '대전';

대전 또는 수원에 사는 사람 검색하기

select * from example where city = '대전' or city = '수원';


**입력 데이터 변경 및 삭제


홍길동의 거주 도시를 뉴욕으로 변경하기

update example set city ='뉴욕' where name = '홍길동';

데이터 변경 확인하기

select * from example;

이름이 이고순인 사람의 데이터 삭제하기

delete from example where name = '이고순';

데이터 변경 확인하기

select * from example;


'데이터 베이스 > mysql 문제' 카테고리의 다른 글

6 select 절  (0) 2017.11.13
5 mysql 함수 & 문제  (0) 2017.11.10
4 mysql 테이블 생성  (0) 2017.11.09
3 mysql 데이터 타입 및 필수 기능  (0) 2017.11.09
2 학사관리 예제 설명  (0) 2017.11.08
1 mysql 데이터 생성 삽입  (0) 2017.11.08

+ Recent posts

티스토리 툴바