[ DB ] 05. SQL DDL

• The SQL data-definition language (DDL) allows the specification of information about relations( table structure(schema) ), including:
  • The schema for each relation
  • The type of values associated with each attribute
  • The Integrity constraints
    ➡️ rules related to data
  • The set of indices to be maintained for each relation
    ➡️ additional data structure to increase the performance( redability )
    - Security and authorization information for each relation
  • The physical storage structure of each relation on disk

---

🖥️ Data Types in SQL

String data

• CHAR(n): Fixed length character, with user-specified length n
  • Maximum length n = [0, 255]
• VARCHAR(n): Variable length character strings, with user-specified
  maximum length n
  • Maximum length n = [0, 65,535]

---

• TEXT: for strings longer than the range of VARCHAR
  • TINYTEXT : 0 – 255 bytes
  • TEXT : 0 – 65,535 bytes (64KB)
  • MEDIUMTEXT : 0 – 16,777,215 bytes (16MB)
  • LONGTEXT : 0 – 4,294,967,295 bytes (4GB)

Numeric data

• INT, INTEGER: Integer ➡️ 32-bit
• SMALLINT: Short integer ➡️ 16-bit
• BIGINT: Long integer ➡️ 64-bit
• TINYINT and MEDIUMINT are also available
  
  
• NUMERIC(p,d): Fixed point number (exact value) with user-specified precision of p digits, with d digits to the right of decimal point
  • E.g., NUMERIC(3,1) allows 44.5 to be stores exactly, but not 444.5 or 0.32)
  • In MySQL, DECIMAL is NUMERIC
• FLOAT: Floating point number (approximate) with single-precision ➡️ 32-bit
• REAL, DOUBLE: Floating point number (approximate) with double-precision ➡️ 64-bit
• DECIMAL vs. INT/FLOAT/DOUBLE
  • FLOAT and DOUBLE are faster than DECIMAL
  • DECIMAL values are exact

Temporal data

• DATE: YYYY-MM-DD
  • Rage: 1000-01-01 to 9999-12-31
  • E.g., ‘2020-03-01’ for March 1, 2020
• TIME: HH:MM:SS
  • Range: -838:59:59 to 838:59:59
  • E.g., ’14:30:03.5’ for 3.5 seconds after 2:30pm
• DATETIME: YYYY-MM-DD HH:MM:SS
  • Range: 1000-01-01 00:00:00 to 9999-12-31 23:59:59
• YEAR: YYYY
  • Range: 1901 to 2155, or 0000 (illegal year values(like null value) are converted to 0000)
• TIMESTAMP(n): Unix time (time since Jan 1, 1970) ➡️ 32-bit signed integer
  • Range: 1970-01-01 00:00:01 UTC to 2038-01-19 03:14:07 UTC
  • Depending on size n, the display pattern changes

Large objects

• BINARY(n): binary byte data type, with user-specified length n
  • Maximum length n = [0, 255]
• VARBINARY(n): binary byte data type, with user-specified maximum length n
  • Maximum length n = [0, 65,535]

---

• BLOB: Binary Large OBject data type
  • TINYBLOB : 0 – 255 bytes
  • BLOB : 0 – 65,535 bytes (65 KB)
  • MEDIUMBLOB : 0 – 16,777,215 bytes (16 MB)
  • LONGBLOB : 0 – 4,294,967,295 bytes (4 GB)

---

🖥️ CREATE

CREATE DATABASE

CREATE DATABASE database_name

• One can specify the default character encoding
  method along with this command
  • CREATE DATABASE test
    DEFAULT CHARACTER SET utf8
    COLLATE utf8_unicode_ci;
  • collation : how to compare and sort strings
• After creating a database, to use it
  • USE database_name

CREATE TABLE

CREATE TABLE table_name( Col1_name data_type[(size)], Col2_name data_type[(size)], ... );

Integrity Constraints in CREATE TABLE

• SQL prevents any update to the database that violates an integrity constraint
  • Integrity constraints allow us to specify what data makes sense for us
• Types of integrity constraints
  • Primary key: PRIMARY KEY (A1, ..., An )
  • Foreign key: FOREIGN KEY (Am, ..., An ) REFERENCES r
  • Unique key: UNIQUE (A1, ..., An )
  • Not null: NOT NULL ➡️ NULL default
  • Value constraints: CHECK (constraint), DEFAULT
    
    
• An attribute or list of attributes may be declared as PRIMARY KEY or UNIQUE
  • Meaning: no two tuples of the relation may agree in all the attribute(s) on the list
  • That is, the attribute(s) do(es) not allow duplicates in values
  • PRIMARY KEY/UNIQUE can be used as an identifier for each row ( like ID )
• PRIMARY KEY vs. UNIQUE
  Ex_ INSERT INTO neighbors (name)
  VALUES (‘Elmo’); ➡️ rejected➡️ release_date can modify to null value
  

🖥️ DROP

DROP TABLE r
: Used to remove elements from a database, such as tables
cf. TRUNCATE (TABLE) r is used to delete the data inside a table, but not the table itself

Ex_
DROP TABLE time_slot_backup;

• Drop a database
  • DROP DATABASE university;

🖥️ ALTER

ALTER TABLE r ADD A
ALTER TABLE r MODIFY A
ALTER TABLE r DROP A
: Used to make changes to the table schema

Ex_
ALTER TABLE time_slot_backup ADD remark VARCHAR(20);
ALTER TABLE time_slot_backup MODIFY remark CHAR(20);
ALTER TABLE time_slot_backup DROP remark;

• Drop a column
  • ALTER TABLE instructor
    DROP (COLUMN) salary;
• DROP a PRIMARY KEY Constraint
  • ALTER TABLE instructor
    DROP PRIMARY KEY;
• DROP a FOREIGN KEY Constraint
  • ALTER TABLE instructor
    DROP FOREIGN KEY instructor_ibfk_1;
• DROP DEFAULT
  • ALTER TABLE student
    ALTER tot_cred DROP DEFAULT;

HGU 전산전자공학부 홍참길 교수님의 23-1 Database System 수업을 듣고 작성한 포스트이며, 첨부한 모든 사진은 교수님 수업 PPT의 사진 원본에 필기를 한 수정본입니다.