设计数据库之内部模式：SQL基本操作

Chapter4：设计数据库之内部模式：SQL基本操作

笔记来源：
1.《漫画数据库》—科学出版社
2.SQL | DDL, DQL, DML, DCL and TCL Commands

设计数据库的步骤：
概念模式

概念模式(conceptual schema)是指将现实世界模型化的阶段进而，是确定数据库理论结构的阶段。
概念模式的设计是通过E-R模型把握现实世界，进而规范化表格来实现的。

内部模式

内部模式是从计算机内部看到的数据库，是确定数据库物理构造的阶段。
内部模式的设计通过设计数据库高速检索方法来实现。

外部模式

外部模式是从用户和应用的角度来观察的数据库。
外部模式的设计是通过设计应用程序所必要的数据来实现的。

我们使用SQL能干什么？

使用数据库必须将数据录入并调取必要数据，因此我们要学习SQL，我们使用该语言与数据库进行对话

SQL命令分类
这些命令有些用于数据库本身，有些数据库中的对象，有些二者皆可（例如CREATE既可用于创建数据库又可用于创建表）

数据定义语言（DDL：Data Definition Language）
CREATE：该命令用于创建数据库或其对象(如表、索引、函数、视图、存储过程和触发器)。
DROP：该命令用于删除数据库中的对象。
ALTER：用于修改数据库的结构。
TRUNCATE：用于从表中删除所有记录，包括为这些记录分配的所有空间。
COMMENT：用于向数据字典中添加注释。
RENAME：用于重命名数据库中存在的对象。

数据查询语言（DQL：Data Query Language）
SELECT：用于从数据库中检索数据。

数据操作语言（DML：Data Manipulation Language）
INSERT：用于向表中插入数据。
UPDATE：用于更新表中的现有数据。
DELETE：用于删除数据库表中的记录。
LOCK：表控制并发性。
CALL：调用PL/SQL或JAVA子程序。
EXPLAIN PLAN：描述数据的访问路径。

数据控制语言（DCL：Data Control Language）
GRANT：这个命令赋予用户访问数据库的权限。
REVOKE：该命令撤销使用GRANT命令赋予用户的访问权限。

事务控制语言（TCL：Transaction Control Language）
BEGIN：打开一个事务。
COMMIT：提交事务。
ROLLBACK：在发生任何错误时回滚事务。
SAVEPOINT：在事务中设置保存点。

具体用法查询入口：SQL Tutorial

5.1 Create Database in SQL（关于数据库）

关于数据库的创建、选择、重命名、删除的操作

SQL CREATE DATABASE Statement（创建数据库语句）

CREATE DATABASE database_name;

SQL Drop Database（删除数据库）

DROP DATABASE database_name;

SQL Rename Database（重命名数据库）

Rename database

SQL Select database（选择数据库）

USE DATABASE_NAME;

5.2 Tables in SQL（关于表）

关于表的创建、删除、重命名、清空的操作

SQL CREATE TABLE（创建表）

CREATE TABLE table_name

SQL Drop Table Statement（删除表语句）

DROP TABLE table_name;

SQL DELETE Statement（删除语句）

DELETE FROM table_name WHERE some_condition;
注：Some_condition: condition to choose a particular record.（记录，即一行）

ALTER(RENAME) in SQL（重命名）

ALTER TABLE table_name RENAME COLUMN old_name TO new_name;
注：仅在MySQL, Oracle中适用

DROP and TRUNCATE in SQL（删除和清空表）

DROP TABLE table_name;
TRUNCATE TABLE table_name;

SQL Query to Copy,Duplicate or Backup Table（复制备份表）
通过备份表来追踪数据变换

选择几列来备份
CREATE TABLE Table_Name AS SELECT col_1, col_2, … FROM Source_Table_Name;
Table_Name: The name of the backup table.
AS: Aliasing
col: Required columns from source table

SQL ALTER TABLE-ADD,DROP,MODIFY（添加、删除、修改表）

添加一个记录（一行）
ALTER TABLE table_name ADD (Columnname_1 datatype,Columnname_2 datatype, …Columnname_n datatype);

5.3 SQL Queries（查询）

SQL中的查询语句用于从数据库中检索或获取数据。

SQL SELECT Query（选择查询）
SOL-TOP,LIMIT,FETCH FIRST Clause（限制查询返回的行数）
SQL-SELECT FIRST（只选择第一行）
SQL-SELECT LAST（只选择最后一行）
SQL-SELECT RANDOM（返回任意一行）
SQL-SELECT IN（返回与列表或子查询（嵌套查询）中的值匹配的值）
SQL-SELECT DATE（从与日期相关的表中获取数据）
SQL Query to Insert Multiple Rows（插入多行）
SQL INSERT INTO Statement（插入）
SQL UPDATE Statement（更新）
SQL DELETE Statement（删除）
SQL Query to Delete Duplicate Rows（删除重复行）

5.4 SQL Clauses（条件）

SQL CLAUSE通过为 SQL 查询提供条件来帮助限制结果集。 CLAUSE有助于从整个记录集中过滤行。

SQL WHERE Clause（根据特定条件获取数据）
SQL WITH clause（给子查询块一个名称）
SQL HAVING Clause （基于聚合函数和分组的查询结果过滤）
ORDER BY in SQL（对获取的数据进行排序）
SQL GROUP BY（在一些函数的帮助下，将相同的数据分组）
SQL LIMIT Clause（若有大量元组满足查询条件，一次只查看少数元组）

5.5 SQL Operators（操作符）

提供算术运算符、比较运算符、逻辑运算符、位运算符

算术运算符可对表中数据进行运算
比较运算符可以判断表中的哪些记录是符合条件的。
逻辑运算符用来判断表达式的真假。
位运算符是在二进制数上进行计算的运算符。位运算会先将操作数变成二进制数，进行位运算。然后再将计算结果从二进制数变回十进制数。

SQL IN Operator（基于特定值的列表筛选数据）
SQL NOT Operator（查询时排除）
SQL NOT EQUAL Operator（检查两个表达式是否不同/不相等）
SQL IS NULL Operator（检查列中缺省值）
SQL UNION Operator（查找结果集或两个或多个表的组合）
SQL UNION ALL（合并两个或多个SELECT语句的结果）
SQL Except Clause（第一个SELECT操作返回，而第二个SELECT操作不返回）
SQL Between（测试表达式是否在值范围内）
SQL ALL and ANY（选择select语句的所有元组）
SQL INTERSECT Clause（只返回两个SELECT语句共有的行）
SQL EXISTS（检查相关嵌套查询的结果是否为空(不包含元组)）
SQL CASE Statement（根据不同的条件有条件地赋值）

5.6 SQL Aggregate Functions（聚合函数）

聚合函数就是将一组数据进行统一计算

COUNT() 符合指定条件的行数
AVG() 数值列的平均值
SUM()返回所有或不同值的总和
MIN()返回所选列的最小值
MAX()返回所选列的最大值

5.7 SQL Data Constraints（数据约束）

数据约束是用来为表中的某一列或多列设置规则的，以确保数据的完整性和准确性。这些规则确保了数据的一致性、有效性和可靠性。如果尝试插入、更新或删除违反这些规则的数据，数据库系统将抛出错误并拒绝执行这些操作。

SQL NOT NULL Constraint（强制特定的值或记录不应该保留空值）
SQL UNIQUE Constraint（检查子查询的结果中是否有重复的元组）
Primary key constraint in SQL（唯一标识表中的每条记录）
Foreign Key constraint in SQL（防止会破坏表间链接的操作）
Composite Key in SQL（多个主键称为组合键）
SQL-ALTERNATE KEY（候选键）
SQL CHECK Constraint（限制了列在关系中可以包含的值）
SQL DEFAULT Constraint（用默认值和固定值填充列）

5.8 SQL Joining Data（连接数据）

将两个或多个表连接到一个表中

以相同意义的列为媒介，连接成表格，称作同等连接(equi join)
我们称将重复的列整合成一列的连接叫做自然连接(natural join)

仅选择数值相同的行进行连接，我们称之为内部连接(inner join)
全部保留任何一方表格的所有行，将一方中没有的行设置为空值的连接方法称为外部连接(outer join)
两个表格在SQL命令中分左右时，根据保留全部表格行的情况，分为左外部连接(left outer join)和右外部连接(right outer join)

内部连接：两张表取交集	外部连接：表A与表B的并集
右外部连接：表B+表A与B共有部分	左外部连接：表A+表A与B共有部分

SQL Outer Join（左外部连接+右外部连接+全外部连接）
SQL Left Join（左外部连接）
SQL Right Join（右外部连接）
SQL FULL JOIN（全外部连接）

SQL CROSS JOIN（笛卡尔连接）

SQL Self Join（两张表结构和数据内容完全一样的表，在做数据处理的时候，我们通常会给它们分别重命名来加以区分，然后进行关联）
SQL UPDATE with JOIN（使用另一个表和连接条件更新一个表）
SQL DELETE JOIN（允许基于涉及另一个表的条件删除表的行）
Recursive Join in SQL（允许我们查询分层数据）

5.9 SQL Functions（函数）

函数提供许多常用功能

SQL Date functions（NOW() 返回当前日期和时间 ）
SQL String functions（对输入字符串执行操作并返回输出字符串）
SQL Numeric Functions（对数字进行操作并返回数字）
SQL-Statistical Functions（对数字数据进行统计计算）
Working With JSON in SQL（存储和传输数据）
Conversion Function in SQL（当您定义表达式和局部变量时，您应该指定将在这些对象中存储什么类型的数据）
SQL Data Types（数据类型）
SQL LTRIM()Function（一个字符串函数，用于从字符串或字符串表达式的左侧删除前导空格）
SQL RTRIM()Function（从字符或字符串右侧删除空格的字符串函数）
SQL UPPER()Function（将表中的所有字符串转换为大写的字符串函数）

5.10 SQL Views（视图）

当用户对数据库中的一张或者多张表的某些字段的组合感兴趣，而又不想每次键入这些查询时，用户就可以定义一个视图，以便解决这个问题。视图中列可以来自于表里的不同列，这些列都是用户所感兴趣的数据列

视图与表不同，它在物理上不是真实存在的，而是一个虚表。
在数据库里仅存放视图的定义，而不存放视图对应的数据。
视图中的这些数据存放在其对应的表中，如果表中的数据发生了变化，从视图中查询出的数据也会随之发生改变。从这个意义来看，视图就像一个窗口，透过它可以看到数据库中用户感兴趣的数据及变化。—引自：数据库中的视图有什么用，我们为什么要用视图？

CREATE VIEW view_name AS
SELECT column1, column2…
FROM table_name
WHERE condition;
备注：view_name: Name for the View
table_name: Name of the table
condition: Condition to select rows

5.11 SQL Indexes（索引）

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。
索引是一种数据结构。数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种B+树。
更通俗的说，索引就相当于目录。为了方便查找书中的内容，通过对内容建立索引形成目录。索引是一个文件，它是要占据物理空间的。—引自：sql数据库中什么情况该加索引index？

SQL CREATE INDEX Statement（创建索引）
SQL DROP INDEX Statement（删除索引）
Difference Between Clustered and Non-Clustered Index（聚集索引根据数据行的键值在表或视图中排序和存储这些数据行。 这些键值是索引定义中包含的列。 每个表只能有一个聚集索引，因为数据行本身只能按一个顺序存储。 只有当表包含聚集索引时，表中的数据行才按排序顺序存储。 如果表具有聚集索引，则该表称为聚集表。 如果表没有聚集索引，则其数据行存储在一个称为堆的无序结构中）