CSED421 Database Systems Lab MySQL Basic Syntax
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
Data Definition Statements Relation 을생성 / 수정 / 제거하는명령어 CREATE TABLE, ALTER TABLE DROP TABLE, RENAME TABLE 등.. CREATE TABLE ); CREATE TABLE Persons( Name VARCHAR(10), Age INTEGER Page 3
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
Naming Rules Table name 과 Column name 기본적으로 A-Z, a-z, 0-9, _(under score), $ 를사용 Reserved words를사용할경우 `로묶음 SELECT `select` FROM `from`; Page 5
Case Sensitivity Database name 과 Table name 은대소문자구분을할수도있음 Depends on file system (NTFS, EXT4, HFS+) Depends on OS (Windows: 구분하지않음, Unix: 구분 ) Column name 은대소문자구분을하지않음 Page 6
Comment /* block comment */ # inline comment -- inline comment 주의 : -- 뒤에는반드시공백이나탭문자가하나이상필요 Page 7
Data Type INT -2147483648 ~ 2147483647 or 0 ~ 4294967295 (32 bit) BIGINT 는 64 bit FLOAT, DOUBLE FLOAT(M,D) M은총자리수, D는소수점자리수 (M >= D) FLOAT(7,4) -999.9999 ~ 999.9999 insert 999.00009 -> stored in 999.0001 DATETIME 1000-01-00 00:00:00 ~ 9999-12-31 23:59:59 TIMESTAMP 1970-01-01 00:00:01 ~ 2038-01-19 03:14:07 Page 8
Data Type CHAR(L) 고정길이문자열. L 의최대값은 255 (characters) VARCHAR(L) 길이 L 이하의가변길이문자열. 최대 64 KB BLOB, TEXT 각각 Binary/Text 자료를저장. 최대 64 KB LONGBLOB, LONGTEXT 는최대 4 GB Page 9
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
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
Data Manipulation Statements UPDATE UPDATE Persons SET Age = 12 WHERE Name = 'Penny'; UPDATE Persons SET Age = 12; SELECT SELECT "Hello, World!"; SELECT 3 + 4 * 2; SELECT * FROM Persons; SELECT Name FROM Persons WHERE age < 14; Page 12
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 ]
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'
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
Create Table CREATE TABLE Persons ( P_Id INT, LastName VARCHAR(20), FirstName VARCHAR(20), Address VARCHAR(30), City VARCHAR(10) );
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');
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
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_Id LastName FirstName Address City 1 Hansen Ola Timoteivn 10 Sandnes 2 Svendson Tove Borgvn 23 Sandnes
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 );
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
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
SQL Functions NOW(), SYSDATE(), UNIX_TIMESTAMP() System 의시간과날짜를 Return STR_TO_DATE(str) 문자열로된시간값을 DATETIME 타입으로 return STR_TO_DATE('2011-03-11 11:23:44', '%Y-%m-%d %h:%i:%s') STR_TO_DATE('2011-03-11','%Y-%m-%d') RAND() [0, 1) 사이의임의의수 return Page 25
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
MySQL 만을위한명령어 사용가능한데이터베이스목록출력 mysql> SHOW DATABASES; lab 데이터베이스를기본데이터베이스로지정 mysql> USE lab1; 기본으로지정된데이터베이스의테이블목록출력 mysql> SHOW TABLES; Persons 테이블의정보출력 mysql> DESCRIBE lab1.persons; 비빌번호바꾸기 mysql> SET PASSWORD = PASSWORD( newpassword );
Example 1 예제테이블, Students StudentId LastName FirstName Major 1 Sunghwan Kim CSE 2 Jonghwan Mun CSE 3 Yoojin Park CSE 4 자기이름 자기이름 자기학과 다음과같은테이블을만들고모든정보를보이게출력
Example 2 예제테이블, Students StudentId LastName FirstName Major 2 Jonghwan Mun CSE 3 Yoojin Park CSE 4 자기이름 자기이름 자기학과 앞의테이블에서 Kim 씨를지우고모든정보출력
Example 3 예제테이블, Students StudentId LastName FirstName Major 2 Jonghwan Mun Computer 3 Yoojin Park Computer 4 자기이름 자기이름 자기학과 앞의테이블에서 MAJOR 의 CSE 를 Computer 로변경하고모든정보출력
Practice 1. 다음 Schema 를갖는 Assignment Table 을생성하라. name VARCHAR(20) score INTEGER penalty INTEGER submit_date VARCHAR(10) 2. submit_date 의 Data Type 을 DATETIME 으로변경하라. Page 31
Practice 3. 다음의자료를입력하라. 이름 점수 감점 제출일 Name score penalty submit_date Ron 80 0 2012-09-22 Davis 50-5 2014-10-09 David 90-10 2014-10-20 Liquid 0-100 2015-01-01 Page 32
Practice 4. 2013 년또는이전에제출된데이터를삭제하라 5. Liquid 의감점을 0 으로수정하라. 6. 평균점수를출력하라 7. 이름과총점 ( 점수 감점 ) 을함께출력하라 Page 33
Submission Exercise 문제를해결한 SQL script 를제출 제출장소 : 게시판 하나의파일로제출하되주석을통해풀이한문제를구분할것.