insert data

        Single tuple insertion

insert into Student(Sno,Sname) Values ('95020','Chen Dong')

        Insert subquery results

Insert into Deptage(Sdept,Avgage)
    select Sdept,AVG(Sage)
    from Student
    Group by Sdept

Modify data

        update...set...where

update Sutdent set Sage = 22 where Sno='95001'

        Modified statement with query (unrelated subquery)

update Sc set Grade = 0 where Sno in 
    (select Sno from Student where Sdept = 'CS')

        Modify statement with related subquery

update SC set Grade = 0 where 'CS'=
    (select Sdept from Student where Student.Sno=SC.Sno)

Delete data

        Specify the tuples to be deleted (meet the where condition). If it defaults, all tuples will be deleted

        Integrity rules are checked when a delete statement is executed (delete operation is rejected or cascade delete is performed)

delete from Student where Sno = '95019'--Delete a tuple/Multiple tuples

delete from Sc--Delete all

delete from Sc where Sno in
    (select Sno from Student where Sdept = 'Cs')--Deletion with unrelated subqueries

view

        It doesn't exist physically, it's virtual

        Correspondence

SQL user	User X
External mode	view
Relational model	Basic table
Internal mode	Storage file

        characteristic:           1. It is a virtual table (it can come from one or several tables)

                            2. Save the view definition without redundant data

                            3. When the data in the base table changes, the view will also change

        Operation:

                Create view (define view)

                        If the column name is defaulted, it will be directly composed of the target column of the sub query

                        You must specify a column name when:           1. The target column is a function set or a list expression

                                                                                2. The objectives are:*

                                                                                3. Select the column with the same name

                                                                                4. A more appropriate name is required in the view

                        order by / distinct cannot appear in the definition of a view

                Row column subset view

                         (the view comes from a table. Only some rows and columns are removed, but the main code must be retained)

create view V_StuIs as Select Sno,Sname,Sage
    from Student where Sdept = 'IS'

                        WITH CHECK OPTION, when updating through the view, it will automatically check whether the operator does not meet the creation conditions

                                sdept='IS' will be automatically added for modification / deletion. If sdept IS not IS, the operation will be rejected. If there IS no value, it will be automatically defined as IS

create view V_StuIs as Select Sno,Sname,Sage
    from Student where Sdept = 'IS' with check option

                View based on multiple base tables

create view V_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

create view V_S2 as select Sno,Sname,Grade from V_S1
    where Grade >= 90

                Views with expressions (must be aliased)

create view V_BTS(Sno,Sname,Sbirth) as select Sno,Sname,2018-Sage
    from Student

                Group view

create view V)SG(Sno,Gavg) as select SSno,AVG(Grade)
    from Sc group by Sno

        Delete view

drop view V_S1

                If V exists_ S2 based on V_S1 then it will not work, but it will still exist in the data dictionary

        Query view

select Sno,Sage from V_StuIs where Sage < 20

                In fact, it is a query table (view materialization method, view resolution method)

        update the view

                In fact, it is to update the source table (view materialization method, view resolution method)

update V_StuIs set Sname = 'Zhang San' where Sno='95002'

                Most views cannot be updated, and 100% of the views that can be updated are row column subset views

        Advantages of view

                1. Simplify user operation

                2. Enable users to view unified data from multiple perspectives

                3. Provide a certain degree of logical independence for the database

                4. Provide security protection for confidential data

Indexes

        The internal mode corresponding to the three-tier mode is an effective means to speed up the query speed

        Indexing

                Automatic system establishment

                        primary key 

                        unique

                DBA or table owner (self created as required)

                Clustered index: data is stored on disk in the order of this index, and one table can have at most one clustered index

                Unique index: you can create multiple indexes, but you cannot create a unique index if there is duplicate data in the column

                Create a unique index

create unique index idx_scno on Course(Cno)

create unique index idx_scno on Sc(Sno ASC,Cno DESC)

        Delete index

drop index idx_scno

         Maintain index

                Automatic system maintenance

        Use index

                The system automatically selects whether / how to use the index