definition

Structured Query Language

CREATE

//DATABASE 생성
create DATABASE opentutorials;
//TABLE 생성 (어떤 database에 table을 생성할건지 "USE 데이터베이스 이름"을 이용해 지정)
CREATE TABLE topic(
id INT(11) NOT NULL AUTO_INCREMENT,
title VARCHAR(100) NOT NULL
description TEXT NULL,
created DATETIME NOT NULL,
author VARCHAR(30) NULL,
profile VARCHAR(100) NULL,
PRIMARY KEY(id));
//TABLE에 내용 추가
INSERT INTO topic (title,description,created,author,profile) VALUES('MySQL','MySQL is ...',NOW(),'egoing','developer');

READ

//DATABASES 출력
SHOW DATABASES
//TABLES 출력
SHOW TABLES
//TABLE 정보 출력
DESC topic
//TABLE 내용 출력
SELECT * FROM topic
SELECT title, description FROM topic

//author는 egoing이고 id로 오름차순, 두개만 출력
SELECT id, title, created, author FROM topic WHERE author='egoing' ORDER BY id ASC LIMIT 2;

//DATE_FORMAT, MONTH 함수 사용,  AS 문법 사용법 
SELECT MEMBER_ID, MEMBER_NAME, GENDER, DATE_FORMAT(DATE_OF_BIRTH, '%Y-%m-%d') 
AS DATE_OF_BIRTH 
FROM MEMBER_PROFILE 
where MONTH(DATE_OF_BIRTH)=3 AND GENDER='W' AND TLNO IS NOT NULL 
ORDER BY MEMBER_ID ASC

UPDATE

//특정 행 정보 수정하기
UPDATE topic set description='Oracle is ...', title='Oracle' where id=2;

DELETE

//DATABASE 삭제
DROP DATABASE opentutorials
//table의 행 삭제
DELETE FROM topic WHERE id=5;