(Big Data Engineer Learning path) Fourth Step SQL Foundation Course----Select detailed

Get ready

Before formally starting this content, you need to download the relevant code from GitHub and set up a database called Mysql_shiyan (with three tables: Department,employee,project) and insert the data into it.

To do this, first enter the command into desktop:

cd Desktop

Then enter the command to download the code:

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

When the download is complete, enter "CD ~" (note that there are spaces) to return to the original directory, then enter the command to open the MySQL service and log in with the root user:

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

The SQL4 directory that you just downloaded from GitHub has a two file "Mysql-04-01.sql" and "Mysql-04-02.sql" where the first file is used to create the database and the second file inserts data into the database.

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

Enter a command to run the first file and build the database:

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

Run a second file to insert data into the database:

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

First, the content

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 SELECT * FROM table_name in many places; This statement is used to view all the contents of a 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 name to query is represented by an * number (which has already been used in Experiment 2 and 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 notation (=,<,>,>=,<=), 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) :

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

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

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

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 for DPT3 or Dpt4 's People:

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 _ 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 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, the% wildcard is used instead of the indefinite characters:

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 oforder 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.

Second, practice

1, using the method of connection query, 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)

The code is as follows: (The SQL Foundation is not good, has studied the good general meeting son, may have some cumbersome place, welcome correct correction!) ）：

Results:

(Big Data Engineer Learning path) Fourth Step SQL Foundation Course----Select detailed