relational database-----SQL standard language

Relational database three-level schema structure

External mode: View, base table Export table, the database only holds the definition of the view, not the data corresponding to the view.

Mode: Basic table

Internal mode: Store files

Data definition

Manipulating objects	Create	Delete	Modify
Mode	Create schema	Drop schema
Table	CREATE table	drop table	ALTER TABLE
View	CREATE view	Drop view
Index	CREATE index	Drop Index	Alter index

Definition and deletion of patterns

To create a pattern, the user who invokes the command must have database administrator privileges, or create schema permissions granted by the database administrator

Statement: Create schema schema name authorization user name

Delete mode, drop schema schema name <cascade| Restrict>

Cascade: Delete the schema and delete all the database objects in this mode,

Restrict restriction: If the schema is defined with a table, the view rejects the execution of the DELETE statement. The drop SCHEMA statement can be executed only if there are no subordinate objects in this mode

Definition, deletion, modification of basic tables

Define the CREATE TABLE table name (< field 1>< data type >[column-level integrity constraints],...,[< table-level integrity constraints;])

Modify a base table alter table< table name >

Delete drop table < table name >[restric|cascade]

Cascade: There are no restrictions on the deletion of the table

Restrict: The deletion of the table is restricted, the base table to be deleted cannot be referenced by the constraints of other tables, cannot have views, triggers, stored procedures or functions (default)

Establishment and deletion of indexes

Indexes can speed up database queries, but occupy a certain amount of storage space. When the base table is updated, the indexes are maintained accordingly.

Create an index create [Unique][cluster] Index < index name > on table name (< column name >[< order >][,< column name >[< Order;])

Modify indexes alter INDEX < old index name > Rename to < new index >

Drop INDEX < index name >

Data query

SELECT [All | distinct] < target column expression >[, target column expression] ...

From < table name or view name >[,< table name or view name >,...] | (SELECT statement) [as]< aliases >

[where < conditional expressions;]

[GROUP by < column name 1> [having < conditional expression;]

[Oder by < column name 2>[asc| DESC]];

Finds a tuple that satisfies a condition from the base table, view, or derived table specified in the FROM clause, and then selects the attribute value in the tuple as the target column expression in the SELECT clause to form the result table, based on the conditional expression of the WHERE clause.

With a GROUP BY clause, the result is grouped by the value of column name 1, which is a group of tuples of equal value for the property column

oder by clause sort By Column Name 2 transaction value in ascending or descending order

Single-Table Query

Specify column Select < column name 1>,< column name 2>,... from TableName;

All columns select * FROM tablename;

Select several tuples in a table

Eliminate duplicate values for rows select DISTINCT column name, ... from TableName;

Tuples that meet a certain set of criteria

Query criteria	Predicate
Comparison	=,>,<,>=,<=,!=,<>,!=,!>,!<,;not + above comparison operator
Determine scope	Between (lower limit) and (upper limit), not between and
Determining the Collection	In, not in
Character matching	Like, isn't like
Null value	Is null, was NOT NULL
Multiple conditions	And, or, not

Aggregation functions

COUNT (*)	T statistical tuple number
Count ([Distinct|all] < column name >)	T counts the number of values in a column
SUM ([Distinct|all] < column name >)	J calculates the sum of a column of values
AVG ([Distinct|all] < column name >)	J calculates the average of a column of values
Max ([Distinct|all] < column name >)	The maximum value of the Y column
MIN ([Distinct|all] < column name >)	Y the minimum value of a column

All is the default

There can be no aggregate function as conditional expression in the WHERE statement, having

relational database-----SQL standard language