How to display the tables within MySQL? Keeps showing empty set

Question

This is my first time doing MySQL. I am using the MySQL 5.6 Command Line Client and am trying to display the tables and it shows empty set. Here's the script statements I want to execute:

CREATE DATABASE ACMEOnline;

CREATE TABLE ITEM(Item_Number Integer DEFAULT 0, Item_Name Varchar(35) DEFAULT 'No Data', Model_Num Varchar(15) DEFAULT 'No Data',
 ->Description Varchar(255) DEFAULT 'No Data', Price DOUBLE(8,2) NOT NULL DEFAULT 0.00, Category_Name Varchar(35), 
 ->CONSTRAINT item_item_number_pk PRIMARY KEY(Item_Number), 
 ->CONSTRAINT item_category_name_fk FOREIGN KEY(Category_Name) REFERENCES CATEGORY(Category_Name));

CREATE TABLE CATEGORY(Category_Name Varchar(35), ShippingPerPound DOUBLE(4,2), DiscountsAllowed Char(1), 
 ->CONSTRAINT category_category_name_pk PRIMARY KEY(Category_Name));

CREATE TABLE LINE_ITEM(Quantity TINYINT(2), Shipping_Amount DOUBLE(6,2), OrderID Integer, Item_Number Integer,
 ->CONSTRAINT line_item_orderid_fk FOREIGN KEY(OrderID) REFERENCES ORDER_ITEM(OrderID),
 ->CONSTRAINT line_item_item_number_fk FOREIGN KEY(Item_Number) REFERENCES ORDER_ITEM(Item_Number));

CREATE TABLE ORDER_ITEM(OrderID Integer, Item_Number Integer, PRIMARY KEY(OrderID, Item_Number));

CREATE TABLE ORDER(OrderID Integer, Total DOUBLE(10,2), CONSTRAINT order_orderid_pk PRIMARY KEY(OrderID), CustomerID Integer, OfferCode Varchar(15),
  ->CONSTRAINT customer_offer_customerid_fk FOREIGN KEY(CustomerID) REFERENCES CUSTOMER(CustomerID),
  ->CONSTRAINT customer_offer_offercode_fk FOREIGN KEY(OfferCode) REFERENCES OFFER(OfferCode));

 CREATE TABLE CUSTOMER_OFFER(CustomerID Integer, OfferCode Varchar(15), PRIMARY KEY(CustomerID, OfferCode));

 CREATE TABLE OFFER(OfferCode Varchar(15), MinAmount DOUBLE(4,2) NOT NULL, Discount Varchar(35), ExpirationDate Char(8) NOT NULL, 
  ->CONSTRAINT offer_offercode_pk PRIMARY KEY(OfferCode));

 CREATE TABLE CUSTOMER(CustomerID Integer, CustomerName Varchar(50), Address Varchar(150), Email Varchar(80), 
  ->CONSTRAINT customer_customerid_pk PRIMARY KEY(CustomerID));

 CREATE TABLE BUSINESS(PurchaseTerms Varchar(50), CustomerID Integer, CONSTRAINT business_customerid_pk PRIMARY KEY(CustomerID),
  ->FOREIGN KEY(CustomerID) REFERENCES CUSTOMER(CustomerID));

 CREATE TABLE HOME(CreditCardNum BIGINT(16) NOT NULL, CardExpiration Char(7) NOT NULL, CONSTRAINT home_customerid_pk PRIMARY KEY(CustomerID),
  ->FOREIGN KEY(CustomerID) REFERENCES CUSTOMER(CustomerID));

 CREATE TABLE CONTACT(ContactName Varchar(50), ContactPhone Char(12) NOT NULL, CONSTRAINT contact_contactname_pk PRIMARY KEY(ContactName),
  ->FOREIGN KEY(Customer ID) REFERENCES BUSINESS(CustomerID));

 SHOW TABLES;
 DESCRIBE ITEM;
 DESCRIBE CATEGORY;
 DESCRIBE LINE_ITEM;
 DESCRIBLE ORDER_ITEM;
 DESCRIBE ORDER;
 DESCRIBE CUSTOMER_OFFER;
 DESCRIBE OFFER;
 DESCRIBE CUSTOMER;
 DESCRIBE BUSINESS;
 DESCRIBE HOME;
 DESCRIBE CONTACT;

Within MySQL 5.6 Command Line Client:

 mysql> use acmeonline;
 Database changed
 mysql> show tables;
 Empty set (0.00 sec)
 mysql> describe item; //the rest of the table names repeated.
 ERROR 1146 (42S02): Table 'acmeonline.item' doesn't exist

I have no idea what that last line means even though I have a table of item created in a database named acmeonline. Maybe I'm missing something important, I don't know what though. Your help would be greatly appreciated!

Namphibian · Accepted Answer · 2013-07-15 05:37:03Z

3

Don't use the show tables command anymore. Learn how to use the information_schema views. I suspect you created the tables in the wrong database.

Execute the following command

SELECT * FROM information_schema.tables

Look for your tables there. The information_schema views are essential for any developer/dba to understand.

answered Jul 15, 2013 at 5:37

Namphibian

12.3k8 gold badges50 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

peterm · Accepted Answer · 2013-07-15 05:44:54Z

1

You should've changed database after you've created it. Right now all your tables reside in a database that has been used when you connected to mysql or last database that you manually changed before executing CREATE TABLE statements.

mysql> CREATE DATABASE ACMEOnline;
Query OK, 1 row affected (0.00 sec)

mysql> USE ACMEOnline;
Database changed

Then create all your tables

mysql> CREATE TABLE test(id int);
Query OK, 0 rows affected (0.02 sec)

mysql> SHOW TABLES;
+----------------------+
| Tables_in_acmeonline |
+----------------------+
| test                 |
+----------------------+
1 row in set (0.00 sec)

The other way to ensure that tables are created in the correct database is to use dot notation <database_name>.<table_name>

mysql> USE mysql;
Database changed

mysql> CREATE TABLE ACMEOnline.test1(id int);
Query OK, 0 rows affected (0.01 sec)

mysql> USE ACMEOnline;
Database changed

mysql> SHOW TABLES;
+----------------------+
| Tables_in_acmeonline |
+----------------------+
| test                 |
| test1                |
+----------------------+
2 rows in set (0.00 sec)

edited Jul 15, 2013 at 5:44

answered Jul 15, 2013 at 5:35

peterm

93k16 gold badges157 silver badges162 bronze badges

2 Comments

TheAmazingKnight Over a year ago

I have to have all my script statements be added into the mysql command client and not the notepad textfile?

peterm Over a year ago

Of course not. It's just a demonstration. Therefore just put USE ACMEOnline; after CREATE DATABASE ACMEOnline; in your script or use dot notation.

Collectives™ on Stack Overflow

How to display the tables within MySQL? Keeps showing empty set

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related