MySQL Use view table SELECT statement

Source: Laboratory Building

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

Basic syntax

Mathematical symbol conditions

and OR in

Wildcard characters

Sort

SQL built-in functions and calculations

Subqueries and connection queries

Enter command, download code: Git clone https://github.com/shiyanlou/SQL4

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

#打开 MySQL Service

sudo service MySQL start

#使用 Root User Login

Mysql-u Root

Just download the SQL4 directory, there are two files 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.) ）

Load the data in the file, you need to enter the command in the MySQL console,

Build database:

Source/home/shiyanlou/desktop/sql4/mysql-04-01.sql

To insert data into a database:

Source/home/shiyanlou/desktop/sql4/mysql-04-02.sql

The basic format of the SELECT statement is:

SELECT the column name to query from the table name WHERE restriction condition;

If you want to query all the contents of a table, the column name you want to query is represented by an asterisk *, which means that you want to query all the columns in the table.

SELECT * from employee;

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;

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

SELECT name,age from Employee WHERE age>25; filters out the results of age greater than 25:

Or find a name,age and phone for an employee named Mary:

SELECT Name,age,phone from employee WHERE name= ' Mary ';

#筛选出 age is less than 25, or age is greater than a SELECT name,age from employee WHERE Age30;

If you need to include 25 and 30 of these two numbers, you can replace the age between and 30;

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 the employee WHERE IN_DPT in (' Dpt3 ', ' dpt4 ');

The keyword like is used in SQL statements and wildcards, which represent unknown characters. The wildcard characters in SQL are _ and%.

Where _ represents an unspecified character, and% indicates 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 the employee WHERE phone like ' 1101__ '; This will look for a 6-digit number that starts with 1101: HTTPS://DOC.SHIYANLOU.COM/MYSQL/SQL-04-09.PNG/WM

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

SELECT Name,age,phone from the employee WHERE name like ' j% '; This will find the person with the first letter J: HTTPS://DOC.SHIYANLOU.COM/MYSQL/SQL-04-10.PNG/WM

Calculates the maximum and minimum value of the salary, using such a statement:

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:

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 Project GROUP by OF_DPT have of_dpt in (SELECT IN_DPT from employee WHERE name= ' Tom ');

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 (derive) two or more tables as a new table, as follows:

SELECT id,name,people_num from employee,department WHERE employee.in_dpt = Department.dpt_name the ORDER 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: https://doc.shiyanlou.com/MySQL/sql-04-14 . PNG/WM Another connection statement format is the use of the join on syntax, just as the statement is equivalent to:

SELECT Id,name,people_num from the employee JOIN department on EMPLOYEE.IN_DPT = Department.dpt_name the ORDER by ID; The result is the same as the statement just now.

Q: 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)

Answer reference: 78428526

Thank you!

MySQL Use view table SELECT statement