Database Statements (iii)

Finally, there are two modules, one is the update of the data (insert, modify, delete), the expected data definition of the fourth module--view (because this is relatively large, so separate), first of all to say that our data update

Inserting data

V two ways to insert data

1. Inserting tuples

2. Inserting sub-query results

• Multiple tuples can be inserted at one time

Let's go first. Insert a tuple:

V Statement format

INSERT

into < table name > [(< attribute column 1>[,< attribute column 2; ...)]

VALUES (< constants 1> [,< constants 2>]

v function

N Inserts a new tuple into the specified table

V INTO clause

The order of the N attribute columns can be inconsistent with the order in the table definition

n does not specify an attribute column

n Specify a partial attribute column

V VALUES clause

n the value provided must match the INTO clause

Number of V values

Type of V-value

v [Example 1] A new student tuple (school number: 200215128; name: Chen; gender: male; Department: is; Age: 18 years) insert into the student table.

V INSERT

V into Student (sno,sname,ssex,sdept,sage)

V VALUES (' 200215128 ', ' Chen ', ' Male ', ' is ', 18);

V [Example 2] Insert the student Zhang Chengmin information into the student table.

V INSERT

V into Student

V VALUES (' 200215126 ', ' Zhang Chengmin ', ' Male ', and ' CS ');

Here, talk. Insert subquery results

Finish the data insertion, and then talk about modifying the data.

(Modify the value of a tuple)

] Change the age of student 200215121 to old

UPDATE Student

SET sage=22

WHERE sno= ' 200215121 ' ;

(Modify values for multiple tuples)

[ example 6] to increase the age of all students 1 years

UPDATE Student

SET sage= sage+1 ;

(Modified statement with subquery)

[ example 7] Zero The scores of all the students in computer science department.

UPDATE SC

SET grade=0

WHERE ' CS ' =

(Selete sdept

From Student

WHERE Student.sno = SC. Sno);

Finally, the last part of the data update, delete the data

(delete a tuple's value)

[ example 8] Delete the study number as 200215128 student Records.

       DELETE from         Student         WHERE sno= 200215128 ';

　　

(Delete values for multiple tuples)

[ example 9] Delete all the student's course selection records.

   DELETE from        SC;

　　

(DELETE statement with sub-query)

Delete all students ' records in the Department of Computer Science.

DELETE from        SC        WHERE  ' CS ' =                           (selete sdept from                            Student                            

　　

Finally came to this chapter of the last part of the view, first we talk about the features of the view:

V virtual table, which is a table exported from one or several basic tables (or views)

V only stores the definition of the view and does not store the data for the view

the data in the V-base table changes, and the data queried from the view changes

And after we get the view, we can also manipulate it again, with the following types of actions:

V Query

V Delete

V restricted updates

V defines a new view based on this view

Well, that's a little fur thing, now let's talk about defining views (sub-setup and deletion):

Establish:

V statement Format

CREATE VIEW

< View name > [(< column name > [,< column name ;] ...)]

As < Sub-query >

[with CHECK OPTION] ;

V the Property column name that makes up the view: omit all or specify all

The v subquery does not allow the ORDER by clause and the DISTINCT phrase to be included

V with CHECK OPTION indicates that the view is being UPDATE,INSERT and DELETE the action ensures that the updated, inserted, or deleted rows satisfy the predicate condition in the view definition (that is, the conditional expression in the subquery).

All column names must be specified in the following three cases :

V (1) The target column is not a simple attribute name, but an aggregate function or column expression;

V (2) Multi-table connection selected several of the same list;

V (3) requires a new, more appropriate name to be enabled in the view.

V RDBMS executes the CREATE view statement by simply storing the view definition in a data dictionary and not executing the SELECT statement.

V when querying a view, isolate the data from the base table as defined by the views.

Example:

[ example 1] Create a view of the students in the information department.

   CREATE VIEW is_student        as        SELECT sno,sname,sage        from    Student        WHERE  sdept= ' is ';

　　

[ example 2] Create a view of the student in the information department, and require that the view is only available to students in the information system when modifying and inserting operations .

  CREATE VIEW is_student        as        SELECT sno,sname,sage        from  Student        WHERE  sdept= ' are '        with CHECK OPTION;

　　

(views based on multiple base tables)

[ example 3] Establishment of the information department elective 1 student view of the course.

  CREATE VIEW is_s1 (sno,sname,grade)        as        SELECT student.sno,sname,grade        from  student,sc        WHERE  sdept= ' is ' and                       student.sno=sc. Sno and                       SC. cno= ' 1 ';

　　

(View-based view)

Example 4] set up the information department to take the 1 course and The student's view of the score above.

CREATE VIEW is_s2        as        SELECT sno,sname,grade        from  is_s1        WHERE  grade>=90;

　　

( A view with an expression )

[ example 5] define a view that reflects the year that the student was born.

  CREATE  VIEW bt_s (Sno,sname,sbirth)        as        SELECT sno,sname,2000-sage        from  Student;

　　

(Group view)

[ example 6] define the student's number and his average score as a view

assuming that the "score" column Grade in the SC table is a digital type

       CREATE  VIEW s_g (sno,gavg)             as              SELECT sno,avg (Grade)             from  SC             GROUP by Sno;

　　

(no attribute column specified)

[ example 7] will be Student all female records in the table are defined as a single view

    CREATE VIEW f_student (f_sno,name,sex,age,dept)      as      SELECT *      from  Student      WHERE ssex= ' female ';

　　

Disadvantages:

After modifying the structure of the base table Student , theStudent table and f_student The view 's image relationship is broken, causing the view to not work correctly

Said so much to create, below we say delete view:

the format of the V statement:

DROP VIEW < View name > ;

• The statement removes the specified view definition from the data dictionary
• If other views are also exported on the view, use the CASCADE Cascade DELETE statement that deletes the view along with all views exported by it
• when you delete a base table, all view definitions that are exported by that base table must be explicitly used DROP VIEW Statement Deletion

[Example 8 ] Delete view bt_s: drop view bt_s;

Delete view is_s1:drop view is_s1;

• Reject execution
• Cascade Delete:

DROP VIEW is_s1 CASCADE;

I'm done. Define the creation and deletion of the view, below we go to the second largest module, query view:

V User angle: Query view is the same as query base table

V RDBMS approach to implementing view queries

• View Digestion Method ( View Resolution )
  • Check for validity
  • Convert to an equivalent query on a basic table
  • Execute the revised query

V [ example 9] in the information Department student's view to find the age of younger than the students.

V SELECT Sno,Sage

V from is_student

V WHERE sage<20;

V is_student definition of view ( See example of view definition 1)

The query statement after the view digestion transformation is:

SELECT  sno,sage       from  Student WHERE  sdept= ' is '  and  sage<20;

　　

limitations of the V-View digestion method

• In some cases, the view digestion method cannot generate the correct query.

Third chunk, update view:

[ example ] The student view of the information system is_student Secondary Number 200215122 the name of the student is changed to "Chen Liu".

(Modify a value)

Update  is_studentset  sname= ' Chen Liu ' WHERE  sno= ' 200215122 '; Converted statement: Update  studentset sname= ' Chen Liu ' WHERE sno= ' 200215122 ' and sdept= ' is ';

　　

(Add record)

[ example ] view to Information system students is_s insert a new student record in: 200215129 , Zhao Xin, - years

Insertinto is_studentvalues (' 95029 ', ' Zhao Xin ', 20); Convert to base table update: Insertinto   Student (sno,sname,sage,sdept) VALUES (' 200215129 ', ' Zhao Xin ', ' is ');

　　

(Delete record)

[ example ] Delete Information student View is_student Secondary School number is 200215129 the records

Deletefrom is_studentwhere sno= ' 200215129 '; Convert to base table update: Deletefrom studentwhere sno= ' 200215129 ' and sdept= ' is ';

　　

V Update View limitations: Some views are not updatable because updates to these views cannot be uniquely translated into updates to the corresponding base tables

V allows updating of a subset view of rows and columns

V updates for other types of views different systems have various restrictions

Finally, the function of the view:

V 1. View simplifies user operations

V 2. views enable users to view the same data in multiple ways

V 3. views provide a degree of logical independence to the refactoring database

V 4. views provide secure protection of confidential data

V 5. proper use of views to express queries more clearly

Database Statements (iii)