如何在mysql中查询以某个字符开头的表 如何在mysql中查询以某个字符开头的表

MYSQL没有类似于MSSQLSERVER的sysobjects或者Oracle的dba_tab_comments这样的表，但MYSQL可以用SHOW命令查询数据库和表、字段、索引等信息：

一些SHOW语句提供额外的字符集信息。这些语句包括SHOW CHARACTER SET、SHOW COLLATION、SHOW CREATE DATABASE、SHOW CREATE TABLE和SHOW COLUMNS。

SHOW CHARACTER SET命令显示全部可用的字符集。它带有一个可选的LIKE子句来指示匹配哪些字符集名。例如：

mysql>SHOW CHARACTER SET LIKE 'latin%'

+---------+-----------------------------+-------------------+--------+

| Charset | Description | Default collation | Maxlen |

+---------+-----------------------------+-------------------+--------+

| latin1 | cp1252 West European| latin1_swedish_ci | 1 |

| latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |

| latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 |

| latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 |

+---------+-----------------------------+-------------------+--------+

见13.5.4.1节，“SHOW CHARACTER SET语法”。

SHOW COLLATION语句的输出包括全部可用的字符集。它带有一个可选的LIKE子句来指示匹配哪些 校对规则名。例如：

mysql>SHOW COLLATION LIKE 'latin1%'

+-------------------+---------+----+---------+----------+---------+

| Collation | Charset | Id | Default | Compiled | Sortlen |

+-------------------+---------+----+---------+----------+---------+

| latin1_german1_ci | latin1 | 5 | | | 0 |

| latin1_swedish_ci | latin1 | 8 | Yes | Yes | 0 |

| latin1_danish_ci | latin1 | 15 | | | 0 |

| latin1_german2_ci | latin1 | 31 | | Yes | 2 |

| latin1_bin| latin1 | 47 | | Yes | 0 |

| latin1_general_ci | latin1 | 48 | | | 0 |

| latin1_general_cs | latin1 | 49 | | | 0 |

| latin1_spanish_ci | latin1 | 94 | | | 0 |

+-------------------+---------+----+---------+----------+---------+

见13.5.4.2节，“SHOW COLLATION语法”。

SHOW CREATE DATABASE语句显示创建给定数据库的CREATE DATABASE语句。结果包括全部数据库选项。支持DEFAULT CHARACTER SET和COLLATE。全部数据库选项存储在命名为db.Opt的文本文件中，该文件能够在数据库目录中找到。

mysql>SHOW CREATE DATABASE test

+----------+-----------------------------------------------------------------+

| Database | Create Database |

+----------+-----------------------------------------------------------------+

| test | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET latin1 */ |

+----------+-----------------------------------------------------------------+

见13.5.4.4节，“SHOW CREATE DATABASE语法”

SHOW CREATE TABLE与SHOW CREATE DATABASE相似，但是显示创建给定数据库的CREATE TABLE语句。列定义显示任何字符集规格，并且表选项包括字符集信息。

见13.5.4.5节，“SHOW CREATE TABLE语法”

当以SHOW FULL COLUMNS调用时，SHOW COLUMNS语句显示表中列的校对规则。具有CHAR、VARCHAR或TEXT数据类型的列有非NULL的 校对规则。数值列和其它非字符类型的列有NULL校对规则。例如：

mysql>SHOW FULL COLUMNS FROM person\G

*************************** 1. row ***************************

Field: id

Type: smallint(5) unsigned

Collation: NULL

Null: NO

Key: PRI

Default: NULL

Extra: auto_increment

Privileges: select,insert,update,references

Comment:

*************************** 2. row ***************************

Field: name

Type: char(60)

Collation: latin1_swedish_ci

Null: NO

Key:

Default:

Extra:

Privileges: select,insert,update,references

Comment:

字符集不是显示的部分。（字符集名隐含在校对规则名中。）

:给你一个程序段,既有[精确]又有[模糊]两种方式的查询

SQL语句只是我用VB6+SQL SERVER2000写的.

共参考吧!

Private Sub CmdCX_Click()

On Error Resume Next

Dim sSQL, L_Name As String

Dim K_je As Double

mConn.Execute " Delete Bg_L_IOMX_1" '清过程用表

Clea_RowCol '清数据网格

'Text1.Text = "" '清除【条件】【提示】框的内容

'Set RS = CreateObject("ADODB.Recordset") ' New ADODB.Recordset

Set RS3 = New ADODB.Recordset

DT1 = Format(Trim(D1.Value), "yyyy-mm-dd")

DT2 = Format(Trim(D2.Value), "yyyy-mm-dd")

If DT1 = DT2 Then MsgBox "正确方法,应选择<日期的区间>后,再进行【开始查询】,OK！！", vbOKOnly, " *** 作提示": Exit Sub

' ##按；日期；数据；字符三种类型检索//=#" &DT0 &"#///WHERE (((LP_001.被保险人名称) Like "*安*"))

If Txt_TJ.Text = "" Then MsgBox "应填好【查询条件】，再点击【开始查询】按钮！！！", vbOKOnly, " *** 作提示": Exit Sub

If ComboCX.Text = "船员分类" Then

Txt_TJ.Text = Trim(UCase(Txt_TJ.Text)) &"%"

sSQL = " Select * From Bg_L_IOMX Where (((Bg_L_IOMX.分类) Like '" &Txt_TJ.Text &"')) and 日期>='" &DT1 &"' and 日期<='" &DT2 &"' Order by 编号"

End If

If ComboCX.Text = "船名" Then

sSQL = " Select * From Bg_L_IOMX where " &ComboCX.Text &" = '" &Txt_TJ.Text &"' And 出数量<>0 and 日期>='" &DT1 &"' and 日期<='" &DT2 &"' Order by 编号"

End If

If ComboCX.Text = "工号" Then

Txt_TJ.Text = Trim(UCase(Txt_TJ.Text)) &"%"

sSQL = " Select * From Bg_L_IOMX where ((Bg_L_IOMX.ID) Like '" &Txt_TJ.Text &"') and 日期>='" &DT1 &"' and 日期<='" &DT2 &"' Order by 编号"

End If

If ComboCX.Text = "姓名" Then

Txt_TJ.Text = Trim(UCase(Txt_TJ.Text)) &"%"

sSQL = " Select * From Bg_L_IOMX where ((Bg_L_IOMX.领用人) Like '" &Txt_TJ.Text &"') and 日期>='" &DT1 &"' and 日期<='" &DT2 &"' Order by 编号"

End If

If ComboCX.Text = "派遣号" Then

'Text1.Text = "【派遣号】可采用<精确>方式，注：组合＝船名第1个字＋上船时间的XXXX月日；进行【条件查询】!"

sSQL = " Select * From Bg_L_IOMX where " &ComboCX.Text &" = '" &Txt_TJ.Text &"'And 出数量<>0 and 日期>='" &DT1 &"' and 日期<='" &DT2 &"' Order by 编号"

End If

mConn.Execute "Insert Into Bg_L_IOMX_1 " &sSQL

Call_MX '调用符合条件数据！！

End Sub

在当前用户下查找

select * from user_tables where table_name like 'tg_cdr01%'

在全库查找，需要dba或者select_catalog_role 权限

select * from dba_tables where table_name like 'tg_cdr01%'

---