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

目录

• 查询
• 语法
• • 1. 查询表中所有的数据行和列，采用“*”符号
  • 2. 查询表中指定列的数据。
  • 3. 在查询中使用别名，使用“AS”关键字。
  • 4. 在查询中使用常量列：如果需要将一些常量的默认信息添加到输出结果中，以方便统计或计算。可以使用常量列
  • 5. DISTINCT关键字的使用：去掉SELECT查询返回的记录结果中重复的记录（所有返回列的值都相同），只返回一条
  • WHERE条件
  • NULL空值条件查询
  • • 语法
    • BETWEEN AND范围查询
    • • 语法
      • LIKE模糊查询
      • 使用IN进行范围查询
      • • 语法
        • 排序
        • 分页
        • 分组查询
        • 子查询
        • • 语法
          • 案例
          • IN子查询
          • • 语法
            • SQL示例
            • 综合案例
            • EXISTS 子查询
            • • 语法
              • SQL示例

                查询

                

                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 ];

                  #查询学生表中所包含的年级ID
                   SELECT   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范围查询：根据一个范围值来检索
                        • 等同于 >= 和 = 110 AND classhour =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  
                                子查询
                                 
                                子查询是一个嵌套在 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 RESULT
                                    		WHERE 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 RESULT
                                    		WHERE 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 result
                                       WHERE studentResult>80
                                       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'))
                                      )
                                      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 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 studentResult>100
                                      )