《数据库语言》PPT课件.ppt

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 REVOKETransaction: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 条件表达式aliasColumn aliasColumn“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,allis null,is not nullbetween x and yexists（subquery）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）,函数一般是在数据上执行的，它给数据的转换和处理提供了方便。只是将取出的数据进行处理，不会改变数据库中的值。,通用函数 nvl,SQL 函数（A）,Sql函数可以分为组函数和单行函数。组函数又被称作聚合函数，用于对多行数据进行操作，并返回一个单一的结果，组函数仅可用于选择列表或查询的having子句单行函数对单个数值进行操作，并返回一个值。,两种SQL 函数（A）,单行函数的分类（A）,字符函数全以字符作为参数，返回值分为两类：一类返回字符值，一类返回数字值concat（string1，string2）连接两个字符串initcap（string）string中每个单词首字母大写Lower(string)以小写形式返回stringlpad,rpad 填充字符型数据ltrim/rtrim（string1,string2）trim()substr 提取字符串的一部分substr（string，1，2）upper(string）以大写形式返回stringinstr 字符串出现的位置,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 charnumberVARCHAR2 or char datenumbervarchar2datevarchar2尽管数据类型之间可以进行隐式转换，仍建议使用显示转换函数，以保持良好的设计风格。,转换函数（C）,to_charto_numberto_date,转换函数（A）,TO_CHAR(date,fmt)用于将日期或时间戳转换成varchar2类型字符串，如果指定了格式字符串，则用它控制结果的结果。格式控制串由格式元素构成。格式控制串必须用单引号括起来,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 empselect sysdate,to_char(sysdate,yyyy-mon-dd hh12:mi:ss)from dual;,TO_CHAR 函数操作日期（A）,to_char（num,format）用于将Number类型参数转换为varchar2类型，如果指定了format，它会控制整个转换。,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;,单行函数嵌套,组函数基于多行数据返回单个值,组函数（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子句。,数据分组,SELECTcolumn,group_functionFROMtableWHEREconditionGROUP BY group_by_expressionORDER BY column;使用GROUP BY子句将表分成小组结果集隐式按升序排列,如果需要改变排序方式可以使用Order by 子句,数据分组（A）,出现在SELECT列表中的字段，如果出现的位置不是在组函数中，那么必须出现在GROUP BY子句中select deptno,avg(sal)from emp group by deptnoGROUP 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子句顺序（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);,例子,多表查询,语法规则：SELECTtable1.column,table2.columnFROMtable1,table2WHEREtable1.column1=table2.column2;在 WHERE 子句中写入连接条件当多个表中有重名列时，必须在列的名字前加上表名作为前缀连接的类型：等值连接-Equijoin非等值连接-Non-equijoin外连接-Outer join自连接-Self join,sql:1992语法的连接（A）,select count(*)from empselect count(*)from deptselect emp.empno,dept.loc from emp,dept检索出的行的数目将是第一个表中的行数乘以第二个表中的行数检索出的列的数目将是第一个表中的列数加上第二个表中的列数应该保证所有联结都有where子句，不然数据库返回比想要的数据多得多的数据,笛卡尔积（A）,等值连接（A）,使用 AND 操作符增加查询条件,等值连接,select emp.empno,emp.ename,dept.deptno,dept.loc from emp,dept where emp.deptno=dept.deptnoand emp.deptno=10select emp.empno,emp.ename,dept.deptno,dept.loc from emp,deptwhere emp.deptno=dept.deptnoand ename=JAMES,等值连接,使用表的别名简化了查询select e.empno,e.ename,e.deptno,d.deptno,d.loc from emp e,dept dwhere e.deptno=d.deptno,连接中使用表的别名（A）,为了连接n个表，至少需要n-1个连接条件。,多于两个表的连接（A）,create table managerasselect*from emp;Manager,emp,deptselect e.empno,e.ename,m.ename,d.locfrom emp e,manager m,dept dwhere e.mgr=m.empnoand e.deptno=d.deptnoand e.job=ANALYST,多于两个表的连接,select*from emp,salgradewhere sal between losal and hisal,非等值连接,emp,DEPt,没有雇员属于40，50部门Dept.deptno=emp.deptno,外连接,为了在操作时能保持这些将被舍弃的元组，提出了外连接的概念，使用外连接可以看到不满足连接条件的记录外连接运算符是(+)有左外连接和右外连接左外连接显示左边表的全部行SELECTtable.column,table.columnFROMtable1,table2WHEREtable1.column=table2.column(+);右外连接显示右边表的全部行SELECTtable.column,table.columnFROMtable1,table2WHEREtable1.column(+)=table2.column;,外连接（A）,select e.ename,d.deptno,d.dnamefrom emp e,dept dwhere d.deptno=e.deptno(+);select e.ename,d.deptno,d.dnamefrom emp e,dept dwhere e.deptno(+)=d.deptno;,外连接（A）,查找每个员工的上级主管select worker.ename|works for|manager.enamefrom emp worker,emp managerwhere worker.mgr=manager.empno,自连接（A）,sql1992的语法规则暴露了这样的缺点：语句过滤条件和表连接的条件都放到了where子句中。当条件过多时，联结条件多，过滤条件多时，就容易造成混淆。SQL1999修正了整个缺点，把联结条件，过滤条件分开来，包括以下新的TABLE JOIN的句法结构：CROSSJOINNATURAL JOINUSING子句ON子句LEFT OUTER JOINRIGHT OUTER JOINFULL 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 deptWhere deptno=10;自然连接的结果不保留重复的属性,自然连接（B）,select e.ename,e.ename,e.sal,deptno,d.locfrom emp e join dept d using(deptno)where deptno=20using子句引用的列在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.dnamefrom dept dleft outer join emp eon e.deptno=d.deptnoselect e.ename,d.deptno,d.dnamefrom emp e,dept dwhere d.deptno=e.deptno(+);,左外连接（A）,RIGHT OUTER JOIN中会返回所有右边表中的行，即使在左边的表中没有可对应的列值。select e.ename,d.deptno,d.dnamefrom emp eright outer join dept don e.deptno=d.deptnoselect e.ename,d.deptno,d.dnamefrom emp e,dept dwhere e.deptno(+)=d.deptno;,右外连接（A）,SQL允许多层嵌套。子查询，即嵌套在其他查询中的查询。SELECTselect_listFROMtableWHEREexpr operator(SELECTselect_list FROMtable);理解子查询的关键在于把子查询当作一张表来看待。外层的语句可以把内嵌的子查询返回的结果当成一张表使用。子查询要用括号括起来 将子查询放在比较运算符的右边(增强可读性),子查询（A）,按照子查询返回的记录数，子查询可以分为单行子查询和多行子查询,单行子查询,CLERK,子查询的种类（A）,子查询返回一行记录使用单行记录比较运算符,单行子查询,我们要查询有哪些人的薪水是在整个雇员的平均薪水之上的：首先求所有雇员的平均薪水select avg(sal+nvl(comm,0)from emp 然后求：select ename,empno,sal,sal+nvl(comm,0)from emp where sal+nvl(comm,0)(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 empwhere 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)twhere t.avg_sal between s.losal and s.hisal;,在From子句中使用子查询,Sql的数据更新包括数据插入、删除和修改3个操作.往表中插入数据的语句是insert语句，方式有两种，一种是元组值的插入，一种是查询结果的插入元组值的插入语法如下：INSERT INTOtable(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 tempasselect*from empwhere 1=2执行插入insert into ss select*from emp;,Insert语句（A）,SQL的删除操作是指从基本表中删除元组，语法如下：DELETE FROMtableWHEREcondition;其语义是从基本表中删除满足条件表达式的元组Delete from table 表示从表中删除一切元组如果想从表中删除所有的行，不要使用delete，可使用truncate table语句，完成相同的工作，但是速度更快。,DELETE 语句（A）,Update语句用于修改基本表中元组的某些列，其语法如下：UPDATEtableSETcolumn=value,column=value WHEREcondition;其语义是：修改基本表中满足条件表达式的那些元组的列值，需修改的列值在set子句中指出。,UPDATE 语句（A）,事务（Transaction）是一个操作序列。这些操作要么都做，要么都不做，是一个不可分割的工作单位，是数据库环境中的逻辑工作单位。事务是为了保证数据库的完整性事务不能嵌套在oracle中，没有事务开始的语句。一个Transaction起始于一条DML(Insert、Update和Delete)语句，结束于以下的几种情况：用户显式执行Commit语句提交操作或Rollback语句回退。当执行DDL(Create、Alter、Drop)语句事务自动提交。用户正常断开连接时，Transaction自动提交。系统崩溃或断电时事务自动回退。,事务处理（A）,Commit表示事务成功地结束，此时告诉系统，数据库要进入一个新的正确状态，该事务对数据库的所有更新都以交付实施。每个Commit语句都可以看成是一个事务成功的结束，同时也是另一个事务的开始。Rollback表示事务不成功的结束，此时告诉系统，已发生错误，数据库可能处在不正确的状态，该事务对数据库的更新必须被撤销，数据库应恢复该事务到初始状态。每个Rollback语句同时也是另一个事务的开始。一旦执行了commit语句，将目前对数据库的操作提交给数据库（实际写入DB），以后就不能用rollback进行撤销。执行一个 DDL，dcl语句或从 SQL*Plus正常退出，都会自动执行commit命令。,Commit&Rollback（A）,以前的数据可恢复当前的用户可以看到DML操作的结果其他用户不能看到DML操作的结果被操作的数据被锁住,其他用户不能修改这些数据,提交或回滚前数据的状态（B）,数据的修改被永久写在数据库中.数据以前的状态永久性丢失.所有的用户都能看到操作后的结果.记录锁被释放,其他用户可操作这些记录.,提交后数据的状态（B）,语句将放弃所有的数据修改修改的数据被回退.恢复数据以前的状态.行级锁被释放.,回滚后数据的状态（B）,表约束视图 索引Sequence,数据库的对象（A）,必须以字母开头可包括数字和三个特殊字符（#_$）不要使用oracle的保留字同一用户下的对象不能同名,数据库对象的命名规则（B）,ORACLE常用数据类型（A）,标准的建表语法：CREATE TABLE schema.table(column datatype DEFAULT expr,);在创建新表时，指定的表名必须不存在，否则将出错。使用默认值：当插入行时如果不给出值，dbms将自动采用默认值。在用Create语句创建基本表时，最初只是一个空的框架，用户可以使用insert命令把数据插入表中。,表的创建（A）,设计要求：建立一张用来存储学生信息的表，表中的字段包含了学生的学号、姓名、年龄、入学日期、年级、班级、email等信息，并且为grade指定了默认值为1，如果在插入数据时不指定grade得值，就代表是一年级的学生create table stu(id number(6),name varchar2(20)not null unique,sex number(1)not null,age number(3),sdate date,grade number(2)default 1,class number(4),email varchar2(50);,表的创建,使用子查询创建表的语法CREATE TABLE table column(,column.)AS subquery;新表的字段列表必须与子查询中的字段列表匹配字段列表可以省略create table emp2 as select*from emp;,表的创建（A）,在基本表建立并使用一段时间后，可以根据实际需要对基本表的结构进行修改增加新的列用“alter table add”语句 alter table emp add address varchar(20)新增加的类不能定义为“n