데이터 정의어(DDL)
데이터 조작어(DML)
데이터 제어어(DCL)
데이터 정의어는 테이블이나 관계의 구조를 생성하는데 사용하며 CREATE, ALTER(테이블 속성 변경 혹은 수정..), DROP문 등이 있다. 데이터 조작어는 테이블에 데이터를 검색, 삽입, 수정, 삭제하는데 사용하며 SELECT, INSERT, DELETE, UPDATE문 등이 있다. 여기서 SELECT 문은 특별히 Query문(질의어)라고도 함. 데이터 제어어는 데이터의 사용 권한을 관리하는데 사용하며, GRANT, REVOKE문 등이 있다. SQL문의 내부적 실행순서는 아래와 같음.
CREATE DATABASE chap3;
create table Students
(sid char(20),
name varchar(20),
login varchar(20),
age integer,
gpa real,
unique (name, age),
constraint StudentsKey primary key (sid) )
default character set = euckr;
👨💻 Students Table에 INSERT & SELECT
insert into Students(sid, name, login, age, gpa) Values (50000, 'Dave', 'dave@cs', 19, 3.2);
insert into Students(sid, name, login, age, gpa) Values (53668, 'Smith', 'smith@ee', 18, 3.2);
insert into Students(sid, name, login, age, gpa) Values ('53666', 'Jones', 'jones@cs', 18, 3.3);
insert into Students Values (53650, 'Smith', 'smith@math', 19, 3.7);
insert into Students Values (53831, 'Madayan', 'madayan@music', 11, 1.8);
insert into Students Values (53832, 'Guldu', 'Guldu@music', 12, 2.0);
create table Courses
(cid char(20),
cname varchar(30),
credits integer,
constraint CoursesKey primary key (cid) )
default character set = euckr;
insert into Courses values ('Carnatic101', 'Intro Carnatic', 3);
insert into Courses values ('Reggae203', 'Understanding Reggae', 2);
insert into Courses values ('History105', 'Intro History', 3);
insert into Courses values ('Topology112', 'Intro Topology', 3);
select * from Courses;
create table Enrolled
(studid char(20),
cid char(20),
grade char(2),
primary key (studid, cid),
foreign key (studid) references Students (sid)
on delete cascade
on update cascade,
foreign key (cid) references Courses (cid)
on delete cascade
on update cascade)
default character set = euckr;
desc enrolled;
insert into enrolled values (53650, 'Topology112', 'A');
insert into enrolled values (53666, 'History105', 'B');
insert into enrolled values (53831, 'Carnatic101', 'C');
insert into enrolled values (53831, 'Reggae203', 'B');
select * from enrolled
delete from students where sid = '53666';
select from students;
select from enrolled;
1175 error가 난다면, 안전모드를 꺼주면 되므로
SET SQL_SAFE_UPDATES = 0;
위 코드를 입력하면 해결됨.
update Students set sid = 6000 where sid = 53831;
select from students;
select from enrolled;
select from students where age <= 13;
select name, age from students where sid = 53832;
select from Students Where name = 'Smith';
select S.name, E.cid
from Students S, Enrolled E
where S.sid=E.studid And E.grade = 'A';
select S.name, C.cname, C.credits, E.grade
from Students S, Enrolled E, Courses C
Where S.sid = E.studid and E.cid = C.cid and S.name = 'Madayan';