View and table in same name is it possible

Is it possible to create a mysql view and table in same name

for example i have a table hs_hr_employee i want create a view as a same name

create VIEW hs_hr_employe开发者_JS百科e AS SELECT * from hs_hr_employee;

I m getting following error

#1050 - Table 'hs_hr_employee' already exists

Any help Thankful

Regards

you can't , give to view different name like

hs_hr_employee_view

from manual

Within a database, base tables and views share the same namespace, so a base table and a view cannot have the same name.

As stated, you can't do it with views, but you can with temporary tables.

If you create a temporary table with the same name as an actual table, the temporary table will shadow (hide) the actual table. This means that you can't access the actual table until you have dropped the temporary table:

mysql> create table t select 1; # actual table t
Query OK, 1 row affected (0.58 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> create temporary table t select*from t; # temp table t
Query OK, 1 row affected (0.53 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert t select 2; # t refers to the temp table
Query OK, 1 row affected (0.06 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select*from t; # temp table
+---+
| 1 |
+---+
| 1 |
| 2 |
+---+
2 rows in set (0.00 sec)

mysql> drop table t; # temp table
Query OK, 0 rows affected (0.06 sec)

mysql> show tables like "t"; # verify that actual table still exists. show tables will not show temporary tables
+--------------------+
| Tables_in_test (t) |
+--------------------+
| t                  |
+--------------------+
1 row in set (0.00 sec)

mysql>

mysql> select*from t; # now t refers to the actual table
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

mysql> drop table t; # actual table
Query OK, 0 rows affected (0.14 sec)

mysql>

However, temporary tables are gone (even if you do not drop them) once your session is disconnected. You'll need to re-create them every time you connect.

Yes, if you create it under a different schema.

for example

dbo.Customers

user.customers (this could be a view)

You can call it by including the schema or if you call it from a user that is assigned the schema.

This is for MS SQL server.

Not only in mysql server but in any sqldialect, tables and views in the same schema can't be with the same name as it will lead to ambiguity when you fire a select query like from where to select the columns either from table or view.