cubrid的中sql查询语法show

c#,net,cubrid,教程,学习,笔记欢迎转载 ，转载时请保留作者信息。本文版权归本人所有，如有任何问题，请与我联系wang2650@sohu.com 。 过错
------ 官方文档是英文的,看不明白可以参看ocracle的同类函数说明.很多都是一样的.

原文
http://www.cubrid.org/manual/93/en/sql/query/show.html#show-slotted-page-slots
DESC, DESCRIBE EXPLAIN 显示列信息.
如
DESC tbl_name;
DESCRIBE tbl_name
EXPLAIN tbl_name;

SHOW TABLES显示所有的表
SHOW TABLES;
Tables_in_demodb
======================
'athlete'
'code'
'event'
'game'
'history'
'nation'
'olympic'
'participant'
'record'
'stadium'
SHOW FULL TABLES;
Tables_in_demodb Table_type
============================================
'athlete' 'BASE TABLE'
'code' 'BASE TABLE'
'event' 'BASE TABLE'
'game' 'BASE TABLE'
'history' 'BASE TABLE'
'nation' 'BASE TABLE'
'olympic' 'BASE TABLE'
'participant' 'BASE TABLE'
'record' 'BASE TABLE'
'stadium' 'BASE TABLE'
SHOW FULL TABLES LIKE '%c%';
Tables_in_demodb Table_type
============================================
'code' 'BASE TABLE'
'olympic' 'BASE TABLE'
'participant' 'BASE TABLE'
'record' 'BASE TABLE'
SHOW FULL TABLES WHERE table_type = 'BASE TABLE' and TABLES_IN_demodb LIKE '%co%';
Tables_in_demodb Table_type
============================================
'code' 'BASE TABLE'
'record' 'BASE TABLE'

SHOW COLUMNS 显示列

SHOW COLUMNS FROM athlete;
Field Type Null Key Default Extra
================================================================================================================
'code' 'INTEGER' 'NO' 'PRI' NULL 'auto_increment'
'name' 'VARCHAR(40)' 'NO' '' NULL ''
'gender' 'CHAR(1)' 'YES' '' NULL ''
'nation_code' 'CHAR(3)' 'YES' '' NULL ''
'event' 'VARCHAR(30)' 'YES' '' NULL ''
SHOW COLUMNS FROM athlete WHERE field LIKE '%c%';
Field Type Null Key Default Extra
================================================================================================================
'code' 'INTEGER' 'NO' 'PRI' NULL 'auto_increment'
'nation_code' 'CHAR(3)' 'YES' '' NULL ''
SHOW COLUMNS FROM athlete WHERE "type" = 'INTEGER' and "key"='PRI' AND extra='auto_increment';
Field Type Null Key Default Extra
================================================================================================================
'code' 'INTEGER' 'NO' 'PRI' NULL 'auto_increment'
SHOW COLUMNS FROM athlete WHERE field LIKE '%c%';
Field Type Collation Null Key Default Extra
====================================================================================================================================
'code' 'INTEGER' NULL 'NO' 'PRI' NULL 'auto_increment'
'nation_code' 'CHAR(3)' 'iso88591_bin' 'YES' '' NULL ''

SHOW INDEX 显示索引

Column name Type Description
Table VARCHAR Table name
Non_unique INTEGER
Unique or not
0: Duplicated value is not allowed
1: Duplicated value is allowed
Key_name VARCHAR Index name
Seq_in_index INTEGER Serial number of the column in the index. Starts from 1.
Column_name VARCHAR Column name
Collation VARCHAR Method of sorting columns in the index. 'A' means ascending and NULL means not sorted.
Cardinality INTEGER The number of values measuring the unique values in the index. Higher cardinality increases the opportunity of using an index. This value is updated every time SHOW INDEX is executed. Note that this is an approximate value.
Sub_part INTEGER The number of bytes of the indexed characters if the columns are indexed partially. NULL if all columns are indexed.
Packed Shows how keys are packed. If they are not packed, it will be NULL. Currently no support.
Null VARCHAR YES if a column can include NULL, NO if not.
Index_type VARCHAR Index to be used (currently, only the BTREE is supported.)
Func VARCHAR A function which is used in a function-based index
The following shows the examples of this syntax.

SHOW INDEX IN athlete;
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Func
=============================================================================================================================================
'athlete' 0 'pk_athlete_code' 1 'code' 'A' 6677 NULL NULL 'NO' 'BTREE' NULL
CREATE TABLE tbl1 (i1 INTEGER , i2 INTEGER NOT NULL, i3 INTEGER UNIQUE, s1 VARCHAR(10), s2 VARCHAR(10), s3 VARCHAR(10) UNIQUE);

CREATE INDEX i_tbl1_i1 ON tbl1 (i1 DESC);
CREATE INDEX i_tbl1_s1 ON tbl1 (s1 (7));
CREATE INDEX i_tbl1_i1_s1 ON tbl1 (i1, s1);
CREATE UNIQUE INDEX i_tbl1_i2_s2 ON tbl1 (i2, s2);

SHOW INDEXES FROM tbl1;
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Func
=====================================================================================================================================
'tbl1' 1 'i_tbl1_i1' 1 'i1' 'D' 0 NULL NULL 'YES' 'BTREE' NULL
'tbl1' 1 'i_tbl1_i1_s1' 1 'i1' 'A' 0 NULL NULL 'YES' 'BTREE' NULL
'tbl1' 1 'i_tbl1_i1_s1' 2 's1' 'A' 0 NULL NULL 'YES' 'BTREE' NULL
'tbl1' 0 'i_tbl1_i2_s2' 1 'i2' 'A' 0 NULL NULL 'NO' 'BTREE' NULL
'tbl1' 0 'i_tbl1_i2_s2' 2 's2' 'A' 0 NULL NULL 'YES' 'BTREE' NULL
'tbl1' 1 'i_tbl1_s1' 1 's1' 'A' 0 7 NULL 'YES' 'BTREE' NULL
'tbl1' 0 'u_tbl1_i3' 1 'i3' 'A' 0 NULL NULL 'YES' 'BTREE' NULL
'tbl1' 0 'u_tbl1_s3' 1 's3' 'A' 0 NULL NULL 'YES' 'BTREE' NULL

SHOW COLLATION 显示排序

SHOW COLLATION [ LIKE 'pattern' ];
This query has the following columns:

Column name Type Description
Collation VARCHAR Collation name
Charset CHAR(1) Charset name
Id INTEGER Collation ID
Built_in CHAR(1) Built-in collation or not. Built-in collations are impossible to add or remove because they are hard-coded.
Expansions CHAR(1) Collation with expansion or not. For details, see Expansion.
Strength CHAR(1) The number of levels to be considered in comparison, and the character order can be different by this number. For details, see Collation Properties.
The following shows the examples of this syntax.

SHOW COLLATION;
Collation Charset Id Built_in Expansions Strength
===========================================================================================================================
'euckr_bin' 'euckr' 8 'Yes' 'No' 'Not applicable'
'iso88591_bin' 'iso88591' 0 'Yes' 'No' 'Not applicable'
'iso88591_en_ci' 'iso88591' 3 'Yes' 'No' 'Not applicable'
'iso88591_en_cs' 'iso88591' 2 'Yes' 'No' 'Not applicable'
'utf8_bin' 'utf8' 1 'Yes' 'No' 'Not applicable'
'utf8_de_exp' 'utf8' 76 'No' 'Yes' 'Tertiary'
'utf8_de_exp_ai_ci' 'utf8' 72 'No' 'Yes' 'Primary'
'utf8_en_ci' 'utf8' 5 'Yes' 'No' 'Not applicable'
'utf8_en_cs' 'utf8' 4 'Yes' 'No' 'Not applicable'
'utf8_es_cs' 'utf8' 85 'No' 'No' 'Quaternary'
'utf8_fr_exp_ab' 'utf8' 94 'No' 'Yes' 'Tertiary'
'utf8_gen' 'utf8' 32 'No' 'No' 'Quaternary'
'utf8_gen_ai_ci' 'utf8' 37 'No' 'No' 'Primary'
'utf8_gen_ci' 'utf8' 44 'No' 'No' 'Secondary'
'utf8_ja_exp' 'utf8' 124 'No' 'Yes' 'Tertiary'
'utf8_ja_exp_cbm' 'utf8' 125 'No' 'Yes' 'Tertiary'
'utf8_km_exp' 'utf8' 132 'No' 'Yes' 'Quaternary'
'utf8_ko_cs' 'utf8' 7 'Yes' 'No' 'Not applicable'
'utf8_ko_cs_uca' 'utf8' 133 'No' 'No' 'Quaternary'
'utf8_tr_cs' 'utf8' 6 'Yes' 'No' 'Not applicable'
'utf8_tr_cs_uca' 'utf8' 205 'No' 'No' 'Quaternary'
'utf8_vi_cs' 'utf8' 221 'No' 'No' 'Quaternary'
SHOW COLLATION LIKE '%ko%';
Collation Charset Id Built_in Expansions Strength
===========================================================================================================================
'utf8_ko_cs' 'utf8' 7 'Yes' 'No' 'Not applicable'
'utf8_ko_cs_uca' 'utf8' 133 'No' 'No' 'Quaternary'

SHOW GRANTS 显示授权

SHOW GRANTS FOR 'user';
The following shows the examples of this syntax.

CREATE TABLE testgrant (id INT);
CREATE USER user1;
GRANT INSERT,SELECT ON testgrant TO user1;

SHOW GRANTS FOR user1;
Grants for USER1
======================
'GRANT INSERT, SELECT ON testgrant TO USER1'

SHOW CREATE TABLE 显示创建表的语句

SHOW CREATE TABLE table_name;
SHOW CREATE TABLE nation;
TABLE CREATE TABLE
============================================
'nation' 'CREATE TABLE [nation] ([code] CHARACTER(3) NOT NULL,
[name] CHARACTER VARYING(40) NOT NULL, [continent] CHARACTER VARYING(10),
[capital] CHARACTER VARYING(30), CONSTRAINT [pk_nation_code] PRIMARY KEY ([code]))
COLLATE iso88591_bin'

SHOW CREATE VIEW 显示创建视图的语句
SHOW CREATE VIEW view_name;

SHOW ACCESS STATUS 显示访问状态
SHOW ACCESS STATUS [LIKE 'pattern' | WHERE expr] ;
This statement displays the following columns.

Column name Type Description
user_name VARCHAR(32) DB user's account
last_access_time DATETIME Last time that the database user accessed
last_access_host VARCHAR(32) Lastly accessed host
program_name VARCHAR(32) The name of client program(broker_cub_cas_1, csql ..)
The following shows the result of running this statement.

SHOW ACCESS STATUS;
user_name last_access_time last_access_host program_name

'DBA' 08:19:31.000 PM 02/10/2014 127.0.0.1 'csql'
'PUBLIC' NULL NULL NULL

Note The above login information which SHOW ACCESS STATUS shows is initialized when the database is restarted, and this query is not replication in HA environment; therefore, each node shows the different result.

SHOW EXEC STATISTICS显示执行统计
SHOW EXEC STATISTICS [ALL];
The following shows the examples of this syntax.

-- set session variable @collect_exec_stats as 1 to start collecting the statistical information.
SET @collect_exec_stats = 1;
SELECT * FROM db_class;

-- print the statistical information of the data pages.
SHOW EXEC STATISTICS;
variable value
===============================
'data_page_fetches' 332
'data_page_dirties' 85
'data_page_ioreads' 18
'data_page_iowrites' 28
SELECT * FROM db_index;

-- print all of the statistical information.
SHOW EXEC STATISTICS ALL;
variable value
============================================
'file_creates' 0
'file_removes' 0
'file_ioreads' 6
'file_iowrites' 0
'file_iosynches' 0
'data_page_fetches' 548
'data_page_dirties' 34
'data_page_ioreads' 6
'data_page_iowrites' 0
'data_page_victims' 0
'data_page_iowrites_for_replacement' 0
'log_page_ioreads' 0
'log_page_iowrites' 0
'log_append_records' 0
'log_checkpoints' 0
'log_wals' 0
'page_locks_acquired' 13
'object_locks_acquired' 9
'page_locks_converted' 0
'object_locks_converted' 0
'page_locks_re-requested' 0
'object_locks_re-requested' 8
'page_locks_waits' 0
'object_locks_waits' 0
'tran_commits' 3
'tran_rollbacks' 0
'tran_savepoints' 0
'tran_start_topops' 6
'tran_end_topops' 6
'tran_interrupts' 0
'btree_inserts' 0
'btree_deletes' 0
'btree_updates' 0
'btree_covered' 0
'btree_noncovered' 2
'btree_resumes' 0
'btree_multirange_optimization' 0
'query_selects' 4
'query_inserts' 0
'query_deletes' 0
'query_updates' 0
'query_sscans' 2
'query_iscans' 4
'query_lscans' 0
'query_setscans' 2
'query_methscans' 0
'query_nljoins' 2
'query_mjoins' 0
'query_objfetches' 0
'network_requests' 88
'adaptive_flush_pages' 0
'adaptive_flush_log_pages' 0
'adaptive_flush_max_pages' 0
'network_requests' 88
'adaptive_flush_pages' 0
'adaptive_flush_log_pages' 0
'adaptive_flush_max_pages' 0

SHOW VOLUME HEADER 显示卷头
可以只显示一条
SHOW VOLUME HEADER OF volume_id;

This query has the following columns:

Column name Type Description
Volume_id INT Volume identifier
Magic_symbol VARCHAR(100) Magic value for for a volume file
Io_page_size INT Size of DB volume
Purpose VARCHAR(32) Volume purposes, purposes type: DATA, INDEX, GENERIC, TEMP TEMP, TEMP
Sector_size_in_pages INT Size of sector in pages
Num_total_sectors INT Total number of sectors
Num_free_sectors INT Number of free sectors
Hint_alloc_sector INT Hint for next sector to be allocated
Num_total_pages INT Total number of pages
Num_free_pages INT Number of free pages
Sector_alloc_table_size_in_pages INT Size of sector allocation table in page
Sector_alloc_table_first_page INT First page of sector allocation table
Page_alloc_table_size_in_pages INT Size of page allocation table in page
Page_alloc_table_first_page INT First page of page allocation table
Last_system_page INT Last system page
Creation_time DATETIME Database creation time
Num_max_pages INT max page count of this volume, this is not equal to the total_pages,if this volume is auto extended
Num_used_data_pages INT allocated pages for DATA purpose
Num_used_index_pages INT allocated pages for INDEX purpose
Checkpoint_lsa VARCHAR(64) Lowest log sequence address to start the recovery process of this volume
Boot_hfid VARCHAR(64) System Heap file for booting purposes and multi volumes
Full_name VARCHAR(255) The full path of volume
Next_vol_full_name VARCHAR(255) The full path of next volume
Remarks VARCHAR(64)
The following shows the examples of this syntax.

-- csql> ;line on
SHOW VOLUME HEADER OF 0;
<00001> Volume_id : 0
Magic_symbol : 'MAGIC SYMBOL = CUBRID/Volume at disk location = 32'
Io_page_size : 16384
Purpose : 'Permanent GENERIC Volume'
Sector_size_in_pages : 10
Num_total_sectors : 640
Num_free_sectors : 550
Hint_alloc_sector : 94
Num_total_pages : 6400
Num_free_pages : 6025
Sector_alloc_table_size_in_pages: 1
Sector_alloc_table_first_page : 1
Page_alloc_table_size_in_pages : 1
Page_alloc_table_first_page : 2
Last_system_page : 2
Creation_time : 06:09:27.000 PM 02/27/2014
Num_max_pages : 6400
Num_used_data_pages : 192
Num_used_index_pages : 180
Checkpoint_lsa : '(0|12832)'
Boot_hfid : '(0|41|50)'
Full_name : '/home1/brightest/CUBRID/databases/demodb/demodb'
Next_vol_full_name : ''
Remarks : ''

				
	
	

		
相关文章