Question

在oracle中，怎么查询有关列的数据？

在oracle中，怎么查询有关列的数据？例如，表test中有a，b，c三个字段，我想查在注释中包含“f”的字段（或者类型为varchar2（）的字段）。怎么查？
请注意，是注释，不是数据内容。

Answer 1

以两表为例。

需要用关联查询。

如有以下两张表，其中两表的deptno是关联字段。

现要求查出每个人对应的dname，可用如下语句：

select a.ename,b.dname from emp a,dept b where a.deptno=b.deptno;

查询结果：

Answer 2

注释中包含“f”的字段：
select owner,table_name,column_name from all_col_comments where comments like '%f%';

参考以下信息：
SQL> desc all_col_comments
Name Type Nullable Default Comments
----------- -------------- -------- ------- ---------------------
OWNER VARCHAR2(30) Owner of the object
TABLE_NAME VARCHAR2(30) Name of the object
COLUMN_NAME VARCHAR2(30) Name of the column
COMMENTS VARCHAR2(4000) Y Comment on the column

类型为varchar2（）的字段：
select owner,table_name,column_name from all_tab_columns where data_type='VARCHAR2';

参考以下信息：
SQL> desc all_tab_columns
Name Type Nullable Default Comments
-------------------- ------------- -------- ------- --------------------------------------------------------------------
OWNER VARCHAR2(30)
TABLE_NAME VARCHAR2(30) Table, view or cluster name
COLUMN_NAME VARCHAR2(30) Column name
DATA_TYPE VARCHAR2(106) Y Datatype of the column
DATA_TYPE_MOD VARCHAR2(3) Y Datatype modifier of the column
DATA_TYPE_OWNER VARCHAR2(30) Y Owner of the datatype of the column
DATA_LENGTH NUMBER Length of the column in bytes
DATA_PRECISION NUMBER Y Length: decimal digits (NUMBER) or binary digits (FLOAT)
DATA_SCALE NUMBER Y Digits to right of decimal point in a number
NULLABLE VARCHAR2(1) Y Does column allow NULL values?
COLUMN_ID NUMBER Y Sequence number of the column as created
DEFAULT_LENGTH NUMBER Y Length of default value for the column
DATA_DEFAULT LONG Y Default value for the column
NUM_DISTINCT NUMBER Y The number of distinct values in the column
LOW_VALUE RAW(32) Y The low value in the column
HIGH_VALUE RAW(32) Y The high value in the column
DENSITY NUMBER Y The density of the column
NUM_NULLS NUMBER Y The number of nulls in the column
NUM_BUCKETS NUMBER Y The number of buckets in histogram for the column
LAST_ANALYZED DATE Y The date of the most recent time this column was analyzed
SAMPLE_SIZE NUMBER Y The sample size used in analyzing this column
CHARACTER_SET_NAME VARCHAR2(44) Y Character set name
CHAR_COL_DECL_LENGTH NUMBER Y Declaration length of character type column
GLOBAL_STATS VARCHAR2(3) Y Are the statistics calculated without merging underlying partitions?
USER_STATS VARCHAR2(3) Y Were the statistics entered directly by the user?
AVG_COL_LEN NUMBER Y The average length of the column in bytes
CHAR_LENGTH NUMBER Y The maximum length of the column in characters
CHAR_USED VARCHAR2(1) Y C if maximum length is specified in characters, B if in bytes
V80_FMT_IMAGE VARCHAR2(3) Y Is column data in 8.0 image format?
DATA_UPGRADED VARCHAR2(3) Y Has column data been upgraded to the latest type version format?
HISTOGRAM VARCHAR2(15) Y

Answer 3

protected void Button1_Click(object sender, EventArgs e)
{
string Afieldname="";//得到列的名字
ListBox1.Items.Clear();
OleDbConnection conn=new OleDbConnection();
OleDbCommand dbc = new OleDbCommand("select distint "+Afieldname+" from theTable", conn);
OleDbDataReader dr = dbc.ExecuteReader();
while (dr.Read())
{
ListBox1.Items.Add(dr[0].ToString());
}
dr.Close();
dbc.Dispose();
conn.Close();
}

如果对您有帮助，请记得采纳为满意答案，谢谢！祝您生活愉快！

vaela

Answer 4

select * from test where a like '%'||'f%' or b like '%'||'f%' or c like '%'||'f%'
希望能帮助到你