DB
1. 파일설정
데이터 베이스 생성= create database employees; DBName: employees
사용자 생성= create user 'hr'@'localhost' identified by 'hr'; DBUser:
권한 부여= grant all privileges on employees.* to 'hr'@'localhost';
create user 'hr'@'192.168.80.14' identified by 'hr'; DBUser:
grant all privileges on employees.* to 'hr'@'192.168.80.crate14';
윈도우 .zip 파일 리눅스로 옮기는 방법
1. C 드라이브에서 풀고자하는 .zip 파일 확인
2. root에서 mv /home/webmaster/employees_db.zip . 으로 옮김
3. yum install -y unzip 설치
4. unzip employees_db.zip
5. sftp webmaster@192.168.80.114 접속(c driver에서)
5. mysql 접속
6. 접속하여서 local 유저와 현재 윈도우 ip주소로 유저 생성
7. employees db의 모든권한을 새로생성한 사용자의 ip에 부여
8. mysql -u hr -D employees -p < employees.sql
2. DB 기본구문
2-1. select
- 형식: select [ column1, column2 ... ] from [ 테이블명 ] ( where ) ( order by )
테이블의 전체 컬럼보기
- select * from departments;
- select * from employees;
테이블 컬럼 구분해서 보기
- select dept_no, dept_name from departments;
- select first_name, last_name, gender, hire_date from employees;
concat, alias 사용 (as, 생략가능)
- select concat(first_name,', ', last_name) as fullname, gender, hire_date from employees;
- select concat(first_name,', ', last_name) name, gender, hire_date from employees;
distinct 중복제거
- select distinct(title) from titles;
2-2 where
- 형식: select [ column1, column2 ... ] from [ 테이블명 ] ( where 조건식 )
예제 : employees 테이블에서 1991년 이전에 입사한 직원의 이름, 성별, 입사일을 출력
- select concat(first_name,' ', last_name) as 이름, gender as 성별, hire_date as 입사일 from employees where hire_date < '1991-01-01';
예제 : employees 테이블에서 1989년 이전에 입사한 여직원의 이름, 입사일을 출력
- select concat(first_name,' ', last_name) as 이름, hire_date as 입사일 from employees where hire_date < '1989-01-01' and gender = 'f';
#in 연습
예제 : dept_emp 테이블에서 부서 번호가 d005나 d009에 속한 사원의 사번, 부서번호 출력
- select emp_no as 사번, dept_no as 부서번호 from dept_emp where dept_no in('d005','d009');
* in 을 사용하지 않은것
select emp_no as 사번, dept_no as 부서번호 from dept_emp where dept_no = 'd005' or dept_no = 'd009';
#like 연습
-- 예제 employees 테이블에서 1989년에 입사한 직원의 이름, 입사일을 출력
select concat(first_name,' ', last_name) as 이름, hire_date as 입사일 from employees where hire_date like '1989%';
-- 논리연산 사용
select concat(first_name,' ', last_name) as 이름, hire_date as 입사일 from employees where hire_date > '1988-12-31' and hire_date < '1990-01-01' ;
-- between 사용
select concat(first_name,' ', last_name) as 이름, hire_date as 입사일 from employees where hire_date between '1988-12-31' and '1990-01-01' ;
2-3 ORDER BY
- 형식: select [ column1, column2 ... ] from [ 테이블명 ] ( where 조건식 ) ( order by 순서 )
ASC: 오름차순 정렬, 기본값
DESC: 내림차순
-- 예제 : salaries 테이블에서 2001년 월급을 가장 높은순으로 사번, 월급순으로 출력
select emp_no as 사번, salary as 월급 from salaries where from_date like '2001%' order by salary desc;
select emp_no as 사번, salary as 월급 from salaries where from_date like '2001%' and to_date like '2001%' order by salary desc;
-- 예제: 직원들의 월급을 사번, 월급 순으로 사번, 월급으로 출력
select emp_no as 사번, salary as 월급, from_date, to_date from salaries order by emp_no, salary desc;
3. DB 함수
3-1. upper
select upper('busan'), upper('douzone');
select upper(first_name) from employees;
3-2 lower
select lower('BUSAN'), lower('DOUZONE');
select lower(first_name) from employees;
3-3 substring (문장, index, length)
select substring('Happy Day', 3, 5);
-- 예제: 1989년에 입사한 사원들의 이름, 입사일 출력
select first_name, hire_date from employees where '1989' = substring(hire_date,1,4);
3-4 lpad(오른쪽 정렬), rpad(왼쪽 정렬)
select lpad('1234', 10, '-');
select rpad('1234', 10,'-');
-- 예제: 직원들의 월급을 오른쪽 정렬(빈공간은 *)
select * from salaries;
select emp_no, lpad(salary, 10,'*') from salaries where from_date Like '2001%';
3-5 trim, ltrim, rtrim
select ltrim(" 1312 asdas 111d ");
select concat("---", ltrim(" 1312 asdas 111d "));
select rtrim(" 1312 asdas 111d ");
select concat(rtrim(" 1312 asdas 111d "),"---");
select concat("---", trim(both ' ' from ' helllo '), "-------");
3-6 abs() : 절대값
select abs(-01), abs(1);
3-7 mod() : 나머지 값
select mod(10,3);
select mod(1,5);
3-8 floor(x) : x 보다 크지 않은 가장 큰 정수를 반환
select floor(3.14);
3-9 ceil(x) : x 보다 큰 정수중에 가장 작은 정수를 반환
select ceil(3.04);
select ceiling(3.04);
3-10 round(x) : x에 가장 근접한 정수를 반환, round(x, d): x 값 중에서 소수점 d 자리에 가장 근접한 실수 반환
select round(123.523);
select round(12.16, 1);
3-11 pow(x, y), power(x, y) : x의 y승을 반환
select pow(2,4);
select power(3,3);
select power(2,30);
3-12 sign(x) : x 가 양수이면 1, 음수이면 -1, 0 이면 0
select sign(1);
select sign(-2);
select sign(0);
3-13 greatest(x, y, ...) , least(x, y, ...)
select greatest(10, 40, 20, 30); -- 결과: 40
select least(10, 40, 20, 30); -- 결과: 10
select greatest("b", "F" ,"z"); -- 결과: z
# 날짜 함수
3-14 curadte(), current_date: 오늘 날짜
select curdate(), current_date;
3-15 curtime(), current_time: 지금 시간
select current_time(), curtime();
3-16 now() , sysdate() // 큰 차이는 없지만 미세한 차이점 구분정도는 하자
select now(), sysdate();
select now(), sleep(2), now(); -- now(): 첫번째 쿼리가 들어갔을때 시간이 나옴
select sysdate(), sleep(2), sysdate(); -- sysdate(): 쿼리가 들어갔을때 마다 계산되는 시간이 나옴
3-17 날짜형 함수
-- DATE_FORMAT(date, format)
select now();
select date_format(now(),'%y년 %m월 %d일 %h시 %i분 %s초');
-- period_diff
-- 예시) 근무 개월 수를 출력
select first_name, period_diff(date_format(curdate(),'%y%m'), date_format(hire_date,'%y%m')) from employees;
3-18 date_add(=adddate), date_sub(subdate)
-- 날짜 date에 type(day, month, year) 형식으로 expr 값을 더하거나 뺀다.
-- 예시) 각 사원들의 근무 년 수가 5년이 되는 날은 언제인가?
select first_name, hire_date, date_add(hire_date, interval 5 year) from employees;
3-19 cast
select cast('2021-05-07' as date);
select cast('1234' as int)+10;
3-20 mysql type
- varchar, char(고정), text(5000 이상 가능)
- signed(unsigned), int(integer), medium int, big int
- float, double
- time, date, datetime
- lob(Large OBject)
etc....
4. 집계함수
4-1 집계 쿼리: SELECT 절에 그룹함수가 적용된 경우.
select avg(salary) from salaries;
4-2 select 절에 그룹 함수가 있는 경우, 어떤 컬럼도 select 절에 올 수 없다.
-- emp_no는 아무런 의미가 없음.
-- 오류!
select emp_no, avg(salary) from salaries;
4-3 쿼리 실행 순서 숙지
-- 1. from: 접근 테이블 확인
-- 2. where: 조건에 맞는 row 선택 -> 임시 테이블
-- 3. 집계
-- 4. projection
select avg(salary) from salaries where emp_no ="10060";
4-4 group by 에 참여하고 있는 컬럼은 projection 가능(select 절에 올 수 있다.)
select emp_no,avg(salary) from salaries group by emp_no;
4-5 having
-- 집계 결과 임시 테이블에서 row를 선택해야하는 경우
-- 이미 where절은 실행 되었기 대문에 having 절에서 조건을 주어야 한다.
select emp_no, avg(salary) as 평균연봉 from salaries group by emp_no having 평균연봉 > 60000;
4-6 order by
select emp_no, avg(salary) from salaries group by emp_no having avg(salary) > 60000 order by avg(salary);