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 ⑦