数据库技术培训课程

1、数据库技术培训课程数据库技术培训课程主要内容主要内容数据库根本管理数据库根本管理SQLSQL语句编写语句编写数据库查询及优化数据库查询及优化PL/SQLPL/SQL开发开发数据库根本管理数据库根本管理用户管理用户管理创立用户创立用户修改用户属性修改用户属性删除用户删除用户权限管理权限管理 在数据库中，权限是执行某种特定数据库操作的权利在数据库中，权限是执行某种特定数据库操作的权利或许可，比方建立数据库连接、创立表或视图等数据库对或许可，比方建立数据库连接、创立表或视图等数据库对象、插入、修改和删除数据等操作。用户在进展数据库操象、插入、修改和删除数据等操作。用户在进展数据库操作时，数据库将会首

2、先验证该用户是否拥有执行该操作的作时，数据库将会首先验证该用户是否拥有执行该操作的权限。权限。数据库根本管理数据库根本管理权限名称权限名称权限描述权限描述create table在用户自己的方案中创建表在用户自己的方案中创建表create any table在任何方案中创建表在任何方案中创建表alter any table在任何方案中更改表在任何方案中更改表drop any table在任何方案中删除表在任何方案中删除表insert any table在任何方案中执行插入记录操作在任何方案中执行插入记录操作update any table在任何方案中执行更新操作在任何方案中执行更新操作dele

3、te any table在任何方案中执行删除操作在任何方案中执行删除操作select any table在任何方案中执行查询操作在任何方案中执行查询操作lock any table在任何方案中锁定任何表在任何方案中锁定任何表数据库根本管理数据库根本管理权限管理权限管理授予权限授予权限(grant)(grant)查看权限查看权限收回权限收回权限(revoke)(revoke)select * from system_privilege_map查询当前数据库系统所有系统权限信查询当前数据库系统所有系统权限信息息s e l e c t * f r o m d b a _ s y s _ p r i

4、v s w h e r e grantee=SCOTT查询所有用户各自所拥有的系统权限查询所有用户各自所拥有的系统权限信息信息select * from session_privs查询当前用户所拥有的系统权限查询当前用户所拥有的系统权限数据库根本管理数据库根本管理角色管理角色管理创立角色创立角色角色授权角色授权授予角色授予角色收回角色收回角色删除角色删除角色SQLSQL语句编写语句编写数据定义语言数据定义语言(DDL)(DDL)语句名称语句名称功能功能/含义含义create创建表创建表alter修改表修改表drop删除表删除表rename重命名重命名truncate删除表中所有数据删除表中所有

5、数据SQLSQL语句编写语句编写数据操作语言数据操作语言(DML)(DML)语句名称语句名称功能功能/含义含义insert向表中插入记录向表中插入记录update修改记录修改记录delete删除记录删除记录select检索数据检索数据SQLSQL语句编写语句编写数据控制语言数据控制语言(DCL)(DCL)语句名称语句名称功能功能/含义含义grant授予权限及角色授予权限及角色revoke收回权限及角色收回权限及角色事物控制语句事物控制语句(TCS)(TCS)语句名称语句名称功能功能/含义含义commit对数据的修改永久保存到数据库对数据的修改永久保存到数据库rollback还原对数据的修改，可

6、还原至开始或任还原对数据的修改，可还原至开始或任意保存点意保存点savepoint设置保存点以标识回滚位置设置保存点以标识回滚位置SQLSQL语句编写语句编写-创立表创立表实例实例数据类型数据类型create table student( sno number(8), sname varchar2(10), sex char(4);类型类型描述描述varchar2变长字符串变长字符串char定长字符串定长字符串number数值型数值型date日期和时间型日期和时间型SQLSQL语句编写语句编写-创立表创立表样本数据样本数据customers表结构表结构字段名字段名 数据类型和宽度数据类型和宽度

7、 字段含义字段含义cid number(10) 顾客编号顾客编号name varchar2(10) 姓名姓名phone varchar2(12) 电话电话car_id varchar2(10) 所购车编号所购车编号数据数据1,悠悠悠悠c0392,毛毛毛毛c0063,灿灿灿灿c0024,叶子叶子null5,冉冉冉冉nullSQLSQL语句编写语句编写-创立表创立表cars表结构表结构字段名字段名 数据类型和宽度数据类型和宽度 字段含义字段含义car_id va

8、rchar2(10) 汽车编号汽车编号producer varchar2(12) 制造商制造商model varchar2(10) 品牌型号品牌型号price number(8) 所购车编号所购车编号produce_date date 出厂日期出厂日期数据数据c021,上海大众上海大众,途观途观,175000,1996-5-28c002,德国大众德国大众,奥迪奥迪100,440000,1999-11-17c006,一汽大众一汽大众,奥迪奥迪A6,540000,2006-8-9c039,东风悦达起亚东风悦达起亚,千里马千里马,13200,2003-8-23c095,北京奔驰北京奔驰,奔驰奔驰28

9、0,420000,2008-12-24SQLSQL语句编写语句编写-插入数据插入数据实例实例create table student( sno number(8), sname varchar2(10), sex char(4);insert into student values(1001,zhangqiang,女女);SQLSQL语句编写语句编写-修改和删除数据修改和删除数据实例实例create table student( sno number(8), sname varchar2(10), sex char(4);update student set sex=男男 where sno=

10、1001;delete from student where sname=zhangqiang;SQLSQL语句编写语句编写-修改表构造修改表构造1、增加字段、增加字段2、改变类型和宽度、改变类型和宽度3、字段重命名、字段重命名4、删除字段、删除字段5、删除表中所有数据、删除表中所有数据6、删除表、删除表SQLSQL语句编写语句编写-约束约束 在插入、更新行或者从表中删除行的时候强制表中的数据在插入、更新行或者从表中删除行的时候强制表中的数据遵循规则。遵循规则。 在在Oracle数据库中，可以为表设置约束（数据库中，可以为表设置约束（Constraint）,来防来防止无效数据输入到表中。止无效

11、数据输入到表中。 当为某个表定义了约束后，对该表做的所有操作都必须满当为某个表定义了约束后，对该表做的所有操作都必须满足约束的要求，否则操作将失败。足约束的要求，否则操作将失败。SQLSQL语句编写语句编写-约束约束约束类型约束类型约束类型约束类型说明说明not null指定列不能包含空值指定列不能包含空值unique指定列的值或者列的组合的值对于表指定列的值或者列的组合的值对于表中所有的行必须是唯一的中所有的行必须是唯一的primary key表的每行的唯一性标识表的每行的唯一性标识foreign key在列和引用表的一个列之间建立并且在列和引用表的一个列之间建立并且强制一个外键关系强制一个

12、外键关系check指定一个必须为真的条件指定一个必须为真的条件SQLSQL语句编写语句编写-约束约束create table emp( empno number(4), ename varchar2(10) not null, job varchar2(9), mgr number(4), hiredate date, sal number(7,2), comm number(7,2), deptno mumber(4) not null);NOT NULLNOT NULL约束约束SQLSQL语句编写语句编写-约束约束列级约束列级约束create table deptment( deptno

13、number(2), dname varchar2(14) constraint dept_dname_uk unique, loc varchar2(13) );UNIQUEUNIQUE约束约束SQLSQL语句编写语句编写-约束约束表级约束表级约束create table deptment( deptno number(2), dname varchar2(14), loc varchar2(13), constraint dept_dname_uk unique(dname) );UNIQUEUNIQUE约束约束SQLSQL语句编写语句编写-约束约束列级约束列级约束create table

14、 deptment( deptno number(2) constraint dept_deptno_pk primary key, dname varchar2(14) , loc varchar2(13) );Primary keyPrimary key约束约束SQLSQL语句编写语句编写-约束约束表级约束表级约束create table deptment( deptno number(2), dname varchar2(14), loc varchar2(13), constraint dept_deptno_pk primary key(deptno) );Primary keyPr

15、imary key约束约束SQLSQL语句编写语句编写-约束约束create table emp( empno number(4), ename varchar2(10), job varchar2(9), mgr number(4), hiredate date, sal number(7,2), comm number(7,2), deptno mumber(4) constraint emp_deptno_fkforeign key references dept(deptno);Foreign keyForeign key约束约束SQLSQL语句编写语句编写-约束约束on delete

16、 cascade选项选项create table test( empno number(4), ename varchar2(10) not null, deptno number(2) not null constraint emp_deptno_fk references dept(deptno) on delete cascade );Foreign keyForeign key约束约束SQLSQL语句编写语句编写-约束约束create table student( sno number(8), sname varchar2(10), sex char(4), age number(3)

17、, constraint student_age_ck check(age=0 and age2000;数据库查询及优化数据库查询及优化-根本查询根本查询SELECTSELECT语句语句select 字段列表字段列表from 表名表名where 条件条件实例实例1、查询出、查询出10部门的雇员的姓名和工作职务部门的雇员的姓名和工作职务2、查询工资在、查询工资在15005000之间的所有员工之间的所有员工数据库查询及优化数据库查询及优化-根本查询根本查询列的别名列的别名空值空值nullnull消除重复行消除重复行(distinct)(distinct)betweenbetween运算符运算符In

18、In运算符运算符likelike运算符运算符数据库查询及优化数据库查询及优化-根本查询根本查询实例实例1、列出职位为（、列出职位为（MANAGER）的员工的编号，姓名）的员工的编号，姓名2、找出每个员工奖金和工资的总和、找出每个员工奖金和工资的总和3、找出奖金高于工资的员工、找出奖金高于工资的员工4、找出有奖金的员工的不同工作、找出有奖金的员工的不同工作5、列出各种类别工作的最低工资、列出各种类别工作的最低工资6、找出没有奖金或者奖金低于、找出没有奖金或者奖金低于500的员工的员工7、列出按年薪降序排序的所有雇员的年薪、列出按年薪降序排序的所有雇员的年薪数据库查询及优化数据库查询及优化-根本查

19、询根本查询查询练习查询练习1、查询、查询emp表表,显示薪水大于显示薪水大于2000,并且工作类别是并且工作类别是MANAGER的雇的雇员信息。员信息。2、查询、查询emp表表,显示年年薪大于显示年年薪大于30000,工作类别不是工作类别不是MANAGER的的雇员信息。雇员信息。3、查询、查询emp表表,显示薪水在显示薪水在1500到到3000之间之间,工作类别以工作类别以M开开头的雇员信息头的雇员信息4、查询、查询emp表表,显示佣金为空的并且部门号为显示佣金为空的并且部门号为20或或30的雇员信息。的雇员信息。数据库查询及优化数据库查询及优化-根本查询根本查询常用字符函数常用字符函数函数函

20、数功能功能用法用法返回结果返回结果lower()转换为小写转换为小写lower(Hello)helloupper()转换为大写转换为大写upper(hello)HELLOinitcap()首字母大写首字母大写initcap(ha er bin)Ha Er Binsubstr()取子串取子串substr(hello,2,2)ellength()求串长求串长length(hello)5concat()字符串连接字符串连接concat(ab,cd)abcdinstr()定位子串定位子串instr(abcd,bc)2replace()替换替换replace(acd,c,mm)ammdlpad()左侧填

21、充左侧填充lpad(Smith,8,*)*Smithrpad()右侧填充右侧填充rpad(Smith,8,*)Smith*trim()去掉首尾空格去掉首尾空格trim( ab c )ab c数据库查询及优化数据库查询及优化-根本查询根本查询常用数值函数常用数值函数函数函数功能功能用法用法返回结果返回结果abs()求绝对值求绝对值abs(-4)4sqrt()开平方开平方sqrt(81)9mod()取余数取余数mod(17,3)2round()四舍五入四舍五入round(3,1415,3)3.142ceil()向上取整向上取整ceil(3.14)4floor()向下取整向下取整floor(3.14

22、)3trunc()截断截断trunc(3.1415,3)3.141sign()判断正负判断正负sign(12)1数据库查询及优化数据库查询及优化-根本查询根本查询常用日期函数常用日期函数函数函数功能功能add_months(x,y)在在x日期基础上增加日期基础上增加y个月后的日期个月后的日期last_day(x)返回日期返回日期x当月最后一天的日期当月最后一天的日期months_between()返回日期返回日期x和和y之间相差的月数之间相差的月数round()将日期将日期x四舍五入到四舍五入到y所指定的单位所指定的单位的第一天的第一天trunc()将日期将日期x截断到截断到y所指定的单位的第

23、所指定的单位的第一天一天next_day()计算指定日期计算指定日期x后的第一个星期几后的第一个星期几对应的日期对应的日期数据库查询及优化数据库查询及优化-根本查询根本查询常用分组函数常用分组函数函数函数功能功能sum()求和求和avg()求平均求平均max()求最大值求最大值min()求最小值求最小值count()查询记录个数查询记录个数1、列出各种工作类别的最低薪金，显示最低薪金大于、列出各种工作类别的最低薪金，显示最低薪金大于1500的记录的记录2、列出各个部门的、列出各个部门的MANAGER（经理）的最低薪金（经理）的最低薪金数据库查询及优化数据库查询及优化-复杂查询复杂查询连接查询连

24、接查询等值连接等值连接非等值连接非等值连接自连接自连接外连接外连接子查询子查询数据库查询及优化数据库查询及优化-连接查询连接查询等值连接等值连接 连接查询是通过多个表之间数据的关联性连接查询是通过多个表之间数据的关联性来进展表间记录连接，进而查询数据的，如果来进展表间记录连接，进而查询数据的，如果表间记录的连接是通过相等的字段值为条件建表间记录的连接是通过相等的字段值为条件建立起来的，这种连接称为等值连接。语法格式：立起来的，这种连接称为等值连接。语法格式：select table1.column,table2.columnfrom table1,table2where table1.colu

25、mn1=table2.column2查询所有雇员的编号、姓名、职位、工资、部门号和部门名查询所有雇员的编号、姓名、职位、工资、部门号和部门名select empno,ename,job,sal,emp.deptno,dnamefrom emp,deptwhere emp.deptno=dept.deptno实例实例数据库查询及优化数据库查询及优化-连接查询连接查询非等值连接非等值连接 如果表间连接条件是使用除等号以外的其如果表间连接条件是使用除等号以外的其他比较运算符相连，这种连接称为非等值连接。他比较运算符相连，这种连接称为非等值连接。1、列出所有雇员的雇员名称、部门名称和薪金、列出所有雇员

26、的雇员名称、部门名称和薪金2、列出所有、列出所有“CLERK”（办事员）的姓名及其部门名称（办事员）的姓名及其部门名称3、列出在、列出在“SALES”（销售）部门工作的雇员的姓名（销售）部门工作的雇员的姓名练习练习数据库查询及优化数据库查询及优化-连接查询连接查询非等值连接非等值连接 如果表间连接条件是使用除等号以外的其他比较运算如果表间连接条件是使用除等号以外的其他比较运算符相连，这种连接称为非等值连接。符相连，这种连接称为非等值连接。select empno,ename,sal,gradefrom emp,salgradewhere sallosal and sal(select sal

27、from emp where ename=CLARK)实例实例2、查询部门平均工资高于单位整体工资的部门编号、查询部门平均工资高于单位整体工资的部门编号select deptno 部门编号部门编号,avg(sal) 部门平均工资部门平均工资from empgroup by deptnohaving avg(sal)(select avg(sal) from emp);数据库查询及优化数据库查询及优化-子查询子查询3、查询与、查询与MARTIN或或SMITH职位相同的所有员工信息职位相同的所有员工信息select *from empwhere job in(select job from emp

28、 where ename=MARTIN or ename=SMITH)实例实例4、查询工资高于所有部门的平均工资的员工信息、查询工资高于所有部门的平均工资的员工信息select *from empwhere salall(select avg(sal) from emp group by deptno);数据库查询及优化数据库查询及优化-子查询子查询子查询练习子查询练习1、列出与、列出与“SCOTT”从事相同工作的所有雇员从事相同工作的所有雇员2、列出某些雇员的姓名和薪金，条件是他们的薪金、列出某些雇员的姓名和薪金，条件是他们的薪金等于部门等于部门30中任何一个雇员的薪金中任何一个雇员的薪金3

29、、列出某些雇员的姓名和薪金，条件是他们的薪金、列出某些雇员的姓名和薪金，条件是他们的薪金高于部门高于部门30中所有雇员的薪金中所有雇员的薪金数据库查询及优化数据库查询及优化-查询优化查询优化1、查询条件合理排序、查询条件合理排序select empno,ename,job,sal,dept.deptno,dnamefrom emp,deptwhere emp.deptno=dept.deptno and emp.deptno=20;select empno,ename,job,sal,dept.deptno,dnamefrom emp,deptwhere emp.deptno=20 and e

30、mp.deptno=dept.deptno;数据库查询及优化数据库查询及优化-查询优化查询优化2、连接中使用表的别名、连接中使用表的别名select e.empno,e.ename,e.job,e.sal,e.deptno,d.dnamefrom emp e,dept dwhere e.deptno=d.deptno and e.deptno=20;数据库查询及优化数据库查询及优化-查询优化查询优化3、用、用exists替换替换distinctselect deptno,dname from dept d;where exists (select y from emp e where e.de

31、ptno=d.deptno);select distinct d.deptno,dnamefrom dept d,emp ewhere d.deptno=e.deptno;数据库查询及优化数据库查询及优化-查询优化查询优化4、用、用where替换替换havingselect语句执行顺序是：先语句执行顺序是：先where子句，再子句，再group by子句，然后子句，然后select查查询，再后询，再后having子句，最后是子句，最后是order by子句，因此在进行分组查询时，子句，因此在进行分组查询时，如果过滤条件不涉及分组计算，则应使用如果过滤条件不涉及分组计算，则应使用where替换替

32、换having子句指定过子句指定过滤条件。滤条件。select deptno,avg(sal) from empwhere deptno in(10,20) group by deptno;select deptno,avg(sal) from empgroup by deptno having deptno in(10,20)PL/SQLPL/SQL开发开发-PL/SQL-PL/SQL块块PL/SQL基本结构基本结构declarebeginexceptionend;PL/SQLPL/SQL开发开发-PL/SQL-PL/SQL块块实例实例declare dn scott.emp.deptno%

33、type;begin select deptno into dn from emp where empno=7566; update emp set sal=sal+33 where deptno=dn; commit;exception when NO_DATA_FOUND then insert into exception_log(msg) values(未查到指定员工未查到指定员工); commit;when others then rollback; insert into exception_log(msg) values(修改工资出现异常修改工资出现异常); commit;end

34、;PL/SQLPL/SQL开发开发-PL/SQL-PL/SQL块块create table exception_log( aris_time date default sysdate, msg varchar2(200); PL/SQLPL/SQL开发开发-程序流程控制程序流程控制分支结构分支结构if(条件表达式条件表达式1) then语句序列语句序列1elsif(条件表达式条件表达式2) then语句序列语句序列2.else语句序列语句序列nend if; PL/SQLPL/SQL开发开发-程序流程控制程序流程控制分支结构分支结构 实例实例set serveroutput on;declar

35、evsal emp.sal%type;beginselect sal into vsal from emp where empno=7369;if(vsal=3000) thendbms_output.put_line(需要交纳个人所得税需要交纳个人所得税);elsedbms_output.put_line(不需缴税不需缴税);end if;end;PL/SQLPL/SQL开发开发-程序流程控制程序流程控制循环结构循环结构-for循环循环for in . loop语句序列语句序列.end loop; PL/SQLPL/SQL开发开发-程序流程控制程序流程控制for循环结构循环结构 实例实例se

36、t serveroutput on;declaretotal number(5):=0;beginfor i in 1.100 looptotal:=total+i;end loop;dbms_output.put_line(1-100之间的和：之间的和：|total);end;PL/SQLPL/SQL开发开发-程序流程控制程序流程控制循环结构循环结构-while循环循环while 条件条件 loop语句序列语句序列.end loop; PL/SQLPL/SQL开发开发-程序流程控制程序流程控制while循环结构循环结构 实例实例set serveroutput on;declarei num

37、ber(5):=1;total number(5):=0;beginwhile i=100 looptotal:=total+i;i:=i+1;end loop;dbms_output.put_line(1-100之间的和：之间的和：|total);end;PL/SQLPL/SQL开发开发-程序流程控制程序流程控制练习练习1、打印乘法口诀表、打印乘法口诀表2、打印水仙花数、打印水仙花数3、编写程序，向表中输入、编写程序，向表中输入10000条记录条记录PL/SQLPL/SQL开发开发-存储过程存储过程创建存储过程创建存储过程create or replace procedure (形式参数形式

38、参数)is|asbeginexceptionend;PL/SQLPL/SQL开发开发-存储过程存储过程创建存储过程创建存储过程-实例实例create or replace procedurechange_salary(v_empno in number,v_new_sal in number)isv_name emp.ename%type;beginselect ename into v_name from emp where empno=v_empno;update emp set sal=v_new_sal where empno=v_empno;dbms_output.put_line(

39、v_name|工资修改完毕工资修改完毕);commit;end;PL/SQLPL/SQL开发开发-存储过程存储过程执行存储过程执行存储过程execute set serveroutput on;exec change_salary(7369,8888);删除存储过程删除存储过程drop procedure change_salary;PL/SQLPL/SQL开发开发-存储过程存储过程存储过程练习存储过程练习 编写一个给特殊雇员加薪编写一个给特殊雇员加薪10%的存储过程，同时检查如果的存储过程，同时检查如果已经雇佣该雇员超过已经雇佣该雇员超过60个月，则给他额外加薪个月，则给他额外加薪3000。

40、PL/SQLPL/SQL开发开发-存储过程存储过程crete or replace procedure raise_sal(no in number) asvhiredate date;vsal emp.sal%type;beginselect hiredte,sal into vhiredate,vsal from empwhere empno=no;if months_between(sysdate,vhiredate)60 thenvsal:=nvl(sal,0)*1.1+3000;elsevsal:=nvl(sal,0)*1.1;end if;update emp set sal=vsal where empno=no;end;PL/SQLPL/SQL开发开发-触发器触发器 触发器是与存储过程类似的另外一种保存在数据库触发器是与存储过程类似的另外一种保存在数据库服务器端的服务器端的PL/SQL程序单元，但运行机制存在明显程序单元，但运行机制存在明显差别，触发器不能由用户或应用程序显示调用，而差别，触发器不能由用户或应用程序显示调用，而是与特定的数据表、视图操作或数据库事件联系到是与特定的数据表、视图操作或数据库事件联系到一起，当进行特定操作或发生特定事件时系统