How to Create a Table in MySQL on Ubuntu 20.04 (Display Data)
How to Create a Table in MySQL on Ubuntu 20.04 (Display Data). MySQL is a popular open source relational database management system that enables efficient storage, organization, and retrieval of data.
Well, MySQL is the most widely used database management system. It is a fundamental skill for any database administrator to understand how to create tables and display data using various methods.
This article walks you through the step-by-step process to create tables with appropriate column definitions and populate these tables with data. Additionally, we explore various techniques to display and retrieve data from these tables using SQL queries.
What is MySQL?
Simply put, MySQL, an open-source relational database management system (RDBMS) with a large user base, offers a reliable and scalable solution for storing, managing, and organizing structured data.
MySQL is an RDBMS that adheres to the relational paradigm, in which data is arranged into tables with rows and columns. It supports SQL (Structured Query Language) for querying, modifying, and managing data in these tables.
It offers a wide range of SQL capabilities, including support for transactions, indexing, and protecting data integrity through constraints. What is more, MySQL has the advantage of being easy to use and comes with extensive documentation, making it accessible to beginners and experienced developers alike. It performs excellently, offers high availability, and boasts strong security features.
Moreover, MySQL is compatible with various operating systems, programming languages, and platforms, making it versatile and applicable in a wide range of scenarios.
Basically, MySQL provides a trustworthy and effective method for handling your data, regardless of whether you’re developing a website, administering an e-commerce platform, or developing enterprise-level applications.
Features of MySQL
Here are some key features of MySQL that contribute to its popularity and widespread use in database management:
- Relational database management.
- SQL language support.
- Scalability and performance.
- Data security.
- Stored procedures and functions.
- Triggers.
- Indexing.
- Views.
- Replication and high availability
- Cross platform compatibility.
- Community support.
Benefits of MySQL
- High performance
- Reliability and availability.
- Scalability.
- Security.
- Open source and cost effective.
- Comprehensive feature set.
Role of Tables in MySQL
Tables play a crucial role in relational databases like MySQL, as they serve as the fundamental building blocks for structured storage, organization, retrieval, manipulation, and analysis of data.
They form the backbone of data management, ensuring efficient handling and utilization of information.
Moreover, tables enforce data integrity by defining constraints. These constraints ensure that the data remains consistent, accurate, and reliable.
Overall, tables enable efficient management and utilization of data, facilitating various data-related operations within the database system.
How to Create a Table in MySQL on Ubuntu 20.04 (Display Data)
Prerequisites
- Access to a terminal window or command line interface.
- Ubuntu 20.04 system with MySQL installed.
- A MySQL user account with root or admin privileges.
CREATE TABLE General Syntax
Data is stored and organized in columns and rows according to table construction specifications in a MySQL table.
The general syntax of the CREATE TABLE command is as follows:
CREATE TABLE [IF NOT EXISTS] table_name(
column_definition1,
column_definition2,
........,
table_constraints
);
In this command, column definition entails the datatype and size specifications as well as special characteristics declarations, for example, not null. The table constraints usually declare the primary or secondary keys, which are used to define relationships in different tables of MySQL.
Create a Table in MySQL on Ubuntu 20.04 Using the Command Line Interface
MySQL Shell Login
Open the terminal by pressing CTRL+ALT+T. And use the following command with your own username to login into the MySQL shell.
sudo mysql -u username\root -p
Enter the password for your account when prompted.
The above command is for password protected accounts. For this article’s implementation, the root user is used, which has no password, so login into the MySQL shell using the following command.
sudo mysql -u root
See the mysql> prompt, which means that you have successfully logged into the MySQL shell. Now run SQL commands to create databases and tables.
Database Creation
You will need to create a database before creating a table. You can create databases using the CREATE DATABASE command.
CREATE DATABASE movies;
Next, run the following command to verify the database is created successfully.
SHOW DATABASES;
The above command shows a list of all existing databases. You see your database name, which verifies the database’s creation.
You need to select the database before making tables or any other changes to it. Run the following command to select the recently created database.
USE movies;
Table Creation
You are making a table called movies as an example, and you are defining its columns: title, genre, director, and release year. You need to set its title as the primary key.
Use the following CREATE TABLE command to make a table according to the above specification.
CREATE TABLE movies(title VARCHAR(50) NOT NULL,genre VARCHAR(30) NOT NULL,director VARCHAR(60) NOT NULL,release_year INT NOT NULL,PRIMARY KEY(title));
Verify the table and its specifications using the following command.
DESCRIBE movies;
Run the following command to populate movie information into the movies table.
INSERT INTO movies VALUE ("Joker", "psychological thriller", "Todd Phillips", 2019);
Verify the success of the INSERT command using the following command.
SELECT * FROM movies;
See the populated records in the output on Ubuntu 20.04 system.
Create a Table in MySQL on Ubuntu 20.04 Using a File Script
Alternatively, you can create a script file and use that script file to do all the steps in one go.
To achieve that, open a text editor on your Ubuntu 20.04 system and enter the following code into it.
CREATE DATABASE movies1;
USE movies1;
CREATE TABLE movies1(title VARCHAR(50) NOT NULL,genre VARCHAR(30) NOT NULL,director VARCHAR(60) NOT NULL,release_year INT NOT NULL,PRIMARY KEY(title));
INSERT INTO movies1 VALUE ("Joker", "psychological thriller", "Todd Phillips", 2019);
Next, save and exit the file with an appropriate name.
Now run the following command to log in to the MySQL shell and run the aforementioned script.
sudo mysql -u root < movies1.sql
Run the following commands to confirm the successful execution of the script.
SHOW DATABASES;
USE movies1;
SELECT * FROM movies1;
Query and Display MySQL Data on Ubuntu 20.04
There are several options to display MySQL table data. You can display the table in its entirety with the following command.
SELECT * FROM movies;
Alternatively, you only display specific columns of the table using the following command.
SELECT title FROM movies;
Another way to display data is to use the concept of View. Views in MySQL are virtual tables created based on the result of a SELECT query. They do not store any data themselves but present data from one or more existing tables based on specific criteria.
For example, if you want to display movie titles whose release year is after 1990, you can create a view called minimum_release_year and run the CREATE VIEW command with these specific query parameters:
CREATE VIEW minimum_release_year AS SELECT title FROM movies WHERE release_year > 1990;
Display the results of the view using the following command.
SELECT * FROM minimum_release_year;
You can see that only records meeting the query parameters are displayed in the above output.
Also alter the view, if necessary with the following command.
ALTER VIEW minimum_release_year AS SELECT title FROM movies WHERE release_year < 2018;
Run the following command to verify the alteration has been made.
SELECT * FROM minimum_release_year;
That’s it for how to create a table and display data in MySQL on an Ubuntu 20.04 system.
How to Create a Table in MySQL on Ubuntu 20.04 (Display Data) Conclusion
With a variety of features and advantages for effective data administration, MySQL is a strong and adaptable relational database management system. With its simplicity of use, great performance, scalability, dependability, and strong security, MySQL offers developers and enterprises an excellent option.
Using MySQL gives you the ability to alter, store, and save data effectively, enabling successful database administration.
In this article, you have learned how to create tables with appropriate columns and data types, retrieve, and display data in MySQL on an Ubuntu 20.04 system.
Explore our MySQL section to learn more.
