Understanding of Schema (schema) concept in database

In the process of learning SQL, you will encounter a concept that confused your schema. In fact, a schema is a collection of database objects that contain objects such as tables, views, stored procedures, indexes, and so on. In order to distinguish different sets, it is necessary to give different names to each set, by default a user corresponds to a set, the user's schema name equals the user name, and is used as the default schema for the user. So the schema collection looks like a user name.

If you think of database as a warehouse, a lot of room (schema), a schema represents a room, table can be considered as a locker in each room, user is the owner of each schema, has the right to operate each room in the database, That is, each database mapping user has a key to each schema (room).

When we visit a table, we do not specify which schema the table belongs to, and the system automatically adds the default Sheman name to the table. The full name of an object in the database is Schema.object, not user.object.

The effect of creating a schema and creating a database in MySQL seems to be the same, but the effect is different in SQL Server and Orcal databases.

In SQL Server 2000, the user and schema always have a layer of implicit relationships, so we rarely realize that user and schema are two completely different concepts, if we create a user Bosco in a database, Then the background also created for our default schema "Bosco", the schema name and user name is the same.

In SQL Server 2005, for backwards compatibility, when creating a user with a sp_adduser stored procedure, sqlserver2005 also creates a schema that is the same as the user name, but this stored procedure is reserved for backwards compatibility. When we create a database user with the creation user, we can specify a schema that already exists as the default schema for that user, and if we do not specify it, the default schema for that user is the DBO schema. The dbo Room (schema) is like a large public room, if you do something in a large warehouse, such as CREATE TABLE, if you do not have a specific room (schema), if you do not have a default schema for the currently logged-on user, Then your belongings will have to be put into the public dbo room (schema). However, if the current logged-on user has a default schema, everything you do is done on the default schema.

A schema cannot be created in the Oracle database, and to create a schema, it can only be resolved by creating a user, creating a user with the same name as the user name and Schem as the default shcema for that user. That is, the number of schemas is the same as the number of user, and the schema name corresponds to the user name one by one and the same.

Excerpt from: Http://www.tuicool.com/articles/MJzaaey

Understanding of Schema (schema) concept in database