CPT-103复习1

数据库部分的第一期复习

Lec 1: SQL - Tables and Data - Part 1

  1. SQL Background

    1. SQL是Structured Query Language 的缩写
    2. Sql包括数据定义语言(DDL)和数据操作语言(DML)
    3. Sql语句大小写不敏感,但对象名字大小写敏感。语句通常是大写。
    4. 结构
             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---.....
    5. 访问结构
             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)
  2. Creating Tables

    1. Syntax:
      CREATE TABLE [IF NOT EXISTS] name (
      	col-name1 datatype [col-options],
      	……
      	col-namen datatype [col-options],
      	[constraint-1],
      	……
      	[constraint-n]
      );
    2. 表名和列名可以包括空格,但是强烈不推荐,若必须添加,则需要添加(`)表示引用。
    3. 一些基础的datatypes

      1. Numerical Data Types

        1. SMALLINT
          1. Use 2 Bytes of memory
          2. range: -32768 to +32767
        2. INT or INTEGER
          1. Use 4 Bytes of memory
          2. range: -2147483648 to + 2147483647
          3. A typical choice for integer
        3. BIGINT
          1. Use 8 Bytes of memory
        4. DECIMAL(M[, D]) or NUMERIC(M[, D])
          1. 4 Bytes per 9 digits.
          2. M is the number of the digits of all. D is the digits of the decimal part
          3. The most suitable type for decimal operations
        5. FLOAT(p)
          1. Floating point number in IEEE 754 standard
          2. represents the precision in bits. (Mantissa size)
          3. 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 what p refers to
      2. String Types

        1. CHAR[(M)]
          1. A fixed-length (M is the length, 0~255) string
          2. Always right-padded with spaces to the specified length when stored.
          3. 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).
        2. VARCHAR(M)
          1. A variable-length string.
          2. The range of M is 0 to 65535
        For Mysql:
        • CHAR is faster but occupies more memory
        • VARCHAR is slower but occupies less memory
      3. Date and Time

        1. DATE
          1. The supported range is '1000-01-01' to '9999-12-31'.
          2. MySQL displays DATE values in 'YYYY-MM-DD' format.
        2. DATETIME[(fsp)]
          1. 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)]'
          2. Display format in MySQL: 'YYYY-MM-DD hh:mm:ss[.fsp]'
        3. TIMESTAMP
          1. Similar to DATETIME, but stores the time as UTC time.
          2. When the date time is looked up, the stored time will be converted to the time of the current timezone of the client.
    4. Column Options

      1. NOT NULL
        • values of this column cannot be null.
      2. UNIQUE
        • each value must be unique (similar to candidate key on a single attribute)
      3. DEFAULT value
        • Default value of this column if not specified by the user.
        • Does not work in MS Access
      4. 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
      如果表t的一个列i被添加了 NOT NULL 关键字,并且这个列的数据类型不能为NULL,那么以下操作为非法。
      INSERT INTO t VALUES();
      INSERT INTO t VALUES(DEFAULT);
      INSERT INTO t VALUES(DEFAULT(i))
    5. Table Constraints

      1. General Syntax:
        CONSTRAINT name TYPE details;
      作用是指定数据添加的条件。
  3. Tuple Manipulation

    1. INSERT

      INSERT INTO tablename (col1, col2, ...)
      	VALUES (val1, val2, ...),
      			......
      		   (val1, val2, ...);
    2. UPDATE

      UPDATE tablename
      SET col1 = val1[, col2 = val2, ...]
      [WHERE conditions];
    3. DELETE

      DELETE FROM
      	tablename
      	[WHERE conditions];