📅2024. 01. 10 23일차

---

📖mysql 오늘의 개념

SQL에서 COUNT() 결과에 NULL이 포함될까?

• 포함되는 경우 : COUNT(*)
• 포함되지 않는 경우: COUNT(ColumnName)
  COUNT(컬럼명)을 사용하면 NULL 값은 제외하고 COUNT 한다.
  COUNT(*)를 사용하면 NULL도 포함하여 전부 COUNT 한다.

salf join?

• 일반적으로 JOIN은 연관된 두 개의 테이블을 연결하기 위해서 사용되지만, 자신의 테이블을 참조하는 셀프조인도 가능하다.

subquery?

• 서브쿼리(subquery)란 다른 쿼리 내부에 포함되어 있는 SELETE 문을 의미한다.
• 서브쿼리를 포함하고 있는 쿼리를 외부쿼리(outer query)라고 부르며,
  서브쿼리는 내부쿼리(inner query)라고도 부른다.
• 서브쿼리는 다음과 같이 괄호() 로 감싸져서 표현 된다.

📋 sql 문제 풀이

📋 문제 : 월급여가 1200 에서 3500 사이의 사원의 사번,이름,월급여를 조회하시오.

🔑내 풀이

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

🔑슨생님 풀이

SELECT empNo, ename, sal
FROM emp
WHERE sal >= 1200 AND 3500 >= sal

• 똑같다. 둘 다 가능!!

📋 문제 : 부서번호가 30인 아닌 사원의 사번,이름,부서번호를 조회하시오.

🔑내 풀이

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

🔑슨생님 풀이

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

• 똑같다. 둘 다 가능!! 다른 방법도 기억해놓자.

📋 문제 : 부서별 전체 사원수와 커미션을 받는 사원들의 수를 구하는 쿼리

🔑내 풀이

SELECT deptno, COUNT(*) AS '커미션을 받는 사원들의 수'
FROM emp 
GROUP BY deptNo

부서명으로 나오게 하고 싶어~ ver.

SELECT d.dname, COUNT(*), COUNT(e.comm) AS '부서별 전체 사원수'
FROM emp AS e 
INNER JOIN dept AS d
ON e.deptNo = d.deptNo
GROUP BY d.deptNo;

• emp , dept을 deptNo로 join 시킨 후 deptNo로 grouping 했다. 이게 더 보기 쉬워보여서 진행시킴.

🔑슨생님 풀이

SELECT deptno, COUNT(*) AS '커미션을 받는 사원들의 수'
FROM emp 
GROUP BY deptNo

SELECT deptno, COUNT(IF(comm = 0, NULL, comm)) AS '커미션을 받는 사원들의 수'
FROM emp 
GROUP BY deptNo

• 만약 0을 포함 안 시킨다면? count에 조건을 걸어버리자.

📋 문제 : 월급여가 1000 이상인 사원만을 대상으로 부서별로 월급여 평균을 구하라. 단, 평균값이 2000 이상인 레코드만 구하라.

🔑내 풀이

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

• 평균 값 구하기 전에 조건 먼저 확인 where로 처리하고 그 후 having 조건 걸기

📋 문제 : 사원명과 부서명을 조회하시오. (inner join)

🔑내 풀이

• 일단 emp와 dept을 deptNo 기준으로 inner join 시켜야 되지 않나?

SELECT e.ename, d.dname  
FROM emp AS e 
INNER JOIN dept AS d
ON e.deptNo = d.deptNo

🔑슨생님 풀이

SELECT ename, dname  
FROM emp  e 
INNER JOIN dept  d
ON e.deptNo = d.deptNo

• ename 과 dname은 하나 씩 있으므로 그냥 써도된다.
• AS는 생략 가능!!

📋 문제 : 이름,월급여,월급여등급을 조회하시오.

🔑해결 풀이

SELECT e.ename, e.sal, s.grade
FROM emp e
INNER JOIN salgrade s
ON e.sal BETWEEN s.losal AND s.hisal

📋 문제 : 이름,직속상사이름을 조회하시오. (hint : self join)

🔑해결 풀이

SELECT em1.ename, em2.ename
FROM emp AS em1
LEFT JOIN emp AS em2
ON em1.mgr = em2.empno

• 그냥 self join 하면 값이 null인 king은 제외된다. 따라서 나는 LEFT join을 해서 null도 포함시켰다.

📋 문제 : 10번 부서에서 근무하는 사원의 이름과 10번 부서의 부서명을 조회하시오. (hint : sub query)

🔑풀이

SELECT e.ename, (SELECT dname FROM dept WHERE deptno = e.deptno) AS dname
FROM emp e
WHERE e.ename = 'JONES';

• 서브 쿼리를 실행시켜 먼저 뽑아내게 한다.
• 근데 조인을 쓰는게 이득이다.
• why? 성능 저하의 원인이 된다!!!

🔑 해결풀이 (JOIN)

select e.ename , d.dname
from emp e
INNER JOIN dept d
ON e.deptno = d.deptno
where e.deptno = 10;

🎬JDBC_AM 시작

🔓 게시물 회원가입 구현 ==> JDBC 활용

💡띵킹

==> JDBC활용? JDBC 쿼리문으로 table , column을 구성한다?

• member
  • id
  • regDate
  • updateDate
  • loginId
  • loginPw
  • name

↓쿼리 이렇게 짜면 될듯??

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

↓ 이렇게 하면 일단 insert는 됨

if (cmd.equals("member join")) {
			System.out.println("==회원가입==");
			System.out.print("loginId : ");
			String loginId = sc.nextLine();
			System.out.print("loginPw : ");
			String loginPw = sc.nextLine();
			System.out.print("이름 : ");
			String name = sc.nextLine();
			
			SecSql sql = new SecSql();

			sql.append("INSERT INTO `member`");
			sql.append("SET regDate = NOW(),");
			sql.append("updateDate = NOW(),");
			sql.append("loginId = ?,", loginId);
			sql.append("loginPw = ?,", loginPw);
			sql.append("`name`= ?;", name);

			int id = DBUtil.insert(conn, sql);

			System.out.println(id + "번 회원이 가입되었습니다.");

문제점 : 아무것도 입력 안해도 가입이 됨, 아이디 중복확인 해야됨.

↓ 문제해결

while (true) {
				System.out.print("로그인 아이디 : ");
				loginId = sc.nextLine().trim();

				if (loginId.length() == 0 || loginId.contains(" ")) {
					System.out.println("아이디 똑바로 입력해");
					continue;
				}

				SecSql sql = new SecSql();
				sql.append("SELECT COUNT(*) > 0");
				sql.append("FROM `member`");
				sql.append("WHERE loginId = ?;", loginId);

				boolean isLoginIdDup = DBUtil.selectRowBooleanValue(conn, sql);

				if (isLoginIdDup) {
					System.out.println(loginId + "는(은) 이미 사용중");
					continue;
				}

				break;
			}
			while (true) {
				System.out.print("비밀번호 : ");
				loginPw = sc.nextLine().trim();

				if (loginPw.length() == 0 || loginPw.contains(" ")) {
					System.out.println("비밀번호 똑바로 입력해");
					continue;
				}

				boolean loginPwCheck = true;

				while (true) {
					System.out.print("비밀번호 확인: ");
					loginPwConfirm = sc.nextLine().trim();

					if (loginPwConfirm.length() == 0 || loginPwConfirm.contains(" ")) {
						System.out.println("확인 똑바로 입력해");
						continue;
					}

					if (loginPw.equals(loginPwConfirm) == false) {
						System.out.println("일치하지 않아");
						loginPwCheck = false;
					}
					break;
				}

				if (loginPwCheck) {
					break;
				}
			}
			while (true) {
				System.out.print("이름  : ");
				name = sc.nextLine().trim();

				if (name.length() == 0 || name.contains(" ")) {
					System.out.println("이름 똑바로 입력해");
					continue;
				}
				break;
			}

• 입력 값의 길이가 == 0 일 때 continue 하게 만들면 된다.
• DB에 저장되있는 loginId를 isLoginIdDup 변수에 담아 만약 isLoginIdDup라면 이미 사용중이라고 continue 시켜버리자.

  🔔 TODO

  • 로직 간단하게 개선해보기
  • scott 문제 혼자 해결 가능하도록
  • 복습