Database Statement (i)

Soon, we went into the core chapters of our database course, the statement chapters, first we talk about SQL, which is structured query statement, is the standard language of relational database , at the same time,SQL is a common, powerful relational database language . Its characteristics are five, respectively

1 ) Integrated and unified

2 ) Highly non-procedural

3 A collection-oriented approach to Operation

4 ) provides multiple ways to use the same syntax structure

5 simple language, easy to learn and use

Let's talk about integrated unification :

n Set data definition language (DDL), Data Manipulation language (DML), Data Control Language (DCL) function in one.

n can complete all activities in the database life cycle independently:

• Define the relationship pattern, insert the data, and set up the database;
• Query and update the data in the database;
• Database refactoring and Maintenance
• Database security, integrity control, etc.

n when the user database is put into operation, the mode can be modified at any time, without affecting the operation of the data .

n data operator Unification

Then talk about highly non-procedural :

The data manipulation language of v non-relational data model " process oriented ", must establish access path

V SQL does not need to know the access path as long as it proposes "what to do".

The selection of the V Access path and the operation of SQL are done automatically by the system.

And then talk about the collection-oriented operation:

The V non-relational data model uses a record-oriented operation, and the object is a record

V SQL takes the set operation mode

• Action object, find result can be a collection of tuples
• An object of an INSERT, delete, update operation can be a collection of tuples

and say it again. provide multiple uses in the same grammatical structure

V SQL is a stand-alone language

The ability to use it independently for online interactions

V SQL is also embedded language

SQL can be embedded in a high-level language (such as a C,c++,java) program for programmers to design programs using

Finally speak the language concise, easy to learn to use

SQL is involved in the basic concepts, we also need to understand that the three-level schema structure diagram, the basic table, storage files, views, first understand the SQL support relational database of the three-level schema structure diagram:

Then look at the basic table:

tables that exist independently of themselves

A relationship in SQL corresponds to a basic table

One (or more) base table corresponding to one storage file

A table can take several indexes

And look at the storage file:

The logical structure makes up the inner mode of the relational database

The physical structure is arbitrary and transparent to the user

Finally look at the view:

tables exported from one or several base tables

Only the definition of the view is stored in the database and not the corresponding data of the view

View is a virtual table

The user can then define the view on the view

After understanding these basics, and then we go to the topic, that is, our statement, first of all, let's talk about our SQL data definition, SQL data definition function: schema (database) definition, table definition, view and index definition, the specific statement is as follows:

Hey, yo, say that. Four classifications of the data definition feature, now let's start with the definition and deletion of the schema (SQL Serverlar version):

Defined

Not to mention other, we directly on the code, simple and straightforward, easy to understand:

[Example 1] Define a student-course model S-T

  CREATE SCHEMA "S-t" AUTHORIZATION WANG;             Defines a schema for user Wang S-t

　　

[Example 2] CREATE schema AUTHORIZATION Wang;            < schema name > implied user name Wang

　　

Note: if the < Mode name >is not specified, then the < Mode name > implied < user name >

Analyze Our Model:

The V definition pattern actually defines a namespace (and then a database is built in the database)

V in this space you can define the database objects that the schema contains, such as basic tables, views, indexes, and so on.

V can accept the Create table,create view and the GRANT clause in the CREATE schema. Its statement block formula is as follows:

CREATE Schema < schema name > AUTHORIZATION < user name >[< table definition clause >|< VIEW definition clause >|< authorization definition clause;]

Example:

Create SCHEMA TEST AUTHORIZATION ZHANG    Create TABLE TAB1 (                       COL1 SMALLINT,                                            COL2 INT,                                            COL3 CHAR),                                            COL4 NUMERIC (10,3),                                            COL5 DECIMAL (5,2)                                          );    created a schema test for user Zhang and defined a table TAB1 in it.

　　

Delete

Delete is very simple, only need to grasp a statement syntax, as well as the statement of the two key words (cascading and restrictions), do not say other, directly summarized as follows:

DROP Schema < schema name > <cascade| Restrict>

CASCADE (Cascade)

Delete all the database objects in the schema while deleting the schema

RESTRICT (limit)

if the subordinate's database object (such as a table, view, and so on) is defined in the pattern, the execution of the DELETE statement is rejected.

executes when there are no subordinate objects in the pattern .

Then we say the second largest module of the data definition, the definition of the basic table, deletion and modification

Defined:

First, define the basic table

CREATE table < table name >

(< column name > < data type >[< column-level integrity constraints >]

[,< column names > < data types >[< column-level integrity constraints;]] ...

[,< table-level integrity constraints >]);

If the integrity constraint involves more than one property column for the table, it must be defined at the table level , otherwise it can be defined at the column level or at the table level.

Hey, look, see, there's a data type and constraint here, so what are the data types and what are the constraints? Then you have to look at the data type first.

Constraints are a bit more restrictive, and SQL has the following types of constraints:

And then I said some additions to the table are the relationship between the table and the pattern:

V Each of the basic tables belongs to a pattern (a database typically has multiple schemas)

V A pattern contains multiple base tables

V defines the schema to which the base table belongs

N method One: The schema name is clearly given in the table name

Create table "S-t". Student (...)   ）; /* Mode named s-t*/

Create table "S-t". Course (...) ）;

Create table "S-t". SC (...) ）;

N method Two: Create a table at the same time in the CREATE SCHEMA statement

N method Three: Set the owning mode

V when creating a base table (same as other database objects), if no schema is specified, the system determines the mode to which the object belongs based on the search path

V RDBMS uses the first existing pattern in the pattern list as the schema name for the database object

V If the pattern name in the search path does not exist, the system will give an error

V Displays the current search path: show Search_path;

The current default value for the V search path is: $user, public

V DBA user can set the search path and then define the base table

SET Search_path to "s-t", public;

Create Table Student (... ）;

Results The basic table of S-t.student was established.

When the first schema name S-t exists in the RDBMS Discovery search path, the

The schema is the schema that the base table student belongs to.

Modify

Its syntax is as follows:

ALTER table < table name >

[ADD < new column name > < data type > [integrity constraint]]

[ADD < mark integrity constraint;]

[DROP < column name;]

[DROP < integrity constraint name >]

[ALTER column< Column name > < data type >];

Where the ADD clause is used to add new columns, a new column-level integrity constraint, and a new table-level integrity constraint condition.

The drop column clause is used to delete a column in a table, and if a cascade phrase is specified, other objects that reference the column, such as a view, or a restrict phrase are automatically deleted, and if the column is referenced by another object, the RDBMS refuses to delete the column.

DROP CONSTRAINT clause is used to delete the specified integrity constraint condition.

ALTER COLUMN clauses used to modify the existing column definition, including modifying the column name ( ALTER TABLE test Change column address Address1 varchar (30)-Modify table column name ) and data type

Delete

DROP table < table name >[restrict| CASCADE];

N RESTRICT: There is a limit to the deletion of tables.

• ? The base table you want to delete cannot be referenced by constraints on other tables
• ? This table cannot be deleted if there are objects that depend on the table

N CASCADE: There is no limit to deleting the table.

• ? Deleting a base table while related dependent objects are deleted together

And then we're going to talk about the third module of data definition, the establishment and deletion of indexes, we need to know some basic knowledge before we say the index is established and deleted, as follows:

V Index Purpose: Speed up query

V who can build an 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

V who maintains the index

DBMS Auto-Complete  

V Use Index

DBMS automatically choose whether to use indexes and which indexes to use

The index of V RDBMS is generally implemented by B + Tree and hash index.

N B + Tree Index has the advantage of dynamic balance

n HASH Index has the characteristics of fast search speed

V using a B + tree or HASH index is determined by the specific RDBMS

V -Index is the internal implementation technology of relational database, which belongs to the scope of internal pattern

V CREATE index statement when defining indexes, you can define indexes as unique, non-unique, or clustered indexes

Hey, well, after we get to the basics, we get to the point of building and deleting.

Establish

V Statement format

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

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

[Example 13] CREATE CLUSTER INDEX stusname on Student (Sname) ;

• Create a clustered index on the sname (name) column of the student table

Sc table by number ascending and course number descending to create a unique index:

Create Unique index scno on SC ( Sno ASC , CNO desc ) ;

Considerations for building a table:

V Create clustered indexes on the most frequently queried columns to improve query efficiency

V only one clustered index can be built on a basic table

V frequently updated columns should not establish clustered index

modifying indexes

For an already established index, if you need to rename it, use the ALTER INDEX statement. The General format is:

ALTER Index < old index name > RENAME to < new index name >;

Delete Index

DROP Index < index name >;

When you delete an index, the system deletes the index from the data dictionary.

Describe.

[Example 15] Delete the Stusname index of the student table

DROP INDEX Stusname;

Database Statement (i)