《精通SQL【经典SQL语句大全】.doc》由会员分享，可在线阅读，更多相关《精通SQL【经典SQL语句大全】.doc（32页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、课程 一 PL/SQL 基本查询与排序本课重点： 1、写SELECT语句进行数据库查询 2、进行数学运算 3、处理空值 4、使用别名ALIASES 5、连接列 6、在SQL PLUS中编辑缓冲，修改SQL SCRIPTS 7、ORDER BY进行排序输出。 8、使用WHERE 字段。 一、写SQL 命令： 不区分大小写。 SQL 语句用数字分行，在SQL PLUS中被称为缓冲区。 最后以；或 / 结束语句。 也可以用RUN来执行语句 二、从表中查询相关的字段。例1：SQL SELECT dept_id, last_name, manager_id FROM s_emp; 2：SQL SELEC
2、T last_name, salary * 12, commission_pct FROM s_emp; 对于数值或日期型的字段，可以进行相应的四则运算，优先级与标准的高级语言相同。 SQL SELECT last_name, salary, 12 * (salary + 100) FROM s_emp; 三、列的别名ALIASES： 计算的时候特别有用； 紧跟着列名，或在列名与别名之间加“AS”； 如果别名中含有SPACE，特殊字符，或大小写，要用双引号引起。 例(因字体原因，读者请记住：引号为英文双引号Double Quotation)： SQL SELECT last_name, sal
3、ary,12 * (salary + 100) ”Annual Salary” FROM s_emp; 四、连接符号：| 连接不同的列或连接字符串 使结果成为一个有意义的短语： SQL SELECT first_name | | last_name| , | title ”Employees” FROM s_emp; 五、管理NULL值： SQL SELECT last_name, title,salary * NVL(commission_pct,0)/100 COMM FROM s_emp; 此函数使NULL转化为有意义的一个值，相当于替换NULL。 六、SQL PLUS的基本内容，请参考
4、 七、ORDER BY 操作： 与其他SQL92标准数据库相似，排序如： SELECT expr FROM table ORDER BY column,expr ASC|DESC;从Oracle7 release 7.0.16开始，ORDER BY 可以用别名。 另：通过位置判断排序： SQL SELECT last_name, salary*12 FROM s_emp ORDER BY 2; 这样就避免了再写一次很长的表达式。 另：多列排序： SQL SELECT last name, dept_id, salary FROM s_emp ORDER BY dept_id, salary D
5、ESC; 八、限制选取行： SELECT expr FROM table WHERE condition(s) ORDER BY expr; 例1： SQL SELECT first_name, last_name, start_date FROM s_emp WHERE start_date BETWEEN 09-may-91AND 17-jun-91; 例2： SQL SELECT last_name FROM s_emp WHERE last_name LIKE _a%; /显示所有第二个字母为 a的last_name 例3： 如果有列为NULL SQL SELECT id, name,
6、 credit_rating FROM s_customer WHERE sales_rep_id IS NULL; 优先级： Order Evaluated Operator 1 All comparison operators (=, , , =, , SELECT first_name, last_name FROM s_emp WHERE UPPER(last_name) = PATEL; FIRST_NAME LAST_NAME - - Vikram Patel Radha Patel 三、数学运算函数(SQL也能用函数来进行四舍五入) 1、ROUND 四舍五入：ROUND(45.9
7、23，2) = 45.92 ROUND(45.923，0) = 46 ROUND(45.923，-1) = 50 2、TRUNC截取函数(取地下) TRUNC(45.923，2)= 45.92 TRUNC(45.923)= 45(默为去除小数点) TRUNC(45.923，-1)= 40 3、MOD 余除 MOD(1600，300) 实例： SQL SELECT ROUND(45.923,2), ROUND(45.923,0),ROUND(45.923,-1) FROM SYS.DUAL; 四、ORACLE 日期格式和日期型函数： 1、默认格式为DD-MON-YY. 2、SYSDATE是一个求
8、系统时间的函数 3、DUALdju:el 是一个伪表，有人称之为空表，但不确切。 SQL SELECT SYSDATE FROM SYS.DUAL; 4、日期中应用的算术运算符 例：SQL SELECT last_name, (SYSDATE-start_date)/7 WEEKS FROM s_emp WHERE dept_id = 43; DATE+ NUMBER = DATE DATE-DATE= NUMBER OF DAYS DATE + (NUMBER/24) = 加1小时 5、函数： MONTHS_BETWEEN(date1, date2) 月份间隔，可正，可负，也可是小数 ADD
9、_MONTHS(date,n) 加上N个月，这是一个整数，但可以为负 NEXT_DAY(date,char) 如：NEXT_DAY (restock_date,FRIDAY),从此日起下个周五。 ROUND(date,fmt) TRUNC(date,fmt) 解释下面的例子： SQL SELECT id, start_date,MONTHS_BETWEEN (SYSDATE,start_date) TENURE, ADD_MONTHS(start_date,6) REVIEW FROM s_emp WHERE MONTHS_BETWEEN (SYSDATE,start_date)48; 我们看
10、到： MONTHS_BETWEEN (SYSDATE,start_date) select round(sysdate,MONTH) from dualROUND(SYSD - 01-11月-01 round(sysdate,YEAR) = 01-1月 -02 ROUND 之后的值比基值大的最小符合值，大家可以用更改系统时间的方法测试，以15天为分界线，也是非常形象的四舍五入，而TRUNC恰好相反，是对现有的日期的截取。 五、转换函数： 1、TO_CHAR 使一个数字或日期转换为CHAR 2、TO_NUMBER 把字符转换为NUMBER 3、TO_DATE 字符转换为日期 这几个函数较为简单，
11、但要多多实践，多看复杂的实例。 SQL SELECT ID,TO_CHAR(date_ordered,MM/YY) ORDERED FROM s_ord WHERE sales_rep_id = 11; 转换时，要注意正确的缺省格式： SELECT TO_DATE(03-MAR-92) CORRECT FROM DUAL；/正确 SELECT TO_DATE() CORRECT FROM DUAL；/不正确 SELECT TO_DATE(,MMDDYY) ERRORR FROM DUAL 输出 3月10日 SELECT TO_DATE(,DDMMYY) ERRORR FROM DUAL 输出
12、10月3日 4、实例： select to_char(sysdate,fmDDSPTH of MONTH YYYY AM) TODAYS FROM DUAL; TODAYS - SIXTEENTH of 11月 2001 下午 大小写没有什么影响，引号中间的是不参与运算。 实例 ： SELECT ROUND(SALARY*1.25) FROM ONE_TABLE； 意义：涨25%工资后，去除小数位。在现实操作中，很有意义。 5、混合实例：SQL SELECT last_name, TO_CHAR(start_date,fmDD ”of” Month YYYY) HIREDATE FROM s_
13、emp WHERE start_date LIKE %91;LAST_NAME HIREDATE- -Nagayama 17 of June 1991Urguhart 18 of January 1991Havel 27 of February 1991 这里要注意：fmDD 和 fmDDSPTH之间的区别。 SQL SELECT id, total, date_ordered FROM s_ord WHERE date_ordered =TO_DATE(September 7, 1992,Month dd, YYYY); 六、独立的函数嵌套SQL SELECT CONCAT(UPPER(la
14、st_name),SUBSTR(title,3) ”Vice Presidents”FROM s_empWHERE title LIKE VP%; * 嵌套可以进行到任意深度，从内向外计算。 例： SQL SELECT TO_CHAR(NEXT_DAY(ADD_MONTHS(date_ordered,6),FRIDAY),fmDay, Month ddth, YYYY) ”New 6 Month Review” FROM s_ord ORDER BY date_ordered;SQL SELECT last_name,NVL(TO_CHAR(manager_id),No Manager) FR
15、OM s_emp WHERE manager_id IS NULL; 对于例子，大家重要的理解，并多做测试，并注意英文版和中文版在日期上的区别。 有些教材上的例子，不要盲目的相信其结果，实践后才有发言权，希望大家能够在学习的过程中不要忽略了用，多想一想为什么实例要如此设计，在何种情况下应用此实例来解决问题。这样，我们才真正掌握了知识。 课程 三 从多个表中提取数据 本课重点：1、SELECT FROM 多个表，使用等连接或非等连接2、使用外连接OUTER JOIN3、使用自连接注意：以下实例中标点均为英文半角一、连接的概念：是指一个从多个表中的数据进行的查询。连接一般使用表的主键和外键。连接类
16、型：等连接、不等连接、外连接、自连接二、Cartesian product ：指的是当JOIN条件被省略或无效时，所有表的行(交叉)都被SELECT出来的现象。Cartesian product可以产生大量的记录，除非是你有意如此，否则应该加上某种条件限制。SQL SELECT name, last_name2 FROM s_dept, s_emp;300 rows selected. 其中一个表12行，一个表25行。三、简单连接查询：SELECT table.column, table.column.FROM table1, table2WHERE table1.column1 = tabl
17、e2.column2;如：SQL SELECT s_emp.last_name, s_emp.dept_id,2 s_dept.name3 FROM s_emp, s_dept4 WHERE s_emp.dept_id = s_dept.id;注意：表前缀的重要性：SQL SELECT s_dept.id ”Department ID”,2 s_region.id ”Region ID”,3 s_region.name ”Region Name”4 FROM s_dept, s_region5 WHERE s_dept.region_id = s_region.id;在WHERE 段中，如果没
18、有前缀，两个表中都有ID字段，就显得的模棱两可，AMBIGUOUS。这在实际中应该尽量避免。WHERE 字段中，还可以有其他的连接条件，如在上例中，加上：INITCAP(s_dept.last_name) = Menchu;再如：WHERE s_emp.dept_id = s_dept.id AND s_dept.region_id = s_region.id AND s_mission_pct 0;四、表别名ALIAS：1、使用别名进行多表查询 。2、仅在这个查询中生效，一旦用了表别名，就不能再用表的原有的名字进行连接。实例：SQL SELECT c.name ”Customer Name”
19、,2 c.region_id ”Region ID”,3 r.name ”Region Name”4 FROM s_customer c, s_region r5 WHERE c.region_id = r.id;别名最多可以30个字符，但当然越少越好。最好也能容易识别。五、非等连接 非等连接一般用在没有明确的等量关系的两个表；最简单的说：非等连接就是在连接中没有“=”出现的连接。SQL SELECT e.ename, e.job, e.sal, s.grade2 FROM emp e, salgrade s3 WHERE e.sal BETWEEN s.losal AND s.hisal;说
20、明：Create a non-equijoin to evaluate an employees salary grade. The salary 必须在另一个表中最高和最低之间。其他操作符= 也可以实现，但是BETWEEN是非常简单实用的。BETWEEN .AND是指闭区间的，这点要注意 ，请大家测试。六、外连接语法结构：SELECT table.column, table.columnFROM table1, table2WHERE table1.column = table2.column(+);实例：SQL SELECT e.last_name, e.id, c.name2 FROM
21、s_emp e, s_customer c3 WHERE e.id (+) = c.sales_rep_id4 ORDER BY e.id;显示.，即使有的客户没有销售代表。* 可以理解为有+号的一边出现了NULL，也可以做为合法的条件。外连接的限制：1、外连接符只能出现在信息缺少的那边。2、在条件中，不能用 IN 或者 OR做连接符。七、自连接同一个表中使用连接符进行查询；FROM 的后面用同一个表的两个别名。实例：SQL SELECT worker.last_name| works for |2 manager.last_name3 FROM s_emp worker, s_emp man
22、ager4 WHERE worker.manager_id = manager.id; 意味着：一个员工的经理ID匹配了经理的员工号，但这个像绕口令的连接方式并不常用。以后我们会见到一种 子查询：select last_name from s_emp where salary=(select max(salary) from s_emp)也可以看作是一种变向的自连接，但通常我们将其 课程 四 组函数本课重点： 1、了解可用的组函数 2、说明每个组函数的使用方法 3、使用GROUP BY 4、通过HAVING来限制返回组 注意：以下实例中标点均为英文半角 一、概念： 组函数是指按每组返回结果的函
23、数。 组函数可以出现在SELECT和HAVING 字段中。 GROUP BY把SELECT 的结果集分成几个小组。 HAVING 来限制返回组，对RESULT SET而言。 二、组函数：(#号的函数不做重点) 1、AVG 2、COUNT 3、MAX 4、MIN 5、STDDEV # 6、SUM 7、VARIANCE # 语法： SELECT column, group_function FROM table WHERE condition GROUP BY group_by_expression HAVING group_condition ORDER BY column; 实例1：一个混合实
24、例，说明所有问题： SQL SELECT AVG(salary), MAX(salary), MIN(salary), 2 SUM(salary) 3 FROM s_emp 4 WHERE UPPER(title) LIKE SALES%; AVG(SALARY) MAX(SALARY) MIN(SALARY) SUM(SALARY) - - - - 1476 1525 1400 7380 说明：很多函数，我们在讲函数的已经向大家介绍过，但在此为何叫分组函数呢，主要是因为它们可以与GROUP BY来形成对不同组的计算，相当于在很多值中进行挑选。 * MIN MAX函数可以接任何数据类型。 如果
25、是MIN(last_name), MAX(last_name)，返回的是什么呢？ 千万记住，不是指LAST_NAME的长度，而是指在FIRST字母的前后顺序，第一个相同，然后比较第二个，如：xdopt cssingkdkdk adopt acccc 实例2： SQL SELECT COUNT(commission_pct) 2 FROM s_emp 3 WHERE dept_id = 31; 返回所有非空行个数 三、GROUP BY的应用： 先看一个简单实例： SQL SELECT credit_rating, COUNT(*) ”# Cust” 2 FROM s_customer 3 GRO
26、UP BY credit_rating; 注意这里别名的应用，复习一下从前的课程，加了引号后，就可以用特殊字符，但也仅有三个：#$_，什么对象的名字都如此。当然空格也是可以的。 复杂实例： SQL SELECT title, SUM(salary) PAYROLL 2 FROM s_emp 3 WHERE title NOT LIKE VP% 4 GROUP BY title 5 ORDER BY SUM(salary); 这里要注意一下几个CLAUSE的先后次序。 WHERE在这里主要是做参与分组的记录的限制。 *另外，如果要选取出来一个不加组函数的列，如上面的TITLE，就要把这个列GRO
27、UP BY !否则要出错的!信息为：ERROR at line 1:ORA-00937: not a single-group group function 理论很简单，如果不GROUP BY TITLE，显示哪一个呢？这个在试题中经常出现。 结论：不加分组函数修饰的列必定要出现在GROUP BY 里。 错误实例： SQL SELECT dept_id, AVG(salary) 2 FROM s_emp 3 WHERE AVG(salary) 2000 4 GROUP BY dept_id; WHERE AVG(salary) 2000*ERROR at line 3:ORA-00934: g
28、roup function is not allowed here 应在GROUP BY 后面加上HAVING AVG(salary) 2000; 因为是用来限制组的返回。 多级分组实例： SQL SELECT dept_id, title, COUNT(*) 2 FROM s_emp 3 GROUP BY dept_id, title; 就是先按照DEPT_ID分组，当DEPT_ID相同的时候，再按TITLE分组，而COUNT(*)以合成的组计数。 顺序对结果有决定性的影响。 总结：本课我们主要学习了分组函数的使用及如何进行分组查询，我们可以想像一下，SQL SERVER中有COMPUTE
29、BY，来进行分组总数的计算，但在ORACLE中是没有的。大家可以建立一个有多个列，多个重复值的表，然后进行各种分组的演示，用得多了，自然明了。 课程 五 子查询本课重点： 1、在条件未知的情况下采用嵌套子查询 2、用子查询做数据处理 3、子查询排序 注意：以下实例中标点均为英文半角 一、概述： 子查询是一种SELECT句式中的高级特性，就是一个SELECT语句作为另一个语句的一个段。我们可以利用子查询来在WHERE字段中引用另一个查询来攻取值以补充其无法事先预知的子结果。 子查询可以用在WHERE子句，HAING子句，SELECT或DELETE语句中的FROM 子句。 注意：1、子查询必须在一
30、对圆括号里。 2、比较符号：, =, 或者 IN. 3、子查询必须出现在操作符的右边 4、子查询不能出现在ORDER BY里 (试题中有时出现找哪行出错) 二、子查询的执行过程： NESTED QUERY MAIN QUERY SQL SELECT dept_id SQL SELECT last_name, title 2 FROM s_emp 2 FROM s_emp 3 WHERE UPPER(last_name)=BIRI; 3 WHERE dept_id = 这里 ，每个查询只运行一次。当然，子查询要首先被执行，大家设想一下，如果子查询中有一个以上的人的LASTNAME为BIRI，会如
31、何？-会出错，因为不能用=来连接。 ORA-1427: single-row subquery returns more than one row 以上的查询也被称之为 单行子查询。 DELECT子查询实例： delete from new_table where cata_time to_date(,yyyymmdd) and pro_name=( select pro_name from new_product where pro_addr in (bj,sh) 三、子查询中的GROUP 函数的应用 实例 1： SQL SELECT last_name, title, salary 2 F
32、ROM s_emp 3 WHERE salary SELECT dept_id, AVG(salary) 2 FROM s_emp 3 GROUP BY dept_id 4 HAVING AVG(salary) 5 (SELECT AVG(salary) 6 FROM s_emp 7 WHERE dept_id = 32); 子查询被多次执行，因为它出现在HAVING 子句中。 SQL SELECT title, AVG(salary) 2 FROM s_emp 3 GROUP BY title 4 HAVING AVG(salary) = 5 (SELECT MIN(AVG(salary)
33、6 FROM s_emp 7 GROUP BY title); 对子查询，我们了解这么多在理论上已经覆盖了所有的知识点，对于UPDATE 和DELETE的子查询，不作为重点，但也要练习掌握。今天到这，谢谢大家。 课程 六 运行时应用变量本课重点： 1、创建一个SELECT语句，提示USER在运行时先对变量赋值。 2、自动定义一系列变量，在SELECT运行时进行提取。 3、在SQL PLUS中用ACCEPT定义变量 注意：以下实例中标点均为英文半角 一、概述： 变量可以在运行时应用，变量可以出现在WHERE 字段，文本串，列名，表名等。 1、我们这里的运行时，指的是在SQL PLUS中运行。 2
34、、ACCEPT ：读取用户输入的值并赋值给变量 3、DEFINE：创建并赋值给一个变量 4、在做REPORT时经常使用，比如对某个部门的销售信息进行统计，部门名称可以以变量代替。 SQL PLUS不支持对输入数据的有效性检查，因此提示要简单且不模棱两可。 二、应用实例： 1、SQL SELECT id, last_name, salary 2 FROM s_emp 3 WHERE dept_id = &department_number; 2、可以在赋值前后进行比较： SET VERIFY ON . 1* select * from emp where lastname=&last_name
35、输入 last_name 的值: adopt 原值 1: select * from emp where lastname=&last_name 新值 1: select * from emp where lastname=adopt -如果在原语句中没有单引号，那么在输入值的时候要手工加上单引号。一般字符和日期型要在语句中加上单引号。 SET VERIFY OFF 之后，原值和新值这两句消失。这在ORACLE8I中是默认为ON。 3、子句为变量：WHERE &condition; 要注意引号 三、DEFINE和ACCEPT的应用： 1、SET ECHO OFF /使内容不 显示在用户界面 ACCEPT p_dname PROMPT Provide the department name: SELECT d.name, r.id, r.name ”REGION NAME” FROM s_dept d, s_region r WHERE d.region_id = r.id AND UPPER(d.name) LIKE UPPER(%&p_dname%) / SET ECHO ON 存为文件：l7prompt.SQL SQL