数据库定义和操作语句的重要语法

数据库📊的10种语法

数据查询语句

• select : 用于从数据库中检索数据。

select column1, column2,....
from table_name
where condition;

• select distinct : 用于从数据库中检索唯一的数据值。

select DISTINCT column1, clumn2,...
from table_name;

工作原理如下：

• 数据库系统会遍历指定列的所有值。
• 系统会将遇到的每个不同的值添加到结果集中。
• 最终，结果集中将包含列中的所有唯一值。

这种方法适用于任何列，但对于大型数据集可能会产生一些性能开销，因为数据库需要遍历整个列来找到唯一值。

• order by : 用于对查询结果进行排序。用在select的末尾。

select column1，column2, ...
from table_name
order by column1 [ASC|DESC], column2 [ASC|DESC],...;

数据插入、更新和删除语句

• insert into : 用于将新数据插入到数据库表中。

insert into table_name (column1, column2, column3...)
values (value1, value2, value3,...);

• update : 用于更新数据库表中的现有数据。

update table_name
set column1=value1, column2=value2,...
where condition;

• delete from : 用于从数据库表中删除数据。

delete from table_name
where condition;

数据库结构定义和修改语句

• create table : 用于创建数据库表。

create table_name(
    column1 datetype,
    column2 datatype,
    ...
);

• alter table : 用于修改数据库表的结构。

alter table table_name
add column_name datatype;

alter table table_name
modify column_name datatype;

alter table table_name
drop column_name;

• drop table : 用于删除数据库表。

drop table table_name;

• 创建索引：用于加速查询数据库中的数据，使得系统能够快速定位，范围查询和加速排序和连接目标值。

CREAT INDEX idx_department_id ON employees (department_id);

-- 查询所有部门为101的员工
SELECT * FROM employees where department_id = 101;

在这个查询中，如果 department_id 列上有索引，数据库系统可能会选择使用该索引来加速查询。

数据库连接查询语句

1. JOIN : 用于连接两个或多个表的行，根据指定的条件关联行。
   • inner join : 返回两个表中满足条件的行。
   • outer join : 返回两个表中至少有一行满足条件的行。?

更复杂的语法：高级查询和数据处理

这些语法用于处理更复杂的查询需求，例如多表联合查询、汇总统计、条件过滤等。

子查询

1. 嵌套查询: 在一个查询中嵌套另一个查询，用于检索嵌套查询的结果。

select column1
from table1
where column2 in (select columns from table2 where condition);

连接查询

1. inner join: 返回两个表中满足条件的行。

select column1, column2
from table1
inner join table2 on table1.column = table2.column;

其中，select column1, column2: 这部分指定了你希望从结果中集中显示的列。在这个例子中，你选择了表'table1'中的'colum1'和'column2'。

from table1: 指定主要表的部分，既要从中选择数据的表。在这里，主要表是‘table1’。

inner join table2 on?table1.column = table2.column : 这里是连接表的部分。通过使用inner join，你正在执行内连接，这意味着只有在两个表中的指定列的值相匹配的情况下，相关的行才会包含在结果集中。

• 'table2':这是要与主要表'table1'进行连接的第二个表。
• 'on table1.column = table2.column': 这是指定连接条件的部分。他告诉数据库在哪两个表的列上进行匹配。在这里，它表示只有在'table1'的列与'table2'的列相等时，两个表的行才会连接。

该查询目的：这个查询的目的是从‘table1’和‘table2’中选择'table1'的'column1'和'column2'，并且只选择那些在连接条件中匹配的行。

举例：假设你有两个表，employees包含员工的信息，包括ID(employee_id)，姓名(employee_name)，和所属部门的ID(departmet_id)。departments包含部门的信息，包括部门ID(department_id)和部门名称(department_name)。

现想要获取每个员工及其所属部门的名称：

# SQL
select employees.employee_id, employees.employee_name, departments.department_name
from employees
inner join departments on employees.department_id = departments.department_id;

这个查询的含义是：

• 从 employees 表中选择员工的ID (employee_id) 和姓名 (employee_name)。
• 从 departments 表中选择部门的名称 (department_name)。
• 通过内连接 (INNER JOIN) 将这两个表连接起来，连接条件是 employees.department_id = departments.department_id，即员工所属部门的ID要与部门表中的ID相匹配。

内连接的好处：

1. 关联数据：内连接使你能够关联两个表中相关的数据，通过共享相同值的列将他们连接起来，不必手动整合。
2. 减少数据冗余：内连接仅返回满足条件的行，从而减少了结果集中的数据冗余。避免了不相关数据的混入。
3. 提高查询灵活性：内连接允许你在查询中指定多个表，并且可以通过不同的连接条件创建不同的关联。
4. 优化性能：仅返回满足条件的行，查询可更快，不需要处理不想干数据。

内连接的强大体现在在关系型数据库中从多个表中检索和组合数据，提供了更丰富和有关联的查询结果。

从四个表中连接表后，查询结果的例子：

select t1.column1, t2.column2, t3.column3, t4.column4
from table t1
inner join table2 t2 on t1.common_colum = t2.common_column
inner join table3 t3 on t2.another_common_column = t3.another_common_column
inner join table4 t4 on t3.yet_another_common_column = t4.yet_another_common_column;

• t1, t2, t3, t4是表的别名，简化了查询语句。
• 'common_column', 'another_common_column', 'yet_another_common_column'是连接表的列，用于建立连接关系。

2. 外连接Outer join：允许检索表中未匹配的行，并在结果集中以NULL返回这些行。

外连接分为左外连接(left outer join)、右外连接(right outer join)和全外连接(full outer join)。

左外连接例子

举例：假设你有两个表，employees包含员工的信息，包括ID(employee_id)，姓名(employee_name)，和所属部门的ID(departmet_id)。departments包含部门的信息，包括部门ID(department_id)和部门名称(department_name)。

employees表：

| employee_id | employee_name | department_id |
|-------------|---------------|---------------|
| 1           | John          | 101           |
| 2           | Jane          | 102           |
| 3           | Bob           | 103           |

departments表：

| department_id | department_name |
|---------------|------------------|
| 101           | HR             |
| 102           | IT             |
| 104           | Marketing      |

现打算获取所有员工及其所属部门的名称，包括那些没有匹配到部门的员工。你可以使用左外连接来实现。

select employees.employee_id, employees.employee_name, departments.department_name
from employees
left outer join departments on employees.department_id = departments.departments_id

语句含义：最终会返回employees 表中的所有行，不论它们在 departments 表中是否有匹配的部门信息。如果某个员工没有匹配到部门，departments 表的相关列将会包含 NULL 值。

left outer join结果：

| employee_id | employee_name | department_name |
|-------------|---------------|------------------|
| 1           | John          | HR               |
| 2           | Jane          | IT               |
| 3           | Bob           | NULL             |

右外连接right outer join例子：

右外连接，以返回departments表中所有数据为基准，employees中缺失值补NULL：

SELECT employees.employee_id, employees.employee_name, departments.department_name
FROM employees
RIGHT OUTER JOIN departments ON employees.department_id = departments.department_id;

在这个查询中，RIGHT OUTER JOIN 表示右外连接。这意味着将返回 departments 表中的所有行，不论它们在 employees 表中是否有匹配的员工信息。如果某个部门没有匹配到员工，employees 表的相关列将会包含 NULL 值。

结果如下：

| employee_id | employee_name | department_name |
|-------------|---------------|------------------|
| 1           | John          | HR               |
| 2           | Jane          | IT               |
| NULL        | NULL          | Marketing        |

全外连接full outer join例子

右外连接，以返回employees表和departments表中所有数据为基准，employees和departments中缺失值补NULL：

SELECT employees.employee_id, employees.employee_name, departments.department_name
FROM employees
FULL OUTER JOIN departments ON employees.department_id = departments.department_id;

运行结果：

| employee_id | employee_name | department_name |
|-------------|---------------|------------------|
| 1           | John          | HR               |
| 2           | Jane          | IT               |
| 3           | Bob           | NULL             |
| NULL        | NULL          | Marketing        |

这个结果集包含了 employees 和 departments 表中的所有记录，不论它们在另一个表中是否有匹配。如果某个记录在其中一个表中没有匹配，相应的列将包含 NULL 值。

聚合函数

聚合函数是用于对一组值进行计算并返回单个结果的SQL函数。这些函数通常用于对数据库中的数据进行汇总和统计，提供了对数据集合进行分析的强大工具。

1. SUM：计算某列的总和。

select SUM(column1) from table1;

2. AVG: 计算某列的平均值。

select AVG(column1) from table1;

3. COUNT: 计算某列的行数。

select COUNT(column1) from table1;

4. MAX: 获取某列的最大值。

select max(column1) from table1;

5. MIN: 获取某列的最小值。

select min(column1) from table1;

GROUP BY 和 HAVING

1. GROUP BY: 对查询结果按例进行分组：

select column1, COUNT(*)
from table1
group by column1;

解释：

• select column1, count(*): 选择查询结果中的列，其中包括column1列和一个计算每个分组中行的数量的计数值。count(*)表示对每个分组中的行数进行计数。
• from table: 指定查询的数据来源，即要统计的表是table。
• group by column1: 根据column1列的值对结果进行分组。这意味着查询将返回每个不同的column1值以及该值出现的次数。

举例1：

| column1 |
|---------|
| A       |
| B       |
| A       |
| A       |
| B       |
| C       |

运行上述查询后，结果可能是：

| column1 | COUNT(*) |
|---------|----------|
| A       | 3        |
| B       | 2        |
| C       | 1        |

解释结果：

• "A" 出现了 3 次。
• "B" 出现了 2 次。
• "C" 出现了 1 次。

这种查询对于了解数据分布、查找出现频率最高的项等情况非常有用。

举例2:

table1包含以下数据：

| column1 | column2 |
|---------|---------|
| A       | X       |
| B       | Y       |
| A       | X       |
| A       | Z       |
| B       | Z       |
| C       | Y       |

运行以下查询：

SELECT column1, column2, COUNT(*)
FROM table1
GROUP BY column1, column2;

可能的结果是：

| column1 | column2 | COUNT(*) |
|---------|---------|----------|
| A       | X       | 2        |
| B       | Y       | 1        |
| A       | Z       | 1        |
| B       | Z       | 1        |
| C       | Y       | 1        |

这表示按照 column1 和 column2 的组合进行分组，然后计算每个组合的行数。例如，"A" 和 "X" 的组合出现了 2 次，"B" 和 "Y" 的组合出现了 1 次，以此类推。

2. HAVING: 在GROUP BY的基础上进行条件过滤。

select column1, COUNT(*)
from table1
group by coumn1
having count(*) > 1;

解释：

这是一个带有having子句的sql查询语句，用于从表table1中选择出现次数大于1的不同column1值以及它们的出现次数。

• select column1, count(*): 选择查询结果中的列，包括column1列和一个计算每个分组中行的数量的计数值。count(*)表示对每个分组中的行数进行计数。
• from table1: 指定查询的数据来源，即要统计的表是table1。
• group by column1: 根据 column1 列的值对结果进行分组。这意味着查询将返回每个不同的 column1 值以及该值出现的次数。

• HAVING COUNT(*) > 1: 通过 HAVING 子句筛选出现次数大于 1 的分组。这样，结果将只包含那些在 column1 列中出现次数大于 1 的值。

举例：

table1如下：

| column1 |
|---------|
| A       |
| B       |
| A       |
| A       |
| B       |
| C       |

运行上面查询语句的结果可能是：

| column1 | COUNT(*) |
|---------|----------|
| A       | 3        |
| B       | 2        |

这表示在 column1 列中，只有 "A" 和 "B" 出现的次数大于 1。这种查询常用于查找具有重复出现的特定值的情况。

3. group by 和聚合函数sum的组合使用

假如有table1:

| column1 | column2 | some_numeric_column |
|---------|---------|---------------------|
| A       | X       | 10                  |
| B       | Y       | 5                   |
| A       | X       | 7                   |
| A       | Z       | 3                   |
| B       | Z       | 8                   |
| C       | Y       | 12                  |

运行：

SELECT column1, column2, SUM(some_numeric_column)
FROM table1
GROUP BY column1, column2;

可能的结果：

| column1 | column2 | SUM(some_numeric_column) |
|---------|---------|---------------------------|
| A       | X       | 17                        |
| B       | Y       | 5                         |
| A       | Z       | 3                         |
| B       | Z       | 8                         |
| C       | Y       | 12                        |

这表示按照 column1 和 column2 的组合进行分组，并计算每个组合中 some_numeric_column 列的总和。