数据库语言.pptx

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,2019/10/27,#,SQL,结构化查询语言,(Structured Query Language),教书育人匠：胡琦光,结构化查询语言,(Structured Query Language),，具有定义、查询、更新和控制等多种功能，是关系数据库的标准语言。,SQL,分类：,数据操纵语言,DML,Data Manipulation Language,:,SELECT INSERT UPDATE DELETE,数据定义语言,DDL,Data definition language,:,CREATE ALTER DROP RENAME TRUNCATE,数据控制语言,DCL,Data Control Language,:,GRANT REVOKE,Transaction:commit rollback savepoint,SQL,语言（,B,）,查看所有表：,select table_name from user_tables;,查看表结构：,describe dept;(,或者,desc dept;),emp,表,雇员表,Empno:,雇员工号,Ename:,雇员名字,Job:,工作。（秘书、销售、经理、分析员、保管）,Mgr(manager):,经理的工号,Hiredate:,雇用日期,Sal:,工资,Comm:,津贴,Deptno:,所属部门号,dept,表,部门表,Deptno:,部门号,Dname:,部门名字,Loc:,地址,salgrade,表,薪水等级,Grade:,等级,losal:,最低工资,hisal:,最高工资,bonus,表,奖金,Ename:,雇员名字，,job:,工作，,sal:,工资,comm:,津贴,实验用数据表,（,B,）,为表添加注释,comment on table emp is,雇员表,;,为列添加注释,comment on column emp.Empno is,雇员工号,;,添加注释,（,B,）,Select-from-where,句型,SELECT DISTINCT*,column alias,.,FROM table,Where,条件表达式,alias,Column alias,Column“alias”,Column as alias,SELECT,（,A,）,检索单个列,select col from tableName;,检索多个列,select col1,col2,col3 from tableName;,检索所有列,select *from tableName;,使用通配符的优点：书写方便、可以检索未知列,使用通配符的缺点：降低检索的性能,给检索出的列起个别名,select job gong zuo from emp;,select job as gong zuo from emp;,SELECT,语言（,A,）,select*from emp;,select empno from emp;,select empno empnumber from emp;,select empno“empnumber”from emp;,select empno as empnumber from emp;,select distinct empno from emp;,select,（,A,）,条件比较,，！,，,=,any,some,all,is null,is not null,between x and y,exists,（,sub,query,）,in,（,list,），,not in,（,list,）,like _,%,escape _%escape,where,（,A,）,select*from emp where comm is null;,select*from emp where comm is not null;,select ename,sal from emp where sal in(800,1250,1500,2000);,select ename,sal from emp where ename in(SMITH,ALLEN,KING);,select ename,sal from emp where sal between 1000 and 2500;,select ename,sal from emp where deptno 10;,where,逻辑复合条件,not,，,and,，,or,计算次序问题的解决，最好用括号进行分组处理,SQL,优化问题：,AND:,把检索结果较少的条件放到后面,OR:,把检索结果较多的条件放到后面,where,（,A,）,select ename,hiredate from emp where hiredate 20-2,月,-81;,select ename,sal from emp where deptno=10 and sal 1000;,select ename,job,deptno from emp where deptno=10 or job=CLERK;,select ename,sal from emp where sal not in(800,1500,2000);,列出,deptno,为,10,或者,30,，并且工资,2000,的所有人。,where,条件表达式中字符串匹配操作符是“,like”,%,通配符,表示任意字符出现任意次数,_,通配符,表示任意字符出现一次,技巧和注意事项：,不能过度使用通配符。如果其他操作符能达到目的，就不要使用通配符。,确实需要使用通配符时，除非绝对必要，否则不要把通配符用到搜索模式最开始处，因为这样搜索起来是最慢的。,select ename from emp where ename like%ALL%;,select ename from emp where ename like _A%;,like,（,A,）,You query the database with this command:SELECT name FROM employee WHERE name LIKE _a%;,Which names are displayed?,A.names starting with aB.names starting with a or AC.names containing a as the second letterD.names containing a as any letter except the first,select test,按照单个列排序,order by col,降序和升序：,order by col desc(asc),按多个列排序,order by col1 desc(asc),col2 desc(asc),order by,（,A,）,select ename,sal from emp order by ename;,select ename,sal from emp order by empno;,select ename,sal from emp order by ename asc;,select ename,sal from emp order by deptno desc;,select ename,sal,deptno from emp order by deptno asc,ename desc;,order by,（,A,）,为什么需要计算字段？,我们经常需要直接从数据库中检索出转换、计算或格式化过的数据；而不是检索出数据，然后再在客户机应用程序中重新格式化。,计算字段并不实际存在于数据库表,Sql,允许,select,子句中出现,+,，,-,，*，,/,以及列名和常数的表达式,拼接字段,(|,+),首选,|(mysql,中,|,表示,or,，一般用,concat(),创建计算字段,（,A,）,select ename|is a|job from emp;,select ename,sal,sal*12 from emp;,select ename,sal,sal*12 +500 from emp;,select ename,sal,sal*,（,12 +500,）,from emp,创建计算字段,Sql,中允许列值为空，空值用保留字,NULL,表示。,NULL,不同与,0,或者空格，它就是代表了一个不确定的内容。任何含有,null,值的数学表达式最后的结果都为空值,select ename,sal,comm,(sal*12)+comm“income”from emp;,可以通过函数,nvl,将,NULL,转换成为一个“,actual value”,select ename,comm,nvl(comm,0)from emp,select ename,sal,comm,(sal*12)+nvl(comm,0)“income”from emp,通用函数,nvl,（,A,）,函数一般是在数据上执行的，它给数据的转换和处理提供了方便。只是将取出的数据进行处理，不会改变数据库中的值。,函数,输入,arg 1,arg 2,arg n,函数执行任务,输出,结果,通用函数,nvl,SQL,函数（,A,）,Sql,函数可以分为组函数和单行函数。,组函数又被称作聚合函数，用于对多行数据进行操作，并返回一个单一的结果，组函数仅可用于选择列表或查询的,having,子句,单行函数对单个数值进行操作，并返回一个值。,函数,单行函数,组函数,两种,SQL,函数（,A,）,转换函数,字符函数,数字函数,日期类型,其他函数,单行函数,单行函数的分类（,A,）,字符函数全以字符作为参数，返回值分为两类：一类返回字符值，一类返回数字值,concat,（,string1,，,string2,）连接两个字符串,initcap,（,string,）,string,中每个单词首字母大写,Lower(string),以小写形式返回,string,lpad,rpad,填充字符型数据,ltrim/rtrim,（,string1,string2,）,trim(),substr,提取字符串的一部分,substr,（,string,，,1,，,2,）,upper(string,）以大写形式返回,string,instr,字符串出现的位置,instr,（,string,，,A,）,length,字符串长度,字符函数（,A,）,select ename,sal from emp where lower(ename)like%a%;,select ename,sal from emp where upper(ename)like%A%;,select substr(Hello,1,3)from dual;,select name,substr(ename,2)from emp;,select ename,length(ename)from emp;,select ename,instr(ename,A)from emp;,select ename,lpad(ename,6)from emp;,select ename,lpad(ename,6,?,)from emp;,Select lpad(ABC,5,#)from dual;,字符函数,数字函数以,NUMBER,类型为参数返回,NUMBER,值,round,（,number,，,n,）,select round(23.652)from dual;,select round(23.652,2)from dual;,select round(23.652,-1)from dual;,trunc,（,number,，,n,）,select trunc(23.652)from dual;,select trunc(23.652,2)from dual;,select trunc(23.652,-1)from dual;,mod,（,x,，,y,）求余数,select mod(13,5)from dual;,ceil(),上取整,select ceil(19.2)from dual;,floor(),下取整,select floor(19.2)from dual;,数字函数（,A,）,Oracle,以内部数字格式存储日期,:,世纪,年,月,日,小时,分钟,秒,sysdate/current_date,以,date,类型返回当前的日期,Add_months(d,x),返回加上,x,月后的日期,d,的值,LAST_DAY(d),返回的所在月份的最后一天,Months_between(date1,date2),返回,date1,和,date2,之间月的数目,日期和时间函数（,A,）,从日期中加或减一个数值,以得当一个日期结果值,select sysdate+2 from dual;,select sysdate-2 from dual;,两个日期相减以便得到他们相差多少天,select ename,round(sysdate-hiredate)/7)weeks from emp where deptno=10,日期和日期时间算术,标量数据可以有类型的转换，转换分为两种，隐式类型转换和显示类型转换。,隐式类型转换可用于：,字符和数字的相互转换,&,字符和日期的相互转换,VARCHAR2 or char,number,VARCHAR2 or char,date,number,varchar2,date,varchar2,尽管数据类型之间可以进行隐式转换，仍建议使用显示转换函数，以保持良好的设计风格。,转换函数,（,C,）,to_char,to_number,to_date,NUMBER,CHARACTER,TO_CHAR,TO_NUMBER,DATE,TO_CHAR,TO_DATE,转换函数,（,A,）,TO_CHAR(,date,fmt,),用于将日期或时间戳转换成,varchar2,类型字符串，如果指定了格式字符串，则用它控制结果的结果。,格式控制串由格式元素构成。,格式控制串必须用单引号括起来,格式元素,含义,YYYY,、,YY,代表四位、两位数字的年份,MM,用数字表示的月份,MON,月份的缩写、对中文月份来说就是全称,DD,数字表示的日,DY,星期的缩写，对中文的星期来说就是全称,HH24,，,HH12,12,小时或者,24,小时进制下的时间,MI,分钟数,SS,秒数,TO_CHAR,函数操作日期,（,A,）,Select to_char(sysdate,dd-mon-yy hh24:mi:ss)Rigth Now from dual;,select ename,hiredate,to_char(hiredate,yyyy/mm/dd)from emp,select sysdate,to_char(sysdate,yyyy-mon-dd hh12:mi:ss)from dual;,TO_CHAR,函数操作日期,（,A,）,to_char,（,num,format,）,用于将,Number,类型参数转换为,varchar2,类型，如果指定了,format,，它会控制整个转换。,控制符,含义,9,代表一位数字，如果该位没有数字则不进行显示，但对于小数点后面的部分仍会强制显示,0,代表一位数字，如果该位没有数字则强制显示,0,$,显示美元符号,L,显示本地货币符号,.,显示小数点,显示千分位符号,TO_CHAR,函数操作数字,（,A,）,select to_char(sal,$99,999.9999)salary from emp where ename=ALLEN;,select to_char(sal,$00,000.0000)salary from emp where ename=ALLEN;,select to_char(123456,99,99,00)from dual;,TO_CHAR,函数操作数字,to_date(String,format),将,char,或,varchar2,类型的,string,转换为,date,类型,Select to_date(04,05,19,10,23,40,yy,mm,dd,hh12,mi,ss)from dual;,select to_date(2004-09-19,yyyy-mm-dd)from dual;,to_number(String,format),将,char,或,varchar2,类型的,string,转换为,number,类型,select to_number($39343.783,$99990.000)from dual;,select to_number(11.231,999.999)from dual;,to_number&to_date,单行函数可被嵌入到任何层,嵌套函数从最深层到最低层求值,F3(F2(F1(col,arg1),arg2),arg3),Step 1=Result 1,Step 2=Result 2,Step 3=Result 3,单行函数嵌套,（,A,）,显示没有上级管理的公司首脑,select ename,nvl(to_char(mgr),no manager),from emp,where mgr is null;,显示员工雇佣期满,6,个月后下一个星期五的日期,Select to_char(next_day(add_months(hiredate,6),Friday),fmDay,Month ddth,YYYY)“review”,from emp order by hiredate;,单行函数嵌套,组函数基于多行数据返回单个值,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,组函数,（,A,）,avg(),返回某列的平均值,min(),返回某列的最小值,max(),返回某列的最大值,sum(),返回某列值的和,count(),返回某列的行数,组函数仅在选择列表和,Having,子句中有效,组函数,（,A,）,在数字类型数据使用,AVG and SUM,函数,select sum(sal),avg(sal),max(sal),min(sal)from emp;,MIN and MAX,适用于任何数据类型,select min(hiredate),max(hiredate)from emp;,组函数除了,count(*),外，都跳过空值而处理非空值,select count(*)from emp;,select count(comm)from emp;,select count(distinct deptno)from emp;,组函数,（,A,）,select avg(comm)from emp;,NVL,函数迫使分组函数包括空值,select avg(nvl(comm,0)from emp;,在分组函数中使用,NVL,函数,创建分组,group by,子句,Group by,子句可以包含任意数目的列。,除组函数语句外，,select,语句中的每个列都必须在,group by,子句中给出。,如果分组列中具有,null,值，则,null,将作为一个分组返回。如果列中有多行,null,值，他们将分为一组。,Group by,子句必须出现在,where,子句之后，,order by,子句之前。,过滤分组（,having,子句,),Where,过滤行，,having,过滤分组。,Having,支持所有,where,操作符。,分组和排序,一般在使用,group by,子句时，应该也给出,order by,子句。,数据分组,SELECT,column,group_function,FROM,table,WHERE,condition,GROUP BY,group_by_expression,ORDER BY,column,;,使用,GROUP BY,子句将表分成小组,结果集隐式按升序排列,如果需要改变排序方式可以使用,Order by,子句,数据分组,（,A,）,出现在,SELECT,列表中的字段，如果出现的位置不是在组函数中，那么必须出现在,GROUP BY,子句中,select deptno,avg(sal)from emp group by deptno,GROUP BY,列可以不在,SELECT,列表中,select avg(sal)from emp group by deptno,不能在,WHERE,子句中使用组函数,.,不能在,WHERE,子句中限制组,.,使用,Having,对分组进行限制,select avg(sal)from emp group by deptno,having avg(sal)1000;,数据分组,（,A,）,子句,说明,是否必须使用,select,要返回的列或表达式,是,from,从中检索数据的表,仅在从表选择数据时使用,where,行级过滤,否,group by,分组说明,仅在按组计算聚集时使用,Having,组级过滤,否,order by,输出排序顺序,否,Select,子句顺序,（,A,）,Sql,语句执行过程：,读取,from,子句中的基本表、视图的数据，,执行笛卡尔积操作,。,选取满足,where,子句中给出的条件表达式的元组,按,group,子句中指定列的值分组，同时提取满足,Having,子句中组条件表达式的那些组,按,select,子句中给出的列名或列表达式求值输出,Order by,子句对输出的目标表进行排序。,Select,子句顺序,（,A,）,select max(sal)from emp group by deptno;,select max(sal),deptno,job,from emp,group by deptno,job;,select avg(sal)from emp,where sal 1200,group by deptno,having avg(sal)1500,order by avg(sal);,例子,EMPLOYEES,DEPARTMENTS,多表查询,语法规则：,SELECT,table1.column,table2.column,FROM,table1,table2,WHERE,table1.column1,=,table2.column2,;,在,WHERE,子句中写入连接条件,当多个表中有重名列时，必须在列的名字前加上表名作为前缀,连接的类型：,等值连接,-Equijoin,非等值连接,-Non-equijoin,外连接,-Outer join,自连接,-Self join,sql:1992,语法的连接,（,A,）,select count(*)from emp,select count(*)from dept,select emp.empno,dept.loc,from emp,dept,检索出的行的数目将是第一个表中的行数乘以第二个表中的行数,检索出的列的数目将是第一个表中的列数加上第二个表中的列数,应该保证所有联结都有,where,子句，不然数据库返回比想要的数据多得多的数据,笛卡尔积,（,A,）,EMPLOYEES,DEPARTMENTS,Foreign key,外键,Primary key,主键,等值连接,（,A,）,使用,AND,操作符增加查询条件,EMPLOYEES,DEPARTMENTS,等值连接,select emp.empno,emp.ename,dept.deptno,dept.loc,from emp,dept,where emp.deptno=dept.deptno,and emp.deptno=10,select emp.empno,emp.ename,dept.deptno,dept.loc,from emp,dept,where emp.deptno=dept.deptno,and ename=JAMES,等值连接,使用表的别名简化了查询,select e.empno,e.ename,e.deptno,d.deptno,d.loc,from emp e,dept d,where e.deptno=d.deptno,连接中使用表的别名,（,A,）,为了连接,n,个表，至少需要,n-1,个连接条件。,EMPLOYEES,LOCATIONS,DEPARTMENTS,多于两个表的连接,（,A,）,create table manager,as,select*from emp;,Manager,emp,dept,select e.empno,e.ename,m.ename,d.loc,from emp e,manager m,dept d,where e.mgr=m.empno,and e.deptno=d.deptno,and e.job=ANALYST,多于两个表的连接,select*,from emp,salgrade,where sal between losal and hisal,非等值连接,emp,DEPt,没有雇员属于,40,，,50,部门,Dept.deptno=emp.deptno,外连接,为了在操作时能保持这些将被舍弃的元组，提出了外连接的概念，使用外连接可以看到不满足连接条件的记录,外连接运算符是,(+),有左外连接和右外连接,左外连接,显示左边表的全部行,SELECT,table.column,table.column,FROM,table1,table2,WHERE,table1.column,=,table2.column(+),;,右外连接,显示右边表的全部行,SELECT,table.column,table.column,FROM,table1,table2,WHERE,table1.column(+),=,table2.column,;,外连接,（,A,）,select e.ename,d.deptno,d.dname,from emp e,dept d,where d.deptno=e.deptno(+);,select e.ename,d.deptno,d.dname,from emp e,dept d,where e.deptno(+)=d.deptno;,外连接,（,A,）,查找每个员工的上级主管,select worker.ename|works for|manager.ename,from emp worker,emp manager,where worker.mgr=manager.empno,自连接,（,A,）,sql1992,的语法规则暴露了这样的缺点：语句过滤条件和表连接的条件都放到了,where,子句中。当条件过多时，联结条件多，过滤条件多时，就容易造成混淆。,SQL1999,修正了整个缺点，把联结条件，过滤条件分开来，包括以下新的,TABLE JOIN,的句法结构：,CROSS,JOIN,NATURAL JOIN,USING,子句,ON,子句,LEFT OUTER JOIN,RIGHT OUTER JOIN,FULL OUTER JOIN,sql:1999,语法的连接,（,A,）,CROSS JOIN,产生了一个笛卡尔积，就象是在连接两个表格时忘记加入一个,WHERE,子句一样,select emp.empno,emp.ename,emp.deptno,dept.loc,from emp,，,dept;,可以使用,CROSS JOIN,来达到相同的结果,select emp.empno,emp.ename,emp.deptno,dept.loc,from emp cross join dept;,交叉连接,（,B,）,NATURAL JOIN,子句基于两个表中列名完全相同的列产生连接,两个表有相同名字的列,数据类型相同,从两个表中选出连接列的值相等的所有行,select*,from emp natural join dept,Where deptno=10;,自然连接的结果不保留重复的属性,自然连接,（,B,）,select e.ename,e.ename,e.sal,deptno,d.loc,from emp e join dept d using(deptno),where deptno=20,using,子句引用的列在,sql,任何地方不能使用表名或者别名做前缀,同样适合,natural,子句,using,创建连接,（,B,）,自然连接的条件是基于表中所有同名列的等值连接,为了设置任意的连接条件或者指定连接的列，需要使用,ON,子句,连接条件与其它的查询条件分开书写,使用,ON,子句使查询语句更容易理解,select ename,dname,from emp join dept on emp.deptno=dept.deptno,where emp.deptno=30;,使用,on,创建连接,（,A,）,检索雇员名字、所在单位、薪水等级：这三个信息在三个表里面，所以只能用多表联结,select ename,dname,grade,from emp,join dept on emp.deptno=dept.deptno,join salgrade on emp.sal between salgrade.losal and salgrade.hisal;,使用,on,创建连接三表连接,在,LEFT OUTER JOIN,中，会返回所有左边表中的行，即使在右边的表中没有可对应的列值。,select e.ename,d.deptno,d.dname,from dept d,left outer join emp e,on e.deptno=d.deptno,select e.ename,d.deptno,d.dname,from emp e,dept d,where d.deptno=e.deptno(+);,左外连接,（,A,）,RIGHT OUTER JOIN,中会返回所有右边表中的行，即使在左边的表中没有可对应的列值。,select e.ename,d.deptno,d.dname,from emp e,right outer join dept d,on e.deptno=d.deptno,select e.ename,d.deptno,d.dname,from emp e,dept d,where e.deptno(+)=d.deptno;,右外连接,（,A,）,SQL,允许多层嵌套。,子查询，即嵌套在其他查询中的查询。,SELECT,select_list,FROM,table,WHERE,expr operator,(SELECT,select_list,FROM,table,);,理解子查询的关键在于把子查询当作一张表来看待。外层的语句可以把内嵌的子查询返回的结果当成一张表使用。,子查询要用括号括起来,将子查询放在比较运算符的右边,(,增强可读性,),子查询,（,A,）,按照子查询返回的记录数，子查询可以分为单行子查询和多行子查询,单行子查询,主查询,子查询,返回,CLERK,多行子查询,CLERK,MANAGER,主查询,子查询,返回,子查询的种类,（,A,）,子查询返回一行记录,使用单行记录比较运算符,Operator,=,=,(select avg(sal+nvl(comm,0)from emp);,此处嵌套的子查询在外层查询处理之前执行,单行子查询,子查询返回多行行记录,使用集合比较运算符,运算符,IN,some,ALL,含义,等于列表中的任何值,将值与子查询返回的任意一个值进行比较,比较子查询返回的每一个值,多行子查询,（,B,）,我们要查在雇员中有哪些人是经理人，也就是说，有哪些人的,empno,号在,mgr,这个字段中出现过，这个时候，应当首先查询,mgr,中有哪些号码，然后再看看有哪些人的雇员号码在此出现：,select empno,ename,from emp,where empno in(,select distinct mgr from emp,);,在多行子查询中使用,in,找出部门编号为,20,的所有员工中收入最高的职员,select*from emp,where sal=all(,select sal,from emp,where deptno=20),and deptno=20,在多行子查询中使用,some all,我们要求每个部门平均薪水的等级，可以这样考虑，首先将每个部门的平均薪水求出来，然后把结果当成一张表，再用这张结果表和,salgrade,表做连接，以此求得薪水等级。,先求出每个部门平均薪水的表,t,。,将,t,和,salgrade,进行关联查询就可以了。,select*from,salgrade s,(select deptno,avg(sal)avg_sal,from emp group by deptno)t,where t.avg_sal between s.losal and s.hisal;,在,From,子句中使用子查询,Sql,的数据更新包括数据插入、删除和修改,3,个操作,.,往表中插入数据的语句是,insert,语句，方式有两种，一种是元组值的插入，一种是查询结果的插入,元组值的插入语法如下：,INSERT INTO,table,(,column,column.,),VALUES,(value,value.,),;,一次插入操作只插入一行,SQL,数据更新,insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno),values(1111,gao,clerk,7902,sysdate,10000,3000,40),此处插入的元组中列的个数、顺序与,emp,的结构完全一致，因此表名之后的列名可以省略不写,insert into emp,values(2222,gaohs,clerk,7902,sysdate,10000,3000,40),可以只插入部分列,insert into emp(empno,ename),values(3333,xiaozhang),但要求,省略的列必须满足下面的条件：,该列定义为允许,Null,值。,在表定义中给出默认值，这表示如果不给出值，将使用默认值。,如果不符合上面两个条件，将会报错。不能成功插入。,Insert,语句,（,A,）,可以用,insert,语句把一个,select,语句的查询结果插入到一个基本表中，语法如下：,Insert into tablename(column,.),select*from tablename2,创建一个临时表,create table temp,as,select*from emp,where 1=2,执行插入,insert into ss select*from emp;,Insert,语句,（,A,）,SQL,的删除操作是指从基本表中删除元组，语法如下：,DELETE FROM,table,WHERE,condition,;,其语义是从基本表中删除满足条件表达式的元组,Delete from table,表示从表中删除一切元组,如果想从表中删除所有的行，不要使用,delete,，可使用,truncate table,语句，完成相同的工作，但是速度更快。,DELETE,语句,（,A,）,Update,语句用于修改基本表中元组的某些列，其语法如下：,UPDATE,table,SET,column,=,value,column,=,valu