数据结构与算法

Oracle数据库操作基本语法


数据库执行是从左到右执行的创建表sQL>create table classes(lassld number(cname varchar2birthday date);添加一个字段SQL>alter table student add(classld number(2))修改字段长度SQL>alter table student modify (xm varchar2(30));修改字段的类型减或是名字(不能有数据)SQL>alter table student modify (xm char(30))删除一个字段SQL>alter table student drop column sal;修改表的名字inSQLSrename student to stu;删除SQL>drop table student;插入所有字段数据docIn. SQLt into student values (00修改日期输入格式SQL> alter session set nls date format=yyy-mm-dd';∥临时生效,重启后不起错用SQLSinsert into student values(001, 'salina, 'I', to date(01-5-05', 'yyyy-mm-dd ), 10)SQLSinsert into student values(001,'salina', ', to date(01/5-05, 'yyyy/mm/dd,), 10)插入部分字段SQL> insert into student(xh,xm,sex) valuest(001,ison’,’女”);插入空值SQL>insert into student(xh, xm, sex, birthday) values (021,, BLYK, 'y', null)一条插入语句可以插入多行数据sQL> insert into kkk ( Myid, myname, mydept) select empno ename, deptno from emphere deptno=10;查询空值(非空)的数据SQL>Select* from student where brithday is null (/not null)修改(更新)数据SQL>update student set sal=sql/2 where sex='95'更改多项数据SQL> update emp set Gob, sal, m)=(select job, sal, m from emp whereename=SMITH) where ename='SCOTT删除数据1.保存还原点SQL>savepoint aa;2.删除数据【1】SQL> delete from student;∥删除表的数据【2】SQL> drop table student;〃删除表的结构和数据【3】SQL> delete from student where xh=001’;∥删除·条记录【4】SQL> truncate table student;m删除表中的所有记录,表结构还在,不写日志无法扎找回的记录,速度快查看表结构SQL>dese student;查询指定列SQL>select sex, xh, xm from student如何取消重复SQL>Select distinct deptno, job from student打开显示操作时间的开关SQL>set timing on为表格添加大的数据行(用于测试反应时间)SQL>insert into users(userid, username, userpss)select from users统计表内有多少条记录SQLSSelect count")from users屏蔽列内相同数据SQL>select distinct deptno, job from emp查询指定列的某个数据相关的数据SQL>select deptno job, sal from emp where ename=smith使用算数表达式SQL>Select sal 12 from emp使用类的别名SQL> eselect ename“姓名”,sal12as“年收入” from emp处理nul(空)值SQL> select sal*13nv(m,0*13“年工资”, ename, m from emp;连接字符串(‖)sQL> select ename‖isa'‖ job from emp;Where子句的使用【1】SQL> select ename, sal from emp where sal>300;/ number的范围确定【2】SQL> select ename, hiredate from emp where hiredate>'1-1月-1982';/日期格式的范围确定【3】SQL> select ename, sal from emp where sal>=2000 and sak<=2500;组合条件Like操作符:2%、’SQL> select ename, sal from emp where ename like“s%’;∥第一个字符【名字第一个字符为S的员工的信息(工资)】SQL> select ename, sal from emp where ename like o%;∥其它字符【名字第三个字符为O的员工的信息(工资)】pocI co批量查询SQL> select*from emp where in(123,456,789);∥查询一个条件的多个情况的批量处理查询某个数据行的某列为空的数据的相关数据SQL >select* from emp where mgr is null;条件组合查询(与、或)SQL>Select* from emp where(sal500 or job='MANAGER,) and ename like J%';Order by排序【1】SQL> select* from emp order by sal(ase);∥从低到高默认【2】SQL> select from emp order by sal desc;∥从高到低【3】SQL> select* from emp order by deptno(asc), sal desc;m组合排序【4】SQL> select ename, sal*12“年薪” from emp order by“年薪”(asc);SQL> select ename,sa+nv(m,0)*12as"年薪" from emp order by"年薪";资料分组(SQL>SelSQL> select ename, sal from emp where sal=( (select max(sa) from emp);∥子查询,组合查询SQL> Select* frorre sal>(select avg(sal fp);/子查询,组合查询SQL> update emp set sal-salII where sal<(select avg(sal) from emp) and hiredate<1-1月-1982;∥将工资小于平均工资并且入职年限早于1982-1-1的人工资增加10%Group by和 having子句/ group by用于对查询出的数据进行分组统计having用于限制分组显示结果SQL> select avg(sal,max(saD, deptno from emp group by deptno;∥显示每个部门的平均工资和最低工资SQL>Select avg(sal), max(sal), deptno from emp group by deptno∥显示每个部门的平均工资和最低工资SQL> select avg(sal), max(sal), deptno from emp group by deptno having avg(sal)>2000;SQL> select avg(sal), max (sal), deptno from emp group by deptno having avg(sal)2000order by avg(sal);多表查询笛卡尔集:规定多表查询的条件是至少不能少于:表的个数-1SQL> select alename, al. sal, a2 dname from emp al, dept a2 whereal deptno= a2 deptnoSQL> select al dname, a2 ename, a2. sal from dept al, emp a2 where al deptno=a2 deptnoand al. deptno=10;∥显示部门编号为10的部门名、员工名和工资SQL> select al ename, al. sal, a2. grade from emp al, salgrade a2 where al. sal betweena2 losal and a2. hisal:SQL> select al ename, al.sal, a2 dname from emp al, dept a2 where al deptno=a2. deptder by al deptno;∥多表排序SQL> select worker ename, boss. ename from emp worker, emp boss whereworker mgr= boss. empno;〃自连接(多表查询的特殊情况)SQL> select worker ename, boss. ename from emp worker, emp boss whereorker. mgr=boss empno and worker. ename=fORD'子查询SQL> select from emp where deptno=(select deptno from emp whereename=SMITH)SQL> select distinct job from emp where deptno=10SQL> select* from emp where job in(select distinct job from emp where deptno=10);∥如何查询和部门10的工作相同的雇员的名字、岗位、工资、部门号。

SQL> select ename sal, deptno from emp where sal>all(select sal from emp wheredeptno=30);/如何查询工资比部门30的所有员工的工资高的员工的姓名、工资和部门号SQL> Select ename sal, deptno from emp where sal(select max(sal)from emp wheredeptno=30)SQL> Select from emp where(deptno,job)=(select deptno job fromename=SMITH);内嵌视图∥在from子句中使用子查询的时候,必须给子查询指定别名SQL>Select a2 ename, a2. sal, a2 deptno, al. mysal from emp a2, (select deptno, avg(sal)(as mysal from emp group by deptno) al where a2 deptno=aldeptno anda2saal. mysal分页SQL> select al*, rownum rn fron( select* from emp)al;/ orcle为表分配的行号SQL> select from (select al. rownum rn from (select from emp) al whererownum<=10) where rn>=6查询内容的变化1.所有的改动(指定查询列)只需更改最里面的子查询2.(排序)只需更改最里面的子查询子查询(用查询结果创建新表)sQL> create table mytable (id, name, sal,job, deptno) as select empno, ename, saljob, deptnofrom emp:合并查询unon(求并集), union all, intersect(取交集),mins(差集)omsQL> select ename, sal, job from emp where sal>2500SQL> select ename, sal, job from emp where job=ManAger;SQL> Select ename, sal, job from emp where sal2500 union select ename, sal, job from empwhere job= MANAGER;∥ union(求并集)Java连接数据库事务SQL> mit;∥事务(第一次创建,第二次提交)当退出数据库时,系统自动提交事务SQL> savepoint a1;∥创建保存点(保存点的个数没有限制)SQL> rollback to aa;使用保存点回滚到SOL>rollback∥/回滚到事务创建开始只读事务SQL>Set transaction read onlyJava中的事务Ct. setAuto Commi( false);∥设置事务自动提交为否Ct. mit;∥提交事务字符函数lower(char)将字符串转换为小写的格式upper(char)将字符串装换为大写的格式length(char)返回字符串的长度substr(char,m,n)取字符串的子串SQL>Select lower(ename)from emp;SQL>select ename from emp where length(ename )=5。