2023-04-05 SQL

sql 명령어 깔끔하게 정리해주는 사이트 
https://www.dpriver.com/pp/sqlformat.htm

create table pivottest (
username char(3),
season charpivottest(2),
amount int);

insert into pivottest values
('김범수', '겨울', 10), ('윤종신', '여름', 15),('김범수', '가을', 25),
('김범수', '봄', 37), ('윤종신', '겨울', 40),('김범수', '여름', 14),
('김범수', '겨울', 22), ('윤종신', '여름', 64);
select *from pivottest;
-- 피벗 형식으로 조회하기 : sum(),if() ,groupby
select username,
sum(if(season ='봄',amount,0 ))as 봄,
sum(if(season ='여름',amount,0 ))as 여름,
sum(if(season ='가을',amount,0 ))as 가을,
sum(if(season ='겨울',amount,0 ))as 겨울,
sum(amount) as 합계
from pivottest
group by username;

select name,height from usertbl
where height >=173;


-- json 타입으로 자료 조회 
select json_object('name',name,'height',height)as json_value
from usertbl
where height >=173;

-- join 하기 merge보다 다양하게 사용할수있다. 
-- crossjoin outer inner self (full(?))이있다.1

-- inner join: 두 개의 테이블의 같은 칼럼을 =로 조인하는 경우 
#문) 사용자 아이디, 사용자명, 상품명 ,수량을 검색
방법1
select u.userID 사용자아이디,name 사용자명,prodname 상품명,amount 수량
from usertbl u inner join buytbl b on u.userID = b.userid
where height>=173;

방법2
select u.userID 사용자아이디,name 사용자명,prodname 상품명,amount 수량
from usertbl u join buytbl b on u.userID = b.userid
where height>=173;

방법3
select u.userID 사용자아이디,name 사용자명,prodname 상품명,amount 수량
from usertbl u join buytbl b 
where u.userID = b.userid
and height>=173;

#문) employees 스키마에서 사원번호, 사원명, 부서명을 출력 현재 근무부소명을
(employees, dept_emp, department, dept_emp.to_date =9999-01-01 이면 현재 부서
-- dept_emp: emp_no 그룹을 만들고, to_date가
가장 큰 값의 부서번호를 찾아서 부서명 검색

쌤ver.
select emp.emp_no, concat(first_name, last_name) 사원명, dept.dept_no,
       dept.dept_name
from employees emp join dept_emp on emp.emp_no = dept_emp.emp_no
        join departments dept on dept_emp.dept_no = dept.dept_no
where first_name like 'M%'
and  dept.dept_no = 'd003'
limit 100;


SELECT t.emp_no, dept_emp.dept_no
FROM (
SELECT emp_no, MAX(to_date) AS max_date 
FROM dept_emp
GROUP BY emp_no
) t, dept_emp
WHERE dept_emp.emp_no = t.emp_no
AND dept_emp.to_date = t.max_date;

마이ver.
SELECT employees.emp_no AS 사번, CONCAT(employees.first_name, ' ', employees.last_name) AS 사원명, 
departments.dept_no AS 부서번호, departments.dept_name AS 부서명, dept_emp.to_date AS 전출일지
FROM employees
INNER JOIN dept_emp ON employees.emp_no = dept_emp.emp_no
INNER JOIN departments ON dept_emp.dept_no = departments.dept_no
WHERE  dept_emp.to_date = '9999-01-01';

-- outer join : left outer join, right outer join, full outer join
select u.userid, u.name, b.prodname, u.addr, concat(mobile1, mobile2) mobile
from usertbl u join  buytbl b on u.userid = b.userid;

-- left join # 이걸안쓰면 데이터가 없어지기때문에 nan 데이터때문에 이걸쓴다.
CREATE TABLE stdtbl 
( stdName    VARCHAR(10) NOT NULL PRIMARY KEY,
  addr      CHAR(4) NOT NULL
);
CREATE TABLE clubtbl 
( clubName    VARCHAR(10) NOT NULL PRIMARY KEY,
  roomNo    CHAR(4) NOT NULL
);
CREATE TABLE stdclubtbl
(  num int AUTO_INCREMENT NOT NULL PRIMARY KEY, 
   stdName    VARCHAR(10) NOT NULL,
   clubName    VARCHAR(10) NOT NULL,
FOREIGN KEY(stdName) REFERENCES stdtbl(stdName),
FOREIGN KEY(clubName) REFERENCES clubtbl(clubName)
);
INSERT INTO stdtbl VALUES ('김범수','경남'), ('성시경','서울'), ('조용필','경기'), ('은지원','경북'),('바비킴','서울');
INSERT INTO clubtbl VALUES ('수영','101호'), ('바둑','102호'), ('축구','103호'), ('봉사','104호');
INSERT INTO stdclubtbl VALUES (NULL, '김범수','바둑'), (NULL,'김범수','축구'), (NULL,'조용필','축구'), (NULL,'은지원','축구'), (NULL,'은지원','봉사'), (NULL,'바비킴','봉사');

select s.stdname 학생명, addr 주소, c.clubname 동아리명, roomno 방번호
from stdtbl s left join stdclubtbl st on s.stdname = st.stdname
  left join clubtbl c on st.clubname = c.clubname;

-- cross join # n*m 의 결과 출력 
select u.name ,b.proname
from usertbl u cross join buytbl b;
select count(*)
from employees.employees cross join employees.titles;

-- self join: 같은 테이블을 여러 번 조인
문)  사원명, 관리자명, 사원의 구내번호, 관리자의 구내번호
select e.emp 사원명, e.manager 관리자명, 
         e.emptel "사원의 구내번호", m.emptel "관리자의 구내번호"
from emptbl e join emptbl m on e.manager = m.emp;

--  관리자가 없는 사원의 정보도 출력
select e.emp 사원명, ifnull(e.manager,'없음') 관리자명, 
         e.emptel "사원의 구내번호", ifnull(m.emptel,'없음') "관리자의 구내번호"
from emptbl e left join emptbl m on e.manager = m.emp

-- union, union all, not in , in
-- 학생의 이름, 주소, 클럽명, 룸번호를 출력
select stdname  학생명, addr 
from stdtbl
union all
select clubname, roomno   룸번호
from clubtbl;

select s.stdname 학생명, addr 주소, c.clubname 동아리명, roomno  방번호
from stdtbl s left join stdclubtbl st on s.stdname = st.stdname 
      left join  clubtbl c on st.clubname = c.clubname
union 
select s.stdname 학생명, addr 주소, c.clubname 동아리명, roomno  방번호
from stdtbl s right join stdclubtbl st on s.stdname = st.stdname 
      right join  clubtbl c on st.clubname = c.clubname; 


#########################################33
DML: select,insert,update,delete
TCL: commit, rollback
DDL: object 관련 명령어 #자동 커밋
object : table,index,view,sequence,synonym,procedure,function,trigger

##########################################################
drop table if exists buytbl;
create table buytbl
(num int auto_increment not null primary key,
userid varchar(8) not null ,
prodname varchar(10) not null,
groupname char(4) null,
price int not null,
amount smallint not null,
foreign key(userid)references usertbl(userid));

INSERT INTO usertbl VALUES('LSG', '이승기', '1987', '서울',  '1111111', 182, '2008-8-8',null);
INSERT INTO usertbl VALUES('KBS', '김범수', '1979', '경남', '2222222', 173, '2012-4-4',null);
INSERT INTO usertbl VALUES('KKH', '김경호', '1971', '전남', '3333333', 177, '2007-7-7',null);

INSERT INTO buytbl VALUES(NULL, 'KBS', '운동화', NULL, 30, 2);
INSERT INTO buytbl VALUES(NULL, 'KBS', '노트북', '전자', 1000, 1);
INSERT INTO buytbl VALUES(NULL, 'JYP', '모니터', '전자', 200, 1); -- error 발생 부모

-- table 생성 : primary key ,foreign key
-- table 생성 : primary key ,foreign key

-- 제약조건 추가 userid를 pk로 설정 
alter table usertbl
add constraint pk_usertbl_userid primary key (userid);

-- 제약조건 삭제:
alter table buytbl
drop constraint buytbl_ibfk_1;

alter table usertbl\\
add column test varchar; #컬럼추가
drop column test ; # 컬럼삭제

alter table usertbl
modify column test char(10) not null #컬럼 속성 변경

use mysql;

-- 사용자 추가
create user mypj;
create user mypj@localhost identified by '1234';

select host,user from user;

-- mypj에 접근할수있는 권한 부여 #모든권한 
grant all privileges on mypj.* to mypj;
flush privileges; #바로 메모리에 부여시켜라 

-- 권한 확인

show grants for mypj@localhost;
grant select on employees.employees to mypj;
revoke select on employees.employees from mypj;

SHOW TABLES;
SHOW COLUMNS FROM member;
SHOW INDEX FROM product;
SHOW GRANTS FOR 'myuser'@'localhost';

 

create database tt;
use tt;
create table member(
member_id varchar(10) not null primary key, 
member_name varchar(20) not null, 
member_passwd varchar(20) not null, 
member_email varchar(40), 
member_hp varchar(16), 
member_indate date default (current_date));
create table product(
  product_id int auto_increment not null primary key,
  product_name varchar(50) not null,
  product_price int,
  product_detail varchar(100)
);

create table `order` (
  order_id int auto_increment not null primary key,
  order_date date default (current_date),
  order_price int not null,
  member_id varchar(10),
  foreign key (member_id) references member(member_id)
);
create table orderdetail (
  order_id int,
  foreign key (order_id) references `order`(order_id),
  product_id int,
  foreign key (product_id) references product(product_id),
  amount int not null,
  money int not null
);

alter table orderdetail add constraint memberadd primary key (order_id, product_id);