SqlPlus常用命令
- Oracle11g 登录 sqlplus 两种方式
- Scott@orcl 密码
- Scott/system@orcl
- 使用查询、插入、删除、update 等等类似 sql 的语句的时候需要加;结尾。
- 查看登录用户：show user；
- 切换用户：
- Conn[ect]
- Conn system/system@orcl
- Conn sys@orcl as sysdba(sysoper)
- 使用 conn sys@orcl 后面一定要跟上 as 角色权限
- 断开连接：disc[onnect]
- passw[word] 用户
- 以高级用户重置低级用户密码
- 普通用户也可以给自己重置密码（前提是得知道自己的密码）
- exit：退出，该命令会断开与数据库的连接，同时退出数据库
文件操作命令
- start
- 调用外部的脚本
- 在地盘存放了 select * from dept;的语句，并保存为 dept.sql
- sql>start d:\dept.sql
- edit
- 该命令可以编辑指定的脚本
- Sql>edit d:\dept.sql
- 在打开的sql脚本中编辑sql
- spool
- 该命令可以将sqlplus屏幕上的内容输出到特定文件里
- Sql>spool d:\dept.sql
- Sql>select * from dept;
- Sql>spool off
- 注意：当 spool off 后，方把所有的输出写入到外部 dept.sql 文件里
- &
- 可以替代变量，变量的值在交互时由用户输入
- Sql>select * from dept DNAME=’&DNAME’;
- Sql>根据提示输入相应的值：例如 SALES
用户管理
- 创建用户（sys system用户）
- create user username identified by password
- 注意：密码不能以数字开头
- 新创建的用户不能通过conn立刻登录，需要先赋予一定的权限
- grant create session to username;
- sql>conn edu/system@orcl 就可以登录了
管理用户
-
创建一个用户 software，然后给他分配权限，可以让 software 登录数据库、创建表、可以操作自己创建的表；回收角色，最后删除用户。
-
使用 system 创建 software 用户,密码设置为 system
create user software identified system;
-
要想让 software 登录（连接）数据库，需要给其 connect、session 权限（角色）；
grant connect to software;
grant resource to software;
-
使用 software 用户登录
conn software/system@orcl
-
Software 创建一张表
create table users(id number)
-
删除 software 用户
drop user software [cascade]
注意：当 software 拥有自己的数据对象时，加上选项 cascade，一并把该用户拥有数据对象删除
赋予权限
访问其他用户表
-
完成一个功能，让 xiaohong 用户可以查询 scott 的 emp 表
-
Scott登录
conn scott/system
-
赋予权限
grant select[/update/delete/insert/all] on emp to xiaohong
-
xiaohong登录
conn xiaohong/system
-
小红查询 scott 的 emp 表
select * from scott.emp;
常用数据类型
- 三大类：文本，数值，时间
文本
-
char（size）
- 存放定长的字符串，最大存放2000个字符
- 长度不能超过size，不够size用空格补齐
- 字符串的长度一般是16的倍数，32，64，128
-
varchar2（size）
变长，最大可存放4000个字符
-
nchar(size),nvarchar2(size)
n代表的意思是编码格式为Unicode编码，无论中文还是英文都用一个字符来存放数据
比如：“a”，占用一个字符，“软”，也是占用一个字符
-
clob（size）
变长，字符型的大对象，最大8tb
在varchar2不够用的情况下使用clob
-
blob（size）
变长，二进制数据，可以存放图片声音等，8tb
数值
-
number（p，s）
变长，可以存放整数和小数
p表示有效位（从左开始），s表示小数位；范围：p[1,38],s[-84,127]
Number 可以表示的数值范围：-1.0e-130 ~~1.0e+126
占用机器码的空间是 1~22bytes
日期
-
date
oracle dd-mm-yyyy
Sql->insert into test1(’01-1 月-14’);
-
timestamp(n)
邮戳的时间类型
与 date 区别，邮戳（timestamp）当某条数据更新时，使用邮戳字段也会自动更新。
修改表字段
- alter table tablename add (colname datatype);
- alter table tablename modify (colname datatype);
- alter table tablename drop column colname;
- rename old_tablename to new_tablename;
- drop table tablename;
分页查询
-
通用模板：查询emp表中第4到6行数据
select t2.* from (select t1.empno,t1.ename,rownum rw from (select * from emp) t1 where rownum<=6 ) t2 where rw >=4;
-
请按照入职时间的先后进行排序，查询从 7 行到 10 行的雇员是谁。
select t2.* from (select t1.empno,t1.ename,rownum rw from (select * from emp order by hiredate) t1 where rownum<=10 ) t2 where rw >=7;
合并查询
-
Union,union all,intersect,minus
-
Union
并集：该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中重复的行。
-
Union all
该操作符不会去掉重复行，而且不排序显示，重复的内容显示 2次。
select ename,sal,job from emp where sal>2500 union all select ename,sal,job from emp where job=’MANAGER’
-
Intersect
该操作符用于取得两个结果集的交集
-
Minus
使用该操作符用于取得两个结果集的差集，它只会显示存在第 1个集合中，而不存在第二个集合中的数据。
函数
单行函数
-
REPLACE(‘string’,’s1’,’s2’)
string：希望被替换的字符或变量
s1：被替换的字符串
s2 ：要替换的字符串
-
INSTR(C1,C2,I,J)
在一个字符串中搜索指定的字符,返回发现指定的字符的位置;
C1：被搜索的字符串
C2：希望搜索的字符串
I ：搜索的开始位置,默认为 1
J ：出现的位置,默认为 1
-
ASCII（s）
返回与指定的字符对应的十进制数;
-
CHR（number）
给出整数,返回对应的字符;
-
CONCAT
连接两个字符串;
-
INITCAP
返回字符串并将字符串的第一个字母变为大写;
-
SUBSTR(string,start,count)
取子字符串,从 start 开始,取 count 个
-
LENGTH
返回字符串的长度;
-
LOWER
返回字符串,并将所有的字符小写
-
UPPER
返回字符串,并将所有的字符大写
-
Rpad 和 lpad 函数
RPAD 在列的右边粘贴字符
LPAD 在列的左边粘贴字符
不够字符则用*来填满
-
LTRIM 和 RTRIM 函数
LTRIM 删除左边出现的字符串
RTRIM 删除右边出现的字符串
-
CEIL（不是四舍五入，向上取整）
返回大于或等于给出数字的最小整数
-
FLOOR
对给定的数字取整数
-
MOD(n1,n2)
返回一个 n1 除以 n2 的余数
-
POWER(n1,n2)
返回 n1 的 n2 次方根
-
ROUND 和 TRUNC
按照指定的精度进行舍入
Round 四舍五入
Trunc 截取（可以指定截取到哪 1 位，没有指定的话，则默认截取到整数）
-
SYSDATE
用来得到系统的当前日期
-
TO_DATE(string,’format’)
将字符串转化为 ORACLE 中的一个日期
-
TO_NUMBER
将给出的字符转换为数字