create schema infomation_schema_test;
set current_schema = infomation_schema_test;
CREATE TABLE employees (emp_id INT, emp_name VARCHAR(50) NOT NULL) COMMENT='公司员工基本信息表';
CREATE TABLE employees2 (emp_id INT, emp_name VARCHAR(50) NOT NULL) COMMENT='Basic Information Form of Company Employees';
CREATE TABLE employees3 (emp_id INT primary key AUTO_INCREMENT, emp_name VARCHAR(50) NOT NULL COMMENT 'this is a comment1');
NOTICE: CREATE TABLE will create implicit sequence "employees3_emp_id_seq" for serial column "employees3.emp_id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "employees3_pkey" for table "employees3"
CREATE TABLE employees4 (emp_id INT primary key AUTO_INCREMENT COMMENT 'this is a comment2', emp_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
NOTICE: CREATE TABLE will create implicit sequence "employees4_emp_id_seq" for serial column "employees4.emp_id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "employees4_pkey" for table "employees4"
select * from information_schema.tables where table_schema = 'infomation_schema_test' and table_name = 'employees';
--?.*
--?.*
--?.*
(1 row)
select * from information_schema.tables where table_schema = 'infomation_schema_test' and table_name = 'employees2';
--?.*
--?.*
--?.*
(1 row)
select * from information_schema.columns where table_schema = 'infomation_schema_test' and table_name = 'employees3' and column_name = 'emp_id';
table_catalog | table_schema | table_name | column_name | ordinal_position | column_default | is_nullable | data_type | character_maximum_length | character_octet_length | numeric_precision | numeric_precision_radix | numeric_scale | datetime_precision | interval_type | interval_precision | character_set_catalog | character_set_schema | character_set_name | collation_catalog | collation_schema | collation_name | domain_catalog | domain_schema | domain_name | udt_catalog | udt_schema | udt_name | scope_catalog | scope_schema | scope_name | maximum_cardinality | dtd_identifier | is_self_referencing | is_identity | identity_generation | identity_start | identity_increment | identity_maximum | identity_minimum | identity_cycle | is_generated | generation_expression | is_updatable | column_type | column_comment | extra | privileges | column_key | srs_id
--------------------+------------------------+------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+-------------------------+---------------+--------------------+---------------+--------------------+-----------------------+----------------------+--------------------+-------------------+------------------+----------------+----------------+---------------+-------------+--------------------+------------+----------+---------------+--------------+------------+---------------------+----------------+---------------------+-------------+---------------------+----------------+--------------------+------------------+------------------+----------------+--------------+-----------------------+--------------+-------------+----------------+----------------+---------------------------------+------------+--------
contrib_regression | infomation_schema_test | employees3 | emp_id | 1 | AUTO_INCREMENT | NO | integer | | | 32 | 2 | 0 | | | | | | | | | | | | | contrib_regression | pg_catalog | int4 | | | | | 1 | NO | NO | | | | | | | NEVER | | YES | integer | | AUTO_INCREMENT | select,insert,update,references | PRI |
(1 row)
select * from information_schema.columns where table_schema = 'infomation_schema_test' and table_name = 'employees4' and column_name = 'emp_id';
table_catalog | table_schema | table_name | column_name | ordinal_position | column_default | is_nullable | data_type | character_maximum_length | character_octet_length | numeric_precision | numeric_precision_radix | numeric_scale | datetime_precision | interval_type | interval_precision | character_set_catalog | character_set_schema | character_set_name | collation_catalog | collation_schema | collation_name | domain_catalog | domain_schema | domain_name | udt_catalog | udt_schema | udt_name | scope_catalog | scope_schema | scope_name | maximum_cardinality | dtd_identifier | is_self_referencing | is_identity | identity_generation | identity_start | identity_increment | identity_maximum | identity_minimum | identity_cycle | is_generated | generation_expression | is_updatable | column_type | column_comment | extra | privileges | column_key | srs_id
--------------------+------------------------+------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+-------------------------+---------------+--------------------+---------------+--------------------+-----------------------+----------------------+--------------------+-------------------+------------------+----------------+----------------+---------------+-------------+--------------------+------------+----------+---------------+--------------+------------+---------------------+----------------+---------------------+-------------+---------------------+----------------+--------------------+------------------+------------------+----------------+--------------+-----------------------+--------------+-------------+--------------------+----------------+---------------------------------+------------+--------
contrib_regression | infomation_schema_test | employees4 | emp_id | 1 | AUTO_INCREMENT | NO | integer | | | 32 | 2 | 0 | | | | | | | | | | | | | contrib_regression | pg_catalog | int4 | | | | | 1 | NO | NO | | | | | | | NEVER | | YES | integer | this is a comment2 | AUTO_INCREMENT | select,insert,update,references | PRI |
(1 row)
select * from information_schema.columns where table_schema = 'infomation_schema_test' and table_name = 'employees3' and column_name = 'emp_name';
table_catalog | table_schema | table_name | column_name | ordinal_position | column_default | is_nullable | data_type | character_maximum_length | character_octet_length | numeric_precision | numeric_precision_radix | numeric_scale | datetime_precision | interval_type | interval_precision | character_set_catalog | character_set_schema | character_set_name | collation_catalog | collation_schema | collation_name | domain_catalog | domain_schema | domain_name | udt_catalog | udt_schema | udt_name | scope_catalog | scope_schema | scope_name | maximum_cardinality | dtd_identifier | is_self_referencing | is_identity | identity_generation | identity_start | identity_increment | identity_maximum | identity_minimum | identity_cycle | is_generated | generation_expression | is_updatable | column_type | column_comment | extra | privileges | column_key | srs_id
--------------------+------------------------+------------+-------------+------------------+----------------+-------------+-------------------+--------------------------+------------------------+-------------------+-------------------------+---------------+--------------------+---------------+--------------------+-----------------------+----------------------+--------------------+--------------------+------------------+--------------------+----------------+---------------+-------------+--------------------+------------+----------+---------------+--------------+------------+---------------------+----------------+---------------------+-------------+---------------------+----------------+--------------------+------------------+------------------+----------------+--------------+-----------------------+--------------+-------------------+--------------------+-------+---------------------------------+------------+--------
contrib_regression | infomation_schema_test | employees3 | emp_name | 2 | | NO | character varying | 50 | 200 | | | | | | | | | | contrib_regression | pg_catalog | utf8mb4_general_ci | | | | contrib_regression | pg_catalog | varchar | | | | | 2 | NO | NO | | | | | | | NEVER | | YES | character varying | this is a comment1 | | select,insert,update,references | |
(1 row)
select * from information_schema.columns where table_schema = 'infomation_schema_test' and table_name = 'employees4' and column_name = 'emp_time';
table_catalog | table_schema | table_name | column_name | ordinal_position | column_default | is_nullable | data_type | character_maximum_length | character_octet_length | numeric_precision | numeric_precision_radix | numeric_scale | datetime_precision | interval_type | interval_precision | character_set_catalog | character_set_schema | character_set_name | collation_catalog | collation_schema | collation_name | domain_catalog | domain_schema | domain_name | udt_catalog | udt_schema | udt_name | scope_catalog | scope_schema | scope_name | maximum_cardinality | dtd_identifier | is_self_referencing | is_identity | identity_generation | identity_start | identity_increment | identity_maximum | identity_minimum | identity_cycle | is_generated | generation_expression | is_updatable | column_type | column_comment | extra | privileges | column_key | srs_id
--------------------+------------------------+------------+-------------+------------------+-----------------------------------+-------------+--------------------------+--------------------------+------------------------+-------------------+-------------------------+---------------+--------------------+---------------+--------------------+-----------------------+----------------------+--------------------+-------------------+------------------+----------------+----------------+---------------+-------------+--------------------+------------+-------------+---------------+--------------+------------+---------------------+----------------+---------------------+-------------+---------------------+----------------+--------------------+------------------+------------------+----------------+--------------+-----------------------+--------------+--------------------------+----------------+-----------------------------------------------------------------+---------------------------------+------------+--------
contrib_regression | infomation_schema_test | employees4 | emp_time | 2 | b_db_statement_start_timestamp(0) | YES | timestamp with time zone | | | | | | 0 | | | | | | | | | | | | contrib_regression | pg_catalog | timestamptz | | | | | 2 | NO | NO | | | | | | | NEVER | | YES | timestamp with time zone | | DEFAULT_GENERATED on update 'b_db_statement_start_timestamp(0)' | select,insert,update,references | |
(1 row)
drop table employees;
drop table employees2;
drop table employees3;
drop table employees4;
select * from information_schema.engines;
engine | support | comment | transactions | xa | savepoints
--------------------+---------+----------------------------------------------------------------+--------------+-----+------------
MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO
InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES
PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO
MyISAM | YES | MyISAM storage engine | NO | NO | NO
ndbinfo | NO | MySQL Cluster system information storage engine | | |
MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO
BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO
CSV | YES | CSV storage engine | NO | NO | NO
ARCHIVE | YES | Archive storage engine | NO | NO | NO
ndbcluster | NO | Clustered, fault-tolerant tables | | |
(10 rows)
reset current_schema;
drop schema infomation_schema_test;