8.1 Database

8.1.1 differences between a relational database and a file database system 1. relational databases are characterized by Data structuring, while file systems are unstructured. the logical structure of a relational database is a two-dimensional table, and the basic element in the file system is a file. 3. The file database system can manage multimedia files and support the working mode of CS. 8.1.2SQL basic function Query

8.1.1 differences between a relational database and a file database system 1. relational databases are characterized by Data structuring, while file systems are unstructured. the logical structure of a relational database is a two-dimensional table, and the basic element in the file system is a file. 3. The file database system can manage multimedia files and support the C/S working mode. 8.1.2 query of basic SQL Functions

8.1.1 differences between a relational database and a file database system

1. Relational databases mainly feature Data structuring, while file systems are unstructured.

2. The logical structure of a relational database is a two-dimensional table, and the basic element in the file system is a file.

3. The file database system can manage multimedia files and support the C/S working mode.

8.1.2 basic SQL Functions

Query, data operations, data definition, and data control.

Query: (DSL ?) Use the select statement.

Data Operations: DML includes three statements: insert data, modify data, and delete data.

Data Definition: DDL, which defines and revokes database users, basic tables, views, and indexes.

Data Control statement: DCL for Unified Control and Management to ensure data security when multiple users share data.

Basic statement

DSL: select a record that meets the conditions; select * from table where statement

DML: insert a record; insert into table (Field 1, Field 2,...) values (value 1, value 2)

Update: update statement: update table set field = value where expression

Delete: Delete record; delete from table where condition expression

DDL: create a data table; create table tablename (Field 1, Field 2 ...)

Drop; delete a data table; drop table tablename

DCL: grant the system permission to the user

Revoke: revokes system permissions;

8.1.3 internal and external connections

An inner join is also called a natural join. Only data matching two tables can appear in the result set. The returned result set contains all the matched data in the two tables and discards the unmatched data. Delete other connections from the result set

All rows in the table that do not match the rows.

Select fieldlist from table1 inner join table2 on table1.column = table2.column

Outer join will contain more non-matching data, including left outer join, right outer join, full outer join, left outer join, right outer join, and full outer join;

8.1.4 transactions

A transaction is a separate execution unit in a database. It is usually caused by the execution of user programs written in the advanced database operating language SQL, Programming Language C ++, and java. When data is successfully changed in the database

The changed data will be submitted and will not be changed. Otherwise, the transaction will be rolled back or canceled, and the change is invalid.

Four features of transactions: ACID

A: atomicity: partial execution of transactions is not allowed, either full execution or full execution.

C: consistency: the database data before and after the transaction execution must be consistent and meet the constraints specified by the mode.

I: isolation: Concurrent execution of multiple transactions. To ensure data security, the internal operations of one transaction are isolated from those of other transactions. There are four types of transaction isolation levels: uncommitted read, committed read, Repeatable read, and serialized.

D: permanent: the DBMS ensures that the data modification is permanent after the transaction is completed. When a problem occurs in the system or media, the modification liquid is saved. Backed up and restored by the database.

COMMIT and ROLLBACK.

8.1.5 stored procedure.

SQL statements are compiled and executed first. To improve efficiency, some SQL statement sets that have completed specific functions are compiled and stored on the database server. You can call and execute them by using the specified name.

Create procedure sp_name @ [parameter name] [type]

As

Begin

...

End

Call: exec sp_name [parameter name]

Delete stored procedure: drop procedure sp_name

The use of stored procedures can enhance the flexibility of SQL, use flow control statements to write stored procedures, and have strong flexibility to complete complex judgment and operations. It can also ensure data security and integrity.

Users without permissions can also access the database indirectly in this way, which is safer.

Essentially, there is no difference with functions, but specifically:

1. The stored procedure is generally executed as an independent part,

2. The functions implemented by stored procedures are complex, and the functions implemented by functions are highly targeted.

3. The function must enclose the input parameters in brackets and only one value or table object can be returned. The stored procedure can return multiple parameters.

4. functions can be nested in SQL statements and can be called in select statements, but stored procedures are not supported.

5. functions cannot operate entity tables. They can only operate built-in tables.

6. The stored procedure is compiled and stored on the server during creation, and the execution is faster.

8.1.6 primary key and foreign key

The primary key cannot be blank. It can only have one primary key. It can uniquely identify a row. The primary key is not necessarily a single column and can be multiple attributes.

A foreign key is the constraint that the table is bound by other tables. The foreign key of the table is the primary key of other tables. Foreign keys can have multiple or be empty.

Maintain data consistency and integrity.

8.1.7 deadlock

Due to the concurrent execution of the program, the memory is continuously applied for and released, and the process of infinite waiting enters into the competition for resources, which is a deadlock.

Conditions for creating a deadlock:

1. mutex: each resource can only be used by one process at a time.

2. Request and hold wait. When a process is blocked by the requested resources, it will not release the acquired resources.

3. cannot be deprived: resources obtained by a process cannot be deprived before they are released.

4. Loop wait; wait for the loop to have a dead lock.

8.1.8 shared locks and mutex locks

A lock is a data protection mechanism.

A shared lock is an S lock, which is used for operations without changing or updating data. For example, after the select statement and the transaction adds the S lock to the data, other transactions can only apply the S lock, but cannot apply the T lock.

The exclusive lock is an X lock used for data change operations, such as insert, update, or delete, to ensure that only one process accesses the object at any time. If the X lock is applied, no lock is allowed for other task transactions.

Note: first lock before operation, and unlock after use.

8.1.9 1234 paradigm

Normalization: it is the refinement work after identifying data elements and relationships in the database and defining these tables.

1NF: each column in the database is an inseparable basic data item. A column cannot contain multiple values. A property cannot contain multiple values or multiple duplicate attributes. If duplicate attributes appear, you need to create an object for the duplicate attributes. The relationship between the new object and the original object is one-to-multiple. The attribute cannot be separated, that is, it cannot be a combination of attributes.

2NF: Based on 1NF, each instance or row must be uniquely distinguished. In R, each non-master attribute is completely dependent on a candidate key, and 2NF is used. If a non-primary attribute is dependent on the candidate key, a new table should be created and the foreign key constraint should be used. Connect as needed.

3NF: The third paradigm requires that a database table do not contain information about non-primary keywords already contained in other tables. For example, there is a department information table, where each department has a department ID (dept_id), department name, Department profile, and other information. After listing the Department numbers in the employee information table, you cannot add the Department name, Department profile, and other information related to the department to the employee information table. If the department information table does not exist, it should also be constructed based on the third paradigm (3NF), otherwise there will be a large amount of data redundancy. In short, the third paradigm is that attributes do not depend on other non-primary attributes. The third paradigm (3NF) must satisfy the second Paradigm (2NF) first ). 3NF eliminates the transfer dependency in 2NF. For example, a student table (student ID, name, Department number, and department name) is a student ID> Department number, while a student ID> Department name, therefore, the system name transfer dependency and student ID will be changed to the following student table 3NF (student ID, name, Department number) (Department number, Department name)

BCNF: 3NF-based. Each attribute does not pass a candidate key dependent on R. The so-called pass function dependency refers to the existence of "A → B → C" decision relationship, then the C transfer function depends on.

There is a detailed description of the write good: http://jacki6.iteye.com/blog/774866

Http://www.2cto.com/database/201404/290140.html

8.1.10 write an SQL statement to retrieve the-30 records of the table (SQL server uses the Automatically increasing id as the primary key, and the id may be discontinuous)

Method 1: Select Top 10 * From S Where ID> (Select MAX (ID) from (Select Top 20 ID From S) as S)

Method 2: Select Top 10 * From S where id not in (select Top 20 ID From S)

8.1.11check Constraints

CHECK (constraint expression) Restrictions on the value or data format.

8.1.12 View

A view is a virtual table that only displays the content we care about. It is a selected logical window. There is a view definition in the database, and there is no data contained in the view. The data is still in the original basic table.

Query statements can be simplified, but query efficiency cannot be improved.