SQL基础操作全解析：从多表查询到表管理与约束

一、引言

SQL（Structured Query Language）是用于管理关系数据库的标准语言，在数据库操作中起着至关重要的作用。无论是数据的检索、插入、更新还是删除，以及数据库结构的创建和管理，都离不开SQL语句。本文将详细讲解SQL中的多表查询、创建和管理表（DDL）、数据处理（DML）以及约束等方面的操作，帮助读者全面掌握SQL的基本使用方法。

二、多表查询

（一）查询方式

1. 通用型（方式一）
   • 使用SELECT... FROM... WHERE语句，明确指定连接条件。例如查询员工表employees和部门表departments按照department_id连接的信息：SELECT e.last_name,e.department_id,d.department_name FROM employees e,departments d where e.department_id = d.department_id。这种方式适用于各种复杂的连接需求，灵活性高。
2. NATURAL JOIN（方式二）
   • 会自动连接两个表中相同的列，但存在局限性，可能连接多个相同列（如department_id和manager_id等），导致结果可能不符合预期。例如SELECT last_name,department_id,department_name FROM employees NATURAL JOIN departments。
3. JOIN… USING（方式三）
   • 比方式二好一些，但要求连接列列名相同，若多表连接列名不同则不适用。如SELECT last_name,department_id,department_name FROM employees JOIN departments USING(department_id)。
4. FROM… JOIN… ON（方式四）
   • 常用方式，较方式一更容易实现外联接（左、右、满外连接）。例如SELECT last_name,e.department_id,department_name FROM employees e JOIN departments d ON e.department_id = d.department_id。

（二）连接类型

1. 内连接（等值连接、不等值连接、非自连接）
   • 内连接只返回满足连接条件的记录。等值连接基于相等条件连接表，不等值连接使用其他比较运算符连接表，非自连接用于连接不同的表。
2. 外连接（左外连接、右外连接、满外连接）
   • 左外连接返回左表的所有记录以及右表中满足连接条件的记录；右外连接返回右表的所有记录以及左表中满足连接条件的记录；满外连接返回两个表中的所有记录，无论是否满足连接条件。

三、创建和管理表（DDL）

（一）创建表

1. 直接创建
   • 使用CREATE TABLE语句，指定表名和列定义。例如create table emp1(name varchar2(20),salary number(8,2)default 1000,id number(4),hire_date date);，可以定义列的数据类型、长度、默认值等属性。
2. 通过子查询创建
   • 可以从现有表中选取数据来创建新表。如create table emp2 select last_name name,employee_id id,hire_date from employees;或者create table emp2 as select last_name name,employee_id id,hire_date from employees where department_id = 80;/where 1=2;，后一种方式可以根据条件筛选数据创建表，where 1=2可创建结构相同但无数据的空表。

（二）修改表

1. 增加新列
   • 使用ALTER TABLE语句，如alter table emp1 add(birthday date)，可以向现有表中添加新的列。
2. 修改现有列
   • 例如alter table emp1 modify(name varchar2(25) default 'abc')，可以修改列的数据类型、长度、默认值等属性。
3. 重命名现有列
   • 通过ALTER TABLE语句，如alter table emp1 rename column salary to sal;，可以更改列的名称。
4. 删除现有列
   • 使用alter table emp1 drop column birthday;可以从表中删除指定列。

（三）清空表数据

• 使用TRUNCATE TABLE语句，如truncate table emp2;，与delete from table_name不同，TRUNCATE TABLE操作更快且自动提交，不可回滚，会删除表中的所有数据并重置自增列（如果有）。

（四）重命名表

• 使用RENAME语句，如rename emp2 to emp3;，可以更改表的名称。

（五）删除表

• 使用DROP TABLE语句，如drop table emp3;，将表从数据库中永久删除，包括表结构和数据，操作不可逆。

四、数据处理（DML）

（一）插入数据

1. 增添一条记录
   • 使用INSERT INTO语句，指定表名和要插入的值。例如insert into [表名](,,,,,) values(,,,,,)，按照表列的顺序依次提供对应的值。
2. 从其他表中拷贝数据
   • 例如insert into [表名] select.... from [另一个表] where....，可以根据条件从其他表中选取数据插入到当前表中。

（二）更新数据

• 使用UPDATE语句，指定表名和要更新的列及其新值，同时使用WHERE子句指定更新条件。如update [表名] set ..... where....，如果省略WHERE子句，将更新表中的所有记录。

（三）删除数据

• 使用DELETE FROM语句，如delete from [表名] where....，根据指定条件从表中删除记录，若省略WHERE子句，将删除表中的所有记录。

（四）查询数据（最常用操作）

• 使用SELECT语句，通过指定列名、表名、WHERE子句（筛选条件）、GROUP BY子句（分组）、HAVING子句（对分组结果进行筛选）、ORDER BY子句（排序）等来检索数据。例如select.... from … where …. group by … having … order by ….。

五、约束

（一）约束定义

1. 表级约束和列级约束
   • 在创建表时，可以在列定义中定义列级约束，如hire_date date not null定义了hire_date列不能为空；也可以在表定义末尾定义表级约束，如constraint emp1_emp_id_pk primary key(employee_id)定义了employee_id为主键约束。
2. 约束类型
   • NOT NULL：确保列不接受空值，只能使用列级约束。
   • UNIQUE：保证列中的值是唯一的，可在列级或表级定义。
   • PRIMARY KEY：定义主键，唯一标识表中的每一行，可在列级或表级定义，一个表只能有一个主键。
   • FOREIGN KEY：建立表之间的关联，引用其他表的主键或唯一键，可在表级定义并指定ON DELETE CASCADE等选项来处理级联操作。
   • CHECK：用于限制列中的值必须满足特定条件，可在表级定义。

（二）添加和删除约束

1. 添加约束
   • 使用ALTER TABLE语句，如alter table emp1 add constaint emp1_sal_ck check(salary > 0)添加检查约束。对于NOT NULL约束，需要使用modify，如alter table emp1 modify (salary not null)。
2. 删除约束
   • 使用alter table emp1 drop constaint emp1_sal_ck删除约束。
3. 使约束失效和激活
   • 使用ALTER TABLE语句，如alter table emp1 disable constraint emp1_email_uk;使约束失效，alter table emp1 enable constraint emp1_email_uk;使约束激活。

六、总结

SQL的多表查询、创建和管理表、数据处理以及约束等操作是数据库管理和开发的基础。熟练掌握这些操作能够帮助开发者有效地管理数据库结构、操作数据以及确保数据的完整性和一致性。在实际应用中，根据具体需求灵活运用这些操作，可以构建高效、可靠的数据库应用系统。随着数据库技术的不断发展，SQL的功能也在不断扩展和优化，但这些基本操作始终是构建复杂数据库应用的基石。无论是小型项目还是大型企业级应用，对SQL的深入理解和熟练运用都是不可或缺的技能。