SQL 정리

USE member;
-- CRUD(DML, Data Manipulation Language) 
-- Create: INSERT INTO~
-- Read: SELECT~
-- Update: UPDATE~
-- Delete: DELETE~
INSERT INTO member VALUES(16,'박지성');
SELECT * FROM member;
INSERT INTO member(num,name) VALUES(17,'System');
UPDATE member SET email='oracle@matrix.com', name='Oracle' WHERE num=17;
DELETE FROM member WHERE num=16 OR name='박지성';
DELETE FROM member WHERE num=11;


-- member 탸이블 생성 (num, name, phone, email)
-- 테이블 구조 확인 : DESC member;
-- 5행 추가
-- 5행 확인

SELECT * FROM member;
-- 테이블 구조 확인
DESC member;
-- 5행 입력
insert into member values(11,'Smith','000-0000-00000','smith@matrix.com');
insert into member values(12,'Neo','000-0000-00000','smith@matrix.com');
insert into member values(13,'Trinity','000-0000-00000','smith@matrix.com');
insert into member values(14,'yona','000-0000-00000','yona@cp.com');
insert into member values(15,'Lucas','000-0000-00000','lucas@cp.com');

SELECT * FROM member;
UPDATE member SET email='neo@matrix.com' WHERE num=12;
UPDATE member SET email='trinity@matrix.com' WHERE num=13;
DELETE FROM member WHERE num=11 OR name='박지성';
SELECT * FROM member;
DELETE FROM member WHERE num=11;
insert into member values(15,'yona','000-0000-00000','yona@cp.com');
insert into member values(14,'Lucas','000-0000-00000','lucas@cp.com');
insert into member values(11, 'Oracle','000-0000-0000','oracle@matrix.com');
SELECT * FROM member;
-- 테이블 나가기
DROP TABLE emp;

-- 테이블 만들기
CREATE TABLE emp
(
	empno INT NOT NULL,
	ename VARCHAR(20) NOT NULL,
    deptno INT,
    salary INT,
    hiredate DATE,
    PRIMARY KEY(empno)
);
DESC emp;
SELECT * FROM emp;
-- 내용입력
INSERT INTO emp (empno, ename, deptno, salary, hiredate)
VALUES
(11, 'neo', 7, 3600, '2000-01-02'),
(12, 'trinity', 7, 3000, '2000-06-24');

-- data 지우기
-- TRUNCATE emp; 구조는 남기고 데이터만 지움
DROP TABLE emp;
DESC emp;

-- 체이블 명 : bbs(num, title author, wdate, hitcount, content)
CREATE TABLE bbs (
	num INT AUTO_INCREMENT,
    title VARCHAR(50) NOT NULL,
    author VARCHAR(20) NOT NULL,
    wdate DATE,
    hitcount  INT,
    content VARCHAR(1000),
    PRIMARY KEY(num)
);
DESC bbs;
DROP TABLE bbs;
INSERT INTO bbs 
(title, author, wdate, hitcount, content) -- colums
VALUES
('게시판 테스트', 'admin', '2000-12-30',0,'게시판을 많이 활용해주세요');

SELECT * FROM bbs;

-- DAO 클래스에 insert(bbsVO vvs) 메소드 선언
-- BBSVO 클래스 생성

-- bbs_list.jsp
-- num, author, title, wdate, hitcount
SELECT num, author, title, wdate, hitcount
FROM bbs;
UPDATE bbs SET title='PSMT Test' WHERE num=4;
DESC bbs;
DROP TABLE member;
CREATE TABLE member (
	num INT AUTO_INCREMENT,
    name VARCHAR(24) NOT NULL,
    phone VARCHAR(13),
    email VARCHAR(50) NOT NULL,
    PRIMARY KEY(num)
);
SELECT* FROM member;
DESC member;
SELECT num, name FROM member WHERE num=1;
SELECT num, name FROM member WHERE num=1 AND name='lucas';
INSERT INTO member 
(name, phone, email)
VALUES
('lucas', '213-909-1687','lucas@cp.com');
SELECT num FROM member WHERE name='yina';
SELECT * FROM member WHERE name='lucas';
-- 맴버 테이블에서 유저 테이블 생성
create TABLE user as SELECT * FROM member;
-- 유저 프라이머리키 설정
alter table user add primary key(num);
-- 자동 증가 필드 
alter table user modify column num int auto_increment;
DESC user;
-- ------------------

DROP TABLE library_user;
CREATE TABLE library_user (
	grade VARCHAR(15),
	num INT AUTO_INCREMENT,
    uid VARCHAR(24) NOT NULL,
    pwd VARCHAR(24) NOT NULL,
    name VARCHAR(24) NOT NULL,
    birth VARCHAR(24) NOT NULL,
    gender VARCHAR(1),
    phone VARCHAR(13),
    email VARCHAR(50),
    address VARCHAR(50),
    PRIMARY KEY(num)
);

SELECT * FROM mydb.library_user;

INSERT INTO library_user
(grade, uid, pwd, name, birth, gender, phone,email, address)
VALUES
('master','lucas','52','김성민', '19890227','m', '010-9686-1687','klucas5227@gmail.com','서울특별시 종로구 자하문로33다길 20');

SELECT * FROM mydb.library_user;

CREATE TABLE `notice` (
  `no` int NOT NULL AUTO_INCREMENT,
  `notice_title` varchar(32) NOT NULL,
  `notice_coments` varchar(4000) NOT NULL,
  `mod_date` timestamp NULL DEFAULT NULL,
  `use_yn` varchar(2) NOT NULL DEFAULT 'Y',
  PRIMARY KEY (`notice_id`)
);
DROP TABLE notice;
SELECT * FROM mydb. notice; 

CREATE TABLE library_bbs (
  no int AUTO_INCREMENT NOT NULL ,
  title varchar(32) NOT NULL,
  content varchar(6000),
  coments varchar(4000),
  date  varchar(10) NOT NULL,
  writer varchar(30) NOT NULL,
  cnt int,
  page int,
  PRIMARY KEY (no)
);

INSERT INTO library_bbs
(title,content,coments,date,writer,cnt,page)
VALUES
('제목 안냥','내용입니다','답글',22222222,'김김',333,1);

CREATE TABLE library_notice (
  notice_id int NOT NULL AUTO_INCREMENT,
  notice_title varchar(32) NOT NULL,
  notice_coments varchar(4000) NOT NULL,
  mod_date timestamp NULL DEFAULT NULL,
  use_yn varchar(2) NOT NULL DEFAULT 'Y',
  PRIMARY KEY (notice_id)
);

CREATE TABLE tb_board (
    idx INT NOT NULL AUTO_INCREMENT COMMENT '번호 (PK)',
    title VARCHAR(100) NOT NULL COMMENT '제목',
    content VARCHAR(3000) NOT NULL COMMENT '내용',
    writer VARCHAR(20) NOT NULL COMMENT '작성자',
    view_cnt INT NOT NULL DEFAULT 0 COMMENT '조회 수',
    notice_yn ENUM('Y', 'N') NOT NULL DEFAULT 'N' COMMENT '공지글 여부',
    secret_yn ENUM('Y', 'N') NOT NULL DEFAULT 'N' COMMENT '비밀글 여부',
    delete_yn ENUM('Y', 'N') NOT NULL DEFAULT 'N' COMMENT '삭제 여부',
    insert_time DATETIME NOT NULL DEFAULT NOW() COMMENT '등록일',
    update_time DATETIME NULL COMMENT '수정일',
    delete_time DATETIME NULL COMMENT '삭제일',
    PRIMARY KEY (idx)
)  COMMENT '게시판';

CREATE TABLE library_board (
    id INT NOT NULL AUTO_INCREMENT,
    title VARCHAR(100) NOT NULL,
    content VARCHAR(3000) NOT NULL,
    writer VARCHAR(20) NOT NULL,
    view_cnt INT NOT NULL DEFAULT 0,
    section VARCHAR(10) NOT NULL,
    secret_yn ENUM('Y', 'N') NOT NULL DEFAULT 'N',
    delete_yn ENUM('Y', 'N') NOT NULL DEFAULT 'N',
    insert_time DATETIME NOT NULL DEFAULT NOW(),
    update_time DATETIME NULL,
    delete_time DATETIME NULL,
	attach VARCHAR(100),
    PRIMARY KEY (id)
);

INSERT INTO library_board
(title,content,writer,section)
VALUES
('제목 안냥','내용입니다','lucas','free');

DROP table library_board;
DROP table library_bbs;
    	UPDATE board 
    	SET title='2번글!!!!!!', content='내용!!!!!!!!!!수정;'
    	WHERE id=10;

      SELECT id,title,content,writer,view_cnt,section,
        secret_yn,delete_yn,insert_time,update_time,delete_time
        FROM board
        WHERE id = 1;

    	UPDATE library_board 
    	SET delete_yn='N', delete_time=now()
    	WHERE id=4;

/* - SELECT uid, name, total FROM order INNER JOIN user ON uid=num;

- 참조 제약 조건(참조 : constraint / foreign key(외부 키) : uid / references user(num)
ex. order 테이블은 user테이블에 있는 데이터를 참조해야한다

- SELECT uid, name, total FROM user LEFT OUTER JOIN book_order ON uid=num;
   OUTER : 연결조건에 따라 연결시도하여 연결하지 못한 경우라도 LEFT에 위치한 테이블(user)의 데이터는 선택하여 가져온다
      --> user 테이블의 uid, name은 uid와 num이 같지 않더라도 total값 없이 다 온다

- SELECT a, b, c, d FROM A INNER JOIN B ON A.id=B.id INNER JOIN C ON A.id=C.id INNER JOIN D on C.name=D.name;
(A와 B테이블에서 컬럼명이 같을 경우 '.' 사용)   */


CREATE TABLE library_comment (
	parent_id INT NOT NULL,
    id INT NOT NULL AUTO_INCREMENT,
    comment VARCHAR(3000) NOT NULL,
    writer VARCHAR(20) NOT NULL,
    section VARCHAR(10) NOT NULL,
    delete_yn ENUM('Y', 'N') NOT NULL DEFAULT 'N',
    time DATETIME NOT NULL DEFAULT NOW(),
    PRIMARY KEY (id)
);

DROP TABLE library_comment;
SELECT * FROM library_comment;
SELECT * FROM library_board;
    	UPDATE library_comment
    	SET delete_yn='Y', time=now()
    	WHERE id=1;
        -- 내림차순 정렬
		SELECT id,title,content,writer,view_cnt,section,
        secret_yn,delete_yn,insert_time,update_time,delete_time
        FROM library_board 
        WHERE delete_yn = 'N'ORDER BY 1 DESC;
        -- 조회수 mean 1증가
		UPDATE library_board 
    	SET view_cnt=view_cnt+1
    	WHERE id=22;
        
		UPDATE library_board 
    	SET view_cnt=veiw_cnt+1
    	WHERE id=22;

-- 타이틀에 수정이 들어가거나 내용에 수정이 들어가는 line
SELECT * FROM library_board WHERE title LIKE '%수정%'
OR content LIKE '%수정%';

-- 컬럼 추가하기
-- ALTER TABLE '테이블명' ADD '컬럼이름' '컬럼타입' [NULL/NOT NULL]
ALTER TABLE testtable ADD test_id nvarchar(30) null;
-- column 삭제
ALTER TABLE testtable DROP COLUMN column_a;
/* 제약 조건 변경 예시
사원번호, 사원명, 직급, 부서번호 4개의 칼럼으로 구성된 EMP01 테이블을 제약조건을 하나도 설정하지 않은 채 생성

CREATE TABLE EMP01(
EMPNO NUMBER(4),
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
DEPTNO NUMBER(4)
);  

생성이 완료된 EMP01 테이블에 2가지 제약조건을 설정

첫 번째는 EMPNO 컬럼에 기본키를 설정하고 두 번째에는 DEPTNO 컬럼에 외래키를 설정

ALTER TABLE EMP01
ADD CONSTRAINT EMP01_EMPNO_PK PRIMARY KEY(EMPNO);

ALTER TABLE EMP01
ADD CONSTRAINT EMP01_DEPTNO_FK
FOREIGN KEY(DEPTNO) REFERENCES DEPT(DEPTNO); 

NOT NULL 제약 조건을 이미 존재하는 테이블에 추가

NOT NULL 제약 조건은 ADD 대신 MODIFY 명령문을 사용하므로 사용에 주의해야 합니다.
이는 'NULL을 허용하는 상태'에서 'NULL을 허용하지 않는 상태'로 변경하겠다는 의미로 이해하기 바랍니다.

ALTER TABLE EMP01
MODIFY ENAME CONSTRAINT EMP01_ENAME_NN NOT NULL; 
*/

SELECT * FROM library_board 
        WHERE delete_yn = 'Y';
        
        SELECT * FROM library_board 
        WHERE (title LIKE "%글%" OR content LIKE "%천재%") AND delete_yn = 'N'
                ORDER BY 1 DESC;

 

+++

Dynamlic SQL xml 

조건절? AND 자동으로 들어가지고 빠짐

    <select id="findWithoutId" 
    	resultType="com.example.demo.vo.BoardVO"
    	parameterType="com.example.demo.vo.BoardVO">
    	SELECT * FROM user
    	<where>
    		<if test="name != null">
    			name=#{name}
    		</if>
    		<if test="phone != null">
    			AND phone=#{phone}
    		</if>
    		<if test="email != null">
    			AND email=#{email}
    		</if>
    	</where>
    </select>

 

+++

Mybatis 사용시 parameterType="String" 오류

1. parameterType="____VO"로 했을 때 #{-----}로 하면 잘 되지만 parameterType="String"으로 했을 때 #{id}로 하면 There is no getter for property named 'id' in 'class.java.lang.String' exception 에러 남

2. parameterType="String"으로 했을 때 #{value}로 넣어줘야함