数据库实验第五讲_简单查询答案

1、 1第第5讲讲 简单查询简单查询 2第第5讲讲 简单查询简单查询Department(dept_no, dept_name, location)Employee(emp_no, emp_fname,emp_lname,dept_no)Project(project_no, project_name, budget)Works_on(emp_no, project_no, job, enter_date) 35.1 SELECT语句的基本格式语句的基本格式SELECT ALL|DISTINCT column_list FROM tab_1,tab_2select * from departmen

2、tselect dept_no,dept_name,location from department例5.1 部门的编号、名称和位置例5.2部门的编号、名称和位置 45.2 where 子句子句SELECT ALL|DISTINCT column_list FROM tab_1,tab_2GROUP BY group_by_expressionHAVING search_conditionORDER BY order_expression ASC|DESCselect dept_no,dept_name from department WHERE location=DallasSELECT p

3、roject_name FROM project WHERE budget*0.5160000例5.3 查询位于Dallas的部门编号和名称例5.4 查询预算额的0.51倍大于60000的项目名称 55.2.1 逻辑运算逻辑运算AND,OR,NOTSELECT * FROM employee WHERE emp_no=25348 OR emp_fanme=Matthew AND dept_no=d1SELECT emp_no,emp_lname FROM employee WHERE NOT dept_no=d1例5.5查询在部门编号为d1的部门工作且职员编号为25348或职工名称为Matth

4、ew 的雇员信息。例5.6 查询不在部门编号为d1的部门工作的职员编号和姓名 6例5.7 获取编号既不是10102也不是9031的职员的所有列。SELECT * FROM employee WHERE emp_no NOT IN (10102 ,9031) 例5.8 获取预算在$95 000 $120 000之间的所有项目的名称和预算。SELECT project_name,budget FROM project WHERE budget BETWEEN 95000 AND 120000 例5.9 获取预算小于$95 000和大于 $120 000之间的所有项目的名称。SELECT proje

5、ct_name FROM project WHERE budget NOT BETWEEN 95000 AND 120000 5.2.2 IN和和BETWEEN操作符操作符 75.2.3 涉及涉及NULL值的查询值的查询column IS NOT NULL例5.10 获取为项目2工作的，具有未知工作的所有职员的职员编号和相应的项目编号。SELECT emp_no,project_no FROM works_on WHERE project_no=p2 AND job IS NULL SELECT emp_no,ISNULL(job,Job unknown task ) FROM works_o

6、n WHERE project_no=p2 AND job IS NULL 系统函数ISNULL允许在显示时用指定的数值来替代NULL 85.2.3 LIKE操作符操作符column NOT LIKE pattern例5.11 找出所有姓是以字母J开头的职员的名字和编号。SELECT emp_fname,emp_lname,emp_no FROM employee WHERE emp_lname LIKE j% Pattern可以是一个串、一个数据常量或表达式可以是一个串、一个数据常量或表达式模式中的特定字符模式中的特定字符-通配符的含义通配符的含义%：指定：指定0个或多个字符序列个或多个字符

7、序列-： 指定单一字符指定单一字符 9例例5.12 找出所有名中第二个字母是找出所有名中第二个字母是a的职员的名字和编号。的职员的名字和编号。SELECT emp_fname,emp_lname,emp_no FROM employee WHERE emp_fname LIKE _a% 例例5.13 获取所在地名从获取所在地名从C到到F的字母打头的所有部门的详细资料。的字母打头的所有部门的详细资料。SELECT * FROM DEPARTMENT WHERE LOCATION LIKE C-F% 例例5.14 获取姓的打头字母不是获取姓的打头字母不是J,K,L,M,N,O,并且名的开头字母是并

8、且名的开头字母是E或者或者Z的所有职员的编号和名字。的所有职员的编号和名字。SELECT * FROM EMPLOYEE WHERE EMP_LNAME LIKE J-O% AND emp_fname LIKE EZ% 10所有被方括号括起来的通配符（所有被方括号括起来的通配符（%,_,）都表示本来的意思。）都表示本来的意思。例例5.15 :SELECT project_no,project_name FROM project WHERE project_NAME LIKE %_% 11简单子查询可以同以下操作符一起使用：简单子查询可以同以下操作符一起使用：o比较操作符比较操作符oin操作符操

9、作符oany或或all操作符操作符oEXISTS函数函数5.3 简单子查询简单子查询 125.3.1 子查询和比较操作符子查询和比较操作符例例5.16 获取在研究部门工作的所有职员的名和姓。获取在研究部门工作的所有职员的名和姓。SELECT emp_fname,emp_lname FROM employee WHERE dept_no= (SELECT dept_no FROM DEPARTMENT WHERE dept_name=research) 13例例5.17 获取职员获取职员moser参与的项目编号。参与的项目编号。SELECT DISTINCT project_no FROM wo

10、rks_on WHERE emp_no= (SELECT emp_no FROM employee WHERE emp_lname=moser ) 145.3.2 子查询和子查询和in操作符操作符例例5.18 获取部门位于获取部门位于Dallas的所有职员的详细信息。的所有职员的详细信息。SELECT * FROM employee WHERE dept_no (SELECT dept_no FROM department WHERE location=Dallas ) 15例例5.19 获取为获取为Apollo项目工作的所有职员的姓。项目工作的所有职员的姓。SELECT emp_lname

11、FROM employee WHERE emp_no ( SELECT emp_no FROM works_on WHERE project_no (select project_no from project where project_name=Apollo ) ) 165.3.3 ANY和和ALL操作符操作符例例5.20 获取不是在项目上花费了最多时间的所有职员的编获取不是在项目上花费了最多时间的所有职员的编号、项目编号和工作名称号、项目编号和工作名称。SELECT DISTINCT EMP_NO,PROJECT_NO,JOB FROM works_on WHERE enter_date

12、 ( SELECT enter_date FROM works_on ) 17例例5.21 获取为项目获取为项目p1工作的所有职员的名和姓。工作的所有职员的名和姓。SELECT DISTINCT emp_fname,emp_lname FROM employee WHERE emp_no = ( SELECT emp_no FROM works_on where project_no=p1 ) 185.3.5 FROM子句中的查询子句中的查询SELECT DISTINCT emp_fname,emp_lname FROM (SELECT * FROM employee WHERE emp_no

13、=10000) AS emp_no10000例例5.22 选择编号大于等于选择编号大于等于10000的所有职员的名字。的所有职员的名字。 195.4 GROUP BY 子句子句SELECT project_no,job FROM works_on GROUP BY project_no, job例例5.23 使用项目编号和工种对所有职员分组。使用项目编号和工种对所有职员分组。 205.5 聚集函数聚集函数v便利聚集函数便利聚集函数v统计聚集函数统计聚集函数v超聚集超聚集聚集函数用于获取累计值的函数，分为三类聚集函数用于获取累计值的函数，分为三类 215.5.1 便利聚集便利聚集vMinvMax

14、vSumvAvgvCountvCount_big 22聚集函数聚集函数MIN、MAXSELECT emp_lname,MIN(emp_no) FROM employee例例5.24 看如下的查询例子，分析正确与否看如下的查询例子，分析正确与否 23例例5.25 获取最小的职员编号获取最小的职员编号SELECT MIN(emp_no) min_employee_number FROM employee例例5.26 获取最小的职员编号和姓获取最小的职员编号和姓SELECT emp_no,emp_lname FROM employee WHERE emp_no= ( SELECT MIN(emp_n

15、o) min_employee_number FROM employee) 24例例5.26 获取表获取表WORKS_ON中进入时间最晚的经理的职员中进入时间最晚的经理的职员编号编号SELECT emp_no FROM WORKS_ON WHERE enter_date ( SELECT MAX(enter_date) FROM WORKS_ON WHERE job Manager ) 25聚集函数聚集函数SUMSELECT SUM(BUDGET) SUM_OF_BUDGETS FROM PROJECT 例例9.27 计算所有项目预算的总和计算所有项目预算的总和 26聚集函数聚集函数COUNT

16、、COUNT_BIGSELECT project_no,COUNT(DISTINCT job)job_count FROM works_on group by project_no 例例5.28 计算每个项目中不同工种的数目计算每个项目中不同工种的数目例例5.29 每个项目中有多少职员为其工作每个项目中有多少职员为其工作SELECT project_no,COUNT(*) emp_count FROM works_on group by project_no 未消除空值 275.5.2 统计聚集函数统计聚集函数vVARvVARPvSTEDVvSTEDVP 28 5.5.3 超聚集超聚集vCUB

17、EvROLLUP作用域数据仓库 295.6 HAVING子句子句SELECT project_no FROM works_on group by project_no having count(*)4HAVING conditionCondition 中可以包含聚集函数或常量中可以包含聚集函数或常量例例5.30 获取招募的总人数少于获取招募的总人数少于4个的项目个的项目 305.7 ORDER BY 子句子句SELECT emp_no,emp_fname,emp_lname FROM employee ORDER BY emp_noORDER BY子句为某个查询的结果定义一个特定行顺序子句为某个查询的结果定义一个特定行顺序语法格式如下：语法格式如下：ORDER BY col_name | col_number ASC|DESC,例例5.31 获取职员的名字和职员编号，以职员编号升序排列获取职员的名字和职员编号，以职员编号升序排列 315.32 获取编号小于获取编号小于2000的职员所在的部门和其编号，并以姓和的职员所在的部门和其编号，并以姓和名升序排列名升序排列SELECT emp_fname,emp_lname,dept_no FROM em