2023学年完整公开课版简单查询

主讲教师：魏巍巍北京信息职业技术学院数据库技术与应用简单查询创建采集任务01简单查询01目录CONTENTS简单查询01Copyright©2017,Oracleand/oritsaffiliates.Allrightsreserved.单表查询语法SELECT字段1,字段2...FROM表名

WHERE条件

GROUPBYfieldHAVING筛选

ORDERBYfieldLIMIT限制条数简单查询Copyright©2017,Oracleand/oritsaffiliates.Allrightsreserved.关键字的执行优先级找到表:from拿着where指定的约束条件，去文件/表中取出一条条记录将取出的一条条记录进行分组groupby，如果没有groupby，则整体作为一组将分组的结果进行having过滤执行select去重distinct将结果按条件排序：orderby限制结果的显示条数limit示例数据库下载地址：/datacharmer/test_db简单查询Copyright©2017,Oracleand/oritsaffiliates.Allrightsreserved.查询指定字段selectemp_no,first_name,last_name,genderfromemployees;简单查询Copyright©2017,Oracleand/oritsaffiliates.Allrightsreserved.单条件查询多条件查询SELECTfirst_nameFROMemployeesWHEREgender='M';SELECTemp_no,first_nameFROMemployeesWHEREgender='M'ANDemp_no<10050;简单查询Copyright©2017,Oracleand/oritsaffiliates.Allrightsreserved.关键字BETWEENAND关键字ISNULL(判断某个字段是否为NULL不能用等号，需要用IS)SELECTfirst_name,birth_dateFROMemployeesWHEREbirth_dateBETWEEN'1953-09-02'AND'1955-01-01';SELECT*FROMemployeesWHEREgenderISNULL;简单查询Copyright©2017,Oracleand/oritsaffiliates.Allrightsreserved.关键字IN集合查询分组查询SELECT*FROMemployeesWHEREemp_noin(10001,10002,10003,10004);SELECT*,count(emp_no)FROMemployeesgroupbyfirst_name;简单查询Copyright©2017,Oracleand/oritsaffiliates.Allrightsreserved.HAVING过滤SELECT*,count(emp_no)cFROMemployeesgroupbyfirst_namehavingc<200;简单查询Copyright©2017,Oracleand/oritsaffiliates.Allrightsreserved.查询排序:ORDERBYSELECT*FROMemployeesorderbybirth_dateasc;SELECT*FROMemployeesorderbybirth_dateasc,emp_nodesc;简单查询Copyright©2017,Oracleand/oritsaffiliates.Allrightsreserv