https://www.inflearn.com/course/mysql/dashboard

select 쿼리문

데이터 베이스 및 테이블 정보 조회

show databases; 데이터베이스의 목록을 조회한다.
use 데이터베이스명; 지정된 데이터베이스를 사용한다.
show tables; 테이블들의 목록을 조회한다.
desc tables; 테이블의 정보를 조회한다.

select문 기본

select * from 테이블명;
select 컬럼명1, 컬럼명2 from 테이블명;

연산자 사용하기

산술 연산자를 사용하면 데이터를 가져올 때 연산을 해서 가져올 수 있다
문자열은 0취급

distinct 연산자를 사용하면 중복을 제거할 수 있다.

-- 산술 연산자

select 20 + 10;
select 20 - 10;
select 20 * 10;
select 20 / 10;

select salary, salary + 1000, salary - 1000, salary * 1000, salary / 1000
from salaries;

-- 각 사원의 사원번호, 급여액, 10% 인상된 급여액을 가져온다.
select emp_no, salary, salary * 1.1
from salaries;

-- 각 사원의 사원번호, 급여액, 10% 인하된 급여액을 가져온다.
select emp_no, salary, salary * 0.9
from salaries;

-- 문자열 컬럼 연산
select first_name + 100, first_name * 100, first_name + last_name
from employees;

-- 근무 부서 테이블에서 부서 번호를 가져온다.
select distinct dept_no
from dept_emp;

-- 각 사원의 직함을 중복되지 않게 가져온다.
select distinct title
from titles;

조건절 사용하기

where 절을 사용하면 데이터를 가져올 때 조건을 설정할 수 있다.

-- 부서번호가 d005인 부서인 매니저 사원의 사원번호, 부서번호를 가져온다.
select emp_no, dept_no
from dept_manager
where dept_no='d005';

-- 부서가 d003이 아닌 매니저 사원들의 사원번호와 부서 번호를 가져온다.
select emp_no, dept_no
from dept_manager
where dept_no <> 'd003';

-- 급여액이 150000 이상인 사원의 사원번호, 급여액을 가져온다.
select emp_no, salary
from salaries
where salary >= 150000;

-- 급여액이 40000 이하인 사원의 사원번호, 급여액을 가져온다.
select emp_no, salary
from salaries
where salary <= 40000;

-- 1986년 이후에 입사한 사원들의 사원번호, 입사일, 성과 이름을 가져온다.
select emp_no, hire_date, last_name, first_name
from employees 
where hire_date >= '1986-01-01';

-- 1990년 이후에 매니저로 근무하고 있는 사원들의 사원번호, 부서번호, 매니저시작날짜를 가져온다.
select emp_no, dept_no, from_date
from dept_manager
where from_date >= '1990-01-01';

-- 1990년 이전에 입사한 사원들의 사원번호, 입사일을 가져온다.
select emp_no, hire_date
from employees
where hire_date < '1990-01-01';

논리 연산자

and, or, not을 사용해 조건식에 대한 작업을 할 수 있다.
between을 사용하면 범위에 대한 조건을 만들 수 있다.
in을 사용하면 지정된 값과 일치하는 조건을 만들 수 있다.

-- d001 부서의 매니저 중에서 1990년 이후부터 매니저인 사원의 사원번호, 부서번호, 매니저, 시작날짜를 가져온다.

select emp_no, dept_no, from_date
from dept_manager
where dept_no='d001' and from_date >= '1990-01-01';

-- 1990년 이후에 입사한 남자 사원의 사원번호, 성별, 입사일을 가져온다.

select emp_no, gender, hire_date
from employees
where hire_date >= '1990-01-01' and gender = 'M';

-- 1990년 이후부터 60000 이상의 급여를 받는 사원의 사원번호, 급여, 급여 수령 시작 날짜를 가져온다.

select emp_no, salary, from_date
from salaries
where from_date >= '1990-01-01' and salary >= 60000;

-- d001 부서와 d002 부서 매니저의 사원 번호, 부서 번호를 가져온다.

select emp_no, dept_no
from dept_manager
where dept_no = 'd001' or dept_no= 'd002';

-- 직함이 Staff인 사원과 Engineer인 사원의 사원번호, 직함을 가져온다.

select emp_no, title
from titles
where title = 'Staff' or title = 'Engineer';

-- 부서 번호가 d003이 아닌 매니저의 사원번호와 부서 번호를 가져온다.

select emp_no, dept_no
from dept_manager
where dept_no <> 'd003';

select emp_no, dept_no
from dept_manager
where not dept_no='d003';

-- 급여가 60000 이상 70000 미만인 사원들의 사원번호 급여를 가져온다.

select emp_no, salary
from salaries
where salary >= 60000 and salary < 70000;

select emp_no, salary
from salaries
where salary between 60000 and 69999;

-- d001 부서와 d002 부서 매니저의 사원번호, 부서번호를 가져온다.

select emp_no, dept_no
from dept_manager
where dept_no = 'd001' or dept_no = 'd002';

select emp_no, dept_no
from dept_manager
where dept_no in('d001', 'd002');

like

like 연산자를 사용하면 다양한 문자열 조건식을 만들 수 있다.

-- 이름이 Tommaso 사원의 사원번호, 이름을 가져온다.

select emp_no, first_name
from employees
where first_name = 'Tommaso';

-- 이름의 첫 글자가 A로 시작하는 사원의 사원번호, 이름을 가져온다.

select emp_no, first_name
from employees
where first_name like 'A%';

-- 이름의 마지막 글자가 s로 끝나는 사원의 사원번호, 이름을 가져온다.

select emp_no, first_name
from employees
where first_name like '%s';

-- 이름의 두번째 글자가 i인 사원의 사원번호, 이름을 가져온다.

select emp_no, first_name
from employees
where first_name like '_i%';

-- 이름에 o가 포함되어 있는 사원의 사원번호, 이름을 가져온다.

select emp_no, first_name
from employees
where first_name like '%o%';

-- 이름에 o가 포함되어 있는 사원의 사원번호, 이름을 가져온다.
-- 단 마지막 글자가 o가 아닌 사원만 가져온다.

select emp_no, first_name
from employees
where first_name like '%o%' and not first_name like '%o';

-- 이름이 5글자인 사원의 사원번호, 이름을 가져온다.

select emp_no, first_name
from employees
where  first_name like '_____';

정렬

order by절을 이용하면 정렬된 데이터를 가져올 수 있다

-- 사원의 번호와 급여를 가져온다. 급여를 기준으로 오름차순 정렬한다.

select emp_no, salary
from salaries
order by salary asc;

select emp_no, salary
from salaries
order by salary;

-- 사원의 번호와 급여를 가져온다. 급여를 기준으로 내림차순 정렬한다.

select emp_no, salary
from salaries
order by salary desc;

-- 사원의 번호와 이름을 가져온다. 이름을 기준으로 오름차순 정렬한다.

select emp_no, first_name
from employees
order by first_name asc;

-- 사원의 번호와 이름을 가져온다. 이름을 기준으로 내림차순 정렬한다.

select emp_no, first_name
from employees
order by first_name desc;

-- 사원의 번호와 입사일을 가져온다. 입사일을 기준으로 오름차순 정렬한다.

select emp_no, hire_date
from employees
order by hire_date;

-- 사원의 번호와 입사일을 가져온다. 입사일을 기준으로 내림차순 정렬한다.

select emp_no, hire_date
from employees
order by hire_date desc;

숫자 함수

숫자 함수를 사용하면 숫자값이나 숫자 컬럼에 대해서 필요한 작업을 할 수 있다.

-- 절대값
select abs(100), abs(-100);

-- 소수점 이하 올림
select ceil(10.1), ceil(10.4), ceil(10.5), ceil(10.8);

-- 소수점 이하 버림
select floor(10.1), floor(10.4), floor(10.5), floor(10.8);

-- 반올림
select round(10.1), round(10.4), round(10.5), round(10.8);
select round(166.555,0), round(166.555, 1), round(166.555, -1);

-- 버림
select truncate(166.555, 0), truncate(166.555, 1), truncate(166.555, -1);

-- x의 y승
select pow(10,2);

-- 나머지 구하기
select mod(10,3);

-- 최대 숫자 구하기
select greatest(10, 4, 20, 1); 

-- 최소 숫자 구하기
select least(10, 4, 20, 1);

-- 사원들의 사원번호와 급여를 가져온다. 급여는 10% 인상된 급여를 가져오며 소수점 이하는 올린 값, 버린값, 반올림한 값을 모두 가져온다.

select emp_no, salary*1.1, ceil(salary*1.1), floor(salary*1.1), round(salary*1.1,0)
from salaries

문자열 함수

문자열 핢수들을 사용하면 데이터를 가져올 때 문자열에 관련된 작업을 할 수 있다.

select concat('aaa', 'bbb', 'ccc');

select insert('aaaaa', 2, 2, 'bbb');

select insert('aaaaa', 2, 0, 'bbb');

select replace('aabbcc', 'bb', 'ff');

select instr('abcdefg', 'cde');

select instr('abcdefg', 'kk');

select left('abcdefg', 3);

select right('abcdefg', 3);

select mid('abcdefg', 3, 3);

select substring('abcdefg', 3, 3);

select concat('[', '          abc             ', ']');

select concat('[', ltrim('          abc             '), ']');

select concat('[', rtrim('          abc             '), ']');

select concat('[', trim('          abc             '), ']');

select lcase('abCDef');
select lower('abCDef');

select ucase('abCDef');
select upper('abCDef');

select reverse('abcdef');

-- 사원의 이름을 가져온다. 성과 이름을 하나의 문자열로 가져온다.

select lower(concat(first_name, ' ', last_name))
from employees;

날짜함수

-- 현재 날짜와 시간
select now();
select sysdate();
select current_timestamp();

-- 현재 날짜
select curdate();
select current_date();

select curtime();
select current_time();

-- 날짜 더하기
select now(), date_add(now(), interval 100 day);

select hire_date, date_add(hire_date, interval 100 day)
from employees;

-- 날짜 빼기
select now(), date_sub(now(), interval 100 day);

select hire_date, date_sub(hire_date, interval 100 day)
from employees;

-- 날짜
select now(), year(now());
select now(), month(now());
select now(), monthname(now());
select now(), dayname(now());
select now(), dayofweek(now());
select now(), weekday(now());
select now(), dayofyear(now());
select now(), week(now());
select from_days(1000);
select to_days(now());

-- 포맷
select now(), date_format(now(), '%Y년 %m월 %d일 %H시 %i분 %s초');

그룹합수

-- 사원의 수를 구한다.

select count(*) from employees;

-- 남자 사원의 수를 구한다.

select count(*)
from employees
where gender='M';

-- d005 부서 현재 근무하고 있는 사원들의 수를 가져온다.
-- to_date가 9999년 01월 01일인 사원이 현재 근무하고 있는 사원이다.
select count(*)
from dept_emp
where dept_no='d005' and to_date='9999-01-01';

-- 현재 받고 있는 급여의 총합을 구한다.

select sum(salary)
from salaries
where to_date='9999-01-01';

-- 현재 받고 있는 급여의 평균을 구한다.

select avg(salary)
from salaries
where to_date='9999-01-01';

-- 현재 받고 있는 급여의 최고액을 구한다.

select max(salary)
from salaries
where to_Date = '9999-01-01';

-- 현재 받고 있는 급여의 최저액을 구한다.

select min(salary)
from salaries
where to_date='9999-01-01';

group by, having

Group by 절을 이용하면 데이터를 그룹별로 나눠서 가져올 수 있다.
Having 절을 이용하면 원하는 그룹의 데이터만 가져올 수 있다.

-- 사원의 수를 성별로 구분하여 가져온다.

select gender, count(*) from employees
group by gender;

-- 각 부서에 근무하고 있는 사원의 수를 가져온다.

select dept_no, count(*) from dept_emp
where to_date = '9999-01-01'
group by dept_no;

-- 각 부서별 과거의 매니저의 수를 구한다.
select dept_no, count(*) from dept_manager
where to_date <> '9999-01-01'
group by dept_no;

-- 급여 수령 시작일별 급여 총합을 구한다.

select from_date, sum(salary)
from salaries
group by from_Date;

-- 급여 수령 시작일별 급여 평균을 구한다.

select from_date, avg(salary)
from salaries
group by from_date;

-- 급여 수령 시작일별 급여 최고액을 구한다.

select from_date, max(salary)
from salaries
group by from_date;

-- 급여 수령시작일별 급여 최저액을 구한다.

select from_date, min(salary)
from salaries
group by from_date;

-- 10만명 이상이 사용하고 있는 직함의 이름과 직원의 수를 가져온다.
select title, count(*)
from Titles
group by title
having count(*) >= 100000;

-- 5만명 이상이 근무하고 있는 부서의 부서 번호와 소속 사원들의 수를 가져온다.
select dept_no, count(*)
from dept_emp
group by dept_no
having count(*) >= 50000;

조인

여러 테이블에서 데이터를 동시에 가져올 때 join문을 사용한다.
join문은 다대다의 관계로 가져오기 때문에 잘못된 데이터를 포함한다. 이를 제거하기 위해 조건문을 설정해야 한다.

-- 사원들의 사원번호, 근무부서 번호, 근무부서 이름을 가져온다.
-- 사원번호를 기준으로 오름차순 정렬

select dept_emp.emp_no, dept_emp.dept_no, departments.dept_name
from departments, dept_emp;

select a2.emp_no, a2.dept_no, a1.dept_no, a1.dept_name
from departments a1, dept_emp a2
where a1.dept_no = a2.dept_no
order by a2.emp_no;

-- 각 사원들의 사원번호, first_name, 근무 부서 번호를 가져온다.
select a2.emp_no, a2.first_name, a1.dept_no
from dept_emp a1, employees a2
where a2.emp_no = a1.emp_no;

-- 각 사원들의 사원번호, first_name, 현재 받고 있는 급여를 가져온다.

select a1.emp_no, a1.first_name, a2.salary
from employees a1, salaries a2
where a1.emp_no = a2.emp_no
	and a2.to_Date = '9999-01-01';
    
-- 각 사원들의 사원번호, first_name, 근무 부서 이름을 가져온다.

select a1.emp_no, a1.first_name, a3.dept_name
from employees a1, dept_emp a2, departments a3
where a1.emp_no = a2.emp_no and a2.dept_no = a3.dept_no;

서브쿼리

set

MySQL 수정, 삭제, 관리

데잌터 베이스 및 테이블 생성
저장, 수정, 삭제
트랜잭션 관리

select * from test_table2;

delete from test_table2;
select * from test_table2;
rollback;
select * from test_table2;

delete from test_table2;
commit;
select * from test_table2;
rollback;
select * from test_table2;

insert into test_table2 (data1, data2, data3) values (100, '문자열1', 11.11);
insert into test_table2 (data1, data2, data3) values (200, '문자열1', 22.22);
insert into test_table2 (data1, data2, data3) values (300, '문자열1', 33.33);

commit;
select * from test_table2;

update test_table2 set data2 = '새로운문자열', data3=44.44 where data1=100;
savepoint aa;
delete from test_table2 where data1 = '100';
select * from test_table2;
rollback to aa;
select * from test_table2;

select @@autocommit;

commit;

select * from test_table2;
delete from test_table2;
select * from test_table2;
rollback;
select * from test_table2;

truncate test_table2;
rollback;
select * from test_table2;

테이블 변경하기

• rename 명령문을 사용하면 테이블의 이름을 변경할 수 있다.
• alter 명령문을 사용하면 테이블의 컬럼을 변경하거나 추가, 삭제할 수 있다.
• drop table 명령문을 사용하면 테이블을 삭제할 수 있다.

show tables;

rename table test_table1 to test_table3;

show tables;

desc test_table3;

alter table test_table3 modify data1 int(100);

desc test_table3;

alter table test_table3 change data1 data10 int(200);

desc test_table3;

alter table test_table3 change data10 data5 int(200);

desc test_table3;

alter table test_table3 add data4 int(20);

desc test_table3;

alter table test_table3 drop data4;

desc test_table3;

show tables;
drop table test_table3;
show tables;

제약조건

제약 조건을 사용하면 데이터의 무결성을 보장할 수 있다
primary key: 중복과 null을 허용하지 않는다.
foreign key: 지정된 primary key 컬럼의 값만 저장할 수 있다. null을 허용한다.
not null: 컬럼에 null을 허용하지 않는다
unique: null은 허용하지만 중복된 값은 허용하지 않는다.
default: 기본값을 설정한다.

create table test_table10(
data1 int not null
);

insert into test_table10 (data1) value (1);
insert into test_table10 (data1) value (2);
insert into test_table10 (data1) value (3);

select * from test_table10;

insert into test_table10 (data1) value (1);

select * from test_table10;

insert into test_table (data1) value (null);

create table test_table20(
data1 int,
data2 int not null,
constraint pk1 primary key(data1)
);

insert into test_table20 (data1, data2) values (10, 100);
insert into test_table20 (data1, data2) values (20, 200);
insert into test_table20 (data1, data2) values (30, 300);
select * from test_table20;

insert into test_table20 (data1, data2) values (10, 100);
insert into test_table20 (data1, data2) values (null, 100);
insert into test_table20 (data2) values (100);

create table test_table30(
data1 int,
data2 int,
constraint pk2 primary key(data1),
constraint fk2 foreign key(data2) references test_table20(data1)
);

select * from test_table20;
insert into test_table30 (data1, data2) values (1, 10);
insert into test_table30 (data1, data2) values (2, 20);
insert into test_table30 (data1, data2) values (3, 30);
select * from test_table30;

insert into test_table30 (data1, data2) values (4, 40);
insert into test_table30 (data1, data2) values (5, null);
insert into test_table30 (data1) values (6);
select * from test_table30;

create table test_table40(
data1 int,
data2 int not null,
constraint uk1 unique(data1),
constraint uk2 unique(data2)
);

insert into test_table40 (data1, data2) values (1, 10);
insert into test_table40 (data1, data2) values (2, 20);
select * from test_table40;

insert into test_table40 (data1, data2) values (1, 30);
insert into test_table40 (data1, data2) values (3, 10);
insert into test_table40 (data1, data2) values (null, 40);
insert into test_table40 (data1, data2) values (null, 50);
select * from test_table40;

insert into test_table40 (data1, data2) values (10, null);

create table test_table50(
data1 int not null,
data2 int not null,
constraint chk1 check (data1 > 10),
constraint chk2 check (data2 in (10, 20, 30))
);

insert into test_table50 (data1, data2) values (20, 30);
select * from test_table50;

insert into test_table50 (data1, data2) values (1, 100);

create table test_table60(
data1 int not null default 1,
data2 int not null default 10
);

insert into test_table60 (data1, data2) values (100, 200);
select * from test_table60;

insert into test_table60 (data1 data2) values (null, null);
insert into test_table60 (data1) values (1000);
insert into test_table60 (data2) values (2000);
select * from test_table60;

시퀀스

시퀀스를 사용하면 자동으로 증가되어 저장되는 컬럼을 만들 수 있다.
limit를 사용하면 정해진 범위에 해당하는 로우를 가져올 수 있다.

create table test_table100(
data1 int auto_increment,
data2 int not null,
data3 int not null,
constraint pk1 primary key(data1)
);

insert into test_table100 (data2, data3) values (100, 200);
insert into test_table100 (data2, data3) values (101, 201);
insert into test_table100 (data2, data3) values (102, 202);
select * from test_table100;

use employees;
select * from employees order by emp_no;
select * from employees order by emp_no limit 0, 10;
select * from employees order by emp_no limit 10, 10;

뷰

Create view 뷰이름 as select쿼리문
Drop view 뷰이름

복잡한 select 쿼리문을 이용해 뷰를 만들면 이후부터는 편하게 데이터를 조회할 수 있다.

create table test_table1000(
data1 int,
data2 int not null,
constraint pk1 primary key (data1)
);

create table test_table2000(
data1 int not null,
data3 int not null,
constraint fk1 foreign key (data1) references test_table1000(data1)
);

insert into test_table1000(data1, data2) values (1, 10);
insert into test_table1000(data1, data2) values (2, 20);
insert into test_table1000(data1, data2) values (3, 30);
select * from test_table1000;

insert into test_table2000(data1, data3) values (1, 100);
insert into test_table2000(data1, data3) values (2, 200);
insert into test_table2000(data1, data3) values (3, 300);
select * from test_table2000;

select a1.data1, a1.data2, a2.data3
from test_table1000 a1, test_table2000 a2
where a1.data1 = a2.data1;

create view test_view1
as
select a1.data1, a1.data2, a2.data3
from test_table1000 a1, test_table2000 a2
where a1.data1 = a2.data1;

select * from test_view1;

insert into test_table1000 (data1, data2) values (4, 40);
insert into test_table2000 (data1, data3) values (4, 404);
select * from test_view1;
create table test_table1000(
data1 int,
data2 int not null,
constraint pk1 primary key (data1)
);

create table test_table2000(
data1 int not null,
data3 int not null,
constraint fk1 foreign key (data1) references test_table1000(data1)
);

insert into test_table1000(data1, data2) values (1, 10);
insert into test_table1000(data1, data2) values (2, 20);
insert into test_table1000(data1, data2) values (3, 30);
select * from test_table1000;

insert into test_table2000(data1, data3) values (1, 100);
insert into test_table2000(data1, data3) values (2, 200);
insert into test_table2000(data1, data3) values (3, 300);
select * from test_table2000;

select a1.data1, a1.data2, a2.data3
from test_table1000 a1, test_table2000 a2
where a1.data1 = a2.data1;

create view test_view1
as
select a1.data1, a1.data2, a2.data3
from test_table1000 a1, test_table2000 a2
where a1.data1 = a2.data1;

select * from test_view1;

insert into test_table1000 (data1, data2) values (4, 40);
insert into test_table2000 (data1, data3) values (4, 404);
select * from test_view1;

drop view test_view1;
select * from test_view1;