103 - Lecture 2 Table and Data Part 1

SQL - Tables and Data Part 1

Relational Database Management System(RDBMS)

关系型数据库管理系统（RDBMS）是基于关系模型的数据库系统，它支持多种关系操作。关系模型是一种数据存储和检索的模型，它使用表格来组织数据，并通过关系操作（如选择、投影、连接等）来处理数据。RDBMS是当前最流行的数据库管理系统类型，被广泛应用于企业级应用、网站、数据仓库等领域。在原文中提到了一些知名的RDBMS软件，包括Oracle、DB2、MySQL和MariaDB、Ingres、PostgreSQL、Microsoft SQL Server和MS Access。

关键词解析:

1. 关系型数据库（Relational Database）：一种使用表格来存储数据的数据库，数据以行和列的形式组织，每一行代表一条记录，每一列代表一个字段。
2. 关系模型（Relational Model）：由E.F. Codd在1970年提出，是一种用于数据库管理的理论模型，它规定了数据如何以表格形式存储，以及如何通过关系操作来访问和处理这些数据。
3. 关系操作（Relational Operations）：在关系模型中定义的一系列操作，如选择（Select）、投影（Project）、连接（Join）等，用于从数据库中检索和修改数据。

RDBMS and Related Software

关系型数据库管理系统（RDBMS）中的 服务器-客户端 架构：

服务器应用：DBMS 是一个服务器端的应用程序（server application)，通常运行在服务器(server)上，用于管理和维护数据库(DB)。服务器负责接收、处理和响应来自客户端的请求。

无用户界面：大多数 DBMS 自身不提供图形化用户界面，而是通过命令行接口或其他客户端工具来与其交互。

客户端应用程序与服务器应用程序进行通信：

•	客户端应用：客户端应用程序负责与 DBMS 进行交互。这些应用程序可以通过网络连接到数据库服务器，发送查询、插入、删除、更新等操作请求，并从服务器接收数据。
•	管理 DBMS 的客户端工具：有些客户端应用程序专门用于管理 DBMS。例如：
•	DBeaver
•	MySQL Workbench
•	PhpMyAdmin

这些工具提供图形化界面，帮助用户管理数据库、编写 SQL 查询、查看表和数据等。

客户端与用户的交互：用户 通过客户端应用（client application)来访问和使用数据库系统中的数据和服务。

RDBMS 是 DBMS 的一种类型，两者都是用于管理数据的软件系统。

•	RDBMS 专注于关系型数据库，是现代数据库系统的主流形式，而 DBMS 是更广泛的概念，包含了所有类型的数据库管理系统。

Structured Query Language(SQL)

数据库拥有自己的一套“编程语言”，叫做结构化查询语言（SQL）。SQL包含三个重要部分：数据定义语言（DDL）、数据操纵语言（DML）和数据控制语言（DCL）。

1. DDL (数据定义语言)：DDL（Data Definition Language）是用于创建、修改或删除数据库结构的SQL语句集合。(允许用户定义specify存储在数据库中的数据类型、结构以及数据的约束constraint条件)
   • CREATE：创建新的数据库或表。
   • ALTER：修改现有的表结构。
   • DROP：删除表或数据库。

2. DML (数据操纵语言)：DML（Data Manipulation Language）是用于操作数据库中的数据的语言。它是数据库操作的核心部分。
   • INSERT：向表中插入数据。
   • UPDATE：更新现有数据。
   • DELETE：删除数据。
   • SELECT：从表中查询数据。

3. DCL：数据控制语言（Data Control Language），用于设置数据库访问权限(rights)和管理用户权限的语言部分。

   • GRANT：授予用户权限。
   • REVOKE：撤销用户权限。

数据定义语言(DDL)和数据操纵语言(DML)。数据定义语言用于定义或修改数据库结构，而数据操纵语言则用于对数据库中的数据进行操作。

XAMPP

XAMPP is a collection of software, including MySQL and PhpMyAdmin.

SQL Format

SQL语句(statements)应该用粗体的等宽字体（Courier字体）来书写

SQL关键字(keywords)不区分大小写(case-insensitive)

表名和列名等可以设置为区分大小写，这在某些数据库系统中是可配置(configured to)的。

SQL语句应该以分号(semi-colons)结束。

添加注释，使用两个破折号(dashes)后跟一个空格(space)来注释掉(comment out)一行代码。

Creating a Schema

创建一个数据库模式（Schema）是数据库管理的一个重要步骤。

创建模式的命令有两种，一种是使用CREATE SCHEMA，另一种是使用CREATE DATABASE。
schema（模式）：在数据库中，一个schema包含了多个数据库组件，比如表（关系）、视图、域、断言、排序规则、翻译和字符集等。这些组件通常拥有相同的创建者或所有者。
创建数据库

CREATE DATABASE [IF NOT EXISTS] 'database_name';

删除数据库

DROP DATABASE [IF EXISTS] 'database_name';

如果表名或者字段名是特殊字符，用‘ ’

查看数据库

SHOW DATABASES

你可以创建一个模式，只允许IT部门访问，同时也可以创建另一个只允许市场部门访问的模式。不同部门的员工无法看到其他部门模式中的内容。本模块不涵盖如何具体实施这一过程。

一. Creating Tables

创建一个表时，必须定义每个字段的名称和数据类型。数据类型决定了该字段可以存储的数据的类型（如整数、字符串等）。

CREATE TABLE table_name (

column1 int,

column2 varchar(40),
……
）；

常见的数据类型包括：

• 整数类型：INT、SMALLINT、BIGINT 用于存储整数。

• 浮点数类型：FLOAT、DOUBLE 用于存储带小数的数字。

• 字符串类型：CHAR（固定长度的字符）和 VARCHAR（可变长度的字符）。

• 日期和时间类型：DATE、TIME、TIMESTAMP 用于存储日期和时间信息。

Conversion units:

• 1 bit = smallest unit of data.

• 1 byte = 8 bits.

• 1 KB (kilobyte) = 1024 bytes.

Keyword USE

Syntax

USE schema_name 

使用数据库

如果表名或者字段名是特殊字符，用`

it forces all subsequent SQL commands to be executed the schema you specified

Inserting Values

INSERT INTO my_table VALUS

(1,'ABC'),(2,'def)



INSERT INTO tablename VALUES (val1,val2……）；

The order of "val1,val2……“must match the order of columns of the table

字符串使用单引号括起来

Avoid creating or modifying tables in the following schemas:

• information_schema

• mysql

• performance_schema

• sys

• 这些 schema 是自动创建的，用于数据库管理。

• They are automatically created for database administration purposes.

说明：

这些 schema 包含数据库的元数据和系统信息，修改它们可能会影响数据库的正常运行。因此，通常只在用户自定义的 schema（如 test 或其他创建的数据库）中进行表的创建和修改。

Creating Tables

CREATE TABLE [IF NOT EXISTS] 表名（

字段名` 列类型[属性][索引][注释],

字段名` 列类型[属性][索引][注释],

字段名` 列类型[属性][索引][注释]

）[表类型][表的字符集设置][注释]

Table and Column Names

In SQL, table or column names can have spaces, but it is strongly not recommended.
If you insist to do so, you must enclose the name with a pair of ` symbols.

CREATE TABLE 'my table '(

	column A INT,
	column B CHAR(11)

);

NULL:不要用NULL进行运算，结果还是NULL

Fixed point

数值数据类型（定点）

• DECIMAL[(M[,D])] 或 NUMERIC[(M[,D])]：

• 固定点数数据类型，用于表示带有固定小数位的数值。

• DECIMAL[(M[,D])] or NUMERIC[(M[,D])]:

CREATE TABLE `staff` (

name` VARCHAR(12),

staff_id` INT(11),

salary` DECIMAL(5,2)

);

• salary 定义为 DECIMAL(5,2) 表示最多 5 位数字，其中包含 2 位小数。

Float

数值数据类型（浮点）

• FLOAT§：

1. 符合 IEEE 754 标准的浮点数，p 表示精度（尾数的位数）。

Floating point number according to IEEE 754 standard, p represents precision (number of bits for the mantissa).

2. MySQL 会根据 p 的值自动选择单精度或双精度( single or double precision)

• 结构：

总位数为 32 位（单精度）或 64 位（双精度）。

Total bit count is 32 for float (single precision) and 64 for double.

3. 结构：

浮点数由三部分组成：符号位、指数位和尾数位。

sign bit, exponent, mantissa.

• 数值表示形式为 ± (1.m) × 2^E，其中 m 是尾数部分，E 是指数。

• 符号位：占 1 位，用于表示正负号。

• 指数位：根据精度的不同，单精度为 8 位，双精度为 11 位。

• 尾数位：决定了数值的精度，单精度为 23 位，双精度为 52 位。

String Types

• CHAR[(M)]：

固定长度的(fixed-length)字符串，M 表示长度（0 到 255），存储时右侧用空格填充。 right-padded with spaces when stored.

• Default behavior: If M is not specified, CHAR defaults to CHAR(1).

• 存储特点：存储时，字符串会在右侧用空格填充以达到指定长度。当检索时，尾部的空格会自动移除（这一行为可以通过设置调整）。
• VARCHAR(M)：

可变长度的(variable-length)字符串，M 范围为 0 到 65,535。

• MySQL 中的对比：

CHAR 更快但占用更多内存；VARCHAR 较慢但占用较少内存。(occupies memory)

使用 CHAR：当存储的数据长度固定时，如身份证号码或邮政编码。

使用 VARCHAR：当存储的数据长度可变且需要节省存储空间时，如姓名或地址。

String Values

• 字符串值在 SQL 中用单引号(single quotes)括起：

‘I AM A STRING’

• 在 MySQL 中：

双引号也可以用来括字符串（非标准特性）。

• 示例：

insert into staff values ('Daryl', 'STF001');

缺点：

1. 数据完整性问题：VARCHAR 或 CHAR 列无法确保输入的是有效的数字，可能导致存储非数字字符，影响数据的准确性。

2. 性能问题：使用 VARCHAR 或 CHAR 存储数字会降低计算性能，因为每次涉及计算或比较时都需要将字符串转换为数字格式。

3. 额外的存储开销：字符串类型比数值类型可能占用更多的存储空间，尤其是存储短数字时，可能导致不必要的空间浪费。

4. 索引和排序效率：在数值列上创建索引和排序的效率通常高于字符串列，特别是在大型数据集上。

优点：

1. 灵活性：在某些情况下，比如存储电话号码、身份证号码或其他带前导零的数字，用 VARCHAR 可以保留格式并避免自动去掉前导零。

2. 非数字字符混合：如果数据可能包含非数字字符，例如标识符（如A123），VARCHAR 或 CHAR 是更合适的选择。

3. 动态长度：VARCHAR 支持可变长度，适用于不同长度的数字字符串，节省存储空间。

TIMESTAMP

• Similar to DATETIME but stores the time as UTC time.

• 当查询(look up)时间时，存储的时间将会被转换为客户端当前时区的时间。( current timezone of the client)

create table `ts_test` (

	time1` timestamp,

	time2` datetime

);

insert into `ts_test` values ('2020-01-08 08:00:00', '2020-01-08 08:00:00');

• TIMESTAMP 适合需要根据时区调整时间显示的情况，如跨地区的应用程序，确保用户查看的是本地时间。

NULL and Special Values

• 可以使用 NULL 或特殊值来指示某种情况。

• NULL 是一种特殊的占位符（placeholder），可用于不同数据类型的列中（允许 NULL）。

• 当涉及算术运算（arithmetic operations）时，它具有特殊的行为。

在涉及 NULL 的算术运算中，结果通常是 NULL，因为 NULL 表示未知。例如，在 5 + NULL 的计算中，结果是 NULL。

Column Options

• NOT NULL：此列的值不能为空。

• UNIQUE：每个值必须唯一。

UNIQUE 约束确保列中的每个值都是独一无二的，不会重复。这在需要保持数据唯一性的情况下，如邮箱地址或用户名时非常有用。

• DEFAULT 值：如果用户未指定（specified），此列的默认值。

age INT DEFAULT 12 

AUTO_INCREMENT

• 必须应用于主键或唯一键列。

Must be applied to a key column (primary key, unique key).

• 通常，当数据添加时，会自动插入 max(col) + 1 的值。

• 第一个值是 1。

CREATE TABLE Persons (

Id INT AUTO_INCREMENT,

…

);

• 可手动（manually)提供值以覆盖(override)此行为。

ALTER TABLE Persons AUTO_INCREMENT = 100;

这会将下一个自动递增值设置为 100。

Example

CREATE TABLE Persons (

    id INT UNIQUE NOT NULL AUTO_INCREMENT,

    lastname VARCHAR(255) NOT NULL,

    `firstname VARCHAR(255),

    age INT DEFAULT 12,

    city VARCHAR(255)

) AUTO_INCREMENT = 5;

Extended Reading - Implicit Default Values

隐式默认值扩展阅读

• 如果未使用 DEFAULT 选项（option），MySQL 可能会根据列的数据类型给出隐式默认值。（ implicit default values）

If the column can take NULL as a value, the column is defined with an explicit DEFAULT NULL clause.

解释：对于允许 NULL 的列，如果在插入数据时没有指定值，该列的默认值将是 NULL。

• If the column cannot take NULL as a value, MySQL defines the column with no explicit DEFAULT clause.

解释：对于不允许 NULL 的列，如果没有默认值，插入时必须提供该列的值，否则会引发错误。

在严格模式下，MySQL 要求插入数据时必须为 NOT NULL 列提供值，或者该列必须有明确的 DEFAULT 子句。

Tuple Manipulation: INSERT, UPDATE, DELETE

INSERT：将行插入到数据库中。

NSERT INTO tablename (col1, col2, …)

    VALUES (val1, val2, …);

    VALUES (val1, val2, …);

If you are adding a value to every column, you don’t have to list them

INSERT INTO tablename VALUES (val1, val2,……）；

UPDATE

• 根据 WHERE 条件更改指定行的值。（WHERE conditions）
• 符合条件的所有行将被设置（set）为指定的值。

UPDATE Employee 
SET
    Salary = 15000,
    Name = 'Jane' 
WHERE ID = 4;

• 如果没有提供条件，则所有行都会被更改。

UPDATE Employee 
SET Salary = Salary * 1.05;

• 值可以是常量或从列中计算得到的。

UPDATE Staff
SET age = age + 1
WHERE age > 0;

DELETE

Removes all rows, or those which satisfy a condition
If no condition is given then ALL rows are deleted.

DELETE FROM
	table- name
	WHERE condition;

CREATE  TABLE `Branch`(
	`branchNo` CHAR(4) PRIMARY KEY,
	`street` VARCHAR(10),
	`city` VARCHAR(10),
	`postCode VARCHAR(10)
);

INSERT INTO Branch (branchNo, street, city, postCode) VALUES
('B005', '22 Deer Rd', 'London', 'SW1 4EH'),
('B007', '16 Argyll St', 'Aberdeen', 'AB2 3SU'),
('B003', '163 Main St', 'Glasgow', 'G11 9QX'),
('B004', '32 Manse Rd', 'Bristol', 'BS99 1NZ'),
('B002', '56 Clover Dr', 'London', 'NW10 6EU');

UPDATE Branch
	SET 
		branchNo = 'B009'
	WHERE branchNo = 'B005';
	
DELETE FORM 
	Branch
WHERE branchNo = 'B003'; 

Example

– Table and column names

CREATE TABLE mytable (
   columnA INT,
   columnB CHAR(11)
);

CREATE TABLE `my table` (
   column A INT,    -- error: Not surrounded with ``
   `column B` CHAR(11)
);

CREATE TABLE `my table` (
   `column A` INT,
   `column B` CHAR(11)
);

– Numerical data types

CREATE TABLE `staff` (
   `name` VARCHAR(12),
   `staff_id` INT,
   `salary` DECIMAL(5,2)
);

CREATE TABLE `staff` (
   `name` VARCHAR(12),
   `id_card` CHAR(6)
);

– date time

create table `ts_test` (
    `time1` timestamp,
    `time2` datetime
);

insert into `ts_test` values
    ('2020-01-08 08:00:00', '2020-01-08 08:00:00');

– column options

create table `student` (
	`id` int unique auto_increment,
	`name` char(12) not null,
	`year` int default 2
) auto_increment = 7;

-- error: null for name not allowed
insert into student (`name`,`year`) values (null, 9);

-- error: null for name not allowed
insert into student (`year`) values (9);

-- year 2 is automatically added
insert into student (`id`,`name`) values (123, 'Jason');

-- error, unique key violated
insert into student (`id`,`name`) values (123, 'Jane');

PRIMARY KEY 确保行唯一且不允许空值，而 UNIQUE 则允许空值（多个NULL值不冲突）但确保非空值唯一。
• 每个表只能有一个 PRIMARY KEY。
• 一个表可以有多个 UNIQUE 约束列。

-- overrides default value of year
insert into student (`id`,`name`,`year`)
    values (234, 'Jason', 3);

-- will automatically generate an id (123 + 1 = 124) and uses the default year 2
insert into student (`name`) values ('Anna');
insert into student (`name`) values ('George');

1.	第一次未指定 id 时，生成的 id 是 7。
2.	之后的每次插入会基于前一条记录的 id 增加 1。例如，如果最后插入的 id 是 123，那么下一条记录的 id 会是 124。

create table a (
    id int,
    name char(2),
    primary key (id)
);

insert into a (`name`) values ('go');