[6주차] SQL (1)

[데이터 베이스]

• 데이터 베이스: 여러 사람이 공유하여 사용할 목적으로 체계화해 통합, 관리하는 데이터의 집합체
• DBMS: 사용자와 데이터베이스 사이에서 사용자의 요구에 따라 정보를 생성해주고 데이터베이스를 관리해주는 소프트웨어
• RDB(관계형 데이터베이스): 서로간에 관계가 있는 데이터 테이블들을 모아둔 데이터 저장공간

• SQL: 데이터베이스에서 데이터를 정의, 조작, 제어하기 위해 사용하는 언어
  

• MySQL 접속
  • 터미널 -> mysql -u root -p -> 패스워드

1. 현재 database 목록 확인: show databases;
2. database 이름 지정 및 생성: create database 이름;
3. 해당 데이터베이스로 이동(사용): use 이름;
4. 데이터베이스 삭제: drob database 이름;

• USER 관리

1. USER 조회: use mysql; select host, user from user;

• USER 생성

1. localhost:  create user 'noma'@'localhost' identified by '1234';
2. 외부 접속 가능: create user 'noma'@'%' identified by '1234';

• USER 삭제

- drop user 'noma'@'localhost'
- drop user 'noma'@'%'

• USER 권한 관리

  • 실습 환경 생성: create database testdb;
  • create user 'noma'@'localhost' identified by '1234';

• USER 권한 확인

  1. 사용자에게 부여된 모든 권한 목록 확인
     -> show grants for 'noma'@'localhost';
     
  2. 사용자에게 특정 데이터 베이스의 모든 권한 부여
     -> grant all on textdb.* to 'noma'@'localhost';

• USER 권한 제거
  • 현재 PC에 접근 가능한 noma에게 testdb의 모든 권한 삭제
  • revoke all on testdb.* from 'noma'@'localhost';
    

참고! - 수정내용이 적용 되지 않은 경우 새로고침

• FLUSH PRIVILEGES;

[Table]

• 제로베이스라는 이름의 데이터베이스 생성
  • create databse zerobase default character set utf8mb4;
    
    

• Table: 데이터베이스 안에서 실제 데이터가 저장되는 형태이고, 행(Row)과 열(Column)로 구성된 데이터 모음

1. 테이블 생성: create table mytable (id int, name varchar(16));
2. 테이블 목록 확인: show tables;
3. 테이블 정보 확인: DESC tables;

• 테이블 변경

1. 테이블 이름 변경: alter table mytable rename person;
2. 테이블 컬럼 추가: alter table person add column agee double;
3. 테이블 컬럼 데이터타입 변경: alter table person modify column agee int;
4. 테이블 컬럼 이름 변경: alter table person change column agee age int;
5. 테이블 컬럼 삭제: alter table person drop column age;

• 테이블 삭제

1. 테이블 삭제: drop table person

[SELECT]

• 실습 환경 생성

- use zerobase;
- create table person( id int, name varchar(16), age int, sex CHAR);

• INSERT - 데이터 추가

- ID 값이 1인 이효리, 43세, 여자(F) 데이터 추가

INSERT INTO person (id, name, age, sex)
VALUES (1, '이효리', 43, 'F')

-> 모든 컬럼값을 추가하는 경우는 컬럼 이름 지정 X 가능 
-> insert into person values (1, '이효리', 43, 'F')

• SELECT - 데이터 조회

1. 테이블 내 특정 칼럼 데이터 조회: select name, age, sex from person;
2. 테이블 내 모든 컬럼 데이터 조회: select * from person;

• WHERE - 조건

1. 테이블 내 조건을 만족하는 데이터 조회: select * from person where sex='F';

• UPDATE - 데이터 수정

1. 이효리 나이를 23세로 수정: update person set age=23 where name='이효리';

• DELETE - 데이터 삭제

1. 이상순 데이터 삭제: delete from person where name='이상순';

[ORDER BY]

• 실습환경 생성
  • use zerobase
    
• ORDER BY: SELECT 문에서 데이터를 특정 컬럼을 기준으로 오름차순 혹은 내림차순 정렬하여 조회
  • ASC(Ascending): 오름차순으로 정렬
  • DESC(Descending): 내림차순으로 정렬

1. celeb 테이블에서 이름과 나이를 나이순으로 조회

select age, name from celeb order by age ASC;

2. celeb 테이블에서 이름과 나이를 나이의 역순으로 정렬한 뒤 이름순으로 정렬하여 조회

select age, name from celeb order by DESC, name ASC;

[비교연산자]

• 실습환경 만들기

- use zerobase;
- DESC celeb;

• 비교 연산자 종류
  

1. 나이가 29세가 아닌 데이터 검색 : select name, age from celeb where age!=29 order by age;

2. 나이가 29세보다 크거나 작은 (같지 않은) 데이터 검색: select name, age from celeb where age<>29 order by age;

[논리연산자]

• 실습 환경 생성 : use zerobase;

• 논리 연산자 정류
  
• AND (조건을 모두 만족하는 경우 TRUE)

문제: 성별이 남자이고 나이가 40세 보다 큰 데이터를 이름의 역순으로 정렬하여 검색

select * from celeb where sex='M' and age>40 order by name;

• OR (하나의 조건이라도 만족하는 경우 TRUE)

문제: 나이가 29세 보다 작고 여자이거나, 나이가 30세 보다 크고 남자인 데이터를 나이와 성별 순으로 정렬하여 검색

select * from celeb where (age<20 and sex='F') or (age>30 and sex='M') order by age, sex;

주의! AND 연산자가 OR 연산자보다 먼저 연산되므로 ( )로 적절히 묶어 사용하자!

• NOT (조건을 만족하지 않는 경우 TRUE)

문제: 소속사가 YG엔터테이먼트이면서 남자가 아니거나, 직업이 가수이면서 소속사가 YG엔터테이먼트가 아닌 데이터 검색

select * from celeb where (agency='YG엔터테이먼트' and not sex='M') or (job_title='가수' and not agency='YG엔터테이먼트');

• BETWEEN (조건이 범위 사이에 있으면 TRUE)

문제: 생년월일이 1980년에서 1995년 사이가 아니면서 여자이거나,
소속사가 YG엔터테이먼트이면서 나이가 20세에서 45세 사이가 아닌 데이터 검색

select * from celeb where (not birthday between 19800101 and 19951231 and sex='F') or (agency='YG엔터테이먼트' and not age between 20 and 45);

• IN (목록 안에 조건이 존재하는 경우 TRUE)

문제: 소속사가 나무엑터스, 안테나, 울림엔터테이먼트가 아니면서, 성별이 여자거나 나이가 45세 이상인 데이터 검색

select * from celeb where not agency in ('나무엑터스', '안테나', '울림엔터테이먼트') and (sex='F' or age>=45);

• Like (조건값이 패턴에 맞으면 True)

문제: 직업이 하나 이상인 연예인 중 영화배우 혹은 탤런트가 아닌 연예인 검색

select * from celeb where job_title like '%,%' and not (job_title like '%영화배우%' or job_title like '%탤런트%');