First, the characteristics of the database:
Permanently stored and organized to share
Function:
1. Data definition function:
(1) Data definition language DDL
2. Data manipulation function:
(2) Data manipulation language DML
(3) Data Control Language DCL
3. Data organization, storage and management:
The basic goal of data organization and storage is to improve storage space utilization and convenient access, provide multiple access methods (such as index lookup, hash lookup, sequential lookup, etc.) to improve access efficiency
4. Database transaction management and operation management
5. Database setup and Maintenance functions
6. Other functions
Second, the concept model:
(1) Entities: things that exist and can be distinguished from each other
(2) attribute: An attribute that an entity has
(3) Code: a property set that uniquely identifies an entity
(4) Entity type: A homogeneous entity that is abstracted and characterized by a collection of entity names and their attribute names
(5) Entity set: A collection of entities of the same type
(6) Contact:
Inter-entity Contact 1:1
One-to-many contact 1:n
Many-to-many m:n
A representation method of a conceptual model:
E-r model
Third, the data model:
It consists of three parts: data structure, operation and integrality constraint condition of data.
Data structure describes the constituent objects of a database and the connections between objects
Data operations A collection of actions that allow the execution of an instance (value) of various objects (types) in a database, including operations and related operational rules
The integrity constraint of the data is a set of integrity rules
Common data models:
1. Hierarchical Model (tree)
Advantages:
(1) The data structure is relatively clear and simple
(2) High query efficiency of hierarchical database
(3) Hierarchical data model provides good integrity support
Disadvantages:
(1) Many connections in the real world are non-hierarchical
(2) More restrictions on insert and delete operations
(3) Querying children's nodes must pass through the parent node.
(4) Hierarchy commands tend to be programmed2. Mesh model (figure)
Advantages:
(1) to describe the real world more directly
(2) Good performance, high access efficiency
Disadvantages:
(1) complex structure, with the expansion of the application environment, the structure of the database becomes more and more complex, not conducive to end-user control
(2) The network model of DDL DML complex, and embedded in a high-level language, the user is not easy to grasp, not easily used
(3) The connection between records is realized through access path, the application must choose the appropriate storage path when accessing the data, so the user must understand the details of the system structure, and aggravate the burden of writing the application.
The hierarchical model and the mesh model are collectively referred to as the format model
3. Relational Model (table)
Data Model:
Relationship: A table for a relationship
Tuples: One behavior in a table a tuple
Properties: A column in a table is a property
Code: Also called code construction, a property group in the table
Domain: A collection of values with the same data type
Component: The value of an attribute in a tuple
The description of a relationship is generally expressed as:
Relationship name (attribute 1, property 2, ..., attribute N)
The relationship model requires that the relationship must be normalized, and that each component of the relationship must be an irreducible data item
Integrity rules:
Entity Integrity Primary property cannot be empty
Referential integrity
User-defined Integrity
Object-oriented data Model (object)
Object Relational Data Model
Semi-structured data model
Four, the database system level three mode two-level image function
Type: A description of the structure and properties of a data
Value: A specific assignment of the type
The model only involves the description of the type, and is the logical structure and characteristic of all the data in the database.
A specific value of the pattern is called an instance of the pattern, which is relatively stable and the instance is relatively variable.
Three-level mode structure:
1. Mode
A description of the logical structure and characteristics of all data in a database, which is the public view of all users
2. External mode
Also known as sub-mode or user mode, it is a description of the logical structure and characteristics of local data that the database user can see and use, a data view of the database user, and a logical representation of the data related to an application.
3. Internal mode
Also known as storage mode, a database has only one internal mode. is a description of the physical structure and storage of the data, and is the way data is organized inside the database.
Secondary image features:
1. External mode/mode image
Logical independence of data
2. Internal mode/mode image
Physical independence of data five, relational model three elements: data structure operation set integrity constraints
Domain
Cartesian product
Relationship: A subset of D1*D2*D3*...*DN is called a relationship on the domain D1, D2 ..., DN, represented as R (d1,d2,..., Dn), R denotes the name of the relationship, N is the mesh or degree of the relationship (number of attributes)
N=1, called unit/eye relationship
N=2, said two yuan/eye relationship
Relationships are Zhang Yiwei tables, each row represents a tuple, and each column represents a domain. N-Mesh relationships must have n attributes
If an attribute group in a relationship uniquely identifies a tuple and its subset is not, the attribute group is called a candidate code
If a relationship has more than one candidate code, select one of the main codes
The attributes in the candidate code are called primary properties. Properties that are not included in any candidate code are called non-primary or non-code attributes
Attribute candidate Code main code six, relational algebra:
And: R U S = {t|t∈r V t∈s}
Turn: R-s = {T|t∈r^t∈s}
Poor: R-s = {T|t∈r^t!∈s}
Generalized Cartesian product: r*s = {TR Ts|tr∈r^ts∈s}
Specialized relational operations:
Select: Select a tuple in relationship R that satisfies the given criteria
Projection: Select a number of attribute columns from R to compose a new relationship
Connection: Select a tuple between attributes that satisfies a certain condition from the Cartesian product of two relationships
Equivalent connections: Select those tuples of a and B attribute values from the generalized Cartesian product of the relationship R and S
Natural connection: a special equivalent connection
A tuple that is discarded as a natural link is called a floating tuple, and if the suspension tuple is also stored in the result relationship, and the values are blank in other attributes, then the connection is called an outer join. If you leave only the suspension tuple in the left relationship R called the LEFT JOIN, if you leave only the suspension tuple in the right relationship s called the right outer join
In addition to the operation: the result of the relationship R divided by the relationship S is the relationship T, then T contains all the properties in R but not in S and their values, and all combinations of tuples of T and S are in the R VII, SQL overview and statements
* Set data definition language ddl:create drop alter data manipulation language dml:update Delete Insert Data Control Language Dcl:grant revoke in one
* Highly non-procedural
* Set-oriented operation mode
* provides two ways to use the same syntax structure: can be used independently for online interaction, is embedded language
1. Definition and deletion of the schema:
Create schema< Mode name >authorization< user name >
Users can further create basic tables, views, define authorizations, and so on in this mode while creating patterns
Drop schema< Mode name ><cascade|restrict>
One of the two must be cascade and restrict. Select CASCADE (Cascade), which means that all database objects in the schema are deleted at the same time as the mode is deleted. Restric (limit) is selected to indicate that the execution of the DELETE statement is rejected if the subordinate's database object (such as a table, view, and so on) is already defined in the pattern.
2. Definition, deletion and modification of the table
Create table< Table name > (< column name >< data type >[column-level integrity constraint)
[, < column name >< data type >[column-level integrity constraints]]
[, < column name >< data type >[column-level integrity constraints]]
...
[, < column name >< data type >[column-level integrity constraint]])
Patterns and tables:
To define a table in S-t
1.create table "S-t". Student (...)
2. Create a table when creating a schema
3. Set the owning mode, when the user creates the base table without specifying a pattern, the system determines the mode of the object according to the search path
Show Search_path shows the current search path
Set Search_path to ' s_t ', public sets the search path
Alter table< table name >
[add[column]< new column name >< data type >[full constraint]]
[add< table-level integrity constraint;]
[drop[column]< column name >[cascade|restrict]]
[Drop constraint< integrity constraint name >[restrict|cascade]]
[Alter column< column name >< data type;] drop table< table name >[restrict|cascade]
If you select Restri, the deletion of the table is restricted, and the table to be deleted cannot be referenced by other table constraints (such as constraints such as Check,foreign key), cannot have views, triggers, stored procedures, and so on.
If you select Cascade, the deletion of the table has no restrictions. When you delete a table, related dependent objects are deleted together
Default Restri
3. Establishment and deletion of indexes
When the data volume of the table is relatively large, the query operation will be time-consuming and an effective means to speed up the query when establishing the index.
create[unique][cluster]index< Index name >
On < table name > (< column name >[< order >][,< column name >[< order;] ...)
Unique indicates that each index value for this index only corresponds to a unique data record
Cluster indicates clustered index clustering for an index to be established: in order to improve the query speed of a property, it is called clustering in a contiguous physical block to set the tuple with the same value on these attributes.
Alter index< old index name >rename to< new index name >
Drop index< Index name >
4. Data query
Select[all | distinct]< target expression >[,< target expression;
from< table name or view name >[, < table name or view name; ...] | (SELECT statement) [as]< aliases >
[where< conditional expression;]
[Group by< column name 1>[having< conditional expression;]]
[Order by< column name 2>[asc|desc]]
Common Query conditions:
=,>,<,>=,<=,!=,<>,!>,!<,not+ The above comparison operators
SELECT * FROM student where sage<20
Between and, not between and
SELECT * FROM student where sage (not) between and 23
In,ont in
SELECT * FROM student where sdept (not) in (' CS ', ' MA ', ' is ')
is Null,is NOT NULL
And,or,not
Like
[Not]like ' < match string > ' [Escape ' < change character > ']
SELECT * FROM course where cmane like ' db\_design ' escape ' \ '
Escape ' \ ' means ' \ ' for the swap character, so that the character "_" immediately after "\" in the matching string does not have the meaning of a wildcard
SELECT * FROM student where sname like ' Liu '
Wildcard characters:
% (Percent semicolon) represents a string of any length (0). A%b a string that ends with a B starting with a
_ (Lower horizontal line) represents any single character. A_b A string that starts with a and ends with a length of 3 b
Aggregation functions
COUNT (*) Statistics tuple count
COUNT ([distinct|all]< column name >) counts the number of values in a column
SUM ([DISTINCT | all]< column name >) calculates a column worth the sum (this column must be a numeric type)
AVG ([DISTINCT | all]< column name >) calculates the average of a column of values (this column must be numeric)
Max ([DISTINCT | all]< column name >) to find the maximum value in a column
MIN ([distinct | all]< column name >) to find the minimum value in a column
If you develop a distinct phrase, it means that you want to cancel the duplicate values in the specified column at the time of calculation. Default to All
Select COUNT (*) from student
Select COUNT (Distinct sno) from SC
Select AVG (grade) from SC where cno = ' 1 '
Select sum (grade) from SC, course where sno = ' 201215012 ' and sc.cno=course.cno
When a cluster function encounters a null value, except for COUNT (*), only null values are skipped and only non-null values are processed
Connection Query
Equivalent and non-equivalent connections
Select student.*,sc.* from STUDENT,SC where Student.sno=sc.sno
Self-Connection
When the course table has only a direct first-class message for each course, and there are no first-class lessons, you will need to use your own connection to get this information.
Select First.cno,second.cpno from Course first,course second where first.cpno=second.cno
External connection
Still keep the suspended tuple in the result
SELECT * FROM Student ieft outer join (left OUTER join) SC on (or using) (STUDENT.SNO=SC.SNO)
Multi-table Connection
Select Student.sno,sname,cname,grade from Student,sc,course where Student.sno=sc.sno and Sc.cno=sourse.cno
Nested queries:
Sub-query with in
Subqueries with comparison operators
Subqueries with any or all predicates
Subquery with EXISTS predicate
(not) Correlated subquery: Subquery condition (not) dependent on parent query
Collection Query
The collection operation mainly includes and operates the union, the intersection operation intersect, the difference operation except
A query based on a derived table
A subquery can appear not only in the WHERE clause, but also in the FROM clause, when the child query generates a temporary derived table that becomes the query object for the main query
Select CNO, CNO fromsc, (select CNO, avg (grade) from SC Group by Sno) as AVG_SC (Avg_sno,avg_grade) where sc.sno=avg_sc.avg_s No and Sc.grade>=avg_sc.avg_grade
If there is no aggregate function in the subquery, the derived table can not specify a property, and a subquery after the select sentence has the column name as its default property. When generating a derived table from a clause, the AS keyword can be omitted, but an alias must be specified for the derived relationship
5. Data Update
Inserting data
Inserting tuples
Insert into< table name >[(< property column 1>[, < attribute column 2>] ...) value (< constant 1>[,< constant 2> ...])
Insert subquery Results
Insert into< table name >[(< property column 1>[, < attribute column 2>] ...) Sub-query
modifying data
update< table name > set< column name >= < expression >[, < column name >= < expression;] ... [where< conditions;]
Its function is to modify the tuple that satisfies the WHERE clause in the specified table, where the SET clause gives the value of the < expression > to replace the corresponding attribute column value. If you omit the WHERE clause, it means that you want to modify all tuples in the table
Update student Set sage = where sno = ' 201215121 '
Update student Set Sage=sage+1
Delete data
Delete from< table name >[where< condition;]
Delete all tuples in a table if you omit where
6. Handling of NULL values
(1) Generation: When inserting tuples, outer joins
(2) Determination of NULL value: With IS null or is not NULL
(3) NULL constraint: The attribute definition has a NOT NULL constraint can not be null value, the property with a unique restriction cannot take null value, code attribute cannot take null value
(4) Arithmetic operations, comparison operations and logical operations of NULL values
A null value compares to another value as unknown, and in a query statement only a tuple that makes the selection condition true in the WHERE and HAVING clause is selected as the output result
7. View
Establish:
Create view< View name >[(< column name >[,< column name;])] as< subquery >[with check OPTION]
Where a subquery can be any SELECT statement, and whether it can contain an ORDER BY clause and a distinct phrase, depends on the implementation of the specific system
With CHECK option to ensure that the updated, inserted, and deleted rows satisfy the predicate condition in the view definition (that is, the conditional expression in the subquery) when you update, insert, and delete the views
The attribute column names that make up the view are either omitted or all specified, and there is no third option. If the individual property column names of the view are omitted, the view is suppressed by the fields in the target of the SELECT clause in the subquery, but all the column names that make up the view must be explicitly specified in the following three cases:
(1) A target is not a simple attribute name, but a clustered or column expression
(2) A number of fields with the same column name as the view are selected when the multi-table connection
(3) You need to enable a new, more appropriate name for a column in the view
CREATE VIEW is_student as Select Sno, Sname, sage from student where sdept = ' is '
Views can be built on one or more base tables, or on one or more defined views or on basic tables and views. If a view is exported from a single table and only some rows and columns of the base table are removed, but the main code is preserved, the view is called a subset of the rows.
Views with virtual columns (view of bands expressions), grouped views
Delete:
Drop view< view name >[cascade]
Inquire:
Similar to table queries
Update:
(1) Because the view does not actually store the virtual table of data, the update to this view is eventually converted to an update to the base table
(2) Not all views can be updated, because some view updates cannot be uniquely converted to updates that correspond to basic tables
(3) In general, the view of a subset of rows is updatable.
The role of the view:
(1) Simplified user operation
(2) Enable users to view the same data in multiple ways
(3) Provide a certain degree of logic to reconstruct the database
(4) Ability to provide security for confidential data
(5) Use view to express query more clearly
8. Authorization: Grant and Recovery
Granted:
GRANT < Permissions >[, < permissions;] ... [on < object type > < object name;]
To < user >[, < user;] ... [with GRANT OPTION];
Description
With GRANT OPTION clause: If the clause is specified, the user who obtains a certain permission can also grant the permission to another user, and if the clause is not specified, the user who obtains a certain permission can only use the permission and cannot propagate the permission.
Recover:
REVOKE < rights >[, < rights;] ... [on < object type > < object name;]
From < user >[, < user;] ...
9. Embedded SQL
The SQL language provides two different ways to use
Interactive: Used as a standalone language in the terminal interaction mode, this is a set-oriented descriptive language, and is non-procedural in nature.
Embedded: The SQL language is embedded in a high-level language, using the procedural structure of the high-level language to implement transaction processing.
10. Stored Procedures
syntax for creating stored procedures:
Create procedure stored Procedure name [{@ parameter name data type}[=default][output]
[{@ parameter name data type}[=default][output]
As
Select statement
such as CREATE PROCEDURE XS as select * FROM student
Perform:
exec stored Procedure name
Modify:
Alter procedure stored procedure name as Select
Delete:
drop procedure Stored Procedure name
Viii. Theory of relational data
Add:
1. Queries using top
Take only a few or a few results
Format:
TOP N[percent] with ties
N is a non-negative integer
Top N: Indicates the first n rows of the query result;
Select TOP 3 sname, sdept from student order by sage DESC
Select TOP 3 with ties sname, sdept from student order by sage DESC exists side by side
Top N Percent: Indicates the first n% row to fetch the results of the query
With ties shows the results of the example
Top is written behind Select or distinct, and the front of the query list
The with ties requires an order by
The result without order may be different from the hope. 2. Save the query results to a new table
Syntax format:
Select query table sequence into new table name
From data source where[< conditions;
The new table has two categories:
1) Permanent table, a table name can be;
2) Local temporary table: the table name plus #, the current connection is available, the lifetime is the lifetime of the current connection.
Global temp table: Add # # before table name, all connections are available. Lifetime is the lifetime of the current connection.
3. After the view is defined, users can query the views as if they were basic tables. When a DBMS implements a view query, it first checks the validation to see if the table or view being queried exists. If present, the definition of the view is taken from the data dictionary, the sub-query in the definition is combined with the user's query, and the query is converted into the equivalent of the basic table, and then the modified query is executed. This conversion process is called View Resolution.
4. General DBMS restrictions on view updates:
(1) This view does not allow updates if the view is exported from more than two base tables.
(2) If the View field is from a field expression or constant, insert and update operations on this view are not allowed, but delete operations are allowed.
(3) This view does not allow updates if the field of the view is from a set function.
(4) This view does not allow updates if the view definition contains a GROUP BY clause.
(5) This view does not allow updates if the view definition contains a distinct phrase.
(6) A view defined on a view that does not allow updates is also not allowed to be updated.
(7) If there is a nested query in the view definition, and the table involved in the FROM clause of the inner query is also the base table to which the view is exported, the view does not allow updates. NOTE: The WHERE clause is not hungry can use the aggregation function as an expression, need to use having not finished to be continued ...
Database Basics Points
