[MySQL] 계정 생성, 권한 부여, 데이터 생성 및 삭제

1. 계정 생성
create user 계정명@'%' identified by '비밀번호';

2. 권한 부여
grant all on *.* to 계정명@'%' with grant option;

3. 사용자 확인
select user();

4. 데이터베이스 생성과 삭제
create database test01;
drop database test01;

5. 데이터베이스 사용 → test01 사용할거다 선언
use test01; 

6. 데이터베이스 사용 확인
select database();

7. 데이터베이스 안의 테이블 확인
show tables;

 

8. 테이블 생성

create table 테이블명;

 

9. 테이블 삭제

drop table 테이블명;

 

10. 테이블 확인

select * from 테이블명;

 

-- department 테이블 생성
create table department(
dno int ,
dname varchar(14) not null,
loc varchar(13) not null,
constraint pk_Dept primary key(dno)
);

desc department;
select * from department;

insert into department values(10,'ACCOUNTING','NEW YORK');
insert into department values(20,'RESEARCH','DALLAS');
insert into department values(30,'SALES','CHICAGO');
insert into department values(40,'OPERATIONS','BOSTON');

commit;
select * from department;

-- employee 테이블 생성
create table employee(
eno int,
ename varchar(10) not null,
job varchar(9) not null,
manager int,
hiredate date not null,
salary double not null,
commission double,
dno int,
constraint pk_emp primary key(eno),
constraint fk_dno foreign key(dno) references department(dno)
);

desc employee;

INSERT INTO EMPLOYEE VALUES
(7369,'SMITH','CLERK', 7902, '2010-12-17', 800,NULL,20);
INSERT INTO EMPLOYEE VALUES
(7499,'ALLEN','SALESMAN', 7698,'2011-2-20',1600,300,30);
INSERT INTO EMPLOYEE VALUES
(7521,'WARD','SALESMAN',  7698,'2011-2-22',1250,500,30);
INSERT INTO EMPLOYEE VALUES
(7566,'JONES','MANAGER',  7839,'2011-4-2',2975,NULL,20);
INSERT INTO EMPLOYEE VALUES
(7654,'MARTIN','SALESMAN',7698,'2011-9-28', 1250,1400,30);
INSERT INTO EMPLOYEE VALUES
(7698,'BLAKE','MANAGER',  7839,'2011-5-1',2850,NULL,30);
INSERT INTO EMPLOYEE VALUES
(7782,'CLARK','MANAGER',  7839,'2011-6-9',2450,NULL,10);
INSERT INTO EMPLOYEE VALUES
(7788,'SCOTT','ANALYST',  7566,'2017-07-13',3000,NULL,20);
INSERT INTO EMPLOYEE VALUES
(7839,'KING','PRESIDENT', NULL,'2011-11-17',5000,NULL,10);
INSERT INTO EMPLOYEE VALUES
(7844,'TURNER','SALESMAN',7698,'2011-9-8',1500,0,30);
INSERT INTO EMPLOYEE VALUES
(7876,'ADAMS','CLERK',    7788,'2017-07-13',1100,NULL,20);
INSERT INTO EMPLOYEE VALUES
(7900,'JAMES','CLERK',    7698,'2011-12-3',950,NULL,30);
INSERT INTO EMPLOYEE VALUES
(7902,'FORD','ANALYST',   7566,'2011-12-3',3000,NULL,20);
INSERT INTO EMPLOYEE VALUES
(7934,'MILLER','CLERK',   7782,'2012-1-23',1300,NULL,10);

commit;
select * from employee;