oracle數(shù)據(jù)庫sql語句詳解大全(編輯修改稿)

　

【文章內(nèi)容簡(jiǎn)介】 ? 查詢 s_emp表中 31部門一共有多少員工： SQL SELECT COUNT(*) 2 FROM s_emp 3 WHERE dept_id = 31。 ? 查詢 s_emp表中銷售人員的數(shù)量 (提成率不為空的記錄個(gè)數(shù) )： SQL SELECTCOUNT(mission_pct) 2 FROM s_emp 應(yīng)用舉例 SQL SELECT , max(),avg(),sum(salary) 2 FROM s_emp e , s_dept d 3 WHERE = 4 GROUP BY dept_id 5 ORDER BY sum(salary)。 ? 查詢 s_emp表中各個(gè)部門員工的平均工資 ,工資總和及部門名稱并按照工資總和排序： 應(yīng)用舉例 SQL SELECT , max(),avg(),sum(salary) 2 FROM s_emp e , s_dept d 3 WHERE = and !=41 4 GROUP BY dept_id 5 ORDER BY sum(salary)。 ? 查詢 s_emp表中 除 41部門以外 的部門員工的平均工資 ,工資總和及部門名稱并按照工資總和排序： SQL SELECT , max(),avg(),sum(salary) 2 FROM s_emp e , s_dept d 3 WHERE = 4 GROUP BY dept_id 5 HAVING !=41 6 ORDER BY sum(salary)。 應(yīng)用舉例 ? 求平均工資高于 1500的部門的工資總和，最高工資，最低工資： SQL SELECT , max(),avg(),sum() 2 FROM s_emp e , s_dept d 3 WHERE = 4 GROUP BY dept_id 5 HAVING avg()1500 6 ORDER BY avg ()。 課堂練習(xí) ? 求不以“ VP”開頭職位的，各個(gè)職位中工資總和大于5000的職位及工資總和，并按工資總和排序： SQL SELECT title, SUM(salary) PAYROLL 2 FROM s_emp 3 WHERE title NOT LIKE 39。VP%39。 4 GROUP BY title 5 HAVING SUM(salary) 5000 6 ORDER BY SUM(salary)。 小結(jié) ?SELECT column, group_function ?FROM table ?[WHERE condition] ?[GROUP BY group_by_expression] ?[HAVING group_condition] ?[ORDER BY column]。 第六章 子查詢 子查詢 ? 查詢 s_emp表中工資最低的員工的姓名： SQL SELECT min（ salary） 2 FROM s_emp SQL SELECT last_name 2 FROM s_emp 3 WHERE salary = 最小工資（上一條的運(yùn)行結(jié)果） SQL SELECT last_name 2 FROM s_emp 3 WHERE salary = (SELECT min(salary) FROM s_emp) 子查詢 ? 查詢 s_emp表中平均工資低于 32部門的部門 ID： SQL SELECT avg（ salary） 2 FROM s_emp 3 WHERE dept_id = 32。 SQL SELECT dept_id,avg(salary) 2 FROM s_emp 3 GROUP BY dept_id 4 HAVING avg(salary)32部門的平均工資 SQL SELECT dept_id,avg(salary) FROM s_emp 3 GROUP BY dept_id 4 HAVING avg(salary)(SELECT avg(salary) 5 FROM s_emp 6 WHERE dept_id = 32) 7 ORDER BY DEPT_ID。 子查詢 ? 查詢 s_emp表中平均工資低于 32部門的部門 ID及名稱： SQL SELECT dept_id,avg(salary),name 2 FROM s_emp,s_dept 3 WHERE = 4 GROUP BY dept_id,name 5 HAVING avg(salary)(SELECT avg(salary) 6 FROM s_emp 7 WHERE dept_id = 32) 8 ORDER BY DEPT_ID。 子查詢舉例 SQL SELECT last_name, first_name, title 2 FROM s_emp 3 WHERE dept_in = 4 (SELECT ID 5 FROM s_dept 6 WHERE name = 39。Finance39。 7 OR region_id = 2)。 ORA01427:singlerow subquery returns more than one row 小結(jié) SELECT select_list FROM table WHERE expr operator (SELECT select_list FROM table)。 第七章 數(shù)據(jù)建模及數(shù)據(jù)庫設(shè)計(jì) 本章目標(biāo) 了解系統(tǒng)開發(fā)的步驟 數(shù)據(jù)關(guān)系的定義 理解實(shí)體關(guān)系映射圖（ ER圖） 系統(tǒng)開發(fā)步驟 Strategy and Analysis Design Build and Document Transition Production 數(shù)據(jù)模型 Model of system in client39。s mind Entity model of client39。s model Table model of entity model Tables on disk Server Entity Relationship Modeling Concepts ? Entity ? A thing of significance about which information needs to be known ? Examples: customers, sales representatives, orders ? Attribute ? Something that describes or qualifies an entity ? Examples: name, phone, identification number ? Relationship ? An association between two entities ? Examples: orders and items, customers and sales representatives Entity Relationship Model ? Create an entity relationship diagram from business specifications or narratives. ?Scenario ? . . . Assign one or more customers to a sales representative . . . ? . . . Some sales representatives do not yet have assigned customers . . . CUSTOMER * id * name o phone EMPLOYEE * id * last name o first name assigned to the sales rep to Relationship Types ? Onetoone ? Have a degree of one and only one in both directions. ? Are rare. ? Example: Husband and wife. ? Manytoone ? Have a degree of one or more in one direction and a degree of one and only one in the other direction. ? Are very mon. ? Example: passengers and plane. ? Manytomany ? Have a degree of one or more in both directions. ? Are resolved with an intersection entity. ? Example: Employees and skills. 數(shù)據(jù)庫 3范式 數(shù)據(jù)庫中的每一列都是不可再分的基本數(shù)據(jù)項(xiàng)，同一列中不能有多個(gè)值 數(shù)據(jù)庫表中不存在非關(guān)鍵字段對(duì)任何候選關(guān)鍵字段的部分依賴 數(shù)據(jù)庫表中不存在非關(guān)鍵字段對(duì)任何候選關(guān)鍵字段的傳遞引用 約束類型 PK ——Primary Key 唯一且非空