[DB] 새로운 곳에 다시 적기 | PRIMARY KEY, INDEX , SELECT (IN, BETWEEN, LIMIT, ORDER BY, LIKE %, GROUP BY, HAVING)

꼬부기의 실수모음.zip 🐢·2021년 4월 15일
정리.zip
0

데이터베이스 특징

  • 객체 → 개체
    : 객체라는 말은 없고 개체(Entity)라는 말을 쓴다.
  • 테이터 값 빼고는 모두 영어만 쓴다.
  • NULL
    : JAVA에서 NULL의 뜻은 주소값이 없다.
    : 데이터베이스에서 NULL의 뜻은 값이 없다.
    : NOT NULL의 뜻은 값이 없을 수가 없다.






DDL

  • CREATE, ALTER, DROP 
CREATE TABLE 이름 (
컬럼명 type(몇글자까지, 더 이상 넘어가서 쓸 수 없다) option
)

영문(숫자, 기호 포함)이나 글을 입력하기 위한 데이터 형식
varChar(8) char(8)
필요한 만큼만 쓴다 무조건 8자리 쓴다
느리다 빠르다

-PRIMARY KEY, PK
기본 설정 옵션 - NOT NULL, unique(절대 중복된 값 들어갈 수 없다.), index (튜닝의 핵심, 목차를 만든다, 데이터들의 목차들을 정리한다, 데이터들을 찾을 때, 인덱스가 정리되어 있으면 빨리 찾아주고, 다른 데이터들이 인덱스가 없다면 전수조사를 해야해서 느려진다.)
인덱스는 내부적으로 정렬된다..
한 컬럼만 PK가 되는 것이 아니고 여러 컬럼이 합쳐져서 PK가 될 수도 있다.
테이블마다 하나씩은 꼭 있어야 하고
각 레코드를 대표하는 값이다. (사람으로 치면 주민등록번호가 primaryKey라고 할 수 있다.)
(unique 옵션이 없다면 중복된 값 얼마든지 들어갈 수 있따)

레코드의 다른 값이 같더라도 pk로 구분된다.

NULL허용은 기본 옵션, Null허용은 따로 안적어도 된다

띄어쓰기로 구분
,로 구분
끝에는 , 안쓴다

SQL

CREATE TABLE memberTBL (
	memberID CHAR(8) PRIMARY KEY NOT NULL,
	memberName CHAR(5) NOT NULL,
	memberAddress CHAR(20)
);

날짜형은 type을 date라고 주면 되고 다른 건 줄 필요 없다.

p51

SQL

CREATE TABLE productTBL (
	productName CHAR(4) PRIMARY KEY,
	cost INT NOT NULL,
	make_Date DATE,
	company CHAR(5),
	amount INT NULL
);

date YYY-MM-DD
datetime YYYY-MM-DD HH:MM:SS

time HH:MM:SS

makedate은 예약어지만 함수라서 컬럼명으로 사용할 수 있다
함수이름은 컬럼명으로 사용할 수 있긴 하지만 지양권유

p56
insert - DML(CRUD)

insert into 테이블명
(컬럼명,컬럼명, 컬럼명,..(생략가능하지만 가능한 하지마- 개발자는 100퍼센트 정확하게 하고 에러가 터질 수 있는 여지는 절대 주지 않는다))
values
()

한 레코드가 맞으면 되지, 데이터베이스에서 순서는 상관없다.

SQL

INSERT INTO producttbl
( PRODUCTNAME, COST, MAKE_DATE, COMPANY, AMOUNT )
VALUES
( '컴퓨터', 10, 20170101, '삼성', 17 ),
( '세탁기', 20, 20180901, 'LG', 3 ),
( '냉장고', 5, 20190201, '대우', 22 );

데이터 활용 - SELECT
SELECT 컬럼명 FROM 테이블명 -> 이것은 기본적으로 있어줘야 한다.

SELECT * -> 모든 컬럼명 -> 컬럼조정
WHERE이 없으면 -> 모든 레코드
WHERE 은 조건절 -> 조건절인 레코드만 -> 레코드 조정
데이터베이스에서 ==없다.

SQL

SELECT * FROM producttbl;

SELECT COMPANY FROM PRODUCTTBL;

SELECT * FROM producttbl WHERE COMPANY='LG';

SELECT PRODUCTNAME FROM PRODUCTTBL WHERE COMPANY='삼성';

SELECT * FROM producttbl WHERE COST >= 10;

EMPLOYEES 넣기

p28-30

cd - change directory

MARIA DB - COMMAND PROMPT

C:\WINDOWS\System32>d:   -> 폴더
 
D:\>cd javabackendclass ->CHANGE DIRECTORY

D:\JavaBackendClass>cd employees -> CHANGE DIRECTORY

D:\JavaBackendClass\employees>mysql -uroot -p -> USERNAME이 ROOT로 PASSWORD는
Enter password: *******  -> 그리고 친다
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.5.9-MariaDB mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> source employees.sql; -> 그리고 이거 적는다

SQL

CREATE TABLE INDEXTBL (
	FIRST_NAME VARCHAR(14),
	LAST_NAME VARCHAR(16),
	HIRE_DATE DATE
);

SELECT FIRST_NAME, LAST_NAME, HIRE_DATE
FROM EMPLOYEES.EMPLOYEES
LIMIT 1000;

INSERT INTO indextbl
(FIRST_NAME, LAST_NAME, HIRE_DATE)
SELECT FIRST_NAME, LAST_NAME, HIRE_DATE
FROM EMPLOYEES.EMPLOYEES
LIMIT 1000;

SELECT * FROM indextbl;

EMPLOYEES에 있는 테이블 내용을 INDEXTBL로 100줄이나 바로 복사해왔다.

SQL

EXPLAIN SELECT * FROM indextbl
WHERE FIRST_NAME = 'MARY';

TYPE이 중요한데 ALL이면 전수조사를 한다는 뜻

SQL

CREATE INDEX IDX_INDEXTBL_FIRSTNAME
ON indextbl(FIRST_NAME);

EXPLAIN SELECT * FROM indextbl
WHERE FIRST_NAME = 'MARY';

TYPE 이 REF로 뜬다 (레퍼런스) -> 그래서 전수조사를 하지 않는다는 뜻 그래서 빠르다는 뜻
ON 테이블명(컬럼명)

CH6. SQL기본 P171

이 정도만 기억하자

DISTINCT -> 중복제거

SELECT DISTINCT 컬럼명 (또는 *) FROM 테이블명 WHERE 조건식 (조건식을 무한대로 넣을 수 있다.)
GROUP BY 컬럼명 (,로 여러 컬럼명을 줄 수 있다.) HAVING (GROUP BY의 조건식)
ORDER BY (레코드의 순서 조정)
LIMIT (레코드 수 조정)

USE 데이터베이스 이름; -> 데이터베이스가 선택된다.

SQL


USE EMPLOYEES;

SELECT * FROM TITLES;

SQL


SHOW DATABASES;
USE EMPLOYEES;
SHOW TABLES;
SHOW TABLE STATUS;

SHOW TABLE STATUS -> 이건 뭐지?

ENGINE 에는 주로 사용하는 것이 INNODB(트래지션 사용가능)와 MYISAM(트래지션 사용불가)이 있다. INNODB를 주로 쓰고 MYISAM은 쓸 일이 거의 없다.

별칭
컬럼명에 ALIAS(별칭주기)
컬럼명이 별칭으로 바껴서 나온다
AS를 빼도 된다
별칭은 별칭으로 디스플레이에 컬럼명이 별칭으로 바껴져서 나오는 것이다.
WHERE절에서만 별칭으로 사용할 수 없다. HAVAING, GRUOP BY에서 가능
그리고 별칭에 공백을 줄 때 ''로 감싸서 쓰면 된다
AVG_SALARY -> 'AVG SALARY'
근데 빈칸 절대 주지마라

무조건 별칭에 한글 쓰지마
값 빼고는 무조건 영어써

select 1 + 1 ;

이게 마음에 안든다는 것이다 그래서

select 1 + 1 as sum;
이렇게 별칭을 쓰는 것이다.

SQL


SELECT EMP_NO AS 'ENO'
FROM TITLES;

SELECT EMP_NO, EMP_NO AS 'ENO'
FROM TITLES;

테이블에 ALIAS(별칭)주기

SQL

SELECT T.EMP_NO, EMP_NO AS 'ENO'
FROM TITLES T;

EMPLOYEES.TITLES에서
EMP_NO가 10600번 이상이상인 레코드가 나타나도록 한다.

SQL

SELECT * 
FROM titles
WHERE EMP_NO >= 10600;

P187
BETWEEN

SQL

SELECT * FROM titles
WHERE EMP_NO >= 10000 AND EMP_NO <=20000;

SELECT * FROM titles
WHERE EMP_NO BETWEEN 10500 AND 20000;

WHERE 조건문 OR 조건문 -> IN

SQL

SELECT *
FROM titles
WHERE TITLE='ENGINEER' OR TITLE='STAFF';

SELECT *
FROM titles
WHERE TITLE
IN ('ENGINEER', 'STAFF', 'SENIOR STAFF');

LIKE는 %와 같이 써야 한다. 전수조사 해야해서 속도가 많이 느리다

SQL

SELECT * FROM employees
WHERE FIRST_NAME = 'G'; ->'G'인 사람

SELECT * FROM employees
WHERE FIRST_NAME LIKE 'G%'; -> 'G'로 시작하는 사람

SELECT * FROM employees
WHERE FIRST_NAME LIKE '%G'; -> 'G'로 끝나는 사람

SELECT * FROM employees
WHERE FIRST_NAME LIKE '%MA%'; -> 'MA'가 포함된 사람 

SELECT * FROM employees
WHERE FIRST_NAME LIKE '_A%'; -> 'A'앞에 무조건 한 글자가 오는 사람 -> _가 2개면 2글자 -> _는 잘 안쓴다

SELECT * FROM employees
WHERE FIRST_NAME LIKE '%_A_%'; -> 'A'앞에 무조건 한 글자가 있어야 하고 'A'뒤에 무조건 한글자가 있어야 하는 데 이게 문자열에 포함된 사람

스칼라값 : 컬럼도 한 개, 레코드도 한 개 -> 결과물이 값처럼 쓰인다.
SQL

SELECT GENDER FROM employees WHERE EMP_NO = 10066;

서브쿼리 - 속도가 느려서 최대한 안쓰는 게 좋다
SQL

SELECT * FROM employees WHERE GENDER = 'M';

SELECT * FROM employees WHERE GENDER =
(SELECT GENDER FROM employees WHERE EMP_NO = 10066);

PK가 2개 있을 때
PK1은 여러개 괜찮, PK2은 괜찮
하지만 PK1과 PK2를 정렬했을 때 중복 되는 게 있으면 안됨

ORDER BY
SALARY를 기준으로 오름차순으로 정렬해줘 (ASC가 기본값, 생략되어 있다)
DESC는 내림차순

SQL

SELECT * FROM salaries ORDER BY SALARY;
SELECT * FROM salaries ORDER BY SALARY DESC;

일단 EMP_NO를 기준으로 (생략되어 있으니까 ASC로) 정렬을 끝내고,
EMP_NO가 같은 번호가 있는데, 그것들 안에서 SALARY를 기준으로 내림차순으로 정렬이 되는 것이다.

SQL

SELECT * FROM salaries ORDER BY EMP_NO, SALARY DESC;

,를 쓸 수 있는 기준이 EMP_NO로 ORDER BY했을 때 같은 값이 여러 개일 때 이것을 다시 정렬하고 싶을 때 사용할 수 있다. 같은 값이 여러개 없으면 ,를 써서 ORDER BY 또 해주는 의미가 없다

중복불가 SELECT -> DISTINCT
값이 다르다면 NULL값도 나온다. 근데 PK라면 NULL갑이 처음부터 없겠지.

SELECT DISTINCT TITLE FROM TITLES;

-> 중복되지 않게 어떤 TITLE만 있는지 알아낼 수 있다.

중복된 값을 찾고 싶다면?

SELECT EMP_NO, TITLE
FROM TITLES
GROUP BY EMP_NO, TITLE
HAVING COUNT(EMP_NO) > 1;

SELECT * FROM TITLES
WHERE EMP_NO = 110765
AND TITLE = 'TECHIQUE LEADER';

SQL

 SELECT DISTINCT DEPT_NO FROM dept_emp;

DISTINCT 2개 하면, 2개가 조합되었을 때 중복된 것만 없애준다. PK를 2개 준 것 같은 것 과 같은 효과.
1개 중복제거 1개 중복제거 그리고 그 결과를 반환하는 것은 아니다. 

`SQL`
SELECT DISTINCT DEPT_NO FROM dept_emp; 

SELECT DISTINCT EMP_NO, DEPT_NO FROM dept_emp;

LIMIT - SELECT쿼리문에 가장 마지막에

  • ORDER BY는 그 앞에 -> LIMIT을 안 쓴다면 ORDER BY가 제일 끝

    SQL

SELECT * FROM salaries LIMIT 5; 

SELECT * FROM salaries ORDER BY SALARY DESC LIMIT 5;

드래그 + 선택실행 -> 쿼리 수 만큼 결과가 뜬다.

더 자세히


더 자세히

LIMIT

`SQL`
SELECT * FROM salaries ORDER BY SALARY DESC LIMIT 5;

SELECT * FROM salaries ORDER BY SALARY DESC LIMIT 5, 5;

GROUP BY가 없다면 전체를 기준으로 한다.

COUNT(* 또는 컬럼명) : 그룹함수, 집계함수, 레코드의 갯수가 몇 개인지.

집계함수는 중첩해서 사용할 수 없다.
MAX() : MAX함수, 가장 큰 값이 나온다.
SUM() : SUM함수, 그 컬럼의 모든 값을 다 더한다.

연산식 가능

AVG(): 평균을 낸다 

  `SQL`
SELECT COUNT(*) FROM salaries;

SELECT COUNT(EMP_NO) FROM salaries;

SELECT MAX(SALARY) FROM salaries;

SELECT MIN(SALARY) FROM salaries;

SELECT SUM(SALARY) FROM salaries;

SELECT SUM(SALARY) / COUNT(SALARY) FROM salaries;

SELECT SUM(SALARY) / COUNT(SALARY) AS AVG_SALARY FROM salaries;

SELECT AVG(SALARY) FROM salaries;

COUNT + DISTINCT

`SQL`
SELECT COUNT(DISTINCT DEPT_NO) FROM dept_emp;

GROUP BY ~별로

EMP_NO별로 가장 큰 값을 구하는 것이다.
EMP_NO 중복되는 레코드들이 하나로 압축되는 것이나 마찬가지 이다.

SQL

SELECT EMP_NO, MAX(SALARY) FROM SALARIES GROUP BY EMP_NO;
SELECT EMP_NO, MAX(SALARY), MIN(SALARY), SUM(SALARY) FROM SALARIES GROUP BY EMP_NO;

그 사람이 몇 개월째 다니고 있냐

SQL

SELECT EMP_NO, MAX(SALARY), MIN(SALARY), SUM(SALARY), COUNT(SALARY) FROM SALARIES GROUP BY EMP_NO;
SELECT EMP_NO, MAX(SALARY), MIN(SALARY), SUM(SALARY), COUNT(SALARY) FROM SALARIES GROUP BY EMP_NO ORDER BY AVG(SALARY) DESC;

가장 많이 다닌 순서대로 (내림차순) 개월 수가 같다면 평균급여가 높은 순대로 (내림차순) 나타나도록
SQL

SELECT EMP_NO, MAX(SALARY), MIN(SALARY), SUM(SALARY), COUNT(SALARY), AVG(SALARY) 
FROM salaries 
GROUP BY EMP_NO 
ORDER BY COUNT(SALARY)  DESC, AVG(SALARY) DESC;

HAVING
집계함수의 결과물에서 조건식을 건다
GROUP BY 뒤에 꼭 적어야 하고,
GROUP BY와 세트, 꼭 GROUP BY가 있어야 쓸 수 있다.
가장 많이 다닌 순서대로 (내림차순) 개월 수가 같다면 평균급여가 높은 순대로 (내림차순) 나타나도록 그런데 10개월만 다닌 사람
WHERE -> 에러

SQL

SELECT EMP_NO, MAX(SALARY), MIN(SALARY), SUM(SALARY), COUNT(SALARY), AVG(SALARY) 
FROM salaries 
GROUP BY EMP_NO 
HAVING COUNT(SALARY) = 10
ORDER BY COUNT(SALARY)  DESC, AVG(SALARY) DESC;

SALARIES테이블
EMP_NO 별 평균 급여가 90000이 이상인 EMP_NO, 평균 급여값이 나오도록 , 평균급여 오름차순 순서대로
SQL

SELECT EMP_NO, AVG(SALARY) FROM salaries
GROUP BY EMP_NO
HAVING AVG(SALARY) >= 90000
ORDER BY AVG(SALARY);

SELECT EMP_NO, AVG(SALARY) FROM salaries
ORDER BY AVG(SALARY);

GROUP BY 가 없으면 전체의 AVG가 구해지므로 레코드가 하나만 출력된다.

TITLES 테이블
가장 많은 인원이있는 TITLES이 무엇인지 표시 레코드는 하나만.
집계함수는 중첩해서 사용할 수 없기 떄문에 서브 쿼리를 사용해야 한다.
SQL

SELECT TITLE FROM titles
GROUP BY TITLE
ORDER BY COUNT(TITLE) DESC
LIMIT 1; 
  
 SELECT TITLE, COUNT(TITLE) AS CNT FROM TITLES GROUP BY TITLE;
  
  SELECT MAX(A.CNT) FROM (
  SELECT COUNT(TITLE) AS CNT FROM TITLES GROUP BY TITLE
  ) A; -> 마치 테이블이 된 것 같이 되었다. 
  
  SELECT TITLE FROM TITLES 
  GROUP BY TITLE 
  HAVING COUNT(TITLE) = (
  SELECT MAX(A.CNT) FROM (
  SELECT COUNT(TITLE) AS CNT FROM TITLES GROUP BY TITLE
  ) A
  );

select 컬럼명
from 테이블명
where 조건식 (레코드 수 관련) (AND OR)
group by ~~별
having 그룹바이의 조건식 (AND OR)
order by 뽑아낸 레코드의 순서
limit 10 (처음부터 10개) - 레코드 수를 제한
limit 10, 20 (10번째 부터 20개) - 기본키를 기준으로 

SELECT * FROM DEPT_EMP
ORDER BY EMP_NO ASC
LIMIT 0, 10;

위 아래 다르다

 SELECT * FROM DEPT_EMP
ORDER BY EMP_NO ASC
LIMIT 1, 10;





<BR><BR><BR><BR><BR><BR>
###   DRB에서 스키마가 아주 중요하다
### DB는 속도싸움 쌓일 수록 더 더 더 더 느려진다

### 데이터베이스의 핵심은 모델링이다.
profile
꼬부기의 실수모음.zip 🐢
몇 번을 넘어져도 앞으로 계속 나아가자
이전 포스트

[DB] | [HEIDISQL] | 데이터베이스 생성, 삭제, 테이블 생성, 내용 입력, 주석, CRUD명령어

다음 포스트

[DB] 새로운 곳에 다시 적기 | PRIMARY KEY, INDEX , SELECT (IN, BETWEEN, LIMIT, ORDER BY, LIKE %, GROUP BY, HAVING)

0개의 댓글