【MySQL】数据查询——DQL基本数据库查询

查询

1. 查询产生一个虚拟表。
2. 看到的是表形式显示的结果，但结果并不真正存储。
3. 每次执行查询只是从数据表中提取数据，并按照表格的形式显示出来。

语法

SELECT   [ALL | DISTINCT] 
{  * |  table.* | [ table.field1 [ as  alias1] [, table.field2 [as  alias2]][, …]] }
FROM  table_name  [ as  table_ alias  ][ left|out|inner  join  table_name2 ]    #多表连接查询[ WHERE  … ]   	#指定结果需满足的条件[ GROUP BY …]	#指定结果按照哪几个字段来分组[ HAVING …]	#过滤分组的记录必须满足的次要条件[ ORDER BY… ]	#指定查询记录按一个或者多个条件排序[ LIMIT  {   [ offset,] row_count    |   row_count OFFSET offset   }] ;  #分页查询

1. 括号代表可选的；
2. { } 括号代表必须的；
3. #MySQL语句中的注释符，也可以用 /**/

1. 查询表中所有的数据行和列，采用“*”符号

SELECT * FROM 表名;

SELECT   *   FROM student;

2. 查询表中指定列的数据。

SELECT 字段名1,字段名2,…字段名n FROM 表名;

SELECT  studentno, studentname, phone FROM student;

3. 在查询中使用别名，使用“AS”关键字。

• 可给数据列取一个新别名
• 可给表取一个新别名
• 可把经计算或总结的结果用另外一个新名称来代替

SELECT field1 [ AS alias1] [,field2 [AS alias2]] […,fieldn [AS aliasn]]
FROM table_name [ AS table_ alias ];

SELECT   studentno   AS “学号”   FROM  student;
SELECT   s.studentno   FROM   student AS  s;
SELECT CONCAT("S",studentno) FROM student;

4. 在查询中使用常量列：如果需要将一些常量的默认信息添加到输出结果中，以方便统计或计算。可以使用常量列

SELECT  studentno AS “学号”, studentname AS “姓名”,phone AS “手机号”,address AS “家庭住址”,‘郑州’ AS “市区”FROM student;

5. DISTINCT关键字的使用：去掉SELECT查询返回的记录结果中重复的记录（所有返回列的值都相同），只返回一条

SELECT DISTINCT field1 [ AS alias1] [,field2 [AS alias2]] […,fieldn [AS aliasn]] FROM table_name [ AS table_ alias ];

#查询学生表中所包含的年级IDSELECT   DISTINCT  gradeid  FROM  student;

WHERE条件

• WHERE条件：用于检索数据表中符合条件的记录
• 搜索条件可由一个或多个逻辑表达式组成，结果一般为真或假
• 搜索条件的组成

  • 逻辑操作符
    

  • 比较操作符
    数值数据类型的记录之间才能进行算术运算
    相同数据类型的数据之间才能进行比较

NULL空值条件查询

• NULL代表“无值”
• 区别于零值0和空符串“”
• 只能出现在定义允许为NULL的字段
• 须使用 IS NULL 或 IS NOT NULL 比较操作符去比较

语法

SELECT 字段1，字段2 ,…FROM 表名 WHERE 字段x IS NULL

BETWEEN AND范围查询

• BETWEEN AND范围查询：根据一个范围值来检索
• 等同于 >= 和 <= 联合使用

语法

SELECT 字段1，字段2 ,…FROM 表名 WHERE 字段x BETWEEN 值1 AND 值2

#查询课程表中课时在110和120之间的所有记录SELECT * FROM subject WHERE classhour BETWEEN  110 AND 120;
#等同于:SELECT * FROM subject WHERE classhour >= 110 AND classhour <=120;

LIKE模糊查询

• 在WHERE子句中，使用LIKE关键字进行模糊查询
• 与“%”一起使用，表示匹配0或任意多个字符
• 与“_”一起使用，表示匹配单个字符

#查询包含“数学”的所有课程
SELECT   *  FROM subject WHERE SubjectName  LIKE  "%数学%";
#查询所有姓名为“李**”三个字的学生信息
SELECT  StudentNo,StudentName FROM student  
WHERE StudentName LIKE "李__";

使用IN进行范围查询

• 在WHERE子句中使用IN进行范围查询
• 查询的字段x的值，至少与括号中的一个值相同
• 多个值之间用英文逗号隔开

语法

SELECT 字段1，字段2 ,…FROM 表名 WHERE 字段x IN ( 值1,值2,值3…值n)

SELECT  *  FROM  subject  where    ClassHour = 100  OR ClassHour =110 OR ClassHour  = 120;  #普通处理方式
SELECT  *  FROM  subject  where ClassHour  IN ( 100, 110,120 );
#使用IN进行查询方式，更为简洁，效率更高

排序

• ORDER BY子句：实现按一定顺序显示查询结果。
• 排序可以是升序（ASC）或者降序（DESC）,如果不指定ASC或DESC，结果集默认按ASC升序排序

#把成绩都降低10%后加5分，再查询及格成绩，并按照成绩从高到低排序。
SELECT studentno AS 学生编号,(studentresult*0.9+5 ) AS 综合成绩
FROM `result`
WHERE (`studentresult`*0.9+5) >=60
ORDER BY studentresult DESC;#把成绩都降低10%后加5分，再查询及格成绩，并按照成绩从高到低排序,如果成绩
#相同，再按照课程编号进行排序。
SELECT studentno AS 学生编号,(studentresult*0.9+5 ) AS 综合成绩
FROM `result`
WHERE (`studentresult`*0.9+5) >=60
ORDER BY studentresult DESC,subjectno;

分页

LIMIT 子句：MySQL查询语句中使用LIMIT子句限制结果集

• 应用1：限制显示的结果集的行数（小说排行榜 新闻只显示最新的5条）

#查询课程编号为1的，考试日期为2019年的考试的前5名同学的学号和成绩
SELECT studentno,studentresult 
FROM result
WHERE subjectno=1 AND YEAR(examdate)=2019
ORDER BY studentresult DESC
LIMIT 5

• 应用2：分页查询（分页显示数据）
  limit后跟连个数字时，第一个表示偏移量(索引)，第二个显示条数
  #查询学生表的学号、姓名、电话 ，每页显示2条记录
  #第一条记录的偏移量为0
  #第一页
  #偏移量 0 1

SELECT studentno,studentname,phone FROM student
LIMIT 0,2

分组查询

• [ GROUP BY …] #指定结果按照哪几个字段来分组
• [ HAVING …] #过滤分组的记录必须满足的次要条件
• 对所有的数据进行分组统计
• 分组的依据字段可以有多个，并依次分组
• 与HAVING结合使用，进行分组后的数据筛选
• GROUP BY子句经常和聚合函数结合使用完成分组统计功能

#查询每门课程及格总人数和及格学生的平均分
SELECT SUBJECTNO,COUNT(1),AVG(STUDENTRESULT) FROM RESULT 
WHERE STUDENTRESULT >= 60
GROUP BY SUBJECTNO;#查询每门课程及格总人数和及格平均分在80分以上的记录
SELECT SUBJECTNO,COUNT(1),AVG(STUDENTRESULT) FROM RESULT 
WHERE STUDENTRESULT >= 60
GROUP BY SUBJECTNO
HAVING AVG(STUDENTRESULT) > 80;#查询每门课程的平均分，并按照降序排列
SELECT SUBJECTNO,AVG(STUDENTRESULT) AVG FROM RESULT
GROUP BY SUBJECTNO
ORDER BY AVG DESC;#查询每个学生参加的所有考试的总分，并按照降序排列
SELECT STUDENTNO,SUM(STUDENTRESULT) SUM FROM RESULT
GROUP BY STUDENTNO
ORDER BY SUM DESC;#查询每个年级学生的平均年龄
SELECT GRADEID,AVG(YEAR(NOW())-YEAR(BORNDATE)) FROM STUDENT 
GROUP BY GRADEID;#查询考试不及格的学生学号和不及格的次数
SELECT STUDENTNO,COUNT(STUDENTRESULT) FROM RESULT
WHERE STUDENTRESULT < 60
GROUP BY STUDENTNO;

子查询

子查询是一个嵌套在 SELECT、INSERT、UPDATE 或 DELETE 语句或其他子查询中的查询。

语法

SELECT … FROM 表名 WHERE 字段X 比较运算符(子查询)

• 习惯上，外层查询称为父查询，圆括号中嵌入的查询称为子查询。
• 执行SQL语句时，先执行子查询，返回所有来自子查询的结果，再执行外围的父查询，返回查询的最终结果（即求解方式为由里及外）。
• 将子查询和比较运算符联合使用，必须保证子查询返回的值不能多于一个（否则会出现提示信息：错误代号1242 Subquery returns more than 1 row）。

案例

编写SQL语句，查看年龄比“李斯文”小的学生，要求显示这些学生的信息（学号、姓名、出生日期）

SELECT studentno, studentname,borndate FROM student WHERE borndate > 
(SELECT borndate FROM student WHERE studentname= '李斯文’);

IN子查询

查询课程名称为”JavaOOP”且考试分数大于60分的学生信息（学号、姓名和联系电话）。

• IN后面的子查询可以返回多条记录。
• 常用IN替换等于（＝）的子查询。

语法

SELECT … FROM 表名 WHERE 字段X IN(子查询)

SQL示例

#查询课程名称为”JavaOOP”且考试分数大于60分的学生信息（学号、姓名和联系电话）。
SELECT studentno,studentname,phone FROM student  WHERE studentno IN
(SELECT studentno FROM result WHERE studentresult>60 AND 
subjectno=(SELECT subjectno FROM `subject`WHERE subjectname='JAVAOOP'))

综合案例

#查询年级名称为"大一"开设的课程信息（课程编号、课程名称、课时数）
SELECT SUBJECTNO,SUBJECTNAME,CLASSHOUR FROM `SUBJECT`
WHERE GRADEID = (SELECT GRADEID FROM GRADE WHERE GRADENAME='大一');#查询课程名称为”高等数学-1”最近一次考试的学生信息（学号、姓名和联系电话）。
SELECT STUDENTNO,STUDENTNAME,PHONE FROM STUDENT
WHERE STUDENTNO IN (SELECT STUDENTNO FROM RESULT WHERE SUBJECTNO = (SELECT SUBJECTNO FROM `SUBJECT` WHERE SUBJECTNAME = '高等数学-1') AND EXAMDATE = (SELECT MAX(EXAMDATE) FROM RESULTWHERE SUBJECTNO = (SELECT SUBJECTNO FROM `SUBJECT` WHERE SUBJECTNAME = '高等数学-1'))
);#查询没有参加课程名称为”高等数学-1”最近一次考试的学生信息（学号、姓名和联系电话）。
SELECT STUDENTNO,STUDENTNAME,PHONE FROM STUDENT
WHERE STUDENTNO NOT IN (SELECT STUDENTNO FROM RESULT WHERE SUBJECTNO = (SELECT SUBJECTNO FROM `SUBJECT` WHERE SUBJECTNAME = '高等数学-1') AND EXAMDATE = (SELECT MAX(EXAMDATE) FROM RESULTWHERE SUBJECTNO = (SELECT SUBJECTNO FROM `SUBJECT` WHERE SUBJECTNAME = '高等数学-1'))
);

EXISTS 子查询

EXISTS 子查询：带有EXISTS的子查询不返回任何记录的数据，只返回逻辑值“True”或“False”。

• 子查询有返回结果： EXISTS子查询结果为TRUE，执行外层查询；
• 子查询无返回结果： EXISTS子查询结果为FALSE, 外层查询不执行；

语法

SELECT …… FROM 表名 WHERE EXISTS(子查询);

SQL示例

# 检查“Logic Java”课程最近一次考试成绩，如果有 80分以上的成绩，显示分数排在前5名的学员学号和分数
SELECT sujectNo FROM `subjectl` WHERE subjectName='Logic Java'
SELECT MAX(examDate) FROM result WHERE subjectNo=(SELECT subjectNo FROM`subject` WHERE subjectName='Logic Java')
SELECT studentNO,studentResult
FROM result
WHERE EXISTS(SELECT * FROM resultWHERE studentResult>80AND subjectNo=(SELECT subjectNO FROM `subject` WHERE subjectName='Logic Java')AND  examDate=(SELECT MAX(examDate) FROM result WHERE subjectNo=(SELECT subjectNO FROM `subject`WHERE subjectName='Logic Java'))
)
AND subjectNo=(SELECT subjectNO FROM `subject`WHERE subjectName='Logic Java')
AND  examDate=(SELECT MAX(examDate) FROM result WHERE subjectNo=(SELECT subjectNO FROM `subject`WHERE subjectName='Logic Java'))
ORDER BY studentResult DESC
LIMIT 5# 检查“Logic Java”课程最近一次考试成绩，如果全部未通过考试（60分及格），认为本次考试偏难，计算的该次考试平均分加5分
SELECT  AVG(studentResult)+5
FROM result
WHERE subjectNo=(SELECT subjectNo FROM `subject` WHERE subjectName='Logic Java')
AND examDate=(SELECT MAX(examDate) FROM result WHERE subjectNo=(SELECT subjectNo FROM `subject` WHERE subjectName='Logic Java'))
AND NOT EXISTS(SELECT * FROM resultWHERE subjectNo=(SELECT subjectNo FROM `subject` WHERE subjectName='Logic Java')AND examDate=(SELECT MAX(examDate) FROM result WHERE subjectNo=(SELECT subjectNo FROM `subject` WHERE subjectName='Logic Java'))AND studentResult>100
)