{"id":94655,"date":"2022-06-15T22:47:05","date_gmt":"2022-06-15T22:47:05","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=94655"},"modified":"2022-06-15T22:47:05","modified_gmt":"2022-06-15T22:47:05","slug":"introducing-the-mysql-select-statement","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/mysql\/introducing-the-mysql-select-statement\/","title":{"rendered":"Introducing the MySQL SELECT statement"},"content":{"rendered":"

This article is part of Robert Sheldon's continuing series on Learning MySQL. To see all of the items in the series, click here<\/a>.<\/strong><\/p>\n\n

Up to this point in the series, I\u2019ve focused primarily on creating schema objects in a MySQL database. The SQL statements used to create these objects are typically categorized as data definition language (DDL). They include statements such as CREATE<\/code> TABLE<\/code>, CREATE<\/code> VIEW<\/code>, and CREATE<\/code> PROCEDURE<\/code>.<\/p>\n

Beginning with this article, I\u2019m switching my attention to data manipulation language (DML), which includes statements used to query and modify data. Four of the most common DML statements are SELECT<\/code>, INSERT<\/code>, UPDATE<\/code>, and DELETE<\/code>. You can use the SELECT<\/code> statement to retrieve data, the INSERT<\/code> statement to add data, the UPDATE<\/code> statement to modify data, and the DELETE<\/code> statement to remove data.<\/p>\n

In this article, I focus on the SELECT<\/code> statement, which is used extensively when working with relational databases. You can use the SELECT<\/code> statement to return data from one or more MySQL tables, as well as to retrieve information about the server and database environment. The SELECT<\/code> statement is one of the most commonly used SQL statements in MySQL. It is also one of the most complex.<\/p>\n

You got a taste of the SELECT<\/code> statement in earlier articles in this series, but it was included only to support the DDL examples in those articles. This article focuses exclusively on the SELECT<\/code> statement and the basic elements that go into building one. I\u2019ll be covering other DML statements later in this series.<\/p>\n

Preparing your MySQL environment<\/h2>\n

For the examples in this article, I used the same database (travel<\/code>) that you saw in the previous articles and the same two tables (manufacturers<\/code> and airplanes<\/code>). However, I added a lot more data this time around. If you want to try out the examples for yourself, you can download the MySQL_06_setup<\/a>\u00a0<\/strong>file and run it against your MySQL instance. The SQL in the file creates the database, adds the two tables, and inserts data in both tables.<\/p>\n

For your convenience, I\u2019ve included the two table definitions here so you can reference them when trying out the SELECT<\/code> statements in the examples later in the article:<\/p>\n

CREATE TABLE manufacturers (\r\n  manufacturer_id INT UNSIGNED NOT NULL AUTO_INCREMENT,\r\n  manufacturer VARCHAR(50) NOT NULL,\r\n  create_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,\r\n  last_update TIMESTAMP NOT NULL \r\n    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\r\n  PRIMARY KEY (manufacturer_id) ) \r\nENGINE=InnoDB AUTO_INCREMENT=1001;\r\nCREATE TABLE airplanes (\r\n  plane_id INT UNSIGNED NOT NULL AUTO_INCREMENT,\r\n  plane VARCHAR(50) NOT NULL,\r\n  manufacturer_id INT UNSIGNED NOT NULL,\r\n  engine_type VARCHAR(50) NOT NULL,\r\n  engine_count TINYINT NOT NULL,\r\n  max_weight MEDIUMINT UNSIGNED NOT NULL,\r\n  wingspan DECIMAL(5,2) NOT NULL,\r\n  plane_length DECIMAL(5,2) NOT NULL,\r\n  parking_area INT GENERATED ALWAYS AS ((wingspan * plane_length)) STORED,\r\n  icao_code CHAR(4) NOT NULL,\r\n  create_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,\r\n  last_update TIMESTAMP NOT NULL \r\n    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\r\n  PRIMARY KEY (plane_id),\r\n  CONSTRAINT fk_manufacturer_id FOREIGN KEY (manufacturer_id) \r\n    REFERENCES manufacturers (manufacturer_id) ) \r\nENGINE=InnoDB AUTO_INCREMENT=101;<\/pre>\n
If you already have the database and tables on your MySQL instance and don\u2019t want to re-create them, you can run just the INSERT<\/code> statements in the MySQL_06_setup.sql<\/strong> script. However, you should first delete any data in the table because the data being added to the airplanes<\/code> table references specific values in the manufacturers<\/code> table. Starting with empty tables will also ensure that you\u2019re seeing the same results as in my examples. When you run the INSERT<\/code> statements, be sure to execute them in the order specified in the file.<\/p>\n
The SELECT<\/strong><\/code> statement syntax<\/h2>\n
A MySQL SELECT<\/code> statement is typically used to retrieve data from a single table or from multiple tables. The statement is made up of one or more clauses that together provide the logic needed to retrieve the necessary data. The statement\u2019s syntax is fairly elaborate, but to help you get started, I\u2019ve narrowed it down to the most fundamental elements:<\/p>\n
SELECT [ALL | DISTINCT] select_list \r\n[FROM table_ref] \r\n[WHERE where_condition] \r\n[GROUP BY group_list] \r\n[HAVING having_condition]\r\n[ORDER BY order_list]<\/pre>\n
The five clauses shown in the syntax are highly versatile and can be used to build a wide range of statements:<\/p>\n