Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to use DQL to query data. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.

Use DQL to query data

DQL language

DQL (Data Query Language data query language)

Query database data, such as SELECT statements

Simple single-table queries or complex and nested queries with multiple tables

It is the core and most important statement in database language.

Statements that are used most frequently

SELECT syntax

SELECT [ALL | DISTINCT] {* | table.* | [table.field1 [as alias1] [, table.field2 [as alias2]] [ ]} FROM table_name [as table_alias] [left | right | inner join table_name2]-- Joint query [WHERE...]-- specify the conditions to be met by the results [GROUP BY...]-- specify the fields by which the results are grouped [HAVING]-- the secondary conditions that must be met by filtering grouped records [ORDER BY.] -- specifies that query records are sorted by one or more criteria [LIMIT {[offset] ] row_count | row_countOFFSET offset}] -- specify which record to query from

Note: [] parentheses represent optional, {} parentheses represent required

Specified query field-query the results of all data columns in the query table, using the symbol * * "\ *, but inefficient and not recommended.-- query all student information SELECT * FROM student;-- query specified column (student number, name) SELECT studentno,studentname FROM student

AS clause as an alias

Function:

You can give the data column a new alias

You can give the table a new alias.

The calculated or summarized results can be replaced by another new name.

-- here is an alias for the column (of course, the as keyword can be omitted) the SELECT studentno AS student number, the studentname AS name FROM student;-- can also use as to give the table an alias SELECT studentno AS student number, and the studentname AS name FROM student AS SELECT CONCAT-- use as to give the query result a new name-- the CONCAT () function splices the string SELECT CONCAT ('name:', studentname) AS new name FROM student

The use of DISTINCT keyword

Function: remove the duplicate records in the record results returned by the SELECT query (return the same values for all columns), and return only one

-- # check which students took the exam (student number) remove duplicates SELECT * FROM result;-check test scores SELECT studentno FROM result;-see which students took the exam SELECT DISTINCT studentno FROM result;-understand: DISTINCT removes duplicates (default is ALL)

Columns that use expressions

Expressions in a database: generally composed of text values, column values, NULL, functions and operators, etc.

Application scenarios:

The SELECT statement returns the result column using the

Used in ORDER BY, HAVING and other clauses in SELECT statements

Use expressions in where conditional statements in DML statements

-- the expression SELECT @ @ auto_increment_increment; can be used in the selcet query-- query increment step SELECT VERSION ();-- query version number SELECT 100room3-1 AS calculation result;-- expression-- students' test scores are collectively scored to view the SELECT studentno,StudentResult+1 AS 'after score' FROM result.

Avoid the return result of SQL containing'. ',' * 'and parentheses interfere with development language programs.

Where conditional statement

Function: used to retrieve eligible records in the data table

The search condition can consist of one or more logical expressions, and the result is generally true or false.

Logical operator

test

-- queries that meet the criteria (where) SELECT Studentno,StudentResult FROM result -- query SELECT Studentno,StudentResultFROM resultWHERE StudentResult > = 95 AND StudentResult=95 & & StudentResult=80-- method 2: use the link query + subquery-- the student number and name of the student whose score is not less than 80, SELECT r. StudentNotem FROM student sINNER JOIN result r ON s.`StudentNo` = r.`StudentNo``WWERE StudentResult > = 80-based on the above SQL, add the demand: the course is advanced mathematics-2SELECT r.studentno Studentname FROM student sINNER JOIN result r ON s.`StudentNo` = r.`StudentNo`where StudentResult > = 80 AND subjectno= (SELECT subjectno FROM `Studt` WHERE subjectname = 'Advanced Mathematics-2')-method 3: use subqueries-write simple sql statements step by step, and then nest them into SELECT studentno Studentname FROM student WHERE studentno IN (SELECT studentno FROM result WHERE StudentResult > = 80 AND subjectno= (SELECT subjectno FROM `mathematics-2') / * exercise topic: check the grade information (student number, name, score) of the top five students in C language-1 using sub-query, query Guo Jing's grade name * / about "how to use DQL to query data", this is the end of the article. Hope that the above content can be helpful to you, so that you can learn more knowledge, if you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.