oracle初学者需要掌握的基本sql操作

　

　一：操作表

　创建表 

　　create table t_user(  id number(3,0),name varchar(100),sex char(2),birthday date);  

 

   查看表 

　　desc t_user;  // (show tables是MySQL的命令)

  

   添加列 

　　alter table t_user add(age number(3));  // number精度是1-38

 

   修改列 

　　alter table t_user modify(age number(4));

 

   删除列 

　　alter table t_user drop column age;

 

   修改表名 

　　rename t_user to t_info; // t_user 是之前的表名  t_info是修改之后的表名

 

   删除表 

　　drop table t_user;

 

二：操作数据 

 

 

   增加数据

 

　　//没写的列就是没数据,字符型用单引号 当插入所有列的数据时 可以不写(id,name,sex,fellowship,birthday) values中可以写null不用符号

　　insert into t_student( id,name,sex,fellowship,birthday) values (1,'韩梅梅','女',5000,to_date('1990-03-10','yyyy-MM-dd'));

　

　修改数据

　　

　　update t_student set sex = '男' where id = 2;//修改一个字段的值

　　

　　update t_student set sex = '男',name = '丽丽' where id = 2;//修改多个字段的值用逗号隔开

　　

　　update t_student set felloship = fellowship + 1000;//修改某个字段的值为在原值的基础上+1000

 

   删除数据 

 

　　delete from t_student where id = 3; 

 

   查询数据

 

　　select * from t_student; //查询所有字段的数据 * 代表所有意思

　　

　　select name,sex from t_student; //查询几个字段的数据 中间用逗号隔开

　　

　　select * from t_student where id = 1; //where关键字  后面接的是限定条件

      

　　select * from t_student where name like '李%';//like关键字 意思是像 后面可以接%占位符  代表多个字符位置 

       

　　select * from t_student where name like '李_';//_占位符 代表一个字符位置

 

　　select * from t_student where birthday is null;//null 关键字 意思是值为空 要注意不能写成 = null 可以写成 is null 或者 is not null 

       

　　select * from t_student where sex != '男';// != 关键字  意思是不等于 

       

　　select * from t_student where sex <男> '';// <> 关键字  意思是不等于

　　

　　select * from t_student where fellowship < 5000 and fellowship > 2000; // < 关键字 意思是小于   > 关键字  意思是大于  <=  小于等于   >=大于等于

 

　　select * from t_student where fellowship between 2000 and 5000; //between and  关键字 意思是介于xx和xx之间  包括边缘两个值（这个是包括2000和5000的）

       

　　select * from t_student where name='李雷' or name = '韩梅梅';// or 关键字 意思是或者  

       

　　select name,fellowship*0.8 from t_student;// 查询的时候可以将查询的值做处理之后输出  但是要注意null 不能参与运算

 

　　select empno,ename,sal*12 + nvl(comm,0) from emp; // nvl 关键字 使用nvl解决null问题  就是当comm为空的时候 将它看成是0 这样就能参与运算了

 

　　select name as 名字,fellowship  薪水 from t_student; //as 关键词  意思是取别名  可以省略不写 但是要跟原字段名直接留出至少一个空格位置

 

　　select name||fellowship from t_student; //用于拼接查询结果

 

　　select * from t_user where age = 11 or age = 13; //or 关键字 意思是或者

       

　　select * from t_user where name in ('uzi','cool'); //in 关键字 意思是穷举 

 

　　select * from t_user order by age asc/desc;// order by desc/asc 关键字 意思是排序 asc正序  desc倒序  不写默认asc

      

　　select * from t_user order by age desc,id desc;// 先根据age升序排,遇到有相同的age时,按照id的降序排

 

　　select distinct * from t_user ;  //distinct 关键字 意思是去重 * 代表所有列  也可以接单独的列名

      

　　select distinct age from t_user; //去除某一列的重复

 

　　select count(1) from t_user;  // 里面是数字的按照*处理//count() 关键字 意思是总数  常用写法count(1)  count(*)  count(字段名)

 

　　select distinct * from t_user where name like '%i%'; // 原则是现根据where条件筛选出来结果再去重           

      

　　select * from emp where hiredate > to_date('1981-01-01','yyyy-MM-dd'); //to_date 关键字 后面跟上时间及其格式（年月日）

 

　　select * from t_user t where addtime > to_date('2019-3-20 8:00:00','yyyy-mm-dd hh24:mi:ss');// to_date 关键字 （年月日时分秒）

 

　　select max(sal) from emp;  //max 关键字 意思是最大值  里面只能是一个参数(字段名)  里面不能是*

 

　　select min(sal) from emp;  //min 关键字 意思是最小值 

 

　　select avg(sal) from emp;  //avg 关键字 意思是平均值

 

　　select sum(sal) from emp; //sum 关键字 意思是和

 

　　select max(sal),min(sal) from emp;  //查最大值和最小值  当出现max min avg sum count的时候 后面不能直接跟搜索字段 格式不对 需要用group by

 

　　select avg(sal),max(sal),deptno,age from emp group by deptno,age;//group by 关键字  意思是分组 用于对查询的结果分组统计  如果前面搜索字段有deptno, 那么group by  后面也必须接上deptno 有多少跟多少 用逗号隔开

 

　　select avg(sal),max(sal),deptno from emp group by deptno order by deptno asc; // group by 和order by  同时出现的时候  group by 写前面  order by 写后面

 

　　select deptno ,avg(sal) from emp group by deptno having avg(sal) <2000;//当使用group by的时候 如果需要写限制条件，要用having 而不能用where

 

　　select * from emp e,dept d where e.deptno = d.deptno;  // 多表查询(笛卡儿积)  需要写明两个表的两个字段对应关系 并且最好要有别名

 

　　select * from emp where deptno = (select deptno from emp where ename = 'SMITH'); // 子查询 是指嵌入在其他sql语句中的select语句,也叫嵌套查询  单行子查询   是指只返回一行数据的子查询语句

    

       select ename ,job ,sal ,deptno from emp where job in (select job from emp where deptno = 10); // 多行子查询  是指返回多行数据的子查询

 

　　select ename,sal,deptno from emp where sal>all(select sal from emp where deptno=30);//在多行子查询中使用all操作符  ==  select ename,sal,deptno from emp where sal>(select max(sal) from emp where deptno =30);

 

　　select * from (select rownum rn,A.* from (select * from emp) A ) where rn > 3; // 分页查询 简化之后也可以写成select * from emp where rownum > 3; (MySQL分页  select * from user limit 0,2   从第0行开始向后查2行)

 

       create table mytable (id,name,sal,job,deptno) as select empno,ename,sal,job,deptno from emp;//用查询结果创建新表

 

       insert into mytable (id,name,sal,job,deptno) select empno,ename,sal,job,deptno from emp; // 自我复制数据(蠕虫复制)

      

　　合并查询  在实际应用中,为了合并多个select语句的结果,可以使用集合操作符号union,union all ,intersect,minus

 

　　union  该操作符用于取得两个结果集的并集.当使用该操作符时,会自动去掉结果集中重复行

　　　　select ename,sal,job from emp where sal>2500 union select ename,sal,job,from emp where job = 'N';//必须保证前后列数量相同

　　union all 该操作符与union相似,但是它不会取消重复行,而且不会排序.

　　　　select ename,sal,job from emp where sal>2500 union all select ename,sal,job,from emp where job = 'M';

　　intersect 使用该操作符用于取得两个结果集的交集.

　　　　select ename,sal,job from emp where sal > 2500 intersect select ename ,sal,job from emp where job = 'm';

　　minus 该操作符用于取得两个结果集的差集,它只会显示存在第一个集合中,而不存在第二个集合中的数据.

　　　　select ename,sal,job from emp where sal>2500 minus select ename,sal,job from emp where job = 'M';

 　　

　　表连接  内连接 外连接

　　　　内连接实际上就是利用where子句对两张表形成的笛卡儿积进行筛选

　　　　外连接 分为左外连接(左侧的表完全显示)  右外连接(右侧的表完全显示)  完全外连接 (完全显示两个表,没有匹配的记录置为空)

　　　　select * from emp e left join dept d on e.deptno = d.deptno; (其中deptno列有1个被删除了 那行右侧那一行的就没有显示出来)

　　　　select * from emp e right join dept d on e.deptno = d.deptno left join salgrade s on e.sal between s.losal and s.hisal;

　　　　select * from emp e right join dept d on e.deptno = d.deptno;

　　　　select * from emp e join dept d on e.deptno = d.deptno; // 完全外连接 join前面的可以省略FULL OUTER JOIN  full通常省略