万字详解ANSI SQL语法及其扩展

ANSI SQL 与扩展SQL详解

一、ANSI SQL语法

ANSI SQL（American National Standards Institute Structured Query Language），即美国国家标准学会结构化查询语言，是一种标准化的数据库查询语言。它用于管理和操作关系数据库中的数据。ANSI SQL 定义了一组标准的 SQL 语句，以确保在不同的数据库管理系统（DBMS）之间保持一致性和互操作性。下面是 ANSI SQL 语法的详解：

数据定义语言 (DDL)

DDL 用于定义和管理数据库结构，如创建、修改和删除数据库对象。

1. 创建数据库:

   CREATE DATABASE database_name;
   

2. 删除数据库:

   DROP DATABASE database_name;
   

3. 创建表:

   CREATE TABLE table_name (
       column1 datatype [constraint],
       column2 datatype [constraint],
       ...
   );
   

4. 删除表:

   DROP TABLE table_name;
   

5. 修改表:

   • 添加列:

     ALTER TABLE table_name ADD column_name datatype;
     

   • 删除列:

     ALTER TABLE table_name DROP COLUMN column_name;
     

   • 修改列数据类型:

     ALTER TABLE table_name ALTER COLUMN column_name datatype;
     

数据操作语言 (DML)

DML 用于查询和修改数据库中的数据。

1. 插入数据:

   INSERT INTO table_name (column1, column2, ...)
   VALUES (value1, value2, ...);
   

2. 更新数据:

   UPDATE table_name
   SET column1 = value1, column2 = value2, ...
   WHERE condition;
   

3. 删除数据:

   DELETE FROM table_name WHERE condition;
   

4. 查询数据:

   SELECT column1, column2, ...
   FROM table_name
   WHERE condition
   GROUP BY column
   HAVING condition
   ORDER BY column
   LIMIT number;
   

数据控制语言 (DCL)

DCL 用于授权和撤销对数据库对象的访问权限。

1. 授予权限:

   GRANT privilege ON object TO user;
   

2. 撤销权限:

   REVOKE privilege ON object FROM user;
   

事务控制语言 (TCL)

TCL 用于管理事务处理，以确保数据的一致性和完整性。

1. 开始事务:

   BEGIN TRANSACTION;
   

2. 提交事务:

   COMMIT;
   

3. 回滚事务:

   ROLLBACK;
   

语法示例

创建表

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    hire_date DATE,
    salary DECIMAL(10, 2),
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

插入数据

INSERT INTO employees (employee_id, first_name, last_name, hire_date, salary, department_id)
VALUES (1, 'John', 'Doe', '2022-01-01', 50000.00, 101);

查询数据

SELECT first_name, last_name, salary
FROM employees
WHERE department_id = 101
ORDER BY last_name;

更新数据

UPDATE employees
SET salary = salary * 1.10
WHERE department_id = 101;

删除数据

DELETE FROM employees
WHERE employee_id = 1;

授予权限

GRANT SELECT, INSERT ON employees TO user1;

回滚事务

BEGIN TRANSACTION;

UPDATE employees
SET salary = salary * 1.10
WHERE department_id = 101;

ROLLBACK;  -- 取消更新操作

ANSI SQL 提供了一套通用的标准语法，用于定义和操作关系数据库。尽管不同的数据库管理系统（DBMS）对 SQL 标准进行了不同程度的扩展，但理解和掌握 ANSI SQL 标准是学习和使用 SQL 的基础。在实际应用中，可以根据特定 DBMS 的文档和功能，进一步了解和使用其特有的 SQL 扩展。

ANSI SQL 多表查询

ANSI SQL 标准定义了多表连接查询的几种常见类型，包括内连接 (INNER JOIN)、左外连接 (LEFT JOIN)、右外连接 (RIGHT JOIN)、全外连接 (FULL JOIN) 、交叉连接 (CROSS JOIN)和自链接（SELF JOIN），下面提供了每种连接类型的语法和示例，并增加图示以帮助理解连接结果。

1. 内连接 (INNER JOIN)

语法

SELECT columns
FROM table1
INNER JOIN table2
ON table1.common_column = table2.common_column;

示例

SELECT employees.name, departments.department_name
FROM employees
INNER JOIN departments
ON employees.department_id = departments.department_id;

图示

employees             departments
+----+------+---------+          +----+---------+
| id | name | dept_id |