<step45>'mysql_기초'

1) CREATE TABLE

create table employee(
	perno int primary key auto_increment,
	pname varchar(10) not null,
	job varchar(10) not null,
	hiredate varchar(20) not null,
	firedate varchar(20),
	sal int not null,
	bonus int,
	deptno int not null
);

 

2) ALTER 테이블명(컬럼명) values()

insert into employee(perno, pname, job, hiredate, firedate, sal, bonus, deptno) values('1','박청규','사원','2010-03-02','2012-08-31','880000',null,'10');
insert into employee(perno, pname, job, hiredate, firedate, sal, bonus, deptno) values('2','강원일','팀장','2010-08-02',null,'2000000','200000','10');
insert into employee(perno, pname, job, hiredate, firedate, sal, bonus, deptno) values('3','이봉수','부장','2010-01-02','2012-02-28','3000000','300000','10');
insert into employee(perno, pname, job, hiredate, firedate, sal, bonus, deptno) values('4','허태훈','과장','2010-05-02',null,'1500000',null,'10');
insert into employee(perno, pname, job, hiredate, firedate, sal, bonus, deptno) values('5','윤주옹','사원','2011-12-01',null,'1500000',null,'10');
insert into employee(perno, pname, job, hiredate, firedate, sal, bonus, deptno) values('6','김태환','대리','2010-02-01','2012-04-30','1700000',null,'20');
insert into employee(perno, pname, job, hiredate, firedate, sal, bonus, deptno) values('7','배현직','본부장','2010-01-02',null,'2500000','200000','20');
insert into employee(perno, pname, job, hiredate, firedate, sal, bonus, deptno) values('8','전민철','이사','2010-01-02',null,'5000000','500000','30');
insert into employee(perno, pname, job, hiredate, firedate, sal, bonus, deptno) values('9','심승진','CEO','2010-01-02',null,'10000000',null,'30');
insert into employee(perno, pname, job, hiredate, firedate, sal, bonus, deptno) values('10','강대원','과장','2010-04-02','2012-08-31','2200000','200000','30');
insert into employee(perno, pname, job, hiredate, firedate, sal, bonus, deptno) values('11','유성원','대리','2012-02-01',null,'1600000',null,'30');
insert into employee(perno, pname, job, hiredate, firedate, sal, bonus, deptno) values('12','안재연','과장','2012-02-01',null,'2000000','200000','40');
insert into employee(perno, pname, job, hiredate, firedate, sal, bonus, deptno) values('13','김진수','사원','2012-03-02',null,'880000',null,'40');
insert into employee(perno, pname, job, hiredate, firedate, sal, bonus, deptno) values('14','김성하','사원','2012-04-01',null,'1000000',null,'40');
insert into employee(perno, pname, job, hiredate, firedate, sal, bonus, deptno) values('15','황철원','대리','2011-12-01',null,'1500000','150000','40');
insert into employee(perno, pname, job, hiredate, firedate, sal, bonus, deptno) values('16','엄효인','사원','2010-12-15',null,'1200000',null,'40');

3) SELECT * from 테이블명

4) 문제01) 이름이 김씨인 사람의 모든 컬럼을 검색

select * from employee
where pname like '김%';

5) 문제02) 퇴사한 사람의 이름과 퇴사일을 검색

select pname, firedate from employee
where firedate is not null;

6) 문제03) 보너스를 받지 않는 사람의 이름을 검색

select pname from employee
where bonus is null;

7) 문제04) job이 사원인 사람의 사원번호, 이름, 직급순으로 출력(이름을 오름차순으로 정렬)

select perno, pname from employee
where job = '사원' order by pname asc;