官术网_书友最值得收藏!

Storing and retrieving data with MySQL

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.

Getting ready

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.

How to do it…

Follow these steps to store and retrieve data with MySQL:

  1. First, we will need to connect to the MySQL server. Replace 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
    
  2. When prompted, enter the password for the admin account. If the password is correct, you will see the following MySQL prompt:
  3. Create a database with the following query. Note the semi-colon at the end of query:
    mysql > create database myblog;
    
  4. Check all databases with a show databases query. It should list myblog:
    mysql > show databases;
    
  5. Select a database to work with, in this case myblog:
    mysql > use myblog;
    Database changed
    
  6. Now, after the database has changed, we need to create a table to store our data. Use the following query to create a table:
    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;
  7. Again, you can check tables with the show tables query:
    mysql > show tables;
  8. Now, let’s insert some data in our table. Use the following query to create a new record:
    mysql > INSERT INTO `articles` (`id`, `title`, `content`, `created_at`)
    VALUES (NULL, ‘My first blog post’, ‘contents of article’, CURRENT_TIMESTAMP);
  9. Retrieve data from the table. The following query will select all records from the articles table:
    mysql > Select * from articles;
  10. Retrieve the selected records from the table:
    mysql > Select * from articles where id = 1;
  11. Update the selected record:
    mysql > update articles set title=”New title” where id=1;
  12. Delete the record from the articles table using the following command:
    mysql > delete from articles where id = 2;

How it works…

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.

主站蜘蛛池模板: 灵台县| 庆城县| 天全县| 鞍山市| 正蓝旗| 平和县| 怀仁县| 工布江达县| 宽甸| 合山市| 康平县| 武穴市| 临夏市| 乌拉特后旗| 宁晋县| 濮阳市| 尉氏县| 昌宁县| 呈贡县| 清河县| 潞城市| 合肥市| 永善县| 嵊州市| 翁源县| 安仁县| 凤庆县| 伊吾县| 龙井市| 偃师市| 赣州市| 那曲县| 晴隆县| 商城县| 太白县| 安康市| 丰宁| 大化| 锦州市| 安多县| 台前县|