MySQL) 예제 sql문

회원, 게시물 테이블

create

DROP DATABASE IF EXISTS mydb1;
CREATE DATABASE mydb1;
USE mydb1;
CREATE TABLE 회원(
	번호 int PRIMARY KEY AUTO_INCREMENT,
	성명 varchar(20),
	주민번호 char(13)
);
CREATE TABLE 게시물(
	번호 int PRIMARY KEY AUTO_INCREMENT,
	제목 varchar(20) NOT NULL UNIQUE,
	내용 varchar(200) NULL,
	작성자번호 int NOT NULL,
    작성일 timestamp DEFAULT CURRENT_TIMESTAMP,
	FOREIGN KEY (작성자번호) REFERENCES 회원(번호)
);

insert

INSERT INTO 회원(성명, 주민번호) VALUES('홍길동', '1111111111111');
INSERT INTO 회원(성명, 주민번호) VALUES('홍민지', '2222222222222');
INSERT INTO 회원(성명, 주민번호) VALUES('김은영', '3333333333333');
INSERT INTO 회원(성명, 주민번호) VALUES('최영웅', '4444444444444');

INSERT INTO 게시물(제목, 내용, 작성자번호) VALUES('제목1', '내용1', 1);
INSERT INTO 게시물(제목, 내용, 작성자번호) VALUES('제목2', '내용2', 2);
INSERT INTO 게시물(제목, 내용, 작성자번호) VALUES('제목3', '내용3', 3);
INSERT INTO 게시물(제목, 내용, 작성자번호) VALUES('제목4', '내용4', 4);

SHOW TABLES;

SELECT * FROM 회원;
SELECT * FROM 게시물;

index

CREATE INDEX idx1 ON 회원(성명);
SELECT * FROM 회원 WHERE 성명='홍길동';
ALTER TABLE 회원 DROP INDEX idx1;

view

CREATE VIEW view1 AS SELECT * FROM 회원;
SELECT * FROM view1;
DROP VIEW view1;

---

부서, 직원 테이블

CREATE DATABASE studydb;
USE studydb;

CREATE TABLE dept(
no INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20)
);

INSERT INTO dept(name) VALUES('A');
INSERT INTO dept(name) VALUES('B');
INSERT INTO dept(name) VALUES('C');

CREATE TABLE employee(
no INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20),
dept_no INT
#,FOREIGN KEY (employee) REFERENCES dept(no)
);

INSERT INTO employee(name, dept_no) VALUES('ga', 2);
INSERT INTO employee(name, dept_no) VALUES('na', 3);
INSERT INTO employee(name, dept_no) VALUES('da', 3);
INSERT INTO employee(name, dept_no) VALUES('la', 2);
INSERT INTO employee(name, dept_no) VALUES('ma', 2);
INSERT INTO employee(name, dept_no) VALUES('sa', null);

#equi join
SELECT e.no, e.name, d.name FROM dept d, employee e
WHERE e.dept_no = d.no;

#left join
SELECT d.no, d.name, e.no, e.name
FROM dept d LEFT JOIN employee e
ON e.dept_no = d.no;

---

빅데이터 테이블

CREATE DATABASE digdata;
USE digdata;
CREATE TABLE data(
	did int PRIMARY KEY NOT NULL,
	dname char(50) NOT NULL,
	dpwd char(100) NOT NULL,
    dtel char(50),
    demail char(150),
    ddate timestamp DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO data(did, dname, dpwd, dtel, demail) VALUES(1, 'aname', '1234', '01012341234', 'aname@naver.com');
INSERT INTO data(did, dname, dpwd, dtel, demail) VALUES(2, 'bname', '1234', '01012125656', 'bname@kakao.com');
INSERT INTO data(did, dname, dpwd, dtel, demail) VALUES(3, 'cname', '1234', '01045458989', 'cname@gamil.com');
INSERT INTO data(did, dname, dpwd, dtel, demail) VALUES(4, 'dname', '1234', '01056567878', 'dname@daum.net');
INSERT INTO data(did, dname, dpwd, dtel, demail) VALUES(5, 'ename', '1234', '01023234545', 'ename@naver.com');

CREATE INDEX dname_index ON data(dname);
SHOW INDEXES FROM data;
DROP INDEX dname_index ON data;

CREATE VIEW vw_data AS SELECT dname, dtel, demail FROM data;
SELECT * FROM vw_data;
DROP VIEW vw_data;

---

멤버 테이블

CREATE TABLE member(
	id int PRIMARY KEY NOT NULL,
	name char(50) NOT NULL,
	pwd char(100) NOT NULL,
	tel char(50),
	email char(150),
	date timestamp DEFAULT CURRENT_TIMESTAMP
);



CREATE INDEX member_name_idx ON member(name);


CREATE VIEW member_view AS SELECT * FROM member;

 

 

 

 

---

참고 글 URL

권한주기 : snowple.tistory.com/260

권한관리 : extbrain.tistory.com/44

인덱스, 뷰 : victorydntmd.tistory.com/140