Database Review ⑦

Database Review ⑦

June 18, 2016

20:03

Main DDL & DML & views data definition language, data manipulation language, view

=============DDL Data Definition Language =============

1. declaring a relational table and deleting a relational table

Simplest form is:

CREATE TABLE <name> (

<list of Elements>

);

And you could remove a relation from the database schema by:

DROP TABLE <name>;

Common Types of data:

Int

REAL or FLOAT

CHAR (n) = fixed length string has n characters

VARCHAR (n) = variable-length string with a maximum of n characters

DATE: ' Yyyy-mm-dd '

Time: ' Hh:mm:ss '

There is a maximum of one primary key primary key in a relational table, but there can be multiple unique attribute columns;

Primary key requires non-null null,unique can have null values present

2. Add an attribute column to an existing table

ALTER table<name> ADD

<attribute declaration>

To illustrate:

ALTER TABLE Bars ADD

Phone CHAR (+) DEFAULT ' unlisted ';

3. Delete an attribute column

ALTER table<name>

DROP <attribute>

Example: ALTER TABLE Bars DROP license;

==========================================

=============DML Data Manipulation Language =============

4. Insert one or more tuple values insert

INSERT into <relation>

VALUES (<list of values>);

To illustrate:

5. Delete tuples that meet certain criteria

DELETE from <relation>

WHERE <condition>;

Delete all tuples Delete all tuples

DELETE from <relation>

6. Update the relationship table

Update some tuple value statements:

UPDATE <relation>

SET <list of attribute assignments>

WHERE <condition on tuples>

To update multiple tuple values:

==========================================

=================views View ================

7. Create a view

CREATE VIEW <name> as <query>;

To illustrate:

Using views for related query operations

8. using Views for query operations

The view is actually a relational table, but the view is not stored on the physical space.

Use the above view for query operations:

9. Update the View

(1) If the field of the view is from a field expression or constant, the INSERT, update operation on this view is not allowed, and the delete operation is allowed;

(2) If the View field is from a library function, the view does not allow updates;

(3) If a GROUP BY clause or aggregate function is defined in the view, the view does not allow updates;

(4) This view is not allowed to update if there are distinct options in the definition of the view;

(5) If there is a nested query in the definition of the view, and the table involved in the FROM clause of the nested query is also the base table for exporting the view, the view does not allow updates;

(6) If the view is exported by more than two base tables, this view does not allow updates;

(7) A view defined on a view that does not allow updates is also not allowed to be updated;

( 8 a view defined by a base table that contains only the primary key or alternate key of the base table, and the view does not have properties defined by an expression or function to allow updates.

10.WITH CHECK OPTION the View

Add with CHECK option to a view to not break the predicate condition in the view definition (that is, the conditional expression in the subquery) when the view is used for the redaction operation

CREATE VIEW

F_student1 (stdnum,name,sex,age,dept) as

SELECT * from Student

WHERE ssex= ' woman ';

Do the following update:

UPDATE F_student1

SET Ssex = ' Male '

WHERE Age = 18

The result would be to change the gender of the 18-year-old girl

SELECT *

From F_student1

The result was that the 18-year-old girls were missing compared to the previous data.

Therefore, you need to add a check constraint.

One by one . Create and delete indexes

You can create up to one clustered index on a base table.

Use of clustered indexes: Queries that are based on clustered index columns (especially scope queries) can improve query efficiency

Scope of application of clustered index:

Clustered index column has a large number of distinct values

Minimal additions and deletions to base tables

Modification of the variable length column is seldom done

CREATE [UNIQUE] [CLUSTER] Index < index name >

On < table name > (< column name > [order] [, column name][< Order;])

Use the < table name > To specify the base table name to be indexed;

The index can be built on one or more columns of the table, separated by commas between the column names

In < order > specify the order of index values, ascending: ASC, descending: DESC. Default value: ASC

Unique indicates that each index value for this index only corresponds to a unique data record cluster indicates that the index to be established is a clustered index

Database Review ⑦