view 와 프로시저 (procedure) - MySQL Workbench

use classicmodels;
show tables;
select * from customers;

select customerNumber, customerName, phone, country, city
from customers;

create view view_customers as
select customerNumber, customerName, phone, country, city
from customers
where country = 'USA';

select * from view_customers;

drop view view_customers;

desc orders;
desc customers;

select customerNumber, customerName from customers;
select orderNumber, orderDate from orders;

select customers.customerNumber, customerName, orderNumber, orderDate, country
from customers
inner join orders
on customers.customerNumber = orders.customerNumber
where country = 'USA';

select customers.customerName, orders.orderNumber, creditLimit, count(customerName)
from customers
left join orders
on customers.customerNumber = orders.customerNumber
group by customers.customerName
order by creditLimit desc;

#customerNumber customerName phone orderNumber orderDate
#left join

select customers.customerNumber, customerName, phone, orderNumber, orderDate
from customers
left join orders
on customers.customerNumber = orders.customerNumber;

desc orderdetails;
select productCode from orderdetails;

use test;
show tables;
desc members;

start transaction; #지금부터 임시작업을 하겠으니 commit을 하지 마라는 의미
#start transaction을 하고 실행했던것들은 autocommit이 1이었어도 롤백이 됨 (reconnect server를 하면 롤백되버림)

#autocommit을 꺼둔다면 start transaction 한것과 같다
set autocommit = 0;
set autocommit = 1;

select @@autocommit;
insert into members values('lim@gmail.com', '임꺽정', '332211', 31, '010-5543-3322');
insert into members values('jang@gmail.com', '장길산', '456789', '30', '010-3456-7890');
select * from members;
commit;
rollback;
delete from members where email = 'lim@gmail.com';
delete from members where email = 'jang@gmail.com';

#문장의 끝을 의미함 (구분 기호를 재 정의하는 명령어)
delimiter //

create procedure test()
	begin
		select * from members;
	end//
    
delimiter ;

call test();

use classicmodels;
select * from customers;

delimiter //

create procedure test2(n int)
	begin
		select customerName, creditLimit
        from customers
        where creditLimit > n;
    end//
    
delimiter ;


#프로시져 실행
call test2(50000);


use test;
select * from members;

drop procedure if exists insert_member;

delimiter //
create procedure insert_member(email varchar(45), name varchar(45), password varchar(45), age int, phone varchar(45))
	begin
		if age > 100 then
			select '나이를 다시 확인하세요.';
		else
			insert into members
			values (email, name, password, age, phone);
		end if;
	end//
delimiter ;

call insert_member('jang', '장상욱', '123456', 40, '010-6565-9989');
call insert_member('dulli', '둘리', '6547887', 5000, '010-6234-1754');
delete from members where email = 'jang';