국비 TIL(Today I Learned) 20220527 mysql, mariadb

union은 조인으로 묶을수도 없을때, 합집합, 동일의 컬럼의 개수를 가져야한다, 중복된 데이터 제거하고 퍼옴

권장사항은 아님, 그래서 union all으로 씀  union all은 다 가져옴, 

 

테이블 만들때 name부분처럼 파란색으로 뜨는건 명령어가 있다는 애기, 그래서 필드명으로 하는건 안 좋다고 함

 

테이블 만들면은 이렇게 정보볼수 있음

 

 

 

 

Constraints 제약조건

FOREIGN KEY - 테이블간에 서로 연결, 한쪽 테이블에서 primary key나 unique여야함, 데이터가 많은쪽에서(N) 적은쪽 참조

DEFAULT - 기본값

CREATE INDEX - 순차검색(선형검색 linear, sequential) 처음부터 원하는 데이터를 찾을때까지) 대신 색인순차(indexd sequential) 색인표(데이터를 정렬해서 쪼갬)를 조회, 근디 검색속도는 빠른데, 색인표 만드는게 비용많이듬, 특정 컬럼으로 만듬(컬럼중에 조회많이 되는거 위주로) , primary key만들면 자동으로 인덱스 자동 만들어짐, 굳이 index안 만들고 primary key만 잘 만들어주면 됨, 100만건 이하면은 

 

 

information_schema 정보볼수 있음, root계정만 가능

 

 

http://www.gurubee.net/

꿈꾸는 개발자, DBA 커뮤니티 구루비

 

오늘 배운 쿼리문

USE mydb;

-- union 
SELECT ename, empno FROM emp
UNION ALL
SELECT ename, empno FROM emp3
UNION ALL 
SELECT dname, deptno FROM dept;


SELECT COUNT(*) FROM emp
UNION ALL
SELECT COUNT(*) FROM emp2
UNION ALL
SELECT COUNT(*) FROM emp3
UNION ALL
SELECT COUNT(*) FROM emp4;



-- 그룹함수 (count, max, min, sum, avg)
-- 그룹단위로 값을 하나만 가져온다
-- select절, order by 정렬가능
-- where 절에는 그룹함수 못 쓴다.

SELECT deptno, COUNT(*)
FROM emp
GROUP BY deptno
ORDER BY COUNT(*) DESC;

-- 밑에 쿼리문은 오류, 대신에 Having절을 씀
SELECT deptno, COUNT(*)
FROM emp
GROUP BY deptno
WHERE COUNT(*)>=5
ORDER BY COUNT(*) DESC;

SELECT deptno, COUNT(*)
FROM emp
GROUP BY deptno
having COUNT(*)>=5
ORDER BY COUNT(*) DESC;

-- 서브쿼리를 사용해서 동일한 결과를 가져와보자
SELECT * 
FROM (
	SELECT deptno, COUNT(*) cnt
	FROM emp
	GROUP BY deptno
) A
WHERE cnt>=5;
ORDER BY cnt DESC;




USE classicmodels;

-- EXISTS 서브쿼리에 데이터 존재하는지 확인, 데이터 있으면 true, 데이터 없으면 false가 반환되서 쿼리문 실행 안됨
-- 중간에 멈출수도 있음, 서브쿼리가 여러개면 하나만 데이터 존재해도 true반환
-- EXISTS 명령어는 서브쿼리의 실행결과의 유무만 따진다. 결과가 있으면 true 없으면 false반환

-- 데이터 나옴
SELECT * FROM customers
WHERE EXISTS (SELECT STATUS
FROM orders
WHERE STATUS= 'shipped');

-- 데이터 안나옴
SELECT * FROM customers
WHERE EXISTS (SELECT STATUS
FROM orders
WHERE STATUS= 'Airplain');


-- IN : = or = or = or 
-- A IN (1,5,7,9) A='1' or A='5' or A='7' or A='9'

-- Any: > or > or > or     < or < or < or    특정값보다 하나만 크거나 작으면 조건만족
-- AlL: > and > and > and     < and < and < and    그중에서 가장 큰거나 가장 작은거랑 같아야 한다.



USE mydb;

-- 구조복사만
CREATE TABLE emp6 AS SELECT * FROM emp WHERE 1=0;
-- INSERT
INSERT INTO emp6 SELECT * FROM emp WHERE deptno=10;
SELECT * FROM emp6;



-- case
SELECT ename, case 
                  when deptno=10 then '개발1부'
                  when deptno=20 then '개발2부'
                  when deptno=30 then '영업부'
						ELSE '부서배정안됨'
						END AS dname
FROM emp;



-- 데이터베이스 만들기
-- CREATE DATABASE 데이터베스명 default character set utf8 collate utf8_general_ci;
CREATE DATABASE project1 default character set utf8 collate UTF8_GENERAL_CI;

-- 삭제명령어
DROP DATABASE project1;

CREATE DATABASE project1 default character set utf8 collate UTF8_GENERAL_CI;

USE project1;

-- DATETIME 날짜와 시간
CREATE TABLE Persons (
	personID INT, 
	NAME VARCHAR(40),
	address VARCHAR(200),
	phone VARCHAR(20),
	wdate DATETIME
);

SELECT * FROM Persons;
DESC persons;

ALTER TABLE persons ADD CONSTRAINT PK_PERSONs PRIMARY KEY(personid);
INSERT INTO persons(personid, NAME, address, phone, wdate)
VALUES(1, '홍길동', '금천동', '010-0000-0000', NOW());
-- now 함수는 현재시간과 날짜를 가져온다

SELECT * FROM persons;

-- 오류남 personid를 primarykey로 지정해놨긴떄문에
INSERT INTO persons(personid, NAME, address, phone, wdate)
VALUES(1, '임꺽정', '금천2동', '010-0000-0001', NOW());


SELECT MAX(personid) FROM persons; -- 1로 나오고

DELETE FROM persons; -- 데이터삭제

SELECT MAX(personid) FROM persons; -- null로 나오고, null에 연산하면 null이니 밑에 쿼리처럼

-- SELECT IFNULL(MAX(personid), 0) +1 FROM persons;

-- mysql 안됨,오라클만 가능
INSERT INTO persons(personid, NAME, address, phone, wdate)
VALUES((SELECT IFNULL(MAX(personid), 0) +1 FROM persons), 
			'임꺽정', '금천2동', '010-0000-0001', NOW());
			
			
						
-- modify는varchar를 int로는 못바꿈, 제약조건이 많아서 아예 삭제하고 다시 만드는게 나음
ALTER TABLE persons MODIFY personid INT NOT NULL AUTO_INCREMENT;

ALTER TABLE persons DROP PRIMARY KEY;

DESC persons;			
			

-- DML(Data Maniplulation Language): insert, delete, update, select
-- DDL(Data Definition Language): create, alter, drop, truncate
-- DCL(Data contrall Language): rollback, commit, grant, revoke 

-- 필드(컬럼) 추가하기
ALTER TABLE persons ADD Email VARCHAR(200);
DESC persons;		
-- 필드(컬럼) 삭제
ALTER TABLE persons DROP COLUMN email;
DESC persons;
-- 필드(컬럼) 수정, 기존 varchar에서 int로 가기, 데이터 없어야 바꿔줌, 있으면 안됨
ALTER TABLE persons MODIFY COLUMN address INT(10);

ALTER TABLE persons MODIFY COLUMN address VARCHAR(100);
DESC persons;


-- not null 제약조건
CREATE TABLE student 
(
	id INT NOT NULL,
	sname VARCHAR(40) NOT NULL,
	phone VARCHAR(40) NOT NULL,
	age int
);

INSERT INTO student(id, sname, phone)VALUES(1, '조승연', '010-0000-0000')
-- 오류남
INSERT INTO student(id, sname)VALUES(2, '조연희', '010-0000-0001')

SELECT * FROM student;

DROP TABLE persons;


-- unique
CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age INT,
    userid VARCHAR(40),
    UNIQUE (userid)
);
-- null 허용
INSERT INTO persons(id, lastname) VALUES(1,'Feet');
SELECT * FROM persons;

INSERT INTO persons(id, lastname, userid) VALUES(2,'Brown','test');
SELECT * FROM persons;

-- UNIQUE 제약조건 에러(중복값 불가) 오류메세지: duplicate, violation
INSERT INTO persons(id, lastname, userid) VALUES(3,'Smith','test');
SELECT * FROM persons;


-- foreign key 보통쓸때 FK
-- 테이블간에 1:n의 관계
-- 참조되는 테이블에서 이 필드가 primary key 또는 
-- unique 제약조건을 만족해야 한다.
-- 1에 해당하는 테이블이 잠조된다.
-- n이 참조

USE mydb;
-- 참조가 되는 테이블 필드에 primary나 unique 제약을 주어야 한다.
SELECT * FROM dept;
-- 1. 데이터 중복 없애고
DELETE FROM dept WHERE deptno >= 50;
-- 2. primary key 지정
ALTER TABLE dept ADD CONSTRAINT PK_DEPT PRIMARY KEY(deptno);
-- 3. foreign key 지정
ALTER TABLE emp ADD FOREIGN KEY FK_EMP_DEPT
(deptno) REFERENCES dept(deptno);

DESC emp;
DESC dept;

SELECT * FROM emp;
SELECT * FROM dept;

-- 신규 추가시 문제없음
INSERT INTO dept(deptno, dname, loc)VALUES(50, 'MARGETING', 'SEOUL');

-- EMP 테이블에서 10번 쓰고 있어서 오류발생

DELETE FROM dept WHERE DEPTNO=10;
DELETE FROM emp WHERE DEPTNO=10; --뭐야 이거는 삭제되네 

INSERT INTO emp(empno, ename, deptno)VALUES(8000, 'BROUN', 40);
-- error dept deptno에 70번이 없어서
INSERT INTO emp(empno, ename, deptno)VALUES(8001, 'BROUN', 70);



CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    PersonID int,
    PRIMARY KEY (OrderID)
);
DESC orders;


CREATE TABLE Persons (
	PersonID int,
	LastName VARCHAR(40),
	FisrName VARCHAR(40),
	age INT,
   PRIMARY KEY (PersonID)
);
DESC persons;



ALTER TABLE Orders ADD FOREIGN KEY FK_PID
(PersonID) REFERENCES Persons(PersonID);


-- DATA DICTIONARY, 읽기만 가능
SELECT * FROM information_schema.columns
WHERE information_schema.columns.TABLE_SCHEMA='CLASSICMODELS';

SELECT * FROM information_schema.columns
WHERE information_schema.columns.TABLE_name='Custoners';

-- 디비스키마.테이블명도 가능핟.
SELECT * FROM classicmodels.customers;
SELECT * FROM myDB.emp;


-- check 해도 들어가긴 하네
CREATE TABLE Persons2 (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    CHECK (Age>=18)
);

INSERT INTO persons2(ID, LASTname, firstname, age)
VALUES(1,'brown','tom', 17);

SELECT * FROM persons2;



-- table 만들기 default 지정
CREATE TABLE board (
    ID int NOT NULL PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(1000),
    contents TEXT, 
    wdate DATETIME DEFAULT NOW(),
    hit INT DEFAULT 0
);

INSERT INTO board(id, title, contents)VALUES(1, '제목1', '내용1');
SELECT * FROM board;


/*

데이터베이스의 기본검색방법은 순차검색이다.
순차검색은 데이터의 처음부터 원하는 데이터를 찾을때까지 읽어나가는
방식이다. 검색방법은 쉽지만 속도가 느리다는게 단점이다.
그래서 디비에서는 색인순차 방법을 지원한다.
색인순차는 데이터를 정렬하여 일정범위로 나누어서 색인을 부여하고 검색시
색인이 있는곳까지 가서 순차검색을 하는 방법을 취한다.
보통 검색을 빠르게 하고자 할때 사용하는데 언제나 빠른거는 아니다. 
색인순차를 설정할때 조건
1. 데이터가 충분히 많을때
2. 데이터가 분포도가 고를때(예를들어서 성별 같은 경우는 인덱스를 안 만드는게 낫음)
3. null값이 많은 필드는 인덱스 안만드는게 좋다.
4. 검색조건에 나타나는 필드에 인덱스를 만든다.(검색조건에 없는 필드는 아무의미없다.)
5. like 연산이나 함수가 조건식에 있을때 인덱스는 의미가 없다.

primary key를 선언하면 자동으로 그 필드에 인덱스가 만들어진다.
초보자는 이것만 잘하자 쓸데 없이 index만들면 인덱스 만드는데 필요한 메모리만
차지하면서 속도는 느려지는 결과가 나옴

*/




/* 
view - 실제존재하는 테이블이 아니라 가상의 테이블이다.
       자주 사용하는 select 쿼리 저장해놓고 마치 테이블처럼 사용한다.
       view를 이용해 데이터를 추가할 수 있는 경우도 있지만 보통은 view 사용목적은
       복잡한 쿼리를 테이블처럼 저장해놓고 사용하기 위한 목적이라 select용이다.
       그러나 요즘은 view를 잘 사용하지 않는 추세       
*/


CREATE VIEW V_emp
AS SELECT empno, ename, dname, A.deptno, sal, hiredate
FROM emp a
LEFT OUTER JOIN dept b ON a.DEPTNO=b.DEPTNO;


SELECT * FROM v_emp;

CREATE or replace VIEW V_emp
AS SELECT A.empno, A.ename, dname, A.deptno, A.sal, A.hiredate, A.mgr, c.ename AS mgr_name
FROM emp a
LEFT OUTER JOIN dept b ON a.DEPTNO=b.DEPTNO;
LEFT OUTER JOIN emp c ON a.mgr= c.empno;

SELECT * FROM emp;