首页 > 数据库 >数据库系统原理——第三章 关系数据库标准语言SQL

数据库系统原理——第三章 关系数据库标准语言SQL

时间：2024-10-18 11:13:05浏览次数：1  
标签：JOIN name column 视图 查询 关系数据库 SQL 数据库系统 SELECT

@

目录
本文首先介绍SQL的基本情况，然后介绍数据定义、数据更新、数据查询和视图。

1.SQL的特点

  1. 功能齐全
  2. 高度非过程化
  3. 面向集合的操作方式
  4. 同一种语法结构提供多种使用方式
  5. 语言简洁

2.SQL的组成

  1. 数据定义语言(DDL)
  2. 数据查询语言(DQL)
  3. 数据操纵语言(DQL)
  4. 数据控制语言(DML)

3SQL语句

3.1数据库的基本操作

  1. 创建数据库
CREATE DATABASE database_name;
  1. 切换数据库
USE database_name;
  1. 修改数据库
ALTER DATABASE database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  1. 删除数据库
DROP DATABASE database_name;

3.2 基本表的定义、修改、删除

  1. 基本表的定义
CREATE TABLE table_name (
    column1 datatype CONSTRAINTS,
    column2 datatype CONSTRAINTS,
    ...
);
数据类型 描述
INT 用于整数值。
VARCHAR(n) 可变长度的字符串，最大长度为 n。
TEXT 用于长文本字符串。
DATE 存储日期值（年、月、日）。
TIME 存储时间值（时、分、秒）。
DATETIME 存储日期和时间。
FLOAT 浮点数，用于存储带小数的数值。
DOUBLE 双精度浮点数，比FLOAT更精确。
BOOLEAN 存储布尔值 (TRUE 或 FALSE)。
BLOB 用于存储二进制数据。
  1. 基本表的增加
ALTER TABLE table_name ADD column_name datatype;
  1. 基本表的修改
ALTER TABLE table_name MODIFY COLUMN column_name new_datatype;
  1. 基本表的删除
DROP TABLE table_name;

3.3索引的建立与删除

  1. 建立索引
CREATE INDEX index_name ON table_name (column1, column2, ...);
索引类型 描述
PRIMARY KEY 一个表中对应唯一记录的索引，不能包含 NULL 值。通常用于标识表中的唯一记录。
UNIQUE 索引中的所有值都必须是唯一的，类似于主键，但可以有多个，并且可以接受 NULL 值。
INDEX 普通索引，用来加速查询过程，无唯一性要求。可以为表中一个或多个列创建索引。
FULLTEXT 专为全文搜索设计的索引，只适用于某些数据库系统如 MySQL。
SPATIAL 用于地理数据存储的索引，可以快速处理空间数据查询。仅在支持该功能的数据库中可用。
FOREIGN KEY 用于建立两个表之间的联系，指向另一个表中的主键。
  1. 删除索引
DROP INDEX index_name ON table_name;

3.4数据更新

  1. 插入数据
INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);
  1. 更新数据
UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;
  1. 删除数据
DELETE FROM table_name WHERE condition;

3.5数据查询

3.5.1单表查询

单表查询是数据库查询中最基础的形式，它仅涉及单个数据表。以下是单表查询中常用的SQL语句和子句：

  1. 基本的SELECT语句
    最基本的单表查询使用SELECT语句来选择表中的列。例如，选择employees表中的name和age列：
SELECT name, age FROM employees;
  1. 使用WHERE子句进行条件过滤
    WHERE子句允许你根据条件过滤结果集。例如，选择年龄大于30岁的员工：
SELECT name, age FROM employees WHERE age > 30;
  1. 使用ORDER BY子句进行排序

ORDER BY子句可以对结果进行排序，可以是升序（ASC）或降序（DESC）。例如，按年龄升序排列员工：

SELECT name, age FROM employees ORDER BY age ASC;
  1. 使用GROUP BY子句进行分组

GROUP BY子句可以将结果集按一个或多个列的值进行分组。例如，按部门分组并计算每个部门的员工数量：

SELECT department, COUNT(*) FROM employees GROUP BY department;
  1. 使用HAVING子句进行分组后的条件过滤
    HAVING子句与WHERE类似，但它是在分组后对结果集进行条件过滤。例如，选择员工数量超过10人的部门：
SELECT department, COUNT(*) FROM employees GROUP BY department HAVING COUNT(*) > 10;
  1. 使用LIMIT子句限制结果数量
    LIMIT子句用于限制查询结果的数量。例如，选择前5名员工：
SELECT name, age FROM employees LIMIT 5;
  1. 使用AS给列指定别名

AS关键字允许你给列指定别名。例如，给员工年龄列指定别名为age_years：

SELECT name, age AS age_years FROM employees;

3.5.2连接查询

连接查询（JOIN）用于结合两个或多个表中的行，这些表之间存在某种关系。最常用的连接类型包括内连接（INNER JOIN）、左连接（LEFT JOIN）、右连接（RIGHT JOIN）和全连接（FULL JOIN）。

3.5.2.1内连接（INNER JOIN）

MySQL中的内连接（INNER JOIN）有几种不同的写法，主要取决于你想要如何表达连接的条件。以下是一些常见的写法：

  1. 隐式内连接（使用WHERE子句）:
 SELECT column_name(s)
 FROM table1, table2
 WHERE table1.column_name = table2.column_name;
  1. 显式内连接（使用INNER JOIN关键字）:
SELECT column_name(s)
FROM table1
INNER JOIN table2 ON table1.column_name = table2.column_name;
    ```
3. **使用USING关键字**（当两个表中有相同名称的列时）:
```sql
SELECT column_name(s)
FROM table1
INNER JOIN table2 USING (column_name);
  1. 使用ON关键字（指定连接条件）:
SELECT column_name(s)
FROM table1
INNER JOIN table2 ON table1.column_name = table2.column_name;
  1. 使用别名（给表起别名以简化查询）:
SELECT a.column_name, b.column_name 
FROM table1 AS a
INNER JOIN table2 AS b ON a.id = b.id;
  1. 多表内连接（连接多个表）:
SELECT a.column_name, b.column_name, c.column_name
FROM table1 AS a
INNER JOIN table2 AS b ON a.id = b.id
INNER JOIN table3 AS c ON b.id = c.id;
  1. 交叉连接（笛卡尔积，虽然不是内连接，但有时候也会用到）:
SELECT *
FROM table1, table2;

3.5.2.2左连接（LEFT JOIN）

  1. 使用LEFT JOIN关键字：
SELECT column_name(s)
FROM table1
LEFT JOIN table2 ON table1.column_name = table2.column_name;
  1. 使用别名：
SELECT a.column_name, b.column_name
FROM table1 AS a
LEFT JOIN table2 AS b ON a.id = b.id;
  1. 使用USING关键字（当两个表中有相同名称的列时）：
SELECT column_name(s)
FROM table1
LEFT JOIN table2 USING (column_name);

3.5.2.3右连接（RIGHT JOIN）

  1. 使用RIGHT JOIN关键字：
SELECT column_name(s)
FROM table1
RIGHT JOIN table2 ON table1.column_name = table2.column_name;
  1. 使用别名：
SELECT a.column_name, b.column_name
FROM table1 AS a
RIGHT JOIN table2 AS b ON a.id = b.id;
  1. 使用USING关键字（当两个表中有相同名称的列时）：
SELECT column_name(s)
FROM table1
RIGHT JOIN table2 USING (column_name);
  1. 多表右连接（连接多个表）：
SELECT a.column_name, b.column_name, c.column_name
FROM table1 AS a
RIGHT JOIN table2 AS b ON a.id = b.id
RIGHT JOIN table3 AS c ON b.id = c.id;

3.5.2.4全连接（FULL JOIN）

全连接返回两个表中的所有行，无论它们是否匹配。如果一个表中没有匹配的行，则结果中该表的部分将为NULL。

SELECT columns
FROM table1
FULL JOIN table2
ON table1.column_name = table2.column_name;

3.5.3嵌套查询

嵌套查询，也称为子查询（Subquery），是嵌套在另一个查询中的SQL查询。它可以出现在SELECT、INSERT、UPDATE或DELETE语句中的任何地方。
1. IN 操作符
IN 操作符用于指定多个值的列表，子查询返回的值必须匹配列表中的一个值。

SELECT * FROM employees 
WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 10);

这个查询返回所有在位置ID为10的部门工作的员工。

  1. 比较操作符

比较操作符包括=, <>, >, <, >=, <=等，用于比较子查询返回的单个值。

SELECT * FROM employees 
WHERE salary > (SELECT AVG(salary) FROM employees);

这个查询返回所有薪水高于平均薪水的员工。

3. ANY 或 ALL 操作符

  • ANY：如果子查询返回的任意一个值满足比较条件，则条件为真。
  • ALL：如果子查询返回的所有值都满足比较条件，则条件为真。
-- ANY
SELECT * FROM employees e1 
WHERE e1.salary > ANY (SELECT salary FROM employees e2 WHERE e2.department_id = e1.department_id);

-- ALL
SELECT * FROM employees e1 
WHERE e1.salary > ALL (SELECT salary FROM employees e2 WHERE e2.department_id = e1.department_id AND e2.salary < 5000);

ANY的查询返回薪水高于同部门任意员工的员工，而ALL的查询返回薪水高于同部门所有低薪水员工的员工。
4. EXISTS 操作符
EXISTS代表存在量词∈，EXISTS 操作符用于检查子查询是否返回至少一个行。

SELECT * FROM employees e1 
WHERE EXISTS (SELECT * FROM orders o WHERE o.employee_id = e1.employee_id);

这个查询返回至少有一个订单的员工。

3.5.4集合查询

在SQL中，集合查询通常指的是使用集合操作符来组合多个查询结果集。这些操作符包括UNION、INTERSECT和EXCEPT（或在某些数据库系统中使用MINUS）。以下是这些操作符的详细解释：
1. UNION
UNION操作符用于合并两个或多个SELECT语句的结果集，去除重复的行。

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

这个查询返回table1和table2中所有不同的行。
2. INTERSECT
INTERSECT操作符用于返回两个或多个SELECT语句共有的行。

SELECT column_name(s) FROM table1
INTERSECT
SELECT column_name(s) FROM table2;

这个查询返回同时存在于table1和table2中的行。

3. EXCEPT（或MINUS）

EXCEPT操作符用于返回第一个查询中存在而第二个查询中不存在的行。在某些数据库系统中，如Oracle，使用MINUS来实现相同的功能。

SELECT column_name(s) FROM table1
EXCEPT
SELECT column_name(s) FROM table2;

或者在Oracle中：

SELECT column_name(s) FROM table1
MINUS
SELECT column_name(s) FROM table2;

使用集合操作符的注意事项

  1. 选择列表一致性：在使用UNION、INTERSECT或EXCEPT时，每个查询的选择列表（即SELECT语句中指定的列）必须具有相同数量的列，并且相应的列数据类型必须兼容。
  2. UNION与UNION ALL：UNION默认去除重复的行，而UNION ALL则保留所有行，包括重复的行。如果确定结果集中不会有重复行，或者你想要保留所有行，使用UNION ALL可能会有更好的性能。
  3. 性能：集合操作可能会影响数据库性能，尤其是当处理大量数据时。优化查询和索引可以提高性能。
  4. 可读性：合理使用集合操作符可以使查询更加清晰和易于理解。

3.6视图

3.6.1视图与基本表的区别

视图（View）:

  • 是一种虚拟存在的表，其内容由查询定义。
  • 视图包含一系列带有名称的列和行数据，但并不在数据库中以存储的数据值集形式存在。
  • 行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。
  • 视图不占用物理存储空间，它们是查询结果的可视化表示。
  • 可以包含表的全部或部分记录，也可以由一个或多个表创建。
    基本表（Base Table）:
  • 是实际存储数据的表。
  • 基本表包含实际的数据行和列，它们存在于数据库的物理存储中。
  • 基本表的数据是持久化的，即使数据库关闭，数据也会被保留。
  • 基本表可以进行各种数据操作，包括插入、更新、删除和查询。

3.6.2视图的优点

  1. 简化复杂的SQL操作：通过将复杂的SQL查询封装为视图，可以简化业务逻辑和提高代码的可维护性 。
  2. 安全性：可以为不同用户定制不同的视图，限制对特定数据的访问，提高安全性 。
  3. 逻辑数据独立性：应用程序可以基于视图而不是基础表进行开发，当基础表结构变化时，可以通过修改视图来保持应用程序的稳定 。
  4. 重构数据库提供了一定程度的逻辑独立性：视图可以在不影响应用程序的情况下对底层数据结构进行修改 。

3.6.3创建与删除视图

创建视图的基本语法如下：

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

如果要替换已存在的视图，可以使用 OR REPLACE 选项。

删除视图的语法如下：

DROP VIEW view_name;

使用 IF EXISTS 可以避免在视图不存在时出现错误。

3.6.4查询视图

查询视图与查询普通表相同，使用 SELECT 语句：

SELECT * FROM view_name;

3.6.5更新视图

更新视图通常指的是通过视图来插入、更新、删除表中的数据。但需要注意的是，并不是所有视图都可以更新。可更新的视图需要满足一定的条件，例如视图和基础表的行列之间存在一一对应关系。
更新视图的语法示例：

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

插入数据到视图：

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

删除视图中的数据：

DELETE FROM view_name
WHERE condition;

3.6.6注意事项

  • 视图的更新操作实际上是对基础表的更新。
  • 更新视图时，需要确保更新操作在视图的权限范围内。
  • 视图的创建和删除只影响视图本身，不影响对应的基本表 。

标签：JOIN,name,column,视图,查询,关系数据库,SQL,数据库系统,SELECT
From： https://www.cnblogs.com/chengfushi/p/18473869

相关文章

  • sql-function
    在SQLite中，除了LENGTH函数，还有许多其他内置函数，分类如下：字符串函数UPPER(string):将字符串转换为大写。LOWER(string):将字符串转换为小写。SUBSTR(string,start,length):返回从指定位置开始的子字符串。TRIM(string):删除字符串两端的空白字符。REPLACE(string,old......
  • MySQL基础函数的学习要点和步骤
    MySQL基础函数的学习要点和步骤可以归纳为以下几个方面：学习要点1.函数的概念：  -函数是一段可以直接被另一段程序调用的程序或代码。2.分类理解：  -字符串函数：如`CONCAT()`,`LOWER()`,`UPPER()`,`LPAD()`,`RPAD()`,`TRIM()`,`SUBSTRING()`等，用于处理字符串......
  • 震惊！MySQL竟然可以平滑从5.7.35升级到8.0.37
    一、前言好多网友问我有没有mysql升级的文章，今天它来了！数据库版本操作系统版本5.7.35CentOSLinuxrelease7.9.20098.0.37CentOSLinuxrelease7.9.2009二、升级方法选择in-place升级，顾名思义就是关闭现有版本MySQL，将二进制包替换成新版本并在现有数据目录上启动MySQL......
  • pg小工：pgsql的安装
    pg小工：pgsql的安装   安装系统：Centos7release7.5(Final) 三种安装方式1.二进制包安装，EnterpriseDB提供了两种PostgreSQL的二进制包，两种都无须编译安装，并且只提供到PostgreSQL10.X版本第一种：傻瓜向导二进制包下载地址：https://www.enterprisedb.com/downloads/pos......
  • MySQL数据库在Windows环境的配置
      本文介绍在Windows电脑中，下载、部署MySQL数据库的方法。  MySQL数据库可以说是最为常用的数据库之一了，在GIS领域中其也经常被用到。之前我们介绍过Redis、PostgreSQL、InfluxDB等多种数据库在Windows电脑中的下载、安装与运行方法，这里就再介绍一下MySQL数据库的配置......
  • 【重学 MySQL】七十、揭秘数据库对象，打造高效数据管理之旅
    【重学MySQL】七十、揭秘数据库对象，打造高效数据管理之旅数据库（Database）数据表（Table）视图（View）存储过程（StoredProcedure）触发器（Trigger）索引（Index）在MySQL中，数据库对象是用于存储、管理和操作数据的实体。这些对象在数据库设计中扮演着至关重要的角色，它们共同......
  • 【重学 MySQL】七十一、揭秘数据库魔法——深入探索并引入视图
    【重学MySQL】七十一、揭秘数据库魔法——深入探索并引入视图视图的定义视图的作用视图的注意事项在MySQL数据库中，视图（View）是一种非常强大且灵活的工具，它为用户提供了以更安全、更清晰的方式查看和管理数据的途径。视图的定义视图是一种虚拟表，其内容由S......
  • Ansible操作MySQL常用的几个模块
    1. mysql_user模块mysql_user模块用来添加，删除用户以及设置用户权限创建MySQL数据库的用户与口令（非root@localhost用户），直接通过playbooks中的案例来说明吧。-name:创建MySQL数据库用户--user_testmysql_user:#-----登陆数据库login_host:"localhost"......
  • mysql explain
    参考：[MySQL高级EXPLAIN用法和结果分析](https://blog.csdn.net/why15732625998/article/details/80388236)explain分析表的读取顺序数据读取操作的操作类型哪些索引可以使用哪些索引被实际使用表之间的引用每张表有多少行被优化器查询examplesqlexplainSELECTcoun......
  • 后端程序员必备：15个MySQL表设计的经验准则
    15个MySQL表设计的经验准则在数据库设计中，MySQL表的设计至关重要。一个良好的表设计可以提高数据库的性能、可维护性和数据的准确性。以下是15个MySQL表设计的经验准则，希望能为你的数据库设计提供有益的指导。一、通用字段的设置在设计表时，尽量包含以下几个通用......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99