When learning database, you will encounter a confusing concept of Schema. In fact, schema is a collection of database objects, this collection contains various objects such as: tables, views, stored procedures, indexes, etc..
If we consider database as a warehouse, there are many rooms in the warehouse (schema), a schema represents a room, table can be seen 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 to say, each database mapping user has the key to each schema (room).
By default a user corresponds to a collection, the user’s schema name is equal to the user name and is used as the default schema for that user. so the schema collection looks like the user name. When accessing a table, the system automatically adds the default schema if it is not specified which schema the table belongs to. the full name of an object is schema.object and does not belong to user.object.
MySQL
Creating a Schema in MySQL seems to have the same effect as creating a Database, but the effect is different in SQL Server and Oracle databases.
SQL Server
In SQL Server, there is always an implicit relationship between user and schema, so that we seldom realize that user and schema are two completely different concepts. If we create a user Bosco in a database, then the schema [Bosco] is also created for us by default, and the name of the schema is the same as the name of the user.
In SQL Server 2005, for backward compatibility, when creating a user with the sp_adduser procedure, sqlserver 2005 also creates a schema with the same user name, however, this procedure is reserved for backward compatibility, when we create a database user with create user, we When we create a database user with create user, we can use the user to specify an existing schema as the default schema, if we do not specify, the user’s default schema is the dbo schema, dbo room (schema) is like a large common room, in the current login user does not have the default schema, if you do some operations in the large repository, such as create table, if you do not have a specific room (schema), then your items will have to be put into the public dbo room (schema). But if the currently logged-in user has a default schema, then all the operations done are on the default schema.
Oracle
You can’t create a new schema in Oracle database, and the only way to create a schema is to create a user, and create a schema with the same name as the user’s name and use it as the default schema for that user. name corresponds to the user name and is the same.
PostgreSQL
The concept of schema in PostgreSQL is the same as the concept of Database in MySQL. In MYSQL, you can access multiple Database objects at the same time, but in PostgreSQL, you can only access one Database object, but in PostgreSQL, you can access multiple Scheme objects.