Skip to main content
mysql

9주차-1 테이블 생성, 수정, 삭제 , cte 연습, Join 연습1

By 10월 31st, 2024No Comments

테스트 테이블 5개 연습하기

use sqldb;

create table testTBL1 
(
    id int,
    userName char(3),
    age int
);


create table testTBL2 
(
    id int AUTO_INCREMENT PRIMARY KEY,
    userName char(3),
    age int
);

create table testTBL3 
(
    id int AUTO_INCREMENT PRIMARY KEY,
    userName char(3),
    age int
);

ALTER TABLE testTBL3 AUTO_INCREMENT = 1000;
set @@auto_increment_increment=3;


insert into testTBL3 VALUES (null, '지민', 25);
insert into testTBL3 VALUES (null, '수영', 22);
insert into testTBL3 VALUES (null, '지민', 25),(null, '민경', 25),(null, '은화', 27);

insert into testTBL1 VALUE ( 1, '홍길동', 25);
insert into testTBL1(id, userName) VALUE ( 2, '김성실');


SELECT * from testTBL3;

CREATE table testTBL4 (
    id int,
    Fname varchar(50),
    Lname varchar(50)
);

insert into testTBL4 SELECT emp_no, first_name, last_name from employees.employees;

SELECT * from testTBL4;
SELECT * from employees.employees;


CREATE TABLE testTBL5 (SELECT emp_no, first_name, last_name from employees.employees);

select * from testTBL5;

 

내용을 수정할 때 사용, 필드가 모두 한꺼번에 수정될 수 있으니 매우 조심

where 조건절을 반드시 함께 사용한다.

 

UPDATE testTBL4 set Lname = '없음' where Fname = 'Kyoichi';

 

전체 가격표의 가격을 2배로 올릴때 where 없이 필드에 한꺼번에 적용 한다.

UPDATE buytbl set price = price / 2;

SELECT * from buytbl;

 

— 삭제할때 이름을 검색하고 limit를 걸어서 숫자만큼만 삭제할 수 있다.

DELETE from testTBL4 WHERE Fname = 'Aamer';
DELETE from testTBL4 WHERE Fname = 'Aamer' LIMIT 5;

 

삭제의 3가지 표현

CREATE TABLE bigTBL1 (SELECT * FROM employees.employees);
CREATE TABLE bigTBL2 (SELECT * FROM employees.employees);
CREATE TABLE bigTBL3 (SELECT * FROM employees.employees);

DELETE from bigTBL1;
SELECT * from bigTBL1;

DROP table bigTBL2;
SELECT * from bigTBL2;

TRUNCATE table bigTBL3;
SELECT * from bigTBL3;

delete 는 한줄씩 실행

drop와 truncate는 한방에 실행

시간의 차이가 난다.

상황실습

 

use employees;

SELECT * from employees;

-- abdDB를 가동시킨다.
use abcdb;

-- testtbl4 테이블 원본과 비교해서 타입을 비슷하게 맞춥니다. 
create	TABLE testtbl4 (
    emp_no int,
    first_name VARCHAR(30),
    last_name VARCHAR(30)
);

-- emp_no, first_name, last_name 테이블값을 전부다 넣어주세요. testtbl4에 넣어주세요 (복사해주세요)
insert into testtbl4 select emp_no, first_name, last_name from employees.employees;

 

join 명령어

join명령을 사용하는 이유 : 데이터의 최적의 사이즈와 불필요한 데이터 중복을 막기 위함, 데이터의 무결성을 위해서 여러 개의 테이블을 분리하여 저장한다.

분리된 데이터를 조합해서 사용할 때 join 명령어를 사용하며 join 명령어는 너무 많이 사용하면 시스템에 부하가 올 수 있다.

join 명령어는 개발자보다 데이터를 분석하는 입장에서 많이 사용하는 명령어이다.

join 은 inner join이라고 말하며 두 테이블에 해당 필드값이 매칭되는 레코드만 가져온다.

 

연습

  • usertbl 과 buytbl 테이블간의 2개 데이터를 조합해서 하나의 합쳐진 테이블을 생성해서 화면에 보여주는 방법
  • usertbl에서 ID는 유니크한 값 (primary key) 이며 아이디가 KBS인 사람의 아이디를 다른 사람이 사용할 수 없다.
  • 1대다 관계이다. 즉,  usertbl에서 다른 추가된 테이블을 연결해서 사용한다.
  • 회원테이블의 아이디는 Primary Key 로 지정하고  구매테이블의 아이디는 Primary Key와 연결된 Foreign Key로 지정한다.

 

실습명령어

SELECT <열 목록>

FROM <첫 번째 테이블> -> buytbl

INNER JOIN <두 번째 테이블> usertbl

ON <조인될 조건>  buytbl.userID = usertbl.userID

[WHERE 검색조건] buytbl.userID 중 ‘kbs’ 인 사람만 출력하기

테이블 연결 그림

userID값의 정확한 위치를 선언하면 에러가 발생하지 않는다.

1대 다의 관계를 반드시 익혀야 한다.

PK , FK 개념을 반드시 익힌다.

foreign key의 역활이 무엇인지 확인

회원데이터가 없는데 회원구매 정보 데이터를 넣으면 fk가 막아주는 역활을 한다. 데이터 무결성을 위해서 존재하고 테이블간이 연결을 보장

회원정보 삭제시 에러나는 경우

반대로 회원 삭제 명령어를 할때도 바로 삭제는 안되고 buytbl에 데이터를 먼저 삭제하고 usertbl에 회원정보를 삭제할 수 있다.

delete from buytbl where userid = 'stj';

delete from usertbl where userID = 'stj';

 

USE sqldb;
select * from buytbl;
select * from usertbl;
select * from buytbl inner join usertbl on buytbl.userID = usertbl.userID where buytbl.userID ='KBS';


USE sqldb; 
select * from buytbl; select * from usertbl; 
select * from buytbl inner join usertbl on buytbl.userID = usertbl.userID where order by ='num';
SELECT 
    userID,
    name,
    prodName,
    addr,
    CONCAT(mobile1, mobile2) AS '연락처'
FROM
    buytbl
        INNER JOIN usertbl
            ON buytbl.userID = usertbl.userID
ORDER BY num;

오류가 발생한다. 왜 발생하는지 이유 알아보기

userID값이 어느 테이블의 userID인지 확인할 수 없다.

 

SELECT 
    buytbl.userID,
    name,
    prodName,
    addr,
    CONCAT(mobile1, mobile2) AS '연락처'
FROM
    buytbl
        INNER JOIN usertbl
            ON buytbl.userID = usertbl.userID
ORDER BY num;

 

SELECT 
    buytbl.userID,
    usertbl.name,
    buytbl.prodName,
    usertbl.addr,
    CONCAT(usertbl.mobile1, usertbl.mobile2) AS '연락처'
FROM
    buytbl
        INNER JOIN usertbl
            ON buytbl.userID = usertbl.userID
ORDER BY buytbl.num;

테이블명을 모두 적어주면 명확하게 지정할 수 있지만 글자가 길어지는 불편함이 있다.

 

SELECT 
    B.userID,
    U.name,
    B.prodName,
    U.addr,
    CONCAT(U.mobile1, U.mobile2) AS '연락처'
FROM
    buytbl B
        INNER JOIN usertbl U
            ON B.userID = U.userID
ORDER BY B.num;

알라아스 (별칭)을 사용자 임의로 입력해서 작업을 진행하면 단순화 시킬 수 있다.

 

SELECT 
    B.userID,
    U.name,
    B.prodName,
    U.addr,
    CONCAT(U.mobile1, U.mobile2) AS '연락처'
FROM
    buytbl B
        INNER JOIN usertbl U
            ON B.userID = U.userID
            where B.userID ='BBK'
ORDER BY B.num;

특정 아이디의 제품 구매목록과 연락처, 주소

 

SELECT 
    U.userID,
    U.name,
    B.prodName,
    U.addr,
    CONCAT(U.mobile1, U.mobile2) AS '연락처'
FROM
    usertbl U
        INNER JOIN buytbl b
            ON U.userID = B.userID
ORDER BY U.userid;

기준이 되는 테이블1과 테이블2를 바꿔서도 무방하다.  기준 이름을 바꾸면 조건식도 변경해서 Join을 진행하면 된다.

 

SELECT DISTINCT
    U.userID, U.name, U.addr
    FROM usertbl U
        INNER JOIN buytbl B
            ON U.userID = B.userID
ORDER BY U.userid;

구매를 한번이라도 경험이 있는 회원의 목록을 뽑고 중복은 한명만 출력한다.

distinct 는 중복되는 결과중 대표만 출력한다. 아이디,이름,주소만 서로 조인해서 한번이라도 구매한 사람을 뽑아서 중복제거하고 대표만 출력한다.

 

SELECT DISTINCT
    U.userID, U.name, U.addr
    FROM usertbl U
        WHERE EXISTS ( SELECT * from buytbl B where U.userID = B.userID);

 

 

 

 

 

 

 

 

 

 

 

Recommended For You

mysql

기말고사 sql 파일

admin
admin2024-12-11
mysql

14주차

admin
admin2024-11-28
mysql

emmet 사이트 안내

admin
admin2024-11-14

Leave a Reply