oracle基本语句
1.conn 用户/密码 （切换用户）
2.show user （显示当前用户）
3.create user 用户名 identified by 密码 （创建新用户）
4.password 用户名/passw （修改密码）
5.grant connect to 用户 （授予用户connect角色（属于系统权限））
6.系统权限（connect,resource,dba）
7.对象权限（select,insert,update,delete,all,create index）
8.grant select on 表 to 用户 （将表授权给用户查询）（select可以换为其他权限，若all则是授予所有权限）
select * from 方案/用户.表 （用户查询别人表的方式）
9.revoke 权限 on 表 from 用户 （撤销用户的权限）
10.对权限的维护即权限的传递{
--若是对象权限则加with grant option
例：grant select on 表 to 用户 with grant option
--若是系统权限则加with admin option
例：grant connect to 用户 with admin option
}
11.profile管理用户口令{
--sql>create profile 文件名 limit failed_login_attempts 3 password_lock_time 2 （连续输入3次错误密码锁定用户两天）
sql>alter user 用户 profile 文件名
--sql>alter user 用户 account unlock （解锁用户）
--sql>drop profile 文件名 （删除profile）
}
12.表的操作{
字段
--sql>alter table 表名 add (名 类型) （添加字段）
--sql>alter table 表名 modify (名 类型) （修改字段的长度或类型或名字）
--sql>desc 表名 （查看表的结构）
--sql>alter table 表名 drop column 字段名 （删除一个字段）
--sql>rename 表名 to 表名 （修改表名）
--sql>drop table 表名 （删除表）
数据
--sql>insert into 表名 values ("格式要对应") （默认日期格式'DD-MON-YY',转换:alter session set nls_date_format="yyyy-mm-dd"）
--sql>insert into 表名 (一系列字段名) values () （插入部分字段，亦可插入空值null）
--sql>select * from 表 where 字段名 is null/not null （查询空与非空）
--sql>update 表名 set 字段名=修改，字段名=修改... where 字段名=值 （修改数据）
--sql>delete from 表名 （删除全部数据保留表的结构，可通过日志找回）
--sql>truncate table 表名 （删除全部数据保留表的结构，可通过日志找回）
--sql>savepoint 保存点的名称 （写日志可用于数据恢复）
sql>rollback to 保存点的名称
--sql>select distinct 字段名,... from 表名 （取消重复行）
}
13.对表数据的操作示例{
--sql>select sal*13+nvl(comm,0)*13 "年工资"，name from emp
解释:sal,comm皆为字段名，emp为表名，此语句中存在对数据的算数计算；
"年工资"是的sal重命名；nvl用于处理null值，此语句中当comm为null时取0
为何要处理null值:在oracle中，任何值与null进行算数计算后的都会等于0
--sql>select sal,name from student where sal<3322 and sal>3111;
解释:sal,name皆为字段名，student为表名,查找 sal<3322并且sal>3111 的数据；
--sql>select name,num,sex from student where name like '_的%';
解释:num,name,sex皆为字段名，student为表名,查找name字段名下第二个字符为'的'的所有数据
}
14.多表查询操作示例{
--sql>select a1.ename,a2.dname,a1.sal from emp a1,dept a2 where a1.deptno=a2.deptno order by a1.deptno;
解释:al,a2分别为表emp，dept的别名,order by 字段名是指按此字段名排序,表emp与表dept都有deptno字段
--实例
SQL> select * from gradelevel;
LEV LEFT RIGHT
---------- ---------- ----------
1 0 20
3 41 60
2 21 40
4 61 80
5 81 100
SQL> select a1.name,a1.grade,a2.lev from student a1,gradelevel a2 where a1.grade between a2.left and a2.right;
NAME GRADE LEV
---------- ---------- ----------
大大 89.6 5
板绘 32 2
大山 45 3
大手 12 1
小新 34 2
答点 87 5
大幅 56 3
分割 56 3
后端 64 4
多个 87 5
--自连接是指在同一张表的连接查询，比如一张职工表中有员工与员工的上级两个属性，且每个员工都对应一个上级。
若要显示某个员工的上级，则可将这张表看成两张，使用两个不同的别名worker与boss，通过 where worker.上级=boss.名字 来查询
}
15.子查询:select name from student where classid=(select classid from student where name='小明');
解释:单行子查询,只返回一行数据，显示与小明同班的所有学生
select * from emp where job in (select distinct job from emp where deptno=10);
解释:多行子查询，返回多行数据，子查询返回了多个job信息，'in'指多个对应，'='为一个对应
其他符号还有'>','<'
select * from emp where (depton,job)=(select depton,job from emp where='SMITH');
解释:返回不同字段时，需一一对应，找出所有与smith有同样工作和相同部门的员工信息
16.create view stucs as select * from table where 字段1 ='a' with check option
以后对该视图插入、修改、删除操作时，会自动加上字段1='a' 的条件
exists : 强调的是是否返回结果集，不要求知道返回什么, 比如：
select name from student where sex = 'm' and mark exists(select 1 from grade where ...) ,只要
exists引导的子句有结果集返回，那么exists这个条件就算成立了,大家注意返回的字段始终为1，如果改成“select 2 from grade where ...”，那么返回的字段就是2，这个数字没有意义。所以exists子句不在乎返回什么，而是在乎是不是有结果集返回。
而 exists 与 in 最大的区别在于 in引导的子句只能返回一个字段，比如：
select name from student where sex = 'm' and mark in (select 1,2,3 from grade where ...)
，in子句返回了三个字段，这是不正确的，exists子句是允许的，但in只允许有一个字段返回，在1，2，3中随便去了两个字段即可。
而not exists 和not in 分别是exists 和 in 的 对立面。
exists （sql 返回结果集为真）
not exists (sql 不返回结果集为真）
下面详细描述not exists的过程：
如下：
表A
ID NAME
1 A1
2 A2
3 A3
表B
ID AID NAME
1 1 B1
2 2 B2
3 2 B3
表A和表B是１对多的关系 A.ID => B.AID
SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE A.ID=B.AID)
执行结果为
1 A1
2 A2
原因可以按照如下分析
SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID=１)
--->SELECT * FROM B WHERE B.AID=１有值返回真所以有数据
SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID=2)
--->SELECT * FROM B WHERE B.AID=２有值返回真所以有数据
SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID=3)
--->SELECT * FROM B WHERE B.AID=３无值返回真所以没有数据
NOT EXISTS 就是反过来
SELECT ID,NAME FROM A WHERE NOT EXIST (SELECT * FROM B WHERE A.ID=B.AID)
执行结果为
3 A3
===========================================================================
EXISTS = IN,意思相同不过语法上有点点区别，好像使用IN效率要差点，应该是不会执行索引的原因
SELECT ID,NAME FROM A WHERE ID IN (SELECT AID FROM B)
NOT EXISTS = NOT IN ,意思相同不过语法上有点点区别
SELECT ID,NAME FROM A WHERE ID NOT IN (SELECT AID FROM B)
有时候我们会遇到要选出某一列不重复,某一列作为选择条件,其他列正常输出的情况.
如下面的表table:
Id Name Class Count Date
1 苹果 水果 10 2011-7-1
1 桔子 水果 20 2011-7-2
1 香蕉 水果 15 2011-7-3
2 白菜 蔬菜 12 2011-7-1
2 青菜 蔬菜 19 2011-7-2
如果想要得到下面的结果:(Id唯一,Date选最近的一次)
1 香蕉 水果 15 2011-7-3
2 青菜 蔬菜 19 2011-7-2
正确的SQL语句是:
SELECT Id, Name, Class, Count, Date
FROM table t
WHERE (NOT EXISTS
(SELECT Id, Name, Class, Count, Date FROM table
WHERE Id = t.Id AND Date > t.Date))
如果用distinct,得不到这个结果, 因为distinct是作用与所有列的
SELECT DISTINCT Id, Name, Class, Count, Date FROM table
结果是表table的所有不同列都显示出来,如下所示:
1 苹果 水果 10 2011-7-1
1 桔子 水果 20 2011-7-2
1 香蕉 水果 15 2011-7-3
2 白菜 蔬菜 12 2011-7-1
2 青菜 蔬菜 19 2011-7-2
如果用Group by也得不到需要的结果,因为Group by 要和聚合函数共同使用,所以对于Name,Class和Count列要么使用Group by,要么使用聚合函数. 如果写成
SELECT Id, Name, Class, Count, MAX(Date)
FROM table
GROUP BY Id, Name, Class, Count
得到的结果是
1 苹果 水果 10 2011-7-1
1 桔子 水果 20 2011-7-2
1 香蕉 水果 15 2011-7-3
2 白菜 蔬菜 12 2011-7-1
2 青菜 蔬菜 19 2011-7-2
如果写成
SELECT Id, MAX(Name), MAX(Class), MAX(Count), MAX(Date)
FROM table
GROUP BY Id
得到的结果是:
1 香蕉 水果 20 2011-7-3
2 青菜 蔬菜 19 2011-7-2
如果用in有时候也得不到结果,（有的时候可以得到，如果Date都不相同（没有重复数据），或者是下面得到的Max（Date）只有一个值）
SELECT DISTINCT Id, Name, Class, Count, Date FROM table
WHERE (Date IN
(SELECT MAX(Date)
FROM table
GROUP BY Id))
得到的结果是：（因为MAX(Date)有两个值2011-7-2，2011-7-3）
1 桔子 水果 20 2011-7-2
1 香蕉 水果 15 2011-7-3
2 青菜 蔬菜 19 2011-7-2
注意in只允许有一个字段返回
有一种方法可以实现：
SELECT Id, Name, Class, COUNT, Date
FROM table1 t
WHERE (Date =
(SELECT MAX(Date)
FROM table1
WHERE Id = t .Id))
浙公网安备 33010602011771号