Database surface questions (1)

1. Data abstraction: Physical abstraction, conceptual abstraction, view-level abstraction, internal mode, pattern, outer mode

2. SQL language includes data definition, data manipulation, data control
Data definition: Create table,alter table,drop Table, craete/drop index, etc.
Data manipulation: Select, Insert,update,delete,
Data Control: Grant,revoke

3. SQL Common commands:
CREATE TABLE Student (
ID number PRIMARY KEY,
NAME VARCHAR2 (not NULL);//Build table
CREATE VIEW View_name as
Select * from table_name;//build view
Create UNIQUE index index_name on TableName (col_name);//Build Index
INSERT into tablename {column1,column2,...} values (Exp1,exp2,...); /insert
INSERT into Viewname {column1,column2,...} values (Exp1,exp2,...); /Insert view actual impact table
Update tablename SET name= ' Zang 3 ' condition;//updated data
Delete from Tablename WHERE condition;//deleted
GRANT (Select,delete,...) On (object) to user_name [with Grant option];//authorization
REVOKE (Permission table) on (object) from user_name [with REVOKE OPTION]//right of withdrawal
List the names of the workers and their leaders:
Select e.name, s.name from EMPLOYEE E S
WHERE E.supername=s.name

4. View:

5. Integrity constraints: Entity integrity, referential integrity, user-defined integrity

6. The third paradigm:
1NF: Each attribute is non-divided. 2NF: If the relationship R is 1NF, and each non-primary attribute is fully functional dependent on the key of R. Example SLC (sid#, courceid#, Sname,grade), then not 2NF; 3NF: If R is 2NF, and any of its non-key properties are not passed depends on any candidate key.

7. ER (Entity/Contact) model

8. Index function

9. Transaction: is a series of database operations, is the basic logical unit of database application. Nature of transactions: Atomicity,
? Atomic nature. That is, the transaction is either fully executed or not executed at all.
? Consistency or can be strung. Transaction execution causes the database to transition from a correct state to another correct state
? Isolation. The transaction is not allowed to make any changes to the data to any other transaction until the transaction is committed correctly.
? Durability. After the transaction is committed correctly, the result is persisted in the database, and the transaction results are saved even if there are other failures after the transaction commits.

10. Lock: Shared lock, mutual exclusion lock
Two-stage Lock protocol: Phase 1: Lock phase Phase 2: Unlock phase

11. Deadlock and Processing: The transaction loop waits for a data lock, it is deadlocked.
Deadlock handling: Preventing deadlock protocol, deadlock recovery mechanism

12. Stored procedure: A stored procedure is a compiled SQL statement.

1. Stored procedure because the SQL statement has been pre-programmed Yi, it runs faster.
2. The security and integrity of the data can be guaranteed. Stored procedures enable users who do not have permissions to access the database indirectly under control, thus guaranteeing the security of the data. Stored procedures allow related actions to occur together to maintain the integrity of the database.
3. You can reduce the amount of traffic on your network. The stored procedure is primarily run on the server, reducing the pressure on the client.
4: Stored procedures can accept parameters, output parameters, return single or multiple result sets, and return values. You can return the cause of the error to the program
5: Stored procedures can contain program flow, logic, and queries to the database. Data logic can also be encapsulated and hidden by entities.

13. Trigger: When the trigger condition is met, the trigger body of the trigger is automatically executed by the system.
Trigger time: Before,after. Trigger event: There are insert,update,delete three kinds. Trigger type: Row trigger, statement trigger

14. What are the differences between inner joins and outer joins?
An inner connection is a guarantee that all rows in two tables meet the conditions of the connection, but not the outer connection.
In an outer join, certain columns of dissatisfaction are also displayed, that is, only the rows of one of the tables are restricted, not the rows of the other table. Left-connected, right-connected, fully connected, three kinds of

Reference: http://www.blogjava.net/314508313/archive/2011/11/24/364690.html

Database surface questions (1)