AI교육과정 - SQL.2

😣 만약 오류가 발생하면 서비스 - MySQL80 서비스가 종료되었는지 확인 필요

• 실서버말고 테스트서버로 코딩 필요
• SQL 에서 ‘’(싱글쿼트)를 사용하는 이유는 띄어쓰기 때문임
• SQL 상식

  • %a : a로 끝나는 문구

  • Primary key와 foreign key

    create table tb_profile(
    		pro_idx int not null,
        ...
        foreign key(pro_idx) references tb_member(mem_idx)
    ); -- Primary key인 mem_idx열과 foreign key인 pro_idx 결합
    #예시1
    insert into tb_profile values (2, 30, 170, 70, 'B', 'INTP'); -- foreign key 제약조건에 위배 (tb_member에 idx값이 2인 행이 없어서)

  • *를 사용하는 것은 자중하는 편이 좋음

    select * from tb_member; -- select : 찾기 / * : 모든 column

  • NULL 의 특징

    select null; # 해당 셀에 insert가 되지 않은 것
    select ''; # 해당 셀에 '' 데이터가 삽입된 것
    select 100 + null; # null은 연산할 수 없음

  • 별명주기

    select 100 + 50 as '덧셈';

  • enum()

    enum('남자', '여자'); 
    # 값에 남자, 여자만 입력 가능

• CRUD : Create(생성), Read(읽기), Update(갱신), Delete(삭제)
• SQL 연산자
  1. 산술 연산자
     • +, -, *, /, div(나누기를 한 후 소수부분을 버림), mod(나머지 연산)
  2. 대입 연산자
     • =
  3. 비교 연산자
     • =(equal), <, >, <=, >=, <>(다르다)
     • is(왼쪽 피연산자와 오른쪽 피연산자가 같으면 참, 다르면 거짓) 예) mem_address1 is null
     • between A and B(값이 A보다는 크거나 같고, B보다는 작거나 같으면 참) 예) mem_point between 100 and 300
     • in(연산자의 값이 매개변수로 전달받은 리스트에 존재하면 참을 반환) 예) mem_hobby('잠자기','코딩','게임')
     • like(패턴으로 물자열을 검색) 예) mem_userid ilke 'a’
  4. 논리 연산자
     • and, or, xor, not
• SQL 구문

  1. 데이터 수정

     • 전체 변경 시 오류 발생하면 메뉴 -> edit -> Preferences -> SQL Editor -> safe updates 체크해제

     update 테이블명 set 필드명1 = 값1, 필드명2 = 값2 ...  # 전체가 바뀜
     update 테이블명 set 필드명1 = 값1, 필드명2 = 값2 ... where 조건절  # 조건에 해당하는 값만 바뀜

  2. 데이터 삭제

     delete from 테이블명; # 전체삭제
     delete from 테이블명 where 조건절; # 조건에 해당하는 필드 삭제

  3. 데이터 검색

     • 자바에서 가져가기 위해선 별명 주기를 하지 않는 편이 나음(필드명으로 가져가서 한글인식이 어렵고 띄어쓰기를 허용하지 않음)

     select 필드1, 필드2 .. from 테이블명;
     select 필드1, 필드2 .. from 테이블명 where 조건절

• 데이터 검색의 추가 옵션

  1. 오름차순, 내림차순

     • asc : 오름차순
     • desc : 내림차순

     select 필드1, 필드2 .. from 테이블명 [where 조건절] order by 필드 [asc, desc]
     # 예시1
     select mem_userid, mem_name, mem_point from tb_member order by mem_point asc; -- 오름차순
     select mem_userid, mem_name, mem_point from tb_member order by mem_point; -- 오름차순
     select mem_userid, mem_name, mem_point from tb_member order by me_point desc; -- 내림차순
     # 예시2
     select mem_idx, mem_userid, mem_name, mem_point, mem_gender, mem_regdate from tb_member
     where mem_gender = '여자' order by mem_point desc, mem_regdate;

  2. 가져올 행 지정 방법

     select 필드1, 필드2 .. from 테이블명 limit 가져올 행의 갯수
     select 필드1, 필드2 .. from 테이블명 limit 시작행, 가져올 행의 갯수
     #예시1
     select mem_userid, mem_name, mem_hp, mem_email from tb_member limit 3, 2; # 4번째 행부터 2개를 가져옴

  3. 집계함수

     • count : 행의 갯수를 세는 함수

     select count(mem_idx) from tb_member;
     # 예시1
     select count(mem_idx) user from tb_member where mem_userid = 'apple' and mem_userpw = '1111';
     -- count 값에 대해 user라는 필드명이 주어짐

  4. 중복값 제외하고 검색

     • distinct : 뒤에 나오는 필드에 대해서 같은 값을 가진 중복된 행을 제외

     select distinct mem_gender from tb_member; -- 남자와 여자만 나옴
     # 예시1
     select count(distinct mem_gender) from tb_member; -- 중복된 값 제거 후 행의 갯수 출력

  5. sum : 행의 값을 더함

     select mem_userid, sum(mem_point) total from tb_member; -- X
     select sum(mem_point) total from tb_member; -- O

  6. avg : 행의 값의 평균을 구함

     select avg(mem_point) avg from tb_member;

  7. min, max : 행의 최대값 또는 최소값을 구함

     select min(mem_point) min from tb_member;
     select max(mem_point) max from tb_member;

  8. 필드의 그룹화

     select 그룹을 맺은 컬럼 또는 집계함수 from 테이블명 [where 조건절] group by 필드 having 조건절 order by 필드 [asc, desc]
     #예시1
     select mem_gender from tb_member group by mem_gender;
     #예시2
     select mem_gender, count(mem_idx) cnt from tb_member group by mem_gender;
     #예시3
     select mem_gender, count(mem_idx) cnt from tb_member where mem_point > 0 group by mem_gender;
     #예시4
     select mem_gender, count(mem_idx) cnt from tb_member where mem_point > 0 group by mem_gender having mem_gender = '남자';
     # 포인트가 0이 아닌 회원의 집합에서 남자, 여자로 그룹을 나눠 포인트의 평균을 구하고 포인트가 300이상인 성별을 검색하여 포인트로 내림차순 정렬
     select mem_gender, avg(mem_point) avg from tb_member where mem_point > 0 group by mem_gender having avg >= 300 order by avg desc;

  9. Join : 서로 다른 테이블의 결합

     • inner : 값이 들어있는 필드만 출력
     • left : A에 NOT NULL 값만 출력
     • right : B에 NOT NULL 값만 출력

     select 필드1, 필드2 ... from 테이블1 [inner, left, right] join 테이블2 on 테이블1.필드 = 테이블2.필드;

     

  10. 뷰(view)

      • 가상의 테이블을 생성
      • 실제 테이블처럼 행과 열을 가지고 있지만, 데이터를 저장하고 있지는 않음
      • SQL 코드를 간결하게 만들기 위함, 보안상 내부 데이터를 전체 공개하고 싶지 않을 때
      • 삽입, 삭제, 수정 작업에 제한 사항을 가짐
      • 자신만의 인덱스를 가질 수 없음
      • 한 번 정의된 뷰는 변경할 수 없음

      create view 뷰이름 as select 쿼리...
      #예시1
      create view vw_member_simple as select mem_idx, mem_userid, mem_userpw, mem_name, mem_hp from tb_member;

      1. 뷰 수정

         create or replace view 뷰이름 as select 쿼리...

      2. 뷰 삭제

         drop view 뷰이름