DQL query data (emphasis)

1. DQL query data (emphasis)

DQL (Data Query Language: Data Query Language)

  • All query operations use it SELECT
  • It can do simple query and complex query
  • The core language, the most important statement in the database
  • Most frequently used sentence
-- 查询全部
SELECT * FROM 表名;

-- 查询指定字段
SELECT `字段1`,`字段2`,... FROM 表名;

-- 别名,给字段名起别名 AS,也可以给表起别名
SELECT `字段名1` AS 别名1,`字段名2` AS 别名2,... FROM 表名 AS 表别名;

-- 函数 Concat(a,b)
SELECT CONCAT(`别名`,字段名) AS 别名 FROM 表名


Sometimes, the column names are not so obvious. We play the role of alias AS field name AS alias
De-
duplicate DISTINCT : remove the duplicate data in the results of the SELECT query, and only display one
-- 查询一下哪些同学参加了考试,成绩
SELECT * FROM result  -- 查询全部的考试成绩
Insert picture description here
SELECT `StudentNO` FROM result --查询有哪些同学参加了考试
Insert picture description here


Duplicate data is found, de-duplicate useDISTINCTKey words

SELECT DISTINCT `studentNo` FROM result -- 发现重复数据,去重
Insert picture description here


View MySQL version

SELECT version();
Insert picture description here
Database column (expression)
SELECT 100*3-1 AS 计算结果 --用来计算(表达式)
SELECT @@auto_increment_increment -- 查询自增的步长(变量)

-- 学员考试成绩+1分查询
SELECT `StudentNo`,`StudentResult`+1 AS `提分后` FROM result
Insert picture description here

Expressions in the database: text values, columns, null, functions, calculation expressions, system variables...
selectexpression from table

1.1, where condition clause

Role: Retrieving dataMeet the criteriaValue of
The search condition consists of one or more expressions! The result is a boolean

Logical Operator
| Operator| Syntax| Description|
|–|--|–|
| and && | a and b / a&&b | Logical AND, both are true, and the result is true |
| or || | a or b / a || b | Logical OR, one of which is true, the result is true |
| Not! | not a / !a | Logical negation, true to false, false to true! |
Try to use English letters
-- 查询考试成绩在95~100分之间
SELECT studentNo,`StudentResult` FROM result WHERE studentResult>=95 AND StudentResult<=100;
-- AND &&
SELECT studentNo,`StudentResult` FROM result WHERE studentResult>=95 && StudentResult<=100;
-- 模糊查询(区间范围 BETWEEN ... AND ... )
SELECT studentNo,`StudentResult` FROM result WHERE studentResult BETWEEN 95 AND 100;
Insert picture description here


Query the results except for student number 1000

-- !=
SELECT studentNo,`StudentResult` FROM result WHERE studentNo != 1000;
-- NOT
SELECT studentNo,`StudentResult` FROM result WHERE NOT studentNo = 1000;
Insert picture description here
Fuzzy query: comparison operator
Operatorgrammardescription
IS NULLa IS nullIf the operator is NULL, the result is true
IS NOT NULLa IS NOT nullIf the operator is not null, the result is true
BETWEEN...AND...a BETWEEN b AND cIf a is between b and c, the result is true
LIKEa LIKE BSQL match, if a matches b, the result is true
INa IN (a1,a2,a3…)Suppose that a is in a1, or a2...and the result is true

Find the student surnamed Liu

-- LIKE关键词,%(代表0到任意个字符) _(一个字符)
SELECT `StudentNo`,`StudentName` FROM `student` WHERE `StudentName` LIKE '刘%';
Insert picture description here


Query the student whose surname is Liu, and the student whose name is two characters

SELECT `StudentNo`,`StudentName` FROM `student` WHERE `StudentName` LIKE '刘_';
Insert picture description here


Query the student whose surname is Liu, and the student whose name is three characters

SELECT `StudentNO`,`StudentName` FROM `student` WHERE `StudentName` LIKE '刘__';
Insert picture description here


Search for students whose name contains "嘉"

SELECT `StudentNo`,`StudentName` FROM `student` WHERE `StudentName` LIKE '&嘉&';
Insert picture description here


==IN (specific one or more values)==Keyword
query No. 1001, 1003, 1003 student

SELECT `StudentNo`,`StudentName` FROM `student` WHERE `StudentNo` IN (1001,1002,1003);
Insert picture description here


Inquire about students in Anhui and students in Luoyang, Henan

SELECT `StudentNo`,`StudentName` FROM `student` WHERE `Address` IN ('安徽','河南洛阳');
```![在这里插入图片描述](https://img-blog.csdnimg.cn/20210607111324993.png)
> null、not null

查询地址为空的学生null/''
```sql
SELECT `StudentNO`,`StudentName` FROM `student` WHERE `Address`='' OR `Address` IS NULL;
Insert picture description here


Query students with date of birth, not empty

SELECT `StudentNo`,`StudentName` FROM `student` WHERE `BornDate` IS NOT NULL;

Query students who do not have a date of birth (empty)

SELECT `StudentNo`,`StudentName` FROM `student` WHERE `BronDate` IS NULL;
Insert picture description here