SQL-基础-CSDN博客

本文链接：https://blog.csdn.net/weixin_45619069/article/details/130876674

SQL 基础

一、创建表

SQL不仅用于表数据操纵，而且还用来执行数据库和表的所有操作，包括表本身的创建和处理。

一般有两种创建表的方法：
❑ 多数DBMS都具有交互式创建和管理数据库表的工具；
❑ 表也可以直接用SQL语句操纵。
用程序创建表，可以使用SQL的CREATE TABLE语句。需要注意的是，使用交互式工具时实际上就是使用SQL语句。这些语句不是用户编写的，界面工具会自动生成并执行相应的SQL语句（更改已有的表时也是这样）。

1.1 表创建基础

利用CREATE TABLE创建表，必须给出下列信息：
❑ 新表的名字，在关键字CREATE TABLE之后给出；
❑ 表列的名字和定义，用逗号分隔；
❑ 有的DBMS还要求指定表的位置。

语法：

CREATE TABLE table_name
(
     column_name1 data_type(size),
     column_name2 data_type(size),
     column_name3 data_type(size),
     ....
);

column_name 参数规定表中列的名称。
data_type 参数规定列的数据类型（例如 varchar、integer、decimal、date 等等）。
size 参数规定表中列的最大长度。

实例：

CREATE TABLE `program` (
    `id` int NOT NULL AUTO_INCREMENT,  --序号
    `language` varchar(255) DEFAULT NULL,  --语言
    `data` date DEFAULT NULL,   --日期
    `identity` varchar(255) DEFAULT NULL, --身份
    `tilong` varchar(100) DEFAULT NULL,  --使用时长
    `source` varchar(255) DEFAULT NULL,  --来源
    `content` varchar(255) DEFAULT NULL,  --备注
    PRIMARY KEY (`id`)  --主键
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb3;

1.2 SQL 约束

SQL 约束用于规定表中的数据规则。
如果存在违反约束的数据行为，行为会被约束终止。
约束可以在创建表时规定（通过 CREATE TABLE 语句），或者在表创建之后规定（通过 ALTER TABLE 语句）。

❑ NOT NULL - 指示某列不能存储 NULL 值。
❑ UNIQUE - 保证某列的每行必须有唯一的值。
❑ PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
❑ FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。
❑ CHECK - 保证列中的值符合指定的条件。
❑ DEFAULT - 规定没有给列赋值时的默认值。

1.3 如何获取当前日期

各个数据库获取当前日期的函数不一致，比如【Oracle】，获取当前日期的函数是【sysdate】，以下是各个数据库获取当前日期的函数：

获得系统日期
在这里插入图片描述

二、更新表

更新表，我们使用【alter table】
使用ALTER TABLE时需要考虑的事情。
❑ 理想情况下，不要在表中包含数据时对其进行更新。应该在表的设计过程中充分考虑未来可能的需求，避免今后对表的结构做大改动。
❑ 所有的DBMS都允许给现有的表增加列，不过对所增加列的数据类型（以及NULL和DEFAULT的使用）有所限制。
❑ 许多DBMS不允许删除或更改表中的列。
❑ 多数DBMS允许重新命名表中的列。
❑ 许多DBMS限制对已经填有数据的列进行更改，对未填有数据的列几乎没有限制。

使用ALTER TABLE更改表结构，必须给出下面的信息：
❑ 在ALTER TABLE之后给出要更改的表名（该表必须存在，否则将出错）；
❑ 列出要做哪些更改。

【ALTER TABLE 语法】
①向表中加入一列

    alter table table_name add column_name datatype;

②删除表中的列（由于约束，某一些是不允许删除的）

alter table table_name drop column column_name;

③要改变表中列的数据类型：

SQL Server / MS Access：

alter table table_name
alter column column_name datatype

My SQL / Oracle：

alter table table_name
modify column column_name datatype

Oracle 10G 之后版本:

alter table table_name
modify column_name datatype;

复杂的表结构更改一般需要手动删除过程，它涉及以下步骤：
(1) 用新的列布局创建一个新表；
(2) 使用INSERT SELECT语句（insert select 的介绍）从旧表复制数据到新表。有必要的话，可以使用转换函数和计算字段；
(3) 检验包含所需数据的新表；
(4) 重命名旧表（如果确定，可以删除它）；
(5) 用旧表原来的名字重命名新表；
(6) 根据需要，重新创建触发器、存储过程、索引和外键。

注意：
小心使用ALTER TABLE使用ALTER TABLE要极为小心，应该在进行改动前做完整的备份（表结构和数据的备份）。数据库表的更改不能撤销，如果增加了不需要的列，也许无法删除它们。类似地，如果删除了不应该删除的列，可能会丢失该列中的所有数据。

三、删除表

DROP TABLE 语句用于删除表。

DROP TABLE table_name

仅仅需要删除表内的数据，但并不删除表本身

TRUNCATE TABLE table_name

四、视图

视图是基于 SQL 语句的结果集的可视化的表。
视图是虚拟的表。与包含数据的表不一样，视图只包含使用时动态检索数据的查询。

为什么要使用视图？
❑ 重用SQL语句。
❑ 简化复杂的SQL操作。在编写查询后，可以方便地重用它而不必知道其基本查询细节。
❑ 使用表的一部分而不是整个表。
❑ 保护数据。可以授予用户访问表的特定部分的权限，而不是整个表的访问权限。
❑ 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。创建视图之后，可以用与表基本相同的方式使用它们。可以对视图执行SELECT操作，过滤和排序数据，将视图联结到其他视图或表，甚至添加和更新数据。

重要的是，要知道视图仅仅是用来查看存储在别处数据的一种设施。视图本身不包含数据，因此返回的数据是从其他表中检索出来的。在添加或更改这些表中的数据时，视图将返回改变过的数据。

注意：性能问题
因为视图不包含数据，所以每次使用视图时，都必须处理查询执行时需要的所有检索。如果你用多个联结和过滤创建了复杂的视图或者嵌套了视图，性能可能会下降得很厉害。

SQL CREATE VIEW 语法

   CREATE VIEW view_name AS
   SELECT column_name(s)
   FROM table_name
   WHERE condition

视图的规则和限制
❑ 与表一样，视图必须唯一命名（不能给视图取与别的视图或表相同的名字）。
❑ 对于可以创建的视图数目没有限制。
❑ 创建视图，必须具有足够的访问权限。这些权限通常由数据库管理人员授予。
❑ 视图可以嵌套，即可以利用从其他视图中检索数据的查询来构造视图。所允许的嵌套层数在不同的DBMS中有所不同（嵌套视图可能会严重降低查询的性能，因此在产品环境中使用之前，应该对其进行全面测试）。
❑ 许多DBMS禁止在视图查询中使用ORDER BY子句。
❑ 有些DBMS要求对返回的所有列进行命名，如果列是计算字段，则需要使用别名。
❑ 视图不能索引，也不能有关联的触发器或默认值。
❑ 有些DBMS把视图作为只读的查询，这表示可以从视图检索数据，但不能将数据写回底层表。
❑ 有些DBMS允许创建这样的视图，它不能进行导致行不再属于视图的插入或更新。

SQL实例

create view luck.v_item_price as
   select 
       a.item_code code,
       a.item_name name,
       (case when a.is_enable = 1 then '启用'
         else
           '停用'
           end) enable,
       b.item_unit unit,
       b.item_price price,
       b.latest_enable_time latest_time
    from luck.item_list a
    left join luck.price_list b on a.item_code = b.item_code;

运行结果

、、、、

	code	name	enable	unit	price	latest_time
1	310206007a	低钠试验	启用	每项目	25.00	2021/5/30 4:41:19
2	310300004	视网膜视力检查	启用	次	5.00	2021/5/28 4:41:19
3	311400006	皮损取材检查	启用	次	30.00	2021/5/28 4:41:19
4	311201023a	产前检查	启用	次	12.00	2021/5/29 4:41:19

讲解
创建一个视图【luck.v_item_price】，通过表【luck.item_list】左联结表【luck.price_list】然后通过字段【item_code】进行关联，进而获取两个表中的数据；

SQL 更新视图
语法

CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

SQL Server

ALTER VIEW [ schema_name . ] view_name [ ( column [ ,...n ] ) ] 
[ WITH <view_attribute> [ ,...n ] ] 
AS select_statement 
[ WITH CHECK OPTION ] [ ; ]

<view_attribute> ::= 
{ 
    [ ENCRYPTION ]
    [ SCHEMABINDING ]
    [ VIEW_METADATA ]     
}

字段释意

schema_name: 视图所属架构的名称。

view_name: 要更改的视图。

column: 将成为指定视图的一部分的一个或多个列的名称（以逗号分隔）。

3.SQL 撤销视图
DROP VIEW 语法

DROP VIEW view_name

五、存储过程

5.1 释义

存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来调用存储过程。

简单的说就是专门干一件事一段sql语句。

可以由数据库自己去调用，也可以由java程序去调用。

在oracle数据库中存储过程是【procedure】。

5.2 为什么要使用存储过程

❑ 通过把处理封装在一个易用的单元中，可以简化复杂的操作（如前面例子所述）。
❑ 由于不要求反复建立一系列处理步骤，因而保证了数据的一致性。如果所有开发人员和应用程序都使用同一存储过程，则所使用的代码都是相同的。
❑ 上一点的延伸就是防止错误。需要执行的步骤越多，出错的可能性就越大。防止错误保证了数据的一致性。
❑ 简化对变动的管理。如果表名、列名或业务逻辑（或别的内容）有变化，那么只需要更改存储过程的代码。使用它的人员甚至不需要知道这些变化。
❑ 上一点的延伸就是安全性。通过存储过程限制对基础数据的访问，减少了数据讹误（无意识的或别的原因所导致的数据讹误）的机会。
❑ 因为存储过程通常以编译过的形式存储，所以DBMS处理命令所需的工作量少，提高了性能。
❑ 存在一些只能用在单个请求中的SQL元素和特性，存储过程可以使用它们来编写功能更强更灵活的代码。

总结
使用存储过程有三个主要的好处，即简单、安全、高性能。

缺点
存储过程可移植比较差，多种DBMS中，存储过程几乎是不可移植的。

5.3 执行存储过程

存储过程的执行远比编写要频繁得多，因此我们先介绍存储过程的执行。执行存储过程的SQL语句很简单，即EXECUTE。

存储过程的执行选择：
❑ 参数可选，具有不提供参数时的默认值。
❑ 不按次序给出参数，以“参数=值”的方式给出参数值。
❑ 输出参数，允许存储过程在正执行的应用程序中更新所用的参数。
❑ 用SELECT语句检索数据。
❑ 返回代码，允许存储过程返回一个值到正在执行的应用程序。

5.4 创建存储过程

由于知识的局限性，本节知识还需我多实践，后续会更新…

六、事务处理

使用事务处理（transaction processing），通过确保成批的SQL操作要么完全执行，要么完全不执行，来维护数据库的完整性。
利用事务处理，可以保证一组操作不会中途停止，它们要么完全执行，要么完全不执行（除非明确指示）。如果没有错误发生，整组语句提交给（写到）数据库表；如果发生错误，则进行回退（撤销），将数据库恢复到某个已知且安全的状态。

6.1 关键术语

❑ 事务（transaction）指一组SQL语句；
❑ 回退（rollback）指撤销指定SQL语句的过程；
❑ 提交（commit）指将未存储的SQL语句结果写入数据库表；
❑ 保留点（savepoint）指事务处理中设置的临时占位符（placeholder），可以对它发布回退（与回退整个事务处理不同）。

提示：可以回退哪些语句？

事务处理用来管理INSERT、UPDATE和DELETE语句。不能回退SELECT语句（回退SELECT语句也没有必要），也不能回退CREATE或DROP操作。事务处理中可以使用这些语句，但进行回退时，这些操作也不撤销。

6.2 控制事务处理

有的DBMS要求明确标识事务处理块的开始和结束。

SQL Server：

    BEGIN TRANSACTION
    ...
    COMMIT TRANSACTION

MariaDB和MySQL中等同的代码为：

    START TRANSACTION
    ...

Oracle使用的语法：

    SET TRANSACTION
    ...

PostgreSQL使用ANSI SQL语法：

    BEGIN
    ...

七、约束

约束（constraint）：管理如何插入或处理数据库数据的规则。

7.1 主键（PRIMARY KEY）

主键是一种特殊的约束，用来保证一列（或一组列）中的值是唯一的，而且永不改动。换句话说，表中的一列（或多个列）的值唯一标识表中的每一行。

表中任意列只要满足以下条件，都可以用于主键。
❑ 任意两行的主键值都不相同。
❑ 每行都具有一个主键值（即列中不允许NULL值）。
❑ 包含主键值的列从不修改或更新。（大多数DBMS不允许这么做，但如果你使用的DBMS允许这样做，好吧，千万别！）
❑ 主键值不能重用。如果从表中删除某一行，其主键值不分配给新行。

7.2 外键（REFERENCES）

外键是表中的一列，其值必须列在另一表的主键中。外键是保证引用完整性的极其重要部分。

7.3 唯一约束（UNIQUE）

唯一约束用来保证一列（或一组列）中的数据是唯一的。它们类似于主键，但存在以下重要区别。
❑ 表可包含多个唯一约束，但每个表只允许一个主键。
❑ 唯一约束列可包含NULL值。
❑ 唯一约束列可修改或更新。
❑ 唯一约束列的值可重复使用。
❑ 与主键不一样，唯一约束不能用来定义外键。

7.4 检查约束

检查约束用来保证一列（或一组列）中的数据满足一组指定的条件。检查约束的常见用途有以下几点。
❑ 检查最小或最大值。例如，防止0个物品的订单（即使0是合法的数）。
❑ 指定范围。例如，保证发货日期大于等于今天的日期，但不超过今天起一年后的日期。
❑ 只允许特定的值。例如，在性别字段中只允许M或F。

八、索引

索引用来排序数据以加快搜索和排序操作的速度。

8.1 创建索引

在开始创建索引前，应该记住以下内容。
❑ 索引改善检索操作的性能，但降低了数据插入、修改和删除的性能。在执行这些操作时，DBMS必须动态地更新索引。
❑ 索引数据可能要占用大量的存储空间。
❑ 并非所有数据都适合做索引。取值不多的数据（如州）不如具有更多可能值的数据（如姓或名），能通过索引得到那么多的好处。 ❑ 索引用于数据过滤和数据排序。如果你经常以某种特定的顺序排序数据，则该数据可能适合做索引。
❑ 可以在索引中定义多个列（例如，州加上城市）。这样的索引仅在以州加城市的顺序排序时有用。如果想按城市排序，则这种索引没有用处。

语法

    CREATE INDEX 索引名
    ON 表名(字段名或者列名);
    
    -----------------实例-----------
    CREATE INDEX prod_name_ind
    ON Products (prod_name);

分析
索引必须唯一命名。这里的索引名prod_name_ind在关键字CREATE INDEX之后定义。ON用来指定被索引的表，而索引中包含的列（此例中仅有一列）在表名后的圆括号中给出。

九、触发器

触发器是特殊的存储过程，它在特定的数据库活动发生时自动执行。触发器可以与特定表上的INSERT、UPDATE和DELETE操作（或组合）相关联。

触发器内的代码具有以下数据的访问权：
❑ INSERT操作中的所有新数据；
❑ UPDATE操作中的所有新数据和旧数据；
❑ DELETE操作中删除的数据。

9.1 触发器的主要用途

❑ 保证数据一致。
❑ 基于某个表的变动在其他表上执行活动。例如，每当更新或删除一行时将审计跟踪记录写入某个日志表。
❑ 进行额外的验证并根据需要回退数据。例如，保证某个顾客的可用资金不超限定，如果已经超出，则阻塞插入。
❑ 计算计算列的值或更新时间戳。

提示：约束比触发器更快

十、数据库安全

任何安全系统的基础都是用户授权和身份确认。

10.1 保护机制

一般说来，需要保护的操作有：
❑ 对数据库管理功能（创建表、更改或删除已存在的表等）的访问；
❑ 对特定数据库或表的访问；
❑ 访问的类型（只读、对特定列的访问等）；
❑ 仅通过视图或存储过程对表进行访问；
❑ 创建多层次的安全措施，从而允许多种基于登录的访问和控制；
❑ 限制管理用户账号的能力。

安全性使用SQL的GRANT和REVOKE语句来管理，不过，大多数DBMS提供了交互式的管理实用程序，这些实用程序在内部使用GRANT和REVOKE语句。