Sql Interrogation To Copy, Duplicate Or Backup Tabular Array Inward Mysql, Oracle Too Postgresql Database - Hallo sahabat BEST LEARNING JAVA, Pada Artikel yang anda baca kali ini dengan judul Sql Interrogation To Copy, Duplicate Or Backup Tabular Array Inward Mysql, Oracle Too Postgresql Database, kami telah mempersiapkan artikel ini dengan baik untuk anda baca dan ambil informasi didalamnya. mudah-mudahan isi postingan Artikel database, Artikel mysql, Artikel programming, Artikel SQL, Artikel SQL Interview Questions, yang kami tulis ini dapat anda pahami. baiklah, selamat membaca.

Judul : Sql Interrogation To Copy, Duplicate Or Backup Tabular Array Inward Mysql, Oracle Too Postgresql Database
link : Sql Interrogation To Copy, Duplicate Or Backup Tabular Array Inward Mysql, Oracle Too Postgresql Database

Sql Interrogation To Copy, Duplicate Or Backup Tabular Array Inward Mysql, Oracle Too Postgresql Database

Many times nosotros involve to arrive at backup or re-create of tables inward database similar MySQL, Oracle or PostgreSQL piece modifying tabular array schema similar adding novel columns, modifying column or dropping columns. Since its e'er best to guide keep a backup of tabular array which tin endure used inward whatever event. I was looking for an tardily agency to create exact re-create or duplicate tables which must endure same inward schema every bit good every bit inward data, similar to creating re-create of folder. Luckily at that spot is an tardily SQL interrogation "CREATE tabular array table_name AS" which allows you lot to arrive at exact re-create of tabular array past times executing just 1 SQL query. Yes, you lot read it correctly, no tool is required to arrive at backup of tabular array you lot just involve to execute an SQL query. This is only awesome given its importance in addition to best purpose of this SQL interrogation is that it industrial plant inward near all the database. I guide keep tested it inward MySQL in addition to Oracle but t it should move perfectly discovery inward other databases similar PostgreSQL, SQL Server in addition to DB2 every bit well. This SQL interrogation tip is inward continuation of my before SQL interrogation examples similar SQL interrogation to discovery duplicate rows inward a table in addition to SQL interrogation to bring together iii tables inward MySQL .

How to re-create tabular array using SQL interrogation inward MySQL

Now let's encounter it an action. In this illustration I am creating exact replica of tabular array for demonstration. We volition utilisation a tabular array called AIRCRAFT which has 3 records in addition to later creating backup of AIRCRAFT tabular array nosotros volition verify both count in addition to records to encounter if its exact replica of source tabular array or not. Here is our  SQL interrogation to arrive at backup of tabular array inward MySQL without whatever tool:

create tabular array table_name every bit guide * from source_table

where table_name is cite of backup tabular array in addition to source_table is cite of source tabular array inward database. SELECT interrogation example which is used to fetch information tin endure a complex interrogation which tin fetch information from multiple tabular array every bit well.

-- showing listing of tabular array before creating backup

mysql> SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| aircraft       |
| user           |
+----------------+
2 rows IN SET (0.34 sec)

-- creating backup of aircraft tabular array past times selecting all data

mysql> CREATE TABLE aircraft_backup AS SELECT * FROM aircraft;
Query OK, 3 rows affected (0.14 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SHOW TABLES;
+-----------------+
| Tables_in_test  |
+-----------------+
| aircraft        |
| aircraft_backup |
| user            |
+-----------------+
3 rows IN SET (0.00 sec)

-- checking publish of records inward source tabular array

mysql> SELECT count(*) FROM aircraft;
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row IN SET (0.00 sec)

-- verifying publish of records inward newly created backup table

mysql> SELECT count(*) FROM aircraft_backup;
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row IN SET (0.00 sec)

-- information inward master copy tabular array

mysql> SELECT * FROM aircraft;
+-----+--------+---------------+
| assistance | aname  | cruisingrange |
+-----+--------+---------------+
| 301 | Boeing |         16000 |
| 302 | Airbus |         10000 |
| 303 | Jet    |          8000 |
+-----+--------+---------------+
3 rows IN SET (0.00 sec)

-- information inward backup tabular array should endure just same alongside source table

mysql> SELECT * FROM aircraft_backup;
+-----+--------+---------------+
| assistance | aname  | cruisingrange |
+-----+--------+---------------+
| 301 | Boeing |         16000 |
| 302 | Airbus |         10000 |
| 303 | Jet    |          8000 |
+-----+--------+---------------+
3 rows IN SET (0.00 sec)

How to arrive at tabular array from roughly other tabular array inward SQL

creating tabular array from roughly other tabular array inward SQL  is same every bit copying tabular array but you lot guide keep a alternative to either just re-create the schema or re-create schema in addition to information together. In guild to arrive at SQL tabular array from roughly other tabular array just utilisation next arrive at tabular array SQL interrogation in addition to supersede cite of tabular array alongside exact cite you lot want.

create tabular array destination_table every bit guide * from source_table;

In guild to arrive at tabular array past times copying schema from roughly other tabular array alongside out information utilisation a status inward WHERE clause which e'er returns false.

mysql> CREATE TABLE AIRCRAFT_SCHEMA_BACKUP AS SELECT * FROM AIRCRAFT WHERE 3=4;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM AIRCRAFT_SCHEMA_BACKUP;
Empty SET (0.00 sec)

mysql> DESCRIBE AIRCRAFT_SCHEMA_BACKUP;
+---------------+-------------+------+-----+---------+-------+
| FIELD         | Type        | NULL | KEY | DEFAULT | Extra |
+---------------+-------------+------+-----+---------+-------+
| assistance           | int(11)     | NO   |     | NULL    |       |
| aname         | varchar(20) | YES  |     | NULL    |       |
| cruisingrange | int(11)     | YES  |     | NULL    |       |
+---------------+-------------+------+-----+---------+-------+
3 rows IN SET (0.06 sec)

f you lot desire to creat a tabular array from roughly other tabular array alongside information in addition to schema than just execute higher upwardly SQL interrogation without WHERE clause.

In illustration if you lot don't desire your novel tabular array to contains all columns in addition to exclusively few columns from the master copy tabular array than instead of using select * just utilisation guide column, column etc every bit shown inward below SQL query:

mysql> CREATE TABLE AIRCRAFT_BK AS SELECT aid, aname FROM AIRCRAFT;
Query OK, 3 rows affected (0.13 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM AIRCRAFT_BK;
+-----+--------+
| assistance | aname  |
+-----+--------+
| 301 | Boeing |
| 302 | Airbus |
| 303 | Jet    |
+-----+--------+
3 rows IN SET (0.00 sec)

That's all on creating backup of tabular array or copying tabular array past times using SQL query. We guide keep seen how to re-create tables, how to arrive at tabular array from roughly other tabular array alongside information in addition to without information in addition to how to arrive at duplicates of table. You e'er guide keep flexibility on choosing columns or data.

Further Learning
How to arrive at out transaction inward database – MySQL

Difference betwixt truncate in addition to delete inward SQL

How to guide keep backup of database using Mysqldump utility inward MySQL

10 MySQL ascendence Example

Difference betwixt clustered in addition to nonclustered index inward SQL

Demikianlah Artikel Sql Interrogation To Copy, Duplicate Or Backup Tabular Array Inward Mysql, Oracle Too Postgresql Database

Sekianlah artikel Sql Interrogation To Copy, Duplicate Or Backup Tabular Array Inward Mysql, Oracle Too Postgresql Database kali ini, mudah-mudahan bisa memberi manfaat untuk anda semua. baiklah, sampai jumpa di postingan artikel lainnya.

Anda sekarang membaca artikel Sql Interrogation To Copy, Duplicate Or Backup Tabular Array Inward Mysql, Oracle Too Postgresql Database dengan alamat link https://bestlearningjava.blogspot.com/2019/09/sql-interrogation-to-copy-duplicate-or.html

Bagikan Artikel ini