데이터베이스_SQL문 예시

 

 

[1] SQL 

---

 

(1) SQL 특징

-구조화된 질의어

-정의,조작 ,제어,검색 기능 모두 제공하는 종합적인 데이터베이스 언어 

- 비절차적 언어 

- 관계형 DBMS 

- 종류 : 데이터 정의어 DDL , 데이터 조작어 DML , 데이터 제어어 DCL

 

 

[2] 데이터 정의어 DDL

---

(1) CREATE 생성

-데이터베이스, 테이블, 인덱스 를 생성하는 명령문 

 

column	type	default value	nullable
ID	int(11)		NO
name	varchar(20)		NO
age	int(11)		NO
address	char(25)		YES
salary	decimal(18,2)		YES

 

varchar : 가변길이 , 타입크기만큼 데이터가 안들어오면 빈공간을 채워넣지 않는다 

char : 타입 크기만큼 데이터가 안들어오면 빈공간을 스페이스로 채워넣는다 

decimal (18,2) 는 전체 18 자리 중에서 소숫점 2자리까지 확보되었다 

 

칼럼 설정 

create table customer (
	ID	int		not null,
	name	varchar(20)	not null,
	age	int		not null,
	address	char(25),
	salary	decimal(18,2)
);

 

 

 

기본키 PK 설정 

ALTER TABLE CUSTOMER
ADD CONSTRAINT pk_customer primary key (ID);

 

 

기본키가 아닌 복합키 설정 

ALTER TABLE CUSTOMER
ADD PRIMARY KEY (ID)

 

 

테이블 표에 기본키를 설정한다 

create table customer (
	ID	int		not null,
	name	varchar(20)	not null,
	age	int		not null,
	address	char(25),
	salary	decimal(18,2)
    	primary	key(ID)
);

 

 

똑같은 테이블표를 복사해서 만든다 

CREATE TABLE NEW_TABLE AS
SELECT * FROM OLD_TABLE WHERE 1=2

 

 

(2) ALTER 수정

-테이블의 속성을 수정하는 명령

 

① 컬럼 추가하기 ADD 

ALTER TABLE 테이블 ADD COLUMN 추가할필드명 varchar(32) NOT NULL;

 

 

② 컬럼 변경하기 MODIFY

ALTER TABLE 테이블 MODIFY COLUMN 필드명 varchar(15) NULL;

 

③ 컬럼 이름 변경 CHANGE

ALTER TABLE 테이블 CHANGE COLUMN 필드명 변경할필드명 varchar(15) NULL;

 

 

④ 테이블 이름 변경 RENAME 

ALTER TABLE 테이블 RENAME COLUMN 필드명;

 

 

(3) DROP 삭제

-테이블을 제거하는 명령, 저장된 데이터도 삭제된다 

-테이블 표 자체도 삭제된다 

 

① 컬럼 삭제하기 DROP

ALTER TABLE 테이블 DROP COLUMN 필드명;

 

② 테이블 삭제하기 DROP

DROP TABLE 테이블

 

 

[2] 데이터 조작어 DML

---

(1) SELECT 조회한다 

// 전체 테이블 조회 
SELECT * FROM 테이블;

// bno>0 이상인 테이블 조회 
SELECT * FROM tbl_board WHERE bno >0

//테이블의 칼럼 1,2,3 만 조회 
SELECT 컬럼1, 컬럼2, 컬럼3 FROM 테이블;

// 테이블의 칼럼1을 조회할때 중복값 제외하고 조회
SELECT DISTINCT 컬럼1 FROM 테이블;

// 별칭으로 조회
SELECT 컬럼1 AS 컬럼1의별칭,컬럼2 AS 컬럼2의별칭 FROM 테이블;

// 테이블의 칼럼1,2에서 조건식이 일치하는 데이터 조회
SELECT 컬럼1,컬럼2  FROM 테이블 WHERE 조건식 ;

// 오름차순 조회 
SELECT 컬럼1,컬럼2  FROM 테이블 WHERE 조건식 ORDER BY 넘버 ;

// 조건식에 일치하면서 정보가 들어있는 데이터를 조회한다 
SELECT 컬럼1,컬럼2  FROM 테이블 WHERE 조건식 LINK %정보% ;

// 조건식 or 조건식이 일치하는 데이터를 조회한다
SELECT 컬럼1,컬럼2  FROM 테이블 WHERE 조건식 OR 조건식

// 속성이 null인 데이터 조회
SELECT * FROM 테이블 WHERE 속성 IS NULL;

// 속성이 not null 인 데이터 조회
SELECT * FROM 테이블 WHERE 속성 IS NOT NULL;

 

 

(2) 튜플의 집단함수

㉮ 합계 구하기 : SUM()

- 상품 테이블에서 분류의 속성값이 아동화인 데이터를 검색하여  판매가들의 합과 아동화 수 , 평균값을 구한다 

- 합계.COUNT(*) :아동화 수의 합계 

- 개수.SUM(판매가) : 판매가들의 합계

- 개수.SUM(판매가)/COUNT(*) : 평균의 값 

SELECT SUM(판매가) AS 합계.COUNT(*)AS 개수.SUM(판매가)/COUNT(*)AS평균 FROM 상품테이블 WHERE 분류 ='아동화' ;

 

㉯ 평균값 구하기 : AVG() 

- 상품 테이블에서 분류 속성값이 아동화 인 데이터를 검색하여 판매가의 평균값을 구한 후 평균판매가를 구한다 

SELECT AVG(판매가) AS 평균판매가 FROM 상품테이블 WHERE 분류 ='아동화' ;

 

㉰ 몇 개인지 값을 구한다 : COUNT()

- 상품 테이블에서 분류의 속성 값의 갯수를 구한다 (중복제외)

SELECT COUNT(DISTINCT 분류) FROM 상품테이블 ;

 

㉱ 그룹화한다 : GROUP BY ...HAVING >>>

- 상품테이블에서 분류항목을 그룹화한다. 그룹화할때 튜플의 갯수가 3개 이상인 분류명과 갯수를 조회한다 

SELECT 분류.COUNT(*)AS 개수 FROM 상품테이블 GROUP BY 분류 HAVING COUNT(*)>=3;

 

㉲ MIN , MAX

 

 

(3) INSERT 데이터 추가

① 튜플(행)의 값을 추가한다 : INSERT INTO ...VALUES

- 상품테이블에 상품번호, 상품명 속성에 각각 41, 에어러닝, 30000을 값을 넣는다

INSERT INTO 상품(상품번호,상품명,판매가)VALUES(41."에어러닝".30000);

 

= 같은 내용에 다른 형식 (조건, 상품테이블에 일부 속성이 아니라 전체 속성을 불러올경우) 

INSERT INTO 상품 VALUES(41."에어러닝".30000);

 

(4) UPDATE 데이터 수정

 

① 데이터 값을 수정한다 : UPDATE .. SET ... WHERE

- 상품 테이블에서 분류의 속성 값 중에서 "상ㅇ의"라는 단어를 찾아 "상의"로 변경한다 

UPDATE 상품 SET 분류 ="상의" WHERE 분류 ="상o의";

 

 (5) DELETE 튜플 삭제 

① 튜플을 식제한다 : DELETE FROM....WHERE..

- 상품 테이블에서 분류의 속성 값 중에서 "상의"라는 튜플 값을 튜플전체 삭제한다

- 데이터 값만 삭제되나 테이블의 표까지 삭제되는것이 아니다  

DELETE FROM 상품 WHERE 분류 ="상의";

 

 

[3]데이터 제어어 DCL

---

(1) grant 권한 부여

① 사용자에게 권한을 부여한다 : GRANT

- DBA가 U1 사용자 1에게 INSERT삽입과 SELECT 검색을 할수 있는 권한과 

권한을 다른 사람에게 부여할수 있는 권한 

U1이 U2에게 검색 권한을 부여한다 

DBA :GRANT INSERT< SELECT ON STUDENT TO U1 WITH GRANT OPTION;
U1 : GRANT SELECT ON STUDENT TO U2;

 

(1) revoke 권한 회수

② 권한을 삭제한다 : REVOKE

-DBA가 사용자1 U1의 권한을 제거하고 

U1이 가지고 있는 다른 사람에게 권한을 부여하는 기능을 제거하고 (GRANT OPTION FOR ON)

U1이 다른 유저에거 부여한 권한도 제거한다 (CASCADE) 

DBA :REVOKE SELECT GRANT OPTION FOR ON STUDENT FROM U1 CASCADE;

 

 

 

 

테이블 삭제

delete from user

 

테이블만 복사 생성 

create table b1 select * from a1 where false 

create table b1 select * from a1 where 1<>1

 

테이블 + 데이터 복사 생성 

create table b1 select * from a1

 

데이터 생성하기 

insert into tbl_board(bno, title,content,writer) values(seq_board.nextval, #{title}, #{content} , #{writer} )

insert into tbl_board(bno, title,content,writer) values(seq_board.nextval, 'title_test' , 'content_test' ,'writer_test')

 

 

오라클 시퀀스 선택하기 

select seq_board.nextval from dual 

 

오라클 계정 생성 

create user book_ex identified by book_ex default tablespace users temporary tablespace temp

 

오라클 시퀀스 생성 

create sequence seq_board;

 

오라클 테이블 칼럼 만들기 

creat table tbl_board{

bno number(10,0),

title varchear2(200) not null,

content varchear2(2000) not null,

writer varchear2(50) not null,

regdate date default sysdate,

updatedate date default sysdate,

)

 

오라클 PK 지정 

alter table tbl_board add constraint pk_board primary key(bno);

 

 

오라클 권한 주기

grant connect , dba to book_ex

 

오라클 포트 질문하기

select dbms_xdb.gethttpport() from dual

 

오라클 포트 변경 

exec dbms_xdb.sethttpport(9090);

 

커밋

commit

 

데이터 역순 출력하기 

select * from tbl_board order by bno desc;