Structured Query Language(SQL)

Structured Query Language(SQL)

Principal

結構化查詢語言 → 用於與關係數據庫系統交互的計算機語言。

characteristics

• SQL is a non-procedural language.是一种非过程化语言。
• We can without difficulty create and replace databases in SQL. It isn’t a time-consuming process.可以轻松地用 SQL 创建和替换数据库。这不是一个耗时的过程。
• SQL is primarily based totally on ANSI standards.主要完全基于 ANSI 标准。
• SQL does now no longer have a continuation individual.现在不再有延续个体。
• SQL is entered into the SQL buffer on one or more lines.在一行或多行上输入 SQL 缓冲区。
• SQL makes use of a termination individual to execute instructions immediately. It makes use of features to carry out a few formatting.使用终止符来立即执行指令。它利用功能来执行一些格式化。
• It uses functions to perform some formatting.使用函数来执行一些格式化。

Parser 解析器

The parser begins by replacing some of the words in the SQL statement with unique symbols, a process known as tokenization. 用唯一符号替换 SQL 语句中的一些单词 → 標記化

必須是分號;結尾 否則會報錯

Relational Engine 關係引擎

query processor 查詢處理器

develops a strategy for efficiently retrieving, writing, or updating relevant data.开发一种有效检索、写入或更新相关数据的策略

Byte code, an intermediate-level representation of the SQL statement, is used to write the plan 字节码是 SQL 语句的中间级表示，用于编写计划 關係數據庫使用字節代碼

Storage Engine 存储引擎

The software element that interprets the byte code and executes the intended SQL statement is known as the storage engine, also known as the database engine. The data in the database files on the physical disc storage is read and stored. 解释字节码并执行预期 SQL 语句的软件元素称为存储引擎，也称为数据库引擎。 The data in the database files on the physical disc storage is read and stored. 读取并存储物理磁盘存储上的数据库文件中的数据。 存儲引擎在完成後將結果傳遞給請求應用程序

SQL Rules:

• A ‘;’ is used to end SQL statements. “;”用于结束SQL语句。
• Statements may be split across lines, but keywords may not. 语句可以跨行分割，但关键字则不能。
• Identifiers, operator names, and literals are separated by one or more spaces or other delimiters. 标识符、运算符名称和文字由一个或多个空格或其他分隔符分隔。
• A comma (,) separates parameters without a clause. 不带子句的参数之间用逗号 (,) 分隔。
• A space separates a clause. 一个空格分隔子句。
• Reserved words cannot be used as identifiers unless enclosed with double quotes. 保留字不能用作标识符，除非用双引号引起来。
• Identifiers can contain up to 30 characters. 标识符最多可以包含 30 个字符。
• Identifiers must start with an alphabetic character. 标识符必须以字母字符开头。
• Characters and date literals must be enclosed within single quotes. 字符和日期文字必须用单引号引起来。
• Numeric literals can be represented by simple values. 数字文字可以用简单的值表示。
• Comments may be enclosed between /* and / symbols and maybe multi-line. 注释可以包含在 / 和 */ 符号之间，并且可以是多行。

DDL: Data Definition Language

SQL commands used to create the database structure are known as data definition language (DDL).

DML: Data Manipulation Language

A relational database can be updated with new data using data manipulation language (DML) statements.

刪除記錄需要謹慎

DQL: Data Query Language

用于访问关系数据库。软件程序使用 SELECT 命令来过滤并返回 SQL 表中的特定结果。

DCL: Data Control language

Data control language (DCL) is a programming language used by database administrators to control or grant other users access to databases.

SQL 是一種客戶端/服務器語言。 個人計算機程序使用SQL通過網絡與保存共享事實的數據庫服務器進行通信。

SQL is Internet facts access language.

SQL is a distributed database language.Distributed database control structures use SQL to assist distribute facts throughout many linked pc structures. The DBMS software program on every gadget makes use of SQL to speak with the opposite structures, sending requests for facts to get entry to. 分布式数据库控制结构使用 SQL 来帮助在多个链接的计算机结构中分发事实。每个设备上的 DBMS 软件都使用 SQL 与相反的结构进行通信，发送对要访问的数据的请求。

SQL Injection： 防註入

SQL vs NO SQL vs NEW SQL

Operation

Data Type:

• Numeric Datatypes 數字型數據類型

• Date and Time Datatypes 日期和时间数据类型

• String Datatypes 字符串数据类型

• Other Data Types

Operator: 运算符

1. Arithmetic Operators算术运算符

perform mathematical operations on numeric values in queries 在查詢中進行數學運算

2. Comparison Operators 比较运算符

3. Logical Operators

combine or manipulate conditions in SQL queries to retrieve or manipulate data based on specified criteria…

4. Bitwise Operators按位运算符

perform bitwise operations on binary values in SQL queries, manipulating individual bits to perform logical operations at the bit level.在 SQL 查詢中對二進位數值執行位運算，操作個別位以執行位層級的邏輯運算。

5. Compound Operators复合运算符

6. Special Operators

進行一些特定操作，例如比較值，檢查是否存在以及根據特定條件過濾。

Query

SELECT 語句

Operate Database

INSERT INTO

add new rows to a table in a database

two primary syntaxes:

1. only values

   仅指定要插入的数据的值，而不指定列名

   INSERT INTO [table_name]
   VALUES (value1, value2, vlaue3,...),
   			 (value1, value2, vlaue3,...),
   			 ...
   			 (value1, value2, vlaue3,...);
   

2. Column names and values both

   将指定要填充的列及其相应的值

   #一一對應
   INSERT INTO [table_name] (column1, column2, column3,...) 
   VALUES (value1, value2, value3,...),
   			 (value1, value2, value3,...),
   			 (value1, value2, value3,...),
   			 ...
   			 (value1, value2, value3,...); 
   

選擇特定的數據插入形成新的表格

INSERT INTO [new_table_name] 
SELECT [*] 
FROM [second_table]
[WHERE condition];

未包含的列將填充默認值，通常為NULL

UPDATE

UPDATE [table_name]
SET [column1] = value1, 
column2 = value2,… 
WHERE condition;

DELETE

DELETE FROM table_name
WHERE some_condition;

**注意：**我们可以根据 WHERE 子句中提供的条件删除单个或多个记录。如果我们省略 WHERE 子句，那么所有记录将被删除，表将为空

Reference

What is SQL?

SQL Operators - GeeksforGeeks

SQL vs NO SQL vs NEW SQL - GeeksforGeeks

SQL Commands: DDL, DQL, DML, DCL and TCL With Examples

SQL Commands: DDL, DQL, DML, DCL and TCL With Examples

SQL INSERT INTO Statement

SQL INSERT INTO 语句 | 菜鸟教程