(1) SQL database architecture
1. Features of SQL
- Comprehensive unity
- Highly non-procedural
- Collection-oriented operation
- Two ways to use
- Concise language, easy to learn and use
2. SQL supports three-level mode structure
SQLThe language supports the three-level schema structure of the relational database, in which the view corresponds to the external schema, the basic table corresponds to the schema, and the storage file corresponds to the internal schema. As shown below:
(Two) the basic composition of SQL
- Data definition language
- Interactive data manipulation language
- Transaction control
- authority management
(Three) SQL data definition
1. Create a table
CREATE TABLE <表名>(<列名><数据类型>[列级完整性约束条件] [,<列名><数据类型>[列级完整性约束条件]]... [,<表级完整性约束条件>] );
Column-level integrity constraints include
UNIQUE(unique value). For example, it
NOT NULL UNIQUEmeans that the value is unique and cannot be null.
2. Modify and delete tables
(1) Modify the table
ALTER TABLE <表名>[ADD <新列名><数据类型>[完整性约束条件]] [DROP <完整性约束名>] [MODIFY <列名><数据类型>];
(2) Delete the table
DROP TABLE <表名>;
3. Index creation and deletion
In the database, the index enables the database program to find the required data without scanning the entire table.
An index in a database is a collection of one or several column values in a table and a corresponding list of logical pointers to data pages that physically identify these values in the table.
The role of the index:
- By creating a unique index, the uniqueness of data records can be guaranteed
- Can greatly speed up data retrieval
- Can speed up the connection between the table and the table, which is of special significance in achieving the referential integrity of the data
- When using
GROUP BYsub-statements to retrieve data, you can significantly reduce the time of grouping and sorting in the query
- Use retrieval can use optimized hiders in the process of retrieving data to improve system performance
Indexes are divided into clustered indexes and non-clustered indexes. A clustered index refers to an index whose order of index items in the index table is consistent with the physical order of records in the table.
(1) Create an index
CREATE [UNIQUE] [CLUSTER] INDEX <索引名> ON <表名>(<列名>[<次序>][,<列名>[<次序>]]...);
- Order: Optional
ASC(ascending order) or
DSC(descending order), the default value is
UNIQUE: Indicates that each index value of this index only corresponds to a unique data record
CLUSTER: Indicates the clustered index of the index to be established, meaning that the order of the index items is the index organization consistent with the physical order of the records in the table
(2) Delete the index
DROP INDEX <索引名>
4. View creation and deletion
A view is a table derived from one or more basic tables or views, and its structure and data are based on queries to the table.
Like a real table, the view also includes several defined data columns and multiple data rows, but in essence, these data columns and data rows are derived from the table it refers to. Therefore, the view is not a real basic table, but a virtual table. The data corresponding to the view is not actually stored in the database in the view structure, but is stored in the table referenced by the view.
Advantages and functions:
- The view can be made to centralize data, simplify and customize the different data requirements of different users on the database
- The use of views can shield the complexity of data, users can easily use and manage data without having to understand the structure of the database, simplify data authority management and reorganize data for output to other applications
- The view allows the user to only care about certain specific data that he is interested in and the specific tasks they are responsible for, while those unnecessary or useless data are not displayed in the view
- The view greatly simplifies the user's manipulation of the data
- Views allow different users to see different or the same data sets in different ways
- In some cases, because the amount of data in the table is too large, the table is often divided horizontally or vertically when designing the table, so as to prevent the change of the table structure from having an adverse effect on the application.
- View provides a simple and effective security mechanism
(1) Creation of view
CREATE VIEW 视图名（列表名） AS SELECT 查询子句 [WITH CHECK OPTION];
- Subqueries can be arbitrarily complex
ORDER BYclauses and
DISTINCTphrases are usually not allowed
WITH CHECK OPTIONIndicates
UPDATE、INSERT、DELETEthat the updated, inserted or deleted row is guaranteed to meet the predicate condition in the view definition (that is, the conditional expression in the subquery) during the operation
- The names of the attribute columns that make up the view are either omitted or all specified
(2) Delete view
DROP VIEW 视图名
(4) SQL data query
1. Basic structure of SELECT
SELECT [ALL|DISTINCT] <目标列表达式> [,<目标列表达式>]... FROM <表名或视图名> [,<表名或视图名>] [WHERE <条件表达式>] [GROUP BY <列名 1> [HAVING <条件表达式>]] [ORDER BY <列名 2> [ASC|DESC]...]
SELECTThe clause corresponds to the projection operation in relational algebra, which is used to list the attributes in the query result
FROMThe clause corresponds to the Cartesian product in relational algebra. It lists the relationships that need to be scanned during the evaluation of the expression, that is
FROM, when multiple basic tables or views appear in the clause, the system first performs the Cartesian product operation
WHEREThe clause corresponds to the selection predicate in relational algebra
2. Simple query
Simple query only requires three reserved words, namely
3. Connect query
If the query involves more than two tables, it is called a join query.
4. Subqueries and aggregate functions
Subqueries are also called nested queries. Nested query means that one
SELECT-FROM-WHEREquery block can be embedded in another query block.
An aggregate function is a function that takes a set of values as input and returns a single value.
- average value
5. Group query
GROUP BYcan be added after the clause to group the tuples, and the reserved words
GROUP BYare followed by a list of grouping attributes. The simplest case is that there is
FROMonly one relationship after the clause, and its tuples are grouped according to the grouping attribute.
If the tuple is restricted in a certain way before grouping, so that the unnecessary grouping is empty,
GROUP BYjust follow the
HAVINGclause with a clause.
6. Rename operation
ASto achieve, for
old-name AS new -nameexample: .
ASClauses can appear in
SELECTclauses or in
7. String manipulation
The most common operation performed on strings
LIKEis pattern matching using operators . Two special characters are used to describe the pattern:
%Match any string
_Match any character
8. View query
When querying the view table, the system first fetches the definition of the view from the data dictionary, and then combines the query statement in the definition with the query statement for the view to form a revised query statement.
(5) SQL data update
INSERT INTO 基本表名（字段名[,字段名]...） VALUES(常量[,常量]...); 或 INSERT INTO 基本表名（列表名） SELECT 查询语句;
DELETE FROM 基本表名 [WHERE 条件表达式]
UPDATE 基本表名 SET 列名=值表达式(,列名=值表达式...) [WHERE 条件表达式];
(6) SQL access control
Data control controls the user's right to store data.
1. Authorized statement format
GRANT <权限>[,<权限>]... [ON <对象类型> <对象名>] TO <用户> [,<用户>]... [WITH GRANT OPTION];
Different types of operation objects have different operation rights. Common operation rights are shown in the following figure:
2. The format of the revocation permission statement
REVOKE <权限> [,<权限>]... [ON <对象类型> <对象名>] FROM <用户> [,<用户>]...;
(7) Embedded SQL
The pre-compiled method is usually used to recognize
SQLsentences embedded in high-level languages .
SQLThe communication between embedded and host language usually has the following three ways:
SQLThe communication area transmits
SQLthe status information of the statement execution to the host language , so that the host language can control the program flow based on this information
- The main variable is also called a shared variable. The main language
SQLprovides parameters to the statement mainly through the main variable, which is defined by the program of the main language and explained by
DECLAREstatement. When quoting, in order to
SQLdistinguish it from the attribute name, you need to add ":" before the main variable
- The cursor
SQLlanguage is set-oriented, a
SQLstatement can generate or process polymorphic records, while the main language is record-oriented, and a set of host variables can only hold one record at a time. Therefore, the introduction of a cursor, by moving the cursor pointer to determine which record to obtain