MySQL

오늘은 DB 첫 수업이다

 

MySQL에 대해 배웠다

 

Database

1. 데이터 베이스 배경지식
   1. DBMS(DateBase Mamagement System)
      1. 종류:oracle,mysql,mariaDB,firebase,mongoDB…
   2. 목적
      1. CRUD
      2. C(Create):데이터 저장
      3. R(Read):데이터 조회
      4. U(Update):데이터 수정
      5. D(Delete):데이터 삭제
   3. SQL(Structured Query Language)
      1. DBMS를 사용 하는 문법
      2. 구조화된 질의어
      3. 사람 → DBMS에게 SQL 문법으로 질문을 하고 그에 대한 결과를 받음
   4. SQL 문법 분류
      1. DDL(Date Definition Language, 데이터 정의어)
         1. 데이터베이스, 사용자,테이블,뷰 등을 생성,수정,삭제할때 사용하는 문법
         2. create,alter,drop
      2. DML(Date Manipulation Languge, 데이터 조작어)
         1. 테이블에 데이터를 CRUD 할 때 사용하는 문법
         2. insert(C) , select(R), update(U), delete(D)
      3. DCL((Date Control Languge 데이터 제어어)
         1. 사용자 권한 관리, 트랜잭션관리에 사용하는 문법
         2. grant,revoke,commit,rollback
   5. 테이블
      1. 데이터가 저장되는 공간
      2. 저장될 데이터를 컬럼(column,열)으로 구분하여 저장
      3. 학생 정보를 저장해야 하는 테이블 이라면
         1. 관리 번호(id),학번(studentNumber) 이름(studentName) 전화번호(studentMobile)학과(studentMajor) 등의 정보
         
         

         id	studentNumber	studentName	studentMobile	studentMajor

         1. 계정
         2. 데이터 베이스는 계정이 있고 계정에 따라 사용 할 수 있는 권한을 분리 시킴
         3. 모든 권한을 가진 계정은 관리지 root
         4. 관리자는 계정을 생성하고 삭제하는 등 모든 권한을 가짐
         5. 계정생성
         6.  

create user[계정이름] idenified by [비밀번호];
grant [권한종류] privileges [db이름] to [계정이름];

1. database(table space)
   1. 데이터가 저장되는 공간인 테이블(table)이 모여있는 공간
   2. mysql은 반드시 데이터 베이스를 연결한 뒤 그 데이터 베이스 안에서 테이블을 만들고 데이터를 저장해야 함
   3. database 생성

create database [db 이름]

1. 테이블 정의 문법
   1. 테이블 생성

create table [테이블 이름] (
[컬럼명1] 타입,
[컬럼명2] 타입,
[컬럼명1] 타입,
);

1. 타입
   1. int: 정수(자바 int와 매칭)
   2. bigint:정수 (자바의 long과 매칭)
   3. varchar:문자열(자바의 String과 매칭)
      1. varchar는 정의할 때 크기도 함께 지정함
      2. 5글자 가 들어가는 컬럼 이라면:varchar(5)
   4. date:날짜형

<예문>

db 생성

-- db생성 
create database db_ex1;	
-- 사용자 생성
create user user_user1@localhost identified by '1234';
-- 사용자에게 권한부여
-- db_ex1.*: db_ex1 dp 에 만들어진 모든 테이블
grant all privileges on db_ex1.* to user_user1@localhost;

create database db_ex2;
create user user_user2@localhost identified by '1234';
grant all privileges on db_ex2.* to user_user2@localhost;

create database db_dbclass;
create user user_dbclass@localhost identified by '1234';
grant all privileges on db_dbclass.* to user_dbclass@localhost;

 

-테이블 생성-

create table table1(
col1 int,
col2 varchar(5)
);

-- 테이블 조회
select * from table1;

-- 테이블에 데이터 저장
insert into table1(col1, col2) values(1, '안녕');
insert into table1(col1, col2) values(2, '화요일');
-- 정의한 컬럼에 맞지 않는 데이터가 들어오면 에러
insert into table1(col1, col2) values('aa', '화요일');
-- 정의한 크기와 맞지않는 데이터가 들어오면 에러
insert into table1(col1, col2) values(4, '화요일입니다');
insert into table1(col1, col2) values(5, 'abcd.');

 

col 이라는 컬럼을 만들고 타입을 선언해준뒤

 

select * from 으로 테이블을 조회할수있다

 

insert into 로 테이블 컬럼에 정보를 저장하고 불러올수있다

 

<예제1>

학생  관리번호(id) bigint,
학번 ( studentNumber),
이름 (studentName),
전화번호 (stundetMobile),
학과 (studentMajor) 정보를 저장하는 studetn_table 생성

 

create table student_table(
id bigint,
studentNumber varchar(8),
studentName varchar(20),
stundetMobile varchar(20),
studentMajor varchar(30)
);
insert into student_table(id,studentNumber ,studentName,stundetMobile,studentMajor) values (1,'12345','학생1','01011111111','컴공과');
 
 
 select * from student_table;

 

위에 예문을 응용해서 학생의 정보를 입력 해주었다

 

<활용>

가상의 회사에 사원들의 데이터

create table dept (
    deptno int,
    dname varchar(14),
    loc varchar(13)
);

create table emp (
    empno int,
    ename varchar(10),
    job varchar(9),
    mgr int,
    hiredate date,
    sal int,
    comm int,
    deptno int
);

create table bonus (
    ename varchar(10),
    job varchar(9),
    sal int,
    comm int
);


create table salgrade (
    grade int,
    losal int,
    hisal int
);
    
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 (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 (7566,'JONES','MANAGER',7839,str_to_date('2-4-1981','%d-%m-%Y'),2975,NULL,20);
insert into emp values (7654,'MARTIN','SALESMAN',7698,str_to_date('28-9-1981','%d-%m-%Y'),1250,1400,30);
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 (7788,'SCOTT','ANALYST',7566,str_to_date('13-7-87','%d-%m-%Y'),3000,NULL,20);
insert into emp values (7839,'KING','PRESIDENT',NULL,str_to_date('17-11-1981','%d-%m-%Y'),5000,NULL,10);
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,str_to_date('13-7-87','%d-%m-%Y'),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 (7902,'FORD','ANALYST',7566,str_to_date('3-12-1981','%d-%m-%Y'),3000,NULL,20);
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);

select * from emp; 
select * from bonus; -- 조회결과 없음
select * from salgrade;
select * from dept;

 

-emp 테이블 조회-

-- emp 테이블 전체 컬럼 조회
select * from emp;
-- emp 테이블의 empno, ename 컬럼만 조회
select empno , ename from emp;
-- select SMITH from emp; -- x
select job from emp;
-- 중복된 값은 한 번만 조회
select distinct job from emp;

 

-오름차순 (asc) , 내림차순(desc)-

-- 사원 이름 조회( 오름차순 )
select * from emp order by ename asc;
-- 사원 이름 조회 ( 내림차순 )
select * from emp order by ename desc;
-- 급여(sal) 기준 오름차순 정렬
select * from emp order by sal asc;
-- 급여(sal) 기준 내림차순 정렬
select * from emp order by sal desc;
-- 입사일(hiredate) 기준 오름차순 정렬
select * from emp order by hiredate asc;
-- 입사일(hiredate) 기준 내림차순 정렬
select * from emp order by hiredate desc;
-- 정렬 기준이 두가지라면 콤마( , ) 로 구분
-- 입사일 기준 내림차순 정렬하고 입사일이 같다면 이름 기준 오름차순 정렬
select * from emp order by hiredate desc , ename asc;

오름차순 일때는 asc 내림차순일때는 desc를 사용한다

 

-- 조건: 특정 데이터를 기준으로 조회하자고 할 때

 

<where>

-- 직급(job) 이 salesman인 사원 조회
select * from emp where job = 'salesman';
-- 직급(job) 이 manager인 사원 조회
select * from emp where job = 'manager';
-- 부서번호(deptno)가 10인 사원 조회
select * from emp where deptno = 10;
-- 이름(ename)이 james인 사원 조회
select * from emp where ename = 'james';
-- 사번(empno)이 7654인 사원 조회
select * from emp where empno = 7654;

emp 테이블에서 where 를 사용하면 특정 데이터를 기준으로 조회 할수있다

 

-- 조건이 2가지 이상
-- 직급이 manager 이고 부서번호가 20인 사원

 

select * from emp where job ='mamager'and deptno = 20;
-- 직급이 salesman 이거나 clerk 인 사원
select * from emp where job ='selesman'or job = 'clerk';
-- 급여 ( sal ) 가 3000 이상인 사원
select * from emp where sal >= 3000;
-- 급여가 1000이상 3000 이하인 사원
select * from emp where sal >=1000 and sal <= 3000;
select * from emp where sal between 1000 and 3000;
-- 급여가 1000 미만이거나 3000 초과
select * from emp where sal not between 1000 and 3000;
-- 급여가 3000 이하인 사원 조회결과를 sal 기준으로 오름차순 정렬
select * from emp where sal <=3000 order by sal asc;
-- 직급이 salesman이 아닌 사원
select * from emp where job != 'salesmam';
select * from emp where not job = 'salesman';
-- 입사일자(hiredate) 가 81년 4월 1일 이후인 사원
select * from emp where hiredate > '1981-04-01';
select * from emp where hiredate > '1981-04-01' order by hiredate asc;
-- str_to_date(): 문자를 날짜형으로 변환하는 함수
select * from emp where hiredate > str_to_date('1981-04-01','%Y-%m-%d');

 

조건이 두 가지 이상일 경우 and , or 를 사용하고  오름차순 혹은 내림 차순으로 정렬을 할때에는 ordet by를 사용해준다

 

-- like 연산자

 

select * from emp where ename like 'm%'; -- martin , miller
select * from emp where ename like '%n'; -- allen, martin
select * from emp where ename like '%m%'; -- smith , martin, james, miller
select * from emp where ename like '_m%'; -- SMITH
select * from emp where ename like '__m%'; -- JAMES

 

m% 를 사용하게 될 경우 맨 앞글자가 m 을 포함한 값을 찾아서 보여준다

%n 을  사용하면 맨 뒷글자가 n인 정보를 불러와주게 되고

%m% 는 m이라는 글자를 포함한 모든 데이터를 조회할수 있고

_는 글자수 한개를 사용하게 되어 _m%는 두번째 글자가 m인 데이터를 조회할수있다

 

-- 함수
-- sum(), avg(),max(),min(),count(),round()

select sum(sal) from emp;
select sum(sal) as '급여 총합' from emp;
-- salesman 급여 총합
select sum(sal) from emp where job = 'salesman';
select sum(sal) from emp where job = 'manager';
-- 평균 급여 조회
select avg(sal) from emp;
-- 평균값을 소수 둘째 자리까지만 조회하려면
select round( avg(sal),2 ) from emp;
-- round() 함수
-- dual: 문장을 완성하기 위한 가상의 테이블 
select round(12.3456,2) from dual; -- 12.35
select round(12.3456,1) from dual; -- 12.3
select round(12.3456,0) from dual; -- 12
select round(12.567,0) from dual; -- 13
select sysdate() from dual;

-- 가장 많은 급여
select max(sal) from emp;
-- 가작 적은 급여
select min(sal) from emp;
-- 전체 사원 수
select count( * ) from emp;
-- 부서번호가 20인 사원 수
select count(*) from emp where deptno = 20;

sum 은  테이블 컬럼의 모든 합

avg는 테이블 컬럼의 평균값

round는 반올림

max는 가장 큰 값

min은 가장 작은값

count 는 테이블 컬럼에 저장되있는 정보가 몇개인지 세어준다