MySQL Basics-SELECT statement

Basic-SELECT statement Detailed SELECT statement detail one, experimental introduction

The most commonly used SELECT statement in SQL, which is used to select data in a table, will be studied in detail using a series of hands-on instructions to learn more about the use of SELECT statements.

II. Preparation of the experiment

Before formally starting this experiment, you need to download the relevant database tables, set up a database named mysql_shiyan (three tables: Department,employee,project), and insert the data into it.

To do this, first enter the command into the /home/shiyanlou/Desktop directory:

cd /home/shiyanlou/Desktop

Then enter the command to download the code:

git clone http://git.shiyanlou.com/shiyanlou/SQL4

When the download is complete, enter the command to open the MySQL service and log in with the root user:

#打开 MySQL 服务sudo service mysql start        #使用 root 用户登录mysql -u root

Just download the SQL4 directory, there is a two file MySQL-04-01.sql and MySQL-04-02.sql , where the first file is used to create a database, the second file inserts data into the database.

(SQL4 directory on the desktop, you can use Gedit to view the two files inside.) ）

To load the data in the file, you need to enter the command in the MySQL console to build the database:

source /home/shiyanlou/Desktop/SQL4/MySQL-04-01.sql

To insert data into the database:

source /home/shiyanlou/Desktop/SQL4/MySQL-04-02.sql

Iii. contents of the experiment

In a database operation statement, the most frequently used, and also considered the most important, is the SELECT query statement. In previous experiments, we have used this statement in a number of places to SELECT * FROM table_name; see everything in a single table. and SELECT with a variety of restrictive terms with the use of keywords, with a variety of rich features, the experiment will be detailed introduction.

1. Basic SELECT statement

The basic format of the SELECT statement is:

SELECT 要查询的列名 FROM 表名字 WHERE 限制条件;

If you want to query all the contents of the table, the column names to be queried are represented by an asterisk * number (which is already used in Experiment 2, 3), which means that you want to query all the columns in the table. In most cases, we only need to look at the specified columns of a table, for example to see the name and age of the employee table:

SELECT name,age FROM employee;

2. Mathematical symbol conditions

SELECT statements often have a WHERE constraint that is used to achieve a more accurate query. Where restrictions can have mathematical symbols ( =,<,>,>=,<= ), we have just queried the name and age, and now make a slight change:

SELECT name,age FROM employee WHERE age>25;

Filter out results with age greater than 25:

Or find an employee whose name is Mary:

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

The result is of course:

3, "and" and "or"

From these two words you can understand their role. There can be more than one limit behind where, and depending on the logical relationship between conditions, you can connect with or (or) and and (and):

#筛选出 age 小于 25，或 age 大于 30SELECT name,age FROM employee WHERE age<25 OR age>30;

#筛选出 age 大于 25，且 age 小于 30SELECT name,age FROM employee WHERE age>25 AND age<30;

The constraints age>25 and age<30, if required to contain the two digits 25 and 30, can be replaced by age between and 30:

4, in and not in

Keywords in and not are as obvious as their names, and are used to filter results in a range of "in" or "not", for example, if we want to query people in DPT3 or DPT4:

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

The effect of not in is that, as in the following command, the person who is not dpt1 or not DPT3 is queried:

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

5. Wildcard characters

The keyword like is used in SQL statements and wildcards, which represent unknown characters. The wildcard characters in SQL are the _ and % . _it represents an unspecified character and represents an unspecified % character.

For example, to remember only the first four digits of the phone number 1101, and then two forgotten, you can use two _ wildcard characters instead:

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

This will find the 6-digit phone number that starts with 1101:

In another case, such as remembering only the first letter of the name and not knowing the length of the name, a wildcard is used % instead of a variable character:

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

This will find the person with the first letter J:

6. Sort the results

To make the query result look more pleasing to the eye, we may need to sort the results by a column, which uses the order by sort key. By default, the result of order by is arranged in ascending order, while the keyword ASC and desc can be used to specify ascending or descending sorting. For example, we sort by salary in descending order, the SQL statement is:

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

7. SQL built-in functions and calculations

SQL allows you to calculate the data in a table. For this, SQL has 5 built-in functions that operate on the results of a SELECT:

function Name:	COUNT	SUM	AVG	MAX	MIN
Role:	Count	Sum	Averaging	Maximum Value	Minimum value

Where the Count function can be used for any data type (because it is just a count), and the other 4 functions can only calculate the numeric class data type.

Specific examples, such as calculating the maximum and minimum value of a salary, use a statement like this:

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

In one detail you may have noticed that using the AS keyword can be used to rename a value, such as a maximum value named for Max_salary:

8, sub-query

The SELECT statement discussed above covers only the data in one table, but sometimes you have to work with multiple tables to get the information you need. For example: Want to know the department of the employee named "Tom" has done several projects. Employee information is stored in the employee table, but the project information is stored in the project table.

For such a situation, we can use subqueries:

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

Subqueries can also be extended to layers 3, 4, or more.

9. Connection Query

When working with multiple tables, subqueries are only useful when the results come from a table. However, if you need to display data from two or more tables, you must use a JOIN operation. The basic idea of a connection is to manipulate two or more tables as a new table, as follows:

SELECT id,name,people_numFROM employee,departmentWHERE employee.in_dpt = department.dpt_nameORDER BY id;

This statement queries the number of employees in the department, where the ID and name of the employee are from the employee table, people_num from the Department table:

The other Connection statement format is using the join on syntax, which is equivalent to the following statement:

SELECT id,name,people_numFROM employee JOIN departmentON employee.in_dpt = department.dpt_nameORDER BY id;

The result is the same as the statement just now.

Iv. Summary

In this section of the experiment, we learned the common methods of SELECT statements:

1. Basic syntax
2. Mathematical symbol conditions
3. and OR in
4. Wildcard characters
5. Sort
6. SQL built-in functions and calculations
7. Subqueries and connection queries

V. Homework

1. Follow the procedure: Build the database with git-downloaded code, insert the data, and try out the various SELECT statements. The experimental process.
2. Using the connection Query method, the number of employees in the department and the number of projects, the number of projects named Count_project. (Connect 3 tables and use the count built-in function)

MySQL Basics-SELECT statement