12. SQL 작업환경 구축 및 기초 구문

0. SQL 기초 - 작업 환경 구축
  1) apm Monitor (CLI 환경)
      apmsetup 완료 후 cmd창에서 netstat -an
      80확인 3306포트 Listening 확인
      apmonitor에서 MySQL 콘솔로 들어가서 암호 입력 후 로그인

  2) MySQL (GUI 환경)
     MySQL 설치 후 MySQL 쿼리 브라우저 실행
     server host 127.0.0.1   : 3306
     username : root
     password : apmsetup
     default schma : webhack

1. SQL 기초 구문
  1) DB 테이블 만들고 자료 삽입 apm monitor 콘솔에서 진행
      use webhack              : 사용하고자 하는 schema 선택

create table member(
no integer primary key,
name varchar(20),
user_id varchar(10),
user_pw varchar(10),
nickname varchar(20),
email varchar(40)
);

  2) 생성한 Table에 자료 입력

insert into member(no, name, user_id, user_pw, nickname, email)
 	values(1, '에이', 'a', 'dpdl', '에이', 'a@kgitbank.com');
insert into member(no, name, user_id, user_pw, nickname, email)
	values(2, '비', 'b', 'ql', '비', 'b@kgitbank.com');
insert into member(no, name, user_id, user_pw, nickname, email)
	values(3, '씨', 'c', 'Tl', '씨', 'c@kgitbank.com');

 3) 입력한 자료 확인

show tables;
select * from member;

 4) 서버컴퓨터에서 MSSQL 쿼리 분석기 사용

use webhack
select * from member

insert 구문
insert into member
values('test', '1234', '테스트', '테스트', '23','','','','','')  -- 테이블의 컬럼개수에 맞게 value입력

insert into member(user_id, user_pw, name, nickname)
values('test2', '1234', '테스트2', '테스트2')

 

client에서 웹페이지 회원가입 시 아래 파일로 요청한다
POST http://192.168.1.20/member/member_register_ok.asp HTTP/1.0
서버에서 파일을 열어보면

strSQL="insert into member(user_id,name,user_pw,nickname,zipcode,address1,address2,email,homepage)"
strSQL=strSQL&"values ('"&user_id&"','"
strSQL=strSQL&name&"','"
strSQL=strSQL&user_pw&"','"
strSQL=strSQL&nick_name&"','"
strSQL=strSQL&zipcode&"','"
strSQL=strSQL&address1&"','"
strSQL=strSQL&address2&"','"
strSQL=strSQL&email&"','"
strSQL=strSQL&homepage&"')"
DBconn.execute(strSQL)

 5) update구문

update member -- member 테이블 use_id가 test인 행의 name을 테스트1으로 변경
set name='테스트1'
where user_id='test'

회원정보 수정 시 GET method로 update SQL 구문을 사용함
GET http://192.168.1.20/member/member_modify_ok.asp?
if exec = "modify" then
strSQL = "update member set nickname = '" + nickname + "' , email = '" + email + "' where user_id = '" + user_id +"'"
set Rs=DBconn.execute(strSQL)

 6) delete구문

delete from member
where user_id='test2'  --  member 테이블의 user_id가 test2인 행을 모두 삭제

게시글 삭제 시 POST method로 delete 구문을 사용함
POST http://192.168.1.20/board/board_delete_ok.asp
if Trim(userPassword) = Trim(strRealPassword) then
strSQL = "delete from board where strNumber = " & intNum
DBconn.Execute strSQL
DBconn.Close
Set DBconn = nothing
%>

 

 7) select구문 (조회)

select name, age
from member
where age>=25 -- member table에서 age가 25 이상인 행의 name, age 칼럼을 보여줘
  
-- where에 다양한 조건을 사용할 수 있음
where age between 20 and 25 -- age가 20 ~ 25인
where zipcode like'신%' -- zipcode가 신**으로 시작하는 
order by age -- age 오름차순 정리
order by age desc, name desc -- 내림차순 정렬(desc) age를 1순위로 내림차순으로 정렬하고, 그안에서 name을 2순위로 내림차순 정렬

select avg(age) /count/max/min/sum -- 다양한 함수 사용가능
select count(distinct age) -- distinct 중복을 제외하고

-- NULL 이랑 빈칸은 다르다

select age, count(*) as 인원수 -- 몇개인지 숫자 세줘 '인원수'라는 별칭을 컬럼으로 해서
from member
where name like '이%' -- 이름이 '이'로 시작하는 회원
group by age  -- 해당 컬럼의 경우의 수대로 그룹을 지어서 
having age>=25 -- age가 25이상인 값들 중에서
order by age desc -- 나이 순으로 내림차순 정렬

 

 8) substring
substring (MSSQL)   mid(MySQL, Oracle)  

select substaring('abcd', 1, 3)
select substring(db_name(), 2, 1) -- blind SQL Injection에서 사용

 9) union (합집합)

   ** union을 사용 할 수 있는 조건
    1. field의 개수가 일치
    2. 각 field의 type이 일치
    3. select 문에서 column의 순서가 같아야 함

select name from member union
select strName from board

실습 문제 member에서 조회
[문제 1] 나이가 가장 많은 회원의 이름을 조회하라
select name, age
from member
where age = (select max(age) from member) 

[문제 2] 모든 회원들의 나이를 7살 높여라
update member
set age = age + 7

[문제 3] id 중에 's'가 들어가는 회원의 tuple을 삭제하라
delete from member
where user_id like '%s%'

[문제 4] 이름 중에 '지'가 들어가는 회원의 모든 정보를 조회하라!!
단 나이와, 이름을 내림차순으로 정렬하라.
select name, are from member
where name like '%지%'
order by age desc, name desc

[문제 5] 나이가 30 이하인 회원들을 그룹별로 인원수를 조회하라
select age, count(*) as 인원수
from member
group by age
having age<=30

[문제 6] 성이 '김'씨인 회원의 성을 '이'씨로 변경하라
update member
set name='이'+ substring(name, 2, 2)
where substring(name, 1, 1) ='김'