select `TABLE_NAME` from `INFORMATION_SCHEMA`.`TABLES` where `TABLE_SCHEMA`='dbname' and `TABLE_NAME`='tbname' 返回不为空就存在,为空就不存在 如果是创建数据库和创建表的时候判断,可以这样create database if not exists `dbname`;DROP TABLE IF EXISTS `tbname`; CREATE TABLE `tbname`(...)
select `TABLE_NAME` from `INFORMATION_SCHEMA`.`TABLES` where `TABLE_SCHEMA`='dbname' and `TABLE_NAME`='tbname' 这句不好用啊
二楼的语句我试了下可以啊。你的MySQL的版本是多少? `INFORMATION_SCHEMA`.从版本5以后开始有的。之前只能用show tables like 'mytable';mysql> select table_name from `INFORMATION_SCHEMA`.`TABLES` where table_name ='t 5' and TABLE_SCHEMA='test'; +------------+ | table_name | +------------+ | t5 | +------------+ 1 row in set (0.06 sec)
MYSQL5以上有系统表,可以直接查找,否则用ADO、ADOX取得
select table_name from `INFORMATION_SCHEMA`.`TABLES` where table_name ='opeator' and TABLE_SCHEMA='vName'; 我像你这样,输入opeator(表名),vName(字段名),可以查,但查不出任何东西来
SQL codemysql> select table_name from `INFORMATION_SCHEMA`.`TABLES` where table_name ='t 5' and TABLE_SCHEMA='test'; TABLE_SCHEMA 传的内容貌似为数据库的名字 而非字段名
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | bbs | | bookstore | | datastruct | | hospital | | jspdev | | mysql | | test | +--------------------+ 8 rows in set (0.00 sec)mysql> use hospital; Database changed mysql> show tables; +--------------------+ | Tables_in_hospital | +--------------------+ | administrator | | appointment | | curappointment | | doctor | | history | | patient | | pinqueue | +--------------------+ 7 rows in set (0.01 sec)mysql>
Enter password: **** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 65 to server version: 5.0.22-community-ntType 'help;' or '\h' for help. Type '\c' to clear the buffer.mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | bbs | | bookstore | | datastruct | | hospital | | jspdev | | mysql | | test | +--------------------+ 8 rows in set (0.00 sec)mysql> use bookstore; Database changed mysql> show tables; +---------------------+ | Tables_in_bookstore | +---------------------+ | allorder | | book | | bookadmin | | bookclass | | orders | | sequence | | shop_user | +---------------------+ 7 rows in set (0.00 sec)mysql>
如果是创建数据库和创建表的时候判断,可以这样create database if not exists `dbname`;DROP TABLE IF EXISTS `tbname`;
CREATE TABLE `tbname`(...)
5' and TABLE_SCHEMA='test';
+------------+
| table_name |
+------------+
| t5 |
+------------+
1 row in set (0.06 sec)
我像你这样,输入opeator(表名),vName(字段名),可以查,但查不出任何东西来
5' and TABLE_SCHEMA='test'; TABLE_SCHEMA 传的内容貌似为数据库的名字 而非字段名
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| bbs |
| bookstore |
| datastruct |
| hospital |
| jspdev |
| mysql |
| test |
+--------------------+
8 rows in set (0.00 sec)mysql> use hospital;
Database changed
mysql> show tables;
+--------------------+
| Tables_in_hospital |
+--------------------+
| administrator |
| appointment |
| curappointment |
| doctor |
| history |
| patient |
| pinqueue |
+--------------------+
7 rows in set (0.01 sec)mysql>
Enter password: ****
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 65 to server version: 5.0.22-community-ntType 'help;' or '\h' for help. Type '\c' to clear the buffer.mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| bbs |
| bookstore |
| datastruct |
| hospital |
| jspdev |
| mysql |
| test |
+--------------------+
8 rows in set (0.00 sec)mysql> use bookstore;
Database changed
mysql> show tables;
+---------------------+
| Tables_in_bookstore |
+---------------------+
| allorder |
| book |
| bookadmin |
| bookclass |
| orders |
| sequence |
| shop_user |
+---------------------+
7 rows in set (0.00 sec)mysql>