[Mysql] mysql select 쿼리 실행 순 익히기, 스키마 create&drop

스키마 생성과 CREATE, DROP

릴레이션 정의 및 생성

create table department(
	deptno integer not null,
	deptname char(10),
	floor integer,
	primary key(deptno));

create table employee(
	empno integer not null,
	empname char(10),
	title char(10),
	manager integer,
	salary integer,
	dno integer,
	primary key(empno),
	foreign key(manager) references employee(empno),
	foreign key(dno) references department(deptno) on delete cascade );

 

제약조건 변경 방법

alter table employee add foreign key(dno) references department(deptno);

alter table employee add constraint empfk1 foreign key(dno) references department(deptno);

 

릴레이션 제거

drop table department;

 

SELECT 처리 순서

1. WHERE / JOIN
2. GROUP BY
3. DISTINCT
4. HAVING
5. ORDER BY
6. LIMIT

FROM 절에 열거된 릴레이션들의 카티션 곱을 구하고, WHERE 절의 조건을 적용하여 튜플들을 골라내고, GROUP BY 절에 의해 결과 튜플들을 그룹화하고, 각 그룹에 HAVING 조건을 적용하여 일부 그룹을 골라내고, SELECT 절에 열거된 애트리뷰트들만 프로젝션 해서, ORDER BY 절에 명시한 순서대로 정렬합니다.

 

1. SELECT

• 데이터베이스에서 원하는 열(칼럼)을 선택하는 데 사용됩니다.

SELECT first_name, last_name, salary FROM employees;

위의 쿼리는 "employees" 테이블에서 "first_name", "last_name", 그리고 "salary" 열을 선택합니다.

 

alias (별칭)

✔️ 서로 다른 릴레이션에 동일한 이름을 가진 애트리뷰트가 속해 있을 때 애트리뷰트의 이름을 구분하는 방법

 

2. FROM

• 데이터를 검색할 테이블을 지정하는 데 사용됩니다.

 

3. JOIN

• 두 개 이상의 테이블을 결합하여 연관된 데이터를 가져올 때 사용됩니다.

SELECT employees.first_name, departments.department_name
FROM employees
JOIN departments 
ON employees.department_id = departments.department_id;

위의 쿼리는 "employees"와 "departments" 테이블을 조인하여 직원의 이름과 그들이 속한 부서의 이름을 가져옵니다.

 

4. WHERE

특정 조건을 만족하는 행만 선택하는 데 사용됩니다.

SELECT product_name, price
FROM products
WHERE price < 50;

위의 쿼리는 "products" 테이블에서 가격이 50보다 작은 제품의 이름과 가격을 선택합니다.

문자열 비교 (Like & %)

select emptname, title, dno from employee where empname like "%이";

• % 는 0개 이상
• _ 는 임의의 한개 문자
• upper(fname), lower(fname)

다수의 검색 조건

AND, IN, (>, =, < , <>), IS NULL

 

5. GROUP BY

• gropu by 절에 사용된 열을 기준으로 동일한 값을 갖는 투플들이 하나의 그룹으로 묶임.

SELECT DNO, AVG(SALARY) AS AVGSAL
FROM EMPLOYEE
GROUP BY DNO;

위의 쿼리는 부서별로 평균 급여를 계산합니다.

 

• 각 그룹에 대하여 결과 릴레이션에 하나의 투플이 생성됨

select 절에는 각 그룹마다 하나의 값을 갖는 애트리뷰트, 집단 함수, 그룹화에 사용된 애트리뷰트들만 나타낼 수 있음

 

6. HAVING

• HAVING 절은 **GROUP BY**와 함께 사용되며, 그룹화된 결과 행에 대한 조건을 설정하는 데 사용됩니다
• 각 그룹마다 하나의 값을 갖는 애트리뷰트를 사용하여 각 그룹이 만족해야 하는 조건을 명시
• HAVING 절에 나타나는 애트리뷰트는 반드시 GROUP BY 절에 나타나거나 집단 함수에 포함되어야 함

SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 50000;

위의 쿼리는 평균 급여가 50,000보다 큰 부서만 선택합니다.

 

7. ORDER BY

• 결과를 특정 열을 기준으로 정렬하는 데 사용됩니다.

SELECT product_name, price
FROM products
ORDER BY price DESC;

위의 쿼리는 제품을 가격에 따라 내림차순으로 정렬하여 선택합니다.

 

• null 값은 오름차순에서 가장 마지막에 나타나고, 내림차순에서는 가장 앞에 나타남

 

집단 함수

• 데이터베이스에서 검색된 여러 투플들의 집단에서 적용되는 함수
• 각 집단 함수는 한 릴레이션의 **한 개의 애트리뷰트**에 적용되어 단일 값을 반환

SELECT 절과 HAVING 절에만 사용할 수 있음

 

• COUNT(*)을 제외하고는 모든 집단 함수들이 널값을 제거한 후 남아 있는 값들에 대해서 집단 함수의 값을 구함
• COUNT(애트리뷰트)는 널값이 아닌 값들의 개수를 구함

SELECT AVG(SALARY) AS AVGSAL, MAX(SALAARY) AS MAXSAL FROM EMPLOYEE;

sum, avg, max, min, count(애트리뷰트)는 중복 값도 1개 튜플로 간주합니다.

“집단함수(distinct 애트리뷰트)”는 중복 값 먼저 제거하고 함수를 적용합니다.

 

DISTINCT

• UNIQUE한 값만 가져오는 경우

 

LIMIT

• 조건에 도달하면 쿼리를 정지
• 가장 마지막에 실행
• LIMIT X, Y : X 번째부터 Y개를 읽어라

 

COUNT

• 특정 테이블에서 원하는 조건에 맞는 ROW를 파악 (개수)