CPT-103复习1
数据库部分的第一期复习
Lec 1: SQL - Tables and Data - Part 1
SQL Background
- SQL是Structured Query Language 的缩写
- Sql包括数据定义语言(DDL)和数据操作语言(DML)
- Sql语句大小写不敏感,但对象名字大小写敏感。语句通常是大写。
- 结构
flowchart A(Computer)---cluster1 A---cluster2 A---B(...) cluster1---catalog1 cluster1---catalog2 cluster1---catalog3 cluster2---catalog4 cluster2---catalog5 cluster2---catalog6 B---C(...) catalog1---schema1 catalog1---schema2 catalog2---schema3 catalog3---schema4 catalog3---schema5 catalog3---schema6 catalog4---schema7 catalog5---... catalog6---.... C---.....
- 访问结构
flowchart Application1---A(Application must know the DBMS Interface) Application2---A Application3---A A---B(Database Management System\n'DBMS') B---C(DB data files)
Creating Tables
- Syntax:
CREATE TABLE [IF NOT EXISTS] name ( col-name1 datatype [col-options], …… col-namen datatype [col-options], [constraint-1], …… [constraint-n] );
- 表名和列名可以包括空格,但是强烈不推荐,若必须添加,则需要添加(`)表示引用。
一些基础的datatypes
Numerical Data Types
SMALLINT
- Use 2 Bytes of memory
- range: -32768 to +32767
INT
orINTEGER
- Use 4 Bytes of memory
- range: -2147483648 to + 2147483647
- A typical choice for integer
BIGINT
- Use 8 Bytes of memory
DECIMAL
(M[, D]) orNUMERIC
(M[, D])- 4 Bytes per 9 digits.
- M is the number of the digits of all. D is the digits of the decimal part
- The most suitable type for decimal operations
FLOAT
(p)- Floating point number in IEEE 754 standard
- represents the precision in bits. (Mantissa size)
- MySQL will automatically choose single precision or double precision based on the value of
p
The total number of bits is 32 for float and 64 for double
Mantissa is whatp
refers to
String Types
CHAR
[(M)]- A fixed-length (M is the length, 0~255) string
- Always right-padded with spaces to the specified length when stored.
- E.g. if you store 'A' to CHAR(5), it will actually be 'A ' inside the memory. But when you retrieve the value, the trailing spaces will be removed automatically (this behavior can be turned on or off).
VARCHAR
(M)- A variable-length string.
- The range of M is 0 to 65535
CHAR
is faster but occupies more memoryVARCHAR
is slower but occupies less memory
Date and Time
DATE
- The supported range is '1000-01-01' to '9999-12-31'.
- MySQL displays DATE values in 'YYYY-MM-DD' format.
DATETIME
[(fsp)]- The supported range is '1000-01-01 00:00:00[.00000000(digit number is fsp)]' to '9999-12-31 23:59:59[.99999999(digit number is fsp)]'
- Display format in MySQL: 'YYYY-MM-DD hh:mm:ss[.fsp]'
TIMESTAMP
- Similar to DATETIME, but stores the time as UTC time.
- When the date time is looked up, the stored time will be converted to the time of the current timezone of the client.
Column Options
- NOT NULL
- values of this column cannot be null.
- UNIQUE
- each value must be unique (similar to candidate key on a single attribute)
- DEFAULT value
- Default value of this column if not specified by the user.
- Does not work in MS Access
- AUTO_INCREMENT[ = baseValue]
- Muse be applied to a key column (primary key, unique key)
- a value (usually max(col) + 1) is automatically inserted when data is added
- you can also manually provide values to override this behavior
INSERT INTO t VALUES(); INSERT INTO t VALUES(DEFAULT); INSERT INTO t VALUES(DEFAULT(i))
- NOT NULL
Table Constraints
- General Syntax:
CONSTRAINT name TYPE details;
- General Syntax:
- Syntax:
Tuple Manipulation
INSERT
INSERT INTO tablename (col1, col2, ...) VALUES (val1, val2, ...), ...... (val1, val2, ...);
UPDATE
UPDATE tablename SET col1 = val1[, col2 = val2, ...] [WHERE conditions];
DELETE
DELETE FROM tablename [WHERE conditions];