SQL Fundamentals | SELECT Statement | Database Management-Mysql Tutorial-php.cn

Introduction

SQL Fundamentals | SELECT Statement | Database Management

SELECT statement, one of the most commonly used statements in SQL, is used to select data in a table. This lab will learn SELECT and how to apply it to real practices.

Learning Objective

SELECT Basic syntax
Mathematical symbol conditions
AND OR IN
Wildcards
Sort
SQL built-in functions and calculations
Subqueries and joins queries

Preparation

Before we start, download relevant data tables and create a database named mysql_labex (3 tables: department, employee, project).

Start MySQL service and log in as root.

cd ~/project sudo service mysql start mysql -u root

Copy after login

There are two files create-database.sql and insert-data.sql, which are located in ~/project/.

Load data in the file. You need to enter the command in the MySQL console to build the database:

source ~/project/create-database.sql source ~/project/insert-data.sql

Copy after login

In the database operating statements, the most frequently used, also considered the most important is the SELECT query. In previous labs, we've used SELECT * FROM table_name; statements in many places to see everything in a table. SELECT can be used with keywords of a variety of constraints, which encompass a variety of features. This lab will introduce these uses in detail.

Basic SELECT statement

The basic format of SELECT statement:

SELECT row name FROM table name WHERE constraint;

Copy after login

If you want to query all contents of the table, thenquery the name of the columnwith an asterisk *, which represents all columns in the table will be queried. In most cases, we only need to see the specified column of a table, such as to see the name and age of the employee table:

USE mysql_labex; SELECT name,age FROM employee;

Copy after login

MariaDB [mysql_labex]> SELECT name,age FROM employee; +------+------+ | name | age | +------+------+ | Tom | 26 | | Jack | 24 | | Rose | 22 | | Jim | 35 | | Mary | 21 | | Alex | 26 | | Ken | 27 | | Rick | 24 | | Joe | 31 | | Mike | 23 | | Jobs | NULL | | Tony | NULL | +------+------+ 12 rows in set (0.000 sec)

Copy after login

Mathmatical symbol conditions

SELECT statements often have WHERE constraints, used to achieve more accurate queries. WHERE constraints can have mathematical notation (=, <,>,>=, <=). We just queried the name and age, and now let's make a slight modification:

SELECT name,age FROM employee WHERE age>25; 
 Filter results with age over 25:
 
 MariaDB [mysql_labex]> SELECT name,age FROM employee WHERE age>25; +------+------+ | name | age | +------+------+ | Tom | 26 | | Jim | 35 | | Alex | 26 | | Ken | 27 | | Joe | 31 | +------+------+ 5 rows in set (0.000 sec)
        
         Copy after login

Or find the name, age, and phone of an employee named Mary:

SELECT name,age,phone FROM employee WHERE name='Mary';

Copy after login

Result:

MariaDB [mysql_labex]> SELECT name,age,phone FROM employee WHERE name='Mary'; +------+------+--------+ | name | age | phone | +------+------+--------+ | Mary | 21 | 100101 | +------+------+--------+ 1 row in set (0.000 sec)

Copy after login

"AND" & "OR"

We can have more than one constraints after WHERE, and based on the logical relationship of these conditions, we can useORandANDto connect:

Filter - age is less than 25, or age is greater than 30

SELECT name,age FROM employee WHERE age<25 OR age>30;

Copy after login

MariaDB [mysql_labex]> SELECT name,age FROM employee WHERE age<25 OR age>30; +------+------+ | name | age | +------+------+ | Jack | 24 | | Rose | 22 | | Jim | 35 | | Mary | 21 | | Rick | 24 | | Joe | 31 | | Mike | 23 | +------+------+ 7 rows in set (0.000 sec)

Copy after login

Filter - age is greater than 25, and age is less than 30

SELECT name,age FROM employee WHERE age>25 AND age<30;

Copy after login

If we need to include age 25 and 30, useage BETWEEN 25 AND 30:

MariaDB [mysql_labex]> SELECT name,age FROM employee WHERE age>25 AND age<30; +------+------+ | name | age | +------+------+ | Tom | 26 | | Alex | 26 | | Ken | 27 | +------+------+ 3 rows in set (0.000 sec) MariaDB [mysql_labex]> SELECT name,age FROM employee WHERE age BETWEEN 25 AND 30; +------+------+ | name | age | +------+------+ | Tom | 26 | | Alex | 26 | | Ken | 27 | +------+------+ 3 rows in set (0.000 sec)

Copy after login

IN & NOT IN

KeywordsINandNOT INare used to filter results in a certain range. For instance, we want to find people indpt3ordpt4:

SELECT name,age,phone,in_dpt FROM employee WHERE in_dpt IN ('dpt3','dpt4');

Copy after login

ForNOT IN, such as in the following command, we will get people not indpt1nordpt3:

SELECT name,age,phone,in_dpt FROM employee WHERE in_dpt NOT IN ('dpt1','dpt3');

Copy after login

MariaDB [mysql_labex]> SELECT name,age,phone,in_dpt FROM employee WHERE in_dpt IN ('dpt3','dpt4'); +------+------+--------+--------+ | name | age | phone | in_dpt | +------+------+--------+--------+ | Tom | 26 | 119119 | dpt4 | | Rose | 22 | 114114 | dpt3 | | Rick | 24 | 987654 | dpt3 | | Mike | 23 | 110110 | dpt4 | | Tony | NULL | 102938 | dpt3 | +------+------+--------+--------+ 5 rows in set (0.000 sec) MariaDB [mysql_labex]> SELECT name,age,phone,in_dpt FROM employee WHERE in_dpt NOT IN ('dpt1','dpt3'); +------+------+--------+--------+ | name | age | phone | in_dpt | +------+------+--------+--------+ | Tom | 26 | 119119 | dpt4 | | Jack | 24 | 120120 | dpt2 | | Mary | 21 | 100101 | dpt2 | | Joe | 31 | 110129 | dpt2 | | Mike | 23 | 110110 | dpt4 | | Jobs | NULL | 19283 | dpt2 | +------+------+--------+--------+ 6 rows in set (0.000 sec)

Copy after login

Wildcards

The keywordLIKEis used with wildcards in SQL statements, with wildcards representing unknown characters. Wildcards in SQL are _ and %. Which _ represents an unspecified character, %representsindefiniteunspecified characters.

For example, if you only remember that the first four digits of the phone number are 1101 and the last two digits are forgotten, you can replace them with two _ wildcards:

SELECT name,age,phone FROM employee WHERE phone LIKE '1101__';

Copy after login

and here we have phone numbers starting with 1101:

MariaDB [mysql_labex]> SELECT name,age,phone FROM employee WHERE phone LIKE '1101__'; +------+------+--------+ | name | age | phone | +------+------+--------+ | Joe | 31 | 110129 | | Mike | 23 | 110110 | +------+------+--------+ 2 rows in set (0.000 sec)

Copy after login

In another case, such as when you only remember the first letter of the name, and you do not know the length of the name, then use % wildcard instead of indefinite characters:

SELECT name,age,phone FROM employee WHERE name LIKE 'J%';

Copy after login

Here we have names starting withJ:

MariaDB [mysql_labex]> SELECT name,age,phone FROM employee WHERE name LIKE 'J%'; +------+------+--------+ | name | age | phone | +------+------+--------+ | Jack | 24 | 120120 | | Jim | 35 | 100861 | | Joe | 31 | 110129 | | Jobs | NULL | 19283 | +------+------+--------+ 4 rows in set (0.000 sec)

Copy after login

Sort your results

In order to make queried results more organized and easy to follow, we might need to sort them by certain rules.ORDER BYcomes in handy. By default,ORDER BYis inascendingarrangement, and by usingASCandDESC, we can also get results in ascending and descending order.

For example, we sort salary in a descending order, SQL statement:

SELECT name,age,salary,phone FROM employee ORDER BY salary DESC;

Copy after login

MariaDB [mysql_labex]> SELECT name,age,salary,phone FROM employee ORDER BY salary DESC; +------+------+--------+--------+ | name | age | salary | phone | +------+------+--------+--------+ | Jobs | NULL | 3600 | 19283 | | Joe | 31 | 3600 | 110129 | | Ken | 27 | 3500 | 654321 | | Rick | 24 | 3500 | 987654 | | Mike | 23 | 3400 | 110110 | | Tony | NULL | 3400 | 102938 | | Alex | 26 | 3000 | 123456 | | Mary | 21 | 3000 | 100101 | | Jim | 35 | 3000 | 100861 | | Rose | 22 | 2800 | 114114 | | Jack | 24 | 2500 | 120120 | | Tom | 26 | 2500 | 119119 | +------+------+--------+--------+ 12 rows in set (0.000 sec)

Copy after login

SQL built-in functions and calculations

SQL allows the calculation of the data in the table. In this regard, SQL has five built-in functions that do the result of SELECT:

Function:	COUNT	SUM	AVG	MAX	MIN
For:	count numbers	sum up	average	maximum value	minimum value

The COUNT function can be used for any data type (because it is only a count), while SUM and AVG functions can only calculate numeric data types. MAX and MIN can be used for numeric, string, or datetime data types.

For example, when we want to calculate the maximum and minimum value of salary, we use a statement like this:

SELECT MAX(salary) AS max_salary,MIN(salary) FROM employee;

Copy after login

You may have noticed a tiny detail.Use AS keyword can rename value. E.g. Max value is renamed into max_salary:

MariaDB [mysql_labex]> SELECT MAX(salary) AS max_salary,MIN(salary) FROM employee; +------------+-------------+ | max_salary | MIN(salary) | +------------+-------------+ | 3600 | 2500 | +------------+-------------+ 1 row in set (0.000 sec)

Copy after login

Subqueries

The SELECT statements discussed above all involve data in only one table, but sometimes you have to process multiple tables to get the information you need. For example, you want to know a few projects done by the department where the employee named "Tom" is located. Employee information is stored in the employee table, but the project information is stored in the project table.

We can use subqueries to deal with such situations:

SELECT of_dpt,COUNT(proj_name) AS count_project FROM project WHERE of_dpt IN (SELECT in_dpt FROM employee WHERE name='Tom');

Copy after login

MariaDB [mysql_labex]> SELECT of_dpt,COUNT(proj_name) AS count_project FROM project -> WHERE of_dpt IN -> (SELECT in_dpt FROM employee WHERE name='Tom'); +--------+---------------+ | of_dpt | count_project | +--------+---------------+ | dpt4 | 2 | +--------+---------------+ 1 row in set (0.000 sec)

Copy after login

Subqueries can also be extended to three, four or more layers.

Join

When dealing with multiple tables, the subquery is only useful when the results are from the same table. However, if you need to display data in two or more tables, you must use thejoinoperation.

The basic idea is to connect two or more tables as a new table to operate, as follows:

SELECT id,name,people_num FROM employee,department WHERE employee.in_dpt = department.dpt_name ORDER BY id;

Copy after login

This result is the number of employees in each department, where employee id and name from the employee table, people_num from the department table:

MariaDB [mysql_labex]> SELECT id,name,people_num -> FROM employee,department -> WHERE employee.in_dpt = department.dpt_name -> ORDER BY id; +----+------+------------+ | id | name | people_num | +----+------+------------+ | 1 | Tom | 15 | | 2 | Jack | 12 | | 3 | Rose | 10 | | 4 | Jim | 11 | | 5 | Mary | 12 | | 6 | Alex | 11 | | 7 | Ken | 11 | | 8 | Rick | 10 | | 9 | Joe | 12 | | 10 | Mike | 15 | | 11 | Jobs | 12 | | 12 | Tony | 10 | +----+------+------------+ 12 rows in set (0.000 sec)

Copy after login

Another connection statement format is to use the JOIN ON syntax. The statement is the same as:

SELECT id,name,people_num FROM employee JOIN department ON employee.in_dpt = department.dpt_name ORDER BY id;

Copy after login

Result is the same.

Summary

In this lab we learned the basic use of SELECT statement:

Basic syntax
Mathmatical symbol conditions
AND OR IN
Wildcards
Sort
SQL built-in functions and calculations
Sunqueries and connection queries

? Practice Now: SQL's SELECT Statement

Want to Learn More?

? Learn the latest MySQL Skill Trees
? Read More MySQL Tutorials
? Join our Discord or tweet us @WeAreLabEx

The above is the detailed content of SQL Fundamentals | SELECT Statement | Database Management. For more information, please follow other related articles on the PHP Chinese website!