'sql' 카테고리의 글 목록

sql

MySql 기본 예제들 2023.04.10
MySql emplyees 연습용 데이터,문제 2023.04.10
문제 - 상황에 맞는 SQL을 작성하기, INNER JOIN, INDEX, UNIQUE INDEX, SQL_NO_CACHE, EXPLAIN, UUID 2023.04.08
상황에 맞는 SQL을 작성하기, SUM, MAX, MIN, COUNT 2023.04.08
상황에 맞는 SQL작성하기, INNER JOIN, 사원, 부서 2023.04.08
기초 sql 명령문 2023.04.08

MySql 기본 예제들

Nubbie 2023. 4. 10. 10:40

2023. 4. 10. 10:40

# a5 데이터베이스 삭제/생성/선택
 DROP DATABASE IF EXIT `a5`;
 
 CREATE DATABASE `a5`;
 
 USE `a5`;

# 부서(dept) 테이블 생성 및 홍보부서 기획부서 추가
DROP TABLE dept;

CREATE TABLE dept(
  id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,PRIMARY KEY(id),
  regDate DATETIME NOT NULL,
  `name` CHAR(100) NOT NULL
);


INSERT INTO dept
SET regDate = NOW(),
`name` = '홍보';

INSERT INTO dept
SET regDate = NOW(),
`name` = '기획';
# 사원(emp) 테이블 생성 및 홍길동사원(홍보부서), 홍길순사원(홍보부서), 임꺽정사원(기획부서) 추가

CREATE TABLE emp(
  id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,PRIMARY KEY(id),
  regDate DATETIME NOT NULL,
  `name` CHAR(100) NOT NULL,
  deptName CHAR(100) NOT NULL
);

INSERT INTO emp
SET regDate = NOW(),
`name` = '홍길동',
deptName = '홍보';

INSERT INTO emp
SET regDate = NOW(),
`name` = '홍길순',
deptName = '홍보';


INSERT INTO emp
SET regDate = NOW(),
`name` = '임꺽정',
deptName = '기획';

DELETE
FROM emp
WHERE id = 5;

SELECT *
FROM emp;
# 홍보를 마케팅으로 변경

UPDATE dept
SET `name` = '마케팅'
WHERE `name` = '홍보';



# 마케팅을 홍보로 변경
UPDATE dept
SET `name` = '홍보'
WHERE `name` = '마케팅';


# 홍보를 마케팅으로 변경

UPDATE dept
SET `name` = '마케팅'
WHERE `name` = '홍보';
# 구조를 변경하기로 결정(사원 테이블에서, 이제는 부서를 이름이 아닌 번호로 기억)

ALTER TABLE emp ADD COLUMN deptId INT(10) UNSIGNED NOT NULL;

UPDATE emp
SET deptId = 1
WHERE deptName = '홍보';

UPDATE emp
SET deptId = 2
WHERE deptName = '기획';

# 사장님께 드릴 인명록을 생성

SELECT *
FROM emp;

# 사장님께서 부서번호가 아니라 부서명을 알고 싶어하신다.
# 그래서 dept 테이블 조회법을 알려드리고 혼이 났다.

# 사장님께 드릴 인명록을 생성(v2, 부서명 포함, ON 없이)
# 이상한 데이터가 생성되어서 혼남

# 사장님께 드릴 인명록을 생성(v3, 부서명 포함, 올바른 조인 룰(ON) 적용)
# 보고용으로 좀 더 편하게 보여지도록 고쳐야 한다고 지적받음

# 사장님께 드릴 인명록을 생성(v4, 사장님께서 보시기에 편한 칼럼명(AS))
SELECT emp.id AS `사원번호`,
emp.name AS `사원명`,
DATE(emp.regDate) AS `입사일`,
dept.name AS `부서명`
FROM emp
INNER JOIN dept
ON emp.deptId = dept.id
ORDER BY `부서명`, `사원번호`;



SHOW TABLES;

DROP TABLE dept;
DROP TABLE emp;

# 부서(홍보, 기획)
CREATE TABLE dept(
id INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
regDate DATETIME NOT NULL,
`name` CHAR(100) NOT NULL UNIQUE

);


INSERT INTO dept
SET regDate = NOW(),
`name` = '홍보';



INSERT INTO dept
SET regDate = NOW(),
`name` = '기획';


# 사원(홍길동/홍보/5000만원, 홍길순/홍보/6000만원, 임꺽정/기획/4000만원)

CREATE TABLE emp (
    id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    regDate DATETIME NOT NULL,
    `name` CHAR(100) NOT NULL,
    deptId INT UNSIGNED NOT NULL,
    salary INT UNSIGNED NOT NULL
);

INSERT INTO emp
SET regDate = NOW(),
`name` = '홍길동',
deptId = 1,
salary = 5000;

INSERT INTO emp
SET regDate = NOW(),
`name` = '홍길순',
deptId = 1,
salary = 6000;

INSERT INTO emp
SET regDate = NOW(),
`name` = '임꺽정',
deptId = 2,
salary = 4000;

SELECT *
FROM emp;

DELETE 
FROM emp
WHERE id = 4;

# 사원 수 출력
SELECT COUNT(*)
FROM emp;

# 가장 큰 사원 번호 출력

SELECT MAX(id)
FROM emp;
# 가장 고액 연봉

SELECT MAX(salary)
FROM emp;

# 가장 저액 연봉

SELECT MIN(salary)
FROM emp;

# 회사에서 1년 고정 지출(인건비)

SELECT SUM(salary)
FROM emp;

# 부서별, 1년 고정 지출(인건비)

SELECT deptId, SUM(salary)
FROM emp
GROUP BY deptId;


# 부서별, 최고연봉

SELECT deptId, MAX(salary)
FROM emp
GROUP BY deptId
LIMIT 1;


# 부서별, 최저연봉
SELECT deptId, MIN(salary)
FROM emp
GROUP BY deptId;



# 부서별, 평균연봉

SELECT deptId, AVG(salary)
FROM emp
GROUP BY deptId;
# 부서별, 부서명, 사원리스트, 평균연봉, 최고연봉, 최소연봉, 사원수 
## V1(조인 안한 버전)
SELECT E.deptId AS 부서번호,
GROUP_CONCAT(E.name) AS 사원리스트,
TRUNCATE(AVG(E.salary), 0) AS 평균연봉,
MAX(E.salary) AS 최고연봉,
MIN(E.salary) AS 최소연봉,
COUNT(*) A`jam``jam``jam`S 사원수
FROM emp AS E
GROUP BY E.deptId;

## V2(조인해서 부서명까지 나오는 버전)
SELECT D.name AS 부서,
GROUP_CONCAT(E.name) AS 사원리스트,
TRUNCATE(AVG(E.salary), 0) AS 평균연봉,
MAX(E.salary) AS 최고연봉,
MIN(E.salary) AS 최소연봉,
COUNT(*) AS 사원수
FROM emp AS E
INNER JOIN dept AS D
ON E.deptId = D.id
GROUP BY E.deptId;

## V3(V2에서 평균연봉이 5000이상인 부서로 추리기)

SELECT D.name AS 부서,
GROUP_CONCAT(E.name) AS 사원리스트,
TRUNCATE(AVG(E.salary), 0) AS 평균연봉,
MAX(E.salary) AS 최고연봉,
MIN(E.salary) AS 최소연봉,
COUNT(*) AS 사원수
FROM emp AS E
INNER JOIN dept AS D
ON E.deptId = D.id
GROUP BY E.deptId
HAVING `평균연봉` >= 5000;
## V4(V3에서 HAVING 없이 서브쿼리로 수행)
### HINT, UNION을 이용한 서브쿼리
# SELECT *
# FROM (
#     select 1 AS id
#     union
#     select 2
#     UNION
#     select 3
# ) AS A
SELECT *
FROM (
    SELECT D.name AS `부서명`,
    GROUP_CONCAT(E.`name`) AS `사원리스트`,
    TRUNCATE(AVG(E.salary), 0) AS `평균연봉`,
    MAX(E.salary) AS `최고연봉`,
    MIN(E.salary) AS `최소연봉`,
    COUNT(*) AS `사원수`
    FROM emp AS E
    INNER JOIN dept AS D
    ON E.deptId = D.id
    WHERE 1
    GROUP BY E.deptId
) AS D
WHERE D.`평균연봉` >= 5000;

#################

#상황 : 커뮤니티 사이트 DB를 구축해야 합니다.
#조건 : member(회원), article(게시물) 테이블을 구현해주세요.
#조건 : 비회원은 글을 쓸 수 없습니다.
#조건 : 게시물 상세페이지에서는 제목, 내용, 작성날짜, 작성자가 보여야 합니다.
#조건 : 특정 게시물을 어떤 회원이 작성했는지 알 수 있어야 합니다.
#조건 : 회원이 자신의 이름을 바꾸면, 그 회원이 예전에 쓴 글에서도 작성자가 자동으로 변경되어야 합니다.
#조건 : 회원 아이디와 비번의 칼럼명은, loginId 와 loginPw 로 해주세요.
#조건 : loginId에는 unique 인덱스를 걸어주세요.
#조건 : 회원 2명이 가입을 햇습니다.
#조건 : 1번 회원은 글을 2개(글 1번, 3번), 2번 회원은 글을 1개(2번) 썻습니다.


# 회원 테이블 생성, loginId, loginPw, `name`
## 조건 : loginId 칼럼에 UNIQUE INDEX 없이
CREATE TABLE `member` (
    id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    PRIMARY KEY(id),
    regDate DATETIME NOT NULL,
    loginId CHAR(50) NOT NULL,
    loginPw VARCHAR(100) NOT NULL,
    `name` CHAR(100) NOT NULL
);

# 회원 2명 생성
## 조건 : (loginId = 'user1', loginPw = 'user1', `name` = '홍길동')
## 조건 : (loginId = 'user2', loginPw = 'user2', `name` = '홍길순')
INSERT INTO `member`
SET regDate = NOW(),
loginId = 'user1',
loginPw = 'user1',
`name` = '홍길동';

INSERT INTO `member`
SET regDate = NOW(),
loginId = 'user2',
loginPw = 'user2',
`name` = '홍길순';

# 회원 2배 증가 쿼리만들고 회원이 백만명 넘을 때 까지 반복 실행
## 힌트1 : INSERT INTO `tableName` (col1, col2, col3, col4)
## 힌트2 : SELECT NOW(), UUID(), 'pw', '아무개'

INSERT INTO `member` (regDate, loginId, loginPw, `name`)
SELECT NOW(), UUID(), 'pw', '아무개'
FROM `member`;

# 회원수 확인
SELECT COUNT(*)
FROM `member`;

# 검색속도 확인
## 힌트 : SQL_NO_CACHE
SELECT SQL_NO_CACHE *
FROM `member`
WHERE loginId = 'user1';

# 유니크 인덱스를 loginID 칼럼에 걸기
## 설명 : mysql이 loginId의 고속검색을 위한 부가데이터를 자동으로 관리(생성/수정/삭제) 한다.
## 설명 : 이게 있고 없고가, 특정 상황에서 어마어마한 성능차이를 가져온다.
## 설명 : 생성된 인덱스의 이름은 기본적으로 칼럼명과 같다.
ALTER TABLE `member` ADD UNIQUE INDEX (`loginId`); 

# 검색속도 확인, loginId 가 'user1' 인 회원 검색
SELECT SQL_NO_CACHE *
FROM `member`
WHERE loginId = 'user1';

# 인덱스 삭제, `loginId` 이라는 이름의 인덱스 삭제
ALTER TABLE `member` DROP INDEX `loginId`;

# 회원 테이블 삭제
DROP TABLE `member`;

# 회원 테이블을 생성하는데, loginId에 uniqueIndex 까지 걸어주세요.
CREATE TABLE `member` (
    id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    PRIMARY KEY(id),
    regDate DATETIME NOT NULL,
    loginId CHAR(50) UNIQUE NOT NULL,
    loginPw VARCHAR(100) NOT NULL,
    `name` CHAR(100) NOT NULL
);

# 회원 2명 생성
## 조건 : (loginId = 'user1', loginPw = 'user1', `name` = '홍길동')
## 조건 : (loginId = 'user2', loginPw = 'user2', `name` = '홍길순')
INSERT INTO `member`
SET regDate = NOW(),
loginId = 'user1',
loginPw = 'user1',
`name` = '홍길동';

INSERT INTO `member`
SET regDate = NOW(),
loginId = 'user2',
loginPw = 'user2',
`name` = '홍길순';

# 회원수 확인
SELECT COUNT(*) FROM `member`;

# 인덱스 쓰는지 확인
## 힌트 : EXPLAIN SELECT SQL_NO_CACHE * ~
EXPLAIN SELECT SQL_NO_CACHE *
FROM `member`
WHERE loginId = 'user1';

# 게시물 테이블 생성(title, body, writerName, memberId)
DROP TABLE `article`;

CREATE TABLE `article` (
    id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    PRIMARY KEY(id),
    regDate DATETIME NOT NULL,
    title CHAR(200) NOT NULL,
    `body` TEXT NOT NULL,
    `writerName` CHAR(100) NOT NULL,
    memberId INT(10) UNSIGNED NOT NULL
);

# 1번 회원이 글 1 작성(title = '글 1 제목', `body` = '글 1 내용')
INSERT INTO article
SET regDate = NOW(),
title = '글 1 제목',
`body` = '글 1 내용',
writerName = '홍길동',
memberId = 1;

# 2번 회원이 글 2 작성(title = '글 2 제목', `body` = '글 2 내용')
INSERT INTO article
SET regDate = NOW(),
title = '글 2 제목',
`body` = '글 2 내용',
writerName = '홍길순',
memberId = 2;

# 1번 회원이 글 3 작성(title = '글 3 제목', `body` = '글 3 내용')
INSERT INTO article
SET regDate = NOW(),
title = '글 3 제목',
`body` = '글 3 내용',
writerName = '홍길동',
memberId = 1;

# 전체글 조회
SELECT *
FROM article;

# 1번회원의 이름변경 홍길동 => 홍길동2
UPDATE `member`
SET `name` = '홍길동2'
WHERE id = 1;

# 전체글 조회, 여전히 게시물 테이블에는 이전 이름이 남아 있음
SELECT *
FROM article;

# 게시물 테이블에서 writerName 삭제
ALTER TABLE article DROP COLUMN writerName;

# INNER JOIN 을 통해서 두 테이블을 조회한 결과를 합침, ON 없이
SELECT * FROM article
INNER JOIN `member`;

# INNER JOIN 을 통해서 두 테이블을 조회한 결과를 합침, 올바른 조인 조건

## 힌트 : 이걸로 조인조건을 걸 칼럼 조사
## SELECT article.id, article.memberId, member.id AS "회원테이블_번호"
## FROM article
## INNER JOIN `member`;

# 조인 완성, ON 까지 추가
SELECT A.*, M.name AS writerName
FROM article AS A
INNER JOIN `member` AS M
ON A.memberId = M.id;

저작자표시 (새창열림)

'sql' 카테고리의 다른 글

MySql emplyees 연습용 데이터,문제 (0)	2023.04.10
문제 - 상황에 맞는 SQL을 작성하기, INNER JOIN, INDEX, UNIQUE INDEX, SQL_NO_CACHE, EXPLAIN, UUID (0)	2023.04.08
상황에 맞는 SQL을 작성하기, SUM, MAX, MIN, COUNT (0)	2023.04.08
상황에 맞는 SQL작성하기, INNER JOIN, 사원, 부서 (0)	2023.04.08
기초 sql 명령문 (0)	2023.04.08

MySql emplyees 연습용 데이터,문제

Nubbie 2023. 4. 10. 10:07

2023. 4. 10. 10:07

연습용 데이터 베이스 생성

####데이터 초기화
DROP DATABASE employees;


USE `cgh`;

SHOW TABLES;



###############################
#TABLE 생성

CREATE TABLE DEPT (
    DEPTNO DECIMAL(2),
    DNAME VARCHAR(14),
    LOC VARCHAR(13),
    CONSTRAINT PK_DEPT PRIMARY KEY (DEPTNO) 
);
CREATE TABLE EMP (
    EMPNO DECIMAL(4),
    ENAME VARCHAR(10),
    JOB VARCHAR(9),
    MGR DECIMAL(4),
    HIREDATE DATE,
    SAL DECIMAL(7,2),
    COMM DECIMAL(7,2),
    DEPTNO DECIMAL(2),
    CONSTRAINT PK_EMP PRIMARY KEY (EMPNO),
    CONSTRAINT FK_DEPTNO FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO)
);
CREATE TABLE SALGRADE ( 
    GRADE TINYINT,
    LOSAL SMALLINT,
    HISAL SMALLINT 
);
INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');
INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,STR_TO_DATE('17-12-1980','%d-%m-%Y'),800,NULL,20);
INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,STR_TO_DATE('20-2-1981','%d-%m-%Y'),1600,300,30);
INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,STR_TO_DATE('22-2-1981','%d-%m-%Y'),1250,500,30);
INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,STR_TO_DATE('2-4-1981','%d-%m-%Y'),2975,NULL,20);
INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,STR_TO_DATE('28-9-1981','%d-%m-%Y'),1250,1400,30);
INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,STR_TO_DATE('1-5-1981','%d-%m-%Y'),2850,NULL,30);
INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,STR_TO_DATE('9-6-1981','%d-%m-%Y'),2450,NULL,10);
INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,STR_TO_DATE('13-7-1987','%d-%m-%Y')-85,3000,NULL,20);
INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,STR_TO_DATE('17-11-1981','%d-%m-%Y'),5000,NULL,10);
INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,STR_TO_DATE('8-9-1981','%d-%m-%Y'),1500,0,30);
INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,STR_TO_DATE('13-7-1987', '%d-%m-%Y'),1100,NULL,20);
INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,STR_TO_DATE('3-12-1981','%d-%m-%Y'),950,NULL,30);
INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,STR_TO_DATE('3-12-1981','%d-%m-%Y'),3000,NULL,20);
INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,STR_TO_DATE('23-1-1982','%d-%m-%Y'),1300,NULL,10);
INSERT INTO SALGRADE VALUES (1,700,1200);
INSERT INTO SALGRADE VALUES (2,1201,1400);
INSERT INTO SALGRADE VALUES (3,1401,2000);
INSERT INTO SALGRADE VALUES (4,2001,3000);
INSERT INTO SALGRADE VALUES (5,3001,9999);
COMMIT;
##########################################################################################

셀렉트문들과 각종 연습문제

SELECT *
FROM emp;


SELECT empno,ename,job,mgr,hiredate,sal,comm,deptno
FROM emp;

SELECT ename,hiredate
FROM emp;

SELECT deptno, ename
FROM emp;



SELECT DISTINCT(job)
FROM emp;

SELECT COUNT(empno)
FROM emp;


SELECT *
FROM emp
WHERE deptno = 10;

SELECT *
FROM emp
WHERE sal >= 2500;

SELECT *
FROM emp
WHERE ename = 'king';

SELECT empno,ename
FROM emp
WHERE ename LIKE 's%';

SELECT empno,ename
FROM emp
WHERE ename LIKE '%T%';

SELECT empno,ename,comm
FROM emp
WHERE comm IN(300,500,1400);

SELECT empno,ename,sal
FROM emp
WHERE sal BETWEEN 1200
AND 3500;

SELECT ename,empno,job,deptno
FROM emp
WHERE deptno = 30 AND job = 'manager';

SELECT empno,ename,deptno
FROM emp
WHERE NOT deptno = 30;

SELECT empno,ename,comm
FROM emp
WHERE comm NOT IN (300,500,1400);

SELECT empno,ename
FROM emp
WHERE ename NOT LIKE '%S%';

SELECT empno,ename,sal
FROM emp
WHERE sal NOT BETWEEN 1200 AND 3700;

SELECT ename,job
FROM emp
WHERE mgr IS NULL;

SELECT deptno,AVG(sal)
FROM emp;

SELECT deptno,AVG(sal)
FROM emp
GROUP BY deptno;

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

SELECT deptno,MAX(sal),MIN(sal)
FROM emp
GROUP BY deptno;

SELECT deptno,AVG(sal)
FROM emp
GROUP BY deptno
HAVING AVG(sal) >= 2000;

SELECT deptno,AVG(sal)
FROM emp
WHERE sal >= 1000
GROUP BY deptno
HAVING AVG(sal) >= 2000;

SELECT empno,ename,sal
FROM emp
ORDER BY sal DESC,ename ASC;

SELECT emp.ename,dept.dname
FROM emp,dept;

SELECT ename,dname
FROM emp,dept
WHERE emp.deptno = dept.deptno;

SELECT e.ename,d.dname
FROM emp AS e,dept AS d
WHERE e.deptno = d.deptno;

SELECT e.ename , m.ename
FROM emp AS e
INNER JOIN emp m
ON e.mgr = m.empno;

SELECT e.ename,e.sal,s.grade
FROM emp AS e,salgrade AS s
WHERE e.sal >= s.losal
AND  e.sal <= s.hisal;

SELECT e.ename,e.sal,s.grade
FROM emp AS e,salgrade AS s
WHERE e.sal BETWEEN s.losal
AND s.hisal;


SELECT e.ename,d.dname,s.grade
FROM emp AS e, dept AS d,salgrade AS s
WHERE e.deptno = d.deptno
AND e.sal BETWEEN s.losal
AND s.hisal;

SELECT e.ename,m.ename
FROM emp AS e, emp AS m
WHERE e.mgr = m.empno;


SELECT e.ename,m.ename
FROM emp AS e
LEFT JOIN emp AS m
ON e.mgr = m.empno;

SELECT e.ename,d.dname
FROM dept AS d
LEFT JOIN emp AS e
ON d.deptno = e.deptno;

SELECT ename,e.deptno,dname
FROM emp AS e, dept AS d
WHERE e.deptno = d.deptno;

SELECT ename,job,e.deptno,loc
FROM emp AS e , dept AS d
WHERE e.deptno = d.deptno
AND d.deptno = 30;


SELECT ename,job,e.deptno,loc
FROM emp AS e
INNER JOIN dept AS d
ON e.deptno = d.deptno
WHERE d.deptno = 30;

SELECT ename,comm,dname,loc
FROM emp,dept
WHERE emp.deptno = dept.deptno
AND emp.comm IS NOT NULL AND emp.comm <>0;

SELECT ename,comm,dname,loc
FROM emp,dept
WHERE emp.deptno = dept.deptno
AND emp.comm IS NOT NULL 
AND emp.comm <> 0;

SELECT e.ename,e.job,d.deptno,d.dname
FROM emp AS e,dept AS d
WHERE e.deptno = d.deptno
AND d.loc = 'dallas';

SELECT e.ename,d.dname
FROM emp AS e , dept AS d
WHERE e.deptno = d.deptno
AND e.ename LIKE '%A%';


SELECT e.ename,e.job,e.sal,s.grade
FROM emp AS e,salgrade AS s
WHERE e.sal BETWEEN s.losal
AND s.hisal;

SELECT c.ename,c.deptno
FROM emp AS e, emp AS c
WHERE e.empno <> c.empno
AND e.deptno = c.deptno
AND e.ename = 'allen'
ORDER BY c.ename;

SELECT c.ename,c.deptno
FROM emp AS e
INNER JOIN emp c ON e.deptno = c.deptno
WHERE e.empno <> c.empno
AND e.ename = 'allen'
ORDER BY c.ename;

SELECT dname
FROM dept
WHERE deptno = (SELECT deptno FROM emp WHERE ename = 'jones');

SELECT e.ename,d.dname
FROM emp AS e, dept AS d
WHERE e.deptno = d.deptno
AND d.deptno = 10;

SELECT e.ename,d.dname
FROM emp AS e,
(SELECT deptno , dname
 FROM dept
 WHERE deptno = 10
 )AS d
 WHERE e.deptno = d.deptno;
 
 SELECT empno,ename,sal
 FROM emp
 WHERE sal >( SELECT AVG(sal) FROM emp)
 ORDER BY sal DESC;
 
 SELECT empno,ename
 FROM emp
 WHERE sal = (SELECT MAX(sal)
 FROM emp
 WHERE deptno = 10);
 
 
 
 
SELECT e.ename, d.dname, e.sal
FROM emp AS e, dept AS d
WHERE e.deptno = d.deptno
AND e.sal BETWEEN 3000 AND 5000;

SELECT e.ename, d.dname, e.sal
FROM emp AS e, dept AS d
WHERE e.deptno = d.deptno
AND e.job = 'manager';

SELECT e.ename, e.hiredate
FROM emp AS e , dept AS d
WHERE e.deptno = d.deptno AND d.dname = 'accounting';

SELECT e.ename,e.hiredate, e.comm
FROM emp AS e , dept AS d
WHERE e.deptno = d.deptno
AND e.comm IS NOT NULL
AND e.comm != 0;

SELECT e.ename , e.sal
FROM emp AS e , dept AS d
WHERE e.deptno = d.deptno
AND d.loc = 'NEW YORK';



SELECT e.ename , e.sal , d.loc
FROM emp AS e, dept AS d
WHERE e.deptno = d.deptno
AND e.sal <= 1200
ORDER BY e.sal ASC;

SELECT friend.ename AS "스미스 동료"
FROM emp `work` , emp friend
WHERE work.deptno = friend.deptno
AND work.ename = 'SMITH' 
AND friend.ename = 'SMITH'; 

SELECT work.ename, work.job
FROM emp `work`, emp manager
WHERE work.mgr = manager.empno
AND manager.ename = 'KING';


SELECT e.ename , d.dname, e.sal
FROM emp AS e
INNER JOIN dept AS d
ON e.deptno = d.deptno
AND e.sal BETWEEN 3000 AND 5000;

SELECT e.ename , d.dname, e.sal
FROM emp AS e
INNER JOIN dept AS d
ON e.deptno = d.deptno
AND e.job = 'manager';

SELECT e.ename, e.hiredate
FROM emp AS e
INNER JOIN dept AS d
ON e.deptno = d.deptno
WHERE d.dname = 'ACCOUNTING';

SELECT * FROM dept;
SELECT * FROM emp;
SHOW TABLES;

SELECT e.ename , e.hiredate , e.comm
FROM emp AS e
INNER JOIN dept AS d
ON e.deptno = d.deptno
WHERE e.comm IS NOT NULL
AND e.comm <> 0;

SELECT e.ename , e.sal
FROM emp AS e
INNER JOIN dept AS d
ON e.deptno = d.deptno
WHERE d.loc = 'new york'
ORDER BY e.sal ASC;

SELECT e.ename, e.sal , d.loc
FROM emp AS e
INNER JOIN dept AS d
ON e.deptno = d.deptno
WHERE e.sal <= 1200
ORDER BY e.sal ASC;


SELECT friend.ename AS "스미스 동료"
FROM emp WORK
INNER JOIN emp friend
ON work.deptno = friend.deptno
WHERE work.ename = 'smith' AND friend.ename <> 'smith';

SELECT *
FROM emp;

SELECT SUM(DISTINCT(sal)) , SUM(DISTINCT(comm)),SUM(sal),SUM(sal)
FROM emp;

SELECT COUNT(*)
FROM emp
WHERE deptno = 30;

SELECT COUNT(*)
FROM emp
WHERE comm IS NOT NULL;

SELECT ROUND(AVG(sal),1)
FROM emp;

SELECT SUM(sal),AVG(sal),ROUND(AVG(sal)),MAX(sal),MIN(sal)
FROM emp;

SELECT deptno , ROUND(AVG(sal))
FROM emp
GROUP BY deptno;

SELECT deptno , job , ROUND(AVG(sal)) desc_sal
FROM emp
GROUP BY deptno , job
ORDER BY deptno DESC,desc_sal DESC;

SELECT ROUND(AVG(sal))
FROM emp
GROUP BY deptno;

SELECT COUNT(*)
FROM emp;

SELECT *
FROM emp;

SELECT *
FROM dept;

1. 문제) 부서번호가 10번인 부서의 사람 중 사원번호, 이름, 월급을

출력하라

SELECT empno, ename, sal 
FROM emp 
WHERE deptno=10;



2. 문제) 사원번호가 7369인 사람 중 이름, 입사일, 부서번호를 출력하라.

SELECT ename, hiredate, deptno 
FROM emp 
WHERE empno = 7369;



3. 문제) 이름이 ALLEN인 사람의 모든 정보를 출력하라.



SELECT * 
FROM emp 
WHERE ename = 'allen';



4. 문제) 입사일이 83/01/12인 사원의 이름, 부서번호, 월급을 출력하라.

SELECT ename,deptno,sal 
FROM emp 
WHERE hiredate ='1983-01-12';



5. 문제) 직업이 MANAGER가 아닌 사람의 모든 정보를 출력하라.

SELECT * 
FROM emp 
WHERE job <> 'manager';



6. 문제) 입사일이 81/04/02 이후에 입사한 사원의 정보를 출력하라.

SELECT * 
FROM emp 
WHERE hiredate > '1981-04-02';





7. 문제) 급여가 $800 이상인 사람의 이름, 급여, 부서번호를 출력하라.

SELECT ename, sal, deptno 
FROM emp 
WHERE sal >800;



8. 문제) 부서번호가 20번 이상인 사원의 모든 정보를 출력하라.

SELECT * 
FROM emp 
WHERE deptno>=20;



9. 문제) 이름이 K로 시작하는 사람보다 높은 이름을 가진 사람의 모든

정보를 출력하라.

SELECT * 
FROM emp 
WHERE ename >= 'l';



10. 문제) 입사일이 81/12/09 보다 먼저 입사한 사람들의 모든 정보를

출력하라.

SELECT * 
FROM emp 
WHERE hiredate <'1981-12-09';



11. 문제) 입사번호가 7698보다 작거나 같은 사람들의 입사번호와 이름을

출력하라.

SELECT empno, ename 
FROM emp 
WHERE empno<7698;



12. 문제) 입사일이 81/04/02 보다 늦고 82/12/09 보다 빠른 사원의 이름,

월급, 부서번호를 출력하라.



SELECT ename, sal, deptno 
FROM emp 
WHERE hiredate < '1982-12-09' 
AND hiredate > '1981-12-09';



13. 문제) 급여가 $1,600보다 크고 $3,000보다 작은 사람의 이름, 직업,

급여를 출력하라.



SELECT ename, job, sal 
FROM emp 
WHERE sal 
BETWEEN 1600 AND 3000;



14. 문제) 사원번호가 7654와 7782 사이 이외의 사원의 모든 정보를

출력하라.

SELECT * 
FROM emp 
WHERE empno BETWEEN 7654 AND 7782;



15. 문제) 이름이 B와 J 사이의 모든 사원의 정보를 출력하라.



SELECT * 
FROM emp 
WHERE ename BETWEEN 'b' AND 'j';



16. 문제) 입사일이 81년 이외에 입사한 사람의 모든 정보를 출력하라.

SELECT * 
FROM emp 
WHERE YEAR(hiredate) <> '1981';



17. 문제) 직업이 MANAGER와 SALESMAN인 사람의 모든 정보를 출력하라.

SELECT * 
FROM emp 
WHERE job ='manager' OR job ='salesman';



18. 문제) 부서번호와 20, 30번을 제외한 모든 사람의 이름, 사원번호, 부서번호를 출력하라.



SELECT ename, empno, deptno 
FROM emp 
WHERE deptno <> 20 AND deptno <> 30;





19. 문제) 이름이 S로 시작하는 사원의 사원번호, 이름, 입사일, 부서번호

를 출력하라.

SELECT ename, hiredate, empno, deptno 
FROM emp 
WHERE ename LIKE 's%';



20. 문제) 입사일이 81년도인 사람의 모든 정보를 출력하라

SELECT * 
FROM emp 
WHERE YEAR(hiredate) = '1981';



21. 문제) 이름 중 S자가 들어가 있는 사람만 모든 정보를 출력하라.

SELECT * 
FROM emp 
WHERE ename LIKE '%s%';



22. 문제) 이름이 S로 시작하고 마지막 글자가 T인 사람의 모든 정보를

출력하라(단, 이름은 전체 5자리이다)

SELECT * 
FROM emp 
WHERE ename 
LIKE 's%' 
AND ename LIKE '%t';



23. 문제) 첫 번째 문자는 관계없고, 두 번째 문자가 A인 사람의 정보를

출력하라.

SELECT * 
FROM emp 
WHERE ename LIKE '_a%';



24. 문제) 커미션이 NULL인 사람의 정보를 출력하라.

SELECT * 
FROM emp 
WHERE comm IS NULL;



25. 문제) 커미션이 NULL이 아닌 사람의 모든 정보를 출력하라.

SELECT * 
FROM emp 
WHERE comm IS NOT NULL;



26. 문제) 부서가 30번 부서이고 급여가 $1,500 이상인 사람의 이름,

부서 ,월급을 출력하라.

SELECT ename, deptno, sal 
FROM emp 
WHERE deptno =30 AND sal>= 1500;



27. 문제) 이름의 첫 글자가 K로 시작하거나 부서번호가 30인 사람의

사원번호, 이름, 부서번호를 출력하라.

SELECT empno,ename, deptno 
FROM emp 
WHERE ename LIKE 'k%'OR deptno =30;



28. 문제) 급여가 $1,500 이상이고 부서번호가 30번인 사원 중 직업이

MANAGER인 사람의 정보를 출력하라

SELECT * 
FROM emp 
WHERE sal>=1500 
AND deptno=30 AND job='manager';



29. 문제) 부서번호가 30인 사람 중 사원번호 SORT하라.

SELECT * 
FROM emp 
WHERE deptno=30 ORDER BY empno;



30. 문제) 급여가 많은 순으로 SORT하라.

SELECT * 
FROM emp 
ORDER BY sal DESC;



31. 문제) 부서번호로 ASCENDING SORT한 후 급여가 많은 사람 순으로

출력하라.

SELECT *
FROM emp 
ORDER BY deptno ASC, sal DESC;



32. 문제) 부서번호가 DESCENDING SORT하고, 이름 순으로 ASCENDING SORT,

급여 순으로 DESCENDING SORT 하라.

SELECT *
FROM emp 
ORDER BY deptno DESC, ename ASC, sal DESC;


SHOW TABLES;

SELECT *
FROM dept;

SELECT *
FROM emp;

SELECT *
FROM salgrade;

저작자표시 (새창열림)

'sql' 카테고리의 다른 글

MySql 기본 예제들 (0)	2023.04.10
문제 - 상황에 맞는 SQL을 작성하기, INNER JOIN, INDEX, UNIQUE INDEX, SQL_NO_CACHE, EXPLAIN, UUID (0)	2023.04.08
상황에 맞는 SQL을 작성하기, SUM, MAX, MIN, COUNT (0)	2023.04.08
상황에 맞는 SQL작성하기, INNER JOIN, 사원, 부서 (0)	2023.04.08
기초 sql 명령문 (0)	2023.04.08

문제 - 상황에 맞는 SQL을 작성하기, INNER JOIN, INDEX, UNIQUE INDEX, SQL_NO_CACHE, EXPLAIN, UUID

Nubbie 2023. 4. 8. 14:18

2023. 4. 8. 14:18

# 데이터베이스 a4가 존재하면 삭제
DROP DATABASE IF EXISTS a4;

# 데이터베이스 a4 생성
CREATE DATABASE a4;

# 데이터베이스 a4 선택
USE a4;

# 회원 테이블 생성, loginId, loginPw, `name`
## 조건 : loginId 칼럼에 UNIQUE INDEX 없이
CREATE TABLE `member` (
    id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    PRIMARY KEY(id),
    regDate DATETIME NOT NULL,
    loginId CHAR(50) NOT NULL,
    loginPw VARCHAR(100) NOT NULL,
    `name` CHAR(100) NOT NULL
);

# 회원 2명 생성
## 조건 : (loginId = 'user1', loginPw = 'user1', `name` = '홍길동')
## 조건 : (loginId = 'user2', loginPw = 'user2', `name` = '홍길순')
INSERT INTO `member`
SET regDate = NOW(),
loginId = 'user1',
loginPw = 'user1',
`name` = '홍길동';

INSERT INTO `member`
SET regDate = NOW(),
loginId = 'user2',
loginPw = 'user2',
`name` = '홍길순';

# 회원 2배 증가 쿼리만들고 회원이 백만명 넘을 때 까지 반복 실행
## 힌트1 : INSERT INTO `tableName` (col1, col2, col3, col4)
## 힌트2 : SELECT NOW(), UUID(), 'pw', '아무개'

INSERT INTO `member` (regDate, loginId, loginPw, `name`)
SELECT NOW(), UUID(), 'pw', '아무개'
FROM `member`;

# 회원수 확인
SELECT COUNT(*)
FROM `member`;

# 검색속도 확인
## 힌트 : SQL_NO_CACHE
SELECT SQL_NO_CACHE *
FROM `member`
WHERE loginId = 'user1';

# 유니크 인덱스를 loginID 칼럼에 걸기
## 설명 : mysql이 loginId의 고속검색을 위한 부가데이터를 자동으로 관리(생성/수정/삭제) 한다.
## 설명 : 이게 있고 없고가, 특정 상황에서 어마어마한 성능차이를 가져온다.
## 설명 : 생성된 인덱스의 이름은 기본적으로 칼럼명과 같다.
ALTER TABLE `member` ADD UNIQUE INDEX (`loginId`); 

# 검색속도 확인, loginId 가 'user1' 인 회원 검색
SELECT SQL_NO_CACHE *
FROM `member`
WHERE loginId = 'user1';

# 인덱스 삭제, `loginId` 이라는 이름의 인덱스 삭제
ALTER TABLE `member` DROP INDEX `loginId`;

# 회원 테이블 삭제
DROP TABLE `member`;

# 회원 테이블을 생성하는데, loginId에 uniqueIndex 까지 걸어주세요.
CREATE TABLE `member` (
    id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    PRIMARY KEY(id),
    regDate DATETIME NOT NULL,
    loginId CHAR(50) UNIQUE NOT NULL,
    loginPw VARCHAR(100) NOT NULL,
    `name` CHAR(100) NOT NULL
);

# 회원 2명 생성
## 조건 : (loginId = 'user1', loginPw = 'user1', `name` = '홍길동')
## 조건 : (loginId = 'user2', loginPw = 'user2', `name` = '홍길순')
INSERT INTO `member`
SET regDate = NOW(),
loginId = 'user1',
loginPw = 'user1',
`name` = '홍길동';

INSERT INTO `member`
SET regDate = NOW(),
loginId = 'user2',
loginPw = 'user2',
`name` = '홍길순';

# 회원수 확인
SELECT COUNT(*) FROM `member`;

# 인덱스 쓰는지 확인
## 힌트 : EXPLAIN SELECT SQL_NO_CACHE * ~
EXPLAIN SELECT SQL_NO_CACHE *
FROM `member`
WHERE loginId = 'user1';

# 게시물 테이블 생성(title, body, writerName, memberId)
CREATE TABLE `article` (
    id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    PRIMARY KEY(id),
    regDate DATETIME NOT NULL,
    title CHAR(200) NOT NULL,
    `body` TEXT NOT NULL,
    `writerName` CHAR(100) NOT NULL,
    memberId INT(10) UNSIGNED NOT NULL
);

# 1번 회원이 글 1 작성(title = '글 1 제목', `body` = '글 1 내용')
INSERT INTO article
SET regDate = NOW(),
title = '글 1 제목',
`body` = '글 1 내용',
writerName = '홍길동',
memberId = 1;

# 2번 회원이 글 2 작성(title = '글 2 제목', `body` = '글 2 내용')
INSERT INTO article
SET regDate = NOW(),
title = '글 2 제목',
`body` = '글 2 내용',
writerName = '홍길순',
memberId = 2;

# 1번 회원이 글 3 작성(title = '글 3 제목', `body` = '글 3 내용')
INSERT INTO article
SET regDate = NOW(),
title = '글 3 제목',
`body` = '글 3 내용',
writerName = '홍길동',
memberId = 1;

# 전체글 조회
SELECT *
FROM article;

# 1번회원의 이름변경 홍길동 => 홍길동2
UPDATE `member`
SET `name` = '홍길동2'
WHERE id = 1;

# 전체글 조회, 여전히 게시물 테이블에는 이전 이름이 남아 있음
SELECT *
FROM article;

# 게시물 테이블에서 writerName 삭제
ALTER TABLE article DROP COLUMN writerName;

# INNER JOIN 을 통해서 두 테이블을 조회한 결과를 합침, ON 없이
SELECT * FROM article
INNER JOIN `member`;

# INNER JOIN 을 통해서 두 테이블을 조회한 결과를 합침, 올바른 조인 조건

## 힌트 : 이걸로 조인조건을 걸 칼럼 조사
## SELECT article.id, article.memberId, member.id AS "회원테이블_번호"
## FROM article
## INNER JOIN `member`;

# 조인 완성, ON 까지 추가
SELECT A.*, M.name AS writerName
FROM article AS A
INNER JOIN `member` AS M
ON A.memberId = M.id;

저작자표시 (새창열림)

'sql' 카테고리의 다른 글

MySql 기본 예제들 (0)	2023.04.10
MySql emplyees 연습용 데이터,문제 (0)	2023.04.10
상황에 맞는 SQL을 작성하기, SUM, MAX, MIN, COUNT (0)	2023.04.08
상황에 맞는 SQL작성하기, INNER JOIN, 사원, 부서 (0)	2023.04.08
기초 sql 명령문 (0)	2023.04.08

상황에 맞는 SQL을 작성하기, SUM, MAX, MIN, COUNT

Nubbie 2023. 4. 8. 14:17

2023. 4. 8. 14:17

# a6 DB 삭제/생성/선택
DROP DATABASE IF EXISTS a6;
CREATE DATABASE a6;
USE a6;

# 부서(홍보, 기획)
# 주의 : INT(10)이 10바이트라는 강사의 설명은 잘못 되었습니다. 그냥 INT라고 쓰시면 됩니다.
CREATE TABLE dept (
    id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    regDate DATETIME NOT NULL,
    `name` CHAR(100) NOT NULL UNIQUE
);

INSERT INTO dept
SET regDate = NOW(),
`name` = '홍보';

INSERT INTO dept
SET regDate = NOW(),
`name` = '기획';

# 사원(홍길동/홍보/5000만원, 홍길순/홍보/6000만원, 임꺽정/기획/4000만원)
# 주의 : INT(10)이 10바이트라는 강사의 설명은 잘못 되었습니다. 그냥 INT라고 쓰시면 됩니다.
CREATE TABLE emp (
    id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    regDate DATETIME NOT NULL,
    `name` CHAR(100) NOT NULL,
    deptId INT UNSIGNED NOT NULL,
    salary INT UNSIGNED NOT NULL
);

INSERT INTO emp
SET regDate = NOW(),
`name` = '홍길동',
deptId = 1,
salary = 5000;

INSERT INTO emp
SET regDate = NOW(),
`name` = '홍길순',
deptId = 1,
salary = 6000;

INSERT INTO emp
SET regDate = NOW(),
`name` = '임꺽정',
deptId = 2,
salary = 4000;

# 사원 수 출력
SELECT COUNT(*)
FROM emp;

# 가장 큰 사원 번호 출력
SELECT MAX(id)
FROM emp;

# 가장 고액 연봉
SELECT MAX(salary)
FROM emp;

# 가장 저액 연봉
SELECT MIN(salary)
FROM emp;

# 회사에서 1년 고정 지출(인건비)
SELECT SUM(salary)
FROM emp;

# 부서별, 1년 고정 지출(인건비)
SELECT deptId, SUM(salary)
FROM emp
GROUP BY deptId;

# 부서별, 최고연봉
SELECT deptId, MAX(salary)
FROM emp
GROUP BY deptId;

# 부서별, 최저연봉
SELECT deptId, MIN(salary)
FROM emp
GROUP BY deptId;

# 부서별, 평균연봉
SELECT deptId, AVG(salary)
FROM emp
GROUP BY deptId;

# 부서별, 부서명, 사원리스트, 평균연봉, 최고연봉, 최소연봉, 사원수
## V1(조인 안한 버전)
SELECT E.deptId AS 부서번호,
GROUP_CONCAT(E.name) AS 사원리스트,
TRUNCATE(AVG(E.salary), 0) AS 평균연봉,
MAX(E.salary) AS 최고연봉,
MIN(E.salary) AS 최소연봉,
COUNT(*) AS 사원수
FROM emp AS E
GROUP BY E.deptId;

## V2(조인해서 부서명까지 나오는 버전)
SELECT D.name AS 부서,
GROUP_CONCAT(E.name) AS 사원리스트,
TRUNCATE(AVG(E.salary), 0) AS 평균연봉,
MAX(E.salary) AS 최고연봉,
MIN(E.salary) AS 최소연봉,
COUNT(*) AS 사원수
FROM emp AS E
INNER JOIN dept AS D
ON E.deptId = D.id
GROUP BY E.deptId;

## V3(V2에서 평균연봉이 5000이상인 부서로 추리기)
SELECT D.name AS 부서,
GROUP_CONCAT(E.name) AS 사원리스트,
TRUNCATE(AVG(E.salary), 0) AS 평균연봉,
MAX(E.salary) AS 최고연봉,
MIN(E.salary) AS 최소연봉,
COUNT(*) AS 사원수
FROM emp AS E
INNER JOIN dept AS D
ON E.deptId = D.id
GROUP BY E.deptId
HAVING `평균연봉` >= 5000;

## V4(V3에서 HAVING 없이 서브쿼리로 수행)
### HINT, UNION을 이용한 서브쿼리
# SELECT *
# FROM (
#     SELECT 1 AS id
#     UNION
#     SELECT 2
#     UNION
#     SELECT 3
# ) AS A

SELECT *
FROM (
    SELECT D.name AS `부서명`,
    GROUP_CONCAT(E.`name`) AS `사원리스트`,
    TRUNCATE(AVG(E.salary), 0) AS `평균연봉`,
    MAX(E.salary) AS `최고연봉`,
    MIN(E.salary) AS `최소연봉`,
    COUNT(*) AS `사원수`
    FROM emp AS E
    INNER JOIN dept AS D
    ON E.deptId = D.id
    WHERE 1
    GROUP BY E.deptId
) AS D
WHERE D.`평균연봉` >= 5000;

저작자표시 (새창열림)

'sql' 카테고리의 다른 글

MySql 기본 예제들 (0)	2023.04.10
MySql emplyees 연습용 데이터,문제 (0)	2023.04.10
문제 - 상황에 맞는 SQL을 작성하기, INNER JOIN, INDEX, UNIQUE INDEX, SQL_NO_CACHE, EXPLAIN, UUID (0)	2023.04.08
상황에 맞는 SQL작성하기, INNER JOIN, 사원, 부서 (0)	2023.04.08
기초 sql 명령문 (0)	2023.04.08

상황에 맞는 SQL작성하기, INNER JOIN, 사원, 부서

Nubbie 2023. 4. 8. 14:17

2023. 4. 8. 14:17

# a5 데이터베이스 삭제/생성/선택
drop database if exists a5;

create database a5;

use a5;

# 부서(dept) 테이블 생성 및 홍보부서 기획부서 추가
create table dept (
    id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    PRIMARY KEY(id),
    regDate DATETIME NOT NULL,
    `name` CHAR(100) NOT NULL UNIQUE
);

insert into dept
SET regDate = NOW(),
`name` = '홍보';

INSERT INTO dept
SET regDate = NOW(),
`name` = '기획';

select *
from dept;

# 사원(emp) 테이블 생성 및 홍길동사원(홍보부서), 홍길순사원(홍보부서), 임꺽정사원(기획부서) 추가
create table emp (
    id int(10) unsigned not null auto_increment,
    primary key(id),
    regDate datetime not null,
    `name` char(100) not null,
    deptName char(100) not null
);

insert into emp
set regDate = NOW(),
`name` = '홍길동',
deptName = '홍보';

INSERT INTO emp
SET regDate = NOW(),
`name` = '홍길순',
deptName = '홍보';

INSERT INTO emp
SET regDate = NOW(),
`name` = '임꺽정',
deptName = '기획';

# 홍보를 마케팅으로 변경
select *
from dept;

update dept
set `name` = '마케팅'
where `name` = '홍보';

select *
from emp;

update emp
set deptName = '마케팅'
where deptName = '홍보';

SELECT *
FROM emp;

# 마케팅을 홍보로 변경

UPDATE dept
SET `name` = '홍보'
WHERE `name` = '마케팅';

SELECT *
FROM dept;

UPDATE emp
SET deptName = '홍보'
WHERE deptName = '마케팅';

SELECT *
FROM emp;

# 홍보를 마케팅으로 변경
# 구조를 변경하기로 결정(사원 테이블에서, 이제는 부서를 이름이 아닌 번호로 기억)
ALTER TABLE emp add COLUMN deptId int(10) unsigned not null;

select *
from emp;

update emp
set deptId = 1
where deptName = '홍보';

UPDATE emp
SET deptId = 2
WHERE deptName = '기획';

SELECT *
FROM emp;

alter table emp drop column deptName;

select *
from dept;

UPDATE dept
SET `name` = '마케팅'
WHERE `name` = '홍보';

select *
from dept;

select *
from emp;

# 사장님께 드릴 인명록을 생성
select *
from emp;

# 사장님께서 부서번호가 아니라 부서명을 알고 싶어하신다.
# 그래서 dept 테이블 조회법을 알려드리고 혼이 났다.
SELECT *
FROM dept
where id = 1;

# 사장님께 드릴 인명록을 생성(v2, 부서명 포함, ON 없이)
# 이상한 데이터가 생성되어서 혼남
SELECT emp.*, dept.name AS `부서명`
FROM emp
inner join dept;

# 사장님께 드릴 인명록을 생성(v3, 부서명 포함, 올바른 조인 룰(ON) 적용)
# 보고용으로 좀 더 편하게 보여지도록 고쳐야 한다고 지적받음
SELECT emp.*, dept.id, dept.name AS `부서명`
FROM emp
inner join dept
ON emp.deptId = dept.id;

# 사장님께 드릴 인명록을 생성(v4, 사장님께서 보시기에 편한 칼럼명(AS))
SELECT emp.id AS `사원번호`,
emp.name AS `사원명`,
DATE(emp.regDate) AS `입사일`,
dept.name AS `부서명`
FROM emp
INNER JOIN dept
ON emp.deptId = dept.id
order by `부서명`, `사원번호`;

저작자표시 (새창열림)

'sql' 카테고리의 다른 글

MySql 기본 예제들 (0)	2023.04.10
MySql emplyees 연습용 데이터,문제 (0)	2023.04.10
문제 - 상황에 맞는 SQL을 작성하기, INNER JOIN, INDEX, UNIQUE INDEX, SQL_NO_CACHE, EXPLAIN, UUID (0)	2023.04.08
상황에 맞는 SQL을 작성하기, SUM, MAX, MIN, COUNT (0)	2023.04.08
기초 sql 명령문 (0)	2023.04.08

기초 sql 명령문

Nubbie 2023. 4. 8. 13:59

2023. 4. 8. 13:59

## 정렬
  - 특정 컬럼을 지정해 그 컬럼의 값으로 행을 정렬.
    - order by 컬럼 asc/desc
        - asc : 오름차순
        - desc : 내림차순

        - Products 테이블에서 가격(Price)으로 오름차순, 제품번호(ProductId)로 내림차순 정렬하기
        ```
        select *
        from Products
        order by Price ASC, ProductID DESC;
        ```

## 별명
  - 특정 컬럼을 새로운 별명을 붙여 사용. 주로 연산이나 함수를 통해 새로 나온 컬럼에 붙인다.
    - 컬럼 as 별명

    - Products 테이블에서 ProductId, SupplierId, CategoryId를 더한 값으로 새로운 컬럼을 만들고 컬럼 이름을 NewId로 정하기
    ```
    SELECT ProductId, SupplierId, CategoryId, ProductId + SupplierId + CategoryId as NewId FROM Products;
    ```

## 사칙연산
  - +, *, /, -
    - 숫자 + 숫자 == 산술 연산
    - 문자 + 숫자 == 문자는 0 취급
    - 컬럼1 + 컬럼2 == 컬럼의 모든 행이 연산.

    - 사칙 연산 예제
    ```
    SELECT 10 + 20;
    SELECT 10 * 20;
    SELECT 10 / 20;
    SELECT 10 - 20;
    ```

## 비교연산
  - >, <, =, !=
    - 결과는 1(참, TRUE), 0(거짓, FALSE)
    - 비교 연산 예제
    ```
    SELECT 10 = 20;
    SELECT 10 != 20;
    SELECT 10 > 20;
    SELECT 10 < 20;
    SELECT 10 >= 20;
    SELECT 10 <= 20;
    ```

## 논리연산
  - and, or
    - and : 조건이 여러개 일 때 모두 만족해야 맞다고 보는 연산
    - or : 조건이 여러개일 때 하나라도 만족하면 맞다고 보는 연산

    - Customers 테이블에서 CustomerId값이 10 이상이고 CustomerId값이 20 이하인 행만 조회
    ```
    SELECT *
    FROM Customers
    where CustomerID >= 10
    and CustomerId <= 20;
    ```

    - Customers 테이블에서 CustomerId값이 20 이하인것 + CustomerId값이 80이상인 행을 모두 조회
    ```
    SELECT *
    FROM Customers
    where CustomerID <= 20
    or CustomerId >= 80;
    ```

    - Customers 테이블에서 CustomerId갑이 20 이하인 것  + CustomerId값이 80이상인 모든 컬럼 중에서 city가 'London'인 행을 조회
    ```
    SELECT *
    FROM Customers
    where (CustomerID <= 20
    or CustomerId >= 80)
    and city = 'London';
    ```

## 사잇값
  - 컬럼 between A and B
    - 컬럼값이 A와 B 사이인 것만 조회
    - Customers 테이블에서 CustomerId값이 10과 20 사이인 행을 조회
    ```
    SELECT *
    FROM Customers
    where CustomerID BETWEEN 10 AND 20;
    ```

## 포함
- 컬럼 in (A, B, C ...)
  - 컬럼값이 A,B,C 인것을 모두 조회
  - Customers 테이블에서 city 값에 'London', 'Berlin', 'Madrid' 중 하나라도 포함하는 행을 모두 조회
    ```
    SELECT *
    FROM Customers
    WHERE City in ('London', 'Berlin', 'Madrid');
    ```

## LIKE, NOT LIKE
  - 특정 문자로 시작여부, 끝나는지 여부, 포함하는지 여부
  - 찾고자하는 문자열을 %기호와 조합해서 사용. 여기서 %는 나머지 문자를 의미
    - 'A%' : A로 시작하는 문자열
    - '%A' : A로 끝나는 문자열
    - '%A%' : A를 포함하는 문자열

    - Employees 테이블에서 LastName이 D로 시작하는 행만 조회
    ```
    SELECT * FROM Employees
    where LastName like 'D%';
    ```

    - Employees 테이블에서 LastName이 D로 시작하지 않는 행만 조회
    ```
    SELECT * FROM Employees
    where LastName not like 'D%';
    ```

## CEIL, ROUND, FLOOR
  - 소수점을 올림, 반올림, 버림
    - CEIL() : 올림
    - ROUND() : 반올림
    - FLOOR() : 버림

  - 1.2를 반올림
  ```
  SELECT ROUND(1.2);
  ```
  - Products 테이블의 Price 컬럼값들을 반올림한다.
  ```
  SELECT ROUND(Price) FROM Products;
  ```

## AVG, MAX, MIN, SUM, COUNT
  - AVG(컬럼) : 컬럼의 모든 행의 평균값 구하기
  - MAX(컬럼) : 컬럼의 모든 행중 최대값 구하기
  - MIN(컬럼) : 컬럼의 모든 행중 최소값 구하기
  - SUM(컬럼) : 컬럼의 모든 행의 평균값 구하기
  - COUNT(컬럼) : 컬럼의 모든 행의 개수를 구하기 (NULL값은 카운팅에서 제외됨)

    - Categories 테이블에서 CategoryName 컬럼의 모든 행의 개수를 구하기.
    ```
    SELECT count(CategoryName) FROM Categories;
    ```
## CONCAT(문자1, 문자2, 문자3 ...)
  - 문자들을 이어 붙여줌.

  - 문자열 'taejin'과 'cha'를 이어 붙이기
  ```
   select concat('taejin', ' ','cha') as name;

  ```

  - Employees 테이블의 LastName과 FirstName을 이어 붙여 FullName이라는 새로운 컬럼으로 만들기
  ```
  SELECT EmployeeId, BirthDate, Photo, Notes, concat(LastName, ' ', FirstName) as FullName FROM Employees;
  ```

## SUBSTR(대상문자, 시작위치, 자르는길이)
  - 문자열을 잘라줌

  - 문자열 'taejincha'를 1번째 문자부터 6개 짜르기
  ```
  select substr('taejincha', 1, 6) as fisrtName;
  ```

  - Employees 테이블에서 BirthDate 컬럼의 모든 값을 1번째 문자부터 4개 짜르기(년도만 나오게 하기)
  ```
  SELECT substr(BirthDate, 1, 4) as `year` FROM Employees
  ```

## REPLACE(대상문자(컬럼), 문자열1, 문자열2)
  - 대상 문자(컬럼)에 문자열1인 부분을 찾아 문자열2로 치환해주기.

    - Customers 테이블의 city 컬럼의 값에서 B문자를 찾아 b로 바꾸기
    ```
    SELECT replace(city, 'B', 'b') FROM Customers;
    ```

## CURDATE(), NOW()
  - 오늘날짜 구하기
    - CURDATE : 연월일
    - NOW : 연월일 시분초

## ADDDATE(기준날짜, interval n day), SUBDATE()
  - 기준날짜로 부터 n만큼의 거리(간격)
## DATE_DIFF(날짜1, 날짜2)
  - 날짜1과 날짜2의 차이를 구함
  ```
  select datediff('2022-03-21', '2021-03-21');
  ```

## DISTINCT
  - 조회 결과에서 중복되는 행을 제거한다.

  - Suppliers에서 Country를 중복제거해서 조회하기
  ```
    SELECT DISTINCT Country
    FROM Suppliers
  ```

### 그룹핑
  - 그룹핑은 특정 컬럼을 기준으로 그룹을 나누어 모든 행을 하나의 그룹으로 편입시켜 그룹별로 집계를 할 때 사용.

  - 예를 들어 ~별 ~결과를 봐야한다고 하면 그룹핑해야 한다고 생각하면 된다.

  - ex )
    - 국가별 ~~ 석유 사용 현황 -> 국가로 그룹핑
    - 연령별 ~~ 투표참여율 -> 연령으로 그룹핑
    - 지역별 ~~ 초등학교 개수 -> 지역으로 그룹핑
    - 도시별 ~~ 평균 주택 가격 -> 도시로 그룹핑

  - Customer 테이블에서 국가별 회원 수 보기
    ```
    SELECT Country, COUNT(Country)
    FROM Customers
    GROUP BY Country

    ```

  - Customer 테이블에서 도시별 회원 수 보기
    ```
    SELECT Country, City, COUNT(City)
    FROM Customers
    GROUP BY Country, City

    ```
### HAVING
  - 일반적으로 select문은 from > where > group by > having > select > order by 순으로 실행된다.
  - where가 group by 보다 먼저 실행되기 때문에 group by 이후에 where로 조건을 따질 수 없다. 그래서 having을 사용한다.
  - Customers 테이블에서 회원수가 5이상인 국가만 보기
    ```
    SELECT *, country, count(*) cnt FROM Customers
    group by country
    having cnt > 5
    ```

저작자표시 (새창열림)

'sql' 카테고리의 다른 글

MySql 기본 예제들 (0)	2023.04.10
MySql emplyees 연습용 데이터,문제 (0)	2023.04.10
문제 - 상황에 맞는 SQL을 작성하기, INNER JOIN, INDEX, UNIQUE INDEX, SQL_NO_CACHE, EXPLAIN, UUID (0)	2023.04.08
상황에 맞는 SQL을 작성하기, SUM, MAX, MIN, COUNT (0)	2023.04.08
상황에 맞는 SQL작성하기, INNER JOIN, 사원, 부서 (0)	2023.04.08

PREV 이전 1 NEXT 다음

생각 기록장

sql

MySql 기본 예제들

'sql' 카테고리의 다른 글

MySql emplyees 연습용 데이터,문제

연습용 데이터 베이스 생성

셀렉트문들과 각종 연습문제

'sql' 카테고리의 다른 글

문제 - 상황에 맞는 SQL을 작성하기, INNER JOIN, INDEX, UNIQUE INDEX, SQL_NO_CACHE, EXPLAIN, UUID

'sql' 카테고리의 다른 글

상황에 맞는 SQL을 작성하기, SUM, MAX, MIN, COUNT

'sql' 카테고리의 다른 글

상황에 맞는 SQL작성하기, INNER JOIN, 사원, 부서

'sql' 카테고리의 다른 글

기초 sql 명령문

'sql' 카테고리의 다른 글

+ Recent posts

티스토리툴바