3-2 data definition

3-2 data definition

The relational database system supports the three-level pattern structure, its mode, the outer mode and the inner mode of single Basic object humor Shibiao, view and index etc. so the data definition functions of SQL include, schema definition, table definition, view and index definition.

manipulating Objects	Create	Delete	Modify
Mode	Create schema	Drop schema
Table	CREATE table	drop table	ALTER TABLE
View	CREATE view	Drop view
Index	CREATE index	Drop Index	Alter

Schemas (schema Schema)

To avoid confusing the use of schemas with the schema domain database logical pattern

The concept of architecture

A schema is a collection of database entities that form a single namespace. A namespace is a collection in which the name of each element is unique. Official description of Microsoft (MSDN): "The database schema is a distinct namespace independent of the database user, and you can treat the schema as a container for the object", for a detailed reference to [http://technet.microsoft.com/zh-cn/library/ Ms190387.aspx] (Microsoft SQL Server documentation)

The meaning of architecture/the benefits of architecture and user separation

In the past, objects in SQL Server were named "server. Database. User name. Object", but the new version of the object name changed to "server. Database. Schema. Object". This makes it more resilient for you to plan database object naming.
Separating the schema from the database user has the following benefits for administrators and developers:
1. Multiple users can have a schema through role membership or Windows group membership. This expands the functionality that allows users of roles and groups to have objects that are familiar to them.
2. Greatly simplifies the operation of deleting database users.
3. Deleting a database user does not need to rename the objects contained in the user schema. Therefore, after you delete the users who created the objects that the schema contains, you no longer need to modify and test the applications that explicitly reference those objects.
4. Multiple users can share a default schema for unified name resolution.
5. Developers can store shared objects in a schema that is created specifically for a particular application, rather than in the DBO schema, by sharing the default schema.
6. You can manage permissions on the objects contained in the schema and schema with a granularity greater than the granularity in earlier versions.

Schema-related SQL definition patterns

create schema <模式名> authorization <用户名>

Authorization, Licensing
To create a pattern, the user who invokes the command must have database administrator privileges, or have access to the Creare schema granted by the database administrator.
If you do not specify a schema name, the schema name defaults to the user name.
Defining a schema actually defines a namespace in which additional database objects such as view table grant can be further defined
The CREATE TABLE View Grant clause can be accepted in the Create schema.

Delete mode

drop schema <模式名> <cascade|restrict>

where Cascade (Cascade) and restrict (limit) are required, cascade means that the database object in the schema is deleted while the schema is deleted, and restrict indicates that if a subordinate's database object is already defined in the schema, The execution of the DELETE statement is rejected. The DROP SCHEMA statement can be executed only if there are no subordinate objects in the pattern.

Oracle and SQL Server architecture differences

Schema in SQL Server
A user in SQL Server has a default schema whose schema name equals the username, which is why we see the schema names as database usernames under the Enterprise Manager scenario. Of course, a user can also use other schemas. If we visit a table and do not indicate which schema the table belongs to, the system automatically adds the default Sheman name to the table. For example, when we access the database, we access the EMP table under the Scott user, through the select * from EMP; In fact, the complete syntax for this SQL statement is select * from Scott.emp. The full name of an object in the database is Schema.object, not user.object. Similar if we do not specify the schema of the object when creating the object, the schema of the object is the user's default schema. This is like a user with a default tablespace, but the user can also use other tablespaces, and if we do not specify a tablespace when we create the object, the object is stored in the default tablespace, and in order for the object to be stored in another table space, we need to specify the table space for the object when it is created.

Schema in Oracle
The schema in Oracle refers to the collection of all objects under a user, the schema itself cannot be understood as an object, and Oracle does not provide the syntax to create the schema, and the schema is not created when the user is created. Instead, after creating the first object under the user, the schema is also generated, so long as the object exists in user, the schema must exist, the schema does not exist if there is no object under User, and if a new user is created, the schema does not exist if there is no object under the user. If you create an object, the schema with the same name as the user is also generated. In fact, in use, Shcema is exactly the same as the user, there is no difference, the user name can appear where the schema name appears.

Basic table table tables related SQL definition tables

create table <表名> (<列名><数据类型>[列级完整性约束条件]                    [,<列名><数据类型>[列级完整性约束条件]]                    [,<列名><数据类型>[列级完整性约束条件]]                    [,<列名><数据类型>[列级完整性约束条件]]                    [表级完整性约束条件]     );create table t_course(  cno cahr(4) primary key,  /*列级完整性约束cno是主码*/                        cname char(40) not null, /*列级完整性约束,cname不能取空值*/                        cpno char(4), /*cpno的含义是先修课*/                        ccredit smallint,                    foreign key(cpno) references t_course(cno) /*表级完整性约束条件,cpno是外码*/                                        )

Check Constraint

A CHECK constraint is used to limit the range of values in a column. If you define a CHECK constraint on a single column, the column only allows a specific value. If a CHECK constraint is defined on a table, the constraint restricts the value in a specific column.

CREATE TABLE 表名(   销售商 VARCHAR(20) NOT NULL CHECK(销售商 LIKE ‘%销售公司‘),                    电话 VARCHAR(8) CHECK(电话 LIKE ‘[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]‘),                    销售日期 DATE NOT NULL DEFAULT GETDATE())create(sex char check(sex=‘男‘ or sex=‘女‘) not null )alter table 表名 add constraint 约束名（sex_check） check(列名=‘男’or 列名=‘女’)

Modify the basic

alter table <表名>[add [column] <新列名> <数据类型> [完整性约束]][add <表级完整性约束>][drop [column] <列名> [cascade|restrict]][drop constraint<完整性约束名> [cascade|restrict]][alter column<列名><数据类型>]alter table student add s_time date

The newly added column is null regardless of whether the original data already exists in the base table.
The drop column clause is used to delete a column in a table, and if a cascade phrase is specified, the other objects to which the column is applied are automatically deleted, and if the restrict phrase is specified, the RDBMS rejects the deletion of the column if it is owed to his object reference.
The ADD clause is used to add new columns, new column levels, and table-level integrity constraints.
The drop CONSTRAINT clause is used to delete the specified integrity constraint condition.
The ALTER COLUMN clause modifies the original column definition, including modifying the column name and data type.

Delete base table

drop table <表名> [restrict|cascade]

The concept of index index

Query operations can be time-consuming when the table has a large amount of data. Indexing is an effective means of speeding up queries. A database index is similar to the index behind a book, and can quickly navigate to the content you are querying. Users can set up one or more indexes on the base table as needed to provide multiple access paths for faster lookups.

Type of index

Index type
? Unique index: Unique index does not allow two rows to have the same index value
? Primary KEY index: Defining a primary key for a table will automatically create a primary key index, which is a special type of unique index. The primary key index requires that each value in the primary key is unique and cannot be empty
? Clustered index (Clustered): The physical order of the rows in the table is the same as the logical (indexed) Order of the key values, and each table can have only one
? Nonclustered indexes (non-clustered): Nonclustered indexes Specify the logical order of tables. The data is stored in one place, the index is stored in a different location, and the index contains a pointer to the location of the data store. can have multiple, less than 249

Unique index

A unique index does not allow two rows to have the same index value. If duplicate key values exist in the existing data, most databases do not allow the newly created unique index to be saved with the table. The database also rejects this data when the new data duplicates the key values in the table. For example, if you create a unique index on the learner ID number (STUID) column in the Stuinfo table, the ID number of all learners cannot be duplicated.
Tip: A unique constraint is created and a unique index is created automatically. Although a unique index can help you find information, it is recommended to use a PRIMARY KEY constraint or a unique constraint for best performance.

Primary key Index

Defining a primary key for a table in a database diagram automatically creates a primary key index, which is a special type of unique index. The primary key index requires that each value in the primary key be unique. When a primary key index is used in a query, it also allows for fast access to the data.

Clustered indexes (clustered index)

In a clustered index, the physical order of the rows in the table is the same as the logical (indexed) Order of the key values. A table can contain only one clustered index. For example, the Chinese word (word) code defaults to alphabetical order of each page number in the dictionary. Phonetic Alphabet A,b,c,d......x,y,z is the logical order of the index, and the page number is ... is the physical order. The dictionary, which is sorted by phonetic Alphabet by default, has the same index order and logical order. That is, the phonetic order of the word (word) corresponding to the page number is also larger. If the phonetic "ha" corresponding to the word (Word) page number is more than the phonetic "ba" corresponding to the word (Word) page number.

Nonclustered indexes (non-clustered)

If it is not a clustered index, the physical order of the rows in the table does not match the logical order of the key values. Clustered indexes have faster data access speeds than nonclustered indexes (nonclustered index). For example, an index sorted by strokes is a nonclustered index, and the word "1" (word) corresponds to a page number that corresponds to the number of words (the word) that the "3" is drawn on (back).
Tip: In SQL Server, a table can only create 1 clustered indexes, multiple nonclustered indexes. Set a column's primary key, which defaults to a clustered index

Advantages and disadvantages of indexes

Advantages and disadvantages of indexes
? Advantages
– Speed up access
– Enhance the uniqueness of the row
? Disadvantages
– Indexed tables require more storage space in the database
– Commands that manipulate data require longer processing time because they require updates to the index

Guidelines for creating indexes

Guidelines for creating indexes
? Select an indexed column according to the following criteria.
– This column is used for frequent searches
– This column is used to sort the data
? Do not create an index using the following columns:
– Only a few different values are included in the column.
– The table contains only a few rows. Creating an index on a small table may not be a good deal because SQL Server spends more time searching for data in the index than it does in a row-by-line search in the table

Index-dependent SQL CREATE index

CREATE [UNIQUE] [CLUSTERED|NONCLUSTERED]INDEX   index_name ON table_name (column_name…)  [WITH FILLFACTOR=x]  CREATE NONCLUSTERED INDEX IX_writtenExam ON stuMarks(writtenExam)      WITH FILLFACTOR= 30/*-----指定按索引 IX_writtenExam 查询----*/SELECT * FROM stuMarks  (INDEX=IX_writtenExam)WHERE writtenExam BETWEEN 60 AND 90

Although we can specify which index SQL Server will query for data, we do not normally need to specify it manually. SQL Server automatically optimizes queries based on the indexes we create
Unique represents a single index, optional
CLUSTERED, nonclustered represents a clustered or nonclustered index, optional
FILLFACTOR represents a fill factor, specifying a value between 0 and 100 that indicates the percentage of space that the index page fills up

modifying indexes

ALTER INDEX < old index name > Rename to < new index name >

Delete Index

Once an index is established, it is used and maintained by the system without user intervention. Indexing is done to reduce the time of the query operation, but if the data is increased, deleted, and operated frequently, the system will spend a lot of time to maintain the index, thus reducing the efficiency of the query. You can delete some unnecessary indexes at this point.

Data dictionary

The data dictionary is a set of system tables inside the relational database system, which records all the definition information in the database including the relational schema definition, the view definition, the index definition, the complete constraint definition, the various kinds of user to the database operation right statistic information and so on. Shutdown database management system when executing SQL data definition statements, is actually updating the corresponding information in the data dictionary. The information in the data dictionary is an important basis for query optimization and processing.

3-2 data definition