oracle sql语句学习

1.desc：描述表的结构，展示有哪些字段及每个字段的数据类型。写法：desc table;

2.select:查询表数据。

 2.1查询表所有数据：select * from table;

2.2查询某些字段：select name,sal*12 from sal;

2.3查询计算表达式：select 2*3 from dual;(dual表中只有一个字段一条记录）;

2.4oracle查询当前时间：select sysdate from dual;mysql查询当前时间写法：select now();

2.5查询结果某些字段显示别名：select name,sal*12 anuual_sal from emp;

2.6任何含有空值（null）的计算表达式结果都是空值

2.7字符串连接符：select name||sal from emp;

2.8字符传连接字符串：select name||'dddd' from emp;//用单引号将字符传包起来。如果字符串中本来就有单引号，则对该单引号再加个单引号。select name||'ddd''ffd' from emp;

2.9去重：select distinct name from emp;如果distinct后跟多个字段则表示对多个字段的组合结果去重。

2.10where过滤：select name sal from emp where id=1;(name='ww',id<>2)

2.11betwen and:select name id from emp where id between 1 and 10;

2.12null值写法：select name,comm from emp where comm is null(is not null);

2.13 in写法：select name,id from emp where id in(1,2,8,9);(not in)

2.14多条件写法：select name,id from emp where deptno in(20,30,40,50) and sal > 1500;

2.15模糊查询：select name ,id from emp where nam like '%dd%';(_代表一个占位符）（like ‘_O’表示第二个字符是O的）

2.17转义：'%\%%'默认用\转义，自定义转义'%$%%' escape '$'

2.18排序：select * from emp order by deptno desc(,name asc)

2.19 或者：select * from emp where name like '_a%' or name like '_A%';

2.20 lower函数：select name from emp where lower(name) like '_a%;

2.21指定日期格式：select name,hiredate from emp where hiredate > to_date('1987-02-20 12:36:20','YYYY-MM-DD HH24:Mi:SS');

2.22nvl函数：select name ,sal*12+nvl(comm,0) from emp;

2.23最值：select max(sal) from emp;(min,avg，sum)

2.24to_char:select to_char(avg(sal),'$9999.99');

2.25总数：select count(*) from emp;

2.26分组：select deptNo,avg(sal) from emp group by deptNo,job;

2.27子查询：select name ,sal from emp where sal=(select max(sal) from emp);

2.28having:select deptNo,avg(sal) from emp group by deptNo having avg(sal) > 2000

2.29自查询join:select name,deptNo,sal from emp join(select max(sal) max_sal,deptNo group by deptNo) t on emp.sal=t.max_sal and emp.deptNo=t.deptNo;

2.30自连接：select e1.name,e2.name from emp e1,emp e2 where e1.mar=e2.empno;

2.32join:select e1.name,e1.deptNo,d1.deptName from emp e1 join dept d1 on e1.deptNo=d2.deptNo;

2.33多表连接查询：select  ename,dname,grade from emp e join dept d on(e.deptNo=d.deptNo) join salgrade s on (e.sal between s.losal and s.hisal) where name not

like '_A%';

2.34左连接：select e1.name,e2.name from emp1 left join emp e2 on(e1.mar=e2.empno)(把左边的表匹配不上的数据显示出来)

2.35右连接：select ename,dname from emp right join dept on(emp.deptNo=dept.deptNo);(把右边表匹配不上的数据显示出来)

2.36全连接：select ename,dname from emp full join dept on(emp.deptNo=dept.deptNo)

2.37子查询连接混合用:select  deptNo,avg_sal,grade from(select deptNo,avg(sal) avg_sal from emp group by deptNo) join salgrade s on(t.avg_sal between s.losal and hisal);部门平均薪水及等级

2.38：select deptNo,avg(grade) from (select deptNo,grade from emp join salgrade s on(emp.sal between s.losal and s. hisal)) t group by deptNo;(部门平均薪水等级)

2.39:selec ename from emp where empNo in (selcet mar form emp);哪些人是经理。

2.40：select distinct sal from emp where sal not in(select distinct e1.sal from emp e1 join emp e2 on e1.sal<e2.sal);不用组函数求出工资最高的人的工资

2.41：select dname from dept where deptNo=(select deptNo from (select deptNo,avg(sal) avg_sal from emp group by deptNo) where avg_sal=(select deptNo,max(avg_sal) from (select deptNo,avg(sal) avg_sal from emp group by deptNo)));平均工资最高的部门名称与编号,也可以这样(max(avg(sal)))组函数嵌套

2.42:select deptNo,dname,grade,avg_sal from(select deptNo,grade,avg_sal  from (select avg(sal) avg_sal,deptNo from emp group by deptNo) t  join salgrade s on( t.avg_sal between s.losal and hisal )) t1 join dept d on(t1.deptNo=d.deptNo) where t1.avg_sal=(selecet min(grade) from (select avg(sal) avg_sal,grade,deptNo from emp group by deptNo) t  join salgrade s on( t.avg_sal between s.losal and hisal )));平均薪资等级最低的部门名称

2.43：创建视图：create view v$_dept_avg_sal_info as select avg_sal,grade,deptNo from (select avg(sal) avg_sal,grade,deptNo from emp group by deptNo) t  join salgrade s on( t.avg_sal between s.losal and hisal );视图就是存储子查询的结果，可以看成是一张虚表。为了简化以后代码的书写，看着简单

2.44select ename from emp where empNo in(select mar from emp where mar is not null) and sal > select max(sal) from emp where empNo not in(select mar from emp where mar is not null);//比普通员工工资高的经理人

2.45创建用户的数据库：1 conn sys/bjsxt as sysdba;(连接)2 create user zyw identified by zyw default tablespace users quota 10M on users;(创建用户) 3grant create session,create table,create view to zyw;(授权)4目录下执行 imp

2.46插入数据：insert into dept values (50,'game','bj'); roolback; insert into dept (deptNo,dname) values(50,'bj');insert into table select * from dept2;

2.46备份表：create table emp2 as select * from emp:

2.47rownum:select ename,empno from emp where empno < 5;(oracle 只能用<=)(select ename from (select rownum r,ename from emp) where r >10 )通过操作子表实现>

2.48 select ename,sal from(select ename,sal from emp order by sal desc) where rownum <=5;工资最高的前5名

2.49 select ename,sal from (select ename ,sal,rownum r  from (select ename ,sal from emp order by sal desc)) where r>=6 and r<=10;工资是第点名到第10名的

2.50 select sname from s join sc on (s.sno=sc.sno) join c on(c.cno=sc.cno) where c.cteacher <> 'liming'//找出所有没有选择黎明老师课程的学生。

2.51select sname from s where s.sno in(select sno from sc where sc.scgrade<60 group by sno having count(*) >=2);//找出两门以上成绩不及格的学生。

2.52select sname from s where s.sno in(select sno from sc where sc.cno=1 and sno in(select sno from sc where sc.cno=2))既学过课程一又学过课程二的学生。

2.53update:update emp2 set sal=sal*2,ename=ename||'-'  where deptNo=10;

2.54delete:delete from emp where deptNO=10;

2.55建表：create table stu (id number(6) primary key,name varchar2(20) not null,sex number(1),age number(3),sdate date,grade number(2) default 1,class number(4),email varchar(40));

2.56约束：字段约束：name varchar2(20) constraint not null,email varchar2 constraint unique,表级约束：加在最后一个字段后面,constraint stu_name_email_unique unique(email,name),constraint stu_id_pk primary key(id);

2.57外键约束：字段级：class number(4) references class(id),表级：constraint stu_class_fk foreign key(class) references class(id);

2.58修改表：alter table stu add(address varchar2(20));alter table stu drop(address); alter talbe stu modify(address varchar2(100));

2.59去除约束条件：alter table stu drop constraint stu_class_fk;

2.60添加约束条件：alter table stu add constraint stu_class_fk foreign key(class) references class(id);

2.61删除表：drop table stu;

2.62建立索引：create index inx_stu_email on stu(email);

2.63建立序列：create sequence seq; select seq.nextval