数据库技术培训课程讲义资料

数据库技术培训课程讲义赵正道主要内容数据库基本管理SQL语句编写数据库查询及优化PL/SQL开发数据库基本管理用户管理创建用户修改用户属性删除用户权限管理在数据库中，权限是执行某种特定数据库操作的权利或许可，比如建立数据库连接、创建表或视图等数据库对象、插入、修改和删除数据等操作。用户在进行数据库操作时，数据库将会首先验证该用户是否拥有执行该操作的权限。数据库基本管理权限名称权限描述createtable在用户自己的方案中创建表createanytable在任何方案中创建表alteranytable在任何方案中更改表dropanytable在任何方案中删除表insertanytable在任何方案中执行插入记录操作updateanytable在任何方案中执行更新操作deleteanytable在任何方案中执行删除操作selectanytable在任何方案中执行查询操作lockanytable在任何方案中锁定任何表数据库基本管理权限管理授予权限(grant)查看权限收回权限(revoke)select*fromsystem_privilege_map查询当前数据库系统所有系统权限信息select*fromdba_sys_privswheregrantee='SCOTT'查询所有用户各自所拥有的系统权限信息select*fromsession_privs查询当前用户所拥有的系统权限数据库基本管理角色管理创建角色角色授权授予角色收回角色删除角色SQL语句编写数据定义语言(DDL)语句名称功能/含义create创建表alter修改表drop删除表rename重命名truncate删除表中所有数据SQL语句编写数据操作语言(DML)语句名称功能/含义insert向表中插入记录update修改记录delete删除记录select检索数据SQL语句编写数据控制语言(DCL)语句名称功能/含义grant授予权限及角色revoke收回权限及角色事物控制语句(TCS)语句名称功能/含义commit对数据的修改永久保存到数据库rollback还原对数据的修改，可还原至开始或任意保存点savepoint设置保存点以标识回滚位置SQL语句编写--创建表实例数据类型createtablestudent(snonumber(8),snamevarchar2(10),sexchar(4));类型描述varchar2变长字符串char定长字符串number数值型date日期和时间型SQL语句编写--创建表样本数据customers表结构字段名数据类型和宽度字段含义cidnumber(10)顾客编号namevarchar2(10)姓名phonevarchar2(12)电话car_idvarchar2(10)所购车编号数据1,'悠悠','010-67820675','c039'2,'毛毛','010-82662288','c006'3,'灿灿','13164567898','c002'4,'叶子','010-64532199',null5,'冉冉','010-62931135',nullSQL语句编写--创建表cars表结构字段名数据类型和宽度字段含义car_idvarchar2(10)汽车编号producervarchar2(12)制造商modelvarchar2(10)品牌型号pricenumber(8)所购车编号produce_datedate出厂日期数据c021,'上海大众','途观',175000,'1996-5-28'c002,'德国大众','奥迪100',440000,'1999-11-17'c006,'一汽大众','奥迪A6',540000,'2006-8-9'c039,'东风悦达起亚','千里马',13200,'2003-8-23'c095,'北京奔驰','奔驰280',420000,'2008-12-24'SQL语句编写--插入数据实例createtablestudent(snonumber(8),snamevarchar2(10),sexchar(4));insertintostudentvalues(1001,'zhangqiang','女');SQL语句编写--修改和删除数据实例createtablestudent(snonumber(8),snamevarchar2(10),sexchar(4));updatestudentsetsex='男'wheresno=1001;deletefromstudentwheresname='zhangqiang';SQL语句编写--修改表结构1、增加字段2、改变类型和宽度3、字段重命名4、删除字段5、删除表中所有数据6、删除表SQL语句编写--约束在插入、更新行或者从表中删除行的时候强制表中的数据遵循规则。在Oracle数据库中，可以为表设置约束（Constraint）,来防止无效数据输入到表中。当为某个表定义了约束后，对该表做的所有操作都必须满足约束的要求，否则操作将失败。SQL语句编写--约束约束类型约束类型说明notnull指定列不能包含空值unique指定列的值或者列的组合的值对于表中所有的行必须是唯一的primarykey表的每行的唯一性标识foreignkey在列和引用表的一个列之间建立并且强制一个外键关系check指定一个必须为真的条件SQL语句编写--约束createtableemp(empnonumber(4),enamevarchar2(10)notnull,jobvarchar2(9),mgrnumber(4),hiredatedate,salnumber(7,2),commnumber(7,2),deptnomumber(4)notnull);NOTNULL约束SQL语句编写--约束列级约束createtabledeptment(deptnonumber(2),dnamevarchar2(14)constraintdept_dname_ukunique,locvarchar2(13));UNIQUE约束SQL语句编写--约束表级约束createtabledeptment(deptnonumber(2),dnamevarchar2(14),locvarchar2(13),constraintdept_dname_ukunique(dname));UNIQUE约束SQL语句编写--约束列级约束createtabledeptment(deptnonumber(2)constraintdept_deptno_pkprimarykey,dnamevarchar2(14),locvarchar2(13));Primarykey约束SQL语句编写--约束表级约束createtabledeptment(deptnonumber(2),dnamevarchar2(14),locvarchar2(13),constraintdept_deptno_pkprimarykey(deptno));Primarykey约束SQL语句编写--约束createtableemp(empnonumber(4),enamevarchar2(10),jobvarchar2(9),mgrnumber(4),hiredatedate,salnumber(7,2),commnumber(7,2),deptnomumber(4)constraintemp_deptno_fkforeignkeyreferencesdept(deptno));Foreignkey约束SQL语句编写--约束ondeletecascade选项createtabletest(empnonumber(4),enamevarchar2(10)notnull,deptnonumber(2)notnullconstraintemp_deptno_fkreferencesdept(deptno)ondeletecascade);Foreignkey约束SQL语句编写--约束createtablestudent(snonumber(8),snamevarchar2(10),sexchar(4),agenumber(3),constraintstudent_age_ckcheck(age=0andage=120));Check约束SQL语句编写--约束SELECTtable_name,constraint_name,constraint_typeFROMuser_constraintsWHEREtable_name='EMP';在约束类型constraint_type中，C代表CHECK，P代表PRIMARYKEY，R代表引用完整性，U代表UNIQUE。查看约束SQL语句编写--约束createtablestudent(sidnumber(10),namevarchar2(20));altertablestudentaddconstraintstudent_sid_pkprimarykey(sid);建表后添加约束SQL语句编写--约束altertablestudentdisableconstraintstudent_sid_pk;altertablestudentenableconstraintstudent_sid_pk;禁用/启用约束SQL语句编写--约束altertablestudentdropconstraintstudent_sid_pk;altertablestudentdropprimarykey;删除约束数据库查询及优化--基本查询选择投影连接数据库查询及优化--基本查询基本SELECT语句select字段列表from表名where条件实例select*fromempwheresal2000;数据库查询及优化--基本查询SELECT语句select字段列表from表名where条件实例1、查询出10部门的雇员的姓名和工作职务2、查询工资在1500—5000之间的所有员工数据库查询及优化--基本查询列的别名空值null消除重复行(distinct)between运算符In运算符like运算符数据库查询及优化--基本查询实例1、列出职位为（MANAGER）的员工的编号，姓名2、找出每个员工奖金和工资的总和3、找出奖金高于工资的员工4、找出有奖金的员工的不同工作5、列出各种类别工作的最低工资6、找出没有奖金或者奖金低于500的员工7、列出按年薪降序排序的所有雇员的年薪数据库查询及优化--基本查询查询练习1、查询emp表,显示薪水大于2000,并且工作类别是MANAGER的雇员信息。2、查询emp表,显示年年薪大于30000,工作类别不是MANAGER的雇员信息。3、查询emp表,显示薪水在1500到3000之间,工作类别以’M’开头的雇员信息4、查询emp表,显示佣金为空的并且部门号为20或30的雇员信息。数据库查询及优化--基本查询常用字符函数函数功能用法返回结果lower()转换为小写lower('Hello')helloupper()转换为大写upper('hello')HELLOinitcap()首字母大写initcap('haerbin')HaErBinsubstr()取子串substr('hello',2,2)ellength()求串长length('hello')5concat()字符串连接concat('ab','cd')abcdinstr()定位子串instr('abcd','bc')2replace()替换replace('acd','c','mm')ammdlpad()左侧填充lpad('Smith',8,'*')***Smithrpad()右侧填充rpad('Smith',8,'*')Smith***trim()去掉首尾空格trim('abc')abc数据库查询及优化--基本查询常用数值函数函数功能用法返回结果a