Database Chapter 3 relational database standard language SQL

Chapter III relational database standard language SQL

SQL Set data query, data manipulation, data definition and data control functions in one, the main features include:

Integrated (unified language style for each part of the process)

High degree of non-process

Collection-oriented operation mode

Provides multiple uses in the same syntax structure (can be used independently or embedded in high-level languages such as C programs)

Simple language, easy to learn and use (only 9 verbs are used to complete the core function)

SQL typically does not provide the action to modify the schema definition, modify the view definition, and modify the index definition. If you want to modify these objects, you can delete them first and then rebuild them.

SQL also supports database three-level schema structure (external mode, mode and internal mode, mode and internal mode are unique, external mode is not unique)

Defining patterns

Definition, deletion and modification of basic tables

An important concept in the relational model is the domain, where each attribute comes from a domain, and its value must be a value in the domain. The concept of a domain in SQL is implemented with a data type.

Each base table belongs to a pattern, and a pattern contains multiple base tables. There are three ways to define the schema to which it belongs when defining a base table:

If the user creates a base table (and the other database objects are the same) without specifying a pattern, the system determines the schema to which the object belongs based on the search path.

SHOW Search_path; This statement can display the current search path.

The current default value for the search path is: $user. public. The meaning is to first search for a schema name that is the same as the user name and, if it does not exist, use the public mode.

Modify a base table

Delete base table

Establishment and deletion of indexes

Indexing is an effective means of speeding up query speed. The user can set up one or more indexes on the base table to provide multiple access paths and speed up the search according to the needs of the application environment.

Data query

SQL provides a SELECT statement for querying the database in the general format:

Select can either complete a simple single-table query (a query involving only one table) or complete complex connection queries and nested queries.

Finally form a result relationship as output.

Cancel value duplicate row using distinct

Connection query: A query that involves more than two tables is called a connection query.

Self-Connection: The connection operation can be made not only between two tables, but also by a table connected to itself, called the table's own connection.

Compound Conditional Connection

There are multiple join conditions in the WHERE clause.

nested queries

Related subqueries are circular queries, that is, querying a parent query tuple, passing in a subquery, then the parent query, and then the next tuple (that is, the value of the subquery needs to be passed in by the parent query), and the unrelated subquery is a one-time check to complete the subquery, and then the parent query.

is the attribute column name and the value one by one correspond.

The role of the view:

View simplifies user operations

Views enable users to view the same data in multiple ways

Views provide a degree of logical independence to the refactoring database

Views can provide security for confidential data

Proper use of views to express queries more clearly

Database Chapter 3 relational database standard language SQL