经典SQL语句大全201309031234.doc

课程 一 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 SELECT 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, salary,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的基本内容，请参考 七、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 DESC; 八、限制选取行： 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, 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.923，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是一个求系统时间的函数 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_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; 我们看到： 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 字符转换为日期 这几个函数较为简单，但要多多实践，多看复杂的实例。 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(031092) CORRECT FROM DUAL；/不正确 SELECT TO_DATE(031095,MMDDYY) ERRORR FROM DUAL 输出 3月10日 SELECT TO_DATE(031095,DDMMYY) ERRORR FROM DUAL 输出 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_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(last_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) FROM s_emp WHERE manager_id IS NULL; 对于例子，大家重要的理解，并多做测试，并注意英文版和中文版在日期上的区别。 有些教材上的例子，不要盲目的相信其结果，实践后才有发言权，希望大家能够在学习的过程中不要忽略了用，多想一想为什么实例要如此设计，在何种情况下应用此实例来解决问题。这样，我们才真正掌握了知识。 课程 三 从多个表中提取数据 本课重点：1、SELECT FROM 多个表，使用等连接或非等连接2、使用外连接OUTER JOIN3、使用自连接注意：以下实例中标点均为英文半角一、连接的概念：是指一个从多个表中的数据进行的查询。连接一般使用表的主键和外键。连接类型：等连接、不等连接、外连接、自连接二、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 = table2.column2;如：SQL SELECT s_emp.last_name, s_emp.dept_id,2 s_3 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”4 FROM s_dept, s_region5 WHERE s_dept.region_id = s_region.id;在WHERE 段中，如果没有前缀，两个表中都有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 ”Customer Name”,2 c.region_id ”Region ID”,3 ”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;说明：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, 2 FROM 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 manager4 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来限制返回组 注意：以下实例中标点均为英文半角 一、概念： 组函数是指按每组返回结果的函数。 组函数可以出现在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：一个混合实例，说明所有问题： 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函数可以接任何数据类型。 如果是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 GROUP 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，就要把这个列GROUP 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: group 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 BY，来进行分组总数的计算，但在ORACLE中是没有的。大家可以建立一个有多个列，多个重复值的表，然后进行各种分组的演示，用得多了，自然明了。 课程 五 子查询本课重点： 1、在条件未知的情况下采用嵌套子查询 2、用子查询做数据处理 3、子查询排序 注意：以下实例中标点均为英文半角 一、概述： 子查询是一种SELECT句式中的高级特性，就是一个SELECT语句作为另一个语句的一个段。我们可以利用子查询来在WHERE字段中引用另一个查询来攻取值以补充其无法事先预知的子结果。 子查询可以用在WHERE子句，HAING子句，SELECT或DELETE语句中的FROM 子句。 注意：1、子查询必须在一对圆括号里。 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，会如何？-会出错，因为不能用=来连接。 ORA-1427: single-row subquery returns more than one row 以上的查询也被称之为 单行子查询。 DELECT子查询实例： delete from new_table where cata_time to_date(19990901,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 FROM 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) 6 FROM s_emp 7 GROUP BY title); 对子查询，我们了解这么多在理论上已经覆盖了所有的知识点，对于UPDATE 和DELETE的子查询，不作为重点，但也要练习掌握。今天到这，谢谢大家。 课程 六 运行时应用变量本课重点： 1、创建一个SELECT语句，提示USER在运行时先对变量赋值。 2、自动定义一系列变量，在SELECT运行时进行提取。 3、在SQL PLUS中用ACCEPT定义变量 注意：以下实例中标点均为英文半角 一、概述： 变量可以在运行时应用，变量可以出现在WHERE 字段，文本串，列名，表名等。 1、我们这里的运行时，指的是在SQL PLUS中运行。 2、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 输入 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 , r.id, ”REGION NAME” FROM s_dept d, s_region r WHERE d.region_id = r.id AND UPPER() LIKE UPPER(%&p_dname%) / SET ECHO ON 存为文件：l7prompt.SQL SQL START l7prompt Provide the department name: sales 2、SQL DEFINE dname = sales SQL DEFINE dname DEFINE dname = ”sales” (CHAR) SQL SELECT name 2 FROM s_dept 3 WHERE lower(name) = &dname; 可以正常执行了。 SQL DEFINE dname 主要是显示当前的变量是否赋值，值是什么。当然，我们可以用UNDEFINEGO 来使变量恢复初始，不然它会一直保持下去。 3、如果变量在SQL SCRIPT文件中确定 ：可以SQL START l7param President 来赋值。 总结：本课主要针对较古老的SQLPLUS方法，在REPORT和结果集生成方面使用变量，达到方便操作，动态修改的目的。 课程 七 其他数据库对象 SEQUENCE 创建实例：SQL CREATE SEQUENCE s_dept_id2 INCREMENT BY 13 START WITH 514 MAXVALUE 99999995 NOCACHE6 NOCYCLE;Sequence created.1、NEXTVAL和CURRVAL的用法 只有在INSERT中，才可以作为子查询出现。以下几个方面不可用子查询：SELECT子句OFAVIEW有DISTINCT的出现的SELECT。有GROUPBY，HAVING，ORDERBY的SELECT子句。SELECT或DELETE，UPDATE中的子查询。DEFAULT选项中不能用。2、编辑SEQUENCE只有OWNER或有ALTER权限的用户才能修改SEQUENCE未来的NUMBER受修改的影响。不能修改STARTWITH，如果变，则要RECREATE。修改会受到某些有效性检验的限制，如MAXVALUE3、删除：DROP SEQUENCE sequence;ORACLE对象之INDEX一、INDEX概述：是ORACLE的一种数据对象，用POINTER来加速查询行。通过快速路径存取方法定位数据并减少I/O。INDEX独立于表。INDEX由ORACLESERVER来使用和保持。二、索引如何建立？1、自动：通过PRIMARYKEY和UNIQUE KEY约束来建立。 2、用户手工建立非唯一性索引。 三、创建方法： 语法：CREATE INDEX indexON table (column, column.); 何时建立INDEX： 此列经常被放到WHERE字段或JOIN来作条件查询。 此列含有大量的数据。 此列含有大量的空值。 两个或几个列经常同时放到WHERE字段进行组合查询 表很大而且只有少于2-4% 的ROW可能被查询的时候。 以下情况不要建立索引： 表很小； 表被更新频繁。 四、查看已经存在的索引： 1、USER_INDEXES可以查询索引名和类型。 2、USER_IND_COLUMNS包含索引名、表名、列名。 实例： SQL SELECT ic.index_name, ic.column_name,2 ic.column_position col_pos, ix.uniqueness3 FROM user_indexes ix, user_ind_columns ic4 WHERE ic.index_name = ix.index_name5 AND ic.table_name = S_EMP; 五、删除索引： DROP INDEX index; SYNONYMS 同义词 语法： CREATE PUBLIC SYNONYM synonym for object; 注意：此对象不能包含在一个包里； 一个私有的同义词不能与同一USER的其他对象重名。 DROP SYNONYM D_SUM； 课程 八 用户访问控制 本课重点： 1、创建用户 2、创建角色来进行安全设置 3、使用GRANT或REVOKE 来控制权限 注意：以下实例中标点均为英文半角 一、概述： ORACLE通过用户名和密码进行权限控制。 数据库安全：系统安全和数据安全 系统权限：使用户可以访问数据库 对象权限：操纵数据库中的对象 SCHEMA：各种对象的集合 二、系统权限： 1、超过80个权限可用。 2、DBA有最高的系统权限： CREATE NEW USER REMOVE USERS REMOVE ANY TABLE BACKUP ANY TABLE 三、创建用户 1、CREATE USER user IDENTIFIED BY password; 2、系统权限：CREATE SESSION Connect to the database. CREATE TABLE Create tables in the users schema. CREATE SEQUENCE Create a sequence in the users schema. CREATE VIEW Create