데이터베이스 – 페이지 3

오라클(oracle)용 SQL 정리

SQL 명령어 DQL(Data Query Language) SELECT DML(Data Manipulation Language) INSERT UPDATE DELETE DDL(Data Definition Language) CREATE CREATE TABLE newtable AS SELECT * from oldtable; <–테이블 복사 : CREATE TABLE newtable AS SELECT * from oldtable WHERE 1=2; <–테이블만 복사 : INSERT INTO newtable SELECT * from oldtable; <–테이블에 내용만 복사… Continue Reading 오라클(oracle)용 SQL 정리

SqlPlus 히스토리 기능 사용(rlwrap)

rlwrap 프로그램 : readline wrapper의 줄임말 sqlplus와 같이 자체적으로 명령어를 기억해 주지 않는 프로그램에 히스토리 기능을 사용할 수 있도록 해주는 프로그램 사용법 : 명령어 앞에 rlwrap을 붙여 실행하면 됨 rlwrap sqlplus 설치 우분투 리눅스 최신버전에는 설치되어 있음, 설치 안해도 됨 sudo apt-get install rlwrap

Sql 명령어 모음

INSERT 문

INSERT INTO DEPARTMENT VALUES(5, '연구', '');

INSERT INTO tmember
	(no, 		dept, 			 name, 		sex)
VALUES
	(201812345,	'스마트미디어학과', 	'홍길동',	'남');"

1

2

3

4

5

6

INSERT INTO DEPARTMENT VALUES(5, '연구', '');

INSERT INTO tmember

(no, dept, name, sex)

VALUES

(201812345, '스마트미디어학과', '홍길동', '남');"

DELETE 문

DELETE FROM DEPARTMENT
WHERE DEPTNO = 4;

DELETE FROM tmember
WHERE dept='사회복지학과';

1

2

3

4

5

DELETE FROM DEPARTMENT

WHERE DEPTNO = 4;

DELETE FROM tmember

WHERE dept='사회복지학과';

UPDATE 문

UPDATE EMPLOYEE
SET
	DNO = 3,
	SALARY = SALARY * 1.05
WHERE
	EMPNO = 2106;


UPDATE tmember
SET
	dept='스마트미디어학과'
WHERE
	no=201892016;

1

2

3

4

5

6

7

8

9

10

11

12

13

UPDATE EMPLOYEE

SET

DNO = 3,

SALARY = SALARY * 1.05

WHERE

EMPNO = 2106;

UPDATE tmember

SET

dept='스마트미디어학과'

WHERE

no=201892016;

SELECT 문

SELECT * FROM DEPARTMENT;
SELECT DEPTNO, DEPTNAME FROM DEPARTMENT;

SELECT TITLE FROM EMPLOYEE;
SELECT DISTINCT TITLE FROM EMPLOYEE;

SELECT * FROM EMPLOYEE WHERE DNO = 2;
SELECT * FROM EMPLOYEE WHERE DNO <> 2;
SELECT * FROM EMPLOYEE WHERE EMPNAME LIKE '이%';
SELECT FLOOR FROM DEPARTMENT WHERE DEPTNAME='영업' OR DEPTNAME='개발';
SELECT * FROM EMPLOYEE WHERE SALARY BETWEEN 3000000 AND 4500000;
SELECT * FROM EMPLOYEE WHERE SALARY >= 3000000 AND SALARY <= 4500000;

SELECT * FROM EMPLOYEE WHERE DNO IN (1, 3);
SELECT * FROM EMPLOYEE WHERE DNO NOT IN (1, 3);

SELECT EMPNAME, SALARY, SALARY * 1.1 AS NEWSALARY FROM EMPLOYEE;
SELECT * FROM EMPLOYEE WHERE MANAGER IS NULL;
SELECT * FROM EMPLOYEE WHERE MANAGER IS NOT NULL;

SELECT * FROM EMPLOYEE WHERE DNO = 2 ORDER BY SALARY;
SELECT * FROM EMPLOYEE WHERE DNO = 2 ORDER BY SALARY DESC;
SELECT * FROM EMPLOYEE WHERE DNO = 2 ORDER BY SALARY, NAME;

SELECT AVG(SALARY) AS AVGSAL, MAX(SALARY) AS MAXSAL FROM EMPLOYEE;

SELECT DNO, AVG(SALARY) AS AVGSAL, MAX(SALARY) AS MAXSAL FROM EMPLOYEE GROUP BY DNO;

SELECT DNO, AVG(SALARY) AS AVGSAL, MAX(SALARY) AS MAXSAL FROM EMPLOYEE GROUP BY DNO HAVING AVG(SALARY) >= 2500000;

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

SELECT * FROM DEPARTMENT;

SELECT DEPTNO, DEPTNAME FROM DEPARTMENT;

SELECT TITLE FROM EMPLOYEE;

SELECT DISTINCT TITLE FROM EMPLOYEE;

SELECT * FROM EMPLOYEE WHERE DNO = 2;

SELECT * FROM EMPLOYEE WHERE DNO <> 2;

SELECT * FROM EMPLOYEE WHERE EMPNAME LIKE '이%';

SELECT FLOOR FROM DEPARTMENT WHERE DEPTNAME='영업' OR DEPTNAME='개발';

SELECT * FROM EMPLOYEE WHERE SALARY BETWEEN 3000000 AND 4500000;

SELECT * FROM EMPLOYEE WHERE SALARY >= 3000000 AND SALARY <= 4500000;

SELECT * FROM EMPLOYEE WHERE DNO IN (1, 3);

SELECT * FROM EMPLOYEE WHERE DNO NOT IN (1, 3);

SELECT EMPNAME, SALARY, SALARY * 1.1 AS NEWSALARY FROM EMPLOYEE;

SELECT * FROM EMPLOYEE WHERE MANAGER IS NULL;

SELECT * FROM EMPLOYEE WHERE MANAGER IS NOT NULL;

SELECT * FROM EMPLOYEE WHERE DNO = 2 ORDER BY SALARY;

SELECT * FROM EMPLOYEE WHERE DNO = 2 ORDER BY SALARY DESC;

SELECT * FROM EMPLOYEE WHERE DNO = 2 ORDER BY SALARY, NAME;

SELECT AVG(SALARY) AS AVGSAL, MAX(SALARY) AS MAXSAL FROM EMPLOYEE;

SELECT DNO, AVG(SALARY) AS AVGSAL, MAX(SALARY) AS MAXSAL FROM EMPLOYEE GROUP BY DNO;

SELECT DNO, AVG(SALARY) AS AVGSAL, MAX(SALARY) AS MAXSAL FROM EMPLOYEE GROUP BY DNO HAVING AVG(SALARY) >= 2500000;

CREATE 문

CREATE TABLE tmember (
	no	INTEGER NOT NULL,
	dept	TEXT DEFAULT '스마트미디어학과',
	name	TEXT NOT NULL,
	sex	TEXT DEFAULT '여' CHECK(SEX in ('남','여')),
	hp	TEXT UNIQUE,
	post	INTEGER,
	PRIMARY KEY(no)
);

CREATE TABLE tbook (
	no	INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
	title	TEXT,
	author	TEXT,
	publ	TEXT,
	yea	INTEGER,
	isbn	TEXT,
	symb	TEXT
);


CREATE TABLE tlend (
	bno	INTEGER,
	mno	INTEGER,
	cdate	TEXT DEFAULT CURRENT_TIMESTAMP,
	days	INTEGER,
	PRIMARY KEY(cdate),
	FOREIGN KEY(bno) REFERENCES tbook(no)
		on update CASCADE
		on delete CASCADE,
	FOREIGN KEY(mno) REFERENCES tmember(no)
		on update CASCADE
		on delete set NULL
);

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

CREATE TABLE tmember (

no INTEGER NOT NULL,

dept TEXT DEFAULT '스마트미디어학과',

name TEXT NOT NULL,

sex TEXT DEFAULT '여' CHECK(SEX in ('남','여')),

hp TEXT UNIQUE,

post INTEGER,

PRIMARY KEY(no)

);

CREATE TABLE tbook (

no INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,

title TEXT,

author TEXT,

publ TEXT,

yea INTEGER,

isbn TEXT,

symb TEXT

);

CREATE TABLE tlend (

bno INTEGER,

mno INTEGER,

cdate TEXT DEFAULT CURRENT_TIMESTAMP,

days INTEGER,

PRIMARY KEY(cdate),

FOREIGN KEY(bno) REFERENCES tbook(no)

on update CASCADE

on delete CASCADE,

FOREIGN KEY(mno) REFERENCES tmember(no)

on update CASCADE

on delete set NULL

);

DROP 문

DROP TABLE employee;

1	DROP TABLE employee;

JOIN 문

SELECT  tlend.mno as hakbun,
	tmember.name as name,
	tbook.title as title,
	tlend.cdate,
	tlend.days
FROM tlend, tbook, tmember
WHERE
	tlend.bno = tbook.no
	AND
	tlend.mno = tmember.no;

1

2

3

4

5

6

7

8

9

10

SELECT tlend.mno as hakbun,

tmember.name as name,

tbook.title as title,

tlend.cdate,

tlend.days

FROM tlend, tbook, tmember

WHERE

tlend.bno = tbook.no

AND

tlend.mno = tmember.no;

오라클 명령

SELECT * FROM dba_users;  <-- DBA계정만 사용 가능
SELECT * FROM all_users;  <-- 모든 계정에서 사용 가능
SELECT * FROM tab; <-- 테이블 목록 보기

DESC DEPARTMENT;


CREATE USER KIM IDENTIFIED BY bluesky
  DEFAULT TABLESPACE users
  TEMPORARY TABLESPACE temp;

grant connect, resource, create session, create view to KIM;

1

2

3

4

5

6

7

8

9

10

11

12

SELECT * FROM dba_users; <-- DBA계정만 사용 가능

SELECT * FROM all_users; <-- 모든 계정에서 사용 가능

SELECT * FROM tab; <-- 테이블 목록 보기

DESC DEPARTMENT;

CREATE USER KIM IDENTIFIED BY bluesky

DEFAULT TABLESPACE users

TEMPORARY TABLESPACE temp;

grant connect, resource, create session, create view to KIM;

데이터베이스 연습

다음 다이어그램을 보고 tmember, tbook, tlend 테이블을 생성하시오. tmember 테이블 제약 사항 no : 기본키, not null, 정수 sex : ‘남’, ‘여’ 중 하나만 입력 가능, 입력이 없으면 기본값 ‘여’ 로 입력 hp : 유일 tbook 테이블 제약 사항 no, 기본키, not null, 자동증가 isbn : 정수 tlend 테이블 제약… Continue Reading 데이터베이스 연습

도서관리 테이블 생성

도서관리용 테이블 생성시 유의사항 필요한 애트리뷰트(속성; 필드)에 제약 조건 추가 적용 기본키(Primary Key) 외래키(Foreign Key) UNIQUE NOT NULL CHECK Sqlite 에서 테이블 생성 회원테이블 : 기본키 추가해야 함

CREATE TABLE  tmember  (
	 no 	INTEGER,
	 dept 	TEXT,
	 name 	TEXT,
	 sex 	TEXT,
	 hp 	TEXT,
	 email 	TEXT,
	 post 	INTEGER,
	 addr 	TEXT,
	 addre 	TEXT
);

1

2

3

4

5

6

7

8

9

10

11

CREATE TABLE tmember (

no INTEGER,

dept TEXT,

name TEXT,

sex TEXT,

hp TEXT,

email TEXT,

post INTEGER,

addr TEXT,

addre TEXT

);

도서테이블 : 기본키 추가해야함

CREATE TABLE  tbook  (
	 no 	INTEGER,
	 title 	TEXT,
	 author 	TEXT,
	 publ 	TEXT,
	 yea 	INTEGER,
	 isbn 	TEXT,
	 symb 	TEXT
);

1

2

3

4

5

6

7

8

9

CREATE TABLE tbook (

no INTEGER,

title TEXT,

author TEXT,

publ TEXT,

yea INTEGER,

isbn TEXT,

symb TEXT

);

대출테이블 : 외래키(foreign key)를 추가해서 설정해야 함

CREATE TABLE  tlend  (
	 bno 	INTEGER,
	 mno 	INTEGER,
	 cdate 	TEXT,
	 days 	INTEGER
);

1

2

3

4

5

6

CREATE TABLE tlend (

bno INTEGER,

mno INTEGER,

cdate TEXT,

days INTEGER

);

오라클에서 테이블생성 참조 회원테이블(tmember) 도서테이블(tbook) 대출테이블(tlend)

Sqlite의 트리거(trigger) 정의

참조 https://nmshome.tistory.com https://thinking-jmini.tistory.com/21 트리거의 정의 데이터베이스가 미리 정해 놓은 조건이 충족되거나, 특정 테이블에 삽입/수정/삭제 등의 데이터 변경이 발생하면 자동 실행되도록 정의한 동작 트리거 이름은 해당 데이터베이스에서 유일해야 함 트리거 생성 CREATE TRIGGER [트리거 명] [동작시점] [SQL 연산] ON [테이블 명] BEGIN [수행할 SQL문] END 동작 시점 BEFORE… Continue Reading Sqlite의 트리거(trigger) 정의

생능_오라클SQL_08장. Index(인덱스)

인덱스 정의 : 어떤 데이터가 어디에 있다 라는 주소록 같은 개념

생능_오라클SQL_07장. Constraint(제약조건)

Constraint(제약 조건)이란 테이블에 올바른 데이터만 입력 받고 잘못된 데이터는 들어오지 못하도록 컬럼마다 정하는 규칙 제약조건 종류 NOT NULL : NULL 값 입력 불가 UNIQUE : 중복된 값 입력 불가 PRIMARY KEY : NOT NULL + UNIQUE 이면서 테이블 내에서 데이터들끼리의 유일성을 보장하는 컬럼에 설정, 테이블당 1개만 설정 가능 FOREIGN KEY… Continue Reading 생능_오라클SQL_07장. Constraint(제약조건)

생능_오라클SQL_06장. DML로 데이터 관리

INSERT 날짜 형식에서 오류 발생 날짜 입력에서 유닉스와 윈도우의 포맷이 달라 오류발생 유닉스 날짜 : DD-MON-YY 윈도우 날짜 : YYYY-MM-DD 유닉스 날짜 -> 윈도우 날짜 형식으로 변환 ALTER SESSTION SET NLS_DATE_FORMAT=’RRRR-MM-DD:HH24:MI:SS’; Null 값 입력하기 INSERT INTO table_name(fld1, fld2) VALUES(‘hong’, NULL); 테이블 생성 및 데이터 입력 CREATE TABLE prof3 AS SELECT… Continue Reading 생능_오라클SQL_06장. DML로 데이터 관리

생능_오라클SQL_05장. DDL 명령과 딕셔너리

오라클 명령어 종류 DML(Data Manipulation Language) : INSERT, UPDATE, DELETE, MERGE DDL(Data Definition Language) : CREATE, ALTER, TRUNCATE, DROP DCL(Data Control Language) : GRANT, REVOKE TCL(Transaction Control Language) : COMMIT, ROLLBACK DQL(Data Query Language) : SELECT CREATE 테이블 생성시 제한 사항 테이블 이름은 반드시 문자로 시작 이름은 최대 30바이트까지 가능… Continue Reading 생능_오라클SQL_05장. DDL 명령과 딕셔너리