一：基本命令
1：链接
格式：conn 用户名/密码@网络服务名[as sysbda/sysoper]
说明：当用户是特权用户时必须带上as sysbda/sysoper
2：断开链接
格式：disc[onnect]
3：修改密码
格式：passw[ord]
说明：修改密码,当想要修改其他用户密码用sys或system登录
4：显示当前用户
格式：show user
5：断开连接,并退出sqlplus
格式：exit
二：文件操作命令
1：执行sql文件
格式：sql sql文件路径 @sql文件路径
说明：在d:\ 下有a.sql这个文件，运行下面的命令即可执行a.sql中的内容
例子：sql>@ d:\a.sql 或者 sql>start d:\a.sql
2：编辑指定的sql脚本
格式：edit sql文件路径
例子：sql>edit d:\a.sql
3：输出查询结果到指定sql文件
说明：该命令可以将sqlplus屏幕上的内容输出到制定的文件中， 例如：l命令将select * from emp；的查询结果输出到指定位置的文件中 然后spool off类似于IO的开启/关闭
例子：spool d:\b.sql -> select * from emp; -> spool off
三：交互式命令
1：替代变量
格式：&
说明：可以替代变量，而该变量在执行时需要用户输入
例子：sql>select * from emp where test='&test'; oracle会提示用户输入值
四：显示、环境变量设置
1：设置行宽
格式：sql>show linesize -〉 sql>set linesize 宽度
说明：设置显示行的宽，默认是80个自己字符
2：设置每页显示行数
格式：sql>show pagesize -〉 sql>set pagesize 行数
说明：设置每页显示的行数默认是14
五：用户管理
1：创建用户
格式：create user 【用户名】identified by 【密码】
说明：需要DBA权限
2：修改密码
格式：password 【用户名】
alter user 【用户名】 identified by 【新密码】
说明：在给其他用户修改密码时 需要具有DBA的权限或拥有alter user的系统权限
3：删除用户格式：drop user 【用户名】 [cascade]
说明：如果要删除的用户，已经创建了表，要在删除时加上一个参数 cascade
六：权限和角色管理
1：说明：
1）权限包含系统权限和对象权限
系统权限：用户对数据库的相关权限
对象权限：用户对其他用户的数据对象操作的权限
2）角色：角色是指由系统权限集合，通常给某个用户授予权限时如果没有角色存在的话，那么需要一条一条的操作，角色的存在，就是使得授权变得很方便。通常一个角色由多个系统权限组成。常用的角色有三个connect（7种权限）、dba、resource（在任何表空间建表）
2：分配权限
格式：grant 【权限名】 to 【用户名】
3：收回权限
格式：revoke 【权限名】 from 【用户名】
4：分配角色
格式：grant 【角色名】 to 【用户名】
5：权限和管理例子：
1）创建用户
create user testuser identified by 000000;
2）使用testuser能连接
grant create session to testuser;
3）让testuser能够在任何表空间下建表
grant resource to testuser;
4) 创建一个表
create table users(id number(2)，username varchar2(50));
5）插入数据
insert into users(id,username) values(1,'name1');
6）登录到scott给testuser授权，让testuser可以查看scott下的emp表
grant select on emp to testuser;
7）使用testuser登录，查看emp表
select * from scott.emp;
当使用testuser执行更新emp表的时候： update scott.emp set username='name2' where id='1; ，将提示权限不足，因为scott只给了stu查看的权利，没有更新权限，如果仍然想更新，要到scott下进行授权。
8）登陆到system下回收resource角色
revoke resource from testuser;
9）登陆scott回收testuser的select权限
revoke select on emp from testuser;
6：用户权限传递
说明：当希望testuser用户可以去查询scott的emp表时，还希望testuser能够把这个权限继续传给其他用户时，就可以使用权限传递。
1）对象权限传递，增加with grant option
格式：grant select on emp to 用户名 with grant option
2）系统权限，增加with admin option，当system给用户授权时，会给用户以及传递的其他用户授权的能力
格式：grant connect to 用户名 with admin option
7：用户权限传递例子
1）system登陆，重新建立两个用户
create user test1 identified by 000000;
create user test2 identified by 000000;
2）给test1分配connect角色
grant connect to test1 with admin option;
3）scott登陆，给test1授权emp表的select的权限
grant select on emp to test1 with grant option;
4）test1登陆，并给test2授权
grant select on scott.emp to test2;
grant connect to test2;
5）test2登陆，并查询scott.emp
select * from scott.emp;
6）系统权限和对象权限有所不同的。对于系统权限，test1分配给test2之后不再收回，对象权限却随着test1的权限被收回也被同时收回了
例如：
A：登录到scott下收回test1的权限
revoke select on emp from test1;
revoke connect from test1;
B：test2登录，可成功登陆，证明test2表的connect角色并没有收回，但查询失败，证明对象权限已经被收回。
七：用profile管理用户口令
说明：profile是口令限制，资源限制的命令集合。当建立数据库时，oracle会自动建立名称为default的profile。当建立用户没有制定profile选项，那oracle就会将default分配给用户。
1：创建并指定profile
格式：sql>create profile 【profile名称】 【限制条件】;
sql>alter user 用户名 profile 【profile名称】;
2：删除profile
格式：drop profile 【profile名称】 [cascade]
说明：cascade表示如果已经将profile分配给某个用户时，仍要删除profile，就要加上cascade
3：创建并指定profile例子
1）：帐号锁定
说明：指定登录时最多可以输入密码的次数，也可以指定用户锁定的时间，以天为单位。一般用dba的身份去执行命令例如：指定stu最多只能尝试三次登录，锁定时间为2天
例子：sql>create profile lock_account limit failed_login_attempts 3 password_lock_time 2;
sql>alter user test1 profile lock_account;
账号解锁
格式：sql>alter user 用户名 account unlock;
2）：终止口令
说明：为了让用户定期修改密码，可以使用终止口令的指令完成，同样这个命令也要dba身份来操作
例子：给test1创建一个profile文件，要求该用户每隔10天要修改登录密码，宽限期2天
sql>create profile test1_profile limit password_life_time 10 password_grace_time 2;sql>alter user test1 profile test1_profile;
3）：口令历史
说明：如果希望用户在修改密码时，不能使用以前用过的密码，可以使用口令历史，这样oracle就会将口令修改的信息存放在数据字典中，这样当用户修改密码时，oracle就会对新密码与就得进行对比，如果一样提示用户重新输入，password_reuse_time 10 表示10天后口令可重复使用
例子：sql>create profile password_history limit password_life_time 10 password_grace_time 2 password_reuse_time 10
sql>alter user test1 profile password_history;
八：表管理
1：Oracle数据类型
| 数据类型 | 参数 | 描述 |
| char(n) | n=1 to 2000字节 | 定长字符串，n字节长，如果不指定长度，缺省为1个字节长（一个汉字为2字节） |
| varchar2(n) | n=1 to 4000字节 | 可变长的字符串，具体定义时指明最大长度n， |
| number(m,n) | m=1 to 38 | 可变长的数值列，允许0、正值及负值，m是所有有效数字的位数，n是小数点以后的位数。 |
| date | 无 | 从公元前4712年1月1日到公元4712年12月31日的所有合法日期， |
| long | 无 | 可变长字符列，最大长度限制是2GB，用于不需要作字符串搜索的长串数据，如果要进行字符搜索就要用varchar2类型。 |
| raw(n) | n=1 to 2000 | 可变长二进制数据，在具体定义字段的时候必须指明最大长度n，Oracle 8i用这种格式来保存较小的图形文件或带格式的文本文件，如Miceosoft Word文档。 |
| long raw | 无 | 可变长二进制数据，最大长度是2GB。Oracle 8i用这种格式来保存较大的图形文件或带格式的文本文件，如Miceosoft Word文档，以及音频、视频等非文本文件。 |
| blob | 无 | 三种大型对象(LOB)，用来保存较大的图形文件或带格式的文本文件，如Miceosoft Word文档，以及音频、视频等非文本文件，最大长度是4GB。 |
| bfile | 无 | 在数据库外部保存的大型二进制对象文件，最大长度是4GB。 |
2：创建表
1）：建表
例子：sql>create table student( Idnumber(4),Name varchar(20),Sex char(2),birthday date);
2）：向已经建立的表中添加字段
例子：sql>alter table student add(ClassId number(2));
3）：修改字段的长度
例子：sql>alter table studentmodify(Name varchar2(50) );
4）：修改字段的类型/或名字（不能有数据）
例子：sql>alter table student modify(Name char(20));
sql>alter table student rename Name to Sname;
5）：删除一个字段（慎重使用）
例子：alter table student drop column Sex;
6）：修改表的名字
例子：sql>rename student to stu;
7）：删除表
例子：drop table student;
8）：查看表结构
例子：desc student;
3：操作表
1）：添加数据
说明：oracle中默认的日期格式‘DD-MON-YY’ （日-月-年），想要修改日期的默认格式可以这样做 sql>alter session set nls_date_format='YYYY-MM-DD';（该命令是临时的）
例子：insert into student values(1,'张三','男','01-5月-05');
在修改日期默认结构后：insert into student values(1,'张三','男','2000-08-31');
2）：插入部分字段,前提是未插入的字段允许为null
例子：insert into student(Id,Name) values(1,'张三');
3）：插入空值
例子：insert into student(Id,Name,Sex) values('1',null,null);
4）：查询Name为空的一条记录
例子：select * from student where Name is null;
5）：修改字段
例子：update student set sex=‘女’ where Id=‘1’; update student set sex=‘男’,Name='赵四' whereId=‘1’;
6）：删除数据，删除表结构
例子：delete from student where Id=‘1’; delete from student; truncate table student; drop table student;
7）：恢复数据
说明：用delete from student 时数据可恢
例子：
A：首先要设置一个保存点（sp是保存点名称，可以随意起名，作用是将数据保存在日志中）
savepoint sp;
B：删除数据
delete from student;
C：回滚数据
rollback to sp;
8）：查询略去重复行
例子：select distinct deptno,job from emp;
9）：快速向数据库中插入大量数据
说明：使用这个语句的前提是表中至少要有一条数据
例子：insert into users(userid,username,userpass) select * from user;
10）：查询的常用注意点
A ：使用算数表达式
例子：select ename "姓名",sal*12 as "年收入" from emp;
B:：如果计算表达式中有一个null值那么计算结果就为null，使用nvl函数处理，nvl(comm,0)的意思是如果comm为null,那么按0计算,不是0按本身计算
例子：select sal*13+nvl(comm,0) "年工资" ,ename from emp;
C：用"||"连接字符串
例子：select ename || 'is a' || job from emp;
D：如何使用like操作符，%： 表示0到多个字符 _：表示任意单个字符
例子：显示首字母为s的员工 select ename from emp where ename like ‘s%’;
显示第三个字母为大写O的所有员工的姓名和工资 select ename, sal from emp where ename like '__O%';
E：desc 为逆序 asc为顺序(默认)
F：使用列的别名排序
例子：select ename ,sal*12 "年薪" from emp order by "年薪" asc;
九:汇总
Oracle
1、set linesize 100; 设置长度
2、set pagesize 30; 设置每页显示数目
3、em a.sql 打开记事本
4、@ a 执行文件a中的代码，可指定文件的路径 @d:a.txt
5、conn 用户名/密码 根据用户名和密码连接数据库 如果连接超级管理员(sys) 则应加上as sysdba;
6、show user; 显示当前连接的用户
7、select * from tab; 得到当前用户下的所有表
8、desc temp; 查看表结构
9、/ 继续执行上一个查询语句
clear scr; 清屏
字符函数
10、select upper('coolszy') from dual; 将小写字母转换成大写，dual 为一虚表
11、select lower('KUKA') from dual; 将大写字母转换成小写
12、select initcap('kuka') from dual; 将首字母大写
13、select concat('Hello',' world') from dual; 连接字符串，但没有||好用select concat('Hello','world') from dual;
14、select substr('hello',1,3) from dual; 截取字符串
15、select length('hello') from dual; 求字符串长度
16、select replace('hello','l','x') from dual; 替换字符串
17、select substr('hello',-3,3) from dual; 截取后三位
数值函数
18、select round(789.536) from dual; 四舍五入，舍去小数
19、select round(789.536,2) from dual; 保留两位小数
20、select round(789.536,-1) from dual; 对整数进行四舍五入
21、select trunc(789.536) from dual; 舍去小数，但不进位
22、select trunc(789.536,2) from dual;
23、select trunc(789.536,-2) from dual;
24、select mod(10,3) from dual; 返回10%3的结果
日期函数
25、select sysdate from dual; 返回当前日期
26、select months_between(sysdate,'16-6月 -08') from dual; 返回之间的月数
27、select add_months(sysdate,4) from dual; 在日期上加上月数
28、select next_day(sysdate,'星期一') from dual; 求下一个星期一
29、select last_day(sysdate) from dual; 求本月的最后一天
转换函数
30、select to_char(sysdate,'yyyy') year,to_char(sysdate,'mm'),to_char(sysdate,'dd') from dual;
31、select to_char(sysdate,'yyyy-mm-dd') from dual;
32、select to_char(sysdate,'fmyyyy-mm-dd') from dual; 取消月 日 前面的0
33、select to_char('20394','99,999') from dual; 分割钱 9表示格式
34、select to_char('2034','L99,999') from dual; 加上钱币符号
35、select to_number('123')*to_number('2') from dual;
36、select to_date('1988-07-04','yyyy-mm-dd') from dual;
通用函数
37、select nvl(null,0) from dual; 如果为null，则用0代替
38、select decode(1,1,'内容是1',2,'内容是2',3,'内容是3') from dual; 类似于 switch...case...
事务处理
39、commit; 提交事务
40、rollback; 回滚事务
41、select rownum from table; 在没一列前面显示行号
42、drop table 表名 cascade constraint
on delete casecade 当父表中的内容被删除后，子表中的内容也被删除
43、desc表名 显示表的结构
44、create user [username] identified by [password] 创建新的用户
45、grant 权限1、权限2...to 用户 给创建用户权限
ex:grant create session to [username] 此时只能连接到数据库
grant connect,resource to [username] 此时权限能满足要求
46、alter user [username] identified by [password] 修改用户密码
47、alter user [username] password expired 下次登录时提示修改密码
48、alter user [username] account lock 锁住用户
49、alter user [username] account unlock 解锁锁用户
50、grant select,delete on scott.emp to [username] 把scott下emp表的两个权限给用户
51、revoke select ,delete on scott.emo from [username] 回收权限
更全面
02.oracle函数
1数值型函数
1.01.返回绝对值.abs()
1.02.返回正负值.sign()
1.03.返回较大的最小整数.ceil()
1.04.返回较小的最大整数.floor()
1.05.返回x的y次幂.power(x,y)
1.06.返回常量e的y次幂.exp(y)
1.07.返回以x为底的y的对数.log(x,y)
1.08.返回以常量e为底的y的对数.ln(y)
1.09.返回x除以y的余数.mod(x,y)
1.10.返回四舍五入后的值.round()
1.11.返回截取后的值.trunc()
1.12.返回x的平方根.sqrt(x)
1.30.三角函数
2字符型函数
2.01.返回字符的ASCII码.ASCII()
2.02.返回ASCII码为x的字符.CHR(x)
2.03.连接两个字符串.CONCAT()
2.04.把每个单词首个字母变为大写.INITCAP()
2.05.将整个字符串转换为小写.LOWER()
2.06.将整个字符串转换为大写.UPPER()
2.07.把每个单词首个字母变为大写.NLS_INITCAP()
2.08.把整个字符串转换为小写.NLS_LOWER()
2.09.将整个字符串转换为大写.NLS_UPPER()
2.10.字符串中搜索字符位置(全角算1字符).INSTR()
2.11.字符串中搜索字符位置(全角算2字符).INSTRB()
2.12.返回字符串的长度(全角算1字符).LENGTH()
2.12.返回字符串的长度(全角算2字符).LENGTHB()
2.13.返回字符串的长度(其它).LENGTHC().LENGTH2().LENGTH4()
2.14.在左边添加字符.LPAD()
2.15.在右边添加字符.RPAD()
2.16.删除左边字符串.LTRIM()
2.17.删除右边字符串.RTRIM()
2.18.替换子字符串.REPLACE()
2.19.字符串语音表示形式.SOUNDEX()
2.20.截取子字符串(全角算1字符).SUBSTR()
2.21.截取子字符串(全角算2字符).SUBSTRB()
2.22.替换子字符.TRANSLATE()
2.23.删除左边和右边字符串.TRIM()
3日期函数
3.01.返回系统当前日期.sysdate
3.02.返回指定月数后的日期.add_months()
3.03.返回本月最后一天的日期.last_day()
3.04.返回2个日期间隔月数.months_between()
3.05.返回时区的对应时间.NEW_TIME()
3.06.四舍五入后的期间第一天.round()
3.07.返回日期所在期间的第一天.trunc()
3.08.返回下周某一天的日期.NEXT_DAY()
3.09.提取时间日期中数据.extract()
3.10.返回会话中的日期和时间.localtimestamp
3.11.返回当前会话时区中的当前日期和时间.current_timestamp
3.12.返回当前会话时区中的当前日期.current_date
3.13.返回数据库时区设置.dbtimezone
3.14.返回当前会话时区.SESSIONTIMEZONE
3.29.变动日期时间数值.INTERVAL
4转换函数
4.01.字符串转换为rowid值.chartorowid()
4.02.rowid值转换字符串.ROWIDTOCHAR()
4.03.字符串语言字符集转换.CONVERT()
4.04.16进制转换为二进制.HEXTORAW()
4.05.二进制转换为16进制.RAWTOHEX()
4.06.数字或日期转换为字符串.TO_CHAR()
4.07.字符串转换为日期型.TO_DATE()
4.08.字符串转换为数字型.TO_NUMBER()
4.09.半角转化为全角.TO_MULTI_BYTE()
4.10.全角转化为半角.to_single_byte()
4.11.字符集名称转为ID.nls_charset_id()
4.12.字符集ID转为名称.nls_charset_name()
5聚组函数
5.01.统计平均值.AVG()
5.02.统计合计值.SUM()
5.03.统计标准误差.STDDEV()
5.04.统计方差.VARIANCE()
5.05.统计查询所得的行数.count()
5.06.统计最大值.MAX()
5.07.统计最小值.MIN()
6分析函数
6.00.oracle分析函数
6.01.连续求和分析函数.sum(...) over(...)
6.02.排序值分析函数.RANK()和dense_rank()
6.03.排序后顺序号分析函数.ROW_NUMBER()
6.04.取上下行数据分析函数.lag()和lead()
7其它函数
7.01.返回数据类型、字节长度和在内部的存储位置.DUMP()
7.02.返回表达式列表中最大值.greatest()
7.03.返回表达式列表中最小值.least()
7.04.为空值赋值.nvl().nvl2()
7.05.返回当前会话对应的数据库用户名.user
7.06.返回当前会话所对应的用户id号.uid
7.07.返回当前会话上下文属性.userenv()
7.08.条件取值.decode()
7.09.相等返回空.NULLIF()
7.10.返回列表第一个不为空的表达式.COALESCE()
7.11.返回当前行号.rownum
7.12.指定一个外部二进制文件.BFILENAME()
7.13.返回X的大小(字节)数.VSIZE(X)
7.14.条件取值.case when then end
7.15.产生32位的随机数.sys_guid()
7.16.返回系统数据.SYS_CONTEXT()
7.17.生成随机数值或者字符串dbms_random
7.18.取得Internet中的主机名和IP地址
常用函数有:
1.count(*),count(distinct 列名); 统计总量
2.to_char(sysdate,'yyyy-mm-dd hh24:mi:ss');
3.to_date(日期字符串,'yyyy-mm-dd hh24:mi:ss');
4.sum(数值表达式);
5.nvl(表达式,0); 如果表达式为空时赋值为0,否则不变
6.substr(字符串,起始值,数值); 截取字符串
6.1 instr(字符串,查询的字符,起始位置) 获取某字符或字符串的位置
7.decode(条件,值1,result1,值2,result2,default); 类似于 switch...case...
8.translate(字符串,要替换的字符,替换成的字符); 替换字符
9.ceil(数值); 返回较大的最小整数.如:2.2 --> 3
10.floor(数值); 返回较小的最大整数.如:2.2 --> 2
11.round(数值,小数点前后数值); 返回四舍五入后的值. 如:round(123.567,2) --> 123.57
12.trunc(数值,小数点前后数值); 返回截取后的值. 如:trunc(123.567,2) --> 123.56
13.add_months(时间,数值); 获取某时间的年份 如:add_months(sysdate,-3) -->返回上三个月的此时的时间
14：其他
select add_months(last_day(sysdate)+1,-2) from dual; 获取上个月的第一天
select add_months(last_day(sysdate),-1) from dual; 获取上个月的最后一天
另一种:select trunc(ADD_MONTHS(SYSDATE, -1),'MM'), trunc(SYSDATE,'MM') from dual;
15：注意
1）：两时间相减是得天,如果要得秒,就必须再乘以24*60*60即可
2）：获取时间中的年月日:to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd');
3）：.对于是否字符串的数值型数据,都可以用like '%139%'来抽取数据
4）：to_date(translate(substr('[08/九月/2009:16:44:01+8000]', 2, 19),'九月','09'),'dd/mm/yyyy hh24:mi:ss');将字符串抽取并转换成时间格式.
5）：在oracle中查询表中各列数据信息，注意表名必须大写
例子：select column_id, column_name, data_type, data_length, data_precision, data_scale,nullable,data_default from user_tab_columns where table_name = 'ACC_NBR' order by column_id
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
