LAB: Web-scale Data Management on a Cloud Lab 1. Introduction 2011/03/04

환영합니다 실습 수업 – 청암학술정보관 501 호, 금요일 15:30 – 17:10 조교 – 이상훈, 김윤구 –{sanghoon, 오피스 – 제 2 공학관 306 호 (IDS 연구실 ) – 전화 : – 시간 : 금요일 13:00 – 15:00 실습 수업 홈페이지 –

Grading Policy Midterm(25%)+Final(25%) 4 Homework (15%) Participation (5%) Labs/Projects (30%) –Labs: User perspective (Commercial DBMS, e.g., Oracle) –Projects: Internals? Clouds?

실습 일정 1.Introduction 2.Basic SQL 3.Constraints 4.Group Functions 5.Join 6.Nested Queries 7.Indexes 8.Views 9.Triggers 10.User-defined Functions (tentative…)

실습 환경 서버 –lightpurple.postech.ac.kr ( ) –Intel(R) Pentium(R) 4 CPU 3.20GHz –1GB Memory –Linux version (Debian lenny2) –MySQL a-24+lenny5 (Debian) 클라이언트 – 아무거나, 편한 툴 (GUI 툴 강추 )

로그인 리눅스 서버에 접속 –lightpurple.postech.ac.kr (ssh, port 22) ( ) –username : stu –password : csed421 MySQL 에 접속 –username/password : your hemos ID $ mysql –u –p Enter password:

GUI Tools MySQL GUI Tools –

GUI Tools MySQL Workbench (GUI Tool) –

GUI Tools SQLyog MySQL GUI –

What is SQL? Structured Query Language 데이터베이스에 접근하고 관리하기 위한 ANSI (American National Standards Institute) 표준 언어 RDBMS –Relational Database Management System –SQL –Table Column (attribute, field) Row (entity, record) –E.g., MS SQL Server, IBM DB2, Oracle, MySQL, Cubrid, Tibero 참고 :

SQL DML & DDL Data Manipulation Language –SELECT –UPDATE –DELETE –INSERT INTO Data Definition Language –CREATE DATABASE –ALTER DATABASE –CREATE TABLE –ALTER TABLE –DROP TABLE –CREATE INDEX –DROP INDEX

Database Table 예제 테이블, “Persons” P_IdLastNameFirstNameAddressCity 1HansenOlaTimoteivn 10Sandnes 2SvendsonToveBorgvn 23Sandnes 3PettersenKariStorgt 20Stavanger

SELECT Statement Syntax –SELECT column_name(s) -- (2) FROM table_name -- (1) Persons 테이블의 모든 레코드의 LastName 과 FirstName 컬럼 출력 sql> SELECT LastName, FirstName FROM Persons; Persons 테이블의 모든 레코드를 출력 sql> SELECT * FROM Persons; P_IdLastNameFirstNameAddressCity 1HansenOlaTimoteivn 10Sandnes 2SvendsonToveBorgvn 23Sandnes 3PettersenKariStorgt 20Stavanger LastNameFirstName HansenOla SvendsonTove PettersenKari

SELECT DISTINCT Statement Syntax –SELECT DISTINCT column_name(s) FROM table_name Persons 테이블에서 서로 다른 City 값들만 출력 sql> SELECT DISTINCT City FROM Persons; City Sandes Stavanger

WHERE Clause Syntax –SELECT column_name(s) -- (3) FROM table_name -- (1) WHERE condition -- (2) City 가 ‘Sandnes’ 인 사람 찾기 sql> SELECT * FROM Persons WHERE City = ‘Sandnes’; 텍스트 값에 대해, –SELECT * FROM Persons WHERE FirstName = ‘Tove’; (O) –SELECT * FROM Persons WHERE FirstName = Tove; (X) 숫자 값에 대해, –SELECT * FROM Persons WHERE Year = 1965; (O) –SELECT * FROM Persons WHERE Year = ‘1965’; (X) P_IdLastNameFirstNameAddressCity 1HansenOlaTimoteivn 10Sandnes 2SvendsonToveBorgvn 23Sandnes

WHERE Clause WHERE 절에 사용되는 연산자들 –SELECT * FROM Persons WHERE P_Id BETWEEN 2 AND 3; –SELECT * FROM Persons WHERE FirstName LIKE ‘T%e’; –SELECT * FROM Persons WHERE FirstName IN (‘Tove’, ‘Ola’); 연산자설명 =Equal <>Not equal (!=) >Greater than <Less than >=Greater than or equal <=Less than or equal BETWEENBetween an inclusive range LIKESearch for a pattern INIf you know the exact value you want to return for at least one of the columns

AND & OR operators FirstName 이 ‘Tove’ 이고 LastName 이 ‘Svendson’ 인 사람 찾기 sql> SELECT * FROM Persons WHERE FirstName = 'Tove’ AND LastName = 'Svendson‘; FirstName 이 ‘Tove’ 이거나 ‘Ola’ 인 사람 찾기 sql> SELECT * FROM Persons WHERE FirstName = 'Tove’ OR FirstName = 'Ola‘; LastName 이 Svendson 이면서, FirstName 이 ‘Tove’ 이거나 ‘Ola’ 인 사 람 찾기 sql> SELECT * FROM Persons WHERE LastName = 'Svendson’ AND (FirstName = 'Tove' OR FirstName = 'Ola‘); P_IdLastNameFirstNameAddressCity 2SvendsonToveBorgvn 23Sandes P_IdLastNameFirstNameAddressCity 1HansenOlaTimoteivn 10Sandes 2SvendsonToveBorgvn 23Sandes P_IdLastNameFirstNameAddressCity 2SvendsonToveBorgvn 23Sandes

ORDER BY Keyword 결과 집합의 레코드들을 특정 컬럼으로 정렬 Syntax –SELECT column_name(s) FROM table_name ORDER BY column_name(s) [ ASC | DESC ] LastName 에 대해 오름차순으로 정렬 후 모두 출력 sql> SELECT * FROM Persons ORDER BY LastName; LastName 에 대해 내름차순으로 정렬 후 모두 출력 sql> SELECT * FROM Persons ORDER BY LastName DESC;

Alias Syntax – 테이블 이름 재지정 –SELECT [table_alias_name.]column_name(s) FROM table_name [ [AS] table_alias_name] – 컬럼 이름 재지정 –SELECT column_name(s) [ [AS] col_alias_name] FROM table_name 모든 사람의 FirstName 을 출력하되, 컬럼 이름을 name 으로 지정 sql> SELECT p.FirstName AS name FROM Persons AS p; name Ola Tove Kari

SELECT/INSERT/UPDATE/DELET E SELECT Syntax –SELECT [DISTINCT] column_name(s) FROM table_name [ WHERE column_name operator value ] [ ORDER BY column_name(s) [ ASC | DESC ] ] INSERT Syntax –INSERT INTO table_name [ (column1, column2, column3, …) ] VALUES (value1, value2, value3, …) UPDATE Syntax –UPDATE table_name SET column1 = value, column2 = value2, … [ WHERE some_column = some_value ] DELETE Syntax –DELETE FROM table_name [ WHERE some_column = some_value ]

SELECT/INSERT/UPDATE/DELET E SELECT Syntax sql> SELECT FirstName, LastName FROM Persons WHERE FirstName='Tove’ ORDER BY LastName DESC INSERT Syntax sql> INSERT INTO Persons (P_Id, LastName, FirstName) VALUES (5, 'Tjessem', 'Jakob‘) UPDATE Syntax sql> UPDATE Persons SET Address = 'Nissestien 67', City = 'Sandnes’ WHERE LastName = 'Tjessem' AND FirstName = 'Jakob‘; DELETE Syntax sql> DELETE FROM Persons WHERE LastName = 'Tjessem' AND FirstName = 'Jakob'

MySQL 만을 위한 명령어 사용 가능한 데이터베이스 목록 출력 mysql> SHOW DATABASES; lab 데이터베이스를 기본 데이터베이스로 지정 mysql> USE lab1; 기본으로 지정된 데이터베이스의 테이블 목록 출력 mysql> SHOW TABLES; Persons 테이블의 정보 출력 mysql> DESCRIBE lab1.Persons; 비빌번호 바꾸기 mysql> SET PASSWORD = PASSWORD(‘newpassword’);