TITLE

Size: px

Start display at page:

Download "TITLE"

정석 구
7 years ago
Views:

1 CSED421 Database Systems Lab MySQL Basic Syntax

2 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

3 Data Definition Statements Relation 을생성 / 수정 / 제거하는명령어 CREATE TABLE, ALTER TABLE DROP TABLE, RENAME TABLE 등.. CREATE TABLE ); CREATE TABLE Persons( Name VARCHAR(10), Age INTEGER Page 3

4 Data Definition Statements DROP TABLE DROP TABLE table_name; RENAME TABLE ALTER RENAME TABLE table_name TO new_table_name; ALTER TABLE Persons ADD Address VARCHAR(60); ALTER TABLE table_name DROP COLUMN column_name; ALTER TABLE table_name MODIFY column_name data_type; Page 4

5 Naming Rules Table name 과 Column name 기본적으로 A-Z, a-z, 0-9, _(under score), $ 를사용 Reserved words를사용할경우 `로묶음 SELECT `select` FROM `from`; Page 5

6 Case Sensitivity Database name 과 Table name 은대소문자구분을할수도있음 Depends on file system (NTFS, EXT4, HFS+) Depends on OS (Windows: 구분하지않음, Unix: 구분 ) Column name 은대소문자구분을하지않음 Page 6

7 Comment /* block comment */ # inline comment -- inline comment 주의 : -- 뒤에는반드시공백이나탭문자가하나이상필요 Page 7

8 Data Type INT ~ or 0 ~ (32 bit) BIGINT 는 64 bit FLOAT, DOUBLE FLOAT(M,D) M은총자리수, D는소수점자리수 (M >= D) FLOAT(7,4) ~ insert > stored in DATETIME :00:00 ~ :59:59 TIMESTAMP :00:01 ~ :14:07 Page 8

9 Data Type CHAR(L) 고정길이문자열. L 의최대값은 255 (characters) VARCHAR(L) 길이 L 이하의가변길이문자열. 최대 64 KB BLOB, TEXT 각각 Binary/Text 자료를저장. 최대 64 KB LONGBLOB, LONGTEXT 는최대 4 GB Page 9

10 NULL data NULL NULL means no data or unknown. Are these TRUE, FALSE or some other things? NULL = 0 NULL <> 1 NULL = NULL NULL IS NULL 0 IS NOT NULL Page 10

11 Data Manipulation Statements INSERT INSERT INTO Persons (Name, Age) VALUES ('John, 38); DELETE DELETE FROM Persons WHERE Age > 120; DELETE FROM Persons; -- DELETE SELECT * FROM Persons WHERE Age > 120; ( 삭제하기전에삭제대상확인 ) Page 11

12 Data Manipulation Statements UPDATE UPDATE Persons SET Age = 12 WHERE Name = 'Penny'; UPDATE Persons SET Age = 12; SELECT SELECT "Hello, World!"; SELECT * 2; SELECT * FROM Persons; SELECT Name FROM Persons WHERE age < 14; Page 12

13 SELECT/INSERT/UPDATE/DELETE 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 ]

14 SELECT/INSERT/UPDATE/DELETE 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'

15 Database Table 예제테이블, Persons P_Id LastName FirstName Address City 1 Hansen Ola Timoteivn 10 Sandnes 2 Svendson Tove Borgvn 23 Sandnes 3 Pettersen Kari Storgt 20 Stavanger Use Hemos ID Create Table Insert tuples

16 Create Table CREATE TABLE Persons ( P_Id INT, LastName VARCHAR(20), FirstName VARCHAR(20), Address VARCHAR(30), City VARCHAR(10) );

17 Insert Tuples INSERT INTO Persons VALUES (1, 'Hansen', 'Ola', 'Timoteivn 10', 'Sandnes'); INSERT INTO Persons VALUES (2, 'Svendson', 'Tove', 'Borgvn 23', 'Sandnes'); INSERT INTO Persons VALUES (3, 'Pattersen', 'Kari', 'Storgt 20', 'Stavanger');

18 SELECT Statement Syntax SELECT column_name(s) -- (2) FROM table_name -- (1) Persons 테이블의모든레코드의 LastName 과 FirstName column 출력 sql> SELECT LastName, FirstName FROM Persons; Persons 테이블의모든레코드를출력 sql> SELECT * FROM Persons; LastName Hansen Svendson Pettersen FirstName Ola Tove Kari P_Id LastName FirstName Address City 1 Hansen Ola Timoteivn 10 Sandnes 2 Svendson Tove Borgvn 23 Sandnes 3 Pettersen Kari Storgt 20 Stavanger

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

20 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_Id LastName FirstName Address City 1 Hansen Ola Timoteivn 10 Sandnes 2 Svendson Tove Borgvn 23 Sandnes

21 WHERE Clause WHERE 절에사용되는연산자들 연산자 설명 = Equal <> Not equal (!=) > Greater than < Less than >= Greater than or equal <= Less than or equal BETWEEN Between an inclusive range LIKE Search for a pattern IN If you know the exact value you want to return for at least one of the columns 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 );

22 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_Id LastName FirstName Address City 2 Svendson Tove Borgvn 23 Sandes P_Id LastName FirstName Address City 1 Hansen Ola Timoteivn 10 Sandes 2 Svendson Tove Borgvn 23 Sandes P_Id LastName FirstName Address City 2 Svendson Tove Borgvn 23 Sandes

23 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;

24 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

25 SQL Functions NOW(), SYSDATE(), UNIX_TIMESTAMP() System 의시간과날짜를 Return STR_TO_DATE(str) 문자열로된시간값을 DATETIME 타입으로 return STR_TO_DATE(' :23:44', '%Y-%m-%d %h:%i:%s') STR_TO_DATE(' ','%Y-%m-%d') RAND() [0, 1) 사이의임의의수 return Page 25

26 SQL Functions COUNT Tuple 의개수를구한다. SUM SELECT COUNT(*) AS count FROM Persons SELECT COUNT(Age) AS count FROM Persons What if some Ages are NULL? Column 값의합계를구한다. 응용 SELECT SUM(column_name) FROM table_name SELECT SUM(Age)/COUNT(Age) AS AverageAge FROM Persons Page 26

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

28 Example 1 예제테이블, Students StudentId LastName FirstName Major 1 Sunghwan Kim CSE 2 Jonghwan Mun CSE 3 Yoojin Park CSE 4 자기이름 자기이름 자기학과 다음과같은테이블을만들고모든정보를보이게출력

29 Example 2 예제테이블, Students StudentId LastName FirstName Major 2 Jonghwan Mun CSE 3 Yoojin Park CSE 4 자기이름 자기이름 자기학과 앞의테이블에서 Kim 씨를지우고모든정보출력

30 Example 3 예제테이블, Students StudentId LastName FirstName Major 2 Jonghwan Mun Computer 3 Yoojin Park Computer 4 자기이름 자기이름 자기학과 앞의테이블에서 MAJOR 의 CSE 를 Computer 로변경하고모든정보출력

31 Practice 1. 다음 Schema 를갖는 Assignment Table 을생성하라. name VARCHAR(20) score INTEGER penalty INTEGER submit_date VARCHAR(10) 2. submit_date 의 Data Type 을 DATETIME 으로변경하라. Page 31

32 Practice 3. 다음의자료를입력하라. 이름 점수 감점 제출일 Name score penalty submit_date Ron Davis David Liquid Page 32

33 Practice 년또는이전에제출된데이터를삭제하라 5. Liquid 의감점을 0 으로수정하라. 6. 평균점수를출력하라 7. 이름과총점 ( 점수 감점 ) 을함께출력하라 Page 33

34 Submission Exercise 문제를해결한 SQL script 를제출 제출장소 : 게시판 하나의파일로제출하되주석을통해풀이한문제를구분할것.

PowerPoint 프레젠테이션

PowerPoint 프레젠테이션 MySQL - 명령어 1. 데이터베이스관련명령 2. 데이터베이스테이블관련명령 3. SQL 명령의일괄실행 4. 레코드관련명령 5. 데이터베이스백업및복원명령 1. 데이터베이스관련명령 데이터베이스접속명령 데이터베이스접속명령 mysql -u계정 -p비밀번호데이터베이스명 C: > mysql -ukdhong p1234 kdhong_db 데이터베이스생성명령 데이터베이스생성명령