Create a Duplicate Table From An Existing Table

Introduction

1. How to Retrieve Data From a Single Table

1.1. The Five Clauses of the SELECT Statement

1.2. Column Specifications

1.3. LIKE and REGEXP Operators

1.4. Arithmetic Operators

1.5. Column Aliases

1.6. Comparison Operators

1.7. IS NULL, BETWEEN, IN Operators

1.8. AND, OR, NOT Logical Operators

1.9. DISTINCT Clause

2. How to Retrieve Data from Multiple Tables

2.1. The JOIN Clause

2.2. Joining More Than Two Tables

2.3. The OUTER JOIN Clause

2.4. How to Code a UNION

3. Using Functions

3.1. Date Functions

3.2. Numeric Functions

3.3. String Functions

4. How to Insert, Update, Delete Data in Tables

4.1. The INSERT Clause With a Column List

4.2. The INSERT Clause Without a Column List

4.4. The UPDATE Clause With a Column List

4.4. The DELETE Clause

5. Summary Queries and Aggregate Functions

5.1. Aggregate Functions

5.2. Grouping Data

5.3. Simple GROUP BY Query

5.4. Improving the GROUP BY Query

5.5. Using the HAVING Clause

5.5. Using the HAVING and WHERE Clauses Together

5.6. COUNT(column_name) and COUNT(*)

5.7. Using the DISTINCT Statement

6. Working With Subqueries

6.1. The Subquery In a SELECT Statement

6.2. The Subquery in an UPDATE statement

6.4. The Subquery In a Delete Statement

7. SQL Views

7.1. SQL View Explained

7.2. Benefits of Using Views

7.3. Views That Allow UPDATE Statements

8. SQL Indexes

8.1. SQL Indexes Explained

8.2. Clustered vs. Non-clustered Indexes

8.3. Create an Index in Workbench Using an ERD

8.4. How to Manually Add an Index to an Existing Table

Glossary

Index

Download

Back to Downloads

HTML can be useful for transferring content to other online publishing platforms, typically by copy/pasting to the new platform.

<div class="callout info" data-callout-custom-class="info">
<h3 id="h3_mCXZ" name="h3_mCXZ">Create a Duplicate Table from an Existing Table with a Select Statement</h3>
<ul id="ul_hyNv" name="ul_hyNv">
<li id="li_CSej" name="li_CSej">It is often helpful to create a duplicate table from an existing table for testing purposes</li>
<li id="li_pyjT" name="li_pyjT">You can combine the CREATE TABLE command with a select statement to create a duplicate of a table structure as well as the data in the table.</li>
</ul>
</div>Code Sample:<code>1    USE world;  2    CREATE TABLE city_bak AS SELECT * FROM city;</code>Results:<img src="https://edtechbooks.org/content_images/learning_mysql/sub_03.png" class="img-fluid" alt="sub_03.png" id="img_cPns" name="img_cPns">USE world;<ul id="ul_Mffk" name="ul_Mffk">
<li id="li_hSWv" name="li_hSWv"> Select world as the default schema</li>
</ul>CREATE TABLE city_bak AS SELECT * FROM city;<ul id="ul_ofCA" name="ul_ofCA">
<li id="li_mmxt" name="li_mmxt"> Create a new table named city_bak with the exact same structure as the city table.</li>
<li id="li_BZrr" name="li_BZrr">Copy all of the data from the city table to the city_bak table</li>
</ul>