达内数据库五天笔记.doc

达内Oracle的五天笔记 1.1. SQLPLUS的命令 初始化表的位置： set NLS_LANG=american_america.us7ascii (设置编码才可以使用下面脚本) cd $ORACLE_HOME/rdbms cd demo summit2.sql * 我们目前使用的是oralce 9i 9201 版本 select * from v$version; 恢复练习表命令： sqlplus */* summit2.sql /shell要在这个文件的位置。 登陆oracle的命令： sqlplus 用户名/密码 show user 显示当前登陆的身份. set pause on set pause off 分页显示. oracle中默认日期和字符是左对齐，数字是右对齐 table or view does not exist ; 表或示图不存在 edit 命令用于自动打开vi修改刚修执行过的sql的命令。 修改方法二： l 3 先定位到行 c /旧串/新串 执行出错时，利用错误号来查错误: !oerr ora 942 (装完系统后会装一个oerr工具，用于通过错误号来查看错误的具体信息) 想在sql中执行unix命令时，把所有的命令前加一个!就可以， 或者host( 用于sql从切换至unix环境中去) /* 初次使用时注意 * 运行角本时的命令： 先切换到unix环境下，cd $oracle_home cd sqlplus cd demo 下面有两个角本 建表语句。 demobld.sql sqlplus nanjing/nanjing demobid.sql 直接运行角本，后面跟当前目录或者是绝对路径 保存刚才的sql语句： save 命令 第二次保存时要替换之前的角本 save 文件名 replace 把刚才保的sql重新放入 buffer中 spool on 开启记录 spool off 关闭记录 spool 文件名 此命令会把所有的操作存在某个文件中去 常见缩写： nls national language support 国家语言支持 1.2. SQL的结构 |DDL 数据库定义 |DML 数据库管理 SQLCommit rollback |DCL 数据库控制 |grant+revoke 权限管理 表分为：系统表（数据字典），用户表 注：知道数据字典可以更便于使用数据库。 1.3. SQL语句 1.3.1. 纵向投影操作 select select * from student; select name|?|id|? EMPLOYEE from employee; select name,salary*13 from employee; NVL function 如果原来的数值是null的话，由指定数值替代。 select last_name,title,salary*NVL(commission_pct,0)/100 COMM from s_emp; 1.3.2. column使用 column(col) columnName clear/format/heading/justify format column salary format $9999999.00 设置数字显示形式 column name fromat a15; 设置字符串显示15个字符 column salary justify left/right/center 输出格式 column salary heading text 设置显示的字段名 column clear 清除格式 column last_name;显示该字段名所用的格式 column salary justify left format $99,999.00 ( 定义工资的显示形式 ) 1.3.3. order by ORDER BY 排序 升序和降序 ASC 升序(默认) DESC 降序 select * from s_emp order by dept_id , salary desc 部门号升序，工资降序 关键字distinct也会触发排序操作。 select * from employee order by 1; /按第一字段排序 NULL被认为无穷大。order by 可以跟别名。 1.3.4. where 选择操作（横向投影） where 条件一定是根据某个字段来进行过滤操作. select * from s_emp where dept_id=42; 查看部门号为42的所有员工 select * from s_emp where salary1000 查看工资高于1000的所有员工 select salary from s_emp where first_name=Geroge 找出名字为Geroge的员工的工资数 select table_name from user_tables where table_name=S_EMP; 查某个具体表名时，表名的字符串必须要为大写 或者采用 upper(table_name) select * from user_talbes where table_name like s_% escape ?使用转义字符对关键字进行转义。 逻辑运算： BETWEEN AND 在什么之间 NOT BETWEEN AND 注意区间： 是一个闭区间 IN( LIST) 在某个集合中 NOT IN (list) 空值会有影响 (等于list其中任何一个就行， 为提高效率常把比例高的放在前面) LIKE 模糊配置 NOT LIKE 通配比较 IS NULL 是空 AND OR NOT 练习3：(查出s_emp表中所有员工的一年的总收入) select first_name , salary*12*( 1+nvl(commission_pct/100 , 0 ) ) year salary from s_emp; nvl函数 专用于处理空值的影响. 练习4:(找出表名以S_开头的所有表)对于一些特殊字符，要用到escape转义,并不是一定要用，escape后面定义是什么字符为转义字符，那就用哪个字符 select table_name from user_tables where table_name like S_% escape ; 1.3.5. 单行函数 单行函数： (dual 哑表 ) 字符函数： lower 转小写 select lower(SQLPLUS) from dual;-对纯字符串处理的时候 upper 转大写 select upper(sqlplus) from dual; initcap 首字符大写 select initcap(tarena) from dual; concat 连接字符串 select concat(first_name , last_name) from s_emp;等效于| substr 求子串 select substr(tarenasd0603 ,1,6) from dual; (取前六个字符) select substr(tarenasd0603,-2) from dual; (取后两个字符) length 求字符长度 select length(tarena) from dual; nvl 空值函数 两个参数的类型要匹配，统一的，表示：如果有，则返回前面的参数，如果没有就返回后面的参数 eg:select first_name,salary from s_emp where lower(first_name)=george; select first_name , substr(first_name , -2 ) from s_emp; (查出s_emp表中所有用户名字的最后两个字符) 默认的是从左向右，如果是-2则表示从右向左数 练习5： select first_name , salary from s_emp where lower(first_name)=george; 数值函数： round 函数(四舍五入) select round(45.935, 2) from dual; 不带参数时默认0位小数 trunc 函数(截取，不管后面的数字) select trunc(45.995, 1) from dual; 日期函数： oracle数据库中存放时间格式的数据，是以oracle特定的格式存贮的，占7个字节，与查询时显示的时间格式无关，具体哪个字节表示什么，我不太清楚，请高 手补充。存贮的时间包括年月日时分秒，最小精度为秒，不存贮秒以下的时间单位。因此在一些前台支持毫秒级的程序(如PB客户端程序)连接到oracle数据库时应注意这点。查询时显示的时间格式，由会话环境决定，或者由用户定义，与数据库无关。 select sysdate from dual; 从伪表查系统时间，以默认格式输出。 sysdate+(5/24/60/60) 在系统时间基础上延迟5秒 sysdate+5/24/60 在系统时间基础上延迟5分钟 sysdate+5/24 在系统时间基础上延迟5小时 sysdate+5 在系统时间基础上延迟5天 所以日期计算默认单位是天 内置函数： months_between（sysdate,addmonth(sysdate,5)） /两个月有多少天。 add_months(sysdate,-5) 在系统时间基础上延迟5月 add_months(sysdate,-5*12) 在系统时间基础上延迟5年 last_day(sysdate) 一个月最后一天 next_day(sysdate,riday? 下个星期星期几。 round (sysdate,day? 不是四除五入了，是过了中午留下，不过的略掉 trunc(sysdate,onth? 不到一月都省略 例子： 上月末的日期：select last_day(add_months(sysdate, -1) from dual; 本月的最后一秒：select trunc(add_months(sysdate,1),month) - 1/24/60/60 from dual 本周星期一的日期：select trunc(sysdate,day)+1 from dual 年初至今的天数：select ceil(sysdate - trunc(sysdate, year) from dual; 格式转换函数： to_char显示日期： 从数字转化为char to_char(date,格式) 从日期转化为char to_char(date, fmt ) yyyy 2007 年份 year two thousand seven 年份 mm 03 (格式缩写显示也缩写) month march 月份 dy fri 星期几缩写 day Friday 星期几 dd 16 一个月第几天 mi 30 分钟 ss 35 秒钟 hh 18 小时 rr 07 年 最近时间 yy 07 年 当前世纪年份 select to_char(sysdate, yyyy mm dd hh24:mi:ss) from dual; select to_char(sysdate, fmyyyy mm dd hh24:mi:ss) from dual; 查出三月分入职的员工： select first_name,start_date from s_emp where to_char(start_date,mm)=03; to_date表达日期： 字符转日期 select to_date(2000 11 20, yyyy mm dd ) from dual; select round(to_date(10-OCT-06 ,dd-mon-RR) ) from dual; to_number 字符转数字 select to_number(10) from dual ; 函数、表达式、隐式数据类型转换会导致索引用不上，where条件后面只能放单行函数，它起了一个过滤的的作用。 1.3.6. 组函数 group by 分组子句 对分组后的子句进行过滤还可以用having 条件 对分组后的条件进行过滤 where 是对记录进行过滤 avg(distinct | all )求平均值 count(distinct | all )统计 max(distinct | all ） 求最大值 min(distinct | all )求最小值 sum(distinct | all ) 求和 (所有组函数会忽略空值 , avg sum只能作用于数字类型） 求有提成员工的提成的平均值； select avg(nvl(commission_pct ,0 ) ) from s_emp; 有多少人有提成： select count( commission_pct ) from s_emp ; count(*) 用于统计记录数： select sum(commission_pct)/ count(*) from s_emp; 员工分部在多少个不同的部门：count 默认为作all的动作 select count(dept_id) from s_emp; select count(distinct dept_id) from s_emp; 求各个部门的平均工资：group by 子句也会触发排序 select dept_id , avg(salary) aa from s_emp group by dept_id order by aa ; /对平均工资排序 select dept_id , avg(salary) aa from s_emp group by dept_id; 注意：group by 子句后面跟有条件只能是组函数查询的结果中的字段,所以我 们会人为在结果要加入一些group by 要用的字段，如：dept_id可能不想要。 select region_id , count(*) from s_dept 此句会有错，count(*)是单组分组函数，如果加上group by region_id 就是找出同地区的部门数量。 select max(region_id) , count(*) from s_dept; (强制语法上可以正确,但是不能保证结果也会正确） 求各个部门不同工种的平均工资： select dept_id , title, avg(salary) from s_emp group by dept_id , title ; 哪些部门的平均工资比2000高: select dept_id, avg(salary) aa from s_emp group by (dept_id) having avg(salary)2000; 除了42部门以外的部门的平均工资: select dept_id , avg(salary) from s_emp group by (dept_id ) having dept_id!=42; select dept_id , avg(salary) from s_emp where dept_id!=42 group by (dept_id ) ;(此种sql效率要高，先过滤) 再计算） where 单行函数。 having 组函数。 求各个部门的平均工资: / 这样统计不详细 select max() , avg (s.salary) from s_emp s, s_dept d where s.dept_id=d.id group by ; /*这问题很经典，为了过 oracle sql 语法关而写max() * select max() , avg(e.salary) , max() from s_emp e, s_dept d , s_region r where e.dept_id = d.id and d.region_id=r.id group by d.id ; 1.3.7. 多表连接 多表连接操作： 两表没有任何关联时会产生迪卡尔机： select first_name , name from s_emp , s_dept; 等值连接： 练习一:查看员工的姓名和员工部门号:(要考虑到表中实际数据中空值的影响） select first_name , name from s_emp e, s_dept d where e.dept_id=d.id; 同时起了别名 select first_name , name from s_emp e, s_dept d where e.dept_id=d.id and e.first_name=George; 具体到哪个人所在的部门 表的两边有空值的话，不会显示出来。 练习二：每个员工所在的部门和部门所在的地区 select first_name , s_, s_ from s_emp, s_dept, s_region where s_emp.dept_id=s_dept.id and s_dept.region_id=s_region.id; 等价于 select first_name,, from s_emp e,s_dept d,s_region r where e.dept_id=d.id and d.region_id=r.id; 等值连接： 练习三:找出每个员工和每个员工的工资级别 select a.ename , a.sal, b.grade from emp a , salgrade b where a.sal between b.losal and b.hisal; select a.ename , a.sal, b.grade from emp a , salgrade b where a.sal=b.losal and a.sal=b.hisal; 自连接： 当一个表的插入行之间有了关系时就发生了又名：内连接) select first_name , manager_id from s_emp; 查出所有员工的部门领导的名称：( 这种sq会少一条记录，总经理没有被配置上） lselect e.first_name , m.first_name from s_emp e , s_emp m where e.manager_id=m.id; /关键是同一张表用不同的别名 外连接： 防止空值忽略，用（+）的一方会模拟一条记录配置另一方)这就称为外连接,不加（+）一个记录都不能少； select e.first_name , m.first_name from s_emp e , s_emp m where e.manager_id=m.id(+); 号放在哪边就表示在哪边补空，来跟对方来匹配，使得数据一个都不会漏掉，这个例子中的领导有可能会没有(最高领导就再没有领导了，所以就 号放在可能出现空值的一边) 标准写法： 显示没有员工的部门select distinct from s_emp e,s_dept d where e.dept_id(+)=d.id and e.dept_id is null 显示有员工的部门： select distinct from s_emp e,s_dept d where e.dept_id=d.id 1.3.8. 子查询 关于子查询 Subqueries找出所有员工中，工资最低的那个员工：( 利用子查询 ） select first_name, salary from s_emp where salary = ( select min(salary) from s_emp) ; select max(first_name), min(salary) from s_emp; /这样写会出错姓名和工资不一致 子查询运行的顺序：右边。 单先运行子查询再运行主查询，子查询一般出现在运算符的值运算符：运算后面只能跟一个值 多值运算符：可以对两个以上的值进行操作 查询谁跟Smith干一样的活：先找出Smith干什么工作 select title from s_emp where last_name=Smith; 再在所有数据中找和Smith工作匹配的人 /下种写法可能还存在bug，没有考虑到数据的全面性有潜在性问题 select last_name , title f s_emp where title =( select title from roms_emp where last_nam ith ) and last_name Smith ; e=Sm/这种写法才考虑的比较全面 select last_name , title from s_emp where title in ( select title from s_emp where last_name=Smith ) and last_name Smith ; 使用子查询时应注意： 单行子查询返回多个结果时会有错误 single-row subquery returns more than one value 查出哪些员工的工资比平均工资低： select e.first_name , e.salary from s_emp e where e.salary1000); 此表中要求c1的值必须要大于1000 才为有效值 . 复制表的语法（仅限于Oracle）： create table child1 as select * from child; 利用已知表建一张新表：只会把非空约束带过来，其它约束要自己添加 create table s_emp_42 as select * from s_emp where dept_id 42;只取要表结构，不想要表中数据的建表方式： create table s_emp_copy as select * fr m s_emp where 1=2; o这是一个小技巧,利用永不等式提取零条数据，在JDBC的学习中会用到 where 1=1 的形式,注意体会） 查看一张表的约束：( 查数据字典示图) desc user_constraints;(这个数据字典中会查到相应的信息） select constraint_name, constraint_type from user_con traints where table_name=S_EMP; P pk R fk sC checU UK k V 这种只定义在示图中(with check option 相当于组示图加了一个约束） O也是出现在示图中非空约束和CHECK都是用C来表示 查看字段约束的方法： desc user_cons_columns; select column_name, position from user_cons_columns where constraint_name=S_EMP_ID_PK ; position 的含义：联合主键，约束名一样。 user_constraints user_cons_columns 两张表的约束名相等，表名相等，两张表一关联就可以查出所需的信息。 select constraint_name , r_constraint_name from user_constraints where constraint_type=R and table_name=S_EMP ; 数据库建立时，数据字典就会建好。 user_constraints; 自己拥有的 all_constraints; 你自己拥有的加上你可以访问的 dba_constraints 所有的 查看当前数据库数据字典的字典（这个示图很重要） desc dict; select table_name form dict where table_name like %cons%; 示图： user_objects; user_tables; select distinct object_type from user_objects; 1.3.13. 介绍事务的概念： commit 提交，此时说明前面所有语句都成功执行 rollback 回退操作，此时会恢复至上一次提交时的状态。 savepoint 设置保存点 注意 insert into 后面可以跟子查询 insert into s_emp_42 select * from s_emp where dept_id =42; UPDATE 修改字段值： update s_emp set dept_id =10 where id =2 ; update s_emp set commission_pct =10 ; 没有where条件时说明是改表中所有的值. 注意：如有外键引用时常会出现外键引用值没有找到等错误？ delete 删除记录命令语法： delete from s_emp where dept_id=42; delete form s_emp ; 没有where条件时说明删除表中所有的值 注意：如有外键引用时，删除一张表时常会出现不能删除的情况， 原因一 是因为此时正在有人操作表中记录 原因二 此表有其他的表引用，没能设及联删除： delete 删除一张大表时空间不释放，非常慢是因为占用大量的系统资源，支持回退操作，空间还被这张表占用着。 truncate table 表名 (删除表中记录时释放表空间) DML 语句： 表级共享锁： 对于操作一张表中的不同记录时，互不影响 行级排它锁：对于一行记录，oracle 会只允许只有一个用户对它在同一时间进行修改操作 wait() 等到行级锁被释放，才进行数据操作 drop一张表时也会对表加锁，DDL排它锁,所以在删除一张表时如果当前还有用户操作表时不能删除表 alter table 命令用于修改表的结构(这些命令不会经常用)： 增加约束： alter table 表名 add constraint 约束名 primary key (字段）; 解除约束：(删除约束) alter table 表名 drop primary key（对于主键约束可以直接用此方法，因为一张表中只有一个主键约束名, 注意如果主键此时还有其它表引用时删除主键时会出错） alter table father drop primary key cascade ; (如果有子表引用主键时，要用此语法来删除主键,这时子表还存在只是子表中的外键约束被及联删除了） alter table 表名 drop constraint 约束名; 怎样取一个约束名：1、人为的违反约束规定根据错误信息获取! 、查询示图获取约束名!) alter table 表名 disable from primary key ; (相当于把一个表的主键禁用) alter table 表名 enable primary key ;（enable 时会自动去检查表的记录是不是符合要求,如果有脏数据时必须要先删除脏数据才可以 enable） * 增加字段： alter table 表名 add(字段字 字段类型) 删除字段： alter table 表名 drop(字段) alter tbale 表名 drop column 字段 ; (8i 以后才支持） 给列改名:920才支持 alter table 表名 rename column 旧字段名 to 新字段名; 修改字段 此时应注意的问题，更改时要看具体值情况之间的转达换， 改为字符类型时，必须要为空) alter table 表名 modify( 字段，类型) 更改表中的字段： update 表名 set 字段 值 where 条件 更改表名 rename 旧表名 to 新表名； 删除表： trucate table 表名:(表结构还在，数据全部删除，释放表所占的空间，不支持回退,常用删除大表） 1.3.14. oralce中产生序列(sequence): create sequence 序列名; 不带参数时默认为从1 开始每次递增 1，oracle中为了提高产生序列的效率一般一次性产生20个序列放入当前会话的序列池中备用以加快效率，序列会出现不连续的动作回退操作不会影响序列取值） sequence 的参数： increment by n 起始值, start with n 递增量, maxvalue n 最大值, minvalue n 最小值，cycle | no cycle 轮回， cache n 绶存(第一次取时会一次取多少个id存起来） 查看 sequence 示