mysql怎么判断数据库是否存在，表是否存在？

[猫工]

mysql怎么判断数据库是否存在，表是否存在？
最好用ＳＱＬ语句来实现

[猫工]

select `TABLE_NAME` from `INFORMATION_SCHEMA`.`TABLES` where `TABLE_SCHEMA`='dbname' and `TABLE_NAME`='tbname'

这句不好用啊

[ACMAIN_CHM]

二楼的语句我试了下可以啊。你的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)

[WWWWA]

MYSQL5以上有系统表，可以直接查找，否则用ADO、ADOX取得

[jsjdyjb]

学习了

[LIHY70]

帮你顶~

[百年树人]

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_name ='opeator' and TABLE_SCHEMA='vName';
我像你这样，输入opeator(表名)，vName(字段名)，可以查，但查不出任何东西来

[Quote=引用 4 楼 ACMAIN_CHM 的回复:]
二楼的语句我试了下可以啊。你的MySQL的版本是多少？ `INFORMATION_SCHEMA`.从版本5以后开始有的。之前只能用show tables like 'mytable';


SQL codemysql> 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)




[/Quote]

[MagicError]

[Quote=引用 7 楼 xiongwenbin 的回复:]
select table_name from `INFORMATION_SCHEMA`.`TABLES` where table_name ='opeator' and TABLE_SCHEMA='vName';
我像你这样，输入opeator(表名)，vName(字段名)，可以查，但查不出任何东西来

引用 4 楼 ACMAIN_CHM 的回复:
二楼的语句我试了下可以啊。你的MySQL的版本是多少？ `INFORMATION_SCHEMA`.从版本5以后开始有的。之前只能用show tables like 'mytable';


SQL codemysql> select table_name from `INFORMA…
[/Quote]


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-nt

Type '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>

[yinyuehan823]

学习～

[lujiang618]

学习~，mysql好复杂！