In this recipe, we will learn how to create databases and tables and store data in those tables. We will learn the basic Structured Query Language (SQL) required for working with MySQL. We will focus on using the command-line MySQL client for this tutorial, but you can use the same queries with any client software or code.
Ensure that the MySQL server is installed and running. You will need administrative access to the MySQL server. Alternatively, you can use the root account of MySQL.
Follow these steps to store and retrieve data with MySQL:
admin
with a user account on the MySQL server. You can use root as well but it’s not recommended:$ mysql -u admin -h localhost -p
admin
account. If the password is correct, you will see the following MySQL prompt:mysql > create database myblog;
show
databases query. It should list myblog
:mysql > show databases;
myblog
:mysql > use myblog; Database changed
CREATE TABLE `articles` ( `id` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(255) NOT NULL, `content` text NOT NULL, `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1;
show tables
query:mysql > show tables;
mysql > INSERT INTO `articles` (`id`, `title`, `content`, `created_at`) VALUES (NULL, ‘My first blog post’, ‘contents of article’, CURRENT_TIMESTAMP);
mysql > Select * from articles;
mysql > Select * from articles where id = 1;
mysql > update articles set title=”New title” where id=1;
articles
table using the following command:mysql > delete from articles where id = 2;
We have created a relational database to store blog data with one table. Actual blog databases will need additional tables for comments, authors, and various entities. The queries used to create databases and tables are known as Data Definition Language (DDL), and queries that are used to select, insert, and update the actual data are known as Data Manipulation Language (DML).
MySQL offers various data types to be used for columns such as tinyint
, int
, long
, double
, varchar
, text
, blob
, and so on. Each data type has its specific use and a proper selection may help to improve the performance of your database.