국비 TIL(Today I Learned) 20220525 mysql 쿼리문, mariaDB

파일 - 데이터 저장

 

        급여시스템                             인사시스템

        1101 홍길동 2020-01-09            1101 홍길동 2020-01-19 (잘못 들어갔어)

 

파일 시스템의 문제점

1. 내용의 중복이 많다.

2. 모든 파일을 동시에 바꿔야 하는데, 빠진 자료가 있을 경우 문제 발생

ex) 홍길동 퇴사를 했어 인사시스템에는 기록되어있는데, 급여시스템에는 실수로 안됨, 급여 계속 나감

 

-> 관계형 데이터베이스 시스템을 제안한다.

1. 데이터 중복성 배제

2. 데이터의 무결성(결함이 없는 성격)

3. 데이터의 일관성

4. 데이터가 여러명이 동시에 접근할 수 있고

5. 현상황을 항상 반영하고 있어야 한다.

 

데이터를 테이블로 만들고, 테이블과 테이블의 관계성에 주목을 한다.

테이블은 열을 만들고 각 열은 필드라고 한다. 그리고 데이터를 행으로 본다.

 

제약조건 - 데이터를 넣을때 아무거나 못 넣게 제약을 가한다. 

 

primary key(주키)

특정 필등중에 중복된 값을 갖지 못하는 필드들이 있다.

그리고 값을 반드시 가져야 하는 필드를 주키로 지정할 수 있다. 이 때 주키로 지정된 필드는 절대 중복된 값을 가질수 없고, null(빈값)도 가질 수 없다. 테이블에 하나의 주키로 가질 수 있다.

때로는 하나의 필드로 조건을 만족하지 못할때 여러개의 필드를 묶어서 하나의 primary key로 만들 수 있다.

primary key로 묶인 필드에 중복값이 들어오면 violation 오류가 발생한다.

 

unique 제약조건

중복은 배제하는데, null은 허용한다. ex) 회원아이디 - primary key로 줄 수도 있지만, 요즘은 회원아이디 바뀌는 사이트도 있어서 unique로 많이 한다.

 

foreign key(외부키)

데이터베이스는 가급적 데이터 중복을 피한다. 만일 학생테이블이 있을때

학생                                        학과데이블

학번       이름      학과코드          학과코드  학과명

0001     홍길동     90                  90         컴퓨터미디어학과

                                             ~100

1. 학생테이블에 학과테이블에 없는 학과코드가 들어가면 안된다.

2. 학과테이블 어느날 삭제시킨다. 학과코드도 함부로 삭제되면 안된다.

두개의 테이블을 묶어서 하나의 관계를 형성해준다. 외부키(foreign key) 관계, 학생테이블이 학과테이블 참조한다 라고 표현

그리고 이 때 학과코드는 primary 또는 unique 일때 외부키를 지정할 수 있다. 

 

범위제약조건

값이 특정 범위를 못벗나게 (성별, 남 녀) 조건값 제외한 나머지 값들을 막는 제약을 걸수도 있다.

 

데이터베이스 - 데이터를 묶어 놓은 공간/개념 포함

DBMS(Database Management System) - 프로그램

대표) 오라클, MSSQL, MYSQL(MARIADB), foxpro, cubrid

오라클: 빠르고 대용량 데이터 처리에 효율적, 대부분의 국가기관, 대기업들, 웬만한 중소기업

MSSQL: ms사가 만든거, 그래도 사용빈도 높음, 연구기간

MYSQL(MARIADB)- 공짜라서 개인쇼핑몰, 꽤 많이 사용, 클라우드 시스템이 오라클이랑 계약이 안되서, MYSQL 많이 쓰기도 함,

근데 오라클, MSSQL 공부해야함, 조금씩 다르데

 

 

 

 

ctrl + shift + F9 현재쿼리 실행

 

db가 배울때는 쉬운데, 나중에 실제 개발하면 어려움

조인? 할때 어렵데, 

db 쿼리를 작성하는거에 따라 어떤거 가져올때는 30분 걸리고, 어떤거는 30초 걸리고

 

오라클은 쿼리는 스스로 고친다고? 자동 튜닝??? 

 

 

위는 콘솔로 데이터 출력, 툴사용하면 그래픽, 국비학원에서는 HeidiSQL 사용중

 

 

오늘 배운 쿼리문1

-- emp 테이블의 모든 필드와 모든 데이터를 다 보겠다. 이렇게 하면 위험? 필드에 데이터가 많을경우
SELECT * FROM emp;

-- 원하는 필드만 찾아서 확인하기
SELECT empno, ename, gender FROM emp;

-- select ~ from ~ where 조건식 order by 정렬
-- 사원번호 중에서 7700 넘어가는 사람, ;세미콜론 단위로 한 문장이 끝나서 밑에처럼 나눠서 쓸수 있음
SELECT empno, ename 
FROM emp 
WHERE empno > 7700;

SELECT empno, ename 
FROM emp 
WHERE empno > 7700 AND empno <7800;

-- 이름으로 검색하기, 쿼리(query)또는 질의라고 불림, 비절차, 대소문자 관계없음, 
-- 데이터는 대소문자를 구분하는게 맞는데, Mysql은 신기하게도 데이터 대소문자 구분 못함, 다른 dbms는 구분함(오라클은 구분함)
SELECT * 
FROM emp
WHERE ename = 'scott';


-- or 연산자
SELECT * 
FROM emp
WHERE ename = 'scott' OR ename = 'ford';

-- 이름이 allen, ward, martin, adams 가 나오길 원함
SELECT * 
FROM emp
WHERE ename = 'allen' OR ename = 'ward' OR ename = 'martin' OR ename = 'adams';

-- in 연산자, or 많을경우사용, in 뒤에 500개까지 가능
SELECT * 
FROM emp
WHERE ename IN('allen', 'ward', 'martin', 'adams');

-- 문제는 '' 붙이고, 숫자는 빼도 됨
SELECT * FROM emp WHERE empno IN (7369, 7499, 7521, 7934, 7900) OR empno IN (7788, 7566);

SELECT * FROM emp WHERE job = 'CLERK' OR job = 'MANAGER';

-- 두개의 조건이 동시에 만족할때 job이 salesman 이고 이중에서 연봉이 1500 넘는 사람
SELECT * FROM emp WHERE job = 'SALESMAN' AND sal >= 1500;

-- 부서번호가 20이고 연봉이 2000이상의 사람의 이름, 연봉정보만
SELECT ename, sal 
FROM emp 
WHERE deptno = 20 AND sal >= 2000;

-- 부서번호가 30 이거나 10이고, 연봉이 5000 이상인 사람
SELECT * FROM emp WHERE (deptno = 30 OR  deptno = 10) AND sal >= 5000;


-- where 조건절까지 모두 실행된 다음에 정렬을 한다
-- 정렬은 순서대로 데이터를 늘어놓는것을 말하는데 작은것부터 큰거순으로 배치를 오름차순, 큰거에서 작은순은 내림차순이라 한다.
-- order by 필드영 asc    ascending(오름차순) 생략가능
-- order by 필드명 desc   descending(내림차순)
-- order by 필드1 desc, 필드2 asc 필드1을 기준으로 내림차순, 필드1의 순서가 같을 경우 필드2를 적용, 오름차순하여 보여줌.
SELECT * 
FROM emp
ORDER BY ename ASC


-- 날짜로 검색하기
SELECT * FROM emp
WHERE hiredate < '1982-01-01';


-- substr(필드명, 첫번째 컬럼, 개수) 프로그램언어는 첫번째가 항상0이었음
-- 그러나 데이터베이스는 1이다.
-- 문자열을 잘라낸다.
-- 필드나 수식뒤에 새로운 필드명을 부여할 수 있다. as 필드명
-- as는 생략가능 이 기능을 aila라고 한다. (별명)
SELECT SUBSTR(hiredate, 1, 4) AS YEAR, SUBSTR(hiredate, 6,2) AS MONTH,
SUBSTR(hiredate,9,2) AS DAY
FROM emp;

-- 입사일이 1980년대인 사람
SELECT * FROM emp
WHERE SUBSTR(hiredate, 1, 4) = '1980';

-- 5월에 태어난 사람들
SELECT * FROM emp
WHERE SUBSTR(hiredate, 6, 2) = '05';


-- 문자열의 경우에 s로 시작하는  %와일드 카드는 like연산자랑
-- s로 시작
SELECT * FROM emp
WHERE ename LIKE 'S%';

-- 중간에 s로 포함하는
SELECT * FROM emp
WHERE ename LIKE '%S%';

-- R로 끝나는
SELECT * FROM emp
WHERE ename LIKE '%R';


-- 연봉 + comm (커미션)
-- null 연산불가능한, 수학으로 따지면 무한대, null값에는 연산을 수행할 수 없다.
-- ifnull(필드명, 값)- 해당필드의 값이 null이 아니면 해당필드값을 반환, mssqul에서는 isnull
-- null이면 값을 반환
SELECT empno, ename, sal+ifnull(comm,0) AS pay FROM emp;

 

ifnull? isnull??  null인지 아닌지 체크하는 값이 조금씩 다름, 

https://coding-factory.tistory.com/98

[MSSQL] NULL값 체크 (ISNULL) 함수 사용법 & 예제

 

오토바이? orderby를 오토바이라고 들었네ㅋㅋㅋㅋ 데이터를 정렬

 

SELECT 절, WHERE절 

IN절

LIKE절

SUBSTR 함수

 

mysql -u root -p 로 들어가야 데이터를 만들수 있음

외부파일 가져올때 source

이런식으로 떠

세션관리자에서 root로 해야 데이터베이스 새로 만들수 있음

 

 

섞어서 한번에 가져올수 있도록 쿼리 짜야함

db 건수가 많으면 마리아db로 안됨 오라클써

 

 

 

테이블에 데이터 넣을때

INSERT INTO dept(deptno, dname) VALUES(50, '홍보부');
INSERT INTO dept VALUES(60, '개발1부','부산');

 

PRIMARY KEY 추가, emp는 테이블 이름임, empno는 열의 이름, 각자 테이블 db로 하면 됨

ALTER TABLE emp ADD PRIMARY KEY (empno);

 

PRIMARY KEY 삭제

ALTER TABLE emp DROP PRIMARY KEY;

 

데이터 새로 만들기

CREATE TABLE company
( 
	id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
	company_name VARCHAR(100),
	company_phone VARCHAR(20),
	company_email VARCHAR(40)
);
INSERT INTO company (company_name, company_phone, company_email ) 
VALUES('Tom''s company', '010-0000-0000','tom@naver.com');

SELECT * FROM company;

결과값

 

오늘 배운 쿼리문2

USE classicmodels;

DESC customers;

SELECT * FROM customers;

-- 문제 1. customers 테이블에서 country가 USA 인 사람들의 고객번호, 이름, 전화번호만 출력하기

-- 문제 2. customers 테이블에서 postal cade가 4로 시작하는 고객번호, 이름, 전화번호, postal code만 출력하기

-- 문제 3. 고객번호가 187, 471, 333, 282, 484, 357, 385인 사람의 고객번호, 이름, 나라, state만 출력하기

-- 문제 4. 사는 나라가 Singapore, Japan, Philippines에 사는 사람들의 정보를 나라이름으로 오름차순 정렬하고,
-- 고객번호로 오름차순하여 출력하기

SELECT customerNumber, customerName, phone 
FROM customers
WHERE country = 'USA';

SELECT customerNumber, customerName, phone , postalCode
FROM customers
WHERE SUBSTR(postalCode, 1, 1) = '4';

SELECT customerNumber, customerName, country , state
FROM customers
WHERE customerNumber IN (187, 471, 333, 282, 484, 357, 385);

SELECT *
FROM customers
WHERE country IN ('Singapore', 'Japan', 'Philippines')
ORDER BY country, customerNumber ASC;


SELECT customerNumber, customerName
FROM customers
WHERE creditLimit >= 21000 AND creditLimit <=59700;

-- BETWEEN은  dbms 중에서 오라클하고 mysql등만 있음
SELECT customerNumber, customerName
FROM customers
WHERE creditLimit BETWEEN 21000 AND 59700;


-- null인 데이터 찾을 때 is null, is not null
SELECT customerNumber, customerName, addressLine2
FROM customers
WHERE addressLine2 IS NULL;

SELECT customerNumber, customerName, addressLine2
FROM customers
WHERE addressLine2 IS not NULL;

USE mydb;

-- DESC 테이블 구조를 확인하는 명령어
DESC dept;

-- char : 고정길이 문자열, varchar - 가변길이 문자열
-- char(100): 실제데이터가 2글자 2글자, 공백 98글자로 채워진다. 공백도 문자
-- 자릿수가 고정인데이터, 보통은 학번, 사번, 주민번호, 우편번호, y or n, 성별 저장들에만 쓰인다.

-- varchar(100): 가변길이, 길이가 바뀐다. 자신의 갖고 있는 데이터의 길이를 갖고 다닌다. 주로 많이 사용
-- text, long text: 긴 문장 쓸때

INSERT INTO dept(deptno, dname, loc) VALUES(40, '총무부', '서울');
INSERT INTO dept(deptno, dname) VALUES(50, '홍보부');
INSERT INTO dept VALUES(60, '개발1부','부산');
SELECT * FROM dept;

-- 이미 테이블에 중복된 데이터 있을때는 primary key 지정이 안된다.
ALTER TABLE dept
ADD PRIMARY KEY (deptno);

SELECT * FROM dept;

DELETE FROM dept WHERE dname='총무부';

SELECT * FROM dept;

ALTER TABLE dept
ADD PRIMARY KEY (deptno);

-- primary key 주고, 중복 값 주면 오류뜸  /* SQL 오류 (1062): Duplicate entry '40' for key 'PRIMARY' */
INSERT INTO dept(deptno, dname, loc) VALUES(40, '총무부', '서울');

-- emp 테이블에 primary key 설정하기
ALTER TABLE emp ADD PRIMARY KEY (empno);

DESC emp;

INSERT INTO emp (empno, ename) VALUES(8000, '홍길동'); -- 성공
INSERT INTO emp (empno, ename) VALUES(8000, '장길산'); -- duplicate error

-- primary key 삭제
ALTER TABLE emp DROP PRIMARY KEY;

INSERT INTO emp (empno, ename) VALUES(8000, '장길산'); -- primary key 삭제하면 들어감

DELETE FROM emp WHERE ename='장길산'; -- 중복된 데이터 제거

ALTER TABLE emp ADD PRIMARY KEY (empno); -- 다시 primary key 넣어주기

SELECT * FROM emp;



-- foreign key 설정하기
-- emp테이블이 dept 테이블을 참조한다.
ALTER TABLE emp ADD FOREIGN KEY(deptno) REFERENCES dept(deptno);

-- 참조하고 있는 데이터 전체 삭제시 에러발생
/* SQL 오류 (1451): Cannot delete or update a parent row: a foreign key constraint fails (`mydb`.`emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`DEPTNO`) REFERENCES `dept` (`DEPTNO`)) */
DELETE FROM dept; 

-- 테이블 통으로 날리기, 이것도 에러남
/* SQL 오류 (1217): Cannot delete or update a parent row: a foreign key constraint fails */
DROP TABLE dept;

-- emp 테이블에서 사용하지 않는 부서번호는 삭제 가능
DELETE FROM dept WHERE deptno=60;



-- 데이터에서 ' (어퍼스트로피)가 있는경우 Tom's company
-- 데이터에서 ' 는 특별한 기능이 있다.
INSERT INTO emp (empno, ename) VALUES(8000, 'Tom''s company'); -- 이렇게 '' 넣어야 하는데, 근디 테이블 데이터가 작으면 안돌어감 테이블 만들어줘야함


CREATE TABLE company
( 
	id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
	company_name VARCHAR(100),
	company_phone VARCHAR(20),
	company_email VARCHAR(40)
);
INSERT INTO company (company_name, company_phone, company_email ) VALUES('Tom''s company', '010-0000-0000','tom@naver.com');
-- 'mycompany' 이렇게 넣을때 
INSERT INTO company (company_name, company_phone, company_email ) VALUES('''mycompany''', '010-0000-0000','mycompany@naver.com');

SELECT * FROM company;



-- 문제1. 테이블을 만든다.
CREATE TABLE to_score
(
	id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
	sname VARCHAR(40),
	kor INT,
	ent INT,
	mat INT
);
-- 문제2. 테이블 안에 값 넣기
INSERT INTO to_score (sname, kor, ent, mat) VALUES('홍길동', 90, 90, 90);
INSERT INTO to_score (sname, kor, ent, mat) VALUES('김경하', 80, 80, 90);
INSERT INTO to_score (sname, kor, ent, mat) VALUES('장길산', 70, 80, 70);
INSERT INTO to_score (sname, kor, ent, mat) VALUES('임꺽정', 100, 90, 80);
INSERT INTO to_score (sname, kor, ent, mat) VALUES('김성훈', 90, 100, 90);

SELECT * FROM to_score;

-- 문제3. 평균 총점 구하기
SELECT id, sname, kor, ent, mat, kor + ent+ mat AS total, (kor + ent+ mat)/3 AS avg FROM to_score;

-- 문제4. 국어성적이 90이상인 사람
SELECT * FROM to_score
WHERE kor >= 90;

-- 문제5. 국어, 영어, 수학 성정이 모두 90 이상인 사람
SELECT * FROM to_score
WHERE kor >= 90 AND ent >=90 AND mat >=90;

-- 문제6. 이름에 '길'자가 들어가는 사람
SELECT * FROM to_score
WHERE sname LIKE '%길%';

-- 다중으로 데이터 insert
INSERT INTO to_score (sname, kor, ent, mat) 
VALUES('유재석', 50, 20, 40),('원빈',60,80,90),('나애리', 30,50,80);

SELECT * FROM to_score;

 

다중으로 데이터 insert

https://shpk333.tistory.com/entry/MYSQL-INSERT%EB%AC%B8-%EC%82%AC%EC%9A%A9%EB%B2%95-%EB%B0%8F-%EC%98%88%EC%A0%9C-%EB%8B%A4%EC%A4%91-%EB%A0%88%EC%BD%94%EB%93%9C-SELECT%EB%AC%B8-%EC%B6%94%EA%B0%80

[MYSQL] INSERT문 사용법 및 예제 ( 다중 레코드, SELECT문 추가 )

 

 

내일은 조인하고 서브쿼리 들어간데