4. DQL query data (most important)

4.1,DQL

Data Query Language

• All query operations use it to SELECT
• It can do simple query and complex query~
• The core language and the most important statement in the database
• Most frequently used statements

SELECT statement syntax

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
    [HIGH_PRIORITY]
    [STRAIGHT_JOIN]
    [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
    [SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr] ...
    [into_option]
    [FROM table_references
      [PARTITION partition_list]]
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}, ... [WITH ROLLUP]]
    [HAVING where_condition]
    [WINDOW window_name AS (window_spec)
        [, window_name AS (window_spec)] ...]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [into_option]
    [FOR {UPDATE | SHARE}
        [OF tbl_name [, tbl_name] ...]
        [NOWAIT | SKIP LOCKED]
      | LOCK IN SHARE MODE]
    [into_option]

Note: [] brackets represent optional and {} brackets represent mandatory

SELECT [ALL | DISTINCT]
 { * | table.* | [table.field1[ AS alias1][, table.field2[ AS alias2]][,...]]}
 FROM table_name1[ AS table_alias]
	[LEFT | RIGHT | INNER JOIN table_name2] -- Joint query
	[WHERE ...] -- Specify the conditions to be met for the results
	[GROUP BY ...] -- Specify which fields the results are grouped by
	[HAVING ...] -- Secondary conditions that must be met to filter grouped records
	[ORDER BY ...] -- Specifies that the query records are sorted by one or more criteria
	[LIMIT {[ OFFSET,]ROW_COUNT | row_countOFFSET OFFSET}]; -- Specify which records to query from

4.2. Specify query fields

-- Query all students 
-- SELECT field FROM surface
SELECT  * FROM student
-- Query specified fields
SELECT  `StudentNo`,`StudentName` FROM student

-- Alias, give the result a name AS((field or table)
SELECT  `StudentNo` AS Student number,`StudentName` AS Student name FROM student AS S

-- function Concat (a,b)
SELECT CONCAT('full name:',StudentName) AS New name FROM student

Syntax: SELECT field FROM table

Sometimes, the names listed are not so well known by name, so we call them AS as

Table name / field name alias

De duplication distinc t

Function: remove the duplicate data in the result of SELECT query and display only one

-- Find out which students have taken the exam and their grades
SELECT * FROM result -- Query all test scores
SELECT `StudentNo` FROM result -- Check which students took the exam
SELECT DISTINCT SELECT `StudentNo` FROM result -- Duplicate data found, de duplication

Database columns (expressions)

SELECT VERSION() -- Query System Version (function)
SELECT 100 * 3 -1 AS Calculation results -- Used to evaluate (an expression)
SELECT @@auto_increment_increment -- Query self increasing step size (variable)

-- Student examination results + 1 Sub view
SELECT `StudentNo`,`StudentResult` + 1 AS 'After raising points' FROM result

Expressions in the database: text values, columns, functions, calculation expressions, system variables.....

select `expression` from `surface`

4.3 where conditional clause

Function: retrieve qualified values in data

Search criteria: composed of one or more expressions! Result Boolean

Logical operator

Try to use English letters

operator	grammar	describe
and &&	a and b a && b	Logic and, double truth is true
or
not !	not a ! a	Logical negation

-- The query test score is 95~100 Between points
SELECT StudentNo,StudentResult FROM result
WHERE StudentResult >= 95 AND StudentResult <= 100

-- and && 
SELECT StudentNo,StudentResult FROM result
WHERE StudentResult >= 95 && StudentResult <= 100

-- Fuzzy query (interval)
SELECT StudentNo,StudentResult FROM result
WHERE StudentResult BETWEEN 95 AND 100

-- Grades of students other than 1000 student numbers
SELECT StudentNo,StudentResult FROM result
WHERE StudentResult != 1000

-- != not
SELECT StudentNo,StudentResult FROM result
WHERE NOT StudentResult = 1000

Fuzzy queries: comparison operators

operator	grammar	describe
IS NULL	a is null	If the operator is NULL, the result is true
IS NOT NULL	a is not null	If the operator is not NULL, the result is true
BETWEEN	a between b and c	If a is between b and d, the result is true
LIKE	a like b	SQL matches. If a matches b, the result is true
IN	a in (a1,a2,a3,...)	Suppose a is in it and the result is true

-- ========= LIKE ======
-- Inquire about students surnamed Liu
-- like combination %(Represents 0 to any character) _(One character)
SELECT StudentNo,StudentName FROM student
WHERE StudentName LIKE 'Liu%'

-- Query the students surnamed Liu. There is only one word after their name(Two words__)
SELECT StudentNo,StudentName FROM student
WHERE StudentName LIKE 'Liu_'

-- Query the students with Jiazi in the middle of their names %Jia%

-- ========= IN(Specific one or more values) ======
-- Inquire about student 100110021003
SELECT StudentNo,StudentName FROM student
WHERE StudentName IN (1001,1002,1003)

-- Query students in Beijing `Address` IN ('Anhui')

-- ========= null  not null ======
-- Query students whose address is empty null ''
SELECT StudentNo,StudentName FROM student
WHERE Address = '' OR Address IS NULL

-- Query students with date of birth is not null
SELECT StudentNo,StudentName FROM student
WHERE BornDate IS NOT NULL

4.4. Associated table query

JoinON

-- Query the students who took the exam (student number, name, subject number, score)
SELECT * FROM student
SELECT * FROM result

/* thinking
1. Analyze the requirements and analyze which tables the query fields come from (connection query)
2. Determine which connection query to use? 7 kinds
 Determine the intersection (which data is the same in the two tables)
Judgment condition: StudentNo in student table = StudentNo in grade table
*/

-- Inner join
SELECT s.StudentNo,StudentName,SubjectNo,StudentResult
FROM student AS s
INNER JOIN result AS r
WHERE s.StudentNo = r.StudentNo
 
-- Right Join
SELECT s.StudentNo,StudentName,SubjectNo,StudentResult
FROM student s
RIGHT JOIN result r
ON s.StudentNo = r.StudentNo

-- Left Join
SELECT s.StudentNo,StudentName,SubjectNo,StudentResult
FROM student s
LEFT JOIN result r
ON s.StudentNo = r.StudentNo

-- join (Connected tables) on (Conditions of judgment) join query
-- where Equivalent query
-- Query the absent students
SELECT s.StudentNo,StudentName,SubjectNo,StudentResult
FROM student s
LEFT JOIN result r
ON s.StudentNo = r.StudentNo
WHERE StudentResult IS NULL

operation	describe
Inner join	If there is at least one match in the table, the row is returned
left join	All values will be returned from the left table, even if there is no match in the right table
right join	All values will be returned from the right, even if there is no match in the left table

-- Thinking questions (query the information of students participating in the exam: student number, student name, subject name, score)
/* thinking
1. Analyze the requirements, and analyze which tables student, result and subject the fields of the query come from (connection query)
2. Determine which connection query to use? Seven kinds
 Determine the intersection (which data is the same in the two tables)
Judgment condition: StudentNo in student table = StudentNo in grade table
*/
SELECT s.StudentNo,StudentName,SubjectName,StudentResult
FROM student s
RIGHT JOIN result r
ON r.StudentNo = s.StudentNo
INNER JOIN `subject` sub
ON r.subjectNo = sub.SubjectNo

-- What data do I want to query select ...
-- From which tables from `surface` XXX join `Connected tables` on Cross condition
-- Suppose there is a multi table query. Take your time. First query two tables and then add them slowly

Self connection

Core: one table can be split into two identical tables

Parent class

categoryid	categoryName
2	information technology
3	software development
5	Art design

Subclass

pid	categoryid	categoryName
3	4	database
2	8	Office information
3	6	web development
5	7	Art design

Operation: query the subclass relationship corresponding to the parent class

Parent class	Subclass
information technology	Office information
software development	database
software development	web development
Art design	ps Technology

CREATE TABLE `category` (
  `catetoryid` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'theme id',
  `pid` INT NOT NULL COMMENT 'father id',
  `categoryName` VARCHAR(50) NOT NULL COMMENT 'Subject name',
  PRIMARY KEY (`catetoryid`)
) ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8

INSERT INTO `category` (`catetoryid`,`pid`,`categoryName`)
VALUES
('2','1','information technology'),
('3','1','software development'),
('4','3','database'),
('5','1','Art design'),
('6','3','web development'),
('7','5','ps technology'),
('8','2','Office information');

-- Query parent-child information,Think of a table as like as two peas of two models.
SELECT a.`categoryName` AS 'Parent column',b.`categoryName` AS 'Sub column'
FROM `category` AS a,`category` AS b
WHERE a.`catetoryid`  = b.`pid`

-- Query the grade (student number) and student name of the student,Grade name)
SELECT studentNo,studentName,gradeName
FROM student s
INNER JOIN grade g
ON s.gradeId = g.graId

4.5 paging and sorting

paging

-- Why pagination?
-- Ease the pressure on the database, give people a better experience, waterfall flow
-- Pagination, only five pieces of data are displayed on each page
-- Syntax: limit `Starting value`,`Page size`
-- Web application: current, total pages, page size
-- LIMIT 0,5 1~5
SELECT s.StudentNo,StudentName,SubjectName,StudentResult
FROM student s
INNER JOIN result r
ON s.StudentNo = r.StudentNo
INNER JOIN `subject` sub
ON r.SubjectNo = sub.SubjectNo
WHERE subjectName = 'database structure -1'
ORDER BY StudentResult DESC
LIMIT 1,5

-- [pageSize: [page size]
-- [(n-1)* pageSIze: Starting value]
-- [n: [current page]
-- [Total data / Page size = Total pages]

Syntax: limit starting value, page size

-- reflection:
-- query Java Information of students whose scores in the first academic year are in the top ten and whose scores are greater than 80 (student number, name, course name, score)
SELECT StudentNO,StudentName,SubjectName,StudentResult
FROM student s 
INNER JOIN result r
ON s.StudentNo =r.StudentNo
INNER JOIN `subject` sub
ON sub.SubjectNo = r.SubjectNo
WHERE SubejectName = 'Java First academic year' AND StudentResult >= 80
ORDER BY StudentResult DESC
LIMIT 0,10

sort

-- Sorting: ascending ASC Descending order DESC
-- ORDER BY Which field is used to sort and how
-- The query results are sorted in descending order according to their grades
SELECT s.StudentNo,StudentName,SubjectName,StudentResult
FROM student s
INNER JOIN result r
ON s.StudentNo = r.StudentNo
INNER JOIN `subject` sub
ON r.SubjectNo = sub.SubjectNo
WHERE subjectName = 'database structure -1'
ORDER BY StudentResult DESC

4.6 sub query

where (this value is calculated)

Essence: nest a subquery statement in the where statement

-- 1,Query database structure-1 All the test results (student number, subject number, score) are arranged in descending order
-- Method 1: use connection query
SELECT StudentNo,SubjectName,StudentResult
FROM result r
INNER JOIN `subject` sub
ON r.SubjectNo = sub.SubjectNo
WHERE SubjectName = 'database structure -1'
ORDER BY StudentResult DESC

-- Method 2: use sub query (from inside to outside)
SELECT StudentNo,SubjectName,StudentResult
FROM result r
WHERE SubjectNo = (
	SELECT SubjectNo FROM `subject` 
	WHERE SubjectName = 'database structure -1'
)
ORDER BY StudentResult DESC

-- Query all database structures-1 Student ID
SELECT SubjectNo FROM `subject` WHERE SubjectName = 'database structure -1'

4.7 grouping and filtering

-- Query the average score of different courses, the highest score, and the average score is greater than 80
-- Core: (grouped according to different courses)
SELECT SubjectName,AVG(StudentResult) AS average,MAX(StudentResult) AS Highest score,MIN(StudentResult) AS Lowest score
FROM result r
INNER JOIN `subject` sub
ON r.SubejectNo = sub.SubjectNo
GROUP BY r.SubjectNo -- By what field
HAVING average>80