What is the difference between schema and database

Creating a schema in MySQL seems to have the same effect as creating a database, which looks different in SQL Server and Orcal databases. At the moment I can only understand that schema<==>database in MySQL.

The relationship between user and schema in the database

If we want to know what the user and schema are like in a database, we first have to look at what the user and schema are in the database.

In SQL Server2000, because of the schema, there is always an implicit relationship between user and schema, which makes it very rare to realize that user and schema are two completely different concepts, but in SQL Server2005 this architecture is broken, The user and schema are also separated.

First I'll do a metaphor, what is database, what is a schema, what is a table, what is a column, what is a row, what is a user? We can think of database as a large warehouse, the warehouse has a lot of rooms, the schema is one of the rooms, a schema represents a room, table can be considered as a bed in each schema, table (bed) is placed in each room, Cannot be placed outside the room, that is not the night sleep homeless J. , and then the bed can be placed a lot of items, like the table can be placed a lot of columns and rows, the database is stored in the basic unit of data is a table, in reality, each warehouse placed in the basic unit of the article is the bed, user is the owner of each schema, (So the schema contains object, not user), in fact, the user is corresponding to the database (that is, the user is the owner of each corresponding database), since there is the right to operate the database (warehouse), there must be operational database of each schema (room) right, That is, each database mapping user has a key to each schema (room), in other words, if he is the owner of a warehouse, then the use of the warehouse and everything in the warehouse is his (including the room), he has the full operation right, can throw away the unused things from each room, can also put some useful things to a room, hehe, and the reality is too similar to it. I can also assign specific permissions to the user, that is, what he can do in a room, can only see (read-only), or can be like the owner has all the control (r/w), this depends on the role of the user roles, as to the issue of assigning permissions, I'll leave it in detail in a separate blog. The analogy here, I believe everyone is clear.

In SQL Server2000, if we create a user Bosco in a database, pressing the background also creates the default schema "Bosco" for us by default. The name of the schema is the same as the name of user, which is why we don't know the user or schema.

In SQL Server2005, for backwards compatibility, when you create a user with a sp_adduser stored procedure, SQL Server2005 also creates a schema that is the same as the user name, but this stored procedure is reserved for backwards compatibility. We should become familiar with the new DDL language create user and create schema to manipulate the database. In SQL Server2005, when creating a database user with create user, we can specify an existing schema as the default schema for that user, and if we do not specify it, the default schema for that user is the dbo schema,dbo The room (schema) is like a large public room, if you do some operations in a large warehouse, such as Create Tabe, if you do not specify 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, then everything is done on the default schema (for example, the current logged on user is login1, the default schema for the user is login1, Then all of the operations are done on this login1 default schema. The experiment has proved to be true). Guess you're going to be a little dizzy at this point, why? I just said that dbo is a schema, but you can see in the database, dbo is also a user, dizzy it, hehe.

When creating a database in SQL Server2005, there will be some schema included, including the schema: Dbo,information_schema, Guest,sys, and so on (there are some character schema, do not mention, Have fainted).

As I mentioned earlier in SQL Server2005, when creating a user with a stored procedure sp_adduser, SQL Server2005 also creates a default schema for us with the same user name, which is the problem. When we create table A, if there is no specific schema prefix, which schema is created on this a-table, which room is entered? The answer is:

1. If the user of the current operational database (which can be identified with select Current_User) has a default schema (specified when the user is created), then table A is created on the default schema.

2. If the user of the current operational database does not have a default schema (that is, it is empty by default when creating user), but there is a schema with the same name as the user name, table A is created on the DBO schema even if there is a schema with the same name as the user name. Since it is not the default schema for the user, the creation of the table is not considered, as a general schema to deal with, although the name is the same, but there is no relationship oh.

3. If you specify a specific schema to prefix when creating table A, then table A is created on the specified schema (is there permission?). ）

Now the problem is out, in the current operation of the database user (with select Current_User can see, re-emphasize) without the default Schema, when we use the CREATE TABLE a statement, a will look for the DBO schema, And try to create it on the DBO schema, but what if the user who creates the a table has only read-only permission to the DBO schema, without the Write permission? At this time the A-table is neither established nor successful, this is the relationship between Login,user, role and schema that I will refer to later. Here, in order to avoid confusing and increasing the speed of manipulating the database (in a small amount of data, it is almost impossible for us to see the difference for our naked eye), it is best to explicitly specify a particular schema prefix every time you manipulate a database object.

Now if the logged-on user is Sue, the user has a default Schema also for Sue, so if there is now a query statement for SELECT * FROM MyTable, what is the order of the search for each room (Schema)? The order is as follows:

1. First search sys.mytable (Sys Schema)

2. Then search for sue.mytable (Default Schema)

3. Last Search dbo.mytable (dbo Schema)

Order of execution since everyone is clear, then in the Query database table data, it is better to specify a specific schema prefix, this way, the database does not have to scan the SYS schema, of course, can improve the speed of the query.

It is also necessary to note that, after each database is created, there are 4 schemas that are necessary (delete is not removed), the 4 schemas are: Dbo,guest,sys and Information_schema, the rest of the schema can be deleted.

Original link: http://blog.163.com/baibai_zheng/blog/static/51980755201022125325689/

What is the difference between schema and database