Database-index, base table creation and deletion

Modify a base table

<表名><新列名><数据类型><完整性约束名> ][ ALTER COLUMN<列名><数据类型> ]；

[例8]向Student表增加“入学时间”列，其数据类型为日期型。     ALTER TABLE Student ADD S_entrance DATE；不论基本表中原来是否已有数据，新增加的列一律为空值。 [例9]将年龄的数据类型由字符型（假设原来的数据类型是字符型）改为整数。            ALTER TABLE Student ALTER COLUMN Sage INT；[例10]增加课程名称必须取唯一值的约束条件。            ALTER TABLE Course ADD UNIQUE(Cname);

Delete base table

DROP TABLE <表名>［RESTRICT| CASCADE］；RESTRICT：删除表是有限制的。欲删除的基本表不能被其他表的约束所引用如果存在依赖该表的对象，则此表不能被删除CASCADE：删除该表没有限制。在删除基本表的同时，相关的依赖对象一起删除 

    [例11]  删除Student表     DROP TABLE  Student  CASCADE ;

[Example 12] if the table has a view, select restrict when the table cannot delete < Span class= "Hljs-keyword" >create  view  is_student as  select  sno,sname,sage from Student where  sdept= ' is ' ; drop  table  Student              RESTRICT ;  --error:cannot drop table Student because other  objects dep End on it  

DROP TABLE Student CASCADE;          --NOTICE: drop cascades to view IS_StudentSELECT * FROM IS_Student;--ERROR: relation " IS_Student " does not exist 

Establishment and deletion of indexes

Purpose of indexing: speed up queries
Who can build the index
The owner of a DBA or table (that is, the person who created the table)
The DBMS will typically automatically establish an index on the following
PRIMARY KEY
UNIQUE
WHO maintains the index
DBMS Auto-complete
Working with Indexes
DBMS automatically chooses whether to use indexes and which indexes to use
The index in RDBMS is usually implemented by B + Tree and hash index.
B + Tree Index has the advantage of dynamic balance
The hash index has the characteristics of fast search speed
The use of B + tree or hash index is determined by the specific RDBMS.
Index is the internal implementation technology of relational database, which belongs to the scope of internal pattern
When the CREATE index statement defines an index, you can define whether the index is a unique index, a non-unique index, or a clustered index

<索引名><表名>(<列名>[<次序>][,<列名>[<次序>] ]…)；

[例13CREATEINDEX Stusname            ON   

 [example 14] indexes the STUDENT,COURSE,SC three tables in the student-course database. create  UNIQUE  INDEX stusno on  Student (Sno); create  unique  INDEX coucno on  Course (Cno); create  unique  INDEX scno on  SC (Sno asc , Cno desc ") Student table by school number ascending unique index course table by course number Ascending unique index SC table by number ascending and course number descending to build a unique index   

DROP INDEX <索引名>；删除索引时，系统会从数据字典中删去有关该索引的描述。[例15]  删除Student表的Stusname索引       DROP INDEX Stusname；

Database-index, base table creation and deletion