수업일지 23일차 (23/10/23) - MySQL

---

 

수업내용 정리 (MySQL)

 

0. SQL(구조적 질의어)

1. DDL(데이터 정의어): create, alter, drop, (truncate/잘안씀) - 테이블, 계정, 뷰 등
2. DQL(데이터 질의어): select ...
3. DML(데이터 조작어): (select/빼는경우가 많음), insert, update, delete - 테이블 또는 뷰 데이터
4. DCL(데이터 제어어): grant(권한부여), revoke(권한회수), commit(작업완료), rollback(작업취소)

 

 

1. Connection 추가

• MySQL Workbench를 켰을때 중앙의 MySQL connections 바로 옆에 있는 + 버튼을 누른다.

 

• Connection Name: Connection 이름 설정
• Username: 해당 Connection을 사용할 유저 이름 설정(이미 있는 유저라면 해당 이름으로 작성)
• Password: 새로운 유저라면 Store in Vault ... 을 눌러 비밀번호를 설정하고, 있는 유저라면 넘어간다.

 

• 추가 완료!
• 해당 Connection을 누르면 비밀번호를 입력한 후 들어갈 수 있다.

 

 

2. 계정

 2-1) 계정 생성 ㄱ 

• 관리자 계정(root)에서 일반 계정 생성!

 

create user 계정이름 identified by '비밀번호';
create user 'icia' identified by '1234';
create user 'icia'@'%' identified by '1234';	-- 위의 코드와 동일
create user 'icia2'@'localhost' identified by '1234';

• @'%': 내/외부 접속이 가능하다. 기본값이므로 생략이 가능하다.
• @'localhost': 내부 접속만 가능하다.

 

 2-2) 비밀번호 변경 ㄱ 

alter user 'icia' identified by '1111';

• icia 계정에 대한 비밀번호를 변경한다.

 

 2-3) 계정 삭제 ㄱ 

drop user 계정이름;
drop user 'icia2'; -- 삭제 불가
drop user 'icia2'@'localhost';

• 계정 이름 뒤에는 기본값인 @'%'가 있기 때문에 @'localhost'로 만든 계정은 삭제할 때도 꼭 @'localhost'를 붙여야 삭제가 가능하다.

 

 2-4) 권한 설정 ㄱ 

grant all privileges on *.*to 'icia';	-- 모든 테이블에 관한 권한
grant all privileges on mydb.*to 'icia';	-- mydb 테이블에 관한 권한

• *. : 모든 테이블
• mydb. : mydb 테이블
• grant all privileges on ~: ~에 관한 모든 권한을 설정

 

revoke all privileges on mydb.*from 'icia';	-- mydb 테이블에 관한 권한 회수

• revoke all privileges on ~: ~에 관한 모든 권한을 회수

 

 

3. database/schemas 생성 (관리자/root 계정에서만 가능)

create schema mydb;
create database teamdb;
show databases;

• 위 두 줄은 똑같이 database/schemas를 생성한 것이다.
• 아래의 show databases는 생성한 database를 보여주는 코드다.

 

 

4. 테이블 (icia 계정으로 진행)

 4-1) 사용할 db 선택 ㄱ 

use mysql;	-- 권한 없어서 에러
use mydb;	-- 사용할 db 선택

• 권한이 없는 것을 선택하면 에러가 나고 선택되지 않는다.

 

 4-2) 테이블 생성 ㄱ 

drop table if exists dept;

• 생성하기 전에 선택한 db에 dept라는 테이블이 있다면 삭제한다.

 

create table dept(
	deptno tinyint unsigned,
	dname varchar(10),
	loc varchar(10),
	constraint pk_dept primary key(deptno));

• deptno, dname, loc: 칼럼명을 설정한 것이다.
• tinyint, varchar: 타입인데 아직 배우지 않았다.
• primary key: 해당 테이블의 기본적인 값. 다른 항목과 중복값을 가질 수 없고, null 값을 가질 수 없다. 보통 테이블의 첫 칼럼이 pk에 배치된다. 테이블의 대표값이 아님!

 

 

create table emp(
	empno INT,  -- 사원번호
	ename VARCHAR(10),   -- 사원이름
	job VARCHAR(9),      -- 직급
	mgr INT,             -- 상관번호
	hiredate DATE,       -- 입사일
	sal INT,  -- 급여
	comm INT,            -- 보너스
	deptno tinyint unsigned, -- references DEPT(DEPTNO), 소속부서번호
    CONSTRAINT pk_emp PRIMARY KEY(empno), 
    CONSTRAINT fk_deptno foreign key(deptno) references dept(deptno)
 );

drop table if exists salgrade;
create table salgrade(
	grade tinyint primary key, -- _-128
    losal int,
    hisal int);

• 사원, 급여 테이블 추가

 

 4-3) 테이블 내 검색 ㄱ 

select deptno, dname, loc from dept;
select * from dept;

• select (검색할 칼럼명) from (검색할 db);
• 검색할 칼럼명에 *(별표/애스터리스크)을 작성 시 모든 칼럼이 표시된다.

 

select ename, sal, 1, 'Hello', null from emp;

• 원하는 칼럼(열)만 선택해서 검색
• 1, 'Hello'는 없는 칼럼이므로 그냥 옆에 추가된 상태로 보인다.

 

select * from emp
where deptno=30;

• 원하는 row(행)만 골라서 검색
• where에 조건을 적는다.

 

select * from emp
order by empno; -- order by절 정렬기준(오름차)
-- order by empno desc; -- order by절 정렬기준(내림차)

• 원하는 순서대로 데이터 검색

 

select*from emp limit 5;
select*from emp limit 0,5;	-- 0부터 5개까지	-- 페이징 1,2,3,4,5
select*from emp limit 5,5;	-- 5부터 5개까지

• 원하는 만큼 데이터 가져오기
• limit (시작인덱스), (가져올 개수)

 

 4-4) 데이터 추가(삽입) ㄱ 

insert into dept(deptno, dname, loc) values(10, 'accounting', 'NEW YORK');
insert into dept values(20, 'research', 'DALLAS');
insert into dept values(30, 'sales', 'CHICAGO');
insert into dept values(40, 'operations', 'BOSTON');

• insert into (db(칼럼)) values(값);
• 여러줄을 실행할 때에는 드래그해서 윗쪽에 번개모양을 누르면 된다.

 

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

insert into salgrade values (1,700,1200);
insert into salgrade values (2,1201,1400);
insert into salgrade values (3,1401,2000);
insert into salgrade values (4,2001,3000);
insert into salgrade values (1,3001,9999);

• 사원, 급여 테이블에 데이터 추가

 

 

5. 사칙연산

• 더하기(+), 빼기(-), 곱하기(*), 나누기(/)는 그대로 사용한다.

 

select mod(10,3);

• 나머지
• MySQL에서는 %를 쓸 수 있지만 Oracle에서는 사용할 수 없기 때문에 mod를 사용한다.

 

select 5-2.5 from dual;

• MySQL에서는 뒤의 from dual을 생략해도 되지만 Oracle에서는 안 된다.

 

select 'ABC' + 3;		-- 3
select '1' + '002';		-- 3

• 문자열에 사칙연산을 하면 0으로 인식하지만, 문자열인데 숫자만 있을 경우엔 알아서 숫자로 인식한다.

 

select true, false;			-- 1, 0
select !true, not 1, !false, not 0;	-- 0, 0, 1, 1

• true: 1
• false: 0

select true is true, true is not true;	-- true는 true다, true는 false다
select true = true, true != true;	-- true는 true다, true는 false다

• is연산자: ==       null 값 비교일 경우 사용한다.
• is not 연산자: !=

 

-- 부서번호가 30이면서 급여가 2000 이상인 사원 검색
select*from emp where deptno = 30 and sal >= 2000;
-- 전체사원중에서 급여가 1000에서 2000인 사원
select*from emp where sal >= 1000 and sal <= 2000;
-- 부서번호가 10 또는 20
select*from emp where deptno = 10 or deptno=20;

• 논리연산자
• &&, and: 논리곱 (모두 해당하는 경우)
• ||, or: 논리합 (하나라도 해당하는 경우)

select*from emp where sal between 1000 and 2000;
select*from emp where deptno in(10,20);

• between: between a and b / a에서 b 사이의 값 and 대응
• in: in(a,b) / a 또는 b의 값 or 대응

 

 ex ㄱ 

-- 사원번호가 홀수인 사원의 사원번호, 사원이름, 직급 검색
select empno,job,ename from emp
where mod(empno,2)=1;

-- 커미션이 null인 사원, null이 아닌 사원
select ename, comm from emp
where comm is null;
select ename, comm from emp
where comm is true;		-- is not null

-- 부서번호가 20번인 사원
select * from emp where deptno=20;
-- 부서번호가 20번이 아닌 사원
select * from emp
-- where !(deptno = 20);
-- where not(deptno != 20);
-- where deptno != 20;
where deptno <> 20;

 

 

 

 

---

 

질문한 내용이나 어려웠던 점 메모

1. 단축키

• 자바를 하다가 와서 그런지 한 줄 삭제를 하려고 ctrl+D를 눌러서 선택된 줄(혹은 드래그한것) 복사 단축키를 알게 되었다..자꾸 복사하고 있어서 ctrl+Z를 계속 누르고 있었다.

 

---

 

전체 피드백

• 오늘 배운 것은 비교적 간단한 것들이라 그렇게 어렵지만은 않았다!
• MySQL을 하고 다시 Java로 돌아갈 것이라고 하셨다(Java에서 하던 PhoneBook이라던가 MySQL에 연동하는 식). 그전에 잊지 않도록 짬나는 시간에 복습하는거 잊지 말기!

 

---

 

 + 카테고리 설정 잊지 말기 ㅠ