Oracle 基础
Oracle 基础知识：
1、系统用户 sys、system、sysman --均为管理员权限，权限由高到低 scott --密码tiger，普通用户，默认被锁定，需要解除 为用户授权：grant 权限 to username; 创建用户：create user c##用户名 identified by 密码; --用户名以c##开头 用户授权：grant 权限 to username; 修改密码：alter user 用户名 identified by 新密码; 备注：dba_开头的是全库所有对象（管理员权限才能访问）， user_开头的是当前用户对象， all_开头的是当前用户有权限访问的所有对象。 2、用户登录 打开SQLplus：cmd-> sqlplus username/password @orcl [as sysdba|sysoper] --orcl是服务名 设置SQLplus显示行宽：set line[size] number 清屏：host cls 编辑上一条执行语句：ed 3、切换用户 conn username/password [as sysdba] 4、查看当前用户 show user --Oracle命令不需要";"结束，SQL语句才需要";"结束 5、用户字典表（保存用户信息，包含用户表空间等） 所有用户：dba_users 当前用户：user_users 6、启用被锁用户 alter user username account unlock; 7、表空间分类 永久表空间 临时表空间 undo表空间 8、表空间字典表 所有用户：dba_tablespaces 当前用户：user_tablespaces 9、更改用户默认表空间和临时表空间 alter user username default|temporary tablespace tbsname; 10、创建表空间（需要对应创建一个数据文件） create tablespace tbsname datafile 'xx.dbf' size xm; --默认表空间 create temporary tablespace tbsname tempfile 'xx.dbf' size xm; --临时表空间 查看表空间数据文件位置：select file_name from `dba_data_files` where tablespace_name='大写表空间名'; --临时表空间数据文件表 dba_temp_files 11、修改表空间状态（联机、脱机、只读） alter tablespace tbsname online|offline|read only; 查看表空间状态：select status from `dba_tablespaces` where tablespace_name='TEST_TBS'; 提示：online与read write等效。 12、给表空间增加或删除数据文件 alter tablespace tbsname add datafile 'xx.dbf' size xm; alter tablespace tbsname drop datafile 'xx.dbf'; 提示：最初的数据文件不可删除。 13、删除表空间 drop tablespace tbsname; --只删除表空间 drop tablespace tbsname including contents and datafiles; --删除表空间及其内容和数据文件 14、常用数据类型 字符串：char(n) --定长，n最大2000个字节。注意是字节 nchar(n) --定长，n最大2000个字符。注意是字符。下同 varchar2(n) --可变长，n最大4000个字节。常用 nvarchar2(n) --可变长，n最大4000个字符 --注意：带前缀"n"指存储Unicode，长度是字符数，而不是字节数，更适合存储中文 --字符串必须是单引号，而服务名、表名可以用双引号！ 数值型：number(p,s) --定点型，p是最大有效位数（不超过38个），s是小数位数，省略s则向上取整 integer --整型，四舍五入，等效于number(38) 日期型：date --精确到秒 timestamp --时间戳，当前时间戳：current_timestamp 大字符：clob --字符串数据，最大长度4G blob --二进制数据，最大长度4G 15、增加字段 alter table tbname add colname datatype; --add后面不要带column，区别于MySQL 16、更改字段类型 alter table tbname modify colname datatype; 17、删除字段 alter table tbname drop column colname; --drop后面带上column 18、字段改名 alter table tbname rename column oldname to newname; 19、表改名 rename oldname to newname; 20、清空表 truncate table tbname; 21、删除表结构 drop table tbname; 22、添加数据 insert into tbname (column1, column2, ...) values(value1, value2, ...); 23、复制表 建表时：create table newtable as select columns from oldtable; 添加时：insert into newtable (columns) select columns from oldtable; --对应字段类型一致，名称不一定一致 24、更新数据 update tbname set column1=value1, column2=value2 where conditions; 25、删除数据 delete from tbname where conditions; 26、约束（系统约束信息表："user_constraints"） 非空约束：not null --默认null，可为空 建表时添加：create table tbname(id number(6) not null); 修改时添加：alter table tbname modify colname not null; 主键约束：primary key --一张表只能一个主键，但可以是联合主键 建表时添加：create table tbname(id number(6) primary key); 联合主键：create table tbname(id number(6), name varchar2(32), constraint cons_name primary key(id,name)); 修改时添加：alter table tbname add constraint pk primary key(id,...); --提示：Oracle没有自增字段！！ 外键约束：foreign key … references … 建表时添加：create table tbname(fk number(32) references mastable(pk) on delete cascade); 修改时添加：alter table tbname add constraint fk foreign key(id) references mastable(pk) on delete cascade; 唯一约束：unique --可以有空值，但是空值只能有一个 建表时添加：create table tbname(id number(6) unique); 修改时添加：alter table tbname add constraint un unique(id); 检查约束：check（范围） 建表时添加：create table tbname(id number(6) check(id>0 and id <100000)); 修改时添加：alter table tbname add constraint ck check(id>0 and id <100000); 27、更改约束名称 alter table tbname rename constraint oldname to newname; 28、禁用和删除约束 alter table tbname disable constraint cons_name; --禁用约束 alter table tbname drop constraint cons_name; --删除约束 alter table tbname drop primary key; --删除主键 29、case … when … 语句（结果作为一个字段） 例： select id,username, case when id<=1 then '一等奖' when id>=2 then '二等奖' end as 等级奖 from users; 30、常用函数 字符串函数： --------------------------------------------+-------------------------------------------------------------------- 函数 | 描述 --------------------------------------------+-------------------------------------------------------------------- lower(str) | 将字符串表达式str中的所有大写字母转换为小写字母 --------------------------------------------+-------------------------------------------------------------------- upper(str) | 将字符串表达式str中的所有小写字母转换为大写字母 --------------------------------------------+-------------------------------------------------------------------- initcap(str) | 首字母转换成大写 --------------------------------------------+-------------------------------------------------------------------- substr(str,start,length) | 返回字符串表达式str中从第start开始的length个字符 --------------------------------------------+-------------------------------------------------------------------- length(str) | 返回字符串表达式str的长度 --------------------------------------------+-------------------------------------------------------------------- ascii(char) | 取char的ascii值 --------------------------------------------+-------------------------------------------------------------------- chr(ascii) | 取ascii对应的字符值 --------------------------------------------+-------------------------------------------------------------------- replace(str,search_str[,replace_str]) | 将字符串str中的子串search_str替换成replace_str；如果search_str=null，返回str； | 如果replace_str=null，则会去掉str中的search_str --------------------------------------------+-------------------------------------------------------------------- instr(str1,str2[,n[,m]]) | 获取子串str2在字符串str1中的位置。n为其实搜索位置，m为子串出现的次数； | n为负，则从尾部开始搜索；n、m默认为1 --------------------------------------------+-------------------------------------------------------------------- lpad(str1,n,str2) | 在字符串str1的左端填充字符串str2直到长度达到n；str2默认为空格， | 如果str1.length>n，则返回str1左端的n个字符 --------------------------------------------+-------------------------------------------------------------------- rpad(str1,n,str2) | 在字符串str1的右端填充字符串str2直到长度达到n；str2默认为空格， | 如果str1.length>n，则返回str1左端的n个字符 --------------------------------------------+-------------------------------------------------------------------- ltrim(str[,set]) | 去掉字符串str左端包含的set中的任意字符 --------------------------------------------+-------------------------------------------------------------------- rtrim(str[,set]) | 去掉字符串str右端包含的set中的任意字符 --------------------------------------------+-------------------------------------------------------------------- trim(str from string) | 从字符串string的头尾或者两端截断特定字符str --------------------------------------------+-------------------------------------------------------------------- concat(str1,str2) | 连接字符串，同"||"的作用一样 --------------------------------------------+-------------------------------------------------------------------- 日期函数： --------------------------------------------+-------------------------------------------------------------------- 函数 | 描述 --------------------------------------------+-------------------------------------------------------------------- sysdate | 返回系统当前日期和时间 --------------------------------------------+-------------------------------------------------------------------- current_date | 返回当前会话时区所对应日期时间 --------------------------------------------+-------------------------------------------------------------------- next_day(day,char) | 返回指定日期day后的第一个工作日char所对应的日期 --------------------------------------------+-------------------------------------------------------------------- last_day(day) | 返回day日期所指定月份中最后一天所对应的日期 --------------------------------------------+-------------------------------------------------------------------- add_months(day,n) | 返回day日期在n个月后(n为正数)或前(n为负数)的日期 --------------------------------------------+-------------------------------------------------------------------- months_between(day1,day2) | 返回day1日期和day2日期之间相差的月份 --------------------------------------------+-------------------------------------------------------------------- round(day[,fmt]) | 返回日期的四舍五入结果。如果fmt指定'yyyy'，则7月1日为分界线； | 如果fmt指定'mm'，则16日为分界线；如果指定'dd'，则中午12:00为分界线；默认舍入到日 --------------------------------------------+-------------------------------------------------------------------- trunc(day[,fmt]) | 日期截断函数。如果fmt指定'yyyy'，则结果为本年度的1月1日； | 如果为'mm'，则将结果为本月1日；默认截断到'dd'，时间部分为0 --------------------------------------------+-------------------------------------------------------------------- extract(year|month|day from day) | 从日期中获取年月日 --------------------------------------------+-------------------------------------------------------------------- 类型转换函数： --------------------------------------------+-------------------------------------------------------------------- 函数 | 描述 --------------------------------------------+-------------------------------------------------------------------- to_char(str[,fmt]) | 将一个数字或日期转换成字符串，fmt如：yyyy-mm-dd hh24:mi:ss --------------------------------------------+-------------------------------------------------------------------- to_number() | 将字符型数据转换成数字型数据 --------------------------------------------+-------------------------------------------------------------------- to_date(str,fmt) | 将字符型数据转换为日期型数据，fmt如：yyyy-mm-dd hh24:mi:ss --------------------------------------------+-------------------------------------------------------------------- cast | 将一种built-in类型转换成另一种built-in类型 --------------------------------------------+-------------------------------------------------------------------- 聚合函数： --------------------------------------------+-------------------------------------------------------------------- 函数 | 描述 --------------------------------------------+-------------------------------------------------------------------- avg(colname) | 计算一列值的平均值 --------------------------------------------+-------------------------------------------------------------------- count(colname) | 统计一列中值的个数 --------------------------------------------+-------------------------------------------------------------------- max(colname) | 求一列值中的最大值 --------------------------------------------+-------------------------------------------------------------------- min(colname) | 求一列值中的最小值 --------------------------------------------+-------------------------------------------------------------------- sum(colname) | 计算一列值的总和 --------------------------------------------+-------------------------------------------------------------------- 其它常用函数： --------------------------------------------+-------------------------------------------------------------------- 函数 | 描述 --------------------------------------------+-------------------------------------------------------------------- decode(colname,val1,res1, | 类似于case...when语句。 val2,res2,…[,default]) | if语句的另一形式。将输入数值与参数列表比较，返回对应值。 | 应用于将表的行转换成列以及if语句无法应用的场合 --------------------------------------------+-------------------------------------------------------------------- sign(number) | 如果number大于0，sign则返回1；如果number小于0，sign则返回-1； | 如果number等于0，sign则返回0 --------------------------------------------+-------------------------------------------------------------------- trunc(number[,decimal_places]) | number是要截取的数字，decimal_places是要保留的小数位。这个参数必须是个整数。 | 如果此参数缺省，默认取整 --------------------------------------------+-------------------------------------------------------------------- greatest(expr1[,expr2]…) | 返回表达式中值最大的一个 --------------------------------------------+-------------------------------------------------------------------- least(expr1[,expr2]…) | 返回表达式中值最小的一个 --------------------------------------------+-------------------------------------------------------------------- nullif(expr1,expr2) | 如果expr1=expr2；则返回null，否则返回expr1 --------------------------------------------+-------------------------------------------------------------------- nvl(expr1,expr2) | 如果expr1存在；则返回expr1；如果expr1不存在；则返回expr2 --------------------------------------------+-------------------------------------------------------------------- nvl2(expr1,expr2,expr3) | 如果expr1存在；则返回expr2；如果expr1不存在；则返回expr3 --------------------------------------------+-------------------------------------------------------------------- 31、存储过程与存储函数 含义：存储在数据库中供所有用户程序调用的子程序。 区别：函数有return，过程没有return。 存储过程简单示例： 不带参数： create procedure hello as --声明部分 begin --子程序主体 dbms_output.put_line('hello world !'); end; SQLplus调用：先打开sqlplus输入"set serveroutput on"，然后"exec hello()"或"call hello()"; 带输入参数： create procedure incrsalary(uid in number) as orisalary users.salary%type; lastsalary users.salary%type; begin select salary into orisalary from users where id = uid; update users set salary = salary + 200 where id = uid; lastsalary := orisalary + 200; --注意！变量赋值用":=" dbms_output.put_line('旧工资：'||orisalary||' 新工资：'||lastsalary); --"||"，连接符 end; 32、存储函数 示例： create function querysalary(uid in number) return number --返回值类型 as sal users.salary%type; countyear users.salary%type; begin select salary into sal from users where id = uid; countyear := sal * 12; return countyear; --返回一个值 end; SQLplus调用：select querysalary(1) from dual; --不能用"exec"命令 33、in和out参数 存储过程和存储函数都可以一个或多个out参数； 存储过程虽无return，但可以通过out参数来实现返回值。 一般原则：若只有一个返回值，用存储函数；否则，就用存储过程。 示例： create or replace procedure userinfo(uid in number, uname out varchar2, usalary out number) as begin select username,salary into uname,usalary from users where id = uid; end; SQLplus调用：先申明out的接收变量 var uname varchar2; var usalary number; exec userinfo(1,:uname,:usalary); --":"引用变量来接收输出参数 select :uname,:usalary from dual; --使用输出
至此。转载请注明出处。
