[MySQL] 공부 (DISTINCT, LIKE ,DESC, ... )

테이블 생성

CREATE TABLE dept ( 
	deptno               int  NOT NULL  AUTO_INCREMENT,
	dname                varchar(20)    ,
	loc                  varchar(20)    ,
	CONSTRAINT pk_dept PRIMARY KEY ( deptno )
 ) engine=InnoDB;

CREATE TABLE emp ( 	
	empno                int  NOT NULL  AUTO_INCREMENT,	
	ename                varchar(20)    ,
	job      varchar(20) ,
	mgr                  smallint    ,
	hiredate             date    ,
	sal                  numeric(7,2)    ,
	comm                 numeric(7,2)    ,
	deptno               int    ,
	CONSTRAINT pk_emp PRIMARY KEY ( empno )
 ) engine=InnoDB;

CREATE INDEX idx_emp ON emp ( deptno );

ALTER TABLE emp ADD CONSTRAINT fk_emp_dept FOREIGN KEY ( deptno ) REFERENCES dept( deptno ) ON DELETE NO ACTION ON UPDATE NO ACTION;

CREATE TABLE SALGRADE ( 
    GRADE TINYINT,
    LOSAL SMALLINT,
    HISAL SMALLINT 
);

insert into dept 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 emp values( 7839, 'KING', 'PRESIDENT', null, STR_TO_DATE ('17-11-1981','%d-%m-%Y'), 5000, null, 10);
insert into emp values( 7698, 'BLAKE', 'MANAGER', 7839, STR_TO_DATE('1-5-1981','%d-%m-%Y'), 2850, null, 30);
insert into emp values( 7782, 'CLARK', 'MANAGER', 7839, STR_TO_DATE('9-6-1981','%d-%m-%Y'), 2450, null, 10);
insert into emp values( 7566, 'JONES', 'MANAGER', 7839, STR_TO_DATE('2-4-1981','%d-%m-%Y'), 2975, null, 20);
insert into emp values( 7788, 'SCOTT', 'ANALYST', 7566, DATE_ADD(STR_TO_DATE('13-7-1987','%d-%m-%Y'),INTERVAL -85 DAY)  , 3000, null, 20);
insert into emp values( 7902, 'FORD', 'ANALYST', 7566, STR_TO_DATE('3-12-1981','%d-%m-%Y'), 3000, null, 20);
insert into emp values( 7369, 'SMITH', 'CLERK', 7902, STR_TO_DATE('17-12-1980','%d-%m-%Y'), 800, null, 20);
insert into emp values( 7499, 'ALLEN', 'SALESMAN', 7698, STR_TO_DATE('20-2-1981','%d-%m-%Y'), 1600, 300, 30);
insert into emp values( 7521, 'WARD', 'SALESMAN', 7698, STR_TO_DATE('22-2-1981','%d-%m-%Y'), 1250, 500, 30);
insert into emp values( 7654, 'MARTIN', 'SALESMAN', 7698, STR_TO_DATE('28-09-1981','%d-%m-%Y'), 1250, 1400, 30);
insert into emp values( 7844, 'TURNER', 'SALESMAN', 7698, STR_TO_DATE('8-9-1981','%d-%m-%Y'), 1500, 0, 30);
insert into emp values( 7876, 'ADAMS', 'CLERK', 7788, DATE_ADD(STR_TO_DATE('13-7-1987', '%d-%m-%Y'),INTERVAL -51 DAY), 1100, null, 20);
insert into emp values( 7900, 'JAMES', 'CLERK', 7698, STR_TO_DATE('3-12-1981','%d-%m-%Y'), 950, null, 30);
insert into emp values( 7934, 'MILLER', 'CLERK', 7782, STR_TO_DATE('23-1-1982','%d-%m-%Y'), 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 (5, 3001, 9999);

commit;

dept, emp, salgrade 테이블을 만들었다.

 

DECIMAL(M,D) = NUMERIC(M,D)

M은 소수 부분을 포함한 실수의 총 자릿수를 나타내며, 최댓값은 65이다.

D는 소수 부분의 자릿수를 나타내며, D가 0이면 소수 부분을 가지지 않는다.
commit; : 테이블에 데이터를 추가하고 데이터베이스에 적용할 때 마지막에 사용.

 

• (*) 는 전체를 나타는 것(테이블 내 모든 field를 가져오겠다) 저 자리에 따로 컬럼명을 입력하면 그 컬럼만 가져온다.

select * from emp;
select * from dept;
select * from salgrade;

 

• 산술 표현식
• 데이터가 중복되는 방식을 수정/계산alter

select ename, sal from emp;
select ename, sal+200 from emp;

-- NULL 값
-- NULL은 이용할 수도 없고, 할당되지도 않고, 알려져 있지도 않으며 적용 불가한 값을 의미한다.
-- NULL이란 0이나 공백하고 다르다.
-- NULL값을 포함한 산술 표현식의 결과는 전부 NULL이다.
-- Column에 데이터 값이 없으면 그 값 자체가 NULL 또는 NULL값을 포함하고 있다.

select ename, sal, comm, 12 * sal + comm from emp;

-- ISNULL 함수
-- NULL값을 어떤 특정한 값(실제값)으로 변환하는 함수
-- 사용가능한 데이터 타입은 날짜, 문자, 숫자이다.
-- IFNULL 함수 사용시 반환되는 값의 데이터 타입을 일치시켜야 한다.
-- IFNULL(expr1, expr2)
-- expr1 : NULL값을 포함하고 있는 column이나 표현식
-- expr2 : NULL 변환을 위한 목표 값

select ename, sal, comm, 12 * sal + ifnull(comm, 0) from emp;

 

• 컬럼명이 따옴표 사이 문자열로 바뀐다.

-- Alias (별명)
select ename, sal, comm, 12 * sal + ifnull(comm, 0) as '연봉 + 커미션' from emp; -- as는 생략가능

 

• 컬럼 내용물이 합쳐져서 하나의 컬럼에 나타난다.

-- 연결 연산자
select concat(ename, " ", job) as '사원'
from emp;

 

• 중복행 제거 (distinct)

select distinct deptno from emp;

 

• desc[ribe] : 테이블의 구조를 보여준다.

desc emp;
describe emp;

 

-- 비교연산자
= (같음),    >,    >=,    <,    <=,    <>(같지않다)

 

• column 이름 between A and B ( A 이상 B 이하 )

select ename as '이름', sal as '급여' from emp where sal between 1000 and 1500 order by ename;

 

• IN 연산자
• 목록에 있는 값을 출력하기 위해서 사용한다. OR을 줄여쓰는데 유용함
• 사원번호, 사원이름, 월급, 매니저(mgr)를 출력하기
• 단, 매니저의 코드가 7902, 7566, 7788에 해당하는 사원에 한해서

select ename, mgr from emp where mgr in(7902, 7566, 7788);

 

• LIKE 연산자
• 와일드 카드 검색을 위해서 사용한다.
• 검색 조건은 문자나 숫자 포함이 가능하다.
• %는 없거나 하나 이상의 문자와 대치되고,
• _는 하나의 문자와 대치된다.

select ename from emp where ename like 's%';
select ename from emp where ename like '%s%';
select ename from emp where ename like '_o%';
select ename from emp where ename like '--R%';

 

• NULL 찾기

select ename, mgr from emp where mgr is null;	-- NULL이면 출력
select ename, mgr from emp where mgr is not null;	-- NULL이 아니면 출력

 

 

• 우선순위 : 괄호 > 비교연산자 > NOT > AND > OR

select ename, job, sal
from emp
where job = 'salesman' or job = 'president' and sal > 1500; -- 5건

select ename, job, sal
from emp
where (job = 'salesman' or job = 'president') and sal > 1500; -- 2건

 

 

• 데이터 정렬
• ORDER BY 절을 사용하여 행을 정렬한다.
• ASC : 오름차순(생략가능)
• DESC : 내림차순
• ORDER BY 절은 SELECT문의 맨 뒤에 기술한다.

select ename, job, deptno, hiredate 
from emp
order by hiredate;

-- Column Alias에 의해서도 정렬된다.
select empno, ename, sal, sal*12 annual 
from emp 
order by annual desc;

-- 다중 열에 의한 정렬
-- 부서별, 월급 순으로 보여준다.
select ename, deptno, sal 
from emp 
order by deptno asc, sal desc;

-- SELECT절에 포함되지 않은 Column으로도 정렬이 가능하다.
select ename, deptno, sal 
from emp 
order by hiredate desc;

 

-- ALTER TABLE salgrade CHANGE 바꾸기전컬럼이름 바꾼후컬럼이름 컬럼타입;
ALTER TABLE GENDER CHANGE MAN WOMAN varchar(5);