Test for database Engineers (4)-statements for viewing the database and table structure in MySQL and SQL Server

Understand the number of databases in the current system, the tables contained in each database, and the structure of each table. However, these operations are not developed using SQL standards. The policies adopted by various DBMS are also different, and the interfaces exposed to users are different. The following uses MySQL and SQL Server as examples.

 

First, where is the information stored? The answer is that the information is stored in tables like the user business data. However, because the data is crucial, users are generally not allowed to directly participate in the access, but the DBMS is responsible for accessing the corresponding system table. Since normal users are not allowed to perform operations directly, some measures must be provided to help users understand the data to a certain extent.

MySQL converts these operations into a group of commands for users to use, while MSSQL combines the corresponding query commands into a stored procedure and stores them in the system for users to call.

 

(1) view the databases in the current system

MySQL: Show databases;

MSSQL: sp_databases;

 

(2) Select the current database

MySQL: use the current database name;

MSSQL: use the current database name;

 

(3) view the tables in the current database

MySQL: show tables;

MSSQL: sp_tables;

 

(4) view the structure of a table

MySQL: Describe table name;

MSSQL: sp_columns table name;

For business data processing, though different DBMS systems are different, they all follow basic SQL standards, so cross-DBMS usage is not a problem.