oracle学习笔记(全)

1、户权限 grant resource,connect to briup; conn briup/briup show user 查看系统时间的格式 select sysdate from dual; 将系统时间改为英文的格式 alter session set nls_date_language=english; select sysdate from dual; 执行脚本文件： start 地址/oracle1.sql select table_name from user_tables; 查看表的结构： desc s_dept; SELECT * from s_dept; select l

2、ast_name,salary from s_emp; 查询员工表中所有对应的部门编号？ select last_name,dept_id from s_emp 查询所有员工的年薪 select last_name,salary*12 sal from s_emp; select last_name,salary*12 "Sal" from s_emp; select last_name,salary*12+100 as sal from s_emp; SELECT last_name, salary, 12 * salary + 100 FROM s_emp; SELEC

3、T last_name, salary, 12 * (salary + 100 FROM s_emp; 字符串的连接：| select first_name|last_name from s_emp; select first_name|' of '|last_name "Name" from s_emp; 查看员工的员工id，全名和职位名称， 全名和职位名称合并成一列显示， 且格式为：姓 名，职位名称 select id, first_name|last_name|','|title "Employ" from s_em

4、p; select last_name,salary,commission_pct from s_emp; select last_name,commission_pct, (salary+commission_pct*12 from s_emp; 处理空值：nvl( select last_name,commission_pct, (salary+nvl(commission_pct,0*12 from s_emp; nvl(commission_pct,1 commission_pct如果为空，该函数处理的 结果就是后面设置的值 如果不为空，就是它本身的值 select name from

5、 s_dept; select distinct name from s_dept; distinct:去除重复行 查询所有所在部门的编号和职位,去除重复的 select distinct dept_id,title from s_emp; 登录到sql*plus这种工具的方式 1：sqlplus 用户名/密码 2：sqlplus 用户名 密码 3：打开运行sql命令行 conn 用户名/密码 select dept_id ,title from s_em; 替换： 2:先定位到错误的行 c/emps/emp 查看上次运行的sql语句：l 执行上次的sql语句：/ 追加： 先定位到错误的行 a

6、 追加的内容 插入： select dept_id ,title from s_emp; 2:先定位到要插入语句的行 i 插入的内容 i where dept_id=42; 删除某一行：del 先定位到你要删除的行 del start fileName:执行脚本文件 fileName :执行脚本文件 get fileName:脚本文件 的内容输出到工具上 save fileName:将buffer中sql保存到 文件中 spool fileName select * from s_emp; show user; spool off; SELECT last_name, dept_id, sta

7、rt_date FROM s_emp ORDER BY last_name desc 对工资进行升序排序： select last_name name,salary from s_emp order by salary desc; select last_name,salary from s_emp order by salary desc, last_name asc; select commission_pct,last_name from s_emp order by 1 asc; 限制查询：where 查询41号部门的所有员工的信息 select last_name,dept_id f

8、rom s_emp where dept_id=41; 工资大于2000的员工的信息 select last_name,salary from s_emp where salary>2000; 工资在2000到4000之内这些员工的信息 select last_name,salary from s_emp where salary not BETWEEN 2000 and 4000; 查询41，42，43号部门的员工 in(list select last_name,dept_id from s_emp where dept_id not in(41,42,43; 查询空的时候用 is

9、查询奖金为空的员工的信息 select last_name, commission_pct from s_emp where commission_pct is not null; N 模糊查询like select last_name from s_emp where last_name like '%N%' %:匹配0个或多个 ?:匹配0个或1个 _:匹配单个字符 _briup SELECT last_name FROM s_emp WHERE last_name LIKE '/_%' escape '/' and or 查询41,42号部门

10、的员工， 或者工资大于2000； select last_name,salary,dept_id from s_emp where dept_id in(41,42 or salary>2000; not and or 查询工资大于2000的并在41号部门的， 或者在44号部门的员工 select last_name,dept_id,salary from s_emp where dept_id=41 and salary>2000 or dept_id=44; 第三章：单值函数 dual:虚表 转化成小写 select LOWER('SQL Course' fro

11、m dual; 转化成大写 select UPPER('SQL Course' from dual; Ngao select last_name from s_emp where lower(last_name='ngao' select last_name from s_emp where upper(last_name='NGAO' select INITCAP('SQL Course' from dual; 字符串长度的函数length select length('SQL Course' from dual

12、; 查询员工的全名，并且以大写的形式 显示，并且全名的长度大于30字符 select first_name|last_name name,upper(first_name from s_emp where length(first_name>6; 字符串的连接：concat select concat('good','string' from dual; 取子串的函数substr select substr('string',1,3 from dual; 四舍五入：round select round(46.66,-1 from dual;

13、 45 44.7 44.66 40 50 只舍不取：trunc select trunc(45.83,-2 from dual; 45 45.8 40 取模:mod select mod(900,300 from dual; select sysdate from dual; select MONTHS_BETWEEN(sysdate,'14-8月-08' from dual; select ADD_MONTHS('11-JAN-94',6 from dual; select NEXT_DAY(sysdate,'FRIDAY' from dual

14、; select LAST_DAY('11-JAN-94' from dual; to_char:将数字类型，日期类型转化成 字符串 to_number：将字符类型转化为数字类型 to_date：将字符类型转化为日期类型 select to_char(to_date('2-mar-2007', 'year-month-dd hh24:mi:ss pm' from dual; SELECT last_name, TO_CHAR(start_date, 'fmDdspth "of" Month YYYY fmHH:MI:S

15、S AM' HIREDATE FROM s_emp WHERE start_date LIKE '%91' SELECT last_name, NVL(TO_CHAR(manager_id, 'No Manager' FROM s_emp WHERE manager_id IS NULL; SELECT TO_CHAR(NEXT_DAY (ADD_MONTHS (date_ordered,6, 'FRIDAY', 'fmDay, Month ddth, YYYY' "New 6 Month Review"

16、; FROM s_ord ORDER BY date_ordered; 第四章：多表查询 其实质也是单表的 查询 将我们的多张表通过一定的 条件连接成一张表 连接的时候产生笛卡儿积： 连接的方式：等连接 查询所有员工的ID， 名字和所在部门的名称 s_emp :id s_dept: id 表取表名 如果我们n张表进行连接的时候， 连接条件至少要n-1; select e.id,e.last_name, from s_emp e,s_dept d where e.dept_id = d.id 查询员工的姓名和部门所在 地区的名称(3张表 s_emp,s_dept,s_region s

17、elect e.last_name, from s_emp e,s_dept d,s_region r where e.dept_id=d.id and d.region_id = r.id 查询部门名称包含sa的员工姓名薪水 select e.salary,e.last_name from s_emp e,s_dept d where e.dept_id = d.id and lower( like '%sa%' 查询欧洲销售部门的薪水在 1000到2000的员工信息 s_emp,s_dept,s_region select s_emp.last_na

18、me,s_emp.salary from s_emp ,s_dept,s_region where s_emp.salary between 1000 and 2000 and s_='Sales' and s_='Europe' and s_emp.dept_id = s_dept.id and s_dept.region_id=s_region.id 查询部门名称是5位，该部门员工的 薪水不等于1500， 并按员工的薪水降序排序 select e.last_name,e.salary from s_emp e,s_de

19、pt d where length(=5 and e.salary!=1500 and e.dept_id = d.id order by e.salary desc; 不等连接：连接条件是用 除等号之外其他连接方式 如：between.and,>等 create table s_grade( id number(7 primary key, name varchar2(20, minsal number(7, maxsal number(7; insert into s_grade values(1,'蓝领',0,1000; insert into s_gr

20、ade values(2,'白领',1000,2000; insert into s_grade values(3,'金领'2000,3000; commit; 查询员工的工资的等级的名称 select ,e.last_name,e.salary from s_emp e ,s_grade g where e.salary between g.minsal and g.maxsal 如果外键为null, 外连接： 左外连接 select . from table1,table2 where table1.colum =table2.colum(+ .

21、 右外连接 select . from table1,table2 where table1.colum(+ =table2.colum . 查询所有员工所在部门的名称，但是 需要把所有部门给查询出来 select e.last_name, from s_emp e ,s_dept d where e.dept_id(+=d.id select e.last_name, from s_emp e right join s_dept d on e.dept_id=d.id insert into s_emp values(999,'briup',null,

22、 null,null,null,null,null, null,null,null; 查询员工所在部门的信息， 包括没有部门号的员工 select ,e.last_name from s_emp e,s_dept d where e.dept_id=d.id(+ 标准的sql语句 select ,e.last_name from s_emp e left join s_dept d on e.dept_id=d.id 查询员工所在部门的信息， 没有部门号的员工 所有的部门都查询出来 full join .on select ,e.last_name from

23、 s_emp e full join s_dept d on e.dept_id=d.id 标准的sql语句： full join.on. left join.on. right join.on. 自连接：在同一张当作两张表来使用 查 询员工的上级的信息 select manger.last_name,manger.id from s_emp worker,s_emp manger where worker.manager_id=manger.id(+ 集合连接 union:将上下结果取并集， 去除掉重复的记录 （重复的只显示一次） union all：将上下结果 全部显示 minus：取差集

24、 A-B intersect：取交集 rownum：记录行号 只等于1 可以小于任何正整数 不能大于任何正整数 select last_name,salary from s_emp where rownum<=6 minus select last_name,salary from s_emp where rownum<=2 查询员工表中第三条到第六条记录 rowid:存放每条记录在磁盘的位置 select rowid,last_name from s_emp; 第五章：组函数 讲一组数据处理完之后返回 一条记录，某一列相等的值 进行分组计算 avg( sum( max( min(

25、 count(：参数可以是列名，常量，变量 查询所有员工的平均工资，最高工资 最低工资，还有有多少个员工 select avg(salary,max(salary ,min(salary,count(* from s_emp; 查询每个部门的平均工资 select avg(salary,dept_id from s_emp group by dept_id order by avg(salary desc; 练习：查看各个部门的最高工资 select max(salary,dept_id from s_emp group by dept_id 查看各个部门的员工数 select count(*

26、,dept_id, last_name from s_emp group by dept_id 注意：如果在select中出现的列 并且这一列不包含在组函数中 就必须出现在group by 查询各个部门各个职称的平均 薪水和最大薪水,并且平均薪水 大于2000的部门id； select avg(salary,max(salary ,title,dept_id from s_emp having avg(salary>500 group by dept_id,title order by avg(salary; 查询(where-group by-having-order by 对分组语句

27、进行过滤使用having 分组不能出现where子句中 查询title中不包含vp字符串的 每个职位的平均薪水，并对薪水进行 降序排列，并且每个职位的总薪水 大于5000 select avg(salary,title from s_emp where lower(title not like '%vp%' group by title having sum(salary>5000 order by avg(salary desc; 第六章：子查询 一条sql语句中嵌套了一条或 多条sql语句 子查询的结构： 子查询出现情况一： 比较值不确定，需要另外 一个select语

28、句执行后 才能得到，使用子查询 select. from. where columName 操作符 （ select. from. where. group by. having. order by. ） group by. having . order by. 查询和Ngao在同一个部门的 员工id,name 第一步：分析需求 s_emp 第二步：查询Ngao所在的部门 select dept_id from s_emp where last_name='Ngao' 第三步，嵌套 select id,last_name,dept_id from s_emp where dep

29、t_id=( select dept_id from s_emp where last_name='Ngao' ; 查看工资大于Chang员工工资的 所有员工的id和名字。 select last_name,id from s_emp where salary>( select salary from s_emp where last_name='Chang' ; 查看职位名称和名字为Chang的员工 一样的所有员工id和名字 select last_name,id from s_emp where title=( select title from s_

30、emp where last_name='Chang' 查看员工工资小于平均工资的所 有员工的id和名字 select last_name,id from s_emp where salary<( select avg(salary from s_emp ; 查看部门和名字为Chang的部门相同 或者区域ID为2的部门信息 select id,name from s_dept where id in ( select dept_id from s_emp where last_name='Chang' or region_id=2 ; 查询员工的工资，他们

31、的工资在41号 部门最大工资和最低工资之间 select salary,last_name from s_emp where salary between ( select min(salary from s_emp where dept_id=41 and ( select max(salary from s_emp where dept_id=41 查看部门平均工资大于32号部门平均 工资的部门id select dept_id,avg(salary from s_emp group by dept_id having avg(salary>( select avg(salary f

32、rom s_emp where dept_id=32 ; 查询工资大于smith所在部门平均工 资的员工的 select id,last_name from s_emp where salary>( select avg(salary from s_emp where dept_id=( select dept_id from s_emp where last_name='Smith' （二）子查询出现情况二：from 将select语句查询出来的结果， 当做一张表嵌套到另外一 个select语句中 查看部门平均工资最高的部门id select dept_id from

33、(select dept_id, avg(salary dept_avg_salary from s_emp group by dept_id dept_avg_salary_tb where dept_avg_salary = ( select max(dept_avg_salary from (select dept_id, avg(salary dept_avg_salary from s_emp group by dept_id dept_avg_salary_tb ; 1: select dept_id, avg(salary dept_avg_salary from s_emp g

34、roup by dept_id 练习： 1.查看薪资大于Chang员工薪资的员工信息 select id,last_name,salary from s_emp where salary>( select salary from s_emp where last_name='Chang' ; 2.查看薪资大于Chang员工薪资或者所 在部门在3号区域下的员工的信息 select e.id,e.last_name,e.salary from s_emp e ,s_dept d where e.salary>( select salary from s_emp wher

35、e last_name='Chang' and e.dept_id=d.id or d.region_id=3 3.查看薪资大于Chang所在区域平均工 资的员工信息 select id,last_name,salary from s_emp where salary>( select avg(salary from s_emp where dept_id in ( select id from s_dept where region_id in( select d.region_id from s_emp e,s_dept d where e.dept_id=d.id

36、and e.salary>( select salary from s_emp where last_name='Chang' ; 4.查看薪资高于Chang员工经理薪资的 员工信息 select id,last_name from s_emp where salary>(select salary from s_emp where id=(select manager_id from s_emp w here last_name='Chang' 5.查看薪资大于Chang员工经理的经理 所在区域的最低工资的员工的信息 select id,last_

37、name,salary from s_emp where salary>( select min(salary from s_emp e1,s_dept d1 where e1.dept_id=d1.id and d1.region_id=( select d.region_id from s_emp e,s_dept d where e.dept_id=d.id and e.id=( select manager_id from s_emp where id=( select manager_id from s_emp where last_name='Chang' 6

38、.查看客户负责员工中工资大于Chang 员工的工资的员工信息 /1Chang员工的工资 select salary from s_emp where last_name='Chang' /2客户负责员工s_customer sales_req_id-s_emp id select sales_req_id from s_customer /3嵌套 select id,last_name,salary from s_emp where id in (select sales_rep_id from s_customer and salary>(select salary f

39、rom s_emp where last_name='Chang' SELECT id, last_name, salary FROM s_emp WHERE dept_id = &department_number; 第八章：数据建模和数据库的设计 需求：需求分析 概要的设计 coding testing 上市 瀑布模式 需求-概要的设计(ER模型 -表的实例图-建表 关系型数据库中的关系： 一对一：丈夫和妻子 一对多：飞机和乘客 多对多：每一个人都有很多种技术 每一种技术被很多人所 掌握 实线和虚线： maybe ,must be 订单表和订单明细表 订单表 有很多

40、个订单明细 数据完整性的约束：实体完整性 参照性完整性 自定义的完整性check 对列的数据类型 设计数据库的时候 实体：生活中存在的东西 属性：不可再分 关系： #：代表主键 *：非空 o:代表任意的，没有任何规定 联合主键： order item id id 1 1 2 2 3 3 4 4 5 5 1 1 1 2 1 3 2 4 1 1 1 2 1 3 2 1 2 2 对于1对1关系，外键放在任何一方都可以 对于1对多关系，外键放在多的这方 对于多对多这种关系 看着两个一对多的关系 建一张桥表 teacher student id name id name teacher_student

41、teacher_id-teacher(id student_id-student(id 第九章 creating table CREATE TABLE schema.table (column datatype DEFAULT expr column_constraint, . table_constraint; schema.:该值就是用户名 如果没有这个，就把该表创建在 你登录时候的用户下 DEFAULT expr：如果你插入数据的数据， 该列没有传入任何的值，存入数据库的 值就是expr create table student( id number(7 primary key , na

42、me varchar2(20 not null, age varchar2(5 default 0, gender varchar2(5 , adddress varchar2(50, constraint student_id_pk primary key(id,name, constarint student_gender_nn check (gender in('male','female' ; drop student; SYS_C. select constraint_name from user_constraints; 取表名的规定： 1：必须以字

43、母开头 2：长度 1到30字符之间 3：有字母，数字，_,$,#组成 4：在同一用户下，不能出现同名的表 5：不能oracle中的保留字 约束：表级约束 列级约束 联合主键，联合外键，联合唯一必须 使用我们的表级约束 非空not null必须使用列级约束 其他的情况，可以使用这两种约束的 其中一种 主键约束：primary key 外键约束：foregin key 非空约束：not null 选择性约束：check 唯一性约束：unique 数据类型： 数字类型：number number(7:有7为有效数0000000-9999999 number(5,2:有5为有效数， 包含2位小数 最大

44、值999.99 字符类型：varchar,varchar2:可变长度 的字符串 char：不可变长度的字符串 varchar2(10 char(10 hello 定义定长的字符串，使用char char(18） clob：最大的空间为2G 日期类型：date 二进制类型：blob order(id,name item(id,order_id,name; create table order( id number(7 , id1 number(7, name varchar2(20 not null, constraint order_pk2 primary key(id,id1 create

45、table item( id number(7, name varchar2(30, id1 number(7, order_id number(7 , constraint item_pk2 primary key(id,order_id,id1, constraint item_fk2 foreign key(order_id,id1 references order(id,id1 create table test( id number(7 primary key, name varchar2(20 not null, age number(5 default 0, gender var

46、char2(10 check (gender in('male','female', phone char(11 unique, address varchar2(30 主键约束： id number(7 constraint test_id_pk primary key, 主键的列级约束的格式： column typedate constraint constraintName constraintType 主键的表级约束的格式: constraint constraintName constraintType(column constraint test_i

47、d_pk primary key(id, 非空约束：not null 非空的列级约束的格式： column datetype constraint constraintName not null 如： name varchar2(20 constraint test_name_nn not null, 选择性约束：check 选择性约束的列级约束 column datetype constraint constraintName check ( column in(list 如： gender varchar2(10 constraint test_gender_ck check( gende

48、r in('male', 'female' 选择性约束的表级约束的格式 constraint constraintName column check(column in(list 唯一性约束：unique 唯一性约束的列级约束的格式: column dateType constraint constraintName unique 如：phone char(11 constarint test_phone_uk unique 唯一性约束的表级约束的格式: constraint constraintName unique(column 如：constraint t

49、est_phone_uk unique(phone 外键约束：foreign key 外键约束的列级约束的格式: column dateType constraint constraintName references pktableName (pkcolumn 外键约束的表级约束的格式: constraint constraintName foreign key(column references pktableName(pkcolumn 联合外键的格式： constraint constraintName foreign key(column1,column2. references pk

50、tableName (pkcolumn1,pkcolumn2. 注意：联合外键必须是另外一张的联合 主键。 联合主键的格式： constraint constraintName primary key(column1,column2. 联合唯一的格式： constraint constraintName unique (column1,column2. 学生表（id,name,age,gender,address phone,classId,course_id） 老师表（id,name,salary,age） 课程表（id,name,time,teacher_id） create table

51、student( id number(7 primary key, name varchar2(10 not null, age number(5 default 0, gender varchar2(10 check( gender in('male', 'female' , address varchar2(20, phone char(11 unique, classId number(5, create table course( id number(7 primary key, name varchar2(20 not null create tabl

52、e s_c( id number(7, student_id number(7 constraint s_c_id1_fk references student(id, couser_id number(7 constraint s_c_id2_fk references course(id, constraint s_c_pk3 primary key(id,student_id, couser_id select table_name from user_tables select count(* from dictionary; 626张 select object_name from

53、user_objects where object_type='TABLE' desc user_objects; 查询数据对象的类型： select distinct object_type from user_objects; 查看约束： select constraint_name from user_constraints; 查看约束建在哪一列：user_cons_columns SELECT constraint_name, column_name FROM user_cons_columns WHERE table_name = 'S_EMP' 第十

54、一章：DML语言 insert: insert into tableName(column1,column2. values(value1,value2. create table insert_test( id number(7 primary key, name varchar2(20 not null, age number(5 default 0 /插入一条记录 insert into insert_test(id,name values( 2,'test2' 注意：主键约束，非空唯一 外键约束 create table insert_test2( id number(

55、7 primary key, test1_id number(7 constraint test2_id_fk references insert_test(id ; insert into insert_test2 values(2,3; create table table1 as select id,last_name,salary,dept_id from s_emp where dept_id=42; insert into table1 select id,last_name,salary,dept_id from s_emp where dept_id=41; 更新update:

56、 update table1 set salary=5000 where dept_id=41; update table1 set id=2 where last_name='Ngao' (1,null (2,1 update insert_test2 set id=1 where test1_id=1; 注意：修改的时候注意主键约束和外键约束 update insert_test2 set test1_id=5 where id=1; update insert_test set name='briup',age=20 where id=1; 删除delet

57、e delete from tableName where column=value delete table1 where id=1; delete,truncate: 共同点：都是删除表中的数据，不删除 表的结构 不同点：delete可以回滚,DML truncate不可以回滚,DDL truncate效率高 drop table tableName:删除表中的数据和 表结构 不可以回滚的 commit; insert into insert_test2 values(5,2; DML语言：不能自动提交 事务： 为什么要使用事务？ 会员注册 try /提交方式手动的 更新用户表 更新等级表

58、 更新积分表 catch(Exception e /回滚事物 会员注册这件事成功了 事务的启动： 1：sql*plus开启 2：一个事物结束就代表另外 一个事物的开始 事物的结束： 1：sql*plus关闭 2：DDL，DCL语言就提交事物 3：exit,Errors,system crash 事务四大特性： 原子性：要么一次性成功， 要么失败 一致性：事物前后数据一致 隔离性：并发访问的事务，是不能 看到别的事物的一些信息 持久性：数据保存到数据库中 commit; insert into insert_test2 values(9,2; savepoint a; insert into i

59、nsert_test2 values(10,2; savepoint b; insert into insert_test2 values(11,2; rollback to b select * from insert_test2 commit; 注意：事物提交之后是不能回滚 rollback to a;/出错 truncate和delete的区别？ undo 隐式的提交 DML语言 为什要使用事物？ 会员注册 第十二章：alter tables and contraints ALTER TABLE table ADD (column datatype DEFAULT exprNOT NUL

60、L , column datatype.; 1:增加约束的时候，只能增加非空约束 并且在增加这种非空约束的时候，表中 必须是没有数据的 alter table insert_test2 add (name varchar2(20; /增加一个age属性 alter table insert_test2 add (age number(5; 删除某一个字段： alter table tableName drop column columnName /删除age属性 alter table insert_test2 drop column age /删除name属性 alter table ins

61、ert_test2 drop column name 修改的属性 alter table tableName modify (columnName datatype default exprnot null /修改name not null alter table insert_test2 modify (name varchar2(20 not null 增加约束 ALTER TABLE tableName ADD CONSTRAINT constraint type (column; /name变为唯一性约束 alter table insert_test2 add constraint test_name_uk unique(name /删除约束 alter table insert_test2