set dolphin.lower_case_table_names = 1;
CREATE TABLE departments (
dept_id INT PRIMARY KEY,
dept_name VARCHAR(50) NOT NULL UNIQUE
);
CREATE TABLE employees (
emp_id INT PRIMARY KEY,
emp_name VARCHAR(50) NOT NULL,
dept_id INT,
hire_date DATE,
CONSTRAINT fk_emp_dept
FOREIGN KEY (dept_id)
REFERENCES departments(dept_id)
ON DELETE SET NULL
ON UPDATE CASCADE
);
CREATE TABLE partition_table_test(c1 int, c2 int, c3 int)
PARTITION BY RANGE (c1) SUBPARTITION BY RANGE (c2)
(
PARTITION P_RANGE1 VALUES LESS THAN (5)
(
SUBPARTITION P_RANGE1_1 VALUES LESS THAN (5),
SUBPARTITION P_RANGE1_2 VALUES LESS THAN (10),
SUBPARTITION P_RANGE1_3 VALUES LESS THAN (15),
SUBPARTITION P_RANGE1_4 VALUES LESS THAN (20)
),
PARTITION P_RANGE2 VALUES LESS THAN (10)
(
SUBPARTITION P_RANGE2_1 VALUES LESS THAN (5),
SUBPARTITION P_RANGE2_2 VALUES LESS THAN (10),
SUBPARTITION P_RANGE2_3 VALUES LESS THAN (15),
SUBPARTITION P_RANGE2_4 VALUES LESS THAN (20)
),
PARTITION P_RANGE3 VALUES LESS THAN (15)
(
SUBPARTITION P_RANGE3_1 VALUES LESS THAN (5),
SUBPARTITION P_RANGE3_2 VALUES LESS THAN (10),
SUBPARTITION P_RANGE3_3 VALUES LESS THAN (15),
SUBPARTITION P_RANGE3_4 VALUES LESS THAN (20)
),
PARTITION P_RANGE4 VALUES LESS THAN (20)
(
SUBPARTITION P_RANGE4_1 VALUES LESS THAN (5),
SUBPARTITION P_RANGE4_2 VALUES LESS THAN (10),
SUBPARTITION P_RANGE4_3 VALUES LESS THAN (15),
SUBPARTITION P_RANGE4_4 VALUES LESS THAN (20)
),
PARTITION P_RANGE5 VALUES LESS THAN (25),
PARTITION P_RANGE6 VALUES LESS THAN (30)
);
select CONSTRAINT_CATALOG,CONSTRAINT_SCHEMA,CONSTRAINT_NAME,TABLE_SCHEMA,TABLE_NAME,CONSTRAINT_TYPE,ENFORCED from information_schema.table_constraints order by 1,2,3,4 limit 1;
select CHARACTER_SET_NAME,DEFAULT_COLLATE_NAME,DESCRIPTION,MAXLEN from INFORMATION_SCHEMA.CHARACTER_SETS order by 1,2,3,4 limit 1;
select CONSTRAINT_CATALOG,CONSTRAINT_SCHEMA,CONSTRAINT_NAME,CHECK_CLAUSE FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS order by 1,2,3,4 limit 1;
select COLLATION_NAME,CHARACTER_SET_NAME,ID,IS_DEFAULT,IS_COMPILED,SORTLEN,PAD_ATTRIBUTE from INFORMATION_SCHEMA.COLLATIONS order by 1,2,3,4 limit 1;
select GRANTEE,TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,PRIVILEGE_TYPE,IS_GRANTABLE FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES order by 1,2,3,4 limit 1;
select 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_SCALE,DATETIME_PRECISION,CHARACTER_SET_NAME,COLLATION_NAME,COLUMN_TYPE,COLUMN_KEY,EXTRA,PRIVILEGES,COLUMN_COMMENT,GENERATION_EXPRESSION,SRS_ID from INFORMATION_SCHEMA.COLUMNS order by 1,2,3,4 limit 1;
select CONSTRAINT_CATALOG,CONSTRAINT_SCHEMA,CONSTRAINT_NAME,TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,ORDINAL_POSITION,POSITION_IN_UNIQUE_CONSTRAINT,REFERENCED_TABLE_SCHEMA,REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME from information_schema.key_column_usage order by 1,2,3,4 limit 1;
select WORD,RESERVED from INFORMATION_SCHEMA.KEYWORDS order by 1,2 limit 1;
select SPECIFIC_CATALOG,SPECIFIC_SCHEMA,SPECIFIC_NAME,ORDINAL_POSITION,PARAMETER_MODE,PARAMETER_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,CHARACTER_OCTET_LENGTH,NUMERIC_PRECISION,NUMERIC_SCALE,DATETIME_PRECISION,CHARACTER_SET_NAME,COLLATION_NAME,DTD_IDENTIFIER,ROUTINE_TYPE from INFORMATION_SCHEMA.PARAMETERS order by 1,2,3,4 limit 1;
select QUERY_ID,SEQ,STATE,DURATION,CPU_USER,CPU_SYSTEM,CONTEXT_VOLUNTARY,CONTEXT_INVOLUNTARY,BLOCK_OPS_IN,BLOCK_OPS_OUT,MESSAGES_SENT,MESSAGES_RECEIVED,PAGE_FAULTS_MAJOR,PAGE_FAULTS_MINOR,SWAPS,SOURCE_FUNCTION,SOURCE_FILE,SOURCE_LINE FROM information_schema.profiling order by 1,2,3,4 limit 1;
select CONSTRAINT_CATALOG,CONSTRAINT_SCHEMA,CONSTRAINT_NAME,UNIQUE_CONSTRAINT_CATALOG,UNIQUE_CONSTRAINT_SCHEMA,UNIQUE_CONSTRAINT_NAME,MATCH_OPTION,UPDATE_RULE,DELETE_RULE,TABLE_NAME,REFERENCED_TABLE_NAME from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS order by 1,2,3,4 limit 1;
select SPECIFIC_NAME, ROUTINE_CATALOG, ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, DATETIME_PRECISION, CHARACTER_SET_NAME, COLLATION_NAME, DTD_IDENTIFIER, ROUTINE_BODY, ROUTINE_DEFINITION, EXTERNAL_NAME, EXTERNAL_LANGUAGE, PARAMETER_STYLE, IS_DETERMINISTIC, SQL_DATA_ACCESS, SQL_PATH, SECURITY_TYPE, CREATED, LAST_ALTERED, SQL_MODE , ROUTINE_COMMENT, DEFINER, CHARACTER_SET_CLIENT, COLLATION_CONNECTION, DATABASE_COLLATION from information_schema.routines order by 1,2,3,4 limit 1;
select CATALOG_NAME,SCHEMA_NAME,DEFAULT_CHARACTER_SET_NAME,DEFAULT_COLLATION_NAME,SQL_PATH,DEFAULT_ENCRYPTION from information_schema.schemata order by 1,2,3,4 limit 1;
select TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,NON_UNIQUE,INDEX_SCHEMA,INDEX_NAME,SEQ_IN_INDEX,COLUMN_NAME,COLLATION,CARDINALITY,SUB_PART,PACKED,NULLABLE,INDEX_TYPE,COMMENT,INDEX_COMMENT,IS_VISIBLE,EXPRESSION from INFORMATION_SCHEMA.STATISTICS order by 1,2,3,4 limit 1;
select GRANTEE,TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,PRIVILEGE_TYPE,IS_GRANTABLE from INFORMATION_SCHEMA.TABLE_PRIVILEGES order by 1,2,3,4 limit 1;
select TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE,ENGINE,VERSION,ROW_FORMAT,TABLE_ROWS,AVG_ROW_LENGTH,DATA_LENGTH,MAX_DATA_LENGTH,INDEX_LENGTH,DATA_FREE,AUTO_INCREMENT,CREATE_TIME,UPDATE_TIME,CHECK_TIME,TABLE_COLLATION,CHECKSUM,CREATE_OPTIONS,TABLE_COMMENT from information_schema.tables order by 1,2,3,4 limit 1;
select THREAD_ID,NAME,TYPE,PROCESSLIST_ID,PROCESSLIST_USER,PROCESSLIST_HOST,PROCESSLIST_DB,PROCESSLIST_COMMAND,PROCESSLIST_TIME,PROCESSLIST_STATE,PROCESSLIST_INFO,PARENT_THREAD_ID,ROLE,INSTRUMENTED,HISTORY,CONNECTION_TYPE,THREAD_OS_ID,RESOURCE_GROUP,EXECUTION_ENGINE,CONTROLLED_MEMORY,MAX_CONTROLLED_MEMORY,TOTAL_MEMORY,MAX_TOTAL_MEMORY,TELEMETRY_ACTIVE from performance_schema.threads order by 1,2,3,4 limit 1;
select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, SUBPARTITION_NAME, PARTITION_ORDINAL_POSITION, SUBPARTITION_ORDINAL_POSITION, PARTITION_METHOD, SUBPARTITION_METHOD, PARTITION_EXPRESSION, SUBPARTITION_EXPRESSION, PARTITION_DESCRIPTION, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH, MAX_DATA_LENGTH, INDEX_LENGTH, DATA_FREE, CHECK_TIME, CHECKSUM, PARTITION_COMMENT, NODEGROUP, TABLESPACE_NAME from information_schema.partitions order by 1,2,3,4 limit 1;
select count(*) from information_schema.schemata where schema_name = 'abc';
select count(*) from information_schema.tables where table_schema = 'abc' and table_name = 'abc';
SELECT c.COLLATION_NAME, c.CHARACTER_SET_NAME, c.ID, cs.MAXLEN, c.IS_DEFAULT='Yes' from INFORMATION_SCHEMA.COLLATIONS AS c LEFT JOIN INFORMATION_SCHEMA.CHARACTER_SETS AS cs ON cs.CHARACTER_SET_NAME=c.CHARACTER_SET_NAME limit 1;
SELECT SQL_DATA_ACCESS FROM information_schema.routines WHERE routine_schema = 'abc' AND routine_name = 'abc';
SELECT A.REFERENCED_TABLE_SCHEMA AS PKTABLE_CAT,NULL AS PKTABLE_SCHEM, A.REFERENCED_TABLE_NAME AS PKTABLE_NAME, A.REFERENCED_COLUMN_NAME AS PKCOLUMN_NAME, A.TABLE_SCHEMA AS FKTABLE_CAT,
NULL AS FKTABLE_SCHEM, A.TABLE_NAME AS FKTABLE_NAME, A.COLUMN_NAME AS FKCOLUMN_NAME, A.ORDINAL_POSITION AS KEY_SEQ,
CASE WHEN R.UPDATE_RULE='CASCADE' THEN 0 WHEN R.UPDATE_RULE='SET NULL' THEN 2 WHEN R.UPDATE_RULE='SET DEFAULT' THEN 4 WHEN R.UPDATE_RULE='RESTRICT' THEN 1 WHEN R.UPDATE_RULE='NO ACTION' THEN 3 ELSE 3 END AS UPDATE_RULE,
CASE WHEN R.DELETE_RULE='CASCADE' THEN 0 WHEN R.DELETE_RULE='SET NULL' THEN 2 WHEN R.DELETE_RULE='SET DEFAULT' THEN 4 WHEN R.DELETE_RULE='RESTRICT' THEN 1 WHEN R.DELETE_RULE='NO ACTION' THEN 3 ELSE 3 END AS DELETE_RULE,
A.CONSTRAINT_NAME AS FK_NAME, (SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_SCHEMA = A.REFERENCED_TABLE_SCHEMA AND TABLE_NAME = A.REFERENCED_TABLE_NAME AND CONSTRAINT_TYPE IN ('UNIQUE','PRIMARY KEY') LIMIT 1) AS PK_NAME,
7 AS DEFERRABILITY FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE A JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS B
USING (TABLE_SCHEMA, TABLE_NAME, CONSTRAINT_NAME) JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R ON (R.CONSTRAINT_NAME = B.CONSTRAINT_NAME AND R.TABLE_NAME = B.TABLE_NAME AND R.CONSTRAINT_SCHEMA = B.TABLE_SCHEMA)
WHERE B.CONSTRAINT_TYPE = 'FOREIGN KEY' AND A.REFERENCED_TABLE_NAME='abc' AND A.TABLE_NAME='abc' ORDER BY A.TABLE_SCHEMA, A.TABLE_NAME, A.ORDINAL_POSITION;
SELECT 2 AS SCOPE, COLUMN_NAME, CASE WHEN UPPER(DATA_TYPE) = 'DECIMAL' THEN 3 WHEN UPPER(DATA_TYPE) = 'DECIMAL UNSIGNED' THEN 3 WHEN UPPER(DATA_TYPE) = 'TINYINT' THEN IF(LOCATE('ZEROFILL', UPPER(COLUMN_TYPE)) = 0 AND LOCATE('UNSIGNED', UPPER(COLUMN_TYPE)) = 0 AND LOCATE('(1)', COLUMN_TYPE) != 0, -7, -6) WHEN UPPER(DATA_TYPE) = 'TINYINT UNSIGNED' THEN IF(LOCATE('ZEROFILL', UPPER(COLUMN_TYPE)) = 0 AND LOCATE('UNSIGNED', UPPER(COLUMN_TYPE)) = 0 AND LOCATE('(1)', COLUMN_TYPE) != 0, -7, -6) WHEN UPPER(DATA_TYPE) = 'BOOLEAN' THEN 16 WHEN UPPER(DATA_TYPE) = 'SMALLINT' THEN 5 WHEN UPPER(DATA_TYPE) = 'SMALLINT UNSIGNED' THEN 5 WHEN UPPER(DATA_TYPE) = 'INT' THEN 4 WHEN UPPER(DATA_TYPE) = 'INT UNSIGNED' THEN 4 WHEN UPPER(DATA_TYPE) = 'FLOAT' THEN 7 WHEN UPPER(DATA_TYPE) = 'FLOAT UNSIGNED' THEN 7 WHEN UPPER(DATA_TYPE) = 'DOUBLE' THEN 8 WHEN UPPER(DATA_TYPE) = 'DOUBLE UNSIGNED' THEN 8 WHEN UPPER(DATA_TYPE) = 'NULL' THEN 0 WHEN UPPER(DATA_TYPE) = 'TIMESTAMP' THEN 93 WHEN UPPER(DATA_TYPE) = 'BIGINT' THEN -5 WHEN UPPER(DATA_TYPE) = 'BIGINT UNSIGNED' THEN -5 WHEN UPPER(DATA_TYPE) = 'MEDIUMINT' THEN 4 WHEN UPPER(DATA_TYPE) = 'MEDIUMINT UNSIGNED' THEN 4 WHEN UPPER(DATA_TYPE) = 'DATE' THEN 91 WHEN UPPER(DATA_TYPE) = 'TIME' THEN 92 WHEN UPPER(DATA_TYPE) = 'DATETIME' THEN 93 WHEN UPPER(DATA_TYPE) = 'YEAR' THEN 91 WHEN UPPER(DATA_TYPE) = 'VARCHAR' THEN 12 WHEN UPPER(DATA_TYPE) = 'VARBINARY' THEN -3 WHEN UPPER(DATA_TYPE) = 'BIT' THEN -7 WHEN UPPER(DATA_TYPE) = 'JSON' THEN -1 WHEN UPPER(DATA_TYPE) = 'ENUM' THEN 1 WHEN UPPER(DATA_TYPE) = 'SET' THEN 1 WHEN UPPER(DATA_TYPE) = 'TINYBLOB' THEN -3 WHEN UPPER(DATA_TYPE) = 'TINYTEXT' THEN 12 WHEN UPPER(DATA_TYPE) = 'MEDIUMBLOB' THEN -4 WHEN UPPER(DATA_TYPE) = 'MEDIUMTEXT' THEN -1 WHEN UPPER(DATA_TYPE) = 'LONGBLOB' THEN -4 WHEN UPPER(DATA_TYPE) = 'LONGTEXT' THEN -1 WHEN UPPER(DATA_TYPE) = 'BLOB' THEN -4 WHEN UPPER(DATA_TYPE) = 'TEXT' THEN -1 WHEN UPPER(DATA_TYPE) = 'CHAR' THEN 1 WHEN UPPER(DATA_TYPE) = 'BINARY' THEN -2 WHEN UPPER(DATA_TYPE) = 'GEOMETRY' THEN -2 WHEN UPPER(DATA_TYPE) = 'VECTOR' THEN -4 WHEN UPPER(DATA_TYPE) = 'UNKNOWN' THEN 1111 WHEN UPPER(DATA_TYPE) = 'POINT' THEN -2 WHEN UPPER(DATA_TYPE) = 'LINESTRING' THEN -2 WHEN UPPER(DATA_TYPE) = 'POLYGON' THEN -2 WHEN UPPER(DATA_TYPE) = 'MULTIPOINT' THEN -2 WHEN UPPER(DATA_TYPE) = 'MULTILINESTRING' THEN -2 WHEN UPPER(DATA_TYPE) = 'MULTIPOLYGON' THEN -2 WHEN UPPER(DATA_TYPE) = 'GEOMETRYCOLLECTION' THEN -2 WHEN UPPER(DATA_TYPE) = 'GEOMCOLLECTION' THEN -2 ELSE 1111 END AS DATA_TYPE, UPPER(CASE WHEN UPPER(DATA_TYPE) = 'TINYINT' THEN CASE WHEN LOCATE('ZEROFILL', UPPER(COLUMN_TYPE)) = 0 AND LOCATE('UNSIGNED', UPPER(COLUMN_TYPE)) = 0 AND LOCATE('(1)', COLUMN_TYPE) != 0 THEN 'BIT' WHEN LOCATE('UNSIGNED', UPPER(COLUMN_TYPE)) != 0 AND LOCATE('UNSIGNED', UPPER(DATA_TYPE)) = 0 THEN 'TINYINT UNSIGNED' ELSE DATA_TYPE END WHEN LOCATE('UNSIGNED', UPPER(COLUMN_TYPE)) != 0 AND LOCATE('UNSIGNED', UPPER(DATA_TYPE)) = 0 AND LOCATE('SET', UPPER(DATA_TYPE)) <> 1 AND LOCATE('ENUM', UPPER(DATA_TYPE)) <> 1 THEN CONCAT(DATA_TYPE, ' UNSIGNED') WHEN UPPER(DATA_TYPE) = 'POINT' THEN 'GEOMETRY' WHEN UPPER(DATA_TYPE) = 'LINESTRING' THEN 'GEOMETRY' WHEN UPPER(DATA_TYPE) = 'POLYGON' THEN 'GEOMETRY' WHEN UPPER(DATA_TYPE) = 'MULTIPOINT' THEN 'GEOMETRY' WHEN UPPER(DATA_TYPE) = 'MULTILINESTRING' THEN 'GEOMETRY' WHEN UPPER(DATA_TYPE) = 'MULTIPOLYGON' THEN 'GEOMETRY' WHEN UPPER(DATA_TYPE) = 'GEOMETRYCOLLECTION' THEN 'GEOMETRY' WHEN UPPER(DATA_TYPE) = 'GEOMCOLLECTION' THEN 'GEOMETRY' ELSE UPPER(DATA_TYPE) END) AS TYPE_NAME, UPPER(CASE WHEN UPPER(DATA_TYPE) = 'YEAR' THEN 4 WHEN UPPER(DATA_TYPE) = 'DATE' THEN 10 WHEN UPPER(DATA_TYPE) = 'DATETIME' OR UPPER(DATA_TYPE) = 'TIMESTAMP' THEN 19 + IF(DATETIME_PRECISION > 0, DATETIME_PRECISION + 1, DATETIME_PRECISION) WHEN UPPER(DATA_TYPE) = 'TIME' THEN 8 + IF(DATETIME_PRECISION > 0, DATETIME_PRECISION + 1, DATETIME_PRECISION) WHEN UPPER(DATA_TYPE) = 'TINYINT' AND LOCATE('ZEROFILL', UPPER(COLUMN_TYPE)) = 0 AND LOCATE('UNSIGNED', UPPER(COLUMN_TYPE)) = 0 AND LOCATE('(1)', COLUMN_TYPE) != 0 THEN 1 WHEN UPPER(DATA_TYPE) = 'MEDIUMINT' AND LOCATE('UNSIGNED', UPPER(COLUMN_TYPE)) != 0 THEN 8 WHEN UPPER(DATA_TYPE) = 'JSON' THEN 1073741824 WHEN UPPER(DATA_TYPE) = 'GEOMETRY' THEN 65535 WHEN UPPER(DATA_TYPE) = 'POINT' THEN 65535 WHEN UPPER(DATA_TYPE) = 'LINESTRING' THEN 65535 WHEN UPPER(DATA_TYPE) = 'POLYGON' THEN 65535 WHEN UPPER(DATA_TYPE) = 'MULTIPOINT' THEN 65535 WHEN UPPER(DATA_TYPE) = 'MULTILINESTRING' THEN 65535 WHEN UPPER(DATA_TYPE) = 'MULTIPOLYGON' THEN 65535 WHEN UPPER(DATA_TYPE) = 'GEOMETRYCOLLECTION' THEN 65535 WHEN UPPER(DATA_TYPE) = 'GEOMCOLLECTION' THEN 65535 WHEN CHARACTER_MAXIMUM_LENGTH IS NULL THEN NUMERIC_PRECISION WHEN CHARACTER_MAXIMUM_LENGTH > 2147483647 THEN 2147483647 ELSE CHARACTER_MAXIMUM_LENGTH END) AS COLUMN_SIZE, 65535 AS BUFFER_LENGTH, UPPER(CASE WHEN UPPER(DATA_TYPE) = 'DECIMAL' THEN NUMERIC_SCALE WHEN UPPER(DATA_TYPE) = 'FLOAT' OR UPPER(DATA_TYPE) = 'DOUBLE' THEN IF(NUMERIC_SCALE IS NULL, 0, NUMERIC_SCALE) ELSE NULL END) AS DECIMAL_DIGITS, 1 AS PSEUDO_COLUMN FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'abc' AND TABLE_NAME = 'abc' AND COLUMN_KEY = 'PRI';
SELECT SCHEMA_NAME AS TABLE_CAT FROM INFORMATION_SCHEMA.SCHEMATA;
SELECT TABLE_SCHEMA AS TABLE_CAT, NULL AS TABLE_SCHEM, TABLE_NAME, COLUMN_NAME, NULL AS GRANTOR, GRANTEE, PRIVILEGE_TYPE AS PRIVILEGE,
IS_GRANTABLE FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES WHERE TABLE_SCHEMA LIKE 'abc' AND TABLE_NAME ='abc' AND COLUMN_NAME LIKE 'abc' ORDER BY COLUMN_NAME, PRIVILEGE_TYPE;
SELECT TABLE_SCHEMA AS TABLE_CAT, NULL AS TABLE_SCHEM, TABLE_NAME, COLUMN_NAME,
CASE WHEN UPPER(DATA_TYPE)='DECIMAL' THEN 3 WHEN UPPER(DATA_TYPE)='DECIMAL UNSIGNED' THEN 3 WHEN UPPER(DATA_TYPE)='TINYINT' THEN CASE WHEN LOCATE('(1)', COLUMN_TYPE) != 0 THEN -7 ELSE -6 END WHEN UPPER(DATA_TYPE)='TINYINT UNSIGNED' THEN CASE WHEN LOCATE('(1)', COLUMN_TYPE) != 0
THEN -7 ELSE -6 END WHEN UPPER(DATA_TYPE)='BOOLEAN' THEN 16 WHEN UPPER(DATA_TYPE)='SMALLINT' THEN 5 WHEN UPPER(DATA_TYPE)='SMALLINT UNSIGNED' THEN 5 WHEN UPPER(DATA_TYPE)='INT' THEN 4 WHEN UPPER(DATA_TYPE)='INT UNSIGNED'
THEN 4 WHEN UPPER(DATA_TYPE)='FLOAT' THEN 7 WHEN UPPER(DATA_TYPE)='FLOAT UNSIGNED' THEN 7 WHEN UPPER(DATA_TYPE)='DOUBLE' THEN 8 WHEN UPPER(DATA_TYPE)='DOUBLE UNSIGNED' THEN 8 WHEN UPPER(DATA_TYPE)='NULL' THEN 0
WHEN UPPER(DATA_TYPE)='TIMESTAMP' THEN 93 WHEN UPPER(DATA_TYPE)='BIGINT' THEN -5 WHEN UPPER(DATA_TYPE)='BIGINT UNSIGNED' THEN -5 WHEN UPPER(DATA_TYPE)='MEDIUMINT' THEN 4 WHEN UPPER(DATA_TYPE)='MEDIUMINT UNSIGNED'
THEN 4 WHEN UPPER(DATA_TYPE)='DATE' THEN 91 WHEN UPPER(DATA_TYPE)='TIME' THEN 92 WHEN UPPER(DATA_TYPE)='DATETIME' THEN 93 WHEN UPPER(DATA_TYPE)='YEAR' THEN 91 WHEN UPPER(DATA_TYPE)='VARCHAR' THEN 12
WHEN UPPER(DATA_TYPE)='VARBINARY' THEN -3 WHEN UPPER(DATA_TYPE)='BIT' THEN -7 WHEN UPPER(DATA_TYPE)='JSON' THEN -1 WHEN UPPER(DATA_TYPE)='ENUM' THEN 1 WHEN UPPER(DATA_TYPE)='SET' THEN 1
WHEN UPPER(DATA_TYPE)='TINYBLOB' THEN -3 WHEN UPPER(DATA_TYPE)='TINYTEXT' THEN 12 WHEN UPPER(DATA_TYPE)='MEDIUMBLOB' THEN -4 WHEN UPPER(DATA_TYPE)='MEDIUMTEXT' THEN -1 WHEN UPPER(DATA_TYPE)='LONGBLOB'
THEN -4 WHEN UPPER(DATA_TYPE)='LONGTEXT' THEN -1 WHEN UPPER(DATA_TYPE)='BLOB' THEN -4 WHEN UPPER(DATA_TYPE)='TEXT' THEN -1 WHEN UPPER(DATA_TYPE)='CHAR' THEN 1 WHEN UPPER(DATA_TYPE)='BINARY'
THEN -2 WHEN UPPER(DATA_TYPE)='GEOMETRY' THEN -2 WHEN UPPER(DATA_TYPE)='UNKNOWN' THEN 1111 WHEN UPPER(DATA_TYPE)='POINT' THEN -2 WHEN UPPER(DATA_TYPE)='LINESTRING' THEN -2 WHEN UPPER(DATA_TYPE)='POLYGON'
THEN -2 WHEN UPPER(DATA_TYPE)='MULTIPOINT' THEN -2 WHEN UPPER(DATA_TYPE)='MULTILINESTRING' THEN -2 WHEN UPPER(DATA_TYPE)='MULTIPOLYGON' THEN -2 WHEN UPPER(DATA_TYPE)='GEOMETRYCOLLECTION' THEN -2
WHEN UPPER(DATA_TYPE)='GEOMCOLLECTION' THEN -2 ELSE 1111 END AS DATA_TYPE, UPPER(CASE WHEN LOCATE('UNSIGNED', UPPER(COLUMN_TYPE)) != 0 AND LOCATE('UNSIGNED', UPPER(DATA_TYPE)) = 0 AND LOCATE('SET', UPPER(DATA_TYPE)) <> 1
AND LOCATE('ENUM', UPPER(DATA_TYPE)) <> 1 THEN CONCAT(DATA_TYPE, ' UNSIGNED') WHEN UPPER(DATA_TYPE)='TINYINT' THEN CASE WHEN LOCATE('(1)', COLUMN_TYPE) != 0 THEN 'BIT' WHEN LOCATE('UNSIGNED', UPPER(COLUMN_TYPE)) != 0
AND LOCATE('UNSIGNED', UPPER(DATA_TYPE)) = 0 THEN 'TINYINT UNSIGNED' ELSE DATA_TYPE END WHEN UPPER(DATA_TYPE)='POINT' THEN 'GEOMETRY' WHEN UPPER(DATA_TYPE)='LINESTRING' THEN 'GEOMETRY' WHEN UPPER(DATA_TYPE)='POLYGON'
THEN 'GEOMETRY' WHEN UPPER(DATA_TYPE)='MULTIPOINT' THEN 'GEOMETRY' WHEN UPPER(DATA_TYPE)='MULTILINESTRING' THEN 'GEOMETRY' WHEN UPPER(DATA_TYPE)='MULTIPOLYGON' THEN 'GEOMETRY' WHEN UPPER(DATA_TYPE)='GEOMETRYCOLLECTION'
THEN 'GEOMETRY' WHEN UPPER(DATA_TYPE)='GEOMCOLLECTION' THEN 'GEOMETRY' ELSE UPPER(DATA_TYPE) END) AS TYPE_NAME,UPPER(CASE WHEN UPPER(DATA_TYPE)='DATE' THEN 10 WHEN UPPER(DATA_TYPE)='TIME' THEN 16 WHEN UPPER(DATA_TYPE)='DATETIME'
THEN 26 WHEN UPPER(DATA_TYPE)='TIMESTAMP' THEN 26 WHEN UPPER(DATA_TYPE)='YEAR' THEN 4 WHEN UPPER(DATA_TYPE)='TINYINT' AND LOCATE('(1)', COLUMN_TYPE) != 0 THEN 1 WHEN UPPER(DATA_TYPE)='MEDIUMINT'
AND LOCATE('UNSIGNED', UPPER(COLUMN_TYPE)) != 0 THEN 8 WHEN UPPER(DATA_TYPE)='JSON' THEN 1073741824 WHEN UPPER(DATA_TYPE)='GEOMETRY' THEN 65535 WHEN UPPER(DATA_TYPE)='POINT' THEN 65535
WHEN UPPER(DATA_TYPE)='LINESTRING' THEN 65535 WHEN UPPER(DATA_TYPE)='POLYGON' THEN 65535 WHEN UPPER(DATA_TYPE)='MULTIPOINT' THEN 65535 WHEN UPPER(DATA_TYPE)='MULTILINESTRING' THEN 65535
WHEN UPPER(DATA_TYPE)='MULTIPOLYGON' THEN 65535 WHEN UPPER(DATA_TYPE)='GEOMETRYCOLLECTION' THEN 65535 WHEN UPPER(DATA_TYPE)='GEOMCOLLECTION' THEN 65535 WHEN CHARACTER_MAXIMUM_LENGTH IS NULL THEN NUMERIC_PRECISION
WHEN CHARACTER_MAXIMUM_LENGTH > 2147483647 THEN 2147483647 ELSE CHARACTER_MAXIMUM_LENGTH END) AS COLUMN_SIZE,65535 AS BUFFER_LENGTH,UPPER(CASE WHEN UPPER(DATA_TYPE)='DECIMAL' THEN NUMERIC_SCALE WHEN UPPER(DATA_TYPE)='FLOAT'
OR UPPER(DATA_TYPE)='DOUBLE' THEN CASE WHEN NUMERIC_SCALE IS NULL THEN 0 ELSE NUMERIC_SCALE END ELSE NULL END) AS DECIMAL_DIGITS,10 AS NUM_PREC_RADIX,UPPER(CASE WHEN IS_NULLABLE='NO' THEN 0 ELSE CASE WHEN IS_NULLABLE='YES'
THEN 1 ELSE 2 END END) AS NULLABLE,COLUMN_COMMENT AS REMARKS,COLUMN_DEFAULT AS COLUMN_DEF,0 AS SQL_DATA_TYPE,0 AS SQL_DATETIME_SUB,CASE WHEN CHARACTER_OCTET_LENGTH > 2147483647 THEN 2147483647
ELSE CHARACTER_OCTET_LENGTH END AS CHAR_OCTET_LENGTH,ORDINAL_POSITION, IS_NULLABLE, NULL AS SCOPE_CATALOG, NULL AS SCOPE_SCHEMA, NULL AS SCOPE_TABLE, NULL AS SOURCE_DATA_TYPE,IF (EXTRA LIKE '%auto_increment%','YES','NO') AS IS_AUTOINCREMENT,
IF (EXTRA LIKE '%GENERATED%','YES','NO') AS IS_GENERATEDCOLUMN FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'abc' AND TABLE_NAME = 'abc' AND COLUMN_NAME = 'abc' ORDER BY TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION;
SELECT A.REFERENCED_TABLE_SCHEMA AS PKTABLE_CAT, NULL AS PKTABLE_SCHEM, A.REFERENCED_TABLE_NAME AS PKTABLE_NAME, A.REFERENCED_COLUMN_NAME AS PKCOLUMN_NAME, A.TABLE_SCHEMA AS FKTABLE_CAT, NULL AS FKTABLE_SCHEM, A.TABLE_NAME AS FKTABLE_NAME,
A.COLUMN_NAME AS FKCOLUMN_NAME, A.ORDINAL_POSITION AS KEY_SEQ,CASE WHEN R.UPDATE_RULE='CASCADE' THEN 0 WHEN R.UPDATE_RULE='SET NULL' THEN 2 WHEN R.UPDATE_RULE='SET DEFAULT' THEN 4 WHEN R.UPDATE_RULE='RESTRICT'
THEN 1 WHEN R.UPDATE_RULE='NO ACTION' THEN 3 ELSE 3 END AS UPDATE_RULE,CASE WHEN R.DELETE_RULE='CASCADE' THEN 0 WHEN R.DELETE_RULE='SET NULL' THEN 2 WHEN R.DELETE_RULE='SET DEFAULT' THEN 4 WHEN R.DELETE_RULE='RESTRICT'
THEN 1 WHEN R.DELETE_RULE='NO ACTION' THEN 3 ELSE 3 END AS DELETE_RULE, A.CONSTRAINT_NAME AS FK_NAME, (SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_SCHEMA = A.REFERENCED_TABLE_SCHEMA
AND TABLE_NAME = A.REFERENCED_TABLE_NAME AND CONSTRAINT_TYPE IN ('UNIQUE','PRIMARY KEY') LIMIT 1) AS PK_NAME,7 AS DEFERRABILITY FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE A JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS B
USING (TABLE_SCHEMA, TABLE_NAME, CONSTRAINT_NAME) JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R ON (R.CONSTRAINT_NAME = B.CONSTRAINT_NAME AND R.TABLE_NAME = B.TABLE_NAME AND R.CONSTRAINT_SCHEMA = B.TABLE_SCHEMA)
WHERE B.CONSTRAINT_TYPE = 'FOREIGN KEY' AND A.REFERENCED_TABLE_SCHEMA LIKE 'abc' AND A.REFERENCED_TABLE_NAME='abc' ORDER BY A.TABLE_SCHEMA, A.TABLE_NAME, A.ORDINAL_POSITION;
SELECT SPECIFIC_SCHEMA AS FUNCTION_CAT, NULL AS `FUNCTION_SCHEM`, SPECIFIC_NAME AS `FUNCTION_NAME`, IFNULL(PARAMETER_NAME, '') AS `COLUMN_NAME`, CASE WHEN PARAMETER_MODE = 'IN' THEN 1 WHEN PARAMETER_MODE = 'OUT' THEN 3 WHEN PARAMETER_MODE = 'INOUT'
THEN 2 WHEN ORDINAL_POSITION = 0 THEN 4 ELSE 0 END AS `COLUMN_TYPE`, CASE WHEN UPPER(DATA_TYPE)='DECIMAL' THEN 3 WHEN UPPER(DATA_TYPE)='DECIMAL UNSIGNED' THEN 3 WHEN UPPER(DATA_TYPE)='TINYINT'
THEN CASE WHEN LOCATE('(1)', DTD_IDENTIFIER) != 0 THEN -7 ELSE -6 END WHEN UPPER(DATA_TYPE)='TINYINT UNSIGNED' THEN CASE WHEN LOCATE('(1)', DTD_IDENTIFIER) != 0 THEN -7 ELSE -6 END
WHEN UPPER(DATA_TYPE)='BOOLEAN' THEN 16 WHEN UPPER(DATA_TYPE)='SMALLINT' THEN 5 WHEN UPPER(DATA_TYPE)='SMALLINT UNSIGNED' THEN 5 WHEN UPPER(DATA_TYPE)='INT' THEN 4 WHEN UPPER(DATA_TYPE)='INT UNSIGNED'
THEN 4 WHEN UPPER(DATA_TYPE)='FLOAT' THEN 7 WHEN UPPER(DATA_TYPE)='FLOAT UNSIGNED' THEN 7 WHEN UPPER(DATA_TYPE)='DOUBLE' THEN 8 WHEN UPPER(DATA_TYPE)='DOUBLE UNSIGNED' THEN 8 WHEN UPPER(DATA_TYPE)='NULL'
THEN 0 WHEN UPPER(DATA_TYPE)='TIMESTAMP' THEN 93 WHEN UPPER(DATA_TYPE)='BIGINT' THEN -5 WHEN UPPER(DATA_TYPE)='BIGINT UNSIGNED' THEN -5 WHEN UPPER(DATA_TYPE)='MEDIUMINT' THEN 4 WHEN UPPER(DATA_TYPE)='MEDIUMINT UNSIGNED'
THEN 4 WHEN UPPER(DATA_TYPE)='DATE' THEN 91 WHEN UPPER(DATA_TYPE)='TIME' THEN 92 WHEN UPPER(DATA_TYPE)='DATETIME' THEN 93 WHEN UPPER(DATA_TYPE)='YEAR' THEN 91 WHEN UPPER(DATA_TYPE)='VARCHAR' THEN 12 WHEN UPPER(DATA_TYPE)='VARBINARY'
THEN -3 WHEN UPPER(DATA_TYPE)='BIT' THEN -7 WHEN UPPER(DATA_TYPE)='JSON' THEN -1 WHEN UPPER(DATA_TYPE)='ENUM' THEN 1 WHEN UPPER(DATA_TYPE)='SET' THEN 1 WHEN UPPER(DATA_TYPE)='TINYBLOB'
THEN -3 WHEN UPPER(DATA_TYPE)='TINYTEXT' THEN 12 WHEN UPPER(DATA_TYPE)='MEDIUMBLOB' THEN -4 WHEN UPPER(DATA_TYPE)='MEDIUMTEXT' THEN -1 WHEN UPPER(DATA_TYPE)='LONGBLOB' THEN -4 WHEN UPPER(DATA_TYPE)='LONGTEXT'
THEN -1 WHEN UPPER(DATA_TYPE)='BLOB' THEN -4 WHEN UPPER(DATA_TYPE)='TEXT' THEN -1 WHEN UPPER(DATA_TYPE)='CHAR' THEN 1 WHEN UPPER(DATA_TYPE)='BINARY' THEN -2 WHEN UPPER(DATA_TYPE)='GEOMETRY' THEN -2
WHEN UPPER(DATA_TYPE)='UNKNOWN' THEN 1111 WHEN UPPER(DATA_TYPE)='POINT' THEN -2 WHEN UPPER(DATA_TYPE)='LINESTRING' THEN -2 WHEN UPPER(DATA_TYPE)='POLYGON' THEN -2 WHEN UPPER(DATA_TYPE)='MULTIPOINT' THEN -2
WHEN UPPER(DATA_TYPE)='MULTILINESTRING' THEN -2 WHEN UPPER(DATA_TYPE)='MULTIPOLYGON' THEN -2 WHEN UPPER(DATA_TYPE)='GEOMETRYCOLLECTION' THEN -2 WHEN UPPER(DATA_TYPE)='GEOMCOLLECTION'
THEN -2 ELSE 1111 END AS `DATA_TYPE`, UPPER(CASE WHEN LOCATE('UNSIGNED', UPPER(DATA_TYPE)) != 0 AND LOCATE('UNSIGNED', UPPER(DATA_TYPE)) = 0 THEN CONCAT(DATA_TYPE, ' UNSIGNED')
ELSE DATA_TYPE END) AS `TYPE_NAME`,NUMERIC_PRECISION AS `PRECISION`, CASE WHEN LCASE(DATA_TYPE)='date' THEN 10 WHEN LCASE(DATA_TYPE)='time' THEN 8 WHEN LCASE(DATA_TYPE)='datetime'
THEN 19 WHEN LCASE(DATA_TYPE)='timestamp' THEN 19 WHEN CHARACTER_MAXIMUM_LENGTH IS NULL THEN NUMERIC_PRECISION WHEN CHARACTER_MAXIMUM_LENGTH > 2147483647 THEN 2147483647 ELSE CHARACTER_MAXIMUM_LENGTH END AS LENGTH,
NUMERIC_SCALE AS `SCALE`, 10 AS RADIX,1 AS `NULLABLE`, NULL AS `REMARKS`, CHARACTER_OCTET_LENGTH AS `CHAR_OCTET_LENGTH`, ORDINAL_POSITION, 'YES' AS `IS_NULLABLE`, SPECIFIC_NAME FROM INFORMATION_SCHEMA.PARAMETERS
WHERE SPECIFIC_SCHEMA LIKE 'abc' AND SPECIFIC_NAME LIKE 'abc' AND (PARAMETER_NAME LIKE 'abc' OR PARAMETER_NAME IS NULL) AND ROUTINE_TYPE='FUNCTION' ORDER BY SPECIFIC_SCHEMA, SPECIFIC_NAME, ORDINAL_POSITION;
SELECT ROUTINE_SCHEMA AS FUNCTION_CAT, NULL AS FUNCTION_SCHEM, ROUTINE_NAME AS FUNCTION_NAME, ROUTINE_COMMENT AS REMARKS, 1 AS FUNCTION_TYPE, ROUTINE_NAME AS SPECIFIC_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE LIKE 'FUNCTION' AND ROUTINE_SCHEMA LIKE 'abc' AND ROUTINE_NAME LIKE 'abc' ORDER BY FUNCTION_CAT, FUNCTION_SCHEM, FUNCTION_NAME, SPECIFIC_NAME;
SELECT A.REFERENCED_TABLE_SCHEMA AS PKTABLE_CAT, NULL AS PKTABLE_SCHEM, A.REFERENCED_TABLE_NAME AS PKTABLE_NAME, A.REFERENCED_COLUMN_NAME AS PKCOLUMN_NAME, A.TABLE_SCHEMA AS FKTABLE_CAT, NULL AS FKTABLE_SCHEM, A.TABLE_NAME AS FKTABLE_NAME, A.COLUMN_NAME AS FKCOLUMN_NAME, A.ORDINAL_POSITION AS KEY_SEQ,CASE WHEN R.UPDATE_RULE='CASCADE' THEN 0 WHEN R.UPDATE_RULE='SET NULL' THEN 2 WHEN R.UPDATE_RULE='SET DEFAULT' THEN 4 WHEN R.UPDATE_RULE='RESTRICT' THEN 1 WHEN R.UPDATE_RULE='NO ACTION' THEN 3 ELSE 3 END AS UPDATE_RULE,CASE WHEN R.DELETE_RULE='CASCADE' THEN 0 WHEN R.DELETE_RULE='SET NULL' THEN 2 WHEN R.DELETE_RULE='SET DEFAULT' THEN 4 WHEN R.DELETE_RULE='RESTRICT' THEN 1 WHEN R.DELETE_RULE='NO ACTION' THEN 3 ELSE 3 END AS DELETE_RULE, A.CONSTRAINT_NAME AS FK_NAME, (SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_SCHEMA = A.REFERENCED_TABLE_SCHEMA AND TABLE_NAME = A.REFERENCED_TABLE_NAME AND CONSTRAINT_TYPE IN ('UNIQUE','PRIMARY KEY') LIMIT 1) AS PK_NAME,7 AS DEFERRABILITY FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE A JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS B USING (CONSTRAINT_NAME, TABLE_NAME) JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R ON (R.CONSTRAINT_NAME = B.CONSTRAINT_NAME AND R.TABLE_NAME = B.TABLE_NAME AND R.CONSTRAINT_SCHEMA = B.TABLE_SCHEMA) WHERE B.CONSTRAINT_TYPE = 'FOREIGN KEY' AND A.TABLE_SCHEMA LIKE 'abc' AND A.TABLE_NAME='abc' AND A.REFERENCED_TABLE_SCHEMA IS NOT NULL ORDER BY A.REFERENCED_TABLE_SCHEMA, A.REFERENCED_TABLE_NAME, A.ORDINAL_POSITION;
SELECT TABLE_SCHEMA AS TABLE_CAT, NULL AS TABLE_SCHEM, TABLE_NAME, NON_UNIQUE,TABLE_SCHEMA AS INDEX_QUALIFIER, INDEX_NAME,3 AS TYPE, SEQ_IN_INDEX AS ORDINAL_POSITION, COLUMN_NAME,COLLATION AS ASC_OR_DESC, CARDINALITY, NULL AS PAGES, NULL AS FILTER_CONDITION FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA LIKE 'abc' AND TABLE_NAME LIKE 'abc' ORDER BY NON_UNIQUE, INDEX_NAME, SEQ_IN_INDEX;
SELECT TABLE_SCHEMA AS TABLE_CAT, NULL AS TABLE_SCHEM, TABLE_NAME, COLUMN_NAME, SEQ_IN_INDEX AS KEY_SEQ, 'PRIMARY' AS PK_NAME FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA LIKE 'abc' AND TABLE_NAME LIKE 'abc' AND INDEX_NAME='PRIMARY' ORDER BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX;
SELECT SPECIFIC_SCHEMA AS PROCEDURE_CAT, NULL AS `PROCEDURE_SCHEM`, SPECIFIC_NAME AS `PROCEDURE_NAME`, IFNULL(PARAMETER_NAME, '') AS `COLUMN_NAME`, CASE WHEN PARAMETER_MODE = 'IN' THEN 1 WHEN PARAMETER_MODE = 'OUT' THEN 4 WHEN PARAMETER_MODE = 'INOUT' THEN 2 WHEN ORDINAL_POSITION = 0 THEN 5 ELSE 0 END AS `COLUMN_TYPE`, CASE WHEN UPPER(DATA_TYPE)='DECIMAL' THEN 3 WHEN UPPER(DATA_TYPE)='DECIMAL UNSIGNED' THEN 3 WHEN UPPER(DATA_TYPE)='TINYINT' THEN CASE WHEN LOCATE('(1)', DTD_IDENTIFIER) != 0 THEN -7 ELSE -6 END WHEN UPPER(DATA_TYPE)='TINYINT UNSIGNED' THEN CASE WHEN LOCATE('(1)', DTD_IDENTIFIER) != 0 THEN -7 ELSE -6 END WHEN UPPER(DATA_TYPE)='BOOLEAN' THEN 16 WHEN UPPER(DATA_TYPE)='SMALLINT' THEN 5 WHEN UPPER(DATA_TYPE)='SMALLINT UNSIGNED' THEN 5 WHEN UPPER(DATA_TYPE)='INT' THEN 4 WHEN UPPER(DATA_TYPE)='INT UNSIGNED' THEN 4 WHEN UPPER(DATA_TYPE)='FLOAT' THEN 7 WHEN UPPER(DATA_TYPE)='FLOAT UNSIGNED' THEN 7 WHEN UPPER(DATA_TYPE)='DOUBLE' THEN 8 WHEN UPPER(DATA_TYPE)='DOUBLE UNSIGNED' THEN 8 WHEN UPPER(DATA_TYPE)='NULL' THEN 0 WHEN UPPER(DATA_TYPE)='TIMESTAMP' THEN 93 WHEN UPPER(DATA_TYPE)='BIGINT' THEN -5 WHEN UPPER(DATA_TYPE)='BIGINT UNSIGNED' THEN -5 WHEN UPPER(DATA_TYPE)='MEDIUMINT' THEN 4 WHEN UPPER(DATA_TYPE)='MEDIUMINT UNSIGNED' THEN 4 WHEN UPPER(DATA_TYPE)='DATE' THEN 91 WHEN UPPER(DATA_TYPE)='TIME' THEN 92 WHEN UPPER(DATA_TYPE)='DATETIME' THEN 93 WHEN UPPER(DATA_TYPE)='YEAR' THEN 91 WHEN UPPER(DATA_TYPE)='VARCHAR' THEN 12 WHEN UPPER(DATA_TYPE)='VARBINARY' THEN -3 WHEN UPPER(DATA_TYPE)='BIT' THEN -7 WHEN UPPER(DATA_TYPE)='JSON' THEN -1 WHEN UPPER(DATA_TYPE)='ENUM' THEN 1 WHEN UPPER(DATA_TYPE)='SET' THEN 1 WHEN UPPER(DATA_TYPE)='TINYBLOB' THEN -3 WHEN UPPER(DATA_TYPE)='TINYTEXT' THEN 12 WHEN UPPER(DATA_TYPE)='MEDIUMBLOB' THEN -4 WHEN UPPER(DATA_TYPE)='MEDIUMTEXT' THEN -1 WHEN UPPER(DATA_TYPE)='LONGBLOB' THEN -4 WHEN UPPER(DATA_TYPE)='LONGTEXT' THEN -1 WHEN UPPER(DATA_TYPE)='BLOB' THEN -4 WHEN UPPER(DATA_TYPE)='TEXT' THEN -1 WHEN UPPER(DATA_TYPE)='CHAR' THEN 1 WHEN UPPER(DATA_TYPE)='BINARY' THEN -2 WHEN UPPER(DATA_TYPE)='GEOMETRY' THEN -2 WHEN UPPER(DATA_TYPE)='UNKNOWN' THEN 1111 WHEN UPPER(DATA_TYPE)='POINT' THEN -2 WHEN UPPER(DATA_TYPE)='LINESTRING' THEN -2 WHEN UPPER(DATA_TYPE)='POLYGON' THEN -2 WHEN UPPER(DATA_TYPE)='MULTIPOINT' THEN -2 WHEN UPPER(DATA_TYPE)='MULTILINESTRING' THEN -2 WHEN UPPER(DATA_TYPE)='MULTIPOLYGON' THEN -2 WHEN UPPER(DATA_TYPE)='GEOMETRYCOLLECTION' THEN -2 WHEN UPPER(DATA_TYPE)='GEOMCOLLECTION' THEN -2 ELSE 1111 END AS `DATA_TYPE`, UPPER(CASE WHEN LOCATE('UNSIGNED', UPPER(DATA_TYPE)) != 0 AND LOCATE('UNSIGNED', UPPER(DATA_TYPE)) = 0 THEN CONCAT(DATA_TYPE, ' UNSIGNED') ELSE DATA_TYPE END) AS `TYPE_NAME`, NUMERIC_PRECISION AS `PRECISION`, CASE WHEN LCASE(DATA_TYPE)='date' THEN 10 WHEN LCASE(DATA_TYPE)='time' THEN 8 WHEN LCASE(DATA_TYPE)='datetime' THEN 19 WHEN LCASE(DATA_TYPE)='timestamp' THEN 19 WHEN CHARACTER_MAXIMUM_LENGTH IS NULL THEN NUMERIC_PRECISION WHEN CHARACTER_MAXIMUM_LENGTH > 2147483647 THEN 2147483647 ELSE CHARACTER_MAXIMUM_LENGTH END AS LENGTH,NUMERIC_SCALE AS `SCALE`, 10 AS RADIX,1 AS `NULLABLE`, NULL AS `REMARKS`, NULL AS `COLUMN_DEF`, NULL AS `SQL_DATA_TYPE`, NULL AS `SQL_DATETIME_SUB`, CHARACTER_OCTET_LENGTH AS `CHAR_OCTET_LENGTH`, ORDINAL_POSITION, 'YES' AS `IS_NULLABLE`, SPECIFIC_NAME FROM INFORMATION_SCHEMA.PARAMETERS WHERE SPECIFIC_SCHEMA LIKE 'abc' AND SPECIFIC_NAME LIKE 'abc' AND (PARAMETER_NAME LIKE 'abc' OR PARAMETER_NAME IS NULL) ORDER BY SPECIFIC_SCHEMA, SPECIFIC_NAME, ROUTINE_TYPE, ORDINAL_POSITION;
SELECT ROUTINE_SCHEMA AS PROCEDURE_CAT, NULL AS PROCEDURE_SCHEM, ROUTINE_NAME AS PROCEDURE_NAME, NULL AS RESERVED_1, NULL AS RESERVED_2, NULL AS RESERVED_3, ROUTINE_COMMENT AS REMARKS, CASE WHEN ROUTINE_TYPE = 'PROCEDURE' THEN 1 WHEN ROUTINE_TYPE='FUNCTION' THEN 2 ELSE 0 END AS PROCEDURE_TYPE, ROUTINE_NAME AS SPECIFIC_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA LIKE 'abc' AND ROUTINE_NAME LIKE 'abc' ORDER BY ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE;
SELECT SCHEMA_NAME AS TABLE_SCHEM,CATALOG_NAME AS TABLE_CATALOG FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME LIKE 'abc';
SELECT WORD FROM INFORMATION_SCHEMA.KEYWORDS WHERE RESERVED = 1 ORDER BY WORD limit 1;
SELECT TABLE_SCHEMA AS TABLE_CAT, NULL AS TABLE_SCHEM, TABLE_NAME, NULL AS GRANTOR, GRANTEE, PRIVILEGE_TYPE AS PRIVILEGE, IS_GRANTABLE FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES where TABLE_SCHEMA = 'abc' AND TABLE_NAME LIKE 'abc';
SELECT TABLE_SCHEMA AS TABLE_CAT, NULL AS TABLE_SCHEM, TABLE_NAME, CASE WHEN TABLE_TYPE='BASE TABLE' THEN CASE WHEN TABLE_SCHEMA = 'mysql' OR TABLE_SCHEMA = 'performance_schema' THEN 'SYSTEM TABLE' ELSE 'TABLE' END WHEN TABLE_TYPE='TEMPORARY' THEN 'LOCAL_TEMPORARY' ELSE TABLE_TYPE END AS TABLE_TYPE, TABLE_COMMENT AS REMARKS, NULL AS TYPE_CAT, NULL AS TYPE_SCHEM, NULL AS TYPE_NAME, NULL AS SELF_REFERENCING_COL_NAME, NULL AS REF_GENERATION FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'abc' AND TABLE_NAME = 'abc' ORDER BY TABLE_TYPE, TABLE_SCHEMA, TABLE_NAME;
SELECT NULL AS SCOPE, COLUMN_NAME, CASE WHEN UPPER(DATA_TYPE)='DECIMAL' THEN 3 WHEN UPPER(DATA_TYPE)='DECIMAL UNSIGNED' THEN 3 WHEN UPPER(DATA_TYPE)='TINYINT' THEN CASE WHEN LOCATE('(1)', COLUMN_TYPE) != 0 THEN -7 ELSE -6 END WHEN UPPER(DATA_TYPE)='TINYINT UNSIGNED' THEN CASE WHEN LOCATE('(1)', COLUMN_TYPE) != 0 THEN -7 ELSE -6 END WHEN UPPER(DATA_TYPE)='BOOLEAN' THEN 16 WHEN UPPER(DATA_TYPE)='SMALLINT' THEN 5 WHEN UPPER(DATA_TYPE)='SMALLINT UNSIGNED' THEN 5 WHEN UPPER(DATA_TYPE)='INT' THEN 4 WHEN UPPER(DATA_TYPE)='INT UNSIGNED' THEN 4 WHEN UPPER(DATA_TYPE)='FLOAT' THEN 7 WHEN UPPER(DATA_TYPE)='FLOAT UNSIGNED' THEN 7 WHEN UPPER(DATA_TYPE)='DOUBLE' THEN 8 WHEN UPPER(DATA_TYPE)='DOUBLE UNSIGNED' THEN 8 WHEN UPPER(DATA_TYPE)='NULL' THEN 0 WHEN UPPER(DATA_TYPE)='TIMESTAMP' THEN 93 WHEN UPPER(DATA_TYPE)='BIGINT' THEN -5 WHEN UPPER(DATA_TYPE)='BIGINT UNSIGNED' THEN -5 WHEN UPPER(DATA_TYPE)='MEDIUMINT' THEN 4 WHEN UPPER(DATA_TYPE)='MEDIUMINT UNSIGNED' THEN 4 WHEN UPPER(DATA_TYPE)='DATE' THEN 91 WHEN UPPER(DATA_TYPE)='TIME' THEN 92 WHEN UPPER(DATA_TYPE)='DATETIME' THEN 93 WHEN UPPER(DATA_TYPE)='YEAR' THEN 91 WHEN UPPER(DATA_TYPE)='VARCHAR' THEN 12 WHEN UPPER(DATA_TYPE)='VARBINARY' THEN -3 WHEN UPPER(DATA_TYPE)='BIT' THEN -7 WHEN UPPER(DATA_TYPE)='JSON' THEN -1 WHEN UPPER(DATA_TYPE)='ENUM' THEN 1 WHEN UPPER(DATA_TYPE)='SET' THEN 1 WHEN UPPER(DATA_TYPE)='TINYBLOB' THEN -3 WHEN UPPER(DATA_TYPE)='TINYTEXT' THEN 12 WHEN UPPER(DATA_TYPE)='MEDIUMBLOB' THEN -4 WHEN UPPER(DATA_TYPE)='MEDIUMTEXT' THEN -1 WHEN UPPER(DATA_TYPE)='LONGBLOB' THEN -4 WHEN UPPER(DATA_TYPE)='LONGTEXT' THEN -1 WHEN UPPER(DATA_TYPE)='BLOB' THEN -4 WHEN UPPER(DATA_TYPE)='TEXT' THEN -1 WHEN UPPER(DATA_TYPE)='CHAR' THEN 1 WHEN UPPER(DATA_TYPE)='BINARY' THEN -2 WHEN UPPER(DATA_TYPE)='GEOMETRY' THEN -2 WHEN UPPER(DATA_TYPE)='UNKNOWN' THEN 1111 WHEN UPPER(DATA_TYPE)='POINT' THEN -2 WHEN UPPER(DATA_TYPE)='LINESTRING' THEN -2 WHEN UPPER(DATA_TYPE)='POLYGON' THEN -2 WHEN UPPER(DATA_TYPE)='MULTIPOINT' THEN -2 WHEN UPPER(DATA_TYPE)='MULTILINESTRING' THEN -2 WHEN UPPER(DATA_TYPE)='MULTIPOLYGON' THEN -2 WHEN UPPER(DATA_TYPE)='GEOMETRYCOLLECTION' THEN -2 WHEN UPPER(DATA_TYPE)='GEOMCOLLECTION' THEN -2 ELSE 1111 END AS DATA_TYPE, UPPER(COLUMN_TYPE) AS TYPE_NAME, CASE WHEN LCASE(DATA_TYPE)='date' THEN 10 WHEN LCASE(DATA_TYPE)='time' THEN 8 WHEN LCASE(DATA_TYPE)='datetime' THEN 19 WHEN LCASE(DATA_TYPE)='timestamp' THEN 19 WHEN CHARACTER_MAXIMUM_LENGTH IS NULL THEN NUMERIC_PRECISION WHEN CHARACTER_MAXIMUM_LENGTH > 2147483647 THEN 2147483647 ELSE CHARACTER_MAXIMUM_LENGTH END AS COLUMN_SIZE, 65535 AS BUFFER_LENGTH,NUMERIC_SCALE AS DECIMAL_DIGITS, 1 AS PSEUDO_COLUMN FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA LIKE 'abc' AND TABLE_NAME LIKE 'abc' AND EXTRA LIKE '%on update CURRENT_TIMESTAMP%';
select count(*) from information_schema.schemata where schema_name = '';
select schema_name from information_schema.schemata order by 1 limit 1;
SELECT PROCESSLIST_ID, PROCESSLIST_USER, PROCESSLIST_HOST FROM performance_schema.threads WHERE PROCESSLIST_ID=1;
select sequence_name,sequence_schema,sequence_catalog,start_value,minimum_value,maximum_value,increment from information_schema.sequences;
select * from information_schema.sequences;
select sequence_name,sequence_schema,sequence_catalog,start_value,minimum_value,maximum_value,increment from information_schema.sequences;
select group_concat(COLUMN_NAME separator ',') as inner_col_exist from information_schema.columns where TABLE_NAME = 'abc';
select COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE, CHARACTER_SET_NAME from INFORMATION_SCHEMA.COLUMNS where table_name='abc' and TABLE_SCHEMA='abc';
set dolphin.sql_mode = 'sql_mode_strict,pipes_as_concat,ansi_quotes,no_zero_date,pad_char_to_full_length,auto_recompile_function,error_for_division_by_zero';
SELECT STATE AS `State`, ROUND(SUM(DURATION),7) AS `Duration`, CONCAT(ROUND(SUM(DURATION)/100,3), '%') AS `Percentage` FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID=123 GROUP BY STATE ORDER BY SEQ;
reset dolphin.sql_mode;
select state, round(sum(duration),5) as `duration (summed) in sec` from information_schema.profiling where query_id = 0 group by state order by `duration (summed) in sec` desc;
SHOW FULL TABLES FROM information_schema WHERE Tables_in_information_schema LIKE 'KEY_COLUMN_USAGE';
SELECT table_name, table_type, table_comment FROM information_schema.tables WHERE table_schema = DATABASE() order by 1,2;
SELECT c.constraint_name AS column_name FROM information_schema.check_constraints AS c WHERE c.table_name = 'abc' AND LOWER(c.check_clause) = 'json_valid(`' + LOWER(c.constraint_name) + '`)' AND c.constraint_schema = DATABASE();
SELECT table_collation FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = 'abc';
SELECT column_name, referenced_column_name, referenced_table_name
FROM information_schema.key_column_usage
WHERE table_name = 'abc'
AND table_schema = DATABASE()
AND referenced_table_schema = DATABASE()
AND referenced_table_name IS NOT NULL
AND referenced_column_name IS NOT NULL;
SELECT engine
FROM information_schema.tables
WHERE
table_name = 'abc' AND
table_schema = DATABASE();
SELECT kc.`constraint_name`, kc.`column_name`,
kc.`referenced_table_name`, kc.`referenced_column_name`,
c.`constraint_type`
FROM
information_schema.key_column_usage AS kc,
information_schema.table_constraints AS c
WHERE
kc.table_schema = DATABASE() AND
(
kc.referenced_table_schema = DATABASE() OR
kc.referenced_table_schema IS NULL
) AND
c.table_schema = kc.table_schema AND
c.constraint_name = kc.constraint_name AND
c.constraint_type != 'CHECK' AND
kc.table_name = 'abc'
ORDER BY kc.`ordinal_position`;
SELECT c.constraint_name, c.check_clause
FROM information_schema.check_constraints AS c
WHERE
c.constraint_schema = DATABASE() AND
c.table_name = 'abc';
SELECT cc.constraint_name, cc.check_clause
FROM
information_schema.check_constraints AS cc,
information_schema.table_constraints AS tc
WHERE
cc.constraint_schema = DATABASE() AND
tc.table_schema = cc.constraint_schema AND
cc.constraint_name = tc.constraint_name AND
tc.constraint_type = 'CHECK' AND
tc.table_name = 'abc';
select pg_typeof(MAXLEN), pg_typeof(DESCRIPTION) from INFORMATION_SCHEMA.CHARACTER_SETS limit 1;
select pg_typeof(character_set_name), pg_typeof(ID), pg_typeof(IS_DEFAULT), pg_typeof(IS_COMPILED), pg_typeof(SORTLEN) from INFORMATION_SCHEMA.COLLATIONS limit 1;
select pg_typeof(PRIVILEGES), pg_typeof(column_key), pg_typeof(SRS_ID) from INFORMATION_SCHEMA.COLUMNS limit 1;
select pg_typeof(REFERENCED_TABLE_SCHEMA), pg_typeof(REFERENCED_TABLE_NAME), pg_typeof(REFERENCED_COLUMN_NAME) from INFORMATION_SCHEMA.KEY_COLUMN_USAGE limit 1;
select pg_typeof(ROUTINE_TYPE) from INFORMATION_SCHEMA.PARAMETERS limit 1;
select pg_typeof(table_name), pg_typeof(referenced_table_name) from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS limit 1;
select pg_typeof(ROUTINE_COMMENT), pg_typeof(SQL_MODE), pg_typeof(DEFINER), pg_typeof(CHARACTER_SET_CLIENT), pg_typeof(COLLATION_CONNECTION),pg_typeof(DATABASE_COLLATION) from INFORMATION_SCHEMA.ROUTINES limit 1;
select pg_typeof(DEFAULT_COLLATION_NAME), pg_typeof(DEFAULT_ENCRYPTION) from INFORMATION_SCHEMA.SCHEMATA limit 1;
select pg_typeof(ENFORCED) from INFORMATION_SCHEMA.TABLE_CONSTRAINTS limit 1;
select pg_typeof(table_collation), pg_typeof(engine), pg_typeof(VERSION), pg_typeof(ROW_FORMAT), pg_typeof(TABLE_ROWS), pg_typeof(AVG_ROW_LENGTH), pg_typeof(DATA_LENGTH), pg_typeof(MAX_DATA_LENGTH), pg_typeof(INDEX_LENGTH), pg_typeof(DATA_FREE), pg_typeof(AUTO_INCREMENT), pg_typeof(CREATE_TIME), pg_typeof(UPDATE_TIME), pg_typeof(CHECK_TIME), pg_typeof(CHECKSUM), pg_typeof(CREATE_OPTIONS) from INFORMATION_SCHEMA.TABLES limit 1;
select pg_typeof(WORD), pg_typeof(RESERVED) from INFORMATION_SCHEMA.KEYWORDS limit 1;
select pg_typeof(TABLE_CATALOG), pg_typeof(TABLE_SCHEMA), pg_typeof(TABLE_NAME), pg_typeof(NON_UNIQUE), pg_typeof(INDEX_SCHEMA), pg_typeof(INDEX_NAME), pg_typeof(SEQ_IN_INDEX), pg_typeof(COLUMN_NAME), pg_typeof(COLLATION), pg_typeof(cardinality), pg_typeof(SUB_PART), pg_typeof(PACKED), pg_typeof(NULLABLE), pg_typeof(INDEX_TYPE), pg_typeof(COMMENT), pg_typeof(INDEX_COMMENT), pg_typeof(IS_VISIBLE), pg_typeof(EXPRESSION) from INFORMATION_SCHEMA.STATISTICS limit 1;
select pg_typeof(table_catalog), pg_typeof(table_schema), pg_typeof(table_name), pg_typeof(PARTITION_NAME), pg_typeof(SUBPARTITION_NAME), pg_typeof(PARTITION_ORDINAL_POSITION), pg_typeof(SUBPARTITION_ORDINAL_POSITION), pg_typeof(PARTITION_METHOD), pg_typeof(SUBPARTITION_METHOD), pg_typeof(PARTITION_EXPRESSION), pg_typeof(SUBPARTITION_EXPRESSION), pg_typeof(PARTITION_DESCRIPTION), pg_typeof(TABLE_ROWS), pg_typeof(AVG_ROW_LENGTH), pg_typeof(DATA_LENGTH), pg_typeof(MAX_DATA_LENGTH), pg_typeof(INDEX_LENGTH), pg_typeof(DATA_FREE), pg_typeof(CREATE_TIME), pg_typeof(UPDATE_TIME), pg_typeof(CHECK_TIME), pg_typeof(CHECKSUM), pg_typeof(PARTITION_COMMENT), pg_typeof(NODEGROUP), pg_typeof(TABLESPACE_NAME) from INFORMATION_SCHEMA.PARTITIONS limit 1;
select pg_typeof(THREAD_ID), pg_typeof(NAME), pg_typeof(TYPE), pg_typeof(PROCESSLIST_ID), pg_typeof(PROCESSLIST_USER), pg_typeof(PROCESSLIST_HOST), pg_typeof(PROCESSLIST_DB), pg_typeof(PROCESSLIST_COMMAND), pg_typeof(PROCESSLIST_TIME), pg_typeof(PROCESSLIST_STATE), pg_typeof(PROCESSLIST_INFO), pg_typeof(PARENT_THREAD_ID), pg_typeof(ROLE), pg_typeof(INSTRUMENTED), pg_typeof(HISTORY), pg_typeof(CONNECTION_TYPE), pg_typeof(THREAD_OS_ID), pg_typeof(RESOURCE_GROUP), pg_typeof(EXECUTION_ENGINE), pg_typeof(CONTROLLED_MEMORY), pg_typeof(MAX_CONTROLLED_MEMORY), pg_typeof(TOTAL_MEMORY), pg_typeof(MAX_TOTAL_MEMORY), pg_typeof(MAX_TOTAL_MEMORY) from PERFORMANCE_SCHEMA.THREADS limit 1;
CREATE TABLE test_index_table_1 (
student_id INT PRIMARY KEY,
course_id INT,
score INT
);
CREATE TABLE test_index_table_2 (
student_id INT,
course_id INT,
score INT
);
CREATE TABLE test_index_table_3 (
student_id INT,
course_id INT,
score INT
);
CREATE TABLE test_index_table_4 (
student_id INT,
course_id INT,
score INT
);
CREATE UNIQUE INDEX test_index_2 ON test_index_table_2(student_id);
CREATE INDEX test_index_3 ON test_index_table_2(course_id, student_id);
CREATE INDEX test_index_4 ON test_index_table_2(student_id, course_id, score);
select * from information_schema.columns where table_name = 'test_index_table_1' order by 1,2,3,4;
select * from information_schema.columns where table_name = 'test_index_table_2' order by 1,2,3,4;
select * from information_schema.columns where table_name = 'test_index_table_3' order by 1,2,3,4;
select * from information_schema.columns where table_name = 'test_index_table_4' order by 1,2,3,4;
drop table test_index_table_1;
drop table test_index_table_2;
drop table test_index_table_3;
drop table test_index_table_4;
CREATE TABLE departments_test1 (
id int PRIMARY KEY,
name VARCHAR(100) NOT NULL UNIQUE
);
CREATE TABLE employees_test1 (
id int PRIMARY KEY,
employee_name VARCHAR(100) NOT NULL,
department_id INTEGER,
department_name VARCHAR(100),
hire_date VARCHAR(50),
CONSTRAINT fk_emp_dept_id FOREIGN KEY (department_id) REFERENCES departments_test1(id) ON DELETE SET NULL ON UPDATE CASCADE,
CONSTRAINT fk_emp_dept_name FOREIGN KEY (department_name) REFERENCES departments_test1(name) ON DELETE SET NULL ON UPDATE CASCADE
);
select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where constraint_name = 'fk_emp_dept_id' order by 1,2,3,4;
select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where constraint_name = 'fk_emp_dept_name' order by 1,2,3,4;
CREATE TABLE employees_test2 (
id int PRIMARY KEY,
employee_name VARCHAR(100) NOT NULL,
department_name VARCHAR(100),
department_id INTEGER,
hire_date VARCHAR(50),
CONSTRAINT fk_emp_dept_name1 FOREIGN KEY (department_name) REFERENCES departments_test1(name) ON DELETE SET NULL ON UPDATE CASCADE,
CONSTRAINT fk_emp_dept_id1 FOREIGN KEY (department_id) REFERENCES departments_test1(id) ON DELETE SET NULL ON UPDATE CASCADE
);
select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where constraint_name = 'fk_emp_dept_id1' order by 1,2,3,4;
select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where constraint_name = 'fk_emp_dept_name1' order by 1,2,3,4;
select * from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS where constraint_name in ('fk_emp_dept_id', 'fk_emp_dept_name', 'fk_emp_dept_id1', 'fk_emp_dept_name1') order by constraint_name;
drop table employees_test1;
drop table employees_test2;
drop table departments_test1;
select routine_comment from INFORMATION_SCHEMA.ROUTINES where routine_name in('pg_get_index_type', 'pg_avg_row_length') order by 1;
CREATE TABLE test_compression_1 (
id INT,
data TEXT
) WITH (orientation = column, compression = 'no');
CREATE TABLE test_compression_2 (
id INT,
data TEXT
) WITH (orientation = column, compression = 'yes');
CREATE TABLE test_compression_3 (
id INT,
data TEXT
) WITH (orientation = column, compression = 'low');
CREATE TABLE test_compression_4 (
id INT,
data TEXT
) WITH (orientation = column, compression = 'middle');
CREATE TABLE test_compression_5 (
id INT,
data TEXT
) WITH (orientation = column, compression = 'high');
CREATE TABLE test_compression_6 (
id INT,
data TEXT
) WITH (orientation = column);
CREATE TABLE test_compression_7 (
id INT,
data TEXT
);
select table_name,row_format,create_options from INFORMATION_SCHEMA.TABLES where table_name like 'test_compression%' and create_time is not null and update_time is not null order by table_name;
create table test_size_1(c1 int, c2 float8, c3 text, c4 varchar(5));
insert into test_size_1 values (1, 1, 1, 1);
insert into test_size_1 values (2, 2, 2, 2);
insert into test_size_1 values (3, 3, 3, 3);
insert into test_size_1 values (4, 4, 4, 4);
insert into test_size_1 values (5, 5, 5, 5);
create index test_size_1_indx1 on test_size_1(c1, c2, c3, c4);
analyze test_size_1;
select table_rows,avg_row_length,data_length,max_data_length,index_length,data_free from INFORMATION_SCHEMA.TABLES where table_name = 'test_size_1' order by 1,2,3,4;
CREATE TABLE test_auto_increase (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE,
age INT
);
select get_auto_increment_nextval(1234, true);
select auto_increment from INFORMATION_SCHEMA.tables where table_name = 'test_auto_increase';
insert into test_auto_increase(username, age) values ('users', 1);
select auto_increment from INFORMATION_SCHEMA.tables where table_name = 'test_auto_increase';
insert into test_auto_increase values (2147483646, 'users2', 1);
select auto_increment from INFORMATION_SCHEMA.tables where table_name = 'test_auto_increase';
insert into test_auto_increase values (2147483647, 'users3', 1);
select auto_increment from INFORMATION_SCHEMA.tables where table_name = 'test_auto_increase';
CREATE TABLE test_auto_increase2 (
id serial PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE,
age INT
);
select auto_increment from INFORMATION_SCHEMA.tables where table_name = 'test_auto_increase2';
insert into test_auto_increase2(username, age) values ('users', 1);
select auto_increment from INFORMATION_SCHEMA.tables where table_name = 'test_auto_increase2';
create table test_collation_1(c1 text) collate utf8_bin;
create table test_collation_2(c1 text);
select table_name, table_collation from information_schema.tables where table_name in ('test_collation_1', 'test_collation_2') order by table_name;
drop table if exists having_alias_info_schema_test;
create table having_alias_info_schema_test(id int);
select
table_schema as table_cat,
null as table_schem,
table_name,
case
when table_type = 'BASE TABLE' then 'TABLE'
else table_type
end as table_type
from information_schema.tables
where table_schema = 'public'
and table_name = 'having_alias_info_schema_test'
group by table_schema, table_name, information_schema.tables.table_type
having table_type in ('TABLE', null)
order by table_type, table_schema, table_name;
drop table if exists having_alias_grouping_test;
create table having_alias_grouping_test(a int, b int);
insert into having_alias_grouping_test values (1, 10), (2, 20);
select a + 1 as a, sum(b)
from having_alias_grouping_test
group by rollup(a)
having grouping(a) = 0
order by 1;
drop table having_alias_grouping_test;
drop table if exists having_alias_outer_test;
drop table if exists having_alias_inner_test;
create table having_alias_outer_test(id int);
create table having_alias_inner_test(id int);
insert into having_alias_outer_test values (1);
insert into having_alias_inner_test values (1), (2);
select count(*)
from (
select id as id
from having_alias_outer_test o
where exists (
select 1
from having_alias_inner_test i
group by i.id
having id > 1
)
) s;
drop table having_alias_outer_test;
drop table having_alias_inner_test;
drop table having_alias_info_schema_test;
drop table test_compression_1;
drop table test_compression_2;
drop table test_compression_3;
drop table test_compression_4;
drop table test_compression_5;
drop table test_compression_6;
drop table test_compression_7;
drop table test_size_1;
drop table test_auto_increase;
drop table test_auto_increase2;
drop table test_collation_1;
drop table test_collation_2;
CREATE TABLE test_index_table1 (
id INT PRIMARY KEY,
user_id INT NOT NULL,
role_id INT NOT NULL,
age int NOT NULL
);
CREATE UNIQUE INDEX test_index_table1_idx1 ON test_index_table1 (user_id, role_id);
CREATE INDEX test_index_table1_idx2 ON test_index_table1 (user_id, role_id, age);
CREATE INDEX test_index_table1_idx3 ON test_index_table1 (age, role_id, user_id);
CREATE INDEX test_index_table1_idx4 ON test_index_table1 (age) using hash;
CREATE INDEX test_index_table1_idx5 ON test_index_table1 (age) using ubtree;
CREATE INDEX test_index_table1_idx6 ON test_index_table1 (user_id desc, role_id desc, age desc);
CREATE INDEX test_index_table1_idx7 ON test_index_table1 (age desc, role_id desc, user_id desc);
insert into test_index_table1 values (1, 1, 1, 1);
insert into test_index_table1 values (2, 2, 2, 2);
insert into test_index_table1 values (3, 3, 3, 3);
insert into test_index_table1 values (4, 4, 4, 4);
analyze test_index_table1;
select * from INFORMATION_SCHEMA.STATISTICS where table_name = 'test_index_table1' order by 1,2,3,4,5,6,7;
CREATE TABLE test_index_table2 (
id INT,
user_id INT,
role_id INT,
age int
);
CREATE UNIQUE INDEX test_index_table2_idx1 ON test_index_table2 (id, user_id, role_id);
CREATE INDEX test_index_table2_idx2 ON test_index_table2 (user_id, role_id, age) COMMENT 'test_index_table1_idx2_d';
select * from INFORMATION_SCHEMA.STATISTICS where table_name = 'test_index_table2' order by 1,2,3,4;
CREATE TABLE test_index_table3 (
id int,
role_id varchar,
age int
);
CREATE INDEX test_index_table3_idx1 ON test_index_table3 (trunc(id));
CREATE UNIQUE INDEX test_index_table3_idx2 ON test_index_table3 (SUBSTR(role_id, 1, 4));
select * from INFORMATION_SCHEMA.STATISTICS where table_name = 'test_index_table3' order by 1,2,3,4;
drop table test_index_table1;
drop table test_index_table2;
drop table test_index_table3;
CREATE TABLE partition_table_test1(c1 int, c2 int, c3 int)
PARTITION BY RANGE (c1)
(
PARTITION P_RANGE1 VALUES LESS THAN (5),
PARTITION P_RANGE2 VALUES LESS THAN (10),
PARTITION P_RANGE3 VALUES LESS THAN (15),
PARTITION P_RANGE4 VALUES LESS THAN (20)
);
select table_catalog,table_schema,table_name,partition_name,subpartition_name,partition_ordinal_position,subpartition_ordinal_position,partition_method, subpartition_method,partition_expression,subpartition_expression,partition_description,table_rows,avg_row_length,data_length,max_data_length, index_length, data_free,check_time, partition_comment,nodegroup,tablespace_name from INFORMATION_SCHEMA.PARTITIONS where table_name = 'partition_table_test1' order by 1,2,3,4;
CREATE TABLE partition_table_test2 (
c1 INT,
c2 INT,
c3 INT
)
PARTITION BY RANGE (c1)
SUBPARTITION BY HASH (c2) (
PARTITION P_RANGE1 VALUES LESS THAN (5) (
SUBPARTITION P_RANGE1_1,
SUBPARTITION P_RANGE1_2,
SUBPARTITION P_RANGE1_3,
SUBPARTITION P_RANGE1_4
),
PARTITION P_RANGE2 VALUES LESS THAN (10) (
SUBPARTITION P_RANGE2_1,
SUBPARTITION P_RANGE2_2,
SUBPARTITION P_RANGE2_3,
SUBPARTITION P_RANGE2_4
),
PARTITION P_RANGE3 VALUES LESS THAN (15) (
SUBPARTITION P_RANGE3_1,
SUBPARTITION P_RANGE3_2,
SUBPARTITION P_RANGE3_3,
SUBPARTITION P_RANGE3_4
)
);
select table_catalog,table_schema,table_name,partition_name,subpartition_name,partition_ordinal_position,subpartition_ordinal_position,partition_method, subpartition_method,partition_expression,subpartition_expression,partition_description,table_rows,avg_row_length,data_length,max_data_length, index_length, data_free,check_time, partition_comment,nodegroup,tablespace_name from INFORMATION_SCHEMA.PARTITIONS where table_name = 'partition_table_test2' order by 1,2,3,4;
CREATE TABLE partition_table_test3 (
c1 INT PRIMARY KEY,
c2 TIMESTAMP NOT NULL,
c3 NUMERIC(10,2)
)
PARTITION BY RANGE (EXTRACT(YEAR FROM c2))
(
PARTITION p2020 VALUES LESS THAN (2021),
PARTITION p2021 VALUES LESS THAN (2022),
PARTITION p2022 VALUES LESS THAN (2023),
PARTITION p_default VALUES LESS THAN (MAXVALUE)
);
select table_catalog,table_schema,table_name,partition_name,subpartition_name,partition_ordinal_position,subpartition_ordinal_position,partition_method, subpartition_method,partition_expression,subpartition_expression,partition_description,table_rows,avg_row_length,data_length,max_data_length, index_length, data_free,check_time, partition_comment,nodegroup,tablespace_name from INFORMATION_SCHEMA.PARTITIONS where table_name = 'partition_table_test3' order by 1,2,3,4;
CREATE TABLE partition_table_test4 (
c1 float4,
c2 int,
c3 TIMESTAMP
)
PARTITION BY RANGE (floor(c1))
SUBPARTITION BY RANGE (abs(c2)) (
PARTITION P_RANGE1 VALUES LESS THAN (1) (
SUBPARTITION P_RANGE1_1 VALUES LESS THAN (1),
SUBPARTITION P_RANGE1_2 VALUES LESS THAN (2),
SUBPARTITION P_RANGE1_3 VALUES LESS THAN (3),
SUBPARTITION P_RANGE1_4 VALUES LESS THAN (4)
),
PARTITION P_RANGE2 VALUES LESS THAN (2) (
SUBPARTITION P_RANGE2_1 VALUES LESS THAN (1),
SUBPARTITION P_RANGE2_2 VALUES LESS THAN (2),
SUBPARTITION P_RANGE2_3 VALUES LESS THAN (3),
SUBPARTITION P_RANGE2_4 VALUES LESS THAN (4)
),
PARTITION P_RANGE3 VALUES LESS THAN (3) (
SUBPARTITION P_RANGE3_1 VALUES LESS THAN (1),
SUBPARTITION P_RANGE3_2 VALUES LESS THAN (2),
SUBPARTITION P_RANGE3_3 VALUES LESS THAN (3),
SUBPARTITION P_RANGE3_4 VALUES LESS THAN (4)
)
);
select table_catalog,table_schema,table_name,partition_name,subpartition_name,partition_ordinal_position,subpartition_ordinal_position,partition_method, subpartition_method,partition_expression,subpartition_expression,partition_description,table_rows,avg_row_length,data_length,max_data_length, index_length, data_free,check_time, partition_comment,nodegroup,tablespace_name from INFORMATION_SCHEMA.PARTITIONS where table_name = 'partition_table_test4' order by 1,2,3,4;
drop table partition_table_test1;
drop table partition_table_test2;
drop table partition_table_test3;
drop table partition_table_test4;
DROP TABLE employees;
DROP TABLE departments;
