group by절, having 절
2개의 구문 차이점 살펴보기 select userID AS '사용자 ' , sum(price*amount) as '총구매액' from buytbl where sum(price* amount) > 10000 group by userID; select userID AS '사용자 ' , sum(price*amount) as '총구매액' from buytbl group by userID having sum(price * amount) > 1000; -- 총구매액이 100원 이상인 사람을 합계순서데로 출력하는 쿼리 구문(내림차순,오름차순 옵션) select userID as '사용자' , sum(price * amount) as '총구매액 ' from buytbl where sum(price*amount) > 100 group by userID; -- 오류의 원인 살펴보기 -- select userID as '사용자' , sum(price * amount) as '총구매액 ' from buytbl; select userID as '사용자' , sum(price * amount) as '총구매액 ' from buytbl group by userID; select userID as '사용자' , sum(price * amount) as '총구매액 ' from buytbl group by userID having sum(price * amount) > 100; select userID as '사용자' , sum(price * amount) as '총구매액 ' from buytbl group by userID having sum(price * amount) > 100 order by sum(price*amount);
having은 where와 비슷한 개념으로 조건식을 걸지만 집계 함수에 대해서 조건을 제한하는 것, having절은 꼭 gruop by 뒤에 나와야 한다. 순서 바뀌면 오류발생
rollup 중간 합계 표현
select num, groupName, sum(price * amount) AS '비용' from buytbl group by groupName, num with rollup; select num, groupName, sum(price * amount) AS '비용' from buytbl group by groupName with rollup;
테이블 선택하면서 복사하기 명령어
create table buytbl2 (select * from buytbl); select * from buytbl2; create table buytbl3 (select userID, prodName from buytbl);
테이블 삭제
drop table ****;
테이블 수정
alert 명령어 : alter 테이블명 add column 컬럼명 타입;
- 기존 테이블에 신규 필드(컬럼) 추가하는 경우 : alter table usertbl add column model_type varchar(10) not null;
- 기존 테이블 컬럼의 타입을 변경 : alter table usertbl modify column model_type varchar(30);
- 기존 테이블 컬럼의 이름을 변경 : alter table usertbl add column model_type varchar(10) not null;
- 기존 테이블 컬럼을 삭제 : alter table usertbl add column model_type varchar(10) not null;
-- model_type라는 컬럼을 추가 -- alter table usertbl add column model_type varchar(10) not null; -- model_type 타입 변경 -- alter table usertbl modify column model_type varchar(30); -- 컬럼 이름 변경 -- alter table usertbl change column 원래이름 변경할이름; -- 컬럼 삭제 -- alter table usertbl drop column 컬럼이름; -- 타입을 변경하거나 삭제할때는 경고 문구가 뜬다. 기존에 데이터가 있을 경우 데이터의 손실이 올 수 있다고 경고함
- 테이블을 생성할때 int 형에서 unsigned 사용하게 되면 음수를 사용하지 않고 모두 양수로 전환된다. 그리고 mysql 8.0 이후부터는 int(숫자) 부분에서 숫자를 지원하지 않는다.
테이블 작성 실습 다시하기
테이블이 기존에 있으며 삭제하고 mytable란 이름으로 아이디,이름,모델넘버,시리즈, 프라이머리키 값을 생성한다.
id : 자동숫자 추가, 양수, 프라이머리키
name : 이름 50자
모델넘버 : 이름 15자
시리즈 : 이름 30자
+——————————————-+
id | name | medel_number | series
+——————————————-+
변경하기 실습
name : 글자길이 20변경
model_number : model_num 컬럼 이름 변경, 글자길이 10 변경
series : model_type 컬럼 이름 변경, 글자길이 10 변경
drop table mytable; create table mytable ( id int unsigned not null auto_increment, name varchar(50) not null, model_number varchar(15) not null, series varchar(30) not null, primary key ( id) ); desc mytable;
alter table mytable modify column name varchar(20) not null; alter table mytable change column modelnumber model_num varchar(10) not null; alter table mytable change column series model_type varchar(10) not null;
mysql 유저 추가하기 (DCL 명령어)
mysql 커멘드 모드에서 user 데이타베이스를 접근
testuser 아이디에 패스워드 1234로 유저 아이디 추가, 2가지 방법 차이 알기
생성명령, 패스워드 변경, 사용자삭제
-- 유저추가 mysql > create user 'testuser'@localhost identified by '1234'; - 패스워드 변경 mysql > set password for 'testuser'@localhost = '12345678'; - 패스워드가 변경된 것은 mysql 에서 빠져나와서 다시 로그인을 해서 확인한다. 사용자삭제 mysql > drop user 'testuser'@localhost;
권한 보기
grant 명령어로 아이디별로 데이터베이스와 테이블의 권한을 부여할 수 있다.
전체 부여는 *.*; 으로 처리
grant all on dbname.tableName;
mysql > grant all on *.*; mysql > show grants for 'testuser'@localhost; mysql > grant all on shopdb.usertbl to 'testuser'@localhost; mysql > grant select, update on shopdb.usertbl to 'testuser'@localhost; -- shopdb의 usertbl 테이블만 select, update 권한을 부여한다.
워크벤치에서 사용자 추가하기
administration 탭에서 users and Privileges 를 클릭해서 add acount 로 접근한다.
모든 권한 주기
부분 권한 주기
접근 권한 체크하기
팀장님,사장님 아이디 생성해서 접근 권한 연습하기
root 권한이 아니라 사장님,팀장님 권한으로 로그인
mysql connection 에서 마우스 우측 > edit connetion 에서 유저 네임을 변경해서 로그인한다.
팀장 : 데이터베이스 생성해보기 create, drop 로 생성과 삭제 테스트
사장 : 선택만 하기 select, delete from memberTBL; 테스트 해보기
일반직원 : 부분 권한 테스트해보기
