DB를 디비자

DB에서는 문자 또한 비교 가능

select 'b'='b';
select 'a'<'b';

INDEX

SHOW TABLES; SHOW FULL TABLES; SHOW INDEX
FROM 테이블명; SHOW INDEX
FROM t_score; SHOW FULL TABLES
WHERE TABLE_TYPE LIKE 'view';

-- CREATE INDEX 인덱스 이름 ON 테이블명(컬럼명);
CREATE INDEX idx_a ON membertbl(memberName);

-- ALTER TABLE 테이블명 DROP INDEX 인덱스명;
DROP INDEX idx_a ON membertbl;

SHOW INDEX FROM membertbl;

view

CREATE VIEW view_a AS 
SELECT*FROM membertbl
WHERE memberName LIKE '%이%';

SELECT*FROM VIEW_a;
WHERE memberID='Dang'; -- 조건식도 가능

오늘의 시험 문제 답안:

CREATE TABLE t_order(
	cus_no int,
	o_no INT PRIMARY key,
	o_date DATE default NOW(),
	o_price INT,
	FOREIGN KEY(cus_no) references
	t_customer(cus_no)
);

SELECT*FROM t_order;

INSERT INTO t_order
(o_no, cus_no, o_price)
VALUES
(1,3,55000),
(2,5,70000),
(3,3,60000);

DELETE FROM t_order WHERE o_no=2;

CREATE TABLE t_customer(
	cus_no INT PRIMARY key,
	nm CHAR(10) NOT null
);

SELECT*FROM t_customer;

INSERT INTO t_customer 
(cus_no, nm)
VALUES
(3, '홍길동'),
(5, '이순신');

UPDATE t_customer
SET nm = '장보고'
WHERE cus_no=5;


SHOW TABLEs;
SHOW full TABLES WHERE table_type LIKE 'view';
SHOW INDEX FROM t_order;


create index idx_customer_nm on T_CUSTOMER(nm);

drop index idx_customer_nm on T_CUSTOMER;


SELECT o_price, o_no
FROM t_order
WHERE cus_no=3;

select

A.o_no, A.o_date, A.o_price, B.nm

from T_ORDER A

INNER JOIN T_CUSTOMER B

ON A.cus_no=B.cus_no;



SELECT A.o_no, A.o_date, A.o_price, B.nm

FROM T_ORDER A

LEFT JOIN T_CUSTOMER B

On A.cus_no = B.cus_no;



CREATE VIEW view_order_info AS select

A.o_no, A.o_date, A.o_price, B.nm

from T_ORDER A

INNER JOIN T_CUSTOMER B

ON A.cus_no=B.cus_no;


SELECT*FROM view_order_info;

DROP VIEW view_order_info;