Read notes in MySQL developer SQL authoritative guide

Using

From teams inner join players on teams. playerno = players. playerno

From teams inner join players using (playerno)

Aggregate functions and grouped data

Aggregate functions

Data Group

The preceding SQL functions are actually row functions. A row function is a function applied to each record. This chapter describes the column functions used for statistical summary. In addition to column functions, this chapter also introduces the Data grouping method, that is, the group by clause usage. In practical applications, statistical summary and Data grouping are often used together.

Use the count function to calculate the number of records

Count syntax

Count (column name)

Example:Count students in student table

Select count (*) as total count from student

Example:Count the number of people without email addresses

Select count (*) as number of people without mailboxes from student where mail is null

Sum Using the sum function

Syntax:

Sum (column name)

Example:Calculate the total score of jihe in wj2

Select sum (jihe) as geometric score from wj2

Calculate the average value of a field using the AVG Function

Syntax:

AVG (column name)

Example:Calculate the average value of jihe in the wj2 table

Select AVG (jihe) as geometric score average from wj2

Use the max and Min functions to obtain the maximum and minimum values.

Syntax:

Max (column name)

Min (column name)

Example:Calculate the highest and lowest geometric score in the wj2 table.

Select max (jihe) as geometric highest score, min (jihe) as geometric lowest score from wj2

Group)

The group by clause is used to group data. First, you must understand that the Group is divided based on different values of the specified field. For example, a gender field has only two types of values. Therefore, if data is grouped by gender field, two arrays are generated. For example, assume that the Department field value contains five different values, five groups will be generated if they are grouped by their respective departments.

Example:Student table, grouped by department

Department of select

From student

Department of group

Use Aggregate functions with grouping

Use count (*) with group

Example:Student table, which is grouped by department and displays the number of student

Select department, count (*) as number of faculty members

From student

Department of group

Histogram of queried data

A histogram is a bar chart that shows the relative distribution of data between different entities. You can use the group by clause in a query statement to query data and format the data to generate a chart.

Example:From the student table, query a histogram showing the number of students in each department.

Select all departments,

Replicate ('=', count (*) * 3) as Count comparison chart

From student

Department of group

Sort group results

Example:In the student table, count the number of students in each department and sort the students in descending order.

Select department, count (*) as student count
From student
Department of group
Order by count (*) DESC

Reverse query results

Sometimes, even if the data obtained after the query statement is executed is correct, it is inconvenient for people to view the data.

Example:Query the student table for the number of boys and girls in each department.

Select department, gender, count (*) as student count
From student
Department of group by, Gender
Order by count (*) DESC

Although the preceding query results show statistics, they are not the statistical style that people are used. What is the statistical style that people are used?

Example:Habits in the previous example

Department of select,

Count (Case

When gender = 'male' then 1

Else null

End) as boys,

Count (Case

When gender = 'female' then 1

Else null

End) as number of girls

From student

Department of group

Use the having clause to set grouping query Conditions

Sometimes, people only want to view the statistics of the desired group, rather than the statistics of all groups. For example, you only want to view the total number of software engineering and computer hardware. In this case, you need to filter out other systems.

Example:In the student table, count the number of students in the Software Engineering Department and computer hardware department, and sort by students

Select department, count (*) as student count

From student

Department of group

Department of having ('Software engine', 'computer hardware ')

Order by count (*)

Example:In the score table, the information of students whose total score is greater than 450

Select student ID, sum (test score) as total test score

From score

Group by student ID

Having sum (exam score)> = 450

Order by total score DESC

Chapter 2

Multi-table join query

Why data is stored in multiple different tables

Paradigm

Connection Query

Advanced connection query combo Query

Multi-table join query is one of the most powerful functions of SQL. It dynamically connects tables during query execution and then queries data from them. This chapter introduces the multi-table join query and introduces the usage of the combined query.

Paradigm

First paradigm:

The first paradigm is the bottom line of relational databases. To become a relational database, it must satisfy the first paradigm. The content of the first paradigm is that the first component of the record is an inseparable basic data item.

Second paradigm:

To satisfy the second paradigm, we must first meet the first paradigm, that is, to satisfy the first paradigm is a prerequisite to satisfy the second paradigm. Secondly, the requirement of the second paradigm is that every non-primary attribute must be fully function dependent on code.

Third paradigm:

The third paradigm is based on satisfying the second paradigm and adds the requirement that non-primary attributes do not pass the code.

Connection Query

No connection rule connection

After the join rule is set, each row in the two tables is connected, that is, the result is the flute product.

Syntax:

Select * (or Field List)

From table name 1, table name 2

Connection with connection rules

The connection with a connection rule is based on the no-connection rule and the where clause is used to specify the connection method of the connection rule.

Syntax:

Select * (or Field List)

From table name 1, table name 2

Where connection rules

For example, use the following statement to correctly connect table T1 and table t2:

Select *

From T1, T2

Where T1. employee ID = T2. employee ID

Use two tables to connect to query data

For example, you can query the average and only-tested scores of all courses for students named "Zhang San ".

Select student. Student ID, student. Name, score. Course number, score. Average score, score. Test Score

From student, score

Where student. Name = 'zhang san'

And student. Student ID = score. Student ID

Order by score. DESC, score. DESC

Use table alias to simplify statements

Example:

Select st. Student ID, st. Name, C. Class Name, S. Current, S. Exam Score

From student as

Score as B

Sourse as C

Where St. Name = 'zhang san'

And st. Student ID = S. Student ID

And S. course No. = C. course No.

For example, if you want to query the "Computer Basics" course, the student's student ID, name, department, and examination score of the student whose examination score is greater than or equal to 90 are sorted in descending order by the examination score

Select st. Student ID, st. Name, st. Department, S. Test Score

From score as S,

Course as C,

Student as St

Where C. Course name = 'computer basics'

And S. Exam score> = 90

And S. course No. = C. course No.

And st. Student ID = S. Student ID

Order by S. Test Score DESC

Join query using inner join

Syntax:

Select * (or Field List)

From table name 1

Inner join table name 2

On connection rules

Inner join table name 3

On connection rules

The keyword "on" is followed by the rule for connecting tables.

Advanced Query

Self-connection Query

Example:

Select st1 .*

From student as ST1, student as st2

Where st1. Department = st2. Department

And st2. name = 'zhang san'

Or:

Select *

From student as ST1, student as st2

Where st2. name = 'zhang san'

And st1. Department = st2. Department

External Connection Query

Left Outer Join:

Example:

Select *

From student left Outer Join student2 on student. Name = student. Name

Outer right connection:

Example:

Select *

From student right Outer Join student2 on student. Name = student. Name

All external connections:

Example:

Select *

From student full outer join student2 on student. Name = student. Name

Left Outer Join

Select * from student left Outer Join student2 on student. Name = student2. name

Outer right connection

Select * from student right Outer Join student2 on student. Name = student2. name

All external connections

Select * from student full outer join student2 on student. Name = student2. name

Combined Query (Note: because the field name list of the Combined Query Result set depends on the field name list of the first select clause, you should pay attention to this when using order)

Union keyword for Combined Query

Syntax:

Select Statement 1

Union

Select Statement 2

Union

Select Statement 3

For example, from the student table, check the Department Information of the student whose source is "Beijing", "Jiangsu", or "Inner Mongolia.

Department of select

From student

Where source = 'guangdong province'

Union

Department of select

From student

Where source = 'jiangsu province'

Union

Department of select

From student

Where source = 'inner Mongolia'

Use Union to get complex statistics.

Example:

Select student ID, course number, test score

From student

Union

Select student ID, 'total score ', sum (exam score)

From student

Group by student ID

Select student ID, 'total score ', AVG (exam score)

From student

Group by student ID

Chapter 2

Subquery

Returns the subquery of a single value.

Returns a subquery of a column value.

Related subqueries

The SELECT statement embedded in another seelct statement is called a subquery. Currently, almost all the work that can be done by subqueries can be done through table join. In the past, because of the inner join operation efficiency
Therefore, subqueries are widely used because external connections are unavailable. However, thanks to the optimization of DBMS over the past few years, the efficiency of internal connections is significantly higher than that of subqueries, and external connections are also
Developed. Therefore, people begin to discard the sub-query statements that are hard to understand, instead of connecting to the query statements that are relatively easy to understand.

In most cases, table join queries are better than subqueries. However, in a specific environment, the efficiency of subqueries may be better than that of table join queries. In order to be able to read and understand the SQL statements written in early years, this book adds the content of subqueries.

Use a subquery that returns a single value

For example, query the scores of all the "Psychology" students and sort the scores in descending order.

Select student ID, Exam Score

From score

Where course No. = (select course No. From course where Course name = 'psychology ')

Order by test score DESC

Use of subqueries and Aggregate functions

For example, query all the information of the student with the minimum birth date.

Select *

From student

Where birthdate = (select Min (birthdate) from student)

For example, you can query the student ID, average score, and average score of all the students whose "Psychology" scores are greater than their scores.

Select student ID, average score, Exam Score

From score

Where course number = (select course number from course where Course name = 'psychology ') and test score> (select
AVG (exam score) from Score where course No. = (select course No. From course where Course name = 'psychology '))

Subquery using in

Example: In

Select Course No.

From score

Where course number in (select course number from course where type = 'compulsory ')

Related subqueries (it is an old method of using SQL, the query efficiency is very low, not recommended. As long as you understand)

Chapter 3 View

View Basics

View Creation

View Deletion

This chapter describes another concept in SQL-view. A view often appears in database applications. Its primary application is to simplify complex query statements. View is a noun translated from the English word view. In fact, view also has the meaning of "View". I think that sometimes the word is translated into "view mode", which may be more appropriate.

View Basics

For example, define a view vw1 and put a query statement into the view.

Create view vw1

As

Select * from student

When using a view:

Select * From vw1

Delete View:

Drop view vw1

View creation Syntax:

Create view name

As

Database Operation statement

Example:

Create view view_student_all

As

Select st1. *, st2 .*

From student as ST1 join student2 as st2 on st1. name = st2. name

Improve data security using views

1. Hide column data

For example, create a view vw_student1 that can only view the student ID name and Gender columns.

Create voiw vw_student1

As

Select student ID, name, gender

From student

2. Hide row data

For example, create one to view only the student information analysis view of the computer system vw_student2

Create view vw_student2

As

Select *

From student

Where Department = 'computer Department'

View to get summary data

For example, create a view vw_student3 to display the number of students in each department.

Create view vw_student3

As

Select department, count (*) as student count

From student

Department of group

Chapter 2

Insert data

Insert data directly into the table

Insert data through views

Inserting data into a data table is also one of the most basic functions of the SQL language. There are multiple ways to insert data, and certain rules need to be followed. The SQL statement used to insert data is insert. This chapter describes in detail the usage of insert and the rules that need to be paid attention to during use.

Insert data directly into the table

Insert into table name or view name

Values (value of Field 1, value of Field 2 ...)

For example, insert a data record.

Insert into student (name, gender, total score, average score, address, Department)

Values ('Big taobao', 'male', 'changchun, China', 'computer software engine ')

Insert query results into a table

Syntax:

Insert into Table Name (field table)

Select statement

For example, store the data queried in the student table to student2.

Insert into student2

Select * from student where name = 'Big Biao'

Differences between insert select and select

Insert select inserts the content queried in the table into another table

Select into restores the content queried in the table as a new table.

Insert data through views

Example: Create a view first

Create view vw_computer

As

Select *

From student

Where Department = 'computer Department'

Run the following statement after creating the View:

Select *

From vw_computer

Query the data of a department that belongs to a computer department.

Insert again

Insert into vw_computer

Values ('20140901', 'jiang 19', 'female ', '2017-05-09', 'shandong province ', null, null, 'computer system ')

With check Option

The last sentence with check option is to ensure that the inserted data is in the department = computer system, so that data security is possible, because the view operates only the student information of the computer system.