mysql
  1. mysql-create-table

CREATE Table - MySQL Table & Views

In MySQL, you can create tables using the CREATE TABLE statement. Tables are used to store data in a structured way, and views provide a way to present that data in a different format without modifying the underlying table. In this tutorial, we'll discuss how to create tables and views in MySQL.

Syntax

The syntax for creating a table in MySQL is as follows:

CREATE TABLE table_name (
   column1 datatype constraints,
   column2 datatype constraints,
   column3 datatype constraints,
   ...
);

The syntax for creating a view in MySQL is as follows:

CREATE VIEW view_name AS
   SELECT column1, column2, column3, ...
   FROM table_name
   WHERE condition;

Example

Let's say we want to create a table called "customers" to store customer data such as a name, email, and phone number. Here's how we can implement it:

CREATE TABLE customers (
   id INT PRIMARY KEY,
   name VARCHAR(50),
   email VARCHAR(50),
   phone VARCHAR(20)
);

Now, we can use the "customers" table to insert, update, and delete data.

INSERT INTO customers (id, name, email, phone) VALUES (1, "John Doe", "johndoe@example.com", "555-1234");

To create a view that shows the customer names and emails, we can implement the following code:

CREATE VIEW customer_emails AS
   SELECT name, email
   FROM customers;

Now, we can use the "customer_emails" view to display the names and email addresses of customers.

SELECT * FROM customer_emails;

Output

When we run the example code above, the output will be:

name         email
John Doe     johndoe@example.com

This is because we created a view that selects the "name" and "email" columns from the "customers" table and used it to display the data.

Explanation

In the example above, we created a table called "customers" to store customer data such as a name, email, and phone number. We then inserted a new customer into the table using the INSERT statement. Lastly, we created a view called "customer_emails" that selects the "name" and "email" columns from the "customers" table. We then used the view to display the names and email addresses of customers.

Use

Tables and views are essential for organizing and presenting data in a structured way. You can use tables to store data, and views to present that data in a different format. This provides flexibility and allows you to optimize your data for different use cases.

Important Points

  • The primary key constraint ensures that each row in the table is unique.
  • Datatypes such as INT and VARCHAR are used to specify the type of data stored in a column.
  • Views provide a way to present data in a different format without modifying the underlying table.

Summary

In this tutorial, we discussed how to create tables and views in MySQL. We covered the syntax, examples, output, explanation, use, and important points of creating tables and views. With this knowledge, you can now create tables and views in MySQL to store and present your data in a structured way.

Published on: