《sql基础教学篇》PPT课件.ppt

SQL基础,在这一章里，你将学会怎样使用“结构化查询语言”（SQL来操作数据库。SQL语言是数据库的标准语言。无论何时你要访问一个数据库，你就要使用SQL语言，你将理解怎样用SQL实现数据库查询，你将学会怎样使用这种查询从数据表中取出信息,学习目的,基本的SELECT 命令,SELECT *, column alias,. FROM table;,SELECT 识别什么列 FROM 识别哪个表,一个最简单的SELECT 命令包括两部分： SELECT子句 , 它指示将显示哪些列 FROM子句,标识要提取数据的表,选择所有列,DEPTNO DNAME LOC - - - 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON,SQL SELECT * 2 FROM dept;,DEPTNO LOC - - 10 NEW YORK 20 DALLAS 30 CHICAGO 40 BOSTON,SQL SELECT deptno, loc 2 FROM dept;,选择指定的列,DEPT location 10 NEW YORK 20 DALLAS 30 CHICAGO 40 BOSTON,SQL SELECT deptno as dept, loc location 2 FROM dept;,指定列别名,算数表达式,对NUMBER和DATE型数据可用算数运算创建表达式,运算 + - * /,描述 加法 减法 乘法 除法,使用算数运算,SQL SELECT ename, sal, sal+300 2 FROM emp;,ENAME SAL SAL+300 - - - KING 5000 5300 BLAKE 2850 3150 CLARK 2450 2750 JONES 2975 3275 MARTIN 1250 1550 ALLEN 1600 1900 . 14 rows selected.,运算的优先级,乘法和除法的优先级高于加法和减法 同级运算的顺序是从左到右 表达式中使用括号可强行改变优先级的运算顺序,运算的优先级,SQL SELECT ename, sal, 10*sal+100 2 FROM emp;,ENAME SAL 12*SAL+100 - - - KING 5000 60100 BLAKE 2850 34300 CLARK 2450 29500 JONES 2975 35800 MARTIN 1250 15100 ALLEN 1600 19300 . 14 rows selected.,使用括号,SQL SELECT ename, sal, 12*(sal+100) 2 FROM emp;,ENAME SAL 12*(SAL+100) - - - KING 5000 61200 BLAKE 2850 35400 CLARK 2450 30600 JONES 2975 36900 MARTIN 1250 16200 . 14 rows selected.,定义空值,空值是指不可用，不知道，不适用的值 空值不等于零或空格(0是一个数字，空格是一个字符),SQL SELECT ename, job, comm 2 FROM emp;,ENAME JOB COMM - - - KING PRESIDENT BLAKE MANAGER . TURNER SALESMAN 0 . 14 rows selected.,注意:,所有的数据类型都可以包含空值，但是某些约束(not null和primary key)不允许在列表中使用空值,空值的运算表达式,包括空值的算法表达式等于空,SQL select ename NAME, 12*sal+comm 2 from emp 3 WHERE ename=KING;,NAME 12*SAL+COMM - - KING,排序时空值处理,SQL select ename,job,comm from emp order by comm asc; ENAME JOB COMM - - - TURNER SALESMAN 0 ALLEN SALESMAN 300 WARD SALESMAN 500 MARTIN SALESMAN 1400 SMITH CLERK JAMES CLERK MILLER CLERK,SQL select ename,job,comm from emp order by comm asc nulls first; ENAME JOB COMM - - - BLAKE MANAGER KING PRESIDENT JAMES CLERK TURNER SALESMAN 0 ALLEN SALESMAN 300 WARD SALESMAN 500 MARTIN SALESMAN 1400 12 rows selected.,排序时空值处理,SQL select ename,job,comm from emp order by comm asc nulls last; ENAME JOB COMM - - - TURNER SALESMAN 0 ALLEN SALESMAN 300 WARD SALESMAN 500 MARTIN SALESMAN 1400 SMITH CLERK JAMES CLERK MILLER CLERK FORD ANALYST JONES MANAGER BLAKE MANAGER CLARK MANAGER KING PRESIDENT 12 rows selected.,排序时空值处理,连结操作,将列或字符与其它列连结 用双竖条表示 (|) 产生的结果列是一个字符表达式,使用连结操作,SQL SELECT ename|job AS “Employees“ 2 FROM emp;,Employees - KINGPRESIDENT BLAKEMANAGER CLARKMANAGER JONESMANAGER MARTINSALESMAN ALLENSALESMAN . 14 rows selected.,重记录,缺省情况下查询显示所有行,包括重行,SQL SELECT deptno 2 FROM emp;,DEPTNO - 10 30 10 20 . 14 rows selected.,剔除重复行,使用DISTINCT关键字可删除重行,SQL SELECT DISTINCT deptno 2 FROM emp;,DEPTNO - 10 20 30,注：DISTINCT操作会引起排序，通过排序去掉重复记录！,一个select语句只能用一个distinct,JOB DEPTNO - - CLERK 20 CLERK 20,JOB DEPTNO - - CLERK 20,SQL select job,deptno from emp where deptno=20 and job=CLERK,Select distinct job,deptno from emp where deptno=20 and job=CLERK,限定所选择的记录,使用where子句限定返回的记录 WHERE子句在 FROM 子句后,SELECT DISTINCT *, column alias, . FROM table WHERE condition(s);,使用WHERE子句,SQL SELECT ename, job, deptno 2 FROM emp 3 WHERE job=CLERK;,ENAME JOB DEPTNO - - - JAMES CLERK 30 SMITH CLERK 20 ADAMS CLERK 20 MILLER CLERK 10,比较运算符,运算符 = = !=,含义 等于 大于 大于等于 小于 小于等于 不等于,使用比较运算符,SQL SELECT ename, sal, comm 2 FROM emp 3 WHERE sal=comm;,ENAME SAL COMM - - - MARTIN 1250 1400,其它的比较运算符,运算符 BETWEEN .AND. IN(list) LIKE IS NULL,含义 在两值之间 (包含) 匹配列出的值 匹配一个字符模式 是空值,使用BETWEEN运算符,ENAME SAL - - MARTIN 1250 TURNER 1500 WARD 1250 ADAMS 1100 MILLER 1300,SQL SELECT ename, sal 2 FROM emp 3 WHERE sal BETWEEN 1000 AND 1500;,使用BETWEEN运算符显示某一 值域范围的记录,使用IN运算符,使用IN运算符获得匹配列表值的记录,SQL SELECT empno, ename, sal, mgr 2 FROM emp 3 WHERE mgr IN (7902, 7566, 7788);,EMPNO ENAME SAL MGR - - - - 7902 FORD 3000 7566 7369 SMITH 800 7902 7788 SCOTT 3000 7566 7876 ADAMS 1100 7788,使用LIKE运算符,SQL SELECT ename 2 FROM emp 3 WHERE ename LIKE S%;,使用 LIKE 运算符执行基于通配符的模糊查询，查询包含字符串的所有数据 查询条件可包含文字字符或数字 (%) 可表示零或多个字符 ( _ ) 可表示一个字符,使用LIKE运算符,使用组合方式匹配字符,SQL SELECT ename 2 FROM emp 3 WHERE ename LIKE _A%;,ENAME - JAMES WARD,使用ESCAPE 标识符来查找带特殊符号的字符号,使用 ESCAPE 关键字定义转义符。在模式中，当转义符置于通配符之前时，该通配符就解释为普通字符。 ESCAPE escape_character 允许在字符串中搜索通配符而不是将其作为通配符使用。escape_character 是放在通配符前表示此特殊用途的字符。,使用ESCAPE 标识符来查找带特殊符号的字符号,escape关键字经常用于使某些特殊字符，如通配符：%,_转义为它们原来的字符意义，被定义的转义字符通常使用/ ,但是也可以使用其他的符号。,insert into emp values(1,mike%n_m,1,1,1981-05-01,1,1,10);,select * from emp where ename like %/% escape /;,使用 IS NULL 运算符,使用 IS NULL 运算符测试空值.,SELECT empno, comm FROM emp WHERE comm IS NULL;,EMPNO COMM - - 7369 7566 7698 7782,逻辑运算符,运算符 AND OR NOT,含义 如果组合的条件都是TRUE, 返回TRUE 如果组合的条件 之一是TRUE,返回TRUE 如果下面的条件是FALSE,返回TRUE,使用AND运算符,AND需要条件都是TRUE.,SQL SELECT empno, ename, job, sal 2 FROM emp 3 WHERE sal=1100 4 AND job=CLERK;,EMPNO ENAME JOB SAL - - - - 7876 ADAMS CLERK 1100 7934 MILLER CLERK 1300,使用OR运算符,SQL SELECT empno, ename, job, sal 2 FROM emp 3 WHERE sal=1100 4 OR job=CLERK;,EMPNO ENAME JOB SAL - - - - 7839 KING PRESIDENT 5000 7698 BLAKE MANAGER 2850 7782 CLARK MANAGER 2450 7566 JONES MANAGER 2975 7654 MARTIN SALESMAN 1250 . 14 rows selected.,OR需要条件之一是TRUE,使用NOT运算符,SQL SELECT ename, job 2 FROM emp 3 WHERE job NOT IN (CLERK,MANAGER,ANALYST);,ENAME JOB - - KING PRESIDENT MARTIN SALESMAN ALLEN SALESMAN TURNER SALESMAN WARD SALESMAN,优先级规则,括号将跨越所有优先级规则,次序 运算符 1 所有的比较运算 2 NOT 3 AND 4 OR,优先级规则,ENAME JOB SAL - - - KING PRESIDENT 5000 MARTIN SALESMAN 1250 ALLEN SALESMAN 1600 TURNER SALESMAN 1500 WARD SALESMAN 1250,SQL SELECT ename, job, sal 2 FROM emp 3 WHERE job=SALESMAN 4 OR job=PRESIDENT 5 AND sal1500;,优先级规则,ENAME JOB SAL - - - KING PRESIDENT 5000 ALLEN SALESMAN 1600,使用括号提高优先级（very important） SQL SELECT ename, job, sal 2 FROM emp 3 WHERE (job=SALESMAN 4 OR job=PRESIDENT) 5 AND sal1500;,ORDER BY 子句,使用ORDER BY 子句将记录排序 ASC: 升序,缺省 DESC: 降序 ORDER BY 子局在SELECT指令的最后,SQL SELECT ename, job, deptno, hiredate 2 FROM emp 3 ORDER BY hiredate;,ENAME JOB DEPTNO HIREDATE - - - - SMITH CLERK 20 17-DEC-80 ALLEN SALESMAN 30 20-FEB-81 . 14 rows selected.,降序排序,SQL SELECT ename, job, deptno, hiredate 2 FROM emp 3 ORDER BY hiredate DESC;,ENAME JOB DEPTNO HIREDATE - - - - ADAMS CLERK 20 12-JAN-83 SCOTT ANALYST 20 09-DEC-82 MILLER CLERK 10 23-JAN-82 JAMES CLERK 30 03-DEC-81 FORD ANALYST 20 03-DEC-81 KING PRESIDENT 10 17-NOV-81 MARTIN SALESMAN 30 28-SEP-81 . 14 rows selected.,多个列排序,通过ORDER BY 列表的顺序来排序,SQL SELECT ename, deptno, sal 2 FROM emp 3 ORDER BY deptno, sal DESC;,ENAME DEPTNO SAL - - - KING 10 5000 CLARK 10 2450 MILLER 10 1300 FORD 20 3000 . 14 rows selected.,可使用不在SELECT序列上的列来排序,SQL SELECT ename, deptno, sal 2 FROM emp 3 ORDER BY empno, sal DESC;,SQL SELECT empno, ename, deptno, sal 2 FROM emp 3 ORDER BY empno, sal DESC;,上面的两个语句排序的结果一致,Order by 后可以是字段名,别名,序号,SQL SELECT ename, deptno, sal 2 FROM emp 3 ORDER BY 1, 3 DESC;,SQL SELECT ename name, deptno, sal 2 FROM emp 3 ORDER BY name DESC;,什么是分组函数,分组函数运算每一组记录,每一组返回一个结果,EMP,EMP表中工资的最高值,DEPTNO SAL - - 10 2450 10 5000 10 1300 20 800 20 1100 20 3000 20 3000 20 2975 30 1600 30 2850 30 1250 30 950 30 1500 30 1250,MAX(SAL) - 5000,分组函数的类型,AVG (DISTINCT|ALLn) COUNT ( *|DISTINCT|ALLexpr) MAX (DISTINCT|ALLexpr) MIN (DISTINCT|ALLexpr) STDDEV (DISTINCT|ALLx)（求标准差） SUM (DISTINCT|ALLn) VARIANCE (DISTINCT|ALLx)（求统计方差）,例子：使用AVG 和 SUM 函数,AVG(SAL) MAX(SAL) MIN(SAL) SUM(SAL) - - - - 1400 1600 1250 5600,AVG and SUM 函数只能用于数字类型,SQL SELECT AVG(sal), MAX(sal), 2 MIN(sal), SUM(sal) 3 FROM emp 4 WHERE job LIKE SALES%;,GROUP BY子句,使用GROUP BY子句将表分成小组 组函数忽略空值,可以使用NVL,NVL2,COALESCE 函数处理空值 结果集隐式按升序排列,如果需要改变排序方式可以使用Order by 子句,SELECT column, group_function(column), . FROM table WHERE condition GROUP BY column ORDER BY column;,SELECT COUNT(*) FROM employees WHERE department_id = 50;,使用 COUNT 函数,COUNT(*) 返回表中的行数.,SELECT department_id, AVG(salary) FROM employees GROUP BY department_id ;,使用 GROUP BY 子句,在 SELECT 列表中出现的列，如果没有包含在组函数中,则必须包含GROUP BY 子句中.,SELECT department_id dept_id, job_id, SUM(salary) FROM employees GROUP BY department_id, job_id ;,在多列上使用 GROUP BY 子句,组函数的错误用法,不能在 WHERE 子句中限制组. 限制组必须使用 HAVING 子句. 不能在 WHERE 子句中使用组函数.,SELECT department_id, AVG(salary) FROM employees WHERE AVG(salary) 8000 GROUP BY department_id;,WHERE AVG(salary) 8000 * ERROR at line 3: ORA-00934: group function is not allowed here,Cannot use the WHERE clause to restrict groups,限定组的结果:HAVING子句,使用HAVING子句限定组 记录被分组 应用组函数 匹配HAVING子句的组被显示,SELECT column, group_function FROM table WHERE condition GROUP BY group_by_expression HAVING group_condition ORDER BY column;,使用HAVING子句,SQL SELECT job, SUM(sal) PAYROLL 2 FROM emp 3 WHERE job NOT LIKE SALES% 3 GROUP BY job 4 HAVING SUM(sal)5000 5 ORDER BY SUM(sal);,JOB PAYROLL - - ANALYST 6000 MANAGER 8275,使用 Oracle 的语法连接多个表,使用连接从多个表中查询数据. 在 WHERE 子句中写入连接条件. 当多个表中有重名列时，在列的名字前加上表名作为前缀.,SELECT table1.column, table2.column FROM table1, table2 WHERE table1.column1 = table2.column2;,什么是等值连接?,EMPLOYEES,DEPARTMENTS,SELECT employees.employee_id, employees.last_name, employees.department_id, departments.department_id, departments.location_id FROM employees, departments WHERE employees.department_id = departments.department_id;,使用等值连接查询数据,SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id FROM employees e , departments d WHERE e.department_id = d.department_id;,使用表的别名,使用表的别名简化了查询. 提高了查询的性能.,对多表作等值连接查询,EMPLOYEES,LOCATIONS,DEPARTMENTS,为了连接n个表, 至少需要n-1个连接条件.例如,为了连接三个表,至少需要两个连接条件.,非等值连接,EMPLOYEES,JOB_GRADES,使用非等值连接查询数据,SELECT e.last_name, e.salary, j.grade_level FROM employees e, job_grades j WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;,外连接,EMPLOYEES,DEPARTMENTS,外连接的语法,为了看到那些不匹配的数据，必须使用外连接. 外连接的操作符为 (+).,SELECT table1.column, table2.column FROM table1, table2 WHERE table1.column(+) = table2.column;,SELECT table1.column, table2.column FROM table1, table2 WHERE table1.column = table2.column(+);,SELECT e.last_name, e.department_id, d.department_name FROM employees e, departments d WHERE e.department_id(+) = d.department_id ;,使用外连接,自连接,EMPLOYEES (WORKER),EMPLOYEES (MANAGER),对一个表作自连接查询,SELECT worker.last_name | works for | manager.last_name FROM employees worker, employees manager WHERE worker.manager_id = manager.employee_id ;,子查询的语法,子查询 (内部查询) 在主查询执行前执行一次. 子查询的结果被用于主查询 (外部查询).,SELECT select_list FROM table WHERE expr operator (SELECT select_list FROM table);,SELECT last_name FROM employees WHERE salary (SELECT salary FROM employees WHERE last_name = Abel);,使用子查询,11000,使用子查询时的一些注意事项,子查询必须用括号扩起. 子查询通常在比较条件的右边. 在子查询中的ORDER BY 子句不需要使用 对单行子查询使用单行操作符，多行子查询使用多行操作符.,单行子查询,只返回一行 使用单行比较操作符,Operator = = ,Meaning Equal to Greater than Greater than or equal to Less than Less than or equal to Not equal to,SELECT last_name, job_id, salary FROM employees WHERE job_id = (SELECT job_id FROM employees WHERE employee_id = 141) AND salary (SELECT salary FROM employees WHERE employee_id = 143);,执行单行子查询,ST_CLERK,2600,在HAVING 子句中使用子查询,Oracle 服务器首先执行子查询. Oracle 将返回的结果用于主查询中的 HAVING 子句.,SELECT department_id, MIN(salary) FROM employees GROUP BY department_id HAVING MIN(salary) (SELECT MIN(salary) FROM employees WHERE department_id = 50);,2500,SELECT employee_id, last_name FROM employees WHERE salary = (SELECT MIN(salary) FROM employees GROUP BY department_id);,下面的语句错在哪里?,ERROR at line 4: ORA-01427: single-row subquery returns more than one row,对多行子查询使用了单行比较操作符.,多行子查询,返回多于一行 使用多行比较操作符,操作符 IN ANY ALL,含义 等于列表中的任何值 与子查询返回的每个值进行比较 与子查询返回的每个值进行比较,在多行子查询中使用 ANY 操作符,9000, 6000, 4200,SELECT employee_id, last_name, job_id, salary FROM employees WHERE salary IT_PROG;,SELECT employee_id, last_name, job_id, salary FROM employees WHERE salary IT_PROG;,在多行子查询中使用 ALL 操作符,9000, 6000, 4200,update emp1 b set b.salary=(select sal from emp a where a.empno=b.empno),2数据操作语言,当完成下列操作时，DML 语句被执行: 在表中添加新的行 修改表中的行 从表中删除行 事务（transaction）由被逻辑组织在一起的多个DML语句构成.,INSERT 语句的语法,通过 INSERT语句在表中添加新的行. 一次插入操作只插入一行.,INSERT INTO table (column , column.) VALUES (value , value.);,在表中插入新行,插入一个包括了所有列的行. 在值列表的顺序默认按照表中列的顺序. 可选的,在INSERT子句中列出 需要插入值的列. 字符和日期值需要单引号扩起.,INSERT INTO departments(department_id, department_name, manager_id, location_id) VALUES (70, Public Relations, 100, 1700); 1 row created.,从另一表中拷贝行,写一个包含子查询的INSERT 语句. 不要使用 VALUES 子句. 在子查询中的列数必须匹配 INSERT 语句中的列数.,INSERT INTO sales_reps(id, name, salary, commission_pct) SELECT employee_id, last_name, salary, commission_pct FROM employe