Memaparkan Lajur Jadual dan Kekangan NULL dalam SQL

When working with databases, it's often useful to know which columns in your tables allow NULL values and which do not. You can easily retrieve this information using SQL queries on the information schema of your database. Below, I'll show you how to do this for both MySQL and PostgreSQL.

MySQL

To get a list of columns and their NULL constraints for a specific table in MySQL, you can query the INFORMATION_SCHEMA.COLUMNS table. Here’s the query you can use:

SELECT COLUMN_NAME AS 'Column Name', IS_NULLABLE AS 'Is Nullable' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME = 'your_table_name' ORDER BY ORDINAL_POSITION;

Explanation:

• COLUMN_NAME: The name of the column.
• IS_NULLABLE: Indicates whether the column allows NULL values (YES means it allows NULL, NO means it does not allow NULL).
• TABLE_SCHEMA: The name of your database.
• TABLE_NAME: The name of your table.
• ORDINAL_POSITION: Ensures the columns are listed in the order they appear in the table schema.

PostgreSQL

Similarly, in PostgreSQL, you can query the information_schema.columns table to retrieve this information. Here’s the equivalent query:

SELECT column_name AS "Column Name", is_nullable AS "Is Nullable" FROM information_schema.columns WHERE table_schema = 'public' AND table_name = 'your_table_name' ORDER BY ordinal_position;

Explanation:

• column_name: The name of the column.
• is_nullable: Indicates whether the column allows NULL values (YES means it allows NULL, NO means it does not allow NULL).
• table_schema: Typically 'public' for most user-defined tables unless you are using a different schema.
• table_name: The name of your table.
• ordinal_position: Ensures the columns are listed in the order they appear in the table schema.

Example

Assume you have a table named users in a database named my_database. Here's how you would query the constraints:

MySQL Example:

SELECT COLUMN_NAME AS 'Column Name', IS_NULLABLE AS 'Is Nullable' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'my_database' AND TABLE_NAME = 'users' ORDER BY ORDINAL_POSITION;

PostgreSQL Example:

SELECT column_name AS "Column Name", is_nullable AS "Is Nullable" FROM information_schema.columns WHERE table_schema = 'public' AND table_name = 'users' ORDER BY ordinal_position;

Example Output:

Using these queries, you can easily check the NULL constraints of your table columns, helping you understand the schema and data requirements better.

Atas ialah kandungan terperinci Memaparkan Lajur Jadual dan Kekangan NULL dalam SQL. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!