Basic SQL

Data Definition Language

DDL: 데이터베이스 구조를 다루는 언어

 

Statements

CREATE //생성

DROP //삭제

ALTER //변경

TRUNCATE //데이터만 삭제

 

 

Database SQL

CREATE DATABASE testDB;

DROP DATABASE testDB;

SHOW DATABASES;

USE testDB;

 

 

 

 

Relation SQL

 

 

CREATE TABLE department(

deptno INT NOT NULL,

deptname VARCHAR(45) NOT NULL,

floor INT

CONSTRAINT PK_Deptno PRIMARY KEY(deptno)

);

​

DROP TABLE department;

​

TRUNCATE TABLE department;

​

ALTER TABLE department

ADD floor VARCHAR(45);

​

​

ALTER TABLE department

DROP COLUMN floor;

​

ALTER TABLE department

CHANGE COLUMN floor floors VARCHAR(45);

​

ALTER TABLE department

modify COLUMN floor INT;

 

 

 

 

MYSQL constraints

NOT NULL

PRIMARY KEY

FOREIGN KEY

UNIQUE

CHECK

DEFAULT

CREATE INDEX

 

CREATE TABLE employee(

empno int NOT NULL,

empname varchar(45),

title varchar(45),

manager int,

salary int,

dno int,

CONSTRAINT PK_Employee PRIMARY KEY (empno),

CONSTRAINT FK_Employee_Manager

FOREIGN KEY (manager) REFERENCES employee(empno),

CONSTRAINT FK_Department_Employee

FOREIGN KEY (dno) REFERENCES department(deptno)

);

 

 

 

 

 

Data Manipulation Language

DML은 실질적으로 데이터를 다루는 언어이다

INSERT

UPDATE

DELETE

SELECT

​

​

INSERT INTO department (deptno, deptname, floor)

VALUES(1,'영업',8);

​

INSERT INTO department VALUES(1,'영업',8);

전체일때

​

​

UPDATE employee

SET title='대리', salary=2500000

WHERE empno=1365;

​

DELETE FROM employee WHERE empno=1365;

​

​

★SELECT statement★

시험에 나오니까 순서 외우자

SELECT

FROM

WHERE

GROUP BY

HAVING

ORDER BY

LIMIT

​

SELECT *

FROM employee

WHERE dno=2;

​

OPERATOR

AND

BETWEEN

OR

LIKE

IN

AS

​

​

Aggregatoin function

집계 함수

COUNT

SUM

AVG

MAX

MIN