SQL数据库操作：从表创建到数据管理

### SQL 数据库操作：从表创建到数据管理 在数据库管理中，SQL 是一种强大的工具，它可以帮助我们创建表、管理数据以及优化查询。下面将详细介绍 SQL 中表的创建、索引的使用以及数据的操作。 #### 1. 表创建与约束设置 在创建表时，我们需要考虑表的结构以及数据的完整性。以下是一些关键要点： - **外键定义**：在创建表时，可以通过 `REFERENCES` 关键字来定义外键。例如，在创建表时，使用 `CUS_CODE` 作为外键引用 `CUSTOMER` 表的 `CUS_CODE`： ```sql -- 假设的表创建语句，仅为说明外键定义 CREATE TABLE SOME_TABLE ( CUS_CODE NUMBER REFERENCES CUSTOMER (CUS_CODE), -- 其他列定义 ); ``` - **默认约束**：可以使用 `DEFAULT` 约束为列设置默认值。例如，使用 `SYSDATE` 函数为 `INV_DATE` 列设置默认值为当前日期： ```sql CREATE TABLE INVOICE ( INV_DATE DATE DEFAULT SYSDATE, -- 其他列定义 ); ``` - **检查约束**：使用 `CHECK` 约束可以确保列中的数据满足特定条件。例如，确保 `INV_DATE` 大于 'January 1, 2010'： ```sql CREATE TABLE INVOICE ( INV_DATE DATE, CHECK (INV_DATE > TO_DATE('January 1, 2010', 'Month DD, YYYY')) ); ``` - **复合主键与唯一约束**：在创建 `LINE` 表时，使用复合主键 `(INV_NUMBER, LINE_NUMBER)` 并添加唯一约束 `UNIQUE(INV_NUMBER, P_CODE)` 来确保同一发票中不会重复订购同一产品： ```sql CREATE TABLE LINE ( INV_NUMBER NUMBER NOT NULL, LINE_NUMBER NUMBER(2,0) NOT NULL, P_CODE VARCHAR(10) NOT NULL, LINE_UNITS NUMBER(9,2) DEFAULT 0.00 NOT NULL, LINE_PRICE NUMBER(9,2) DEFAULT 0.00 NOT NULL, PRIMARY KEY (INV_NUMBER, LINE_NUMBER), FOREIGN KEY (INV_NUMBER) REFERENCES INVOICE ON DELETE CASCADE, FOREIGN KEY (P_CODE) REFERENCES PRODUCT(P_CODE), CONSTRAINT LINE_UI1 UNIQUE(INV_NUMBER, P_CODE) ); ``` 这里的 `ON DELETE CASCADE` 表示当删除 `INVOICE` 表中的一行时，会自动删除 `LINE` 表中与之相关的所有行，以保证数据的一致性。 #### 2. SQL 索引 索引可以提高搜索效率并避免列值重复。以下是关于索引的详细介绍： - **创建索引的语法**：使用 `CREATE INDEX` 命令可以在任何选定的属性上创建索引，语法如下： ```sql CREATE [UNIQUE] INDEX indexname ON tablename(column1 [, column2]) ``` - **普通索引示例**：基于 `PRODUCT` 表中的 `P_INDATE` 属性创建名为 `P_INDATEX` 的索引： ```sql CREATE INDEX P_INDATEX ON PRODUCT(P_INDATE); ``` - **唯一索引示例**：使用 `UNIQUE` 关键字创建唯一索引，确保索引列中的值不会重复。例如，为 `PRODUCT` 表的 `P_CODE` 列创建唯一索引： ```sql CREATE UNIQUE INDEX P_CODEX ON PRODUCT(P_CODE); ``` 如果尝试插入重复的 `P_CODE` 值，SQL 会产生 “duplicate value in index” 错误消息。 - **复合索引**：当需要对多个列进行搜索或排序时，可以使用复合索引。例如，为了避免员工测试记录的重复，可以使用 `EMP_NUM`、`TEST_CODE` 和 `TEST_DATE` 创建唯一复合索引： ```sql CREATE UNIQUE INDEX EMP_TESTDEX ON TEST(EMP_NUM, TEST_CODE, TEST_DATE); ``` 以下是一个包含重复测试记录的示例表格： | EMP_NUM | TEST_NUM | TEST_CODE | TEST_DATE | TEST_SCORE | | ------- | -------- | --------- | ------------ | ---------- | | 110 | 1 | WEA | 15-Jan-2010 | 93 | | 110 | 2 | WEA