[21.08.12] My SQL 익히기

- DB는 3계층 구조로 이루어진다.

• 인스턴스 = DB서버
• 스키마 = Database
• 테이블

- CRUD : Create + Read + Update + Delete

 : 데이터를 다루는 software의 기본적인 인터페이스

• C : insert into 테이블 values();
• R : select 필드 (as 필드명 변경) from 테이블 where 레코드조건
• U : update 테이블 set 필드=값 where 레코드조건
• D : delete from 테이블 where 레코드조건 

update minco.honey set age=15 where num=4;
delete from minco.honey where num=1;
insert into minco.honey values(50, "촉촉", 100);
select * from minco.honey

insert into minco.honey values(8, "순신", 20)
update minco.honey set age=320 where num=7;
update minco.honey set name='정현' where num=6;
delete from minco.honey where num =7;
delete from minco.honey where num =8;

select * from minco.honey
select * from minco.honey where num=3 or num=5;
SELECT name, age FROM minco.honey where num>=2 and num <=5 ;
select * from minco.honey where age>=20 and age <30;
select name from minco.honey where num%2=0 and age%2=1 ;
select name as '이름' from minco.honey;
select * from minco.honey order by age desc;
select num as '번호', name as '성함', age as '나이' from minco.honey order by age; 
select name,name,name,name,name from minco.honey where age<20;
honeyselect age, name from minco.honey order by name

 

- 웹에서의 CRUD

• 로그인 : select
• 회원가입 : select , insert, update
• 회원탈퇴 : delete
• 게시판 : insert, select, update, delete (CRUD 전체사용)

- UPDATE Error 

 -> Safe update mode

    : 다수의 레코드들이 한꺼번에 update되는사고를 방지하기위함 (설정에서 off가능)

 

- CMD에서 사용하기 -> 모든 명령어 ; 필수

• mysql -u root -p (패스워드입력 uveba40d50!)
• show database;
• use minco(user name)
• show tables;
• desc honey;
• CRUD사용 -> workbench에서 동일한 테이블 확인가능

- git에서 test_db가져오기

https://github.com/datacharmer/test_db

GitHub - datacharmer/test_db: A sample MySQL database with an integrated test suite, used to test your applications and database

• cd Desktop
• git clone https://github.com/datacharmer/test_db.git

GitHub - datacharmer/test_db: A sample MySQL database with an integrated test suite, used to test your applications and database

• cd test_db
• mysql -u root -p < employees.sql (패스워드입력 uveba40d50!)

- test_db활용 검색하기 -> between, in, like

SELECT * FROM employees.employees  where emp_no between 10002 and 10004; 
SELECT * FROM employees.employees  where first_name in ('Parto', 'Bezalel');
SELECT * FROM employees.employees  where first_name like ('G__'); -- 'G%'

- group by -> 조건절 having

• SUM() / AVG()
• MIN() / MAX()
• COUNT

select ID, sum(Population) as 합계 from city group by CountryCode;
select CountryCode, count(Population) as from city group by CountryCode;

select CountryCode, count(Population) as '도시수' from city 
group by CountryCode
having count(Population) > 200;

- limit -> 특정개수만 보여줌으로써 빠른 쿼리 성능구현

- 날짜, 시간 검색하기

select DATE("2025-12-25 11:42:59");
select TIME("2025-12-25 11:42:59");
select YEAR("2025-12-25 11:42:59");
select MONTH("2025-12-25 11:42:59");
select DAY("2025-12-25 11:42:59");
select hour("2025-12-25 11:42:59");
select minute("2025-12-25 11:42:59");
select second("2025-12-25 11:42:59");

- 날짜 샘하기

SELECT year(from_date) as '시작년도', year(to_date) as '종료년도' FROM employees.dept_emp limit 3 ;
select date_format("2020-12-31","%Y년 %m월 %d일이었지")
select date_format("2020-12-31 11:37:40","%Y년 %m월 %d일 %h시 %i분 %s초 였을거야")
select str_to_date("1997년 12월 25일","%Y년 %m월 %d일이었지")

select date_format(from_date,"%Y년 부터 시작이었지..") as '때는' from employees.dept_emp;

select timestampdiff(year, '2011-06-12', '2021-08-15'); -- 날짜간격구하기
select date_add(now(), interval 1 day); --날짜 덧셈
select date_sub(now(), interval 1 month); --날짜 뺄셈

select timestampdiff(year, '1996-08-23', now()) +1 ; -- 내나이 구하기
select timestampdiff(month, '1996-08-23', now()); -- 내가 살아온 달 구하기
select timestampdiff(day, now(), '2096-08-23'); -- 내가 100살까지 산다면 남은 날 구하기

- null값 유무 확인후 다른문자 대체하기

SELECT * FROM employees.departments where dept_name is not null;
select dept_no, ifnull(dept_name,'#') from employees.departments;

- 문자열함수

select concat('안녕', '하', '시오');
select concat(emp_no, '-', dept_no, '-', from_date) from employees.dept_emp;

select length('abcde'); -- 문자열 길이 반환

-- INSERT(문자열, 시작위치, 길이, 새로운문자열) 문자열의 시작위치부터 길이만큼의 문자열을 제거하고 새로운 문자열 삽입
select insert('abcde' , 2, 2, 'bbb'); -- abbbde

-- replace(문자열, 기존문자열, 새로운 문자열)
select replace('aacaa','a','b'); -- bbcbb

-- instr(문자열1, 문자열2) 문자열 1에서, 문자열2를 찾아서 위치 반환
select instr('abcdefg', 'cde'); -- 3

-- left(문자열, 개수) 문자열의 좌측부터 개수만큼 가져옴
select left('abcdefg', 3) -- abc

-- right(문자열, 개수) 우측부터 가져옴
select right('abcdefg', 3) -- efg

-- mid(문자열, 시작위치, 개수) 문자열 시작위치에서 개수만큼 가져온다
select mid('abcdefg', 3, 3) -- cde

-- substring(문자열, 시작위치, 개수) 문자열의 시작위치에서 개수만큼 가져옴
select substring('abcdefg',3,3) -- cde

-- trim (공백제거) ltrim rtrim

select concat('[','         abc    ',']')
select concat('[',trim('         abc    '),']')
select concat('[',ltrim('         abc    '),']')

select lcase("abcDEF");
-- lcase, lower 

select upper("abcDEF");
-- ucase, upper

select reverse("abcdef");

 

- 집합

1. 합집합 - Union

• select칼럼 from 테이블 union select 칼럼테이블
• union - 자동 중복 제거
• union all - 중복 제거 하지않고 바로 나타냄
• 교칩한 차집합에 비해 중요도가 떨어짐

2. 교집합 - join

• 첫번째 테이블 이름 INNER JOIN 두번째 테이블 이름 ON 조건
• select * from 테이블1, 테이블2 where 테이블1.name=테이블2.name

3. 차집합 - subquery

• select id from 테이블 1 where id NOT IN(select id from 테이블2)
• select 테이블1.id from 테이블1 left join 테이블2 ON 테이블1.id = b.id where b.id IS NULL
• 서브쿼리를 활용한다

select a.emp_no, frist_name from employees as a, dept_emp as b where a.emp_no = b.emp_no and b.dept_no = 'd001';

-- 현재 받는 급여의 평균보다 많이 받는 사원들의 사원번호, 급여액을 가져온다

-- 조건문을 만들어야되는데, 값을 직접 지정하지 못할때(평균연봉) 쿼리문을 통해서 가져와야되는경우 서브쿼리를 통해서 값을 구해 조건문으로 완성한다.
select avg(salary) from salaries;
select emp_no, salary from salaries where salary > (select avg(salary) from salaries where to_date = '9999-01-01') and to_date = '9999-01-01';

-- d001 부서에 근무하고 있는 사원들의 사원 번호와 first_name을 가져온다

-- join
select a.emp_no, first_name from employees a, dept_emp b where a.emp_no = b.emp_no and b.dept_no = 'd001'; 
-- sub
select emp_no, dept_no from dept_emp where dept_no = 'd001';
select * from employees;
select emp_no, first_name from employees  where emp_no in(select emp_no from dept_emp where dept_no = 'd001');


-- 1960 년 이후에 태어난 사원들이 근무하고 사원들의 사원번호, 근무 부서번호를 가져온다

-- join 
select a.emp_no, dept_no  from employees a, dept_emp b where a.emp_no = b.emp_no and birth_date >= '1960-01-01';
-- sub
select emp_no from employees where birth_date >= '1960-01-01';
select emp_no, dept_no from dept_emp where emp_no in (select emp_no from employees where birth_date >= '1960-01-01');