oracle学习笔记.doc

01sqlplus sys/tiger as sysdba-DBA登陆show user -显示当前用户=02alter user scott account unlock;-解锁用户=03SQL语言分类：数据查询语言（SELECT语句）：用于检索数据库数据。在SQL所有语句中，SELECT语句的功能和语法最复杂、最灵活。数据定义语言（Data Definition Language，DDL）：用于建立、修改和删除数据库对象。例如使用CREATE TABLE可以建立表；使用ALTER TABLE可以修改表结构；使用DROP TABLE可以删除表。但是要注意，DDL语句会自动提交事务。数据操纵语言（Data Manipulation Language, DML)：用于改变数据库数据，包括INSERT，UPDATE和DELETE三条语句。其中INSERT语句用于将数据插入数据库中，UPDATE语句用于更新已经存在的数据库数据，而DELETE语句则用于删除已经存在的数据库数据。数据控制语言（Data Control Language，DCL）：用于执行权限授予和收回操作，包括GRANT和REVOKE两条命令，其中GRANT命令用于给用户或角色授予权限，而REVOKE命令则用于收回用户或角色具有的权限。但是要注意，DCL语句会自动提交事务。事务控制语言（Transactional Control Language，TCL）：用于维护数据的一致性，包括COMMIT，ROLLBACK和SAVEPOINT三条语句。其中COMMIT语句用于确认已经进行的数据库改变，ROLLBACK语句用于取消已经进行的数据库改变，而SAVEPOINT语句则用于设置保存点，查询部分数据库改变。desc empdeptsalgradebonus;-描述表=04select ename,sal*12 from emp;select 2*3 from dual;select sysdate from dual;select ename,sal*12 year_sal from emp;给数据库表中字段sal*12起个别名year_salselect ename,sal*12 year sal from emp;如果别名需要空格，则必须加上双引号，可以保持原来的格式select ename,sal*12+comm from emp;任何含有空值null的计算结果都是空值select ename|sal|goodluck from emp;-连接 字符串当字符串中含有单引号，你可以使用两个单引号来代替一个单引号。=05select distinct deptno,job from emp;-去除重复=06select * from emp where ename=CLARK;select ename,sal from emp where sal 1500;select ename,sal from emp where sal =800 and sal 1000;select ename,sal from emp where deptno = 10 or sal 1000;select ename from emp where ename like _A%;-模糊查询 查询第二个字母是A的select ename from emp where ename like %;指定转义字符=07select empno,ename from emp order by empno asc;-默认升序select * from dept order by deptno desc;-降序select ename,sal,deptno from emp order by deptno asc,ename desc;-组合条件先按deptno升序排列，deptno相同的时候按照enamel降序排列=08单行函数zselect lower(ename) from emp;-变小写select upper(ename) from emp;-变大写select ename from emp where lower(ename) like _a%;-可用于查询忽略大小写select ename from emp where ename like _a% or _A%; 结果和上面一样select subser(ename,1,3) from emp;-从第一个字母开始截取，截取三个select chr(65) from dual; Aselect ascii(A) from dual;65select round(23.652) from dual;-24 四舍五入select round(23.652,2) from dual;23.65 保留两位小数，参数可以是负数，-1的话就是20select to_char(sal,$999,999.99)from emp;-自由长度万位没有就不显示select to_char(sal,$000,000.00)from emp;-固定长度select to_char(sal,L999,999.99)from emp;-本地货币符号select to_char(hiredate,YYYY-MM-DD HH:MI:SS) from emp;-时间格式 12小时select to_char(sysdate,YYYY-MM-DD HH24:MI:SS) from dual; 24小时=09select ename, hiredate from emp where hiredate to_date(1981-2-20 12:34:56,YYYY-MM-DD HH24:MI:SS);select ename, sal from emp where sal to_number($1,250.00,$999,999.99);select ename,sal*12+nvl(comm,0) from emp;如果comm的是空值，就用0替代它，如果不是空值，就是原来comm的值。=10组函数 max min avg sum countselect max(sal) from emp;select min(sal) from emp;select avg(sal) from emp;select to_char(avg(sal),999,999.99) from emp;select round(avg(sal),3) from emp;-三位小数select sum(sal) from emp;select count(*) from emp;求表里的记录数select count(comm) from emp;count某一个字段，不为空值算一个select count(distinct deptno) from emp;=11select deptno,avg(sal) from emp group by deptno;部门平均薪水可以按照多个字段分组select ename from emp where sal = (select max(sal) from emp);select deptno,max(sal) from emp group by deptno;出现在select列表中的没有出现在组函数的话必须出现在group by中group一般和组函数使用=12select avg(sal), deptno from emp group by deptno having avg(sal) 2000;select *选择from *从哪个表where单条过滤条件group by *对数据进行分组having *对分组进行限制order by*对产生最后的结果进行排序=13select ename, sal from emp where sal = (select max(sal) from emp);select ename, sal from emp where sal (select avg(sal) from emp);-求各部门中薪水等级最高的人select emp.deptno,ename, sal from empjoin (select max(sal) max_sal , deptno from emp group by deptno) ton (emp.sal = t.max_sal and emp.deptno = t.deptno);=14 自身和自身连接，需要起个别名select e1.ename, e2.ename from emp e1, emp e2where e1.mgr = e2.empno;=15select ename,dname from emp,dept where emp.deptno = dept.deptno;-1992标准select ename,dname from emp join dept on (emp.deptno = dept.deptno);-1999标准不推荐usingselect ename,grade from emp e join salgrade s on (e.sal between losal and hisal)显示员工的工资等级select e1.ename, e2.ename from emp e1 left join emp e2 on(e1.mgr = e2.empno);-左外连接，左面的表字段全部显示，右边可能会出现空值右外连接，右边表的字段全部显示，左边可能会出现空值left join也可以写成left outer joinselect e1.ename, e2.ename from emp e1 full join emp e2 on(e1.mgr = e2.empno);左右没有连接的数据都拿出来=16-求部门的平均薪水的等级select deptno, avg_sal, grade from(select deptno,avg(sal) avg_sal from emp group by deptno) t join salgrade s on(t.avg_sal between s.losal and s.hisal);=17-求部门的平均的薪水等级select deptno,avg(grade) from(select ename,deptno,grade from empjoin salgrade on (sal between losal and hisal) tgroup by deptno=18-雇员中那些是经理人select ename from emp where empno in(select distinct mgr from emp);=19-不用组函数求最高薪水select sal from emp where sal not in (select distinct e1.sal from emp e1 join emp e2 on (e1.sal a2.avg_sal)=23-授权用户conn sys/tiger as sysdba;grant create table, create view to scott;conn scott/tiger创建视图，视图就是一个子查询或者说是一个表，虚表create view v$_dept_avg_sal_info asselect deptno,grade,avg_sal from (select deptno,avg(sal) avg_sal from emp group by deptno) tjoin salgrade s on (t.avg_sal between s.losal and s.hisal)=24-比普通员工的最高薪水还要高的经理select ename from empwhere empno in (select distinct mgr from emp where mgr is not null)and sal (select max(sal) from emp where empno not in (select distinct mgr from emp where mgr is not null);=25 exp 导出数据库create user liuchao identified by liuchao default tablespace users quota 10M on users/DBA相关：用户数据的导出、新用户的创建和数据的导入。rollback;回退备份表: create table emp_bak as select * from emp;create table dept_bak as select * from dept;create table salgrade_bak as select * from salgrade;还原表:create table emp as select * from emp_bak;删除表:drop table emp;insert into dept2 values (50,game,beijing);insert into dept2 (deptno, dname) values (60,game2);insert into dept2 select * from dept;select ename from emp where rownum = 5;rownum只能和或=使用-薪水最高的前5个人select ename, salfrom (select ename,sal from emp order by sal desc )where rownum = 5 and r = 10;=26update emp2 set sal = sal*2, ename = ename|- where deptno = 10;delete from emp2;-删除所有数据-DDLcreate table t (a varchar2(10);desc t;drop table t;rollback;-回退上面所有的DML语句(3条语句)也算是一个结束。DML语句（update，delete，rollback）的一句transaction开始 commit提交transaction，此时rollback失效；是一个事务结束DDL（create，alter，drop）、DCL（grant，revoke）语句事物自动提交正常退出如exit 事务自动提交非正常退出，如直接关黑色窗口，事务会自动回滚；=27定长字符串效率高，浪费空间=28constraint-约束create table stu(id number(6),name varchar2(20) constraint stu_name_nn not null,sex number(1),age number(3),sdate date,grade number(2) default 1,class number(4),email varchar2(50),constraint stu_name_email_uni unique(email,name)/指email和name组合唯一);not null，unique，主键，外键（两个字段，一张表或者两张表中的字段约束），check加字段后加在表后=29-值约束/create table stu(id number(6) constraint stu_id_pk primary key,在语法上时非空唯一的，逻辑上代表一条记录name varchar2(20) constraint stu_name_nn not null,sex number(1),age number(3),sdate date,grade number(2) default 1,class number(4) constraint stu_class_fk references class(id),email varchar2(50) unique);-表约束create table stu(id number(6),name varchar2(20) not null,sex number(1),age number(3),sdate date,grade number(2) default 1,class number(4),email varchar2(50),constraint stu_id_pk primary key(id),constraint stu_class_fk foreign key (class) references class(id),外键被参考的必须是主键constraint stu_name_email_uni unique(email,name);-班级表create table class(id number(4) primary key,name varchar2(20) not null);=30=31alter table stu add(addr varchar2(100);-插入列alter table stu modify(addr varchar2(150);-修改精度alter table stu drop(addr);-删除列alter table stu drop constraint stu_class_fk;-删除约束alter table stu add constraint stu_class_fk foreign key (class)references class(id);-添加约束drop table *-删除表=32desc user_tables;-user_tables数据字典表select table_name from user_tables order by table_name;-查看所有表名select view_name from user_views;-查看所有视图select constraint_name from user_constraints;-查看所有约束select table_name, constraint_name from user_constraints;select * from dictionary;-查看所有数据字典表select table_name from dictionary where table_name like USER% order by table_name;ed-编辑刚执行的SQL语句/ -执行刚执行的或刚编辑的语句=33读数据快，修改起来慢create index idx_stu_email on stu(email);-创建索引drop index idx_stu_email;-删除索引-主键和唯一约束默认创建索引create view v$_* as select . -创建视图，视图就是子查询=34create table article(id number,title varchar2(1024),content long);create sequence seq;-序列，用一次自动 +1drop sequence seq;-删除序列select * from seq;-查看所有序列insert into article values (seq.nextval,a,b);=35一范式：要有主键，列不可分二范式：不存在部分依赖（组合主键的情况下）三范式：不存在传递依赖=36=37=38set serveroutput on;-打开输出-begindbms_output.put_line(HelloWorld!);end;=39declarev_name varchar2(20);beginv_name := zhouwenjian;dbms_output.put_line(v_name);end;-declarev_num number := 0;beginv_num := 2/v_num;dbms_output.put_line(v_num);exceptionwhen others thendbms_output.put_line(error);end;=40-变量声明的规则1、变量名不能使用保留字，如from、select等2、第一个字符必须是字母3、变量名最多包含30个字符4、不要与系统数据库的表或者列同名5、每一行只能声明一个变量-常用变量类型1、binary_integer: 整数，主要用来计数儿不是用来表示字段类型2、number：数字类型3、char：定长字符串4、varchar2：变长字符串5、date：日期6、long：长字符串，最长2GB7、booblean：布尔类型，可以取值为true、flase和null，最好开始赋值，不给的话就是null|代表字符串连接打印不出boolean类型的值-v_empno2 emp.empno%type-前者类型精度与保持后者一致-注释一行/*/注释多行=41=42=43declarev_ename emp.ename%type;v_sal emp.sal%type;beginselect ename, sal into v_ename, v_sal from emp where empno = 7369;dbms_output.put_line(v_ename | | v_sal);end;Select语句只能一条数据，没有或很多也不行-declare v_deptno dept.deptno%type := 50;v_dname dept.dname%type := AAAA;v_loc dept.loc%type := BEIJING;begininsert into dept2 values(v_deptno, v_dname, v_loc);commit;end;-declarev_deptno emp2.deptno%type := 10;v_count number;beginupdate emp2 set sal = sal/2 where deptno = v_deptno;-select deptno into v_deptno from emp2 where empno = 7369;-select count(*) into v_count from e