Software Designer Exam | Chapter 9 Basics of Database Technology | Introduction to Relational Database SQL Language

(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:

Three-level schema structure of relational database

(Two) the basic composition of SQL

  • Data definition language
  • Interactive data manipulation language
  • Transaction control
  • Embedded SQLand dynamicSQL
  • Completeness
  • authority management

(Three) SQL data definition

1. Create a table

Statement format:

CREATE TABLE <表名>(<列名><数据类型>[列级完整性约束条件]
[,<列名><数据类型>[列级完整性约束条件]]...
[,<表级完整性约束条件>]
);

Column-level integrity constraints include NULL(empty) and 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

Statement format:

ALTER TABLE <表名>[ADD <新列名><数据类型>[完整性约束条件]]
[DROP <完整性约束名>]
[MODIFY <列名><数据类型>];

(2) Delete the table

Statement format:

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 ORDER BYand 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

Statement format:

CREATE [UNIQUE] [CLUSTER] INDEX <索引名>
ON <表名>(<列名>[<次序>][,<列名>[<次序>]]...);

Parameter Description:

  • Order: Optional ASC(ascending order) or DSC(descending order), the default value isASC
  • 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

Statement format:

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

Statement format:

CREATE VIEW 视图名(列表名)
AS SELECT 查询子句
[WITH CHECK OPTION];

note:

  • Subqueries can be arbitrarily complex SELECTstatements, but 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

Statement format:

DROP VIEW 视图名

(4) SQL data query

1. Basic structure of SELECT

Statement format:

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 SELECT, FROMand WHERE.

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.
include:

  • average valueAVG
  • MinimumMIN
  • MaxMAX
  • SumSUM
  • countCOUNT

5. Group query

  • GROUP BYClause
    A WHEREclause 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.
  • HAVINGClause
    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

By using ASto achieve, for old-name AS new -nameexample: .

ASClauses can appear in SELECTclauses or in FROMclauses.

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

1. Insert

Statement format:

INSERT INTO 基本表名(字段名[,字段名]...)
VALUES(常量[,常量]...);
或
INSERT INTO 基本表名(列表名)
SELECT 查询语句;

2. Delete

Statement format:

DELETE FROM 基本表名
[WHERE 条件表达式]

3. Modification

Statement format:

UPDATE 基本表名
SET 列名=值表达式(,列名=值表达式...)
[WHERE 条件表达式];

(6) SQL access control

Data control controls the user's right to store data.

1. Authorized statement format

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:

Common operating permissions

2. The format of the revocation permission statement

Statement format:

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 SQLthe 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