MySQL Study ( Two )— Use DQL Query data

01 DQL Language introduction

DQL（Data Query Language Data query language ） Can be used to query database data , Simple single table query or multi table complex query and nested query .

02 SELECT

# Query all data column results in the table , Using wildcard characters *
# Example 1： Inquire about student All the information in the table
select * from student;
# Example 2： Inquire about student The specified column in the table （ Student number , full name ）
select StudentNo,StudentName from student;

03 AS

# AS Keyword can give the data column a new alias , In the union query, it is convenient to distinguish the same column names of different tables
# Example ： Inquire about student The specified column in the table （ Student number , full name ）, And display the column name as student number , full name
select StudentNo as ' Student number ',StudentName as ' full name ' from student;

04 DISTINCT

# Use DISTINCT You can remove duplicate records from the record results returned by the query , That is, when the values of some columns returned are the same , Only one record is returned
# Example ： Inquire about studenNo And get rid of the duplicate numbers ji
select distinct StudentNo from student;

05 WHERE

# WHERE Used to retrieve eligible records in the data table
# The search criteria may consist of one or more logical expressions , The result is usually true or false
# Logical operators AND( Can be written as &&) OR( Can be written as ||) NOT( Can be written as ！)
# Example
# Check the test results in 95-100 Between
SELECT Studentno,StudentResult
FROM result
WHERE StudentResult>=95 AND StudentResult<=100;
# The student ID is 1009 The results of other students other than the students of
SELECT studentno,studentresult
FROM result
WHERE NOT studentno=1009;

06 Fuzzy query

• between and

# Query subjects 8 Achievements in 90 be assigned to 100 The student number of the students in between
select StudentNo
from result
where SubjectNo=8 and StudentResult between 90 and 100;

• like

# It needs to be used in combination with wildcards
# Inquire about the student number and name of the student surnamed Li
SELECT studentno,studentname FROM student
WHERE studentname LIKE ' Li %';
# Query the student number and name of the student whose surname is Li and whose first name is only two words
SELECT studentno,studentname FROM student
WHERE studentname LIKE ' Li _';
# Query the student number and name of the student whose surname is Li and whose first name is only three words
SELECT studentno,studentname FROM student
WHERE studentname LIKE ' Li _';
# Query the student number and name of the students whose names contain words （ There is no limit to where words appear ）
SELECT studentno,studentname FROM student
WHERE studentname LIKE '% writing %';

• in

# Inquire about address For Beijing , nanjing , Students in Luoyang, Henan Province
SELECT studentno,studentname,address FROM student
WHERE address IN (' Beijing ',' nanjing ',' Luoyang, Henan ');
# Query subjects 8 Achievements in 90 be assigned to 100 The names of the students in between （ Using nested join queries ）
select StudentName
from student
where StudentNo
in(select StudentNo from result where SubjectNo=8 and StudentResult between 90 and 100);

• null

# The student's date of birth was not filled in （ Be careful , Out-of-service =null Judge , To use is null Judge ）
SELECT studentname FROM student
WHERE BornDate IS NULL;
# Check the date of birth of the students filled in
SELECT studentname FROM student
WHERE BornDate IS NOT NULL;
# It should be noted that , An empty string '' Is not the same as NUll