MySQL CREATE view

(1). First Class:

Create View  as Select *  from table;

(2). Type II:

Create View  as Select  from table;

(3). Category III:

Create View v[vid,vname,vage]asselectfromtable ;

If the amount of data involved is too large, the business logic needs a cross-table query, and so on, you can also create a cross-table view, there are three scenarios:

One, based on the same database

This is a simpler situation, just a simple line of SQL commands, as follows:

Create View  as (Select*fromUnion All (select* from Table2);

Second, based on different databases

This is only a database name that is more than the SQL statement above, as follows:

Create View  as (Select*fromUnion All (select* from Database 2.table2);

Or

Create View  as (Select*fromUnion All (select* from Database 2.table2);

If execution of the first SQL will establish a view under Database 1, and vice versa;

Third, based on different server

This is a bit of a hassle, you need to first set up a remote table to access the remote server's data table, and then the newly created remote table and local table view, the steps are as follows:

1. See if MySQL supports the federated engine

(1). log in to MySQL;

(2). Mysql>show engines;

(3). If displayed as no, add: federated (in My.ini) to the configuration file and restart the MySQL service.

2. Create a remote table

Mysql>CREATE TABLEfederated_table (IDINT( -) not NULLauto_increment, nameVARCHAR( +) not NULL DEFAULT "', OtherINT( -) not NULL DEFAULT '0',                     PRIMARY KEY(ID)) ENGINE=Federateddefault CHARSET=UTF8 CONNECTION='Mysql://[email protected]_host:3306/federated/test_table';

The connection can be configured as follows:

  (1). CONNECTION='mysql://username:[email protected]:p ort/database/tablename'   (2). CONNECTION='mysql://[email protected]/database/tablename'  (  3). CONNECTION='mysql://username:[email protected]/database/tablename'

3. Build a View

Create View  as (Select*fromUnion All (select* from remote database. test_table);

MySQL CREATE view