Oracle11gDatabaseSQL语言基础.ppt

Oracle11g Database SQL语言基础,2011.8ByKevinlin.林少杰,SQL语言简介,SQL语言(Structured Query Language)是访问关系型数据库的标准语言。SQL语言可以分为五种类型：查询(QUERY)-SELECT数据处理(DML)-INSERT,UPDATE,DELETE数据定义(DDL)-CREATE,ALTER,DROP,RENAME,TRUNCATE事务控制(TC)-COMMIT,ROLLBACK,SAVEPOINT数据控制(DCL)GRANT,REVOKE,SQL语句可以被嵌入其他语句编写的程序里进行运行，比如C+程序，也可以使用JDBC将SQL语句 写到Java程序里。SQL语句也可以使用一些工具运行，与数据库交互，比如Oracle SQL*Plus，Oracle SQL Developer以及其他第三方工具。,查询数据,使用SELECT查询数据,表1,表2,查询表中的若干行数据,查询表中的若干列,联合不同表中的数据,基本SELECT语句,SELECT 子句指定要查询的数据列。FROM 子句指定对象表。,SELECT*|DISTINCT column|expression alias,.FROM table;,查询所有列,SELECT*FROM departments;,查询指定列,SELECT department_id,location_idFROM departments;,SELECT语句编写,大小写不敏感。可以写成一行或多行。关键字不能缩写或跨行。语句中的子句(clause)通常分行输入。缩进增加可读性。在一般的工具中，SQL语句一般以分号“;”结尾。,SELECT语句中的四则运算,SELECT last_name,salary,12*(salary+100)FROM employees;,Null值,Null值表示相应的值未知、不可用、未被分配。Null值不同于数字0或空格。所有与Null值相关的四则运行，结果为Null。,定义列别名(Column Alias),重命名列名在有四则运算的情况下，可以使列名更具有意义列别名在定义时，紧跟列名，以空格或“as”分隔当列别名的大小写敏感，或者含有空格、特殊字符，需要使用双引号。,SELECT last_name Name,salary*12 Annual SalaryFROM employees;,连接符操作,将列、字符串连接在一起，作为一个新列。连接符为两个竖线：“|”最终形成的列为字符表达式。,SELECTlast_name|job_id AS EmployeesFROM employees;,原义字符串(Literal Character Strings),原义字符指字符、数字，或者日期。日期和字符需要使用单引号封装。每行数据显示一次。,SELECT last_name|is a|job_id AS Employee DetailsFROM employees;,Quote(q)操作,可以选择分隔符。可选择任意方便的分隔符，单字节或多字节，或者下列符号:,(),。增加可读性与实用性。,SELECT department_name|Department|qs Manager Id:|manager_id AS Department and Manager FROM departments;,消除重复行,SELECT DISTINCT department_idFROM employees;,SELECT DISTINCT department_id,job_idFROM employees;,可以在多个列之前使用DISTINCT修饰词，影响指定的所有列，其结果是这些列组合后的不同值。,DESCRIBE命令,DESCRIBE employees,限制返回结果,使用WHERE子句限制返回结果:WHERE 子句在 FROM子句之后。,SELECT*|DISTINCT column|expression alias,.FROM tableWHERE condition(s);,SELECT employee_id,last_name,job_id,department_idFROM employeesWHERE department_id=90;,比较运算符(Comparison Operators),不等于,两个值之间(包括限值),BETWEEN.AND.,符合列表内的值,IN(set),符合字符样式,LIKE,是null值,IS NULL,小于,小于或等于,=,大于或等于,=,大于,等于,=,意义,运算符,使用比较运算符,SELECT last_name,salaryFROM employeesWHERE salary=3000;,使用比较运算符,SELECT last_name,salaryFROM employeesWHERE salary BETWEEN 2500 AND 3500;,使用BETWEEN 操作显示符合范围的值:,下限,上限,使用比较运算符,SELECT employee_id,last_name,salary,manager_idFROM employeesWHERE manager_id IN(100,101,201);,使用IN 操作符 显示符合列表内值的数据:,使用比较运算符,LIKE 操作符筛先符合查找字符串的数据行。查找条件可以包括字符或数字：“%”表示数字0或者多个字符。“_”表示一个字符。,SELECTfirst_nameFROM employeesWHEREfirst_name LIKE S%;,使用比较运算符,可以同时使用(%,_)匹配符:使用 ESCAPE“”标识符查找值中含有“%”和“_”字符的数据。,SELECT last_nameFROM employeesWHERE last_name LIKE _o%;,SELECT last_nameFROM employeesWHERE last_name LIKE%SA_%ESCAPE;,使用比较运算符,SELECT last_name,manager_idFROM employeesWHERE manager_id IS NULL;,使用IS NULL 操作检验是否有Null值,逻辑运算符,如果条件为假，则返回TRUE,NOT,如果前后两个条件有一个为真，则返回TRUE,OR,如果前后两个条件都为真，则返回TRUE,AND,Meaning,Operator,使用逻辑运算符,SELECT employee_id,last_name,job_id,salaryFROM employeesWHERE salary=10000AND job_id LIKE%MAN%;,AND 需要前后两个条件为TRUE:,使用逻辑运算符,OR 需要前后两个条件有一个为TRUE:,SELECT employee_id,last_name,job_id,salaryFROM employeesWHERE salary=10000OR job_id LIKE%MAN%;,使用逻辑运算符,NOT 操作符可以与IN,BETWEEN,LIKE,and NULL配合使用:,.WHERE job_id NOT IN(AC_ACCOUNT,AD_VP).WHERE salary NOT BETWEEN 10000 AND 15000.WHERE last_name NOT LIKE%A%.WHERE commission_pct IS NOT NULL,运算符的优先级规则,可以使用括号改变默认的优先级别,Not equal to,6,NOT,7,AND,8,OR,9,IS NOT NULL,LIKE,NOT IN,4,NOT BETWEEN,5,比较运算符,3,连接运算符,2,四则运算,1,运算符,优先级,排序ORDER BY,排序使用ORDER BY 子句:ASC:升序(默认行为)DESC:倒序如果不使用ORDER BY，相同的两次查询返回的结果顺序可以不一样。使用NULL FIRST和NULL LAST指定NULL值在排序中的位置。ORDER BY 子句中的每个列都可以单独指定排序顺序。,排序ORDER BY,默认的排序为升序：数字是最小的值在前(1 to 999).日期的值最早的在前(01-JAN-11在 01-JAN-12之前)字符按字母表顺序(“A”最前，“Z”最后).Null 值在升序时显示在最后面，在降序时显示在最前面可以使用一个不在SELECT列表中的列来排序,替换变量Substitution Variables,替换变量临时保存数据，使用“&”和“&”符号替换变量可用于：WHERE 条件子句ORDER BY 子句列表达式表名整个SELECT语句,SELECT employee_id,last_name,job_id,SQL函数SQL functions,函数,输入,参数 1,参数2,参数n,函数进行运算,输出,结果,SQL函数的两种类型,单行函数,多行函数,每行返回一个结果,多行返回一个结果,函数,单行函数Single-Row Functions,用于处理数据对象接收参数，然后输出一个结果每返回一行数据就要进行运算每行返回一个结果可以修改数据类型可以被嵌套参数可以是一个列或一个表达式,function_name(arg1,arg2,.),转换函数,字符函数,数字函数,日期函数,一般函数,字符函数Character Functions,字符函数,LOWERUPPERINITCAP,CONCATSUBSTRLENGTHINSTRLPAD|RPADTRIMREPLACE,大小写转换函数,字符处理函数,大小写转换函数Case-Conversion,sql course,LOWER(SQL Course),Sql Course,INITCAP(SQL Course),SQL COURSE,UPPER(SQL Course),结果,函数,字符处理函数,BLACK and BLUE,REPLACE(JACK and JUE,J,BL),10,LENGTH(HelloWorld),6,INSTR(HelloWorld,W),*24000,LPAD(salary,10,*),24000*,RPAD(salary,10,*),HelloWorld,CONCAT(Hello,World),elloWorld,TRIM(H FROM HelloWorld),Hello,SUBSTR(HelloWorld,1,5),结果,函数,数字函数,100,MOD(1600,300),45.93,ROUND(45.926,2),45.92,TRUNC(45.926,2),结果,函数,日期函数,08-SEP-95,NEXT_DAY(01-SEP-95,FRIDAY),28-FEB-95,LAST_DAY(01-FEB-95),19.6774194,MONTHS_BETWEEN(01-SEP-95,11-JAN-94),29-FEB-96,ADD_MONTHS(31-JAN-96,1),Result,Function,01-JUL-03,TRUNC(SYSDATE,MONTH),01-JAN-03,TRUNC(SYSDATE,YEAR),01-AUG-03,ROUND(SYSDATE,MONTH),01-JAN-04,ROUND(SYSDATE,YEAR),转换函数,隐式转换,显式转换,数据类型转换,数据类型的隐式转换,NUMBER,VARCHAR2 or CHAR,DATE,VARCHAR2 or CHAR,To,From,VARCHAR2 or CHAR,NUMBER,VARCHAR2 or CHAR,DATE,To,From,where hire_date 01-JAN-90,where name_varchar 2345,不建议使用隐式转换，可能导致SQL的性能下降,数据类型的显式转换,数字类型,字符类型,TO_CHAR(),TO_NUMBER(),日期类型,TO_CHAR(),TO_DATE(),日期转换为字符,SELECT last_name,TO_CHAR(hire_date,fmDD Month YYYY)AS HIREDATEFROM employees;,数字转换为字符,SELECT TO_CHAR(salary,$99,999.00)SALARYFROM employeesWHERE last_name=Ernst;,字符转换为日期、数字,SQL select TO_number(6,000.00,99,999.00)from dual;TO_NUMBER(6,000.00,99,999.00)-6000,SQL select TO_date(20110818,YYYYMMDD)from dual;TO_DATE(201-18-AUG-11,函数嵌套,单行函数可以嵌套到任意层。函数的运算顺序是从内到外。,F3(F2(F1(col,arg1),arg2),arg3),Step 1=Result 1,Step 2=Result 2,Step 3=Result 3,一般函数,下面的函数与NULL值的使用有关:,条件表达式Conditional Expressions,IF-THEN-ELSE的逻辑判断有两种方法：CASEDECODE,CASE expr WHEN comparison_expr1 THEN return_expr1 WHEN comparison_expr2 THEN return_expr2 WHEN comparison_exprn THEN return_exprn ELSE else_exprEND,DECODE(col|expression,search1,result1,search2,result2,.,default),组函数,对各组的数据行进行运算，每组返回一个值,EMPLOYEES,Maximum salary in EMPLOYEES table,组函数类型,对各组的数据行进行运算，每组返回一个值,AVGCOUNTMAXMINSTDDEV(平均差)SUMVARIANCE(方差),组函数,数据分组 GROUP BY,表EMPLOYEES,在EMPLOYEES 表中，计算每个部门的平均工资,SELECT column,group_function(column)FROM tableWHERE conditionGROUP BY group_by_expressionORDER BY column;,多个列的GROUP BY,SELECT department_id dept_id,job_id,SUM(salary)FROM employeesGROUP BY department_id,job_id ORDER BY department_id;,组函数的使用,在SELECT子句中没有使用组函数的数据列，必须在GROUP BY 子句中列出:,SELECT department_id,COUNT(last_name)FROM employees;,SELECT department_id,job_id,COUNT(last_name)FROM employeesGROUP BY department_id;,组函数的使用,不能在WHERE 子句中限制组，需要使用HAVING。不能在WHERE 子句中使用组函数。,错误：SELECT department_id,AVG(salary)FROM employeesWHERE AVG(salary)8000GROUP BY department_id;,正确：SELECT department_id,AVG(salary)FROM employeesGROUP BY department_idHAVING AVG(salary)8000;,组函数的嵌套,SELECT MAX(AVG(salary)FROM employeesGROUP BY department_id;,多个表的数据查询,SQL:1999标准中的JOIN语法,使用join从多个表中查询数据：table1.column 表示表table1中的列名NATURAL JOIN 用于对两个表中相同的列进行joinJOIN table2 USING column_name 基于指定的列进行对等joinJOIN table2 ON table1.column_name=table2.column_name 基于on条件指定的列进行对等joinLEFT/RIGHT/FULL OUTER 用于外连接CROSS JOIN 进行笛卡尔乘积,SELECTtable1.column,table2.columnFROMtable1NATURAL JOIN table2|JOIN table2 USING(column_name)|JOIN table2 ON(table1.column_name=table2.column_name)|LEFT|RIGHT|FULL OUTER JOIN table2 ON(table1.column_name=table2.column_name)|CROSS JOIN table2;,NATURAL JOIN,NATURAL JOIN 基于两个表中所有相同的列返回两个表中相同的列的值相同的数据行如果两个表中，名字相同的列的数据类型定义不同，那么会返回错误,使用USING进行join,如果两个表中的列名相同，但数据类型不同，那么可以使用using进行对等join.使用USING只能指定的一个名字相同的列,使用ON进行join,使用ON 可以指定任意的条件或相关的列进行join.使用ON 可以增加语句的易读性。,SELECT e.employee_id,e.last_name,e.department_id,d.department_id,d.location_idFROM employees e JOIN departments dON(e.department_id=d.department_id);,使用ON进行join,SELECT employee_id,city,department_nameFROM employees e JOIN departments dON d.department_id=e.department_id JOIN locations lON d.location_id=l.location_id;,额外条件,SELECT e.employee_id,e.last_name,e.department_id,d.department_id,d.location_idFROM employees e JOIN departments dON(e.department_id=d.department_id)AND e.manager_id=149;,如要使用额外条件，可以使用AND或WHERE子句：,SELECT e.employee_id,e.last_name,e.department_id,d.department_id,d.location_idFROM employees e JOIN departments dON(e.department_id=d.department_id)WHERE e.manager_id=149;,或,自连接self-join,自连接是指一个表与自己进行join,SELECT worker.last_name emp,manager.last_name mgrFROM employees worker JOIN employees managerON(worker.manager_id=manager.employee_id);,表EMPLOYEES,查询结果,非对等连接Nonequijoins,SELECT e.last_name,e.salary,j.grade_levelFROM employees e JOIN job_grades jON e.salary BETWEEN j.lowest_sal AND j.highest_sal;,外连接Outer Joins,EMPLOYEES,DEPARTMENTS,There are no employees in department 190.,内连接和外连接,在SQL:1999标准中，只返回匹配记录的JOIN称为内连接(inner join)除返回匹配记录的结果外，还要从左边(右边)的表中返回不相匹配的数据记录的join称为左外(右外)连接(left/right outer join)既返回内连接的结果，又返回左外连接与右外连接的join称为全外连接(full outer join)外连接种类：LEFT OUTERRIGHT OUTERFULL OUTER,左外连接Left Outer Join,SELECT e.last_name,e.department_id,d.department_nameFROM employees e LEFT OUTER JOIN departments dON(e.department_id=d.department_id);,全外连接Left Outer Join,SELECT e.last_name,d.department_id,d.department_nameFROM employees e FULL OUTER JOIN departments dON(e.department_id=d.department_id);,笛卡尔乘积Cartesian Products,以下情况会进行笛卡尔乘积:没有join条件Join条件无效所有数据都符合join条件尽量避免笛卡尔乘积,子查询subquery,问题：谁的工资比员工A高?,哪个员工的工资比员工A高?,主查询:,员工A的工资是多少?,子查询:,子查询语法,子查询首先运行子查询的结果被主查询使用子查询放于小括号中单行操作符必须与单行子查询匹配多行操作符必须与多行子查询匹配,SELECTselect_listFROMtableWHEREexpr operator(SELECTselect_list FROMtable);,Exists,EXISTS 测试子查询是否具有相关的数据行如果找到相关数据行:子查询停止运行返回结果为TRUE如果没有找到相关数据行:返回结果为FALSE继续子查询，直到子查询结束,NOT Exists 和 NOT in,如果子查询返回NULL值，则NOT IN 返回假当没有null值时NOT in 与 not exists效果相同,SELECT department_id,department_nameFROM departments dWHERE NOT EXISTS(SELECT X FROM employees WHERE department_id=d.department_id);,查找哪个部门没有员工(表employees中有一行数据的department_id为null),SELECT department_id,department_nameFROM departments dWHERE department_id NOT IN(SELECT department_id FROM employees);,No rows selected.,IN、ANY、ALL,ANY意谓着大于子查询结果的最小值=ANY 和IN的效果相同ANY中的子查询如果返回0行，则ANY，=ANY，!=ANY，=ANY等操作都判断为假，主查询不返回数据,SELECT employee_id,last_name,job_id,salaryFROM employeesWHERE salary IT_PROG;,IN、ANY、ALL,ALL意谓着大于子查询结果的最大值ALL中的子查询如果返回0行，则ALL，=ALL，!=ALL，=ALL等操作都判断为真，主查询返回符合其他条件的数据,SELECT employee_id,last_name,job_id,salaryFROM employeesWHERE salary IT_PROG;,集合操作符Set Operators,UNION/UNION ALL,A,B,A,B,A,B,INTERSECT,A,B,MINUS,集合操作符说明,两个查询语句中引用的列数量要一致第二个查询的列的数据类型须要与第一个查询的列一一对应可以使用括号改变执行顺序除UNION ALL外，重复行只显示一次列名的显示以第一个查询为准默认情况下，除UNION ALL外，查询结果以升序排序,UNION,SELECT employee_id,job_idFROM employeesUNIONSELECT employee_id,job_idFROM job_history;,UNION ALL,SELECT employee_id,job_id,department_idFROM employeesUNION ALLSELECT employee_id,job_id,department_idFROM job_historyORDER BY employee_id;,INTERSECT,SELECT employee_id,job_idFROM employeesINTERSECTSELECT employee_id,job_idFROM job_history;,MINUS,SELECT employee_idFROM employeesMINUSSELECT employee_idFROM job_history;,列数量与类型匹配,SELECT location_id,department_name Department,TO_CHAR(NULL)Warehouse location FROM departmentsUNIONSELECT location_id,TO_CHAR(NULL)Department,state_provinceFROM locations;,集合操作符与ORDER BY,ORDER BY 只能在整个查询的最后面出现一次ORDER BY 中指定的列，必须在第一个查询语句中指定的列默认情况下，第一个查询语句中的第一个列作为返回结果的排序依据，顺序为升序,多列子查询Multiple-Column Subqueries,主查询,WHERE(MANAGER_ID,DEPARTMENT_ID)IN,子查询结果,100 90102 60124 50,多列子查询Multiple-Column Subqueries,不成对比较成对比较,成对比较子查询,返回与名字为John的员工在同一个部门，且同一个领导的员工,SELECTemployee_id,manager_id,department_idFROMempl_demoWHERE(manager_id,department_id)IN(SELECT manager_id,department_id FROM empl_demo WHERE first_name=John)AND first_name John;,不成对比较子查询,返回与名字为John的员工中任意一个领导相符和任意一个部门相符的员工当名字为John多于一人时，与成对比较子查询的返回结果不同。,SELECT employee_id,manager_id,department_idFROM empl_demoWHERE manager_id IN(SELECT manager_id FROM empl_demo WHERE first_name=John)AND department_id IN(SELECT department_id FROM empl_demo WHERE first_name=John)AND first_name John;,关联子查询,关联子查询用于行与行的处理，关联子查询对每个数据行都要进行 一次。,GET主查询fetch到一行数据,EXECUTE子查询根据这行数据的值进行运算,USE根据子查询的结果，决定是否返回这行数据,关联子查询 vs 嵌套子查询,嵌套子查询：子查询首先执行，且执行一次，返回相关结果主查询使用子查询返回的结果进行一次运算关联子查询：主查询fetch到一行数据根据这行数据，进行子查询运算根据子查询的结果，决定是否返回这行数据重复以上过程，直到主查询结束,使用关联子查询,SELECT column1,column2,.FROM table1 WHERE column1 operator(SELECT column1,column2 FROM table2 WHERE expr1=.expr2);,outer,outer,SELECT e.employee_id,last_name,e.job_idFROM employees e WHERE 2=(SELECT COUNT(*)FROM job_history WHERE employee_id=e.employee_id);,WITH,使用WITH，可以在查询中多次引用相同的子查询使用WITH 返回的结果存放在临时空间在复杂的查询中适当使用WITH，可以提高性能,WITH示例,WITH dept_costs AS(SELECT d.department_name,SUM(e.salary)AS dept_total FROM employees e JOIN departments d ON e.department_id=d.department_id GROUP BY d.department_name),avg_cost AS(SELECT SUM(dept_total)/COUNT(*)AS dept_avg FROM dept_costs)SELECT*FROM dept_costs WHERE dept_total(SELECT dept_avg FROM avg_cost)ORDER BY department_name;,处理数据,DML Data Manipulation Language,DML 的作用:添加新的数据到表中修改表中已有的数据删除表中已有的数据事务：由一系列的DML语句组成，成为一个逻辑工作单元。事务中的语句必须全部成功，否则必须全部回退。,INSERT,INSERT INTOdepartmentsVALUES(100,Finance,NULL,NULL);,INSERT INTOdepartments(department_id,department_name)VALUES(30,Purchasing);,INSERT INTO sales_reps(id,name,salary,commission_pct)SELECT employee_id,last_name,salary,commission_pct FROM employees,INSERT INTO employees(employee_id,hire_date)VALUES(113,SYSDATE);,多表INSERT,多表插入有以下种类:无条件 INSERT有条件 INSERT ALL 旋转(Pivoting)INSERT条件 INSERT FIRST,Unconditional INSERT ALL,INSERT ALL INTO sal_history VALUES(EMPID,HIREDATE,SAL)INTO mgr_history VALUES(EMPID,MGR,SAL)SELECT employee_id EMPID,hire_date HIREDATE,salary SAL,manager_id MGR FROM employees WHERE employee_id 200;,Unconditional INSERT ALL,INSERT ALL INTO sal_history VALUES(EMPID,HIREDATE,SAL)INTO mgr_history VALUES(EMPID,MGR,SAL)SELECT employee_id EMPID,hire_date HIREDATE,salary SAL,manager_id MGR FROM employees WHERE employee_id 200;,Conditional INSERT ALL,INSERT ALL WHEN HIREDATE 01-JAN-95 THEN INTO emp_history VALUES(EMPID,HIREDATE,SAL)WHEN COMM IS NOT NULL THEN INTO emp_sales VALUES(EMPID,COMM,SAL)SELECT employee_id EMPID,hire_date HIREDATE,salary SAL,commission_pct COMM FROM employees,Conditional INSERT FIRST,INSERT FIRSTWHEN salary 5000 THEN INTO sal_low VALUES(employee_id,last_name,salary)WHEN salary between 5000 and 10000 THEN INTO sal_mid VALUES(employee_id,last_name,salary)ELSE INTO sal_high VALUES(employee_id,last_name,salary)SELECT employee_id,last_name,salaryFROM employees,Pivoting INSERT,行列转换,Pivoting INSERT,INSERT ALL INTO sales_info VALUES(employee_id,week_id,sales_MON)INTO sales_info VALUES(employee_id,week_id,sales_TUE)INTO sales_info VALUES(employee_id,week_id,sales_WED)INTO sales_info VALUES(employee_id,week_id,sales_THUR)INTO sales_info VALUES(employee_id,week_id,sales_FRI)SELECT EMPLOYEE_ID,week_id,sales_MON,sales_TUE,sales_WED,sales_THUR,sales_FRI FROM sales_source_data;,UPDATE,UPDATE employeesSET department_id=50WHERE employee_id=113;,UPDATE copy_empSET department_id=110;,UPDATE employeesSET department_id=50WHERE employee_id=null;,UPDATE employeesSET job_id=(SELECT job_id FROM employees WHERE employee_id=205)WHERE employee_id=113;,UPDATE中的关联子查询,UPDATE table1 alias1SET column=(SELECT expression FROM table2 alias2 WHERE alias1.column=alias2.column);,DELETE,DELETE FROM depa