출처| https://www.youtube.com/watch?v=UVY0mfa4VP0&list=PLqTUMsvO70nk8WfCyU-IPmc85390CaSqM&index=1
https://cafe.naver.com/thisisMySQL
https://velog.io/@ong_hh/%EB%8D%B0%EC%9D%B4%ED%84%B0%EB%B2%A0%EC%9D%B4%EC%8A%A4-%EA%B5%AC%EC%B6%95
DROP TABLE 테이블이름;
주의 사항은 외래 키
제약 조건의 기준 테이블은 삭제 할 수가 없다.
먼저 외래 키가 생성된 외래 키 데이블을 삭제해야 한다.
-> 외래 키 먼저 삭제 -> 기준 키 삭제
alter table usertbl
add homepage varchar(10) -- 열 추가
default 'http//wwwwww'
null;
FIRST
또는 AFTER 열 이름
을 지정하면 된다.alter table usertbl
drop column mobile1;
drop column
제약 조건이 걸려 있다면, 제약조건이 걸린 열을 먼저 삭제 후, 열을 삭제해야 한다.alter table usertbl
change column name uName varchar(10) null;
alter table usertbl
drop primary key;
이렇게 삭제하게 된다면 오류가 발생한다. 그 이유는 userid는 buytbl에 외래 키로 연결되어 있기 때문이다. 그러므로 외래 키를 제거 후 기본 키를 제거해야한다.
alter table buytbl
drop foreign key buytbl_ibfk_1;
-- <실습 5> --
USE tabledb;
DROP TABLE IF EXISTS buytbl, usertbl;
CREATE TABLE usertbl
( userID CHAR(8),
name VARCHAR(10),
birthYear INT,
addr CHAR(2),
mobile1 CHAR(3),
mobile2 CHAR(8),
height SMALLINT,
mDate DATE
);
CREATE TABLE buytbl
( num int AUTO_INCREMENT PRIMARY KEY,
userid CHAR(8),
prodName CHAR(6),
groupName CHAR(4),
price INT ,
amount SMALLINT
);
INSERT INTO usertbl VALUES('LSG', '이승기', 1987, '서울', '011', '1111111', 182, '2008-8-8');
INSERT INTO usertbl VALUES('KBS', '김범수', NULL, '경남', '011', '2222222', 173, '2012-4-4');
INSERT INTO usertbl VALUES('KKH', '김경호', 1871, '전남', '019', '3333333', 177, '2007-7-7');
INSERT INTO usertbl VALUES('JYP', '조용필', 1950, '경기', '011', '4444444', 166, '2009-4-4');
INSERT INTO buytbl VALUES(NULL, 'KBS', '운동화', NULL , 30, 2);
INSERT INTO buytbl VALUES(NULL,'KBS', '노트북', '전자', 1000, 1);
INSERT INTO buytbl VALUES(NULL,'JYP', '모니터', '전자', 200, 1);
INSERT INTO buytbl VALUES(NULL,'BBK', '모니터', '전자', 200, 5);
ALTER TABLE usertbl
ADD CONSTRAINT PK_usertbl_userID
PRIMARY KEY (userID);
DESC usertbl;
ALTER TABLE buytbl
ADD CONSTRAINT FK_usertbl_buytbl
FOREIGN KEY (userID)
REFERENCES usertbl (userID);
DELETE FROM buytbl WHERE userid = 'BBK';
ALTER TABLE buytbl
ADD CONSTRAINT FK_usertbl_buytbl
FOREIGN KEY (userID)
REFERENCES usertbl (userID);
INSERT INTO buytbl VALUES(NULL,'BBK', '모니터', '전자', 200, 5);
SET foreign_key_checks = 0;
INSERT INTO buytbl VALUES(NULL, 'BBK', '모니터', '전자', 200, 5);
INSERT INTO buytbl VALUES(NULL, 'KBS', '청바지', '의류', 50, 3);
INSERT INTO buytbl VALUES(NULL, 'BBK', '메모리', '전자', 80, 10);
INSERT INTO buytbl VALUES(NULL, 'SSK', '책' , '서적', 15, 5);
INSERT INTO buytbl VALUES(NULL, 'EJW', '책' , '서적', 15, 2);
INSERT INTO buytbl VALUES(NULL, 'EJW', '청바지', '의류', 50, 1);
INSERT INTO buytbl VALUES(NULL, 'BBK', '운동화', NULL , 30, 2);
INSERT INTO buytbl VALUES(NULL, 'EJW', '책' , '서적', 15, 1);
INSERT INTO buytbl VALUES(NULL, 'BBK', '운동화', NULL , 30, 2);
SET foreign_key_checks = 1;
ALTER TABLE userTBL
ADD CONSTRAINT CK_birthYear
CHECK ( (birthYear >= 1900 AND birthYear <= 2023) AND (birthYear IS NOT NULL) );
UPDATE usertbl SET birthYear=1979 WHERE userID='KBS';
UPDATE usertbl SET birthYear=1971 WHERE userID='KKH';
ALTER TABLE userTBL
ADD CONSTRAINT CK_birthYear
CHECK ( (birthYear >= 1900 AND birthYear <= 2023) AND (birthYear IS NOT NULL) );
INSERT INTO usertbl VALUES('TKV', '태권뷔', 2999, '우주', NULL , NULL , 186, '2023-12-12');
INSERT INTO usertbl VALUES('SSK', '성시경', 1979, '서울', NULL , NULL , 186, '2013-12-12');
INSERT INTO usertbl VALUES('LJB', '임재범', 1963, '서울', '016', '6666666', 182, '2009-9-9');
INSERT INTO usertbl VALUES('YJS', '윤종신', 1969, '경남', NULL , NULL , 170, '2005-5-5');
INSERT INTO usertbl VALUES('EJW', '은지원', 1972, '경북', '011', '8888888', 174, '2014-3-3');
INSERT INTO usertbl VALUES('JKW', '조관우', 1965, '경기', '018', '9999999', 172, '2010-10-10');
INSERT INTO usertbl VALUES('BBK', '바비킴', 1973, '서울', '010', '0000000', 176, '2013-5-5');
UPDATE usertbl SET userID = 'VVK' WHERE userID='BBK';
SET foreign_key_checks = 0;
UPDATE usertbl SET userID = 'VVK' WHERE userID='BBK';
SET foreign_key_checks = 1;
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 ;
SELECT COUNT(*) FROM buytbl;
SELECT B.userid, U.name, B.prodName, U.addr, CONCAT(U.mobile1, U.mobile2) AS '연락처'
FROM buytbl B
LEFT OUTER JOIN usertbl U
ON B.userid = U.userid
ORDER BY B.userid ;
SET foreign_key_checks = 0;
UPDATE usertbl SET userID = 'BBK' WHERE userID='VVK';
SET foreign_key_checks = 1;
ALTER TABLE buytbl
DROP FOREIGN KEY FK_usertbl_buytbl;
ALTER TABLE buytbl
ADD CONSTRAINT FK_usertbl_buytbl
FOREIGN KEY (userID)
REFERENCES usertbl (userID)
ON UPDATE CASCADE;
UPDATE usertbl SET userID = 'VVK' WHERE userID='BBK';
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.userid;
DELETE FROM usertbl WHERE userID = 'VVK';
ALTER TABLE buytbl
DROP FOREIGN KEY FK_usertbl_buytbl;
ALTER TABLE buytbl
ADD CONSTRAINT FK_usertbl_buytbl
FOREIGN KEY (userID)
REFERENCES usertbl (userID)
ON UPDATE CASCADE
ON DELETE CASCADE;
DELETE FROM usertbl WHERE userID = 'VVK';
SELECT * FROM buytbl ;
ALTER TABLE usertbl
DROP COLUMN birthYear ;