Sql 명령어 모음

Posted on: 2019년 10월 24일 2019년 10월 24일
Categories: 강의자료, 데이터베이스

INSERT 문

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

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

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;

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
);

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;

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;

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;

whan0623

516