부스트코스 DB연결 웹앱#7 SQL

SQL을 설치한 후 유저를 생성후 접속한다

mysql -uroot -p 후 비밀번호를 입력하고

데이터베이스를 생성하려면 create database DB명;을 입력한다. db이름은 connectdb로 한다 

 

데이터베이스가 생성됐으면 유저를 생성한다. 현재최신버전은 8버전이고 8버전의 코드는 이걸 입력하면 된다.

create user 'connectuser'@'localhost' identified by 'connect123!@#';

grant all privileges on connectdb.* to 'connectuser'@'localhost';

grant all privileges on connectdb.* to 'connectuser'@'%';

flush privileges;

 

그리고 나서 만든 유저명으로 로그인한다.

로그인할 때 주의사항은 mysql창이 아닌 그냥 cmd창에서 입력해야한다는 것이다.

mysql –h127.0.0.1 –uconnectuser –p connectdb 입력 후 비밀번호인 connect123!@#을 입력한다.

 

학습 목표

1. insert문을 수행할 수 있다.
2. update문을 수행할 수 있다.
3. delete문을 수행할 수 있다.
4. select문을 수행할 수 있다.

select 문의 기본문법은 select 칼럼명 from 테이블명; 이다

departments 테이블의 모든 데이터를 출력하려면

select * from departments;로 하면된다.

두 가지를 출력하려면 사이에 콤마를 넣으면 된다

select empno, name from employee;

 

칼럼명을 내 마음대로 바꿔서 출력할 수 있다. 여기서 as는 안써도 되지면 명시적으로 표시하기위해 사용하는 것이 좋다.

select empno as 사번, name as 이름 from employee;

 

칼럼명을 한 칼럼에 합쳐서 출력할 수 있다.

select concat( empno, '-', deptno) as '사번 - 부서번호'from employee;

 

칼럼의 중복을 제거할 수 있다.

select distinct deptno from employee;

 

칼럼을 원하는 순서로 정렬할 수 있다.

select empno, name, job from employee order by name;

이름을 원하는대로 하려면

select empno as 사번, name as 이름, job as 직업 from employee order by 이름; 하면 된다.

내림차순 정렬은

select empno, name, job from employee order by name desc; 로 하면된다

 

 

WHERE절 사용

기본적인 문법이다.

select * from employee where title = 'staff'를 하면 타이틀이 스태프인사람만 출력된다.

select * from employee where deptno in (10,30); 10에서 30사이의 사번인 사람만 출력된다.

in을 안쓰고 select * from employee where deptno=10 or deptno = 30; 을 써도 된다.

 

like를 활용해 검색할 수 있다.

a로 시작하는 이름을 출력한다.

select * from employee where name LIKE 'a%';

 

a가 들어가는 이름을 출력한다.

select * from employee where name LIKE '%a%';

 

특정위치에 a가 들어가는 이름을 출력한다.

select * from employee where name LIKE '_a%'; >>언더바가 한개면 2번째 문자를 검색한다.

 

그 외에도 여러가지 기능이 있는데 기능이 있다는것만 알아놓고 필요할 때 검색해보자

대소문자 변경

특정문자열만 출력

글자수 채워서 출력, 모자랄시 원하는 문자를 넣어서 출력함

SELECT LPAD('hi',5,'?'),LPAD('joe',7,'*');

 ???hi | ****joe |

문자열 공백제거

절대값 구하기

나머지 구하기

그외들

• FLOOR(x) : x보다 크지 않은 가장 큰 정수를 반환합니다. BIGINT로 자동 변환합니다.
• CEILING(x) : x보다 작지 않은 가장 작은 정수를 반환합니다.
• ROUND(x) : x에 가장 근접한 정수를 반환합니다.
• POW(x,y) POWER(x,y) : x의 y 제곱 승을 반환합니다.
• GREATEST(x,y,...) : 가장 큰 값을 반환합니다.
• LEAST(x,y,...) : 가장 작은 값을 반환합니다.
• CURDATE(),CURRENT_DATE : 오늘 날짜를 YYYY-MM-DD나 YYYYMMDD 형식으로 반환합니다.
• CURTIME(), CURRENT_TIME : 현재 시각을 HH:MM:SS나 HHMMSS 형식으로 반환합니다.
• NOW(), SYSDATE() , CURRENT_TIMESTAMP : 오늘 현시각을 YYYY-MM-DD HH:MM:SS나 YYYYMMDDHHMMSS 형식으로 반환합니다. 
• DATE_FORMAT(date,format) : 입력된 date를 format 형식으로 반환합니다.
• PERIOD_DIFF(p1,p2) : YYMM이나 YYYYMM으로 표기되는 p1과 p2의 차이 개월을 반환합니다.

 

그룹함수

2개 이상의 칼럼을 한번에 처리하는 것을 그룹함수라고 한다. 앞서배운 것들은 하나의 칼럼에서만 적용되는 것이고 이후부터는 2개이상 칼럼에 적용가능하다.

 

타입변경 cast함수

cast (expression as type) 타입은 원하는 것으로 변경가능하다.

select cast(now() as date);

2003-09-25

select cast(1-2 as unsigned);

18446744073709551615

 

그외 그룹함수들

예제 : employee 테이블에서 부서번호가 30인 직원의 급여 평균과 총합계를 출력하시오.

 

 

 

SELECT AVG(salary) , SUM(salary) FROM employee WHERE deptno = 30;

 

예제 : employee 테이블에서 부서별 직원의 부서번호, 급여 평균과 총합계를 출력하시오.

 

 

 

SELECT deptno, AVG(salary) , SUM(salary) FROM employee group by deptno;

부서별, ~~별이 붙으면 무조건 group by를 사용한다. 부서별 회사별처럼 ~~별이 무슨말인지 이해가 안가는 사람을 위해 설명하자면

 

db에 deptno에는 10부터 30까지 사번을 가진 여려명의 사원이 존재한다. 내가보고싶은 것은 각 사원별

즉 10번사원평균, 20번사원평균, 30번사원 평균이다. 이 세개를 각각 계산해서 출력해주는 것이 group by다.

 

회사별로 생각해도 it회사평균, 서비스업평균등등 각 회사마다 평균이 필요할 때 사용할 수 있다.

 

데이터 입력

INSERT INTO 테이블명(필드1, 필드2, 필드3, 필드4, … )

VALUES ( 필드1의 값, 필드2의 값, 필드3의 값, 필드4의 값, … )

필드명은 생략가능하지만 생략했을 시 모든 벨류값은 다 입력해야 한다.

 

예제 : ROLE테이블에 role_id는 200, description에는 'CEO'로 한건의 데이터를 저장하시오.

하기전에 desc로 테이블을 탐색한다. desc role;

그 후 필드값을 생략해도되고 넣어서 입력해도 된다.

 

 

 

insert into role values (200, 'ceo');

 

 

데이터 수정(UPDATE문)

UPDATE 테이블명 SET 필드1=필드1의값, 필드2=필드2의값, 필드3=필드3의값, … WHERE 조건식

 

ROLE테이블에 role_id가 200일 경우 description을 'CTO'로 수정하시오.

 

 

 

update ROLE set description = 'CTO' where role_id = 200;

 

 

데이터 삭제(DELETE문)

DELETE FROM 테이블명 WHERE 조건식

 

예제 : ROLE테이블에서 role_id는 200인 정보를 삭제하시오.

 

delete from ROLE where role_id = 200;

 

 

JOIN

다른 테이블의 값을 가져올 때 사용한다. 종류가 많고 어렵기 때문에 기본적인 join에 대해서만 서술하겠다.

사원의 이름과 그 사원이 속한 부서명을 구하려면 어떻게 해야할까?

join을 활용해야 한다. 기본문법은

 

SELECT 테이블별칭.조회할칼럼, 테이블별칭.조회할칼럼

FROM 기준테이블 별칭

(INNER) JOIN 조인테이블 별칭 ON 기준테이블별칭.기준키 = 조인테이블별칭.기준키

 

select employee.name, department.name
from employee join department

on employee.deptno = department.deptno;

 

로 한다

여기서 on 부분이 없다면

직원테이블 이름들 + 사원테이블 이름들이 모두 출력된다.

우리가 구해야 할 것은 사원의 이름과 그 사원이 속한 부서다. 사번이 중복되는 사람을 구해야 한다.

그렇기 때문에 on을 사용해서 a테이블과 b테이블의 중복된 값을 출력한다.